MySQL 学习笔记

SQL语言

SQL语言不区分大小写，但是字符串区分。
SQL语句必须以分号结尾。
SQL语句中的空白和换行没有限制。

分类

DML

添加

插入多行：

INSERT INTO 表名(列名1，列名2...)
VALUES(值1,值2,...),
(值1,值2,...);

插入检索出的数据：

INSERT INTO 表名(列名1,列名2,...)
SELECT语句;

修改

UPDATE 表名 SET 列名=值 WHERE 条件
若需要更新多行时，即使发生错误也要更新，可使用IGNORE关键字。

删除

DELETE FROM 表名 WHERE 条件
如果想快速清空表中所有数据，可使用TRUNCATE TABLE语句。
它实际是删除原来的表，并重新创建一个表，而不是逐行删除表中数据。

查询

检索单列：SELECT 列名 FROM 表名;
检索多列:SELECT 列名1,列名2,... FROM 表名;
检索所有列：SELECT * FROM 表名;

去重 DISTINCT

SELECT DISTINCT 列名 FROM 表名;

返回特定的几行 LIMIT

SELECT 列名 FROM 表名 LIMIT 开始位置,要检索的行数;
SELECT 列名 FROM 表名 LIMIT 检索行数 OFFSET 开始位置
此时检索出来的第一行为行0，在行数不够时，结果返回检索的最大行数。

分组 GROUP BY

使用规则:
1.可以包含任意数目的列
2.数据将在最后规定的分组上汇总
3.子句中列出的每个列都必须是检索列或有效的表达式
4.除聚集运算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
5.NULL值列单独分成一组
6.GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
此时筛选要使用HAVING子句。

排序 ORDER BY

使用ORDER BY子句，按多列排序时，列名之间用逗号分隔。
默认为升序排序，若要降序排序需使用DESC关键字。

筛选 WHERE

使用WHERE子句对数据进行筛选。
可使用操作符：

操作符	说明
=	等于
<> / !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN...AND...	在指定的两个值之间
AND	必须满足所有条件
OR	满足所给条件之一
IN	指定条件的范围
NOT	否定后续条件

AND的优先级比OR高。
HAVING和WHERE的区别：
HAVING是在分组后进行筛选，而WHERE是分组前筛选。

通配符 LIKE

用来匹配值的一部分。
% : 任意字符出现任意次数，无法匹配NULL
_ : 匹配单个字符

聚集函数

函数	说明	备注
AVG()	返回某列的平均值	AVG()只用于单列，多列求平均需使用多个AVG函数，计算中忽略键值为NULL的行
COUNT()	返回某列的行数	当括号内为*时，不忽略NULL 当括号内为某列名时，忽略NULL
MAX()	返回某列的最大值	函数计算中忽略NULL，当用于文本数据时，如果数据按相应列排序，则MAX函数返回最后一行
MIN()	返回某列的最小值	函数计算中忽略NULL，当用于文本数据时，如果数据按相应列排序，则MIN函数返回最前面的行
SUM()	返回某列值之和	函数计算中忽略NULL

表连接

内连接：INNER JOIN
外连接：
左外连接：LEFT JOIN
右外连接：RIGHT JOIN

其他

拼接多个字段：Concat(列名1,' (',列名2,')')
删除字段空格：

LTrim() # 去掉左边的空格
RTrim() # 去掉右边的空格
Trim() # 去掉两边的空格

使用别名:AS

DCL

数据权限的控制

用户

创建新用户：CREATE USER
重命名：RENAME USER
删除用户：DROP USER

权限

赋予权限：GRANT
使用该语句时需要给出的信息：要授予的权限，被授予访问权限的数据库或表，用户名
收回权限：REVOKE

事务

指一组SQL语句。
事务开始：START TRANSACTION
回退：撤销指定的SQL语句。ROLLBACK;无法回退CREATE和DROP语句。
提交：将未存储的SQL语句结果写入数据库。COMMIT;

DDL

数据定义语言

逻辑库

创建：CREATE DATABASE 名称;
显示：SHOW DATABASE;
删除：DROP DATABASE 名称;

数据表

创建:

CREATE TABLE 数据表(
	列名1 数据类型 [约束] [COMMENT 注释].
	列名2 数据类型 [约束] [COMMENT 注释].
	...
)[COMMENT 注释];

举个栗子:
创建一个员工表

create table employees (
id int(11) primary key,
name varchar(45) not null,
idcard char(18) not null,
phone char(11) not null,
sex varchar(10),
userid int(11),
postid int(11),
departmentid int(11),
foreign key(userid) references users(id),
foreign key(postid) references post(id),
foreign key(departmentid) references department(id)
)

查看有哪些数据表:SHOW TABLES
查看数据表的信息:DESC 表名
查看创建数据表的SQL语句:SHOW CREATE TABLE 表名
更新数据表:ALTER TABLE 表名 修改选项
修改选项的语法格式如下：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

eg:给表中字段添加UNIQUE属性
ALTER TABLE 表名 ADD UNIQUE(列名)
重命名数据表:RENAME TABLE 表名
删除数据表:DROP TABLE 名称
AUTO_INCREMENT:为该列设置自动增量
DEFAULT:为该列设置默认值
在MySQL中，在设置删除数据的限制条件时，为数据表设置了别名，删除数据时应在DELETE后加上表别名

视图

创建视图:

CREATE VIEW 视图名 AS
查询语句

删除视图：
DROP VIEW 视图名
更新视图：
可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。
使用规则：
1.唯一命名
2.不能有索引，也不能有关联的触发器或默认值
3.ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将会被覆盖。
4.创建视图时，必须有足够的访问权限。

存储过程

创建：

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	执行语句
END;

执行：
CALL 存储过程名(参数列表);
删除：
DROP PROCEDURE 存储过程名[IF EXISTS];
显示创建存储过程的CREATE语句:
SHOW CREATE PROCEDURE 存储过程名

游标

是一个SELECT语句检索出来的结果集，只能用于存储过程和函数。
创建：DECLARE 游标名 CURSOR FOR SELECT语句
打开：OPEN 游标名
关闭：CLOSE 游标名
使用：在游标被打开后，可以使用FETCH语句分别访问它的每一行。

触发器

触发器是MySQL响应INSERT,DELETE,UPDATE而自动执行的一条MySQL语句。
创建触发器时需要给出：触发器名，关联的表，响应语句，何时执行。
创建：CREATE TRIGGER
删除：DROP TRIGGER
使用：
INSERT触发器：在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行，在BEFORE INSERT触发器中，NEW中的值也可以被更新。对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
DELECT触发器：在DELECT触发器内，可以引用一个名为OLD的虚拟表，访问被删除的行，该表中所有值均为只读的，不可被更新。
UPDATE触发器：将更新操作看作为先删除再插入，在UPDATE触发器内，可以引用一个名为OLD的虚拟表，访问被删除的行，也可引用一个名为NEW的虚拟表，访问被插入的行。在BEFORE UPDATE触发器中，NEW中的值也可以被更新。OLD表为只读属性。

注释

# 这是一行注释
/*这是一行注释*/

–Not the end–

参考资料

1.《MySQL必知必会》
2.link