三天速成——mysql数据库
文章目录
DQL高级(重点)
- 子查询(单行单列、多行单列、多行多列)
DQL和MySQL函数(理解)
单行函数
-
字符串函数
-
数学函数
-
日期函数
-
其它函数
分组(聚合)函数
MySQL事务(理解)
-
事务的概念
-
为什么需要事务
-
事务的操作
-
事务的提交方式
-
事务的操作
-
事务隔离级别
第一章DQL加强(重点)
1.1子查询
在一个DQL查询中嵌套一个DQL查询,所有的子查询都包含在父查询里面。所有的子查询必须定义在一对小括号中。
子查询可以出现的地方:where之后、having之后、from之后、from之前
子查询返回的结果集有三中:单(单行单列)、多(多行单列)、多(多行多列)
1.1.1单行单列子查询
场景:查询工资高于公司平均工资的所有员工
分析:公司平均工资我们不知道可以作为子查询嵌入到父查询的条件中
步骤:1确定父查询的表名称 emp
2确定父查询条件 where sal > 高于公司平均工资
3 确定列名称
select avg(sal) 公司平均工资
from emp;
select empno,ename,job,sal,deptno,hiredate
from emp
where sal >(select avg(sal) 公司平均工资 from emp);
1.1.2多行单列子查询
场景:查询工资大于任意一部门的最高工资的员工信息
分析:需要用到any关键字。该关键字通常定义在where后面用作子查询,子查询结果是一个多行单列的集合,只要父查询条件匹配子查询集合的任意的一行数据,就提取父查询的数据。
注意:每个部门最高工资我们无法得知,它作为子查询嵌套在父查询里面
步骤:1 确定父查询使用的表
2 确定父查询条件 where sal > any(任意一部门的最高工资)
3 编写子查询, 将子查询嵌入到父查询中
4 确定父查询的列
select empno,ename,sal,job,deptno,hiredate
from emp
where sal > any(
select max(sal)
from emp
group by deptno
);
场景:查询工资大于所有部门的平均工资的员工信息
分析:
需要用到all关键字,该关键字通常定义在where后面作为子查询,子查询的结果是一个多行单列的结果集,当父查询的条件匹配子查询结果集所有数据,就提取父查询的数据。
部门平均工资是多少我们无法得知,必须编写DQL语句进行查询,查询的结果作为父查询的条件。
步骤:
1 确定父查询的表emp
2 确定父查询条件 where sal > all(所有部门的平均工资)
3 编写子查询
4 将子查询嵌入到父查询
select empno,ename,sal,job,deptno
from emp
where sal > all(
select avg(sal)
from emp
group by deptno
);
场景:查询员工编号、员工名称、员工所在部门
步骤:
1使用多表联合查询确定表 emp dept
2确定条件 emp.deptno = dept.deptno
3确定列
select e.empno,e.ename,d.dname
from emp e inner join dept d on e.deptno = d.deptno;
-- 4查询员工编号、员工名称、员工所在部门 要求:使用子查询完成
-- 部门名称作为子查询嵌入在父查询的select语句中
select empno,ename,
(select dname from dept where dept.deptno = emp.deptno)部门名称
from emp ;
场景:查询部门平均工资高于公司平均工资的部门编号和该部门的员工平均工资
分析:公司平均工资我们不知道,可以作为子查询嵌入在父查询中
步骤:
1 确定父查询的表emp
2 编写父查询分组语句
select deptno,avg(sal)部门平均工资
from emp
group by deptno
having 部门平均工资 >(
select avg(sal)
from emp
);
1.1.3多行多列子查询
场景:查询平均工资高于2000的职位名称和职位平均工资
步骤:
1 确定表名称
2 确定分组条件,此时对“职位名称”进行分组,对“职位平均工资”进行汇总
3 编写having对聚合函数进行过滤,having 职位平均工资 > 2000
select job 职位名称, avg(sal)职位平均工资
from emp
group by 职位名称
having 职位平均工资 > 2000;
上面场景使用子查询完成
分析:子查询可以定义在from之后,作为一张伪表。查询条件使用where来代替having
select 职位名称,职位平均工资
from (
select job 职位名称, avg(sal)职位平均工资
from emp
group by 职位名称
)e
where 职位平均工资 > 2000;
小结:定义在from之后的子查询是一张伪表,返回结果多行多列
工作中尽量不要在having后面编写子查询,效率不高
场景:查询部门编号、部门名称、部门位置、部门人数、部门平均工资
select d.deptno,d.dname,d.loc,e.部门人数,e.部门平均工资
from dept d ,(
select deptno,count(1)部门人数, avg(sal)部门平均工资
from emp
group by deptno
)e
where d.deptno = e.deptno;
场景:查询出所有在销售部工作的员工编号,姓名,基本工资,奖金,职位,入职日期,部门最高和最低工资
-- 员工编号 姓名 基本工资 奖金 职位 入职日期 部门最高 最低工资
-- 7788 Tom 1211 10 Clerk 1988 1641 1010
-- 7789 Jim 1256 20 Clerk 1989 1641 1010
select e1.empno,e1.ename,e1.sal,e1.comm,e1.job,e1.hiredate,
e2.部门最高工资,e2.部门最低工资
from emp e1,(
select deptno, max(sal)部门最高工资,min(sal)部门最低工资
from emp
group by deptno
)e2
where e1.deptno = e2.deptno and e1.deptno = (
-- 子查詢查询出SALES的部门编号
select deptno
from dept
where dname='SALES'
);
SELECT e1.empno,e1.ename,e1.sal,e1.comm,e1.job,e1.hiredate,
e2.部门最低工资,e2.部门最高工资
from emp e1,(
SELECT deptno 部门名称, max(sal)部门最高工资,min(sal)部门最低工资
FROM emp
group by 部门名称
)e2
where e1.detpno = e2.deptno
子查询小结:
1 子查询出现的地方 where之后、from之前、from之后、having之后
2 子查询返回的结果:单行单列(where之后、having之后、from之前) 、多行单列( where之后的any和all)、多行多列(from之后)
from之前的子查询:伪列
from之后的子查询:伪表
3 工作中尽量不要在having后面编写子查询,而是使用伪表+where条件
问题:where和having区别?
having主要对分组和聚合函数查询的结果集进行过滤
where 对from结果集进行过滤,where不支持聚合函数过滤,因为where先执行group by后执行。
第二章 函数
完成某个功能的一系列步骤的集合
2.1 字符串函数
函数 | 用法 |
---|---|
CONCAT(S1,S2,…,Sn) | 连接S1,S2,…,Sn为一个字符串 |
CONCAT_WS(分隔符, S1,S2,…,Sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上分隔符 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(s,n) | 返回字符串s最左边的n个字符 |
RIGHT(s,n) | 返回字符串s最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(【BOTH 】s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1,s2 |
SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符 |
- 举例1:大小写控制函数
函数 | 结果 |
---|---|
LOWER(‘SQL Course’) | sql course |
UPPER(‘SQL Course’) | SQL COURSE |
这类函数改变字符的大小写。
- 举例2:字符控制函数
函数 | 结果 |
---|---|
CONCAT(‘Hello’,‘World’) | HelloWorld |
SUBSTR(‘HelloWorld’,1,5) | Hello |
LENGTH(‘HelloWorld’) | 10 |
INSTR(‘HelloWorld’,‘W’) | 6 |
LPAD(salary,10,’*’) | *****24000 |
RPAD(salary,10, ‘*’) | 24000***** |
TRIM(‘H’ FROM ‘HelloWorld’) | elloWorld |
REPLACE(‘abcd’,‘b’,‘m’) | amcd |
2.2 数值函数/数学函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根 |
POW(x,y) | 返回x的y次方 |
- 举例1:ROUND:四舍五入
ROUND(45.926, 2) --> 45.93
- 举例2:TRUNCATE:截断
TRUNCATE(45.926) --> 45
- 举例3:MOD:求余
MOD(1600, 300) --> 100
2.3 日期函数
函数 | 用法 |
---|---|
CURDATE() 或 CURRENT_DATE() | 返回当前日期 |
CURTIME() 或 CURRENT_TIME() | 返回当前时间 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期时间 |
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
WEEK(date) / WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFWEEK() | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
DAYNAME(date) | 返回星期:MONDAY,TUESDAY…SUNDAY |
MONTHNAME(date) | 返回月份:January,。。。。。 |
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2) | 返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔 |
DATE_ADD(datetime, INTERVAL expr type) | 返回与给定日期时间相差的INTERVAL(间隔)时间段的日期时间 |
DATE_FORMAT(datetime ,fmt) | 按照字符串fmt格式化日期datetime值 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
其中:
(1)DATE_ADD(datetime,INTERVAL expr type)
表达式类型:
参数类型 | 参数类型 |
---|---|
YEAR | YEAR_MONTH |
MONTH | DAY_HOUR |
DAY | DAY_MINUTE |
HOUR | DAY_SECOND |
MINUTE | HOUR_MINUTE |
SECOND | HOUR_SECOND |
MINUTE_SECOND |
举例:
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); #需要单引号
(2)DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)
格式符 | 说明 | 格式符 | 说明 |
---|---|---|---|
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
%M | 月名表示月份(January,…) | %m | 两位数字表示月份(01,02,03。。。) |
%b | 缩写的月名(Jan.,Feb.,…) | %c | 数字表示月份(1,2,3,…) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,…) | %d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数(1,2,3,4,5…) | ||
%H | 两位数字表示小数,24小时制(01,02…) | %h和%I | 两位数字表示小时,12小时制(01,02…) |
%k | 数字形式的小时,24小时制(1,2,3) | %l | 数字形式表示小时,12小时制(1,2,3,4…) |
%i | 两位数字表示分钟(00,01,02) | %S和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
%j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 | ||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
2.4 流程函数
函数 | 用法 |
---|---|
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
- 举例1:
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;
- 举例2:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "实发工资"
FROM employees;
- 举例3:
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
2.5 其他函数
函数 | 用法 |
---|---|
database() | 返回当前数据库名 |
version() | 返回当前数据库版本 |
user() | 返回当前登录用户名 |
password(str) | 返回字符串str的加密版本,41位长的字符串 |
md5(str) | 返回字符串str的md5值,也是一种加密方式 |
2.6分组(聚合)函数
可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
COUNT(*)count(1返回表中记录总数,适用于任意数据类型。
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
•COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
-
问题:用count(*),count(1)谁好呢?
其实,对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*)直接读行数,效率很低,因为innodb真的要去数一遍。
第三章MySQL事务(TCL)
TCL Transaction Control Language
3.1概念
事务就是完成某个独立的行为所需的一系列步骤的集合。
某个独立的行为:转账(张三向李四转账)
事务管理的行为要么全部执行成功,要么全部执行失败
3.2为什么需要事务
场景:定义一个账户表,每个账户1000块钱,张三向李四转账500,中途停电了。
步骤:1 创建account表
2 插入数据
3 执行update转账
-- 1定义一个tb_account 表
create table tb_account(
id int(11) auto_increment,
account_name varchar(30) not null,
account_balance double default 1000, -- 账户余额
primary key(id)
);
-- 2插入数据
insert into tb_account(account_name)values('ZS'),('LS');
-- 3转账
-- 张三账户-500
update tb_account set account_balance = account_balance-500 where id = 1;
停电了
-- 李四账户+500
update tb_account set account_balance = account_balance+500 where id = 2;
-- 问题出现了:张三的钱减少了,但是李四的钱没有增加
-- 如何解决这个问题,使用事务来管理转账
3.3事务的操作
MySQL使用三个命令来管理事务,commit和rollback不会同时执行
-- 开启事务
start transaction;
-- 没问题 提交事务
commit;
-- 有问题 回滚事务
rollback;
3.4 事务的提交方式
自动提交和手动提交
-- 通过下面的命令可以查看事务提交方式 1 自动提交 0 手动提交 MySQL默认自动提交
select @@autocommit;
update tb_account set account_balance = 1000;
commit;
3.5 操作事务
-- 张三向李四转账 停电了需要回滚
-- 步骤:1 开启事务 2 执行Update语句 3 Update语句没有问题就提交事务,有问题回滚事务
-- 注意:回滚事务到事务开启之前的状态,一旦执行start transaction;命令开启事务自动提交将会失效。
start transaction;
update tb_account set account_balance = account_balance-500 where id = 1;
-- 转账过程中发生了意外,部分成功部分失败
停电了
update tb_account set account_balance = account_balance+500 where id = 2;
rollback;
-- 事务只用于管理DML操作
-- 张三向李四转账 正常情况
start transaction;
update tb_account set account_balance = account_balance-500 where id = 1;
update tb_account set account_balance = account_balance+500 where id = 2;
-- 没问题:提交事务
commit;
3.6事务的四大特征
原子性:转账全部成功or全部失败,转账使用事务管理就是一个整体不可再分
一致性:转账之前和转账之后总金额不变
隔离性:多个事务之间进行的操作彼此保持隔离
持久性:事务执行成功之后数据会持久化到磁盘中数据文件中(转账成功李四金额多了500)
事务四大特征简称:ACID
Atomicity(原子性) Consistency(一致性) Isolation(隔离性) Durability(持久性)
3.7事务隔离级别
有4个隔离级别:
-- 读未提交
read uncommitted
-- 读已提交
read committed
-- 可重复读
repeatable read
-- 串行化
serializable
如何查看MySQL数据库隔离级别?
select @@tx_isolation;
-- MySQL数据库默认事务隔离级别是repeatable read,Oracle数据库默认事务隔离级别是read committed。
-- 隔离级别越高越安全,但是性能也会越差。所以工作中我们不用第一个隔离级别(不安全),也不用第四个隔离级别(效率低),通常只会在read committed和repeatable read之间进行切换
晚上作业:
-- 1、查找部门30中员工的详细信息。
-- 2、找出从事职员工作的员工的编号、姓名、部门号。
-- 3、检索出奖金多于基本工资的员工信息。
-- 4、检索出奖金多于基本工资60%的员工信息。
-- 5、找出姓名中包含A的员工信息。
-- 6、找出姓名以A、B、S开始的员工信息。
-- 7、找到名字长度为7个字符的员工信息。
-- 8、名字中不包含R字符的员工信息。
-- 9、返回员工的详细信息并按姓名升序排序。
-- 10、返回员工的信息并按姓名降序,工资升序排列。
-- 11、计算员工的日薪(按30天)。
-- 12、找出获得奖金的员工的工作。
-- 13、找出奖金少于100或者没有获得奖金的员工的信息。
-- 14、找出10部门的经理、20部门的职员 的员工信息。
-- 15、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
-- 注意:员工如果没有特殊指明,指的就是所有人。
-- 分组查询
-- 1、返回部门号及其本部门的最低工资。
-- 2、查询员工姓名和年薪,并且按年薪降序排序。
-- 3、返回员工工作及其从事此工作的最低工资。
-- 子查询
-- 4、查找和SCOTT从事相同工作的员工信息
-- 5、工资水平多于JAMES的员工信息。
-- 6、返回工资大于平均工资的员工信息。
-- 7、返回销售部(SALES)所有员工的姓名。
-- 8、返回工资高于30部门所有员工工资水平的员工信息。
-- 多表连接
-- 9、返回拥有员工的部门名、部门号。
-- 显示内连接
-- 省略inner
-- 隐式内连接
-- 10、返回员工的姓名、所在部门名及其工资。
-- 11、返回从事职员工作的员工姓名和所在部门名称。
-- 12、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
-- 13、返回员工(职员或者销售员)和所属经理的姓名。
-- 14、返回员工(职员或者销售员)的入职日期早于其经理入职日期的员工及其经理姓名。
-- 15、返回最高工资和最低工资的职员信息
-- 16、返回工资处于第四级别的员工的姓名和工资。