mysql操作和详解
温馨提示
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.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket 4.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多
二、数据库的一些名词
1、数据/记录：描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机 例如：1,明哥,猛男,20,东北 但是单纯的一条记录并没有任何意义，如果我们按逗号作为分隔，给各个字段设置标题，那么就具有可读性了 id,name,sex,age,hometown 1,明哥,猛男,20,东北 2、数据库(DataBase,DB):数据库是长期存放在计算机内、有组织、可共享的数据集合。 其实数据库中的库就是文件夹，一般情况下 每个程序使用一个库，库中一张表(table)就是一个文件 3、数据库管理系统(DataBase Management System ,DBMS)：数据库管理系统就是一款专门用于管理数据库的系统软件，就是通过一个软件来管理文件夹、文件、数据 现在主流的数据库有： 关系型数据库 mysql 开源，主要用于大型门户 oracle 企业级，功能强大、安全但是费用高 sqlite 轻量级，文件数据库 sql server 大学课程多数用这个，是微软公司的产品 非关系型数据库：消息转发 memcache redis MongoDB nosql 解释什么是关系型 关系型 一条数据包含了一个事物的多条信息，这些信息之间是有关联性的 非关系型 :存取频繁的，并且要求效率高的，不突出数据之间关联的 k-v id content 4、数据库服务器：数据库管理系统装在哪台机器上，哪台机器就是数据库服务器，但是对这台机器的内存要求比较高 5、数据库管理员(Database Administrator，DBA)：维护数据库管理系统(DBMS)的相关工作人员的统称 6、总结 数据库服务器：运行数据库管理软件 数据库管理员：维护数据库管理软件的人 数据库管理软件：管理-数据库 数据库：即文件夹，用来组织文件/表 表：即文件，用来存放多行内容/多条记录
三、初识MySQL
1、介绍
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
DDL(data definition language)语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML(data manipulation language)语句 数据库操作语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL(Data Control Language)语句 数据库控制语言： 例如控制用户的访问权限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 mysql 2、停止服务 - 可以在service服务中操作，也可以打开cmd窗口 > net stop mysql 3、登录用户 > 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> \c 11、创建账号 # %表示这个网段内所有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'@'%' # 表示所有机器都可以连接
mysql> drop 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; 3-3.查看数据库或者表的结构和表的引擎 show create database 数据库名称; show create table 表名; 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 (字段); # 增加unique索引的简写，省略的索引名默认就为字段名 ALTER TABLE 表名 ADD UNIQUE KEY (字段); # 增加unique索引的简写，省略的索引名默认就为字段名 ALTER TABLE 表名 ADD UNIQUE INDEX (字段); # 增加unique索引的简写，省略的索引名默认就为字段名 # 三种方式都可以 ALTER TABLE 表名 ADD UNIQUE 索引名(字段); # 增加unique索引的完整写法 ALTER TABLE 表名 ADD UNIQUE KEY 索引名(字段); # 增加unique索引的完整写法 ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段); # 增加unique索引的完整写法 ALTER TABLE 表名 ADD FOREIGN KEY(当前表字段) REFERENCES 其他表(其他表的字段); 7.删除约束 ALTER TABLE 表名 DROP PRIMARY KEY; # 删除主键 ALTER TABLE 表名 DROP INDEX 索引名; # 删除索引 # 删除外键 SHOW CREATE TABLE 外键所在的表; # 查看外键别名 ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
create table staff_info( id int(11),name varchar(50), age int(3),sex enum('male','female'), phone bigint(11),job varchar(11));
mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 表重命名 mysql> alter table staff_info rename staff; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 删除age列 mysql> alter table staff drop age; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 添加age列，并将此列放在id后面 mysql> alter table staff add age int Not null after id; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改name的宽度 mysql> alter table staff modify name varchar(20); mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改name列的字段名 mysql> alter table staff change name sname varchar(25) not null; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sname | varchar(25) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改sname列的位置 mysql> alter table staff modify sname varchar(25) after id; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 创建自增id主键 mysql> alter table staff modify id int primary key auto_increment; mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ # 删除主键，但是删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ mysql> alter table staff drop primary key; Query OK, 3 rows affected (0.98 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 添加联合主键 mysql> alter table staff add primary key (id,sname); mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(25) | NO | PRI | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 删除联合主键 mysql> alter table staff drop primary key; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | sname | varchar(25) | NO | | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 为id添加自增属性(自增需要字段是唯一的) mysql> alter table staff modify id int unique auto_increment; mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(25) | NO | | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+
3、drop:删除
drop database 数据库名; // 删除数据库 drop table 表名; // 删除表
4、show/desc:查询
show databases; // 查询所有的数据库 show tables; // 查询所有表 desc 表名; // 查看表的结构 describe 表名; //查看表的结构 show create table 表名 \G; 查看当前表更详细的信息
show create database 库名 \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后面不写where条件，那么会更新这个表所有的这个字段的值。
示例： UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’; 注：mysql库的user表存储的是用户的信息 3、delete:删除数据 语法： DELETE FROM 表名 WHERE CONITION;
注：如果deleete后面不写where条件，那么会删除这个表的所有记录。 示例： 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.找到表:from 2.拿着where指定的约束条件，去表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.distinct去重 7.将结果按条件排序：order by 8.限制结果的显示条数
2、建表数据
company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, emp_name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | emp_name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：IT，策划，运营 insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values ('小明','male',18,'20170301','外交',7300.33,401,1), #以下是IT部 ('晓东','male',22,'20150302','IT',1000000.31,401,1), ('张三','male',81,'20130305','IT',8300,401,1), ('李四','male',73,'20140701','IT',3500,401,1), ('王铭','male',28,'20121101','IT',2100,401,1), ('小晶','female',18,'20110211','IT',9000,401,1), ('小红','male',18,'19000301','IT',30000,401,1), ('张一帆','male',48,'20101111','IT',10000,401,1), ('依依','female',48,'20150311','plan',3000.13,402,2),#以下是策划部门 ('尔尔','female',38,'20101101','plan',2000.35,402,2), ('伞伞','female',18,'20110312','plan',1000.37,402,2), ('思思','female',18,'20160513','plan',3000.29,402,2), ('呜呜','female',28,'20170127','plan',4000.33,402,2), ('张三丰','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('朱一','male',18,'19970312','operation',20000,403,3), ('朱二','female',18,'20130311','operation',19000,403,3), ('朱三','male',18,'20150411','operation',18000,403,3), ('朱四','female',18,'20140512','operation',17000,403,3) ; 如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
3、简单的查询示例
# 简单查询 SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT emp_name,salary FROM employee; # 避免重复DISTINCT SELECT distinct post FROM employee; # 通过四则运算查询 SELECT emp_name, salary*12 FROM employee; # 查询一年的工资 表中显示的列名是emp_name, salary*12 SELECT emp_name, salary*12 as year_salary FROM employee; #as给salary*12起别名 表中显示的列名是emp_name, year_salary SELECT emp_name, salary*12 year_salary FROM employee; # 或者可以不写as，空一格直接写别名 表中显示的列名是emp_name, year_salary # 定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',emp_name,' 年薪: ', salary*12) AS year_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',emp_name,salary*12) AS year_salary FROM employee; # CASE语句：
SELECT ( CASE WHEN emp_name = '小明' THEN emp_name WHEN emp_name = '晓东' THEN CONCAT(emp_name,'_GOOD') ELSE concat(emp_name, 'SB') END ) as new_name FROM employee; 例子： 1 查出所有员工的名字，薪资,格式为 <名字:小明> <薪资:7300.33> mysql> select concat('<名字：',emp_name,'> < 薪资：',salary,'>') from employee; 2 查出所有的岗位（去掉重复） mysql> select distinct post from employee; 3 查出所有员工名字，以及他们的年薪,年薪的字段名为yesr_salary mysql> select emp_name,salary*12 as year_salary from employee;
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
(1)单条件查询 SELECT emp_name FROM employee WHERE post='plan'; (2)多条件查询 SELECT emp_name,salary FROM employee WHERE post='IT' AND salary>10000; (3)关键字BETWEEN AND SELECT emp_name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT emp_name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; (4)关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL; SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL; SELECT emp_name,post_comment FROM employee WHERE post_comment=''; # 注意''是空字符串，不是null (5)关键字IN集合查询 SELECT emp_name,salary FROM employee WHERE salary=1000 OR salary=2000 OR salary=3000 OR salary=4000 ; SELECT emp_name,salary FROM employee WHERE salary IN (1000,2000,3000,4000) ; # 这个查询跟上句查询是一样的结果 SELECT emp_name,salary FROM employee WHERE salary NOT IN (1000,2000,3000,4000) ; (6)关键字LIKE模糊查询 通配符’%’ 可匹配任意长度的字符 SELECT * FROM employee WHERE emp_name LIKE '小%'; 通配符’_’ 可匹配任意一个字符 SELECT * FROM employee WHERE emp_name LIKE '朱_'; #此查询有4条记录：朱一、朱二、朱三、朱四 SELECT * FROM employee WHERE emp_name LIKE '朱__'; #此查询无结果，因为有多少个'_'就得匹配多少个字符，少一个都不符合 3.例题 (1) 查看岗位是IT的员工姓名、年龄 select emp_name,age from employee where post='IT'; (2) 查看岗位是IT且年龄大于30岁的员工姓名、年龄 select emp_name,age from employee where post='IT' and age>30; (3) 查看岗位是IT且薪资在9000-10000范围内的员工姓名、年龄、薪资 select emp_name,age,salary from employee where post='IT' and salary between 9000 and 10000; (4) 查看岗位描述不为NULL的员工信息 select * from employee where post_comment is not null; (5) 查看岗位是IT且薪资是9000或10000或30000的员工姓名、年龄、薪资 select emp_name,age,salary from employee where post='IT' and salary in (9000,10000,30000); (6) 查看岗位是IT且薪资不是9000或10000或30000的员工姓名、年龄、薪资 select emp_name,age,salary from employee where post='IT' and salary not in (9000,10000,30000); (7) 查看岗位是IT且名字是'张'开头的员工姓名、年薪 select emp_name,salary*12 as year_salary from employee where post='IT' and emp_name like '张%';
5、group by
(1) 单独使用GROUP BY关键字分组 SELECT emp_name,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对比concat 4-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 | +---------------------+
group_concat的其他用法
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values ('小明','female',28,20191112,'外交',8500,402,1); # 同一个部门有两个小明 select post,group_concat(emp_name) as emp_name from employee group by post; +-----------+-----------------------------------------------------+ | post | emp_name | +-----------+-----------------------------------------------------+ | IT | 小红,晓东,张三,李四,王铭,小晶,张一帆 | | operation | 朱一,朱四,朱二,朱三,张三丰 | | plan | 呜呜,思思,伞伞,依依,尔尔 | | 外交 | 小明,小明 | +-----------+-----------------------------------------------------+ # distinct去重 select post,group_concat(distinct emp_name) as emp_name from employee group by post; +-----------+-----------------------------------------------------+ | post | emp_name | +-----------+-----------------------------------------------------+ | IT | 小红,晓东,张三,李四,王铭,小晶,张一帆 | | operation | 朱一,朱四,朱二,朱三,张三丰 | | plan | 呜呜,思思,伞伞,依依,尔尔 | | 外交 | 小明 | +-----------+-----------------------------------------------------+ # ORDER BY排序 select post,group_concat(distinct emp_name,age order by age) as emp_name from employee group by post; +-----------+-------------------------------------------------------------------+ | post | emp_name | +-----------+-------------------------------------------------------------------+ | IT | 小晶18,小红18,晓东22,王铭28,张一帆48,李四73,张三81 | | operation | 朱三18,朱二18,朱四18,朱一18,张三丰28 | | plan | 伞伞18,思思18,呜呜28,尔尔38,依依48 | | 外交 | 小明18,小明28 | +-----------+-------------------------------------------------------------------+ # GROUP_CONCAT 函数拼接字符串默认的分隔符是逗号 ,SEPARATOR 可设置分隔符 select post,group_concat(distinct emp_name,age order by age separator '|') as emp_name from employee group by post; +-----------+-------------------------------------------------------------------+ | post | emp_name | +-----------+-------------------------------------------------------------------+ | IT | 小晶18|小红18|晓东22|王铭28|张一帆48|李四73|张三81 | | operation | 朱三18|朱二18|朱四18|朱一18|张三丰28 | | plan | 伞伞18|思思18|呜呜28|尔尔38|依依48 | | 外交 | 小明18|小明28 | +-----------+-------------------------------------------------------------------+
6、聚合函数
聚合函数聚合的是分组后每一组的内容，若是没有分组，则默认所有数据都是一组 函数： COUNT:数分组的记录的数量 MAX:分组内最大的数值 MIN:分组内最小的数值 AVG:分组内的数值的平均数 SUM:分组内的数值的和 示例： SELECT COUNT(*) FROM employee;
SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee;
1. 查询岗位名以及岗位包含的所有员工名字 select post,group_concat(emp_name) as name from employee group by post; 2. 查询岗位名以及各岗位内包含的员工个数 select post,count(id) as count from employee group by post; 3. 查询公司内男员工和女员工的个数 select sex,count(id) as count from employee group by sex; 4. 查询岗位名以及各岗位的平均薪资 select post,avg(salary) from employee group by post; 5. 查询岗位名以及各岗位的最高薪资 select post,max(salary) from employee group by post; 6. 查询岗位名以及各岗位的最低薪资 select post,min(salary) from employee group by post; 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex;
1.count(*)和count(1)的效率是一样的！两者几乎没有性能差异！ 如果表存在主键，他们都是根据主键去count的，速度都较快；如果不存在主键，则速度都较慢！ 2.count(1)/count(*)会统计表中的所有的记录数，包含字段为null 的记录； count(列名)会统计该字段在表中出现的次数，不统计字段为null 的记录。 且在效率方面count(非主键列)的效率往往低于count(*)/count(1)！而count(主键列)效率差不多！ 3.总结 由于mysql对count(*)做了优化，基于MySQL的Innodb存储引擎， 统计表的总记录数按照效率排序的话： count(字段)<count(主键id)<count(1)≈count(*) 效率最高是count(*),并不是count(1)，如果有面试官问你mysql中count(*)和count(1)哪个效率高？ 你就可以明确地告诉他，Innodb存储引擎下效率最高是count(*)。
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 ASC,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,1,2,3,4 五条记录 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; # 取索引为 5,6,7,8,9 五条数据 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; 由此可看出: limit第一个参数代表索引起始位置，第二个参数代表顺着取的记录个数；如果只有一个参数，那么默认起始位置为0。
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、数据准备
#建表 create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('cat','male',18,200), ('dog','female',48,201), ('pig','male',38,201), ('bird','female',28,202), ('tiger','male',18,200), ('lion','female',18,204);
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、内连接
内连接(inner join)：只连接匹配的行 说明： 找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-------+------+--------+--------------+ | id | name | age | sex | name | +----+-------+------+--------+--------------+ | 1 | cat | 18 | male | 技术 | | 2 | dog | 48 | female | 人力资源 | | 3 | pig | 38 | male | 人力资源 | | 4 | bird | 28 | female | 销售 | | 5 | tiger | 18 | male | 技术 | +----+-------+------+--------+--------------+ 5 rows in set (0.00 sec) 上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
注意：若连接的字段是唯一的，可省略表名，比如employee.age在employee表和department表是唯一的，可直接写age，但是name在employee表和department表都有，因此要用哪个表
的name，需要写上哪个表。
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会去掉相同的纪录，union all则不会。 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)
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 mysql> select employee.name,employee.age,department.name as depart_name from employee inner join department on employee.dep_id=department.id where employee.age>25; +------+------+--------------+ | name | age | depart_name | +------+------+--------------+ | dog | 48 | 人力资源 | | pig | 38 | 人力资源 | | bird | 28 | 销售 | +------+------+--------------+ 3 rows in set (0.00 sec) 示例2：以内连接的方式查询employee和department表，且employee表中的age字段值必须大于25，以age字段的升序方式显示 # 若某字段在两个表中都是唯一的，那么可以直接使用而不需要在前面加表名， # 若某字段在两个表中不是唯一的，那么使用时需要在前加表名区分， # 例如employee表中有name，department表中也有name，使用哪个表的name就在name前加哪里表名，例如：employee.name # 而employee表中的age,sex等字段是唯一的，即department表中没有这些字段，那么可以直接使用age,sex不需要加表名，当然加了也是可以的。 mysql> select employee.name,sex,age,department.name from employee inner join department on employee.dep_id=department.id where age>25 order by age; +------+--------+------+--------------+ | name | sex | age | name | +------+--------+------+--------------+ | bird | female | 28 | 销售 | | pig | male | 38 | 人力资源 | | dog | female | 48 | 人力资源 | +------+--------+------+--------------+ 3 rows in set (0.00 sec) 示例3：给表起别名 mysql> select a.name,sex,age,b.name from employee as a inner join department as b on a.dep_id=b.id where age>25 order by age asc; +------+--------+------+--------------+ | name | sex | age | name | +------+--------+------+--------------+ | bird | female | 28 | 销售 | | pig | male | 38 | 人力资源 | | dog | female | 48 | 人力资源 | +------+--------+------+--------------+ 3 rows in set (0.00 sec)
8、三表查询
1.创建表 # 学生表 create table student( sid int, # 学生编号 sname varchar(6), # 学生姓名 sage int, # 学生年龄 ssex enum('male', 'female') # 学生性别 ); # 课程表 create table course( cid int, # 课程编号 cname varchar(20), # 课程名称 tid int # 教师编号 ); # 教师表 create table teacher( tid int, # 教师编号 tname varchar(20) # 教师姓名 ); # 成绩表 create table sc( sid int, # 学生编号 cid int, # 课程编号 score int # 分数 ); 2.表数据 insert into student values (110,'小明',18,'male'), (111,'小红',17,'female'), (112,'小花',19,'female'); insert into course values (200,'语文',1), (201,'数学',2), (202,'英语',1); insert into teacher values (1,'马老师'), (2,'狗老师'); insert into sc values (110,200,60), (110,201,70), (110,202,78), (111,200,80), (111,201,88), (111,202,95), (112,200,50), (112,201,60), (112,202,34);
1.查询所有学生的学生编号、学生姓名、选课总数、所有课程总成绩，并从高到低排序 # 两个表查询 select student.sid, student.sname,count(sc.sid) as count, sum(score) as score from student inner join sc on student.sid=sc.sid group by student.sid order by score DESC; 2.查询不同老师所教不同课程平均分从高到低显示 # 三表查询 select teacher.tname,course.cname, avg(sc.score) as avg_score from course inner join teacher on course.tid=teacher.tid inner join sc on course.cid=sc.cid group by course.cid order by avg(score) DESC; 3.结果 mysql> select student.sid, student.sname,count(sc.sid) as count, sum(score) as score -> from student inner join sc on student.sid=sc.sid group by student.sid order by score DESC; +------+--------+-------+-------+ | sid | sname | count | score | +------+--------+-------+-------+ | 111 | 小红 | 3 | 263 | | 110 | 小明 | 3 | 208 | | 112 | 小花 | 3 | 144 | +------+--------+-------+-------+ 3 rows in set (0.01 sec) mysql> select teacher.tname,course.cname, avg(sc.score) as avg_score -> from course inner join teacher on course.tid=teacher.tid -> inner join sc on course.cid=sc.cid -> group by course.cid -> order by avg(score) DESC; +-----------+--------+-----------+ | tname | cname | avg_score | +-----------+--------+-----------+ | 狗老师 | 数学 | 72.6667 | | 马老师 | 英语 | 69.0000 | | 马老师 | 语文 | 63.3333 | +-----------+--------+-----------+
9、子查询
(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不能直接使用聚合函数)
# 例如: select name,age from employee where age>avg(age) 是错误的语法，因为where不能和聚合函数直接使用
# 因此我们改变思路，先用子查询将年龄平均值算出来，再用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语句数据库控制语言)
相关权限如下：
| Privilege | Grant Table Column | Context |
|---|---|---|
ALL [PRIVILEGES] |
Synonym for “all privileges” | Server administration |
ALTER |
Alter_priv |
Tables |
ALTER ROUTINE |
Alter_routine_priv |
Stored routines |
CREATE |
Create_priv |
Databases, tables, or indexes |
CREATE ROUTINE |
Create_routine_priv |
Stored routines |
CREATE TABLESPACE |
Create_tablespace_priv |
Server administration |
CREATE TEMPORARY TABLES |
Create_tmp_table_priv |
Tables |
CREATE USER |
Create_user_priv |
Server administration |
CREATE VIEW |
Create_view_priv |
Views |
DELETE |
Delete_priv |
Tables |
DROP |
Drop_priv |
Databases, tables, or views |
EVENT |
Event_priv |
Databases |
EXECUTE |
Execute_priv |
Stored routines |
FILE |
File_priv |
File access on server host |
GRANT OPTION |
Grant_priv |
Databases, tables, or stored routines |
INDEX |
Index_priv |
Tables |
INSERT |
Insert_priv |
Tables or columns |
LOCK TABLES |
Lock_tables_priv |
Databases |
PROCESS |
Process_priv |
Server administration |
PROXY |
See proxies_priv table |
Server administration |
REFERENCES |
References_priv |
Databases or tables |
RELOAD |
Reload_priv |
Server administration |
REPLICATION CLIENT |
Repl_client_priv |
Server administration |
REPLICATION SLAVE |
Repl_slave_priv |
Server administration |
SELECT |
Select_priv |
Tables or columns |
SHOW DATABASES |
Show_db_priv |
Server administration |
SHOW VIEW |
Show_view_priv |
Views |
SHUTDOWN |
Shutdown_priv |
Server administration |
SUPER |
Super_priv |
Server administration |
TRIGGER |
Trigger_priv |
Tables |
UPDATE |
Update_priv |
Tables or columns |
USAGE |
Synonym for “no privileges” | Server administration |
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) ， 最大为decimal(65,30) |
decimal(5,2)代表一共显示5位数，小时占两位，即整数占3位；decimal最大表示范围是：decimal(65,30) | 依赖于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,2 mysql> 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.145 mysql> 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 |
YYYYMMDDHHMMSS | 混合日期和时间值，时间戳 |
常用类型介绍
date 描述年月日 1000-01-01/9999-12-31 time 描述时分秒 datetime 描述年月日时分秒 1000-01-01 00:00：00/9999-12-31 23:59:59 timestamp 描述年月日时分秒，字段默认不为空 1970-01-01 00:00:00/2038 timestamp默认不为空，默认是当前时间，范围比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 | +------------+----------+------+---------------------+---------------------+ # 输入正常时间格式 mysql> insert into t1 values("2019-09-29","20:57:29","2019","2019-09-29 20:57:29","20190929205729"); Query OK, 1 row affected (0.00 sec) 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 | | 2019-09-29 | 20:57:29 | 2019 | 2019-09-29 20:57:29 | 2019-09-29 20:57:29 | +------------+----------+------+---------------------+---------------------+
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 key mysql> 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 请输入密码：112233 select * from userinfo where username='ming' and password='112233' 1 登陆成功
3、SQL注入问题
1. 什么是SQL注入？ 用户输入的内容有恶意的SQL语句，后端拿到用户输入的内容不做检测直接做字符串拼接，得到一个和预期不一致的SQL语句 例如：上面例子的代码完全不变，但是我这样输入： 请输入用户名：ming' -- 请输入密码：12345 select * 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' -- 请输入密码：12345 select * from userinfo where username=%s and password=%s; 0 登录失败 结果2： 请输入用户名：ming 请输入密码：112233 select * 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 pymysql class 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、视图的优点
- 安全：有些重要的数据表中存在一些保密的字段，不希望其他用户看到，那么可以创建视图，视图中只保留一些可对外开发的字段，那么用户只能访问他们被允许查询的结果集。
- 查询效率：复杂的连表查询等操作，可以使用视图进行一次连表查询，之后的查询都基于这个视图，就可以避免每次查询都进行复杂join连表操作。
- 灵活：假如一张旧表即将要被废弃(冗余字段太多，表太大)，但是很多应用都基于这张表，不易修改，这个时候就可以使用视图对旧表进行映射达到去除无用字段的目的。
- 权限控制：对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率(这里的查询效率指的是sql的复杂度)。
3、视图的创建
#两张有关系的表 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | | 4 | 美术 | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> select * from teacher; +-----+-----------------+ | tid | tname | +-----+-----------------+ | 1 | 张三老师 | | 2 | 李四老师 | | 3 | 刘五老师 | | 4 | 朱六老师 | | 5 | 李七老师 | +-----+-----------------+ 5 rows in set (0.00 sec)
语法：CREATE VIEW 视图名称 AS SQL语句 create view teacher_view as select tid from teacher where tname='李四老师'; show tables; # 可以找到teacher_view这个视图 desc teacher_view; # 可以看到teacher_view这个视图有表结构 select * from teacher_view; # 可以看到teacher_view这个视图也有表数据 注意！！！ 创建的teacher_view视图表实际上是没有数据的，你可以在硬盘上找到自己的mysql安装目录里面的data文件夹里面的对应的那个库的文件夹，这个文件夹里面存着的就是表信息，
打开之后你会发现，这个视图表，只有表结构的teacher_view.frm文件，没有那个.idb存放数据的文件， 因为实际上它并没有真实的数据，它的数据来源于其他两个表，所以它本质在后台对应的就是一个sql语句而已，所以，视图只有表结构，没有表数据。 视图的好处是以后我们如果再需要查询或者使用上面的虚拟表，就可以直接使用这个视图了，sql的代码量也会省很多。 示例：查询李四老师教授的课程名的sql可以改写为 mysql> select cname from course where teacher_id = (select tid from teacher_view); +--------+ | cname | +--------+ | 物理 | | 美术 | +--------+ 2 rows in set (0.00 sec) 但是视图也有它的弊端 1.使用视图以后就无需每次都重写子查询的sql，开发的时候是方便了很多，但是这么效率并不高，还不如我们写子查询的效率高。 2.视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，
则必须去数据库中进行修改，然后再到自己的应用程序里面将那个sql语句改一改，需要很多的修改工作，并而对视图的更改通常在一般中型及以上公司中数据库有专门的DBA负责，
你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便。 3.视图一般都是用于查询，尽量不要修改(插入、删除等)视图中的数据，虽然有时候可以修改成功，但是尽量不要这样做，因为这个视图可能是多个表联合起来生成的一个结果，
如果你修改它，可能会造成很多表里面的数据都跟着被修改了。
4、视图的作用
1.提高重用性(查询效率) 如果需要频繁获取 user.name 和 info.sex 字段： # 使用sql select user.name,info.sex from user inner join info on user.id = info.uid; # 后续如果要查，那么就要重复上面的sql，效率慢。 # 使用视图，只需要查一次 create view userinfo as select user.name,info.sex from user inner join info on user.id = info.uid; # 然后后续需要查就直接查视图即可 select * from userinfo; 2.对数据库表进行重构 比如：user_1 表有 name 比如：user_2 表有 age 查询时，我想直接从user表中查这两个字段，那么可以 create view user as select user_1.name,user_2.age from user_1 inner join user_2 on user_1.id = user_2.id; select * from user; 3.进行权限控制，对不同的用户开放对应权限的查询字段 # 用户1只能查看name字段 create view user as select user_1.name from user_1; # 用户2只能查看age字段 create view user as select user_2.age from user_2;
5、修改视图记录
注意：
原则上我们一般不应该去修改视图，因为修改视图，视图对应的基表也会被修改。
而且在涉及多个表的情况下是根本无法修改视图中的记录的
# 修改视图，原始表也跟着改 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: 0 mysql> 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)
6、修改视图结构
语法：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)
7、删除视图
语法：DROP VIEW 视图名称
DROP VIEW teacher_view
十四、触发器
使用触发器(trigger)可以定制用户对某一张表的数据进行【增、删、改】操作时前后的行为，注意：没有查询，在进行增删改操作的时候，触发的某个操作，称为触发器。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
1、触发器语法
CREATE TRIGGER trigger_name trigger_time trigger_even
ON table_name FOR EACH ROW trigger_stmt
注意：
trigger_time是触发器的触发时间，可以为before（在检查约束前触发）或after（在检查约束后触发）；
trigger_event是触发器的触发事件，包括insert、update和delete;
对同一个表相同触发时间的相同触发事件，只能定义一个触发器；
定义一个触发器
create trigger triggerName after/before insert/update/delete on 表名 for each row # 这句话在mysql是固定的 begin sql语句; end;
2、触发器示例
CREATE TABLE cmd ( #这是一张指令信息表，你在系统里面执行的任何的系统命令都在表里面写一条记录 id INT PRIMARY KEY auto_increment, #id USER CHAR (32), #用户 priv CHAR (10), #权限 cmd CHAR (64), #指令 sub_time datetime, #提交时间 success enum ('yes', 'no') #是否执行成功，0代表执行失败 ); CREATE TABLE errlog ( #指令执行错误的信息统计表，专门提取上面cmd表的错误记录 id INT PRIMARY KEY auto_increment, #id err_cmd CHAR (64), #错误指令 err_time datetime #错误命令的提交时间 );
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 // create procedure p1() BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now()); END // delimiter ;
# 存储过程的创建
create procedure 自定义的名字()
BEGIN
# 在mysql中调用 call p1(); # call 名字(参数);
# MySQL的视图啊触发器啊if判断啊等等都能在存储过程里面写，这是一大堆的sql的集合体，都可以综合到这里面
# 在python中基于pymysql调用 cursor.callproc('p1') print(cursor.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=0 print(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语句要么同时成功，要么都不成功，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
事务的四大特性
- 原子性
- 一致性
- 隔离性
- 持久性
1、原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，操作完全应用到数据库；
要么操作出现失败，进行回滚，不能对数据库有任何影响。
2、一致性（Consistency）
事务执行前和执行后必须处于一致性状态。
例如：小明有100块钱，小红也有100块钱，两人一共有200块钱，那么不管小明和小红之间如何转账、转几次账，
事务结束后两人的钱加起来还是200，这就是事务的一致性。
3、隔离性（Isolation）
当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作所干扰，多个并发事务之间要相互隔离。
4、持久性（Durability）
一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便在数据库系统遇到故障的情况下也不会丢失事物的操作。
例如：小明有100块钱，小红也有100块钱，小明转给小红20块，那么小明还有80，小红则有120，
只要事务显示执行完成了，即使这时候数据库出现了问题，小明仍然是80，小红仍然是120。
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('buyer',1000), ('jian',1000), ('sale',1000);
如何开启事务
事务设置： SET autocommit = 0;
默认情况下 autocommit = 1，是自动提交事务的。
autommit 是 session 级别的，就是当前连接更改了 autocommit，对其他连接没有影响。
设置 autocommit 之后，本次连接的所有 sql 都是事务的形式，比如每次 commit 提交。
START TRANSACTION 或 BEGIN 开始新的事务 COMMIT 提交当前事务 ROLLBACK 回滚当前事务，其中开启事务的方式 start transaction 和 begin 是相同的。
MySQL事务实现原理
mysql实现事务，是基于undo/redo日志
undo记录修改前状态，rollback基于undo日志实现
redo记录修改后的状态，commit基于redo日志实现
既然是基于redo日志实现记录修改后的状态，那么大家应该也知道，redo日志是innodb专有的，所以innodb会支持事务
在mysql中无论是否开启事务，sql都会被立即执行并返回执行结果，只是事务开启后执行后的状态只是记录在redo日志，执行commit之后，数据才会被写入磁盘。
MySQL事务与Redis事务的区别：https://www.cnblogs.com/Zzbj/p/10151771.html#autoid-7-1-0
存储过程结合事务
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参数的值只要不是这三个值就行了，这里给的是100 print(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 sqlexception BEGIN 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，否则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default IF(test,t,f) 如果test是真，返回t；否则返回f IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
控制流函数 # 1、准备表，将下面这些内容保存为一个.txt文件或者.sql，然后通过navicat的运行sql文件的功能导入到数据库中 /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50720 Source Host : localhost:3306 Source Database : student Target Server Type : MYSQL Target Server Version : 50720 File Encoding : 65001 Date: 2018-01-02 12:05:30 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `c_id` int(11) NOT NULL, `c_name` varchar(255) DEFAULT NULL, `t_id` int(11) DEFAULT NULL, PRIMARY KEY (`c_id`), KEY `t_id` (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', 'python', '1'); INSERT INTO `course` VALUES ('2', 'java', '2'); INSERT INTO `course` VALUES ('3', 'linux', '3'); INSERT INTO `course` VALUES ('4', 'web', '2'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s_id` int(10) DEFAULT NULL, `c_id` int(11) DEFAULT NULL, `num` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '1', '79'); INSERT INTO `score` VALUES ('2', '1', '2', '78'); INSERT INTO `score` VALUES ('3', '1', '3', '35'); INSERT INTO `score` VALUES ('4', '2', '2', '32'); INSERT INTO `score` VALUES ('5', '3', '1', '66'); INSERT INTO `score` VALUES ('6', '4', '2', '77'); INSERT INTO `score` VALUES ('7', '4', '1', '68'); INSERT INTO `score` VALUES ('8', '5', '1', '66'); INSERT INTO `score` VALUES ('9', '2', '1', '69'); INSERT INTO `score` VALUES ('10', '4', '4', '75'); INSERT INTO `score` VALUES ('11', '5', '4', '66.7'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `s_id` varchar(20) NOT NULL, `s_name` varchar(255) DEFAULT NULL, `s_age` int(10) DEFAULT NULL, `s_sex` char(1) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '鲁班', '12', '男'); INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女'); INSERT INTO `student` VALUES ('3', '刘备', '35', '男'); INSERT INTO `student` VALUES ('4', '关羽', '34', '男'); INSERT INTO `student` VALUES ('5', '张飞', '33', '女'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `t_id` int(10) NOT NULL, `t_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '大王'); INSERT INTO `teacher` VALUES ('2', 'alex'); INSERT INTO `teacher` VALUES ('3', 'chao'); INSERT INTO `teacher` VALUES ('4', 'peiqi'); # 2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] select score.c_id, course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]', sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]', sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]', sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]' from score,course where score.c_id=course.c_id GROUP BY score.c_id;
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 int BEGIN declare num int; set num = i1 + i2; return(num); END // delimiter ; # 2、自定义函数2 delimiter // create function f5( i int ) returns int begin 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 // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ;
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、loop BEGIN 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只有加速查询的效果，没有约束效果）
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据
为什么添加索引会提高查询速度
- 索引可以将无序内容转换为有序的一个集合（相对），就如同新华字典，如果没有目录，那么查询一个汉字就需要很长时间了。
- 如果没有索引我们查询数据是需要遍历双向链表来定位对应的page，现在通过索引创建的“目录”就可以很快定位对应页上了
索引可以提高查询速度，但是会降低增删改的速度
- 因为创建了索引，如果进行拆入删除的时候，就需要改变这个"目录"，就必须要改变拆入或者删除的这个数据 后面的所有数据的索引
2、索引的原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。 那么你想，书的目录占不占页数，这个页是不是也要存到硬盘里面，也占用硬盘空间。你再想，你在没有数据的情况下先建索引或者目录的速度快，还是已经存在好多的数据了，然后再去建索引/目录速度快，那肯定是没有数据的时候快，因为如果已经有了很多数据了，你再去根据这些数据建索引，要将数据全部遍历一遍，然后根据数据建立索引。你再想，索引建立好之后再添加数据速度快，还是没有索引的时候添加数据速度快，因为索引是用来加速查询的，那对你写入数据肯定会有一些影响的，插入数据的速度肯定是慢一些的，因为你但凡加入一些新的数据，都需要把索引或者说书的目录重新做一个，所以索引虽然会加快查询，但是会降低写入的效率。
注意：
1、在表中有大量数据的前提下，再来创建索引速度会很慢
2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低
本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
3、B+树结构介绍
参考：https://baijiahao.baidu.com/s?id=1712077472070798964&wfr=spider&for=pc
一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来存索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
这里特别要注意有几点：
其一：每个父节点的元素都出现在子节点中，是子节点的最大（或最小）元素；因此所有叶子节点包含了全量元素信息；
其二：每个叶子节点都带有指向下一个节点的指针，形成了一个有序链表；
其三：只有叶子节点带有卫星数据，其余中间节点仅仅是索引，没有任何数据关联，如下图，所谓卫星数据，指的是索引元素所指向的数据记录，比如数据库中的某一行，在B树中，无论中间节点还是叶子节点都带有卫星数据。
B+树的查找以典型的方式进行，类似于二叉查找树。起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部典型的使用是二分查找来确定这个位置。
设计优势
B+树的好处主要体现在查询性能上，由于B+树的中间节点没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，这就意味着，一次性加载到内存中的节点元素更多，从而使得查询时IO次数也更少。(举个简单的例子，一个磁盘页可以加载B树的100个节点元素，但是可以加载B+树的1000个节点元素，那么对于查找999这个数来说，B树需要10次IO，B+树只需要1次IO)
B+树相对B树的优点：
1.IO一次读数据是从磁盘上读的，磁盘容量是固定的，取数据量大小是固定的，非叶子节点不存储数据，节点小，磁盘IO次数就少。
2.B+树查询性能稳定，因为B+树的查询必须最终查找到叶子节点；而B树，只要找到匹配元素即可，无论匹配元素处于中间节点，还是叶子节点。所以B树的查询性能并不稳定，最好情况是只查根节点，最坏情况是查到叶子节点
3.B+树的所有Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串联起来（可以认为是链表），遍历叶子节点就能获取全部数据，这样就能进行区间访问了。B树做范围查询，只能繁琐的遍历，但是B+树，只需要查到查找到范围下限以后，遍历叶子节点（有序链表）就可以了。
综合起来，B+树比B树的优势有三个：1、IO次数更少；2、查询性能更佳；3、范围查询简便
场景：Mysql索引
4、索引的实现
参考：https://blog.csdn.net/hzp666/article/details/82747590
其实索引就是一种用于快速查找数据的数据结构，是帮助MySQL高效获取数据的排好序的数据结构。
不加索引的情况下
假如SQL如下：
select * from t1 where age=19
需要从表的第一行一行行遍历比对age的值是否等于19，如果数据在最后一行，这样就需要比对整个表才能查到。如果是百万级千万级的表，执行速度会非常慢。
加索引的情况下
如果age这列加了索引，mysql内部会维护一个数据结构。
假设mysql用的是B+树(InnoDB默认索引)的数据结构建立索引，那就会根据B+树的查找算法去查找数据，速度非常快。
这就是索引的好处。索引使用比较巧妙的数据结构，利用数据结构的特性来大大减少查找遍历次数。
聚集索引(clustered index，也称聚类索引、簇集索引)：主键primary key，以主键创建的索引。
聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，但该索引可以包含多个列（组合索引）。
非聚集索引(nonclustered index，也称非聚类索引、非簇集索引、辅助索引)：也称为非聚集索引，unique key、index key， 非主键创建的索引。
非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
聚集索引：叶节点就是数据节点
非聚集索引：叶节点仍然是索引节点，有一个指针指向对应的数据块。
索引为什么能加快MySQL的查询速度？
通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。 数据结构：B+树。
深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。
其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。
如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；
同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自动中查到这个字。
但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。
但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。
很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。
我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
注意
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，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.创建的几种方法
"""[索引名] 是可选的，缺省时，MySQL将根据第一个索引列赋一个名称""" # 方法一：创建表时创建索引 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], UNIQUE | FULLTEXT | SPATIAL | INDEX [索引名](字段名) ); 记忆方法：create table 表名(字段1，字段2，索引 [索引名](字段名)) # 方法二：CREATE 在已存在的表上创建索引 CREATE UNIQUE | FULLTEXT | SPATIAL | INDEX [索引名] ON 表名(字段名) ; 记忆方法：create 索引 [索引名] on 表名(字段名) # 方法三：ALTER TABLE 在已存在的表上创建索引 ALTER TABLE 表名 ADD UNIQUE | FULLTEXT | SPATIAL | INDEX [索引名](字段名); 记忆方法：alter table 表名 ADD 索引 [索引名](字段名) # 查看索引 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.在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
创建索引
create index ix_id on s1(id);
或者
alter table s1 add primary key(id);
在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
4.索引建立完毕后，以该字段为查询条件时，查询速度提升明显
select * from s1 where id=333333333;
Empyt set (0.00 sec)
5.总结
1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引 2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快 比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。 3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。 因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.
那是不是全部加索引就一定好的呢，首先，我们加上索引，查询时快了，但是写入的时候就慢了，还记得吗，每次插入新的记录，你的整个索引结构都会跟着改，所以如果你乱加索引，你会发现，即便是你的网站没有几个人在注册，或者说没有几个写入数据的操作，你的磁盘IO会居高不下，磁盘在疯狂的转，
因为你每插入一条数据，我们的索引都需要重新建，重建的索引要写入硬盘里面的，还记得我们给那三百万条数据建索引的时候的速度吗？每次都要把所有的数据取出来，做好一个数据结构，然后再写回硬盘，也就是要经历很多的IO才能实现这个事儿，所以乱加索引的一个弊端就是，你很少的写入都会导致你的磁盘IO非常的高，导致效率很差，所以我们要学一下怎么正确的加索引。
8、正确使用索引
1.索引未命中(所谓命中索引，就是应用上了索引)
1、最左匹配原则 1. mysql会一直向右匹配直到遇到范围查询(>, <, between, like)就停止匹配, 比如a=1 and b=2 and c>3 and d=4 如果建立了(a,b,c,d)顺序的索引, d是用不到索引的,
因为遇到c>3的时候就停止了。如果建立(a,b,d,c)的索引, 则都可以使用到, a,b,d的顺序可以任意调整. 2. = 和 in 可以乱序, 比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序, mysql 的查询优化器会帮你优化成索引可以识别的形式. 2、多列索引(遵循：最左匹配原则) 1.创建索引 ALTER TABLE user ADD INDEX user_index(name,age,phone); 这种创建索引的方式在多项查找时要优于单一索引，由于mysql的采用的b+树方式，因此不再需要扫描任何记录，直接就可以得到满足需求的结果集。
而这种方式其实相当于创建了三个索引(name)，(name,age)，(name,age,phone)。 2.命中mysql索引的查询 select * from user where name='小东'; select * from user where name='小东' and age=18; select * from user where name='小东' and age=18 and phone=13113; 3.不会命中索引的查询 # 1.如果where后面有or，这样不会命中索引 select * from user where name='小东' or age=18; 如果想要or后面的也走索引的话，应该是创建两个单列索引： ALTER TABLE user ADD INDEX name_index(name); ALTER TABLE user ADD INDEX age_index(age); # 2.like是以%开头的不会命中索引 select * from user where name like %明 # 3.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 select * from user where name='小东' and age=18 and phone=13113; 4.没有查询条件，或者查询条件没有建立索引，则不使用索引 5.查询条件中，在索引列上使用函数（+/-*/） select * from user where name='小东' and age-1=18 # 不使用索引 select * from user where name='小东' and age=20 # 使用索引 6.采用 not in, not exist，!=, <> , is null , is not null 不会命中索引
2.注意事项
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
9、联合索引
普通索引： create index 索引名 on 表名(列名) drop index 索引名 on 表名 唯一索引： create unique index 索引名 on 表名(列名) drop index 索引名 on 表名 联合索引： create unique index 索引名 on 表名(列名1,列名2...) drop 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: 0 mysql> 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_CACHE 1.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-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir\[filename]] # service mysqld restart 2. 暂停 //仅当前会话 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=930 4. 截断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=1 slow-query-log-file=slow.log long_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
二十四、数据库三大范式
原文链接：https://www.cnblogs.com/wsg25/p/9615100.html
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。
1、第一范式
第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
举例说明：
在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：
可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；
2、第二范式
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
举例说明：
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，
但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，
这样就不满足第二范式的要求，调整如下，需分成两个表：
3、第三范式
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
举例说明：
上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，
而不是主键“学号”，所以需做如下调整：
这样一来，就满足了第三范式的要求。
二十五、数据库表分割技术
参考文章：https://www.cnblogs.com/xdp-gacl/p/4128202.html
数据库表分割技术包含以下内容:
- 水平分割
- 垂直分割
- 库表散列
1、水平分割
什么是水平分割？打个比较形象的比喻，在食堂吃饭的时候，只有一个窗口，排队打饭的队伍太长了，都排成S型了，这时容易让排队的人产生焦虑情绪，容易产生混 乱，这时一个管理者站出来，增加多个打饭窗口，把那条长长的队伍拦腰截断成几队。更形象一点的理解，你拿一把“手术刀”，把一个大表猛的切了几刀，结果这个大表，变成了几个小表。
水平分割根据某些条件将数据放到两个或多个独立的表中。即按记录进分分割，不同的记录可以分开保存，每个子表的列数相同。水平切割将表分为多个表。每个表包含的列数相同(字段相同)，但是数据行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。
通常用来水平分割表的条件有：日期时间维度、地区维度等，当然还有更多的业务维度。
例如：
案例1：某个公司销售记录数据量太大了，我们可以对它按月进行水平分割，每个月的销售记录单独成一张表。
案例2：某个集团在各个地区都有分公司，该集团的订单数据表太大了，我们可以按分公司所在的地区进行水平切割。
案例3：某电信公司的话单按日期、地市水平切割后，发现数据量太大，然后他们又按品牌、号码段进行水平切割
水平分割通常在下面的情况下使用：
1.表数据量很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，加快了查询速度。
2.表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
3.需要把数据存放到多个介质上。
4.需要把历史数据和当前的数据拆分开。
水平分割优点：
1：降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，加快了查询速度。
水平分割缺点：
1：水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。
2、垂直分割
什么是垂直分割呢？打个形象的比喻，一个小公司通过短短几年发展变成了一个跨国大企业，以前的部门架构明显不能满足现在的业务发展，CEO噼里啪啦的把公司分成了财务部、人事部、生产部、销售部门.....，一下子成立了多个部门，各司其职。这个还算比较形象吧，有木有？呵呵
垂直分割表（不破坏第三范式），把主键列和一些列放到一个表，然后把主键列和另外的一些列放到另一个表中。将原始表分成多个只包含较少列的表。如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割。
垂直分割优点：
1：垂直分割可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)。
2：垂直分割表可以达到最大化利用Cache的目的。
垂直分割缺点：
1：表垂直分割后，主码(主键)出现冗余，需要管理冗余列
2：会引起表连接JOIN操作（增加CPU开销）需要从业务上规避
3、库表散列
表散列与水平分割相似，但没有水平分割那样的明显分割界限，采用Hash算法计算出数据的值，然后根据值分散到各个分表中, 这样IO更加均衡。一般来说，我们会按照业务或者功能模块将数据库进行分离，不同的模块对应不同的数据库或者表，再按照一定的策略对某个页面或者功能进行更小的数据库散列，比如用户表，按照用户ID进行表散列，散列128张表，则应就能够低成本的提升系统的性能并且有很好的扩展性
4、分割例子
参考文章：https://www.cnblogs.com/behindman/p/8727495.html
1、垂直分割
垂直拆分:
顾名思义是将表垂直着给拆掉,即:
+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+
| userid | groupid | areaid | amount | point | modelid | message | islock | vip | overduedate | siteid | connectid | from | mobile |
+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+
| 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | 0 | 1 | 0 | 1 | | | |
+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+
比如说一个用户表有很多的属性,关联了很多数据,如果放到同一个表里面的话查询是方便了,但是效率不行,所以这里就是用到了垂直拆表:
拆成如下:
+--------+---------+--------+--------+-------+---------+---------+ | userid | groupid | areaid | amount | point | modelid | message | +--------+---------+--------+--------+-------+---------+---------+ | 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | +--------+---------+--------+--------+-------+---------+---------+ 和 +--------+--------+-----+-------------+--------+-----------+------+--------+ | userid | islock | vip | overduedate | siteid | connectid | from | mobile | +--------+--------+-----+-------------+--------+-----------+------+--------+ | 1 | 0 | 1 | 0 | 1 | | | | +--------+--------+-----+-------------+--------+-----------+------+--------+
- 把常用的字段放一个表,不常用的放一个表
- 把字段比较大的比如text的字段拆出来放一个表里面
- 使用的话是根据具体业务来拆,查询时使用多表联查,可以再配合redis存储
2、水平分割
水平拆分:
顾名思义是将表数据水平的拆掉,即:
表0 user_0 +--------+---------+--------+--------+-------+---------+---------+ | userid | groupid | areaid | amount | point | modelid | message | +--------+---------+--------+--------+-------+---------+---------+ | 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | +--------+---------+--------+--------+-------+---------+---------+ 表1 user_1 +--------+---------+--------+--------+-------+---------+---------+ | userid | groupid | areaid | amount | point | modelid | message | +--------+---------+--------+--------+-------+---------+---------+ | 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | +--------+---------+--------+--------+-------+---------+---------+ 表2 user_2 +--------+---------+--------+--------+-------+---------+---------+ | userid | groupid | areaid | amount | point | modelid | message | +--------+---------+--------+--------+-------+---------+---------+ | 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | +--------+---------+--------+--------+-------+---------+---------+ . . . 表9 user_9 +--------+---------+--------+--------+-------+---------+---------+ | userid | groupid | areaid | amount | point | modelid | message | +--------+---------+--------+--------+-------+---------+---------+ | 1 | 5 | 0 | 0.00 | 50 | 10 | 0 | +--------+---------+--------+--------+-------+---------+---------+
当然这里不一定要0-9一共10张表来表示,通常情况下使用"取模"的形式来将数据进行表的存储,如果用4张表那么就是id%4 结果会是0,1,2,3四种,user_0,user_1,user_2,user_3就够了,具体这里就要看表的数据量了.
对水平分表的数据进行CURD操作也是一样,之前根据id取模算出当前数据在哪张表中,然后再select * from user_"取的模",这里有人要问了,我添加数据之前都不知道数据库的id,更不能进行取模了,怎么找到对应的表添加啊,对了,这里就需要一张临时表,临时表的作用就是提供数据插入的自增id,得到自增id后再通过取模进行分表插入.
水平分表的表结构是一样的,只是去掉了自增的属性.
这里不得不说水平分表的另一种形式,就是不是通过取模计算的分表,而是user_0存数10w条数据,存满创建新表user_1,继续存储在user_1,存满创建user_2一直存储并新建下去,个人建议这种分表使用场景是user_1的数据为历史数据,访问需求量会慢慢减小,而新表的数据访问量是很高的.
二十六、一条sql语句的完整执行过程
第一步:客户端把语句发给服务器端执行 第二步:语句解析 1.查询高速缓存(library cache) 服务器进程在接到客户端传送过来的SQL语句时,不会直接去数据库查询，而是会先在数据库的高速缓存中去查找是否存在相同语句的执行计划。 如果在数据高速缓中, 则服务器进程就会直接执行这个SQL 语句,省去后续的工作。 2.语句合法性检查(data dict cache) # 语法 当在高速缓存中找不到对应的SQL 语句时,则服务器进程就会开始检查这条语句的合法性。 这里主要是对SQL 语句的语法进行检查,看看其是否合乎语法规则。 如果服务器进程认为这条SQL语句不符合语法规则的时候,就会把这个错误信息,反馈给客户端。 在这个语法检查的过程中, 不会对SQL语句中所包含的表名、列名等等进行检查，它只是语法上的检查。 3.语言含义检查(data dict cache) # 语义 若SQL语句符合语法上的定义的话,则服务器进程接下去会对语句中的字段、表等内容进行检查。 看看这些字段、表是否在数据库中。如果表名与列名不准确的话,则数据库会就会反馈错误信息给客户端。 所以, 有时候我们写select语句的时候,若语法与表名或者列名同时写错的话,则系统是先提示说语法错误,等到语法完全正确后,再提示说列名或表名错误。 4.获得对象解析锁(control structer) 当语法、语义都正确后,系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发生改变。 5.数据访问权限的核对(data dict cache) 当语法、语义通过检查之后,客户端还不一定能够取得数据。服务器进程还会检查,你所连接的用户是否有这个数据访问的权限。 若你连接上服务器 的用户不具有数据访问权限的话,则客户端就不能够取得这些数据。 6.确定最佳执行计划 当语句与语法都没有问题,权限也匹配的话,服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则,对这条语句进行优化。 不过要注意,这个优化是有限的。一般在应用软件开发的过程中, 需要对数据库的sql 语言进行优化,这个优化的作用要大大地大于服务器进程的自我优化。 所以,一般在应用软件开发的时候,数据库的优化是少不了的。当服务器进程的优化器确定这条查询语句的最佳执行计划后, 就会将这条SQL 语句与执行计划保存到数据高速缓存(library cache)。 如此的话,等以后还有这个查询时,就会省略以上的语法、语义与权限检查的步骤, 而直接执行SQL 语句, 提高SQL 语句处理效率。 第三步:语句执行 语句解析只是对SQL语句的语法进行解析,以确保服务器能够知道这条语句到底表达什么意思。语句解析完成之后, 数据库服务器进程才会真正的执行这条SQL语句。 这个语句执行也分两种情况。 一是若被选择行所在的数据块已经被读取到数据缓冲区的话,则服务器进程会直接把这个数据传递给客户端,而不是从数据库文件中去查询数据。 二是若数据不在缓冲区中,则服务器进程将从数据库文件中查询相关数据,并把这些数据放入到数据缓冲区中(buffer cache)。 第四步:提取数据 当语句执行完成之后,查询到的数据还是在服务器进程中,还没有被传送到客户端的用户进程。所以,在服务器端的进程中,有一个专门负责数据提取的一段代码。 它的作用就是把查询到的数据结果返回给用户端进程,从而完成整个查询动作。 注意点： 一是要了解数据库缓存跟应用软件缓存是两码事情。 数据库缓存只有在数据库服务器端才存在,在客户端是不存在的。只有如此,才能够保证数据库缓存中的内容跟数据库文件的内容一致。才能够根据相关的规则,防止数据脏读、错读的发生。 而应用软件所涉及的数据缓存,由于跟数据库缓存不是一码事情,所以,应用软件的数据缓存虽然可以提高数据的查询效率,但是,却打破了数据一致性的要求,有时候会发生脏读、错读等情况的发生。 所以,有时候,在应用软件上有专门一个功能,用来在必要的时alter:跟新候清除数据缓存。不过,这个数据缓存的清除,也只是清除本机上的数据缓存,或者说,只是清除这个应用程序的数据缓存,而不会清除数据库的数据缓存。 二是绝大部分SQL语句都是按照这个处理过程处理的。特别要注意,数据库是把数据查询权限的审查放在语法语义的后面进行检查的。
二十七、补充
# 数据库需要存储千万级别的量--如何设计表、如何查询 # 如何设计表 分库 -- 水平分割 水平分割根据某些条件将数据放到两个或多个独立的表中。即按记录进分分割，不同的记录可以分开保存，每个子表的列数相同。 水平切割将表分为多个表。每个表包含的列数相同(字段相同)，但是数据行更少。 例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月的数据。 任何需要特定月份数据的查询只需引用相应月份的表。 通常用来水平分割表的条件有：日期时间维度、地区维度等，当然还有更多的业务维度。 分表 -- 垂直分割 垂直分割表（不破坏第三范式），把主键列和一些列放到一个表，然后把主键列和另外的一些列放到另一个表中。 将原始表分成多个只包含较少列的表。如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割。 硬件分区 硬件分区将数据库设计为利用可用的硬件构架 # 如何查询效率高 1、建立索引 如果索引是复合索引，必须使用该索引的第一个字段作为条件才能保证系统使用该索引，否则索引不会被引用，并且应尽可能的让字段顺序与索引顺序一致。 避免索引失效 where条件使用如下语句会导致索引失效：null、!=、<>、or、in（非要使用，可用关键字exist替代）、not in、'%abc%; 使用参数：num=@num、表达式操作：where num/2=100、函数操作：where substring（name，1,3）=‘abc’-name； --exist代替in select id from table where num exist(1,2,3,4,5,6) --where字句使用or连接条件的替代方案 select id from table where num=10 union select id from table where num=20; --连续的数值，能用between就不要用in select id from table where num between 1 and 3; --使用参数的替代方案 --如果在where子句中使用参数（num = @num;），也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引的输入项。 select id from table where num=@num; --替代为：强制查询使用索引： select id from table with(index(索引名)) where num=@num; --使用表达式的替代方案 select id from table where num/2=100; 替代为 select id from table where num=100*2; --使用函数操作的替代方案 select id from t where substring(name, 1, 3) = ’abc’–name; //以abc开头 替代为 select id from t where name like ‘abc%’；//单个百分号 2、只查询有用的字段，不要用 * 查询出所有字段 3、建表的一些优化 尽量使用数字型字段，若数据只含有数值信息尽量不要设计成字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符，而对于数字型而言只需比较一次就够了。 尽量使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。 4、切换数据库 MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表 # MySQL查询七天前的数据 今天： SELECT * FROM 表名 WHERE TO_DAYS( 时间字段名) = TO_DAYS(NOW()); 昨天： SELECT * FROM 表名 WHERE TO_DAYS( NOW() ) - TO_DAYS( 时间字段名) <= 1; 7天前： SELECT * FROM 表名 WHERE TO_DAYS( NOW() ) - TO_DAYS( 时间字段名) <= 7; // DATE_SUB() 函数从日期减去指定的时间间隔。 // DATE_SUB(date,INTERVAL expr type) // CURDATE当前日期 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名); 30天： SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名); 本月： SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ); # MySQL除了delete from 还能怎样清空整个表数据 参考：https://www.jianshu.com/p/ddc5b65e63af 如果要清空MySQL表中所有数据，可使用下面两种方法： DELETE FROM table_name TRUNCATE TABLE table_name 区别： 1、DELETE是可以带WHERE的，所以支持条件删除；而TRUNCATE只能删除整个表。 2、事务回滚 由于DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚； 而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。 3、清理速度 在数据量比较小的情况下，DELETE和TRUNCATE的清理速度差别不是很大。 但是数据量很大的时候就能看出区别。由于第二项中说的，TRUNCATE不需要支持回滚，所以使用的系统和事务日志资源少。 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项，固然会慢，但是相对来说也较安全。 4、高水位重置 随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样。 而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。 # 聚集索引跟非聚集索引的根本区别 参考：https://blog.csdn.net/weixin_30951515/article/details/113217014 1、聚集(clustered)索引，也叫聚簇索引。 定义：数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。 比如： id user age 0x01 1 小明 18 0x02 2 小红 17 0x03 3 小狗 19 ...... 0x99 99 小猫 55 第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。 结合上面的表格解释：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据， 那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。 索引的叶子节点就是对应的数据节点(MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别)， 可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询。 因此在查询方面，聚集索引的速度往往会更占优势。 2、非聚集索引 定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。 非聚集索引的二次查询问题 非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询， 而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。
MySQL表自增id用完了该怎么办？
首先表的自增 id 达到上限后，再插入数据时，申请的id值是不会改变的，此时插入数据就会报主键冲突的错误。 解决办法： 1. int 类型(4个字节，无符号 2的32次方) 改完 bigint(8个字节，无符号 2的64次方) 但是对于修改数据类型这种操作，是不支持并发的DML操作。 也就是说，如果你直接使用alter这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(delete、update、insert)。 所以，想在生产线上执行修改表结构这样的方案是不可行的。 2.如果数据量很大的情况下，我们不应该等到自增主键用完才去处理的， 数据量太大的情况下，也会影响性能，所以一般达不到最大值，就要考虑分表分库了。 3.我们也可以使用 id + uuid的联合主键
