【数据库】基本数据库操作
基本数据库操作详细，对应《7天MySQL魔鬼训练营（入门到高手）》课程
关系型数据库：sqllite,db2,oracle,access,sql server,MySQL
非关系型数据库：MongoDB,redis
学员管理：
表结构：班级\学生\老师
班级表（第一天）：
id title
1 全栈4期
2 全栈5期
学生表：
id name 班级ID（FK）
1 张英杰 1
老师表（第一天）：
id name
1 林海峰
2 林狗
3 苑日天
老师班级关系表：
id 老师ID 班级ID
1 1 1
2 1 2
3 2 2
单表操作：
- 增
- 删
- 改
- 查
一对多操作：
- 增
- 删
- 改
- 查
多对多操作：
- 增
- 删
- 改
- 查
1. 关于连接
文件夹【数据库】 databases
文件【表】 tables
数据行【行】
数据行
数据行
连接：
默认：用户root
show databases;
use 数据库名称;
show tables;
select * from 表名;
select name,age,id from 表名;
mysql数据库user表
use mysql;
select user,host from user;
创建用户：
create user \'alex\'@\'192.168.1.1\' identified by \'123123\';
create user \'alex\'@\'192.168.1.%\' identified by \'123123\';
create user \'alex\'@\'%\' identified by \'123123\';
授权：
权限 人
grant select,insert,update on db1.t1 to \'alex\'@\'%\';
grant all privileges on db1.t1 to \'alex\'@\'%\';
revoke all privileges on db1.t1 from \'alex\'@\'%\';
DBA: 用户名密码
2. SQL语句
数据库转储dump
备份：数据表结构+数据
mysqldump -uroot db1 > db.sql -p
备份：数据表结构
mysqldump -uroot -d db1 > db.sql -p
执行文件：
create database db5;
mysqldump -uroot -d db5 < db1.sql -p;
操作文件夹
create database db2;
create database db2 default charset utf8; *****
show databases;
drop database db2;
操作文件
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10))engine=innodb default charset=utf8;
create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8; *****
create table t1(
列名 类型 null,
列名 类型 not null,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
# innodb 支持事务，原子性操作，可回滚
# myisam myisam 不支持回滚
auto_increment 表示：自增
primary key： 表示 约束(不能重复且不能为空); 加速查找
not null: 是否为空
3. 数据类型
数字：
tinyint
int
bigint
FLOAT
0.00000100000123000123001230123
DOUBLE
0.00000000000000000000100000123000123001230123
0.00000100000123000000000000000
decimal
0.1
字符串：
char(10) 速度快()
root
root
varchar(10) 节省空间
root
PS: 创建数据表定长列往前放
text
上传文件：
文件存硬盘
db存路径
时间类型
DATETIME
enum
set
4. 外键
-
建立多约束外键时，最后一行不要加逗号
好处：
1.节省空间
2.添加约束
create table userinfo(
uid bigint auto_increment primary key,
name varchar(32),
department_id int,
xx_id int,
constraint fk_user_depar foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
5. 作业
一个汉字在utf8中占3个字节
create table class(
cid int auto_increment primary key,
caption varchar(32)
)engine=innodb default charset=utf8;
create table student(
sid int auto_increment primary key,
sname varchar(32),
gender char(5)，
class_id int,
constraint fk_stu_class foreign key (class_id) references class(cid)
)engine=innodb default charset=utf8;
create table teacher(
tid int auto_increment primary key,
tname varchar(35)
)engine=innodb default charset=utf8;
create table course(
cid int auto_increment primary key,
cname varchar(32),
teacher_id int,
constraint fk_cou_tea foreign key (teacher_id) references teacher(tid)
)engine=innodb default charset=utf8;
create table score(
sid int auto_increment primary key,
student_id int,
corse_id int,
number int,
constraint fk_sco_stu foreign key (student_id) references student(sid),
constraint fk_sco_cor foreign key (corse_id) references course(cid)
)engine=innodb default charset=utf8;
6. 主键
主键：
一个表只能有一个主键
主键可以由多列组成（保证唯一即可）
primary key (nid, pid),
CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
num int(11),
primary key(nid,pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# t5有联合主键，则外键可以复合，如下
create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t5(nid,pid)
)engine=innodb default charset=utf8;
- 数据行（增删改查）
insert into tb1(name,age) values(\'alex\',18);
drop table tb1;
delete from tb1;
truncate table tb1;
delete from tb1 where id > 10
update tb1 set name=\'root\' where id > 10
select * from tb;
select id,name from tb;
7. 自增
desc t10; # 查看table结构属性
show create table t10;
show create table t10 \G; # 反转
alter table t10 AUTO_INCREMENT=20; # 在删除某行之后，自增值仍为原数，需要重新调整自增数
MySQL: 自增步长
基于会话级别：（每次登录的会话）
show session variables like \'auto_inc%\'; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10; 设置起始值
基于全局级别：
show global variables like \'auto_inc%\'; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;
SqlServer：自增步长：（可直接在单独table中设置步长与起始值）
基础表级别：
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
8. 唯一索引
create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名), # 组合需唯一，联合唯一索引
constraint ....
)
#
1 1 1
2 1 2
PS:
唯一：
约束不能重复（**可以为空**）
PS: 主键不能重复（**不能为空**）
加速查找
9. 外键的变种
9.1 一对多
a. 用户表和部门表
用户：
1 alex 1
2 root 1
3 egon 2
4 laoyao 3
部门:
1 服务
2 保安
3 公关
===》 一对多
9.2 一对一
b. 用户表和博客表
用户表：
1 alex
2 root
3 egon
4 laoyao
博客表：（单人只能单一博客）
FK() + 唯一 （unique：单列不能重复）
1 /yuanchenqi/ 4
2 /alex3714/ 1
3 /asdfasdf/ 3
4 /ffffffff/ 2
===> 一对一
- 某些人能操作数据表
- 上述浪费password空间，调整为info与admin表
create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table admin(
id int not null auto_increment primary key,
username varchar(64) not null,
password VARCHAR(64) not null,
user_id int not null,
unique uq_u1 (user_id),
CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;
9.2 多对多
示例1：
用户表
相亲表
示例2：unique 联合索引
用户表
主机表
用户主机关系表
===》多对多 （双向外键一对多）
create table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;
create table user2host(
id int auto_increment primary key,
userid int not null,
hostid int not null,
unique uq_user_host (userid,hostid),
CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
10. 数据行操作补充
增
insert into tb11(name,age) values(\'alex\',12);
insert into tb11(name,age) values(\'alex\',12),(\'root\',18);
insert into tb12(name,age) select name,age from tb11;
删
delete from tb12;
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name=\'alex\'
delete from tb12 where id >=2 and name=\'alex\'
drop table tb1;
delete from tb1;
truncate table tb1;
改
update tb12 set name=\'alex\' where id>12 and name=\'xx\'
update tb12 set name=\'alex\',age=19 where id>12 and name=\'xx\'
查
select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name =\'xxx\';
select id,name as cname from tb12 where id > 10 or name =\'xxx\'; # as cname别名
select name,age,11（11为额外添加的列） from tb12;
其他：
select * from tb12 where id != 1 （<>也为不等于）
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5 and 12;
通配符：
select * from tb12 where name like "a%" #以a开头的字符串
select * from tb12 where name like "a_" #以a开头的后一位占位
分页：（便于查看，且降低服务端或客户端压力）
select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10; # 起始位置，之后的多少条
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20; # offset为起点
python实现分页：
# page = input(\'请输入要查看的页码\')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
排序：
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc; 先按照age降序，如果中间列重复，则再按照id降序排
取后10条数据
select * from tb12 order by id desc limit 10;
分组：
select count(id),max(id),part_id from userinfo5 group by part_id;
聚合函数
count
max
min
sum
avg
**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
where后不能加聚合函数
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作：
select * from userinfo5,department5（笛卡尔积） 需声明关系，如下：
select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo5左边全部显示
# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
将出现null时一行隐藏(即两表中不对应的项目，这些项目就会隐藏)
select * from
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id
select count(id) from userinfo5; 计算表的数据行个数
上述取*会出错，因为会有相同的列名重复，因此
select
score.sid,
student.sid
from
score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.tid
回顾
数据行：
临时表：(select * from tb where id>10)
指定映射：
select id,name,1,sum(x)/count()
条件：
case when id>8 then xx else xx end
三元运算： if(isnull(xx),0,1)
补充：
左右连表： join
上下连表： union
# 自动去重（需要相同列数）
select id,name from tb1
union
select num,sname from tb2
# 不去重
select sid,sname from student
UNION ALL
select sid,sname from student
11. pymysql
11.1 查询及SQL注入 (13.7)
import pymysql
user = input(\'username\')
pwd = input(\'password\')
conn = pymysql.connect(host=\'localhost\',
user=\'root\', password=\'210071Xzc12145\',
database=\'sql_exercise\', charset=\'utf8\')
cursor = conn.cursor()
sql = "select * from mysql where username=\'%s\' and password=\'%s\'" % (user, pwd)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
print("登陆成功")
else:
print("登陆失败")
print(result)
cursor = conn.cursor()
cursor.execute("select * from hosts")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode=\'relative\') # 相对当前位置移动
cursor.scroll(2,mode=\'absolute\') # 相对绝对位置移动
# 重点使用，游标设置为字典类型
# 列表中套字典，知道每一列什么意思
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute(sql)
-
上述字符串直接在sql指令上拼接，容易SQL注入，即
-
SQL注入简介: SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。 -
SQL注入攻击的总体思路: 1.寻找到SQL注入的位置 2.判断服务器类型和后台数据库类型 3.针对不通的服务器和数据库特点进行SQL注入攻击
username时输入：dadas\' or 1=1 --
password时输入：
在数据库服务端，指令语句则显示为 select * from testsql where username=\'dadas\' or 1=1 -- and password=\'\'
因为条件后面username=\'dadas\' or 1=1 用户名等于 \'dadas\' 或 1=1 那么这个条件一定会成功；然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。
-
解决方案：不要自己拼接字符串，让PyMySQL模块去拼接
-
# sql = "select * from mysql where username=%s and password=%s" # cursor.execute(sql, [user, pwd]) # 以列表形式 # cursor.execute(sql, (user, pwd)) # 以元组形式 sql = "select * from mysql where username=%(u)s and password=%(p)s" cursor.execute(sql, {\'u\': user, \'p\': pwd}) # 以字典形式 result = cursor.fetchone() 注意：去掉%s的引号。
11.2 增删改查
‘增删改’操作时，需要增加 conn.commit()
import pymysql
conn = pymysql.connect(host=\'localhost\',
user=\'root\', password=\'210071Xzc12145\',
database=\'sql_exercise\')
cursor = conn.cursor()
sql = "insert into mysql(username, password) value(\'root\', \'123123\')"
# 受影响的行数
r = cursor.execute(sql)
# 提交，不然无法保存新建或者修改的数据
conn.commit()
cursor.close()
conn.close()
user = \'tyty\'
pwd = \'123123\'
cursor = conn.cursor()
sql = "insert into mysql(username, password) value(%s, %s)"
# 受影响的行数
r = cursor.execute(sql, (user, pwd))
conn.commit()
- 提交多个数据行
import pymysql
conn = pymysql.connect(host=\'localhost\',
user=\'root\', password=\'210071Xzc12145\',
database=\'sql_exercise\')
cursor = conn.cursor()
sql = "insert into mysql(username, password) value(%s, %s)"
# 受影响的行数
r = cursor.executemany(sql, [(\'rt\', \'312\'), (\'yu\', \'fsafas\')]) # executemany 只适用于insert时用
conn.commit()
cursor.close()
conn.close()
11.3 获取新创建数据自增ID
import pymysql
conn = pymysql.connect(host=\'127.0.0.1\', port=3306, user=\'root\', passwd=\'123\', db=\'t1\')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
# 获取最新自增ID
new_id = cursor.lastrowid
cursor.close()
conn.close()
12. 需求设计
基于用户权限管理
参考表结构：
用户信息
id username pwd
1 alex 123123
权限
1 订单管理
2 用户劵
3 Bug管理
....
用户类型&权限
1 1
1 2
2 1
3 1
程序：
用户登录
--------------------------------------------------------------------------------------
基于角色的权限管理
用户信息
id username pwd role_id
1 alex 123123 1
2 eric 123123 1
权限
1 订单管理
2 用户劵
3 Bug管理
....
角色表：
1 IT部门员工
2 咨询员工
3 IT主管
角色权限管理
1 1
1 2
3 1
3 2
3 3
===>
1. 基于角色的权限管理
2. 需求分析
13. 视图、触发器、函数、存储过程、索引
课程代码：https://www.cnblogs.com/wupeiqi/p/5713323.html
13.1 视图
视图：只能写查询操作
100个SQL：
88个: 用到了v1映射的语句
select .. from v1 :把视图当成一张表进行调用
select asd from v1
某个查询语句设置别名，日后方便使用
临时表的别称
- 创建
create view 视图名称 as SQL
PS: 虚拟
- 修改
alter view 视图名称 as SQL
- 删除
drop view 视图名称;
13.2 触发器
查询 不会引发触发器
当对某张表做：增删改操作时，可以使用触发器自定义关联行为
insert into tb (....)
delimiter 为更改结束符 从 ; --> //
以使得begin到end中所有语句被执行，否则，就只执行到分号为止。
-- delimiter //
-- create trigger t1 BEFORE INSERT on student for EACH ROW
-- BEGIN
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- END //
-- delimiter ;
--
-- insert into student(gender,class_id,sname) values(\'女\',1,\'陈涛\'),(\'女\',1,\'张根\');
-- each row 即为每一行都执行一遍触发器中的内容（上述向student表插入两行，即执行两遍触发器）
-- 关键字 NEW,代指新数据：即上述insert语句执行时，其中要插入的数据继承到NEW，并执行触发器操作
-- 关键字 OLD,代指老数据：delete,update会用，即要删除的数据为老数据
13.3 函数
内置函数：
执行函数 select CURDATE();
blog（对blog中每一日进行分组）
id title ctime
1 asdf 2019-11-11 11:11
2 asdf 2019-11-11 12:11
3 asdf 2019-10-10 13:11
4 asdf 2019-10-10 14:11
select ctime,count(1) from blog group by ctime (直接group by无法实现分组)
--需要先时间格式化（到 年-月）
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group by DATE_FORMAT(ctime, "%Y-%m")
2019-11 2
2019-10 2
自定义函数：其中不能写查询语句
自定义函数(有返回值)：
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int default 0; 声明变量
set num = i1 + i2;
return(num);
END \\
delimiter ;
SELECT f1(1,100); # 用select进行调用函数
13.4 存储过程
- 存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行
与视图区别：
视图是当成一张临时表进行查询，
存储过程是若干查询语句的累加，调用更简单（用于替代程序员写SQL语句），能够实现更多功能
优点：在查询语句较长的时候，不用进行网络传输，直接输入调用指令，操作保存在数据库中的存储过程即可
······存储过程无返回值return，但可以写关键字out或inout进行伪造return传参······
方式一：
MySQL: 存储过程
程序：调用存储过程
方式二：
MySQL：。。
程序：SQL语句
方式三：
MySQL：。。
程序：类和对象（转化成SQL语句）
1. 简单存储过程
delimiter //
create procedure p1() #加括号为传参
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END //
delimiter ;
call p1() # 用 call 进行调用存储过程
cursor.callproc(\'p1\') # pymysql中调用的语句
2. 传参数（in,out,inout）
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from student where sid > n1;
END //
delimiter ;
call p2(12,2)
cursor.callproc(\'p2\',(12,2))
3. 参数 out :只做输出，不做输入
delimiter //
create procedure p3(
in n1 int,
out n2 int
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
END //
delimiter ;
-- 加@为设置session级别的变量
set @v1 = 10; --因为此时@v1作为输出变量，因此在begin内n2是没有值的，因为其不是in变量
call p2(12,@v1)
select @v1; --变量@v1已在内部被修改为n2,并进行了输出，并用select显示出来
-- 返回时，显示两个结果。即查询结果 和 参数修改后的结果
pymysql中调用：
cursor.callproc(\'p3\',(12,2))
r1 = cursor.fetchall()
print(r1) # 返回查询结果
cursor.execute(\'select @_p3_0,@_p3_1\') #规定写法，pymysql认识的语句
r2 = cursor.fetchall()
print(r2) # 返回查询结果及修改后的值（为什么有结果集又有out伪造的返回值？见下：）
3. 参数 inout :既可以传入又可以当作返回值
delimiter //
create procedure p3(
in n1 int,
inout n2 int
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
END //
delimiter ;
作为inout变量，此时begin内n2有值，为其传入值
为什么有结果集又有out伪造的返回值？
当在其中进行多出插入操作时，外界不知道其能不能最终执行成功左右的语句，所以需要out值进行传参，告诉人员过程进行得如何。
delimiter //
create procedure p3(
in n1 int,
out n2 int 用于标识存储过程的执行结果 1,2,3 用以上数字代表不同的执行结果
)
BEGIN
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
END //
delimiter ;
13.5 事务
事务：原子性操作，即数据行操作，发生错误回滚，正确时提交
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2; --正常代码
END //
delimiter ;
=============== 代码如下 ================
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values(\'seven\');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END\\
delimiter ;
13.6 游标
-
适用类型：对每一行数据进行单独操作时才用游标，全部表数据则直接用update
-
将A表数据导入到B表，如果id相同，则在B表加row_id存储
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --检测游标还有没有数据
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num; --把cursor数据赋值给 row_id,row_num
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimter ;
13.7 动态执行SQL（防SQL注入）
sqlServer端防注入
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句
set @xo = arg; -- arg必须是session的变量，所以要单独设置
PREPARE xxx FROM \'select * from student where sid > ?\';
-- 上边xxx代表的后边的语句
EXECUTE xxx USING @xo; --@xo动态替换‘？’
DEALLOCATE prepare xxx;
end //
delimter ;
call p7("select * from tb where id > ?",9) --9替换？
===> 下面是真实代码，上边是伪代码
delimiter \\
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM \'select * from student where sid > ?\';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;
14. 索引
内容回顾：
1. 数据库是什么
2. MySQL安装
3. 用户授权
4.
数据库操作
-
数据表
- 数据类型
- 是否可以为空
- 自增
- 主键
- 外键
- 唯一索引
数据行
增
删
改
查
排序： order by desc/asc
分组：group by
条件：where
连表：
left join
right join
inner join
临时表：
通配符：like
分页：limit
组合：
union
视图（虚拟）
触发器
函数 select xx(f)
存储过程
- 游标
- 事务
- 结果集+ “返回值”
pymysql
- 连接 connect(...)
- 操作（游标）
- 增删改 -> commit
- 查 -> fetchone,fetchall
- SQL注入
- 调用存储过程：
callproc(\'p1\',参数)
select @_存储过程名称_0
- 关闭游标
- 关闭连接
14.1 索引
作用：
- 约束
- 加速查找
索引：
- 主键索引：加速查找 + 不能为空 + 不能重复
- 普通索引：加速查找
- 唯一索引：加速查找 + 不能重复（可以为空）
- 组合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引
加速查找：
快：
select * from tb where name=\'asdf\' （慢）
select * from tb where id=999 （快）
假设：
id name email
...
...
..
1.无索引：从前到后依次查找
2. 索引：
id 创建额外文件(某种格式存储)
name 创建额外文件(某种格式存储)
email 创建额外文件(某种格式存储) create index ix_name on userinfo3(email);
name email 创建额外文件(某种格式存储)
索引种类（某种格式存储）：
hash索引：
原理：将所建索引转换成hash值索引表，并配套有数据存储地址。之后查找就直接匹配哈希值，并到地址中查询。
hash表排列顺序与真实表不同
优点： 单值快
缺点： 找范围不行
btree索引： btree索引（innodb）
二叉树，堆排序之类的，按照特定顺序排列，有利于范围查找
========》 结果：快 《========
建立索引：
- a. 额外的文件保存特殊的数据结构
- b. 查询快；插入更新删除慢
- c. 命中索引（需要用法得当）
select * from userinfo3 where email=\'asdf\';（命中创建的email索引表）
select * from userinfo3 where email like \'asdf\'; （没有命中，慢，没在索引表中查）
...
主键索引：
系统默认创建了主键索引
普通索引：
- create index 索引名称 on 表名(列名,)
- 对于文本太长的列（text），必须使用前n个字符建立索引
- create index 索引名称 on 表名(列名(16)) # 利用该列前16个字符建立索引
- drop index 索引名称 on 表名
唯一索引：
- create unique index 索引名称 on 表名(列名)
- drop unique index 索引名称 on 表名
组合索引(最左前缀匹配)：
- create unique index 索引名称 on 表名(列名,列名)
- drop unique index 索引名称 on 表名
- create index ix_name_email on userinfo3(name,email,)
- 最左前缀匹配
select * from userinfo3 where name=\'alex\';
select * from userinfo3 where name=\'alex\' and email=\'asdf\';
前两个走索引
select * from userinfo3 where email=\'alex@qq.com\';
上面的不走索引
说明：（A,B,C）:支持：(A);(A,B);(A,B,C);(A,C)
不支持：(B,C);(C)
组合索引效率 > 索引合并 ****
组合索引（直接建立组合索引的一个索引文件，有两个组合可能）
- (name,email)
select * from userinfo3 where name=\'alex\' and email=\'asdf\';
select * from userinfo3 where name=\'alex\';
索引合并：(各项分别建立单独索引，两个单独的索引合起来叫索引合并，但是有三种组合可能)
- name
- email
select * from userinfo3 where name=\'alex\' and email=\'asdf\';
select * from userinfo3 where name=\'alex\';
select * from userinfo3 where email=\'alex\';
名词：
覆盖索引：（查询的行为）
- 在索引文件中直接获取数据
select email from tableA where email=“A”，直接从索引文件中查找
索引合并：
- 把多个单列索引合并使用
select * from userinfo where email="A" and id=999;
14.2 频繁查找的列创建索引
- 创建索引
- 命中索引 ************（非常重要）
- like \'%xx%\' 效率很低，避免使用
select * from tb1 where email like \'%cn%\';
- 使用函数 避免使用函数，否则数据库先执行函数，再匹配数据表
select * from tb1 where reverse(email) = \'wupeiqi\';
- or 关键字
select * from tb1 where nid = 1 or name = \'seven@live.com\';
特别的：当or条件中有未建立索引的**列**才失效，否则，会先忽略没有索引的条件，先走索引
select * from tb1 where nid = 1 or name = \'seven\';
select * from tb1 where nid = 1 or name = \'seven@live.com\' and email = \'alex\'
上面的一行，若name没有索引，会自动忽略，而去执行第一个与最后一个索引
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然会很慢
select * from tb1 where email = 999;
- !=
select * from tb1 where email != \'alex\'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > \'alex\'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select name from tb1 order by email desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
其他注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
14.3 时间
explain 执行计划：让mysql预估执行操作(一般正确，不会真正去执行)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email
慢：
select * from userinfo3 where name=\'alex\'
explain select * from userinfo3 where name=\'alex\'
type: ALL(全表扫描肯定慢)
特殊：select * from userinfo3 limit 1;这个也很快，并非explain完全正确
快：
select * from userinfo3 where email=\'alex\'
type: ref(走索引,因为之前这个email已经建立了索引)
14.4 DBA工作
慢日志：
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句。
具体指运行时间超过long_query_time值的 SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 内存
show variables like \'%query%\'
set global 变量名 = 值
- 配置文件
指定配置文件路径
mysqld --defaults-file=\'E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini\'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务
14.5 分页*
a. select * from userinfo3 limit 20,10;
起始值越大，翻页越慢。
b.
- 不让看
- 索引表中扫：
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
速度不是最优方案
- 最优方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页
# max_id
# min_id
下一页：
select * from userinfo3 where id > max_id limit 10;
select * from userinfo3 where id > max_id+10 limit 10;
上一页：
select * from userinfo3 where id < min_id order by id desc limit 10;
2. 上一页 192 193 [196] 197 198 199 下一页 （从196到199）
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)
## 其中，(select id from userinfo3 where id > max_id limit 30) 为197，198，199三页中所有数据，
从小往大排，因此，如果取199页的数据，需要建立临时表倒叙，取前10个，即为199当页数据。
c. *****重点*****：
id不连续，所以无法直接使用id范围进行查找
（id不一定是连续的，所以不能limit 0，10）
15. ORM框架SQLAchemy
参考文件：https://www.cnblogs.com/wupeiqi/p/5713330.html
SQLAlchemy：SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
-
补充：数据库方言（dialect）是什么？（https://foofish.net/what-is-db-dialect.html）
-
ORM框架：SQLAlchemy - 作用： 1. 提供简单的规则 2. 自动转换成SQL语句 - DB first: 先手动创建数据库以及表 -> ORM框架 -> 自动生成类 - code first： 先手动创建类、数据库 -> ORM框架 -> 自动创建表 a. 功能 - 创建数据库表 - 连接数据库（非SQLAlchemy,pymyql,mysqldb,....） - 类转换SQL语句 - 操作数据行 增 删 改 查
子查询：select * from (select * form tb) as B
q1 = session.query(UserType).filter(UserType.id > 0).subquery() # 需要先声明子查询
result = session.query.(q1).all()
select id from (select * from users) from usertype
ret = session.query(UserType.id, session.query(User).subquery()).all() # 这样写，会造成笛卡尔积,subquery()查询的还是一张表
ret = session.query(UserType.id, session.query(User).as_scalar()).all() #
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, CHAR, VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:210071Xzc12145@127.0.0.1:3306/sql_exercise",
max_overflow=5)
Base = declarative_base()
class UserType(Base):
__tablename__ = \'usertype\'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(32), nullable=True, index=True)
class Users(Base):
__tablename__ = \'users\'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=True, default=\'sf\', index=True)
# string 包含 char 与 varchar
extra = Column(String(16), unique=True)
# 创建外键
user_type_id = Column(Integer, ForeignKey(\'usertype.id\'))
# 该行语句不在数据库中执行，通过建立relationship，自动创建关联，之后不同通过连表进行查询
# **********relationship写在foreignKey的类里*************
user_type = relationship(\'UserType\')
# __table_args__ = (
# UniqueConstraint(\'id\', \'name\', name=\'uix_id_name\'),
# Index(\'ix_id_name\', \'name\', \'extra\'), # 创建索引，索引名放置在最前
# )
# 创建数据库
def init_db():
Base.metadata.create_all(engine)
# 删除数据库
def drop_db():
Base.metadata.drop_all(engine)
class fourDos():
def __init__(self):
# 从 max_overflow 获取一个 session 来操作数据库
Session = sessionmaker(bind=engine)
self.session = Session() # 与session.commit()搭配，提交
# 类 --> 表
# 对象 --> 行
def add(self):
# 增
obj = Users(name=\'alex0\', extra=\'sn\')
self.session.add(obj) # 单个添加
# 多个添加
self.session.add_all([
Users(name=\'alex1\', extra=\'sd\'),
Users(name=\'alex2\', extra=\'sh\'),
])
self._closeSession()
def deleteItem(self):
self.session.query(Users).filter(Users.id > 2).delete()
self._closeSession()
def updateItem(self):
self.session.query(Users).filter(Users.id > 1).update({\'name\': \'jason\'})
# 字符串类型的相加，用该参数 synchronize_session=False
self.session.query(Users).filter(Users.id > 1).update({Users.name: Users.name + "099"}, synchronize_session=False)
# 数字类型的相加，synchronize_session="evaluate"
# self.session.query(Users).filter(Users.id > 2).update({"num": Users.user_type_id + 1}, synchronize_session="evaluate")
self._closeSession()
def selectItem(self):
# self.session.query(Users) 本质为查询的sql语句
# self.session.query(Users).all() 本质为查询到的所有行的对象list
# 所以可有下面的：
# user_list = self.session.query(Users).all()
# for row in user_list:
# print(row.id, row.name)
ret01 = self.session.query(Users).all() # 只返回对象
ret02 = self.session.query(Users.name, Users.extra).all()
ret03 = self.session.query(Users).filter_by(name=\'alex0\').all() # 只返回对象
return ret03
def _closeSession(self):
self.session.commit()
self.session.close()
if __name__ == \'__main__\':
init_db()
example01 = fourDos()
# example01.add()
# example01.deleteItem()
# example01.updateItem()
ret = example01.selectItem()
print(ret)
去除.all相当于迭代器，直接print，可打印ORM执行的SQL语句，进而调整查询指令
* 连表操作
user_list = session.query(Users.name, UserType.title).join(UserType, isouter=True) # 默认外键关联
print(user_list)
for row in user_list:
print(row[0], row[1], row.name, row.title) #可通过索引或名称查询
#该行语句不在数据库中执行，通过建立relationship，自动创建关联，之后不同通过连表进行查询
# user_type = relationship(\'UserType\')
# 建立relationship之后，不用连表查询
user_list = session.query(Users)
print(user_list)
for row in user_list:
print(row.name, row.id, row.user_type.title) #可通过索引或名称查询
#建立relationship，并添加backref参数，可建立反向操作，可通过UserType表反向进行表连接
# user_type = relationship(\'UserType\', backref="init")
user_list = session.query(UserType)
print(user_list)
for row in user_list:
print(row.name, row.id, init) #可通过init包涵的对象列表进行查询
版权声明：本文为holaplace原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。