SQL基础教程（二）

SQL基础教程（二）

一、函数

函数的概念：

​ 具有特定功能的代码块，它可以接收参数，并将参数用于运算，最终会给出一个结果

​ 在mysql中，函数必须返回一个结果

​ 是一个具有特定功能，可以反复被调用的代码块**

---

1、数字函数

函数	说明
round(m,n)	将数字m精确到小数点后n位（四舍五入）
truncate(m,n)	将数字m精确到小数点后n位（不会四舍五入，直接截取）
ceil(m,n)	向上取整，返回大于等于m的最小整数
floor(m)	向下取整，返回小于等于m的最大整数
rand()	返回0到1之间的一个随机数
mod(m,n)	返回m除以n的余数

SELECT round(123.456); #如果不指定小数位数,则四舍五入到整数
SELECT round(123.456,2); #四舍五入到2位小数
SELECT round(123.456,-1); #四舍五入到十位

SELECT ename,sal,round(sal,-2) FROM emp; #如果from一个表,每一行都会计算一个结果

mod(m,n)	返回m除以n的余数
SELECT mod(7,3);

SELECT ROUND(RAND()*100);
# 生成一个随机数
# 所谓函数嵌套，就是指一个函数中包含了另外一个函数；会优先执行内层函数(括号里面的)

---

2、字符函数

函数	说明
upper(str)	返回大写的字符串str
lower(str)	返回小写的字符串str
concat(str1,str2,…,strN)	将字符串连接在一起
length(str)	返回字符串的字节长度
left(str,n)	获取字符串str最左边的n个字符
right(str,n)	获取字符串str最右边的n个字符
mid(str,m,n) substring(str,m,n) substr(str,m,n)	获取字符串str第m个字符开始，长度为n的字符 n可以不写，则到字符串末尾
replace(str1,str2,str3)	在str1中找到str2替换为str3
trim(str)	返回删除了左右空格的字符串str
LPAD(str,len,padstr)	在字符串str左边填充padstr，填充到总长度len
RPAD(str,len,padstr)	在字符串str右边填充padstr，填充到总长度len
INSTR(str,substr)	返回str中substr第一次出现的位置编号

# upper、lower函数
upper(str)	将字符串str转为大写
SELECT UPPER('hello');
lower(str)	将字符串str转为小写
SELECT ename, LOWER(ename) FROM emp;

# concat函数
CONCAT(str1,str2,...)	 作用是将各个逗号之间的数据进行连接，形成一个字符串
# 查询emp表，展示如下信息： 姓名 是一个 工作 -> 张三 是一个 经理 
SELECT CONCAT(ename, ' 是一个 ', job) FROM emp;
# 查询emp表，展示信息如下： 张三 的薪水 是 xxx
SELECT CONCAT(ename, ' 的薪水是: ', sal) FROM emp;

# length、left、right、mid函数
length(str)	返回字符串的字节长度
	一个中文占的字节比英文多，具体的字节长度跟编码有关系
SELECT LENGTH('hello world中');

char_length()	会把每一个字当做一个字符处理
SELECT CHAR_LENGTH('hello world你');

left(str,n)	获取字符串str最左边的n个字符
SELECT LEFT('hello world!', 6);

right(str,n)	获取字符串str最右边的n个字符
SELECT RIGHT('nice to meet you!', 10);

# substr、substring函数
substr(str,m,n)
substring(str,m,n)	获取字符串str第m个字符开始，长度为n的字符, n可以不写, 则到字符串末尾

SELECT SUBSTR('横看成岭侧成峰', 1, 3);
SELECT SUBSTR('横看成岭侧成峰', 1);
SELECT SUBSTR('横看成岭侧成峰', 7, -2);	# 最后的n不能写成负数

---

3、时间和日期函数

MySQL中日期格式 YYYY-MM-DD 时间格式HH:MM:SS

函数	说明
curdate()	获取当前日期
curtime()	获取当前时间
now() sysdate()	获取当前的日期和时间 now()语句执行一开始就获取了 sysdate()在执行的过程中才获取，比较慢
year(date) month(date) day(date)	获取日期date的年、月、日
date_add(date,interval n type)	给日期date加上n个type的时间单位，可以是负数 type可以使year month day
datediff(date1,date2)	计算两个日期的间隔天数
last_day(date)	获取与日期date相同月份的最后一天的日期
date_format(date,`格式)	以不同的格式显示日期/时间数据 %Y 年 %m 月 %d 日 %H 时 %i 分 %s 秒

-- day(date)	获取日期date的年、月、日
SELECT year('2020-09-01');
SELECT month('2020-09-01');
SELECT day('2020-09-01');
SELECT day(now());
SELECT ename,month(hiredate) FROM emp;

-- date_add(date,interval n type)	给日期date加上n个type的时间单位,n可以是负数,type可以是year  month  day
SELECT date_add('1999-12-31',INTERVAL 1 day); #后一天的日期

SELECT date_add('1999-12-31', INTERVAL 2 MONTH);	# 给日期+1个月

SELECT date_add('2000-01-01',INTERVAL -1 day); #前一天的日期

SELECT date_add(now(),INTERVAL 3 day);

-- datediff(date1,date2)	计算 date1 - date2 的间隔天数
SELECT DATEDIFF(now(), '1920-03-05');

---

4、条件判断函数

1）if(条件,为真的返回值，为假的返回值)

# 如果工资大于2000,显示高富帅,否则显示矮矬穷
SELECT ename, sal, if(sal>2000, "高富帅", "矮矬穷") 代号 FROM emp;

2）ifnull(为假的返回值，为真的返回值)

# 显示员工的名字,工资,和奖金。如果奖金为空,则显示0
SELECT ename, sal, IFNULL(comm,0) FROM emp;

# 计算员工的年薪,工资+奖金。不能出现空的结果
SELECT ename, sal, comm, (sal+IFNULL(comm,0))*12 年薪 FROM emp;

-- 根据员工不同的职位，显示不同的中文职位名称
-- clerk显示为 '办事员'，salesman显示为 '销售员'
-- manager显示为 '经理'，其他人显示为 '其他岗'
SELECT ename, job, 
  CASE job
	WHEN 'CLERK' THEN '办事员'
	WHEN 'SALESMAN' THEN '销售员'
	WHEN 'MANAGER' THEN '经理'
	ELSE '其他岗'
	END '岗位'
FROM emp;

3）条件判断函数case2

​ 结构：

case 
	when 判断1 then result1

	when 判断2 then result2

	……

	when 判断N then resultN

	else result N+1

	end

---

5、其它系统函数

函数	说明
database()	显示数据库名字
version()	显示数据库版本信息
user()	显示当前连接用户

SELECT DATABASE(), VERSION(), USER();

---

二、从多个表查询数据

1、笛卡尔结果

• 设A,B为集合，集合A中所有元素和集合B中的所有元素组成的有序对，所有这样的有序对组成的集合 叫做A与B的笛卡尔积，记作A×B
• 为了避免笛卡尔结果我们需要在where语句中使用有效连接条件，查询条件>=（表的数量-1）
• 当连接n张表时，需要写n-1个连接条件

– 笛卡尔积是多表查询时，没有写连接条件，导致多表进行全排列组合，把不正确的数据也展示出来的情况
– 笛卡尔积出现，主要的原因为没有写连接条件。

---

2、连接类型

1）给表起别名

**注：**表的别名不能用双引号或单引号。如果要用只能用反引号

SELECT e.*, d.*
FROM emp e, dept d;

SELECT * FROM dept;

2）等值连接

​ 使用等号做连接，通过主键和外键进行连接查询

-- 显示king的员工编号,姓名,部门编号和部门地址。可以在where条件后继续加and条件进一步筛选
SELECT e.empno, e.ename, e.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND ename = 'KING';

3）不等值连接

-- 显示员工的姓名,工资和工资等级
SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;	# 薪水 介于该级别的最低和最高的工资之间

-- 显示员工的姓名,工资,部门编号,工资等级和部门地址
SELECT e.ename, e.sal, e.deptno, s.grade, d.loc
FROM emp e, salgrade s, dept d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

4）内连接

只显示符合连接条件的数据。不符合的全部不显示。
– 第二种写法
/*
SELECT 表1.列1，表2.列2
FROM 表1 INNER JOIN 表2
ON 连接条件(表1.列 = 表2.列)
注意：
1.系统默认是进行内连接，因此inner 可以不写
*/

SELECT e.empno, e.ename, e.mgr, m.empno, m.ename
FROM emp e INNER JOIN emp m	-- 默认为内连接，所以INNER 可以不写
ON e.mgr = m.empno;

insert into emp values(9527, '华安', '书童', 7839, now(), 200, NULL, NULL);

5）外连接

​ 一般分左外连接，右外连接，全外连接。mysql不支持全外连接。

​ 左外连接 left join 。 如果join左边的表有数据不符合连接条件，也要显示出来

​ 外连接 right join 。 如果join右边的表有数据不符合连接条件，也要显示出来

-- 显示部门的编号,名称及部门所有成员。如果部门没有人员也要把这个部门显示出来
SELECT e.*, d.dname, d.loc
FROM emp e RIGHT OUTER JOIN dept d	-- OUTER也可以省略
ON e.deptno = d.deptno;

-- 显示员工信息和部门信息，没有部门的员工也要显示出来
SELECT e.*, d.dname, d.loc
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

4）自连接

-- 显示员工姓名以及他经理的名字
-- 同一个表起两个别名，自己连接自己
SELECT e.empno, e.ename, e.mgr, m.empno, m.ename
FROM emp e, emp m 	-- e为员工表，m为经理表
WHERE e.mgr = m.empno;	-- 员工的经理编号 == 经理的员工编号

---

三、聚合函数

1、常见聚合函数：

– 普通的函数,每一行都会计算一个结果。
– 聚合函数，针对多行数据，返回一个结果
/*

​ AVG() 平均数
​ SUM()求和
​ COUNT()计数(统计行数)
​ MAX() 最大值
​ MIN() 最小值

SELECT 列1，列2…, 聚合函数
FROM 表名
WHERE 限定条件
GROUP BY 列1，列2，…列n
HAVING 聚合函数作为限定条件
ORDER BY 列名
limit n

注意：
AVG()和SUM()只能针对数字类型的数据; count(), max()和min()可以针对任何数据类型
*/

-- 显示每个人名字的前三个字母
SELECT ename, LEFT(ename, 3) FROM emp;

-- 显示公司所有员工一起算一个平均工资
SELECT AVG(sal) FROM emp;

-- 显示所有人的工资总和
SELECT SUM(sal) FROM emp; # 29525

– count通常需要去重，如果不需要去重，可以写*代表所有行，建议写主键

– 普通列不能直接跟聚合函数放在select后面进行展示。

– 注意： 普通列想要跟聚合函数一起展示出来，则必须将普通列放在group by后面

– 注意：聚合函数天然会忽略空值

– max和min可以处理任意类型。avg、sum只能对数字进行处理。

-- 显示公司有多少人。就是计算记录的行数
SELECT COUNT(*) FROM emp;	# 15

SELECT 29525/15;	# 1968.3333

-- 显示comm不为空的行数
-- 注意：聚合函数天然会忽略空值
SELECT COUNT(comm) FROM emp;

-- 显示入职最早的日期。
SELECT min(hiredate) FROM emp;

-- 显示工资最高的值。max和min可以处理任意类型。avg、sum只能对数字进行处理。
SELECT MAX(sal) FROM emp;

-- 统计平均奖金。不会统计空值的
SELECT AVG(comm) FROM emp;

- 统计30部门的平均工资	
-- 分组函数是可以跟where子句一起使用的
SELECT avg(sal) FROM emp
WHERE deptno = 30;

-- 同时显示10部门,20部门,30部门的平均工资
SELECT deptno, avg(sal)
FROM emp
WHERE deptno in(10, 20, 30)
GROUP BY deptno

-- 普通列不能直接跟聚合函数放在select后面进行展示。
-- 注意： 普通列想要跟聚合函数一起展示出来，则必须将普通列放在group by后面
SELECT deptno, avg(sal)
FROM emp
group by deptno;	# 建议，group by后面的列也展示出来，方便查看

-- 显示每个岗位的人数
SELECT job, COUNT(*)
FROM emp
GROUP BY job;

-- 显示每个部门工资大于1500,小于5000的人数
SELECT deptno, COUNT(*) FROM emp
WHERE sal > 1500 
AND sal < 5000
GROUP BY deptno;

---

2、分组函数（GROUP BY子句、HAVING子句）

– 分组函数只有在有大量重复数据的地方，才有意义
– 多列分组，会将多列的值组合当成一个值，来进行分组(保证组合值不重复)

SELECT deptno, job, COUNT(*) FROM emp
GROUP BY deptno, job;

-- 错误写法 SELECT deptno,ename,count(*) FROM emp GROUP BY deptno;
-- 错误写法 SELECT deptno,count(*) FROM emp;

**注：**where后面不能跟聚合函数进行限定
– 如果想要用聚合函数进行结果限定 则必须使用having子句
注：**有group by子句，不一定有having。但是有having子句，就一定有group by
– 如果想用 max(), min(), avg(), count(), sum()进行条件限定，则必须用group by + having

-- 查询平均工资大于2000的部门
SELECT deptno, avg(sal) FROM emp
GROUP BY deptno
HAVING avg(sal) > 2900;

-- 显示20部门的各个岗位的名称及平均工资,只显示平均工资大于2000的,
-- 并且最后按平均工资升序排列
SELECT job, avg(sal) FROM emp
WHERE deptno = 20
GROUP BY job 
HAVING AVG(sal) > 2000	# 聚合函数作为限定条件，则必须用having，有having必须有group by
ORDER BY avg(sal);

---

四、子查询

​ 子查询在主查询前执行一次

​ 主查询使用子查询的结果

– 注意：建议把子查询语句单独写一行

**注：**主查询的接受列和子查询的返回列，列必须一一对应(列的个数，数据类型)

**注：**主查询中可以是用聚合函数作为限定条件

使用子查询的规则：

• 子查询要用括号括起来
• 习惯上将子查询放在比较运算符的右边
• 子查询作为条件时不要加ORDER BY子句
• 对单行子查询结果判断使用单行运算符
• 对多行子查询结果判断使用多行运算符

---

1、单行子查询

​ 返回一行记录

​ 使用单行记录比较运算符：=, >, >=, <, <=, !=(<>)

-- 查询工资高于jones的员工
-- 注意：建议把子查询语句单独写一行
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename = 'JONES');	-- 子查询会在主查询前执行

-- 找出部门和allen相同的员工
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN');

-- 找部门及岗位都和allen相同的员工
SELECT * FROM emp
WHERE deptno = 
(SELECT deptno FROM emp WHERE ename = 'allen')
AND job = 
(SELECT job FROM emp WHERE ename = 'allen');

-- 多列子查询
-- 主查询的接受列和子查询的返回列，列必须一一对应(列的个数，数据类型)
SELECT * FROM emp
WHERE (deptno, job)=
(SELECT deptno, job FROM emp WHERE ename = 'ALLEN');

-- 查询最低工资的员工姓名,岗位,及部门编号。用子查询做
SELECT * FROM emp
WHERE sal =
(SELECT min(sal) FROM emp);

-- 查询最低工资比20部门的最低工资高的部门
-- 主查询中可以是用聚合函数作为限定条件
SELECT deptno, min(sal) FROM emp
GROUP BY deptno
HAVING min(sal) >
(SELECT min(sal) FROM emp WHERE deptno = 20)

---

2、多行子查询

– 对于多行数据,不能用=号 SELECT * FROM emp WHERE sal = (SELECT min(sal) FROM emp GROUP BY deptno);
– 可以使用IN 列表匹配， ANY 任何的， ALL 所有的
– **注：**当不确定子查询会返回几行数据时，统一用in会更加稳妥。

– 关于ANY和ALL 建议记单词本身的含义，想不清楚的时候，推导一下

– 把子查询结果当表使用,必须起个别名。子查询中列的别名在外面作为列名使用

-- 查询和smith及miller相同部门的员工
SELECT * FROM emp
WHERE deptno in 
(SELECT deptno FROM emp WHERE ename in ('SMITH', 'MILLER'))

-- 查询工资高于任意一个部门的平均工资的员工.
-- 关于ANY和ALL 建议记单词本身的含义，想不清楚的时候，推导一下
SELECT * FROM emp
WHERE sal > ANY
(SELECT avg(sal) FROM emp GROUP BY deptno);

-- 查询工资高于所有部门的平均工资的员工
SELECT * FROM emp 
WHERE sal > ALL
(SELECT avg(sal) FROM emp GROUP BY deptno)

-- 把子查询结果当表使用,必须起个别名。子查询中列的别名在外面作为列名使用
-- 查询30部门的员工姓名,部门编号和年薪,要求显示年薪>30000的。
SELECT * FROM 
(SELECT ename, deptno, sal, sal*12 年薪 FROM emp) s
WHERE s.年薪 > 30000;

-- 显示员工的姓名,工资,部门编号,及所在部门的平均工资
-- 思路： 将部门编号和平均工资查询出来，当做一个新表
-- 将emp表跟新表进行关联(deptno相同)， 然后直接查询
SELECT e.ename, e.sal, e.deptno, s.avg_sal
FROM emp e,
(SELECT deptno, avg(sal) avg_sal FROM emp GROUP BY deptno) s	# s是新表的别名 ()内部子查询会生成一张新表
WHERE e.deptno = s.deptno;

/*
什么时候用子查询，什么时候用多表查询？

1. 如果查询结果来自于多张表，则必须使用多表查询
2. 如果查询结果(展示列)来自于同一张表，则可以使用子查询和多表查询，但是推荐使用子查询
3. 子查询的执行效率比多表查询高，因为子查询是+的关系，而多表是乘的关系

*/

/*
1.什么时候用多表查询，什么时候用子查询？
关于性能：
多表查询如果不写连接条件，会生成笛卡尔积，就算写了连接条件， 其实表与表之间还是乘的关系
假定A表中10条数据，B表中5条数据，进行夺标查询时，最坏会生成10*5=50条数据
子查询，表与表之间是加的关系，随着AB两表的数据量增大，则子查询的优势越明显
SELECT dname FROM dept – 4次
WHERE deptno = (
SELECT deptno FROM emp WHERE ename = ‘allen’) – 14次 最多4+14=18次，是加的关系
建议：
日常查询中使用子查询，而不建议用多表查询
答案：
如果查询的结果展示内容来自于不同的多个表，则建议使用多表查询
如果查询展示的结果来自于一个表，建议用子查询
*/

---

3、多列子查询

select empno, ename from emp
where (deptno, job) IN 
(select deptno, job from emp where ename = 'SMITH')

---

五、表的集合操作

UNION和UNION ALL

• union求合集时，会去掉重复的数据
• 如果要保留重复数据，使用union all

-- 查询工资大于2000的员工
SELECT * FROM emp WHERE sal > 2000;
-- 查询30部门的员工
SELECT * FROM emp WHERE deptno = 30;
-- union把多个查询语句的结果放到一起。会去重。
SELECT * FROM emp WHERE sal > 2000
UNION
SELECT * FROM emp WHERE deptno = 30;

-- union把多个查询语句的结果放到一起。不会去重。
SELECT * FROM emp WHERE sal > 2000
UNION ALL
SELECT * FROM emp WHERE deptno = 30;

1）UNION

– union把多个查询语句的结果放到一起。会去重。
– union 操作格式：查询语句1 union 查询语句2
– 集合操作，每个查询语句的展示列的数量，类型和顺序必须要一致

-- 查询工资大于2000的员工
SELECT * FROM emp WHERE sal > 2000

-- 查询30部门的员工
SELECT * FROM emp WHERE deptno = 30

-- union把多个查询语句的结果放到一起。会去重。
-- union 操作格式：查询语句1 union 查询语句2
-- 集合操作，每个查询语句的展示列的数量，类型和顺序必须要一致
SELECT * FROM emp WHERE sal > 2000
UNION
SELECT * FROM emp WHERE deptno = 30
ORDER BY deptno;		-- ORDER BY 只能写在最后

2)UNION ALL

– union all把多个查询语句的结果放到一起。不会去重。
– 即是同时满足两个查询语句的数据会被展示2次

-- union all把多个查询语句的结果放到一起。不会去重。
-- 即是同时满足两个查询语句的数据会被展示2次
SELECT * FROM emp WHERE sal > 2000
UNION ALL
SELECT * FROM emp WHERE deptno = 30	
ORDER BY deptno;	

0

– union把多个查询语句的结果放到一起。会去重。
– union 操作格式：查询语句1 union 查询语句2
– 集合操作，每个查询语句的展示列的数量，类型和顺序必须要一致
SELECT * FROM emp WHERE sal > 2000
UNION
SELECT * FROM emp WHERE deptno = 30
ORDER BY deptno; – ORDER BY 只能写在最后

> 2)UNION ALL
>
> -- union all把多个查询语句的结果放到一起。不会去重。
> -- 即是同时满足两个查询语句的数据会被展示2次

```mysql
-- union all把多个查询语句的结果放到一起。不会去重。
-- 即是同时满足两个查询语句的数据会被展示2次
SELECT * FROM emp WHERE sal > 2000
UNION ALL
SELECT * FROM emp WHERE deptno = 30	
ORDER BY deptno;