目录：
- MySQL概述
- 安装及启动登陆mysql操作
- MySQL基本数据类型
- MySQL数据库基本SQL操作
- MySQL高级SQL操作
一、MySQL概述
1、什么是数据库？
数据库（Database）是按照来组织、和管理数据的仓库，它产生于距今六十多年前，随着和市场的发展，特别是二十世纪九十年代以后，不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种，从最简单的存储有各种数据的到能够进行海量的大型都在各个方面得到了广泛的应用。
主流的数据库有：sqlserver，mysql，Oracle、SQLite、Access、MS SQL Server等，本文主要讲述的是mysql
2、数据库管理是干什么用的？
- a. 将数据保存到文件或内存
- b. 接收特定的命令，然后对文件进行相应的操作
PS：如果有了以上管理系统，无须自己再去创建文件和文件夹，而是直接传递 命令 给上述软件，让其来进行文件操作，他们统称为数据库管理系统（DBMS，Database Management System）
二、安装及启动登陆mysql操作
1、安装步骤：
- a. 安装MySQL服务端
- b. 安装MySQL客户端
- c. 【客户端】连接【服务端】
- d. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等)
2、windows版本安装及启动：
1）下载mysql服务端：
http://dev.mysql.com/downloads/mysql/
2）解压zip
3）到对应目录执行cmd命令
cd mysql-5.7.18-winx64\bin
4）初始化数据库
mysqld --initialize-insecure
5）启动数据库
net start mysql
net stop mysql # 关闭服务器
6）连接测试：
mysql -u root -p
<输入初始化数据库的最后一行字符串>
3、linux安装（yum安装）
1）安装
yum install mysql-server
2）启动
mysql.server start
3）客户端连接
mysql -h host -u user -p
4、mysql 权限控制
mysql对于权限模块有以下限制：
1 all privileges 除grant外的所有权限 2 select 仅查权限 3 select,insert 查和插入权限 4 ... 5 usage 无访问权限 6 alter 使用alter table 7 alter routine 使用alter procedure和drop procedure 8 create 使用create table 9 create routine 使用create procedure10 create temporary tables 使用create temporary tables11 create user 使用create user、drop user、rename user和revoke all privileges12 create view 使用create view13 delete 使用delete14 drop 使用drop table15 execute 使用call和存储过程16 file 使用select into outfile 和 load data infile17 grant option 使用grant 和 revoke18 index 使用index19 insert 使用insert20 lock tables 使用lock table21 process 使用show full processlist22 select 使用select23 show databases 使用show databases24 show view 使用show view25 update 使用update26 reload 使用flush27 shutdown 使用mysqladmin shutdown(关闭MySQL)28 super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆29 replication client 服务器位置的访问30 replication slave 由复制从属使用
对于数据库及内部其他权限如下：
1 数据库名.* 数据库中的所有2 数据库名.表 指定数据库中的某张表3 数据库名.存储过程 指定数据库中的存储过程4 *.* 所有数据库
对于用户和IP的权限如下：
1 用户名@IP地址 用户只能在改IP下才能访问2 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)3 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
1）查看权限：
show grants for '用户'@'IP地址'
2）授权：
grant 权限 on 数据库.表 to '用户'@'IP地址'
3）取消授权：
revoke 权限 on 数据库.表 from '用户'@'IP地址'
4）实例：
1 grant all privileges on db1.tb1 TO '用户名'@'IP'2 grant select on db1.* TO '用户名'@'IP'3 grant select,insert on *.* TO '用户名'@'IP'4 revoke select on db1.tb1 from '用户名'@'IP'
ps.用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
flush privileges，将数据读取到内存中，从而立即生效。
密码忘记：
1 # 启动免授权服务端2 mysqld --skip-grant-tables3 4 # 客户端5 mysql -u root -p6 7 # 修改用户名密码8 update mysql.user set authentication_string=password('666') where user='root';9 flush privileges; 5、DB连接工具（略） ：SQLyog、 Navicat
三、MySQL基本数据类型
1、数值类型
a. 整型
类型 字节 范围（有符号位）
tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表示总位数
- 默认存在符号位，unsigned 属性修改
- 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
例：int(5) 插入一个数'123'，补填后为'00123'
- 在满足要求的情况下，越小越好。
- 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。
b. 浮点型
类型 字节 范围
float(单精度) 4字节 (长度越长越不精准)
double(双精度) 8字节 (双精度,精度比float稍高) 范围比float更大
decimal 精准(内部使用字符串进行存储的) -> 适合对精度有要求的
浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
不同于整型，前后均会补填0.
定义浮点型时，需指定总位数和小数位数。
float(M, D) double(M, D)
M表示总位数，D表示小数位数。
M和D的大小会决定浮点数的范围。不同于整型的固定范围。
M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
支持科学计数法表示。
浮点数表示近似值。
c. 定点数
decimal -- 可变长度
decimal(M, D) M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。
2. 字符串类型
a. char, varchar
char 定长字符串，速度快，但浪费空间
varchar 变长字符串，速度慢，但节省空间
M表示能存储的最大长度，此长度是字符数，非字节数。
不同的编码，所占用的空间不同。
char,最多255个字符，与编码无关。
varchar,最多65535字符，与编码有关。
一条有效记录最大不能超过65535个字节。
utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符
varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。
例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3
b. blob, text
blob 二进制字符串（字节字符串）
tinyblob, blob, mediumblob, longblob
text 非二进制字符串（字符字符串）
tinytext, text, mediumtext, longtext
text 在定义时，不需要定义长度，也不会计算总长度。
text 类型在定义时，不可给default值
c. binary, varbinary
类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.
3、 日期时间类型
一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节 日期 1000-01-01 到 9999-12-31
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
datetime “YYYY-MM-DD hh:mm:ss”
timestamp “YY-MM-DD hh:mm:ss”
“YYYYMMDDhhmmss”
“YYMMDDhhmmss”
YYYYMMDDhhmmss
YYMMDDhhmmss
date “YYYY-MM-DD”
“YY-MM-DD”
“YYYYMMDD”
“YYMMDD”
YYYYMMDD
YYMMDD
time “hh:mm:ss”
“hhmmss”
hhmmss
year “YYYY”
“YY”
YYYY
YY
4、 枚举和集合
枚举(enum)
enum(val1, val2, val3...)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
表现为字符串类型，存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。
集合（set）
set(val1, val2, val3...)
create table tab ( gender set('男', '女', '无') );
insert into tab values ('男, 女');
最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
当创建表时，SET成员值的尾部空格将自动被删除。
四、MySQL基本SQL操作
1、数据库操作sql（databases）
1）显示数据库
SHOW DATABASES;
2）创建数据库
1 # utf-82 CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;3 4 # gbk5 CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
ps.字符编码
1 /* 字符集编码 */ ------------------ 2 -- MySQL、数据库、表、字段均可设置编码 3 -- 数据编码与客户端编码不需一致 4 SHOW VARIABLES LIKE 'character_set_%' -- 查看所有字符集编码项 5 character_set_client 客户端向服务器发送数据时使用的编码 6 character_set_results 服务器端将结果返回给客户端所使用的编码 7 character_set_connection 连接层编码 8 SET 变量名 = 变量值 9 set character_set_client = gbk;10 set character_set_results = gbk;11 set character_set_connection = gbk;12 SET NAMES GBK; -- 相当于完成以上三个设置13 -- 校对集14 校对集用以排序15 SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 查看所有字符集16 SHOW COLLATION [LIKE 'pattern'] 查看所有校对集17 charset 字符集编码 设置字符集编码18 collate 校对集编码 设置校对集编码
3)进入输入库
USE db_name;
2、数据表操作sql（table）
1）创建表
1 create table 表名( 2 列名 类型 是否可以为空， 3 列名 类型 是否可以为空 4 )ENGINE=InnoDB DEFAULT CHARSET=utf8 5 6 /* 7 是否可空，null表示空，非字符串 8 not null - 不可空 9 null - 可空10 */
1 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值2 create table tb1(3 nid int not null defalut 2,4 num int not null5 )6
1 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 2 create table tb1( 3 nid int not null auto_increment primary key, 4 num int null 5 ) 6 或 7 create table tb1( 8 nid int not null, 9 num int not null,10 primary key(nid,num)11 )
1 外键，一个特殊的索引，只能是指定内容 2 creat table color( 3 nid int not null primary key, 4 name char(16) not null 5 ) 6 7 create table fruit( 8 nid int not null primary key, 9 smt char(32) null ,10 color_id int not null,11 constraint fk_cc foreign key (color_id) references color(nid)12 )
2）删除表
drop table 表名
3）清空表
delete from 表名 -- 比较慢
truncate table 表名 -- 比较快
4）修改表
1 添加列：alter table 表名 add 列名 类型 2 删除列：alter table 表名 drop column 列名 3 修改列： 4 alter table 表名 modify column 列名 类型; -- 类型 5 alter table 表名 change 原列名 新列名 类型; -- 列名，类型 6 7 添加主键： 8 alter table 表名 add primary key(列名); 9 删除主键：10 alter table 表名 drop primary key;11 alter table 表名 modify 列名 int, drop primary key;12 13 添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);14 删除外键：alter table 表名 drop foreign key 外键名称15 16 修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;17 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5）其他说明（补充）
1 /* 表的操作 */ ------------------ 2 -- 创建表 3 create [temporary] table[ if not exists] [库名.]表名 ( 表的结构定义 )[ 表选项] 4 每个字段必须有数据类型 5 最后一个字段后不能有逗号 6 temporary 临时表，会话结束时表自动消失 7 对于字段的定义： 8 字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] 9 -- 表选项10 -- 字符集11 CHARSET = charset_name12 如果表没有设定，则使用数据库字符集13 -- 存储引擎14 ENGINE = engine_name 15 表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同16 常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive17 不同的引擎在保存表的结构和数据时采用不同的方式18 MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引19 InnoDB表文件含义：.frm表定义，表空间数据和日志文件20 SHOW ENGINES -- 显示存储引擎的状态信息21 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息22 -- 数据文件目录23 DATA DIRECTORY = '目录'24 -- 索引文件目录25 INDEX DIRECTORY = '目录'26 -- 表注释27 COMMENT = 'string'28 -- 分区选项29 PARTITION BY ... (详细见手册)30 -- 查看所有表31 SHOW TABLES[ LIKE 'pattern']32 SHOW TABLES FROM 表名33 -- 查看表机构34 SHOW CREATE TABLE 表名 （信息更详细）35 DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']36 SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']37 -- 修改表38 -- 修改表本身的选项39 ALTER TABLE 表名 表的选项40 EG: ALTER TABLE 表名 ENGINE=MYISAM;41 -- 对表进行重命名42 RENAME TABLE 原表名 TO 新表名43 RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）44 -- RENAME可以交换两个表名45 -- 修改表的字段机构46 ALTER TABLE 表名 操作名47 -- 操作名48 ADD[ COLUMN] 字段名 -- 增加字段49 AFTER 字段名 -- 表示增加在该字段名后面50 FIRST -- 表示增加在第一个51 ADD PRIMARY KEY(字段名) -- 创建主键52 ADD UNIQUE [索引名] (字段名)-- 创建唯一索引53 ADD INDEX [索引名] (字段名) -- 创建普通索引54 ADD 55 DROP[ COLUMN] 字段名 -- 删除字段56 MODIFY[ COLUMN] 字段名 字段属性 -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)57 CHANGE[ COLUMN] 原字段名 新字段名 字段属性 -- 支持对字段名修改58 DROP PRIMARY KEY -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)59 DROP INDEX 索引名 -- 删除索引60 DROP FOREIGN KEY 外键 -- 删除外键61 62 -- 删除表63 DROP TABLE[ IF EXISTS] 表名 ...64 -- 清空表数据65 TRUNCATE [TABLE] 表名66 -- 复制表结构67 CREATE TABLE 表名 LIKE 要复制的表名68 -- 复制表结构和数据69 CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名70 -- 检查表是否有错误71 CHECK TABLE tbl_name [, tbl_name] ... [option] ...72 -- 优化表73 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...74 -- 修复表75 REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]76 -- 分析表77 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... ps.主键、外键（列属性（列约束））
/* 列属性（列约束） */ ------------------1. 主键 - 能唯一标识记录的字段，可以作为主键。 - 一个表只能有一个主键。 - 主键具有唯一性。 - 声明字段时，用 primary key 标识。 也可以在字段列表之后声明 例：create table tab ( id int, stu varchar(10), primary key (id)); - 主键字段的值不能为null。 - 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。 例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));2. unique 唯一索引（唯一约束） 使得某字段的值也不能重复。 3. null 约束 null不是数据类型，是列的一个属性。 表示当前列是否可以为null，表示什么都没有。 null, 允许为空。默认。 not null, 不允许为空。 insert into tab values (null, 'val'); -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null 4. default 默认值属性 当前字段的默认值。 insert into tab values (default, 'val'); -- 此时表示强制使用默认值。 create table tab ( add_time timestamp default current_timestamp ); -- 表示将当前时间的时间戳设为默认值。 current_date, current_time5. auto_increment 自动增长约束 自动增长必须为索引（主键或unique） 只能存在一个字段为自动增长。 默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;6. comment 注释 例：create table tab ( id int ) comment '注释内容';7. foreign key 外键约束 用于限制主表与从表数据完整性。 alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id); -- 将表t1的t1_id外键关联到表t2的id字段。 -- 每个外键都有一个名字，可以通过 constraint 指定 存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。 作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。 MySQL中，可以对InnoDB引擎使用外键约束： 语法： foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作] 此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。 可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。 如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择： 1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。 2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。 3. restrict，拒绝父表删除和更新。 注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。/* 建表规范 */ ------------------ -- Normal Format, NF - 每个表保存一个实体信息 - 每个具有一个ID字段作为主键 - ID主键 + 原子表 -- 1NF, 第一范式 字段不能再分，就满足第一范式。 -- 2NF, 第二范式 满足第一范式的前提下，不能出现部分依赖。 消除符合主键就可以避免部分依赖。增加单列关键字。 -- 3NF, 第三范式 满足第二范式的前提下，不能出现传递依赖。 某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。 将一个实体信息的数据放在一个表内实现。
3、数据操作sql（增、删、改、查）
1）增加
1 insert into 表 (列名,列名...) values (值,值,值...)2 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)3 insert into 表 (列名,列名...) select (列名,列名...) from 表
2）删除
1 delete from 表2 delete from 表 where id＝1 and name＝'xxx'
3）修改
1 update 表 set name ＝ 'alex' where id>1
4）查询
1 select * from 表2 select * from 表 where id > 13 select nid,name,gender as gg from 表 where id > 1
5、select详解
1）group by分组，having条件，order by排序， limit 限制结果集
1 select [all|distinct] select_expr from -> where -> group by [合计函数] -> having -> order by -> limit 2 3 a. select_expr 4 -- 可以用 * 表示所有字段。 5 select * from tb; 6 -- 可以使用表达式（计算公式、函数调用、字段也是个表达式） 7 select stu, 29+25, now() from tb; 8 -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。 9 - 使用 as 关键字，也可省略 as.10 select stu+10 as add10 from tb;11 12 b. from 子句13 用于标识查询来源。14 -- 可以为表起别名。使用as关键字。15 select * from tb1 as tt, tb2 as bb;16 -- from子句后，可以同时出现多个表。17 -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。18 select * from tb1, tb2;19 20 c. where 子句21 -- 从from获得的数据源中进行筛选。22 -- 整型1表示真，0表示假。23 -- 表达式由运算符和运算数组成。24 -- 运算数：变量（字段）、值、函数返回值25 -- 运算符：26 =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, 27 in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor28 is/is not 加上ture/false/unknown，检验某个值的真假29 <=>与<>功能相同，<=>可用于null比较30 31 d. group by 子句, 分组子句32 group by 字段/别名 [排序方式]33 分组后会进行排序。升序：ASC，降序：DESC34 35 以下[合计函数]需配合 group by 使用：36 count 返回不同的非NULL值数目 count(*)、count(字段)37 sum 求和38 max 求最大值39 min 求最小值40 avg 求平均值41 group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。42 43 e. having 子句，条件子句44 与 where 功能、用法相同，执行时机不同。45 where 在开始时执行检测数据，对原数据进行过滤。46 having 对筛选出的结果再次进行过滤。47 having 字段必须是查询出来的，where 字段必须是数据表存在的。48 where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。49 where 不可以使用合计函数。一般需用合计函数才会用 having50 SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。51 52 f. order by 子句，排序子句53 order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...54 升序：ASC，降序：DESC55 支持多个字段的排序。56 57 g. limit 子句，限制结果数量子句58 仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。59 limit 起始位置, 获取条数60 省略第一个参数，表示从索引0开始。limit 获取条数61 62 h. distinct, all 选项63 distinct 去除重复记录64 默认为 all, 全部记录
2）union将多个select查询的结果组合成一个结果集合。
1 /* UNION */ ------------------ 2 将多个select查询的结果组合成一个结果集合。 3 SELECT ... UNION [ALL|DISTINCT] SELECT ... 4 默认 DISTINCT 方式，即所有返回的行都是唯一的 5 建议，对每个SELECT查询加上小括号包裹。 6 ORDER BY 排序时，需加上 LIMIT 进行结合。 7 需要各select查询的字段数量一样。 8 每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准。 9 10 11 /* 子查询 */ ------------------12 - 子查询需用括号包裹。13 -- from型14 from后要求是一个表，必须给子查询结果取个别名。15 - 简化每个查询内的条件。16 - from型需将结果生成一个临时表格，可用以原表的锁定的释放。17 - 子查询返回一个表，表型子查询。18 select * from (select * from tb where id>0) as subfrom where id>1;19 -- where型20 - 子查询返回一个值，标量子查询。21 - 不需要给子查询取别名。22 - where子查询内的表，不能直接用以更新。23 select * from tb where money = (select max(money) from tb);24 -- 列子查询25 如果子查询结果返回的是一列。26 使用 in 或 not in 完成查询27 exists 和 not exists 条件28 如果子查询返回数据，则返回1或0。常用于判断条件。29 select column1 from t1 where exists (select * from t2);30 -- 行子查询31 查询条件是一个行。32 select * from t1 where (id, gender) in (select id, gender from t2);33 行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...)34 行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。35 36 -- 特殊运算符37 != all() 相当于 not in38 = some() 相当于 in。any 是 some 的别名39 != some() 不等同于 not in，不等于其中某一个。40 all, some 可以配合其他运算符一起使用。
3）join 连接查询
1 /* 连接查询(join) */ ------------------ 2 将多个表的字段进行连接，可以指定连接条件。 3 -- 内连接(inner join) 4 - 默认就是内连接，可省略inner。 5 - 只有数据存在时才能发送连接。即连接结果不能出现空行。 6 on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真） 7 也可用where表示连接条件。 8 还有 using, 但需字段名相同。 using(字段名) 9 10 -- 交叉连接 cross join11 即，没有条件的内连接。12 select * from tb1 cross join tb2;13 -- 外连接(outer join)14 - 如果数据不存在，也会出现在连接结果中。15 -- 左外连接 left join16 如果数据不存在，左表记录会出现，而右表为null填充17 -- 右外连接 right join18 如果数据不存在，右表记录会出现，而左表为null填充19 -- 自然连接(natural join)20 自动判断连接条件完成连接。21 相当于省略了using，会自动查找相同字段名。22 natural join23 natural left join24 natural right join
4）示例
1 a、条件 2 select * from 表 where id > 1 and name != 'alex' and num = 12; 3 4 select * from 表 where id between 5 and 16; 5 6 select * from 表 where id in (11,22,33) 7 select * from 表 where id not in (11,22,33) 8 select * from 表 where id in (select nid from 表) 9 10 b、通配符11 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串）12 select * from 表 where name like 'ale_' - ale开头的所有（一个字符）13 14 c、限制15 select * from 表 limit 5; - 前5行16 select * from 表 limit 4,5; - 从第4行开始的5行17 select * from 表 limit 5 offset 4 - 从第4行开始的5行18 19 d、排序20 select * from 表 order by 列 asc - 根据 “列” 从小到大排列21 select * from 表 order by 列 desc - 根据 “列” 从大到小排列22 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序23 24 e、分组25 select num from 表 group by num26 select num,nid from 表 group by num,nid27 select num,nid from 表 where nid > 10 group by num,nid order nid desc28 select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid29 30 select num from 表 group by num having max(id) > 1031 32 特别的：group by 必须在where之后，order by之前33 34 f、连表35 无对应关系则不显示36 select A.num, A.name, B.name37 from A,B38 Where A.nid = B.nid39 40 无对应关系则不显示41 select A.num, A.name, B.name42 from A inner join B43 on A.nid = B.nid44 45 A表所有显示，如果B中无对应关系，则值为null46 select A.num, A.name, B.name47 from A left join B48 on A.nid = B.nid49 50 B表所有显示，如果B中无对应关系，则值为null51 select A.num, A.name, B.name52 from A right join B53 on A.nid = B.nid54 55 g、组合56 组合，自动处理重合57 select nickname58 from A59 union60 select name61 from B62 63 组合，不处理重合64 select nickname65 from A66 union all67 select name68 from B
五、MySQL高级SQL操作
1、视图（view）
1）什么是视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图具有表结构文件，但不存在数据文件。 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。 视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。2）创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- 视图名必须唯一，同时不能与表重名。 - 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。 - 可以指定视图执行的算法，通过ALGORITHM指定。 - column_list如果存在，则数目必须等于SELECT语句检索的列数 1 --格式：CREATE VIEW 视图名称 AS SQL语句2 CREATE VIEW v1 AS 3 SELET nid, 4 name5 FROM6 A7 WHERE8 nid > 4
3）查看结构
SHOW CREATE VIEW view_name
4）删除视图
- 删除视图后，数据依然存在。
- 可同时删除多个视图。 DROP VIEW [IF EXISTS] view_name ... 1 --格式：DROP VIEW 视图名称2 3 DROP VIEW v1
5）修改视图结构
- 一般不修改视图，因为不是所有的更新视图都会映射到表上。
ALTER VIEW view_name [(column_list)] AS select_statement 1 -- 格式：ALTER VIEW 视图名称 AS SQL语句 2 3 ALTER VIEW v1 AS 4 SELET A.nid, 5 B. NAME 6 FROM 7 A 8 LEFT JOIN B ON A.id = B.nid 9 LEFT JOIN C ON A.id = C.nid10 WHERE11 A.id > 212 AND C.nid < 5
6）使用视图
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
1 select * from v1
7）视图作用：简化业务逻辑、对客户端隐藏真实的表结构
8）视图算法(ALGORITHM)
MERGE 合并
将视图的查询语句，与外部查询需要先合并再执行！TEMPTABLE 临时表 将视图执行完毕后，形成临时表，再做外层查询！UNDEFINED 未定义(默认)，指的是MySQL自主去选择相应的算法。2、触发器（triggered）
1）triggered定义
触发程序是与表有关的命名数据库对象，当该表出现特定事件时，将激活该对象
监听：记录的增加、修改、删除。即，对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。2）语法结构：
1 -- 创建触发器 2 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 3 参数： 4 trigger_time是触发程序的动作时间。它可以是 before 或 after，以指明触发程序是在激活它的语句之前或之后触发。 5 trigger_event指明了激活触发程序的语句的类型 6 INSERT：将新行插入表时激活触发程序 7 UPDATE：更改某一行时激活触发程序 8 DELETE：从表中删除某一行时激活触发程序 9 tbl_name：监听的表，必须是永久性的表，不能将触发程序与TEMPORARY表或视图关联起来。10 trigger_stmt：当触发程序激活时执行的语句。执行多个语句，可使用BEGIN...END复合语句结构11 12 -- 删除13 DROP TRIGGER [schema_name.]trigger_name14 15 可以使用old和new代替旧的和新的数据16 更新操作，更新前是old，更新后是new.17 删除操作，只有old.18 增加操作，只有new.19 20 -- 注意21 1. 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
3）示例
1 1、创建基本语法 2 3 # 插入前 4 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW 5 BEGIN 6 ... 7 END 8 9 # 插入后10 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW11 BEGIN12 ...13 END14 15 # 删除前16 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW17 BEGIN18 ...19 END20 21 # 删除后22 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW23 BEGIN24 ...25 END26 27 # 更新前28 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW29 BEGIN30 ...31 END32 33 # 更新后34 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW35 BEGIN36 ...37 END38 39 40 # 插入前触发器41 delimiter //42 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW43 BEGIN44 45 IF NEW. NAME == 'alex' THEN46 INSERT INTO tb2 (NAME)47 VALUES48 ('aa')49 END50 END//51 delimiter ;52 53 54 # 插入后触发器55 delimiter //56 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW57 BEGIN58 IF NEW. num = 666 THEN59 INSERT INTO tb2 (NAME)60 VALUES61 ('666'),62 ('666') ;63 ELSEIF NEW. num = 555 THEN64 INSERT INTO tb2 (NAME)65 VALUES66 ('555'),67 ('555') ;68 END IF;69 END//70 delimiter ;71 特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。72 73 2、删除触发器74 DROP TRIGGER tri_after_insert_tb1;75 76 3、使用触发器77 触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。78 insert into tb1(num) values(666) 3、存储过程（procedure）
1）什么是存储过程
存储存储过程 是一段代码（过程），存储在数据库中的sql组成。
一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。即，存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
2）语法结构
1 -- 创建 2 CREATE PROCEDURE sp_name (参数列表) 3 过程体 4 5 参数列表：不同于函数的参数列表，需要指明参数类型 6 IN，表示输入型 7 OUT，表示输出型 8 INOUT，表示混合型 9 10 注意，没有返回值。11 12 13 /* 存储过程 */ ------------------14 存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。15 调用：CALL 过程名16 -- 注意17 - 没有返回值。18 - 只能单独调用，不可夹杂在其他语句中19 20 -- 参数21 IN|OUT|INOUT 参数名 数据类型22 IN 输入：在调用过程中，将数据输入到过程体内部的参数23 OUT 输出：在调用过程中，将过程体处理完的结果返回到客户端24 INOUT 输入输出：既可输入，也可输出25 26 -- 语法27 CREATE PROCEDURE 过程名 (参数列表)28 BEGIN29 过程体30 END
3）示例
1 1、创建存储过程 2 -- 创建存储过程 3 无参数存储过程 4 delimiter // 5 create procedure p1() 6 BEGIN 7 select * from t1; 8 END// 9 delimiter ; 10 11 12 13 -- 执行存储过程 14 15 call p1() 16 17 /* 18 对于存储过程，可以接收参数，其参数有三类： 19 20 in 仅用于传入参数用 21 out 仅用于返回值用 22 inout 既可以传入又可以当作返回值 23 */ 24 # 有参数的存储过程 25 -- 创建存储过程 26 delimiter \\ 27 create procedure p1( 28 in i1 int, 29 in i2 int, 30 inout i3 int, 31 out r1 int 32 ) 33 BEGIN 34 DECLARE temp1 int; 35 DECLARE temp2 int default 0; 36 37 set temp1 = 1; 38 39 set r1 = i1 + i2 + temp1 + temp2; 40 41 set i3 = i3 + 100; 42 43 end\\ 44 delimiter ; 45 46 -- 执行存储过程 47 1. 结果集 48 set @t1 =4; 49 set @t2 = 0; 50 CALL p1 (1, 2 ,@t1, @t2); 51 SELECT @t1,@t2; 52 53 delimiter // 54 create procedure p1() 55 begin 56 select * from v1; 57 end // 58 delimiter ; 59 60 61 2. 结果集+out值 62 delimiter // 63 create procedure p2( 64 in n1 int, 65 inout n3 int, 66 out n2 int, 67 ) 68 begin 69 declare temp1 int ; 70 declare temp2 int default 0; 71 72 select * from v1; 73 set n2 = n1 + 100; 74 set n3 = n3 + n1 + 100; 75 end // 76 delimiter ; 77 78 3. 事务 79 delimiter \\ 80 create PROCEDURE p1( 81 OUT p_return_code tinyint 82 ) 83 BEGIN 84 DECLARE exit handler for sqlexception 85 BEGIN 86 -- ERROR 87 set p_return_code = 1; 88 rollback; 89 END; 90 91 DECLARE exit handler for sqlwarning 92 BEGIN 93 -- WARNING 94 set p_return_code = 2; 95 rollback; 96 END; 97 98 START TRANSACTION; 99 DELETE from tb1;100 insert into tb2(name)values('seven');101 COMMIT; 102 103 -- SUCCESS 104 set p_return_code = 0; 105 106 END\\107 delimiter ;108 109 4. 游标110 delimiter //111 create procedure p3()112 begin 113 declare ssid int; -- 自定义变量1 114 declare ssname varchar(50); -- 自定义变量2 115 DECLARE done INT DEFAULT FALSE;116 117 118 DECLARE my_cursor CURSOR FOR select sid,sname from student;119 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;120 121 open my_cursor;122 xxoo: LOOP123 fetch my_cursor into ssid,ssname;124 if done then 125 leave xxoo;126 END IF;127 insert into teacher(tname) values(ssname);128 end loop xxoo;129 close my_cursor;130 end //131 delimter ;132 133 5. 动态执行SQL134 delimiter \\135 CREATE PROCEDURE p4 (136 in nid int137 )138 BEGIN139 PREPARE prod FROM 'select * from student where sid > ?';140 EXECUTE prod USING @nid;141 DEALLOCATE prepare prod; 142 END\\143 delimiter ;144 145 146 2、删除存储过程147 drop procedure proc_name;148 149 3、执行存储过程150 -- 无参数151 call proc_name()152 153 -- 有参数，全in154 call proc_name(1,2)155 156 -- 有参数，有in，out，inout157 set @t1=0;158 set @t2=3;159 call proc_name(1,2,@t1,@t2) 4）python之pymysql模块调用
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 import pymysql 4 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') 6 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 7 # 执行存储过程 8 cursor.callproc('p1', args=(1, 22, 3, 4)) 9 # 获取执行完存储的参数10 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")11 result = cursor.fetchall()12 13 conn.commit()14 cursor.close()15 conn.close()16 17 18 print(result) 4、函数（function）
1）字符连接函数、if、case、while、变量
1 -- 字符连接函数 2 concat(str1[, str2,...]) 3 4 -- 分支语句 5 if 条件 then 6 执行语句 7 elseif 条件 then 8 执行语句 9 else10 执行语句11 end if;12 13 -- 修改最外层语句结束符14 delimiter 自定义结束符号15 SQL语句16 自定义结束符号17 18 delimiter ; -- 修改回原来的分号19 20 -- 语句块包裹21 begin22 语句块23 end24 25 -- 特殊的执行26 1. 只要添加记录，就会触发程序。27 2. Insert into on duplicate key update 语法会触发：28 如果没有重复记录，会触发 before insert, after insert;29 如果有重复记录并更新，会触发 before insert, before update, after update;30 如果有重复记录但是没有发生更新，则触发 before insert, before update31 3. Replace 语法 如果有记录，则执行 before insert, before delete, after delete, after insert32 33 34 /* SQL编程 */ ------------------35 36 --// 局部变量 ----------37 -- 变量声明38 declare var_name[,...] type [default value] 39 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。 40 41 -- 赋值42 使用 set 和 select into 语句为变量赋值。43 44 - 注意：在函数内是可以使用全局变量（用户自定义的变量）45 46 47 --// 全局变量 ----------48 -- 定义、赋值49 set 语句可以定义并为变量赋值。50 set @var = value;51 也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。52 还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。53 select @var:=20;54 select @v1:=id, @v2=name from t1 limit 1;55 select * from tbl_name where @var:=30;56 57 select into 可以将表中查询获得的数据赋给变量。58 -| select max(height) into @max_height from tb;59 60 -- 自定义变量名61 为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。62 @var=10;63 64 - 变量被定义后，在整个会话周期都有效（登录到退出）65 66 67 --// 控制结构 ----------68 -- if语句69 if search_condition then 70 statement_list 71 [elseif search_condition then72 statement_list]73 ...74 [else75 statement_list]76 end if;77 78 -- case语句79 CASE value WHEN [compare-value] THEN result80 [WHEN [compare-value] THEN result ...]81 [ELSE result]82 END83 84 85 -- while循环86 [begin_label:] while search_condition do87 statement_list88 end while [end_label];89 90 - 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。91 92 -- 退出循环93 退出整个循环 leave94 退出当前循环 iterate95 通过退出的标签决定退出哪个循环
2）内置函数
1 --// 内置函数 ---------- 2 -- 数值函数 3 abs(x) -- 绝对值 abs(-10.9) = 10 4 format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46 5 ceil(x) -- 向上取整 ceil(10.1) = 11 6 floor(x) -- 向下取整 floor (10.1) = 10 7 round(x) -- 四舍五入去整 8 mod(m, n) -- m%n m mod n 求余 10%3=1 9 pi() -- 获得圆周率10 pow(m, n) -- m^n11 sqrt(x) -- 算术平方根12 rand() -- 随机数13 truncate(x, d) -- 截取d位小数14 15 -- 时间日期函数16 now(), current_timestamp(); -- 当前日期时间17 current_date(); -- 当前日期18 current_time(); -- 当前时间19 date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分20 time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分21 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间22 unix_timestamp(); -- 获得unix时间戳23 from_unixtime(); -- 从时间戳获得时间24 25 -- 字符串函数26 length(string) -- string长度，字节27 char_length(string) -- string的字符个数28 substring(str, position [,length]) -- 从str的position开始,取length个字符29 replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str30 instr(string ,substring) -- 返回substring首次在string中出现的位置31 concat(string [,...]) -- 连接字串32 charset(str) -- 返回字串字符集33 lcase(string) -- 转换成小写34 left(string, length) -- 从string2中的左边起取length个字符35 load_file(file_name) -- 从文件读取内容36 locate(substring, string [,start_position]) -- 同instr,但可指定开始位置37 lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length38 ltrim(string) -- 去除前端空格39 repeat(string, count) -- 重复count次40 rpad(string, length, pad) --在str后用pad补充,直到长度为length41 rtrim(string) -- 去除后端空格42 strcmp(string1 ,string2) -- 逐字符比较两字串大小43 44 -- 流程函数45 case when [condition] then result [when [condition] then result ...] [else result] end 多分支46 if(expr1,expr2,expr3) 双分支。47 48 -- 聚合函数49 count()50 sum();51 max();52 min();53 avg();54 group_concat()55 56 -- 其他常用函数57 md5();58 default(); 3）自定义函数结构
1 --// 存储函数，自定义函数 ---------- 2 -- 新建 3 CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型 4 函数体 5 6 - 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。 7 - 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。 8 - 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。 9 - 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。10 - 多条语句应该使用 begin...end 语句块包含。11 - 一定要有 return 返回值语句。12 13 -- 删除14 DROP FUNCTION [IF EXISTS] function_name;15 16 -- 查看17 SHOW FUNCTION STATUS LIKE 'partten'18 SHOW CREATE FUNCTION function_name;19 20 -- 修改21 ALTER FUNCTION function_name 函数选项
4）自定义函数示例
1 1、自定义函数 2 delimiter \\ 3 create function f1( 4 i1 int, 5 i2 int) 6 returns int 7 BEGIN 8 declare num int; 9 set num = i1 + i2;10 return(num);11 END \\12 delimiter ;13 14 2、删除函数15 drop function func_name;16 17 3、执行函数18 # 获取返回值19 declare @i VARCHAR(32);20 select UPPER('alex') into @i;21 SELECT @i;22 23 24 # 在查询中使用25 select f1(11,nid) ,name from tb2; 5、事物（transaction）
1）事物的定义：
事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。 - 支持连续SQL的集体成功或集体撤销。 - 事务是数据库在数据晚自习方面的一个功能。 - 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。 - InnoDB被称为事务安全型引擎。 事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
-- 事务开启 START TRANSACTION; 或者 BEGIN; 开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。-- 事务提交 COMMIT;-- 事务回滚 ROLLBACK; 如果部分操作发生问题，映射到事务开启前。-- 事务的特性 1. 原子性（Atomicity） 事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 2. 一致性（Consistency） 事务前后数据的完整性必须保持一致。 - 事务开始和结束时，外部数据一致 - 在整个事务过程中，操作是连续的 3. 隔离性（Isolation） 多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。 4. 持久性（Durability） 一个事务一旦被提交，它对数据库中的数据改变就是永久性的。-- 事务的实现 1. 要求是事务支持的表类型 2. 执行一组相关的操作前开启事务 3. 整组操作完成后，都成功，则提交；如果存在失败，选择回滚，则会回到事务开始的备份点。-- 事务的原理 利用InnoDB的自动提交(autocommit)特性完成。 普通的MySQL执行语句后，当前的数据提交操作均可被其他客户端可见。 而事务是暂时关闭“自动提交”机制，需要commit提交持久化数据操作。-- 注意 1. 数据定义语言（DDL）语句不能被回滚，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。 2. 事务不能被嵌套-- 保存点 SAVEPOINT 保存点名称 -- 设置一个事务保存点 ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点 RELEASE SAVEPOINT 保存点名称 -- 删除保存点-- InnoDB自动提交特性设置 SET autocommit = 0|1; 0表示关闭自动提交，1表示开启自动提交。 - 如果关闭了，那普通操作的结果对其他客户端也不可见，需要commit提交后才能持久化数据操作。 - 也可以关闭自动提交来开启事务。但与START TRANSACTION不同的是， SET autocommit是永久改变服务器的设置，直到下次再次修改该设置。(针对当前连接) 而START TRANSACTION记录开启前的状态，而一旦事务提交或回滚后就需要再次开启事务。(针对当前事务)
2）支持事务的存储过程
1 delimiter \\ 2 create PROCEDURE p1( 3 OUT p_return_code tinyint 4 ) 5 BEGIN 6 DECLARE exit handler for sqlexception 7 BEGIN 8 -- ERROR 9 set p_return_code = 1; 10 rollback; 11 END; 12 13 DECLARE exit handler for sqlwarning 14 BEGIN 15 -- WARNING 16 set p_return_code = 2; 17 rollback; 18 END; 19 20 START TRANSACTION; 21 DELETE from tb1;22 insert into tb2(name)values('seven');23 COMMIT; 24 25 -- SUCCESS 26 set p_return_code = 0; 27 28 END\\29 delimiter ; 6、索引（index）
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
1）普通索引
普通索引仅有一个功能：加速查询
1 1、创建表+索引 2 create table in1( 3 nid int not null auto_increment primary key, 4 name varchar(32) not null, 5 email varchar(64) not null, 6 extra text, 7 index ix_name (name) 8 ) 9 10 2、创建索引11 create index index_name on table_name(column_name)12 13 3、删除索引14 drop index_name on table_name;15 16 4、查看索引17 show index from table_name;
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。
1 create index ix_extra on in1(extra(32));
2）唯一索引
唯一索引有两个功能：加速查询 和 唯一约束（可含null）
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 unique ix_name (name) 7 ) 8 9 2、创建唯一索引10 create unique index 索引名 on 表名(列名)11 12 3、删除唯一索引13 drop unique index 索引名 on 表名
3）主键索引
主键有两个功能：加速查询 和 唯一约束（不可含null）
1 1、创建表+主键索引 2 create table in1( 3 nid int not null auto_increment primary key, 4 name varchar(32) not null, 5 email varchar(64) not null, 6 extra text, 7 index ix_name (name) 8 ) 9 10 OR11 12 create table in1(13 nid int not null auto_increment,14 name varchar(32) not null,15 email varchar(64) not null,16 extra text,17 primary key(ni1),18 index ix_name (name)19 )20 21 2、创建主键索引22 alter table 表名 add primary key(列名);23 24 3、删除主键索引25 alter table 表名 drop primary key;26 alter table 表名 modify 列名 int, drop primary key;
4）组合索引
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666
1 1、创建表+组合索引 2 create table in3( 3 nid int not null auto_increment primary key, 4 name varchar(32) not null, 5 email varchar(64) not null, 6 extra text 7 ) 8 9 2、创建组合索引10 create index ix_name_email on in3(name,email);
创建组合索引之后，查询：
- name and email -- 使用索引
- name -- 使用索引
- email -- 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。
7、杂项
1 /* 杂项 */ ------------------ 2 1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！ 3 2. 每个库目录存在一个保存当前数据库的选项文件db.opt。 4 3. 注释： 5 单行注释 # 注释内容 6 多行注释 /* 注释内容 */ 7 单行注释 -- 注释内容 (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）) 8 4. 模式通配符： 9 _ 任意单个字符10 % 任意多个字符，甚至包括零字符11 单引号需要进行转义 \'12 5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。13 6. SQL对大小写不敏感14 7. 清除已有语句：\c
1）锁表
/* 锁表 */表锁定只用于防止其它客户端进行不正当地读取和写入MyISAM 支持表锁，InnoDB 支持行锁-- 锁定 LOCK TABLES tbl_name [AS alias]-- 解锁 UNLOCK TABLES
2）备份恢复
1 /* 备份与还原 */ ------------------ 2 备份，将数据的结构与表内数据保存起来。 3 利用 mysqldump 指令完成。 4 5 -- 导出 6 1. 导出一张表 7 mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 8 2. 导出多张表 9 mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)10 3. 导出所有表11 mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)12 4. 导出一个库 13 mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)14 15 可以-w携带备份条件16 17 -- 导入18 1. 在登录mysql的情况下：19 source 备份文件20 2. 在不登录的情况下21 mysql -u用户名 -p密码 库名 < 备份文件
3）导入导出
1 /* 导入导出 */ ------------------ 2 select * into outfile 文件地址 [控制格式] from 表名; -- 导出表数据 3 load data [local] infile 文件地址 [replace|ignore] into table 表名 [控制格式]; -- 导入数据 4 生成的数据默认的分隔符是制表符 5 local未指定，则数据文件必须在服务器上 6 replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理 7 -- 控制格式 8 fields 控制字段格式 9 默认：fields terminated by '\t' enclosed by '' escaped by '\\'10 terminated by 'string' -- 终止11 enclosed by 'char' -- 包裹12 escaped by 'char' -- 转义13 -- 示例：14 SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'15 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'16 LINES TERMINATED BY '\n'17 FROM test_table;18 lines 控制行格式19 默认：lines terminated by '\n'20 terminated by 'string' -- 终止21 22 /* insert */ ------------------23 select语句获得的数据可以用insert插入。24 25 可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。26 或者使用set语法。27 insert into tbl_name set field=value,...；28 29 可以一次性使用多个值，采用(), (), ();的形式。30 insert into tbl_name values (), (), ();31 32 可以在列值指定时，使用表达式。33 insert into tbl_name values (field_value, 10+10, now());34 可以使用一个特殊值 default，表示该列使用默认值。35 insert into tbl_name values (field_value, default);36 37 可以通过一个查询的结果，作为需要插入的值。38 insert into tbl_name select ...;39 40 可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。41 insert into tbl_name values/set/select on duplicate key update 字段=值, …;42 43 /* delete */ ------------------44 DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]45 46 按照条件删除47 48 指定删除的最多记录数。Limit49 50 可以通过排序条件删除。order by + limit51 52 支持多表删除，使用类似连接语法。53 delete from 需要删除数据多表1，表2 using 表连接操作 条件。54 55 /* truncate */ ------------------56 TRUNCATE [TABLE] tbl_name57 清空数据58 删除重建表59 60 区别：61 1，truncate 是删除表再创建，delete 是逐条删除62 2，truncate 重置auto_increment的值。而delete不会63 3，truncate 不知道删除了几条，而delete知道。64 4，当被用于带分区的表时，truncate 会保留分区