Python——数据库进阶
MySQL用户操作
创建用户
方法一： CREATE USER语句创建
CREATE USER "用户名"@"IP地址" IDENTIFIED BY "密码";
方法二： INSERT语句创建
INSERT INTO mysql.user(user,host, password,ssl_cipher,x509_issuer,x509_subject)
VALUES(‘用户名’,’IP地址’,password(‘密码’),’’,’’,’’);
FLUSH PRIVILEGES;
方法三： GRANT语句创建
GRANT SELECT ON *.* TO 用户名@’IP地址’ IDENTIFIED BY ‘密码’;
FLUSH PRIVILEGES;
删除用户
方法一：DROP USER语句删除
DROP USER "用户名"@"IP地址";
方法二：DELETE语句删除
DELETE FROM mysql.user
WHERE user=’用户名’ AND host=’IP地址’;
FLUSH PRIVILEGES;
修改密码
root用户修改自己密码：
方法一：
# mysqladmin -uroot -p123 password 'new_password' //123为旧密码
方法二：
UPDATE mysql.user SET password=password(‘new_password’)
WHERE user=’root’ AND host=’localhost’;
FLUSH PRIVILEGES;
方法三：
SET PASSWORD=password(‘new_password’);
FLUSH PRIVILEGES;
root修改其他用户密码：
方法一：
SET PASSWORD FOR 用户名@’IP地址’=password(‘new_password’);
FLUSH PRIVILEGES;
方法二：
UPDATE mysql.user SET password=password(‘new_password’)
WHERE user=’用户名’ AND host=’IP地址’;
FLUSH PRIVILEGES;
方法三：
GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘yuan’;
FLUSH PRIVILEGES;
普通用户修改自己密码：
SET password=password(‘new_password’);
丢失root用户密码：
# vim /etc/my.cnf
skip-grant-tables
# service mysqld restart
# mysql -uroot
mysql> UPDATE mysql.user SET password=password(‘new_password’)
WHERE user=’root’ AND host=’localhost’;
mysql> FLUSH PRIVILEGES;
修改用户名：
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
注解 flush privileges：
/*
flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。
MySQL用户数据和权限有修改后，搜索希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令
*/
MySQL权限管理
授权
--语法格式：
grant 权限列表 on 库名.表名 to 用户名@'客户端主机' [identified by '密码' with option参数];
示例：
-- 给该用户授予对数据库db1下的表tb1所有的操作权限 grant all privileges on db1.tb1 TO '用户名'@'IP' -- 给该用户授予对数据库db1下所有表进行查操作的权限 grant select on db1.* TO '用户名'@'IP' -- 给该用户授予对所有数据库下所有表进行查和增的操作 grant select,insert on *.* TO '用户名'@'IP' -- 用户只能在该IP段下才能(通配符%表示任意)对所有数据库下所有表进行查操作 grant select on *.* TO '用户名'@'192.168.1.%' -- 用户能用户可以在任意IP下(通配符%表示任意)对所有数据库下所有表进行查操作 grant select on *.* TO '用户名'@'%'
with_option参数：
GRANT OPTION： -- 授权选项 MAX_QUERIES_PER_HOUR： -- 定义每小时允许执行的查询数 MAX_UPDATES_PER_HOUR： -- 定义每小时允许执行的更新数 MAX_CONNECTIONS_PER_HOUR： -- 定义每小时可以建立的连接数 MAX_USER_CONNECTIONS： -- 定义单个用户同时可以建立的连接数
示例: 限制用户每小时的查询数量
mysql> grant select on *.* to '用户名'@'IP地址' identified by '123456' with max_queries_per_hour 5;
查看权限
show grants for '用户'@'IP地址'
回收权限
--语法：
REVOKE 权限列表 ON 库名.表名 FROM 用户名@‘客户端主机’
示例：
REVOKE DELETE ON *.* FROM 用户名@’%’; -- 回收部分权限
REVOKE ALL PRIVILEGES FROM 用户名@’%’; -- 回收所有权限
视图
1.为什么使用视图
为了提高复杂SQL语句的复用性和表操作的安全性（例如：工资字段不想展示给所有能查看该查询结果的人），MySQL提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表，并且在具体使用视图时动态生成。
视图有如下特点;
1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表（实表）产生的表（虚表）。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
5. 当视图来自多个基本表时，不允许添加和删除数据。
2.创建视图
在创建视图时，首先要确保拥有CREATE VIEW的权限，并且同时确保对创建视图所引用的表也具有相应的权限。
2.1 创建视图的语法形式：
虽然视图可以被看成是一种虚拟表，但是其在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句，因此创建视图的语法为：
CREATE VIEW view_name AS 查询语句
//说明：和创建表一样，视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。
示例：
use view_test; //选择一个自己创建的库
create table t_product( //创建表
id int primary key,
pname varchar(20),
price decimal(8,2)
);
insert into t_product values(1,'apple',6.5); //向表中插入数据
insert into t_product values(2,'orange',3); //向表中插入数据
create view view_product as select id,pname from t_product; //创建视图
select * from view_product;
结果为：
//其实在创建视图时实际代码里写的是一个表查询语句，只不过把这个查询语句封装起来重新起了一个名字，方便可以重复使用。
//再者，安全性方面可以隐藏一些不希望看到的字段，比如这里的价格字段。
//注意：在SQL语句的命名规范中，视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。
2.2 创建各种视图：
由于视图的功能实际上时封装查询语句，那么是不是任何形式的查询语句都可以封装在视图中呢？
封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图： 示例：
首先准备需要用到的两张表及其初始化数据;
CREATE TABLE t_group(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
sex CHAR(1),
group_id INT,
FOREIGN KEY (group_id) REFERENCES t_group (id)
);
//t_group表中插入数据
INSERT INTO t_group (NAME) VALUES('group_1');
INSERT INTO t_group (NAME) VALUES('group_2');
INSERT INTO t_group (NAME) VALUES('group_3');
INSERT INTO t_group (NAME) VALUES('group_4');
INSERT INTO t_group (NAME) VALUES('group_5');
//t_student表中插入数据
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_1','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_2','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_3','M',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_4','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_5','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_6','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_7','M',3);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_8','W',4);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_9','W',4);
================================================================
mysql> create view view_test2 as select count(name) from t_student;
Query OK, 0 rows affected (0.71 sec)
mysql> select * from view_test2;
+-------------+
| count(name) |
+-------------+
| 9 |
+-------------+
1 row in set (0.01 sec)
2.2.3 封装了实现排序功能（ORDER BY）查询语句的视图：
示例：
mysql> create view view_test3 as select name from t_student order by id desc;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from view_test3;
+--------+
| name |
+--------+
| yuan_9 |
| yuan_8 |
| yuan_7 |
| yuan_6 |
| yuan_5 |
| yuan_4 |
| yuan_3 |
| yuan_2 |
| yuan_1 |
+--------+
9 rows in set (0.00 sec)
2.2.4 封装了实现表内连接查询语句的视图：
示例：（第二组学生的姓名）
mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test5;
+--------+
| name |
+--------+
| yuan_3 |
| yuan_4 |
| yuan_5 |
| yuan_6 |
+-------+
4 rows in set (0.00 sec)
2.2.6 封装了实现子查询相关查询语句的视图：
示例：
mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test7;
+--------+
| name |
+--------+
| yuan_1 |
| yuan_2 |
| yuan_3 |
| yuan_4 |
| yuan_5 |
+--------+
5 rows in set (0.00 sec)
2.2.7 封装了实现记录联合（UNION和UNION ALL）查询语句的视图：
mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test8;
+----+---------+
| id | name |
+----+---------+
| 1 | yuan_1 |
| 2 | yuan_2 |
| 3 | yuan_3 |
| 4 | yuan_4 |
| 5 | yuan_5 |
| 6 | yuan_6 |
| 7 | yuan_7 |
| 8 | yuan_8 |
| 9 | yuan_9 |
| 1 | group_1 |
| 2 | group_2 |
| 3 | group_3 |
| 4 | group_4 |
| 5 | group_5 |
+----+---------+
14 rows in set (0.01 sec)
3.查看视图
3.1 SHOW TABLES语句查看视图名：
执行SHOW TABLES 语句时不仅可以显示表的名字，同时也是显示出视图的名字。
示例：
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| goods |
| order |
| orders |
| person |
| t_group |
| t_product |
| t_student |
| view_product |
| view_test2 |
| view_test3 |
| view_test5 |
| view_test7 |
| view_test8 |
+-----------------+
13 rows in set (0.00 sec
3.2 SHOW TABLE STATUS语句查看视图详细信息：
和SHOW TABLES语句一样，SHOW TABLE STATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息。
语法如下：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//参数db_name用来设置数据库，SHOW TABLES STATUS表示将显示所设置库的表和视图的详细信息。
//设置LIKE关键字，可以查看某一个具体表或视图的详细信息。例如： SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G
3.3 SHOW CREATE VIEW语句查看视图定义信息：
语法为：
SHOW CREATE VIEW viewname;
3.4 DESCRIBE | DESC 语句查看视图定义信息：
语法为：
DESCRIBE | DESC viewname;
3.5 通过系统表查看视图信息：
当MySQL安装成功后，会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格，可以通过查看表格views来查看所有视图的相关信息。
示例：
mysql> use information_schema;
Database changed
mysql> select * from views where table_name='view_test7' \G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test2
TABLE_NAME: view_test7
VIEW_DEFINITION: select `s`.`NAME` AS `name` from `test2`.`t_student` `s`
where `s`.`id` in (select `test2`.`t_group`.`id` from `test2`.`t_group`)
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)
4.删除视图
在删除视图时首先要确保拥有删除视图的权限。
语法为：
DROP VIEW view_name [,view_name] ......
//从语法可以看出，DROP VIEW一次可以删除多个视图
示例：
mysql> drop view view_test1,view_test2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
5.修改视图
5.1 CREATE OR REPLACE VIEW语句修改视图：
对于已经创建好的表，尤其是已经有大量数据的表，通过先删除，然后再按照新的表定义重新建表的方式来修改表，需要做很多额外的工作，例如数据的重载等。可是对于视图来说，由于是“虚表”，并没有存储数据，所以完全可以通过该方式来修改视图。
实现思路就是：先删除同名的视图，然后再根据新的需求创建新的视图即可。
DROP VIEW view_name;
CREATE VIEW view_name as 查询语句;
但是如果每次修改视图，都是先删除视图，然后再次创建一个同名的视图，则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法，完整语法为：
CREATE OR REPLACE VIEW view_name as 查询语句；
5.2 ALTER语句修改视图：
语法为：
ALTER VIEW view_name as 查询语句;
6.利用视图操作基本表
再MySQL中可以通常视图检索基本表数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
6.1检索（查询）数据：
通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据：
由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点;
1. 对视图数据进行添加、删除直接影响基本表。
2. 视图来自于多个基本表时，不允许添加、删除数据。
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。
存储过程
一、存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户
通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。而我们常用的操作数据库语言SQL语句在执行的时
候需要要先编译，然后执行，所以执行的效率没有存储过程高。
存储过程优点如下：
重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。提高性能。存储过程在创建的时候在进行了编译，将来使用的时候不再重新翻译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。
存储过程简单语法：
CREATE PROCEDURE 存储过程名称(
输入输出类型 变量名称 类型,
...
)
BEGIN
-- 声明， 语句要完成的操作，增删改查。。。
END
二、参数介绍示例
表结构如下：
-- 创建表
DROP TABLE IF EXISTS person;
CREATE TABLE person (
`id` int(11) AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT person VALUES (1,"yuan",23,123),
(2,"egon",33,456),
(3,"alex",35,789)
1、无参数存储
DELIMITER ! DROP PROCEDURE IF EXISTS proc_no_para; -- 创建存储过程 CREATE PROCEDURE proc_no_para() BEGIN SELECT * FROM person; END! DELIMITER ; CALL proc_no_para();
2、只带IN（输入参数）的存储过程
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。
DROP PROCEDURE IF EXISTS proc_person_findById; -- 创建存储过程 CREATE PROCEDURE proc_person_findById( in n int ) BEGIN SELECT * FROM person where id=n; END; -- 定义变量 SET @n=2; -- 调用存储过程 CALL proc_person_findById(@n);
3、只带OUT（输出参数）的存储过程
该值可在存储过程内部被改变，并可返回。
DELIMITER ! CREATE PROCEDURE Procdure_out( OUT n INT(11) ) BEGIN SELECT COUNT(*) INTO n FROM person; END! DELIMITER ; CALL Procdure_out(@n); SELECT @n AS "总数";
4、带IN（输入参数）和OUT（输出参数）,调用时指定，并且可被改变和返回
DELIMITER ! DROP PROCEDURE IF EXISTS proc_person_findInfoById; -- 创建存储过程 CREATE PROCEDURE proc_person_findInfoById( IN n INT(11), OUT pusername VARCHAR(255), OUT page INT(11) ) BEGIN SELECT username, age INTO pusername, page FROM person WHERE id=n; END! DELIMITER ; -- 定义变量 SET @id=1; -- 调用存储过程 CALL proc_person_findInfoById(@id,@username, @age); SELECT @username as '用户名', @age '年龄';
5、带INOUT（输入输出）参数的存储过程
DELIMITER ! DROP PROCEDURE IF EXISTS proc_person_findInfoById; -- 创建存储过程 CREATE PROCEDURE proc_person_findInfoById( INOUT n INT ) BEGIN SELECT age INTO n FROM person WHERE id=n; END! DELIMITER ; -- 定义变量 SET @id=2; -- 调用存储过程 CALL proc_person_findInfoById(@age); SELECT @age '年龄';
注意：关于输入输出参数
IN为输入， 定义参数时，可以不加，不加则默认为输入参数。OUT为输出，定义参数时，必须加上。INOUT为输入和输出，必须加上。表示该参数可以输入也可在处理后存放结果进行输出。
三、变量和变量赋值
变量定义
|
1
|
DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; |
中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
变量赋值
|
1
|
SET 变量名 = 表达式值 [,variable_name = expression ...] |
四、条件语句
if-then -else语句：
DELIMITER //
CREATE PROCEDURE proc_if(IN parameter int)
begin
declare var int;
set var=parameter+1;
if var=0 then
insert into person values(7,"yuan",34,123456);
end if;
if parameter=0 then
update person set password=password+1;
else
update person set password=password+2;
end if;
end;
//
DELIMITER ;
case语句
DELIMITER //
CREATE PROCEDURE proc_case (in parameter int)
begin
declare var int;
set var=parameter+1;
case var
when 0 then
insert into person values(var,"yuan",var+10,123456+var);
when 1 then
insert into person values(var,"yuan",var+20,123456+var);
else
insert into person values(var,"yuan",var+30,123456+var);
end case;
end;
//
DELIMITER ;
五、循环介绍
循环的分类：
- WHILE ... END WHILE
- LOOP ... END LOOP
(1)WHILE ... END WHILE
DELIMITER $$
CREATE PROCEDURE insert_person4()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 10 DO
INSERT INTO person VALUES (v+1,concat("yuan",v),v,v+123);
SET v = v + 1;
END WHILE;
END;
$$
DELIMITER ;
这是WHILE循环的方式。它跟IF语句相似，使用"SET v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。
(2)REPEAT ... END REPEAT
DELIMITER $$
CREATE PROCEDURE insert_person2()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO person VALUES (v+1,concat("yuan",v),v,v+123);
SET v = v + 1;
UNTIL v >= 5
END REPEAT;
END;
$$
DELIMITER ;
删除存储过程
drop procedure proc_name;
触发器
什么是触发器？
触发器是数据库的一个程序，他是用来监听着数据表的某个行为，一旦数据表的这个行为发生了，马上执行相应的sql语句
触发器的语法结构：
|
1
|
create trigger 触发器的名称触发器事件 on 监听的表名 for each row 行为发生后执行的sql语句 |
注意：行为发生后执行的sql语句可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
触发器事件两个点：
- 触发器事件发生的时间-----是在监听的表的行为 after before 常用的是after
- 触发器执行的内容：增删改
案例研究
一旦生成订单，对应的库存表要减去相应的数据
(1）建两张表 ：一个商品goods表 一个订单order表
-- 创建表
create table goods( goods_id int primary key auto_increment,
goods_name varchar(64),
shop_price decimal(10,2),
goods_number int)charset=utf8;
create table orders(goods_id int primary key auto_increment,
goods_name varchar(64),
buy_number int)charset=utf8;
-- 插入数据
insert into goods values(null,'nokiaN85',2000,35),
(null,'iphone4S',4500,30),
(null,'Lnmia',5000,40),
(null,'samsung',4200,20);
mysql> select*from goods;
+---------------+-------------------+-------------------+----------------------+
| goods_id | goods_name | shop_price | goods_number |
+---------------+-------------------+-------------------+----------------------+
| 1 | nokiaN85 | 2000.00 | 35 |
| 2 | iphone4S | 4500.00 | 30 |
| 3 | Lnmia | 5000.00 | 40 |
| 4 | samsung | 4200.00 | 20 |
+---------------+-------------------+-------------------+----------------------+
(2)创建触发器
create trigger alter_goods_number after insert on orders for each row
update goods set goods_number=goods_number-5 where goods_id=1;
insert into orders values(1,'nokiaN85',5);
select*from goods;
new和old的使用
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
关键词new 的使用：
create trigger alter_goods_number after insert on orders for each row
update goods set goods_number=goods_number-new.buy_number
where where goods_id=new.goods_id;
insert into orders values(1,'samsung',10);
关键词old 的使用：
create trigger back_goods_number after delete on orders for each row
update goods set goods_number=goods_number+old.buy_number
where goods_id=old.goods_id;
delete from orders where goods_id=7;
更新 (update将之前下的订单撤销，再重新下订单)
create trigger update_goods_number after update on orders for each row
update goods set goods_number=goods_number+old.buy_number-new.buy_number
where where goods_id=new.goods_id;
update orders set buy_number = 10;
查看触发器
|
1
|
SHOW TRIGGERS [FROM schema_name]; |
其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就 不必先USE database_name了
删除触发器
和删除数据库、删除表格一样，删除触发器的语法如下：
|
1
|
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name |
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。
基本函数
1、数学函数
ABS(x) 返回x的绝对值 BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制） CEILING(x) 返回大于x的最小整数值 EXP(x) 返回值e（自然对数的底）的x次方 FLOOR(x) 返回小于x的最大整数值 GREATEST(x1,x2,...,xn)返回集合中最大的值 LEAST(x1,x2,...,xn) 返回集合中最小的值 LN(x) 返回x的自然对数 LOG(x,y)返回x的以y为底的对数 MOD(x,y) 返回x/y的模（余数） PI()返回pi的值（圆周率） RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 ROUND(x,y)返回参数x的四舍五入的有y位小数的值 SIGN(x) 返回代表数字x的符号的值 SQRT(x) 返回一个数的平方根 TRUNCATE(x,y) 返回数字x截短为y位小数的结果
2、聚合函数
常用于GROUP BY从句的SELECT查询中
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
3、字符串函数
ASCII(char)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
4、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值 DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date为一年中第几周(0~53) YEAR(date) 返回日期date的年份(1000~9999)
5、格式化函数
DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值
FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip) 返回IP地址的数字表示
INET_NTOA(num) 返回数字所代表的IP地址
TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
SELECT FORMAT(34234.34323432,3); SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); SELECT DATE_FORMAT(19990330,'%Y-%m-%d'); SELECT DATE_FORMAT(NOW(),'%h:%i %p'); SELECT INET_ATON('10.122.89.47'); SELECT INET_NTOA(175790383);
6、自定义函数
delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ;
7、删除函数
drop function func_name;
8、执行函数
# 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
事务
事务命令
事务特性
<1> 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
<2> 一致性（Consistency）：事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的，无论事务是否执行成功，事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点，如果数据库中的所有记录都能保证满足当前数据库中的所有约束，则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工（已经建立外键），如果数据库服务器发生错误，有一个员工没删掉，那么此时员工的部门表已经删除，那么就不符合完整性约束了，所以这样的数据库也就性能太差啦！
<3>隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
<4>持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
三、隔离性：
将数据库设计为串行化程的数据库，让一张表在同一时间内只能有一个线程来操作。如果将数据库设计为这样，那数据库的效率太低了。所以数据库的设计这没有直接将数据库设计为串行化，而是为数据库提供多个隔离级别选项，使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别。
不考虑隔离性可能出现的问题：
脏读
--一个事务读取到了另一个事务未提交的数据,这是特别危险的，要尽力防止。 a 1000 b 1000 a: start transaction； update set money=money+100 where name=b; b: start transaction; select * from account where name=b;--1100 commit; a: rollback; b: start transaction; select * from account where name=b;--1000
不可重复读
--在一个事务内读取表中的某一行数据，多次读取结果不同。（一个事务读取到了另一个事务已经提交 -- 的数据--增加记录、删除记录、修改记录），在某写情况下并不是问题，在另一些情况下就是问题。 a: start transaction; select 活期账户 from account where name=b;--1000 活期账户：1000 select 定期账户 from account where name=b；--1000 定期账户:1000 select 固定资产 from account where name=b；--1000 固定资产：1000 ------------------------------ b: start transaction; update set money=0 where name=b; commit; ------------------------------ select 活期+定期+固定 from account where name=b; --2000 总资产： 2000
虚读
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一个事务读取到了另一个事务已经提交的数据---增加记录、删除记录），在某写情况下并不是问题，在另一些情况下就是问题。 b 1000 c 2000 d 3000 a: start transaction select sum(money) from account;---3000 3000 ------------------- d:start transaction; insert into account values(d,3000); commit; ------------------- select count(*)from account;---3 3 3000/3 = 1000 1000
四个隔离级别：
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中设置数据库的隔离级别语句：
|
1
|
set [global/session] transaction isolation level xxxx; |
如果使用global则修改的是数据库的默认隔离级别，所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改，则修改的是当前客户端的隔离级别，和数据库默认隔离级别无关。当前的客户端是什么隔离级别，就能防止什么隔离级别问题，和其他客户端是什么隔离级别无关。
mysql中设置数据库的隔离级别语句：
select @@tx_isolation;
索引
定义
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
|
1
2
3
4
5
6
7
|
30 10 40 5 15 35 661 6 11 19 21 39 55 100 |
索引种类
MySQL中常见索引有：
- 普通索引
- 唯一索引
- 主键索引
- 组合索引
1、普通索引
普通索引仅有一个功能：加速查询
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) )
create index index_name on table_name(column_name)
drop index_name on table_name;
show index from table_name;
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。
create index ix_extra on in1(extra(32));
2、唯一索引
唯一索引有两个功能：加速查询 和 唯一约束（可含null）
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) )
create unique index 索引名 on 表名(列名)
drop unique index 索引名 on 表名
3、主键索引
主键有两个功能：加速查询 和 唯一约束（不可含null）
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) )
alter table 表名 add primary key(列名);
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
4、组合索引
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。
create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text )
create index ix_name_email on in3(name,email);
如上创建组合索引之后，查询：
- name and email -- 使用索引
- name -- 使用索引
- email -- 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。
索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
相关命令
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
- 查看表结构 desc 表名- 查看生成表的SQL show create table 表名- 查看索引 show index from 表名- 查看执行时间 set profiling = 1; SQL... show profiles; |
使用索引和不使用索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。# 有索引mysql> select * from tb1 where name = 'wupeiqi-888';+-----+-------------+---------------------+----------------------------------+---------------------+| nid | name | email | radom | ctime |+-----+-------------+---------------------+----------------------------------+---------------------+| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |+-----+-------------+---------------------+----------------------------------+---------------------+1 row in set (0.00 sec)# 无索引mysql> select * from tb1 where email = 'wupeiqi888@live.com';+-----+-------------+---------------------+----------------------------------+---------------------+| nid | name | email | radom | ctime |+-----+-------------+---------------------+----------------------------------+---------------------+| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |+-----+-------------+---------------------+----------------------------------+---------------------+1 row in set (1.23 sec) |
正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
- 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 -- 不使用索引 |
其他注意事项
|
1
2
3
4
5
6
7
8
9
|
- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复少）不适合建索引，例：性别不适合 |
limit分页
无论是否有索引，limit分页是一个值得关注的问题
每页显示10条： 当前 118 120， 125 倒序： 大 小 980 970 7 6 6 5 54 43 32 21 19 98 下一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10; 上一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) order by nid desc limit 10;
执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
|
1
2
3
4
5
6
7
|
mysql> explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec) |
id
查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
| 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
特别的：如果使用union连接气值可能为null
select_type
查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
...
table
正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引
key
真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
更多参见：
http://www.cnblogs.com/xiaoboluo768/p/5400990.html
http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_system
慢日志查询
a、配置MySQL自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
注：查看当前配置信息：
show variables like '%query%'
修改当前配置：
set global 变量名 = 值
b、查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
"""--verbose 版本--debug 调试--help 帮助-v 版本-d 调试模式-s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM 显示前N条just show the top n queries-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'-n NUM abstract numbers with at least n digits within names-g PATTERN 正则匹配；grep: only consider stmts that include this string-h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all-i NAME name of server instance (if using mysql.server startup script)-l 总时间中不减去锁定时间；don't subtract lock time from total time""" |
参考袁老师：http://www.cnblogs.com/yuanchenqi/articles/7250680.html
http://www.cnblogs.com/yuanchenqi/articles/6437362.html
武沛奇：http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.cnblogs.com/wupeiqi/articles/5716963.html
浙公网安备 33010602011771号