MySQL目录
DBMS,SQL,DB三者的关系
DBMS:数据库管理系统
SQL:用于访问和处理数据库的标准的计算机语言
DB:数据库
三者的关系:DBMS数据库管理系统执行SQL语句,来操作DB数据库当中的数据的
关于in与or的建议
条件查询 in(a,b,c,…)
可以替代or,提高检索效率
例:
select id,name,age from student where name = '张三' or name = '李四'
替换为:
select id,name,age from student where name in ('张三' , '李四')
模糊查询like
%代表任意多个字符
_代表任意1个字符
例1:查询姓张的同学
select id,name,age from student where name like '张%'
例2:查询姓李且只有两个字的同学
select id,name,age from student where name like '李_'
ps:CSDN遵循Markdown语法,不能打出下划线,所以需要转义字符\,如\_
排序 order by
asc 升序(默认)
desc 降序
排序 order by sal 薪资 asc/desc ;
例:按薪水为公司的员工排序降序
select name,age,sal from emp order by sal desc
SQL完整的语句系统的执行顺序(重要)
select
* 5
from
tableName 1
where
条件(join) 2
group by
... 3
having
... 4
order by
... 6
limit
... 7
分组函数
分组函数:多行处理函数
函数 | 说明 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
注意:
- 分组函数一共5个
- 输入多行,输出结果总是1行
- 分组函数自动忽略NULL
- NULL参与运算时,结果为NULL,解决办法是:ifnull(comm,0)
- 分组函数不可直接使用在where子句中,因为分组函数是在group by执行之后才会执行
- count(*) 统计总记录的条数,count(字段名) 统计某个字段不为NULL的数量
聚合函数 group by
group by :按照某个字段或者某些字段进行分组
having :对分组之后的数据进行再次过滤
例1:按公司员工的工作岗位进行分组
select name,job from emp group by job;
例2:找出每个部门的最高薪资,要求显示薪资大于2900的数据
select max(sal),deptno from emp group by deptno having max(sal)>2900; 效率低
select max(sal),deptno from emp where sal>2900 group by deptno;
效率高,建议能够使用where过滤的尽量使用能够在分组之前过滤的,就先进行where过滤
例3:找出每个部门的平均薪资,要求显示薪资>2000的数据
步骤1.找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
步骤2.要求显示薪资>2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
去重 distinct
select distinct job from emp;
distinct 只能出现在所有字段的最前边,表示字段的联合去重
连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的数据
一般一个业务都会对应多张表,比如:学生和班级,起码两张表
连接查询的分类
根据语法出现的年代来划分
- SQL92(一些老的DBA可能还在使用这种语法。DBA:数据库管理员)
- SQL99(比较新的语法)
根据表的连接方式来划分
内连接:
- 等值连接
- 非等值连接 between and
- 自连接
外连接
- 左外连接(左连接)
- 右外连接(右连接)
全连接(很少用)
注:在表的连接查询方面有一种现象被称为:笛卡尔积现象
在数据库中,两张表连接查询默认显示的结果条数是两张表记录条数的乘积
案例:找出每一个员工的部门名称,要求显示员工名和部门名
select ename,dname from emp,dept;
如何避免笛卡尔积现象?
避免了笛卡尔积现象,不会减少记录的匹配次数,只不过显示的是有效记录
等值连接
select //SQL92 旧语法
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
**内连接之等值连接:**
特点:条件是等量关系
select //SQL99 新语法,常用的
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
语法:
...
A
(inner) join //inner 可读性更强,但可以省略
B
on
连接条件
where
...
SQL99语法结构更清晰,表的连接条件和后来的where条件分离了
内连接之非等值连接:
特点:连接条件中的关系是非等量的关系
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and hisal;
内连接之自连接
特点:一张表看做两张表,自己连接自己
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名
select
a.ename,b.ename
from
emp a
inner join
emp b
on
a.mgr = b.empno;
外连接
内连接:
假设A表和B表进行连接,使用内连接,凡是A表和B表能够匹配上的记录查询出来
AB两张表没有主副之分,两张表是平等的
外连接:
假设A表和B表进行连接,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的
数据,捎带着查询副表,当副表的数据没有和主表数据匹配上,副表自动模拟出NULL与之匹配
外连接的分类
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示右边的这张表是主表
案例:找出每个员工的上级领导
select
a.ename,b.ename
from
emp a
left (outer) join //left 左边是主表 outer可以省略
emp b
on
a.mgr = b.empno;
在实际开发中,大部分是外连接
外连接最重要的特点:主表的数据无条件的全部查询出来
案例:找出哪个部门没有员工?
select
d.deptno,d.dname
from
emp e
right outer join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
三张表连接查询
案例:找出每一个员工的部门名称以及工资等级
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
e.ename '员工',d.dname '部门',s.grade '工资等级',em.ename '领导'
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and hisal
left join
emp em
on
e.mgr = em.empno;
子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询
select
..(select) 在select中嵌套
from
..(select) 在from中嵌套
where
..(select) 在where中嵌套
- 在where子句中使用子查询
案例:找出高于平均薪资的员工信息 select * from emp where sal > (select avg(sal) from emp);
- from后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级
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 hisal;
案例:找出每个部门平均的薪水等级
select
e.ename,e.sal,e.deptno,s.grade,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno
- 在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名和部门名
select
emp.ename,dept.dname
from
emp
join
dept
on
dept.deptno = emp.deptno;
将查询结果集相加 union
案例:找出工作岗位是SALESMAN和MANAGER的员工?
select
ename,job
from
emp
where
// job = 'salesman' or job = 'manager';
// or改成in
job in ('salesman','manager');
方法二:
select ename,job from emp where job = 'salesman';
union
select ename,job from emp where job = 'manager';
limit (重点,分页查询)
特点:mysql特有的,其他数据库没有。
作用:limit取结果集中的部分数据
语法:limit startIndex,length
startIndex 表示起始位置,length 表示取几个
案例:取出工资前5名的员工
select
ename,sal
from
emp
order by
sal desc
limit
0,5;
案例:找出工资排名在第4到第9名的员工
select
ename,sal
from
emp
order by
sal desc
limit
3,6;
** 通用的标准分页sql **
每页显示3条记录
第1页:0,3
第2页:3,3
第3页:6,3
每页显示pageSize条记录(如上述的pageSize=3)
pageNo表示第几页,如上述的第1页、第2页、第3页,pageNO=1,2,3,....
公式:第pageNo页:(pageNo-1)*pageSize,pageSize
创建表 create
create table 表名(
字段名1 数据类型 约束,
字段名2 数据类型 约束,
字段名3 数据类型 约束,
...
);
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
主键值自增: auto_increment 从1开始递增
插入表 insert
insert into
表名(字段名1,字段名2,字段名3,....)
values
(值1,值2,值3,...)
insert into
t_student(no,name,sex,classno,birth)
values
(1,'zhangsan','1','gaosan1ban','1999-07-06');
复制一张表
create table emp1 as select * from emp;
创建一张表emp1,将emp表的查询结果放到emp1中
修改数据 update
update 表名 set 字段名1=值1,字段名2=值2,... where 条件;
删除数据 delete
delete from 表名 where 条件;
删除大表 truncate table emp; //表被截断,不可回滚,永久丢失
关于MySQL从删除到跑路的方法 : )
1.删除数据库删除后可能会遗留日志,一些数据还是可以通过日志恢复的,所以索性把日志也一起删了吧
drop database databasename
purge binary logs to '日志名字';
2.直接删除mysql的服务和数据
find / -name mysql
3.删除找到的关于mysql的一切
rm -rf /var/lib/mysql
rm -rf /var/lib/mysql
rm -rf /usr/lib64/mysql
rm -rf /etc/my.cnf
(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)
上面仅仅是个玩笑,但是多学习一点总归是好的
(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)
约束
- 非空约束 not null :约束的字段不能为NULL
- 唯一数据 unique : 约束的字段不能重复
- 主键约束 primary key : 约束的字段既不能为NULL,也不能重复
- 外键约束 foreign key : oreign key(classno) references t_class(cno)
classno的外键引用t_class表中的cno字段 - 检查约束 check : Oracle有,mysql没有
存储引擎 engines
查看当前mysql支持的存储引擎
show engines \G
-
MyISAM: 读 咪塞姆
不支持事务,mysql最常用的存储引擎,但不是默认的
使用3个文件表示一张表,表结构.frm,表数据.MYD,索引.MYI
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间 -
InnoDB:
支持事务、外键、行级锁
mysql默认存储引擎
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容(逻辑概念),无法被压缩,无法转换成只读
提供一组用来记录事务性活动的日志文件
在mysql服务器崩溃后提供自动恢复 -
MEMORY:
不支持事务,数据容易丢失,因为所有数据和索引都是存储在内存当中
查询速度最快,以前被称为HEAP引擎
在数据库目录中,每个表以.frm格式文件表示
表数据及索引被存储在内存中
表级锁机制
不能包含TEXT(CLOB)或BLOB字段
事务 transaction
一个事务是一个完整的业务逻辑单元,不可再分
比如:银行账户转账,从A账户向B账户转账1000,需要执行两条update语句
update t_act set balance = balance -1000 where actno='act-001';
update t_act set balance = balance +1000 where actno='act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败
事务的存在是为了保证数据的完整性、安全性(insert delete update)
假设一个业务,需要先执行一条Insert,再执行update,最后执行delete
- 首先开启事务机制
- 执行insert语句–>这个执行成功之后,把执行记录到数据库的操作历史中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据
- 执行update语句–>这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据
- 执行delete语句–>这个执行也是记录一下历史操作,记录到缓存,不会真正的修改硬盘上的数据
- 提交事务commit或者回滚事务rollback(结束)
事务的特性:
ACID
A 原子性:事务是最小的工作单元,不可再分
C 一致性:事务必须保证多条DML语句同时成功或者同时失败
I 隔离性:事务A与事务B之间具有隔离
D 持久性:最终数据必须持久化到硬盘文件,事务才算成功的结束
事务之间的隔离性:事务隔离性存在隔离级别
-
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据
读未提交存在脏读(Dirty Read)现象,表示读到了脏的数据
未提交的事务,另一个线程用户也能够读取到未提交的数据 -
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到
解决了脏读现象
读已提交存在的问题是:不可重复读(做不到从头到尾读取到的数据一样的)
只有提交了事务,另一个线程的用户才能读取到提交后的数据 -
第三级别:可重复读(repeatable read)
解决了不可重复读的问题
但存在的问题是:读取到的数据是幻象
数据可以一直读,读取到的是备份数据,即使数据已经被另一个线程的用户删除了 -
第四级别:序列化读/串行化读(serializable)
解决了所有问题
效率低,需要事务排队
两个线程的用户不可同时进行事务操作,用户A进行操作事务,用户A必须提交事务,用户B才能进行事务操作,排队oracle数据库默认的隔离级别是:第二级别
mysql数据库默认的隔离级别是:第三级别
mysql事务默认情况下是自动提交的(只要执行任意一条DML语句则提交一次)
关闭自动提交 start transaction;(即开启事务)
set global transaction isolation level read uncommitted;
设置全局的事务隔离级别 读未提交
select @@global.tx isolation; 查看全局事务隔离级别
索引 index
- 什么是索引,有什么作用
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源
在数据库方面,查询一张表的时候有两种检索方式:
1)全表扫描
2)根据索引检索(效率很高) - 索引为什么可以提高检索效率?
原因是缩小了扫描的范围
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,维护是有成本的。
比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值添加索引是给某一个字段,或者说某些字段添加索引 select ename,sal from emp where ename = 'SMITH';
当ename字段上添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值 - 怎么创建索引对象?怎么删除索引对象?
创建索引对象
create index 索引名称 on 表名(字段名);
删除索引对象
drop index 索引名称 on 表名;
- 什么时候考虑给字段添加索引?
* 数据量庞大 (根据客户的需求,根据线上的环境)
* 该字段很少的DML操作 (因为字段进行修改操作,索引也需要维护)
* 该字段经常出现在where子句中 (经常根据哪个字段查询)
主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,
explain 查看该语句的解释
索引底层采用的数据结构是:B + Tree
- 索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率最高
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址 = '0x001';
- 索引的分类
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上自动添加索引
唯一索引:有unique约束的字段上会自动添加索引 - 索引什么时候失效?
select ename from emp where ename like '%A%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的
视图 view
站在不同的角度去看待数据(同一张表,通过不同的角度去看待)
创建视图 create view myview as select empno,ename from emp;
删除视图 drop view myview;
对视图进行增删改查,会影响到原表数据。(通过视图影响原表的数据的,不是直接操作的原表)
只有DQL语句才能以视图对象的方式创建出来
视图的作用
- 视图可以隐藏表的实现细节
- 保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD
DBA命令
将数据库中的数据导出
mysqldump 数据库名>E:\test.sql -uroot -proot (DOS命令)
导出数据库中的指定表
mysqldump 数据库名 emp>E:\test.sql -uroot -proot
导入数据
create database 数据库名;
user 数据库名;
source E:\test.sql (source,文件拖进来)
数据库设计三范式
设计范式:设计表的依据,按照三范式设计的表不会出现数据冗余
三范式:
- 第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分
- 第二范式:所有非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段直接依赖主键,不能产生传递依赖
口诀:
多对多,三张表,关系表两个外键
一对多,两张表,多的表加外键
在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度
rank函数的三种用法
- rank() over(业务逻辑)
select name,score, rank() over(order by score desc) 'rank' from student
作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。
说明:例如学生排名,使用这个函数,成绩相同的两名是并列,
下一位同学空出所占的名次。即:1 1 3 4 5 5 7
- dense_rank() over(业务逻辑)
select name,score, dense_rank() over(order by score desc) 'rank'
作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。
说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。
例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6
- row_number() over(业务逻辑)
select name,score, row_number() over(order by score desc) 'rank'
作用:查出指定条件后的进行排名,条件相同排名也不相同,排名间断不连续。
说明:这个函数不需要考虑是否并列,即使根据条件查询出来的数值相同也会进行连续排序。
即:1 2 3 4 5 6
关于面试中遇到的问题
- 面试官说一下你的项目干啥了
答:我的这个项目有某模块…我负责哪些模块,这个模块用到了哪些技术,当时我在做这个的时候遇到了哪些困难,这个困难是如何解决的 - 你项目的哪一块用到索引了
答:当时的业务是怎么回事,为什么要考虑用索引,用了索引项目的效率有什么提升
MySQL试题
1.取得每个部门最高薪水的人员名称
select
e.ename,t.deptno,t.maxsal
from
(select
deptno,max(sal) maxsal
from
emp
group by
deptno) t
join
emp e
on
t.maxsal=e.sal and t.deptno=e.deptno;
2.哪些人的薪水在部门的平均薪水之上
select
e.ename,e.sal,t.*
from
(select
deptno,avg(sal) avgsal
from
emp e
group by
deptno) t
join
emp e
on
e.sal>avgsal and e.deptno = t.deptno;
3.取得部门中所有人的平均的薪水等级
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal betweeen s.losal and s.hisal
group by
deptno;
4.不用max函数,取得最高薪水
select
sal
from
emp
order by
sal desc
limit
0,1
5.取得平均薪水最高的部门的部门编号
select
deptno,avg(sal) avgsal,
from
emp
group by
deptno
order by
avgsal desc
limit
1;
6.取得平均薪水最高的部门的部门名称
select
d.dname,avg(e.sal) avgsal
from
emp e
join
dept d
on
d.deptno = e.deptno
group by
d.dname
order by
avgsal desc
limit
1;