Sql语法

发表于 更新于 分类于 阅读次数： 本文字数： 2.9k 阅读时长 ≈ 10 分钟
Yes, the past can hurt. But I think you can either run from it or learn from it. — Lion King

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 - 数据库操作

查询

  • 查询所有数据库
1
SHOW DATABASES;
  • 查询当前数据库
1
SELECT DATABASE();

创建

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

删除

1
DROP DATABASE[IF EXISTS] 数据库名;

使用

1
USE 数据库名;

DDL - 表操作 - 查询

  • 查询当前数据库所有表
1
SHOW TABLES;
  • 查询表结构
1
DESC 表名;
  • 查询指定表的建表语句
1
SHOW CREATE TABLE 表名;

DDL - 表操作 - 创建

1
2
3
4
5
6
7
8
9
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 - 表操作 - 修改

添加字段

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

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

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

修改数据类型

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

修改字段名和字段类型

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

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

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

删除字段

1
ALTER TABLE 表名 DROP 字段名;
案例：

将emp中的字段username删除

1
alter table emp drop username;

修改表名

1
ALTER TABLE 表名 RENAME TO 新表名;
案例：

将emp表的表名修改为employee；

1
alter table emp rename to employee;

DDL - 表操作 - 删除

删除表

1
DROP TABLE [IF EXISTS] 表名;

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

1
TRUNCATE TABLE 表名;

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

DML - 介绍

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

添加数据(INSERT)

给指定字段添加数据
1
INSERT INTO 表名(字段1, 字段2, ....) VALUES(值1, 值2, ....);
给全部字段添加数据
1
INSERT INTO 表名 VALUES(值1, 值2);
批量添加数据
1
2
3
4
5
6
7
8
9
INSERT INTO 表名(字段名1, 字段名2....) VALUES(值1, 值2....), (值1, 值2....), (值1, 值2....);
INSERT INTO 表名 VALUES(值1, 值2....) (值1, 值2....) (值1, 值2....);

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

修改数据(UPDATE)

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

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

删除数据(DELETE)

1
2
3
4
5
6
7
DELETE FROM 表名 [WHERE 条件];

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

DQL - 介绍

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

  • 查询关键字：SELECT

DQL - 语法

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

基本查询

  • 查询多个字段
1
2
SELECT 字段1，字段2，字段3....FROM 表名;
SELECT * FROM 表名;
  • 设置别名
1
SELECT 字段1 [AS 别名1], 字段2[AS 别名2] .... FROM 表名;
  • 去除重复记录
1
SELECT DISTINCT 字段列表 FROM 表名;

条件查询（WHERE）

语法
1
SELECT 字段列表 FROM 表名 WHERE 条件列表;

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

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
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）

语法

1
2
3
4
SELECT 字段列表 FROM 表名[WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件]; # 两个条件
where 和 having 的不同
# 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组; 而having是分组之后对结果进行过滤
# 判断时机不同：where不能对聚合函数进行判断，而having可以
1
2
3
4
5
6
7
8
9
10
11
12
# 根据性别分组，统计不同性别男性员工和女性员工的数量
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）

语法

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

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

排序方式

  • ASC：升序（默认值）

  • DESC：降序

1
2
3
4
5
6
7
8
9
10
# 根据年龄按照升序排序, 降序
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）

语法

1
2
3
4
5
6
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

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

# 查询第二页员工的数据，每页展示10条记录 -------> (页码 - 1) * 展示记录数
select * from emp limit 10, 10;
案例练习
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询年龄为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-执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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. 查询用户
1
2
USE mysql;
SELECT * FROM user
  1. 创建用户
1
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  1. 修改用户密码
1
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
  1. 删除用户
1
DROP USER '用户名'@'主机名';

案例

  1. 创建一个名叫itcast的用户，只能在当前主机localhost访问，设置其密码为123456
1
create user 'itcast'@'localhost' identified by '123456';
  1. 创建用户Bob，可以在任意主机访问其数据库，密码为123456
1
2
create user 'itcast'@'%' identified by '123456';
# 注：% 为通配符，将其设置为在任意主机均可访问
  1. 修改用户Bob的密码为1234
1
alter user 'Bob'@'%' identified with mysql_native_password by '1234';
  1. 删除用户itcast的用户
1
drop user 'Bob'@'%';

注意：

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

DCL - 权限控制

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

MySQL-权限控制
  1. 查询权限
1
SHOW GRANTS FOR '用户名'@'主机名';
  1. 授予权限
1
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  1. 撤销权限
1
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

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