MySQL-基础-DDL、DML、DCL、TCL详解

MySQL-基础-DDL、DML、DCL、TCL详解

总结一些常用的SQL命令和样例

首先由一张思维导图开始

TCL （Transaction Control Language）

事务控制语言

• TRANSACTION 事务

• SAVEPOINT 设置保存点

• ROLLBACK 回滚

• START TRANSACTION 开始事务

• COMMIT 提交事务

DML（Data Manipulation Language）

它们是SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作的语言

INSERT

1. 单条语句插入

INSERT INTO 表名(字段1,...,字段n) VALUES(字段1值,...,字段n值);

2. 多条语句插入

INSERT INTO 表名(字段1,...,字段n)
VALUES
(字段1值,...,字段n值), 
(字段1值,...,字段n值), 
(字段1值,...,字段n值);

3. select多条插入

select后面的字段一定要和要插入的表的字段属性一致（数量和类型）

INSERT INTO 表名
SELECT 字段1,...,字段n
  FROM 表（或字段集合）
 WHERE 条件;

DELETE

1. 按条件删除

DELETE FROM 表名 WHERE 条件;

UPDATE

1. 按条件更新

UPDATE 表名 SET 要变更字段 WHERE 条件;

SELECT

查询的时候尽量少使用 * （星号），这样会遍历所用的字段，影响性能，最好是查什么就写什么

1. 单表查询

SELECT 字段1,...,字段n
  FROM 表名（或查询的集合）
 WHERE 条件;

多表查询时，尽量少使用join…on这样的语句，因为如果使用，那么SQL引擎就会先把两个表做笛卡尔积，然后在根据on的条件进行筛选返回，这样还是对多个表进行了查询，影响性能，最好是使用where子句来进行SQL嵌套查询，因为使用where是把满足条件的查询出来，然后返回结果，所以性能会好一些，个人观点如有不足请指出。对于SQL执行顺序可以查看另一篇博客SQL的执行顺序

2. 多表查询

1).多表查询 where

AS 后面的别名可以随便起名，合法就行，并且表可以有多张表（这里只列举 两张表）

SELECT A.字段1,...,A.字段n,B.字段1,...,B.字段n
  FROM 表1 AS A, 表2 AS B 
 WHERE 条件;

2).多表查询 join … on / left join … on / right join … on

• 这里值举join … on 的用法，另外两个和前者用法一样
• left join … on： 查询出来的是两个表（或多张表）满足条件的部分，并且保留FROM（或者左面的表）后面的表的数据
• right join … on： 查询出来的是两个表（或多张表）满足条件的部分，并且保留JOIN（或者右面的表）后面的表的数据

SELECT A.字段1,...,A.字段n,B.字段1,...,B.字段n
  FROM 表1 AS A
  JOIN 表2 AS B
    ON (A.字段 = B.字段...)
 WHERE 条件;

DDL（Data Definition Language）

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

CREATE

1. 创建表

一张表只能有一个主键，但是主键可以有联合主键

CREATE TABLE 表名(
    字段1名 类型 [CHARACTER SET 编码集 ][NOT NULL][PRIMARY KEY],
    ... ...
    字段n名 类型 [CHARACTER SET 编码集 ][NOT NULL][PRIMARY KEY] 
) ENGINE = 引擎类型(eg:INnoDB);

2. 创建视图

CREATE VIEW 视图名
    AS 
SELECT 视图字段1,...,视图字段n
  FROM 表名
 WHERE 条件;

3. 创建索引

联合索引

1. 查询条件中出现联合索引第一列,或者全部,则能利用联合索引.
2. 条件列中只要条件相连在一起,就是联合索引.
3. 查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

创建索引是如果是多个字段及时联合索引，要注意联合索引的用法，否则用错了就不会触发索引，那样的话查询会很慢

CREATE INDEX 索引名
    ON 表名(字段名1,...,字段名n);

4. 创建存储过程

DROP PROCEDURE IF EXISTS 存储过程名;
DELIMITER $
CREATE PROCEDURE 存储过程名(
    参数列表
)
BEGIN
    ...
    逻辑SQL语句;
    ...
END $
DELIMITER ;

5. 创建函数

DROP FUNCTION IF EXISTS 函数名;
DELIMITER $
CREATE FUNCTION 函数名(参数列表)RETURNS 返回类型 
BEGIN
    ...
    逻辑SQL语句;
    ...
    RETURN 返回类型值;
END $
DELIMITER ;

ALTER-修改

1. 删除表的某个字段

ALTER TABLE 表名 DROP COLUMN 字段名;

2. 当表中的字段名和MySQL的关键字重名时要使用 数字键旁边的点作为转义才可以改变

ALTER TABLE USER CHANGE `PASSWORD VARCHAR` PASSWORD VARCHAR(20) CHARACTER SET UTF8 NOT NULL;

3. 表字段重命名

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

4. 为表添加主键（外键同理）

ALTER TABLE表名 ADD
CONSTRAINT 主键名 PRIMARY KEY (表中的字段名);

5. 添加注释（字段和表）

-- 表注释
ALTER TABLE 表名 COMMENT='这是表的注释';  

-- 字段注释
ALTER TABLE 表名 
MODIFY `字段名` [字段类型] [字段默认值] COMMENT '这是字段的注释' ;

DROP-删除（表级）

DROP 表名;

TRUNCAT-清空表数据

TRUNCAT 表名;

DCL（Data Control Language）

数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

GRANT-赋权

GRANT 权限1,...,权限n ON 表名 TO 用户名;

REVOKE-撤权

REVOKE 权限1,...,权限n ON 表名 TO 用户名;