mysql数据库---sql语句的练习

最新推荐文章于 2023-02-14 23:25:08 发布

大碗稀饭

最新推荐文章于 2023-02-14 23:25:08 发布

阅读量480

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_40935723/article/details/100600241

数据库专栏收录该内容

5 篇文章

订阅专栏

本文深入解析SQL语句的使用技巧，包括创建表、插入数据、查询、条件筛选、排序、聚合函数、分组及子查询等，通过实例演示如何高效管理和检索数据库信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql语句的相关总结

练习一：

#创建种类表：
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(10),
cdesc VARCHAR(31)
);
INSERT INTO category VALUES(NULL,'手机数码','电子产品'),
(NULL,'鞋靴箱包','江南皮革厂'),
(NULL,'香烟酒水','二锅头'),
(NULL,'酸奶','哇哈哈'),
(NULL,'小零食','辣条');

#创建商品表：
CREATE TABLE product(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
price DOUBLE,
pdate TIMESTAMP,
cno INT
);
INSERT INTO product VALUES
(NULL,'小米',998,NULL,1),
(NULL,'锤子',2888,NULL,1),
(NULL,'阿迪王',99,NULL,2),
(NULL,'老村长',88,NULL,3),
(NULL,'劲酒',35,NULL,3),
(NULL,'小熊饼干',1,NULL,4),
(NULL,'卫龙辣条',1,NULL,5),
(NULL,'旺旺大饼',1,NULL,5);

查询：
select …
from…
是先执行from在执行select

表别名：

select p.pname,p.price from product as p;

列别名：

select pname as 商品名称,price as 商品价格 from product ;

查询所有价格:

select price from product;

去重：

select distinct price from product;

select运算查询:仅仅在查询结果上做运算

select *,price*1.5 as 折后价 from product;

条件查询 where关键字

select * from product where price > 60;

<> : 不等于，标准sql语句
!= : 不等于，非标准sql语句

select * from product where price <> 88;

查询价格10到100的：

select * from product where price > 10 and price <100;

between…and…

select * from product where price between 10 and 100;

逻辑运算 and or not

–like ：模糊查询
_ :代表的是一个字符
% :代表的是多个字符

查询出名字带有饼的所有商品

select * from product where pname like '%饼%';

查询第二个名字是熊的所有商品

select * from product where pname like '_熊%';

in在某个范围中获得值
查询出商品分类ID在1，4，5里面的所有商品

select * from product where cno in (1,4,5);

排序查询： order by 关键字
asc: ascend 升序(默认)
desc: descend 降序
1,查询所有商品，按照价格排序

select * from product order by price ;
select * from product order by price desc;

2,查询名称中包含小的商品，按照价格升序排序

select * from product where pname like '%小%' order by price asc;

–聚合函数：
sum() : 求和
avg() : 求平均值
count() : 统计数量
max() : 最大值
min() : 最小值

1，获取商品价格的总和

select sum(price) from product;

2，获取商品价格的平均值

select avg(price) from product;

3，获取所有商品的个数

select count(*) from product;

注意：where 条件后面不能接聚合函数
4，查出商品价格大于平均价格的所有商品

select * from product where price >(select avg(price) from product);

分组：group by
1,根据cno字段分组，分组后统计商品的个数

select cno,count(*) from product group by cno;

2,根据cno分组，分组统计每组商品的平均价格，并且商品平均价格>60

select cno,avg(price)  from product group by cno having avg(price)>60;

having 关键字可以接聚合函数的出现在分组之后
where 关键字不可以聚合函数出现在分组之前

–编写顺序
– S…F…W…G…H…O

–执行顺序

F..W..G..H..S..O

from.. where.. group by.. having.. select.. order by

练习二：

员工信息表

CREATE TABLE emp(
	empno INT,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr	INT,
	hiredate DATE,
	sal	DECIMAL(7,2),
	comm DECIMAL(7,2),
	deptno INT
) ;

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);

部门信息表

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

基本查询

--所有员工的信息
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno;

--薪资大于等于1000并且小于等于2000的员工信息
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno and e.sal>=1000 
and e.sal<=2000;

--从员工表中查询出所有的部门编号
select distinct e.deptno from emp e;

--查询出名字以A开头的员工的信息
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno and e.ename like 'A%';

--查询出名字第二个字母是L的员工信息
select * from emp where ename like '_L%';

--查询出没有奖金的员工信息
select * from emp where comm is null;

--所有员工的平均工资
select avg(sal) as 平均工资 from emp;

--所有员工的工资总和
select sum(sal) as 总和 from emp;

--所有员工的数量
select count(*) from emp;

--最高工资
select max(sal) from emp;

--最少工资
select min(sal) from emp;

--最高工资的员工信息
select * from emp where sal = (select max(sal) from emp)

--最低工资的员工信息
select * from emp where sal = (select min(sal) from emp)

分组查询

--每个部门的平均工资
select deptno,avg(sal) as 平均工资 from emp group by deptno;

子查询

-- 单行子查询(> < >= <= = <>)
	-- 查询出高于10号部门的平均工资的员工信息
	select * from emp where sal >(select avg(sal) from emp where  deptno=10);
	
-- 多行子查询(in  not in any all)    >any  >all
	-- 查询出比10号部门任何员工薪资高的员工信息
	select * from emp where sal >(select max(sal) from emp where  deptno=10);
	
-- 多列子查询(实际使用较少)   in
	-- 和10号部门同名同工作的员工信息	
select * from emp where deptno<>10 and (ename,job) in (select ename,job from emp where deptno=10)
		
-- Select接子查询
	-- 获取员工的名字和部门的名字
	select e.ename , d.dname from emp e,dept d where e.deptno = d.deptno
	
-- from后面接子查询
	-- 查询emp表中经理信息
	select * from emp where job='MANAGER';
	
-- where 接子查询
	-- 薪资高于10号部门平均工资的所有员工信息
	select * from emp where sal>(select avg(sal) from emp where deptno=10)
	
-- having后面接子查询
	-- 有哪些部门的平均工资高于30号部门的平均工资	
	select deptno, avg(sal) from emp group by deptno having avg(sal)
	 >(select avg(sal) from emp where deptno=30)
	
-- 工资>JONES工资
select * from emp where sal >(select sal from emp where ename='JONES')

-- 查询与SCOTT同一个部门的员工
select * from emp where deptno = (select deptno from emp where ename='SCOTT')

-- 工资高于30号部门所有人的员工信息
select * from emp where sal >(select max(sal) from emp where deptno =30)

SQL查询的综合案例

--查询出高于本部门平均工资的员工信息
select * from emp e where sal > (select avg(sal) from emp where e.deptno = deptno)

--列出达拉斯加工作的人中,比纽约平均工资高的人
select * from emp where deptno =(select deptno from dept where loc='DALLAS') 
and sal>(select avg(sal) from emp where deptno=(select deptno from dept where loc='NEW YORK'))

--查询出各个部门薪水最高的员工所有信息
select * from emp e where sal =(select max(sal) from emp where deptno=e.deptno)