温馨提示
mysql安装包里面：
mysqld是服务端，mysql是客户端。
mysqld其实是SQL后台程序(也就是MySQL服务器)，它是关于服务器端的一个程序，mysqld意思是mysql daemon，在后台运行，监听3306端口，如果你想要使用客户端程序，这个程序必须运行，因为客户端是通过连接服务器来访问数据库的。你只有启动了mysqld.exe，你的mysql数据库才能工作。
mysql是一个客户端软件，可以对任何主机的mysql服务（即后台运行的mysqld）发起连接，mysql自带的客户端程序一般都在cmd或者终端下进行操作
mysqld是用来启动mysql数据库的命令
mysql是打开并执行sql语句的命令
注意
在你使用MySQL的过程中，即使你用的是UTF-8编码的客户端，服务器也是UTF-8编码的，数据库也是，就连要保存的这个字符串“ <…”也是合法的UTF-8。
有的时候会报一串奇怪的错误，问题的症结在于，MySQL的“utf8”实际上不是真正的UTF-8。
MySQL的“utf8”只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节。
MySQL一直没有修复这个bug，他们在2010年发布了一个叫作“utf8mb4
”的字符集，绕过了这个问题。
当然，他们并没有对新的字符集广而告之（可能是因为这个bug让他们觉得很尴尬），以致于现在网络上仍然在建议开发者使用“utf8”，但这些建议都是错误的。
简单概括如下：
1.MySQL的“utf8mb4”是真正的“UTF-8”。
2.MySQL的“utf8”是一种“专属的编码”，它能够编码的Unicode字符并不多。
因此：所有在使用“utf8”的MySQL和MariaDB用户都应该改用“utf8mb4”，永远都不要再使用“utf8”。
一、什么是数据库
1、概念：数据库是一个可以在一台机器上独立工作的，并且可以给我们提供高效、便捷的方式对数据进行增删改查的一种工具。2、优势1.程序稳定性 ：任意一台服务器崩溃了都不会影响数据和另外的服务器。2.数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象3.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket4.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多
二、数据库的一些名词
1、数据/记录：描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机
例如：1,明哥,猛男,20,东北
但是单纯的一条记录并没有任何意义，如果我们按逗号作为分隔，给各个字段设置标题，那么就具有可读性了
id,name,sex,age,hometown1,明哥,猛男,20,东北2、数据库(DataBase,DB):数据库是长期存放在计算机内、有组织、可共享的数据集合。
其实数据库中的库就是文件夹，一般情况下 每个程序使用一个库，库中一张表(table)就是一个文件3、数据库管理系统(DataBase Management System ,DBMS)：数据库管理系统就是一款专门用于管理数据库的系统软件，就是通过一个软件来管理文件夹、文件、数据
现在主流的数据库有：
关系型数据库
mysql 开源，主要用于大型门户
oracle 企业级，功能强大、安全但是费用高
sqlite 轻量级，文件数据库
sql server 大学课程多数用这个，是微软公司的产品
非关系型数据库：消息转发
memcache
redis
MongoDB
nosql
解释什么是关系型
关系型
一条数据包含了一个事物的多条信息，这些信息之间是有关联性的
非关系型 :存取频繁的，并且要求效率高的，不突出数据之间关联的
k-v
id content4、数据库服务器：数据库管理系统装在哪台机器上，哪台机器就是数据库服务器，但是对这台机器的内存要求比较高5、数据库管理员(Database Administrator，DBA)：维护数据库管理系统(DBMS)的相关工作人员的统称6、总结
数据库服务器：运行数据库管理软件
数据库管理员：维护数据库管理软件的人
数据库管理软件：管理-数据库
数据库：即文件夹，用来组织文件/表
表：即文件，用来存放多行内容/多条记录
三、初识MySQL
1、介绍
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTERDML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
2、相关操作
0、Mysql数据库的导入和导出
1.mysqldump是mysql用于转存储数据库的实用程序(后缀是dump或者sql都行)
注意：这个命令是在linux/windows的终端敲的，用于导出数据库(就是还没有进入mysql客户端)
导出一个数据库的结构以及数据
mysqldump -u root -p dbname > dbname.sql
导出多个数据库的结构以及数据
mysqldump -u root -p -B dbname1 dbname2 > dbname.sql
导出所有数据库
mysqldump -u root -p --all-databases > xxx.dump
2.mysql导入数据库
假设已经导出了一个数据库文件 db.sql
方法一：
1. 进入MySQL客户端
2. 创建数据库
create database db;
3. 退出mysql客户端，在终端敲
mysql -u root -p < /opt/db.sql
方法二：
1. 进入MySQL客户端
2. 创建数据库
create database db;
3. use db;
4. source /opt/db.sql
1、 启动server端 - 可以在service服务中操作，也可以打开cmd窗口> net start mysql2、停止服务 - 可以在service服务中操作，也可以打开cmd窗口> net stop mysql3、登录用户> mysql -u root -p # mysql5.6默认是没有密码的,遇到password直接按回车键4、查看所有的库
mysql> show databases;
5、查看当前用户
mysql> select user();
6、查看当前使用的数据库
mysql> select database();
7、退出当前用户
mysql> exit # 也可以用\q或者quit退出8、给当前用户设置密码
mysql> set password = password('newpassword');
9、给其他用户设置密码
mysql> set password for 'username'@'host' = password('newpassword')
10、在输入sql语句的过程中 如果想要放弃本条语句
mysql> \c11、创建账号
# %表示这个网段内所有ip都可以连接,identified by '123'表示给这个用户设置密码123
mysql> create user 'ming'@'192.168.16.%' identified by '123';
mysql> create user 'ming'@'192.168.16.5' # 表示某机器可以连接
mysql> create user 'ming'@'%' # 表示所有机器都可以连接
12、远程登陆
mysql> mysql -u ming -p123 -h 192.168.16.31 # u是user p是password h是localhost的ip地址13、使用某个数据库(切换到文件夹)use 数据库名;
四、存储引擎
1、概念
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
2、mysql支持的存储引擎
mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、BLACKHOLE、CSV、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。
其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
3、各种存储引擎的特性
并发性：某些应用程序比其他应用程序具有很多的颗粒级锁定要求（如行级锁定）。
事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如ACID兼容等。
引用完整性：通过DDL定义的外键，服务器需要强制保持关联数据库的引用完整性。
物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。
索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如B-tree索引）对几乎所有的存储引擎来说是共同的。
内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。
性能帮助：包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。
其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。
4、事务，行级锁，表级锁的解释
1.事务：一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
比如：一个转账的场景小明转账给小红，如果转账的过程中数据库挂了，那么小明和小红的数据都不会发生改变事务的四大特性： 原子性：事务包含的所有操作要么全部成功，要么全部失败回滚;成功必须要完全应用到数据库，失败则不能对数据库产生影响；
一致性：事务执行前和执行后必须处于一致性状态
隔离性：当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便在数据库系统遇到故障的情况下也不会丢失事物的操作
开启事务
更新小明的数据：他的钱-200更新小红的数据：她的钱+200关闭事务
如果在小明转账但是小红还没收到账的中途，数据库挂了：
开启事务
更新小明的数据：他的钱-200这个时候数据库挂了
更新小红的数据：她的钱+200关闭事务
那么小明的钱不会-200，小红的钱也不会+200，好像没发生转账这件事一样2.行级锁：不同行可以同时修改，同一行不能同时修改
msyql同一张表中不同行的记录可以被同时修改
但是同一张表中同一行的记录不能被同时修改3.表级锁：不同表可以同时修改，同一张表不能同时修改
5、存储引擎机制
1.InnoDB：支持事务、行级锁、外键,保持事务的完整性，在修改数据的效率比较快 MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。 InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。
2.MyISAM：表级锁，查询速度快，但是插入和修改效率慢 MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。
3.Memory：数据都存在内存中，处理数据的速度快，但是对内存要求高，重启服务和断电消失 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失， 长用于数据量小的数据库，并对服务器的内存有要求，一般应用于每个用户的登录状态。
4.BLACKHOLE：放进去的所有数据都不会存储，但有一个日志记录着插入的数据，利用日志分流数据 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。
5.CSV 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。
6.NDB (又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。目前能够支持 MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。
7.Merge 允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。
8.Federated 提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。
6、常用的存储引擎及适用场景
InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。 InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
7、存储相关sql语句
1.查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";2.查询当前数据库支持的存储引擎
mysql> show engines \G;3.指定存储引擎建表3-1.在建表时指定
mysql> create table t1(id int,name varchar(20)) ENGINE=MyISAM;
mysql> create table country(id int,cname varchar(50)) ENGINE=InnoDB;3-2.也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table country engine = MyISAM;4.在配置文件中指定
#my.ini文件[mysqld]default-storage-engine=INNODB
五、库操作(DDL语句数据库定义语言)
1、create:创建
create database 数据库名 charset utf8; // 创建数据库create table 表名(id int,name char); // (列名1 数据类型,列名2，数据类型...) 这个是创建表和表头，还没有内容的
2、alter:跟新
ALTER数据库的操作alter database 数据库名 charset latin1(要修改的内容); // 修改数据库ALTER表的操作1. 修改表名 ALTER TABLE 表名 RENAME 新表名;2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段 ALTER TABLE 表名 DROP 字段名;4. 修改字段：modify 修改数据类型和约束，change修改字段名并重新定义数据类型和约束 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 放在首位
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 在某个字段后
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
6.增加/删除约束 ALTER TABLE 表名 ADD PRIMARY KEY (字段);
ALTER TABLE 表名 ADD UNIQUE (字段);
ALTER TABLE 表名 DROP PRIMARY KEY;
例子
3、drop:删除
drop database 数据库名; // 删除数据库drop table 表名; // 删除表
4、show/desc:查询
show databases; 表名; 表名 \G; 查看当前表更详细的信息
show 库名 \G; 查看当前数据的详细信息
六、表(记录)操作(DML语句数据库操纵语言)
1、表的基础操作
1、insert:插入数据1. 插入完整数据（顺序插入）
语法一： INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二： INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据
语法： INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录
语法： INSERT INTO 表名 VALUES (值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);
4. 插入查询结果
语法： INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;2、update:跟新数据语法： UPDATE 表名 SET 字段1=值1,字段2=值2,WHERE CONDITION;
示例： UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;
注：mysql库的user表存储的是用户的信息3、delete:删除数据语法： DELETE FROM 表名
WHERE CONITION;
示例： DELETE FROM mysql.user WHERE password=’’;4、select:查询数据查看所有列的数据select * from staff_info;
查看指定列的数据select name,age from staff_info;4-1单表查询语法SELECT [DISTINCT] 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 筛选 ORDER BY 字段[ASC/DESC]
LIMIT 限制条数
1、关键字执行的优先级1.找到表:from2.拿着where指定的约束条件，去表中取出一条条记录3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组4.将分组的结果进行having过滤5.执行select6.distinct去重7.将结果按条件排序：order by8.限制结果的显示条数
2、建表数据
示例表结构
3、简单的查询示例
查询
4、where
1.where字句中可以使用：
(1) 比较运算符：> < >= <= <> !=(2) between 80 and 100 值在80和100之间的数
(3) in(80,90,100) 值是80或90或100
(4) like 'abc%'
pattern可以是%或_， %表示任意多字符
_表示一个字符
(5) 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
where示例
5、group by
(1)
单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select默认查询只会显示每个组的第一个数据，想要获取组内的其他相关信息，需要借助函数
(2)GROUP BY关键字和GROUP_CONCAT()函数一起使用
GROUP_CONCAT：把分组的内容拼成一列展示出来 SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post; # 按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(emp_name,age) FROM employee GROUP BY post; # 按照岗位分组，并查看组内成员名和年龄 SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
(3)GROUP BY与聚合函数一起使用count：数分组的记录的数量 SELECT post,COUNT(id) AS count FROM employee GROUP BY post; # 按照岗位分组，并查看每个组有多少人
(4)group_concat对比concat4-1.
GROUP_CONCAT()里面的参数只能用逗号隔开，不能完全自由拼接SELECT post,GROUP_CONCAT(emp_name,age) FROM employee GROUP BY post;
+-----------+-------------------------------------------------------------------+| post | GROUP_CONCAT(emp_name,age) |+-----------+-------------------------------------------------------------------+| IT | 小红18,晓东22,张三81,李四73,王铭28,小晶18,张一帆48 || operation | 朱一18,朱二18,朱三18,朱四18,张三丰28 || plan | 呜呜28,思思18,伞伞18,尔尔38,依依48 || 外交 | 小明18 |+-----------+-------------------------------------------------------------------+4-2.
CONCAT() 函数用于连接字符串，完全可以按照自己的想法来拼接字符串SELECT CONCAT('姓名: ',emp_name,' 年薪: ', salary*12) AS year_salary FROM employee;+--------------------------------------+| year_salary |+--------------------------------------+| 姓名: 小明 年薪: 87603.96 || 姓名: 晓东 年薪: 12000003.72 || 姓名: 张三 年薪: 99600.00 || 姓名: 李四 年薪: 42000.00 || 姓名: 王铭 年薪: 25200.00 || 姓名: 小晶 年薪: 108000.00 || 姓名: 小红 年薪: 360000.00 || 姓名: 张一帆 年薪: 120000.00 || 姓名: 依依 年薪: 36001.56 || 姓名: 尔尔 年薪: 24004.20 || 姓名: 伞伞 年薪: 12004.44 || 姓名: 思思 年薪: 36003.48 || 姓名: 呜呜 年薪: 48003.96 || 姓名: 张三丰 年薪: 120001.56 || 姓名: 朱一 年薪: 240000.00 || 姓名: 朱二 年薪: 228000.00 || 姓名: 朱三 年薪: 216000.00 || 姓名: 朱四 年薪: 204000.00 |+--------------------------------------+4-3.
CONCAT_WS() 第一个参数为分隔符，跟GROUP_CONCAT类似，不能完全自由拼接，但是可自定义拼接符号SELECT CONCAT_WS(':',emp_name,salary*12) AS year_salary FROM employee;+---------------------+| year_salary |+---------------------+| 小明:87603.96 || 晓东:12000003.72 || 张三:99600.00 || 李四:42000.00 || 王铭:25200.00 || 小晶:108000.00 || 小红:360000.00 || 张一帆:120000.00 || 依依:36001.56 || 尔尔:24004.20 || 伞伞:12004.44 || 思思:36003.48 || 呜呜:48003.96 || 张三丰:120001.56 || 朱一:240000.00 || 朱二:228000.00 || 朱三:216000.00 || 朱四:204000.00 |+---------------------+
6、聚合函数
聚合函数聚合的是分组后每一组的内容，若是没有分组，则默认所有数据都是一组
函数： COUNT:数分组的记录的数量 MAX:分组内最大的数值 MIN:分组内最小的数值 AVG:分组内的数值的平均数 SUM:分组内的数值的和
示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;
聚合函数示例
7、HAVING过滤
(1)HAVING与WHERE不一样的地方在于： 1.执行优先级从高到低：where > group by > having
2. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 3. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
(2)例子：1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数select post,group_concat(emp_name) as name,count(id) as count from employee group by post having count(id)<2;3. 查询各岗位平均薪资大于10000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary)>10000;4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
8、ORDER BY 查询排序
(1)按单列排序 SELECT * FROM employee ORDER BY salary; # 默认是升序排序 SELECT * FROM employee ORDER BY salary ASC; # 升序排序(默认) SELECT * FROM employee ORDER BY salary DESC; # 降序排序
(2)按多列排序:先按照age升序排序，如果年纪相同，则按照薪资降序排序 SELECT * from employee ORDER BY age,salary DESC;
(3)例子：1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序select * from employee order by age,hire_date DESC;2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary);3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
9、LIMIT 限制查询的记录数
用法：LIMIT n,m ---> n是起始位置，m是包括起始记录在内，一共要查的记录数量
#默认初始位置为0，即取索引0,1,2的记录
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
#从索引为0的记录(第一条记录)开始，往后再查4条记录(第五条记录) SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;
#从索引为5的记录(第六条记录)开始，往后再查4条记录(第十条记录) SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5;
10、使用正则表达式查询(也类似于模糊查询like)
1.MySQL中使用 REGEXP 操作符来进行正则表达式匹配 SELECT * FROM employee WHERE emp_name REGEXP '^小';
2.示例：SELECT * FROM employee WHERE emp_name REGEXP '^小'; # 匹配以'小'开头的人的信息SELECT * FROM employee WHERE emp_name REGEXP '三$'; # 匹配以'三'结尾的人的信息SELECT * FROM employee WHERE emp_name REGEXP 'a{2}'; # # 匹配有两个'a'的人的信息3.小结：对字符串匹配的方式WHERE emp_name = '小明';WHERE emp_name LIKE '小%';WHERE emp_name REGEXP '三$';4.例子：
查看所有员工中名字是'小'开头的员工信息select * from employee where emp_name regexp '^小';
七、多表查询
1、数据准备
表结构和数据
2、连接语法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
3、交叉连接
交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;+----+-------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name |+----+-------+--------+------+--------+------+--------------+| 1 | cat | male | 18 | 200 | 200 | 技术 || 1 | cat | male | 18 | 200 | 201 | 人力资源 || 1 | cat | male | 18 | 200 | 202 | 销售 || 1 | cat | male | 18 | 200 | 203 | 运营 || 2 | dog | female | 48 | 201 | 200 | 技术 || 2 | dog | female | 48 | 201 | 201 | 人力资源 || 2 | dog | female | 48 | 201 | 202 | 销售 || 2 | dog | female | 48 | 201 | 203 | 运营 || 3 | pig | male | 38 | 201 | 200 | 技术 || 3 | pig | male | 38 | 201 | 201 | 人力资源 || 3 | pig | male | 38 | 201 | 202 | 销售 || 3 | pig | male | 38 | 201 | 203 | 运营 || 4 | bird | female | 28 | 202 | 200 | 技术 || 4 | bird | female | 28 | 202 | 201 | 人力资源 || 4 | bird | female | 28 | 202 | 202 | 销售 || 4 | bird | female | 28 | 202 | 203 | 运营 || 5 | tiger | male | 18 | 200 | 200 | 技术 || 5 | tiger | male | 18 | 200 | 201 | 人力资源 || 5 | tiger | male | 18 | 200 | 202 | 销售 || 5 | tiger | male | 18 | 200 | 203 | 运营 || 6 | lion | female | 18 | 204 | 200 | 技术 || 6 | lion | female | 18 | 204 | 201 | 人力资源 || 6 | lion | female | 18 | 204 | 202 | 销售 || 6 | lion | female | 18 | 204 | 203 | 运营 |+----+-------+--------+------+--------+------+--------------+24 rows in set (0.00 sec)
4、内连接
内连接( employee.id,employee.name,employee.age,employee.sex,department.name employee department employee.dep_id id name age sex name cat male 技术 dog female 人力资源 pig male 人力资源 bird female 销售 tiger male 技术 rows ( employee.id,employee.name,employee.age,employee.sex,department.name employee,department employee.dep_iddepartment.id;
5、外链接之左连接
外链接之左连接：优先显示左表全部记录
说明：
以左表为准，即找出所有员工信息，包括没有部门的员工
本质就是：在内连接的基础上增加左表有而右表没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;+----+-------+--------------+| id | name | depart_name |+----+-------+--------------+| 1 | cat | 技术 || 5 | tiger | 技术 || 2 | dog | 人力资源 || 3 | pig | 人力资源 || 4 | bird | 销售 || 6 | lion | NULL |+----+-------+--------------+6 rows in set (0.00 sec)
6、外链接之右连接
外链接之右连接：优先显示右表全部记录
说明：
以右表为准，即找出所有部门信息，包括没有员工的部门
本质就是：在内连接的基础上增加右表有而左表没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;+------+-------+--------------+| id | name | depart_name |+------+-------+--------------+| 1 | cat | 技术 || 2 | dog | 人力资源 || 3 | pig | 人力资源 || 4 | bird | 销售 || 5 | tiger | 技术 || NULL | NULL | 运营 |+------+-------+--------------+6 rows in set (0.00 sec)
7、全外连接
全外连接：显示左右两个表全部记录
说明：
在内连接的基础上增加左表有右表没有的和右表有左表没有的结果
注意：mysql不支持全外连接 FULL JOIN
但是：mysql可以使用UNION这种方式间接实现全外连接，需要注意的是union与union all的区别：union会去掉相同的纪录select * from employee left join department on employee.dep_id=department.id
union select * from employee right join department on employee.dep_id=department.id;+------+-------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name |+------+-------+--------+------+--------+------+--------------+| 1 | cat | male | 18 | 200 | 200 | 技术 || 5 | tiger | male | 18 | 200 | 200 | 技术 || 2 | dog | female | 48 | 201 | 201 | 人力资源 || 3 | pig | male | 38 | 201 | 201 | 人力资源 || 4 | bird | female | 28 | 202 | 202 | 销售 || 6 | lion | female | 18 | 204 | NULL | NULL || NULL | NULL | NULL | NULL | NULL | 203 | 运营 |+------+-------+--------+------+--------+------+--------------+7 rows in set (0.02 sec)
查询示例
8、子查询
(1)子查询是将一个查询语句嵌套在另一个查询语句中。
(2)内层查询语句的查询结果，可以为外层查询语句提供查询条件。
(3)子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
(4)还可以包含比较运算符：= 、 !=、> 、<等4-1、带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);+------+--------------+| id | name |+------+--------------+| 201 | 人力资源 || 202 | 销售 |+------+--------------+2 rows in set (0.04 sec)
#查看技术部员工姓名
mysql> select name from employee where dep_id=(select id from department where name='技术');+-------+| name |+-------+| cat || tiger |+-------+2 rows in set (0.00 sec)
#查看不足1人的部门名(没有人的部门)
mysql> select name from department where id not in (select distinct dep_id from employee);+--------+| name |+--------+| 运营 |+--------+1 row in set (0.00 sec)4-2、带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>#查询大于所有人平均年龄的员工名与年龄(where里面是不能使用聚合函数的)
mysql> select name,age from employee where age>(select avg(age) from employee);+------+------+| name | age |+------+------+| dog | 48 || pig | 38 |+------+------+2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
mysql> select name,age from employee group by dep_id having age>avg(age);+------+------+| name | age |+------+------+| dog | 48 |+------+------+1 row in set (0.00 sec)4-3、带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个bool值，True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=200，Ture
mysql> select * from employee where exists (select id from department where id=200);+----+-------+--------+------+--------+| id | name | sex | age | dep_id |+----+-------+--------+------+--------+| 1 | cat | male | 18 | 200 || 2 | dog | female | 48 | 201 || 3 | pig | male | 38 | 201 || 4 | bird | female | 28 | 202 || 5 | tiger | male | 18 | 200 || 6 | lion | female | 18 | 204 |+----+-------+--------+------+--------+6 rows in set (0.00 sec)
八、权限相关(DCL语句数据库控制语言)
1、权限：：
usage 无权限 all 全部权限 select 查 create 增 drop 删
2、授权具体语句：grant 权限 on 库.表 to 用户
mysql> grant all on *.* to 'ming'@'%'; # all表示授予所有权限，第一个*代表所有库，第二个*代表所有表，'ming'@'%'代表用户和客户端主机，%代表任意主机
mysql> grant select on db1.* to 'ming'@'localhost'; # select代表只给查的功能，db1.* 代表库db1的所有表，localhost代表本地主机
# 给未存在的用户授权，会创建再授权
mysql> grant all on *.* to 'dong'@'%' identified by '123456' # 创建账号并授权
# 刷新权限
mysql> flush privileges; # 刷新使授权立即生效
# 查看用户权限
show grants for 'ming'@'%';3、回收权限：revoke 权限 on 库.表 from 用户
mysql> revoke select on *.* from 'ming'@'%';
九、mysql中的数据类型
1、数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号）unsigned约束 | 用途 |
|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT |
4 字节
float(255,30)
| (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度
浮点数值 |
| DOUBLE |
8 字节
double(255,30)
| (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度
浮点数值 |
| DECIMAL |
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
double(65,30)
| 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
介绍常用的类型：
整型：int\integer:
有符号时的范围：(-2147483648，2147483647) --> 2**31 无符号时的范围：(0，4294967295) --> 2**32 给字段添加一个unsigned表示无符号，默认是有符号的计算范围：int占4个字节，每个字节用8位二进制表示的，共4*8=32位二进制数，而每位二进制可以表示两种情况，故无符号时的范围是：2**32，
有符号时，用一位二进制表示符号，故有符号的范围是：2**31，其他数据类型的范围也是类似此算法。
小数：float： 单精度小数，不是很精确double： 双精度小数，不是很精确，但是比float精确一点DECIMAL：非常精确的小数，对数据要求很精确的时候使用，比如汇率，利息等。
int整数示例
# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table int_table (num1 int,num2 int(5));
# 向t1中插入数据1,2mysql> insert into int_table values(1,2);
# 此时并没有异常
mysql> select * from int_table;+------+------+| num1 | num2 |+------+------+| 1 | 2 |+------+------+# 继续插入比宽度更大的值，num2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> insert into int_table values(123456,1234567);
mysql> select * from int_table;+--------+---------+| num1 | num2 |+--------+---------+| 1 | 2 || 123456 | 1234567 |+--------+---------+# 修改num1字段 给字段添加一个unsigned表示无符号
mysql> alter table int_table modify num1 int unsigned;
mysql> desc int_table;+-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| num1 | int(10) unsigned | YES | | NULL | || num2 | int(5) | YES | | NULL | |+-------+------------------+------+-----+---------+-------+# 当给num1添加的数据大于2147483647时，可以顺利插入
mysql> insert into int_table values (2147483648,2147483647);
Query OK, 1 row affected (0.07 sec)
# 当给num2添加的数据大于2147483647时，会报错
mysql> insert into int_table values (2147483648,2147483648);
ERROR 1264 (22003): Out of range value for column 'num2' at row 1# 总结：
int类型，存储的值只要不超过int的范围(有符号和无符号的范围不同)，指不指定宽度都是一样的。
小数示例
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table test_table(num1 float(5,2),num2 double(5,2),num3 decimal(5,2));
# 向表中插入3.14，结果正常
mysql> insert into test_table values(3.14,3.14,3.14);
Query OK, 1 row affected (0.11 sec)
mysql> select * from test_table;+------+------+------+| num1 | num2 | num3 |+------+------+------+| 3.14 | 3.14 | 3.14 |+------+------+------+1 row in set (0.00 sec)
# 向表中插入3.141，会发现1都被截断了
mysql> insert into test_table values(3.141,3.141,3.141);
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> select * from test_table;+------+------+------+| num1 | num2 | num3 |+------+------+------+| 3.14 | 3.14 | 3.14 || 3.14 | 3.14 | 3.14 |+------+------+------+2 rows in set (0.00 sec)
# 向表中插入3.145发现数据虽然被截断，但是遵循了四舍五入的规则
mysql> insert into test_table values(3.145,3.145,3.145);
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> select * from test_table;+------+------+------+| num1 | num2 | num3 |+------+------+------+| 3.14 | 3.14 | 3.14 || 3.14 | 3.14 | 3.14 || 3.15 | 3.15 | 3.15 |+------+------+------+3 rows in set (0.00 sec)
# 建新表去掉参数约束
mysql> create table test2_table(num1 float,num2 double,num3 decimal);
Query OK, 0 rows affected (0.63 sec)
# 分别插入3.145mysql> insert into test2_table values(3.145,3.145,3.145);
Query OK, 1 row affected, 1 warning (0.06 sec)
# 发现decimal默认值是(10,0)的整数
mysql> select * from test2_table;+-------+-------+------+| num1 | num2 | num3 |+-------+-------+------+| 3.145 | 3.145 | 3 |+-------+-------+------+1 row in set (0.00 sec)
# 当对小数位没有约束的时候，输入超长的小数，会发现float没有double精确，但是实际上两个都不完全准确(用更长的数字试试)
mysql> insert into test2_table values(3.14159612345,3.14159612345,3.14159612345);
Query OK, 1 row affected, 1 warning (0.09 sec)
mysql> select * from test2_table;+--------+---------------+------+| num1 | num2 | num3 |+--------+---------------+------+| 3.145 | 3.145 | 3 || 3.1416 | 3.14159612345 | 3 |+--------+---------------+------+2 rows in set (0.00 sec)
2、日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值
| 类型 | 大小
(字节) | 范围 | 格式 | 用途 |
|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 年月日 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时分秒 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 年月日时分秒 |
| TIMESTAMP | 4 |
1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07
| YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
常用类型介绍
date 描述年月日 1000-01-01/9999-12-31time 描述时分秒
datetime 描述年月日时分秒 1000-01-01 00:00：00/9999-12-31 23:59:59timestamp 描述年月日时分秒，字段默认不为空 1970-01-01 00:00:00/2038timestamp默认不为空，默认是当前时间，范围比datetime小
mysql> create table t1 (d date,t time,y year,dt datetime,ts timestamp);
mysql> desc t1;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+-------------------+-----------------------------+| d | date | YES | | NULL | || t | time | YES | | NULL | || y | year(4) | YES | | NULL | || dt | datetime | YES | | NULL | || ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+-------+-----------+------+-----+-------------------+-----------------------------+mysql> insert into t1 values (null,null,null,null,null);
mysql> select * from t1;+------+------+------+------+---------------------+| d | t | y | dt | ts |+------+------+------+------+---------------------+| NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 |+------+------+------+------+---------------------+# 插入当期时间
mysql> insert into t1 values(now(),now(),now(),now(),now());
# 每种数据类型表示的时间格式
mysql> select * from t1;+------------+----------+------+---------------------+---------------------+| d | t | y | dt | ts |+------------+----------+------+---------------------+---------------------+| NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 || 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 |+------------+----------+------+---------------------+---------------------+# datetime 和 timestamp的范围控制
mysql> insert into t1 (dt) values (10010101000000);
mysql> select * from t1;+------------+----------+------+---------------------+---------------------+| d | t | y | dt | ts |+------------+----------+------+---------------------+---------------------+| NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 || 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 || NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 12:13:14 |+------------+----------+------+---------------------+---------------------+# 超出了timestamp的范围，就使用它的"零"值
mysql> insert into t1(ts) values (10010101000000);
mysql> select * from t1;+------------+----------+------+---------------------+---------------------+| d | t | y | dt | ts |+------------+----------+------+---------------------+---------------------+| NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 || 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 || NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 12:13:14 || NULL | NULL | NULL | NULL | 0000-00-00 00:00:00 |+------------+----------+------+---------------------+---------------------+
3、字符串类型
| 类型 | 大小 | 用途 |
|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
常用类型介绍
CHAR 0-255字节 定长字符串
定长 浪费磁盘 存取速度非常快
VARCHAR 0-65535 字节 变长字符串
变长 节省磁盘空间 存取速度相对慢
例如：char(5)：
定长的，无论写多少字节都是你设置的那个长度
'abcde':5个字节
'abc':会变成' abc' 会在前面补两个空格，还是5个字节
适用于数据的长度变化小或者不变化的场景：手机号 身份证号 学号
频繁存取、对效率要求高
短数据varchar(5)：
不定长，但是会在你输入的字节前加上长度信息，即总是多一个字节
'abc' ：会变成'3abc' 3是你输入的字节abc的长度，但是显示的时候我们看不到这个3，但存储的时候总字节是4
'abcde' 会变成'5abcde' 5是你输入的字节abcde的长度，但是显示的时候我们看不到这个5，但存储的时候总字节是6
适用于数据长度变化大的场景：name 描述信息
对效率要求相对小
数据相对较长
例子：
mysql> create table t10 (c char(5),vc varchar(5));
mysql> desc t10;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| c | char(5) | YES | | NULL | || vc | varchar(5) | YES | | NULL | |+-------+------------+------+-----+---------+-------+# 插入ab，实际上存储中c占用5个字节，vc只占用3个字节，但是我们查询的时候感知不到
# 因为char类型在查询的时候会默认去掉所有补全的空格
mysql> insert into t10 values ('ab','ab');
mysql> select * from t10;+------+------+| c | vc |+------+------+| ab | ab |+------+------+# 插入的数据超过了约束的范围，会截断数据
mysql> insert into t10 values ('abcdef','abcdef');
mysql> select * from t10;+-------+-------+| c | vc |+-------+-------+| ab | ab || abcde | abcde |+-------+-------+# 插入带有空格的数据，查询的时候能看到varchar字段是带空格显示的，char字段仍然在显示的时候去掉了空格
mysql> insert into t10 values ('ab ','ab ');
mysql> select * from t10;+-------+-------+| c | vc |+-------+-------+| ab | ab || abcde | abcde || ab | ab |+-------+-------+# 用concat连接查看结果
mysql> select concat(c,'+'),concat(vc,'+') from t10;+---------------+----------------+| concat(c,'+') | concat(vc,'+') |+---------------+----------------+| ab+ | ab+ || abcde+ | abcde+ || ab+ | ab + |+---------------+----------------+
4、ENUM和SET类型
| 类型 | 大小 | 用途 |
| ENUM |
对1-255个成员的枚举需要1个字节存储;
对于255-65535个成员，需要2个字节存储;
最多允许65535个成员。
| 单选：选择性别 |
| SET |
1-8个成员的集合，占1个字节
9-16个成员的集合，占2个字节
17-24个成员的集合，占3个字节
25-32个成员的集合，占4个字节
33-64个成员的集合，占8个字节
| 多选：兴趣爱好 |
常用类型介绍
枚举 enum 单选
集合 set 多选
mysql> create table t11 (name varchar(20),sex enum('male','female'),hobby set('抽烟','喝酒','烫头','翻车'));
mysql> desc t11;+-------+------------------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------------------------------+------+-----+---------+-------+| name | varchar(20) | YES | | NULL | || sex | enum('male','female') | YES | | NULL | || hobby | set('抽烟','喝酒','烫头','翻车') | YES | | NULL | |+-------+------------------------------------------+------+-----+---------+-------+# 如果插入的数据不在枚举或者集合范围内，数据无法插入表
mysql> insert into t11 values ('ming','aaaa','bbbb');
mysql> select * from t11;+------+------+-------+| name | sex | hobby |+------+------+-------+| ming | | |+------+------+-------+# 向集合中插入数据，自动去重
mysql> insert into t11 values ('ming','female','抽烟,抽烟,烫头');
mysql> select * from t11;+------+--------+---------------+| name | sex | hobby |+------+--------+---------------+| ming | | || ming | female | 抽烟,烫头 |+------+--------+---------------+# 向集合中插入多条数据，不存在的项无法插入
mysql> insert into t11 values ('ming','female','抽烟,抽烟,烫头,打架');
mysql> select * from t11;+------+--------+---------------+| name | sex | hobby |+------+--------+---------------+| ming | | || ming | female | 抽烟,烫头 || ming | female | 抽烟,烫头 |+------+--------+---------------+
十、完整性约束
1、完整性约束
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：NOT NULL ：非空约束，指定某列不能为空；
UNIQUE : 唯一约束，指定某列或者几列组合不能重复PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性not null：非空 default：设置了非空约束的字段才能设置默认值，如果不输入就使用默认的值
unique：唯一
auto_increment：只有唯一且数字类型的字段才能设置自增
联合唯一 unique(字段1，字段2...)
就是给一个以上的字段设置 唯一约束
primary key：主键--> 唯一+非空 加速查询 每张表只能有一个主键 当我们以唯一且非空的约束来创建一个字段的时候，
如果我们没有指定主键，那么第一个唯一且非空的字段将会被设置成主键
如果主动设置了主键，那么唯一且非空的字段不会成为主键
联合主键 primary key(字段1，字段2...)
就是给一个以上的字段设置 唯一非空约束foreign key：外键--> 只有另一个表中设置了unique的字段才能关联本表的外键 表类型必须是innodb存储引擎 on delete cascade --> 连级删除
on update cascade --> 连级跟新
2、示例
1. 非空：NOT NULL
mysql> create table t3 (id int not null);
mysql> desc t3;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | NO | | NULL | |+-------+---------+------+-----+---------+-------+# 插入非空，显示错误
mysql> insert into t3 values(null);
ERROR 1048 (23000): Column 'id' cannot be null# 插入123，正常插入
mysql> insert into t3 values(123);
Query OK, 1 row affected (0.08 sec)DEFAULT：在设置非空之后，如果这一列经常有重复的内容输入，为了方便插入，可以设置默认值
创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
mysql> create table t4 (id int not null,sex enum('male','female') not null default 'male');
mysql> describe t4;+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || sex | enum('male','female') | NO | | male | |+-------+-----------------------+------+-----+---------+-------+# 只向id字段添加值，会发现sex字段会使用默认值填充
mysql> insert into t4 (id) values (1);
mysql> select * from t4;+----+------+| id | sex |+----+------+| 1 | male |+----+------+# id字段不能为空，所以不能单独向sex字段填充值
mysql> insert into t4 (sex) values('female');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
# 向id，sex中分别填充数据，sex的填充数据会覆盖默认值
mysql> insert into t4 (id,sex) values(2,'female');
mysql> select * from t4;
+----+--------+
| id | sex |
+----+--------+
| 1 | male |
| 2 | female |
+----+--------+
2. UNIQUE：唯一
方法一：在定义字段的时候约束
mysql> create table t1(id int unique,name varchar(20));
mysql> desc t1;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | UNI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+方法二：最后使用unique约束字段
mysql> create table t2(id int,name varchar(20),unique(id));
mysql> desc t2;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | UNI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+# auto_increment 只有唯一且数字类型才能设置自增
mysql> create table t3(id int auto_increment,name varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql> create table t3(id int unique auto_increment,name varchar(20));
Query OK, 0 rows affected (0.39 sec)
mysql> insert into t3(name) values('a'),('b');
mysql> select * from t3;+----+------+| id | name |+----+------+| 1 | a || 2 | b |+----+------+# 联合唯一：就是给一个以上的字段设置 唯一非空约束，即单独的字段可以不唯一，但是联合在一起的组合必须是唯一的
mysql> create table t4(id int unique auto_increment,
ip varchar(15) not null,
port int not null,unique(ip,port)); # 联合唯一
mysql> insert into t4 (ip,port) values('192.168.0.1',8000);
Query OK, 1 row affected (0.07 sec)
mysql> insert into t4 (ip,port) values('192.168.0.1',8001); # ip相同，端口不同，可以插入
Query OK, 1 row affected (0.09 sec)
mysql> insert into t4 (ip,port) values('192.168.0.2',8001); # ip不同，端口相同，可以插入
Query OK, 1 row affected (0.08 sec)
mysql> insert into t4 (ip,port) values('192.168.0.2',8001); # ip相同，端口相同，不可以插入
ERROR 1062 (23000): Duplicate entry '192.168.0.2-8001' for key 'ip'mysql> select * from t4;+----+-------------+------+| id | ip | port |+----+-------------+------+| 1 | 192.168.0.1 | 8000 || 2 | 192.168.0.1 | 8001 || 3 | 192.168.0.2 | 8001 |+----+-------------+------+
3. PRIMARY KEY：主键--非空且唯一(主键也可以设置AUTO_INCREMENT自增的，因为主键也是唯一的)
mysql> create table t5 (id int not null unique); # 若没有主键，那么把id设置为非空且唯一后，自动成为主键
mysql> desc t5; # 可以看到id Key的属性被设置为PRI(primary主键)+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | |+-------+---------+------+-----+---------+-------+# 也可以自己直接设置主键
mysql> create table t6 (num int primary key);
mysql> desc t6;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| num | int(11) | NO | PRI | NULL | |+-------+---------+------+-----+---------+-------+# 注意：若指定主键之后其他的非空 + 唯一约束都不会再成为主键
mysql> create table t7 (id1 int unique not null,id2 int primary key);
mysql> desc t7;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1 | int(11) | NO | UNI | NULL | || id2 | int(11) | NO | PRI | NULL | |+-------+---------+------+-----+---------+-------+# 联合主键--把一个以上的字段设置成主键，即单独的字段可以不唯一，但是联合在一起的组合是主键，必须是唯一且非空的mysql> create table t8(ip varchar(15),port char(5),primary key(ip,port)); # 设置成联合主键
mysql> desc t8; # 可以看到ip和port都显示是主键PRI+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ip | varchar(15) | NO | PRI | | || port | char(5) | NO | PRI | | |+-------+-------------+------+-----+---------+-------+mysql> insert into t8 values('192.168.0.1','9000');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t8 values('192.168.0.1','9001'); # ip相同，端口不同，可以插入
Query OK, 1 row affected (0.15 sec)
mysql> insert into t8 values('192.168.0.2','9001'); # ip不同，端口相同，可以插入
Query OK, 1 row affected (0.10 sec)
mysql> insert into t8 values('192.168.0.2','9001'); # ip相同，端口相同，不可以插入
ERROR 1062 (23000): Duplicate entry '192.168.0.2-9001' for key 'PRIMARY'mysql> select * from t8;+-------------+------+| ip | port |+-------------+------+| 192.168.0.1 | 9000 || 192.168.0.1 | 9001 || 192.168.0.2 | 9001 |+-------------+------+
4. FOREIGN KEY：外键--只有另一个表中设置了unique的字段才能关联本表的外键
mysql> create table t9 (id int unique,course varchar(20)); # 先设置表t9的id是唯一的
mysql> create table t10 (id int,age int,t9_id int,foreign key(t9_id) references t9(id)); # 在表t10指定t9_id作为外键关联到表t9的id字段
mysql> desc t10;
+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || age | int(11) | YES | | NULL | || t9_id | int(11) | YES | MUL | NULL | |+-------+---------+------+-----+---------+-------+# 注意：如果一个表中的字段是另外一个表的外键，即这个表的某个字段对外表提供服务，那么默认不能直接删除外表中正在使用的数据
# 比如上面的例子，t10表的外键t9_id关联着t9表的id，那么t9不能直接删除自己的id字段，因为t9的id字段t10在使用着，
# 如果要删除，那么要先删除t10的外键后才能删除t9的id，这样就很麻烦，那么就需要使用：
# on delete cascade # 连级删除
# on update cascade # 连级更新
# 表示当t9删除自己id字段的时候，t10会跟着删除相应的外键
例如：
mysql> create table course (cid int primary key auto_increment,cname varchar(20) not null); # 创建course表
# 创建student表，外键是course_id关联course表的cid，并设置连级删除，连级跟新
mysql> create table student (sid int primary key auto_increment, -> sname varchar(20) not null, -> course_id int, -> foreign key(course_id) references course(cid) on delete cascade on update cascade);
# 学生表
mysql> select * from student;+-----+------------+-----------+| sid | sname | course_id |+-----+------------+-----------+| 1 | zhangsange | 1 || 2 | lisihao | 2 |+-----+------------+-----------+# 课程表
mysql> select * from course;+-----+--------+| cid | cname |+-----+--------+| 1 | python || 2 | linux |+-----+--------+# 删除课程表的python课，学生表也跟着删除了
mysql> delete from course where cid = 1;
mysql> select * from student;+-----+---------+-----------+| sid | sname | course_id |+-----+---------+-----------+| 2 | lisihao | 2 |+-----+---------+-----------+# 跟新课程表的linux课id，学生表也跟着跟新了
mysql> update course set cid = 1 where cid = 2;
mysql> select * from student;+-----+---------+-----------+| sid | sname | course_id |+-----+---------+-----------+| 2 | lisihao | 1 |+-----+---------+-----------+
十一、pymsql模块
1、介绍
1、说明：
想在python代码中连接上mysql数据库，就需要使用pymysql模块，
pymysql是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，在Python2中则使用mysqldb。
Django中也可以使用PyMySQL连接MySQL数据库。
2、 安装
在cmd窗口执行命令行：pip install pymysql # 注意：如果电脑上安装了两个版本的python解释器，则应该明确pip的版本，pip2还是pip3。
补充
pip -V --> 查看当前pip的版本
pip list --> 查看当前python解释器环境中安装的第三方包和版本(安装完成后可用此命令查看是否安装成功)3、注意事项
应该确定你有一个MySQL数据库，并且已经启动
应该确定你有可以连接该数据库的用户名和密码
应该确定你有一个有权限操作的database
2、使用步骤
1. 导入pymysql模块 import pymysql
2. 连接database
conn = pymysql.connect(
host='127.0.0.1', # 数据库的IP地址
port=3306, # 数据库的端口
user='root', # 用户名
password='123abc' # 密码
database='test', # 具体的一个数据库(文件夹)
charset='urf8' # 编码方式，注意：charset='utf8'，不要写成'utf-8' )
3. 获取光标对象
cursor = conn.cursor()
4. 执行SQL语句
cursor.execute('select * from userinfo;')
5. 关闭 1. 关闭光标
cursor.close()
2. 关闭连接
conn.close()
6.例子import pymysql# 获取用户输入name = input('请输入用户名：')
pwd = input('请输入密码：')# 连接数据库，得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306, # mysql默认端口3306
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标cursor = conn.cursor()# sql语句sql = "select * from userinfo where username='%s' and password='%s';" % (name, pwd)print(sql)# 执行sql语句ret = cursor.execute(sql)print(ret) # ret是受影响的记录数量，若不为空，则代表查到相应的记录# 关闭cursor.close()
conn.close()# 结果if ret: print('登陆成功')else: print('登录失败')
结果：
请输入用户名：ming
请输入密码：112233select * from userinfo where username='ming' and password='112233'1登陆成功
3、SQL注入问题
1. 什么是SQL注入？
用户输入的内容有恶意的SQL语句，后端拿到用户输入的内容不做检测直接做字符串拼接，得到一个和预期不一致的SQL语句
例如：上面例子的代码完全不变，但是我这样输入：
请输入用户名：ming' -- 请输入密码：12345select * from userinfo where username='ming' -- ' and password='12345';1登陆成功
结果是登录成功了，为什么？(密码是112233)
这是因为我在输入用户名的时候，输入的是ming' -- 在sql语句中，--代表注释的意思，也就是说
select * from userinfo where username='ming' -- ' and password='12345'; 这句sql语句相当于select * from userinfo where username='ming'那肯定能登录成功啊，因为我数据库中就是有这个用户，相当于找用户名，有这个用户就成功，并没有去匹配密码，
这就是恶意注入的问题。2. 如何解决SQL注入？
对用户输入的内容做检测，有引号怎么处理，注释怎么处理，过程很麻烦，但是，
pymysql内置了这种检测，我们可以直接使用，所以我们不应该自己拼接sql语句，而是让pymysql帮我们拼接sql语句。
cursor.execute(sql, [name, pwd]) # 让pymysql模块帮我们拼接sql语句，执行SQL语句 例子：import pymysql# 获取用户输入name = input('请输入用户名：')
pwd = input('请输入密码：')# 连接数据库，得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306, # mysql默认端口3306
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标cursor = conn.cursor()# sql语句:按照pymysql模块的写法定义好占位符,pymysql只有%s一种占位符sql = "select * from userinfo where username=%s and password=%s;"print(sql)# 执行sql语句ret = cursor.execute(sql,[name,pwd]) # 让pymysql模块帮我们拼接sql语句，执行SQL语句print(ret) # ret是受影响的记录数量，若不为空，则代表查到相应的记录# 关闭cursor.close()
conn.close()# 结果if ret: print('登陆成功')else: print('登录失败')
结果1：
请输入用户名：ming' -- 请输入密码：12345select * from userinfo where username=%s and password=%s;
0
登录失败
结果2：
请输入用户名：ming
请输入密码：112233select * from userinfo where username=%s and password=%s;1登陆成功
4、pymysql的增删改查
涉及到修改数据库内容的时候，一定要提交：conn.commit()1. 增、删、改import pymysql# 连接数据库,得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标cursor = conn.cursor()# 得到SQL语句sql1 = "insert into userinfo(username, password) values (%s,%s);" # 增sql2 = "delete from userinfo where username=%s;" # 删sql3 = "update userinfo set password=%s where username=%s;" # 改# 使用光标对象执行SQL语句cursor.execute(sql1, ['sb', '456']) # 让pymysql模块帮我们拼接sql语句，执行SQL语句# 涉及到修改数据库内容,一定要 commit 提交conn.commit()
cursor.execute(sql2, ['ming'])
conn.commit()
cursor.execute(sql3, ['123', 'sb'])
conn.commit()# 关闭cursor.close()
conn.close()2. 查 1. 返回的数据类型 1. 默认返回的元组，且每个元素也是用元组 2. 可以设置为返回的是列表，列表中的每个元素是字典
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 2. 常用的方法 1. fetchall() 返回所有查询到的记录 2. fetchone() 返回一条查询到的记录 3. fetchmany(size) 返回size条查询到的记录 4.cursor.scroll(1, mode="absolute") 光标按绝对位置移动 5.cursor.scroll(1, mode="relative") 光标按照相对位置(当前位置)移动
例子1：import pymysql# 连接数据库,得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标cursor = conn.cursor()# 得到SQL语句sql = "select * from userinfo;"# 使用光标对象执行SQL语句cursor.execute(sql) # 没有参数则不用拼接，直接执行ret = cursor.fetchall()print(ret)# 关闭cursor.close()
conn.close()
结果1：
(('hong', 123), ('sb', 123), ('ming', 456), ('dong', 789))
例子2：import pymysql# 连接数据库,得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标，设置查询结果为列表且元素为字典cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 得到SQL语句sql = "select * from userinfo;"# 使用光标对象执行SQL语句cursor.execute(sql) # 没有参数则不用拼接，直接执行# 查询所有# ret = cursor.fetchall()# print(ret)# 查询单条记录# ret = cursor.fetchone()# print(ret)# ret = cursor.fetchone()# print(ret)# 查询指定数量的数据ret = cursor.fetchmany(2)print(ret) # [{'username': 'hong', 'password': 123}, {'username': 'sb', 'password': 123}]print(cursor.fetchone()) # {'username': 'ming', 'password': 456}print(cursor.fetchone()) # {'username': 'dong', 'password': 789}# 移动光标cursor.scroll(-1, mode='relative') # 相对位置,基于光标当前位置移动print(cursor.fetchone()) # {'username': 'dong', 'password': 789}cursor.scroll(0, mode='absolute') # 绝对位置，你让光标移动到哪里就到哪里print(cursor.fetchone()) # {'username': 'hong', 'password': 123}# 关闭cursor.close()
conn.close()3.批量增(插入)import pymysql# 连接数据库,得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123abc',
database='test',
charset='utf8')# 获取光标cursor = conn.cursor()# 得到SQL语句sql = "insert into userinfo(username, password) values (%s,%s);" # 增# 数据data = [("a", 18), ("b", 19), ("c", 20)]# 使用光标对象执行SQL语句cursor.executemany(sql, data) # 批量增使用executemany# 涉及到修改数据库内容,一定要提交conn.commit()# 关闭cursor.close()
conn.close()
5、回滚操作
conn.rollback()：在执行增删改操作时，如果不想提交前面的操作，可以使用 rollback() 回滚取消操作。
cursor.lastrowid：获取插入数据的ID(关联操作时会用到)# 导入pymysql模块import pymysql# 连接数据库,得到一个连接conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123abc',
database='test',
charset='utf8')# 得到一个可以执行SQL语句的光标对象cursor = conn.cursor()# sql语句sql = "insert into userinfo(username, password) values (%s,%s);" try: # 执行SQL语句
cursor.execute(sql, ['xiazi', 58])
# 提交 conn.commit() # 提交之后，获取刚插入的数据的ID
last_id = cursor.lastrowid
except Exception as e: # 有异常，回滚取消操作 conn.rollback()
cursor.close()
conn.close()
十二、使用with语句优化pymysql的操作
1、with语句的好处
with语句的好处在于，它可以自动帮我们释放上下文，就比如文件句柄的操作，
如果你不使用with语句操作，你要先open一个文件句柄，使用完毕后要close这个文件句柄，
而使用with语句后，退出with代码块的时候就会自动帮你释放掉这个文件句柄。
场景使用：
网络连接、数据库连接、文件句柄、锁
2、如何让对象支持with语句
方法：
在创建类的时候，在内部实现__enter__方法，with语句一开始就会执行这个方法，
再实现__exit__方法，退出with代码块的时候会自动执行这个方法。
例子：class A: def __enter__(self): print('with语句开始') return self # 返回self就是把这个对象赋值给as后面的变量
def __exit__(self, exc_type, exc_val, exc_tb): print('with语句结束')
with A() as f: print('IG牛批') print(f)print('IG真的牛批')
结果：
with语句开始
IG牛批<__main__.A object at 0x0000027B4D1596D8>with语句结束
IG真的牛批
3、使用with语句连接pymysql数据库基本操作
import pymysqlclass SQLManager(object): # 初始化实例的时候调用connect方法连接数据库
def __init__(self):
self.conn = None
self.cursor = None
self.connect()
# 连接数据库
def connect(self):
self.conn = pymysql.connect(
host='127.0.0.1',
port=3306,
database='mydb',
user='root',
password='123abc',
charset='utf8'
)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) # 关闭数据库
def close(self):
self.cursor.close()
self.conn.close()
# 进入with语句自动执行
def __enter__(self): return self
# 退出with语句自动执行
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
4、还可以在上面的基础上实现pymysql的一些操作
class SQLManager(object): # 初始化实例方法
def __init__(self):
self.conn = None
self.cursor = None
self.connect() # 连接数据库
def connect(self):
self.conn = pymysql.connect(
host='127.0.0.1',
port=3306,
database='mydb',
user='root',
password='123abc',
charset='utf8'
)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) # 查询多条数据sql是sql语句，args是sql语句的参数
def get_list(self, sql, args=None):
self.cursor.execute(sql, args)
result = self.cursor.fetchall() return result # 查询单条数据
def get_one(self, sql, args=None):
self.cursor.execute(sql, args)
result = self.cursor.fetchone() return result # 执行单条SQL语句
def moddify(self, sql, args=None):
self.cursor.execute(sql, args)
self.conn.commit() # 执行多条SQL语句
def multi_modify(self, sql, args=None):
self.cursor.executemany(sql, args)
self.conn.commit() # 创建单条记录的语句
def create(self, sql, args=None):
self.cursor.execute(sql, args)
self.conn.commit()
last_id = self.cursor.lastrowid return last_id # 关闭数据库cursor和连接
def close(self):
self.cursor.close()
self.conn.close() # 进入with语句自动执行
def __enter__(self): return self
# 退出with语句块自动执行
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
十三、视图
1、什么是视图
视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。
通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
基表：用来创建视图的表叫做基表base table
视图其实就是对若干张基本表的引用，是一张虚表，是查询语句执行的结果，不存储具体的数据（基表数据发生了改变，视图也会跟着改变)，
视图就是一条SELECT语句执行后返回的结果集，可以跟基表一样，进行增删改查操作(ps:增删改操作有条件限制)
2、视图的优点
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
3、视图的创建
表的结构和数据
语法： 视图名称 teacher_view tid teacher tname cname course teacher_id ( tid cname 物理 美术 rows (.视图一般都是用于查询，尽量不要修改(插入、删除等)视图中的数据，虽然有时候可以修改成功，但是尽量不要这样做，因为这个视图可能是多个表联合起来生成的一个结果，如果你修改它，可能会造成很多表里面的数据都跟着被修改了
4、修改视图记录
注意：
原则上我们一般不应该去修改视图，因为修改视图，视图对应的基表也会被修改。
而且在涉及多个表的情况下是根本无法修改视图中的记录的
# 修改视图，原始表也跟着改
mysql> select * from course;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 体育 | 3 || 4 | 美术 | 2 |+-----+--------+------------+4 rows in set (0.00 sec)
mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)
mysql> select * from course_view;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 体育 | 3 || 4 | 美术 | 2 |+-----+--------+------------+4 rows in set (0.00 sec)
mysql> update course_view set cname='xxx'; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)
mysql> select * from course; #发现原始表的记录也跟着修改了+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 1 | xxx | 1 || 2 | xxx | 2 || 3 | xxx | 3 || 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+5 rows in set (0.00 sec)
5、修改视图结构
语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from teacher_view;+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+2 rows in set (0.00 sec)
6、删除视图
语法：DROP VIEW 视图名称
DROP VIEW teacher_view
十四、触发器
使用触发器(trigger)可以定制用户对某一张表的数据进行【增、删、改】操作时前后的行为，注意：没有查询，在进行增删改操作的时候，触发的某个操作，称为触发器。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
1、触发器语法
before ;
2、触发器示例
准备表
1.场景
现在的需求是：不管正确或者错误的cmd，都需要往cmd表里面插入，然后，如果是错误的记录，还需要往errlog表里面插入一条记录
若果没有触发器，我们完全可以通过应用程序来做，根据cmd表里面的success这个字段是哪个值（yes成功，no表示失败），在给cmd插入记录的时候，判断一下这个值是yes或者no，来判断一下成功或者失败，如果失败了，直接给errlog来插入一条记录
但是有了触发器，你只需要往cmd表里面插入数据就行了，没必要你自己来判断了，可以使用触发器来实现，可以判断你插入的这条记录的success这个字段对应的值，然后自动来触发触发器，进行errlog表的数据插入
2.示例
# 创建触发器delimiter // # 或者写$$，其他符号也行，但是不要写mysql不能认识的，delimiter 是告诉mysql，遇到这句话的时候，就将sql语句的结束符分号改成delimiter后面的//CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW # 在你cmd表插入一条记录之后触发的。BEGIN # 每次给cmd插入一条记录的时候，都会被mysql封装成一个对象，叫做NEW，里面的字段都是这个NEW的属性 IF NEW.success = 'no' THEN # mysql里面是可以写这种判断的，等值判断只有一个等号，然后写then INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; # 必须加分号，并且注意，我们必须用delimiter来包裹，不然，mysql一看到分号，就认为你的sql结束了，所以会报错 END IF ; # 然后写end if，必须加分号 END// # 只有遇到//这个完成的sql才算结束delimiter ; # 然后将mysql的结束符改回为分号# 往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志INSERT INTO cmd ( USER,
priv,
cmd,
sub_time,
success
)VALUES
('chao','0755','ls -l /etc',NOW(),'yes'),
('chao','0755','cat /etc/passwd',NOW(),'no'),
('chao','0755','useradd xxx',NOW(),'no'),
('chao','0755','ps aux',NOW(),'yes');# 查询错误日志，发现有两条mysql> select * from errlog;+----+-----------------+---------------------+| id | err_cmd | err_time |+----+-----------------+---------------------+| 1 | cat /etc/passwd | 2017-09-14 22:18:48 || 2 | useradd xxx | 2017-09-14 22:18:48 |+----+-----------------+---------------------+2 rows in set (0.00 sec)
3、使用触发器
触发器无法由用户直接调用，而由对表的【增/删/改】操作被动引发的。
4、删除触发器
drop trigger tri_after_insert_cmd;
十五、存储过程
1、什么是存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放在MySQL中，通过调用它的名字可以执行其内部的一堆sql，例如视图、触发器等等的内容，我们可以把它们的sql语言写好，放到存储过程中并给它们命名，下次想使用视图、触发器就可以直接用这个命名调用，而不需要再写sql语言了。
总之，类比成函数的话，就是，你可以在函数里面定义视图、触发器、事务等等，下次需要使用的时候，直接调用这个函数即可。
存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及进行流程控制，存储过程可以接收参数，可以接收输入类型的参数，也可以接收输出类型的参数，并且可以存在多个返回值。
存储过程的效率要比我们简单的sql语句的执行的效率要高，原因就在于，比如我们要执行两条语句，那么mysql引擎就会逐一的对这两条语句进行分析，编译和执行，而我们使用存储过程之后，只有在第一次才会使用语法分析和编译，以后客户端再调用，只调用编译后的结果就可以了，省略了分析和编译的环节，所以效率要比之前要高。
2、存储过程的优点
第一，增强了sql语句的功能和灵活性。
因为在存储过程中可以写控制语句，那么就有很强的灵活性，也可以完成复杂的判断和运算。
第二，实现了较快的执行速度。
如果某一个操作包含了大量的操作语句，那么这些语句都将被mysql引擎进行语法分析和编译，所以执行过程的效率相对较低，而存储过程是预编译的，当客户端第一次调用这个存储过程的时候，mysql引擎将对他进行语法分析和编译操作，然后把这个编译的结果存储到内存当中，所以说第一次是和之前的效率是相同的，但是以后客户端再次调用这个存储过程的时候，便直接在内存当中来执行，所以说相比效率比较高，速度比较快。
第三，减少网络流量
如果我们通过客户端单独发送sql语句让服务器来执行的话，那么通过http协议提交的数据量相对较大，而使用存储过程，我们只需要传递存储过程的名字，后面加上我们要操作的那个值就行了，所以说，他提交给服务器的数据量相对较少，那么也就减少了网络流量。
3、语法
CREATE PROCEDURE过程名([[IN OUT INOUT] 参数名 数据类型[,[IN OUT INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER //
CREATE PROCEDURE myproc(OUT s int) BEGIN
SELECT COUNT(*) INTO s FROM students; END //DELIMITER ;
1. 分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；最后再“DELIMITER ;”把分隔符还原。
2. 参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回
3. 过程体
过程体的开始与结束使用BEGIN与END进行标识。
4、创建无参的存储过程
delimiter
blog(name,sub_time) 自定义的名字()
BEGIN
名字(参数).callproc((.fetchall())
5、创建有参的存储过程
对于存储过程，可以接收参数，其参数有三类：IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回
额外的小知识：
查看存储过程的一些信息：show create procedure p3;
查看视图啊、触发器啊都这么看，还可以用\G，show create procedure p3\G;
\G的意思是你直接查看表结构可能横向上显示不完，\G是让表给你竖向显示，一row是一行的字段
1. in:调用时传入参数
delimiter //create procedure p2( in n1 int, # n1,n2参数是需要传入的，也就是接收外部数据的，并且这个数据必须是int类型 in n2 int)BEGIN
select * from blog where id > n1; # 直接应用变量END //delimiter ;
# 在mysql中调用
call p2(3,2)
# 在python中基于pymysql调用cursor.callproc('p2',(3,2))print(cursor.fetchall())
2. out:在存储过程中可修改此参数，用做返回值
delimiter //create procedure p3( in n1 int,
out res int)BEGIN
select * from blog where id > n1; set res = 1;END //delimiter ;
这里设置一个res=1，如果上面的所有sql语句全部正常执行了，那么这一句肯定也就执行了，那么此时res=1，
如果我最开始传入的时候，给res设置的值是0，最后我接收到的返回值如果是0，那么说明你中间肯定有一些sql语句执行失败了，
注意，out的那个参数，可以用set来设置，set设置之后表示这个res可以作为返回值，直接set之后的值，就是这个存储过程的返回值。
# 在mysql中调用set @res=0; # 这是MySQL中定义变量名的固定写法(set @变量名=值)，可以自己规定好，0代表假（执行失败），1代表真（执行成功），如果这个被改为1了，说明存储过程中的sql语句执行成功了
call p3(3,@res); # 注意：不要这样写：call p3（3，1），这样out的参数值你写死了，没法确定后面这个1是不是成功了select @res; # 看一下这个结果，就知道这些sql语句是不是执行成功了
# 在python中基于pymysql调用，在python中只需要知道存储过程的名字就行了cursor.callproc('p3',(3,0)) # 为什么这里这个out参数可以写常数0，因为你用pymysql，当你直接传入out参数为0时，pymysql会自动帮你进行操作：set @res=0print(cursor.fetchall()) # 查询select的查询结果cursor.execute('select @_p3_0,@_p3_1;') #@_p3_0代表第一个参数，@_p3_1代表第二个参数，即返回值print(cursor.fetchall())cursor.close()
conn.close()
3. inout:调用时传入，在存储过程中可被修改，并且可返回
delimiter //create procedure p4(
inout n1 int)BEGIN
select * from blog where id > n1; set n1 = 1;END //delimiter ;
# 在mysql中调用set @x=3;
call p4(@x);select @x;
# 在python中基于pymysql调用cursor.callproc('p4',(3,))print(cursor.fetchall()) # 查询select的查询结果cursor.execute('select @_p4_0;')
print(cursor.fetchall())
6、在python中基于pymysql来执行存储过程
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123abc', 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)
7、删除存储过程
drop procedure proc_name;
十六、事务
事务用于将某些操作的多个SQL作为原子性操作，也就是这些sql语句要么同时成功，要么都不成功，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
数据准备
存储过程结合事务
delimiter //
create procedure p4(
out status int
) BEGIN
1. 声明如果出现异常则执行{ set status = 1; rollback;
}
开始事务 -- 由A账户减去100
-- B账户加90
-- C账户加10
commit;
结束
set status = 2;
END //
delimiter ;
#实现
delimiter //create PROCEDURE p5(
OUT p_return_code tinyint)BEGIN
DECLARE exit handler for sqlexception # 声明如果一旦出现异常则执行下面的这个begin和end里面的操作 BEGIN
-- ERROR # --是注释的意思，就告诉你后面是对错误的处理
set p_return_code = 1; # 将out返回值改为1了，这是你自己规定的，1表示出错了 rollback; # 回滚事务 END;
DECLARE exit handler for sqlwarning # 声明了出现警告信息之后你的操作行为 BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION; # 其实这个存储过程里面就是执行这个事务，并且一直检测着这个事务，一旦出错或者出现警告，就rollback DELETE from tb1; # 事务里面的任何一条sql执行失败或者执行出现警告，都会执行上面我们声明的那些对应的操作，如果没有任何的异常，就会自动执行下面的commit，并执行后面成功的sql insert into blog(name,sub_time) values('yyy',now()); #拿我的代码进行测试的时候，别忘了改成你自己库里的表，还有表里面对应的字段名要有的，自己测试的时候，可以自己写一个错误的sql来试试看 COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功END //delimiter ;
# 在mysql中调用存储过程set @res=123;
call p5(@res);select @res;
# 在python中基于pymysql调用存储过程cursor.callproc('p5',(123,)) #注意后面这个参数是个元祖，别忘了逗号，按照我们上面规定的，上面有三个值0，1，2：0成功、1失败、2警告也是失败。所以我们传给这个out参数的值只要不是这三个值就行了，这里给的是100print(cursor.fetchall()) #查询select的查询结果cursor.execute('select @_p5_0;')print(cursor.fetchall())
# 执行成功以后，查看一下结果就能看到执行后的值了
# 原子操作(balance是你的账户余额)
start transaction;update user set balance=900 where name='buyer'; # 买支付100元update user set balance=1010 where name='jian'; # 中介拿走10元update user set balance=1090 where name='sale'; # 卖家拿到90元commit; # 只要不进行commit操作，就没有保存下来，没有刷到硬盘上
# 出现异常，回滚到初始状态
start transaction;update user set balance=900 where name='buyer'; # 买支付100元update user set balance=1010 where name='jian'; # 中介拿走10元
uppdate user set balance=1090 where name='sale'; # 卖家拿到90元,出现异常没有拿到rollback; # 如果上面三个sql语句出现了异常，就直接rollback，数据就直接回到原来的状态了。但是执行了commit之后，rollback这个操作就没法回滚了
# 我们要做的是检测这几个sql语句是否异常，没有异常直接commit，有异常就rollback，但是现在单纯的只是开启了事务，但是还没有检测异常commit;
# 通过存储过程来捕获异常
delimiter //create PROCEDURE p5()BEGIN DECLARE exit handler for sqlexceptionBEGIN rollback;
END;
START TRANSACTION;
update user set balance=900 where name='buyer'; # 买支付100元update user set balance=1010 where name='jian'; # 中介拿走10元update user2 set balance=1090 where name='sale'; # 卖家拿到90元(让这里出现异常)COMMIT;END //delimiter ;
mysql> select * from user;+----+------+---------+| id | name | balance |+----+------+---------+| 1 | buyer | 1000 || 2 | jian | 1000 || 3 | sale | 1000 |+----+------+---------+3 rows in set (0.00 sec)
十七、函数
MySQL中提供了许多内置函数，但是注意，这些函数只能在sql语句中使用，不能单独调用。
1、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值
RAND()
返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
2、聚合函数
AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
3、字符串函数
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
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)
变大写REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
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'
4、时间和日期函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
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)
重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00'
5、加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
6、控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真，则返回resultN，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f)
如果test是真，返回t；否则返回f
IFNULL(arg1,arg2)
如果arg1不是空，返回arg1，否则返回arg2NULLIF(arg1,arg2)
如果arg1=arg2返回NULL；否则返回arg1
小练习
7、需掌握的函数(date_format)
#1 基本使用
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009'mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00'mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277'mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00'#2 准备表和记录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');
#3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组，统计一下每年每月的博客数量，怎么写呢，按照sub_time分组，但是我们的sub_time是年月日加时间，我想看每年每月，直接按照sub_time来分组是不行的，每篇博客的发表时间基本都是不同的，所以我们需要通过这个date_format来搞了SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
#结果+-------------------------------+----------+| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |+-------------------------------+----------+| 2015-03 | 2 || 2016-07 | 4 || 2017-03 | 3 |+-------------------------------+----------+3 rows in set (0.00 sec)
8、自定义函数
注意！！！ 函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能 ，若要想在begin...end...中写sql，请用存储过程
# 1、自定义函数1
delimiter //create function f1(
i1 int,
i2 int)returns intBEGIN
declare num int; set num = i1 + i2; return(num);END //delimiter ;
# 2、自定义函数2
delimiter //create function f5(
i int)returns intbegin
declare res int default 0; if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300; else
set res=400; end if; return res;end //delimiter ;
# 3、删除函数drop function func_name;
# 4、执行函数
# 获取返回值select UPPER('chao') into @res;SELECT @res;
# 在查询中使用select f1(11,nid) ,name from tb2;
十八、关于查看存储过程，函数，视图，触发器的语法
查询数据库中的存储过程和函数 select name from mysql.proc where db = 'xx' and type = 'PROCEDURE' //查看xx库里面的存储过程 select name from mysql.proc where db = 'xx' and type = 'FUNCTION' //函数
show procedure status; //存储过程
show function status; //函数
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
查看视图
SELECT * from information_schema.VIEWS //视图
SELECT * from information_schema.TABLES //表
查看触发器
SHOW TRIGGERS [FROM db_name] [LIKE expr] SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G；其中triggers T就是triggers as T的意思，起别名
十九、流程控制
1、条件语句
# if条件语句
delimiter
i i
2、循环语句
# 1、while循环
delimiter //CREATE PROCEDURE proc_while ()BEGIN
DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT
num ; SET num = num + 1 ; END WHILE ;END //delimiter ;
#2、repeat循环
delimiter //CREATE PROCEDURE proc_repeat ()BEGIN
DECLARE i INT ; SET i = 0 ;
repeat select i; set i = i + 1;
until i >= 5
end repeat;END //delimiter ;
# 3、loopBEGIN
declare i int default 0;
loop_label: loop
set i=i+1; if i<8 then
iterate loop_label; end if; if i>=10 then
leave loop_label; end if; select i; end loop loop_label;END
二十、索引介绍
1、什么是索引
索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据
2、索引的原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。 那么你想，书的目录
占不占页数，这个页是不是
也要存到硬盘里面，也占用硬盘空间。你再想，你在
没有数据的情况下先建索引或者目录的速度快，还是已经存在好多的数据了，然后再去建索引/目录速度快，那肯定是没有数据的时候快，因为如果已经有了很多数据了，你再去根据这些数据建索引，要将数据全部遍历一遍，然后根据数据建立索引。你再想，索引建立好之后再添加数据速度快，还是没有索引的时候添加数据速度快，
因为索引是用来加速查询的，那对你写入数据肯定会有一些影响的，插入数据的速度肯定是慢一些的，因为你但凡加入一些新的数据，都需要把索引或者说书的目录重新做一个，所以索引虽然会加快查询，但是会降低写入的效率。
注意：
1、在表中有大量数据的前提下，再来创建索引速度会很慢
2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低
本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
3、索引分类
1. 分类
聚集索引：主键primary key
辅助索引：也称为非聚集索引，unique key、index key
2. 作用
聚集索引：
它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。
范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
辅助索引：如果查找的字段不是主键，使用辅助索引可以提高查询速度
3. 注意
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录，这种查找的效率也是非常高。
二十一、MySQL中的索引
1、功能
1. 索引的功能就是加速查找
2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
2、MySQL常用的索引
普通索引INDEX：加速查找
唯一索引： -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复） -唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引： -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
3、各种索引的应用场景
举个例子来说，比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段：
会员编号 INT会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT那么这个 会员编号，作为主键，使用 PRIMARY会员姓名 如果要建索引的话，那么就是普通的 INDEX会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）# 除此之外还有全文索引，即FULLTEXT会员备注信息，如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。# 其他的如空间索引SPATIAL，了解即可，几乎不用
4、索引的两大类型hash与btree
# 我们可以在创建上述索引的时候，为其指定索引类型，分两类hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）# 不同的存储引擎支持的索引类型也不一样InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
5、创建、查看、删除索引的语法
1.创建的几种方法
# 方法一：创建表时创建索引
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC])
);
# 方法二：CREATE 在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
# 方法三：ALTER TABLE 在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
# 查看索引
SHOW INDEX FROM 表名字;
# 查看查询语句使用索引的情况//explain 查询语句
explain SELECT * FROM table_name WHERE column_1='123';
# 删除索引DROP INDEX 索引名 ON 表名字;
2.索引分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值;ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值ALTER TABLE 'table_name' ADD UNIQUE index_name('col');3、普通索引：用表中的普通列构建的索引，没有任何限制ALTER TABLE 'table_name' ADD INDEX index_name('col');4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3));
表示使用col1的前4个字符和col2的前3个字符作为索引
6、创建、查看、删除索引示例
# 方式一：创建表时创建索引create table t1(
id int,
name char,
age int,
sex enum('male','female'), unique uni_id(id), # uni_id是索引名，括号里面的id是字段，意思是给id字段创建一个unique key的索引 index ix_name(name) # index没有关键字key，给name字段创建一个index索引，索引名是ix_name
);
# 方式二：create 在已经存在的表上创建索引create index ix_age on t1(age);
# 方式三： alter table 在已经存在的表上创建索引alter table t1 add index ix_sex(sex);
# 查看
mysql> show index from t1;
# 删除drop index uni_id on t1;
7、测试索引
1. 准备
# 1. 准备表create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);# 2. 创建存储过程，实现批量插入记录delimiter $$ # 声明存储过程的结束符号为$$create procedure auto_insert1()BEGIN
declare i int default 1; while(i<3000000)do insert into s1 values(i,'ming','male',concat('ming',i,'@qq.com')); set i=i+1; end while;END$$ # $$结束delimiter ; # 重新声明分号为结束符号# 3. 查看存储过程show create procedure auto_insert1\G
# 4. 调用存储过程call auto_insert1();
2.在没有索引的前提下测试查询速度
# 无索引：mysql根本就不知道到底是否存在id等于333333333的记录，也不知道存在几条id=333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)
3.在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
创建索引
ix_id s1(id);
或者
s1 (id);
在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
4.索引建立完毕后，以该字段为查询条件时，查询速度提升明显
s1 id
5.总结
. 一定是为搜索条件的字段创建索引，比如select s1 id idx
8、正确使用索引
1.索引未命中(所谓命中索引，就是应用上了索引)
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题1. 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、# 大于号、小于号如果你写where id >1 and id <1000000;你会发现，随着你范围的增大，速度会越来越慢，会成倍的体现出来。# 不等于 !=不等于是一个很大的范围，查询速度也是会很慢的# between ...and...范围大查询速度很慢
范围小查询速度很快# likelike后面如果没有那些特殊字符，通配符之类的，就跟 等于 是一个效果，精确匹配，查询速度很快。
like后面有通配符%如果%在最后面，例如 select count(*) from s1 where email like 'xxx%'查询速度依然很快。
如果%在最前面，例如 select count(*) from s1 where email like '%xxx'查询速度很慢。2. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录3. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式4. 索引列不能参与计算，保持列“干净”，比如select count(*) from s1 where id=3000; 查询速度很快，因为id字段有索引select count(*) from s1 where id*3=3000; 查询速度很慢，因为字段id参与了计算，无法拿到一个明确的值去索引树中查找，每次都得临时算一下
但如果是select count(*) from s1 where id = 3000/3;你会发现速度变得很快，因为等于号后面的数字，是在比较之前就计算出来了，不需要每次都计算一下，跟直接等于一个常数是一样的，所以很快。
结论是不要让你的索引字段参与到计算中。5. and/or#1、and与or的逻辑
条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
条件1 or 条件2:只要有一个条件成立则最终结果就成立#2、and的工作原理
条件：
a = 10 and b = 'xxx' and c > 3 and d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
索引的本质原理就是先不断的把查找范围缩小下来，然后再进行处理，对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序#3、or的工作原理
条件：
a = 10 or b = 'xxx' or c > 3 or d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
只要一个匹配成功就行，所以对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d6. 最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
例如：create index ix_all on s1(id,name,gender,email);select count(*) from s1 where name='ming' and gender='male' and id > 333 and email='xxx'建立索引时未将条件中关于范围比较的字段放到后面，因此查询速度很慢。create index ix_all on s1(name,gender,email,id);select count(*) from s1 where name='ming' and gender='male' and id > 333 and email='xxx'建立索引时将条件中关于范围比较的字段放到后面，因此查询速度很快。7. 其他情况- 使用函数 select * from tb1 where reverse(email) = 'ming';
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where email = 999;
# 排序条件为索引，则select字段必须也是索引字段，否则无法命中- order by
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢 select name from s1 order by email desc; # 速度很慢 select email from s1 order by email desc; # 速度很快
特别的：如果对主键排序，则还是速度很快： select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 命中索引
name -- 命中索引
email -- 未命中索引- count(1)或count(列)代替count(*)在mysql中没有差别了- create index xxxx on tb(title(19)) # text类型，必须制定长度
2.注意事项
- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复少）不适合建索引，例：性别不适合
9、联合索引
普通索引： create index 索引名 on 表名(列名) drop index 索引名 on 表名
唯一索引： create unique index 索引名 on 表名(列名) drop unique index 索引名 on 表名
联合索引： create unique index 索引名 on 表名(列名1,列名2...) drop unique index 索引名 on 表名
注意建立联合索引的一个原则：索引是有个最左匹配的原则的，所以建联合索引的时候，将区分度高的放在最左边，依次排下来，范围查询的条件尽可能的往后边放。
联合索引的好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了
10、覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性
对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，...,key1，key2，...）。例如
select age from s1 where id=123 and name = 'ming'; # id字段有索引，但是name字段没有索引,该sql命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。
最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了
mysql> desc s1;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || name | varchar(20) | YES | | NULL | || gender | char(6) | YES | | NULL | || email | varchar(50) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set (0.21 sec)
mysql> explain select name from s1 where id=1000; # 没有任何索引+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2688336 | 10.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> create index idx_id on s1(id); # 创建索引
Query OK, 0 rows affected (4.16 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> explain select name from s1 where id=1000; # 命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找name+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.08 sec)
mysql> explain select id from s1 where id=1000; # 在辅助索引中就找到了全部信息，Using index代表覆盖索引+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Using index |+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.03 sec)
二十二、查询优化神器-explain
1、基础
参考官网 参考博客 参考博客
执行计划：让mysql预估执行操作(一般正确) all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email
慢： select * from userinfo3 where name='ming'
explain select * from userinfo3 where name='ming'
type: ALL(全表扫描) select * from userinfo3 limit 1;
快： select * from userinfo3 where email='ming'
type: const(走索引)
2、慢查询优化的基本步骤
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）3.order by limit 形式的sql语句让排序的表优先查4.了解业务方使用场景5.加索引时参照建索引的几大原则6.观察结果，不符合预期继续从0分析
3、慢日志管理
慢日志 - 执行时间 > 10
- 未命中索引 - 日志文件路径
配置： - 内存
show variables like '%query%';
show variables like '%queries%'; set global 变量名 = 值 - 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务
MySQL日志管理========================================================错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志： 审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等========================================================一、bin-log1. 启用
# vim /etc/my.cnf[mysqld]log-bin[=dir\[filename]]
# service mysqld restart2. 暂停//仅当前会话SET SQL_LOG_BIN=0;SET SQL_LOG_BIN=1;3. 查看
查看全部：
# mysqlbinlog mysql.000002按时间：
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数：
# mysqlbinlog mysql.000002 --start-position=260# mysqlbinlog mysql.000002 --stop-position=260# mysqlbinlog mysql.000002 --start-position=260 --stop-position=9304. 截断bin-log（产生新的bin-log文件）
a. 重启mysql服务器
b. # mysql -uroot -p123 -e 'flush logs'5. 删除bin-log文件
# mysql -uroot -p123 -e 'reset master' 二、查询日志
启用通用查询日志
# vim /etc/my.cnf[mysqld]log[=dir\[filename]]
# service mysqld restart
三、慢查询日志
启用慢查询日志
# vim /etc/my.cnf[mysqld]log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1slow-query-log-file=slow.loglong_query_time=3查看慢查询日志
测试:BENCHMARK(count,expr)SELECT BENCHMARK(50000000,2*3);
二十三、Mysql数据库的备份
1.mysqldump是mysql用于转存储数据库的实用程序(后缀是dump或者sql都行)
注意：这个命令是在linux/windows的终端敲的，用于导出数据库(就是还没有进入mysql客户端)
导出一个数据库的结构以及数据
mysqldump -u root -p dbname > dbname.sql
导出多个数据库的结构以及数据
mysqldump -u root -p -B dbname1 dbname2 > dbname.sql
导出所有数据库
mysqldump -u root -p --all-databases > xxx.dump
2.mysql导入数据库
假设已经导出了一个数据库文件 db.sql
方法一： 1. 进入MySQL客户端 2. 创建数据库 create database db; 3. 退出mysql客户端，在终端敲
mysql -u root -p < /opt/db.sql
方法二：
1. 进入MySQL客户端
2. 创建数据库
create database db;
3. use db;
4. source /opt/db.sql
发起投票
技术讨论
加好友
发消息
2019-9-6 16:16:26 |
赞(
操作
