数据库进阶
一、视图
数据库视图是虚拟表或逻辑表，它被定义为具有连接的SQL SELECT查询语句。其本质是为常用的查询语句起个别名。用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。不是真正存在。视图存在数据库中。
数据库视图是动态的，因为它与物理模式无关。数据库系统将数据库视图存储为具有连接的SQL SELECT语句。当表的数据发生变化时，视图也反映了这些数据的变化。
数据库视图的优点
- 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
数据库视图的缺点
除了上面的优点，使用数据库视图有几个缺点：
- 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。
- 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。
一、创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
1 参数解释 2 1）OR REPLACE：表示替换已有视图 3 2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表 4 3）select_statement：表示select语句 5 4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内 6 cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件 7 local表示更新视图的时候，要满足该视图定义的一个条件即可 8 TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性
基本格式：
create view <视图名称>[(column_list)]
as
select语句
with check option;
mysql> create view v_F_players(编号,名字,性别,电话) -> as -> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS -> where SEX='F' -> with check option; Query OK, 0 rows affected (0.00 sec) mysql> desc v_F_players; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | 编号 | int(11) | NO | | NULL | | | 名字 | char(15) | NO | | NULL | | | 性别 | char(1) | NO | | NULL | | | 电话 | char(13) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select * from v_F_players; +--------+-----------+--------+------------+ | 编号 | 名字 | 性别 | 电话 | +--------+-----------+--------+------------+ | 8 | Newcastle | F | 070-458458 | | 27 | Collins | F | 079-234857 | | 28 | Collins | F | 010-659599 | | 104 | Moorman | F | 079-987571 | | 112 | Bailey | F | 010-548745 | +--------+-----------+--------+------------+ 5 rows in set (0.02 sec)
mysql> create view v_match -> as -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION -> from -> PLAYERS a,MATCHES b,TEAMS c -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO; Query OK, 0 rows affected (0.03 sec) mysql> select * from v_match; +----------+-----------+---------+-----+------+--------+----------+ | PLAYERNO | NAME | MATCHNO | WON | LOST | TEAMNO | DIVISION | +----------+-----------+---------+-----+------+--------+----------+ | 6 | Parmenter | 1 | 3 | 1 | 1 | first | | 44 | Baker | 4 | 3 | 2 | 1 | first | | 83 | Hope | 5 | 0 | 3 | 1 | first | | 112 | Bailey | 12 | 1 | 3 | 2 | second | | 8 | Newcastle | 13 | 0 | 3 | 2 | second | +----------+-----------+---------+-----+------+--------+----------+ 5 rows in set (0.04 sec)
- 视图将我们不需要的数据过滤掉，将相关的列名用我们自定义的列名替换。视图作为一个访问接口，不管基表的表结构和表名有多复杂。
- 如果创建视图时不明确指定视图的列名，那么列名就和定义视图的select子句中的列名完全相同；
- 如果显式的指定视图的列名就按照指定的列名。
注意：显示指定视图列名，要求视图名后面的列的数量必须匹配select子句中的列的数量。
二、查看视图
1、使用show create view语句查看视图信息
1 mysql> show create view v_F_playersG; 2 *************************** 1. row *************************** 3 View: v_F_players 4 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_F_players` AS select `PLAYERS`.`PLAYERNO` AS `编号`,`PLAYERS`.`NAME` AS `名字`,`PLAYERS`.`SEX` AS `性别`,`PLAYERS`.`PHONENO` AS `电话` from `PLAYERS` where (`PLAYERS`.`SEX` = 'F') WITH CASCADED CHECK OPTION 5 character_set_client: utf8 6 collation_connection: utf8_general_ci 7 1 row in set (0.00 sec)
2、视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询
mysql> select * from view_name;
3、有关视图的信息记录在information_schema数据库中的views表中
1 mysql> select * from information_schema.views 2 -> where TABLE_NAME='v_F_players'G; 3 *************************** 1. row *************************** 4 TABLE_CATALOG: def 5 TABLE_SCHEMA: TENNIS 6 TABLE_NAME: v_F_players 7 VIEW_DEFINITION: select `TENNIS`.`PLAYERS`.`PLAYERNO` AS `编号`,`TENNIS`.`PLAYERS`.`NAME` AS `名字`,`TENNIS`.`PLAYERS`.`SEX` AS `性别`,`TENNIS`.`PLAYERS`.`PHONENO` AS `电话` from `TENNIS`.`PLAYERS` where (`TENNIS`.`PLAYERS`.`SEX` = 'F') 8 CHECK_OPTION: CASCADED 9 IS_UPDATABLE: YES 10 DEFINER: root@localhost 11 SECURITY_TYPE: DEFINER 12 CHARACTER_SET_CLIENT: utf8 13 COLLATION_CONNECTION: utf8_general_ci 14 1 row in set (0.00 sec)
三、视图的更改
1、CREATE OR REPLACE VIEW语句修改视图
基本格式：
create or replace view view_name as select语句;
在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图
2、ALTER语句修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
注意：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致
3、DML操作更新视图
因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中
DML更新视图当然，视图的DML操作，不是所有的视图都可以做DML操作。
①select子句中包含distinct
②select子句中包含组函数
③select语句中包含group by子句
④select语句中包含order by子句
⑤select语句中包含union 、union all等集合运算符
⑥where子句中包含相关子查询
⑦from子句中包含多个表
⑧如果视图中有计算列，则不能更新
⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作
4、drop删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
mysql> drop view v_student;
- 注：如果视图不存在，则抛出异常；使用IF EXISTS选项使得删除不存在的视图时不抛出异常。
四、使用WITH CHECK OPTION约束
对于可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束
作用：
对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。
示例：创建视图，包含1960年之前出生的所有球员（老兵）
mysql> create view v_veterans -> as -> select * from PLAYERS -> where birth_date < '1960-01-01' -> with check option; Query OK, 0 rows affected (0.01 sec) mysql> select * from v_veterans; +----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+ | PLAYERNO | NAME | INITIALS | BIRTH_DATE | SEX | JOINED | STREET | HOUSENO | POSTCODE | TOWN | PHONENO | LEAGUENO | +----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+ | 2 | Everett | R | 1948-09-01 | M | 1975 | Stoney Road | 43 | 3575NH | Stratford | 070-237893 | 2411 | | 39 | Bishop | D | 1956-10-29 | M | 1980 | Eaton Square | 78 | 9629CD | Stratford | 070-393435 | NULL | | 83 | Hope | PK | 1956-11-11 | M | 1982 | Magdalene Road | 16A | 1812UP | Stratford | 070-353548 | 1608 | +----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+ 3 rows in set (0.02 sec)
mysql> update v_veterans -> set BIRTH_DATE='1970-09-01' -> where PLAYERNO=39; ERROR 1369 (HY000): CHECK OPTION failed 'TENNIS.v_veterans'
- 因为违反了视图中的WHERE birth_date < '1960-01-01'子句，所以抛出异常；
- 利用with check option约束限制，保证更新视图是在该视图的权限范围之内。
嵌套视图：定义在另一个视图的上面的视图
mysql> create view v_ear_veterans -> as -> select * from v_veterans -> where JOINED < 1980;
使用WITH CHECK OPTION约束时，(不指定选项则默认是CASCADED)
可以使用CASCADED或者 LOCAL选项指定检查的程度：
WITH CASCADED CHECK OPTION：检查所有的视图
例如：嵌套视图及其底层的视图
WITH LOCAL CHECK OPTION：只检查将要更新的视图本身
例如：对嵌套视图不检查其底层的视图
五、定义视图时的其他选项
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
1、ALGORITHM选项：选择在处理定义视图的select语句中使用的方法 ①UNDEFINED：MySQL将自动选择所要使用的算法 ②MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分 ③TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句 缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED 2、DEFINER选项：指出谁是视图的创建者或定义者 ①definer= '用户名'@'登录主机' ②如果不指定该选项，则创建视图的用户就是定义者，指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同 3、SQL SECURITY选项：要查询一个视图，首先必须要具有对视图的select权限。 但是，如果同一个用户对于视图所访问的表没有select权限，那会怎么样？ SQL SECURITY选项决定执行的结果： ①SQL SECURITY DEFINER：定义(创建)视图的用户必须对视图所访问的表具有select权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。 ②SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有select权限。 缺省SQL SECURITY选项等同于SQL SECURITY DEFINER 视图权限总结： 使用root用户定义一个视图(推荐使用第一种)：u1、u2 1）u1作为定义者定义一个视图，u1对基表有select权限，u2对视图有访问权限：u2是以定义者的身份访问可以查询到基表的内容； 2）u1作为定义者定义一个视图，u1对基表没有select权限，u2对视图有访问权限，u2对基表有select权限：u2访问视图的时候是以调用者的身份，此时调用者是u2，可以查询到基表的内容。
六、视图查询语句的处理
示例：所有有罚款的球员的信息
mysql> create view cost_raisers -> as -> select * from PLAYERS -> where playerno in (select playerno from PENALTIES);
mysql> select playerno from cost_raisers -> where town='Stratford'; +----------+ | PLAYERNO | +----------+ | 6 | +----------+
# 先把select语句中的视图名使用定义视图的select语句来替代； # 再处理所得到的select语句。 mysql> select playerno from -> ( -> select * from PLAYERS -> where playerno in-> (select playerno from PENALTIES) -> )as viewformula -> where town='Stratford'; +----------+ | PLAYERNO | +----------+ | 6 | +----------+
先处理定义视图的select语句，这会生成一个中间的结果集； 然后，再在中间结果上执行select查询。 mysql> select <列名> from <中间结果>;
二、存储过程
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
优点
- 存储过程可封装，并隐藏复杂的商业逻辑。
- 存储过程可以回传值，并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
- 存储过程可以用在数据检验，强制实行商业逻辑等。
缺点
- 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
- 存储过程的性能调校与撰写，受限于各种数据库系统。
一.创建存储过程
create procedure sp_name()
begin
.........
end
二.调用存储过程
1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
三.删除存储过程
1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
四.其他常用命令
1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.show create procedure sp_name
显示某一个MySQL存储过程的详细信息
五、存储过程实例
1、创建存储过程
-- 创建存储过程 delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; -- 执行存储过程 call p1() 无参数存储过程
对于存储过程，可以接收参数，其参数有三类：
- 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 p1() begin select * from v1; end // delimiter ;
delimiter // create procedure p2( in n1 int, inout n3 int, out n2 int, ) begin declare temp1 int ; declare temp2 int default 0; select * from v1; set n2 = n1 + 100; set n3 = n3 + n1 + 100; end // delimiter ; 2. 结果集+out值
delimiter \ create PROCEDURE p1( 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; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\ delimiter ;
delimiter // create procedure p3() begin declare ssid int; -- 自定义变量1 declare ssname varchar(50); -- 自定义变量2 DECLARE done INT DEFAULT FALSE; DECLARE my_cursor CURSOR FOR select sid,sname from student; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; xxoo: LOOP fetch my_cursor into ssid,ssname; if done then leave xxoo; END IF; insert into teacher(tname) values(ssname); end loop xxoo; close my_cursor; end // delimter ;
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
2、删除存储过程
drop procedure proc_name;
3、执行存储过程
-- 无参数 call proc_name() -- 有参数，全in call proc_name(1,2) -- 有参数，有in，out，inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2) 执行存储过程
#!/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执行存储过程
三、触发器
如果希望在对某张表的增、删、改的前后进行触发某个特定的行为时，可以使用触发器来自定义操作，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
创建触发器
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。
INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。
INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 详解
在MySQL中，BEGIN … END 语句的语法为：
BEGIN
[statement_list]
END
其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。
这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
# 假设系统中有两个表： # 班级表 class(班级号 classID, 班内学生数 stuCount) # 学生表 student(学号 stuID, 所属班级号 classID) # 要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下： DELIMITER $ create trigger tri_stuInsert after insert on student for each row begin declare c int; set c = (select stuCount from class where classID=new.classID); update class set stuCount = c + 1 where classID = new.classID; end$ DELIMITER ;
MySQL 中使用 DECLARE 来定义一局部变量，该变量只能在 BEGIN … END 复合语句中使用，并且应该定义在复合语句的开头，
即其它语句之前，语法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 为变量名称，同 SQL 语句一样，变量名不区分大小写；type 为 MySQL 支持的任何数据类型；可以同时定义多个同类型的变量，用逗号隔开；变量初始值为 NULL，如果需要，可以使用 DEFAULT 子句提供默认值，值可以被指定为一个表达式。
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。
SET var_name = expr [,var_name = expr] ...
查看触发器
SHOW TRIGGERS [FROM schema_name]; # 其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就 # 不必先“USE database_name;”了。
删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
- 如果 BEFORE 触发器执行失败，SQL 无法正确执行。
- SQL 执行失败时，AFTER 型触发器不会触发。
- AFTER 类型的触发器执行失败，SQL 会回滚。
四、索引
1.索引简介：索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
2.索引作用：加速查找+约束
3.索引种类：
主键索引：加速查找、不重复、非空
唯一索引：加速查找、不重复
普通索引：加速查找
联合索引：加速查找
联合唯一索引：加速查找、不重复
PS：联合索引遵循最左前缀原则
id name pwd email 假设name,pwd,email 建立联合索引 select * from name='xx'; 命中索引 select * from name='xx' and pwd='123'; 命中索引 select * from name='xx' and pwd='123' and email='qq'; 命中索引 select * from name='xx' and emai='qq'; 命中索引 select * from email='qq' and pwd='123'; 不能命中索引
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖,如索引为name，也仅查找name列
索引合并：使用多个单列索引去查找数据，如name和age 都为索引，使用name和age联合查询
4.创建索引语法
--创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) );
CREATE TABLE test2( id INT, name VARCHAR(20), INDEX index_name (name) );
---CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; ---ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; CREATE INDEX index_emp_name on emp1(name); ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);
# 语法：DROP INDEX 索引名 on 表名 DROP INDEX index_emp_name on emp1; DROP INDEX bank_num on emp2;
5.索引测试实验
--创建表 create table t1(id int,name varchar(20)); --存储过程 delimiter $$ create procedure autoinsert() BEGIN declare i int default 1; while(i<5000)do insert into t1 values(i,'yuan'); set i=i+1; end while; END$$ delimiter ; --调用函数 call autoinsert(); -- 花费时间比较： -- 创建索引前 select * from t1 where id=300000;--0.32s -- 添加索引 create index index_id on t1(id); -- 创建索引后 select * from Indexdb.t1 where id=300000;--0.00s --删除索引 drop index index_id on t1;
use crawed; desc doutula; select count(*) from doutula; +----------+ | count(*) | +----------+ | 27308 | +----------+ select name from doutula where name like '%坏蛋%'; --34 rows in set (0.53 sec) create index index_name on doutula (name); select name from doutula where name like '%坏蛋%'; --34 rows in set (0.05 sec)
6.正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：
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 -- 不使用索引
五、函数
mysql提供了许多内置函数
CHAR_LENGTH(str) 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ，则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接（自定义连接符） CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 CONV(N,from_base,to_base) 进制转换 例如： SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos：要替换位置其实位置 len：替换的长度 newstr：新字符串 特别的： 如果pos超过原字符串长度，则返回原字符串 如果len超过原字符串长度，则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' 部分内置函数
delimiter \ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \ delimiter ;
drop function func_name;
# 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
mysql> select concat('M','y','SQL'); +-----------------------+ | concat('M','y','SQL') | +-----------------------+ | MySQL | +-----------------------+ 1 row in set (0.00 sec) mysql> select concat('M','y','SQL','5.7'); +-----------------------------+ | concat('M','y','SQL','5.7') | +-----------------------------+ | MySQL5.7 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select concat('M','y','SQL',' 5.7'); +------------------------------+ | concat('M','y','SQL',' 5.7') | +------------------------------+ | MySQL 5.7 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT concat_ws("-",'2012','09'); +----------------------------+ | concat_ws("-",'2012','09') | +----------------------------+ | 2012-09 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT concat_ws(":",'2012','09'); +----------------------------+ | concat_ws(":",'2012','09') | +----------------------------+ | 2012:09 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT concat_ws("-:",'2012','09'); +-----------------------------+ | concat_ws("-:",'2012','09') | +-----------------------------+ | 2012-:09 | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT concat_ws(":-",'2012','09'); +-----------------------------+ | concat_ws(":-",'2012','09') | +-----------------------------+ | 2012:-09 | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT concat_ws(null,'2012','09'); +-----------------------------+ | concat_ws(null,'2012','09') | +-----------------------------+ | NULL | +-----------------------------+ 1 row in set (0.00 sec) mysql> select concat(1,2); +-------------+ | concat(1,2) | +-------------+ | 12 | +-------------+ 1 row in set (0.00 sec) mysql> select strcmp('ac','ac'); +-------------------+ | strcmp('ac','ac') | +-------------------+ | 0 | +-------------------+ 1 row in set (0.06 sec) mysql> select strcmp('ab','ac'); +-------------------+ | strcmp('ab','ac') | +-------------------+ | -1 | +-------------------+ 1 row in set (0.00 sec) mysql> select strcmp('ac','ab'); +-------------------+ | strcmp('ac','ab') | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) mysql> help length; Name: 'LENGTH' Description: Syntax: LENGTH(str) Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. URL: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT LENGTH('text'); -> 4 mysql> select length('1 2'); +---------------+ | length('1 2') | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) mysql> select length('1 2 '); +----------------+ | length('1 2 ') | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> select length(' 1 2 '); +-----------------+ | length(' 1 2 ') | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) mysql> help charlength; Nothing found Please try to run 'help contents' for a list of all accessible topics mysql> help char_length; Name: 'CHAR_LENGTH' Description: Syntax: CHAR_LENGTH(str) Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. URL: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html mysql> select char_length(hello world); ERROR 1583 (42000): Incorrect parameters in the call to native function 'char_le ngth' mysql> select char_length('hello world'); +----------------------------+ | char_length('hello world') | +----------------------------+ | 11 | +----------------------------+ 1 row in set (0.00 sec) mysql> select upper('ADSDAsdadsf'); +----------------------+ | upper('ADSDAsdadsf') | +----------------------+ | ADSDASDADSF | +----------------------+ 1 row in set (0.02 sec) mysql> select ucase('ADSs'); +---------------+ | ucase('ADSs') | +---------------+ | ADSS | +---------------+ 1 row in set (0.00 sec) mysql> select lower('ADSs'); +---------------+ | lower('ADSs') | +---------------+ | adss | +---------------+ 1 row in set (0.00 sec) mysql> select find_in_set('MySQL','a,b,c,MySQL'); +------------------------------------+ | find_in_set('MySQL','a,b,c,MySQL') | +------------------------------------+ | 4 | +------------------------------------+ 1 row in set (0.08 sec) mysql> help field; Name: 'FIELD' Description: Syntax: FIELD(str,str1,str2,str3,...) Returns the index (position) of str in the str1, str2, str Returns 0 if str is not found. If all arguments to FIELD() are strings, all arguments are strings. If all arguments are numbers, they are compared a Otherwise, the arguments are compared as double. If str is NULL, the return value is 0 because NULL fails e comparison with any value. FIELD() is the complement of EL URL: http://dev.mysql.com/doc/refman/5.7/en/string-functio Examples: mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo -> 2 mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo -> 0 mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo +------------------------------------------------+ | FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo') | +------------------------------------------------+ | 2 | +------------------------------------------------+ 1 row in set (0.05 sec) mysql> select left("2013-09-28 23:58:54"); ERROR 1064 (42000): You have an error in your SQL syntax; corresponds to your MySQL server version for the right syn line 1 mysql> select left("2013-09-28 23:58:54","10"); +----------------------------------+ | left("2013-09-28 23:58:54","10") | +----------------------------------+ | 2013-09-28 | +----------------------------------+ 1 row in set (0.04 sec) mysql> select right("2013-09-28 23:58:54","10"); +-----------------------------------+ | right("2013-09-28 23:58:54","10") | +-----------------------------------+ | 8 23:58:54 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select right("2013-09-28 23:58:54",10); +---------------------------------+ | right("2013-09-28 23:58:54",10) | +---------------------------------+ | 8 23:58:54 | +---------------------------------+ 1 row in set (0.00 sec) mysql> select substring("this is test string",6,2); +--------------------------------------+ | substring("this is test string",6,2) | +--------------------------------------+ | is | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select substring("this is test string",6,200); +----------------------------------------+ | substring("this is test string",6,200) | +----------------------------------------+ | is test string | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select mid("this is test string",6,200); +----------------------------------+ | mid("this is test string",6,200) | +----------------------------------+ | is test string | +----------------------------------+ 1 row in set (0.00 sec) mysql> help mid; Name: 'MID' Description: Syntax: MID(str,pos,len) MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len). URL: http://dev.mysql.com/doc/refman/5.7/en/string-functio mysql> select ltrim("1 2"); +--------------+ | ltrim("1 2") | +--------------+ | 1 2 | +--------------+ 1 row in set (0.06 sec) mysql> help trim; Name: 'TRIM' Description: Syntax: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRI FROM] str) Returns the string str with all remstr prefixes or suffixe none of the specifiers BOTH, LEADING, or TRAILING is given assumed. remstr is optional and, if not specified, spaces URL: http://dev.mysql.com/doc/refman/5.7/en/string-functio Examples: mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' mysql> select replace('this is test string','is',"is not") +----------------------------------------------+ | replace('this is test string','is',"is not") | +----------------------------------------------+ | this not is not test string | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.4352682404548565 | +--------------------+ 1 row in set (0.06 sec) mysql> select rand(3); +--------------------+ | rand(3) | +--------------------+ | 0.9057697559760601 | +--------------------+ 1 row in set (0.00 sec) mysql> help rand; Name: 'RAND' Description: Syntax: RAND(), RAND(N) Returns a random floating-point value v in the range 0 <= constant integer argument N is specified, it is used as th which produces a repeatable sequence of column values. In example, note that the sequences of values produced by RAN same both places where it occurs. URL: http://dev.mysql.com/doc/refman/5.7/en/mathematical-f Examples: mysql> CREATE TABLE t (i INT); Query OK, 0 rows affected (0.42 sec) mysql> INSERT INTO t VALUES(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT i, RAND() FROM t; +------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.61914388706828 | | 2 | 0.93845168309142 | | 3 | 0.83482678498591 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND(3) FROM t; +------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND() FROM t; +------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.35877890638893 | | 2 | 0.28941420772058 | | 3 | 0.37073435016976 | +------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT i, RAND(3) FROM t; +------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.01 sec) mysql> select ceil(5.6); +-----------+ | ceil(5.6) | +-----------+ | 6 | +-----------+ 1 row in set (0.00 sec) mysql> select floor(5.6); +------------+ | floor(5.6) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec) mysql> select floor(5.1); +------------+ | floor(5.1) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec) mysql> select floor(5.9); +------------+ | floor(5.9) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec) mysql> select truncat(123.456,2); ERROR 1046 (3D000): No database selected mysql> select truncate(123.456,2); +---------------------+ | truncate(123.456,2) | +---------------------+ | 123.45 | +---------------------+ 1 row in set (0.00 sec) mysql> select truncate(123.456,-2); +----------------------+ | truncate(123.456,-2) | +----------------------+ | 100 | +----------------------+ 1 row in set (0.00 sec) mysql> select truncate(123.456,-1); +----------------------+ | truncate(123.456,-1) | +----------------------+ | 120 | +----------------------+ 1 row in set (0.00 sec) mysql> select round(123.456); +----------------+ | round(123.456) | +----------------+ | 123 | +----------------+ 1 row in set (0.00 sec) mysql> select round(123.456,2); +------------------+ | round(123.456,2) | +------------------+ | 123.46 | +------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-01-09 11:12:25 | +---------------------+ 1 row in set (0.00 sec) mysql> select curdate(); +------------+ | curdate() | +------------+ | 2018-01-09 | +------------+ 1 row in set (0.06 sec) mysql> select from_unixitime("123"); ERROR 1046 (3D000): No database selected mysql> select from_unixtime("123"); +----------------------------+ | from_unixtime("123") | +----------------------------+ | 1970-01-01 08:02:03.000000 | +----------------------------+ 1 row in set (0.07 sec) mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1515467669 | +-----------------------+ 1 row in set (0.00 sec) mysql> select sysdate(); +---------------------+ | sysdate() | +---------------------+ | 2018-01-09 11:15:00 | +---------------------+ 1 row in set (0.00 sec) mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2018 | +-------------+ 1 row in set (0.00 sec) mysql> select hour(now()); +-------------+ | hour(now()) | +-------------+ | 11 | +-------------+ 1 row in set (0.02 sec) mysql> select to_days('20'); +---------------+ | to_days('20') | +---------------+ | NULL | +---------------+ 1 row in set, 1 warning (0.07 sec) mysql> select to_days('2013-4-22'); +----------------------+ | to_days('2013-4-22') | +----------------------+ | 735345 | +----------------------+ 1 row in set (0.00 sec) mysql> h For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference M http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other produc https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments delimiter (d) Set statement delimiter. ego (G) Send command to mysql server, display resul exit (q) Exit mysql. Same as quit. go (g) Send command to mysql server. help (h) Display this help. notee ( ) Don't write into outfile. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file na status (s) Get status information from the server. tee (T) Set outfile [to_outfile]. Append everything use (u) Use another database. Takes database name a charset (C) Switch to another charset. Might be needed with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. resetconnection(x) Clean session context. For server side help, type 'help contents' mysql> s -------------- mysql Ver 14.14 Distrib 5.7.17, for Win64 (x86_64) Connection id: 20 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.17-log MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 1 day 21 hours 34 min 49 sec Threads: 3 Questions: 1081 Slow queries: 0 Opens: 314 Flush tables: 1 Open tables: 267 Queries per second avg: 0.006 -------------- mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select version; ERROR 1054 (42S22): Unknown column 'version' in 'field list' mysql> select version(); +------------+ | version() | +------------+ | 5.7.17-log | +------------+ 1 row in set (0.00 sec) mysql> create table auto_test(id int auto_increment); ERROR 1046 (3D000): No database selected mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | company | | index_test | | mysql | | num | | performance_schema | | sakila | | sys | | table_test | | test | | world | +--------------------+ 11 rows in set (0.00 sec) mysql> use test; Database changed mysql> create table auto_test(id int auto_increment primary key); Query OK, 0 rows affected (0.81 sec) mysql> show create table auto_increment; ERROR 1146 (42S02): Table 'test.auto_increment' doesn't exist mysql> show create table auto_test; +-----------+------------------------------------------------------------------- -----------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------- -----------------------------------------------------------+ | auto_test | CREATE TABLE `auto_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+------------------------------------------------------------------- -----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into auto_test values(null); Query OK, 1 row affected (0.11 sec) mysql> insert into auto_test values(null); Query OK, 1 row affected (0.05 sec) mysql> insert into auto_test values(null); Query OK, 1 row affected (0.09 sec) mysql> insert into auto_test values(null); Query OK, 1 row affected (0.23 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 4 | +------------------+ 1 row in set (0.00 sec) mysql> select * from auto_test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) mysql> insert into auto_test values(null); Query OK, 1 row affected (0.14 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) mysql> select * from auto_test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec) mysql> help ifnull; Name: 'IFNULL' Description: Syntax: IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. URL: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html Examples: mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes' mysql> SELECT IFNULL(1,0); +-------------+ | IFNULL(1,0) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> password; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'passw ord' at line 1 mysql> password(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'passw ord()' at line 1 mysql> help password(); Nothing found Please try to run 'help contents' for a list of all accessible topics mysql> help password; Name: 'PASSWORD' Description: Syntax: PASSWORD(str) *Note*: This function is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. Returns a hashed password string calculated from the cleartext password str. The return value is a nonbinary string in the connection character set, or NULL if the argument is NULL. This function is the SQL interface to the algorithm used by the server to encrypt MySQL passwords for storage in the mysql.user grant table. The old_passwords system variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause. URL: http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html Examples: mysql> SET old_passwords = 0; mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass'); +-------------------------------------------+------------------------+ | PASSWORD('mypass') | OLD_PASSWORD('mypass') | +-------------------------------------------+------------------------+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | 6f8c114b58f2ce9e | +-------------------------------------------+------------------------+ mysql> SET old_passwords = 1; mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass'); +--------------------+------------------------+ | PASSWORD('mypass') | OLD_PASSWORD('mypass') | +--------------------+------------------------+ | 6f8c114b58f2ce9e | 6f8c114b58f2ce9e | +--------------------+------------------------+
六、事务
事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
数据库开启事务命令
-- start transaction 开启事务
-- Rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置
-- Commit 提交事务，提交未存储的事务
-- savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)
转账实例：
select * from test; +------+-------+--------+ | id | name | salary | +------+-------+--------+ | 1 | zhang | 8000 | | 2 | fei | 10000 | | 3 | kobe | 8000 | | 4 | james | 9000 | +------+-------+--------+ 4 rows in set (0.00 sec) start transaction; update test set salary=salary-5000 where id=2; rollback; update test set salary=salary-5000 where id=2; update test set salary=salary+5000 where id=1; commit; savepoint create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb; INSERT INTO test2(name) VALUE ("alvin"), ("yuan"), ("xialv"); start transaction; insert into test2 (name)values('silv'); select * from test2; commit; -- 保留点 start transaction; insert into test2 (name)values('wu'); savepoint insert_wu; select * from test2; delete from test2 where id=4; savepoint delete1; select * from test2; delete from test2 where id=1; savepoint delete2; select * from test2; rollback to delete1; select * from test2; savepoint
事务特性
<1> 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
<2> 一致性（Consistency）：事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的，无论事务是否执行成功，事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点，如果数据库中的所有记录都能保证满足当前数据库中的所有约束，则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工（已经建立外键），如果数据库服务器发生错误，有一个员工没删掉，那么此时员工的部门表已经删除，那么就不符合完整性约束了，所以这样的数据库也就性能太差啦！
<3>隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
<4>持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
四个隔离级别：
- Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
- Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）不可以避免虚读
- Read committed：可避免脏读情况发生（读已提交）
- Read uncommitted：最低级别，以上情况均无法保证。(读未提交)
安全性考虑：Serializable>Repeatable read>Read committed>Read uncommitted
数据库效率：Read uncommitted>Read committed>Repeatable read>Serializable
一般情况下，我们会使用Repeatable read、Read committed mysql数据库默认的数据库隔离级别Repeatable read
mysql中设置数据库的隔离级别语句：set [global/session] transaction isolation level xxxx;
如果使用global则修改的是数据库的默认隔离级别，所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改，则修改的是当前客户端的隔离级别，和数据库默认隔离级别无关。当前的客户端是什么隔离级别，就能防止什么隔离级别问题，和其他客户端是什么隔离级别无关。
mysql中设置数据库的隔离级别语句：select @@tx_isolation;
七、数据库授权
1. 创建用户
命令:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明：
- username：你将创建的用户名
- host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符
% - password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY ''; CREATE USER 'pig'@'%';
2. 授权:
命令:
GRANT privileges ON databasename.tablename TO 'username'@'host'
说明:
- privileges：用户的操作权限，如
SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL - databasename：数据库名
- tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用
*表示，如*.*
例子:
GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; GRANT ALL ON *.* TO 'pig'@'%'; GRANT ALL ON maindataplus.* TO 'pig'@'%';
注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
3.设置与更改用户密码
命令:
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
例子:
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
四. 撤销用户权限
命令:
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明:
privilege, databasename, tablename：同授权部分
例子:
REVOKE SELECT ON *.* FROM 'pig'@'%';
注意:
假如你在给用户'pig'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。
具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。
五.删除用户
命令:
DROP USER 'username'@'host';
八、Python关于MySQL的API接口-pymysql模块
pymsql是Python中操作MySQL的模块，其使用方法和py2的MySQLdb几乎相同。
安装：pip install pymysql
代码：
# -*- coding: utf-8 -*-
import pymysql
#1.连接mysql
conn = pymysql.connect(host='localhost',port=3306,user='root',password='0000',db='python3')
# conn = pymysql.connect(host='localhost',user='root',password='0000',db='crawed',charset='utf8',cursorclass = pymysql.cursors.DictCursor,use_unicode=True)
#2.创建游标
cursor = conn.cursor()
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #更改获取数据结果的数据类型
#3.创建表添加数据
# sql = 'create table test(id int,name varchar(20))'
# sql1 = "insert into test values (1,'zhang'),(2,'fei');"
# cursor.execute(sql)
# cursor.execute(sql1)
#4.查询数据
sql2 = 'select * from test;'
cursor.execute(sql2)
# num = cursor.fetchone()
# print(cursor.fetchone())
# print(cursor.fetchmany(2))
print(cursor.fetchall())
#5.scroll 控制游标位置
# print(cursor.fetchone())
# print(cursor.fetchone())
# cursor.scroll(-1,mode='relative') # 相对当前位置移动
# cursor.scroll(0,mode='absolute') # 相对绝对位置移动
# print(cursor.fetchone())
conn.commit()
conn.close()
# import pymysql # # conn = pymysql.Connect(host='localhost', user='root', password='0000', database='flask_code', charset='utf8') # # # cursor = conn.cursor() # 每一行是元组 # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 每一行是字典 # sql = "select * from userinfo where username='{0}' and password='{1}'".format('zhangsan','317bc264bfd3d562fa415dbb905e2d8a') # sql注入 # sql = "select * from userinfo where username='{0}' and password='{1}'".format("xx ' or 1=1 -- ",'317bc264bfd3d562fa415dbb905e2d8a') # sql = "select * from userinfo where username='%s' and password ='%s'" %("xiao ' or 1=1 -- ","47f5abdd7f4083f0cc5c94d587fa3ca4") # print(sql) # cursor.execute(sql) # cursor.execute("select * from userinfo where username=%s and password=%s",("xx ' or 1=1 --",'317bc264bfd3d562fa415dbb905e2d8a')) # cursor.execute("select * from userinfo where username=%s and password=%s",('zhangsan','317bc264bfd3d562fa415dbb905e2d8a')) # cursor.execute("select * from userinfo where username=%(us)s and password=%(pw)s",{'us':'zhangsan','pw':'317bc264bfd3d562fa415dbb905e2d8a'}) # cursor.execute("select id,username,password from userinfo where username=%(us)s and password=%(pw)s",{'us':'zhangsan','pw':'317bc264bfd3d562fa415dbb905e2d8a'}) # data = cursor.fetchall() # data = cursor.fetchone() # # print(data['id'],data['username']) # # cursor.close() # conn.close()
from DBUtils.PooledDB import PooledDB, SharedDBConnection import pymysql POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=6, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=5, # 链接池中最多闲置的链接，0和None不限制 maxshared=3, # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='0000', database='flask_code', charset='utf8' )
# -*- coding: utf-8 -*- """ @Datetime: 2018/12/26 @Author: Zhang Yafei """ import pymysql from settings import POOL def connect(): conn = POOL.connection() cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) return conn, cursor def connect_close(conn, cursor): cursor.close() conn.close() def fetchone(sql, arg=list()): conn, cursor = connect() cursor.execute(sql, arg) data = cursor.fetchone() connect_close(conn, cursor) return data def fetchall(sql, arg=list()): conn, cursor = connect() cursor.execute(sql, arg) data = cursor.fetchall() connect_close(conn, cursor) return data def insert(sql, arg=list()): conn, cursor = connect() row = cursor.execute(sql, arg) conn.commit() connect_close(conn, cursor) return row
补充：数据库优化方案
- 避免使用select *
- 固定长度在前面
- 内存代替表，如：性别等
- 读写分离
- 分库
- 分表
- 水平分表
- 垂直分表
- 命中索引
- 组合索引代替索引合并
- 尽量使用短索引
- 如果取一条数据时，使用limit 1
select id,name from tb where name ='alex' limit 1;
- 使用连接（JOIN）来代替子查询(Sub-Queries)
