常用的sql语句补充
添加字段
alter table t1 add age int 约束条件 after 字段;
# after 被添加的字段添加在某字段的后面
# after换成first后不加字段 字段添加在开头
面试题：如何在已经存储了一百万个数据的数据表中增加一个字段
修改字段
alter table t1 modify name varchar(64);
# modify修改字段数据类型
alter table t1 change name name1 varchar(64);
# change可以修改字段数据类型也可以修改字段名
删除字段
alter table t1 drop name;
python操作mysql
首先需要借助pymysql的模块(不是内置模块)
在项目内下载安装该模块
import pymysql
# 1.先连接mysql，得到一个连接对象
# mysql的服务端已经开启了，python此时是客户端，连接方法如下
conn = pymysql.connect(
host='127.0.0.1', # 当mysql在本地服务器时填写这个，如果连接的是远程mysql，写其的ip地址
port=3306, # mysql默认端口号3306
user='root', # 管理员id为root
password='123', # 输入用户的密码
db='db2', # 进入的数据库名
charset='utf8', # 字符格式
)
# 2.拿到游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 写了这行代码后就等于进入mysql界面，可以开始写sql代码了
# cursor=pymysql.cursors.DictCursor 代表返回值是字典的形式
# 不写括号内代码则返回元组，缺点是返回时不知其所属
# 3.写sql语句
sql = 'show tables'
# 4.执行，返回值是影响的行数
affect_row = cursor.execute(sql)
conn.commit() # 二次确认，在增删改数据时需要加这行代码
# 或者直接在连接时加入代码 autocommit=True 增删改数据时就不需要二次确认了
# 5.拿到结果
res = cursor.fetchall()
# fetchone是一次拿一个，fetchall是一次拿全部
print(res)
# 6.关闭游标
cursor.close()
sql注入
利用SQL中的一些特殊语法，绕过SQL语句
# 先写一段登陆的代码
# 在mysql中先建立一个存放用户名和密码的数据库
use db2;
create table userinfo(id int,username varchar(64),password varchar(64));
insert into userinfo values(1,'egon',123)；
# 再在python中写登陆代码
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db2',
charset='utf8',
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('username>>>').strip()
password = input('password>>>').strip()
sql = 'select * from userinfo where username="%s" and password="%s"'%(username,password)
cursor.execute(sql)
res = cursor.fetchone()
if res:
print('登陆成功')
else:
print('登录失败')
# 如果此时input用户第一次输入时输入了sql代码内包裹%s用到的"符号，则会直接修改原本的sql代码
# 例如，输入 egon" or 1=1
# 此时就不需要密码和用户名输入正确即可登陆
# 这就叫做sql注入
# 解决sql注入问题的办法
sql = 'select * from userinfo where username="%s" and password="%s"'
# 修改sql代码 后面不写%s写入
cursor.execute(sql,(username,password))
# 修改执行代码，将需要%s输入的数据写到sql后面组成元组
视图
什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
select * from student inner join score on student.sid=score.student_id;
# 得到的是一张虚拟表
为什么用视图
如果要频繁使用虚拟表内容时，可以不用重复查询
如何用视图
create view student2score as select * from student inner join score on student.sid=score.student_id;
# view视图关键字
# create view 视图名 as 搜索的虚拟表
强调：
1.在硬盘中，视图只有表结构，没有表数据文件
2.视图通常是用于查询，尽量不要修改视图中的数据
思考：开发过程中会不会去使用视图
不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能
触发器
在满足对某张表数据的增删改的情况下，自动触发的功能称之为触发器
为何要用触发器
触发器专门针对我们对某一张表数据增删改的行为，这类型为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法
'''
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for eache row
begin
sql语句
end
我们只需要写sql语句，其他的基本都不需要写
'''
# 针对插入
create trigger tri_after_insert_t1 after/before insert on 表名 for eache row
begin
sql代码
end
# after插入之前触发 before插入之后触发
# 针对删除
create trigger tri_after_insert_t1 after/before delete on 表名 for eache row
begin
sql代码
end
# 针对修改
create trigger tri_after_insert_t1 after/before updata on 表名 for eache row
begin
sql代码
end
'''
需要强调：
在书写sql代码的时候结束符是;而整个触发器的结束也需要;
这个时候就会出现语法冲突，需要我们临时修改结束符号
delimiter $$ # 以$$为结束符
delimiter ; # 以;为结束符
该语法只在当前窗口有效
'''
事务
什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
事务的作用
保证了对数据操作的数据安全性
事务的四个属性
事务应该具有4个属性：
原子性、一致性、隔离性、持久性
这四个属性通常称为ACID特性
原子性(atomicity)：一个事物是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做
# 只要有一个操作不成立，全部操作返回，不做操作
一致性(consistency)：事务必须是使数据库从一个一致性状态变到另一个一致性状态，一致性与原子性是密切相关的
# 两边数据或状态保持一致
隔离性(isnlation)：一个事务的执行不能被其他事务干扰，即一个事物内部的操作及使用的数据对并发的其他事物是隔离的，并发执行的各个事物之间不能互相干扰
# mysql中可以开n多个事务，事务互相之间不能互相干扰
持久性(durability)：持久性也称永久性(permanence)，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响
# 一旦提交事务就无法改变
开启事务
开启事务关键字：
start transaction;
在开启事务关键字之后的sql语句只要没有回滚或取消，全部归于一个事务
回滚关键字：
rollback;
提交关键字：
commit;
存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调用它的名字可以执行其内部的一对sql，类似于python中的自定义函数
理解：存储(动词)过程(名词)
基本使用
delimiter $$
create procedure p1()
# procedure 存储过程关键词 p1 赋的名
begin
select * from user;
# 写想要实现的功能，一大堆逻辑
end $$
delimiter ;
call p1();
# 调用
三种开发模型
第一种
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
第二种
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
第三种
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
创建存储过程
# 介绍形参特点 再写具体功能
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
# 如果res=0说明已被执行
如何用存储过程
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 1、直接在mysql中调用
set @res=10 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10); # 报错
call p1(2,4,@res);
# 查看结果
select @res; # 执行成功，@res变量值发生了变化
# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10)) # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')
# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
update user set balance=900 where id =1;
update user123 set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
函数
注意：函数与储存过程的区别，mysql内置函数只能在sql语句中使用
参考博客：
http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
# 按照年月分组，关键字date_format
流程控制
if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO # do 不管条件成不成立先执行一次
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
索引
索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
- primary key
# 主键
- unique key
# 唯一索引
- index key
# 普通索引
- foreign key
# foreign key不是用来加速查询用的，不在我们研究范围之内
索引的本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据
索引的影响
在表中有大量数据的前提下，创建索引速度会很慢
在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
b+树
类似于二分法：
将根索引分为几大树枝节点，每一块再分配给叶子节点，叶子节点存放真实数据，根和树枝节点存放虚拟数据，用于对比使用
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
聚集索引
聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。
myisam在建表的时候对应到硬盘有三个文件
innodb在建表的时候对应到硬盘有两个文件，frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中
特点:叶子结点放的一条条完整的记录
辅助索引
辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
select name from user where name='jason';
上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select age from user where name='jason';
上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3;
# 给gender加然而不用gender字段
select count(id)j from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
浙公网安备 33010602011771号