MySQL数据库重点(Grit_my)
1.字段
【1】char为定长，指定字节数后，无论实际数据为多大，始终开辟指定的内存大小，存取效率高，不指定长度时默认为1个字节；
varchar为不定长，必须要指定最大大小，开辟的内存空间按照实际数据大小而定，节省空间，但存取效率较低；
【2】字段约束：
unique：唯一索引
null = True:允许字段值为空
primary key:主键索引
auto_increment:自增
default：默认
decimal(6,2)：数据类型（小数，取值范围：-9999.99~9999.99）
2.数据表增删改查基本操作
【1】增：insert into 表名 values(值1，值2...);
【2】删：delete from book where 条件;
【3】改：update 表名 set 字段1=值1 where 条件;
【4】查：select * from 表名 [where 条件];
3.表结构修改（alter table 表名 执行动作）
【1】增加字段：alter table 表名 add 字段名 数据类型 [after 字段名] [first];
【2】删除字段：alter table 表名 drop 字段名;
【3】修改字段：alter table 表名 change 旧字段名 新字段名 数据类型；
【4】数据表重命名：alter table 表名 rename 新表名；
4.高级查询（重点）
【1】模糊查询：select * from 表名 where 字段名 like '%';（'%'代表0个或者多个字符）
select * from 表名 where 字段名 like '_';（'_'代表任意一个字符）
【2】as用法：在sql语句中给查询结果的字段或者数据表重命名（只能在当前语句中有效）；
select 字段名 as 重命名 from 表名;
【3】对查询结果排序：order by
select * from 表名 order by 字段名;（默认排序为升序）
select * from 表名 order by 字段名 desc;(desc为降序排序)
复合排序：select * from 表名 order by 字段1 desc,字段2;(字段1相同时按照字段2进行排序)
【4】限制：limit 数量
限制查询、删除、修改数量；
【5】联合查询：union
连接两个select语句的查询结果；
可以将多个表的查询结果拼在一起，但是查询的字段数量需要一致；
【6】子查询：一个select语句中嵌套另外一个select语句（将一次select的查询结果作为下一次查询的基准进行再次select查询）
（1）将子查询结果的具体某个值放在where子句中作为明确的值进行判断；
select * from book where price > (select price from book where name='html');
（2）子查询的结果作为select查询的基准表（将子查询的结果集重命名方便where子句引用操作）；
select * from (select name,price from book) as s where s.price > 12;
key-point：select语句执行顺序
【7】SELECT #5.开始查询
【8】[DISTINCT 去重] <select_list> #6.确定需要查询哪些字段<有分组时，必须是分组的字段或聚合函数>
【1】FROM table_name [<left_table>
【2】<join_type> JOIN <right_table>
【3】ON <join_condition>] #1.先确定查询的基准表<可以进行表关联查询：内连接>
【4】WHERE <where_condition> #2.确定查询条件
【5】GROUP BY <group_by_list> #3.分组
【6】HAVING <having_condition> #4.按照聚合结果进行筛选<必须与group by进行配合使用>
【9】ORDER BY <order_by——condition> #7.确定排序条件
【10】LIMIT <limit_number> #8.确定限制条数
< 每个步骤会产生1个虚拟表作为下一个步骤的输入，知道执行完最后一个步骤产生的结果才能被调用方使用 >
5.聚合操作
基于数据查找的分组结果，对每个分组数据做进一步的整理(聚合)与筛选(having)；
【1】聚合函数：avg(字段名)/max(字段名)/min(字段名)/sum(字段名)/count(字段名)/count(*)
【2】聚合分组：将查询结果进行分组（对于某个字段有多个相同记录时进行分组比较合理，相当于分组归纳）
（1）例：select country,max(attack) from sanguo group by country;
# 使用分组时：select 后面只能使用被分组的字段和聚合函数
（2）例：select country,count(*) from sanguo where name like '__' group by country;
# group by 语句在where子句后；
（3）例：select country,gender,count(*) as number from sanguo group by country,gender order by number;
# group by 复合分组时，多个字段的值都相同的时候为一组
【3】HAVING语句：聚合筛选
1.必须与group by 分组进行配合；
2.一般对各分组的聚合结果进行条件筛选；
【4】去重语句：distinct
去除查询结果的重复记录；
例：select distinct country from sanguo
6.索引操作（只为提高查询效率）
【1】索引的作用：通过对数据表中经常需要查询的字段创建索引表，改变数据的查询方式从而提高查询效率，缺点是创建索引表需要占用物理内存且需要动态维护，降低了数据表写操作的效率；
通常索引表采用二叉树结构组织索引数据，加速了数据的访问；
【2】索引分类：普通索引（MUL）、唯一索引（UNI）、主键索引（PRI）
【3】普通索引：create index 索引名 on 表名(字段名);
唯一索引：create unique index 索引名 on 表名(字段名);
主键索引：alter table 表名 add primary key(字段名);
【4】查看索引：show index from 表名；
删除索引：drop index 索引名 on 表名；
alter table 表名 drop primary key;
7.外键约束
【1】主表与从表：在同一个数据库中B表中关联了A表的主键，A表为主表，B表为从表；
【2】外键约束在从表中建立（一个数据库中的外键名称不能重复），且会自动为外键字段创建了普通索引：
（1）已建数据表新增外键约束：
alter table 从表 add constraint 从表外键名 foreign key(从表关联字段) references 主表(主表关联字段);
（2）创建表时增加外键约束字段：
constraint 外键名 foreign key(字段名) references 主表(主表关联字段);
【3】解除外键约束：alter table 表名 drop foreign key 外键名；
drop index 索引名 on 表名；
【4】级联动作：
（1）RESTRIC（默认）：主表删除记录时，如果从表中有关联外键则不允许删除；
（2）CASECADE：主表删除或者更新记录时，从表也会进行级联更新（用的最多）；
例：alter table person add constraint dept_fk foreign key(dept_id) references dept(id) on delete cascade on update cascade;
（3）set null:主表删除记录时，从表关联字段变为null；
8.表关联设计
【1】一对一关系：一对一关联，关联从表的外键是unique或primary key；
【2】一对多关系：从表中建立外键进行关联主键；
【3】多对多关系：需要建立关系表对两张表需要关联的字段进行关联；
【4】一般先建主表，再建立从表，同一个数据库中不能出现相同的外键名；
9.表关联整理与查询
【1】简单多表查询：
例：select * from 表1，表2 where 表1.id = 表2.id； #多表查询：表名.字段名
【2】连接查询方法：将两个有关联的数据表中的数据进行整理后作为查询的基准表；
（1）内连接查询：两个表中有关系的记录为交集数据，其它还没有建立关系的记录为独有数据；（不一定要有外键关联的两个数据表才能进行查询）-->查询结果为两个表的交集记录；
内连接整理：表1 inner join 表2 on 表1.字段名=表2.字段名
查询：select * from 表1 inner join 表2 on 表1.字段名=表2.字段名；
（2）左连接查询：查询结果为两个表的交集记录 + 左表中的独有数据；
（3）右连接查询： 查询结果为两个表的交集记录 + 右表中的独有数据；
【3】为提高查询效率，一般将数据量大的表作为基准表；
10.视图（生成的是虚拟表）
【1】视图相当于基于一大张数据表通过特定的复杂sql查询语句生成的虚拟表；用户对该虚拟表的操作方式和普通数据表一样，通过这样使用户操作更方便，保障了数据库的系统安全；
【2】创建视图：create view view_name as [select_statement];
【3】对视图的增删改查操作与一般数据表相同（但是不能对关联查询结果产生的虚拟表进行写操作）；
【4】视图的作用：
（1）简化高频复杂的过程提取成视图，方便做后续操作；
（2）提高数据安全性；
（3）让数据更加清晰；
11.函数与存储过程<存储过程：存储的是过程>
函数与存储过程都是对sql语句的封装，函数关注的是结果，存储过程关注的是过程；
【1】函数-->必须返回一个结果；
存储过程-->对一系列sql语句执行，重要在执行的逻辑，没有返回值;(用的更多些)
函数中的select语句结果如果没有作为返回值则必须要赋值给一个变量；函数可以有写操作，但是每次调用都会执行，<需要考虑对数据库的影响是否有异常>；
函数使用select调用，存储过程使用call调用；
【2】存储过程：将多个sql语句进行封装后调用，重点关注语句的执行，没有返回值！存储过程可以看做对sql语句代码的封装和重用；
【3】MySQL函数与存储过程的区别：
（1）函数必须有且只有一个返回值，而存储过程不能有返回值；
（2）函数采用普通传参，存储过程有in，out，inout多个类型传参方式；
（3）存储过程是为了完成一系列复杂的sql操作步骤，语句功能丰富，函数是为了完成查询逻辑而返回一个结果的过程，函数中不能展示查询结果集，函数只能返回单个结果；
12.事务
【1】一个流程的数据库操作被称为一个事务，通过事务操作，保证事务操作过程中数据的安全性；
【2】begin; #开启事务
执行事务中若干条sql语句；
commit; #事务提交至数据库执行操作,使事务操作生效,必须要commit才能生效
rollback; #回滚到事务之前的状态(未提交之前的状态)
【3】事务的特性：
(1)原子性：事务的所有操作作为一个整体，只有全部成功时才算事务成功,如果有一个步骤错误，则回滚到事务操作之前的状态<要么全成功，要么全失败！>；
(2)一致性:事务完成后，数据的完整性没有被破坏，关联数据影响一致，由于原子性才有一致性；
(3)持久性；一旦事务提交，数据库修改后保存在磁盘中，永久有效；
(4)隔离性:并发事务间的操作互不影响，同一时刻不允许多个事务操作同一记录；(重点)<事务控制保证数据安全的特点>
【4】事务的隔离等级：
（1）读未提交；<一般的数据库都不会采用这种隔离级别>
（2）读已提交；<一个事务提交的数据，另一事务能够马上读到>
（3）可重复读；<一个事务提交的数据，另一事务在未commit之前不能读到>
（4）串行化；<表级锁，吞吐量太低，用户体验差>
MySQL默认的事务级别：可重复读
python中事务操作有行级锁，当一个事务对某一条记录操作未提交时，其它事务不能对这个记录进行操作；
13.数据库的优化
【1】建议采用数据库的范式设计主要为了降低数据的冗余性；
【2】MySQL的存储引擎：
InnoDB：支持表/行级锁（默认）；
MyISAM:支持表级锁；
我们可以通过合理的引擎使我们对数据库的读写达到最优！
【3】字段类型选择，在满足设计的条件下，选择占用空间小的字段；
【4】where子句中尽量不使用！=；（尽量避免全盘扫描）
【5】表的拆分：垂直拆分，水平拆分；
【6】使用缓存，减小数据库的压力<重点>；
14.数据库的备份与用户管理
【1】表的复制：create table 表名 select查询语句;(不会复制主键，需要手动重新创建)
【2】数据备份（备份到磁盘）：
$ mysqldump -u用户名 -p 源库名 > 文件名.sql
【3】用户权限管理：
$ mysql -u用户名 -p 目标库名 < 文件名.sql
15.数据库用户权限管理
【1】如何进行MySQL远程访问？
（1）修改配置文件，重启服务：
$ cd /etc/mysql/mysql.conf.d
$ sudo vi mysql.cnf
关闭bind_address = 127.0.0.1
（2）重启MySQL服务；
（3）进入MySQL修改用户表的host值：
use mysql;
update user set host='%' where user = 'root' #%代表任意IP
（4）最后刷新权限：flush privileges;
【2】添加授权用户：
（1）用root用户登录MySQL：
$ mysql -u root -p
（2）添加新用户：（允许的IP为%时，表示任意IP）
create user '新用户名'@'允许的IP' identified by '新用户密码';
（3）授权新用户：
grant 权限列表 on 库.表 to '用户名'@'%';
（4）取消授权：
revoke 权限列表 on 库.表 from 'user'@'%'；
（5）刷新权限：
flush privileges;
（6）删除用户：
drop user '用户名'@'%';
权限列表：all privileges、select、insert、update、delete、alter等
16.pymysql模块
import pymysql
# 1.创建数据库连接对象,连接到数据库
db = pymysql.connect(host="localhost",
port=3306,
user="root",
password="123456",
database="grade",
charset="utf8")
# 2.通过连接对象，创建游标对象,通过游标对象操作数据库
cur = db.cursor()
# sql语句
sql = "insert into student values(%s,%s,%s,%s);"
# 3.通过游标对象提供的execute方法执行sql语句
value_list = [7, 'Emam', 18, 'running', 98]
cur.execute(sql, value_list)
# 4.执行的sql语句如果是写操作,则通过数据局对象提供的commit方法提交操作,完成对数据库的实际更改
db.commit()
# 5.关闭游标对象
cur.close()
# 6.断开数据库连接
db.close()
17.MySQL存储引擎
【1】数据库的存储引擎：
（1）引擎：在数据库管理系统中操作数据库的核心代码；
【1】InnoDB:默认引擎,支持行级锁、事务、事务回滚操作；
【2】MyISAM:支持表级锁，不支持事务控制/外键；
......
（2）选择合适的引擎使数据库的操作达到更优；
【2】MYISAM与INNODB之间的区别:
(1)InnoDB支持外键和事务，而MyISAM不支持外键和事务；
(2)InnoDB支持表级锁和行级锁，而MyISAM只支持表级锁；
(3)InnoDB采用聚集索引,必须要有主键；
MyISAM采用非聚集索引,可以没有主键；本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
