SQL基本语法_sql语法

在 MySQL Command Line Client 或者 navicat 等工具中都可以编写SQL指令。

由于本人之前的程序都用Pycharm专业版编写，所以在Pycharm中下载插件和驱动即可连接数据集进行编写SQL指令。

1、SQL 基本语法

• SQL指令不区分大小写
• 每条SQL表达式结束之后要以;结束
• SQL关键词之间以空格进行分隔
• SQL之间不限制换行（可以有空格的地方就可以有换行）

2、DDL 数据定义语言

使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

DDL-数据库操作

查询数据库

## 显示当前mysql中的数据库列表
show databeses;

## 显示指定名称的数据库创建的SQL指令
show create database <dbName>;
1
2
3
4
5

创建数据库

## 创建数据库
create database <dbName>;

## 创建数据库，当指定名称的数据库不存在的时候执行创建
create database if not exists <dbName>;

## 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式 utf8 gbk）
create database <dbName> character set utf8;
1
2
3
4
5
6
7
8

修改数据库 修改数据库字符集

## 修改数据库的字符集
alter database <dbName> character set utf8;
1
2

删除数据库 删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据

## 删除数据库
drop database <dbName>;

## 如果数据库存在则删除数据库
drop database if exists <dbName>;
1
2
3
4
5

使用/切换数据库

use <dbName>;
1

DDL-数据表操作

创建数据表

create table students(
	stu_num char(8) not null unique,
	stu_name varchar(20) not null,
	stu_gender char(2) not null,
	stu_age int not null,
	stu_tel char(11) not null unique,
	stu_qq varchar(11) unique
);
1
2
3
4
5
6
7
8

查询数据表

show tables;
1

查询表结构

desc <tableName>;
1

删除数据表

## 删除数据表
drop table <tableName>;

## 当数据表存在时删除数据表
drop table if exists <tableName>;
1
2
3
4
5

修改数据表

## 修改表名
alter table <tableName> rename to <newTableName>;

## 数据表也是有字符集的，默认字符集和数据库一致
alter table <tableName> character set utf8;

## 添加列（字段）
alter table <tableName> add <columnName> varchar(200);

## 修改列的列表和类型
alter table <tableName> change <oldColumnName> <newColumnName> <type>;

## 只修改列类型
alter table <tableName> modify <columnName> <newType>;

## 删除列
alter table <tableName> drop <columnName>;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3、MySQL数据类型

数据类型，指的是数据表中的列支持存放的数据类型。

数值类型

字符串类型

日期类型

4、字段约束

约束介绍

在创建数据表的时候，指定的对数据表的列的数据限制性的要求（对表的列中的数据进行限制）

为什么要给表中的列添加约束呢？

• 保证数据的有效性
• 保证数据的完整性
• 保证数据的正确性

字段常见的约束

• 非空约束（not null）：限制此列的值必须提供，不能为null
• 唯一约束（unique）：在表中的多条数据，此列的值不能重复
• 主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据
• 外键约束 (foreign key)：建立不同表之间的关联关系

主键约束

创建表时添加主键约束

create table books(
	book_isbn char(4) primary key,
	book_name varchar(6) not null
);
1
2
3
4

或者

create table books(
	book_isbn char(4),
	book_name varchar(6) not null,
	primary key(book_isbn)
);
1
2
3
4
5

删除数据表主键约束

alter table books drop primary key;
1

创建表之后添加主键约束

create teble books(
	book_isbn char(4),
	book_name varchar(6) not null
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;
1
2
3
4
5
6
7

主键自动增长

在我们创建一张数据表时，如果数据表中有列可以作为主键，我们直接以该列为主键。

当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本身无关的列（ID）作为主键，此列数据无具体的含义，主要用于标识一条记录，在mysql中我们可以将此列定义为int，同时设置为自动增长，当我们向数据表中新增一条记录时，无需提供ID列的值，它会自动生成。

关键词–auto_increment

create table types(
	type_id int primary key auto_increment,
	type_name varchar(20) not null,
	type_remark varchar(100)
);
1
2
3
4
5

注意：自动增长从1开始，每添加一条记录，自动的增长的列会自动+1，当我们把某条记录删除之后再添加数据，自动增长的数据也不会重复生成（自动增长只保证唯一性、不保证连续性）

联合主键

当一张数据表中一列不能唯一标识整张表，而两列或多列可以，此时可以用联合主键。

定义联合主键

create table grades(
	stu_num char(8),
	course_id int,
	score int,
	primary key(stu_num, course_id)
);
1
2
3
4
5
6

注意：在实际企业项目的数据库设计中，联合主键使用频率不高；
当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键。

联合主键

在多表关联部分详解

5、DML数据操纵语言

用于完成对数据表中数据的插入、删除、修改操作

插入数据

语法

insert into <tableName>(columnName, columnName...)
values(value1,value2...);
1
2

示例

## 向数据表中指定的列添加数据（不允许为空的列必须提供数据）
insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel)
values('20220101','张三', '男', 21, '13030303030');

## 当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，
## 但是values中的值的顺序要与数据表定义的字段保持一致（项目开发中不建议）
insert into stus values('20220101','张三', '男', 21, '13030303030');
1
2
3
4
5
6
7

删除数据

语法

delete from <tableName> [where conditions];
1

[ ] 表示可以没有

示例

## 删除学号为20210102的学生信息
delete from stus where stu_num='20210102';

## 删除年龄大于20岁的学生信息（如果满足where子句的巨鹿有多条，则删除多条信息）
delete from stus where stu_age>20;

## 如果删除语句没有where子句，则表示删除当前数据表中的所有记录（敏感操作）
delete from stus;
1
2
3
4
5
6
7
8

修改数据

语法

update <tableName> set columnName=value [where conditions];
1

示例

## 将学号为20210105的学生姓名修改为“孙七”（只修改一例）
update stus set stn_name='孙七' where stu_num='20210105';

## 将学号为20210103的学生 性别改为男，同时将QQ改成 777777（修改多列）
update stus set stu_gender='男',stu_qq='777777' where stu_num='20210103';

## 根据主键值修改其他列
update stus set stu_name='韩梅梅', stu_gender='女',stu_age=18,stu_tel='13113131313',stu_qq='999999' where stu_num='20210102';

## 如果update语句没有where子句，则表示修改修改当前表中的所有行（记录）
update stus set stu_name='Tom';
1
2
3
4
5
6
7
8
9
10
11

6、DQL数据查询语言

从数据表中提取满足特定条件的记录

• 单页查询
• 多表联合查询

6.1 查询基础语法

## select 关键词后指定要显示查询到的记录的那些列
select columnName1 [, columnName2, ...] from <tableName> [where conditions];

## 如果要显示查询到的记录的所有列，则可以用 * 替代字段名列表
select * from <tableName>;
1
2
3
4
5

6.2 where 子句

在删除、修改及查询的语句后都可以添加where子句（条件），用于筛选满足特定的添加的数据进行删除、修改和查询操作。

delete from tableName where conditions;
updatw tableName set ... where conditions;
select ... from tableName where conditions;
1
2
3

条件关键运算符

##  = 等于
select * from stus where stu_num = '20220101';

## !=  <>  不等于
select * from stus where stu_num != '20220101';
select * from stus where stu_num <> '20220101';

## > 大于
select * from stus where stu_age > 18;

## < 小于
select * from stus where stu_age < 20;

## >= 大于等于
select * from stus where stu_age >= 20;

## <= 小于等于
select * from stus where stu_age <= 20;

## between and 区间查询 [v1, v2]闭区间
select * from stus where stu_age between 18 and 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

条件逻辑运算符

在where子句中，可以将多个条件通过逻辑运算（and or not）进行连接，从而来筛选要操作的数据

## and 并且 筛选多个条件同时满足的记录
select * from stus where stu_gender='女' and stu_age < 21;

## or 或者 筛选多个条件中至少满足一个条件的记录
select * from stus where stu_gender='女' or stu_age < 21;

## not 取反
select * from stus where stu_age not between 18 and 20;
1
2
3
4
5
6
7
8

6.3 LIKE 子句

在where 子句的条件中，我们可以用 like 关键字来实现模糊查询

语法

select * from tableName where column like 'reg';
1

• 在 like 关键字后的 reg 表达式中
  • %表示任意多个字符【%o%包含字母o】
  • _表示任意一个字符【_o%第二个字母为o】

示例

# 查询学生姓名包含字母o的学生信息
select * from stus where stu_name like '%o%';

# 查询学生姓名第一个字为‘张’的学生信息
select * from stus where stu_name like '张%';

# 查询学生姓名第二个字母为o的学生信息
select * from stus where stu_name like '_o%';
1
2
3
4
5
6
7
8

6.4 对查询结果的处理

设置查询的列

声明显示查询结果的自定列

select columnName1, columnName2, ... from stus where stu_age > 20;
1

计算列

对从数据表中查询的记录的列进行一定的运算之后显示出来

## 出生年份 = 当年年份 - 年龄
select stu_name, 2022-stu_age from stus;

+-----------+--------------+
| stu_name | 2021-stu_age |
+-----------+--------------+
| omg   | 2000 |
| 韩梅梅 | 2003 |
| Tom   | 2001 |
| Lucy  | 2000 |
| Polly | 2000 |
| Theo  | 2004 |
+-----------+--------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

as字段取别名

我们可以为查询结果的列名 取一个语义性更强的别名（如下案例中as关键字也可以省略）

select stu_name, 2022-stu_age as stu_birth_year from stus;

+-----------+--------------+
| stu_name | stu_birth_year |
+-----------+--------------+
| omg   | 2000 |
| 韩梅梅 | 2003 |
| Tom   | 2001 |
| Lucy  | 2000 |
| Polly | 2000 |
| Theo  | 2004 |
+-----------+--------------+
1
2
3
4
5
6
7
8
9
10
11
12

distinct消除重复行

从查询的结果中将重复的记录消除distinct

select distinct stu_age from stus;
1

6.5 排序 - order by

将查询到的满足条件的记录按照指定的列的值升序/降序排列

语法

select * from tableName where conditions order by columnName asc|desc;
1

• order by columnName 表示将查询结果按照指定的列排序
  • asc 按照指定的列升序（默认）
  • desc 按照指定的列降序

# 单字段排序
select * from stus where stu_age>15 order by stu_gender desc;

# 多字段排序：先满足第一个排序规则，当第一个排序的列的值相同时再按照第二个条件排序
select * from stus where stu_age>15 order by stu_gender asc, stu_age desc;
1
2
3
4
5

6.6 聚合函数

SQL 中提供了一些可以对查询的记录的列进行计算的函数–聚合函数

• count
• max
• min
• sum
• avg

• count()统计函数，统计满足条件的指定字段值的个数（记录数）

# 统计学生表中学生总数
select count(stu_num) from stus;

# 统计学生表中性别为男的学生总数
select count(stu_num) from stus where stu_gender='男';
1
2
3
4
5

• max() 计算最大值，查询满足条件的记录中指定列的最大值
• min() 计算最小值，查询满足条件的记录中指定列的最小值
• sum() 计算和， 查询满足条件的记录中 指定的列的值的总和
• avg() 求平均值，查询满足条件的记录中 计算指定列的平均值

6.7 日期函数 和 字符串函数

日期函数

当我们向日期类型的列添加数据时，可以通过字符串类型赋值（字符串格式必须为 yyyy-MM-dd hh:mm:ss）
如果我们想要获取当前系统时间添加到日期类型的列，可以使用now()或者sysdate()

示例

# 通过now()获取当前时间
insert into
stus(stu_num,stu_name,stu_enterence)
values('20220109','张小四',now());

# 通过sysdate()获取当前时间
insert into
stus(stu_num,stu_name,stu_enterence)
values('20220109','张小四',sysdate());

# 通过now和sysdate获取当前系统时间
select now();

select sysdate();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

字符串函数

通过SQL指令对字符串进行处理

# concat(column1, column2, ...) 拼接多列
select concat(stu_name, '-', stu_gender) from stus;

+---------------------------------+
| concat(stu_name,'-',stu_gender) |
+---------------------------------+
| 韩梅梅-⼥ |
| Tom-男 |
| Lucy-⼥ |
| 林涛-男 |
+---------------------------------+

# upper(column) 将字段的值转换成大写
select upper(stu_name) from stus;

# lower(column) 将指定列的值转换成小写
select lower(stu_name) from stus;

# substring(column, start,len) 从指定列中截取部分显示 start从1开始
select stu_name, substring(stu_tel, 8, 4) from stus;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

6.8 分组查询 - group by

分组：将数据表中记录按照指定的类分组

语法

select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组列名 [having 条件]
[order by 排序字段]
1
2
3
4
5

• select后使用*显示对查询结果进行分组之后，显示每组的第一条记录（这种显示通常是无意义的）
• select后通常显示分组字段和聚合函数（对分组后的数据进行统计、求和、平均值等）
• 语句执行属性：1: 先根据where条件从数据库查询记录 2: group by对查询记录进行分组 3: 执行having对分组后的数据进行筛选

示例

# 先对查询的学生信息按性别进行分组（分成男女两组）
# 然后分别统计每组学生的个数
select stu_gender, count(stu_num) from stus group by stu_gender;

+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| ⼥ | 4 |
| 男 | 5 |
+------------+----------------+


# 先对查询的学生信息按性别进行分组（分成男女）
# 然后再计算每组的平均年龄
select stu_gender, avg(stu_age) from stus group by stu_gender;

+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| ⼥ | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+


# 先对学生按年龄进行分组，然后统计各组的学生数量，还可以对最终的结果排序
select stu_age, count(stu_num) from stus group by stu_age order by stu_age;

+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 17 | 1 |
| 18 | 1 |
| 20 | 3 |
| 21 | 1 |
| 22 | 1 |
+---------+----------------+


# 查询所有学生，按年龄进行分组，然后分别统计每组的人数，
# 再筛选当前人数 > 1的组，再按照年龄升序显示
select stu_age, count(stu_num)
from stus
group by stu_age
having count(stu_num) > 1
order by stu_age;

+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 20 | 3 |
+---------+----------------+


# 查询性别为‘男’的学生，按年龄进行分组，然后分组统计每组的人数
# 再筛选当前组人数 > 1 的组，再按年龄升序显示出来
select stu_age, count(stu_num)
from stus
where stu_gender='男'
group by stu_age
having count(stu_num)>1
order by stu_age
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

6.9 分页查询 - limit

当数据表中的记录比较多的时候，如果一次性全部查询出来显示给用户，用户的可读性/体验性就不太好，因此我们可以将这些数据分页进行展示。

语法

select ...
from ...
where ...
limit param1, param2
1
2
3
4

• param1 int ,表示获取查询语句的结果中的第一条数据的索引（索引从0开始）
• 表示获取的查询记录的条数（如果剩下的数据条数<param，则返回剩下的所有记录）

案例
对数据表中的学生信息进行分页显示，总共有10条数据，要求每页显示3条

总记录数 count 10
每页显示 pageSize 3
总页数 pageCount = count % pageSize == 0 ? count / pageSize : count / pageSize + 1

# 查询第一页
select * from stus [where ...] limit 0, 3;

# 查询第二页
select * from stus [where ...] limit 3, 3;

# 查询第三页
select * from stus [where ...] limit 6, 3;

# 查询第四页
select * from stus [where ...] limit 9, 3;	

# 如果在一张表中,pageNum 表示查询的页码
# pageSize 表示每页显示的条数
# 通用分页语句如下
select * from <tableName> [where ...] limit (pageNum-1)*pageSize, pageSize;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16