四、MySQL逻辑结构

4.1 逻辑结构

4.1 记录

五、SQL 结构化查询语言

5.1 SQL概述

SQL（Structural Query Language）结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统

5.1.1 SQL发展

SQL是在1981年由IBM公司推出，一经推出基于其简洁的语法在数据库中得到了广泛的应用，成为主流数据库
的通用规范

• SQL由ANSI组织确定规范

• 在不同的数据库产品中遵守SQL的通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令

  • MySQL:limit

  • SOLServer top

  • Oracle:rownum

5.1.2 SQL分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类：

• DDL Data Definition Language 数据定义语言
  • 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改
• DMLData Manipulation Language 数据操作/操纵语言
  • 用于完成对数据表中的数据的添加、删除、修改操作
  • 添加：将数据存储到数据表
  • 删除：将数据从数据表移除
  • 修改：对数据表中的数据进行修改
• DQL Data Query Language 数据查询语言
  • 用于将数据表中的数据查询出来
• DCL Data Control Language 数据控制语言
  • 用于完成事务管理等控制性操作

5.2 SQL基本语法

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

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

5.3 DDL-数据定义语言

5.3.1 DDL-数据库操作

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

查询数据库

##显示当前mysq1中的数据库列表
show databases;
##显示指定名称的数据的创建的SQL指令
show create database <dbName>;

创建数据库

##创建数据库dbName:表示创建的数据库名称，可以自定义
create database <dbName>;
#创建数据库，当指定名称的数据库不存在时执行创建
create database if not exists <dbName>;
##在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式utf8gbk)
create database <dbName>character set utf8;

修改数据库修改数据库引擎、字符集

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

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

##删除数据库
drop database <dbName>;
##如果数据库存在则删除数据库
drop database is exists <dbName>;

5.3.2 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
):

查询数据表

show tables;

查询表结构

desc <tableName>;

删除数据表

##删除数据表
drop table <tableName>;
##当数据表存在时除数据表
drop table if exists <tableName>;

修改数据表

##修改表名
alter table <tableName>rename to <newTableName>;
##数据表也是有字符集的，默认字符集和数据库一致
alter table <tableName>character set utf8;
#添加列（字段）
alter table <tableName>add <columnName>varchar(200);
##修改列（字段）的列表和类型
alter table <tableName>change <oldColumnName><newcloumnName><type>;
##只修改列（字段）类型
alter table <tableName>modify <columnName><newType>;
#删除列（字段）
alter table stus drop <columnName>;

5.4 MySQL数据类型

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

5.4.1 数值类型

在MySQL中有多种数据类型可以存放数值，不同的类型存放的数值的范围或者形式是不同的

5.4.2字符串类型

存储字符序列的类型

5.4.3 日期类型

在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查间操作（查询
在某个时间段内的数据)就不便于查询实现

5.5 字段约束

5.5.1 约束介绍

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

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

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

字段常见的约束：

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

5.5.2 非空约束

限制数据表中此列的值必须提供

• 创建表：设置图书表的book_name not null

  create table books(
  	book_isbn char(4),
      book_name varchar(10) not null,
      book_author varchar(6)
  );
  

• 添加数据

​

5.5.3 唯一约束

在表中的多条数据，此列的值不能重复

• 创建表：设置图书表的book_isbn为unique

  create table books(
  	book_isbn char(4) unique,
      book_name varchar(10) not null,
      book_author varchar(6)
  );
  

• 添加数据：

  

5.5.4 主键约束

主键——就是数据表中记录的唯一标识，在一张表中只能有一个主键（主键可以是一个列，也可以时多个列的组合）

当一个字段声明为主键之后，添加数据时：

• 此字段不能为null
• 此字段不能重复

创建表时定义主键

create table books(
	book_isbn char(4) primary key,
    book_name varchar(10) not null,
    book_author varchar(6)
);

或者

create table books(
	book_isbn char(4),
    book_name varchar(10) not null,
    book_author varchar(6),
    primary key(book_isbn)
);

删除数据表主键约束

alter table books drop primary key;

创建表之后添加主键约束

## 创建表时没有添加主键约束
create table books(
	book_isbn char(4),
    book_name varchar(10) not null,
    book_author varchar(6)
);
## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

5.5.5 主键自动增长

在我们创建一张数据表时，如果数据表中有列可以作为主键（例如：学生表的学号，图书表的id）我们可以直接设置这个列为主键；

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

定义主键自动增长

create table types(
	type_id int primary key auto_increment,
    type_name varchar(20) not null,
    type_remark varchar(100)
);

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

5.5.6 联合主键

联合主键一一将数据表中的多列组合在一起设置为表的主键

定义联合主键

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)
);

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

5.5.7 外键约束

5.6 DML数据操纵语言

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

create table students(
    stu_num char(8) primary key,
    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
);

5.6.1 插入数据

语法

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

示例

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

##数据表名后的字段名列表顺序可以不与表中一致，但是va1ues中值的顺序必须与表名后字段名顺序对应
insert into stus(stu_num,stu_name,stu_age,stu_tel,stu_gender)
va1ues('28210103',王五'，20,13030303302'，'女);

##当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，但是v1ues中的值的顺序要与数据表定义的字段保
持一致；
insert int0 stus va1ues('20210105','孙琦'，'男'，21，'13030303304'，'666666');
       
##不过在项目开发中，即使要向所有列添加数据，也建议将列名的列表显式写出来（增强$QL的稳定性】
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq)
va1ues('20218185,孙琦'，'男'，21，'13038303304'，'666666');

5.6.2 删除数据

从数据表中删除满足特定条件（所有）的记录

语法

delete from <tableName>[where conditions];

示例

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

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

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

5.6.3 修改数据

对数据表中已经添加的记录进行修改

语法

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

示例

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

##将学号为20219183的学生性别修改为“男”，同时将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='13131313311'
,stu_qq='999999'where stu._num='28210102';

##如果update语句没有where-子句，则表示修改当前表中所有行（记录）
update stus set stu_name='Tom';

5.7 DQL数据查询语言

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

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

5.7.1查询基础语法

##se1lect关键字后指定要显示查询到的记录的那些列
select colnumName1[,colnumName2,colnumName3...]from <tableName>;

##如果要显示查询到的记录的所有列，则可以使用*替代字段名列表 (在项目开发中不建议使用*)
select from stus;

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

delete from tableName where conditions;

update tabeName set...where conditions;

select ...from tableName where conditions;

条件关系运算符

## = 等于
select from stus where stu_num ='20210101';

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

# > 大于
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...  区间查询
between v1 and v2		[v1,v2]
select from stus where stu_age between 18 and 20;

条件逻辑运算符

在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;

5.7.2 LIKE子句

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

语法

select from tableName where columnName like 'reg';

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

示例

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

#查询学生姓名第一个字为张的学生信息
select from stus where stu_name 1ike'张%'；

#查询学生姓名最后一个字母为o的学生信息
select from stus where stu_name like '%o';

#查询学生姓名中第二个字母为o的学生信息
select from stus where stu_name
1ike'_o%':

5.7.3 对查询结果的处理

设置查询的列

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

select colnumName1,columnName2,...from stus where stu_age>20;

计算列

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

## 出生年份 = 当前年份 - 年龄
select stu_name,2021-stu_age from stus;
+-----------+--------------+
| stu_name | 2021-stu_age |
+-----------+--------------+
| omg 		| 2000 		  |
| 韩梅梅 	  | 2003 		|
| Tom 		| 2001 		  |
| Lucy 		| 2000		  |
| Polly 	| 2000		  |
| Theo 		| 2004		  |
+-----------+--------------+

as 字段别名

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

select stu_name,2021-stu_age as stu_birth_year from stus;
+-----------+----------------+
| stu_name | stu_birth_year |
+-----------+----------------+
| omg 		| 2000 		|
| 韩梅梅 	  | 2003 	  |
| Tom 		| 2001 		|
| Lucy 		| 2000 		|
| Polly 	| 2000 		|
| Theo 		| 2004 		|
+-----------+----------------+
select stu_name as 姓名,2021-stu_age as 出生年份 from stus;
+-----------+--------------+
| 姓名 | 出生年份 |
+-----------+--------------+
| omg 	| 2000 |
| 韩梅梅 | 2003 |
| Tom   | 2001 |
| Lucy  | 2000 |
| Polly | 2000 |
| Theo  | 2004 |
+-----------+--------------+

distinct 消除重复行

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

select stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 	 |
| 18 	 |
| 20 	 |
| 21 	 |
| 21   	 |
| 17  	 |
+---------+
select distinct stu_age from stus;
+---------+
| stu_age |
+---------+
| 21  	 |
| 18 	 |
| 20 	 |
| 17 	 |
+---------+

5.7.4 排序 — order by

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

语法

select from tableName where conditions order by columnName asc desc;

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

实例

# 单字段排序
select * from stus where stu_age>15 order by stu_gender desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210101 | omg 	  | 男		 | 21	  	 | 13030303300 | NULL |
| 20210103 | Tom 	  | 男 		 | 20	  	 | 13030303302 | 777777 |
| 20210105 | Polly    | 男 		 | 21	     | 13030303304 | 666666 |
| 20210106 | Theo 	  | 男 		 | 17	     | 13232323322 | NULL |
| 20210102 | 韩梅梅 	| 女 	   | 18		   | 13131313311 | 999999 |
| 20210104 | Lucy     | 女 		 | 21	     | 13131323334 | NULL |
+----------+-----------+------------+---------+-------------+--------+
# 多字段排序 ： 先满足第一个排序规则，第一个排序规则完成之后，在按照第二个排序规
则执行
（以下案例就是先按照性别升序排列，然后再降序排序相同性别的年龄）
select * from stus where stu_age>15 order by stu_gender asc,stu_age desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210104 | Lucy 	 | 女 		  | 21 | 13131323334 | NULL |
| 20210102 | 韩梅梅   | 女 			| 18 | 13131313311 | 999999 |
| 20210101 | omg 	 | 男 			| 21 | 13030303300 | NULL |
| 20210105 | Polly   |  男 			| 21 | 13030303304 | 666666 |
| 20210103 | Tom 	 | 男 			| 20 | 13030303302 | 777777 |
| 20210106 | Theo 	 | 男 			| 17 | 13232323322 | NULL |
+----------+-----------+------------+---------+-------------+--------+

5.7.5 聚合函数

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

• count
• max
• min
• sum

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

#统计学生表中学生总数
select count(stu_age ) from students;

#统计学生表中性别伪男的学生总数
select count(stu_num)from students where stu_gender='男';

• max() 计算最大值，查询满足条件的记录中指定列的最大值

select max(stu_age) from students;

• min() 计算最大=小值，查询满足条件的记录中指定列的最小值

select min(stu_age) from students;

• sum() 计算和，查询满足条件的记录中指定的列的值的总和

#计算所有学生年龄的总和
select sum(stu_age) from students;

• avg() 计算均值，查询满足条件的记录中指定的列的值的均值

#计算所有学生年龄的均值
select avg(stu_age) from students;

5.7.6 日期函数和字符串函数

日期函数

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

示例：

# 增加一个入学时间列
alter  table students  add stu_enterence varchar(30);

#通过字符串类型给日期类型的列赋值
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200108','张小三','女',20,'13434343344','123111','2023-11-01 09:00:00');
       
#通过now()获取当前时间
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200109','王芳','女',23,'13434343377','123333',now());
       
#通过sysdate()获取当前时间
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200107','李丽','女',20,'13434343388','133333',sysdate());

字符串函数

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

示例：

 # concat(colnum1,colunm2,..）拼接多列
 select concat(stu_num,'-',stu_name,'-',stu_age) from students;
 
 # upper(column)将字段的值转换成大写
 select upper(stu_name) from students;
 
 # lower(column)将字段的值转换成小写
 select lower(stu_name) from students;
 
 # substring(column,start,len)从指定列中截取部分显示start从1开始
 select stu_name,substring(stu_tel,8,4) from students;

5.7.7 分组查询 — group by

分组——就是将数据表中的记录按照指定的类进行分组

语法

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

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

示例

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

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

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

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

#查询性别为'男'的学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数>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; 

5.7.8 分页查询 — limit

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

语法

select ...from ...where ...
limit param1 , param2

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

案例

对数据表中的学生信息进行分页显示，总共有10条数据，我们每页显示3条总记录数count 10

每页显示 pageSize 3

总页数:pageCount = count%pageSize==0 ? count/pageSize : count/pageSize +1;

#查询第一页:
select k from stus [ where ...] limit 0,3;				( 1-1)*3

#查询第二页:
select k from stus [ where ...] limit 3,3 ;				(2-1)*3

#查询第三页:
select k from stus [ where ...] limit 6,3;				(3-1)*3

#查询第四页:
select k from stus [ where ...] limit 9,3;				(4-1)*3

#如果在一张数据表中:
# pageNum表示查询的页码
# pageSize表示每页显示的条数

#通用分页语句如下:
select * from <tableName>[where ...] limit (pageNum-1 )*pageSize , pageSize;