SQL-Day03

最新推荐文章于 2022-05-08 14:04:45 发布

JJ_Zhangjian

最新推荐文章于 2022-05-08 14:04:45 发布

阅读量438

点赞数

分类专栏： Databases 文章标签： SQL

本文链接：https://blog.csdn.net/JJ_Zhangjian/article/details/93227394

版权

Databases 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

2019年6月21日数据库 : 第三天

select 查询里的常用方法：

is null 和 is not null

1. 从员工表中查询 没有上级领导的员工姓名,工资
	select ename,sal from emp where mgr is null;
2. 查询有领导的员工姓名和上级领导的编号
	select ename,mgr from emp where mgr is not null;

别名

select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;

比较运算符 > < >= <= = !=和<>

1. 查询工资在2000以下的员工姓名和工资
	select ename,sal from emp where sal<2000;
2. 查询职位(job)是manager的员工姓名、工资、职位
	select ename,sal,job from emp where job='manager';
3. 查询工资小于等于1600的员工姓名、职位、工资
	select ename,job,sal from emp wehere sal<=1600;
4. 查询不是10号部门的员工姓名和部门编号（两种写法）
	select ename,deptno from emp where deptno!=10;
	select ename,deptno from emp where deptno<>10;
5. 查询商品表（t_item）中单价为23的商品信息
	select * from t_item where price=23 \G;
6. 查询单价不是8443的商品标题(title)
	select title from t_item where price!=8443;

去重 distinct

1. 查询员工表中有哪些职位
	select distinct job from emp;
2. 查询员工表中出现了那几个部门编号
	select distinct deptno from emp;

and 和 or

如果查询数据时需要同时满足多个条件则使用and &&

如果查询数据时只需要满足条件中的某一个则使用or ||

  1. 查询10号部门中工资低于2000的员工信息
  	select * from emp where deptno=10 and sal<2000;
  2. 查询部门是30号或者工资高于3000的员工信息
  	select * from emp where deptno=30 or sal>3000;

模糊查询 like

% 代表0或多个未知字符

_ 代表单个未知字符

  举例:   第一个字符是x       x%
  		最后一个字符是y     %y
  		包含               %x%
  		倒数第二个字符是x	   %x_
  		x开头y结尾         x%y
  		第二个字符是x 倒数第三个字符是y     _x%y__

查询名字以j开头的所有员工姓名
select ename from emp where ename like ‘j%’;
查询名字第二个字符是l的员工姓名和工资
select ename,sal from emp where ename like ‘_l%’;
查询t_item表中标题中包含记事本的商品标题
select title from t_item where title like ‘%记事本%’;
查询职位中包含an并且工资高于1500的员工姓名、工资、职位
select ename,sal,job from emp where job like ‘%an%’ and sal>1500;
查询有赠品的dell商品详情(卖点sell_point中包含“赠”，并且title包含dell)
select * from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;
查询单价低于100块钱的笔记本标题和价格(提示:title包含笔记本)
select title,price from t_item where price<100 and title like ‘%笔记本%’;
查询有图片的得力商品信息（image字段不为null,title包含得力）
select * from t_item where image is not null and title like ‘%得力%’;
查询不包含a的员工姓名
select ename from emp where ename not like ‘%a%’;

between x and y

查询工资在2000到3000之间的员工姓名和工资(要求包含2000和3000)
select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;
查询单价在50到100之间的商品标题和单价
select title,price from t_item where price between 50 and 100;
查询工资在1000到2000之外的员工姓名和工资
select ename,sal from emp where sal not between 1000 and 2000;

in

当查询字段值为多个值的时候使用

  1. 查询员工工资为800,1300,1500的员工信息
  	select * from emp where sal=800 or sal=1300 or sal=1500;
  	select * from emp where sal in(800,1300,1500);
  2. 查询商品价格为56,58,89的商品标题和单价
  	select title,price from t_item where price in(56,58,89);
  3. 查询工资不等于3000,5000,1500的员工姓名和工资
  	select ename,sal from emp where sal not in(3000,5000,1500);

综合练习题

1. 查询分类id为238,917的商品信息 (涉及字段自己查)
	select * from t_item where category_id in(238,917);
2. 查询价格在50-200之间的得力商品标题和价格
	select title,price from t_item where price between 50 and 200 and title like '%得力%';
3. 查询有上级领导并且工资小于2000的员工姓名、工资和领导编号
	select ename,sal,mgr from emp where mgr is not null and sal<2000;
4. 查询有奖金并且有上级领导的员工姓名、奖金、领导编号
	select ename,comm,mgr from emp where comm>0 and mgr is not null;
5. 查询名字中包含a,并且工资在3000以内的员工从事的职业有哪几种
	select distinct job from emp where ename like '%a%' and sal<3000;

排序 order by

格式: order by 字段名 asc/desc asc:升序(默认ascend) desc:降序 descend
order by 写在条件的后面
1. 查询工资小于3000的员工姓名和工资要求按照工资降序排序
```
 select ename,sal from emp where sal<3000 order by sal desc;
```
2. 查询10号部门每个员工的姓名和工资按照工资升序排序
```
 select ename,sal from emp where deptno=10 order by sal;
```
3. 查询有奖金的员工姓名、工资、奖金按照奖金降序排序
```
 select ename,sal,comm from emp where comm>0 order by comm desc;
```
4. 查询单价低于100的商品标题和单价并按照单价升序排序
```
 select title,price from t_item where price<100 order by price;
```

多字段排序 order by 字段1 asc/desc,字段2 asx/desc;

5、查询每个员工姓名，工资和部门编号按照部门编号降序拍寻

	select ename,sal,deptno from emp order by deptno desc,sal desc;

分页查询 limit 跳过的条数,请求的条数

- 请求第一页的10条数据  limit 0,10
- 请求第三页的10条数据  limit 20,10
- 请求第八页的5条数据   limit  35,5  
- 公式   limit (页数-1)*数量,数量
- 请求第四页的7条数据  limit 21,7

查询每个员工的姓名和工资,按照工资降序排序请求第三页的3条数据
select ename,sal from emp order by sal desc limit 6,3;
请求员工表中工资最高的前三名员工的信息
select * from emp order by sal desc limit 0,3;
查询商品标题和单价按照单价升序排序第三页的5条数据
select title,price from t_item order by price limit 10,5;
查询30号部门中工资最高的员工信息
select * from emp where deptno=30 order by sal desc limit 0,1;

数值计算 + - * / %

查询员工姓名,工资和年终奖(年终奖=工资5)
select ename,sal,sal5 年终奖 from emp;
查询商品标题，商品单价，库存和总价值（单价库存）
select title,price,num,pricenum 总价值 from t_item;
查询每个员工的姓名和加薪5块钱之后的工资
select ename,sal+5 from emp;

日期相关SQL

1. 获取系统时间 now()

create table t_date(name varchar(10),birthday datetime);
insert into t_date values('刘德华',now());

mysql> select * from t_date;
+-----------+---------------------+
| name      | birthday            |
+-----------+---------------------+
| 刘德华    | 2019-06-25 20:34:57 |
+-----------+---------------------+

2. 获取当前的年月日和当前的时分秒 cur=current 当前

select "hellworld";
select curdate(),curtime();

+-----------+
| hellworld |
+-----------+
| hellworld |
+-----------+
1 row in set (0.00 sec)

+------------+-----------+
| curdate()  | curtime() |
+------------+-----------+
| 2019-06-25 | 20:36:26  |
+------------+-----------+
1 row in set (0.00 sec)

3. 从年月日时分秒中提取年月日和提取时分秒

select date(now());
select time(now());

+-------------+
| date(now()) |
+-------------+
| 2019-06-25  |
+-------------+
1 row in set (0.04 sec)

+-------------+
| time(now()) |
+-------------+
| 20:37:27    |
+-------------+
1 row in set (0.00 sec)

查询商品的上传日期 只查询年月日
select created_time from t_item; //商品上传年月日时分秒
+---------------------+
| created_time        |
+---------------------+
| 2017-10-25 15:08:55 |
| 2017-10-25 15:08:55 |
| 2017-10-25 15:08:55 |
| 2017-10-25 15:08:55 |
+---------------------+

select date(created_time) from t_item;//商品上传年月日
+--------------------+
| date(created_time) |
+--------------------+
| 2017-10-25         |
| 2017-10-25         |
| 2017-10-25         |
| 2017-10-25         |
+---------------------+

4. 从年月日时分秒中提取时间分量

select extract(year from now());
	+--------------------------+
	| extract(year from now()) |
	+--------------------------+
	|                     2019 |
	+--------------------------+
select extract(month from now());
	+---------------------------+
	| extract(month from now()) |
	+---------------------------+
	|                         6 |
	+---------------------------+
select extract(day from now());
	+-------------------------+
	| extract(day from now()) |
	+-------------------------+
	|                      25 |
	+-------------------------+
select extract(hour from now());
	+--------------------------+
	| extract(hour from now()) |
	+--------------------------+
	|                       20 |
	+--------------------------+
select extract(minute from now());
	+----------------------------+
	| extract(minute from now()) |
	+----------------------------+
	|                         43 |
	+----------------------------+
select extract(second from now());
	+----------------------------+
	| extract(second from now()) |
	+----------------------------+
	|                         47 |
	+----------------------------+

查询员工入职的年份

select extract(year from hiredate) from emp;

	+-----------------------------+
	| extract(year from hiredate) |
	+-----------------------------+
	|                        1980 |
	|                        1981 |
	|                        1981 |
	|                        1981 |
	+-----------------------------+

5. 日期格式化 date_format(时间,格式);

格式规则:
%Y 四位年 2019 %y 两位年 19
%m 2位月 06 %c 1位月 6
%d 日
%H 24小时 %h 12小时
%i 分钟
%s 秒

测试:

把now() 2019-06-21 15:29:30 转成 2019年06月21号 15点29分30秒

  	select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
  		+------------------------------------------------------+
  		| date_format(now(),'%Y年%m月%d号 %H点%i分%s秒')       |
  		+------------------------------------------------------+
  		| 2019年06月25号 20点49分12秒                          |
  		+------------------------------------------------------+

6. 反向格式化 str_to_date(时间,格式)

21.06.2019 15点36分20秒转回 2019-06-21 15:36:20

select str_to_date('21.06.2019 15点36分20秒','%d.%m.%Y %H点%i分%s秒');
	+----------------------------------------------------------------------+
	| str_to_date('21.06.2019 15点36分20秒','%d.%m.%Y %H点%i分%s秒')       |
	+----------------------------------------------------------------------+
	| 2019-06-21 15:36:20                                                  |
	+----------------------------------------------------------------------+

ifnull(x,y)

age=ifnull(x,y) 如果x值为null则age=y 否则age=x
练习:修改奖金为null的值为0 不为null则不变
update emp set comm=ifnull(comm,0);

聚合函数

对查询的多条数据进行统计查询: 平均值,最大值,最小值,求和,计数

1. 平均值 avg(字段名)

-查询员工的平均工资
select avg(sal) from emp;

2. 最大值 max(字段名)

-查询20号部门的最高工资	
select max(sal) from emp where deptno=20;

3. 最小值 min(字段名)

-查询名字里面包含a的员工的最低工资
select min(sal) from emp where ename like '%a%';

4. 求和 sum(字段名)

-查询10号部门工资总和
select sum(sal) from emp where deptno=10;

5. 计数 count(字段名)

-查询工资高于2000的员工数量
select count(*) from emp where sal>2000;

***注意：若是字段名下某1个值为null，则该count值就会-1；***

- 查询20号部门的平均工资,最高工资,最低工资,工资总和,员工数量
select avg(sal),max(sal),min(sal),sum(sal),count(*) from emp where deptno=20;

字符串相关

1. 字符串拼接concat(s1,s2); s1s2

select concat('aa','bb');

mysql> select concat('aa','bb');
+-------------------+
| concat('aa','bb') |
+-------------------+
| aabb              |
+-------------------+
- 查询每个员工的姓名和工资 要求工资以元为单位
select ename,concat(sal,'元') from emp;

2. 获取字符串的长度 char_length(str);

select char_length('abc');
- 查询每个员工的姓名和名字长度
select ename,char_length(ename) from emp;

3. 获取字符串出现的位置 instr(str,substr);

select instr('abcdefg','d');

4. 转大写和转小写

select upper('abc'),lower('NBA');

5. 截取字符串

- 左边截取   select left('abcdefg',2);
- 右边截取   select right('abcdefg',2);
- 自由截取	select substring('abcdefg',2,3); 3代表长度

6. 去两端空白 trim()

select trim('   a b   ');

7. 重复repeat()

select repeat('ab',2);

8. 替换replace()

select replace('abcde abc','b','m');

9. 反转reverse()

select reverse('abc');

课后习题

1 案例：查询没有上级领导的员工的编号，姓名，工资

select empno,ename,sal from emp where mgr is null;

2 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金

select ename,job,sal,comm from emp where comm is null;

3 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金

select empno,ename,job,comm from emp where comm>0;

4 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号

select ename,sal,mgr from emp where mgr is not null;

5 案例：查询emp表中名字以‘S’开头的所有员工的姓名

select ename from emp where ename like 's%';

6 案例：查询emp表中名字的最后一个字符是’S’的员工的姓名

select ename from emp where ename like '%s';

7 案例：查询倒数的第2个字符是‘E’的员工的姓名

select ename from emp where ename like '%e_';

8 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名

select ename from emp where ename like '%n__';

9 案例：查询emp表中员工的名字中包含‘A’的员工的姓名

select ename from emp where ename like '%a%';

10 案例：查询emp表中名字不是以’K’开头的员工的所有信息

select *from emp where ename not like 'k%';

11 案例：查询emp表中名字中不包含‘A’的所有员工的信息

select *from emp where ename not like '%a%';

12 案例：做文员的员工人数（job 中含有 CLERK 的）

select count(*) from emp where job='clerk';

13 案例：销售人员 job: SALESMAN 的最高薪水

select max(sal) from emp where job='salesman';

14 案例：最早和最晚入职时间

select min(hiredate),max(hiredate) from emp;

15 案例：查询类别 163的商品总库存量

select sum(num) from t_item where category_id=163;

16 案例：查询类别 163 的商品

select * from t_item where category_id=163;

17 案例：查询商品价格不大于100的商品名称列表

select title from t_item where price<=100;

18 案例：查询品牌是联想,且价格在40000以上的商品名称和价格

selec title,price from t_item where price>40000 and title like '%联想%';

19 案例：查询品牌是三木,或价格在50以下的商品名称和价格

select title,price from t_item where title like '%三木%' or price<50;

20 案例：查询品牌是三木、广博、齐心的商品名称和价格

select title,price from t_item where title like '%三木%' or title like '%广博%' or title like '%齐心%';

21 案例：查询品牌不是联想、戴尔的商品名称和价格

select title,price from t_item where title not like '%联想%'  and title not like '%戴尔%';

22 案例：查找品牌是联想且价格大于10000的名称

select title from t_item where title like '%联想%' and price>10000;

23 案例：查询联想或戴尔的电脑名称列表

select title from t_item where title like '%联想%' or title like '%戴尔%';

24 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。

select empno,ename,job,sal from emp where sal between 1000 and 2000;

25 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号

select ename,job,mgr,deptno from emp where deptno=10;

26 案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。

select empno,ename,job,sal from emp where ename like '%e%' and job !='%manager%';

27 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号

select empno,ename,deptno from emp where deptno=10 or deptno=20;

28 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金

select empno,ename,job,comm from emp where comm=0 or ename not like '%t_';

29 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号

select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;

30 案例：查询不是30号部门的员工的所有信息

select *from emp where deptno !=30;

31 案例：查询奖金不为空的员工的所有信息

select *from emp where comm is not null;

32 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列

select empno,ename,job from emp order by empno desc;

33 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列

select ename,job,sal from emp where deptno=10 or deptno=30 order by sal;

34 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列

select * from emp order by deptno,empno desc;

35 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。

select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;

36 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列

select empno,ename,sal,comm from emp where ename not like '%s%' order by sal,empno desc;

37 案例：统计emp表中员工的总数量

select count(*) from emp;

38 案例：统计emp表中获得奖金的员工的数量

select count(*) from emp where comm>0;

39 案例：求出emp表中所有的工资累加之和

select sum(sal) from emp;

40 案例：求出emp表中所有的奖金累加之和

select sum(comm) from emp;

41 案例：求出emp表中员工的平均工资

select avg(sal) from emp;

42 案例：求出emp表中员工的平均奖金

select avg(comm) from emp;

43 案例：求出emp表中员工的最高工资

select max(sal) from emp;

44 案例：求出emp表中员工编号的最大值

select max(empno) from emp;

45 案例：查询emp表中员工的最低工资。

select min(sal) from emp;

46 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。

select count(*) 人数,sum(sal) 工资的总和,avg(sal) 平均工资,max(comm) 奖金的最大值,min(comm) 奖金的最小值 from emp;