MySQL细节笔记
一、DB、DBMS、SQL三者关系
-
SQL:
结构化查询语句,是一门标准通用的的语言。标准的SQL适合所有的数据库产品
-
DBMS:
DataBase Management System(数据库管理系统,常见的有MYSQL Oracle DB2 Sybase SqlServer…)
-
DB:
DataBase,数据库,数据库实际上在硬盘上以文件的形式存在
二、SQL语句分类
- DQL(数据査询语言):查询语句,凡是select语句都是DQL。
- DML(数据操作语言):insert delete update,对表当中的数据进行增删改。
- DDL(数据定义语言):create drop alter,对表结构的增删改。
- TCL(事务控制语言):commi提交事务,rollLback回滚事务
- DCL(数据控制语言):grant授权、 revoke撤销权限等
三、DQL语句
1. 模糊查询
like关键字,%代表任意多个,_代表任意一个,区分大小写
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gzfg2jDC-1678280545574)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\image-20201104205739748.png)]
‘字段+%'代表以该字段名开头的任意多个字符的属行名
’%+字段‘代表以该字段名结尾的任意多个字符的属性名
数据排序
select ename,sal from emp order by sal;默认升序
select en
ame,sal from emp order by sal asc ;指定升序
select ename,sal from emp order by sal desc;指定降序
示例一:
按照工资降序排列,当工资相同时再按照名字升序排列
select ename,sal from emp order by sal desc, ename asc;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LQtQOktt-1678280545575)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\image-20201105145757404.png)]
2.分组函数
-
count
select count(ename) from emp;
count(*):不是统计某个字段中的数据个数,而是 统计总记录条数
count(comm):表示统计cwomm子段中不为null的数据总数量
-
sum
select sum(sal) from emp;
-
avg
select avg(sal) from emp;
-
max
select max(sal) from emp;
-
min
select min(sal) from emp;
-
分组函数自动忽略null,当null值参与运算结果一定是null,分组函数自动忽略null
3.分组查询
-
group by:按照某个字段或者某些字段分组
-
Having:having是对分组之后的数据进行再次过滤
-
案例:
-
找出每个岗位的最高薪资
select max(sal) from emp group by job;
规则:当一个语句中有group by的话,select后面只能跟分组函数和参与分组的字段
-
查询不同部门,不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job;
-
查询不同部门平均工资大于2000部门
select deptno,avg(sal) from emp group by deptno having by avg(sal)>2000;
-
having是group by 的搭档
-
4.连接查询
-
内连接值非等值连接查询
查询不同员工工资等级,要求显示员工姓名,工资,工资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
-
自连接
查询每个员工的领导,显示员工姓名及对应领导姓名
select e.ename,e.empno,m.ename from emp e join emp m on e.empno = m.mgr;
-
外连接
内连接:
假设A表和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。AB两张表没有主副之分,两张表是平等的。
外连接:
假设A表和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张是副表,主要查询主表中的数据,捎带查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接分类:
左外连接(左连接):表示左边这张表是主表
右外连接(右连接):表示右边这张表是主表
示例:查询每个员工上级领导,要求显示每一个
select e.ename,m.ename from emp m left join emp e on e.empno=m.mgr;
示例:查询没有员工的部门名称
select d.dname from dept d left join emp e on e.deptno=d.deptno where e.empno is null;
-
三表连接查询
示例:查询员工部门名称和工资等级
select e.ename,d.dname,s.grade
from emp e join dept d join salgrade s
on e.deptno=d.deptno where sal between and s.losal and s.hisal;
优化:
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
sal between s.losal and s.hisal;
```sql
示例:找出每一个员工的部门名称,工资等级,以及上级领导
select e.ename,d.dname,s.grade,e1.ename
from emp e join dept d
on e.deptno=d.deptno
join salgrade s
on sal between s.losal and s.hisal
left join emp e1
on e.mgr=e1.empno;
5. 子查询
-
子查询出现位置
select
…(select)
from
…(select)
where
…(select)
-
where子句中使用子查询
-
from子句中使用子查询
示例一:查询每个部门平均工资的薪资等级
select s.grade,a.avsal from salgrade s join (select deptno,avg(sal) as avsal from emp group by deptno) a on a.avsal between s.hosal and s.hisal;
select deptno,avg(sal) as avsl from emp group by deptno
deptno | avg(sal)
20 | 2175.000000
30 | 1566.666667
10 | 2916.666667将查询出的部门平均薪资作为新表,与工资等级表建立连接查询
| grade | avsal
| 4 | 2175.000000
| 3 | 1566.666667
| 4 | 2916.666667
示例二:查询每个部门员工薪水等级的平均值
先查询每个员工的薪水等级表:
select e.deptno,s.grade from salgrade s join emp e
on e.sal between s.losal and s.hisal;
| ename | deptno | grade |
| SMITH | 20 | 1 |
| ALLEN | 30 | 3 |
| WARD | 30 | 2 |
| JONES | 20 | 4 |
| MARTIN | 30 | 2 |
| BLAKE | 30 | 4 |
| CLARK | 10 | 4 |
| SCOTT | 20 | 4 |
| KING | 10 | 5 |
| TURNER | 30 | 3 |
| ADAMS | 20 | 1 |
| JAMES | 30 | 1 |
| FORD | 20 | 4 |
| MILLER | 10 | 2 |
将查询出结果当做临时表 g (错误写法,降低了效率)
select e.deptno,avg(g.grade) from emp e join
(select e.deptno,s.grade from emp e join salgrade s on s.sal
between s.losal and s.hisal) group by e.deptno
| deptno | avg(g.grade) |
| 20 | 2.8571 |
| 30 | 2.8571 |
| 10 | 2.8571 |
正确写法
select e.deptno,avg(s.grade) from emp e join salgrade s
e.sal between s.losal and s.hisal group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+-------------+
- select 后面使用子查询
6.union的用法
案例:找出工作岗位是SALESMAN和MANAGER的员工?
select ename,job from emp where job in ('SALESMAN','MANAGER');
select ename,job from emp where job='SALESMAN' or job='MANAGER'
select ename,job from emp where job='SALESMAN'
union//使用union将两张不想关的表中的数据拼接一起显示
select ename,job from emp where job='MANAGER';
7.分页查询
limit
语法机制:
limit startIndex, length 第一个数字代表起始下标,第二个代表长度, 而不是下标
limit是mysql特有,其他数据库没有
limit取结果中的部分数据
limit是sql语句最后执行的一个环节
示例:取出工资前五名
select ename,sal from emp order by desc limit 0,5;
或者
select ename sal from emp order by desc limit 5;
8. SQL执行顺序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BvYsAwgs-1678280545576)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\image-20201107112234848.png)]
四、DML语句
1.创建表
关于MysQL当中字段的数据类型?以下只说常见的
int 整数型(java中的int)
bigint 长整型(ava中的long)
float 浮点型ava中的f1oat double)char定长字符串(string)
varchar 可变长字符串(stringBuffer/stringBuilder)
date 日期类型(对应Java中的java.sql.Date类型)
BLOB 二进制大对象(存储图片、视频等流媒体信息)Binary Large oBject(对应java中的object)
CLOB字符大对象(存储较大文本,比如,可以存储4c的字符串。) character Large 0Bject(对应java中的object)
2.表的复制
语法格式:
create table 表名 as select语句
例:
create table emp1 as select * from data.emp;//将表emp中的内容复制到emp1中
将查询结果插入一张表中
insert into dept1 select * from dept;//将dept的全部内容插入表的dept1中
3.修改数据
语法格式:
update 表名 set 字段名1 = 值1,字段名2 = 值2. . . where 条件;
注意:没有条件整张表全部更新
4.删除数据
语法格式:
delete from 表名 where 条件;
注意:没有条件全部删除
截断表:
truncate table 表名;//表被截断,不可回滚,永久丢失
5. 插入语句
语法格式:
insert into 表名(字段1,字段2…) values(字段1,字段2…);//往指定字段插入数据
或
insert into 表名 values();//每个字段必须插入数据
CRUD:create、Retrieve、Update、Delete
6.表字段修改
alter table table_name add col_name datatype;
alter table table_name drop col_name;
alter table table_name modify col_name datatype;
五、约束
1.常见约束
非空约束:not null
唯一约束:unique
主键约束:primary key
外键约束:foreign key
检查约束:check
2. 唯一约束
唯一约束修饰字段不能重复,可以为null
create table t_user (
id int,
usercode varchar (255) ,
username varchar (255),
unique (usercode , username)) ;
create table t_user (
id int,
usercode varchar (255) unique,
username varchar (255)unique);
3.主键约束
主键相关术语:
主键约束:primary key
主键字段:id字段添加primary key以后,id叫做主键字段
主键值:id字段的每一个值都是主键值
主键作用:
表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键
主键作用:主键值是这行记录在这张表当中的唯一标识
主健分类:
根据主键字段的字段数量分类:
单一主键:(推荐的、常用的)
复合主键:(多个字段联合起来添加一个主键约束,复合主键不建议使用,因为复合主键违背三范式)
根据主键性质划分:
自然主键:主键最好就是一个和业务没有任何关系的自然数(推荐的)
业务主键:主键值和系统的业务挂钩,例如拿身份证做主键,拿银行卡卡号做主键(不推荐)
一张表的主键约束只能有一个
主键自增:
create table t_user(
id int primary key auto_increment,//自增关键字
username varchar(255) not null
);
4.外键约束
顺序要求:
删除数据的时候,先删除子表,再删除父表。
添加数据的时候,先添加父表,在添加子表。
创建表的时候,先创建父表,再创建子表。
删除表的时候,先删除子表,在删除父表。
外键可以为NULL
创建表使用外键约束:
create table t_class( //先创建父表
cno int,
cname varchar(255),
primary key(cno)
)
create table t_student( //子表
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)//外键引用
//引用的外键应具有唯一性,不一定是主键,至少具有unique约束
)
5.存储引擎
1.MyISAM
这种存储引擎不支持事务
采用三个文件组织一张表
xxx.frm存储格式的文件
xxx.MYD存储表中数据的文件
xxx.MYI存储索引的文件
优点:可被压缩,节省空间,并且可以转换为只读表,提高检索效率
缺点:不支持事务
2.InnoDB
MYSQL的缺省引擎
具有以下特征:
每个InnoDB表在数据库目录中以.frm格式文件表示
使用SAVEPOINT、COMMIT、ROLLBACK支持事务处理
在MYSQL服务器崩溃后提供自动恢复
InnoDB表空间在tablespace被用于存储标的内容
3.MEMORY
缺点:不支持事务,数据容易丢失。因为所有数据和索引都是存储在内存中
优点:查询速度最快
六、事务
1.什么是事务
1. 一个事务是一个完整的业务逻辑单元,不可再分。
比如:银行转账,从A账户转B账户10000需要执行两条update语句
update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_ace set balance = balance + 10000 where actno =‘act-002’;
以上两条DML语句必须同时成功,或者同时失败,不允许一条成功,一条失败
要想保证以上两条DML语句同时成功或失败,那么就需要使用数据库的"事务机制"。
- 和事物相关的只有DML语句(insert、delete、update)
这三个语句都是和数据库当中的数据相关
事务的存在是为了保证数据的完整性、安全性
2.事务原理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-atPQnw1G-1678280545577)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\image-20201108212202292.png)]
3.事务特性
事务四大特性:ACID
A:原子性:事务是最小的工作单元,不可再分。
C:一致性:事务必须保证多条DMG语句同时成功或者同时失败。
l:隔离性:事务A与事务B之间具有隔离。
D:持久性:持久性说的是最终数据必须持久化到硬盘文件中,事务才算成功的结束。
事务隔离性存在隔离级别,理论上隔离级别包括4个
第一级别:读未提交( read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据读未提交存在脏读( Dirty Read)现 象:读到了脏的数据。
第二级别:读已提交( read committed)
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了:脏读现象没有了读已提交存在的问题是:不可重复读
第三级别:可重复读( repeatable read)
这种隔离级别解决了:不可重复读问题
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读
解决了所有问题效率低,需要事务排队
7.SQL例题
-
查询每个部门最高工资的员工姓名
select e.ename,e.deptno,d.maxsal from (select deptno,max(sal) as maxsal from emp group by deptno) as d join emp e on e.deptno=d.deptno and e.sal=d.maxsal;
-
那些人的薪水在部门平均薪水之上
//查询部门平均薪水 select deptno,avg(sal) as avgsal from emp group by deptno; select e.ename,e.sal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) as d on e.deptno=d.deptno and e.sal>d.avgsal; +-------+---------+ | ename | sal | +-------+---------+ | ALLEN | 1600.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+
-
查询平均的薪水等级
//正确写法
select e.deptno,avg(s.grade) from emp e join salgrade s
s.sal between s.hosal and s.hisal
group by deptno;
//错误写法
select t.deptno,avg(t.grade) from
(select e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal)
as t group by deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+--------------+
-
查询部门平均薪水的最大的部门编号
方法一: select avg(sal) as avgsal from emp order by avgsal desc limit 1; 方法二: select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) as t; 方法三: select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from emp (select avg(sal) from emp group by deptno))
-
取得平均薪水最高的部门的部门名称
select d.dname,avg(e.sal) from emp e join dept d
on d.deptno=e.deptno group by d.dname order by avg(e.sal) 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; RESEARCH 2175 SALES 1566.666667 ACCOUNTING 2916.666667 //将上述查询结果作t表,连接查询 select t.*,s.grade from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) as t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade=3;
-
取得比普通员工最高薪水还要高的领导人姓名
//查询普通员工
select distinct mgr from emp where mgr is not null;
//查询普通员工的最高薪水
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
select empno,ename,sal from emp where
sal>select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
-
取的薪水最高的前五名
select ename,sal from emp order by sal desc limit 0,5;
-
取的薪水最高的第六到第十名
select ename,sal from emp order by sal desc limit 5,5;
-
取的最后入职五名员工姓名
select hiredate,ename from emp order by hiredate desc limit 5;
-
取的每个薪水等级的员工人数
select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
-
取的所有员工及领导姓名
select e.ename as '员工',d.dname as '领导' from emp e left join emp d on e.mgr=d.empno; | 员工 | 领导 | | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK |
-
取的员工比直接上级入职日期早的员工和领导姓名及入职日期
select e.ename as '员工',e.hiredate,l.ename as '领导',l.hiredate,d.dname from emp e join emp l on e.mgr=l.empno and e.hiredate<l.hiredate join dept d on e.deptno = d.deptno; +--------+------------+--------+------------+------------+ | 员工 | hiredate | 领导 | hiredate | dname | +--------+------------+--------+------------+------------+ | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES | | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH | | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES | | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING | | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH | +--------+------------+--------+------------+------------+
-
列出部门名称和部门所属员工信息,同时列数哪些没有员工的部门
select e.ename,d.dname from dept d left join emp e
on e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
| NULL | OPERATIONS |
+--------+------------+
-
列出至少有5个员工的所有部门
select d.dname,count(*) from emp e join dept d on e.deptno=d.deptno group by d.dname having count(*)>=5; | dname | count(*) | | RESEARCH | 5 | | SALES | 6 |
-
列出薪金比 ”SMITH“ 多的员工信息
select sal from emp where (select sal from emp where ename='smith');
| sal |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
-
取的工作岗位为clerk的员工姓名,部门名称及部门所在人数
//难点:部门所在人数 //查出每个部门员工人数 select deptno,count(*) from emp group by deptno; //查出工作岗位为clerk的员工姓名及所在部门名称 select e.ename,e.job,d.dname frome emp e join dept d on e.deptno=d.deptno where e.job='clerk'; select t1.*,t2.deptcount from (select deptno,count(*) as deptcount from emp group by deptno) as t2 join (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where job='clerk') as t1 on t1.deptno=t2.deptno;
-
列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal)>1500;
-
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员工资等级
//正确说法 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); //错误写法 select e.sal,e.ename,d.dname,l.ename,s.grade from emp e join dept d join salgrade s join emp l on e.mgr=l.empno and e.deptno=d.deptno and e.sal>(select avg(sal) from emp) and e.sal between s.losal and s.hisal order by e.sal desc;
-
列出与’scott’从事相同工作的所有员工及部门名称
select job from emp where ename='SCOTT' select e.ename,e.job,d.dname from emp e join dept d on e.deptno=d.deptno where e.job=(select job from emp where ename='SCOTT') and e.ename<>'SCOTT';
-
列出薪金等于等于部门30中员工的薪金的其他员工的姓名和薪金
select sal,ename from emp where sal in(select sal from emp where deptno=30) and deptno not in(30);
-
列出每个部门工作员工的员工数量,平均工资和平均服务期限没有员工的部门,部门人数是0
select d.*,count(e.ename),ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;
-
查询部门全部信息,包括人数
select d.*,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;
-
列出各种工作岗位的最低薪资,及从事此工作岗位的雇员姓名
select e.ename,e.job,e.sal from emp e join (select min(sal) as minsal,job from emp group by job) as t on t.job=e.job and e.sal in(t.minsal);
-