MySQL解释说明constra_mysql总结笔记（一）

mysql增删改查

#### create

- alter 修改表结构 add/change/drop 列名/字段/属性 类型

change只能修改列的类型，不能修改列的名字

- 添加约束

> alter table scores add constranint stu_score foreign key(stuid) references students(id)

update table set column1=value1,column2=value2,... where

condition

修改数据，修改属性值

delete from table where condition

物理删除

select

原始数据集和结果集，结果集基于原有数据集合过滤提取。

- 查询所有

对列筛选

select * from table

select column1,cloumn2 from table

- 去重

去重某个字段中相同的值

select distinct gender from table

select distinct gender,id from table

- 条件

逐行筛选，逐行判断

where 逐行筛选，并把满足要求的行放到结果集中。

- 比较运算符

< > = !=

- 逻辑运算符

and or not

- 模糊查找

- like

- %表示多个任意字符

- _表示任意一个字符

select * from table where name like "%鹏%"

select * from table where name like "_鹏"

select * from student where name like "黄%" or name like "%靖"

- 范围查询

- in表示在非连续的范围

> select * from where id in(1,3,8)

- between ... and ... 表示一个连续的范围

> select * from where id between 3 and 8

- 空判断

- 注意 null与''是不同的

> null不指向内存 ''内存存储空值

- 判空 is null

> select * from student where birthday is null

- 判断非空 is not null

> select * from student where birthday is not null

- 优先级

> 小括号，not，比较运算符，逻辑运算符

> and 优先于 or

### 聚合函数

> 将现有的多行数据统计，将原始数据集统计成结果集

> mysql提供了常用的5个聚合函数

> count() max(column) min(column) sum(column) avg(column)

> select count(*) from table where isDelete = 1

> 先拿到原始数据集过滤，然后聚合

### 分组

- 分组的目的还是为了聚合，把此字段相同的数据组合在同一个组,更好的进行数据统计

-语法

> select column1,column2,column3 from table group by column1,column2,column3

select gender as 性别,count(*) from table group by gender;

### 分组后的数据筛选

- **语法**：

> select column1,column2,column3 from table group by column1,column2,column3 having column1 = xxx

>having功能相当于where，不同点是having必须加上group by 上；where是对原始数据集筛选，having是对group by分组后的结果集筛选

### 排序

- 为了方便查看数据，可以对数据进行排序。

- 语法：

> select * from table order by column1 asc|desc, column2 asc|desc

> select * from students where isDelete = 0 and gender = 1 order by id desc;

> update subjects set isDelete=1 where title in('linux','redhat')

### 分页

- 当数据量过大时，在一页中查看数据是一件非常麻烦的事情

- 语法

> select * from table_name limit start,count

- 从start开始，获取count条数据

- start索引从0开始

### 示例：实现分页

- 已知：每页显示m条数据，当前显示第n页

- 求总页数：此段逻辑后面也会在python/golang中实现

- 查询总条数p1

- p1/m=p2

- 如果整除则p2为总页数

- 如果不整除则p2+1为总页数

- 求第n页的数据

> select * from students where isDelete = 0 limit (n-1)*m,m

### 总结

- 完整select语句

```

select distinct *

from table_A inner|left|right join table_B on table_A.xxx = table_B.xxx

where ...

group by ... having ...

order by ...

limit start,count

```

- 执行顺序为

```

from table_name

where

group by

select distinct *

having

order by

limit start,count

```

mysql高阶操作

### 简介

- 实体与实体之间有三种对应关系

- 视图用于完成查询语句的封装

- 事务可以保证复杂增删改查有效

- 当数据量巨大时，为了提高查询速度可以通过创建索引实现

### 关系

> 示例 crate 三张表 students score subjects

>成绩表需要引用学生表和科目表

> 关系分为1 to 1 1 to n n to n

> 学生表 1 to 成绩表 n;成绩表 1 to 学生表 1;

> 科目表 1 to 成绩 n;成绩表 1 to 科目表 1；

> so把关系建立在成绩表

### 建立表关系

> 关系也是一种字段，需要要表中创建

> 先设计E-R模型中表结构关系，再创建约束

### 外键约束

>保证数据有效性

>先确定表与表之间是否有关系，再确定时几对几的关系，然后为了确认数据有效性，建立外键约束

```

crate table scores (

id int primary auto_increment,

stuid int

subid int

score decimal(5,2),

foreign key(stuid) references students(id),

foreign key(subid) references subjects(id)

);

```

### 外键的级联操作

- 级联操作的类型

- restrict(限制)：默认值，抛异常

- cascade(级联)：如果主表的记录删除，则从表中相关联的记录会被删除

- set null:将外键设置为空

- no action:什么都不做

### 连接查询

>使用场景：需要用到的信息来源于多张表,找到表与表之间的关系再连接查询

- table_A inner join table_B: table_A 与table_B匹配的行会出现在结果中

- table_A left join table_B:table_A 与table_B匹配的行会出现在结果中,外加外表table_A独有的数据，未对应的数据使用null填充

- table_A right join table_B:table_A 与table_B匹配的行会出现在结果中,外加外表table_B独有的数据，未对应的数据使用null填充

> 三种连接主要的区别是结果集的区别：

```

select students.name,subjects.tile,scores.score

from scores

inner join students on scores.stuid = students.id

inner join subjects on scores.subid = students.id;

```

rename oldname to newname

engine不一样 底层的数据结构不一样

备份和恢复

备份

mysqldump -uroot -p database_name > backup_db.sql

恢复(注意要先创建数据库)

mysqldump -uroot -p database_name < backup_db.sql

### 产品设计

确认有哪些实体，确认实体是几对几的关系，确认在哪个实体中建立哪些字段

#### 示例

- 购物车

- 消费者

- 商品

- 数量

- 价格

- 商品信息

- 名称

- 价格

- 单位

- 日期

...

> 分析对应关系 1个购物车对应1个商品信息

#### 练习

> 查询男生的姓名、总分

> 使用sum，考虑使用分组，按人名分组

```

select students.name,sum(scores.score) from students inner join scores on scores.stuid = students.id

where students.gender = 1

group by students.id

```

### 自关联

> 同一张表中不同字段关联,比如 pid refrences aid 把省,市，县放在同一个表中，减少表开销。那pid 关联 aid就称为自关联

### 视图

- 视图本质是对查询语句的封装

- 对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情，解决:定义视图

```

create view stuscore as

select students.*,scores.score

from studejts inner join scores on students.id = scores.stuid

where

//查询视图

select * from stuscore

```

```

create view view_name as statements

```

### 事务

- 一个业务逻辑需要执行多条sql语句，如果某个sql语句出错，则希望真个操作都回退，保证业务逻辑的完整性

- 事务的四大特性(ACID)

- 原子性(Atomicity) 要么全部成功，要么全部失败

- 一致性(Consistency) 几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果一致。

- 隔离性(Isolation) 事务执行不受其他事务干扰，事务执行的中间结果必须对其他事务是透明的

- 持久性(Durability):对已经提交的事务，其数据必须被持久存储

- 要求：表的类型必须是 innodb或者bdb类型，才可以使用事务

- innodb 行级锁

>查看建表语句

```

show crate table students;

```

- 事务语句

>使用事务的前提条件：当存在insert/update/delete

```

begin;

update students set name = "michael" where id = 1 //内存级的临时表，并且上行级锁

//持久化存储数据并解锁

commit

//抛出异常后回滚

rollback

```

### 索引

- 为了优化查询，提高数据库的访问速度需要建立索引

- 主键和唯一索引都是索引，可以提高速度

#### 选择列的数据类型

- 数据小，简单数据类型，避免null

#### 操作

- 索引分单列索引和组合索引

- 单列索引 一个索引只包含单个列

- 组合索引，一个索引包含多个列

- 查看索引

```

show index from table_name;

```

- 创建索引及删除索引

```

create index index_name on table(username(length))

DROP index [index_name] on table

```

- 缺点：更新表时速度会变慢

- 启用运行监测

set profiling = 1

- 查看运行时间

show profiles