SQL 思维导图模板_ProcessOn思维导图、流程图

DDL

数据库定义语言：数据库、表、视图、索引、存储过程

DML

数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

DCL

数据库控制语言：例如控制用户的访问权限GRANT、REVOKE

数据库结构

创建数据库

create database 名称 charset utf8；

查看数据库

show create database 名称；

show databases；

select database();

更改数据库

alter database 名称 charset GBK

使用数据库

use 名称；

删除数据库

drop database 名称；

总结

对于数据结构有创建，查看，使用，删除四种语法

create database 名称;，show create database 名称;，drop database 名称;

use 名称; show databases;

其他：显示数据库下的表 show tables;

数据库表结构

新建数据库表

create table (filed 类型,[约束条件]...);

复制表

create tabke new_table select * from old_table;

复制表结构+记录，不复制主键外键和索引

create new_table select * from old_table where 1=2;

只复制表结构

更改数据库表

修改表名

alter table 名称 rename 新表名；

增加字段

alter table 名称 add 字段名数据类型 [完整性约束 first(放在第一位)/ after field （放到字段后面）];

删除字段

alter table 名称 drop filed;

修改字段类型或者约束条件

alter table 名称 modify filed 数据类型 [完整性约束]；

只改属性

修改字段名

alter table 名称 change oldname newname 原数据属性和约束条件;

只改字段名

alter table 名称 change oldname newname 新数据属性和约束条件;

字段名和属性都改

查看数据库表

show tables;

show create table 表名\G;

desc table 表名;

删除数据库表

删除表

drop table 表名;

修改表名称

rename table 原表名 to 新表名

数据库记录

增加记录

insert into 表名 (field1, field2...) values(v1,v2,...);

insert into 表名 values(v1,v2,...),(v1,v2,..),...;

insert into 表名 (field1,field2,...) select (filed1,filed2,..) from 表2 where ...;

插入查询结果

删除记录

delete from 表名 where ...;

修改记录

update 表名 set field1 = v1, field2 = v2, where ...;

查询记录

单表查询

语法

select filed1, filed2,... from 表名

where 条件

group by field

having 筛选

order by feild

limit 限制条数

执行顺序

from 找到表

where

group by

having

select

distinct 去重

order by

limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

子主题

#定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;

where字句中可以使用： 1. 比较运算符：> < >= <= <> != 2. between 80 and 100 值在10到20之间 3. in(80,90,100) 值是10或20或30 4. like 'egon%' pattern可以是%或_， %表示任意多字符 _表示一个字符 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000;

SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;

SELECT name,post_comment FROM employee WHERE post_comment IS NULL;

SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;

GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

HAVING与WHERE不一样的地方在于!!!!!! #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 SELECT * from employee ORDER BY age, salary DESC;

select * from employee where name regexp '^jin.*[gn]$';

查看所有员工中名字是jin开头，n或者g结果的员工信息

多表查询

多表连接查询

inner join

select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;

left join

select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

right join

select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

full join

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

注意 mysql不支持全外连接 full JOIN， mysql可以使用此种方式间接实现全外连接，union与union all的区别：union会去掉相同的纪录

复合条件查询

select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;

select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc【DESC】;

子查询

总结

#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等

带in关键字的子查询

select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

select name from department where id not in (select distinct dep_id from employee);

not in 无法处理null的值

select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);

带ANY关键字的子查询

select * from employee where salary = any ( select max(salary) from employee group by depart_id);

“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符

ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT * FROM T_Book WHERE
FYearPublished < ANY (2001, 2003, 2005)

带ALL关键字的子查询

select * from employee where salary > all ( select avg(salary) from employee group by depart_id);

all同any类似，只不过all表示的是所有，any表示任一

select * from employee where salary < any ( select avg(salary) from employee group by depart_id);

带比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>

mysql> select name,age from emp where age > (select avg(age) from emp);

select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture mysql> select * from employee -> where exists -> (select id from department where id=200);

#department表中存在dept_id=205，False mysql> select * from employee -> where exists -> (select id from department where id=204); Empty set (0.00 sec)

in与exists

当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率

# exists的执行原理为： 1、依次执行外部查询：即select * from class 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid) 3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录

in后跟的都是子查询，in()后面的子查询是返回结果集的

in的执行原理为： in()的执行次序和exists()不一样,in()的子查询会先产生结果集, 然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

not in与 not exists

not exists查询的效率远远高与not in查询的效率。

not in()子查询的执行顺序是：为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。

not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

权限管理

登录

mysql -uroot -p

初次登录没有密码

select user()

查看账号

创建用户

create user 'egon'@'1.1.1.1' identified by '123';

create user 'egon'@'192.168.1.%' identified by '123';

create user 'egon'@'%' identified by '123';

初始状态下，管理员root，密码为空，默认只允许从本机登录localhost

设置密码

mysqladmin -uroot password "123"

设置初始密码由于原密码为空，因此-p可以不用

mysqladmin -uroot -p"123" password "456"

修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码

mysql -h172.31.0.2 -uroot -p456

忘记密码

#1 关闭mysql net stop mysql

#2 在cmd中执行：mysqld --skip-grant-tables；或者：打开MySQL目录下的my.ini文件，在文件的[mysqld]后面最后添加一行“skip-grant-tables”，保存并关闭文件（linux在 /etc/my.cnf）,然后 net stop mysql、用管理员账户操作。

#3 net start mysql;在cmd中执行：mysql

#4 执行如下sql：

update mysql.user set authentication_string=password('') where user = 'root';

flush privileges;

#5 tskill mysqld #或taskkill -f /PID 7832

#6 重新启动mysql

更改密码

update mysql.user set password = password('...') where user = ' ..' and host = 'localhost';

flush privileges;

对所有库的授权 *.*

grant select on *.* to 'egon1'@'localhost' identified by '123';

只在user表中可以查到egon1用户的select权限被设置为Y

对某一数据库：db1.*

grant select on db1.* to 'egon2'@'%' identified by '123';

只在db表中可以查到egon2用户的select权限被设置为Y

对某一个表：db1.t1

grant select on db1.t1 to 'egon3'@'%' identified by '123';

只在tables_priv表中可以查到egon3用户的select权限

对某一个字段：

grant select (id,name),update (age) on db1.t3 to 'egon4'@'localhost' identified by '123';

可以在tables_priv和columns_priv中看到相应的权限

删除权限

revoke select on db1.* from 'egon'@'%';

总结

#授权：对文件夹，对文件，对文件某一字段的权限查看帮助：help grant 常用权限有：select,update,alter,delete all可以代表除了grant之外的所有权限

数据类型

数值

int

11个数字，int不够的话还有bigint（20个数字）

float/double

默认为（10,2）

日期时间

date

YYYY-MM-DD格式，在1000-01-01 和 9999-12-31之间

datetime

YYYY-MM-DD HH:MM:SS格式

time

HH:MM:SS

YEAR(2|4)

字符串

char(m)

如果内容小于指定长度，右边填充空格，默认长度为1

varchar(m)

查询速度慢，节省存储空间，必须指定长度

text

存储二进制大数据，最大长度65535

枚举

enum

单选只能在给定的范围内选一个值，如性别 sex 男male/女female

集合

set

多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

约束条件

PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录

一张表中必须有且只有一个主键。

primary key(ip,port) # 复合主键

FOREIGN KEY (FK) 标识该字段为该表的外键

NOT NULL 标识该字段不能为空

UNIQUE KEY (UK) 标识该字段的值是唯一的

unique(host,port) #联合唯一

AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT 为该字段设置默认值

缺省的默认值是NULL

UNSIGNED 无符号

ZEROFILL 使用0填充

其他

视图

view多表查询产生的临时表，可以存成视图，方便下次使用，并节省内存

create view teacher_view as select tid from teacher where tname='李平老师';

#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
复制代码

DROP VIEW teacher_view

触发器

trigger使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

#创建触发器 delimiter // CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN IF NEW.success = 'no' THEN #等值判断只有一个等号 INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 END IF ; #必须加分号 END// delimiter ;

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

存储过程

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

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

procedure把一堆sql语句放到procedure里头，通过调procedure的名字就可以执行一堆语句。

无参

delimiter // create procedure p1() BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now()); END // delimiter ; #在mysql中调用 call p1() #在python中基于pymysql调用 cursor.callproc('p1') print(cursor.fetchall())

有参

#in 仅用于传入参数用

delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN

select * from blog where id > n1 and id < n2;
END //
delimiter ;

#在mysql中调用
call p2(3,2)

#在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

#out 仅用于返回值用

delimiter // create procedure p3( in n1 int, out res int ) BEGIN select * from blog where id > n1; set res = 1; END // delimiter ; #在mysql中调用 set @res=0; #0代表假（执行失败），1代表真（执行成功） call p3(3,@res); select @res; #在python中基于pymysql调用 cursor.callproc('p3',(3,0)) #0相当于set @res=0 print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值 print(cursor.fetchall())

#inout 既可以传入又可以当作返回值

delimiter // create procedure p4( inout n1 int ) BEGIN select * from blog where id > n1; set n1 = 1; END // delimiter ; #在mysql中调用 set @x=3; call p4(@x); select @x; #在python中基于pymysql调用 cursor.callproc('p4',(3,)) print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p4_0;') print(cursor.fetchall())

drop procedure proc_name;

事务

transaction 同时成功，同时失败，比如转账

pymysql

try: cursor.execute(sql_1) cursor.execute(sql_2) cursor.execute(sql_3) except Exception as e: connect.rollback() # 事务回滚 print('事务处理失败', e) else: connect.commit() # 事务提交 print('事务处理成功', cursor.rowcount)# 关闭连接 cursor.close() connect.close()

索引

类似书的目录，用于优化查询，主要是key

primary key 主键

PRIMARY KEY(id,name):联合主键索引

unique key 无重复

UNIQUE(id,name):联合唯一索引

index key 索引键

-INDEX(id,name):联合普通索引

索引的两大类型

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 等索引；

用法

创建索引

创建表时　　CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) );

create table t1( id int, name char, age int, sex enum('male','female'), unique key uni_id(id), index ix_name(name) #index没有key );

CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;

create index ix_age on t1(age);

ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

alter table t1 add index ix_sex(sex);

删除索引

DROP INDEX 索引名 ON 表名字;

命中索引，提高效率

条件明确，避免条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、

选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

索引列不能参与计算，保持列“干净

在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率

最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

排序条件为索引，则select字段必须也是索引字段，否则无法命中

其他注意事项

- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合

数据备份

MySQL数据备份

#1. 物理备份：直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。

#2. 逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

使用mysqldump实现逻辑备份

mysqldump -h 服务器 -u用户名 -p密码数据库名 > 备份文件.sql

mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

mysqldump -uroot -p123 --all-databases > all.sql

恢复逻辑备份

[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

mysql> use db1; mysql> SET SQL_LOG_BIN=0; mysql> source /root/db1.sql

#3. 导出表：将表导入到文本文件中

SELECT... INTO OUTFILE 导出文本文件

mysql> SELECT * FROM school.student1 INTO OUTFILE 'student1.txt' FIELDS TERMINATED BY ',' //定义字段分隔符 OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来 LINES TERMINATED BY '\n' ; //定义换行符

LOAD DATA INFILE 导入文本文件

mysql> DELETE FROM student1; mysql> LOAD DATA INFILE '/tmp/student1.txt' INTO TABLE school.student1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '”' LINES TERMINATED BY '\n';

pymysql模块

pip3 install pymysql

链接、执行sql、关闭（游标）

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)

cursor.close()
conn.close()

if res:
print('登录成功')
else:
print('登录失败')

execute()之sql注入

# 原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
# print(sql)
# res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

增、删、改：conn.commit()

insert 多条记录
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
print(res)

conn.commit() #提交后才发现表中插入记录成功

查：fetchone，fetchmany，fetchall

res3=cursor.fetchone() res4=cursor.fetchmany(2) res5=cursor.fetchall()

获取插入的最后一条数据的自增ID

sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看0