关于数据库(DB)
- 数据库是按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
- 有什么类型
- 关系型数据库
- 所谓关系模型就是“一对一、一对多、多对多”等关系模型，
- 非关系型数据库
- 它们不保证关系数据的ACID特性。去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。
- 关系型数据库
- 什么是数据库管理系统（DBMS）
- 数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。
- 常见的数据库管理系统有：MySQL、Oracle、DB2、MS SQL Server、SQLite、PostgreSQL、Sybase等。
- 什么是SQL
- 是结构化查询语言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
- SQL的分类
- DQL：数据查询语言 SQL中最重要的
- DDL：数据定义语言
- DML：数据操纵语言
- DCL：数据控制语言
- TPL：数据事务管理语言
- CCL：指针控制语言
- DBMS、SQL、DB之间的关系
- DBMS通过执行SQL来操作DB中的数据。
什么是MySql
- MySQL是目前最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS应用软件之一。 国内淘宝网站就使用的是MySQL集群。
- MySQL特点
- MySQL有开源版本和收费版本，你使用开源版本是不收费的。
- MySQL支持大型数据库，可以处理上千万记录的大型数据库。
- MySQL使用标准的SQL数据库语言形式。
- MySQL在很多系统上面都支持。
- MySQL对Java，C都有很好的支持，当然其他的语言也支持比如Python、PHP。
- MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
MySql的基本命令
- show databases; 列出数据库管理系统中的数据库
- create database xxx; 创建新的数据库
- ues xxx; 使用数据库
- selece database(); 查看使用的哪个数据库
- show tables; 查看数据库中的表
- drop database xxx; 删除数据库
DQL命令
查询语句
select 字段名 from 表名;
条件查询
| 条件 | 说明 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| > | 大于 |
| < | 小于 |
| between...and... | 等同于 >= and <= |
| is null | 为空 |
| is not null | 不为空 |
| <=> | 安全等于（可读性差，很少使用了）。 |
| and 或 && | 并且 |
| or 或 || | 或者 |
| in | 在指定的值当中 |
| not in | 不在指定的值当中 |
| exists | 存在 |
| not exists | 不存在 |
| like | 模糊查询 |
-
条件查询语法格式
select 3 ... from 1 ... where 2 过滤条件;
排序操作
select .. from .. order by 字段 asc(可不写，默认升序)/desc(降序)
-
多字段排序
-- 查询员工的编号、姓名、薪资，按照薪资升序排列，如果薪资相同的，再按照姓名升序排列。 select empno,ename,sal from emp order by sal asc, ename asc;
distinct去重
- 当distinct出现后，后面多个字段一定是联合去重的
数据处理函数
字符串处理函数
-
转大写upper或ucase
select upper(ename) as ename from emp; -
转小写lower和lcase
select lower(ename) as ename from emp; -
截取字符串substr
-
第一种：substr('被截取的字符串', 起始下标, 截取长度)
-
第二种：substr('被截取的字符串', 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾
注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）-- 找出员工名字中第二个字母是A的 select ename from emp where substr(ename, 2, 1) = 'A';
-
-
获取字符串长度length
-
注意一个汉字占两个字节
select length('你好111') -- 结果是 7
-
-
获取字符的个数char_length
select char_length('你好111') -- 结果是 5 -
字符串拼接concat
-- 语法 concat('字符串1', '字符串2', '字符串3'....) select concat('hello','你好','word'); -- 结果是 hello你好word -
去除字符串前后空白trim
select concat(trim(' abc '), 'def');
数字相关
-
rand()和rand(x)
rand()生成0到1的随机浮点数。
带x参数有一个随机种子，会生成固定的值
-
round(x)和 round(x,y) 四舍五入
-- round(x) 四舍五入保留整数位，舍去小数位 select round(5.333); -- 结果是5 -- round(x,y) 四舍五入，保留y位小数 select round(5.5456,2); -- 结果是5.55 -
truncate(x, y) 舍去,保留y个小数位
select truncate(9.999, 2); -- 结果是 9.99 -
ceil(向上取整)与floor(向下取整)
select ceil(2.1); -- 结果是 3 select floor(2.9); -- 结果是 2
空处理 (这个时比较重要的)
-
ifnull(x,y)
-- 如果x为空时，将x当作y进行处理 -- 在sql语句中，凡是有NULL参与的运算，结果一律时NULL -- 所以ifnull很有必要 -- 比如 查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。） select ename,(sal + ifnull(comm,0)) * 12 yearsal from emp;
日期和时间相关函数
-
获取当前日期和时间
select now(); -- 获取执行select语句的时刻 select sysdate(); -- 获取的时执行sysdate函数的时刻 -- 这个sql语句能测试出两者的区别 -- sysdate()显示的时间会比now()显示的时间晚2秒 select now(), sleep(2), sysdate(); -- 获取本地时间 select localtime(); -
获取当前日期
-- 三个语句的结果都是获取当前日期 格式是 yyyy-mm-dd select curdate(); select current_date(); select current_date; -
获取当前时间
-- 三个语句都是获取当前时间 格式是 HH：MM：SS select curtime(); select current_time(); select current_time; -
获取单独的年月日时分秒
select year(now()); select month(now()); select day(now()); select hour(now()); select minute(now()); select second(now()); -
date_format日期格式化函数
-
将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型）
-- 语法格式 date_formate(日期， '日期格式'); /* 第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。 第二个参数：指定要格式化的格式字符串。 %Y：四位年份 %y：两位年份 %m：月份（1..12） %d：日（1..30） %H：小时（0..23） %i：分（0..59） %s：秒（0..59）*/ -- 获取当前系统时间，让其以这个格式展示：2000-10-11 20:15:30 select date_format(now(),'%Y-%m-%d %H:%i:%d'); /*在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串*/
-
-
str_to_date函数
-
该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）
-- 如果日期格式符合以下的几种格式，mysql都会自动进行类型转换的。 '2023-10-01' '23-10-01' '23/10/01' '2023/10/01' -- str_to_date('10/01/2023','%m/%d/%Y')
-
-
dayofweek、dayofmonth、dayofyear函数
-- dayofweek：一周中的第几天（1~7），周日是1，周六是7。 select dayofweek(now()); -- dayofmonth：一个月中的第几天（1~31） select dayofmonth(now()); -- dayofyear：一年中的第几天（1~366） select dayofyear(now()); -
datediff函数
-- 计两个日期的时间差，不计算时分秒 -- 前日期大显示的是正数，后日期大显示的负数 select datediff('2000-10-11','2000-10-10'); -- 结果是 1 -
timediff函数
-- 计算两个日期所差时间 select timediff('12:00:00','11:00:00'); -- 结果是 01:00:00
if函数
-
如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：
select if(500<1000, "YES", "NO"); -- 工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。 select ename, job, if(job='MANAGER',sal*1.1, if(job='SALESMAN', sal*1.2, sal)) sal from emp;
case
-
和if函数差不多
-- 语法格式 case.. when.. then.. when.. then.. else.. end -- 上面if的语句可以使用case语句来完成 -- 工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。 select ename,job, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.2 else sal end sal from emp;
cast函数
-
cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
-- 语法格式 cast(值 as 数据类型) -- 表示将字符串'2020-10-11'转换成日期date类型 select cast('2020-10-11 20:00:00' as date); -- 2020-10-11 /* 在使用cast函数时，可用的数据类型包括 date：日期类型 time：时间类型 datetime：日期时间类型 signed：有符号的int类型（有符号指的是正数负数） char：定长字符串类型 decimal：浮点型 */ select cast('2020-10-11 20:00:00' as time) -- 20:00:00 select cast('2020-10-11 20:00:00' as datetime) -- 2020-10-11 20:00:00 select cast('-5.3' as signed) -- -5 -5.3可以不带引号 select cast('5.3' as signed) -- 5 select cast(123.456 as char(4)) -- 123. select cast(123.456 as decimal(5,1)) -- 123.4 五位有效数字，一位小数
加密函数
-
md5加密
- 可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的
select md5('sixu'); -- 结果是：2188dbcaa30c0cf3e03405eaac523bc7
分组函数
- max
- min
- avg
- sum
- count
分组查询
- group by 注意：当sql语句出现group by的时候，select后面只能出现分组的字段和分组函数
总结单表的DQL语句执行顺序
select... 5
from... 1
where... 2
group by... 3
having... 4
order by... 6
连接查询
从两张或更多张表中联合查询数据称为多表查询，又叫做连接查询。
- 连接查询的分类
- 根据连接方式的不同进行分类：
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接 注意：MySql中没有全连接
- 内连接
- 根据连接方式的不同进行分类：
笛卡尔积现象
-
两张表或者多张表进行联合查询的时候，没有做任何条件的限制，查出的结果会是多张表表记录的乘积
-
为了避免笛卡尔积的现象出现，多表查询的时候要添加限制条件（虽然添加条件限制后笛卡尔积的这种现象被避免了，但是底层匹配的次数一次也没有少）
内连接
- 所谓内连接（inner join），就是指多张表的交集部分，如果是两张表，将两张表看做两个圆，那么圆相交的部分就是内连接的数据
内连接之等值连接
-
找多张表的等值条件
# 1. 等值连接：查询员工的名字和工作岗位，找两张表的等值关系，根据内连接进行查询 select e.ename '员工姓名', d.dname '所属部门' from emp e join dept d on e.deptno = d.deptno;
内连接之非等值连接
-
查询条件不是等值的多表查询
# 2. 非等值连接：查询每个员工的工资等级，要求显示员工名、工资、工资等级。 select e.ename, e.sal, s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
内连接之自连接
-
将一张表当做两张表或多张表进行自连接查询
# 3. 自连接：将一张表当成两张表看 找出每个员工的直属领导，要求显示员工名、领导名。 select e.ename '员工姓名', l.ename '领导姓名' from emp e join emp l on e.mgr = l.empno;
外连接
- 外连接是除了满足条件的记录查询出来，再将其中一张表的记录全部查询出来，另一张表如果没有与之匹配的记录，自动模拟出NULL与其匹配。
左连接
-
显示满足条件的记录和左表的所有信息
# 4. 查询所有部门信息，并且找出每个部门下的员工。 select d.*,e.ename from dept d left join emp e on d.DEPTNO = e.DEPTNO; # 找出所有员工的上级领导，要求显示员工名和领导名。
右连接
-
找出所有员工的上级领导，要求显示员工名和领导名。
# 所有的右连接都可以写成左连接，改变表的位置即可 # 5. 找出所有员工的上级领导，要求显示员工名和领导名。 select e.ename 员工, l.ename 领导 from emp e left join emp l on e.mgr = l.empno; select e.ename 员工, l.ename 领导 from emp l right join emp e on e.mgr = l.empno;
多表查询
-
多张表联合进行查询
# 6. 多表查询 找出每个员工的部门，并且要求显示每个员工的薪资等级。 # 可以先找出员工和部门之间的关系，然后在去查员工的薪资等级 SELECT e.ename,d.dname, s.grade from emp e join dept d on e.DEPTNO = d.DEPTNO join salgrade s on e.SAL BETWEEN s.LOSAL and HISAL;
子查询
- select中嵌套select语句就叫字查询
- select语句可以嵌套在哪些地方
- where后面
- from后面
- select后面
出现在where后的
# 7. 找出高于平均薪资的员工姓名和薪资。
select ename, sal from emp where sal > (select avg(sal) from emp);
出现在from后面的
- 把from后面的子查询当做是一张临时表来看待
# 8.找出每个部门的平均工资等级
# 先找出每个部门的平均工资
select deptno, avg(sal) avgsal from emp group by deptno;
# 在根据找出的平均工资使用多表查询其等级,
# select t.*, s.grade from （这里应该将刚刚的查询结果当做一个新的表然后与等级表连接查询） join salgrade s on t.avgsal between s.losal and s.hisal;
select t.*, s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
出现在select后面的
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
in和exists区别
IN 和 EXISTS 都是用于关系型数据库查询的操作符。不同之处在于：
-
IN操作符是根据指定列表中的值来判断是否满足条件，而EXISTS操作符则是根据子查询的结果是否有返回的记录集来判断
-
EXISTS 操作符通常比 IN 操作符更快，尤其是在子查询返回记录数很大的情况下。因为 EXISTS 只需要判断是否存在符合条件的记录，而 IN 操作符需要比对整个列表，因此执行效率相对较低。
-
IN 操作符可同时匹配多个值，而 EXISTS 只能匹配一组条件。
# in 和 exists # 查询下过订单的用户 select * from t_customer c where exists (select * from t_order o where c.customer_id = o.customer_id); select * from t_customer c where not exists (select * from t_order o where c.customer_id = o.customer_id); # 使用in查询 select * from t_customer where customer_id in(select distinct customer_id from t_order); /* 他们之间的区别 1. in 和 exists 都是关系型数据库的查询操作符，in根据列表中的值进行判断是否满足条件，而exists根据子查询返回的结果集进行判断是否满足条件 2. in可以同时匹配多个值，而exists只有一个结果集，只能匹配一组条件 3. exists的效率是比in的效率要高的，特别时在子查询返回数据量很大的情况下，因为in需要与子查询的结果一一进行比对，而exists只需要判断是否存在符合条件的记录 4. in后面是需要跟字段名的但是exists并不需要 */
union和union all
- 将两个查询结果进行拼接，union可以去重，union all不能去重
limit
-
查询第几条到第几条的记录。通常是因为表中数据量太大，需要分页显示。
-- 语法格式 limit 开始下标, 长度 -- eg:查询员工表前5条记录 select * from emp limit 0,5; -- 假设每页显示3条记录：pageSize = 3 -- 第pageNo页：limit (pageNo - 1)*pageSize, pageSize
练习
# 1. 取得每个部门最高薪水的人员名称
# 先找出每个部门的最高工资
select deptno,max(sal) maxsal from emp GROUP BY deptno;
# 将上面的数据当做一张临时表，再根据做高工资等于员工工资即可进行获取
select e.ename, t.* from emp e join (select deptno,max(sal) maxsal from emp GROUP BY deptno) t on e.sal = t.maxsal;
# 2. 哪些人的薪水在部门的平均薪水之上
# 先找到部门的平均薪水
select deptno, avg(sal) avgsal from emp GROUP BY deptno;
# 将上面的查询结果当做一个子表，然后在根据部门和工资进行查询
select e.ename, t.*, e.sal from emp e join (select deptno, avg(sal) avgsal from emp GROUP BY deptno) t on e.deptno = t.deptno and e.sal > t.avgsal;
# 3. 取得每个部门平均薪水的等级
# 同样先获取部门的平均薪水
select deptno, avg(sal) avgsal from emp group by deptno;
# 根据部门的平均薪水对比薪水等级
select t.*, s.grade from salgrade s join (select deptno, avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.LOSAL and s.HISAL;
# 4. 取得部门中（所有人的）平均的薪水等级
# 先找到部门所有人的薪水等级
select e.deptno,e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.LOSAL and s.hisal;
# 再根据部门进行分组得到部门之间的平均薪水等级
select e.deptno, avg(s.grade) from emp e join salgrade s on e.sal between s.LOSAL and s.hisal GROUP BY e.deptno;
# 5. 不准用组函数（Max），取得最高薪水（给出两种解决方案）
# 使用max方法查询
select max(sal) from emp;
# 第一种：排序
select sal from emp order by sal desc LIMIT 1;
# 第二种方式 自连接
# 这种方法时找不出来最大的值
select DISTINCT a.sal from emp a join emp b where a.sal < b.sal;
# 在用not in 联合查询即可
select sal from emp where sal not in(select DISTINCT a.sal from emp a join emp b where a.sal < b.sal);
# 6. 取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
#
select deptno, avg(sal) avgsal from emp GROUP BY deptno ORDER BY avgsal desc LIMIT 1;
select deptno,avg(sal) as avgsal from emp group by deptno having avg(sal)=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
# 7. 取得平均薪水最高的部门的部门名称
select e.deptno, avg(e.sal) avgsal, d.dname from emp e join dept d on e.deptno = d.DEPTNO group by e.deptno, d.dname ORDER BY avgsal desc LIMIT 1;
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname order by avgsal desc limit 1;
# 8. 求平均薪水的等级最低的部门的部门名称
# 先求出部门的最低低平均工资是哪个
select e.deptno, avg(e.sal) avgsal, d.dname from emp e join dept d on e.deptno = d.deptno group by deptno order by avgsal limit 1;
# 再根据工资计算其等级
select t.*, s.grade from salgrade s join (select e.deptno, avg(e.sal) avgsal, d.dname from emp e join dept d on e.deptno = d.deptno group by deptno order by avgsal limit 1) t on t.avgsal between s.losal and hisal;
# 9. 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
# 先找普通员工的最高薪水
select max(sal) from emp where empno not in (SELECT MGR from emp where mgr is not null);
select ename, sal from emp e where sal > (select max(sal) from emp where empno not in (SELECT MGR from emp where mgr is not null));
# 10. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc LIMIT 5;
select ename, sal from emp where empno not in (SELECT MGR from emp where mgr is not null) ORDER BY sal desc LIMIT 5;
# 11. 取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc LIMIT 5,5;
# 12. 取得最后入职的5名员工
select ename, hiredate FROM emp order by HIREDATE desc LIMIT 5;
# 13. 取得每个薪水等级有多少员工
# 取得员工的薪水等级
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal BETWEEN s.losal and hisal;
# 根据等级进行分组
select s.GRADE, count(s.grade) from emp e join salgrade s on e.sal BETWEEN s.losal and hisal GROUP BY s.grade;
# 14. 列出所有员工及领导的姓名
select e.ename, l.ename FROM emp e left join emp l on e.mgr = l.empno;
# 15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select e.empno, e.ename, l.ename, d.dname from emp e join emp l on e.mgr = l.empno join dept d on d.DEPTNO = e.DEPTNO where e.HIREDATE < l.HIREDATE;
# 16. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select e.*, d.* from emp e right join dept d on e.deptno = d.deptno;
# 17. 列出至少有5个员工的所有部门
# 先根据部门进行分组，计算部分员工数
select deptno, count(deptno) from emp GROUP BY deptno;
# 在上面的基础上在过滤条件
select deptno, count(deptno) c from emp GROUP BY deptno HAVING c>=5;
# 18. 列出薪资比"SMITH"多的所有员工信息
# 先查到smith的薪资
select sal from emp where ename = 'SMITH';
# 在寻找比SMITH薪资多的员工
select ename, sal from emp where sal > (select sal from emp where ename = 'SMITH');
# 19. 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
# 先找到姓名和所属部门
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';
# 计算部门的人数
select deptno, count(deptno) total FROM emp GROUP BY DEPTNO;
# 联合查询
select e.ename,d.dname,t.total from emp e join dept d on e.deptno = d.deptno join (select deptno, count(deptno) total FROM emp GROUP BY DEPTNO) t on t.deptno = d.deptno where job = 'CLERK';
-- ---------------
# 20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
# 先找到薪资大于1500的工作信息
select e.job from emp e where e.sal>1500 GROUP BY e.job;
select job, count(*) from emp group by job HAVING min(sal)>1500;
select ename, job from emp where job in (select DISTINCT e.job from emp e where e.sal>1500);
-- ---------------
# 21. 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
# 22. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
select avg(sal) from emp;
select e.ename,d.dname,l.ename,s.grade from emp e join dept d on e.deptno = d.deptno left join emp l on e.mgr = l.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);
# 23. 列出与"SCOTT"从事相同工作的所有员工及部门名称
select job from emp where ename = 'SCOTT';
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where job = (select job from emp where ename = 'SCOTT');
# 24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
# 先查出30部门的人员薪资
select distinct sal from emp where deptno = 30;
# 在根据这个薪资去查找不是30部门的人员信息
select ename ,sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
# 25. 列出薪金高于 在部门30工作 的所有员工的薪金 的员工姓名和薪金.部门名称
# 找出30部门的最高薪资
select sal from emp where deptno = 30 order by sal desc limit 1;
# 在查询部门不是30的且薪资高于30部门的最高薪资
select e.ename, e.sal, e.deptno, d.dname from emp e join dept d on e.deptno = d.deptno where sal > (select sal from emp where deptno = 30 order by sal desc limit 1) and e.deptno <> 30;
# 26. 列出在每个部门工作的员工数量,平均工资和平均服务期限
select deptno, count(*), avg(sal), avg(datediff(now(),hiredate)/365) from emp group by deptno;
# 27. 列出所有员工的姓名、部门名称和工资
select e.ename, d.dname, e.sal from emp e join dept d on e.deptno = d.deptno;
# 28. 列出所有部门的详细信息和人数
select d.*, count(e.deptno) from dept d left join emp e on d.deptno = e.deptno group by d.DEPTNO;
# 29. 列出各种工作的最低工资及从事此工作的雇员姓名
# 先找出各工作的最低工资
select job, min(sal) from emp group by job;
# 将上面的查询结果当做临时表再次进行查询
select e.ename, e.job, t.minsal from emp e join (select job, min(sal) minsal from emp group by job) t on t.minsal = e.sal and e.job = t.job;
# 30. 列出各个部门是MANAGER的最低薪金
select min(sal), job, deptno from emp where job = 'MANAGER' group by deptno;
# 31. 列出所有员工的年工资,按年薪从低到高排序
# IFNULL() 函数 有两个参数，如果第一参数为null,返回第二个参数值，如果第二各参数不为null,则返回第一个参数值
select ename, (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;
# 32. 求出员工领导的薪水超过3000的员工名称与领导名称
select e.ename, e.sal, l.ename, l.sal from emp e left join emp l on e.mgr = l.empno where l.sal > 3000;
DDL(数据定义语言)
创建表
create table 表名 (
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
...
);
删除表
drop table 表名;
MySQL数据类型
MySQL的数据类型有整数类型，浮点数类型，定点数类型，日期和时间类型，字符串类型，二进制类型等
整数类型
- int：4个字节
- bigint：8个字节
浮点数类型
- float：4字节，单精度，最多5位小数
- double：8字节，双精度，最多16位小数
定点数类型
- decimal：底层采用字符串形式存储数字
- 语法：decimal（m，d）
- decimal（3,2）：表示3个有效数字，两个小数（最多有效数字65个，小数位30个）
日期和时间类型
- year：1个字节，只存储年，格式YYYY
- date：3个字节，只存储年月日，格式：YYYY-MM-DD
- time：3个字节，只存储时分秒，格式HH:MM:SS / HHMMSS
- datetime：8个字节，存储年月日加时分秒，格式：YYYY-MM-DD HH:MM:SS
- timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS，或者格式为 YYYYMMDDHHMMSS
字符串类型
- char(m)
- 固定长度的字符串，范围是0~255字符，长度不足时会自动填充空格到所设长度
- varchar(m)
- 可变长。m的长度是0~16383个字符
- text
- tinytext 表示长度为 255字符的 TEXT 列。
- text 表示长度为 65535字符的 TEXT 列。
- mediumtext 表示长度为 16777215字符的 TEXT 列。
- longtext 表示长度为 4294967295 或 4GB 字符的 TEXT 列。
- enum
- 语法：<字段名> enum('值1','值2',...)
- 该字段插入值时，只能是指定的枚举值。
- set
- 语法：<字段名> set('值1','值2','值3',...) 注意：值不可重复。
- 该字段插入值时，只能是指定的值。
二进制类型
二进制大对象，可以存储图片、声音、视频等文件。
- blob：小的，最大长度65535个字节
- mediumblob：中等的，最大长度16777215个字节
- longblob：大的，最大长度4GB的字节
DML(数据操作语言)
DML主要包括insert,delete,update
insert
-
语法格式
insert into 表名(字段名1，字段名2，字段名3...) values (值1，值2，值3...) -
表名后面的字段名可以省略，但是values值的顺序和建表语句时的顺序必须一致
-
还可以一次插入多条记录
insert into t_stu(no,name,age) values(1,'jack',20),(2,'lucy',30);
delete
-
语法格式
# 删除表中的所有记录 delete from 表名; # 删除符合条件的记录 delete from 表名 where 条件; -
上除的删除方式可以通过事务回滚的方式重新恢复数据，效率较低
-
有一种效率较高的，删除之后不可以恢复，不支持事务，不可以回滚，这种删除叫做：表被截断
-
truncate不是DML语句
truncate table 表名;
update
-
语法格式
update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3 where 条件;
约束
创建表时，为了保证数据的有效性和完整性
约束通常包括
- 非空约束：not null
- 检查约束：check
- 唯一性约束：unique
- 主键约束：primary key
- 外键约束：foreign key
非空约束
-
建表时在字段后面添加 not null
-
在插入数据时此字段不允许为空
create table t_stu( id int, not null, name varchar(50) );
检查约束
-
建表时指定某个字段可以填入的范围
create table t_stu( id int, name varchar(50), age int, check (age > 18) );
唯一约束
-
指定某个字段中的值时唯一的，不可重复出现
-
唯一约束可以为null，如果不想让他为空，在建表时在给一个not null 非空约束
create table t_stu( id int, name varchar(50), phone int unique );
主键约束
-
主键：primary key
-
主键的字段称为主键字段
-
主键字段不能为空也不能重复，是一行记录在表中的唯一标识
-
如果一个表中没有主键，则这张表相当于无效表
-
一张表只能有一个主键
-
mysql为主键值提供了一种自增机制用来自动维护该字段
-
主键分类
- 根据字段数量进行分类
- 单一主键（一个字段作为主键） 推荐的
- 复合主键
- 根据业务逻辑分
- 自然主键（主键和任何业务数据无关，只是一个单纯的自然数据） 建议的
- 业务主键
create table t_vip( no int primary key auto_increment, name varchar(255) ); - 根据字段数量进行分类
外键约束
-
外键 foreign key，简称FK
-
添加了外键约束的字段中的数据必须来自其他字段，不能随便填。
-
假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。
-
外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。
-
a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表。
- 创建表时，先创建父表，再创建子表。
- 插入数据时，先插入父表，在插入子表。
- 删除数据时，先删除子表，再删除父表。
- 删除表时，先删除子表，再删除父表。
create table t_school( sno int primary key, sname varchar(255) ); create table t_student( no int primary key, name varchar(255), age int, sno int, constraint t_school_sno_fk foreign key(sno) references t_school(sno) );
三大范式
第一范式
- 任何一张表都应该有主键，每个字段是原子性的不能再分
第二范式
- 建立在第一范式的基础上，要求所有的非主键字段完全依赖主键，不能产生部分依赖
第三范式
- 建立在第二范式的基础上，非主键字段不能传递依赖于主键字段
如何进行表设计设计
- 多对多设计口诀
- 多对多三张表，关系表两外键
- eg：一张学生表有学生编号和学生姓名，一张教师表有教师编号和教师姓名，一张关系表将学生编号和教师编号添加为外键
- 多对多三张表，关系表两外键
- 一对多口诀
- 一对多两张表，多的表加外键
- eg：客户和订单，一个客户可以有多个订单，订单表中将就需要添加客户的id（或者是客户的唯一标识）为外键
- 一对多两张表，多的表加外键
- 一对一
- 第一种：主键共享
- 第二种：外键唯一
视图
-
只能将select语句当做视图
-
创建视图
create or replace view 视图名 as 别名 select语句; -
视图可以隐藏表的字段名
-
如果开发中有一条非常复杂的SQL，而这个SQL在多处使用，会给开发和维护带来成本。使用视图可以降低开发和维护的成本。
-
修改视图
alter view v_emp as select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno; -
删除视图
drop view if exists 视图名; -
对视图增删改（DML：insert delete update）可以影响到原表数据。
事务
-
事务是最小的工作单元，在数据库中表示的就是一件完整的事
-
在事务中，多条DML语句要么同时成功或者同时失败，不存在部分成功或者部分失败的现象（例如转账）
-
事务只针对DML语言有效，DML语句时修改表中数据的
事务的四大特性（ACID）
-
原子性（Atomicity）：事务的操作要么同时成功，要么同时失败
-
一致性（Consistency）：事务开始前和事务完成后数据是一致的。比如说和张三和李四的账户一共是10000块钱，那么无论中间如何操作，他们两个的值加起来一定是10000块钱
-
隔离性（Isolation）：当多个用户并发访问数据库时，比如同时操作有一张数据表时，数据库为每一个用户开启的事务，不能被其他事务所干扰，多个并发事务之间相互隔离
-
持久性（Durability）：一个事务一旦提交，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇见故障的情况下也不会丢失提交的事务操作。
事务的隔离级别
隔离级别从低到高依次是：读为提交（read uncommitted）<读提交（read committed）<可重复度（repeatable read）<串行化（serializable）
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 存在 | 存在 | 存在 |
| 读提交 | 不存在 | 存在 | 存在 |
| 可重复读 | 不存在 | 不存在 | 存在 |
| 串行化 | 不存在 | 不存在 | 不存在 |
不同的现象
不同的隔离级别会产生不同的现象，现象产生的严重性从高到低是：脏读>不可重复读>幻读
-
脏读：事务A可以读到事务B未提交的数据
- 现在有两个事务，一个A一个B，事务A能读到了事务B未提交（对表中的数据使用了DML语句但是事务还未提交）的数据，这种现象称为脏读
-
不可重复读：未提交的事务A能够读到事务B提交过后的数据
- 还是两个事务，一个A一个B，B事务使用了DML语句修改了表中的记录也提交了数据，A事务并未提交数据，但是能够读到事务B修改之后的数据
-
幻读：在执行前后两次查询的时候返回的结果不一致，可能是多了，也可能是少了
- 还是来个事务A和B，A先对某表进行查询数据可能得到的是3条记录，事务B对表中数据进行添加或者是删除，并提交事务。A事务未提交再去查询该数据就会变成4条或者2条，这样的现象就称为幻读现象
查看设置隔离级别
-
mysql默认的隔离级别：可重复读
# 查看当前会话的隔离级别 select @@transaction_isolation; # 查看全局的隔离级别： select @@gobal.transaction_isolation; # 设置当前会话的隔离级别 set session transaction isolation level 隔离级别名; # 设置全局隔离级别 set global transaction isolation level 隔离级别名;
可重复读的幻读问题
mysql的可重复读在尽可能的解决幻读问题（但是并没有完全解决这个问题），那么mysql是如何解决幻读问题的呢
- 针对快照读（普通的select语句）：通过MVCC（多版本并发控制）方式解决了幻读问题
- 快照读。顾名思义：在整个事务的处理过程中，执行相同的一个select语句时，每次都是读取的快照。
- 底层由 MVCC（多版本并发控制）实现，实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。
- 针对当前读（select... for update）：通过next-key-lock（间隙锁+记录锁的方式）解决了幻读问题
- 当前读，顾名思义：每一次都读取最新的数据。当前读包括：update、delete、insert、select...for update。这个很好理解，因为增删改的时候都要基于最新的数据进行增删改。
- 而select...for update原理是：对查询范围内的数据进行加锁，不允许其它事务对这个范围内的数据进行增删改。也就是说这个select语句范围内的数据是不允许并发的，只能排队执行，从而避免幻读问题。
select...for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作。
练习
-- 1. 用一条SQL语句，查询出每门课程都大于80分的学生姓名。 t_student
-- 先查询有课程分数不在80分以上的学生
select * from t_student;
select name from t_student where fenshu < 80;
-- 找到数据库中没有次名字的数据就是没门分数都大于80的学生
select name from t_student where name not in (select name from t_student where fenshu < 80);
-- 2. g_cardapply g_cardapplydetail
select * from g_cardapply;
select * from g_cardapplydetail;
-- 查询身份证号为440401430103082的申请日期
select gc.g_applydate from g_cardapply gc join g_cardapplydetail gcd on gc.g_applyno = gcd.g_applyno where gcd.g_idcard = 440401430103082;
-- 查询同一个身份证号码有两条以上记录的身份证号码及记录个数
-- 先查询身份证号的记录个数, 然后在用having查询
select g_idcard ,count(*) from g_cardapplydetail GROUP BY g_idcard having count(*) > 2;
-- 将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。
update g_cardapply a join g_cardapplydetail b on a.g_applyno = b.g_applyno set a.g_state = '07', b.g_state = '07' where b.g_idcard = 440401430103082;
-- 删除g_cardapplydetail表中所有姓李的记录
delete t1, t2 from g_cardapplydetail t1 join g_cardapply t2 on t1.g_applyno = t2.g_applyno where t1.g_name like '%李%';
-- 3. stuscore
select * from stuscore;
-- 统计如下：课程不及格[0~59]的多少个，良[60~80]多少个，优[81-100]多少个。
select
case
when score > 0 and score < 60 then '0~59'
when score > 60 and score < 81 then '60~80'
when score >= 81 and score <= 100 then '81~100'
else 'xx'
end
as source, count(*)
from stuscore
group by source;
-- 计算科科及格的人的平均成绩。
select score from stuscore where score < 60;
select name, avg(score) from stuscore where name not in (select score from stuscore where score < 60) group by name;
-- 4. WCMEmploy
select * from WCMEmploy;
-- 请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资。
select dname, avg(sal) from WCMEmploy where job = '钳工' group by dname;
-- 请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资高于2000的部门。
select dname, avg(sal) avgsal from WCMEmploy where job = '钳工' group by dname having avgsal > 2000;
-- 5. Employee 员工表 Company 公司信息表 Works 员工与公司的雇员关系 Manages 员工于直属领导
select * from Employee;
select * from Company;
select * from Works;
select * from Manages;
-- 找出所有居住地与工作的公司在同一城市的员工的姓名。
select distinct w.`person-name`, c.`city`, e.city from Works w join Company c on w.`company-name` = c.`company-name` join Employee e on e.city = c.city;
-- 找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名。
select sum(salary) from Works where `company-name` = 'Small Bank Corporation';
select `person-name`,salary from Works where salary > (select max(salary) from Works where `company-name` = 'Small Bank Corporation');
-- 找出平均年薪在10000美元以上的公司及其平均年薪。
select `company-name`, avg(salary) sal from Works group by `company-name` having sal > 10000;
-- 6. 客户表Client 订单表Order 客户订单表ClientOrder 图书表Book
select * from client;
select * from `order`;
select * from clientorder;
select * from book;
-- 请写出一条SQL语句，查询出每个客户的所有订单并按照地址排序，要求输出格式为：address client_name phone order_id
select c.address, c.`client_name`, c.phone, co.`order_id` from client c join clientorder co on co.`client_id` = c.`client_id` order by address;s
-- 请写出一条SQL语句，查询出每个客户订购的图书总价。要求输出格式为：client_name total_price
select
c.client_name, sum(b.price) total_price
from
client c
join
clientorder co
on
c.`client_id` = co.`client_id`
join
`order` o
on
co.`order_id` = o.`order_id`
join
book b
on
o.`book_id` = b.`book_id`
group by
c.`client_name`;
-- 如果要求每个订单可以包含多种图书，应该如何修改Order表的主键？为了保证每个订单只被一个客户拥有，应该在ClientOrder表上增加怎样的约束？
-- 7. student course sc teacher
select * from student;
select * from course;
select * from sc;
select * from teacher;
-- 查询平均成绩大于60分的学号和平均成绩。
select `s#` sid, avg(score) avgscore from sc group by sc.`s#` having avgscore > 60;
-- 查询所有学生学号、姓名、选课数、总成绩。
select sc.`s#` sid, s.sname, count(sc.`c#`), sum(sc.score) sumscore from sc join student s on s.s_id = sc.`s#` group by sc.`s#`, s.sname;
-- 查询姓“李”的老师的个数
select count(*) from teacher where tname like '李%';
-- 查询没学过“叶平”老师课的学号、姓名。
-- 查询叶平老师的编号
select `t#` from teacher where tname = '叶平';
-- 根据编号查询此老师所教的科目编号
select `c#` from course where `t#` = (select `t#` from teacher where tname = '叶平');
-- 查询选过叶萍老师课程的学生学号
select distinct `s#` from sc where `c#` in(select `c#` from course where `t#` = (select `t#` from teacher where tname = '叶平'));
-- 查询学生学号，姓名，不在上面的学号中
select s_id, sname from student where s_id not in (select distinct `s#` from sc where `c#` in(select `c#` from course where `t#` = (select `t#` from teacher where tname = '叶平')));
-- 8. 学生表：student 课程表：class 选课表：chosen_class
select * from student;
select * from class;
select * from chosen_class;
-- 没有选修课程编号为C1的学生姓名
select sname from student where s_id not in (select distinct s_id from chosen_class where c_id = 'C1');
-- 列出每门课程名称和平均成绩，并按照成绩排序
select c.c_name, avg(cc.grade) avggrade from chosen_class cc join class c on cc.c_id = c.c_id group by c.c_name order by avggrade;
-- 选了2门课以上的学生姓名。
select s.s_id, s.sname, count(s.s_id) from chosen_class cc join student s on s.s_id = cc.s_id GROUP BY s.s_id, s.sname having count(s.s_id) > 2;
以上所学均来自动力节点
标签：sal,--,学习,emp,MySQL,deptno,where,select From： https://www.cnblogs.com/Leybxin-AXB/p/18305535