SQL 语句

1 数据定义语言（Data Definition Language, DDL）

负责数据结构定义与数据库对象定义

1.1 CREATE

1.1.1 创建数据库

CREATE DATABASE test;

1.1.2 创建表

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

1.2 DROP

1.2.1 删除数据库

DROP DATABASE test;

1.2.2 删除表

DROP TABLE user;

1.3 ALTER

1.3.1 添加列

ALTER TABLE user
ADD age int(3);

1.3.2 删除列

ALTER TABLE user
DROP COLUMN age;

1.3.3 修改列

ALTER TABLE `user`
MODIFY COLUMN age tinyint;

1.3.4 添加主键

ALTER TABLE user
ADD PRIMARY KEY (id);

1.3.5 删除主键

ALTER TABLE user
DROP PRIMARY KEY;

2 数据操纵语言（Data Manipulation Language, DML）

对数据库其中的对象和数据运行访问

2.1 INSERT

2.1.1 插入整行数据

INSERT INTO user
VALUES (10, 'admin', 'root', 'xxx@163.com');

2.1.2 插入部分字段

INSERT INTO user(username, password, email)
VALUES ('admin', 'root', 'xxx@163.com');

2.2 UPDATE

更新表中的记录

UPDATE user
SET username='Sally', password='123'
WHERE username = 'admin';

2.3 DELETE

删除表中的记录

DELETE FROM user
WHERE username = 'Sally';

2.4 TRUNCATE

清空表中的数据

TRUNCATE TABLE user;

2.5 SELECT

2.5.1 查询指定列

SELECT username, email FROM user;

2.5.2 查询所有列

SELECT * FROM user;

2.5.3 查询结果去重

SELECT DISTINCT * FROM user;

2.5.4 限制查询结果

-- 返回前 5 行
SELECT * FROM user LIMIT 5;
SELECT * FROM user LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM user LIMIT 2, 3;

2.5.5 子查询

嵌套在较大查询中的 SQL 查询

• 可以嵌套在 SELECT，INSERT，UPDATE 或 DELETE 语句内或另一个子查询中
• 通常会在另一个 SELECT语句的 WHERE 子句中添加
• 可以使用比较运算符，如 >，<，或 =
  • 比较运算符也可以是多行运算符，如 IN，ANY 或 ALL
• 必须被圆括号 () 括起来
• 在其父查询之前执行，以便可以将内部查询的结果传递给外部查询

-- 多层嵌套
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

2.6 WHERE

过滤记录，即缩小访问数据的范围

2.6.1 IN

在指定的几个特定值中任选一个值

SELECT * FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

2.6.2 BETWEEN

选取介于某个范围内的值

SELECT * FROM products
WHERE prod_price BETWEEN 3 AND 5;

2.6.3 LIKE

字符串模糊匹配

• % 表示任何字符出现任意次数
• _ 表示任何字符出现一次

SELECT prod_id, prod_name, prod_price FROM products
WHERE prod_name LIKE '%bean bag%';

SELECT prod_id, prod_name, prod_priceFROM products
WHERE prod_name LIKE '__ inch teddy bear';

2.6.4 =/<>/</>/<=/>=/AND/OR/NOT

2.7 聚合函数

• AVG()：平均值
• COUNT()：结果行数
• MAX()：最大值
• MIN()：最小值
• SUM()：总数

2.8 ORDER BY

• ASC ：升序（默认）
• DESC ：降序

-- 指定多个列的排序方向
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

3 事务控制语言（Transaction Control Language, TCL）

管理数据库中的事务

COMMIT 和 ROLLBACK

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

4 数据控制语言（Data Control Language, DCL）

控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权

• CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES