1 MySQL初级
1 基础
- RDNMS:关系数据库管理系统
- 数据以表格形式出现
- 每行为各种记录
- 每列为记录名称所对应的数据域
- 行和列构成表
- 表构成database
- 术语:
- 主键:唯一的,使用主键可以快速定位到行
- 外键
- 复合键:将多个列作为一个索引键,一般用于复合索引
- 关系型数据库都支持SQL语言
- 语句分类:
- DQL:数据查询语言,用于对数据进行查询,如select
- DML:数据操作语言,对数据进行增加、修改、删除,如insert、udpate、delete
- TPL:事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
- DCL:数据控制语言,进行授权与权限回收,如grant、revoke
- DDL:数据定义语言,进行数据库、表的管理等,如create、drop
- CCL:指针控制语言,通过控制指针完成表的操作,如declare cursor
2 数据库完整性
- 约束:
- 主键primary key:物理上存储的顺序
- 非空not null:字段不允许为空
- 唯一unique:字段的值不允许重复
- 默认default:当不填写此值时会使用默认值
- 外键foreign key:对关系字段进行约束,当关系字段对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
- 说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
3 数据类型
- 数据类型:
- 整数:int,bit
- 小数:decimal
- 字符串:varchar,char
- 日期时间:data,time,datatime
- 枚举类型:enum
- 特别说明:
- decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
- char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ’
- varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’
- 字符串text表示存储大文本,当字符大于4000时推荐使用
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
类型 | 字节大小 | 有符号范围 | 无符号范围 |
---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
字符串
类型 | 字节大小 | 示例 |
---|---|---|
CHAR | 0-255 | 类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’ |
VARCHAR | 0-255 | 类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’ |
TEXT | 0-65535 | 大文本 |
日期时间
类型 | 字节大小 | 示例 |
---|---|---|
DATE | 4 | ‘2020-01-01’ |
TIME | 3 | ‘12:29:59’ |
DATETIME | 8 | ‘2020-01-01 12:29:59’ |
YEAR | 1 | ‘2017’ |
TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC |
4 sql命令
1、Mysql指令
-
备份
- mysqldump -uroot -p 数据库名 > python.sql;
-
恢复
- mysql -uroot -p 新数据库名< python.sql
-
启动Mysql
- net start mysql :启动mysql服务
- mysql -h 主机名 -p 端口号 -u UserName -p PASSWARD :打开mysql客户端
-
关闭Mysql
- net stop mysql:关闭mysql服务
- exit:在客户端内退出
2、数据库级别操作
-
创建数据库
- create database if not exixts dbName
- create database 数据库名 charset=utf8
-
删除数据库:
- drop database if exists dbName
- drop database
-
查看数据库:show databases
-
使用数据库:use DBName
-
查看表:
- show tables
- show tables from DBName
- desc tableName:查看表结构
-
查看当前数据库名称:select database()
-
查看当前用户:select user()
-
设置指定数据的字符集:alter database DbName character set utf8
-
查看版本:select version()
-
显示时间:select now()
-
修改输入提示符:prompt python>
-
查看数据库的字符集
- show variables like ‘%character%’;
- show variables like ‘%char’;
- shwo variables like ‘%collation%’;
-
查看数据库引擎:show engines;
3、数据表级别操作
- 表约束
- NOT NULL:非空约束
- DEFAULT:默认,用于保证该字段有默认值
- PRIMARY KEY:主键约束
- UNIQOE:唯一约束
- CHENCK:检查约束
- FOREIGN KEY:外键约束
- create table tbName(
name type …;
…
) - 修改表-添加字段:
- alter table 表名 add 列名 类型
- 修改字段-重命名属性:
- alter table 表名 change 原名 新名 类型及约束
- 修改字段-不重命名:
- alter table 表名 modify 列名 类型及约束
- 删除字段
- alter table 表名 drop 列名
- 删除表:
- drop table 表名
- 查看表的创建语句:
- show create table 表名
4、表内级别增删改查curd
查询
-
select * from 表名
-
select 列1,列2… from 表名
关键字 -
distinct(去重):select distinct fieldName from tableName
-
limit(分页查询)
- select * from tableName limit start,count;
- start:跳过数据条数
- count:每页数据条数
- select * from studentss where gender=1 limit 3,5:检索数据4-9
- select * from tableName limit start,count;
-
offset:跳过多少条数据
- select * from tableName limit num offset point:跳过point条数据选取num条数据
-
union和union all(联合查询)
- 内连接查询:查询的结果为两个表匹配到的数据
select s.name,c.name from student s inner join class c on s.classId = c.classId
- 右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
select s.name,c.name from student s right join class c on s.classId = c.classId
- 左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
select s.name,c.name from student s left join class c on s.classId = c.classId
- union连接查询:
- SQL语句1 union all SQL语句2:取所有数据,记录可能重复
- SQL语句1 union SQL语句2:取所有值但其中数据不会重复
- 内连接查询:查询的结果为两个表匹配到的数据
-
like(模糊查询)
- %:匹配任意个字符
- _:匹配一个任意字符
-
where、between、in、or、and
-
order by
- select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]
- 默认按照列值从小到大排列(asc)
- asc从小到大排列,即升序
- desc从大到小排序,即降序
- select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]
-
group by
1、group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
2、group by可用于单个字段分组,也可用于多个字段分组group by + group_concat()
1、group_concat(字段名)可以作为一个输出字段来使用,
2、表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合group by+集合函数
1、通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个值的集合做一些操作group by + having
1、having 条件表达式:用来分组查询后指定一些条件来输出查询结果
2、having作用和where一样,但having只能用于group bygroup by + with rollup
with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和 -
having
-
case
-
聚合
总数:select count(\*) from students;
最大值:select max(id) from students where gender=2;
最小值:select min(id) from students where is_delete=0;
求和:
select sum(age) from students where gender=1;
select sum(age)/count(\*) from students where gender=1;
平均值:select avg(id) from students where is_delete=0 and gender=2;
-
子查询
- 分类:
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 返回的结果是一列(一列多行)
- 行子查询: 返回的结果是一行(一行多列)
查询还有学生在班的所有班级名字:
select name from classes where id in (select cls_id from students);
- 分类:
插入
- insert into 表名 values (…) [(value2)…]
- insert into 表名 (列1,列2…) values ()
更新
- update 表名 set 列1=值1,… where 条件
删除
- delete from 表名 where 条件
5 mysql函数
字符串函数
- concat(string,string,…):拼接字符串
select concat(upper(s_name),lower(s_name)) from student s
- substr(string,start,lenth):截取字符串,索引从1开始
- instr(string,subString):获取子串第一个出现的位置
- lpad(string,length,char):左边以指定字符填充到指定长度
- rpad(string,length,char):右边以指定字符填充到指定长度
- upper(string):转换为大写
- lower(string):转换为小写
- replace(string,subString,replaceString):替换函数
- length(string):获取字节长度
- trim(string):去掉字符串前后空格
日期函数
-
now():返回当前系统日期+时间
-
year(日期):返回当前系统日期
- year(now())
- year(‘2020-1-1’)
- year(fieldOfDate) from table;
-
month():与year一样
-
monthname():与year一样
-
day()
-
hour()
-
mimute()
-
second()
-
datediff(date1,date2):返回两个日期相差的天数date1-date2
-
date_format():将日期转换为字符串
-
str_to_date(stringOfDate,format):将字符串转换为日期
- %Y:4位的年份
- %y:2位的年份
- %m:2位的月份
- %c:1位的月份
- %d:日
- %H:24小时制的小时
- %h:12小时制的小时
- %i:分钟
- %s:秒
- (’%m-%d-%Y’)
-
curdate():返回当前系统日期,年-月-日,不包含时间
-
curtime():返回当前系统日期,时:分:秒,不包含日期
数学函数
- ceil()
- floor()
- round()
- rand():随机0-1之间的小数
- mod()
- truncate(num,length):截取length为小数
6 视图和索引
视图
- 通俗的讲,视图就是一条SELECT语句执行后返回的结果集
- 定义视图
create view 视图名称[新字段名1,新字段名2,...] as select语句
create view ss(id,name,class) as select * from student
- 查看表会将所有的视图也列出来
- 使用视图:将视图当成表使用查询
- 删除视图:drop view 视图名称
- 视图的作用:
1、提高了重用性,就像一个函数
2、对数据库重构,却不影响程序的运行
3、提高了安全性能,可以对不同的用户
4、让数据更加清晰
索引
- 数据量非常大时使用
- 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针
- 更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度
- 查看索引:show index from 表名
- 创建索引:
- 如果指定字段是字符串,需要指定长度,建议长度与定义字段时的长度一致
- 字段类型如果不是字符串,可以不填写长度部分
- create index 索引名称 on 表名(字段名称(长度))
- 删除索引:drop index 索引名称 on 表名
- 建立太多索引会影响更新和插入的速度,因为需要更新每个索引文件,会占用磁盘空间
7、事务
事务四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小工作单元,要么完全执行,要么全部不做
- 一致性(Consistency):从一个一致性的状态转换为另一个状态
- 强一致性:读操作可以立即读到提交的更新操作
- 弱一致性:提交的更新操作,不一定立即会被读操作读到,此种情况会存在一个不一致窗口,指的是读操作可以读到最新值的一段时间
- 最终一致性:是弱一致性的特例。事务更新一份数据,最终一致性保证在没有其他事务更新同样的值的话,最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生,不一致窗口的大小依赖于:通信延迟,系统负载等
- 隔离性(Isolation):在提交之前对其他事务不可见
- 持久性(Durability):一旦提交,永久保存
事务命令
- SQL事务:
- start transaction;
… - commit;
- start transaction;
- 命令:
- begin或者start transaction开始事务
- commit提交事务
- rollback回滚事务
- 注意:
- 修改数据的命令会自动的触发事务,包括insert、update、delete
- 而在SQL语句中有手动开启事务的原因是:可以进行多次数据的修改,如果成功一起成功,否则一起会滚到之前的数据
并发事务存在的问题
- 脏读:A事务正在访问B数据并进行修改但没有提交,C事务准备读取数据,那么此时读到的(未提交到数据库的)数据就是“脏数据”,A事务可能会回滚之类的操作
- 丢失修改:A事务和C事务同时读取B数据,A事务修改数据后,C可能也进行修改,此时A的修改会被覆盖
- A读取B为20,C读取B为20,A修改后B为B=B-1=19,但是C修改后为B=B-1=19,理论上最终结果应该为18,但因为丢失修改,A的操作被覆盖,所以此时数据会异常
- 不可重复读:同一个事务内多次读取同一数据,在多次读取之间,可能存在第二个事务对数据进行修改,导致多次读取的数据不一致
- 幻读:同一个事务多次读取同一数据之间,可能存在第二个事务插入了其他数据导致多次读取数据的数量不同
事务隔离级别
- READ-UNCOMMITTED(读取未提交):最低隔离级别,允许读取未提交的数据,可能导致脏读、幻读、不可重复读
- READ-COMMITTED(读取已提交):允许读取并发事务已经提交的数据,可以避免脏读,但是无法避免幻读、不可重复度
- REPEATABLE-READ(可重复读):对同一字段的多次读取结果一致,除非数据被本身事务所修改,可以阻止脏读和不可重复读,但是可能存在幻读
- SERIALIZABLE(可串行化):最高隔离级别,完全服从ACID隔离级别,所有事务依次逐个执行,事务之间互不干扰
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ-UNICOMMITTED | √ | √ | √ |
READ-COMMITTED | x | √ | √ |
REPEATABLE-READ | x | x | √ |
SERIALIZABLE | x | x | x |
- MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABL-READ
- select @@tx_isolation:查看隔离级别
8、账户管理
-
账户分类:
- 服务实例级账号:启动了一个mysqld,即为一个数据库实例;如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
-
账户操作需要使用root账户登录
命令: -
查看所有用户:select host,user,authentication_string from user;
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
-
查看表的结构 desc user;
-
用户信息存储在mysql数据库的user表中
创建账户、授权
- 需要使用root用户
- 权限:create、alter、drop、insert、update、delete、select
- 如果分配所有权限可以使用all privileges
- 创建用户:CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
- 授权:grant 权限列表 on 数据库.表名 to ‘用户名’@‘访问主机’ ;
- 访问主机通常使用百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
- 查看权限:show grants for laowang@localhost;
- 修改权限:
- grant 权限名称 on 数据库 to 账户@主机 with grant option;
- flush privileges;
- 修改密码:
- update user set authentication_string=password(‘新密码’) where user=‘用户名’;
- 注册完后刷新权限flush privileges
- 删除账户
- 1、drop user ‘用户名’@‘主机’;
- 2、delete from user where user=‘用户名’
- 操作结束后刷新权限
9 性能检测
- set profiling=1;开启运行时间监测
- show profiles;查看执行时间
- explain select 语句:查看select执行相关信息
10 数据库设计
三范式
- 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列
考虑这样一个表:[联系人](姓名,性别,电话) 如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。1NF 很好辨别,但是 2NF 和 3NF 就容易搞混淆。 - 第二范式(2NF):首先是 1NF,另外包含两部分内容:一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分
考虑一个订单明细表:
[OrderDetail](OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)
因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把[OrderDetail]表拆分为[OrderDetail](OrderID,ProductID,Discount,Quantity)和[Product](ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况 - 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况
E-R模型
- E表示entry,实体,设计实体就像定义一个类一样,指定从哪些方面描述对象,一个实体转换为数据库中的一个表
- R表示relationship,关系,关系描述两个实体之间的对应规则,关系的类型包括包括一对一、一对多、多对多
- 关系也是一种数据,需要通过一个字段存储在表中
- 实体A对实体B为1对1,则在表A或表B中创建一个字段,存储另一个表的主键值
- 实体A对实体B为1对多:在表B中创建一个字段,存储表A的主键值
- 实体A对实体B为多对多:新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值
逻辑删除:
- 对于重要数据,并不希望物理删除,一旦删除,数据无法找回
- 删除方案:设置isDelete的列,类型为bit,表示逻辑删除,默认值为0
- 对于非重要数据,可以进行物理删除
- 数据的重要性,要根据实际开发决定
11 乐观锁与悲观锁
- 悲观锁:每次拿数据都认为别人会修改,所以每次都会上锁,此时会阻塞其他线程,用完数据后释放锁,把资源转让给其他线程
- 乐观锁:不会上锁,在更新的时候会判断是否被修改过,可以使用版本号机制和CAS算法实现
- 多用于多读的应用类型,可以提高吞吐率
版本号机制
- 在数据表中添加一个数据版本号的version字段,表示数据被修改的次数
- 数据被修改时,version加一
- 提交更新时,若刚才读取的version值为当前数据库中的version值时才进行更新操作,否则重试更新操作
MVCC
- 全称是Multi-Version Concurrent Control,即多版本并发控制,在MVCC协议下,每个读操作会看到一个一致性的snapshot,并且可以实现非阻塞的读。MVCC允许数据具有多个版本,这个版本可以是时间戳或者是全局递增的事务ID,在同一个时间点,不同的事务看到的数据是不同的
- MySQL的innodb引擎是如何实现MVCC:
- innodb会为每一行添加两个字段,分别表示该行创建的版本和删除的版本,填入的是事务的版本号,
- 这个版本号随着事务的创建不断递增。在repeated read的隔离级别下,具体各种数据库操作的实现:
- select:满足以下两个条件innodb会返回该行数据:
- 该行的创建版本号小于等于当前版本号,用于保证在select操作之前所有的操作已经执行落地
- 该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了
- insert:将新插入的行的创建版本号设置为当前系统的版本号
- delete:将要删除的行的删除版本号设置为当前系统的版本号
- update:不执行原地update,而是转换成insert + delete。将旧行的删除版本号设置为当前版本号,并将新行insert同时设置创建版本号为当前版本号
- 由于旧数据并不真正的删除,所以必须对这些数据进行清理,innodb会开启一个后台线程执行清理工作,具体的规则是将删除版本号小于当前系统版本的行删除,这个过程叫做purge
CAS算法
- compare and swap(比较与交换),在不用锁的情况下实现多线程之间的变量同步,因为没有线程被阻塞,因此也叫非阻塞同步
- CAS涉及的操作数
- 需要读写的值V
- 进行比较的值A
- 拟写入的新值B
- 仅当V等于A时,CAS通过原子方式来更新V的值
乐观锁缺点
- ABA问题:在进行比较的时候,检查的值为A,但期间可能有两次修改,使得最后仍然为A
- 循环时间长,开销大
- 自旋CAS,即在不成功的情况下不断循环执行直到成功,如果长时间不成功,则会给CPU带来非常的执行开销
- 只能保证一个共享变量的原子操作
锁机制
-
表级锁:MySQL中粒度最大的一种锁,对当前操作的整张表进行加锁,实现简单、资源消耗比较少、加锁快,不会出现死锁。锁定粒度大,触发锁冲突的概率最高,并发度最低
-
行级锁:粒度最小的一种锁,只针对当前操作的行进行加锁,行级锁能大大减少数据库操作的冲突,其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁
-
InnoDB存储引擎的锁算法
- Record lock:单个记录上的锁
- Gap lock:间隙锁,锁定一个范围,不包括记录本身
- Next-key lock:record+gap锁定一个范围,包含记录本身