MySQL（二）
2018-03-01 本文已影响0人
邹霉霉
pymysql
conn = pymysql.connect(host="localhost",
user="root",
password="",
database="day65",
charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select cid,caption from caption"
cursor.execute(sql)
result = cursor.fetchall()
# print(result)
cursor.close()
conn.close()
import pymysql
user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="localhost",
user="root",
password="",
database="day60")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,[user, pwd])
result = cursor.fetchone()
print(result)
if result:
sql1 ="select * from authority where authority.id in " \
"(select dep_id from user_auth WHERE user_id = %s )"
cursor.execute(sql1,[result[0]])
result1 = cursor.fetchall()
print(result1[0][1].encode("utf8"))
cursor.close()
conn.close()
pymysql执行存储过程
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
pymysql执行存储过程
视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
创建视图
--格式：CREATE VIEW 视图名称 AS SQL语句
CREATE VIEW v1 AS
SELET nid,
name
FROM
A
WHERE
nid > 4
修改视图
-- 格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELET A.nid,
B. NAME
FROM
A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
A.id > 2
AND C.nid < 5
删除视图
--格式：DROP VIEW 视图名称
DROP VIEW v1
使用视图
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
触发器
当对某张表做：增删改操作时，可以使用触发器自定义关联行为
# 查看触发器
SHOW TRIGGERS [FROM schema_name];
# 删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
# 创建触发器
-- 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,'张根');
-- NEW,代指新数据
-- OLD,代指老数据
函数
存储过程
存储过程是保存在MySQL上的一个别名，就是一堆SQL语句。用来替程序员写SQL语句
基本结构
-- 基本存储过程
delimiter //
create procedure p_add_teacher()
begin
SELECT * from student ;
insert into teacher(tname) values("祝元娜");
END//
delimiter ;
-- 调用
call p_add_teacher();
-- pymysql调用方式
cursor.callproc("p_add_teacher");
参数in,out,inout
-- 创建存储过程
delimiter \\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
事物
delimiter //
create procedure p4(
out p_return_code int
)
begin
declare exit handler for SQLEXCEPTION
begin
-- ERRORS
set p_return_code = 1;
rollback;
end;
declare exit handler for SQLWARNING
begin
-- WARNINGS
set p_return_code=2;
end;
start TRANSACTION;
DELETE from student where sid = 1;
INSERT into teacher(tname) values("加钱");
commit;
-- sucess
set p_return_code = 0;
end//
delimiter ;
set @t = 0;
call p4(@t);
select @t;
游标
conn.commit()
cursor.close()
conn.close()
print(result)
动态执行存储过程
delimiter \\
CREATE PROCEDURE p4 (
in nid int
)
BEGIN
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;
5. 动态执行SQL
索引
主键索引：加速查找 + 不能为空 + 不能重复
普通索引：加速查找
唯一索引：加速查找 + 不能重复
普通索引
create index 索引名称 on 表名(列名)
drop index 索引名称 on 表名
show index from userinfo;
create table tb1(
id int not null auto_increment primary key,
name char(32) not null,
email char(64) not null,
index ix_name (name)
)
唯一索引
create unique index 索引名称 on 表名(列名)
drop unique index 索引名称 on 表名
sql语句的执行计划
type类型
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。
- system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
- const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
- eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
- ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
- fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
- ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
- unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
- index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
- range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
- index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
- index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
- all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
索引未命中
- like '%xx'
select * from tb1 where name like '%cn';
- 使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
- !=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
组合索引和索引合并
组合索引：即联合索引，多个字段组合形成索引，采用最左前缀匹配。
索引合并:把多个单列索引组合使用
create index ix_name_email on userinfo3(name,email,)
组合索引效率>索引合并
组合索引采用最左前缀匹配
覆盖索引
能在索引文件中直接获取数据
select id from userinfo where id=12345
慢日志
基于内存修改
show variables like "%queries%"; -- 查看当前配置信息
set GLOBAL log_queries_not_using_indexes=ON;
set global slow_query_log=ON;
set global long_query_time=2;
基于配置文件
运行mysql服务前，加载配置信息
mysqld default-file="D:\mysql\my-default.ini"
分页
-- 上一页 192 193 194 195 [196] 197 198 199 下一页 19601-19610 19571
-- 下一页
select * from userinfo where id >19610 limit 10;
-- 上一页
select * from userinfo where id<19601 order by id desc LIMIT 10;
-- 跳转
select * from userinfo where id<19601 order by id desc LIMIT 20,10;
select * from userinfo where id<19601 order by id desc LIMIT 20,10;
select id from
(select id from userinfo where id > 19610 limit 30) as B order by B.id desc limit 10 )as t)
