【面试准备日常】从头复习mysql--20240308

更多好玩有趣有用的内容，请关注微信公众号：零氪的云原生

1.mysql数据类型

a.数值类型

分类	类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
数值类型	TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 bytes	(-263，263-1)	(0，2^64-1)	极大整数值
FLOAT	4 bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)
			age TINYINT UNSIGNED
			score double(4, 1)

b.字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串	char(10) -----------> 性能好	用户名 username varchar(50)
VARCHAR	0-65535 bytes	变长字符串	varchar(10) ---------> 性能较差	性别 gender char(1)
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

c.日期时间类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

2.mysql基础

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。 

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4. 注释： • 单行注释：-- 注释内容 或 # 注释内容(MySQL特有) • 多行注释： /* 注释内容 */

库操作

查询有多少个数据库：
SHOW DATABASES;

查询当前使用哪个数据库：
SELECT DATABASE();

创建数据库：
CREATE DATABASE IF NOT EXISTS 表名 DEFAULT CHARSET UTF8MB4;

删除数据库：
DROP DATABASE IF EXISTS库名;

使用数据库：
USE 库名;

常用：
create database if not exists '';

show databases;

drop database if exists '';

表操作

查询当前库有多少表：
SHOW TABLES;

查询创建表的语句：
SHOW CREATE TABLE 表名;

查询表结构：
DESC 表名;

创建表和字段：
CREATE TABLE 表名(ID CHAR(18) COMMENT ‘’)COMMENT ‘’;

增加表字段：
ALTER TABLE 表名 ADD name vachar(18) comment ‘’;

改表字段类型：
ALTER TABLE 表名 MODIFY NAME CHAR(19);

改表字段名和类型：
ALTER TABLE 表名 CHANGE name NAME1 VACHAR(18);

改表名：
ALTER TABLE 表名 RENAME TO 新表名;

删除字段：
ALTER TABLE 表名 DROP NAME;

删除表：
DROP TABLE IF EXISTS 表名;

删除表和数据然后重新创建表结构：
TRUNCATE TABLE 表名;

常用：
create table ''(id int primary key not null comment '')comment '';

show tables;

alter table '' add '' int not null unique comment '';

alter table '' modify ''int;

alter table '' change '' '' varchar(20);

alter table '' drop '';

alter table '' rename to '';

drop table if exists '';

数据操作

插入全部字段的对应数据：
insert into infouser values (1,'001','何嘉劲','男',25,'44128319960521397X','2022-9-19'),( 1,'001','何嘉劲','男',25,'44128319960521397X','2022-9-19');

插入指定的字段的对应数据：
insert into mysql_learn.emp (name, age, dept_id) values ('1',12,3);

常用：
update 表名 set name =’姓名’ where id = ‘’;

delete from 表名 where id = ‘’;

insert into emp (字段1，字段2) values (‘1’,’2’)

查询操作

Select name , count(*) from A01 where name like ‘’ group by name order by name asc ,age desc having *** limit 10,10

select name,count(*) as num from mysql_learn.emp where age >10 group by name having num>3 order by name;

用户操作

创建用户和对应的密码，%代表通配符，也可以定义地址：
create user 'hjj'@'%' identified by '111111'

修改用户的密码，%代表通配符，也可以定义地址：
alter user 'hjj'@'%' identified with mysql_native_password by '185211'

删除用户，%代表通配符，也可以定义地址：
drop user 'hjj'@'%'

常用：
create user ''@'' identified by '';
alter user ''@'' identified with mysql_native_password by '';
drop user if exists ''@'';

权限赋予

（只能给小权限，撤回大权限，不能给大权限，撤回小权限）

赋予某库某表权限到某个用户上：
grant all on mysql_test.* to Hejj@'%';

查询这个用户在哪些库表有操作权限：
show grants for Hejj@'%';

撤回权限，撤回最高权限：
revoke all on *.* from 'Hejj'@'%';

常用：
grant all on *.* to ''@'';
show grants for ''@'';
revoke all on  *.* from ''@'';

字符函数

select concat(1,2,3,4);# 拼在一起，结果是1234

select lpad('123',5,0);# 左填充，5代表五位数，0代表填充数字，结果是00123

select rpad('321',5,0);# 右填充，5代表五位数，0代表填充数字，结果是32100

select lower('ABC');# 变成小写，结果是abc

select upper('abc');# 变成大写，结果是ABC

select trim(' as3 23 ');# 去掉头尾的空格，结果是as3 23

select SUBSTRING('string',2,4);# 截取，索引从1开始，2代表开始索引，4代表结束索引，结果是tri

数值函数

select round(3.23556,2);# 四舍五入，3.23556是需处理的数据，2是代表小数精度的位数，结果是3.24

select rand();查询结果是0-1的随机小数，如0.7435630129124625

select lpad(round(1000000*rand(),0),6,0);#6位随机验证码，rand取出大于0的六位数以及小数，用round处理精度，0位小数精度，即去掉了小数，得到整数，但是不确定是否满足6位数，因为随机数有可能是0.000001，所以lpad左填充，填充满6位数，用0填充。

select ceil(2.35);# 向上取整，结果是3

select floor(2.35);# 向下取整，结果是2

select mod(5,4);# 取余数，5/4=1余1

日期函数

select curdate();#当前日期

select curtime();#当前时间

select now();#当前日期+时间

select year(curdate()),year(now());#取出当前时间的年

select month(curdate()),month(now());#取出当前时间的月

select day(curdate()),day(now());#取出当前时间的日

select date_add(curdate(), INTERVAL 70 DAY);#第一个是当前时间，第二个是固定值INTERVAL，第三个是具体数据（int类型），第四个是年月日其中一个。curdate()取出当前日期，INTERVAL是固定的，70可正可负，正是+70，负是-70，DAY是天数，也可以换成year、month。

select datediff(curdate(),'2022-11-27');#前者和后者的时间天数差：前者-后者。

流程函数

select if(true,'1','2');
#如果是true则返回1，false则返回2

select ifnull('','default');
#''作为判断值，如果''不是null，返回''的内容，否则就返回'default'，注意''也是空值，''
#如果是select ifnull(null,'default')则结果是default.

select name,case workadree when '上海' then '一线城市' when '北京' then '一线城市' else '其他' end from emp;
#case后面加需处理的字段名，然后when后面的值如成立，则显示then后面的值,多种循环选择即继续加when '' then ''，最后结尾的话用 else '' end。

Select name,case when math>=80 then '优秀' when math>=60 then '合格' else '不合格' end from sorce;
#case when 后面的判断式，用true和false作比较，true代表了then后面的值，false则往后退，直到else ‘’ end。

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

**外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

#创建表和数据
create table test_gp(
    id int primary key auto_increment comment '主键',
    gp_name varchar(200) unique not null comment '部门名称，不为空，唯一'
)comment '部门表';

insert into test_gp (gp_name) values ('研发部'),('销售部'),('项目部');
create table test_emp(
    id int primary key auto_increment comment '主键',
    name varchar(200) unique not null comment '名字，不为空，唯一',
    emp_id int comment '部门id',
    constraint fk_emp_id foreign key (emp_id) references test_gp(id)
)comment '员工表';

insert into test_emp (name,emp_id) values ('测试1',1),('测试2',2),('测试3',2),('测试4',3);

#增加外键
alter table test_emp add constraint fk_emp_id foreign key (emp_id) references test_gp(id) on update set null on delete set null ;

#外键更新行为：
级联：alter table test_emp add constraint fk_emp_id foreign key (emp_id) references test_gp(id) on update cascade on delete cascade;
置null：alter table test_emp add constraint fk_rmp_id foreign key (emp_id) references test_gp(id) on update set null on delete set null;

#删除外键
alter table test_emp drop foreign key fk_emp_id;

#删除索引
alter table test_emp drop index fk_emp_id;

多表关系

分为一对一，一对多，多对多。

create table test_gp(
    id int primary key auto_increment comment '主键',
    gp_name varchar(200) unique not null comment '部门名称，不为空，唯一'
)comment '部门表';
create table test_emp(
    id int primary key auto_increment comment '主键',
    name varchar(200) unique not null comment '名字，不为空，唯一',
    emp_id int comment '部门id',
    constraint fk_emp_id foreign key (emp_id) references test_gp(id)
)comment '员工表';

create table book(
    id int primary key auto_increment,
    name varchar(300) unique
);
create table stud(
    id int primary key auto_increment,
    name varchar(200) unique
);
create table stud_book(
  id int primary key auto_increment,
  stud_id int not null ,
  book_id int not null ,
  constraint fk_stud_id foreign key (stud_id) references stud(id),
  constraint fk_book_id foreign key (book_id) references book(id)
);

create table stud(
    id int primary key auto_increment,
    name varchar(200) unique
);
create table stud_info(
    id int primary key auto_increment,
    age int check ( age>0&&age<=100 ),
    stud_info_id int not null unique,
    constraint fk_stud_info_id foreign key (stud_info_id) references stud(id)
);

多表查询

概述: 指从多张表中查询数据

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

内连接：相当于查询A、B交集部分数据

select * from emp e inner join dept d on e.dept_id = d.id#inner可隐藏

外连接：

​ 左外连接：查询左表所有数据，以及两张表交集部分数据

select * from emp e left outer join dept d on e.dept_id = d.id;
等同于
select * from dept d right outer join emp e on e.dept_id = d.id;

​ 右外连接：查询右表所有数据，以及两张表交集部分数据

select * from emp e right outer join dept d on e.dept_id = d.id;
等同于
select * from dept d left outer join emp e on e.dept_id = d.id;

自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接查询：
select A.name as 姓名,B.name as 领导姓名 from emp A , emp B where A.managerid =B.id;
外连接查询：
select A.name as 姓名,B.name as 领导姓名 from emp A left join emp B on A.managerid =B.id;

联合查询-union , union all 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

没去重：
select * from emp where age >50
union all
select *from emp where salary>1000;

去重后：
select * from emp where age >50
union
select *from emp where salary>1000;

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

select *from t1 where column1 = (select column1 from t2)

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

根据子查询结果不同，分为：

	标量子查询（子查询结果为单个值）:子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。 常用的操作符：= <> > >= < <=
	查询 "销售部" 的所有员工信息
		select * from emp where dept_id = (select id from dept where name = '销售部');
	查询在 "方东白" 入职之后的员工信息
		select * from emp where entrydate > (select entrydate from emp where name = '方东白');

	列子查询(子查询结果为一列) :子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN(在指定的集合范围之内，多选一) 、NOT IN(不在指定的集合范围之内
) 、 ANY(子查询返回列表中，有任意一个满足即可) 、SOME(与any等同，使用some的地方都可以使用any) 、 ALL(子查询返回列表的所有值都必须满足)
	查询 "销售部" 和 "市场部" 的所有员工信息
		select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
	查询比 财务部 所有人工资都高的员工信息
		select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
	查询比研发部其中任意一人工资高的员工信息
		select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

	行子查询(子查询结果为一行) :子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
	查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
		select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

	表子查询(子查询结果为多行多列):子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
	查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
		select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
	查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
		select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

根据子查询位置，分为：WHERE之后 、FROM之后、SELECT 之后。

多表查询练习

根据需求，完成SQL语句的编写

# 1. 查询员工的姓名、年龄、职位、部门信息。
select e.name      as 姓名,
       age       as 年龄,
       job       as 职位,
       d.name    as 部门信息
from emp e inner join dept d on e.dept_id = d.id;

# 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select emp.name, emp.age, emp.job, dept.*
from emp
         left join dept on emp.dept_id = dept.id
where age < 30;

# 3. 查询拥有员工的部门ID、部门名称。
select distinct (d.name), d.id
from emp
         inner join dept d on emp.dept_id = d.id;
         
# 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来。
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id
where e.age > 40;

# 5. 查询所有员工的工资等级。
select name, salary, grade
from emp e,
     salgrade s
where e.salary > s.losal
  and e.salary < s.hisal;
  
# 6. 查询 "研发部" 所有员工的信息及工资等级。
select *
from (select name, dept_id, grade
      from emp e,
           salgrade s
      where e.salary > s.losal
        and e.salary < s.hisal) e
         inner join dept d on e.dept_id = d.id
where d.name = '研发部';

# 7. 查询 "研发部" 员工的平均工资。
select avg(salary)
from emp
where dept_id = (select id from dept where name = '研发部');

# 8. 查询工资比 "灭绝" 高的员工信息。
select *
from emp
where salary > (select salary from emp where name = '灭绝');

# 9. 查询比平均薪资高的员工信息。
select *
from emp
where salary > (select avg(salary) from emp);

# 10. 查询低于本部门平均工资的员工信息。
select e.*, d.name
from emp e,
     dept d
where e.salary < (select avg(salary) from emp where emp.dept_id = e.dept_id)
  and e.dept_id = d.id;
  
# 11. 查询所有的部门信息, 并统计部门的员工人数。（需要传参）
select distinct (d.name), (select count(*) from emp where emp.dept_id = e.dept_id)
from emp e
         right join dept d on d.id = e.dept_id;
         
# 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select s.name, s.no, c.name
from student s,
     student_course sc,
     course c
where s.id = sc.studentid
  and sc.courseid = c.id;

总结：

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作 请求，即这些操作要么同时成功，要么同时失败。事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

查询事务的提交方式0为手动，1为自动：

select @@autocommit;

设置事务的提交方式：

set autocommit = 0；

手动开启事务：

start transaction/begin;

提交：

commit;

回滚：

rollback;

事务四大特性
• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。 
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 脏读:一个事务读到另外一个事务还没有提交的数据。

• 不可重复:读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

• 幻读:一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影 。

查看事务隔离级别

select @@transaction_isolation;

设置事务隔离级别

set session/global transaction isolation level read uncommitted/read committed/repeatable read/serializable;

事务隔离级别

read uncommitted：会引发脏读，不可重复读，幻影问题。

read committed：会引发不可重复读，幻影问题。解决脏读问题，解决一个事务执行过程中读到其他事务没有提交的内容。

repeatable read：会引发幻影问题。解决脏读、不可重复读问题，解决一个事务执行过程两次读到不同内容，原因是先后读到其他事务已经提交前后的内容。

serializable：不会引发脏读，不可重复读，幻影问题。解决脏读、不可重复读、幻影问题，解决一个事务执行过程中查询前后结果一致但无法操作成功的情况。

3.mysql进阶

存储引擎

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户 端验证它所具有的操作权限。

服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存 储引擎的功能也在这一层实现，如 过程、函数等。

引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我 们可以根据自己的需要，来选取合适的存储引擎。

存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。