Sql语法

$注意：$ 个人博客传送门

SQL

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾(默认是以分号结尾的)
2. SQL语句可以使用空格 / 缩进来增强语句的可读性
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
4. 注释：
   1. 单行注释：–注释内容 或 # 注释内容(MySQL特有)
   2. 多行注释：/* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL - 介绍

DDL - 数据库操作

查询

• 查询所有数据库

SHOW DATABASES;

• 查询当前数据库

SELECT DATABASE();

创建

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

删除

DROP DATABASE[IF EXISTS] 数据库名;

使用

USE 数据库名;

DDL - 表操作 - 查询

• 查询当前数据库所有表

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查询指定表的建表语句

SHOW CREATE TABLE 表名;

DDL - 表操作 - 创建

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

#注意：[...]为可选参数，最后一个字段后面没有逗号

例1：

ID	name	age	gender
1	令狐冲	28	男
2	风清扬	68	男
3	东方不败	32	男

• 建表语句如图：

• 表结构如图：

DDL - 表操作 - 数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型，字符串类型，日期时间类型。

数值类型：

字符串类型：

时间类型

DDL - 表操作 - 修改

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

案例：

为emp表增加一个新的字段 "nickname", 类型为varchar(20)

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

修改数据类型

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

修改字段名和字段类型

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

案例：

将emp表的nickname字段修改为username，类型为varchar(30)

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

删除字段

ALTER TABLE 表名 DROP 字段名;

案例：

将emp中的字段username删除

alter table emp drop username;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例：

将emp表的表名修改为employee；

alter table emp rename to employee;

DDL - 表操作 - 删除

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

注意：在删除表时，表中的全部数据也会被删除。

DML - 介绍

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

添加数据(INSERT)

给指定字段添加数据

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

给全部字段添加数据

INSERT INTO 表名 VALUES(值1, 值2);

批量添加数据

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

/*
注意：
	插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
	字符串和日期型数据应该包含在引号中。
	插入数据大小，应该在字段规定范围内。
*/

修改数据(UPDATE)

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

# 注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据(DELETE)

DELETE FROM 表名 [WHERE 条件];

/*
注意：
	DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
	DELETE 语句不能删除某个字段的值(可以使用UPDATE)。
*/

DQL - 介绍

• DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

• 查询关键字：SELECT

DQL - 语法

注意：where是分组之前过滤，having是分组之后过滤

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

基本查询

• 查询多个字段

SELECT 字段1，字段2，字段3....FROM 表名;
SELECT * FROM 表名;

• 设置别名

SELECT 字段1 [AS 别名1], 字段2[AS 别名2] .... FROM 表名;

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询（WHERE）

语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

聚合查询（聚合函数：count, max, min, avg, sum）

语法

SELECT 聚合函数(字段列表) FROM 表名;

SELECT count(*) from emp; # 所有员工的数量

SELECT avg(age) from emp; # 所有员工的平均年龄

SELECT max(age) from emp; # 所有员工年龄的最大值

SELECT min(age) from emp; # 所有员工年龄的最小值

SELECT sum(age) from emp where workadress = '西安'; # 西安地区员工的年龄总和

注意：所有的null值不参与聚合函数运算

分组查询（GROUP BY）

语法

SELECT 字段列表 FROM 表名[WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件]; # 两个条件
where 和 having 的不同
# 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组; 而having是分组之后对结果进行过滤
# 判断时机不同：where不能对聚合函数进行判断，而having可以

# 根据性别分组，统计不同性别男性员工和女性员工的数量
select gender, count(*) from emp group by gender;

# 根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;

# 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select count(*) from emp where age < 45 group by workaddress having count(*) >= 3;

注意：
# 执行顺序：where > 聚合函数 > having
# 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询（ORDER BY）

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式2; # 支持多字段排序

注意：
如果是多字段排序，当第一个字段相同时，才会根据第二个字段排序

排序方式

• ASC：升序（默认值）

• DESC：降序

# 根据年龄按照升序排序, 降序
select * form emp order by age asc;
select * form emp order by age desc;

# 根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;

# 根据年龄对公司员工进行升序排序，年龄相同，在按照入职时间进行降序排序
select * from emp order by age asc, entrydate desc;

分页查询（LIMIT）

语法

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

注意：
# 起始索引从 0 开始，起始索引 = (查询页码 - 1) * 每页记录数，比如查询第1页，需要输入0
# 分页查询是数据库的方言，不同数据库有不同的实现，Mysql中是LIMIT。
# 如果查询的是第一业的数据，起始索引可以省略，直接写成LIMIT 10;

# 查询第1页员工数据，每页展示10条记录
select * from emp limit 0, 10;

# 查询第二页员工的数据，每页展示10条记录 -------> (页码 - 1) * 展示记录数
select * from emp limit 10, 10;

案例练习

#查询年龄为20，21，22，23岁的女性员工信息
select * from emp where gender = '女' and age = in(20, 21, 22, 23);

#查询性别为男，并且年龄在 20 - 40岁(含)以内的姓名为3个字的员工
select * from emp where gender = '男' and age between 20 and 40 and name like '___';

#统计表中，年龄小于60岁的，男性员工和女性员工的数量
select gender, count(*) from emp where age < 60 group by gender;

#查询所有年龄小于等于35岁的员工的性别和年龄，并对查询结果按年龄升序排序，如果年龄相同则按照入职时间降序排序
select name, age from emp where age <= 35 order by age asc, entrydate desc;

#查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询结果按年龄升序排序，如果年龄相同则按照入职时间降序排序
select * from emp where gender = '男' and between 20 and 40 order by age asc, entrydate asc limit 0, 5; 

DQL-执行顺序

SELECT                                                    FROM
	字段列表                /*第四步*/                           表名列表
FROM                                                      WHERE
	表名列表                /*第一步*/                           条件列表 
WHERE                                                     GROUP BY 
	条件列表                /*第二步*/                           分组字段列表
GROUP BY                             ==>                  HAVING
	分组字段列表             /*第三步*/                           分组后条件列表
HAVING                                                    SELECT
	分组后条件列表                                               字段列表  
ORDER BY                   /*第五步*/                      ORDER BY 
	排序字段列表												   排序字段列表							   
LIMIT                      /*第六步*/                      LIMIT
	分页参数                                                     分页参数

DCL - 介绍

• DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库 用户，控制数据库的访问 权限。

DCL - 管理用户

1. 查询用户

USE mysql;
SELECT * FROM user

2. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3. 修改用户密码

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

4. 删除用户

DROP USER '用户名'@'主机名';

案例

1. 创建一个名叫itcast的用户，只能在当前主机localhost访问，设置其密码为123456

create user 'itcast'@'localhost' identified by '123456';

2. 创建用户Bob，可以在任意主机访问其数据库，密码为123456

create user 'itcast'@'%' identified by '123456';
# 注：% 为通配符，将其设置为在任意主机均可访问

3. 修改用户Bob的密码为1234

alter user 'Bob'@'%' identified with mysql_native_password by '1234';

4. 删除用户itcast的用户

drop user 'Bob'@'%';

注意：

• 主机名可以使用%通配
• 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据库管理人员) 使用

DCL - 权限控制

MySql中定义了许多权限，但常见的就以下几种：

1. 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2. 授予权限

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

3. 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

• 多个授权之间，使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有
  er user ‘Bob’@‘%’ identified with mysql_native_password by ‘1234’;

4. 删除用户`itcast`的用户

```sql
drop user 'Bob'@'%';

注意：

• 主机名可以使用%通配
• 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据库管理人员) 使用

DCL - 权限控制

MySql中定义了许多权限，但常见的就以下几种：

[外链图片转存中…(img-NcugDV1d-1715762726180)]

1. 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2. 授予权限

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

3. 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

• 多个授权之间，使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有