常用的SQL语句

2020-04-23

数据库

字数统计: 2.6k字 | 阅读时长≈ 9分钟

常用的SQL语句

DDL

DDL是操作是数据库和表的SQL语句。

1. 操作数据库

创建
- 创建数据库
  - CREATE DATABASE 数据库名称；
- 创建数据库，判断不存在，再创建
  - CREATE DATABASE IF NOT EXISTS 数据库名称；
- 创建数据库，并指定字符集
  - CREATE DATABASE 数据库名称 character set 字符集名；
- 创建数据库，判断不存在，再创建，并指定字符集
  - CREATE DATABASE IF NOT EXISTS 数据库名称 character set 字符集名；
查询
- 查询所有数据库的名称
  - SHOW DATABASES；
- 查询某个数据库的字符集，查询某个数据库的查询语句
  - SHOW CREATE DATABASE 数据库名称；
修改
- 修改数据库的字符集
  - ALTER DATABASE 数据库名称 character set 字符集名；
删除
- 删除数据库
  - DROP DATABASE 数据库名称；
- 判断数据库存在，再删除
  - DROP DATABASE IF EXISTS 数据库名称；
使用数据库
- 查询当前正在使用的数据库名称
  - SELECT DATABASE();
- 使用数据库
  - USE 数据库名称；

2. 操作表

创建
- 创建表
```
CREATE TABLE 表名称(
    列名1，数据类型1，
    列名2，数据类型2，
    列名3，数据类型3，
    .......
    列名n，数据类型n
)；
```
- 注意最后一列后面不用加逗号
- SQL中常用的数据类型
  1. int：整数类型
  2. double：小数类型
  3. date：日期，只包含年月日，yyyy-MM-dd
  4. datetime：日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  5. timestamp：时间戳类型，包含年月日时分秒，不赋值时可以默认添加当前系统时间
  6. varchar：字符串
- 复制表
  - CREATE TABLE 表名 LIKE 被复制的表名；
查询
- 查询某个数据库中所有表的名称
  - SHOW TABLES；
- 查询表结构
  - DESC 表名称；
修改
- 修改表名
  - ALTER TABLE 表名称 RENAME TO 新的表名；
- 修改表的字符集
  - ALTER TABLE 表名称 character set 字符集名；
- 添加一列
  - ALTER TABLE 表名称 ADD 列名数据类型；
- 修改列名和类型
  - ALTER TABLE 表名称 CHANGE 列名新列名新数据类型；
  - ALTER TABLE 表名称 MODIFY 列名新数据类型；
- 删除列
  - ALTER TABLE 表名称 DROP 列名；
删除
- 删除表
  - DROP TABLE 表名称；
- 判断表存在，再删除
  - DROP TABLE IF EXISTS 表名称；

DML

DML是增删改数据库的表中的数据的SQL语句。

添加数据
- INSERT INTO 表名(列名1，列名2，列名3，…..列名n) VALUES(值1，值2，值3，…..值n);
- 注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值。
  - INSERT INTO 表名VALUES(值1，值2，值3，…..值n);
3. 除了数字类型，其他类型需要使用引号（单双引号都可以）引起来。
删除数据
- DELETE FROM 表名 [WHERE 条件]；
- 注意：如果不加条件，则删除表中所有记录。
- 如果要删除表中所有记录，不推荐使用DELETE FROM 表名；，可以使用TRUNCATE TABLE 表名；。
修改数据
- UPDATE 表名 SET 列名1=值1，列名2=值2，….列名n=值n [WHERE 条件]；
- 注意：如果不加任何条件，则会将表中所有记录修改。

DQL

DQL是查询表中记录的SQL语句。

SELECT * FROM 表名；

语法：

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段
HAVING
    分组之后的条件
ORDER BY
    排序
LIMIT
    分页限定

1. 基础查询

多个字段的查询
- SELECT 字段名1，字段名2… FROM 表名；
去除重复
- SELECT DISTINCT 字段名1，字段名2… FROM 表名；
计算列
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
- IFNULL（表达式1，表达式2）：NULL参与的运算，计算结果都为NULL。
  - 表达式1：哪个字段需要判断是否为NULL。
  - 表达式2：如果该字段为NULL后的替换值。
起别名
- SELECT 字段名1 AS 别名 FROM 表名；
- 注意：AS也可以省略，用一个空格代替。

2. 条件查询

WHERE子句后跟条件。
运算符
- < > >= <= <> =
- BETWEEN…AND
- IN(集合)
- LIKE：模糊查询
  - 占位符：
    - _：单个任意字符。
    - %：多个任意字符。
- IS NULL
- AND 或 &&
- OR 或 ||
- NOT 或！

3. 排序查询

语法
- ORDER BY 排序字段1 排序方式1，排序字段2 排序方式2….
排序方式
- ASC：升序，默认的。
- DESC：降序。
- 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

4. 聚合函数

将一列数据作为一个整体，进行纵向的计算。

注意：聚合函数的计算会排除NULL值

解决方案：1.选择不包含空的列（主键）进行计算。2.IFNULL函数。

COUNT；计算个数。
- SELECT COUNT(字段名) FROM 表名；
MAX；计算最大值。
- SELECT MAX(字段名) FROM 表名；
MIN；计算最小值。
- SELECT MIN(字段名) FROM 表名；
SUM：计算和。
- SELECT SUM(字段名) FROM 表名；
AVG；计算平均值。
- SELECT AVG(字段名) FROM 表名；

5. 分组查询

语法：
- GROUP BY 分组字段；
- SELECT 分组字段, 聚合函数1，…. FROM 表名 GROUP BY 分组字段;
注意：1.分组之后查询的字段只能是分组字段或者聚合函数。2.在分组之前可以用WHERE进行条件限定且后面不可以跟聚合函数判断，如果要在分组之后进行条件限定需要用HAVING且后面可以跟聚合函数判断。

SELECT 分组字段, 聚合函数1，.... FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 条件;

6. 分页查询

语法：
- LIMIT 开始的索引，每页查询的条数；
- SELECT * FROM 表名 LIMIT 开始的索引, 每页查询的条数;

LIMIT是一个“方言”，只能在MySQL中用。

DCL

1.管理用户

添加用户：
- CREATE USER ‘用户名‘@’主机名’ IDENTIFIED BY ‘密码’；
删除用户：
- DROP USER ‘用户名‘@’主机名’；
修改用户密码：
- UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’；
- SET PASSWORD FOR ‘用户名‘@’主机名’ = PASSWORD(‘新密码’)；
- MySQL中忘记了root用户的密码怎么办？
  1. cmd –> net stop mysql80 停止mysql服务
  2. 使用无验证方式启动mysql服务：mysqld –skip-grant-tables
  3. 打开新的cmd窗口，直接输入mysql命令，敲回车，就可以登陆成功
  4. USE mysql；
  5. UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘root’；
  6. 关闭两个窗口
  7. 打开任务管理器，手动关闭mysqld.exe的进程
  8. 启动mysql服务
  9. 使用新密码登录
查询用户：
1. 切换到mysql数据库。USE mysql;
2. 查询user表。SELECT * FROM user;
- 通配符：%表示可以在任意主机使用用户登录数据库。

2.权限管理

查询权限：
- SHOW GRANTS FOR ‘用户名‘@’主机名’;
授予权限
- GRANT 权限列表 ON 数据库名.表名 TO ‘用户名‘@’主机名’;
- GRANT ALL ON . TO ‘用户名‘@’主机名’; 给一个用户授予对任意数据库中任意表进行任意操作的权限。
撤销权限
- REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名‘@’主机名’;

约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

1.非空约束

某一列的值不能为NULL。

创建表时添加非空约束

CREATE TABLE 表名(
    字段名1 数据类型1，
    字段名2 数据类型2 NOT NULL，
    .....
);

创建表完后添加非空约束

ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL；
删除非空约束

ALTER TABLE 表名字段名数据类型；

2.唯一约束

某一列的值不能重复。

创建表时，添加唯一约束

CREATE TABLE 表名(
    字段名1 数据类型1，
    字段名2 数据类型2 UNIQUE，
    .....
);

删除唯一约束

ALTER TABLE 表名 DROP INDEX 字段名；
在表创建完后，添加唯一约束

ALTER TABLE 表名 MODIFY 字段名数据类型 UNIQUE；

3.主键约束

某一列的值非空且唯一，一张表只能有一个字段为主键，就是表中记里的唯一标识。

创建表时，添加主键约束

CREATE TABLE 表名(
    字段名1 数据类型1，
    字段名2 数据类型2 PRIMARY KEY，
    .....
);

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY；
创建完表后，添加主键

ALTER TABLE 表名 MODIFY 字段名数据类型 PRMARY KEY；
自动增长
- 如果某一列是数值类型，使用AUTO_INCREMENT可以完成值自动增长，一般可以配合数值类型的主键使用。
- 注意：自动增长的值为最后一条记录自动增长字段的值加一
- 创建表时，添加主键，并且完成自动增长
```
CREATE TABLE 表名(
    字段名1 数据类型1，
    字段名2 数据类型2 PRIMARY KEY AUTO_INCREMENT，
    .....
);
```
- 删除自动增长
  
  ALTER TABLE 表名 MODIFY 字段名数据类型；
- 添加自动增长
  
  ALTER TABLE 表名 MODIFY 字段名数据类型 AUTO_INCREMENT；

4.外键约束

让表与表产生关系，从而保证数据的正确性，外键可以为NULL，但是不能是主表中不存在的值。

创建表时，添加外键

CREATE TABLE 表名(
    字段名1 数据类型1，
    字段名2 数据类型2，
    .....
    CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);

删除外键

ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称；
创建表之后，添加外键

ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)；
级联操作

可以在外键关联的主表中的列进行操作时，外键中的值自动更新。
- 级联更新
  - 在添加外键语句后加上ON UPDATE CASCADE
    
    ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE；
- 级联删除
  - 在添加外键语句后加上ON DELETE CASCADE
    
    ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE；

级联更新与级联删除可以同时存在。

命令行操作数据库备份和还原

备份：MYSQLDUMP -u用户名 -p密码数据库名称 > 保存的路径；
还原：
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 使用文件。SOURCE 文件路径

版权声明： 本博客所有文章除特别声明外，均采用 Apache License 2.0 许可协议。转载请注明出处！