mysql测试数据：https://github.com/datacharmer/test_db

一、MySQL基础

SQL的分类：

1. DDL—数据定义语言（Data Define Language）：create（创建），alter（修改），drop（删除），TRUNCATE（截断），RENAME（重命名）；
2. DML—数据操纵语言（Data Manipulation Language）：select（查询），delete（删除），update（更新），insert（新增）；
3. DCL—数据控制语言（Data Control Language）：grant（添加权限），revoke（回收权限）；

1.1 DDL操作

注意：使用DDL语言时，必须在动词后跟上数据库对象名词 （例如：TABLE、VIEW、INDEX、SCHEMA、TRIGGER等）。
数据库的链接

# mysql -h 127.0.0.1 -u root -p

常用的参数解释：

-A 不预读数据库信息，提高连接和切换数据库速度,使用--disable-auto-rehash代替
--default-character-set 使用的默认字符集
-e 执行命令并退出
-h 主机地址
-p 连接到服务器时使用的密码
-P 连接的端口号

1.1.1 数据库

创建

create database [if not exists] db_name;/*创建数据库，覆盖已存在数据库*/

查看

like用于匹配指定数据库名称，like可以部分匹配也可完全匹配。

show databases [like '%db_name%'];

修改

/*查看数据库定义声明的结果*/
show create database db_name;
/*修改db_name指定字符集*/
alter database db_name DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;

选择

use db_name;

删除

/*[if exists]防止系统报错误提示*/
drop database [if exists] db_name;

注释

注释分为单行注释和多行注释

单行注释有两种

1. 使用#注释符，#注释内容
2. 使用--注释符，--后面需加空格。-- 注释内容

多行注释
/注释内容/

1.1.2 表

创建

create [temporary] table [if not exists] [database_name.] <table_name>
(
  <column_name> <data_type> [[not] null],…
)

/注：temporary：指明创建临时表
　　if not exists：如果要创建的表已经存在，强制不显示错误消息
database_name：数据库名
table_name：表名
column_name：列名
data_type：数据类型/

drop table if exists `db_user`.`t_user`;
create table `t_user`(
  `id` bigint(20) unsigned not null auto_increment comment '主键id',
  `name` varchar(64) not null default '' comment '名称',
  `create_time` timestamp not null default current_timestamp comment '创建时间',
  `update_time` timestamp not null default current_timestamp on update current_timestamp comment '更新时间', 
  primary key (`id`) using btree,
  unique key `unq_name`(`name`) using btree
) engine = innodb default charset=utf8mb4 comment='用户表';

复制表结构

根据已有表，创建新表，当两张表位于同一数据库时，可以省略数据库名称。
语法：

create table table_source like table_target;
#例：
create table `t_user_copy` like `t_user`;

查看定义

describe/desc <table_name>;
例：
describe `t_user`;

展示数据如下：

查看创建的表

show create table <table_name>;

可在分号前添加\g或者\G参数改变展示形式。

更新表名

alter table <table_name> rename <table_name2>;

删除表

drop table [if exists] <table_name>;

删除所有表：

select 
  concat('drop table ',table_name,';')
from 
  information_schema.`tables` 
where 
  table_schema = '数据库名';

清空表(主键重新从1开始)

truncate table <table_name>;

增加表字段

alter table <table_name> add column <column_name> <data_type>;

data_type后面可添加字段的位置，可以添加到表开头也可在中间位置(frist/after column_name);

alter table <table_name> add column <column_name> <data_type> [constraint] default [null] first;

如：

alter table `table_name` 
add column `column_name` varchar(255) not null default "" comment '注释' first/after column_name2,
add column `column_name3` varchar(255) not null default "" comment '注释' first/after column_name2;

constraint(约束条件)

1）not null：非空约束，保证字段的值不能为空
s_name VARCHAR(10) NOT NULL, #非空
2）default：默认约束，保证字段总会有值，即使没有插入值，都会有默认值！
age INT DEFAULT 18, #默认约束
3）unique：唯一，保证唯一性但是可以为空，比如座位号
s_seat INT UNIQUE,#唯一约束
4）check：检查性约束【MySQL不支持，语法不报错，但无效】
s_sex CHAR(1) CHECK(s_sex='男' OR s_sex='女'),#检查约束(Mysql无效)
5）primary key：主建约束，同时保证唯一性和非空
id INT PRIMARY KEY,#主建约束（唯一性，非空）
6）foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值！
teacher_id INT REFERENCES teacher(id) #这是外键,写在列级,Mysql无效

修改表字段

alter table <table_name> modify <column_name> <data_type>;
alter table <table_name> change <column_name> <column_name> <data_type>;

change和modify区别：前者可以修改列名称，后者不能。change需要写两次列名称。

字段增加修改add/change/modify添加顺序:
1.add增加在表尾.
2.change/modify不改表字段位置.
3.修改字段可以带上以下参数进行位置调整

alter table <table_name> change <column_name> <column_name> <data_type> after <column_name2>;
alter table <table_name> change <column_name> <column_name> <data_type> first;

删除表字段

alter table <table_name> drop column <column_name>;

1.2 DML语句

1.2.1 增删改

插入记录

//指定字段,
//自增,默认值等字段可以不用列出来,没有默认值的为自动设置为NULL
insert into <table_name> (<column_name1>,<column_name2>,<column_name3>,<column_name4>) values (<value1>,<value2>,<value3>,<value4>);

//可以不指定字段,但要一一对应
insert into <table_name> values (<value1>,<value2>,<value3>,<value4>);

批量记录

insert into <table_name> [(<column_name1>,<column_name2>)] values (<value1>,<value2>),(<value1>,<value2>);

自我复制(蠕虫复制)

mysql蠕虫复制，简单来说就是将查询出来的数据不断的新增插入到指定的数据表中。通常情况，mysql蠕虫复制时用来测试表压力。

1.查询插入：

insert into table_target select * from table_source;
#例：
insert into t_account_year (account_id) select t.account_id from t_account_info t;

注：select列会造成主键冲突，最好不要复制主键
2.覆盖插入：

replace into table values(...);

3.忽略插入：

insert ignore into table values(...);

4.insert主键重复则update：

insert into table tb1 values(id,col1,col2) on duplicate key update col2=....;

更新记录

update <table_name> set <column_name1>=<value1> [where <expression>];
update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";

特殊
将table2某列赋予table1的某列批量更新(使用ID速度快)

语法：

update table_target,table_source set table_target.name = table_source.name where table1.id = table2.id;

将行号赋予某列

update table,(select (@rowNO := @rowNo+1) AS rowno,stuID from table,(SELECT @rowNO :=0)r) as a
set table.`order` = a.rowno where a.ID = table.ID;

删除记录

//请仔细检查where条件,慎重
delete from emp where ename='jack';

关联删除

delete t.* from t_monthly_cycle t,(
select account_id,Max(t2.cycle_id) as cycle_id from t_monthly_cycle t2 where t2.account_id not in (180822000151112,180822000151128)
group by t2.account_id ) as a where a.account_id = t.account_id and t.cycle_id = a.cycle_id;

1.2.2 查询

查看记录

select 
  {* | <column_name>} 
from 
  <table_name>,<table_name2> 
[
  where <expression>
  [group by <definition>]
  [having <expression>]
  [order by <definition>]
  [limit <offset>,<row count>]
]

//查看所有字段
select * from <table_name>;

//查询不重复记录
select distinct(deptno) from emp;
select distinct(deptno),emp.* from emp;

//条件查询
//算数运算法：+,-,*,/,%
//比较运算符: =,>,<,>=,<=,<>或!=,
//           is not null,between and,in,like,
//           greatest(两个或多个参数时返回最大值),
//           least(两个或多个参数时返回最小值)
//逻辑运算符: and或&&,or或Ⅱ,not或!,xor(逻辑异或)
//位运算符：&(按位与),|(按位或),~(按位取反),^(按位异或),<<(按位左移),>>按位右移)
select * from emp where sal="18000" and deptno=2;

排序

//desc降序,asc 升序(默认)
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;

限制记录数

select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;

聚合函数

count():记录数 / sum():总和 / max():最大值 / min():最小值

select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;

group by分组

//分组统计
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;

having

对分组结果二次过滤

select count(deptno) as count,deptno from emp group by deptno having count > 2;

with rollup

对分组结果二次汇总

select count(sal),emp.* from emp group by sal, deptno with rollup;

通配符

用于替换字符串中的部分字符，通常与like一起使用，并协同where完成查询。常用通配符
• %：表示零个、一个或多个
• _：表示单个字符

select * from <table_name> where <column_name> like 'xiaer%';
select * from <table_name> where <column_name> like 'x_a_r';

1.2.3 表连接

SQL-92标准

• left join：左连接，返回左表中所有的记录以及右表中连接字段相等的记录；
• right join：右连接，返回右表中所有的记录以及左表中连接字段相等的记录；
• inner join：内连接，又叫等值连接，只返回两个表中连接字段相等的行；
• full join：外连接，返回两个表中的行：left join + right join；
• cross join：结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

内连接

内连接又叫等值连接，只返回两个表中连接字段相等的行

#SQL92：
select * from emp as e,dept as d where e.deptno = d.deptno;
#SQL99：
select * from emp as e inner join dept as d on e.deptno = d.deptno;

左外连接

包含左表中所有的记录以及右表中连接字段相等的记录

#SQL92[现在数据库不支持]：
select * from emp e,dept d where e.deptno = d.depetno(+);
#SQL99：
select * from emp as e left join dept as d on e.deptno = d.deptno;

右外连接

包含右表中所有的记录以及左表中连接字段相等的记录

#SQL92[现在数据库不支持]：
select * from emp e,dept d where e.deptno(+) = d.deptno;
#SQL99：
select * from emp as e right join dept as d on e.deptno=d.deptno;

子查询

也称为内查询或嵌套查询，先于主查询被执行，其结果将作为外层主查询的条件，在增删改查中都可以使用子查询，支持多层嵌套，IN语句是用来判断某个值是否在给定的结果集中。

//=, !=
select * from emp where deptno = (select deptno from dept where deptname = "技术部");
select * from emp where deptno != (select deptno from dept where deptname = "技术部");

//in, not in 
//当需要使用里面的结果集的时候必须用in(); 
select * from emp where deptno in (select deptno from dept where deptname = "技术部");
select * from emp where deptno not in (select deptno from dept where deptname = "技术部");

//exists , not exists
//当需要判断后面的查询结果是否存在时使用exists();
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);

select * from table_name1 where exists (select 1 from table_name2 where t1.id = t2.id and cloum in (XX) );
select * from A where exists (select 1 from B where b.id=a.id);

in和exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。

in 是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

如果查询语句使用了not in那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

多层嵌套
当多层嵌套时，每一层的内外字段名要一致才可以，否则报错

select * from table1 where id in (select id from table2 where name in (select name from table3));

table2的字段名必须和table1的where字段名一致，同样的table3的字段名必须要和table2的where字段名一样

from后跟子查询

select a.id,a.name from (select * from table1) a;

column子查询

select 
a.id,
(case a.type 
when 1 then (select name from  b where a.id = b.id )
when 2 then (select name from c where c.id = a.id)
else null end
) as name
from a

注意：column多表链接时，a表和b/c表的条件在where里

记录联合

union：返回去重之后的结果
select ename from emp union select ename from emp;

union all：返回所有结果
select ename from emp union all select ename from emp;

1.2.4 高级查询

行行比较

select * from table_name 
where (<column_name1>,<column_name2>) in ((<value1>,<value2>),(<value3>,<value4>));
#例：
select * from table_name where (id,name) in ((1,'李'),(2,'张'));

正则表达式

MySQL正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。
MySQL的正则表达式使用REGEXP这个关键字来指定正则表达式的匹配模式，REGEXP操作符所支持的匹配模式。

select * from table_name where name regexp '^W';
select * from table_name where name regexp 'r$';
select * from table_name where name regexp '.r';
select * from table_name where name regexp 't*r';

1.3 DCL语句

1.3.1 添加权限

grant select,insert on test.* to 'db_user_1'@'localhost'  identified by '123456';
flush privileges;

1.3.2 回收权限

revoke insert on test.* from 'db_user_1'@'localhost';

二、MySQL数据类型

2.1 整数类型

指定宽度：指定显示的宽度为5,不影响实际数据

create table t1 (id int , id2 int(5));

注：INT(4)与INT(5)区别，括号中的字符表示显示宽度，整数列的显示宽度与MySQL需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，INT类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。其实当我们在选择使用INT的类型的时候，不论是int(4)还是int(5)，它在数据库里面存储的都是4个字节的长度。

zerofill:
采用零填充,不足5位采用0填充，配合数据宽度

create table t2 (id int , id2 int(5) zerofill);

unsigned:

create table t3 (id int , id2 int(5) unsigned);

auto_increment:
只用于整数类型
产生唯一标识
值从1开始,逐行增加
一个表中最多只能存在一个自增列
自增列应该定义为not null
自增列应该这只为 primary key 或者 unique

id int not null auto_increment primary key

2.2 小数类型

小数包括浮点数和定点数

float , double , decimal 特点:
1.(m,d)表示方式:m指的是整数位,d指的是小数位(又称作精度和标度)
2.float/double四舍五入丢失精度,decimal会截断数据并输出warning
3.如果不指定精度,float/double采用操作系统默认,decimal则是(10,0)

2.3 二进制类型

M表示可以为其指定长度。

1 存放位字段值
2 指定存放多位二进制的长度,默认为1(范围:1~64)
3 读取需要bin()/hex(),普通的select读取结果为null
4 插入的值会转化为二进制码,如果长度运行则正常处理,否则插入失败

create table t6 (id bit(1));
select bin(id) from t6;

2.4 日期和时间类型

当前系统日期

timestamp：返回yyyy-mm-dd hh:mm:ss 宽度19
timestamp：不适合存放久远日期,超出范围则会采用零值填充

//不同格式的显示零值格式
d date, t time,dt datetime
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2016-11-25 | 14:52:44 | 2016-11-25 14:52:44 |
+------------+----------+---------------------+

//默认值的体现
id1 timestamp
+---------------------+
| id1                 |
+---------------------+
| 2016-11-25 14:55:45 |
+---------------------+

//timestamp字段只能有一个"CURRENT_TIMESTAMP"
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

//timestamp和时区相关:SYSTEM 指的是和主机时区保持一致
show variables like "%_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

//修改时区
set time_zone="+9:00";//在 [mysqld] 之下加 default-time-zone=timezone

年份

year：默认为4位格式.1901~2155和0000. 2位的已经不推荐,高版本已经不支持了.

timestamp和datetime区别:
1、timestamp支持范围小(1970-01-01 08:00:01到2038年某个点)
2、表中第一个timestamp字段,会默认采用当前系统时间.如果更新其他字段,该字段没有赋值的话,则该字段会自动更新.如果指定字段不满足规格,则采用零值填充
3、timestamp查询和插入都会受到当地时区影响

datetime支持范围宽度大(1000-01-01 00:00:00到9999-12-31 23:23:59)

2.5 字符串类型

括号中的M表示可以为其指定长度。

char和varchar的区别：

• char定长，效率高,在创建字段的时候就已经指定,一般用于固定长度的表单提交数据存储
• char在检索的时候回去掉尾部的空格
• varchar是动态长度
• varchar在检索的时候回保留尾部的空格
• varchar将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度(长度>255时需要2个字节)
• 字符类型若为gbk，每个字符最多占2个字节，字符类型若为utf8，每个字符最多占3个字节

2.6 枚举类型

• 枚举在集合中取值,只能取一个
• 如果值不存在则采用第一个值为默认
• 如果插入NULL不会出错,会将NULL写入表
• 成员个数在1255占用1个字节,在25565535占用2个字节,节约资源

create table `t8` (
`gender` enum('m','f') default null
) engine=innodb default charset=utf8

2.7 集合类型

• 集合类型和枚举很像,但是支持多值选择
• 最多可以保存64个成员,每8个成员占1个字节
• 重复值只会插入一次,如果查出集合范围则插入为null

create table t9 (col set ('a','b','c','d'));

MySQL函数

MySQL提供了实现各种功能的函数，常用的函数分类：数学函数、聚合函数、字符串函数和日期时间函数

3.1 数学函数

常用的数学函数

3.2 聚合函数

3.3 字符串函数

3.4 日期时间函数

MySQL也支持日期时间处理，提供了很多处理日期和时间的函数。具体请参考MySQL常用日期时间函数

3.5 解析Json

JSON解析：

select replace(JSON_EXTRACT('[{"companyCode":"000000","companyName":"全部","companyId":"000000"}]
',"$[*].companyCode") ,'"','') ;

参考文章

• MySQL高级查询语句
• SQL92和SQL99
• SQL92与SQL99
• 行行比较