数据库MySQL

数据库MySQL

数据类型

1. 数值类型

• TINYINT 1B
• SMALLINT 2B
• MEDIUMINT 3B
• INT 4B
• BIGINT 8B
• FLOAT(M,D) 4B
• DOUBLE(M,D) 8B
• DECIMAL(M,D) M<65&&D<30&&D<M 定点精确小数

2. 字符串类型(常用)

• CHAR(n) 0-255B 定长字符串
• VARCHAR(n) 0-65535B 变长字符串

3. 日期时间类型

• DATE
  ‘1000-01-01’ 至 ‘9999-12-31’ YYYY-MM-DD 日期
• TIME
  ‘-838:59:59’ 至 ‘838:59:59’ hh:mm:ss 时间或持续时间
• YEAR
  1901 至 2155 YYYY 年份
• DATETIME
  ‘1000-01-01 00:00:00’ 至 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 日期时间
• TIMESTAMP
  ‘1970-01-01 00:00:01’ UTC 至 ‘2038-01-19 03:14:07’ UTC YYYY-MM-DD hh:mm:ss

约束

1. 分类

• 列级约束:NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK | AUTO_INCREMENT
• 表级约束:PRIMARY KEY | UNIQUE | CHECK | FOREIGN KEY

2. 注意项

• 自增字段必须为唯一键,如果忽略则从添加过的最大值开始递增
• 定义外键时可以设置 ON UPDATE|DELETE CASCADE 表示当主表更新或删除时副表随主表产生变化

数据定义语言DDL

1. 展示数据库、数据表

#展示所有
show databases
show tables
#展示创建语句
show create database <库名>
show create table <表名>

2. 创建数据库、数据表

#数据库创建
create database <库名> [default character set <字符编码> collate <字符编码校验>]
#数据表创建
create table <表名>(
  <字段名> <字段类型> <列约束>,
  ...
  [constraint <约束名>] <表约束>
)

3. 修改数据库、数据表

#修改数据库编码
alter database <库名> default character set <字符编码>
#添加字段
alter table <表名> add column <字段描述>,...
#添加约束
alter table <表名> add [constraint <约束名>] <表约束>
#修改字段
alter table <表名> modify column <字段描述>
#修改字段名
alter table <表名> change column <原名> <字段描述>
#删除字段
alter table <表名> drop column <字段名>,...
#修改表名
alter table <原名> rename to <新名>

4. 删除数据库、数据表

drop database [if exists] <库名>
drop table [if exists] <表名>

5. 查看表结构

desc <表名>

数据管理语言DML

1. 插入

#插入值需要与字段对应
insert into <表名> [(<字段名>,...)] values
(<字段值>,[<字段值>,]),
[(<字段值>,[<字段值>,]),]

2. 更新

update <表名> set <字段名>=<字段值>,... [where <条件>]

3. 删除

#删除行之后,表的递增点不变
delete from <表名> [where <条件>]

数据查询语言DQL

1. 简单查询

select * from <表名>
select <字段名>,... from <表名>
select distinct <字段名> from <表名>
select <字段名> <别名> from <表名>
select <计算字段> from <表名>

2. 条件查询

#比较
< > = <= >= !=
#区间
[not] between a and b
#集合
[not] in (a,b,c)
#空值
is [not] null
#模糊匹配
[not] like
#逻辑
and or

3. 聚合查询

#除count(*)外都跳过空值
sum([distinct] <字段名>)
count([distinct] <字段名>)
max([distinct] <字段名>)
min([distinct] <字段名>)
avg([distinct] <字段名>)

4. 分组查询

#分组后对每组进行聚合查询
group by <字段名> [having <条件>]

5. 排序查询

order by <字段名> [desc]

6. 分页查询

limit (<页码>-1)*<页条数>,<页条数>

7. 连接查询

#字段名重复时需要加上表名
#内连接
select <字段名> from <表1>,<表2> where <条件>
#左外连接
select <字段名> from <表1> left join <表2> on <条件>
#右外连接
select <字段名> from <表1> right join <表2> on <条件>

8. 嵌套查询

select <字段名> from <表名> where <字段名> in (<子查询>)
#子查询只返回一个值
select <字段名> from <表名> where <字段名> <比较运算符> (<子查询>)
#any对集合中某个值比较通过即返回,all对集合中所有值比较通过即返回
select <字段名> from <表名> where <字段名> <比较运算符> any|all (<子查询>)
#exists语句只返回true或false
select * from <表名> where [not] exists (<子查询>)

9. 集合查询

#交并差集
intersect union except

数据控制语言DCL

1. 用户管理

#创建用户
create user <用户名>@<主机名> identified by <密码>
#修改用户
alter user <用户名>@<主机名> identified with mysql_native_password by <新密码>
#删除用户
drop user <用户名>@<主机名>

2. 权限控制

#常用权限
all select insert update delete create alter drop
#查询权限
show grants for <用户名>@<主机名>
#授予权限
grant <权限>,... on <库名>.<表名> to <用户名>@<主机名> [with grant option]
#收回权限
revoke <权限>,... on <库名>.<表名> from <用户名>@<主机名> [restrict|cascade]

数据库设计范式

1. 字段不可再分
2. 字段完全依赖于主键
3. 字段直接依赖于主键
4. 联合主键字段间无依赖关系

函数

1. 字符串函数

concat(s1,s2)          #连接字符串
lower(s)               #转为小写
upper(s)               #转为大写
lpad(s,n,pad)          #以pad向左填充至长度为n
rpad(s,n,pad)          #以pad向右填充至长度为n
trim(s)                #去除首尾空格
substring(s,start,len) #截取字符串

2. 数值函数

ceil(x)                #向上取整
floor(x)               #向下取整
round(x,y)             #四舍五入保留y位小数
mod(x,y)               #取模
rand()                 #0-1随机值

3. 日期函数

curdate()              #当前日期
curtime()              #当前时间
now()                  #当前日期时间
year(date)             #年
month(date)            #月
day(date)              #日
hour(time)             #时
minute(time)           #分
second(time)           #秒
datediff(date1,date2)  #相差天数
timediff(time1,time2)  #相差时间
date_add(date,interval)#指定日期加上间隔后的日期
date_sub(date,interval)#指定日期减去间隔后的日期

4. 流程函数

#判断
if(val,a,b)
#判空
ifnull(a,b)
#多选择
case val
when a then b
[when c then d]
else z
end

事务

1. 使用事务

begin;    #开启
commit;   #提交
rollback; #回滚

2. 并发事务问题

• 脏读:读取到未提交数据
• 不可重复读:读取数据不一致
• 幻读:读取数据不存在,但是插入失败

索引

1. 索引结构

• B树索引:多路查找树,每个节点保存数据
• B+树索引:多路查找树,只在叶子节点保存数据,效率比B树高且稳定
• hash索引:哈希表,不支持范围查询
• full-text索引:倒排索引,快速匹配文档

2. 索引分类

• 主键索引:随主键创建,唯一
• 唯一索引:随唯一键创建
• 常规索引:用户创建
• 全文索引:匹配文档

聚集索引:选择主键索引或者第一个唯一键索引,唯一,其叶子节点保存行数据
二级索引:叶子节点保存主键

3. 使用索引

#查看索引
show index from <表名>
#创建索引
create [unique|fulltext] index <索引名> on <表名>(<字段名>,...)
#删除索引
drop index <索引名> on <表名>

4. sql性能查询

#查看耗时
show profiles
#查看执行计划
explain <查询语句>

5. 索引使用法则

• 对于联合索引,查询条件应按索引顺序,跳过或者使用范围查询则后面的索引部分失效
• 索引列不能进行运算
• 字符串应加引号
• 头部模糊匹配,索引失效
• or后面的条件没有索引,前面的索引失效
• 使用提示:<简单查询> [use|ignore|force] index(<索引名>) where <条件>
• 字符串尽量取一部分索引
• 尽量使用联合索引

视图

1. 使用视图

#创建视图
create view <视图名> [(<字段名>,...)] as <查询语句> [with check option]
#修改视图
alter view <视图名> [(<字段名>,...)] as <查询语句> [with check option]
#删除视图
drop view [if exists] <视图名>
#查询视图与查询表一致

2. 注意项
   视图与基本表的数据行必须一对一才可进行更新

触发器

1. 使用触发器

#查看触发器
show triggers
#创建触发器(old为旧数据,new为新数据)
create trigger <触发器名> before|after insert|update|delete on <表名> for each row
begin <触发语句> end
#删除触发器
drop trigger <触发器名>

存储过程

1. 使用存储过程

#查看存储过程
show create procedure <存储过程名>
#创建存储过程(in为输入参数,out为输出参数)
create procedure <存储过程名>([in|out] <参数> <参数类型>,...)
begin <SQL语句> end
#调用存储过程
call <存储过程名>(<参数>,...)
#删除存储过程
drop procedure [if exists] <存储过程名>

2. 变量

#定义用户变量
set @<变量名>=<变量值>
#查看用户变量
select @<变量名>
#定义局部变量
declare <变量名> <变量类型> [default <默认值>]
#修改局部变量
set <变量名>=<变量值>

3. 流程控制

#判断
if <表达式> then <SQL语句>
elseif <表达式> then <SQL语句>
else <SQL语句>
end if
#判断
case <变量名>
when <变量值> then <SQL语句>
else <SQL语句>
end case
#循环(leave终止,iterate跳过)
while <表达式> do
<SQL语句>
end while

4. 游标(使用查询结果集)

#定义游标
declare <游标名> cursor for <查询语句>
#开启游标
open <游标名>
#获取记录(只能顺序获取)
fetch <游标名> into <变量名>
#关闭游标
close <游标名>

5. 异常处理

#定义异常处理
declare continue|exit handler for sqlstate <状态值> <处理语句>

6. 存储函数

#创建函数
create function <函数名>(<参数> <参数类型>,...) returns <数据类型> deterministic|no sql|reads sql data
#调用函数
select <函数名>

锁

1. 使用锁

#全局锁
flush tables with read lock
#表锁(read只能读,write只有自己能读写)
lock tables <表名> read|write
#行锁由引擎自动添加