MySQL语法

前言

1. 每一列被称为列/字段/属性/数据项（column/field/attribute/data
   item）；每一行被称为行/元组/记录（row/tuple/record）。
2. 每一列的第一个为列名；其他为列值。
3. DISTINCT表示去重。
4. []表示可选的。
5. 访问权限即所有权限ALL PRIVILEGE。
6. 增加记录时，必须用逗号将各个数据分开，字符型数据和时间日期型数据要用单引号括起来。`

一、表的创建

CREATE TABLE 表名(
字段名1 数据类型 [完整性约束] [comment ‘注释字段名’],
字段名2 数据类型 [完整性约束] [comment ‘注释字段名’],
字段名3 数据类型 [完整性约束] [comment ‘注释字段名’]
)[comment ‘注释表名’];

添加外键约束时可以在后面加上主表信息：
[CONSTRAINT 外键名称] FOREIGN KEY(本表外键字段名1[,本表外键字段名2]) REFERENCES 主表名(主表字段名1[,主表字段名1])

1.数据类型

（1）字符类型

①CHAR

定长字符串

② VARCHAR

变长字符串

（2）数字类型

ps：（p,s），精度为p（一共p位），标度为q（小数点后q位）

①INT

4字节

②TINYINT

1字节

③DOUBLE

8字节

④DECIMAL

如，DECIMAL(p,s)
m=max(p,q)
为m+2字节

2.约束

（1）NULL/NOT NULL

非空约束

（2）UNOIQUE

唯一约束

（3）PRIMARY KEY

主码（主键）约束

（4）FOREIGN KEY

外码（外键）约束
删除/更新行为
ALTER TABLE 表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE|SET NULL;

①CASCADE

当在父表中删除/更新对应记录时，首先检查是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

②SET NULL

当在父表中删除对应记录时，首先检查记录是否有对应外键，如果有则设置子表中该外键值为NULL（这就要求外键允许取NULL）。

（5）CHECK

检查约束
eg：CHECK(age>0 AND age<120)

（6）AUTO_INCREMENT

自动增长

（7）DEFAULT

默认约束
eg:DEFAULT value

ps：一个字段可以添加多个约束,多个约束之间用空格分开。

二、数据操纵

1.增

（1）添加一个字段

ALTER TABLE 表名
ADD 新字段名 数据类型 [完整性约束] [FIRST|AFTER 已有字段名] ;

（2）添加多个字段

ALTER TABLE 表名
ADD (新字段名1 数据类型 ,新字段名2 数据类型);

（3）添加CHECK约束

ALTER TABLE 表名
ADD CONSTRAINT 约束名 CHECK (约束条件);

（4）添加外键约束

ALTER TABLE 表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

（5）增加一条记录

INSERT|REPLACE INTO 表名 [字段名1[, 字段名2]] VALUES (值,值);
ps：如果INTO子句中没有指定字段名，则新添加的记录必须在每个字段上均有值，且VALUES子句中值的排列顺序要和表中各字段的排列顺序一致。

（6）增加一条记录的部分数据

INSERT|REPLACE INTO 表名 [字段名1[, 字段名2]] VALUES (值,值);
ps：对于INTO中没有出现的字段，新添加的记录在这些字段上将被赋NULL值。

（7）添加多条记录

INSERT|REPLACE INTO 表名 [字段名1[, 字段名2]] VALUES (值,值),(值,值);

2.改

（1）修改表名

ALTER TABLE 旧表名
RENAME [TO] 新表名;

（2）修改字段名和数据类型

ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型;

（3）修改字段数据类型和字段排序

ALTER TABLE 表名
MODIFY 字段名1 数据类型 [FIRST|AFTER 字段名2];

（4）修改表的存储引擎

ALTER TABLE 表名
ENGINE=修改后存储引擎名;

（5）修改表中数据

UPDATE 表名
SET 字段名=表达式[,字段名=表达式]
[WHERE 条件];

3.删

（1）删除字段

ALTER TABLE 表名
DROP 字段名[,DROP 字段名];

（2）删除CHECK约束

ALTER TABLE 表名
DROP CONSTRAINT 约束名;

（3）删除外码、唯一约束

ALTER TABLE 表名
DROP FOREIGN KEY|UNIQUE 约束名;

（4）删除外键

由于一个表中只有一个主码，所以可以不指定主码名就可以删除
ALTER TABLE 表名
DROP PRIMARY KEY;

（5）删除NOT NULL约束

ALTER TABLE 表名
CHANGE [COLUMN] 字段名 字段名 数据类型 (NOT)NULL;

（6）删除数据表

DROP TABLE [IF EXISTS] 表名;

（7）删除表中数据(一条或多条）

DELETE
FROM 表名
WHERE 条件;

（8）删除表（删除表并重新创建名称相同的新表=表还在表中数据没有了）

TRUNCATE TABLE 表名;

4.查

（1）查看当前数据库

SELECT DATABASE();
ps：注意！是有括号的。

（2）查看已创建的数据表

SHOW TABLES;

（3）查看表的字段信息

DESCRIBE|DESC 表名;

（4）查看创建表时的详细语句

SHOW CREATE TABLE表名;

（5）单表查询

SELECT [ALL|DISTINCT] 字段名 [[AS] 别名][,字段名 [[AS]别名]]
FROM 表名或视图名 [[AS]别名]
[WHERE 检索条件]
[GROUP BY 字段名 [HAVING 条件表达式]]
[ORDER BY 字段名[ASC|DESC]]
[LIMIT子句];

①比较大小

=、>、<、>=、<=、!=、<>

②多重条件

AND(&&)、OR(||)、NOT(!)

③确定范围

BETWEEN (最小值) AND(最大值)、NOT BETWEEN AND
ps：包含端点值

④确定集合

IN、NOT IN
eg：SELECT sname FROM student WHERE sage IN(18,20,25);
等同于：SELECT sanme FROM student WHERE sage=18 OR sage=20 OR sage=25;

⑤模糊查询

LIKE、NOT LIKE
%匹配两个或多个字符
_匹配任意单个字符
\转义字符，用于去掉一些字符的特定含义，使其被作为普通字符对待，如用“%”去匹配字符%.

⑥空值

IS NULL、IS NOT NULL

⑦聚合函数查询

AVG()、SUM()、MAX()、MIN()、COUNT()
ps：所有聚合函数只考虑了有效值，没有将空值计算在内。=

⑧排序

ASC：升序（默认）
DESC：降序
ps：ORDER BY 字段1 ASC|DESC,字段2 ASC|DESC;
先按字段1排序，字段1相同，则按字段2排序。

⑨限制查询结果数量（分页查询）

LIMIT[offset,]row_count|row_count OFFSET offset;
如果没有offset，则默认从第一行开始，等同于offset=0。
从第offset+1行开始，返回row_count行。

（6）函数

SELECT 函数(参数);

①字符串函数

CONCAT(s1,s2,…,sn)字符串拼接
LOWER(str)将字符串str全部转换成小写
UPPER(str)将字符串str全部转换成小写
LPAD(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM去掉字符串头部和尾部的空格
SUBSTRING(str, start,len)返回从字符串str从start位置起的len个长度的字符串

②数值函数

CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x,y)取模
RAND()生成一个0到1之间的随机数，ps：括号内没有参数！
ROUND(x,y)对x四舍五入，保留y位小数
例：生成一个六位数的随机验证码？
SELECT LPAD(ROUND(RAND()*1000000,0),6,‘0’);

③日期函数

CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取date的年份
MONTH(date)获取date的月份
DAY(date)获取date的日期
DATE_ADD(date,INTERVAL expo type)返回一个日期/时间值加上一个时间间隔expr后的时间值
eg：SELECT DATE_ADD(now(),INTERVAL 70 DAY/MONTH/YEAR);
DATEDIFF(date1,date2)date1减date2的天数

④流程函数

IF(values,t,f)如果value值为true返回t，否则返回f
IFNULL(value1,value2)如果value1值不为空返回value1，否则返回value2
CASE [expr] WHEN value1 THEN result1[,WHEN value2 THEN result2] ELSE default END
当没有expr时，value1值为true时返回result1，否则返回default；
当有expr时，当expr值等于value1或value2时返回result1，否则返回default。
eg：统计班级各个学员的成绩：大于等于85，展示优秀；大于等于60，展示及格；否则，展示不及格。
CASE WHEN math>=85 THEN ‘优秀’ WHEN math >=60 THEN ‘及格’ ELSE ‘不及格’ END;

（7）多表查询

①内连接

隐式内连接

SELECT 字段名1 [[AS] 别名][, 字段名2 [[AS] 别名]]
FROM 表名1,表名2[,表名3]
WHERE 条件;

显式内连接

SELECT 字段名1 [[AS] 别名] [, 字段名2 [[AS] 别名]]
FROM 表名1 [[AS] 别名] [INNER] JOIN 表名2 [[AS] 别名]
ON 条件
INNER|LEFT|RIGHT|FULL|JOIN 表名3 [[AS] 别名]
ON 条件;

②外连接

SELECT 字段名1 [[AS] 别名], 字段名2 [[AS] 别名]
FROM 表名1 [[AS] 别名] LEFT| RIGHT| FULL [OUTER] JOIN 表名2 [[AS] 别名]
ON 条件
INNER|LEFT|RIGHT|FULL|JOIN 表名3 [[AS] 别名]
ON 条件;

ps：内连接存在弊端，即比如老师授课，涉及教师表和课程信息表，如果某个老师没有教任何课，那么使用内连接，就会导致在连接后的选课关系表中，该老师的信息丢失（连接条件为课程号相同），这样讲可能难以理解，看例子：
teacher

T#	Tname	D#	Salary
001	赵三	01	1200.00
002	赵四	02	1400.00
003	赵五	03	1000.00
004	赵六	04	1100.00

course

C#	Cname	Chour	Credit	T#
001	数据库	40	6	001
003	数据结构	40	6	003
004	编译原理	40	6	001
005	C语言	30	4.5	003
002	高等数学	80	12	004

那么通过T#内连接teacher表和course表获得任课情况表：

T#	Tname	Cname
001	赵三	数据库
001	赵三	编译原理
003	赵五	数据结构
003	赵五	C语言
004	赵六	高等数学

因为02号教师没有授课，如果使用内连接，在连接形成的授课表中02号教师的信息就会丢失，所以应该使用外连接。

③自身连接

SELECT x.字段名 [AS 别名], x.字段名 [AS 别名]
FROM 表名1 AS x ,|NNER JOIN 表名2 AS y
ON ;

（8）子查询

①谓词

(NOT) IN
θ-SOME、θ-ALL
(NOT) EXISTS

三、视图和索引

1.视图

（1）创建视图

CREATE VIEW 视图名
AS 查询语句;

（2）查看视图

①查看视图结构信息

DECSCRIBE 视图名称;

②查看视图状态情况

SHOW TABLE STATUS LIKE ‘视图名称’;

③查看视图创建信息

SHOW CREATE VIEW 视图名称;

（3）删除视图

DROP VIEW [IF EXISTS] 视图名称1[, 视图名称2];

（4）更新视图

ps：对视图中数据的更新会作用到视图引用的基本表的更新。

①通过视图向表中插入数据

INSERT INTO 视图名 [字段名1[,字段名2]]
VALUES (值[,值]);

②删除视图中数据

DELETE FROM 视图名
WHERE 条件;

③WITH CHECK OPTION

更新视图时会进行条件检查，如不满足，则拒绝更改。

2.索引

（1）创建索引

CREATE INDEX 索引名称
ON 表名 (字段名(索引字符长度) [ASC|DESC]);

ps：创建表时直接附带创建索引
CREATE TABLE 表名(
字段名1 数据类型 [完整性约束],
字段名2 数据类型 [完整性约束],
字段名3 数据类型 [完整性约束]
[表约束],
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名1] (字段名 [( 索引字符长度)[ASC|DESC]]),
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名2] (字段名 [( 索引字符长度)[ASC|DESC]])
);

（2）添加索引

①创建唯一索引/全文索引

ALTER TABLE 表名
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名1] ( 字段名称 [(索引字段长度) [ASC|DESC]]),
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名2] ( 字段名称 [(索引字段长度) [ASC|DESC]])
);

②创建联合索引

CREATE INDEX 索引名 ON 表名(字段名1[,字段名2]);

（3）查看索引

SHOW INDEX FROM 表名 [FROM 数据库名];

（4）删除指定索引

ALTER TABLE 表名
DROP INDEX 索引名;

四、安全性管理

1.用户

（1）添加用户

CREATE USER [IF NOT EXISTS]
‘用户名1’[@’ 主机地址或标识’] [IDENTIFIED BY ‘用户口令’],
‘用户名2’[@’ 主机地址或标识’] [IDENTIFIED BY ‘用户口令’];

（2）查看用户

SELECT USER
FROM 表名
WHERE USER=‘用户名’;

（3）重命名用户

RENAME USER ‘原用户名’ TO ‘新用户名’ [,‘原用户名’ TO ‘新用户名’];

（4）修改用户口令

①SET

SET PASSWORD [FOR ‘用户名’@‘主机名’] =‘新密码’;

②ALTER

ALTER USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘新密码’;

（5）删除用户

DROP USER ‘用户名’@‘主机信息’ [,‘用户名’@‘主机信息’];

2.权限授予

GRANT 权限名称 [(字段列表)] [, 权限名称 [(字段列表)]]
ON 授予级别及对象
TO ‘用户名’@‘主机信息’ [,‘用户名’@‘主机信息’]
[WITH GRANT OPTION];

（1）权限

SELECT、
MODIFY、INSERT、UPDATE、DELETE、
CREATE、ALTER、DROP、
ALL [PRIVILEGE]

（2）授予级别及对象

数据库名.表名               *表示所有
*.*                       服务器级别权限
db_name.*                 数据库级别权限
db_name.table_name        表或列级别权限
db_name.routine_name      存储过程级别权限
ps：db_name可以是表名也可以是视图名

3.权限查看

SHOW GRANTS FOR ‘用户名’@‘主机信息’;

4.权限回收

REVOKE 权限名称 [(字段列表)] [,权限名称[(字段列表)]]
ON 回收权限级别及对象
FROM ‘用户名’@‘主机信息’ [,‘用户名’@‘主机信息’];

5.权限转移

WITH GRANT OPTION 表示允许权限转移

6.角色

（1）角色创建

CREATE ROLE ‘角色名称’@‘主机信息’[,‘角色名称’@‘主机信息’];

（2）为角色授权

GRANT 权限名称 ON 授权级别及对象 TO 角色;

（3）为用户分配角色

GRANT ‘用户名’ TO ‘角色名称’;

（4）角色激活

①让某个用户的某个角色生效

SET DEFAULT ROLE ‘用户名’ TO ‘角色名称’;

②让用户的全部角色生效

SET DEFAULT ROLE ALL TO ‘用户名’;

（5）角色查看

SELECT CURRENT_ROLE;

（6）角色撤销

REVOKE '用户名’FROM ‘角色名称’;

五、并发控制与封锁

六、备份还原和日志管理

1.查看日志

SHOW VARIABLES LIKE ‘日志名’;

2.数据库管理员查看二进制日志中用户对数据库的操作

SHOW BINLOG EVENTS in ‘filename’;

3.开启通用日志

SET GLOBAL general_log=on/1;

4.慢查询日志

（1）开启慢查询日志

SET GLOBAL slow_query_log=on;
SET GLOBAL slow_lanch_time=1;

（2）查看慢查询日志相关变量或定义的时间值

SHOW GLOBAL VARIABLES LIKE ‘变量名或long_query_time’;

（3）删除（重置）慢查询日志

SET GLOBAL slow_query_log=0;

（4）生成新的慢查询日志

SET GLOBAL slow_query_log=1;

七、存储过程与存储函数

1.存储过程

（1）创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
SQL语句
END;

（2）存储过程的调用

CALL 名称([参数]);

（3）删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

（4）查看系统变量

①查看所有系统变量

SHOW [SESSION|GLOBAL] VARUABLES;

②模糊匹配查找变量

SHOW [SESSION|GLOBAL] VARIABLES LIKE ‘……’;

③查看已知系统变量名的变量值

SELECT @@[SESSION|GLOBAL] 系统变量名;

（5）设置系统变量

SET [@@][SESSION|GLOBAL] 系统变量名:=值;

（6）变量赋值

SET|SELECT @变量名:=表达式[,@变量名:=表达式];
SELECT 字段名 INTO @变量名 FROM 表名;

（7）使用用户定义变量

SELECT @变量名;

（8）局部变量声明

DECLARE 变量名 变量类型 [DEFAULT 初始值];

（9）流程控制语句

①IF 语句

IF condition1 THEN
statement_list1
[ELSEIF condition2 THEN
statement_list2]
……
[ELSE
statement_listn]
END IF;

②CASE 语句

CASE expression
WHEN value1 THEN statement_list1
[WHEN value2 THEN statement_list2]
……
[ELSE statement_listn]
END;或

CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2]
……
[ELSE statement_listn]
END;

③WHILE 语句

WHILE condition DO
statement_lists
END WHILE;

④REPEAT 语句

REPEAT
statement_lists
UNTIL condition
END REPEAT;

⑤LOOP 语句

LOOP
statement_lists
END LOOP;

ps：
LEAVE 退出循环
ITERATE 跳过当前循环，直接进入下一次循环

（10）参数

IN 该参数作为输入
OUT 该参数作为输出
INOUT 既可作为输入参数，也可作为输出参数

（11）CURSOR（游标）

①声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

②打开游标

OPEN 游标名称;

③获取游标记录

FETCH 游标名称 INTO 变量[,变量];

④关闭游标

CLOSE 游标名称;

（12）HANDLER（条件处理程序）

DECLARE handler_action HANDLER FOR condition_values[,condition_values] …statement;
ps：handler_action：
①CONTINUE：继续执行当前程序
②EXIT：终止执行当前程序
ps：condition_values：
①SQLWARNNING：01开头的SQLSTATE代码的简写
②NOT FOUND：02开头的SQLSTATE代码的简写
③SQLEXCEPTION：所有没有被SQLWARNNING或NOT FOUND捕获的SQLSTATE代码的简写

2.存储函数

CREATE FUNTION 存储函数名称 ([参数列表])
RETURN type [characteristic]
BEGIN
SQL语句
RETURN……;
END;

ps：
characteristic：
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

八、触发器和事件

1.触发器创建

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
trigger_stmt;
END;

2.触发器查看

SHOW TRIOGGERS;

3.删除触发器

DROP TRIGGER [schema_name] trigger_name;
ps：schema_name为触发器所在数据库名称，若没有，默认为当前数据库。

总结

以上就是我要分享的所有内容，如有不正确的地方，欢迎评论指正；如果帮助到你，希望能点个免费的赞，支持一下；如果觉得有用，收藏起来，以防下次想看找不到了。谢谢陌生的你！