MySQL基础(3)----其他函数约束多表查询子查询组合查询

码农终将翻身

已于 2022-05-01 19:42:52 修改

阅读量879

点赞数 1

分类专栏： MySQL 文章标签： mysql

于 2022-04-26 23:34:49 首次发布

本文链接：https://blog.csdn.net/weixin_68509156/article/details/124439790

版权

MySQL 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

1.其他函数

1.1.字符串函数 --- 操作字符串

常用函数：

函数	功能
CONCAT(s1, s2, ..., sn)	字符串拼接，将s1, s2, ..., sn拼接成一个字符串
LOWER(str)	将字符串全部转为小写
UPPER(str)	将字符串全部转为大写
LPAD(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回从字符串str从start位置起的len个长度的字符串

-- 字符串拼接函数
select name from t_student;

select concat('我的名字叫:',name,",年龄:",age) as n from t_student;

-- 字符串转换为大写和小写
select name,upper(name),LOWER(name) from t_student;

-- 去除前后空格
select address,trim(address) from t_student;

-- 找到姓李得学生字符串得截取
select * from t_student where name like '李%';

select * from t_student where substr(name,1,1)='李'

1.2.数字函数

常见函数：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	返回x/y的模求余
abs(x)	求某个数得绝对值

-- 向上取整 5.1你给定的值
select ceil(5.1),floor(5.9),mod(10,3),abs(-5.9) from t_student;

-- 要求如果status(列名)为0变为1 为1变为0 使用update修改记录
update aaa set status=abs(status-1);

1.3 时间函数

常用函数：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

-- CURDATE()返回当前日期后面可以不用跟表名
select curdate();
-- 可以用在where
select * from tb_emp where curdate()-entrydate=1

-- CURTIME()返回当前时间
select curtime() ;

-- NOW()返回当前日期和时间
select now();

-- YEAR(date)获取指定date的年份
select year('2022-04-25');

select year(entrydate),count(*) from tb_emp group by year(entrydate);

select * from tb_emp where year(entrydate)=2022

-- DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
SELECT DATE_ADD('2020-04-25', INTERVAL 3 YEAR);

-- DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
select datediff('2020-05-25','2020-04-25');
-- 入职30天得员工.

1.4 流程函数

常用函数：

函数	功能
IF(value, t, f)	如果value为true，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END	如果val1为true，返回res1，... 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END	如果expr的值等于val1，返回res1，... 否则返回default默认值

-- 如果年龄>30妇女否则少女
select age,if(age>30,'妇女','少女') from tb_emp;

-- ifnull 如果为null，则
select job,ifnull(job,'工作待定') from tb_emp;

select name,(case when age>30 then '中年' else '青年' end) from emplyee;

select name,(case address when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址' from emplyee;

2.约束

什么是约束? 就是在表中为某些列添加约束，使该列得值必须符合这个约束。

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.16版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

drop table if exists t_teacher; -- 检测是否存在表`t_teacher`，存在删掉，不存在不执行

create table t_teacher(
   id int primary key auto_increment, -- 要求该列的值必须唯一且不能为null
   tname varchar(20) unique, -- 唯一约束表示该列的值必须唯一,但是可以拥有多个null
   age int not null, -- 非空约束表示该列的值不能为null
   sex char(2) default '男' -- default 默认约束表述如果该列没有给定值则默认为男
);

select * from t_teacher;
insert into t_teacher(id,tname,age,sex) values(null,'刘德华',15,'女');
-- Duplicate entry '刘德华' for key 'tname' 由于tname具有唯一约束表中有名字为刘德华所以不能在添加刘德华名称
insert into t_teacher(id,tname,age,sex) values(null,'刘德华',18,'男');
-- Column 'age' cannot be null 因为age设置了非空约束所以添加数据时年龄必须有值。
insert into t_teacher(id,tname,age,sex) values(null,'张学友',null,'男');

-- 因为sex有默认约束所以在添加数据时可以不为sex设置值，则采用默认值。
insert into t_teacher(id,tname,age) values(null,'张学友',22);

2.1 外键约束

-- 添加外键的两种方式: 外键列的数据类型必须和主表中主键的数据类型一致
-- 1: 创建表时添加外键
constraint 约束名 foreign key(外键的列名) references 主表(主键);
-- 2: 修改表时添加外键约束
alter table 从表名 add constraint 约束名 foreign key(外键列名) references 主表名(主键);

外键约束用来让两张图的数据之间建立连接，保证数据的一致性和完整性。

-- 外键
drop table if exists t_class;
create table t_class(
cid int primary key auto_increment comment '班级编号',
cname varchar(20) unique comment '班级名'
);
drop table if exists t_stu;
create table t_stu(
   sid int primary key auto_increment comment '学号',
   sname varchar(20) unique comment '学生姓名',
   classid int comment '班级编号' -- 表示外键列
);

-- add constraint 约束的名称 foregin key 外键的列名 REFERENCES 主表名(主键)

-- alter table 从表名 add constraint 约束名随便起 foreign key 外键列名 references 主表(主键)
alter table t_stu add CONSTRAINT fk_stu_class FOREIGN key (classid) REFERENCES t_class(cid);

insert into t_class values(null,'QY145'),(null,'QY151');

insert into t_stu values(null,'张三',2);
-- 因为classid=3 这个值在我们得班级表中不存在。所以不能添加
insert into t_stu values(null,'张青',1);

注意: 外键列的数据类型必须和主表中主键的数据类型一致。

3.多表查询

概述

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在各种联系,基本费为三种:

一对多(多对一)

多对多

为什么需要连表查询?

1.如果查询得结果在一张表中无法获取，需要关联多张表，那么这时就需要连表查询。

2.连表查询大多数都作用在外键得基础上。--- 表与表之间有关联。

查询所有学生信息以及学生所在得班级信息。

3.1 表与表之间存在得关系

一对多: 在多得一方添加外键列
多对多: 需要在创建一个中间表，该表中至少有两个外键列

3.2 连表查询

3.3.内连接查询

内连接演示---结果都是一样，只是语法不同。看个人习惯用哪个?
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)

2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
-- 连表查询时，如果不使用连表条件则出现笛卡尔集。
-- 所谓笛卡尔集 就是A表中每一条记录关联B中中得每条记录
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
 -- 如果表的名字很长 可以为表起别名
select * from tb_emp e, tb_dept d where e.dept_id=d.id;
 

-- 显示连接: A表 inner join B表 on 连表条件。 
2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;

-- 上面的 inner可以省略。
select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

3.4.外连接查询

外连接演示
--1.查询emp表的所有数据, 和对应的部门信息(左外连接)

--2．查询dept表的所有数据，和对应的员工信息(右外连接)

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

内连接和外连接的使用

3.5.自连接查询

自己和自己相连接查询。
select * from A表 join A表 on 连表条件。

-- 1．查询员工及其所属领导的名字。你要查询的结果再一张表中，但是还不能使用单表查询得到结果。
select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;
 
-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

4. 子查询---嵌套查询

一个查询的结果作为另一个查询的条件或者临时表。

子查询能解决的都可以用连表查询但是连表查询能解决的子查询不一定能解决

-- 查询市场部的员工信息-----
-- 子查询返回的结果一列一条记录。 这个时候可以用=
select * from tb_emp where dept_id=(select id from tb_dept where name='市场部')

-- 查询市场部和研发部员工的信息。in 
 -- -- 查询市场部和研发部员工的信息。
   -- a) 查询市场部和研发部的编号
	 select id from tb_dept where name in('市场部','研发部')

   -- b) 再员工表中根据部门编号查询员工信息
	 select * from tb_emp where dept_id in (select id from tb_dept where name in('市场部','研发部'))


-- 查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白')

-- -- 查询比财务部所有人工资都高的员工信息。
  a)求出财务部中最高的工资。
	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部'
	
	b) 根据财务部最高工资查询其他员工信息
	select * from tb_emp where salary>(	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')

5. 组合查询

多个查询的结果组合到一起。

(select * from t_student s1)
UNION all
(select * from t_student s2)
--------
(select * from t_student s1)
UNION
(select * from t_student s2)

sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。

注意: 这两条sql返回的字段必须一样。



UNION all 和 UNION 区别是：
1、显示结果不同

union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来。

2、对重复结果的处理不同

union all是直接连接，取到得是所有值，记录可能有重复；union 是取唯一值，记录没有重复。所以union在进行表链接后会筛选掉重复的记录，union all不会去除重复记录。

3、对排序的处理不同

union将会按照字段的顺序进行排序；union all只是简单的将两个结果合并后就返回。从效率上说，union all 要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用union all。

   select name from tb_emp where salary>8000
   UNION
   select * from tb_emp where age>40;

总结:

1.内连接---inner join on
2.外连接---left join on right join on
3.自连接---
4.子查询
5.组合查询---sql union sql union all

例:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122
-- 1：查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

-- (1)思考: 从哪些表获取数据。 tb_emp;

-- a) 查询员工表中最高的薪水。

select max(salary) from tb_emp;

-- b) 根据最高的薪水查询员工信息

select * from tb_emp where salary=(select max(salary) from tb_emp)

-- 2：查询每个雇员和其所在的部门名

-- 需要查询哪些表? tb_emp tb_dept.

select e.*,d.name from tb_emp e,tb_dept d where e.dept_id=d.id

-- 3：查询每个雇员姓名及其工资所在的等级

-- 需要查询的表? tb_emp salgrade. 这两张表没有外键关联连表时他们的连表条件？

select e.name,s.grade from tb_emp e,salgrade s where e.salary BETWEEN s.losal and s.hisal;

-- 4：查询雇员名第2个字母不是敏的雇员的姓名、所在的部门名、工资所在的等级。

-- 需要查询的表: tb_emp,tb_dept ,salgrade.

select e.name, d.name , s.grade

from tb_emp e, salgrade s, tb_dept d

where e.salary BETWEEN s.losal and s.hisal and e.dept_id=d.id

and e.name not like '_敏%'

-- and substr(e.name,2,1)!='敏'

;

select tb_emp.name, tb_dept.name, salgrade.grade from tb_emp

left join tb_dept on tb_emp.dept_id=tb_dept.id

left join salgrade on tb_emp.salary between salgrade.losal and salgrade.hisal

where tb_emp.name not like '_敏%';

-- 5：查询每个雇员和其经理的姓名

-- 6：查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））

-- 7：查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）

select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id=d.id;

-- 8：查询每个部门中工资最高的人的姓名、薪水和部门编号

-- 查询的表: tb_emp;

-- a) 查询每个部门的最高工资

select dept_id,max(salary) maxsalary from tb_emp group by dept_id;

-- b) 根据上面查询的结果。员工的信息。注意: 之前嵌套查询: where 一个列 in(一个列的值)

select name,salary,dept_id from tb_emp where (dept_id,salary) in

(select dept_id,max(salary) maxsalary from tb_emp group by dept_id)

select distinct e.name,a.sal,dept_id from (select max(salary) sal from tb_emp group by dept_id) a join tb_emp e on e.salary=a.sal;

-- 9：查询每个部门平均工资所在的等级

-- a) 查询每个部门的平均工资.

select dept_id,avg(salary) av from tb_emp group by dept_id;

-- b) 在等级表中查询。我可以把上面的查询当作一个新的表

select grade from salgrade s join (select dept_id,avg(salary) av from tb_emp group by dept_id) a on a.av BETWEEN s.losal and s.hisal

-- 10．查询员工的姓名、年龄、职位、部门信息―（隐式内连接)

-- 连表查询 tb_emp tb_dept

select * from tb_emp e,tb_dept d where e.dept_id=d.id;

-- 11．查询年龄小于30岁的员工的姓名、年龄、职位、部门信息〈显式内连接）

select * from tb_emp e join tb_dept d on e.dept_id=d.id where age<30;

-- 12．查询拥有员工的部门ID、部门名称

-- a) 从员工表中查询部门的编号。

select dept_id from tb_emp where dept_id is not null;

-- b) 查询部门信息

select * from tb_dept where id in(select dept_id from tb_emp where dept_id is not null)

-- 13．查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来

select e.*,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id where age>40

-- 14.查询所有员工的工资等级

select e.*,s.grade from tb_emp e left join salgrade s on e.salary BETWEEN s.losal and s.hisal;

-- 15．查询“研发部”所有员工的信息及工资等级

select e.*,s.grade from

tb_emp e left join salgrade s

on e.salary BETWEEN s.losal and s.hisal

left join tb_dept d on e.dept_id = d.id

where d.name='研发部'

;

-- 16．查询“研发部”员工的平均工资

select avg(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='研发部';

-- 17．查询工资比“灭绝”高的员工信息。

-- a) 查询灭绝的薪水

select salary from tb_emp where name='灭绝';

-- b) 根据上面的结果查询员工信息

select * from tb_emp where salary >(select salary from tb_emp where name='灭绝')

-- 18．查询比平均薪资高的员工信息

-- a) 查询平均薪水

select avg(salary) from tb_emp

select * from tb_emp where salary >(select avg(salary) from tb_emp)

-- 19．查询所有的部门信息，并统计部门的员工人数.

-- a) 统计每个部门的人数.

select dept_id,count(id) rs from tb_emp where dept_id is not null group by dept_id;

-- b) 部门表和上面的临时表连表查询

select d.*, ifnull(c.rs,0) from tb_dept d

left join

(select dept_id,count(id) rs from tb_emp where dept_id is not null group by dept_id) c

on d.id=c.dept_id order by d.id

select d.*,count(e.id) from tb_emp e right join tb_dept d on e.dept_id=d.id group by d.id

order by d.id

;

-- select 后可以跟查询列也可以在跟一个子查询

select d.id,d.name,ifnull((select count(*) from tb_emp e where e.dept_id = d.id),0) '部门人数' from tb_dept d;

use mydb03;
desc borrow_info;
-- 8、检索同时借了总编号为209116和209124两本图书的借书证号
select * from borrow_info where book_id ='209116' or book_id='209124'

-- 上面的写法是错误。
-- a) 查询借阅了209116的读者id.
   select reader_id from borrow_info where book_id='209116';
   -- b) 查询借阅了209124 且读者的编号为上面查询的结果。
   select * from borrow_info where book_id='209124'
   and reader_id in( select reader_id from borrow_info where book_id='209116')

desc readers;
-- 10、检索“扬凡”所借的所有图书的书名和借阅日期
-- 查询的表: borrow_info books readers 这个答案不固定可以使用连表也可以使用子查询
   select b.book_name,bi.borrow_time
   from borrow_info bi
   join books b on bi.book_id=b.book_id
   join readers r on r.reader_id=bi.reader_id
   where r.name='杨凡'

-- 11、检索价格在20元以上且已经借出的图书，结果按单价降序排列
-- 查询哪些表: books borrow_info
   select distinct b.* from books b join borrow_info bi on b.book_id=bi.book_id
   where b.price>20 order by price desc;

-- 13、检索与“杨凡”在同一天借阅了图书的读者的姓名和所在单位
-- 连表也可以子查询也可以
       -- a) 查询杨凡的reader_id
       select reader_id from readers where name='杨凡';
       -- b) 根据杨凡的reader_id 查询它的借书时间
       select borrow_time from borrow_info
       where reader_id=(select reader_id from readers where name='杨凡')

       -- c) 根据上面的借书时间查询其他读者相同时间的reader_id
       select distinct reader_id from borrow_info
       where borrow_time in (
           select borrow_time from borrow_info
               where reader_id=(select reader_id from readers where name='杨凡')
       )

       -- d) 姓名和单位
       select name,dept from readers where reader_id in(
       select distinct reader_id from borrow_info
               where borrow_time in (
                   select borrow_time from borrow_info
                       where reader_id=(select reader_id from readers where name='杨凡')
                   )
       ) and name!='杨凡'

-- 22、检索当前至少借阅了5本图书的读者姓名和所在单位
-- a) 查询每个读者借阅图书的个数
select reader_id
   from borrow_info
   group by reader_id
   having count(distinct book_id)>=5

   -- b) 根据上面的读者编号查询信息
   select name,dept from readers where reader_id in(
           select reader_id
           from borrow_info
           group by reader_id
           having count(distinct book_id)>=5
       )

-- 23、分别找出借书人数超过10个人的单位和人数
select r.dept,count(distinct r.reader_id)
from readers r join borrow_info bi on r.reader_id=bi.reader_id
group by dept
having count(distinct r.reader_id) >10

-- 24、检索没有借阅任何图书的读者姓名和所在单位
-- a) 查询借阅图书的读者编号
select reader_id from borrow_info
-- b) 根据上面信息查询没有借阅图书的读者信息
select distinct name,dept from readers where reader_id not in( select reader_id from borrow_info)

完结！

持续更新！！！

感谢观看！！！！

码农终将翻身

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础(3)----其他函数约束多表查询子查询组合查询

MySQL基础----其他函数(字符串函数,数字函数,时间函数,流程函数), 约束(非空约束,唯一约束,主键约束,默认约束,检测约束,外键约束) 多表查询--->内连接查询,外连接查询,自连接查询子查询(嵌套查询) 组合查询
复制链接

扫一扫