mysql

9.1聚合函数

聚合函数:比较简单
avg():返回结果集的平均值
count():返回结果集的函数
max():返回某字段的最大值
min():返回某字段的最小值
sum();返回某字段总和

9.2单行函数

9.2.1数学函数

数学函数:主要用于处理数字,包括整数和浮点数
abs(x):返回x绝对值
ceil(x):返回x 向上取整
floor(x):返回x 向下取整
round(x,y):参数1是数字,参数2是保留几位小数点 进行四舍五入
rand():返回0~1的随机数

9.2.2字符串函数

字符串函数:主要用于处理字符串数据的函数
length(字符串):返回字符串长度(统计字节数量)
如:select length('abc')  ---3
	select length('你好abc')  ---9
char_length(字符串):返回字符串长度(统计字符)
concat(s1,s2,s3...):将多个字符串拼接在一起展示
upper(字符串):转换大写
lower(字符串):转换小写
left(字符串,个数):返回字符串前几个字符
right(字符串,个数):返回字符串后几个字符
substring(字符串,起始值,长度):截取字符串 起始值初值为1,长度是表示从起始值开始截取 截取多长
substring(字符串,起始值):截取字符串,从初值开始截取到末尾
replace(字符串,原内容,替换后的内容):

9.2.4字符串函数

日期函数:主要用于处理日期时间
now() 或者 sysdate() 返回当前时间
curdate() 返回当前年月日
month() 返回日期中的月份  day() hour()
year() 返回日期中的年份
adddate(日期,天数):给当前添加多少天
last_day(日期):返回日期当月最后一天
datediff(日期1,日期2):两个日期的天数差
from_days(天数):把天数转换为日期格式 只支持365天以上
extract(类型 from 日期):表示从日期中提取任意部分
	类型:year month day hour minute second
	select extract(year from now())

9.2.4条件判断函数

条件判断函数:
if(表达式,值1,值2):如果表达式成立,返回值1,否则值2
	如:select if(1>0,'正确','错误');
ifnull(值1,值2):如果值1不为null,返回值1,否则值2
	如:select ifnull(null,'java');
case函数:结构相对较复杂 语法如下
case
	when 条件1 then 结果1
	...
	when 条件2 then 结果n
	eles 其他结果
end

系统信息函数:了解
version():查看数据库版本
database():返回当前使用数据库名
connection_id():返回数据库连接数

加密函数:了解
md5(字符串):可以对字符串进行MD5算法加密,不可逆的
password(字符串):可以对字符串进行加密

其他函数:
format(x,n):可以实现数字格式化,可以将数字保留几位小数
			保留x数字第n位的小数
如:select format(3.1415,2); --3.14
date_format(日期,格式):日期转换字符串
str_to_date(字符串,格式):字符串转换日期

日期格式: %Y-%m-%d %H:%i:%s
%W 星期名字 英文单词
%Y 年 四位年   %y 年 两位年
%a 星期缩写 %m月 %d天 %H时 %i分 %s秒

10.分组查询—难点

将查询的数据按照指定的字段进行分组,这样就可以把一张表的数据分成不同的类别分别处理,分组的字段可以是一个也可以是多个,分组之后使用 count sum avg等聚合函数

where不能和having更换位置,因为where是分组前条件,而having是分组后条件

语法如下
select [聚合函数],字段? from 表 
[where 分组前条件]
group by 分组字段,分组字段2...
[having 分组后条件]
注:如果查询语句添加分组,查询字段 只能是分组的字段或是聚合函数,根据分组字段才能查
   mysql可以任意写,oracle严格要求不可以写

11.连接查询

内连接:只能够查询出满足关联条件的数据,如果不满足不会查询出来

语法:select * from 表1
	inner join 表2 on 表1.字段=表2.字段
	inner join 表3 on 表2.字段=表3.字段
	...
注:两张关联条件的字段 是为了描述一个类型的数据
简化版:select * from 表1,表2,...
		where 表1.字段=表2.字段...

外连接:左外连接和右外连接和全外连接
左外连接:以左侧表为主,以右侧表为副,主表的数据会全部展示,副表 只能查询满足关联条件的数据,不满足会显示null

右外连接:类似于左外连接 两者的表颠倒了数据
语法:right join on
全外连接:full join on

语法结构: left join on
select * from 表1 left join 表2
	on 表1.字段=表2.字段

注:mysql不支持全外连接

自连接:自连接并不是连接的一种类型,是连接的一种使用方式,本身相当于一张表当成两张表使用,自己连接自己

案例:求出员工的姓名和工作和上级领导姓名和工作
select e1.ename,e1.job,e2.ename,e2.job 
from emp e1,emp e2
where e1.mgr=e2.empno

12.子查询

子查询也叫内部查询,包含子查询的语句叫外部查询,子查询允许一个查询语句嵌套另一个查询数据,并且可以嵌套在insert update delete 语句可以使用

子查询主要三大类: where型子查询 from型子查询exists型子查询(比较难理解)

12.1where型子查询

where型:把内层查询结果,当成外层查询条件
语法:select * from 表 where deptno=(子查询)

-- 查询比3-245课程最高分 还高的学生信息
select * from student where sno=比245课程最高分还高的学生id
-- 比245课程最高分还高的学生id
select sno from t_score where degree>245课程最高分
-- 245课程最高分
select max(degree) from t_score where cno='3-245'

汇总
select * from student where sno in (select sno from t_score where degree>(select max(degree) from t_score where cno=''))
注:如果子查询的结果不是一条 需要使用 in

12.2from型子查询

from型:把内层查询结果 当做一张临时表来处理,一般这个临时表都需要加别名

语法:select sno,cno from 表,(子查询) 别名

where 条件
汇总
select * from student where sno in
(select sno from t_score s1,
(select cno,avg(degree) sc from t_score
group by cno) s2
where s1.cno=s2.cno and s1.degree<sc)

12.3exists型子查询

exists型:把内层查询结果,拿到外层查询进行测试,如果内层查询成立(可以查到数据) 才会执行外层查询,否则不会执行外层查询

语法:比较类似于where型子查询
-- 表示是否存在子查询这种情况
select * from 表 where exists(子查询)
-- 表示是否不存在子查询这种情况
select * from 表 where not exists(子查询)
注:exists功能特点比较类似于in 性能会高于in

13.mysql约束

用于限制表中的数据,保证添加到数据库中的数据是准确的和可靠的,如果数据不合法,插入数据就会出现错误

添加约束时可以创建表添加也可以修改表添加

主要分类:非空约束 默认约束 唯一约束 检查性约束

​ 主键约束 外键约束

13.1非空约束

非空约束:not null 保证字段不能为null
create table a(
	name varchar(10) 约束 索引
    ename varchar(10) not null
)

13.2默认约束

默认约束:default 保证字段总是有值,即使没有插入,使用默认值
create table a(
	age int default 18
)

13.3唯一约束

唯一约束:unique 保证数据唯一,允许数据为null,可以多个,唯一约束会自动添加改了的索引(唯一索引)
create table a(
	card varchar(30) unique
)

13.4检查性约束

检查性约束:check (mysql语法通过,但是会失效)
create table a(
	age int check(age between 0 and 120),
    sex char(1) check(sex='1' or sex='0')
)

13.5主键约束

主键约束:primary key 用来确保每行数据唯一一种方式(每个表必须加主键),唯一且非空,一张表必须有且只有一个主键,如果有多列 可以创建联合主键  主键也会自动创建索引(主键索引) 重点
create table a(
	id int primary key
)
-- 联合
create table b(
	id int,
    primary key(id1,id2)
)

13.6外键约束

外键约束:foreign key 定义在具有父子关系的子表中,外键约束保证子表中的外键 必须对应 父表中主键列(或唯一键)
用于确保数据完整性 重点 难点
emp empno ename deptno(外键)(子表)
	1	aaa		10			
	2	bbb		20					
	3   ccc		30   不能保证完整性数据
	4   ddd    null  外键允许为空
	5	eee		20	  外键可以重复
dept deptno(主键) dname(父表)
	10			部门1
	20			部门2
	
-- 创建外键时,需要先定义好父表 还有主键
create table emp(
	-- deptno int references 父表(主键)
	deptno int references dept(deptno)
)

如果表已经存在了  如何添加约束
创建 约束(非空 唯一 主键 默认)
alter table 表 add primary key(id)
alter table 表 add unique(name)

删除约束
alter table 表 drop 约束名

修改约束
alter table 表 modify 字段 类型 unique

外键约束
alter table 表 add foreign key(字段) references 父表(主键)

14.mysql索引—高频面试题

索引目的是用于提高数据库查询效率,也是优化sql语句非常重要一种方式,类似于字典中的目录,可以不断的缩小查询数据的范围,相当于每个索引都会对应一个类似于下标值

以后查询数据库只要查询索引字段 就可以快速定位下标这个位置 这样就可以不用全表扫描 这种类似于下标值计算方式是实现B树 或者B+树

mysql索引分类:
a.普通索引: index  加快查询速度
b.唯一索引:
		主键索引:primary key :都是加快速度 保证数据唯一非空
		唯一索引:unique:加快速度  数据唯一
c.联合索引:
		primary key(id,name):联合主键索引
		unique(id,name):联合唯一索引
		index(id,name):联合普通索引
d.全文索引:fulltext
		用于实现搜索长篇文章效果很好
...

案例:
会员表--编号 姓名 身份证号 电话 家庭住址 备注信息
create table 表名(
	id int,
    name varchar(100),
    card varchar(18),
    mess text
)
id 适合主键索引   name 适合普通索引(经常可能用于条件查询)
card 适合唯一索引  mess 适合全文索引

创建和删除索引
方式1:创建表添加
create table 表名(
	字段1 类型 约束,
    ...
    字段n 类型 约束,
    [unique][fulltext] index 索引名 (字段),
    primary key(字段,...)
);
方式2:表已经存在了
	create [unique][fulltext] index 索引名 on 表(字段)
方式3:修改表结构 利用修改表结构
alter table 表 add [unique][fulltext] index 索引名(字段)

删除索引  删除普通和唯一索引
drop index 字段 on 表
drop index 索引名
-- 删除主键索引
alter table 表 drop primary key

面试题

使用索引: 通过添加了索引的字段 做为查询条件,通查都会走索引 进而可以提高查询的速度

如何查看是否走索引
explain + 查询语句 查看sql语句指定计划,查看sql语句整体性能  也能看到是否有索引
比如:explain select * from emp

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rKr2VHCm-1660755386314)(C:\Users\zm\AppData\Roaming\Typora\typora-user-images\image-20220815165314267.png)]

指定计划解析:
select_type:表示是否存在多表连接 或者子查询 或者 union
table:查询语句所涉及的表
type:可以查看sql语句整体性能,也可以查看是否走索引
   性能从高到低:
   system>const>eq_ref>ref>range>index>all
   一般的查询语句至少保证性能是range级别的,最好是ref,否则会出现性能问题
    system:表中只有一条数据或者为空,innerDB引擎的表  默认都是all
    const:表示是主键或唯一键索引,如果主键不存在的话则为null
    eq_ref;使用在表连接中 表中有一条数据返回 对应另一个表的主键或唯一键 且必须为not null
    ref:相比eq_ref没有这么多要求,一般普通索引都会返回它
    range:索引扫描范围 使用 > < is null between like in 等查询中可能出现它
    index:索引全表扫描,把索引从头扫描一遍
    all:全表扫描,只有all 不走索引
    总结:type=all 不走索引 一般情况下 都会索引
    
possible_key 和 key:两者都类似 如果不走索引值是空的
	如果走了索引 一般是有值的
rows:指定计划中 估算扫描行数

索引什么时候失效—面试题目

索引什么时候失效:
a.使用 or 由于是或者的关系 不一定所有的条件都加了索引 会导致失效
b.使用 like 做模糊查询的时候  使用前% 不走索引
	name like '%王' 不走索引
	name like '王%' 走索引
c.如果条件是字符串类型,一定要加引号,如果每加 mysql会自动类型转换了 只要转换了就不走索引
d.使用不等于<> 作为条件 不会走索引
e.使用 is null 或 is not null 不走索引
f.索引列参与运算  不走索引
	where id+10=20  不走索引
	where id=10+20  走索引
g.索引列使用函数 不走索引
	where concat(name,'abc')=??? 不走索引
	where name=concat('aa','bb') 走索引

注:索引是否是越多越好,索引确实可以增加查询速度,如果给每列都添加了索引会降低增 删 改速度(目录差不多,因为每次添加数据,目录也需要更改,类似),因为每次数据修改都应索引值也需要做响应的更新类似于更新字段的目录

15.视图—面试题

(view)视图是一种虚表,表示本身并不存在,本身不包含数据,包含的一条查询语句,每次使用视图相当于重新执行一遍查询语句

好处:

​ a.安全:屏蔽用户数据库表的逻辑,无法查看数据库表的结构

​ b.简单:只要通过视图名当做表名使用,就可以实现原来的功能,这样屏蔽了sql语句的复杂度

是否可用提高效率?

​ 不能提高效率,每次使用视图知识重新执行一边sql语句,数据还在原来的表中,所以不会提高效率

创建语法:
create view 视图名 as 任何查询语句

修改语法
alter view 视图名 as 查询语句
alter view 视图名 as 其他视图名

删除语法
drop view 视图名

16.事务

事务是绑定在一起的逻辑工作单元(一个业务功能),这个工作单元可能会包含多次的sql语句操作,必须保证这么多次sql语句操作同时成功,同时失败.保证数据安全

16.1事务的四大特性—面试题

原子性:不可分割，这个逻辑工作单元包含多次sql语句操作必须同时成功 同时失败

一致性：数据库必须从一个一致性状态到另一个一致性状态

​ （程序运行期间 数据前后保持一致）

隔离性：一个事务和其他事务之间不能相互干扰

持久性：事务一旦提交，对数据的修改是永久的

16.2事务的并发问题—面试题

脏读:事务A读取到了事务B未提交的数据,事务B执行回滚操作,那么事务A读取到的数据就是脏数据

幻读:事务A多次统计数据行数,事务B再事务A统计过程中对数据新增或删除,导致事务A多次统计行数不一致

不可重复读:事务A多次读取同一个数据,事务B再事务A读取的过程中对数据进行更新操作立即提交事务,导致事务A多次读取同一个数据时结果不一致

总结: 不可重复读 很容易出现混淆,不可重复与脏读侧重于修改,幻读侧重于新增与删除

16.3事务的隔离级别—面试题

数据库允许多个事务并发访问数据,隔离性可以防止多个事务之间并发访问时出现数据不一致的问题,所以隔离性就出现事务的隔离级别

级别:读未提交 < 读已提交 < 可重复读 < 串行化

级别越高,越能保证数据完整性和一致性,但是对性能影响越大

mysql: 默认事务隔离级别是可重复读

事务隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	x	√	√
可重复读	x	x	√
串行化	x	x	x

16.4mysql怎么做事务

mysql本身默认提交事务,每次对数据修改都会自动提交事务
set autocommit=1 开启自动提交(默认是自动提交)
set autocommit=0 开启手动提交

commit 提交事务 (确认数据没问题 数据准备修改了)
rollback 回滚事务 (数据有问题 要撤销)

数据库范式

设计关系型数据库,需要遵循一些规范要求,才能够设计出合理的数据结构,这些规范称之为范式

范式是一中分层结构 主要分六层,高层会比低层更加严格 更能保证数据完整性和一致性

六层范式:1NF 2NF … 6NF最高层 最严格,但是数据库不仅要保证数据安全也要保证性能,一般情况下只需要满足前三范式

面试题:
第一范式(1NF):要求设计数据库时字段具有原子性,(字段不能是对象或者集合或者数组 这类非原子性数据)
员工编号 姓名 部门对象(不满足1NF)

第二范式(2NF):在满足第一范式的基础上,还要求每张表的数据唯一(强制要求你加主键)

第三范式(3NF):在满足第二范式基础上,还要求不能有其他表非主键列(不能有重复的列 否则数据冗余 需要添加主外键关联)
学号(pk) 姓名 系名(不满足3NF)  学编号(pk) 系名 系主任

17.行转列 列转行—笔试题

行转列:case when
列转行:union 或 union all

• 行转列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hnUwKWAN-1660755386316)(C:\Users\zm\AppData\Roaming\Typora\typora-user-images\image-20220816152054896.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3gK2RoS5-1660755386317)(C:\Users\zm\AppData\Roaming\Typora\typora-user-images\image-20220816152109916.png)]

• 列转行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mrs9xXZx-1660755386318)(C:\Users\zm\AppData\Roaming\Typora\typora-user-images\image-20220816154946908.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-za8kDg4W-1660755386320)(C:\Users\zm\AppData\Roaming\Typora\typora-user-images\image-20220816154952713.png)]

18.sql优化

a.建立索引 同时避免不走索引情况查询
b.禁止使用*来查询,查找要写具体字段
c.如果只查询一条数据,可以使用 limit 1
d.分表和分库
	分库:将一个库的数据拆分成很多小的子库,查询时只需要查询子库
	分表:将一张表的数据拆分成很多的子表,查询时可以对子表进行查询
	分表和分库方式:
	垂直拆分;相当于把表的不同字段的数据 分别存储在不同表
		id un pw name age
		id un pw 	id name age
	水平拆分:相当于把每行数据 分成几个子表进行存储