所有带 [ ] 表示可选
字段名用` `,属性用’ '
1. 操作数据库
1. 操作数据库(了解)
DDL 定义数据库
DML 操作数据库
DQL 查询数据库
DCL 控制数据库
create database [if not exist] xx; [如果不存在]创建xx数据库
drop database [if not exist] xx; [如果不存在]删除xx数据库
use `xx`; 切换到xx数据库
show databases; 查看所有数据库
show table; 查看数据库所有表
2. 数据类型
数值
- int 常用整数 4字节
- double 常用浮点数 8字节
- decimal 字符串形式浮点数,无精度损失,金融计算用
字符串
- varchar 可变字符串 0~65535
- text 保存大文本 2^16-1
时间日期
- date yyyy-mm-dd 日期格式
- time hh:mm:ss 时间格式
- datetime date+time 最常用时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
3. 数据库的字段属性(重点)
unsigned:
- 无符号的整数
- 声名不能为负数
zero fill:
- 0填充
- 不足位数,用0填充
auto_increament:
- 自增(默认+1)
- 设计唯一的主键
- 必须整数
- 可自定义起始值和增量
not null:
- 必须赋值
- 可以为空值(null占空间,空值不占)
- 拓展:两个null比较结果还是null
default:
- 设置默认值
- 不设置值有默认值
comment:
- 对列进行备注
项目表必备字段:
- id:主键
- `version`:乐观锁
- is_delete:伪删除
- gmt_create:创建时间
- gmt_update:修改时间
4. 数据库表格式
create table [if not exists] `xx`(
'字段名' 列类型 [属性][索引][注释],
...
'字段名' 列类型 [属性][索引][注释]
[主键设置]
)[表类型][字符集设置][注释]
常用语句
show create database xx -- 查看创建xx数据库的语句
show create table xx -- 查看xx数据表的定义语句
describe xx -- 显示表结构
5. 数据表的类型
innodb 默认使用
myisam 早些年使用
myisam | innodb | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,2倍 |
常规使用操作:
- myisam 节约空间,速度较快
- innodb 安全性高,支持事务处理,支持多表多用户操作
物理空间存在位置:data目录下,本质还是文件的存储
mysql引擎在物理文件上的区别
- innodb在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- myisam:
- *.frm 表结构的定义文件
- *.myd 数据文件(data)
- *.myi 索引文件(index)
设置数据库表的字符集编码:charset=utf8
6. 修改删除表
-- 修改表名
alter table 表名1 rename as 表名2
-- 增加表的字段
alter table 表名 add 字段名 字段类型
-- 修改表的字段
alter table 表名 modify 字段名 字段类型 -- 只能修改约束
alter table 表名 change 字段名1 字段名2 字段类型 -- 重命名,还能修改类型
-- 删除表的字段
alter table 表名 drop 字段名
-- 删除表
drop table if exists 表名
2. MySQL数据管理
1. 外键(了解)
方式一:创建表的时候增加外键(麻烦)
-
定义外键key
-
给外键添加约束,然后引用
key `约束名`(`作为外键的列`)
constraint `约束名` foreign key (`作为外键的列`) references `引用表`(`引用表字段`)
删除有外键关系的表,必须先删除引用别人的表(从表),再删除被引用的表(主表)
方式二:创建表之后增加字段
alter table `表名`
constraint `约束名` foreign key (`作为外键的列`) references `引用表`(`引用表字段`)
以上操作都是物理外键,数据库级别外键,不建议使用
2. DML语言
数据库意义:数据存储,数据管理
dml语言:数据操作语言
- insert
- update
- delete
3. 添加
不写表字段,值会一一匹配
insert into `表名`([`字段名1`,`字段名2`...]) value ('值1','值2'...),('值3','值4'...)
4. 修改
不指定条件,会改变所有表!!!!
update `表名` set `字段名1`='修改后的值',`字段名2`='修改后的值' where 条件
=, <>(!=), >, <, >=, <=,
between…and 在某个范围内
and 和
or 或
5. 删除
不指定条件,会改删除所有表!!!!
delete from `表名` where 条件
完全清空一个数据库表,表结构索引不改变
truncate `表名`
delete和truncate:
- 相同点:都能删除数据,但不删除表结构
- 不同:
- truncate 重新设置自增列,计数器归零
- truncate 不会影响事务
【了解即可】delete删除问题,重启数据库,现象:
- innodb 自增列会从1开始(存在内存中,断电即失)
- myisam 继续从上一个自增量开始(存在文件中,不会丢失)
3. DQL查询数据(重点)
1. DQL
- 所有查询都用select
- 简单,复杂查询都能做
- 数据库核心语言,最重要的语句
- 使用频率最高的数据
select完整语法:
select
from
[join] [on] -- 联合查询
[where] -- 条件
[group by] -- 按字段分组
[having] -- 对分组后的数据筛选
[order by] -- 排序
[limit] -- 分页
2. 指定查询字段
-- 查询表全部数据
select * from 表名
-- 查询指定字段
select 字段名,字段名 from 表名
-- 起别名(表和字段都可以)
select 字段名1 as 别名1,字段名2 as 别名
from 表名 as 别名
-- 函数 concat(a,b)
select concat('姓名',name) as 新名字
from 表名
去重:distinct
取出查询结果中重复的数据,只显示一条
select distinct 字段名 from 表名
select version() -- 查版本
select 100*3 -- 计算
select @@auto_inrement_increment -- 查询自增步长(变量)
select `num`+1 from 表名 -- 查询数据处理
3. where条件字句
逻辑运算符:
- and &&:逻辑与
- or ||:逻辑或
- not !:逻辑非
模糊查询:比较运算符
- is null:如果操作符为null,结果为true
- is not null:若果操作符为not null,结果为true
- between and:在两者之间,结果为true
- like:sql匹配,匹配到为true
- %:代表0到任意个字符
- _:代表一个字符
- like ‘a%z_’:匹配第一个字母为a,倒数第二个字母为z的字符,只能在like中用!
- in (…):操作符是数组中的一个,结果为true
4. 联表查询 JOIN
3种join
- inner join:只返回两表都匹配的行
- left join:从左表返回所有值,即使右表中没有匹配
- right join:从右表中返回所有的值,即使左表中没有
on和where
- 在多表查询时,ON和where都表示筛选条件;on先执行,where后执行
- 外连接时:
- on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录
- where条件是在临时表生成好后,再对临时表进行过滤的条件。
语法:
select b1.字段1, b2.字段2 ...
from 表1 as b1
inner join 表2 as b2
on 条件
left join 表3 as b3
on 条件
right join 表4 as b4
on 条件
自连接:
自己的表和自己的表连接
select a.字段1, b.字段2
from 表1 as a, 表1 as b
where 条件
5. 分页和排序
排序:order by
- 升序:asc
- 降序:desc
select *
from 表
order by 字段 asc -- 升序或降序
分页:limit a, b (a:起始位置,b:条数)
第n页:limit (n-1)*pageSize, pageSize
select *
from 表
limit 0,2 -- 第一条数据开始,总共查2条
6. 子查询
本质:在where语句中嵌套一个查询语句
原本的查询:
select *
from 表1 b1
inner join 表2 b2
on 条件
where 条件
order by 字段 desc
子查询
select *
from 表1
where 字段1 = (
select 字段1
from 表2
where 条件
)
order by 字段 desc
7. 分组和过滤
select *
from 表1
left join 表2
on 条件
where 条件
group by 字段 -- 按字段分组
having 条件 -- 对分组后的数据筛选
4. MySQL函数
1. 常用函数
-- 数学运算
select abs(-8) -- 绝对值 8
select ceiling(9.4) -- 向上取整 10
select floor(9.4) -- 向下取整 9
select rand() -- 返回一个0-1的随机数
select sing(10) -- 判断一个数的符号 -1/0/1
-- 字符串函数
select char_length('asdsd') -- 字符串长度 5
select concat('i','love','u') -- 拼接字符串 'iloveu'
select insert('1234',2,2,'abc') -- 替换 '1abc4'
select lower('Abc') -- 转小写字母 'abc'
select upper('Abc') -- 转大写字母 'ABC'
select instr('abcd','c') -- 返回第一次出现字符串的索引 3
select substr('abc',1,2) -- 返回指定字符串 'ab'
select replace('1234','12','a') -- 替换出现的指定字符串 'a34'
select reverse('123') -- 反转 '321'
-- 时间和日期函数(重点)
select now() -- 获取当前日期
select localtime() -- 获取本地时间
select sysdate() -- 系统时间
select year(now()) -- 获取年
...
-- 系统
select user() -- 获取当前用户
select version() -- 获取版本
2. 聚合函数(重点)
函数名称 | 描述 |
---|---|
count() | 统计行数 |
sun() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
select count(字段) from 表 -- 会忽略所有null
select count(*) from 表 -- 不会忽略null,本质:计算行数
select count(1) from 表 -- 不会忽略null,本质:计算行数
3. 数据库级别的md5加密(扩展)
算法复杂度高
不可逆性
不防碰撞
update `user` set password=md5(password) -- 密码全部加密
-- 用户传递的密码进行md5加密,然后比对加密后的值
select * from `user` where `name`='name' and password=md5(`123`)
5. 事务
事务:将一组sql放在一个批次中去执行
ACID:
- 原子性
- 要么都成功,要么都失败
- 一致性
- 事务前后的数据完整性一致
- 隔离性
- 隔离多个用户对同一数据的操作
- 持久性
- 断电事务没有提交,恢复到原状
- 断电事务提交,持久化数据
隔离所导致的问题:
- **脏读:**一个事务读取了另一个事务没有提交的诗句
- **不可重复读:**一个事务内读取某个数据,多次读取结果不同(不一定错误,至少是某些场合不对)
- 幻读::一个事务内读取到了别的事务插入的数据,导致读取前后不一致
mysql是默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启
自定义事务:
set autocommit = 0 -- 关闭事务自动提交
start transaction -- 开启事务
insert 数据
update 数据
...
commit -- 提交:持久化(成功)
rollback -- 回滚:回到事务前的样子(失败)
set autocommit = 1 -- 结束后恢复默认
-- 了解
savepoint -- 设置一个事务的保存点
rollback to savepoint -- 回滚到保存点
release savepoint -- 撤销保存点
事务的隔离级别:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read-uncommitted(读取未提交) | √ | √ | √ |
reda-conmitted(读取提交) | × | √ | √ |
repeatable-read(可重复读) | × | × | √ |
serializable(可串行化) | × | × | × |
MySQL innodb 默认隔离级别是repeatable-read(可重复读)
6. 索引
索引是帮助MySQL高效获取数据的数据结构。
索引是数据结构
1. 索引的分类
- 主键索引(primary key)
- 唯一的表示,主键不可重复
- 不允许空值
- 唯一索引(unique key)
- 该字段列数据不重复,多个列都可以标识为唯一索引
- 允许空值
- 常规索引(key / index)
- 默认
- 全文索引(fullkey)
- 特定数据库引擎才有,myisam
- 快速定位数据
基础语法
-- 1.创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 3.create index 索引名 on 表(列名)
-- 显示所有的索引信息
show index from 表
-- 增加一个全文索引 (索引名)列明=名
alter table 表.列名 add fulltext index `索引名`('列名')
-- explain:分析SQL执行情况
explain select * from 表 -- 非全文索引
explain select * from 表 where match(列名) against('数据') -- 全文索引
2. 测试索引
索引在小数据量的时候,用处不大,但在大数据的时候,用处十分明显
3. 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的列(字段)上
索引的数据结构:
harsh类型的索引
Btree:innodb的默认数据结构
7. 权限管理和备份
1. 用户管理
用户表:mysql.user
本质:对这张表的增删改查
-- 创建用户
create user 用户名 identified by 密码
-- 修改密码(当前用户)
set password = password(新密码)
-- 修改密码(指定用户)
set password for 指定用户 = password(新密码)
-- 用户重命名
rename user 用户名 to 新用户名
-- 删除用户
drop user 用户名
-- 用户授权
-- all privileges,不能给别人授予权限
grant all privileges on 库.表 to 用户
-- 查询指定用户权限
show grant for 用户
show grant for root@localhost
-- 撤销权限
remove all privileges on 库.表 from 用户
2. MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份方式
-
直接拷贝物理文件
-
可视化工具中手动导出
-
使用命令行(不是编辑器)导出 mysqldump
# 导出 mysqldump -h地址 -u用户 -p密码 数据库 表1 表2 表n > 位置(D:/a.sql) # 导入 source D:/a.sql # 提前登录 mysqldump -h地址 -u用户 -p密码 数据库 < 备份文件位置(D:/a.sql) #不建议!!!防止输错导致覆盖
8. 规范数据库设计
1. 为什么需要设计
糟糕的数据库设计:
- 数据库冗余,浪费空间
- 数据库的插入和删除都会麻烦、异常【处理:屏蔽使用物理外键】
- 程序性能差
良好的数据库设计:
- 节省内存空间
- 保证数据的完整性
- 方便我们开发系统
关于数据库的设计:
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图 E-R 图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表 user(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表 category(文章分类,谁创建)
- 文章表 blog(文章的信息)
- 友链表 links(友链信息)
- 标识实体(把需求落到每个字段)
- 标识实体之间的关系
- 写博客:user—>blog
- 创建分类:user–>category
- 关注:user–>user
- 友链:links
- 评论:user–>user–>blog
2. 三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
专有名词:
- 元组:二维表中的行
- 属性:二维表中的列
- 超键:能唯一标识元祖的属性集
- 候选键:不含多余属性的超建
- 主键:用户选作元组标识的候选键
- 外键:对于当前模式而言,是另一模式下的主键。
- 主属性:构成候选键的属性
三大范式:
- 1NF
- 原子性:保证每一列不可再分
- 2NF
- 满足1NF
- 非主属性完全依赖于候选键
- 3NF
- 满足2NF
- 消除非主属性对候选键的传递依赖
- BCNF
- 满足3NF
- 消除每一属性对候选键的传递依赖
**部分依赖:**C可以通过AB得到,并且C也可以仅通过A得到,仅通过B得到, 那么就说C部分依赖AB。
**完全依赖:**C可以通过AB得到,并且C不可以仅通过A得到,也不可以仅通过B得到, 那么就说C完全依赖AB。
**传递依赖:**B可以通过A得到,C可以通过B得到,那么就称C传递依赖A。
规范和性能问题
关联查询的表不得超过三张表(阿里规范)
- 考虑商业化的需求和目标(成本,用户体验),数据库性能更加重要
- 在提高性能的时候,需要适当的考虑下规范性
- 故意给某些表增加一些冗余手段。(多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询)