MySQL大纲整理笔记

最新推荐文章于 2021-10-13 22:18:04 发布

JTOOP

最新推荐文章于 2021-10-13 22:18:04 发布

阅读量400

点赞数

分类专栏： MySQL 文章标签： python mysql 数据库索引

本文链接：https://blog.csdn.net/xiaoduu/article/details/108271876

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

文章目录

MySQL笔记

MySQL笔记

文章以及视频

参考文章：https://www.cnblogs.com/linhaifeng/p/7278389.html

参考视频：https://www.bilibili.com/video/BV1Dt4y1X7gV?p=1

数据库管理软件，套接字软件
共享数据，带来的数据竞争
处理数据安全问题加锁
处理数据库库层面安全问题
数据库管理软件性能问题
mysql的基本管理，SQL语句的一个学习
mysql就是一个基于socket编写的C/S架构的软件
数据库服务器：运行数据库管理软件的计算机
数据库管理软件：mysql，oracle，db2，sqlserver
库：文件夹
表：文件
记录：事物一系列典型的特征
数据：描述事物特征的符号
设置密码与破解密码（跳过授权表）
mysqld --skip-grant-tables # 跳过授权表
mysqladmin -uroot -p password “xxx” # 设置mysql管理员的账号密码
updata mysql.user set password=password("") where user=“root” and host=“localhost”; # 密码为空
flush privileges;
tasklist |findstr mysql
taskkill -F /PID xxx
mysqld --install：将mysqld添加到服务组件中
mysqld --remove：将mysqld从服务组件中已出
net start mysql
net stop mysql
mysql -uroot -pxxx -h 127.0.0.1 -P 3306

SQL语句：

help create查询create语句有哪些，同理其他语句也一样

操作文件夹（库）

增
- create database db1 charset utf8;
查
- show create database db1;
- show databases;
改
- alter database db1 charset gbk;
删
- drop database db1;

操作文件（表）

切换文件夹
- ```
use db1;
```
查看当前所在文件夹
- select database();
增
- create table t1(id int, name char);
查
- show create table t1;
- show tables;
- desc t1;
改
- alter table t1 modify name char(6);
- alter table t1 change name NAME char(7);
删
- drop table t1;

操作文件内容（记录）

增
- insert t1(id,name) values(1,‘alex’),(2,‘hali’),(3,‘sogo’);
查
- select id,name from db1.t1;
- select * from db.t1;（不推荐）
改
- update db1.t1 set name=‘hhh’;
- update db1.t1 set name=‘gg’ where id=2;
删
- delete from t1;# 清空表
- truncate t1; # 应该用truncate来清空表
- delete from t1 where id=2;
什么是存储引擎
- 存储引擎就是表的类型
查看mysql支持的存储引擎
- show engines;
指定表类型/存储引擎
- create table t1(id int)engine=innodb;
- create table t2(id int)engine=memory;
- create table t3(id int)engine=blackhole;
- create table t4(id int)engine=myisam;
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
查看表结构/数据一行一行的显示：加\G，不用加分号
- show create table mysql.user\G
复制表
- 复制表结构和数据
  - create table t1 select * from mysql.user;
- 只复制表结构，没有数据，下面两句一样的效果
  - create table t1 select * from mysql.user where 1>2；
  - create table t1 like mysql.user;

字段类型

整数类型

整数类型的宽度指的是显示宽度，存储宽度修改不了，其他类型都是存储宽度
tinyint 1个字节
int 4个字节
浮点类型
- 区别在于精度，还有存储长度
- float，最大总长为255，小数最大30，精度最低
- double，最大总长为255，小数最大为30，精度其次
- decimal，最大总长为65，小数最大为30，精度最高

日期类型

create table student(id int,name char(6),born_year year,birth_date date,class_time time,reg_time datetime);
insert into student values(1,‘hh’,now(),now(),now(),now());
insert into student values(1,‘gg’,‘1997’,‘1997-12-12’,‘12:12:12’,‘1997-12-12 12:12:12’);
datetime和timestamp都是表示时间的类型，而且都有时间范围
- datetime 8个字节，表示1001-9999年（一般使用这个，因为timestamp只能到2038年）
- timestamp 4个字节，表示1970-2038年

字节类型

char：定长，存取速度快，浪费空间（一般选这个）
varchar：变长，多加了一个bytes用来存储数据长度，存取速度慢，不浪费空间
字符类型的宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));
insert into t13 values(‘理解’);
insert into t14 values(‘lijie’);

枚举类型与集合类型

字段的值只能在给定的范围中选择，如单选框、多选框
enum 单选
- 只能在给定的范围内选一个值，如性别sex 男male/女female
set 多选
- 在给定的范围内选择一个或一个以上的值（爱好1，爱好2，爱好3）

create table consumer(
	id int,
	name char(16),
	sex enum('male','female','other'),
	level enum('vip1','vip2','vip3'),
	hobbies set('play','music','read','run')
	);
insert into consumer values(1,'alex','male','vip2','music,read,run');

约束条件

null与default

create table t15(
	id int(11) unsigned zerofill # 这些都是type而不是约束条件
	);

create table t16(
	id int,
	name char(6),
	sex enum('male','female') not null default 'male'
);

insert into t16(id,name) values(1,'alex');

unique key

单列唯一

方式一：
create table department(
	id int unique,
	name char(10) unique
);

方式二：
create table department(
	id int,
	name char(10),
	unique(id),
	unique(name)
);
insert into department values(1,'IT'),(2,'sale');

联合唯一

create table services(
	id int unique,
	ip char(15),
	port int,
	unique(ip,port)
);

insert into services values(1,'192.168.1.10',80),(2,'192.168.1.10',81),(3,'192.168.1.13',80);

primary key:不为空且唯一

primary key 相当于 not null unique

存储引擎（innodb）：对于innodb存储引擎来说，一张表内必须有一个主键

单列主键

create table t17(
	id int primary key,
	name char(16)
);

insert into t17 values(1,'alex'),(2,'alun');
insert into t17(name) values('gg');
insert into t17(name) values('hh');

create table t18(
	id int not null unique,
	name char(16)
);

复合主键

create table t19(
	ip char(15),
	port int,
	primary key(ip,port)
);

insert into t19(name) values('1.1.1.1',80),('1.1.1.1',81),('2.2.2.2',80);

auto_increment

create table t20(
	id int primary key auto_increment,
	name char(16)
);

insert into t20(name) values('egon'),('alex'),('wwx');

insert into t20(id,name) values(7,'hh');

insert into t20(name) values('egon1'),('alex1'),('wwx1');

了解

show variables like 'auto_inc%';

#步长:
auto_increment_increment默认为1
#起始偏移量:
auto_increment_offset默认为1

#session级别(本次链接)设置步长
set session auto_increment_increment=5;
# global全季设置
set global auto_increment_increment=5;

# 设置起始偏移量
set global auto_increment_offset=3;
#强调:起始偏移量<=步长

create table t21(
	id int primary key auto_increment,
	name char(16)
);

insert into t21(name) values('egon'),('alex'),('wxx');

# 清空表:
delete from t20;
delete form t20 where id=3;
insert into t20(name) values('xxx');
truncate t20; # 应该用truncate来清空表

foreign key :建立表之间的关系

一般建表只是逻辑上关联而不使用外键

1 建立表关系

# 先建被关联的表(必须有主键)
create table dep(
	id int primary key,
	name char(16),
	comment char(50)
);

# 再建关联的表
create table emp(
	id int primary key,
	name char(10),
	sex enum('male','female'),
	dep_id int,
	foreign key(dep_id) references dep(id) on delete cascade on update cascade
);

2 插入数据

# 先往被关联表插入数据
insert into dep values(1,'IT','技术能力有限部门'),(2,'销售','销售能力不足部门'),(3,'财务','花钱特别多部门');

# 再往关联表插入数据
insert into emp values(1,'egon','male',1);
insert into emp values(2,'alex','male',1);
insert into emp values(3,'hhh','male',2);
insert into emp values(4,'faker','male',3);

# 现在要删除部门和对应的员工
delete from emp where dep_id=1;
delete from dep where id=1;

表关系

先站在左边表角度看两张表关系，再站在右边表看两张表关系

多对一

出版社与书

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

create table book(
	id int primary key auto_increment,
	name varchar(20),
	press_id int not null,
	foreign key(press_id) references press(id)
	on delete cascade
	on update cascade
);

insert into press(name) values
('北京出版社'),('广东出版社'),('湖南出版社');

insert into book(name, press_id) values
('九阳神功',1),('九阴真经',2),('天龙八部',2),('降龙十八掌',3),('葵花宝典',3);

多对多

两边都是多对一，就是多对多，因为要先建被关联的表再建关联表，这样会导致两边的表谁都不能先建，所以建一个中间表将外键独立出来

作者与书

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

# 书的表上面多对一已经建立了

# 建立中间表
create table author2book(
	id int not null unique auto_increment,
	author_id int not null,
	book_id int not null,
	constraint fk_author foreign key(author_id) references author(id)
	on delete cascade
	on update cascade,
	constraint fk_book foreign key(book_id) references book(id)
	on delete cascade
	on update cascade,
	primary key(author_id,book_id)
);

# 插入4个作者，id依次排开
insert into author(name) values('egon'),('alex'),('bihu'),('hty');

# 每个作者与自己的代表作
egon: 九阳神功，九阴真经
alex：九阳神功，葵花宝典
bihu：九阴真经，天龙八部
hty：降龙十八掌，天龙八部

insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(2,1),
(2,5),
(3,2),
(3,3),
(4,4),
(4,3)
;

一对一

在foreign key的基础上加unique

培训机构：客户和学生，客户发展成学生

# 学生一定是客户转换来的
# 客户不一定称为一个学生
# 一定是student表来foreign key 表customer，而且加上unique

create table customer(
	id int primary key auto_increment,
	name varchar(20) not null,
	qq varchar(10) not null,
	phone char(11) not null
);

create table student(
	id int primary key auto_increment,
	class_name varchar(20) not null,
	customer_id int unique, # 因为是一对一，该字段一定要唯一
	foreign key(customer_id) references customer(id) # 外键的字段一定要保证unique
	on delete cascade
	on update cascade
);


# 增加客户
insert into customer(name,qq,phone) values
('aaa','213213123','123123123'),
('bbb','213213123','123123123'),
('cc','213213123','123123123'),
('dd','213213123','123123123'),
('ee','213213123','123123123')
;

# 增加学生
insert into student(class_name,customer_id) values
('培训1期',1),
('培训1期',2),
('培训2期',3),
('培训4期',5),
;

查询语句

https://www.cnblogs.com/linhaifeng/articles/7356064.html

**语句执行顺序：**from ->on->join-> where -> group by -> having ->select -> distinct -> order by ->limit

单表查询

select distinct 字段1,字段2,字段3 from 库.表
- where条件
- group by 分组条件
- having 过滤
- order by 排序字段
- limit n;
简单查询
避免重复distinct
通过四则运算查询（加减乘除）
定义显示格式：
- concat()函数用于连接字符串
  - concat(‘姓名：’,name,‘年薪：’.salary*12)
- concat_ws() 第一个参数可以为分隔符
  - SELECT CONCAT_WS(’:’,name,salary*12) AS Annual_salary
    FROM employee;
分组：group by
- set global sql_mode=‘ONLY_FULL_GROUP_BY’; # 只能取分组的字段，以及每个组聚合结果
- group_concat(字段) 获取分组后的字段内容
聚合函数
- max
- min
- avg
- sum
- count
having：分组后过滤
order by 排序：执行顺序是在select 后面
- 升序：order by 字段 asc
- 降序：order by 字段 desc
limit 5,5：记录条数从5开始取5条

总结：

语法顺序：
- select distinct 字段1,字段2,字段3 from 库.表
  - where条件
  - group by 分组条件
  - having 过滤
  - order by 排序字段
  - limit n;

执行顺序：from ->on->join-> where -> group by -> having ->select -> distinct -> order by ->limit

def from(db,table):
	f=open(r'%s\%s' %(db,table))
	return f

def where(condition,f):
	for line in f:
		if condition:
			yield line

def group(line):
	pass

def having(group_res):
	pass
	
def distinct(having_res):
	pass
	
def order(distinct_res):
	pass

def limit(order_res):
	pass

def select():
	f = from('db1','t1')
	lines = where('id>3',f)
	group_res = group(lines)
	having_res = having(group_res)
	distinct_res = distinct(having_res)
	order_res = order(distinct_res)
	res = limit(order_res)
	print(res)
	return res

正则表达式：regexp
- select * from employee where name regexp ‘^jin.*(g|n)$’;
- 匹配员工名字开头是jin后面可能是g或者n结尾的信息

连表操作

表数据：https://www.cnblogs.com/linhaifeng/articles/7267596.html

不要使用where关联两张表，因为mysql有专门的语法来关联，where适合过滤查询数据

内连接inner join

只取两张表的共有部分
select * from employee inner join department on employee.dep_id = department.id;

左连接left join

在内连接的基础上只保留左表的记录
select * from employee left join department on employee.dep_id = department.id;

右连接right join

在内连接的基础上只保留右表的记录
select * from employee right join department on employee.dep_id = department.id;

全外连接full join（mysql不支持）

虽然mysql不支持，但可以去实现
在内连接的基础上保留左右两表没有对应关系的记录
这条语句不支持，但可以用下面代替：select * from employee full join department on employee.dep_id = department.id;
select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中
带IN关键字的子查询
带比较运算符的子查询
带EXISTS关键字的子查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H9tk3qFf-1598547538525)(C:\Users\dujun\AppData\Roaming\Typora\typora-user-images\image-20200826173028616.png)]

# 查询平均年龄在25岁以上的部门名
# 联合查询
select department.name from employee right join department on employee.dep_id=department.id group by dep_id having avg(age)>25;
# 子查询
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) >25);

# 查看技术部门员工的姓名
select name from employee where dep_id in (select id from department where name='技术');

# 查看不足2人(<=1)的部门名
select * from department where id not in (select dep_id from employee group by dep_id having count(dep_id) >1);

# 带EXISTS关键字的子查询
select * from employee where EXISTS (select id from department where name='IT');

# 可以将查询查出来的表用括号括起来然后as成一张表用于关联表
select * from department inner join 
(select name,sex,dep_id from employee group by dep_id having sex='female') as female 
on department.id = female.dep_id;

# 查询全部学生都选修了的课程号和课程名 
select cid,cname from course where cid in (select course_id from score group by course_id having count('sid') = (select count(sid) from student))

# 查询平均成绩大于85的学生姓名和平均成绩
select s1.sname,s2.avg(number) from student as s1 inner join (select student_id,avg(number) from score group by student_id having avg(number) > 85) as s2 on s1.sid=s2.student_id;

多表查询

使用表关联语句和子查询语句来搭配完成多表查询

权限管理(grant/revoke)

1 创建账号

# 本地账号
create user 'djt'@'localhost' identified by '123';  
	# mysql -udjt -p123
	
# 远程账号
create user 'djt'@'192.168.1.%' identified by '123'; 
	# 这句远程账号命令是指创建一个名为djt密码为123的账号，只允许在ip网段地址为192.168.1的客户端主机上来连接我现在本地的mysql服务端
	#mysql -udjt -p123 -h 服务端ip
create user 'djt'@'192.168.1.31' identified by '123';
create user 'djt'@'%' identified by '123'; #任意网段

2 授权与撤回权限

# 下面四张表对权限的范围是依次递减，user是指所有库下所有的表，db是指只对指定的库开放权限，tables_priv指只对指定的表，column_priv指只对指定的字段
user:*.*
db:db1.*
tables_priv:db1.t1
columns_priv:id,name

# 授权权限只有root用户拥有
# 授权
grant all on *.* to 'djt'@'localhost';  # 对localhost下的djt用户开放所有权限作用在所有库和所有表
grant select on *.* to 'djt'@'localhost'; # 对localhost下的djt用户只开放select权限作用在所有库和所有表
grant select on db4.t1 to 'djt'@'localhost'; #对localhost下的djt用户只开放select权限作用在数据库db4
grant select(id,name),update(age) on db4.t2 on 'djt'@'localhost'; # 指定了字段，只开放了select和update权限，针对db4库t2表下的id、name、age字段

# 取消权限（注意是from）
revoke select on *.* from 'djt'@'localhost'; 对localhost下的djt用户撤回对所有
库的所有权限
revoke select on db4.* from 'djt'@'localhost'; # 对localhost下的djt用户撤回对db4
库的select权限

navicat

pymysql

# 增删改
import pymysql
#建立连接
conn = pymysql.connect(
    host='xxxx',
    port=3306,
    user = 'xx',
    password = 'xx',
    db = 'xx',
    charset = 'utf8'
)
#拿到游标
cursor = conn.cursor()
#执行sql语句 增删改
# sql = 'insert into dep(name) values(%s)'
# sql = 'update dep set name=%s where name="time"'
sql = 'delete from dep where name=%s'
rows = cursor.execute(sql,('time1'))
# rows = cursor.executemany(sql,['time1','time2','time3'])
print(rows)
# 需要提交事务
conn.commit()
cursor.close()
conn.close()

# 查询
# import pymysql
# #建立连接
# conn = pymysql.connect(
#     host='192.168.31.90',
#     port=3306,
#     user = 'root',
#     password = 'root',
#     db = 'db8',
#     charset = 'utf8'
# )
# #拿到游标
# cursor = conn.cursor(pymysql.cursors.DictCursor) #以字典形式显示
# #执行sql语句 增删改
# sql = 'select * from dep'
# rows = cursor.execute(sql)
# print(rows)
# cursor.scroll(1,mode='absolute') # 游标移动3个位置（绝对位置）
# print(cursor.fetchone())
# cursor.scroll(2,mode='relative') # 游标移动2个位置（相对位置）
# print(cursor.fetchone())
# print(cursor.fetchmany(3))
# print(cursor.fetchall())
#
# cursor.close()
# conn.close()

MySQL内置功能

参考文章：

https://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

视图(create view xxx as xxxx)（不推荐）

不推荐使用，因为数据库中的数据经常修改的话，对应之前创建的虚拟表就也需要修改。
将虚拟表保存起来，就不用每次用虚拟表的时候重复执行sql语句，例如：存储多表查询创建的虚拟表
在磁盘里面只有表结构，没有表数据，因为它是虚拟表

create view course2teacher as select * from course inner join teacher on course.teacher_id=teacher.tid;

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
就像钩子函数，抑或是装饰器，可以定义函数执行前后的操作，为函数添加功能

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

#准备表
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

#创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #等值判断只有一个等号，NEW是一个触发器提供的对象，表示即将插入的数据行的对象，相对的有OLD对象，OLD表示更新/删除操作之前的数据对象
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
      END IF ; #必须加分号
END//
delimiter ;

# 删除触发器：drop trigger tri_after_insert_cmd;

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');


#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调用它的名字可以执行其内部的一堆sql
解释：
- 对于应用程序员和数据库程序员，应用程序员负责编写程序，而数据库程序员负责管理数据库中的数据，他们在一个项目中需要互相配合。
- 当应用程序员做项目需要操作数据时，数据库程序员可以将mysql的sql多条语句综合总结在一起，相当于封装起来，封装成一个API，直接给应用程序员去调用使用就可以了。
- 而存储过程就相当于将多条sql语句封装成sql操作API的一个过程。
- 这样应用程序员就不需要在应用层方面去编写处理mysql数据库数据的函数，直接使用数据库程序员编写好的API。
优点：
- 1 用于代替程序写的SQL语句，实现程序与sql解耦
- 2 基于网络传输，传别名的数据量小，而直接传sql数据量大
缺点：
- 程序员扩展功能不方便

程序与数据库结合使用的三种方式

# 方式一
	MySQL：存储过程
	程序：调用存储过程
	
# 方式二
	MySQL：
	程序：纯SQL语句
	
# 方式三
    MySQL：
    程序：类和对象，即ORM（本质还是纯SQL语句）

1 无参存储过程

delimiter //
create procedure p1()
BEGIN
	select * from db7.teacher;
END //
delimiter ;

# MySQL中调用
call p1();

# Python中调用
cursor.callproc('p1')
print(cursor.fetchall())

2 有参数过程

in表示输入，out表示输出，inout表示即可以是输入又可以是输出

delimiter //
create procedure p2(in n1 int, in n2 int, out res int)
BEGIN
	select * from db7.teacher where tid>n1 and tid<n2;
	set res=1;
END //
delimiter ;

# MySQL中调用
set @x=0;	# 定义一个x值为0
call p2(2,5,@x);
select @x; 	# 查看结果

# Python中调用
cursor.callproc('p1',(2,4,0)) #@_p2_0=2,@_p2_1=4,@_p2_2=0，mysql内部其实是这三个变量，所以要查看返回值的话，就需要查询这三个元素
cursor.execute('select @_p2_2')
print(cursor.fetchone())

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原始的状态，从而数据库数据完整性。
事务是一种对sql语句的处理方式，将多条sql语句捆绑在一起一起执行。
事务、触发器、视图都可以封装到存储过程里面

函数

函数和存储过程的区别：
- 单个函数功能没那么多，而存储过程可以将函数、触发器、事务、视图、流程控制语句等都封装在一起，也是另外一方面的函数。
mysql提供了内置的函数可以供我们调用，比如：格式化时间的函数date_format(column_name,’%Y-%m’)
也可以自定义函数

流程控制

流程控制意思是使用mysql给予的if、for、while、repeat语句去控制整个代码的流程。
只是mysql的语法和python语法不太一样

索引

参考文章：

https://www.cnblogs.com/linhaifeng/articles/7274563.html

底层是B+树实现
索引在mysql中也叫做“键”
加快数据查询速度
索引也是一个文件，索引太多会影响应用程序的性能，索引太多又可能会导致查询速度不够而且修改索引会比较麻烦，所以索引一开始的建立定位就很重要。应该由应用程序员去添加，不该由DBA去添加，因为应用程序员比较熟悉业务的数据流，不管是添加还是修改都比较方便。

索引的原理

通过不断地缩小想要获取的数据的范围来筛选出最终的结果，同时把随机事件变成顺序事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的数据结构

由于数据是存储在磁盘中，而每次读取数据都要去磁盘的话，会严重浪费计算机资源，这就有了磁盘预读操作，当一次OI时，不仅将当前磁盘地址的数据放入内存缓冲区中，而且还把相邻的数据也读取到内存缓冲区中，因为局部预读行原理，当数据被访问时，它相邻的数据被访问的速度也会很快，这个理论对于索引的数据结构设计非常有帮助。B+树的结构可以实现。
索引是B+树实现，B+树是通过二叉查找树，再由平衡二叉树，B树演化而来
数据都是存放在B+树的叶子节点，而其他节点都是存放着虚拟的节点外加指针

B+树性质

BTree：https://blog.csdn.net/ty_hf/article/details/53526822
- B树：二叉树
- B-树：平衡二叉树，在B树的基础上加上了平衡算法
- B+树：在B-树的基础上再定义，只有叶子节点中可以存放数据，非叶子节点都是存储数据的数据层（指针）。根节点和叶子节点都有横向链指针，指向挨着的邻居，加快查询速度
- B*树：在B+树的基础上为非叶子节点也添加链表指针，用于指向兄弟节点/邻居节点
- 1 索引字段要尽量的小
  - 因为从B+树的结构来看，树的高度就是代表查询数据时IO操作的次数，当索引字段小的时候，即代表磁盘块中的数据被分为更多的数据块，这样B+树每一层的数据块就越多，进而B+树的高度就可以越低，即IO操作的次数就越低，数据查询速度就越快。
- 2 索引的最左匹配特性
  - 当要查询复合的数据时（’alex‘,20,male），先查询’alex’是在字段name中进行查询，进而在age和sex中查询，是从左到右

聚集索引与辅助索引

InnoDB存储引擎就是索引组织表，即表中的数据是按照主键顺序存放的，整张表只可以有一个主键
聚集索引是根据每个表的主键构造的一颗B+树，而辅助索引是根据某个指定字段来构建的B+树
使用explain可以查看索引的情况
数据库中的B+树可以分为聚集索引（clustered index）和辅助索引（secondary index）
- 相同点：
  - 聚集索引和辅助索引内部都是B+树的形式，即高度是平衡的，叶子节点存放着数据
- 不同点
  - 聚集索引中的叶子节点存放的是整行数据，辅助索引只存放对应字段的数据和指向聚集索引的指针

MySQL索引管理

MySQL常用的索引

普通索引INDEX：加快查找
唯一索引：
主键索引PRIMARY KEY：加快查找+约束（不为空、不能重复）
唯一索引UINIQUE：加快查找+约束（不能重复，可以为空）
联合索引：
- PRIMARY KEY(id,name)：联合主键索引
- UNIQUE(id,name)：联合唯一索引
- INDEX(id,name)：联合普通索引
其他索引例如空间索引等，暂时不去了解

应用场景

商场会员卡活动，会员卡信息包括：会员卡编号、姓名、身份证、其他详细信息
- 编号可以使用主键索引（PRIMARY KEY）
- 姓名可以使用普通索引（INDEX）
- 身份证可以使用唯一索引（UNIQUE）
- 其他详细信息可以使用全文索引（FULLTEXT）

索引两大类型

hash类型的索引：查询单条块，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（innodb默认支持它）
不同的存储引擎支持的索引类型：
- InnoDB：支持事务，支持行级别锁定，支持B-tree、Full-text等索引，不支持Hash索引
- MyISAM：不支持事务，支持标级别锁定，支持B-tree、Full-text等索引，不支持Hash索引
- Memory：不支持事务，支持标级别锁定，支持B-tree、hash等索引，不支持Full-text索引
- NDB：支持事务，支持表级别锁定，支持Hash索引，不支持B-tree、Full-text等索引
- Archive：不支持事务，支持表级别锁定，不支持B-tree、Hash、Full-text等索引

联合索引和覆盖索引

联合索引：指让两个字段或更多字段结合成一个索引
- alter table s1 add key|index(name,sex)
- 使用key或者index关键字，将name字段和sex字段做一个联合索引
覆盖索引：https://www.jianshu.com/p/8991cbca3854
- InnoDB聚集索引普通索引，回表操作，索引覆盖

查询优化神奇-explain

explain语句可以查看查询语句的执行状态，显示使用了什么索引，查询记录是几条，查询语句是否filesort，较为重要的两个数据是rows和Extra显示的数据

慢查询优化

慢查询是指查询的时间超过了阈值（规定的时间值）
优化的基本步骤
- 0 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
- 1 where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的却分度最高
- 2 使用explain语句查看执行状态
- 3 order by limit 形式的sql语句让排序的表优先查
- 4 了解业务方使用场景，判断是否需要增加索引来优化
- 5 加索引时参照建索引的几大原则
- 6 观察结果，不符合预期则从0步重新分析

慢查询日志管理

JTOOP

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL大纲整理笔记

文章目录MySQL笔记文章以及视频SQL语句：操作文件夹（库）操作文件（表）操作文件内容（记录）字段类型整数类型日期类型字节类型枚举类型与集合类型约束条件null与defaultunique keyprimary key:不为空且唯一auto_incrementforeign key :建立表之间的关系表关系多对一多对多一对一查询语句单表查询连表操作内连接inner join左连接left join右连接right join全外连接full join（mysql不支持）子查询多表查询权限管理(grant/r
复制链接

扫一扫