SQL分类
DDL(数据定义语言):create、alter、drop、rename、truncate
DML(数据操作语言):insert、delete、update、select
DCL(数据控制语言):commit、rollback、savepoint、grant、revoke
基本语句
select语句
查询
全查询
SELECT * FROM employees;
子列查询
SELECT employee_id,last_name,department_id
FROM employees;
取别名
as关键字
SELECT employee_id AS eid
FROM employees;
双引号取别名
SELECT department_id "did"
FROM employees;
SELECT salary*12 "annual sal"
FROM employees;
去除重复行
distinct
SELECT DISTINCT department_id
FROM employees;
注:distinct作用于其后的所有列
空值NULL参与运算
SELECT employee_id,salary "月工资",salary*(1+IFNULL(commission_pct,0))*12 "年工资"
FROM employees;
注:空值不等于0,对其参与运算时需要用函数将其转换为0
运算符
算术运算符
加(+)、减(-)、乘(*)、除(/)和取模(%)
练习:
SELECT 12 MOD 3,12 MOD 5,-12 MOD 5,-12 MOD-5,-12 % -3
FROM DUAL;
结果:0 2 -2 -2 0
注:mod结果正负号取决于mod前数字正负号
比较运算符
等号运算符
SELECT 1=1,1=2,1='a',0='a','a'='a','a'='b'
FROM DUAL;
结果:1 0 0 1 1 0
注:数字与字符进行比较时,字符转换为数字0,字符与字符进行比较时,字符转换为ASCII值
安全等于运算符<=>
专为null的比较而生
SELECT 1=NULL,NULL=NULL
FROM DUAL;
结果:null null
注:当null参与运算时,其结果永远为null
SELECT 1<=>NULL,NULL<=>NULL
FROM DUAL;
结果:0 1
注:使用<=>可使得null运算符进行比较并返回正确结果
练习:查询绩效为null的员工信息
解1:
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE NOT commission_pct <=>NULL;
解2:
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
解3:
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
练习 :查询绩效不为null的员工信息
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
最小运算符LEAST与最大运算符GREATEST
SELECT LEAST('a','b','d'),GREATEST(1,23,100)
FROM DUAL;
SELECT LEAST(first_name,last_name),GREATEST(LENGTH(first_name),LENGTH(last_name))
FROM employees;
逻辑运算符
between and
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >=6000 && salary<= 8000;
注:between and运算符范围边界为双闭区间,边界值都可以取到
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
IN
SELECT employee_id,last_name,salary
FROM employees
WHERE salary IN(6000,7000,8000);
SELECT employee_id,last_name,salary
FROM employees
WHERE salary=6000 OR salary=7000 OR salary=8000;
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT IN(6000,7000,8000);
模糊查询
like
多字符匹配%
练习:查找姓名以字母a开头的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE last_name LIKE ‘a%’;
练习:查找姓名包含字母a的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%';
练习:查找姓名包含字母a和字母e的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE'%e%';
单字符匹配_
练习:查找姓名第二个字母是a的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE last_name LIKE '_a%';
练习:查找姓名第二个字母是_的并且第三个字母是a员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE last_name LIKE '_\_a%';
正则匹配
被匹配字符串 RLIKR\REGEXP 正则表达式
选项 | 说明 | 举例 | 备注 |
---|---|---|---|
^ | 匹配以字符开头的串 | ‘^b’ | 匹配以b开头的字符串 |
$ | 匹配以字符结尾的串 | ‘st$’ | 匹配以st为结尾的字符串 |
. | 匹配单个字符 | ‘b.t’ | 匹配b和t之间有一个未知字符的字符串 |
* | 匹配任意各字符 | ‘b*t’ | 匹配b和t之间有任意个未知字符的字符串 |
+ | 匹配前面的字符一个或多个 | ‘ba+’ | 匹配b开头且有一个或多个a的字符 |
[字符串] | 匹配包含字符串的文本 | ‘[abc]’ | 匹配包含a或b或c的字符 |
SELECT last_name,salary
FROM employees
WHERE last_name RLIKE '___a';
SELECT last_name,salary
FROM employees
WHERE last_name RLIKE '%a%' OR last_name RLIKE '%k%';
SELECT last_name,salary
FROM employees
WHERE first_name RLIKE 'e$';
SELECT last_name,salary
FROM employees
WHERE employee_id i;
SELECT last_name,salary
FROM employees
WHERE last_name RLIKE '%a%' OR last_name RLIKE '%k%';
位运算符
运算符 | 作用 |
---|---|
& | 按位与 |
| | 按位或 |
^ | 按位异或(XOR) |
~ | 按位取反 |
>> | 按位右移 |
<< | 按位左移 |
SELECT 8>>1,8<<1
FROM DUAL;
结果:4 16
注:右移一位除以2,左移一位乘以2
排序与分页
排序
order by
升序:asc(默认) 降序:desc
练习:按照员工工资升序排列显示员工信息
SELECT employee_id,salary,salary*12 'annual_salary'
FROM employees
ORDER BY salary;
练习:按照年工资降序排列显示员工信息
SELECT employee_id,salary,salary*12 'annual_salary'
FROM employees
WHERE department_id IN(50,60,70)
ORDER BY annual_salary DESC;
注:sql的编译顺序是 from表 -> where过滤-> select查询信息 ->order by
故,一般将order by语句写在最后,where需要声明在from后,order by之前,并且对别名的使用不能在where语句中出现
练习:二级排序,按照employee_id降序,若employee_id相同按照annual_salary升序
SELECT employee_id,salary,salary*12 'annual_salary'
FROM employees
ORDER BY employee_id DESC ,annual_salary;
分页(切片)
limit 开始偏移量,显示的记录条数
练习:查询从第40条记录开始的20条记录的信息
SELECT *
FROM employees
LIMIT 40,20;
SELECT *
FROM employees
LIMIT 20 OFFSET 40;
注:limit语句写在整个select语句之后!
多表查询
基本多表查询
练习:查询员工号及部门名称
SELECT employee_id,department_name
FROM employees,departments
--连接条件
WHERE employees.department_id=departments.department_id;
注:多表查询需要指明连接条件,因此多表查询的前提是需要查询的多个表之间要有连接键
练习:查询员工号、部门名称、部门号
SELECT employee_id,departments.department_id,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
注:当查询的字段在多个表中均存在时,要注明查询的是哪个表中的字段
练习:查询员工号,部门号,部门名称(可对表进行别名使用)
SELECT em.employee_id,de.department_id,de.department_name
FROM employees em,departments de
WHERE em.department_id=de.department_id;
注:由于sql语句的执行顺序是from -》 where -》 select,因此对表取别名后,在sql语句中表的原名均被别名覆盖,所以where语句以及select语句均需要使用的表的别名而不能使用表的原名
如下示例是错误的做法:
-- 错误写法
SELECT employees.employee_id,de.department_id,de.department_name
FROM employees em,departments de
WHERE employees.department_id=de.department_id;
练习:查询员工号,员工姓名,部门名称,员工所在城市(三表查询)
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;
多表查询的分类
等值连接与非等值连接
非等值连接如下
练习:求每个员工的姓名,工资,以及对应工资等级
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
分析:员工姓名及工资在employees表中,工资等级在job_grades表中,但这两张表并无连接键相连,此时可用非等值连接来连接两张表并避免笛卡尔积错误
自连接与非自连接
自连接如下
练习:求员工id,姓名以及他的管理者 的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id;
分析:由于员工的id和姓名以及他的管理者的id和姓名均在employees表中,此时需要使用自连接
注:自连接即同一张表自己连接,连接时要将该表视为两张表并用不同的别名代替进行等值连接
内连接与外连接
内连接:满足两表连接条件的连接,如下当两表满足employees.department_id=departments.department_id条件时查询出的结果(中图,A∩B)
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
内连接的另一种写法
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;
三表内连接
SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.department_id=d.department_id
JOIN locations l
ON d.location_id=l.location_id;
外连接:在上述连接条件中employees.department_id=departments.department_id中,可能存在employees.department_id为null但departments.department_id不为空的记录,反之亦然,然而此时由于不满足连接条件因此无法查到这些记录。外连接即为了查询到这些记录
左外连接:查询左表有值但右表为空的记录(左上图)
右外连接:查询左表为空但右表有值的记录(右上图)
左连接练习:查询所有员工的员工姓名及其对应所在部门(左上图)
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;
右连接练习:查询所有部门名称及各部门所在员工姓名(右上图)
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;
练习:左中图
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL;
练习:右中图
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;
满连接:mysql无法实现,需要借助union all实现
union:A∪B且去重,由于有去重操作因此效率较低
union all:A∪B,不去重
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departmnets d
ON e.department_id=d.department_id;
左上图union alll右中图=满连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;
多表连接练习
练习1:显示所有员工的姓名,部门号和部门名称
SELECT last_name,d.department_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;
练习2:查询90号部门的员工job_id和location_id
SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id=90;
练习3:查询所有有奖金的员工的e.last_name,d.department_name,l.location_id,l.city
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id
LEFT JOIN locations l
ON d.location_id=l.location_id
WHERE e.commission_pct IS NOT NULL;
练习4:选择城市在Toronto工作的员工的e.last_name,j.job_id,d.department_id,d.department_name,city
SELECT e.last_name,j.job_id,d.department_id,d.department_name,city
FROM employees e JOIN departments d
ON e.department_id=d.department_id
JOIN jobs j
ON e.job_id=j.job_id
JOIN locations l
ON d.location_id=l.location_id
WHERE city='Toronto';
练习5:查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为Executive
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.department_id=d.department_id
LEFT JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_name='Executive';
练习6:选择指定员工的姓名、员工号,及其管理者的姓名、员工号
SELECT emp.last_name emp_name,emp.employee_id emp_id,mgr.last_name mgr_name,mgr.employee_id mgr_id
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id=mgr.employee_id;
练习7:查询哪些部门没有员工
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;
练习8:查询哪个城市没有部门
SELECT city,department_name
FROM locations l LEFT OUTER JOIN departments d
ON l.location_id=d.location_id
WHERE d.location_id IS NULL;
练习9:查询部门名为Sales和IT的员工信息
SELECT e.*,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id
WHERE department_name IN('Sales','IT');
单行函数
数值函数
普通函数
函数 | 作用 |
---|---|
ABS(X) | 绝对值 |
SIGN(X) | 符号函数 |
PI() | Π值 |
CEIL(x) | 向下取整 |
FLOOR(X) | 向上取整 |
LEAST(X1,X2,X3…) | 最小值 |
GREATEST(X1,X2,X3…) | 最大值 |
RAND() | 0~1之间的随机数 |
RAND(X) | 0~1之间随机数,x表示随机数种子 |
ROUND(x,y) | 四舍五入,保留x的y位小数 |
TRUNCATE(x,y) | 截断x的y位小数 |
SQRT(x) | 开根号 |
SELECT ABS(-5),PI(),CEIL(42.5),FLOOR(42.3),RAND(),RAND(10),ROUND(255.6),ROUND(123.456,2),TRUNCATE(123.589,1)
FROM DUAL;
三角函数
进制转换
字符串函数
函数 | 作用 |
---|---|
ASCII | 返回第一个字符的ascii值 |
length | 返回字符串占用字节数 |
char_length | 返回汉字个数 |
concat(s1,s2,s3,…,sn) | 拼接字符串 |
concat_ws(x,s1,s2,s3,…,sn) | 拼接字符串,并在每个字符串之间添加一个连接符x |
insert(str,idx,len,repstr) | 将str从idx位置开始的len个长度的字符替换为repstr |
replace(str,a,b) | 将str中的a替换为b |
upper(str) | 将str转换为大写 |
lower(str) | 将str转换为小写 |
left(str,n) | 取出str最左边的n个字符 |
right(str,n) | 取出str最右边的n个字符 |
trim(str) | 去除str两边空格 |
ltrim(str) | 去除str左边空格 |
rtrim | 去除str右边空格 |
lpad(str,len,x) | 用x对str进行左填充,直到所有字符个数满足len为止,常用于右对齐 |
rpad(str,len,x) | 用x对str进行右填充,直到所有字符个数满足len为止,常用于左对齐 |
strcmp(str1,str2) | 字符串比较 |
substr(str,idx,len) | 取str从idx开始的len个字符 |
locate(substr,str) | 返回substr在str中第一次出现的位置 |
elt(m,s1,s2,s3) | 返回第m个字符串 |
练习
SELECT ASCII('abc'),LENGTH('ab1'),LENGTH('中国'),CHAR_LENGTH('中国')
FROM DUAL;
SELECT CONCAT('hello','-','world'),CONCAT_WS('-','a','b','c')
SELECT INSERT('hello',2,3,'ax'),REPLACE('helloworld','wor','abcsx')
SELECT UPPER('abcx'),LOWER('ANXS')
SELECT LEFT('abc',2),RIGHT('asdfg',3)
SELECT LPAD(last_name,12,'*'),RPAD(salary,15,'*')
FROM employees
SELECT LTRIM(' lo jh h g '),RTRIM(' lo jh h g '),TRIM(' lo jh h g ')
SELECT REPEAT('i love you ',3),STRCMP('abc','abx')
SELECT SUBSTR('helloworld',2,5),LOCATE('s','aabsbsbs'),ELT(2,'sl','s2','s3')
日期时间函数
流程控制函数
if函数
SELECT last_name,salary,IF(salary>=8000,'高工资','低工资') 'details'
FROM employees;
ifnull(value1,value2)如果value1为null,则返回value2,否则返回原值
SELECT last_name,commission_pct,IFNULL(commission_pct,0) 'details'
FROM employees;
if else函数
SELECT last_name,salary,CASE WHEN salary>6000 THEN '小屌丝'
WHEN salary >8000 THEN '高薪'
WHEN salary>12000 THEN '白骨精'
END 'detail',department_id
FROM employees;
switch case 函数
SELECT last_name,department_id,CASE department_id WHEN 10 THEN salary*2
WHEN 20 THEN salary*3
WHEN 30 THEN salary*4
END 'detail'
FROM employees;
加密解密函数
MySQL信息函数
聚合函数
常见的聚合函数
avg\sum\count三者均不计算null值
SELECT AVG(salary) avg_salary,SUM(salary)
FROM employees;
注:avg()与sum()函数仅使用于数值型数据
SELECT MAX(hire_date),MIN(hire_date)
FROM employees;
注:max()、min()、count()函数可用于任意型数据
avg()=sum()/count()
SELECT COUNT(*),COUNT(last_name)
FROM employees
SELECT COUNT(IFNULL(commission_pct,0))
FROM employees;
SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)),SUM(commission_pct)/107
FROM employees;
结果:0.222 0.072 0.072
注:由于count()函数不计算null值,且avg()函数被除数使用count()计算,因此计算含有null值的平均数的字段时,需要重新定义均值函数
group by使用
声明在from、where之后,order by、limit之前
练习:计算各部门的平均工资
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id;
练习:计算各部门内各工种的平均工资
SELECT department_id,job_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id,job_id
ORDER BY department_id;
练习:计算各部门平均工资以及所有部门平均工资
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP;
注:with rollup可将分组后的平均数再次计算所有平均数
having使用
聚合函数的过滤
练习:显示各部门的平均工资中大于4000的部门
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
HAVING avg_sal>4000;
注:having必须和group by配合使用
练习:显示部门号为10,20,30的平均工资中大于4000的部门
SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IN(10,20,30)
GROUP BY department_id
HAVING avg_sal>4000;
注:
- 不能在where中使用聚合函数过滤,因为聚合函数在where之后执行
- having可过滤非聚合字段,但一般不使用,因为having是先连接后过滤,而where是先过滤后连接,因此从效率上讲,应当把非聚合函数放在where后过滤,将聚合函数放在having后过滤
sql底层原理
sql查询结构
方式1
select 查询字段
from 表
where 多表连接条件 and 非聚合过滤
group by 分组字段
having 聚合函数过滤
order by (asc\desc)排序字段
limit
方式2
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
sql语句执行顺序
from -> where -> group by -> having -> select -> distinct -> order by -> limit
练习:查询各个管理者手下员工的最低工资,其中最低工资不能低于6000。 没有管理者的员工不计算在内
SELECT manager_id,MIN(salary) m_w_minsal
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING m_w_minsal>=6000;
练习:.查询所有部门的名字,location. _id,员工数量和平均工资。并按平均工资降序
SELECT department_name,location_id,COUNT(employee_id),AVG(salary) avg_sal
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name,location_id
ORDER BY avg_sal DESC;
子查询
引例:谁的工资比Abel多?
实现方式1:(自连接)
SELECT e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.salary>e2.salary
WHERE e2.last_name='Abel'
实现方式2:(子查询)
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
单行子查询
练习:查询工资大于149号员工工资的员工信息
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE employee_id=149
);
练习:查询job_id和141号员工job_id相同,且工资大于143号员工工资的员工last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
练习:返回工资最少的员工last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
练习:查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,
manager_id,department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
SELECT manager_id
FROM employees
WHERE employee_id =141
)
AND department_id=(
SELECT department_id
FROM employees
WHERE employee_id =141
)
AND emploee_id<>141;
having子查询练习:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) min_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING min_sal>(
SELECT MIN(salary)
FROM employees
WHERE department_id=110
);
流程控制子查询练习:查询员工的employee_id,last_name,location
其中,如果员工的department_id与location_id为1800的department_id相同,则location为Canana,否则其余为USA
SELECT employee_id,last_name, IF(department_id=(SELECT department_id
FROM departments
WHERE location_id=1800 ),
'Canana','USA') 'location'
FROM employees
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id
FROM departments
WHERE location_id=1800)
THEN 'Canana'
ELSE 'USA'
END 'location'
FROM employees
多行子查询
子查询结果是由多条记录组成的一张表
IN ANY ALL
IN:符合子查询结果中的其中一个
练习:查询员工工资符合各部门最小工资的员工信息
SELECT employee_id,last_name
FROM employees
WHERE salary IN(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
)
ANY:符合子查询结果中任意一个
练习:返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号
SELECT employee_id,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)
ALL:符合子查询结果中所有值
练习:查询平均工资最低的部门id
解1:
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
HAVING avg_sal <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
解2:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
-- 查询各部门最小的平均工资
SELECT MIN(avg_sal)
FROM (
-- 查询各部门平均工资,返回一张表,若该表作为子查询的表,需将其进行别名命名
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_avg_sal
)
注:聚合函数不能嵌套使用
多行子查询的空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN(
-- 若子查询结果中存在空值,则整个查询结果不显示结果
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
)
相关子查询
不相关子查询:子查询的查询条件不依赖于外层查询,执行顺序为先执行子查询,在子查询结果集上执行外层查询
相关子查询:子查询中的查询条件设计与外层表数据的关联,执行顺序为先执行外层查询,取出外层查询的每一条记录与子查询中的条件进行判断是否满足,如果满足则保留该记录
在子查询中使用外部表
练习:查询员工工资大于公司平均工资的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
练习:查询员工工资大于本部门平均工资的员工信息
解1:使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id=e1.department_id -- 子查询中使用外部表
)
解2:在from中声明子查询
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1,(
-- 在子查询中查出各部门的平均工资作为新的查询表,并通过department_id与员工表进行连接
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) e2
WHERE e1.department_id=e2.department_id
AND e1.salary>e2.avg_sal
注:除了group by 与 limit之外,其他位置均可使用子查询
练习:查询员工号和工资,并按照部门名降序排列
FROM employees e
ORDER BY(
SELECT department_name
FROM departments d
WHERE e.department_id=d.department_id
)DESC
exists关键字:满足子查询的结果,就返回true并保留记录,否则返回false不保留记录
练习:查询管理者的employee_id,last_name,department_id
解1:使用多表连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id=mgr.employee_id
解2:使用子查询
SELECT employee_id,last_name,department_id
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
)
解3:使用exists关键字
SELECT employee_id,last_name,department_id
FROM employees e1
WHERE EXISTS(
SELECT * -- 使用exists关键字时,子查询中的select字段无关
FROM employees e2
WHERE e1.employee_id=e2.manager_id
)
not exists关键字
练习:查询departments表中,不存在于employees表中的部门的department_id与department_name
解1:多表连接
SELECT e.department_id,d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL
解2:子查询
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
SELECT*
FROM employees e
WHERE e.department_id=d.department_id
)
子查询习题
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id =(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
)
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
SELECT job_id,last_name,salary
FROM employees
WHERE salary> (
SELECT AVG(salary)
FROM employees
)
#3.选择工资大于所有JOB_ID=‘SA_ MAN’的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
SELECT salary
FROM employees
WHERE job_id='SA_MAN'
)
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT job_id,last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
)
#5.查询在部门的location_id为1700的部门工作的员工的员工。
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=1700
)
#6.查询管理者是King的员工姓名和工资
SELECT emp.last_name,emp.salary
FROM employees emp JOIN employees mgr
ON emp.manager_id=mgr.employee_id
WHERE mgr.last_name='King'
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name='King'
)
#7.查询工资最低的员工信息: last. name, salary
SELECT last_name,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
)
#8.查询平均工资最低的部门信息
-- 方式1
SELECT *
FROM departments
WHERE department_id=(
-- 获取平均工资最小的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = ( -- 查询各部门平均工资最小值
SELECT MIN(avg_sal)
FROM (-- 查询各部门平均工资
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dp_avg_sal
)
)
-- 方式2
SELECT *
FROM departments
WHERE department_id=(
-- 获取平均工资最小的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL( -- 查询各部门平均工资最小值
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
)
-- 方式3
SELECT *
FROM departments
WHERE department_id=(
-- 获取平均工资最小的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = ( -- 查询各部门平均工资最小值
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)
)
-- 方式4
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)t_avg_sal
WHERE d.department_id=t_avg_sal.department_id
#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)t_avg_sal
WHERE d.department_id=t_avg_sal.department_id
#10.查询平均工资最高的job信息
-- 方式1
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY job_id
)
)
-- 方式2
SELECT *
FROM jobs
WHERE job_id =(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 0,1
)
)
#11.查询平均工资高于公司平均工资的部门的哪些?
SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING avg_sal>(
SELECT AVG(salary)
FROM employees
)
#12.查询出公司中所有manager 的详细信息
SELECT DISTINCT mgr.employee_id,mgr.salary
FROM employees emp JOIN employees mgr
ON emp.manager_id=mgr.employee_id
SELECT DISTINCT employee_id,salary
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
)
#13.各个部门中最高工资中最低的那个部门的最低工资是多少?
-- 方式1
SELECT department_id,salary
FROM employees
HAVING salary = (
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
)t_max_sal
)
-- 方式2
SELECT e.department_id,e.salary
FROM employees e,(
SELECT department_id,MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal
LIMIT 0,1
)t_max_sal
WHERE e.department_id=t_max_sal.department_id
#14.查询平均工资最高的部门的manager的详细: last_name, department_id, email, salary
mgr.last_name, mgr.department_id, mgr.email, mgr.salary
-- 方式1
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=(
-- 获取在平均工资最高的部门中工作的经理的manager_id
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
-- 获取平均工资最高的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(-- 查询各部门最高的平均工资
SELECT MAX(avg_sal)
FROM (
SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) t_avg_sal
)
AND manager_id IS NOT NULL
)
)
-- 方式2
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
-- 获取在平均工资最高的部门中工作的经理的manager_id
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
-- 获取平均工资最高的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(-- 查询各部门最高的平均工资
SELECT AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1
)
)
)
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
-- 获取在平均工资最高的部门中工作的经理的manager_id
SELECT DISTINCT manager_id
FROM employees e,(
-- 查询各部门最高的平均工资
SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1
)t_max_sal
WHERE e.department_id=t_max_sal.department_id
)
15.查询部门的部门号,其中不包括job_id是‘ST_CLERK’的部门号
SELECT department_id
FROM employees
WHERE department_id!=(
SELECT DISTINCT department_id
FROM employees
WHERE job_id='ST_CLERK'
)
SELECT department_id
FROM employees e
WHERE NOT EXISTS(
SELECT *
FROM departments d
WHERE e.department_id=d.department_id
AND job_id='ST_CLERK'
)
#16.选择所有没有管理者的员工的last_ name
SELECT *
FROM employees
WHERE last_name='King'
SELECT last_name
FROM employees
WHERE manager_id IS NULL
#17.查询员工号、姓名、雇用时间、工资,其中员工的管理者为"De Haan "
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='De Haan'
)
#18.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资(相关子查询)
SELECT employee_id,last_name,salary
FROM employees e
WHERE salary >(
SELECT AVG(salary)
FROM employees
WHERE department_id=e.department_id
)
#19.查询每个部门下的部门人数大于5的部门名称(相关子查询)
SELECT department_name
FROM departments d
WHERE 5 < (
SELECT COUNT(*)
FROM employees
WHERE department_id=d.department_id
)
#20.查询每个国家下的部门个数大于2的国家编号(相关子查询)
数据库的创建与删除
创建数据库
CREATE DATABASE test1
CREATE DATABASE IF NOT EXISTS test2;
CREATE DATABASE IF NOT EXISTS test3 CHARACTER SET 'UTF8'-- 创建数据库并设定字符集
使用数据库
SHOW DATABASES
SHOW TABLES
删除数据库
DROP DATABASE test1
DROP DATABASE IF EXISTS test2
创建和管理表
创建表
方式1
CREATE TABLE IF NOT EXISTS mytemp1
(
sid INT,
sname VARCHAR(20),
hireDate DATE
);
#查看创建表的语句结构
SHOW CREATE TABLE mytemp1
方式2:基于现有的表,同时导入数据
CREATE TABLE mytemp2
AS
SELECT employee_id,last_name,salary
FROM employees
练习:创建与员工表相同的字段,且不导入数据
CREATE TABLE mytemp3
AS
SELECT *
FROM employees
WHERE 1=2
修改表
添加字段
-- 默认添加在最后
ALTER TABLE mytemp1
ADD salary DOUBLE(10,2)-- 添加一个工资字段,数据类型为double,共十位数,其中2位小数
-- 添加在第一个字段位置
ALTER TABLE mytemp1
ADD phone_number VARCHAR(20) FIRST
-- 添加在sname字段后
ALTER TABLE mytemp1
ADD email VARCHAR(45) AFTER sname
修改字段
ALTER TABLE mytemp1
MODIFY sname VARCHAR(25) DEFAULT '张三'
重命名字段
ALTER TABLE mytemp1
CHANGE salary mouth_salary DOUBLE(10,3)
删除字段
ALTER TABLE mytemp1
DROP COLUMN email
重命名表
方式1:
RENAME TABLE mytemp1
TO mytemp2
方式2:
ALTER TABLE mytemp2
RENAME TO mytemp3
删除表
DROP TABLE IF EXISTS mytemp3
清空表
TRUNCATE TABLE mytemp2
commit与rollback
truncate与delete异同点
相同点:均可清空表中数据而保留表的字段
不同点:truncate删除全部数据,且不可回滚
delete可通过where过滤从而只删除部分数据,且可回滚
注:DDL自动提交,且不可回滚
DML自动提交,但若在执行DML之前,执行 SET autocommit=FALSE,便可实现回滚操作
练习:
CREATE TABLE mytemp3
AS
SELECT *
FROM employees;
SET autocommit=FALSE;
DELETE FROM mytemp3;
SELECT * FROM mytemp3;
ROLLBACK;
SELECT * FROM mytemp3;
数据处理之增删改
添加数据
方式1:添加全部数据
INSERT INTO test VALUES(1,'张三',3500,'1998-2-6')
方式2:添加指定字段数据
INSERT INTO test(id,sname,salary)
VALUES(2,'李四',2000)
方式3:根据查询结果添加数据
INSERT INTO test(id,sname,salary)
SELECT employee_id,last_name,salary
FROM employees
LIMIT 0,2
更新数据
UPDATE test
SET salary=salary+salary*0.2
WHERE id=2
删除数据
DELETE FROM test
WHERE id=2
约束
添加与删除约束
create table添加约束
alter table添加删除约束
查看约束
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME='employees'
not null(非空约束)
-- 添加非空约束
ALTER TABLE emp1
MODIFY email VARCHAR(15) NOT NULL
INSERT INTO emp1(employee_id,email) VALUES('1001','846@qq.com')
DESC emp1
-- 删除非空约束
ALTER TABLE emp1
MODIFY salary DOUBLE(8,2)
unique(唯一约束)
-- 添加唯一约束
ALTER TABLE emp1
MODIFY first_name VARCHAR(20) UNIQUE
ALTER TABLE emp1
ADD CONSTRAINT UNIQUE(last_name)
-- 创建混合唯一约束
CREATE TABLE stu1
(
id INT,
sname VARCHAR(20),
passw VARCHAR(20),
-- 表级约束
CONSTRAINT uk_name_pas UNIQUE(sname,passw)
)
CREATE TABLE stu1
(
id INT,
sname VARCHAR(20),
passw VARCHAR(20),
UNIQUE KEY(sname,passw)
)
-- 删除唯一约束
-- 创建唯一约束时会自动创建唯一索引
-- 删除唯一索引时需要根据索引名进行删除
-- 故删除唯一约束需要根据唯一索引名进行删除
ALTER TABLE stu
DROP INDEX sname
ALTER TABLE emp1
DROP INDEX last_name_2
primary key(主键)
-- 添加主键约束
ALTER TABLE stu
ADD PRIMARY KEY (id)
自增列auto_increment
foreign key(外键)
创建表时添加外键
-- 先创建主表
CREATE TABLE dept1
(
dept_id INT,
dept_name VARCHAR(15)
);
ALTER TABLE dept1
ADD CONSTRAINT PRIMARY KEY(dept_id);-- 定义主表主键
DESC dept1
-- 创建从表
CREATE TABLE emp1
(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
-- 表级约束
-- 定义从表外键
CONSTRAINT fd_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);
创建表后添加外键
-- 先创建主表
CREATE TABLE dept2
(
dept_id INT,
dept_name VARCHAR(15)
);
-- 创建从表
CREATE TABLE emp2
(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);
ALTER TABLE dept2
ADD CONSTRAINT PRIMARY KEY(dept_id);-- 定义主表主键
ALTER TABLE emp2
ADD CONSTRAINT fd_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);
删除外键约束
alter table 从表名
drop foreign key 外键约束名
check(检查约束)
default(默认值)
视图
视图的创建
1.创建新的数据库与表
-- 创建数据库testdb
CREATE DATABASE IF NOT EXISTS testdb;
-- 切换数据库
USE testdb;
-- 在当前数据库创建表emp与表哦dept
CREATE TABLE emp
AS
SELECT *
FROM atguigudb.employees;
CREATE TABLE dept
AS
SELECT *
FROM atguigudb.departments;
2.创建视图
单表创建视图
CREATE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emp
SELECT * FROM vu_emp1
-- 创建视图并使用别名作为视图字段名
CREATE VIEW vu_emp2
AS
SELECT employee_id eid,last_name lname,salary msalary
FROM emp
SELECT * FROM vu_emp2
CREATE VIEW vu_emp3(eid,lname,msalary)
AS
SELECT employee_id,last_name,salary
FROM emp
WHERE salary>8000
SELECT * FROM vu_emp3
-- 使用聚合函数创建不存在的字段组成视图
CREATE VIEW vu_emp_sal
AS
SELECT department_id did,AVG(salary) avg_sal
FROM emp
GROUP BY department_id
SELECT * FROM vu_emp_sal
多表创建视图
CREATE VIEW vu_emp_dept
AS
SELECT e.employee_id,d.department_id,d.department_name
FROM emp e JOIN dept d
ON e.employee_id=d.department_id
SELECT * FROM vu_emp_dept
CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,'-',d.department_name) emp_info
FROM emp e JOIN dept d
ON e.employee_id=d.department_id
SELECT * FROM vu_emp_dept1
查看视图
-- 查看数据库中的表与视图
SHOW TABLES
-- 查看视图结构
DESC vu_emp1
-- 查看视图属性信息
SHOW TABLE STATUS LIKE 'vu_emp1'
视图的修改
视图的更新
ALTER VIEW vu_emp1
AS
SELECT last_name,salary
FROM emp
视图的删除
DROP VIEW vu_emp1
注:若视图中的字段在原表中存在,则对原表的修改会影响到视图,同样对视图的修改也会影响到原表,但若视图的字段在原表中不存在,则无法进行修改
存储过程与函数
存储过程的创建与使用
有参存储过程
DELIMITER //-- 设置分隔符(结束标志默认为;)为//,避免存储过程的创建过程中遇见;时停止编译
CREATE PROCEDURE show_avg_salary()
BEGIN
SELECT AVG(salary) avg_sal
FROM emp;
END//
DELIMITER;-- 将分隔符重新换回;
CALL show_avg_salary()-- 调用存储过程
有参存储过程
-- 创建存储过程
DELIMITER//
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)-- 使用输出参数ms
BEGIN
SELECT MIN(salary) INTO ms
FROM emp;
END//
DELIMITER;
-- 调用存储过程
CALL show_min_salary(@ms)
-- 查看参数值
SELECT @ms
-- 创建存储过程,根据姓名查询该人的工资
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))-- 使用输入参数
BEGIN
SELECT salary
FROM emp
WHERE last_name=empname;
END//
DELIMITER ;
-- 调用方式1,直接传入参数
CALL show_someone_salary('Abel')
-- 调用方式2,定义变量赋值,再传入参数
SET @empname :='Abel';-- :=为标准赋值符号
CALL show_someone_salary(@empname)
输入 输出双参数
-- 根据姓名查询工资
DELIMITER //
CREATE PROCEDURE showsalary(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary
FROM emp
WHERE last_name=empname;
END//
DELIMITER ;
SET @empname='Abel';
CALL showsalary(@empname,@empsalary);
SELECT @empsalary
练习:查询员工名称为ename的领导姓名
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT ename VARCHAR(20))
BEGIN
SELECT last_name INTO ename
FROM emp
WHERE employee_id = (
SELECT manager_id
FROM emp
WHERE last_name=ename
);
END//
DELIMITER ;
SET @ename='Abel';
CALL show_mgr_name(@ename);
SELECT @ename
函数的使用
-- 创建函数前执行此语句可防止函数创建失败
SET GLOBAL log_bin_trust_function_creators=1;
-- 根据姓名查询某员工的email
DELIMITER //
CREATE FUNCTION show_email_by_name(empname VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
RETURN(SELECT email FROM emp WHERE last_name=empname);
END//
DELIMITER ;
SET @empname='Abel';
SELECT show_email_by_name(@empname)
-- 查询某部门的员工数
DELIMITER //
CREATE FUNCTION show_number(dept_id INT)
RETURNS INT
BEGIN
RETURN (
SELECT COUNT(*)
FROM emp
WHERE department_id=dept_id
);
END//
DELIMITER ;
SET @dept_id=30;
SELECT show_number(@dept_id)