MySQL数据库

第一章 数据库概述

1.1 应用

1.2 概述

1.数据（Data）-数据库中存储的基本对象

定义：描述事物的符号记录

特点：数据的含义称为数据的语义，数据和其语义是不可分的

2.信息

定义：

•信息是数据经过加工处理后的有用的数据结果

•简单地说：有用的数据就是信息。

3.数据库（DB）

(Datalbase ，简记为DB）

定义：是长期存储在计算机内、有组织的、可共享的大量数据的集合。

4.数据库管理系统（DBMS）

位于用户应用与操作系统之间的一层数据管理软件。是基础软件，是一个大型复杂的软件系统。数据库在计算机系统中的位置

5.数据库系统（DBS）

数据库系统 (Database System，简称DES)

-定义：是指在计算机系统中引入数据库后的系统构成。数据库系统的构成

1.3 产生与发展

人工管理阶段(20世纪40年代中期--50年代中期)

文件系统阶段(20世纪50年代末期--60年代中期）

数据库系统阶段(20世纪60年代末期-现在)

1.4 数据模型

一.模型与数据模型

数据模型定义：

数据库中的数据是按一定的方式存储在一起的，这种数据的组织结构又称为数据模型，它决定了数据库中数据之间联系的表达方式。

数据模型分类（层次上）：

（1）概念模型（信息模型）

是按用户的观点来对数据和信息建模，用于数据库设计。

（2）逻辑模型和物理模型（统称结构模型）

a)逻辑模型：按计算机系统的观点对数据建模，用于DBMS实现。

主要包括关系模型、层次模型、网状模型、面向对象模型和对象关系模型等。其中最常用的是关系模型。

b)物理模型是对数据最底层的抽象，描述数据在系统内（磁盘上）的表示方式和存取方法。

二.概念模型

所谓概念模型就是对现实世界数据的抽象与模拟。

概念模型的用途

=概念模型用子信息世界的建模

是现实世界到机器世界的一个中间层次

是数据库设计的有力工具

数据库设计人员和用户之间进行交流的语言

1. 术语

（1）实体 (Entity)

客观存在并且可以互相区分的事物。可以是实际的事物，如一名学生、一本书等：也可以是抽象的事件，如一场比赛、一个创意等。

（2）属性 (Attribute)

实体的某一特性称为属性。一个实体可以由若干个属性来刻画。

（3）码(Key)

唯一标识实体的属性集。

（4）实体型 (Entity Type)

用实体名及其属性名集合来抽象和刻面同类实体，称为实体型。

（5）实体集 (Entity Set)

同一类型实体的集合称为实体集。

（6）联系(Relationship）：在现实世界中，事物内部和事物之间是有联系的，这些联系在信息世界中反映为实体内部的联系和实体之间的联系。

实体内部的联系：是指组成实体的各属性之问的联系。

实体之间的联系：通常是指不同实体集之问的联系。

实体之间的联系有一对一(1:1)，一对多(1:n)，多对多(m:n)三种类型。

3.表示形式（E-R图）

班长和班级的E-R图

E-R图

提供了 表示实体型、属性和联系的方法：

实体型：用矩形表示，矩形框内写明实体名。

属性：用椭圆形表示，并用无向边将其与相应的实体型连接起来。

三、数据库的逻辑模型

数据模型（其实是指逻辑模型）

数据模型是严格定义的一组概念的集合，精确地描述了系统的静态精性、动态特性和完整性约束条件。

逻辑模型要素

（1） 数据结构—指述系统的静态特性

描述数据的组成对象以及对象之同的联系。

层次结构、网状结构、关系结构

（2）数据操作一描述系统的动态特性

检索、更新（包括插入、删除、修改）

（3）完整性约束

一组完整性规则的集合。

完整性规则：给定的数据模型中数据及其联系所具有的。

1.分类

典型的逻辑模型主要有以下几种：层次、网状、关系等模型。

归类：

关系模型和非关系模型

2.关系模型

（1） 关系模型的数据结构

在用户观点下，关系模型中数据的逻辑结构是一张二维表。

相关概念：

（1） 关系（Relation）

一个关系对应通常说的一张二维表。

（2） 元组（Tuple）

表中的一行（记录）即为一个元组。

（3）属性(Attribute)

表中的一列即为一个属性（字段），给每一个属性起一个名称即属性名（字段名）。

（4） 域（Domain）

是一组具有相同数据类型的值的集合。

属性的取值范围来自某个域。

（5）分量：元组中的一个属性值。

（6）关系模式：对关系的描述。

关系名（属性1，属性2，…属性m）

（7） 关键码（键，Key）

①超键（super key）：在关系中能唯一标识元经的属性集称为关系模式的超键/码。

②候选键（candidate key）：不含多余属性的超健你为候选键，即其真子集不再是超键（换句话说，在候越键中，若再删除一个属性，就不是键了）

③主键（primary key）：用户选作元组标识的一个候选键称为主键，是候选键之一。

以上三者的关系：候选键是超键的子集，主键是候选键中的一个。

④外键（外关键字，foreign key）：设K是关系模式R中的属性，但不是主键。如果K是其它模式的主键，那么K在模式R中称为外键。

FOREIGN KEY (外键)

REFERENCES 父表表名（父表列名）

（2）性质

不允许表里还有表

（3）操纵、完整性约束

3、概念模型向关系模型转换的基本方法

（1） 实体型的转换

一个实体型转换为一个关系模式。

关系模式的属性：实体的属性。

关系模式的码：实体的码。

（2）联系类型的转换

二元联系类型的转换规则：

实体之间是1:1的联系，可以在两个实体类型转换两个模式中某一端关系模式的属性中加入另一端关系模式的键与联系的属性；​​​​​​​

1.5 数据库系统结构

一、数据库系统模式的概念

1、型（Type）是指对某一类数据的结构和属性的说明，值（Value）是型的一个具体赋值。前面讲的关系模式就可以理解为型，而具体每一行元组就是值。

2、模式（Schema）是数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及到型的描述，不涉及到具体的值。模式的一个具体值称为模式的一个实例（Instance）。同一个模式可以有多个实例。

总结图：数据库系统的三级模式结构+两层映像功能

总结：

RDBMS的一些术语：

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

​​​​​​​

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成

• 表头(header): 每一列的名称;
• 列(col): 具有相同数据类型的数据的集合;
• 行(row): 每一行用来描述某条记录的具体信息;
• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
• 键(key): 键的值在当前列中具有唯一性。

第四章 数据定义

4.1 概述

语言简洁，易学易用

SQL功能极强，完成核心功能只用了9个动词。

SQL功能	动词	备注
数据查询DQL	SELECT
数据定义DDL	CREATE, DROP, ALTER	对表（结构）进行增时改
数据操纵DML	INSERT, UPDATE, DELETE	对表中数据进行增时改
数据控制DCL	GRANT, REVOKE	授权，撤销权限
事务控制TCL	COMMIT,ROLLBACK	事务提交，事务回滚

4.2 创建数据库

Mysql（安装自己系统的适合的版本）：

https://dev.mysql.com/downloads/mysql/

可视化工具：

Navicat Premium 是一套可创建多个连接的数据库开发工具，让你从单一应用程序中同时连接MySQL、Redis、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL 和 SQLite。它与 OceanBase 数据库及Amazon RDS, Amazon Aurora, Amazon Redshift, Amazon ElastiCache, Microsoft Azure, Oracle

Cloud、MongoDB Atlas、Redis Enterprise Cloud、阿里云、腾讯云和华为云等云数据库兼容。你可以快速轻松地创建、管理和维护数据库。

Mac os启动mysql命令：sudo /usr/local/mysql/support-files/mysql.server start

导入数据〔后期大家练习的时候使用这个演示的数据〕

第一步：登录mysql数据库管理系统（法一：终端）

dos命令窗口：

mysql -uroot -p

通过Navicat可视化界面（法二）

第二步：查看有哪些数据库

show databases;（这个不是sql语句，属于mysQl的命令。〕

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

第三步：创建属于我们自己的数据库

create database bookstore;（这个不是sQI语句，属于mysQL的命令。）

第四步：选择使用数据库

Use 数据库；

第五步：删除数据库

drop databases 数据库；

第六步：修改数据库

alter databases 数据库；

4.3 数据库的管理

查看当前连接下的所有数据库：show databases;

查看当前所使用的数据库：select database( );

查看当前数据库下的所有表：show tables;

查看某表的结构，如emp表：desc emp;

查看某表的创建语句，如emp表：show create table emp;

退出MySQL服务器：exit;

删除数据库：drop database 数据库名;

修改数据库：alter database 数据库名;

一、表的创建与操作

1、建表的语法格式：

create table 表名(

字段名1 数据类型,

字段名2 数据类型,

……

字段名n 数据类型

);

2、关于mysql中的数据类型

常见的数据类型：

①varchar(最长65535)：可变长度的字符串；比较智能，节省空间；会根据实际的数据长度动态分配空间。

优点：节省空间

缺点：需要动态分配空间，速度慢。

②char(最长255)：定长字符串；分配固定长度的空间去存储数据；使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。

缺点：使用不当可能会导致空间的浪费。

③int：数字中的整数型（4Byte，数据范围为-2147483648~2147483647[-2^31~2^31-1]）

④bigint：数字中的长整型（8Byte，数据范围为-2^63到2^63-1(即从-9,223,372,036,854,775,808到 9,223,372,036,854,775,807)）

⑤float：单精度浮点型数据

⑥double：双精度浮点型数据

⑦date：短日期类型，默认格式：%Y-%m-%d

⑧datetime：长日期类型，默认格式：%Y-%m-%d %h:%i:%s

⑨text：最大长度为64 K的变长文本

⑩Blob：二进制大对象，专门用来存储图片、声音、视频等流媒体数据。

注意：选择varchar还是char取决于你的具体需求和数据特点。如果字符串长度固定且不太长，对存储空间要求较高，可以选择char；如果字符串长度变化较大或需要存储NULL值，可以选择varchar。在实际应用中，可以根据具体情况进行尝试和选择。​​​​​​​

3、where子句

从数据表中读取数据的通用语法：

SELECT field1,field2,
WHERE condition1 [AND [OR]] condition2.....

查询语句中可以使用一个或者多个表，表之间使用逗号, 分割

可以使用 AND 或者 OR 指定一个或多个条件。

4、like子句

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如"图书编号：TP2525"

当需要查询图书编号里含25的记录，like子句使用百分号 %字符来表示任意字符（与unix和正则表达式的*一样）没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

语法：

SELECT * from book  WHERE 图书编号 LIKE '%25';

​​​​​​​可以使用like快速复制表：

create table emp2 like emp;

insert into emp2 select * from emp;

二、表数据操作（DML）

1、插入数据insert（DML）

语法格式：insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

注意：字段名和值要一一对应。什么是一一对应？数量要对应；数据类型要对应。

（1）在t_student表中插入三条数据 ，参考代码如下：

insert into t_student(no,name,sex,age,email) values(1,'zs','m',20,'zs@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
insert into t_student(no) values(3);

注意：

如果数据是字符型，必须使用单引号或者双引号，如："value"。

字段名和值要一一对应。数量要对应；数据类型要对应。

insert语句但凡执行成功了，那么必然会多一条记录。

没有给其它字段指定值的话，默认值是NULL。

now() 函数，并且获取的时间带有：时分秒信息！！是datetime类型的。

insert语句可以一次插入多条记录

查看表结构：desc t_user;

语法：insert into t_user(字段名1,字段名2) values(),(),(),();

2、修改数据update（DML）

语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

注意：没有where条件限制会导致所有数据全部更新。

update t_user set name='abc';

3.删除数据 delete （DML）

语法格式:delete  from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

总结：

可以对Delete和Update采用：

（1） RESTRICT，即拒绝（NO ACTION）执行

• 当要删除或更新父表（被参照表）中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。
• 该策略一般设置为默认策略。

（2）级联（CASCADE）操作

•从父表删除或更新行时自动删除或更新子表（参照表）中匹配的行。

（3）设置为空值（SET NULL)

•当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。

（4） 设置为默认值（SET DEFAULT）

• 作用和SET NULL一样，只不过SET DEFAULT是指定子表中的外键列为默认值

4.表的创建和删除

快速创建表

create table emp2 like emp;
insert into emp2 select * from emp;

创建表mytable，复制emp表中job为MANAGER的前两列empno,ename

create table mytable as select empno,ename from emp where job = 'MANAGER';

快速删除表中的数据

delete from emp2; //这种删除数据的方式比较慢。

delete语句删除数据的原理？（delete属于DML语句！！！）

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！

这种删除缺点是：删除效率比较低。

这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

 truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。

这种删除缺点：不支持回滚。

这种删除优点：快速。

用法：

truncate table emp2; //（这种操作属于DDL操作。）

使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

删除表操作

drop table 表名; // 这不是删除表中的数据，这是把表删除。

二、表的创建与操作

一、完整性约束

1、什么是约束？

约束对应的英语单词：constraint（mysql 5不支持此关键词）

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！

约束的作用就是为了保证：表中的数据有效！！

2、约束包括哪些？

非空约束：not null

唯一性约束: unique

主键约束: primary key （简称PK）

外键约束：foreign key（简称FK）

检查约束：check（mysql 5不支持，mysql 8与oracle支持）

3、非空约束：not null

非空约束not null约束的字段不能为NULL。

create table t_vip(
id int,
name varchar(255) not null  // not null只有列级约束，没有表级约束！
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

4、唯一性约束: unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) unique,
	email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
select * from t_vip;  //查看结果

name和email两个字段联合起来具有唯一性！！！！

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) unique,     //约束直接添加到列后面的，叫做列级约束。
	email  varchar(255)  unique
);

unique(name,email)   // 约束没有添加在列的后面，这种约束被称为表级约束。

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

在mysql中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

5、主键约束（primary key，简称PK）

注意：任何一张表都应该有主键，没有主键，表无效！！

主键的特征：not null + unique（即主键值不能是NULL，同时也不能重复！）

//1个字段做主键，叫做：单一主键

drop table if exists t_vip; 
create table t_vip(
	id int primary key,  //列级约束
	name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

表级约束主要是给多个字段联合起来添加约束

drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');

在实际开发中不建议使用：复合主键。

建议使用单一主键！

因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。

复合主键比较复杂，所以不建议使用！

一张表，主键约束只能添加1个。在创建表时定义了多个主键在MySQL中，一个表只能有一个主键

主键值建议使用：

int

bigint

char等类型。

主键除了：单一主键和复合主键之外，还可以这样分类

自然主键：主键值是一个自然数，和业务没关系。

业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

3）自增字段

在mysql当中，是否有一种机制，可以帮助我们自动维护一个主键值？

drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
	name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;

6、外键约束（foreign key，简称FK）

外键通常默认情况下不能为NULL。

drop table if exists t_classes;
create table t_classes(
classes_id int(3) ,
classes_name varchar(30) not null,
constraint pk_classes_id primary key(classes_id)   
);

drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(50) not null,
email varchar(30) unique,
classes_id int(3) not null,
constraint  pk_student_id  primary key(student_id),
constraint fk_classes_id  foreign key(classes_id) references t_classes(classes_id) 
);

 删除表的顺序？

先删子，再删父。

创建表的顺序？

先创建父，再创建子。

 删除数据的顺序？

先删子，再删父。

插入数据的顺序？

先插入父，再插入子。

第5章 数据操作

5.1 插入数据

INSERT INTO table_name ()
VALUES ();

如果数据是字符型，必须使用单引号 ' 或者双引号 "

如果你要插入所有列的数据，可以省略列名：

INSERT INTO members VALUES
(),
();

5.2 修改数据

UPDATE { 表名 | 表名列表 }
SET 列名1 = 表达式1 [ , 列名2 =表达式2 ... ]
[ WHERE 条件 ]

🌰：

UPDATE Members
SET 联系电话 ='13802551234’ , 密码 ='111111’ 
WHERE 姓名 = '张三';

5.3 删除数据

1.单表删除

DELETE FROM 表名
[ WHERE 条件 ]

🌰：

DELETE FROM Members 
WHERE 姓名='张三' ;

2.多表删除

DELETE 表名列表
FROM 表名列表
[ WHERE 条件 ]

🌰：

DELETE FROM sell, members 
Using sell, members 
WHERE sell.用户号=members.用户号 AND members.用户号='D1963’;

3.清空数据

◆ 语法格式：TRUNCATE TABLE 表名

说明：删除表中的所有数据，且无法恢复，因此使用时必须十分小心。

◆ 与不带 WHERE子句的DELETE语句的区别：

➢DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

➢TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，

并且只在事务日志中记录页的释放。

➢TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资

源少。

→如果清空数据，建议用TRUNCATE TABLE命令。

第6章 数据查询

数据查询

◼SELECT子句：指定要显示的属性列

◼FROM子句：指定查询对象（基本表或视图）

◼WHERE子句：指定查询条件

◼GROUP BY子句：对结果按列名1的值分组，该属性列值相等的元

组为一个组。通常会在每组中作用聚集函数。

➢HAVING短语：如果带此短语指定条件，则只输出满足条件的

组。

◼ORDER BY子句：对结果表按列名2的升序或降序排序。

◼LIMIT子句：将结果表的一部分取出来，通常用在分页查询中。

6.1 单表查询

1.选择表中的若干列

SELECT ()
FROM Members ;

🌰：

SELECT 图书编号, 订购册数*订购单价
FROM Sell;

替换查询结果中的数据

◆使用CASE表达式：

CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式
END

2.选择表中的若干元组

（1）消除取值重复的行

◆ 两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相

同的行。可以用DISTINCT消除它们。

◆ 如果没有指定DISTINCT关键词，则缺省为ALL。

❖指定DISTINCT关键词，去掉表中重复的行：

🌰：

SELECT DISTINCT 图书类别, 出版社 FROM Book;

① 比较大小

比较运算符一般包括：=（等于）、<（小于）、<=（小于等于）、>（大于）、

>=（大于等于）、<>或!=（不等于）、 !> （不大于）, !< （不小于）

② 逻辑运算

◆逻辑运算操作的结果是“1”或“0”，分别表示“true”或

“false”。

NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

由高到低优先级

③字符匹配

❖ 语法： [NOT] LIKE 匹配串 [ ESCAPE 换码字符 ]

◼ 匹配串 可以是一个完整的字符串，也可以含有通配符%和 _

➢ % （百分号） ：代表任意长度（长度可以为0）的字符串。

例如a%b表示以a开头，以b结尾的任意长度的字符串

➢ _ （下横线） ：代表任意单个字符。

例如a_b表示以a开头，以b结尾的长度为3的任意字符串

查询Members表中姓“李”的用户的用户号、姓名及注册时间。
SELECT 用户号, 姓名, 注册时间
FROM Members 
WHERE 姓名 LIKE '李%'; 
查询姓“李”且全名为三个汉字的用户信息。
SELECT *
FROM Members 
WHERE 姓名 LIKE '李_ _' ;

◆使用“ ESCAPE 换码字符 ”短语将通配符转义为普通字符。

SELECT * 
FROM Book 
WHERE 书名 LIKE '%#_%' ESCAPE '#’ ;

➢ ESCAPE '#' 表示“#” 为换码字符

 使得匹配串中紧跟在“#”后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符

◆注意：”\”本身就为转义字符，不需要再用“ ESCAPE 换码字符 ”短语声明。

④ 确定范围

❖谓词：BETWEEN … AND …

NOT BETWEEN … AND …

SELECT * 
FROM Book 
WHERE 出版时间 BETWEEN ‘2021-1-1' AND ‘2021-12-31’;
SELECT * 
FROM Book 
WHERE 出版时间 NOT BETWEEN ‘2021-1-1' AND ‘2021-12-31';

注意：

◆使用BETWEEN AND的时候，必须

遵循左小右大。

◆ BETWEEN AND是闭区间，包括

两端的值。

⑤ 确定集合

❖谓词：IN 值表 , NOT IN 值表

◼ 可用来查找属性值属于（或不属于）指定集合的元组

SELECT * 
FROM Book
WHERE 出版社 IN ('高等教育出版社’, '北京大学出版社’, '人民邮电出版社’) ;

➢IN谓词实际上是多个OR运算符的缩写

注意：

IN不是一个范围，IN后面跟的是具

体的值。

⑥ 涉及空值的查询

❖谓词： IS NULL 或 IS NOT NULL

◼ 注意：“IS” 不能用 “=” 代替

SELECT * 
FROM Sell 
WHERE 是否发货 IS NULL ;
SELECT * 
FROM Sell 
WHERE 是否发货 IS NOT NULL ;

空值的处理

❖空值就是“不知道”或“不存在”或“无意义”的值。

❖一般有以下几种情况：

◼该属性应该有一个值，但目前不知道它的具体值

➢例如某学生的年龄，因为漏统计了，所以不知道该生年龄，因此取

空值。

◼该属性不应该有值

➢例如缺考学生的成绩为空，因为没有参加考试。

◼由于某种原因不便于填写

➢例如某人的电话号码不想让大家知道。

1） 空值的产生

❖空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。

//将Student表中学生号为”201215200”的学生所属的系改为空值。
UPDATE Student
SET Sdept = NULL
WHERE Sno='201215200';

2）空值的判断

❖判断一个属性的值是否为空值，用IS NULL或IS NOT 

NULL来表示

3）空值的约束条件

❖属性定义（或者域定义）中

◼有NOT NULL约束条件的不能取空值

◼码属性不能取空值

4）空值的算术运算、比较运算和逻辑运算

◼ 算术运算：空值与另一个值（包括另一个空值）的算术

运算的结果为空值

◼ 比较运算：空值与另一个值（包括另一个空值）的比较

运算的结果为UNKNOWN。

◼ 逻辑运算：有UNKNOWN后，传统二值（TRUE，

FALSE）逻辑就扩展成了三值逻辑

T表示TRUE， U表示UNKNOWN ，F表示FALSE

◆ 级别：T>U>F；

◆ AND最严格，以最低级别为准；

◆ OR最宽泛，以最高级别为准。

3.ORDER BY子句

◼可以按一个或多个属性列排序

◼升序：ASC（默认）; 降序：DESC

❖对于空值，排序时显示的次序由具体系统实现来决定

🌰：

将Sell表中记录按订购册数从高到低排列。
SELECT *
FROM Sell
ORDER BY 订购册数 DESC ;

4.聚集函数

◼统计元组个数

COUNT(*)

◼统计一列中值的个数

COUNT ( [DISTINCT| ALL] 列名 )

◼求一列中的最大值和最小值

MAX ( [DISTINCT|ALL] 列名 )

MIN ( [DISTINCT|ALL] 列名 )

◼计算一列值的总和（此列必须为数值型）

SUM ( [DISTINCT|ALL] 列名 )

◼计算一列值的平均值（此列必须为数值型）

AVG ( [DISTINCT|ALL] 列名 )

使用聚集函数的注意事项

◼COUNT(*)统计空值，其余函数不统计。

➢COUNT(*)是对元组进行计数，某个元组的一个或部分列取空值不影响COUNT的统计结果。

◼聚集函数不能放在WHERE子句中，只能用于SELECT和HAVING子句

5.GROUP BY子句

聚集函数又称为分组函数

◆分组函数在使用的时候一般先进行分组，然后再用。

◆如果没有对数据进行分组，整张表默认为一组。

细化聚集函数的作用对象

◼如果未对查询结果分组，聚集函数将作用于整个查询结果

◼对查询结果分组后，聚集函数将分别作用于每个组

◼按指定的一列或多列值分组，值相等的为一组

❖执行顺序是什么？

1. from

2. where

3. group by

4. select

5. order by

注意事项：​​​​​​​

在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟

GROUP BY中的HAVING短语

❖HAVING短语可以对分组后的数据进行条件过滤：如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

❖三点注意：

➢HAVING不能单独使用

➢HAVING不能代替WHERE

➢HAVING必须和GROUP BY联合使用。

优化策略：WHERE和HAVING，优先选择WHERE，WHERE实在完成不了，再选择HAVING

单表查询总结

❖执行顺序？

1. from

2. where

3. group by

4. having

5. select

6. order by

6.2 连接查询（多表查询）

1.两表的连接

🌰：

SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book, Sell 
WHERE Book.图书编号=Sell.图书编号 ;

需加上where条件限制，结果才合理。

2.内连接

◆SQL92的缺点：结构不清晰——表的连接条件和后期进一步筛选的条件，都放到了WHERE后面。

◆SQL99常用语法格式

表名1 INNER JOIN 表名2

ON 连接条件

🌰：

//查找Bookstore数据库中客户订购的图书书名，订购册数和订购时间。
SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book INNER JOIN Sell 
ON Book.图书编号=Sell.图书编号

//inner可以省略（带着inner可读性更好！一眼就能看出来是内连接）

◆ SQL99的优点：表连接的条件是独立的。连接之后，如果还需要进一步筛选，在后面继续添加WHERE。

SELECT ...
FROM a INNER JOIN b ON a和b的连接条件
WHERE 筛选条件

3.内连接之自连接

◆作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

技巧：一张表看成两张表  Members a 当做会员表 Members b 当做会员组长表

◆内连接：A和B连接，AB两张表没有“主次”关系，平等的

内连接的特点：将能够匹配该条件的数据查询出来。

4.外连接

◆指定了OUTER关键字的连接为外连接。

◆外连接包括：

（1）左外连接（LEFT OUTER JOIN）：

结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。

（2）右外连接（RIGHT OUTER JOIN）：

结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。

 在外连接当中，两张表连接，产生了“主次”关系。

左外连接

LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

SELECT ...
FROM a
LEFT JOIN b ON a.x = b.x;

右外连接

RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：

SELECT ...
FROM a
RIGHT JOIN b ON a.x = b.x;

外连接的特点

任何一个右连接都有左连接的写法；

任何一个左连接都有右连接的写法；

6.3 嵌套查询

❖嵌套查询概述

◼一个SELECT-FROM-WHERE语句称为一个查询块

◼将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语中的查询称为嵌套查询

🌰：

SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
( SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');

1、不相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

2、相关子查询

相关子查询：子查询的查询条件依赖于父查询

6.4 联合查询

🌰：

SELECT 订单号, 用户号,图书编号,订购册数 FROM sell WHERE 用户号= 
'C0138'
UNION 
SELECT 订单号, 用户号,图书编号,订购册数 FROM sell WHERE 图书编号
= 'TP.2525’;

合并后的结果，默认去掉重复行

◆如何不去掉重复行？

SELECT 订单号, 用户号,图书编号,订购册数 FROM sell WHERE 用户号= 
'C0138'
UNION ALL 
SELECT 订单号, 用户号,图书编号,订购册数 FROM sell WHERE 图书编号
= 'TP.2525’;

UNION 与OR、IN的使用区别在哪里？

UNION、OR、IN都是SQL语句中的逻辑操作符，用于对表或查询结果进行条件组合和筛选。它们的使用区别如下：

1. UNION：用于合并两个或多个查询的结果集，并返回合并后的唯一记录集。UNION操作符连接的查询必须具有相同的列数和数据类型。

2. OR：用于在WHERE子句中指定多个条件中的任意一个满足即可返回结果。OR操作符可用于连接多个条件，例如： SELECT * FROM table WHERE condition1 OR condition2

3. IN：用于筛选在指定列表中的匹配值的结果集。IN操作符可用于代替多个OR操作符，例如： SELECT * FROM table WHERE column IN (value1, value2, value3)

6.5 基于派生表的查询

◆子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中。

技巧：可以将子查询结果当做一张临时表，放在FROM后面

🌰：

SELECT b.用户号, b.图书编号
FROM ( SELECT 用户号, AVG(订购册数) avgC FROM Sell GROUP BY 
用户号) a JOIN Sell b ON b.用户号= a.用户号
WHERE b.订购册数>a. avgC ;

6.6 LIMIT子句

1、作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

例如：百度默认一页显示10条记录。

➢ 分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体

验差，可以一页一页翻页看。

2、常用语法格式

LIMIT [startIndex , ] length

➢ startIndex是起始下标（从0开始），length是长度。

3、注意：MySQL中，LIMIT在ORDER BY之后执行！

🌰：

// 查找Members表中注册时间最靠前的5位会员的信息。
SELECT * FROM Members 
ORDER BY 注册时间
LIMIT 5 ;

❖每页显示pageSize条记录，第pageNo页

记公式：LIMIT (pageNo - 1) * pageSize , pageSize

DQL语句大总结

顺序

1. from 2. where 3. group by 4. having 5. select 6. order by 7. limit

第7章 索引 

7.1 索引及其分类

1、什么是索引？

索引是一种提高查找速度的机制。

MySQL 中有两种方式访问表的行数据：

（1）顺序访问

在表中实行全表扫描。执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至在无序的行数据中找到符合要求的记录。实现比较简单，但表里面的记录数量越多，这个操作的代价就越高。

（2）索引访问

即通过遍历索引来直接访问表中记录行的方式，前提是对表建立一个索引。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。

如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍

2、分类

（1）根据存储方式分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL中常用的索引在物理上分为两类：B-树索引（BTREE）、哈希索引。

（2）根据用途分类

根据索引的具体用途， MySQL中的索引在逻辑上分为：普通索引（INDEX）、唯一性索引（UNIQUE）、主键（PRIMARY KEY）、全文索引（FULLTEXT）

 普通索引（INDEX）

这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。

 唯一性索引（UNIQUE）

这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。

 主键（PRIMARY KEY）

主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。

 全文索引（FULLTEXT）

MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM（存储引擎）表中创建。 

注意：一个字段为主键、ID、UNIQUE约束，会自动创建索引

3、什么条件下，适合加索引？

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）

条件2：该字段经常出现在WHERE的后面，以条件的形式存在，也就是说这个字段总是被扫描。

条件3：该字段很少有DML(insert delete update)操作（因为DML操作之后，索引需要重新排序）。

 建议不要随意添加索引，因为索引需要维护，添加太多反而会降低系统的性能。

 建议通过主键查询，建议通过UNIQUE约束的字段进行查询，效率是比较高的。

 注意：唯一性比较弱的字段上添加索引用处不大

7.2 索引的操作

（1）CREATE INDEX创建索引

使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。

常用语法格式：

CREATE INDEX 索引名 ON 表名 ( 列名 , ... )

🌰：

//在Sell表的“用户号”列和“图书编号”列上建立一个复合索引user_bh_sell。
CREATE INDEX user_bh_sell ON Sell ( 用户号, 图书编号) ;

（2）ALTER TABLE创建索引

常用语法格式：

ALTER TABLE 表名

ADD INDEX [索引名] (列名, ...) /*添加索引*/

| ADD PRIMARY KEY (列名, ...) /*添加主键*/

| ADD UNIQUE [索引名] (列名, ...) / *添加唯一性索引*/

//在Book表的“书名”列上创建一个普通索引。
ALTER TABLE Book ADD INDEX ( 书名 ) ;

查看表中创建的索引：

SHOW INDEX FROM 表名 ;

（3）创建表时创建索引

//创建sell_copy表，设置“用户号”和“图书编号”为联合主键，并在“订购册数”列上创建索引。
CREATE TABLE sell_copy (
用户号 CHAR(18) NOT NULL ,
图书编号 CHAR(20) NOT NULL ,
……
PRIMARY KEY (用户号, 图书编号) ,
INDEX (订购册数)
 );

2、删除索引

（1）DROP INDEX删除索引

DROP INDEX 索引名 ON 表名

（2）ALTER TABLE删除索引

ALTER TABLE 表名
 | DROP PRIMARY KEY /*删除主键*/
 | DROP INDEX 索引名 /*删除索引*/

索引列：使用union查询效率更高；

非索引列：or,in采用的是全表扫描机制，更适合于非索引列查找；

对于既有索引字段【索引字段有效】又包含非索引字段来时，使用or 、in、union 、all 都可以，推荐使用or、in。针对单表

第8章 视图VIEW 

8.1 视图的概念

什么是视图？

直白的理解：

站在不同的角度去看待同一份数据。

概念：

视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义。对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

8.2 创建和删除视图

 1、创建视图

常用语法格式：

CREATE VIEW 视图名 [ (列名列表) ] 
AS SELECT语句
[ WITH CHECK OPTION ]

注意：只有DQL语句才能以VIEW的形式创建

2、删除视图

常用语法格式：

DROP VIEW 视图名（或视图列表）

8.3 操作视图

1、查询视图

2、更新视图（DML操作）

 通过视图更新基本表数据，也就是在视图中使用INSERT、UPDATE或

DELETE等语句操作基本表。操作视图必须保证视图是可更新视图，即视

图中的行和基本表中的行之间必须具有一对一的关系。

在MySQL中，若视图包含下述结构中的任何一种，则是不可更新的：

（1）聚合函数； （6）UNION运算符；

（2）DISTINCT关键字； （7）位于选择列表中的子查询；

（3）GROUP BY子句； （8）FROM子句中包含多个表；即一个视图依赖多个基本表

（4）ORDER BY子句； （5）HAVING子句； （9）SELECT语句中引用了不可更新视图

8.4 修改和查看视图定义

1、修改视图定义

使用ALERT语句可以对已有视图的定义进行修改。

ALTER VIEW XX AS SELECT...

2、查看视图定义

语法：

SHOW CREATE VIEW 视图名

第9章 数据库编程

9.1 编程基础知识

9.1.1 常量与变量

1、常量

（1）字符串常量

（2）数值常量

（3）日期时间常量

（4）布尔值

2、变量

变量用于临时存放数据。

MySQL变量分为两大类：用户变量（局部变量、用户变量）、系统变量（会话变量、全局变量）。

（1）系统变量

◆ 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

◆ 全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

◆ 查看命令

① 查看会话变量清单：show [session] variables

② 查看全局变量清单：show global variables

③ 查看某个变量：例如获得现在使用的MySQL版本

SELECT @@ VERSION ; //@后面没空格

（2）用户变量

①局部变量

◆ 局部变量一般用在SQL语句块中，比如存储过程的BEGIN-END。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。

◆ 局部变量一般用declare来声明，可以使用default来说明默认值。

二者的区别：局部变量前面没有@符号，局部变量在其所在的BEGIN-END语句块处理完成后就消失了，而用户变量存在于整个会话当中。

9.1.2 系统内置函数

9.1.3 流程控制函数

◆ 在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句

1、IF语句

语法格式为：

IF 条件1 THEN 语句序列1
[ELSE 语句序列e ]
END IF

还可以在THEN和ELSE子句中嵌套IF语句

🌰：

DELIMITER $$
CREATE PROCEDURE COMPAR
(IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IF K1>K2 THEN
SET K3= '大于';
ELSE IF K1=K2 THEN
SET K3= '等于';
ELSE 
SET K3= '小于';
END IF; END IF;
END$$
DELIMITER ;

2、WHILE语句

语法格式为：

WHILE 条件 DO
程序段
END WHILE

🌰：

DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE a INT DEFAULT 5 ;
WHILE a > 0 DO
SET a = a-1 ;
END WHILE ;
END$$
DELIMITER ;

9.2 存储过程

9.2.1 创建存储过程

◆ 存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

➢由声明式的SQL语句（如CREATE、UPDATE和SELECT等语句）和过程式的SQL语句（如IF-THEN-ELSE语句）组成。

➢存储过程可以由程序、触发器或另一个存储过程来调用而激活。

1、创建存储过程

（1）常用语法格式

CREATE PROCEDURE 存储过程名 ( [ 参数 ] ) 存储过程体

1）存储过程可以有0个、1个或多个参数（用逗号隔开）；

2）参数的名字不要等于列的名字。

（2）修改结束符号

◆ 在MySQL中，服务器处理语句时以分号为结束标志。但在创建存储过程时，存储过程体中可能包含多个SQL语句，每个SQL语句都以分号为结尾，这时服务器处理程序时遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

◆ 语法格式： DELIMITER $$

说明：$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，两个“￥”等；也可以只是用一个符号，如“DELIMITER $”

2、局部变量

（1）常用语法格式

DECLARE 变量 类型 [DEFAULT值]

（2）使用SET语句赋值

语法格式为：

SET var_name = expr

（3）使用SELECT...INTO语句赋值

◆ 此语句可以把选定的列值直接存储到变量中，因此，返回的结果只能有

一行。

◆ 常用语法格式为：

SELECT 列名 INTO 变量名 数据来源表达式

9.2.2 调用存储过程

◆ 存储过程创建完后，可以在程序、触发器或者存储过程中被调用，调用时都必须使用到CALL语句。

 常用语法格式：

CALL 存储过程名 ( [ 参数 [ ,… ] ] )

个数必须等于存储过程的参数个数

9.2.3 显示与删除存储过程

1、显示存储过程

（1）查看数据库中有哪些存储过程

SHOW PROCEDURE STATUS;

（2）查看某个存储过程的具体信息

SHOW CREATE PROCEDURE 存储过程名 ;

2、删除存储过程

◆ 删除之前，必须确认该存储过程没有任何依赖关系（如嵌套），否则会

导致其他与之关联的存储过程无法运行。

◆ 语法格式：

DROP PROCEDURE [IF EXISTS] 存储过程名

使用存储过程的优点

1、存储过程在服务器端运行，执行速度快。

2、存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

3、确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

9.3 触发器

9.3.1 创建触发器

1、语法格式：

CREATE TRIGGER 触发器名 触发时间 触发事件 ON 表名 FOR EACH ROW 触发器动作

◆ 触发时间：触发器触发的时刻，有AFTER和BEFORE两个选项。

◆ 触发事件：激活触发程序的语句的类型，可以是下述值之一：INSERT、UPDATE、DELETE等。

◆ 对于某一表，不能有两个BEFORE UPDATE触发器，但可以有1个BEFORE UPDATE触发器和1个BEFORE INSERT触发器，或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。

◆ FOR EACH ROW：这个声明用来指定，对于受触发事件影响的每一

行，都要激活触发器的动作。

◆ 触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用BEGIN…END复合语句结构。

注意：触发器不能返回任何结果到客户端，也不能调用将数据返回客户端的存储过程。为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句

2、关于引用列名

◆ MySQL触发器动作中的SQL语句可以关联表中的任意列，但不能直接使用列的名称去标识。

◆ 必须用：“NEW.列名”或者“OLD.列名”。

◆ 对于INSERT事件，只有NEW是合法的；对于DELETE事件，只有OLD才合法；而UPDATE事件可以与NEW或OLD同时使用。

9.3.2 删除触发器

◆ 和其他数据库对象一样，使用DROP语句即可将触发器从数据库中删除。

1、语法格式：

DROP TRIGGER [ IF EXISTS ] 触发器名

2、查看数据库中的触发器

SHOW TRIGGERS;

9.4 存储函数

9.4.1 创建存储函数

1、存储函数与存储过程比较

（1）相同点

存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。

（2）区别

1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；

2）不能用CALL语句来调用存储函数；

3）存储函数必须包含一条RETURN语句，存储过程中不能有。

2、语法格式

CREATE FUNCTION 存储函数名 ( [ 参数或参数列表 ] )
RETURNS 类型
DETERMINISTIC
函数体

9.4.2 调用存储函数

◆ 存储函数创建完后，就如同系统提供的内置函数，所以调用存储函数的

方法也差不多，都是使用SELECT关键字。

常用语法：

SELECT 存储函数名 ( [参数或参数列表 ] )