SQL语句复习

数据库定义语言（DDL）

数据库操作

• 创建数据库
  create database 数据库名
• 为了能够支持中文，我们在创建时可以设定编码格式：
  CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
• 删除数据库：
  drop database 数据库名

创建表

create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])

SQL数据类型

以下的数据类型用于字符串存储：

• char(n)可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充。
• varchar(n)也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充。

以下数据类型用于存储数字：

• smallint用于存储小的整数，范围在 (-32768，32767)
• int用于存储一般的整数，范围在 (-2147483648，2147483647)
• bigint用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
• float用于存储单精度小数
• double用于存储双精度的小数

以下数据类型用于存储时间：

• date存储日期
• time存储时间
• year存储年份
• datetime用于混合存储日期+时间

列级约束

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 check （MySQL不支持）、默认default 、非空/空值 not null/ null

表级约束条件

表级约束有四种：主键、外键、唯一、检查

修改表

ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
                         [DROP COLUMN 列名[restrict|cascade]]
                         [ALTER COLUMN 列名 新数据类型]

我们可以通过ADD来添加一个新的列，通过DROP来删除一个列，不过我们可以添加restrict或cascade，默认是restrict，表示如果此列作为其他表的约束或视图引用到此列时，将无法删除，而cascade会强制连带引用此列的约束、视图一起删除。还可以通过ALTER来修改此列的属性。

删除表

DROP TABLE 表名[restrict|cascade]

数据库操纵语言（DML）

插入数据

INSERT INTO 表名 VALUES(值1， 值2， 值3)
如果插入的数据与列一一对应，那么可以省略列名，但是如果希望向指定列上插入数据，就需要给出列名：
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)
一次性向数据库中插入多条数据：
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

修改数据

UPDATE 表名 SET 列名=值,...WHERE 条件

删除数据

DELETE FROM 表名 WHERE 条件

数据库查询语言（DQL）

单表查询

-- 查询某列数据
SELECT 列名[，列名] FROM 表名
-- 以别名显示该列
SELECT 列名 别名 FROM 表名
-- 查询所有数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名
-- 添加限定条件
SELECT * FROM 表名 WHERE 条件

常用查询条件

• 一般的比较运算符，包括=、>、<、>=、<=、!=等
• 是否在集合中：in、not in
• 字符模糊匹配：like，not like
• 多重条件连接查询：and、or、not

排序查询

SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC
使用ASC表示升序排序，使用DESC表示降序排序，默认为升序。
我们也可以可以同时添加多个排序：
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC
这样会先按照列名1进行排序（主要关键字），每组列名1相同的数据再按照列名2排序（次要关键字）。

聚集函数

聚集函数一般用作统计，包括：

• count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
• count([distinct]列名)统计某列的值总和
• sum([distinct]列名)求一列的和（注意必须是数字类型的）
• avg([distinct]列名)求一列的平均值（注意必须是数字类型）
• max([distinct]列名)求一列的最大值
• min([distinct]列名)求一列的最小值
  使用示例：
  SELECT count(distinct 列名) FROM 表名 WHERE 条件

多表查询

多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询。

SELECT * FROM 表1, 表2

直接这样查询会得到两张表的笛卡尔积，也就是每一项数据和另一张表的每一项数据都结合一次，会产生庞大的数据。

SELECT * FROM 表1, 表2 WHERE 条件

这样，只会从笛卡尔积的结果中得到满足条件的数据。

注意：如果两个表中都带有此属性吗，需要添加表名前缀来指明是哪一个表的数据。

外连接查询

外连接就是专门用于联合查询情景的，比如现在有一个存储所有用户的表，还有一张用户详细信息的表，我希望将这两张表结合到一起来查看完整的数据，我们就可以通过使用外连接来进行查询，外连接有三种方式：

• 通过使用inner join进行内连接，只会返回两个表满足条件的交集部分：
  

• 通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已，这里就不再介绍了）：

嵌套查询

我们可以将查询的结果作为另一个查询的条件，比如：

SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)

数据库控制语言(DCL)

庞大的数据库不可能由一个人来管理，我们需要更多的用户来一起管理整个数据库。

创建用户

CREATE USER 用户名 identified by 密码
也可以不带密码

登录用户

login -u 用户名 -p
输入密码后即可登陆此用户

用户授权

我们可以通过使用grant来为一个数据库用户进行授权：
grant all|权限1，权限2...(列1,...) on 数据库.表 to 用户 [with grant option]
其中all代表授予所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户。

我们可以使用revoke来收回一个权限：
revoke all|权限1，权限2...(列1,...) on 数据库.表 from 用户