数据库-MySQL

2021.08.15

1. MySQL数据类型和选择

（1）数值

（2）日期/时间

（3）字符串类型

 举例：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据类型选择：

日期：选择按照格式

数值/字符串：按照大小

2.0 数据库的增删改查

insert into 表名 (key1,key2) values('1', '2');
delete from 表名 where 条件；
update 表名 set 字段='1' where 条件；
select * from 表名 where 条件；

2.5 MySQL索引

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

2.5.1 Mysql索引类型

主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

2.5.2 索引种类

• 普通索引（index）：仅加速查询

• 唯一索引（unique）：加速查询 + 列值唯一（可以有null）

• 主键索引（primary key）：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引（FULLTEXT）：对文本的内容进行分词，进行搜索

--创建普通索引
CREATE INDEX index_name ON table_name(col_name);
--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

 --查看索引
show index from table_name;

补充：

• 索引合并，使用多个单列索引组合搜索
• 覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

2.5.3 创建索引的时机

在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引

因为在以通配符%和_开头作查询时，MySQL不会使用索引

SELECT t.Name FROM t 
LEFT JOIN m ON t.Name=m.username 
WHERE m.age=20 AND m.city= '郑州';
--此时就需要对city和age建立索引，由于m表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

2.5.4 命中索引

--like  '%xx'     
select * from tb1 where name like  '%cn' ;
-- 使用函数
select * from tb1 where reverse(name) =  'wupeiqi' ;
-- or 
select * from tb1 where nid = 1  or  email =  'seven@live.com' ;

--特别的：当 or 条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1  or  name =  'seven' ;
select * from tb1 where nid = 1  or  email =  'seven@live.com'  and  name =  'alex' 

--类型不一致
--如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
- !=    select * from tb1 where name !=  'alex'

特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >    select * from tb1 where name >  'alex'     

特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
 select * from tb1 where num > 123- order by    select email from tb1 order by name desc;

当根据索引排序时候，选择的映射如果不是索引，则不走索引

特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc; - 组合索引最左前缀

--如果组合索引为：(name,email)
-- name  and  email：使用索引
-- name：使用索引
-- email：不使用索引


-- LIMIT分页
--下一页
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);
--上一页
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);
--当前页之后的某一页
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);
--当前页之前的某一页
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;

3. MySQL建表约束（Constraints）

共7种：primary key、unique、auto increasement、not null、default、foreign、check

（1.1）主键约束（primary key）-单个主键

唯一确定一张表中的一条记录，使得字段不重复且不为空null。

create table user(
id int primary key,
name varchar(20)
);

——修改表结构，添加主键add

——修改表结构，删除主键drop

——修改表结构，添加主键modify

create table user4(
id int,
name varchar(20)
)

alter table user4 add primary key(id);
alter table user4 drop primary key;
alter table user4 modify id int primary key;

（1.2）主键约束（primary key）-联合主键

多个联合的主键值同时不重复就可以。联合起来唯一；任何一个字段都不能为空null。

create table user2(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);

（2）唯一约束（unique）-单个/联合

约束修饰的字段的值不可重复，可为null

unique(a,b)：两个键在一起不重复

create table user5(
id in,
name varchar(20) unique
);

create table user5(
id in,
name varchar(20),
unique(name)
);


alter table user6 add unique(name);
alter table user6 modify name varchar(20) unique;
alter table user6 drop index name;

总结：

1、建表的时候添加约束

2、使用alter .. add .. 或者 alter .. modify .. 进行添加

3、使用alter .. drop .. 进行删除

 （3）自增约束（auto_increment）

管控字段，值自动递增

create table user3(
id int primary key auto_increment,
name varchar(20)
);

（4）非空约束（Not Null 大小写皆可）

修饰的字段不能为空 null

create table user8(
id int,
name varchar(20) NOT NULL
);

（5）默认约束（default）

当插入字段值时，如果没有传值，就会使用默认值

 create table user8(
id int,
name varchar(10),
age int default 10
);

（6）外键约束（foreign）

涉及两个表：父表、子表（或 主表、副表）

1、主表中没有的数据值，副表中不可以使用存在

2、主表中的记录被副表引用，是不可以删除的

-- 班级
create table class(
id int primary key,
name varchar(20)
);


-- 学生
create table student(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references class(id)
);

（7）CHECK - 保证列中的值符合指定的条件

3. 数据库的三大设计范式

3.1 第一范式（1NF）-拆字段

数据表中的所有字段都是不可分割的原子值

范式：设计的越详细，对于某些实际操作可能更好，但不一定都是好处。

address拆字段为country、Provence、city、area

3.2 第二范式（2NF）-拆表

必须满足第一范式的前提下，第二范式要求除主键外的每一列必须完全依赖于主键

如果出现不完全依赖，只可能是联合主键的情况。

 3.3 第三范式（3NF）-拆表

必须满足第二范式的前提下，第三范式要求除主键外的其他列之间不能有传递依赖关系。

 4. mysql的查询

--查询
select * from user;
select id, name, age from user;
--去重 distinct
select distinct name from user;
--区间 between或者运算符
select * from user where score between 60 and 80;
select * from user where score > 60 and score < 80;
--字段值 或者关系 in
select * from user where score in(50,60,80);
--字段 或者关系 or
select * from user where score id > 10 or sex="female";
--排序 降序desc 升序asc（默认，可不写）
select * from user order by id desc;
select * from user order by id asc;
select * from user order by id asc,score desc; --优先以id排序，其次是score
--统计 count
select count(*) from user where class='1';
--最值 最高max 最低min
select * from user where score=(select max(score) from class);
--取有限条数 limit n,m（指从第n条开始取m条数据）
select * from user order by desc limit 0,1;
--平均值 avg()
--分组 group by
select id, avg(score) from user where id = 1;
select id, avg(score) from user group by id;
--先分组后加条件用having，先加条件后分组用where
--模糊匹配 like
select class,avg(score),count(*) from user 
group by class
having count(class) >= 2 
and class like '3%';
--多表查询 =（共同字段建立联系）
select uid,name,class from user,class
where user.uid = class.uid;
select uid,name,class,score from user,class,score
where user.uid = class.uid and user.course = score.course
--获取年份 year()内置函数
--子查询 in（子查询）
select * from user where year(birthday) in (selecet * from user where uid > 10)


B站P33-P56未看

5. MySQL事务

5.1 事务定义和作用

mysql中，事务是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性 

多条SQL语句，要求要么同时成功，要么同时失败，否则会数据前后出现不一致。

5.2 控制事务commit_rollback

（1）MySQL默认事务开启（即可自动提交commit）

事务开启commit：作用是执行一个SQL时，效果会立即体现且不能回滚（自动提交和手动提交）

事务回滚rollback：撤销SQL语句执行效果，提供一个反悔的机会

1表示事务开启，0表示事务关闭。

（2）关闭MySQL的自动提交事务（set autocommit=0），当前事务在提交时结束

需要手动提交数据（commit;），提交前可回滚，提交后不可回滚（rollback;）

 

 回滚成功：

 回滚失败：

 

（3）语句手动开启一个事务 (begin; 或  start transaction;)

 5.3 事务的特征（ACID）

A  atomicity 原子性：事务是最小的工作单元，不可再分割。同时成功或者同时失败

C consistency 一致性：事务在执行前后，数据的完整性保持不变（例：原本的外键约束在进行事务成功后不会损坏）

I isolation 隔离性：事务1和事务2之间具有隔离性

D durability 持久性：事务一旦结束（commit 或 rollback），就不可以返回

 5.4 事务的隔离性

查看数据库的隔离级别 

 修改数据库的隔离级别  

 

（1）read uncommitted; 读未提交的（默认隔离级别）

如果有事务A和事务B，A事务对数据进行操作，操作过程中事务没有被提交时，事务B可以看见A操作的结果。会出现“脏读”，实际开发不允许出现。

“脏读”：一个事务读到了另一个事务没有提交的数据。

（2）read committed; 读已提交的

虽然只能读到另一个事务提交的数据，但还会出现“不可重复读”现象

“不可重复读”现象：读取同一个表的数据，发现前后不一致的问题

（3）repeatable read; 可重复读

“幻读”：事务A和事务B同时操作一张表，事务A提交的数据，不能被事务B读到，就会造成幻读。

（4）serializable; 串行化

当表被事务A操作的时候，其他事务里面的写操作不可以进行。即进入排队状态（串行化），事务A提交完成后，其他事物的写操作才会执行（在没有等待超时的情况）

总结：

性能正向排序（即隔离级别倒序）：

read uncommitted（啥问题都出现，脏读） > read committed（不可重复读） > repetable read(mysql默认隔离级别，幻读) > serializable（性能差）

1-4 章节：B站学习视频

2.5 章节：什么是索引？Mysql目前主要的几种索引类型