MYSQL_SQL语句

数据库操作

查询

-- 查询所有数据库
SHOW DATABASES;

-- 查询当前正在使用的数据库名
SELECT DATABASE();

-- 查询某个数据库的创建字符集
SHOW CREATE DATABASE 数据库名;

创建

-- 创建数据库
CREATE DATABASE 数据库名;

-- 创建数据库(判断不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名;

-- 创建数据库(指定字符集)
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

修改

-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;

删除

-- 删除数据库
DROP DATABASE 数据库名;

-- 删除数据库(判断存在则删除)
DROP DATABASE IF EXISTS 数据库名;

数据表操作

-- 使用数据库
USE 数据库名;

查询

-- 查询数据库中所有的数据表
SHOW TABLES;

-- 查询表结构
DESC 表名;

-- 查询表字符集
SHOW TABLE STATUS FROM 库名 LIKE 表名;

创建

CREATE TABLE 表名(
	列名1 数据类型1 COMMENT '字段注释1'
    , 列名2 数据类型2 COMMENT '字段注释2'
    , ...
    , 列名n 数据类型n
) COMMENT '表注释';

常用数据类型	说明
INT	整数类型
DOUBIE	小数类型
DATE	日期:只包含年月日(yyyy-MM-dd)
DATETIME	日期:包含年月日时分秒(yyyy-MM-dd HH:mm:ss)
VARCHAR	字符串

约束

CREATE TABLE 表名(
    列名 数据类型 UNIQUE -- 唯一约束(不能重复)
    , 列名 数据类型 NOT NULL -- 非空约束(不能为空)
	, 列名 数据类型 PRIMARY KEY -- 主键约束(不能重复,不能为空)
    , 列名 数据类型 PRIMARY KEY AUTO_INCREMENT -- 主键自增长约束
    , ...
);

修改

-- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;

-- 修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称;

-- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;

-- 修改列名称和数据类型(若没有 新列名 则只修改数据类型)
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

删除

-- 删除表
DROP TABLE 表名;

-- 删除数据表(判断存在则删除)
DROP TABLE IF EXISTS 表名;

-- 删除列
ALTER TABLE 表名 DROP 列名;

数据操作

查询

SELECT
	列名1
	, 列名2
	, ...
FROM
	表名1
	, 表名2
	, ...
WHERE
	条件1
	AND 条件2 -- AND:并且 OR:或者
	AND ...
GROUP BY
	分组字段
HAVING
	分组之后的条件
ORDER BY
	排序字段 DESC -- ASC:正序(由小到大,默认可以省略) DESC:倒序(由大到小)
LIMIT
	从哪条开始查, 查几条 -- 索引从0开始 若从0开始第一个参数(0)可以省略

-- 查询该表所有数据
SELECT * FROM 表名;

-- 查询该表 id=1 的数据
SELECT * FROM 表名 WHERE id = 1;

-- 查询该表所有id和name
SELECT id, name FROM 表名;

-- 查询该表所有name并去重
SELECT DISTINCT name FROM 表名;

创建

-- 在该表创建一条新数据(默认添加所有列,值的顺序与创建表时列顺序一致)
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 在该表创建一条新数据(列名与值一一对应,没有添加的列默认为空)
INSERT INTO 表名(列名1, 列名2, ...) VALUES (值1, 值2, ...);

修改

-- 根据条件修改(不加条件修改全部)
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... WHERE 条件;

删除

-- 根据条件删除(不加条件删除全部)
DELETE FROM 表名 WHERE 条件;

进阶

聚合函数

聚合函数	说明
count(列名)	统计数量(默认选用不为null的列)
MAX(列名)	最大值
MIN(列名)	最小值
SUM(列名)	求和
AVG(列名)	平均值
UCASE(列名)	把小写字母转换为大写字母
LCASE(列名)	把大写字母转换为小写字母
MID(列名, int1, int2)	提取该列值第 int1 到第 int2 个字符
LEN(列名)	获取该列值的长度
EXISTS(查询语句)	查询是否存在
NOW()	获取当前时间
FORMAT(列名, 格式)	将时间字段转换为指定格式(‘%Y-%m-%d’ -> ‘yyyy-MM-dd’)

多表查询

内连接

-- 查询表1和表2 name 相同并且表1中 id=1 的数据
SELECT *
FROM 表名1 as a -- as:起别名
	, 表名2 as b -- 内连接(一次查询多张表)
WHERE a.name = b.name
	AND a.id = 1;

左连接

-- 查询表1中 id=1 的数据 若表2中有 name 与表1 name 相同的数据则查出来,否则为空
SELECT *
FROM 表名1 as a
LEFT JOIN 表名2 as b -- 左连接(查询左表的全部与右表符合条件的)
ON a.name = b.name
WHERE a.id = 1;

子查询

-- 查询表1中 name 在表2 id=1 的 name 中的数据
SELECT * 
FROM 表名1
WHERE name in (
	SELECT name
	FROM 表名2
	WHERE id = 1
	); -- 子查询(以一次查询的结果作为查询条件)

自关联查询

-- 查询员工及其领导的编号与姓名,没有领导则只显示员工编号与姓名
SELECT
	a.id -- 员工编号
	, a.name -- 员工姓名
	, a.mgr -- 领导的员工编号
	, b.name -- 领导姓名
FROM
	employee a -- 员工表
LEFT JOIN
	employee b -- 自关联查询(与a同一张表)
ON
	a.mgr = b.id;

索引

查看索引

SHOW INDEX FROM 表名;

添加索引

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名(列名1,列名2,...);

-- 主键索引(添加主键约束，默认添加主键索引)
ALTER TABLE 表名 ADD PRIMARY KEY(列名); 

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名);

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 索引名 FOREIGN KEY (本表列名) REFERENCES 主表名(主表列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名(列名);

删除索引

DROP INDEX 索引名 ON 表名;

自定义排序

-- 当 user_name = 'A' 时以 4 参与排序
-- 当 user_name = 'B' 时以 5 参与排序
-- 当 user_name = 'C' 时以 2 参与排序
-- 当 user_name = 'D' 时以 3 参与排序
-- 否则以 1 参与排序 DESC 倒序排序
SELECT *
FROM 表名
ORDER BY
CASE user_name
WHEN 'A' THEN 4
WHEN 'B' THEN 5
WHEN 'C' THEN 2
WHEN 'D' THEN 3
ELSE 1
END DESC
-- 排序结果: B,A,D,C,其他...

-- 当 user_name = 'admin' 时以 create_time 字段值参与排序,否则以 update_time 字段值参与排序
SELECT *
FROM 表名
ORDER BY
CASE
WHEN user_name = 'admin'
THEN create_time
ELSE update_time
END

视图

查询视图

-- 查询该数据库所有视图
SHOW FULL TABLES IN 数据库名 WHERE Table_type = 'VIEW';

-- 查询视图创建语法
SHOW CREATE VIEW 视图名称;

创建视图

-- 将查询语句查询出的数据当作视图
CREATE VIEW 视图名称 AS 查询语句;

-- 将查询语句查询出的数据以指定列名当作视图
CREATE VIEW 视图名称 (列名1, 列名2, ...) AS 查询语句;

修改视图

ALTER VIEW 视图名称 AS 查询语句;

删除视图

-- 删除视图
DROP VIEW 视图名称;

-- 如果存在则删除
DROP VIEW IF EXISTS 视图名称;

存储过程

查看存储过程

-- 查看该数据库所有存储过程
SELECT *
FROM mysql.proc
WHERE db='数据库名称';

创建存储过程

-- 修改分隔符为$
DELIMITER $

CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

调用存储过程

CALL 存储过程名称(参数);

删除存储过程

-- 删除存储过程
DROP PROCEDURE 存储过程名称;

-- 如果存在则删除
DROP PROCEDURE IF EXISTS 存储过程名称;

触发器

事务

//TODO 未完成