Mysql基础复习

SQL语言

DDL数据库操作（对库的操作）

1.show databases   # 查询所有数据库
2.create database [if not exists] [databaseName] [default charset 字符集] [collate 排序规则] # 创建数据库
3.select database() # 查询当前数据库
4.drop database [if exists] [databaseName] # 删除数据库
5.use [databaseName] # 使用对应数据库
6.show tables # 查询当前数据库所有的表
7.desc 表名 # 查看表结构
8.show create table 表名 # 查询指定表的建表语句
9.create table emp(
		id int commit '编号',
		name varchar(20) commit '姓名',
		gender char(1) commit '性别'
	)engine = InnoDB default charset = 'utf8' comment '员工表'  # 创建表
10.alter table 表名 add 字段名 类型 [comment 注释][约束]  # 向表中添加字段
11.alter table 表名 change 旧字段 新字段 类型 [comment 注释][约束] # 修改表中字段
12.alter table 表名 drop 字段名 # 删除表中字段
13.alter table 表名 rename to [newName] # 修改表名
14.drop table [if exists] 表名 # 删除表
15.truncate table 表名 # 删除表并重新创建表

DML 增删改

1.insert into 表名(...字段名) values (...值),(...值) # 添加数据
2.update 表名 set 字段名1 = 值1,字段名2 = 值2 where 条件  # 修改数据
3.delete from 表名 where 条件 # 删除数据，如果没有条件，则全部删除

DQL 查询

select 字段列表
 from 表名
 where 条件
 group by 分组字段列表
 having 分组后条件列表
 order by 排序字段列表
 limit 分页参数

聚合函数

聚合函数： 将一列数据作为一个整体，进行纵向计算

1.count  # 统计数量
	SELECT COUNT(age) from emp;   # 不会统计null值  * 统计全部
2.max	# 最大值
	SELECT MAX(age) from emp;
3.min	# 最小值
	SELECT MIN(age) from emp;
4.avg	# 平均值
	SELECT AVG(age) from emp;
5.sum	# 和
	SELECT SUM(age) from emp where gender = 'F';
	

分组查询

主要配合聚合函数使用

select 字段列表
 from 表名
 where 条件
 group by 分组字段列表
 having 分组后条件列表,对每个分组进行判断

where 在分组之前进行过滤，不满足where，不参与分组； having是分组之后进行过滤
where 不能对聚合函数过滤

# 根据性别进行分组，查询个数
SELECT gender,count(*) from emp group by gender;

# 根据性别进行分组，查询每个性别员工的平均年龄
SELECT gender,avg(age) from emp group by gender;

# 根据性别进行分组，查询总数大于4的性别人数
SELECT gender,count(*) from emp group by gender HAVING count(*)>4;

排序查询

select 字段列表
 from 表名
 where 条件
 order by 排序字段列表

# 按age排序查询
select name,age from emp order by age asc;   # 升序 默认
select name,age from emp order by age desc;	 # 降序

分页查询

select 字段列表
 from 表名
 limit 起始索引,查询记录数

# 索引从0开始  如果是选择从0开始，0可以省略  limit 3;
select name,age from emp limit 0,3;

DCL 用户权限管理

# 查询用户
use mysql;
select * from user;

# 创建用户
create user '用户名'@'主机名' identified by '密码';

# 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

# 删除用户
drop user '用户名'@'主机名'

# 查询权限
show grants for '用户名'@'主机名'

# 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' # *.*代表所有数据库和表

# 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

函数

字符串函数

1.concat(s1,s2,...)	# 字符串拼接
	select * from emp where name like concat('%','三','%');
	select CONCAT(name,age)	as component from emp
2.lower(str)	# 小写
	select name,lower(gender) as gender from emp
3.upper(str)	# 大写
	select name,upper(gender) as gender from emp
4.lpad(str,n,pad)	# 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
	select LPAD('z',10,1);
5.rpad(str,n,pad)	# 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
	select RPAD('z',10,1);
6.trim(str)	# 去掉首尾空格
	select trim('    5   ')
7.substring(str,start,len)	# 字符串截取，从start开始，截取len个长度
	select substring('123456789',1,5)  # start从1开始
	

数值函数

1.ceil(x)	# 向上取整
	select ceil(2.6)
2.floor(x)	# 向下取整
	select floor(2.6)
3.mod(x,y)	# 返回x/y的模(余数)
	select mod(9,4)
4.rand()	# 返回0-1随机数
	select rand()
	select floor(rand()*10+1)	# 随机1-10的数
	select lpad(floor(rand()*1000000),6,'0')  # 6位验证码  填充0
5.round(x,y)	# 求参数x的四舍五入的值，保留y位小数
	select round(2.666666,3)

日期函数

1.curdate()	# 返回当前日期
	select curdate()
2.curtime()	# 返回当前时间
	select curtime()
3.now()		# 返回当前日期和时间
	select now()
4.year(date)# 获取指定date的年份
	select year(now())
5.month(date) # 获取指定date的月份
	select month('2022-03-06')
6.day(date)	# 获取指定date的日期
	select day(now())
7.date_add(date,interval expr type)	# 返回一个日期/时间值加上一个时间间隔expr后的时间值
	select date_add(now(),INTERVAL 70 day) 	# type: day/year/month;	expr数值
8.datediff(date1,date2)	# 返回起始时间date1和结束时间date2之间的天数,减法
	select datediff('2022-03-10','2022-03-06')
9.date(date)	# 返回date的日期
	select date(now())

流程函数

1.if(value,t,f)	# 如果value为true,则返回t,否则返回f
2.ifnull(value1,value2)	# 如果value1不为空，返回value1，否则返回value2
3.case [expr] when [val1] then [res1] ... else [default] end
	# 如果expr的值等于val1,返回res1,否则返回default默认值	类似于switch-case

约束

1.not null	# 非空约束，限制该字段的数据不能为null
2.unique	# 唯一约束 保证该字段的所有数据都是唯一，不重复的
3.primary key # 主键约束 主键是一行数据的唯一标识，要求非空且唯一
4.default	# 默认约束 保存数据时，如果未指定该字段的值，则采用默认值
5.check	# 检查约束 保证字段值满足一个条件(8.0.16之后)
6.foreign key	# 外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性

# 创建用户表
create table user(
	id int primary key auto_increment comment '主键',
	name varchar(20) not null unique comment '姓名',
	age int check (age > 0 && age<=120) comment '年龄',
	status char(1) default '1' comment '状态',
	gender char(1) comment '性别'
)	comment '用户表'

# 插入数据
insert into user(name,age,status,gender) values 
('tom1',19,'1','M'),('tom2',20,'0','F');

insert into user(name,age,gender) values ('tom4',30,'M')

# 创建部门表
create table dept(
	id int primary key auto_increment,
	name varchar(20)
)comment '部门表'

insert into dept(name) values('安保部'),('研发部'),('销售部'),('财务部')

# 添加外键 
alter table emp add constraint dept_fk foreign key(did) references dept(id)

# 删除外键
alter table emp drop foreign key dept_fk

外键删除/更新行为

1. no action # 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许被删除/更新
2. restrict # 同上
3. cascade	# 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新子表记录
4. set null # 当在父表中删除对应记录时，首先检查是否有对应外键，如果有则设置子表中该外键值为null

# 修改外键删除修改行为    emp表数据会跟着dept表的数据变化而变化(删除/修改)
	alter table emp add CONSTRAINT dept_fk foreign key(did)
		references dept(id) on update cascade on delete cascade

多表查询

多表关系

1.一对多(多对一)
	例子：一个班主任对应着一群学生，多个学生有一个班主任
2.多对多
	例子：一个学生可以选多门课，每门课可以被多个学生选
3.一对一
	例子：每个人只有一个身份证号

多表查询

笛卡尔积	两个集合中的每个元素都互相匹配
消除笛卡尔积使用条件查询
select * from emp,dept where dept.id = emp.did

内连接

内连接查询的是两张表交集的部分

# 隐式内连接 使用逗号
select * from emp,dept where dept.id = emp.did
# 显示内连接  inner join
select * from emp [inner] join dept where dept.id = emp.did

外连接

# 左外连接  左表所有数据和右表交集数据
select * from emp left [outer] join dept on 条件
# 右外连接  右表所有数据和左表交集数据
select * from emp right [outer] join dept on 条件

自连接

自己连接自己查询，使用别名，可以使用内连接，也可以外连接

# 通过managerId字段内连接查询manager姓名
select e1.name as emp,e2.name as manager from emp as e1,emp as e2 where e1.managerId = e1.id

联合查询

把多次查询的结果合并起来，形成一个新的结果集

select 字段列表 from 表A
union [ALL]
select 字段列表 from 表B

子查询

sql语句嵌套查询

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

# 标量子查询 子查询结果为一个值
	select * from emp where id = (select id from dept where name = '销售部')
# 列子查询	子查询结果为一列(常用操作符 IN ,NOT IN, ANY,SOME,ALL)
	select * from emp where id = in (select id from dept where name = '销售部' or name = '市场部')
# 行子查询	子查询结果为一行(常用操作符 =，<>,IN,NOT IN)  组合条件
	select * from emp where (salary,managerId) = (select salary,managerId from emp where name = '张无忌')
# 表子查询	子查询结果为一张表 组合条件，查询出多个组合条件，使用IN来包含
	select * from emp where (salary,managerId) in (select salary,managerId from emp where name = '张无忌' or name = '宋远桥')
	

事务

# 查看/设置事务提交方式
	select @@autocommit; # 默认1，自动提交
	set @@autocommit = 0;
# 提交事务
	commit;
# 回滚事务
	rollback;

事务隔离级别

# 查看事务隔离级别
	select @@transaction_isolation;
# 设置事务隔离级别
	set session transaction isolation level [read uncommited/read commited];