01. SQL Basics

01. SQL 基础

不要在系统库里执行任何操作！！！

1. SQL 通用语法

1. SQL 可以单行或多行书写，分号结尾

2. 可以使用空格、缩进

3. 不区分大小写，关键字建议大写

4. 注释

5. 1. 单行注释：-- + 注释内容 / # + 注释内容（仅限MySQL）

   2. 多行注释：/* 注释内容 */

2. DDL（Data Definition Language）

数据定义语言，定义数据库对象：数据库、表、字段

2.1. 数据库操作

2.1.1. 查询

• 查询所有数据库 SHOW DATABASES;

系统库自带 4 个。

• 查询当前是哪个数据库 SELECT DATABASE();

2.1.2. 创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET / 字符集] [COLLATE 排序规则];

2.1.3. 删除

DROP DATABSE [IF EXITS] 数据库名

2.1.4. 使用

USE 数据库名

2.2. 表操作

2.2.1. 查询

• 查询当前数据库所有表 SHOW TABLES;

• 查询表结构 DESC 表名;

• 查询指定表的建表语句SHOW CREATE TABLE 表名;

2.2.2. 创建

CREATE TABLE 表名(
  字段1 字段1类型[COMMENT 字段1注释],
  字段2 字段2类型[COMMENT 字段2注释],
  字段3 字段3类型[COMMENT 字段3注释],
  ...
  字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

2.2.3. 修改

添加字段ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束];

修改字段数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释] [约束];

修改表名ALTER TABLE 表名 RENAME TO 新表名;

2.2.4. 删除

删除字段ALTER TABLE 表名 DROP 字段名;

删除表DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表（没有任何数据，只有表结构）TRUNCATE TABLE 表名;

3. DML（Data Manipulation Language）

数据操作语言，对数据库表中的数据进行增、删、改

3.1. INSERT 添加数据

1. 给指定字段添加数据INSERT INTO 表名(字段名1，字段名2，...) VALUES(值1，值2);

2. 给全部字段添加数据INSERT INTO 表名 VALUES(值1，值2，...);

3. 批量添加数据

4. 1. INSERT INTO 表名(字段名1，字段名2，...) VALUES(值1，值2)，(值1，值2)，(值1，值2);

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

3.2. UPDATE 修改数据

1. UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ...[WHERE 条件];

如果没有条件，默认修改整张表的数据。

3.3. DELETE 删除数据

1. DELETE FROM 表名 [WHERE 条件]

2. 不能删除数据的某个字段，如果需要，可以用 UPDATE 设为 NULL

4. DQL（Data Query Language）

数据查询语言，查询表中记录

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

4.1. 基本查询

1. 查询多个字段

2. 1. SELECT ... FROM ...;

   2. SELECT * FROM ...;

   3. 尽量不要写 * ，实际开发中

3. 设置别名

4. 1. SELECT ... [AS ...] ...FROM ...;

5. 去除重复记录

6. 1. SELECT DISTINCT ... FROM ...;

4.2. 条件查询

1. SELECT ... FROM ... WHERE ...;

2. 条件有：>、<、=、BETWEEN ... AND、模糊匹配（LIKE _ / %）、IS NULL等

3. BETWEEN 最小值... AND 最大值...

4.3. 聚合函数

1. 一列数据作为一个整体进行纵向计算

2. min、max、count、avg、sum

3. SELECT ... FROM ...

4. 不计算 NULL 值的

4.4. 分组查询

1. SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...

• WHERE 不能使用聚合函数 HAVING 可以

• 执行顺序：WHERE -> 聚合函数 -> having

• 分组之后查询的字段一般为聚合函数和分组字段

4.5. 排序查询

1. SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

2. 排序方式：ASC：升序、DESC：降序

3. 默认是升序

4.6. 分页查询

1. SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

• 起始索引从 0 开始，起始索引 = （查询页码 - 1）* 每页显示记录数

• 分页查询是 数据库 的方言，不同数据库用不同方法实现

5. DCL（Data Control Language）

数据控制语言，创建数据库用户、控制数据库的访问权限

5.1. 管理用户

1. 查询用户USE mysql; / SELECT * FROM user;

2. 创建用户CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'

3. 修改用户密码ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4. 删除用户DROP USER '用户名'@'主机名';

• 主机名可以使用 % 通配。

• 这类操作主要是 DBA 使用。

5.2. 权限控制

1. 查询权限SHOW GRANTS FOR '用户名'@'主机名';

2. 授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3. 撤销操作REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

• 多个权限之间，使用逗号进行分隔

• 授权时，数据库名和表名可以使用 * 进行通配

6. 案例

1. 编号

2. 工号

3. 姓名

4. 性别

5. 年龄

6. 身份证号

7. 入职时间

create table emp(
  id int comment '编号',
  workno varchar(10) comment '工号',
  name varchar(10) comment '姓名',
  gender char(1) comment '性别',
  age tinyint unsigned comment '年龄',
  idcard char(18) comment '身份证号',
  entrydate date comment '入职时间'
) comment '员工表';

6.1.1. 添加字段

alter table emp add nickname varchar(20) comment '昵称';

6.1.2. 修改字段

alter table emp change nickname username varchar(30) comment '用户名';

6.1.3. 删除字段

alter table emp drop username;