MySQL参考

MySQL官网文档
菜鸟MySQL教程
MySQL架构原理之 MySQL存储引擎
MySQL配置
mysql配置文件详解
MySQL笔记
官网json函数

命令

连接

格式： mysql -h HOST -P PORT -u USERNAME -p PWD
示例： mysql -u Tim -p 123456

增删选数据库

-- 创建数据库
CREATE DATABASE hello;

--删除数据库
DROP DATABASE hello;

-- 选择数据库
USE user；

增删数据表

-- 创建表
CREATE TABLE staff (
	id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	city_code int DEFAULT NULL COMMENT '城市code',
	name varchar(50) NOT NULL COMMENT '姓名',
	PRIMARY KEY (id),
	INDEX name (name(20))
);

-- 查看建表语句
SHOW CREATE TABLE staff;

CREATE TABLE city (
	id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	code int DEFAULT NULL COMMENT '城市code',
	name varchar(50) NOT NULL COMMENT '城市名称',
	PRIMARY KEY (id),
	INDEX name (name(20))
);

-- 删除表
DROP TABLE staff;

插入

INSERT INTO staff (city_code, name) 
	VALUES (1, 'Tim'),(2, 'Nana');

-- insert into select
INSERT INTO staff (city_code, name)
	SELECT code, 'Tim' FROM city;

删除

DELETE FROM staff WHERE id=1;

-- 连表判定删除，delete后跟表别名。删除多表时，逗号隔开
DELETE s FROM staff s 
	LEFT JOIN city c ON s.city_code=c.code
	WHERE c.id>3;

-- 删除重复数据
delete t1 from 
produce_delivery_ref t1 
left join ( select min(id) id from produce_delivery_ref group by order_id) t2 on t1.id=t2.id
where t2.id is null

更新

UPDATE staff SET city_code=2, name='Nancy'
	WHERE id=4;

-- 连表更新
UPDATE staff s 
	LEFT JOIN city c ON s.city_code=c.code
	SET name='Bill'
	WHERE c.id=3;

查询

-- limit offset, size; limit 3等效于 limit 0, 3
SELECT city_code, count(name) num FROM staff 
	WHERE id>5
	GROUP BY city_code
	HAVING num>3
	ORDER BY num DESC
	LIMIT 3;

-- 连表查询
SELECT c.code, s.name FROM staff s 
	LEFT JOIN city c ON s.city_code=c.code
	WHERE (c.id, s.id)=(3, 4)
		OR ((c.id, s.id) NOT IN ((1, 3), (2, 4) AND c.id IS NOT NULL)
		OR (c.id IS NULL AND s.city_code=2);

-- union查询；UNION去重，UNION ALL不去重
SELECT * FROM city WHERE id=2
UNION
SELECT * FROM city WHERE id=2;

-- 行号
SELECT  (@rownum := @rownum+1) rowNum, c.* 
FROM city c, (SELECT  @rownum := 0) AS rn

操作符

MySQL文档–操作符

算术运算符：+-*\、div整除、%取模
比较运算符：><=、>=、<=、<>、!=、<=>
逻辑运算符：and、or、xor、not
布尔值比较：IS，比较布尔值，TRUE、FALSE、UNKOWN。null为UNKOWN
		NOT，取反。如：NOT LIKE、NOT IN、NOT BETWEEN-AND
null运算：IS NULL、IS NOT NULL
正则运算：LIKE、REGEX、RLIKE
范围：BETWEEN-AND、IN

分组排序、限制

GROUP BY
分组
HAVING
分组后过滤
ORDER BY
格式：ORDER BY column ASC/DESC；默认ASC
示例：ORDER BY id, name DESC
LIMIT
格式：LIMIT [offset,] size
示例：LIMIT 3；等效于LIMIT 0, 3

MySQL常用函数

流程控制

官网流程控制函数

-- case-when-else
SELECT CASE WHEN code IS NULL THEN '' WHEN code=0 THEN 'unkown' ELSE name END nameStr 
	FROM city;
	-- 简写
SELECT CASE sex WHEN 0 THEN '女' ELSE '男' END sexStr
	FROM staff;
	
-- if(expr1, expr2, expr3)；返回 expr1 ? expr2 : expr3。仅数值0，null；为false
SELECT IF(sex=0, '女', '男') sexStr
	FROM staff;

-- ifnull(expr1, expr2)；返回 expr is null ? expr2 : expr1。
SELECT IF(code, -1) code
	FROM city;
	
-- nullif(expr1, expr2)；返回 expr1=expr2 ? null : expr1
SELECT NULLIF(1,1), NULLIF(1,0), NULLIF(0,0);

数字、字符串、日期

菜鸟MySQL函数
官网数字函数
官网日期函数

%Y，%y；年
%m；月
%d；日。
%Y-%m-%d，年-月-日
%H，%h；时
%i；分
%s；秒
%T；同%H:%i:%s

官网字符串函数
官网日期增减单位
官网日期格式化字符

获取连续值、日期

-- 连续值，无表
select d from (
           SELECT @xi:=@xi+1 as d from
               (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
               (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
               (SELECT @xi:=-1) xc0
           ) as m where d <= 5

-- 连续值，模拟行号。包含30，使用时替换report_template 表

SELECT  (@rownum := @rownum+1) rowNum 
FROM report_template c, 
	(SELECT  @rownum := 0) rn 
where @rownum<30

-- 连续日期
    SELECT DATE_FORMAT(DATE_ADD('2018-09-09', INTERVAL d DAY), '%Y-%m-%d') as year_month_day
       FROM (
       select d from (
           SELECT @xi:=@xi+1 as d from
               (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
               (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
               (SELECT @xi:=-1) xc0
           ) as m where d <= 5
       ) dtc

聚合函数

官网聚合函数

-- max、min、avg、sum；计算时忽略null
SELECT MAX(city_code) FROM staff;

-- count([distinct] expr)；统计非null值个数
SELECT COUNT(DISTINCT city_code) FROM staff;

-- with rollup，逐级统计；
-- 无GROUP BY时统计全部数据。

-- group_concat
SELECT city_code, GROUP_CONCAT(username SEPERATOR ';') FROM staff 
WHERE city_code=1 GROUP BY city_code

ALTER命令

-- 增加字段
ALTER TABLE staff ADD COLUMN addr varchar(100) DEFAULT NULL COMMENT '住址',
	ADD COLUMN sex tinyint(4) DEFAULT 1 COMMENT '性别，0女、1男' AFTER addr;

-- 删除字段
ALTER TABLE staff DROP addr,
	DROP sex;

-- 修改字段
ALTER TABLE staff CHANGE name user_name varchar(20) COMMENT '用户名',
	CHANGE city_code bigint(20);

索引

-- 普通索引
CREATE INDEX city_name_index ON staff (city_code, username);

-- 建表时建索引
CREATE TABLE staff (
	id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	city_code int DEFAULT NULL COMMENT '城市code',
	name varchar(50) NOT NULL COMMENT '姓名',
	PRIMARY KEY (id),
	INDEX name (name(20))
);

--alter新增索引
ALTER TABLE staff ADD INDEX city_idx (city_code),
	ADD INDEX name_idx (username);

-- 删除索引
DROP INDEX city_idx ON staff;

ALTER TABLE staff DROP INDEX city_idx,
	DROP INDEX name_idx;

-- 查看索引
SHOW INDEX FROM staff;\G

索引类型

• PRIMARY KEY；主键，索引值必须是唯一的，且不能为NULL。
• UNIQUE INDEX；索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• INDEX；普通索引，索引值可出现多次。
• FULLTEXT；全文索引。

资源

MySQL资料大全