视图使用 SELECT * from vw1;
删除视图 drop view vw1
修改视图
-
-- 修改视图vw1, 修改内容直接写上现今要执行的sql语句即可
-
ALTER VIEW vw1 AS
-
SELECT
-
userinfo.nid,userinfo.
`name` AS uname, color.tag AS color
-
FROM
-
userinfo
-
LEFT JOIN color ON userinfo.color_nid = color.nid
** 在介绍触发器, 存储过程, 函数以及事务之前我们先简单过一下mysql中的条件和循环语句块 **
条件判断
-
if 条件 then
-
普通sql语句;
-
elseif 条件 then
-
普通sql语句;
-
else
-
普通sql语句;
-
end
if;
循环语句
while循环
-
while 条件 do
-
普通sql语句;
-
end while;
repeat循环
-
repeat
-
普通sql语句;
-
until 条件;
-
end repeat;
loop循环
-
loop_label: 标签名
-
普通sql语句;
-
-- 继续循环
-
iterate loop_label;
-
-- 跳出循环
-
leave loop_label;
-
end loop;
** 触发器是在对某个表执行操作(增加, 删除和修改)的前后执行用户特定的行为, 比如对其他的表执行增删改的操作 **
创建触发器
-
-- 定义结束符为$$, 在mac和linux中很有必要
-
delimiter $$
-
-- 如果存在tri_before_update_userinfo触发器则删除, 方便调试和修改
-
DROP TRIGGER
if EXISTS tri_before_update_userinfo $$
-
-- 创建tri_before_update_userinfo触发器, 该触发器会在更新userinfo表之前执行begin和end之间的内容(before表示之前, after表示之后)
-
CREATE TRIGGER tri_before_update_userinfo BEFORE UPDATE ON userinfo FOR EACH ROW
-
BEGIN
-
-- 如果在userinfo表中更改name为tom的行则会在color表中插入一行(old表示原来的数据)
-
IF old.name =
'tom' THEN
-
INSERT INTO color(tag) VALUES(
'black');
-
-- 如果在userinfo表中有name修改后为cc则会在color表中插入一行(
new表示修改后的数据)
-
ELSEIF
new.name =
'cc' THEN
-
INSERT INTO color(tag) VALUES(
'yellow');
-
END IF;
-
end $$
-
delimiter ;
执行触发器只需要修改userinfo表中的数据即可; 对于update操作既有old又有new关键字, 对于insert操作只有new关键字, 对于delete操作只有old关键字
删除指定触发器 drop trigger if exists 触发器名
** 存储过程相当于一些sql语句的堆积, 但是sql语句执行后的结果集以及变量都可以返回给用户; 而函数不能返回结果集, 仅仅是变量的操作 **
创建存储过程
-
delimiter $$
-
DROP PROCEDURE IF EXISTS p1 $$
-
CREATE PROCEDURE p1(
-
-- 声明仅用传入参数用的整型形参
-
in in_1 INT,
-
-- 声明既可以传入又可以当返回值的整型形参
-
INOUT inout_1
int,
-
-- 声明仅用做返回值的整型形参
-
OUT out_1 INT
-
)
-
BEGIN
-
-- 声明语句块中的临时变量
-
DECLARE tmp_in_1 INT;
-
DECLARE tmp_inout_1 INT;
-
-- 赋值语句
-
SET tmp_in_1 = in_1;
-
set tmp_inout_1 = inout_1;
-
SET out_1 = tmp_in_1 + tmp_inout_1;
-
-- 正常的sql查询语句
-
SELECT * from userinfo LIMIT in_1, inout_1;
-
end $$
-
delimiter ;
使用存储过程
-
-- 设置用户变量传值,in类型可不用变量传值, out类型不能传入值,
-
-- set @in_1_tmp=
1;
-
set @inout_1_tmp=
3;
-
-- 调用存储过程, 传入参数
-
CALL p1 (
1,@inout_1_tmp,@out_1_tmp);
-
-- 取得存储过程的执行结果, 包括sql语句结果集以及变量值(in, inout以及out类型变量都能取得他们的值)
-
SELECT @in_1,@inout_1_tmp,@out_1_tmp;
删除存储过程 drop procedure 存储过程名
** mysql中有许多对变量进行操作的内置函数, 同时我们也可以自定义函数 **
内置函数第一部分
-
SELECT
-
CHAR_LENGTH(
"test") AS
"字符串长度",
-
-- 拼接的任意一个参数为null, 则拼接结果为null
-
CONCAT(
"C://",
"workplace") AS
"字符串拼接",
-
CONCAT_WS(
"-",
"nick",
"tom") AS
"自定义连接符拼接",
-
CONV(
'c',
16,
10) AS
"进制转换",
-
FORMAT(
10000.00041,
4) AS
"格式化数字",
-
INSERT(
"teach",
1,
2,
'xxxx') AS
"字符串替换"
内置函数第二部分
-
SELECT
-
INSTR(
"mttm",
"tt") AS
"字串位置",
-
LEFT(
"hello, world",
5) AS
"从左截取字符串",
-
LOWER(
"HELLO") AS
"转换小写",
-
UPPER(
"world") AS
"转换大写",
-
LTRIM(
" test ") AS
"开始去空格",
-
RTRIM(
" now ") AS
"结尾去空格",
内置函数第三部分
-
SELECT
-
LOCATE(
"tt",
"hehettlolo",
2) AS
"获取子序列位置",
-
REPEAT(
" | roor",
5) AS
"重复字符串生成",
-
REPLACE(
"hello",
"ll",
"ww") AS
"字符串替换",
-
REVERSE(
"123456") AS
"字符串反转",
-
RIGHT(
"hello",
3) AS
"从右截取字符串",
-
SUBSTRING(
"hello, test, world" FROM
-11 FOR
4) AS
"自定义截取字符串",
-
SPACE(
5) AS
"返回空格字符串",
-
TRIM(
" test ") AS
"去除空格"
创建函数
-
delimiter $$
-
DROP FUNCTION IF EXISTS func1 $$
-
CREATE FUNCTION func1(
-
-- 定义整型形参
-
i1
int,
-
i2
int
-
)
-
-- 定义返回参数类型
-
RETURNS INT
-
BEGIN
-
DECLARE tmp INT DEFAULT
0;
-
SET tmp = i1 + i2;
-
RETURN tmp;
-
END $$
-
delimiter ;
使用自定义函数 SELECT func1(1,1);
删除自定义函数 DROP FUNCTION IF EXISTS 函数名;
** 事务的本质就是在存储过程中将多条sql语句作为一个原子操作来执行, 其中之一未执行成功则直接回滚到原始状态 **
创建事务
-
delimiter $$
-
CREATE PROCEDURE tp1(
-
-- 定义返回结果参数
-
OUT num_flag_retunr INT
-
)
-
BEGIN
-
-- sql执行发生异常时候, 返回值为
2, 并回滚到原始状态
-
DECLARE EXIT HANDLER FOR SQLEXCEPTION
-
BEGIN
-
SET num_flag_retunr=
2;
-
ROLLBACK;
-
END;
-
-- sql语言发生警告时候, 返回值为
1, 并回滚到原始状态
-
DECLARE EXIT HANDLER FOR SQLWARNING
-
BEGIN
-
SET num_flag_retunr=
1;
-
ROLLBACK;
-
END;
-
-- 开始事务具体要执行的内容, 正确则提交执行结果否则执行上面的异常
-
START TRANSACTION;
-
DELETE FROM userinfo WHERE nid=
4;
-
INSERT INTO color(tag) VALUES(
"green");
-
COMMIT;
-
SET num_flag_retunr=
0;
-
END $$
-
delimiter ;
执行事务
-
CALL tp1(@out_1);
-
SELECT @out_1;
SELECT @out_1;
删除事务 DROP PROCEDURE IF EXISTS 事务名
** 索引相当于为我们指定的列建立一个目录, 根据目录我们能快速查找到所需数据 **
索引种类
普通索引 index : 仅仅加速查询, 无约束
唯一索引 unique : 加速查询, 指定的列不能重复, 可出现一次null
主键索引 primary key : 加速查询, 列值唯一切不能为null
组合索引 : 多列作为共同体组成索引, 效率高于多个索引列合并查询
全文索引: 数据库存储时候对存储内容进行分词存储便于搜索查询
创建普通索引 CREATE INDEX name_index ON userinfo (name) 或者在创建表时候在表末尾加上 index 索引名 (列); 对于其他索引也是类似
删除索引 drop 索引名 on 表名;
查看当前表那些字段有索引 SHOW INDEX FROM userinfo;
以下情况即使创建了索引也不会使用索引:
%开头的模糊匹配条件: select * from 表名 where 列名 like '%其他';
对建立索引的列使用函数查询: select * from 表名 where 函数名(列名) = 其他条件
列类型匹配错误的条件查询时候 : select * from 表名 where 列名 = 不正确的类型;
当or条件中含有未建立索引的列时: select * from 表名 where 条件01 or 条件02';
匹配条件为不等于时候(主键例外) : select * from 表名 where 非主键列 != 其他;
匹配条件为大于的时候(主键或索引为整型例外) : select * from 表名 where 列名 > 其他;
排序时候选择的映射与排序列不一样即使都是索引列也不走索引(主键例外) : select 索引列-01 from 表名 order by 索引列-02 desc;
组合索引为 索引列-01和索引列-02, 当先查索引列-02时候也不走索引
用执行计划可以详细显示sql语句(使用查询语句才有意义)的执行情况, 例如如执行EXPLAIN SELECT * from userinfo;
关于上述表中详细字段解释 :
id 查询顺序标识, 表示sql查询语句的执行顺序
select_type 查询语句的类型: 简单查询SIMPLE, 最外层查询PRIMARY, 子查询DERIVED, 映射是子查询SUBQUERY, 联合查询UNION, 联合查询所得UNION RESULT
table 正在访问的表名
partitions 分区类型
type 查询方式, 查询时间性能排序 : 全表扫描all > 全索引扫描index > 索引部分范围内扫描range > 多单列索引合并扫描index_merge > 索引匹配一个或多个值扫描ref > 联合主键索引或者唯一索引扫描eq_ref > 最多有一个匹配行优化后作为常量表扫描const, 还有system特列, 性能与const近似
possible_keys 可能使用的索引
key 实际使用的索引
key_len 字节长度
rows 预测找到所需内容要查找的行数
extra 其他信息, 多为mysql解决查询的详细信息
limit分页优化
-
-- 查询第
1000条数据之后的
10条数据
-
-- 这种查询方式会进行全文扫描
-
SELECT * FROM userinfo LIMIT
1000,
5;
-
-- 这种方式仅仅优化了一点, 使用了一些普通索引和索引合并查询
-
EXPLAIN SELECT * FROM userinfo WHERE nid > (SELECT nid FROM userinfo LIMIT
1000,
1) LIMIT
5;
-
-- 直接根据计算所得后的最大条数倒序排列查询
-
SELECT * FROM userinfo WHERE nid < 上次最大条数 ORDER BY nid DESC LIMIT
5;
慢查询日志可以根据自定义设置记录那些查询性能查的sql语句 :
查看全局变量 show global variables like "%名称"
设置全局变量 set global 变量名=值
是否开启慢日志全局变量 slow_query_log = OFF
时间限制全局变量 long_query_time = 2
日志文件存放位置全局变量 slow_query_log_file = /usr/slow.log
是否记录未使用索引的查询语句全局变量 log_queries_not_using_indexes = OFF
格式化查看慢日志 mysqldumpslow [option] 日志存放位置, 常用option如下:
版本 -v或者--verbose
调试模式 -d或者--debug
排序方式 -s 规则选项, 默认是平均查询时间at
倒序排列显示 -r
显示前number条 -t number
不要将sql中数字转换成N，字符串转换成S, 选项为, -a
** python使用pymysql操作数据库 **
-
#!/usr/bin/env python
-
# -*- coding:utf
-8 -*-
-
import pymysql
-
-
# 创建连接通道, 设置连接ip, port, 用户, 密码以及所要连接的数据库
-
conn = pymysql.connect(host=
'127.0.0.1', port=
3306, user=
'root', passwd=
'', db='pymysql_db
')
-
-
# 创建游标, 操作数据库, 指定游标返回内容为字典类型
-
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
-
-
# 调用存储过程, 传入参数
-
cursor.callproc('p1
', (1, 5, 0))
-
-
# 得到结果集1, 即sql语句执行结果
-
select_result = cursor.fetchone()
-
print(select_result)
-
-
# 执行存储过程, 获取返回值, @_存储过程名_第一个参数
-
cursor.execute("select @_p1_0,@_p1_1,@_p_2")
-
select_result = cursor.fetchone()
-
print(select_result)
-
-
cursor.close()
-
conn.close()
转载：https://blog.csdn.net/qq_36772866/article/details/105424425