概述

sql包含4个部分：

• 数据定义语言DDL:create,drop,alter
• 数据操作语言DML:insert,update,delete
• 数据查询语言DQL:select
• 数据控制语言DCL:grant,revoke,commit,rollback

数据引擎

mysql有InnoDB,MyISAM,Merge等存储引擎，我们可以针对每一个表指定引擎。

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

查看支持的引擎。show engines

InnoDB和MyISAM对比：

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)

字符集

字符集可以针对全局、数据库、表、列进行设置

• show variables like ‘character%’; 查看当前字符集设置
• /etc/mysql/mysql.conf.d/mysqld.cnf 文件[musqld]下增加：character-set-server =utf8
• 重启服务 sudo service mysql restart

数据类型

mysql数据类型主要包含整数、浮点数、日期、时间及字符串。

整数

小数

字段名 float(M,N)，M代表总共位数；N代表小数位数。若不知道M，N则有硬件和操作系统决定。

日期时间

datetime输入什么时间取出就是什么时间，但是timestamp就是会根据数据库的时区变化。

我们还可以利用timestamp来指定字段插入或更新的时候自动生成/修改值

alter table user add last_time timestamp default current_timestamp on update current_timestamp;

字符串

CHAR(M), VARCHAR(M)不同之处

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检 索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率比较高。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间(旧版本255），(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则 使用两个字节)。VARCHAR值保存时不进行填充。

enum只能选用其中的一个，而set可以是其中几个的联合，set会把值的末尾空格去除。

二进制

数据类型的选择

• 在长度一定的情况下，浮点数float能表示更大的范围，但是定点数decimal更准确
• char固定长度，自动删除尾部空格，varchar是可变长度，不会删除尾部空格。

数据库基本操作

showdatabases查看所有数据库,其中MySQL是必须的，描述用户访问权限

• 创建数据库。create database db1;
• 查看数据库创建信息。show create database db1;
• 删除数据库。drop database db1;
• 切换数据库。use db1;

数据表的基本操作

创建数据表。

create table tb_name
(
	字段名1，数据类型[列级别约束条件][默认值]，
	字段名2，数据类型[列级别约束条件][默认值]
)

约束

主键约束

1. 主键约束。要求唯一，不能为空。 可以直接在类型后指定，也可以定义完所有的列之后指定。

create table tb1
(
	id int primary key,
	name varchar(25)
)

create table tb2
(
	id int,
	name varchar(25),
	primary key(id)
)

2. 联合主键约束。 联合主键要用第二种声明方式了。

create table tb2
(
	id int,
	name varchar(25),
	primary key(id,name)
)

外键约束

只有InnoDB引擎才能使用外键 外键约束用在表之间建立链接。可以是一列或多列。外键可以为空值，如果不为空则必须等于另一个表中的主键。

外键不一定必须是另一个表的主键，只要满足唯一性就好。

外键中主键所在的那个表是主表，想关联外表的表是从表。

create table if not exists user
(
	id int,
	name varchar(25),
	classid int,
	cityid int,
	constraint fk_user_class foreign key (classid) references class(id) on delete cascade on update cascade,
	constraint fk_user_city foreign key (cityid) references city(id)
)

user表classid，cityid分别链接class，city表中的id。

另外我们还自定义了一个on，当我们没指定on的时候，删除和修改主表中的主键都是不可以的。 on delete，on update后面都可以跟参数，有4种参数：

1. restrict方式：严格模式，同no action，都是立即检查外键约束；不能删除和改
2. cascade方式：也叫级联方式，在父表上update/delete记录时，同步update/delete子表的匹配记录
3. No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete
4. set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null

外键默认是严格模式。

非空约束

create table class
(
id int primary key,
name varchar(20) not null
)

唯一约束

create table person
(
id int primary key,
name varchar(20) unique
)

要求表中该值唯一，但只能出现一个空值。但是mysql是可以多个null的。

默认约束

字段名 数据类型 default 默认值 比如用户表男性较多，那么性别默认值可以设置为男

自增约束

自增类型可以是任何整数类型 字段名 数据类型 auto_increment

查看数据表结构

• desc tb1 查看表的结构
• show create table tb1 查看表的创建语句

修改表

• 重命名表 alter table 旧表名 rename 新表名
• 修改表引擎 alter table 表名 engine=“InnoDB”
• 修改字段类型 alter table 表名 change 旧字段 新字段 数据类型
• 添加字段 alter table 表名 add 字段名 类型（这里我们可以指定字段的位置）
• 删除字段 alter table 表名 drop 字段名
• 添加外键 alter table 表名 add constraint 外键名 foreign key （字段名）references 主表名（字段名）
• 删除外键 alter table 表名 drop foreign key 外键名

删除表

• 删除表 drop table 表名1，表名2… 如果有字段被其他表关联，要先删除外键

数据库函数

数学函数

常见的有:

• 绝对值函数abs(x)
• 三角函数
• 随机函数 rand(),rand(x) 0<=结果<=1，x是种子
• 向上取整 ceil(x)
• 向下取整 floor(x)
• 平均值 avg(x)

select avg(price) from user;

字符串函数

• 长度 char_length(“xxx”)
• 小写 lcase(“XXX”)
• 大写 ucase(“xxx”)
• 去除空格 trim(”x")，ltrim(“xx”)，rtrim(“xx”)
• 取子串 substring(“str”,start,len) start从1开始，负数表示从后面开始
• 子串位置 instr(“abcd”,“c”)
• 字符串翻转 reverse(“abc”)

日期时间函数

• 当前时间 now()
• 当前日期 current_date()
• 当前时间 current_time()
• 获取年月日year(now())，month(now())，day(now())
• 格式化 date_format(date,format)
• 日期时间加减 date_add()，addtime()等

加密函数

• md加密 md5(str)

聚合函数

• AVG()函数忽略列值为NULL的行。
• MAX()函数忽略列值为NULL的行。
• MIN()函数忽略列值为NULL的行。
• SUM()函数忽略列值为NULL的行。
• COUNT()函数有两种使用方式： 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

其他函数

• 返回最近一次插入的数据的id last_insert_id() 这里需要注意，last_insert_id是与表无关的，向a插入，再向b插入，获取的是b表的最新id

增、删，改表中的数据

• 插入数据 insert into 表名（字段名，字段名）values(值，值)；
• 将查询结果插入数据表 insert into 表名 （字段名）select ….
• 修改数据 update 表名 set 字段名=value，字段名=value [条件]
• 删除数据 delete from 表名 [条件]

单表的查询

select
	{* |字段列表}
	[
		from 表1，表2
		[where]
		[group by]
		[having]
		[order by]
		[limit [offet,]rowcount]
	]

where

• in操作 where id in (1,2,3)或者where id not in(1,2,3)
• 位于两者之间 where price between 1 and 2或者where price not between 1 and 2
• 通配符 where name like ‘%xxx%’。_只能通配一个字符
• is null判断 where name is null或者where name not is null
• 多个条件 用and连接
• 条件或用or连接
• 去重 select distinct 字段名

分组

分组通常和count(),max()等函数配合使用 比如：

select s_id,count(*) from fruits group by s_id

另外group_concat(字段名)可以把分组中想要的各字段名显示出来

多字段分组

分组可以指定多个字段，先按第一个字段分组，再按第二个分组，一次类推。

having过滤分组

group by 和having配合，只有满足条件的分组才能被显示。

select s_id,count(*) from fruits group by s_id having count(*)>2

排序

• order by 字段名，字段名 [asc/desc] 默认升序
• 如果有分组是对分组的排序

limit

• limit[offset] rows
• 如果想跳过n行取所有，可以指定一个很大的数字

连接查询

连接查询就是对2个或2个以上的表连接为一个表进行查询。

• from 表1 [链接方式] join 表2 [on 条件]

链接方式常有交叉链接cross,内连接inner，左外连接left,右外连接right

交叉连接

select * from user cross join class;

这样的话，左表每条记录分别和右表每条记录连接，造成很大的无用的数据，实际意义不大。

内连接

select * from user inner join class on user.classid=class.id;

保留的都是满足条件的记录，使用最多。 除此之外，内连接还有where形式的：

select * from user,class where user.classid=class.id;

但是where相比inner join on效率低

左外连接

select * from user left join class on user.classid=class.id;

在进行连接后，以左边的表为主，左边表所有的数据都会显示在结果中，哪怕他们不符合on后边的条件，此时右边表的数据显示null

右连接

右连接和左连接类似，只不过是以右边为主

联合查询

联合查询就是把多个查询语句结果集中在一起。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

联合的时候两个结果集的字段名不要求一样，但是数量和类型要求一样。

• union联合的时候不包括重复行
• union all联合的时候包括重复行

子查询

子查询是指一个查询语句嵌套在另一个查询语句中，首先内部子查询的结果作为外部查询的输入。

• any 只要满足子查询随意的一个结果即可
• all 满足所有子查询的结果
• exists 子查询如果查到了一行，那么exists就是true,否则为false外查询不进行查询
• in 存在和子查询中的结果一样的数据
• not in 参考in

起别名

• 字段 [as] 别名
• 表 [as] 别名

利用正则查询

利用正则匹配查询

select * from user where name regexp "^h";
select * from user where name regexp "a$"

查询name以h开头的数据，name以a结尾的数据。

索引

索引是一个独立的、存在在磁盘上的数据库结构，它包含着表里所有记录的引用指针。有了索引查询的时候就不用从头到尾一行一行的判断了，速度更快。比如两万条数据，select * from user where id =30必须遍历表。有了索引直接找到id=30的行。

• mysql的索引类型有btree和hash，具体和存储引擎相关。

索引优点：

• 通过创建唯一索引，保证每一条数据的唯一性
• 大大提升查询速度，这是主要原因

索引缺点

• 创建和维护需要时间，尤其是数据量大以后，每次插入要排序
• 占用空间

索引分类

普通索引和唯一索引

普通索引允许索引列有重复值和空值。

唯一索引必须唯一，可以为空。主键索引是特殊的唯一索引，不能为空。 mysql创建外键的时候会自动为该列创建普通索引

单列索引和组合索引

单列索引顾名思义就是只包含一个列。

组合索引包含多个列，联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

全文索引

全文索引可以在char、varchar或者text类型上创建。 常用的全文检索模式有两种：

1. 自然语言的全文索引 自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。 默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

2. 布尔全文索引 在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

• 必须包含该词

• 必须不包含该词

提高该词的相关性，查询的结果靠前 < 降低该词的相关性，查询的结果靠后 (*)星号 通配符，只能接在词后面 对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。

select * test where match(content) against(‘a*’ in boolean mode);

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。 最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引。

创建索引

创建表的时候，修改表的时候或者直接create index都可以创建索引。 一个列上可以创建多个索引

创建表时建索引

create table 表名 [字段名 类型] [unionque|fulltext|spatial] [index|key] [索引名] (字段名[length]) [asc|desc]

其中unique、fulltext、spatial分别代表唯一、全文、空间索引，不指定时为普通索引；index和key意义一样；索引名可以为空，此时索引名默认为列名；asc或desc代表升序或降序的索引。

**explain select * from user where id=30;**利用explain可以查看执行的时候是不是用到了索引。

修改表时增索引

alter table 表名 add [unionque|fulltext|spatial] [index|key] [索引名] (字段名[length]) [asc|desc]

直接increate index

create [unionque|fulltext|spatial] [index|key]  [索引名] on 表名 (字段名[length]) [asc|desc]

删除索引

• drop index 索引名 on 表名
• alter table 表名 drop index 索引名

索引涉及原则

• 索引并不是越多越好
• 避免经常更新的表进行索引，并且索引尽可能少。经常查询的表可以建索引。
• 数据量少的话不用建索引，不然适得其反。
• 如果列上相同的值很多，不适合建索引，比如性别列
• 频繁进行排序order by和分组group by的列上适合创建索引
• 尽量使用短索引，比如有的字段0-255，实际上前10个字符就能判断出唯一，可指定索引长度10

索引无用的情况

利用索引可以直接定位，免去了遍历，是最有效的优化查询方案。但是有些情况虽然字段带索引，是不起作用的：

• 利用like，而且%在第一个位置，索引无效。
• 一个索引可以有16个字段，只有查询条件是索引的第一个字段时才有效。
• 查询条件有or时，or前后条件都是索引才有效。

视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

视图本身没有数据，数据的变化是修改的数据表。 不是所有的视图都可以做DML操作。

有下列内容之一，视图不能做DML操作：

　　①select子句中包含distinct

　　②select子句中包含组函数

　　③select语句中包含group by子句

　　④select语句中包含order by子句

　　⑤select语句中包含union 、union all等集合运算符

　　⑥where子句中包含相关子查询

　　⑦from子句中包含多个表

　　⑧如果视图中有计算列，则不能更新

　　⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

存储过程和函数

存储过程可以简单的看成一条或多条sql语句的集合。

创建存储过程

create procedure sp_name ([param])
[characteristics ...] routine_body

其中参数可以是 IN OUT INOUT三种类型 routine_body是sql代码的内容，可以用begin…end开始和结束。 示例：

delimiter //
create procedure pdtest()
begin
select * from user;
end //

delimiter //指定语句用//来结束，防止与sql语句冲突，定义完存储过程后，在回到原来的delimiter ;

创建函数

create function func_name([param])
returns type
[characteristic...] routine_body

参数和存储过程一样，也是那三种，默认是IN。 returns types是必须要的。不需要begin end 示例：

delimiter //
create function functest()
returns varchar(10)
return (select name from user where id=2);
//

变量及流程控制

我们可以声明变量、使用流程控制、光标等。

调用存储过程或函数

• call pd_name() 调用存储过程
• select func_name() 调用函数

查看存储过程和函数

• show {procedure | function} status [like “pattern”]
• show create {procedure | function} name

修改、删除存储过程和函数

• alter {procedure | function} name…
• drop procedure name;
• drop function name;

触发器

触发器和存储过程类似，都是嵌入到mysql的一段程序。 触发器是由事件触发某个操作，包括insert、update、delete。

创建触发器

create trigger trigger_name trigger_time trigger_event on ta_name for each row trigger_stmt

当有多条sql语句时，用begin end

create trigger trigger_name trigger_time trigger_event on ta_name for each row 
begin
trigger_stmt...
end

示例：

create trigger tgtest after update on user for each row 
begin
...//此处小心插入死循环
end

查看、删除触发器

• show triggers;
• 直接从information_schema数据库triggers表中查，另外还有存储过程和函数表
• drop trigger name

事务

MyISAM 不支持事务，InnoDB支持事务。

在innodb里面， 所有的活动都是运行在事务里面的。innodb默认autocommit=1的，意思就是MySQL会在每个语句执行的时候自动提交事务，当然是语句没有报错，如果报错了，那就会自动回滚rollback。

• 查看当前autocommit模式

show variables like 'autocommit';

设置事务级别

• 开启autocommit模式

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

这里session是指对当前连接上执行的事务设置默认事务级别。默认是对下一个（未开始）事务设置隔离级别

• 查看事务隔离级别

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

各种读问题

• 未提交读（Read uncommitted）就是能读取到其他回话中未提交事务的修改，造成脏读问题。
  • 脏读。就是一个事务读取到了另一个事务修改还未提交的数据变化，因为这个变化可能被恢复。
• 提交读(Read Committed) 只能读取到别人已经提提交的数据。避免了脏读的问题。但是在自己的事务中多次读取，数据可能不一致也就是不可重复读。
  • 不可重复读。就是在同一事务中多次读一个数据，但是不一致，因为期间哟其他事务修改并提交了。
• 可重复读(Repeated Read) InnoDB默认级别，同一个事务中多次读取都是一致的，解决了不可重复读的问题。但是有幻读问题。
  • 幻读。可重复读级别虽然外部的修改和插入不会影响本事务查看数据，但是可能影响本事务修改或插入。
• 串行读(Serializable) 现在好了，完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

事务使用

start transaction;
...
...
commit;//rollback;

三大范式

• 第一范式:确保每列的原子性. 第一范式是最基本的范式。

数据库表中的字段都是单一属性的，不可再分。

只要是关系数据库都满足第一范式

如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.

例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

• 第二范式(确保表中的每列都和主键相关). 如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.

例如:订单表(订单编号、产品编号、定购日期、价格、……)，“订单编号"为主键，“产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

• 第三范式(确保每列都和主键列直接相关,而不是间接相关).

如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.

为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖。

例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，“顾客编号"和"订单编号"又相关，最后经过传递依赖，“顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

案例

先分组取分组内的n条

查询男、女生前两名

select a.* 
from student a where
(select count(distinct b.score) from student b where a.sex=b.sex and b.score>a.score)<2;

其中子查询的select取决于外面的查询，每次拿外面的一个数据带入到里面进行判断，如果比他分数大的人少于两个，那么不用说他在前两名，满足条件。

为啥要distinct？因为分数可能有重复！

看比他大的在两个以内那就是前两名，注意条件中<=和<的区别