MySQL

一、初识MySQL

MySQL是一个开放源代码的数据库管理系统（DBMS），是由MySQL 公司开发、发布并支持的。
MySQL是一个跨平台的开源关系型数据库管理系统，广泛地应用在Internet上的中小型网站开发中。

1、数据库基础

数据库由一批数据构成有序的集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映了客观事物间的本质联系。数据库系统提供对数据的安全控制和完整性控制。

1.1 什么是数据库

数据库的概念诞生于60年前，随着信息技术和市场的快速发展，数据库技术层出不穷，随着应用的拓展和深入，数据库的数量和规模越来越大，其诞生和发展给计算机信息管理带来了一场巨大的革命。数据库的发展大致划分为如下几个阶段：人工管理阶段、文件系统阶段、数据库系统阶段、高级数据库阶段。其种类大概有3种：层次式数据库、网络式数据库和关系式数据库。不同种类的数据库按不同的数据结构来联系和组织。对于数据库的概念，没有一个完全固定的定义，随着数据库历史的发展，定义的内容也有很大的差异，其中一种比较普遍的观点认为，数据库（DataBase，DB）是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。它是一个按数据结构来存储和管理数据的计算机软件系统。数据库包含两层含义：保管数据的“仓库”，以及数据管理的方法和技术。
数据库的特点是：实现数据共享，减少数据冗余；采用特定的数据类型；具有较高的数据独立性；具有统一的数据控制功能。

1.2 表

在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成。行被称为记录。是组织数据的单位。列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等。
比如我们有个员工表，用于保存员工的基本信息

1.3 数据类型

数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有整数数据类型、浮点数数据类型、精确小数类型、二进制数据类型、日期/时间数据类型、字符串数据类型。表中的每一个字段就是某种指定数据类型。

类型	大小	范围（有符号	范围（无符号	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或 INTEGER	4 Bytes	(-2 147 483 648， 2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808， 9 223 372 036 854 775 807)	(0， 18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes			单精度浮点数值
DOUBLE	8Bytes			双精度浮点数值
DECIMAL		对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2		小数值

日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999- 12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据

主键
主键（Primary Key）又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上既不能有两行相同的值，也不能为空值。假如，定义authors表，该表给每一个作者分配一个“作者编号”，该编号作为数据表的主键，如果出现相同的值，将提示错误，系统不能确定查询的究竟是哪一条记录；如果把作者的“姓名”作为主键，则不能出现重复的名字，这与现实中的情况不符，因此“姓名”字段不适合作为主键。

1.4 数据库技术构成

数据库系统由硬件部分和软件部分共同构成。硬件主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分主要包括DBMS、支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。
数据库系统数据库系统有3个主要的组成部分
● 数据库：用于存储数据的地方。
● 数据库管理系统：用于管理数据库的软件。
● 数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库的软件补充。
数据库系统（Database System）提供了一个存储空间，用以存储各种数据，可以将数据库视为一个存储数据的容器。一个数据库可能包含许多文件，一个数据库系统中通常包含许多数据库。
数据库管理系统（DataBase Management System，DBMS）是用户创建、管理和维护数据库时所使用的软件，位于用户与操作系统之间，对数据库进行统一管理。DBMS能定义数据存储结构，提供数据的操作机制，维护数据库的安全性、完整性和可靠性。数据库应用程序（DataBase Application）虽然已经有了DBMS，但是在很多情况下，DBMS无法满足对数据管理的要求。数据库应用程序的使用可以满足对数据管理的更高要求，还可以使数据管理过程更加直观和友好。数据库应用程序负责与DBMS进行通信，访问和管理DBMS中存储的数据，允许用户插入、修改、删除DB中的数据。

1.4.1 SQL语言

对数据库进行查询和修改操作的语言叫作SQL。SQL的含义是结构化查询语言（Structured Query
Language）。
SQL有许多不同的类型，有3个主要的标准：ANSI（美国国家标准机构）SQL；对ANSI SQL修改后在 1992年采纳的标准，称为SQL-92或SQL2；近的SQL-99标准，从SQL2 扩充而来并增加了对象关系特征和许多其他新功能。各大数据库厂商提供不同版本的SQL。这些版本的SQL不但能包括原始的ANSI标准，而且在很大程度上支持SQL-92标准。

SQL语言包含以下4部分。
（1）数据定义语言（Data Definition Language DDL）：DROP、CREATE、ALTER等语句。
（2）数据操作语言（Data Manipulation Language DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
（3）数据查询语言（Data Query Language DQL）：SELECT语句。
（4）数据控制语言（Data Control Language DCL）：GRANT、REVOKE、COMMIT、 ROLLBACK等语句。

1.4.2 数据库接口

不同的程序设计语言会有各自不同的数据库访问接口，程序语言通过这些接口执行SQL语句，进行数据库管理。主要的数据库访问接口有ODBC、 JDBC、ADO.NET和PDO【PHP】。

JDBC（Java Data Base Connectivity，Java数据库连接）用于Java应用程序连接数据库的标准方法，是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，由一组用Java语言编写的类和接口组成。

二、MySQL

MySQL是一个小型关系数据库管理系统。与其他大型数据库管理系统（例如Oracle、DB2、SQL Server 等）相比，MySQL规模小、功能有限，但是它体积小、速度快、成本低，并且提供的功能对稍微复杂的应用来说已经够用，这些特性使得MySQL成为世界上受欢迎的开放源代码数据库

2.1 客户端/服务器软件

主从式架构（Client-Server Model）或客户端/服务器（Client/Server）结构（简称C/S结构），是一种网络架构，通常在该网络架构下的软件可分为客户端（Client）和服务器（Server）。服务器是整个应用系统资源的存储与管理中心，多个客户端则各自处理相应的功能，共同实现完整的应用。在客户端/服务器结构中，客户端用户的请求被传送到数据库服务器，数据库服务器进行处理后，将结果返回给用户，从而减少了网络数据传输量。用户使用应用程序时，首先启动客户端通过有关命令告知服务器进行连接以完成各种操作，而服务器则按照此请示提供相应的服务。每一个客户端软件的实例都可以向一个服务器或应用程序服务器发出请求。这种系统的特点就是，客户端和服务器程序不在同一台计算机上运行，这些客户端和服务器程序通常归属不同的计算机。主从式架构通过不同的途径应用于很多不同类型的应用程序，比如现在人们熟悉的在因特网上使用的网页。例如，当顾客想要在当当网站上买书的时候，电脑和网页浏览器就被当作一个客户端，同时组成当当网的电脑、数据库和应用程序就被当作服务器。当顾客的网页浏览器向当当网请求搜寻数据库相关的图书时，当当网服务器从当当网的数据库中找
出所有该类型的图书信息，结合成一个网页，再发送回顾客的浏览器。服务器一般使用高性能的计算机，并配合使用不同类型的数据库，比如Oracle、Sybase或者是MySQL等；客户端需要安装专门的软件，比如专门开发的客户端工具浏览。

MySQL的不同版本

MySQL Community Server（社区版服务器）该版本完全免费，但是官方不提供技术支持。
MySQL Enterprise Server（企业版服务器）能够以很高的性价比为企业提供数据仓库应用，支持ACID 事物处理，提供完整的提交、回滚、崩溃恢复和行级锁定功能。但是该版本需付费使用，官方提供电话技术支持。 MySQL Cluster主要用于架设集群服务器，需要在社区版或企业版基础上使用。
MySQL 8.0是新开发的稳定（GA）发布系列，是将执行新功能的系列，目前已经可以正常使用。
MySQL 8.0是比较稳定（GA）发布系列。只针对漏洞修复重新发布，没有增加会影响稳定性的新功能。
MySQL 5.7是前一稳定（产品质量）发布系列。只针对严重漏洞修复和安全修复重新发布，没有增加会影响该系列的重要功能。

MySQL的优势：
（1）速度：运行速度快。
（2）价格：MySQL对多数个人来说是免费的。（3）容易使用：与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习
（4）可移植性：能够工作在众多不同的系统平台上，例如Windows、 Linux、UNIX、Mac OS等。
（5）丰富的接口：提供了用于C、C++、Eiffel、Java、Perl、PHP、 Python、Ruby和Tcl等语言的
API。
（6）支持查询语言：MySQL可以利用标准SQL语法和支持ODBC的应用程序。
（7）安全性和连接性：十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。由于MySQL是网络化的，因此可以在因特网上的任何地方访问，提高数据共享的效率。

MySQL 8.0的新特性和MySQL 5.7相比，MySQL 8.0的新特性主要包括以下几个方面。
1.数据字典
MySQL 8.0包含一个事务数据字典，用于存储有关数据库对象的信息。在MySQL 8.0之前的版本中，字典数据存储在元数据文件和非事务表中。
2.原子数据定义语句
MySQL 8.0支持原子数据定义语言（DDL）语句。此功能称为原子 DDL。原子DDL语句将与DDL操作关联的数据字典更新，存储引擎操作和二进制日志写入组合到单个原子事务中。即使服务器在操作期间暂停，也会提交事务，并将适用的更改保留到数据字典、存储引擎和二进制日志，或者
回滚事务。通过在MySQL 8.0中引入MySQL数据字典，可以实现原子 DDL。在早期的MySQL版本中，元数据存储在元数据文件、非事务性表和存储引擎特定的字典中，需要中间提交。MySQL数据字典提供的集中式事务元数据存储消除了这一障碍，使得将DDL语句操作重组为原子事务成为可能。

3.安全和账户管理
MySQL 8.0通过以下功能增强数据库的安全性，并在账户管理中实现更
高的DBA灵活性。 MySQL数据库的授权表统一为InnoDB（事务性）表。每个语句都是事务性的，并且对所有创建的用户都是成功或者回滚，发生任何错误都无效。如果成功，就将语句写入二进制日志；如果失败则不写入，发生回滚并且不
进行任何更改。 MySQL 8.0开始支持角色，角色可以看成是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无须为每个用户单独授
权。管理员可以创建和删除角色。 MySQL 8.0开始维护有关密码历史的信息，从而限制了以前密码的重用。管理员可以在全局以及每个账户的基础上建立密码重用策略，从而在密
码更改时限制使用以前使用过的密码。 MySQL 8.0允许账户具有双密码，从而在多服务器系统中无缝地执行分阶段密码更改，无须停机。

4.资源管理
MySQL现在支持资源组的创建和管理，并允许将服务器内运行的线程分配给特定的资源组。资源组属性可以控制其资源，以启用或限制资源组中线程的资源消耗。数据库管理员可以根据不同的工作负载修改这些属性。

5.InnoDB增强功能

MySQL 8.0增强了InnoDB的功能，主要表现如下：
（1） MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数
器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入重做日志时，并不会马上刷新数据库系统。
（2）如果索引损坏，InnoDB将索引损坏标志写入重做日志，从而使得
损坏标志安全。InnoDB还将内存中损坏标志数据写入每个检查点上的引擎专用系统表。在恢复期间，InnoDB从两个位置读取损坏标志并在将内存表和索引对象标记为损坏之前合并结果。
（3）新的动态变量innodb_deadlock_detect可用于禁用死锁检测。在高
并发系统上，当许多线程等待同一个锁时，死锁检测会导致速度减慢，此时禁用死锁检测可能更有效。
6.字符集支持
默认字符集已经更改latin1为utf8mb4。该utf8mb4字符集有几个新的排序规则，其中包括 utf8mb4_ja_0900_as_cs。
7.增加JSON功能
MySQL增强JSON功能主要表现在以下几个方面：
（1）添加了->>运算符，相当于调用JSON_UNQUOTE()的结果。
（2）添加了两个JSON聚合函数JSON_ARRAYAGG()和 39JSON_OBJECTAGG()。JSON_ARRAYAGG()将列或表达式作为其参数，并将结果聚合为单个JSON数组。JSON_OBJECTAGG()取两个列或表达式，将其解释为键和值，并将结果作为单个JSON对象返回。
（3）添加了JSON实用程序功能JSON_PRETTY()，JSON以易于阅读的格式输出现有值；每个JSON对象成员或数组值都打印在一个单独的行上子对象或数组相对于其父对象是2个空格。
（4）添加的JSON_MERGE_PATCH()可以合并符合RFC 7396标准的 JSON。在两个JSON对象上使用时，可以将它们合并为单个JSON对象。
8.数据类型支持
MySQL 8.0支持将表达式用作数据类型的默认值，包括BLOB、TEXT、 GEOMETRY和JSON数据类型，在以前的版本中是根本不会被分配默认值的。

9.查询的优化
MySQL 8.0在查询方面的优化表现如下：
（1） MySQL
8.0开始支持不可见索引。优化器根本不使用不可见索引，但会以其他方式正常维护。默认情况下，索引是可见的。通过不可见索引，数据库管理员可以检测索引对查询性能的影响，而不会进行破坏性的更改。
（2） MySQL8.0开始支持降序索引。DESC在索引定义中不再被忽略，而且会降序存储索引字段。

2.2MySQL的安装与配置

查看是否链接成功

三、数据库基本操作

3.1 创建数据库

MySQL安装完成之后，将会在其data目录下自动创建几个必需的数据库，可以使用

1	SHOW DATABASES;

语句来查看当前所有存在的数据库，输入语句如下。

其中有六个库是mysql必须的，其余是自己创建的库。如何自己创建库呢？可以使用sql语句创建数据库;

1	CREATE DATABASE database_name;

可以使用命令查看数据库的定义

1	SHOW CREATE DATABASE mybatis;

3.2 删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。删除数据库语句和创建数据库的命令相似，MySQL中删除数据库的基本语法格式为：

1	DROP DATABASE database_name

其中，“database_name”为要删除的数据库的名称。若指定的数据库不存在，则删除出错

3.3 InnoDB表

从MySQL 8.0开始，系统表全部换成事务型的InnoDB表，默认的 MySQL实例将不包含任何MyISAM 表，除非手动创建MyISAM表。

1	SELECT DISTINCT ENGINE FROM information_schema.`TABLES`;

面试题：

InnoDB和MyISAM的区别

区别：

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而
MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 小的锁粒度是行锁，MyISAM 小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成
InnoDB 的重要原因之一；

如何选择：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。
删除数据库时需要注意什么？
使用DROP DATABASE命令时要非常谨慎，在执行该命令时，MySQL 不会给出任何提醒确认信
息。用DROP DATABASE声明删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。

练习：

1.查看当前系统中的数据库
2.创建数据库mydb,使用语句查看数据库定义新新
3.删除数据库mydb

四、数据表的基本操作

4.1 创建数据表

在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。
数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。

创建数据表的语句为CREATE TABLE，语法规则如下：

CREATE TABLE TAB_NAME(
	字段1 类型 约束 默认值,
	字段2 类型 约束 默认值,
	字段n 类型 约束 默认值
)

使用CREATE TABLE创建表时，必须指定以下信息：
（1）要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT 等。
（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。
案例：创建员工表：employee

字段名	数据类型	备注
emp_id	int	员工编号
emp_name	varchar(50)	员工名字
emp_sex	char(2)	员工性别
emp_salary	float	员工工资
emp_phone	varchar(50)	员工电话
emp_birth	date	员工生日
dept_id	int	所在部门

创建表的sql语句如下：

CREATE TABLE employee(
	emp_id INT,
	emp_name VARCHAR(50),
	emp_sex CHAR(2),
	emp_salary FLOAT,
	emp_phone VARCHAR(50),
	emp_birth DATE,
	dept_id INT
);

此时可以通过以下命令查看该库下的表

1	SHOW TABLES;

通过上图可以看到表中还有一些默认值，主键，非空等。这些都是属于表的约束。

4.2 表约束

1.数据完整性

数据的完整性是指数据的可靠性和准确性
1.实体完整性：实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性).
2.域完整性：限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则).
3.引用完整性：在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的引用要进行一致的更改.
4.自定义完整性：用户自己定义的业务规则.
四种完整性约束：

实体完整性：唯一约束、主键约束、标识列

域完完整性：限制数据类型、外键约束、默认值、非空约束

引用完整性：外键
自定义完整性：过程，触发器等

2.约束
约束是在表上强制执行的一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。
五类完整性约束：
NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键 FOREIGN KEY 外键

AUTO_INCREMENT自增

CHECKED 检查(mysql不支持的)

4.2.1 主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

1.单子段主键

主键由一个字段组成，SQL语句格式分为以下两种情况。
1)定义列的同时指定主键，语法如下

1	字段名字段类型 primary key

如：创建部门表 dept，表结构如下

字段名	数据类型	备注
deptid	int	部门编号主键
dept_name	varchar(50)	部门名称
dept_desc	varchar(200)	部门描述
dept_num	int	部门人数

sql语句如下：

CREATE TABLE dept(
	deptid INT PRIMARY KEY,
	dept_name VARCHAR(50),
	dept_desc VARCHAR(200),
	dept_num INT
);

通过上图，我们还看到有个自增，自增是什么？
在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加 AUTO_INCREMENT关键字来实现。默认的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条
记录，字段值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT 等）。

CREATE TABLE dept(
	deptid INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(50),
	dept_desc VARCHAR(200),
	dept_num INT
);

2)定义表之后指定主键

1	ALTER TABLE 表名 ADD CONSTRAINT主键名 PRIMARY KEY(字段);

1	ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(emp_id);

2.联合主键

主键由多个字段联合组成，语法规则如下：

1	primary key(字段1,字段2,...字段n)

表关系详细讲解。
主键选取原则：

主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
主键应该是单列的，以便提高连接和筛选操作的效率。
永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
主键应当有计算机自动生成
我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

4.2.2 非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

1	字段名数据类型 not null

4.2.3 唯一约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。唯一性约束的语法规则如下

1	字段名数据类型 UNIQUE

如果定义完表后也可以给某个字段添加唯一约束，语法如下：

1	ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);

1	ALTER TABLE employee ADD CONSTRAINT uk_phone UNIQUE (emp_phone);

删除唯一索引的语法：

1	ALTER TABLE 表名 DROP INDEX 约束名;

1	ALTER TABLE employee DROP INDEX uk_phone;

4.2.4 默认值约束

默认约束（Default Constraint）指定某列的默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。默认约束的语法规则如下：

1	字段名字段类型 default 默认值

CREATE TABLE employee(
	emp_id INT,
	emp_name VARCHAR(50) NOT NULL ,
	emp_sex CHAR(2) DEFAULT '男',
	emp_salary FLOAT,
	emp_phone VARCHAR(50) UNIQUE ,
	emp_birth DATE,
	dept_id INT
);

4.2.5 外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptId与这个id关联。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
语法：

1	ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);

1 2	ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(deptid);

以上案例就是一个一对多的表关系。一个部门下有多个员工，一个员工从属一个部门。
常用的表关系【重点】有三种：一对一，一对多【自关联】，多对多

一对一：人与身份证

/*
	一对一
	人 	身份证
	1 	   1
*/
CREATE TABLE person(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(50),
	pphone VARCHAR(50)
);
CREATE TABLE idcard(
	cid INT PRIMARY KEY,
	cnum VARCHAR(50),
	cstart DATE,
	cend DATE,
	cpublish VARCHAR(200)
);
ALTER TABLE idcard ADD CONSTRAINT fk_card_person FOREIGN KEY(cid) REFERENCES person(pid);

一对多：部门与员工

/*
	1.一对多
	部门 员工
	1 	N
	1 	1
*/
CREATE TABLE department(
	departid INT PRIMARY KEY AUTO_INCREMENT,
	departname VARCHAR(50),
	departnum INT DEFAULT 0,
	departdesc VARCHAR(200)
);
CREATE TABLE employee(
	empid INT PRIMARY KEY AUTO_INCREMENT,
	empname VARCHAR(50),
	empsex CHAR(2) DEFAULT '男',
	empbirth DATE,
	empphone VARCHAR(50),
	deptid INT -- FK
);
/*
alter table tabname add constratin 约束名 foreign key(字段) references 主表
(主键);
*/
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES department(departid);

多对多：用户与角色【中间表多列主键】

/* 多对多的关系
用户 角色
1	 N
N 	1
N 	N
*/
CREATE TABLE users(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR(50) NOT NULL UNIQUE,
	upwd VARCHAR(50) NOT NULL,
	ustatus INT
);
CREATE TABLE roles(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(50) NOT NULL
);
CREATE TABLE userroles(
	uid INT ,
	rid INT,
	PRIMARY KEY(uid,rid)
);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES users(uid);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES roles(rid);

自关联：省市

CREATE TABLE cities(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(50),
	pid INT ,
	CONSTRAINT fk_pro_ci FOREIGN KEY(pid) REFERENCES cities(cid)
)

4.2.6 查看表结构

DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值等。语法规则如下：

1	DESCRIBE 表名;

或者简写为

DESC 表名;

其中，各个字段的含义分别解释如下
NULL：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；
UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，有的话指定值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT等。
查看表详细结构语句：
SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句，语法格式如下：

1	SHOW CREATE TABLE employee;

CREATE TABLE `employee` (
	`emp_id` int(11) NOT NULL AUTO_INCREMENT,
	`emp_name` varchar(50) NOT NULL,
	`emp_sex` char(2) DEFAULT '男',
	`emp_salary` float DEFAULT NULL,
	`emp_phone` varchar(50) DEFAULT NULL,
	`emp_birth` date DEFAULT NULL,
	`dept_id` int(11) DEFAULT NULL,
	PRIMARY KEY (`emp_id`),
	UNIQUE KEY `emp_phone` (`emp_phone`),
	KEY `fk_emp_dept` (`dept_id`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept`
    (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4.2.7 修改表结构

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用 ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。

修改表名

1	ALTER TABLE <旧表名> RENAME [TO] <新表名>;

1	ALTER TABLE employee RENAME TO emp;

修改字段的数据类型

1	ALTER TABLE <表名> MODIFY <字段名> <数据类型>

1	ALTER TABLE emp MODIFY emp_name VARCHAR(30);

修改字段名

1	ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

1	ALTER TABLE emp CHANGE dept_id deptid INT;

添加字段

1	ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST\|AFTER 已存在字段名];

1
2
3

ALTER TABLE emp ADD empaddr VARCHAR(20);
ALTER TABLE emp ADD empaddr VARCHAR(20) AFTER empsex;
ALTER TABLE emp ADD empnum VARCHAR(20) FIRST;

比如条件null

1	ALTER TABLE tabname ADD column1 VARCHAR(12) not null;

删除字段

1	ALTER TABLE <表名> DROP <字段名> ;

1	ALTER TABLE emp DROP empno;

修改字段的排序位置

1	ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST\|AFTER <字段2>;

1	ALTER TABLE emp MODIFY deptid INT AFTER empphone;

删除表的外键约束

1	ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

1	ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

更改表的存储引擎

1	ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

1	ALTER TABLE tb_deptment3 ENGINE=MyISAM;

删除数据表

1	DROP TABLE [IF EXISTS]表1, 表2,…表n;

练习：

练习一：

创建数据库company,在库中创建两个表offices和employees表 offices表结构：

employees表结构
查看该库下几个表以及查看两张表结构。
将表employees的mobile字段修改到officeCode字段后面。
将表employees的birth字段改名为employee_birth。
修改sex字段，数据类型为CHAR(1)，非空约束。
删除字段note。
增加字段名favoriate_activity，数据类型为VARCHAR(100)。
删除表offices。
将表employees名称修改为employees_info。

练习二：

创建数据库Market，在Market中创建数据表customers

创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束。
将c_contact字段插入c_birth字段后面。
将c_name字段数据类型改为VARCHAR(70)。
将c_contact字段改名为c_phone。
增加c_gender字段，数据类型为CHAR(1)。
将表名修改为customers_info。
删除字段c_city。
修改数据表的存储引擎为MyISAM。
在Market中创建数据表orders。
创建数据表orders，在o_num字段上添加主键约束和自增约束，在 c_id字段上添加外键约束，关联customers表中的主键c_num。
删除orders表的外键约束，然后删除表customers。

练习三：

1.创建数据库MySchool

2.创建以下数据表
1> 班级表：Grade表

2> 学生表： Student表

3> 科目表：Subjects表

4> 成绩表：Result表

5> 表关系：

建表的时候，如果选择数据类型

整数和浮点数
如果不需要小数部分，就使用整数来保存数据；如果需要表示小数部分，就使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，假设列的值的范围为1~99999，若使用整数，则 MEDIUMINT UNSIGNED是好的类型；若需要存储小数，则使用FLOAT 类型
浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存储精度较高时应选择DOUBLE类型
浮点数和定点数浮点数

FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。进行数值比较时，好使用DECIMAL类型
日期与时间类型
MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可；如果只记录时间，则使用TIME类型。
如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期好使用DATETIME。
TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时，使用TIMESTAMP是方便的。另外，TIMESTAMP在空间上比 DATETIME更有效。
CHAR与VARCHAR之间的特点与选择
CHAR和VARCHAR的区别如下
CHAR是固定长度字符，VARCHAR是可变长度字符。
CHAR会自动补空格，VARCHAR不自动补。
CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存储空间，所以对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。

五、数据CRUD操作

1、插入数据

1
2
3

INSERT INTO 表名 VALUES(val_list);
或
INSERT INTO 表名(col_list) VALUES(val_list);

可以插入多条记录

1	INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3),(val_list4);

备份数据表

如果表不存在，此时

1	CREATE TABLE emp_bak AS SELECT * FROM emp;

如果表存在，此时

1	INSERT INTO emp_bak SELECT * FROM emp;

2、更新数据

1	UPDATE table_name SET col=val,col=val [WHERE <condition>];

1	UPDATE emp SET empname='jerry',empaddr='延安',empphone='118' WHERE empid=1;

3、删除数据

1	DELETE FROM table_name [WHERE <condition>];

如果删除语句没where,此时会将数据表中的记录全部删除，类似TRUNCATE TABLE.

TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为 TRUNCATE TABLE table_name。

TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。而且不能用在有主外键关系的主表中。

mysql为什么不建议delete数据?
delete对性能会有影响，一般不建议硬性delete数据，而是标记deleted = 1这种软删除
为啥呢？
根据之前了解的mysql底层存储原理 小存储单元page页，无论是非叶子节点page存的是索引key和指针，还是叶子节点存的是行数据.
1.当删除大量数据时
MySQL内部不会真正删除空间，而且做标记删除，即将delflag:N修改为delflag:Y，commit之后会会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入的记录小于等于delete的记录空会被重用
2.当少量删除中间数据时
你在中间删了某些个值，实际上只是找到那个page页找到对应的数据做删除标记，并不会实际影响page 页已经占的大小，这块空间可能也永远不会被利用，产生了内存碎片导致索引频繁分裂，影响SQL执行计划的稳定性
正确姿势：
使用deleted = 1 字段来软删除，保证索引连续性，
必要时，可将deleted = 0的字段完整迁移到新表，解决碎片问题

4、查询数据

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：

1 2	1SELECT 字段列表 FROM 表1,表2... WHERE 表达式 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT [<offset>,] <row count>]

{* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，后一个字段后不加逗号。
FROM <表1>,<表2>…，表1和表2表示查询数据的来源，可以是单个或者多个。
WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY <字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序
（ASC）、降序（DESC）。
[LIMIT [,] ]，该子句告诉MySQL每次显示查询出来的数据条数。

单表查询：

1> 查询所有字段

1	SELECT * FROM 表名;

2> 查询指定字段

1	SELECT 列名 FROM 表名;

3> 查询多个字段

1	SELECT 字段名1,字段名2,…,字段名n FROM 表名;

1	SELECT empid,empname,empsex,empaddr,empbirth,empphone,deptid FROM emp;

查询的时候可以起别名

1	SELECT e.`empid` AS 员工编号 ,e.`empname` AS 员工名字 FROM emp e;

4> 查询指定记录
数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在
SELECT语句中，通过WHERE子句可以对数据进行过滤，语法格式为：

1	SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件

如：查询性别为女的员工信息

1	SELECT * FROM emp WHERE empsex='女';

5>带IN关键字的查询
N操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。
在IN关键字前面加上了NOT关键字，这使得查询的结果与前面一个的结果正好相反，

如：查询编号为 1 4 8 10 的员工信息

1 2	SELECT * FROM emp WHERE empid=1 OR empid=4 OR empid=8 OR empid=10; SELECT * FROM emp WHERE empid IN (1,4,8,10);

1	SELECT * FROM emp WHERE empid NOT IN (1,4,8,10);

6>带BETWEEN AND的范围查询
BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数,即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。
BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定的范围内的值，则这些记录被返回。

1 2	SELECT * FROM emp WHERE empid>=6 AND empid<=10; SELECT * FROM emp WHERE empid BETWEEN 6 AND 10;

7>带LIKE的字符匹配查询
通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL 语句中支持多种通配符，可以和
LIKE一起使用的通配符有‘%’和‘_’。

百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

下划线通配符‘_’，一次只能匹配任意一个字符

1
2
3

SELECT * FROM emp WHERE empname LIKE 'a%'; -- 以a开头
SELECT * FROM emp WHERE empname LIKE '%a'; -- 以a结尾
SELECT * FROM emp WHERE empname LIKE '%a%'; -- 包含a

名字是两个字母的而且是a开头的

SELECT * FROM emp WHERE empname LIKE 'a_';
SELECT * FROM emp WHERE empaddr IN ('西安','武当山');
SELECT * FROM emp WHERE empaddr ='西安' OR empaddr = '武当山';
SELECT * FROM emp WHERE empaddr LIKE '西安%';

8>查询空值
数据表创建的时候，设计者可以指定某列中是否包含空值（NULL）在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。与IS NULL相反的是NOT NULL，该关键字查找字段不为空的记录。

空的查询查询地址为空的员工信息

1
2
3

SELECT * FROM emp WHERE empaddr IS NULL;
SELECT * FROM emp WHERE empaddr ='';
SELECT * FROM emp WHERE empaddr IS NULL OR empaddr='';

非空查询

1
2
3

SELECT * FROM emp WHERE empaddr IS NOT NULL;
SELECT * FROM emp WHERE empaddr !='';
SELECT * FROM emp WHERE empaddr IS NOT NULL AND empaddr!='';

9>带AND的多条件查询
MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND 连接两个甚至多个查询条件，多个条件表达式之间用AND分开

如：查询性别为男同时地址是西安锦业路

1	SELECT * FROM emp WHERE empsex='男' AND empaddr='西安锦业路';

10>带OR的多条件查询
与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开

11>查询结果不重复

在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。

1	SELECT DISTINCT empid,empname,empsex FROM emp;

12>对查询结果排序
使用ORDER BY子句对指定的列数据进行排序。 desc:倒叙 asc:默认值，升序

单列排序
1
SELECT * FROM emp ORDER BY empid DESC;
多列排序

1	SELECT * FROM emp ORDER BY empid DESC ,deptid DESC;

13>聚合函数
有时候并不需要返回实际表中的数据，而只是对数据进行总结。 MySQL提供一些查询功能，可以对获取的数据进行分析和报告。这些函数的功能有：计算数据表中记录行数的总数、计算某个字段列下数据的
总和，以及计算表中某个字段下的大值、小值或者平均值。常用的聚合函数 MAX()、MIN()、
COUNT()、SUM()、AVG()。

函数	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的大值
MIN()	返回某列的小值
SUM()	返回某列值的和

1） COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。

COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
2） SUM()是一个求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。
3） AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
4） MAX()函数返回指定列中的大值。
MAX()函数除了用来找出大的列值或日期值之外，还可以返回任意列中的大值，包括返回字符类型
的大值。在对字符类型数据进行比较时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码小，z的大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，‘b’与‘t’比较时，‘t’为大值；“bcd”与“bca”比较时，“bcd”为大值。
5） MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。
14>分组查询
GROUP BY关键字通常和集合函数一起使用

查询每个部门的人数

1	SELECT deptid,COUNT(1) FROM emp GROUP BY deptid;

统计每个部门的男女人数

1	SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex;

分组后再排序

1 2	SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex ORDER BY deptid DESC,COUNT(1) DESC;

15>使用HAVING过滤分组
GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示对于统计的每个部门的男女人数只要大于等于3的信息

1 2	SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex HAVING COUNT(1)>=3 ORDER BY COUNT(1) DESC,deptid DESC;

16>LIMIT
SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或者前几行，可使用LIMIT 关键字。

练习：数据修改与删除

将地址是西安市的更新为西安科技二路
将S1001的email修改为空的字符串
将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
将S1001 ，课程编号为 2 的成绩提高 5分
将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
经核实 S1004 课程编号为2 的缺考
将计算机网络课程删除掉

数据查询【部分函数后面章节介绍，学习完毕后继续完成】：
1、查询全部一年级的学生信息。
2、查询全部二年级的学生的姓名和电话。
3、查询全部一年级女同学的信息。
4、查询课时超过60的科目信息。
5、查询二年级的科目名称
6、查询二年级男同学的姓名和住址。
7、查询无电子邮件的学生姓名和年级信息。
8、查询出生日期在1992年之后的男学生姓名和年级信息。
9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
10、按照出生日期查询一年级的学生信息。
11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
13、查询课时多的科目名称及课时。
14、查询年龄小的学生所在的年级及姓名。
15、查询考试的低分出现在哪个科目
16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
17、查询年龄超过25周岁的学生信息。
18、查询1月份过生日的学生信息
19、查询今天过生日的学生姓名及所在年级。
20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
21、查询住址为“雁塔区”的学生姓名、电话、住址
22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
23、查询电话中含有以“130”开头的学生姓名，住址和电话。
24、查询姓“赵”的学号、姓名和住址。
25、统计一年级女生的总人数。
26、查询李四总成绩
27、学号为s1003的学生所有课总成绩
28、学号为s1003的学生考试的平均分。
29、查询一年级的科目“Mysql”的高分、低分、平均分。
30、查询每个年级的总学时数，并按照升序排列。
31、查询每个参加考试的学员的平均分。（Group by 学号）
32、查询每门课程的平均分，并按照降序排列。（group by 课程）
33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
34、查询一年级的平均年龄。
35、查询每个年级西安地区的学生人数。
36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
37、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
38、查询学生姓名、所属年级名称及联系电话。
39、查询年级编号为1的科目名称、年级名称及学时。
40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
42、查询所有科目的参考信息（某些科目可能还没有被考试过）
43、查询没有被考过的科目信息。

六、连接查询和子查询

6.1 链接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询.

1.内连接查询

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

1	SELECT * FROM dept d INNER JOIN emp e ON d.`deptid`=e.`deptid`;

2.外连接查询

左外连接

1	SELECT * FROM dept LEFT JOIN emp ON dept.`deptid`=emp.`deptid`

右外连接

1	SELECT dept.,emp. FROM emp RIGHT JOIN dept ON dept.`deptid`=emp.`deptid`

6.2 子查询

1)子查询

-- 查询员工信息，并展示员工所在的部门名字
SELECT emp.*, (SELECT deptname FROM dept WHERE
dept.`deptid`=emp.`deptid`)deptname
FROM emp WHERE emp.`deptid` IN (SELECT deptid FROM dept)

-- 查询用户锁拥有的角色
SELECT ur.*,
	(SELECT users.`uname` FROM users WHERE users.`uid`=ur.`uid`) uname,
	(SELECT roles.`rname` FROM roles WHERE roles.`rid`=ur.`rid`) rname
	FROM userroles ur

-- 查询成绩最高的学生信息
SELECT * FROM xueshenbiao WHERE xuehao IN
	(
		SELECT xuehao FROM 成绩表 WHERE 成绩
		=(SELECT MAX(chengji) FROM 成绩表)
	);

-- 查询考试没有及格的学生信息
SELECT * FROM xueshengbiao WHERE xuehao IN (
SELECT DISTINCT xuehao FROM 成绩表 WHERE chengji<60
);

1
2
3

-- 查询两门课以上不及格的学生信息
SELECT * FROM xuesb WHERE xuhao IN(SELECT xh FROM chengjibao WHERE cj<60
GROUP BY xh HAVING COUNT(1)>1) ;

-- 查询平均分低于总平均的记录
SELECT studentno,AVG(studentresult)
FROM result GROUP BY studentno HAVING
AVG(studentresult)<
(SELECT AVG(studentresult) FROM result);

-- 查询所有科目中成绩最低的学生信息
SELECT * FROM student WHERE studentno IN
(SELECT studentno FROM result WHERE studentresult =
(SELECT MIN(studentresult) FROM result) );

-- 查询成绩高于所有科目平均分的学生信息
SELECT * FROM student WHERE studentno IN (
SELECT studentno FROM result a,
	(SELECT AVG(studentresult) avgscore ,subjectid FROM result GROUP BY
subjectid)b
	WHERE a.`subjectid`=b.subjectid AND a.`studentresult`>b.avgscore);

-- 查询成绩比张三的语文成绩还低的学生信息
SELECT * FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult <(
SELECT studentresult FROM result WHERE studentno=
(SELECT studentno FROM student WHERE studentname='张三')
AND subjectid = (SELECT subjectid FROM subjects WHERE subjectname='语文');
)
)

2)any,some

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

1 2	CREATE table tbl1 ( num1 INT NOT NULL); CREATE table tbl2 ( num2 INT NOT NULL);

有两个表tb1与tb2,数据如下

1
2
3

SELECT num1 FROM tb1 WHERE num1> ANY(SELECT num2 FROM tb2);
-- 或
SELECT num1 FROM tb1 WHERE num1> SOME(SELECT num2 FROM tb2);

ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回 TRUE。
将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。

3)All

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

1	SELECT num1 FROM tb1 WHERE num1> ALL(SELECT num2 FROM tb2);

1
2
3

-- 找到工资比人事部工资高的信息
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE emp.deptid=
(SELECT deptid FROM dept WHERE deptname='人事部')) ;

4)EXISTS

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返
回的结果是false，此时外层语句将不进行查询。

1 2	SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb2 WHERE num2>10) AND num1>10;

如果 SELECT * FROM tb2 WHERE num2>10 有返回结果，则执行外部查询，此时会查询tb1表，并且要过来num1的值大于10的。如果将num2大于100的没有结果，则外部查询不执行。

NOT EXISTS 与 EXISTS相反

5)UNION

创建tb1与tb2，并插入数据

1
2
3

SELECT * FROM tb1
UNION ALL
SELECT * FROM tb2;

1
2
3

SELECT * FROM tb1
UNION
SELECT * FROM tb2;

6)正则查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

-- 以字母a开头
SELECT * FROM employees WHERE first_name REGEXP '^a';
-- $结尾
SELECT * FROM employees WHERE first_name REGEXP 'a$';
-- b中间是任意字符c b与c之间得有一个任意字符，
SELECT * FROM employees WHERE first_name REGEXP 'b.c';
-- +b必须得出现到n次
SELECT * FROM employees WHERE first_name REGEXP 'b+c';
-- * 必须得0到n次
SELECT * FROM employees WHERE first_name REGEXP 'b*c';
SELECT * FROM employees WHERE first_name REGEXP 'b{2,}c';
-- {2,5} 2到5次
SELECT * FROM employees WHERE first_name REGEXP 'b{2,4}c';

练习：

创建数据表：employee表

创建部门表 dept

输入记录： employee表

部门表中的记录

查询练习：

查询所有记录的e_no、e_name和e_salary字段值
查询dept_no等于10和20的所有记录
查询工资范围在800~2500之间的员工信息
查询部门编号为20的部门中的员工信息
查询每个部门高工资的员工信息
查询员工BLAKE所在部门和部门所在地
查询所有员工的部门和部门信息
计算每个部门各有多少名员工
计算不同类型职工的总工资数
计算不同部门的平均工资
查询工资低于1500的员工信息
将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
查询员工姓名以字母‘A’或‘S’开头的员工的信息
查询到目前为止工龄大于等于18年的员工信息
计算所有女员工（‘F’）的年龄
使用LIMIT查询从第3条记录开始到第6条记录的结果
查询销售人员（SALSEMAN）的低工资
查询名字以字母N或者S结尾的记录
查询在BeiJing工作的员工的姓名和职务
使用左连接方式查询employee和dept表
查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果
使用LIKE查询员工姓名中包含字母a的记录
使用REGEXP查询员工姓名中包含T、C或者M这3个字母中任意1个的记录

七、系统函数

MySQL中的函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等其他函数

7.1 数学函数

绝对值函数ABS(x)和返回圆周率的函数PI()
平方根函数SQRT(x)和求余函数MOD(x,y)
获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

CEIL(x)和CEILING(x)的意义相同，返回不小于x的小整数值，返回值转化为一个BIGINT。

FLOOR(x)返回不大于x的大整数值，返回值转化为一个BIGINT。
获取随机数的函数RAND()和RAND(x)

RAND(x)返回一个随机浮点值v，范围在0到1之间（0 ≤ v ≤ 1.0）。若已指定一个整数参数x，则它被用作种子值，用来产生重复列。
函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

ROUND(x)返回接近于参数x的整数，对x值进行四舍五入。

ROUND(x,y)返回接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截（归零）x小数点左起第y位开始后面所有低位的值。

符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。
幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。 EXP(x)返回e的x乘方后的值。

1 2	SELECT ROUND(AVG(empid),1) FROM emp; SELECT TRUNCATE(AVG(empid),1) FROM emp;

7.2 字符串函数

计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。
合并字符串函数**CONCAT(s1,s2,…)**、 CONCAT_WS(x,s1,s2,…)
替换字符串的函数INSERT(s1,x,len,s2)
字母大小写转换函数 LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成小写字母。 UPPER(str)或者UCASE(str可以将字符串str中的字母字符全部转换成大写字母。
获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
填充字符串的函数LPAD(s1,len,s2)和RP
删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)
重复生成字符串的函数REPEAT(s,n)
空格函数**SPACE(n)和替换函数REPLACE(s,s1,s2)**SPACE(n)返回一个由n个空格组成的字符串。
比较字符串大小的函数STRCMP(s1,s2)
获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)
匹配子串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3个函数的作
字符串逆序的函数REVERSE(s)

REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。
返回指定字符串位置的函数FIELD(s,s1,s2,…,sn)

FIELD(s,s1,s2,…,sn)返回字符串s在列表s1,s2,…,sn中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。
返回子串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为 NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

SELECT CHAR_LENGTH('helloworld');
-- 字符串拼接
SELECT CONCAT('hello','-','world','-','你好');
-- 以*来拼接后面的字符串
SELECT CONCAT_WS('*','hello','world','你好');
-- 查找hello,再后面的字符串列表中的位置 从 1 开始
SELECT FIELD('hello','你好','延安','helloworld');
SELECT FIND_IN_SET('hello','你好,延安,hello,world');
SELECT empid,empname,empsex FROM emp;
SELECT CONCAT_WS('-',empid,empname,empsex) FROM emp;
SELECT FORMAT("256998.855",1);
-- 从第6个开始4个长度替换为ab
SELECT INSERT('helloworld',6,4,'ab');
-- 检索world再helloworld中出现的位置
SELECT LOCATE('world','helloworld');
-- 从左边截取
SELECT LEFT('helloworld',8);
-- 从右边截取
SELECT RIGHT('helloworld',8);
-- 从第6个位置开始截取到最后
SELECT SUBSTR('helloworld',6);
-- 从第6个位置开始截取三个长度
SELECT SUBSTR('helloworld',6,3);

练习：

已知表中保存一下记录 5-602 12-1203 5-1303 12-608 请按照单元和门牌号排序
1）创建表

CREATE TABLE sales(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	snum VARCHAR(50)
);

2）插入记录

1 2	INSERT INTO sales VALUE(NULL,'5-602'),(NULL,'12-1203'),(NULL,'5-1303'), (NULL,'12-608'),(NULL,'15-1602'),(NULL,'5-802'),(NULL,'20-1602');

3）实现查询的sql

SELECT LOCATE('-',snum) FROM sales;
SELECT LEFT(snum,LOCATE('-',snum)-1) FROM sales;
SELECT CHAR_LENGTH(snum)-LOCATE('-',snum) FROM sales;
SELECT snum,
	LEFT(snum,LOCATE('-',snum)-1),
	RIGHT(snum,CHAR_LENGTH(snum)-LOCATE('-',snum)) FROM sales;
-- 排序
SELECT snum FROM sales ORDER BY CONVERT(LEFT(snum,LOCATE('-
',snum)-1),SIGNED),
CONVERT(RIGHT(snum,CHAR_LENGTH(snum)-LOCATE('-',snum)) ,SIGNED);
-- 日期类型
SELECT CONVERT('2000-8-9',DATE);
-- 转为正数signed
SELECT CONVERT('1000',SIGNED);

7.3 日期和时间函数

1.系统当前时间

SELECT NOW();
SELECT SYSDATE();
SELECT CURDATE(); -- 年月日
SELECT CURRENT_DATE(); -- 年月日
SELECT CURRENT_TIME; -- 时分秒
SELECT CURTIME(); -- 时分秒

2.addXXX()加上

-- adddate()加上天数
SELECT ADDDATE(NOW(),40);

SELECT ADDDATE('2020-10-19 12:32:45',INTERVAL 5 MINUTE); -- 5 分钟
SELECT ADDDATE('2020-10-19 12:32:45',5); -- 5 天
SELECT ADDDATE('2020-10-19 12:32:45',INTERVAL 5 YEAR); -- 5 年
SELECT ADDDATE('2020-10-19 12:32:45',INTERVAL 5 MONTH); -- 5 个月
-- 加上秒,最大到59
SELECT ADDTIME(NOW(),59),NOW();

3.DATEDIFF() 两个时间相差天数

1	SELECT DATEDIFF(NOW(),'2020-5-8');

4.DATE_FORMAT 日期格式化

1	SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点%i分%s秒')

简写	含义
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

DATE_SUB(date,INTERVAL expr type) 减去多少天

1
2
3

SELECT DATE_SUB(NOW(),INTERVAL 45 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 3 YEAR);
SELECT DATE_SUB(NOW(),INTERVAL 3 MONTH);

EXTRACT
EXTRACT(type FROM date)函数所使用的时间间隔类型说明符与DATE_ADD()或DATE_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算
1
2
3
SELECT EXTRACT(DAY FROM NOW()) ;
SELECT EXTRACT(YEAR FROM NOW()) ;
SELECT EXTRACT(MONTH FROM NOW()) ;

MONTH(),MONTHNAME() 获取月份函数

1 2	SELECT MONTH(NOW()); -- 获取月份数字 SELECT MONTHNAME(NOW()); -- 获取月份英文

DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
DAYNAME(d)函数返回d对应的工作日的英文名称，例如Sunday、 Monday等
DAYOFWEEK(d)函数返回d对应的一周中的索引（位置，1表示周日，2表示周一，…，7表示周六）。
WEEKDAY(d)返回d对应的工作日索引：0表示周一，1表示周二，…，6表示周日。
WEEK(d)和WEEKOFYEAR(d)
WEEK(d)计算日期d是一年中的第几周WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为0~~53或1~~53。若Mode参数被省略，则使用default_week_format 系统自变量的值.

WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是1~53，相当于WEEK(d,3)。

1	SELECT WEEK(NOW()),WEEK(NOW(),0),WEEK(NOW(),1),WEEK(NOW(),3);

10.DAYOFYEAR(d)和DAYOFMONTH(d)
DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1366。
DAYOFMONTH(d)函数返回d是一个月中的第几天，范围是131。
11.YEAR(),QUARTER(),MINUTE(),SECOND()
YEAR(date)返回date对应的年份，范围是19702069.
QUARTER(date)返回date对应的一年中的季度值，范围是14。
MINUTE(time)返回time对应的分钟数，范围是059。
SECOND(time)返回time对应的秒数，范围是059。

练习：
1> 求今天过生日的学生信息
2> 本周过生日的学生信息
3> 统计本年每个季度的消费总金额

7.4条件判断函数

IF(expr,v1,v2)函数
IF(expr, v1, v2)：如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则返回值为v1；否则返回值为v2.IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。
1
SELECT salary, IF(salary>20000,'高工资','低工资') FROM employees;
IFNULL(v1,v2)函数
IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。
1
SELECT manager_id,IFNULL(manager_id,'无上级领导') FROM employees;

CASE函数
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

SELECT salary,
	CASE salary WHEN 24000 THEN '高工资'
				WHEN 17000 THEN '基本工资'
	END
	
	FROM employees;
	SELECT job_id,
	CASE job_id WHEN 'AD_PRES' THEN 'CEO'
				WHEN 'AD_VP' THEN '经理'
				WHEN 'IT_PROG' THEN 'IT'
				ELSE '基本工种'
	END jobtype
	
FROM employees;

7.5系统信息函数

SELECT VERSION(); -- 版本
SELECT CONNECTION_ID(); -- 查看当前用户的链接数
-- 查看链接的详细信息
SHOW PROCESSLIST; -- 前100条
SHOW FULL PROCESSLIST; -- 所有信息

Id列，用户登录MySQL时，系统分配的是“connection id”。
User列，显示当前用户。如果不是root，这个命令就只显示用户权限范围内的SQL语句。
Host列，显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出现问题语句的用户。
db列，显示这个进程目前连接的是哪个数据库。
Command列，显示当前连接执行的命令，一般取值为休眠（Sleep）、查询（Query）、连接（Connect）。
Time列，显示这个状态持续的时间，单位是秒。
State列，显示使用当前连接的SQL语句的状态，很重要的列。后续会有所有状态的描述， State只是语句执行中的某一个状态一个SQL语句，以查询为例，可能需要经过Copying to tmp table、Sorting result、 Sending data等状态才可以完成。
Info列，显示这个SQL语句，是判断问题语句的一个重要依据。

7.6 获取用户名函数

1	SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();

7.7 获取最后一个自动生成的ID的值函数

LAST_INSERT_ID()函数返回后生成的AUTO_INCREMENT值。

1	select LAST_INSERT_ID()

在向数据表中插入一条新记录时，LAST_INSERT_ID()返回带有 AUTO_INCREMENT约束的字段
新生成的值；使用一条INSERT语句插入多行时，LAST_INSERT_ID()只返回插的第一行数据时产生的值之所以这样，是因为这使依靠其他服务器复制同样的INSERT语句变得简单。

7.8其他函数

格式化函数FORMAT(x,n)

1	SELECT FORMAT(14722.25862, 4),FORMAT(14722.25868, 4),FORMAT(14722.25862,0);

类型转换函数：CAST(x, AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值，可转换的type有BINARY、CHAR(n)、DATE、TIME、 DATETIME、DECIMAL、SIGNED、UNSIGNED。

八、存储过程与自定义函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE
PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

1>存储过程：

创建存储过程，需要使用CREATE PROCEDURE命令。语法如下：

CREATE PROCEDURE name(param)
	BEGIN
		body;
	END$$

name：代表参数的名字，param:参数。参数有 in|out|inout类型。in:输入参数，out:输出参数， inout:输入输出参数。

-- 查找所有的部门数量
DELIMITER $$
	CREATE PROCEDURE demo()
	BEGIN
		SELECT COUNT(1) FROM depts;
	END$$
DELIMITER ;
-- 调用过程
CALL demo();
-- 删除过程
DROP PROCEDURE demo;

-- 输出类型的参数 参数不能以@开头
DELIMITER $$
    CREATE PROCEDURE demo2(OUT num INT)
    BEGIN
    	SELECT COUNT(1) INTO num FROM depts;
    END$$
DELIMITER ;

-- 声明变量 @
CALL demo2(@num);
SELECT @num;

小练习：

根据编号查询名字【传入编号，获取名字】
根据编号查询vip的名字(传入编号，获取名字和性别)
创建过程，根据输入的成绩编号【pk】，获取该学号，科目号以及成绩
根据考试号查找学生的名字，科目名字，以及成绩

2>函数：

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下

1	CREATE FUNCTION funname(parama) RETURNS type

注意，如果是8.0，需要设置可以

1	SET GLOBAL log_bin_trust_function_creators=TRUE;

DELIMITER $$
    CREATE FUNCTION fun_01() RETURNS INT
    BEGIN
        DECLARE _num INT;
        SELECT COUNT(*) INTO _num FROM grade;
        RETURN _num;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE FUNCTION fun_02(_sid INT) RETURNS VARCHAR(50)
    BEGIN
        DECLARE _sno VARCHAR(50);
        DECLARE _subid INT;
        DECLARE _sco INT;
        SELECT s_no,sub_id,student_sco INTO _sno,_subid,_sco FROM score WHERE sco_id=_sid;
        RETURN CONCAT(_sno,'--',_subid,'--',_sco);
    END$$
DELIMITER ;

DELIMITER $$
    CREATE FUNCTION fun_03(_sid INT, _sno VARCHAR(50), _subid VARCHAR(50)) RETURNS VARCHAR(100)
    BEGIN
    	DECLARE _sco INT;
    	SELECT s_no,sub_id,IFNULL(student_sco,0) INTO _sno,_subid,_sco FROM score WHERE sco_id=_sid;
    	RETURN CONCAT(_sno,'--',_subid,'--',_sco);
    END$$
 DELIMITER ;

SELECT * FROM score;
SELECT fun_03(8,@sno,@subid);
SELECT CONCAT(@sno,@subid);

-- 传入条件 如果1 查询
DELIMITER $$
    CREATE FUNCTION fun_05(_cond INT,_id VARCHAR(20) ) RETURNS VARCHAR(100)
    BEGIN
        DECLARE _sco VARCHAR(50);
        IF _cond=1 THEN
        	SELECT g_name INTO _sco FROM grade WHERE g_id=_id ;
        ELSEIF _cond=2 THEN
       		SELECT s_name INTO _sco FROM student WHERE s_no=_id;
        ELSE
        	SELECT sub_name INTO _sco FROM subjects WHERE sub_id=_id;
        END IF;
    
    	RETURN CONCAT(_cond,'--',_id,'--',_sco);
    END$$
DELIMITER ;

小练笔：

使用存储函数|过程传入学号，获取学生的姓名，总分，平均分
输入，科目名字，学时，以及gradeid ，将该数据插入到数据库
根据传入的科目编号删除科目信息，如果该科目以及被考过试，则不能删除

九、触发器、游标与视图

1、触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATAE和 DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。比如当对fruits表进行操作（INSERT、DELETE或UPDATE）时就会激活它执行。触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。

例如，可以根据客户当前的账户状态控制是否允许插入新订单
触发器：在哪个表触发

行级触发器：影响几条记录就会触发几次

语句级触发器:一条sql语句执行了，就会触发一次

before after

新值：新添加的值教新值

旧值：
update grade set gradename=’新班级’ where gradeid=1;此时“新班级”为新值，数据库中原有的值为旧值。

1
2
3

一个表不要添加过多的触发器
oracle支持行级触发器以及语句级触发器
mysql:不支持语句级触发器

-- 行级触发
DELIMITER $$
    CREATE TRIGGER tri_01 AFTER INSERT ON dept FOR EACH ROW
    BEGIN
    	INSERT INTO depts VALUES(NULL,new.deptid,'新增');
    END$$
DELIMITER ;

DELIMITER $$
    CREATE TRIGGER tri_02 AFTER DELETE ON dept FOR EACH ROW
    BEGIN
    	INSERT INTO depts VALUES(NULL,old.deptid,'删除');
    END$$
DELIMITER ;

会员等级总的消费金额到底一定的额度，对应的等级信息

DELIMITER $$
    CREATE TRIGGER tri_03 AFTER INSERT ON sale FOR EACH ROW
    BEGIN
        DECLARE _allmoney INT;
        SELECT SUM(money) INTO _allmoney FROM sale WHERE vipId=new.vipId;
        IF _allmoney>5000 THEN
        	UPDATE vip SET vipLeve=4 WHERE vipId=new.vipId;
        ELSEIF _allmoney>1000 THEN
       		UPDATE vip SET vipLeve=3 WHERE vipId=new.vipId;
        ELSEIF _allmoney>100 THEN
        	UPDATE vip SET vipLeve=2 WHERE vipId=new.vipId;
        END IF;
    END$$
DELIMITER ;

小练习：

部门表中有个部门人数，如何自动维护部门人数

DELIMITER $$
    CREATE TRIGGER tri_demo2 AFTER DELETE ON emp FOR EACH ROW
    BEGIN
    	UPDATE dept SET deptnum=deptnum-1 WHERE deptid=old.deptid;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE TRIGGER tri_demo3 AFTER UPDATE ON emp FOR EACH ROW
    BEGIN
        IF old.deptid IS NOT NULL AND new.deptid IS NOT NULL THEN
        UPDATE dept SET deptnum=deptnum-1 WHERE deptid=old.deptid;
        UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
        ELSEIF new.deptid IS NOT NULL THEN
        	UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
        END IF;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE TRIGGER tri_demo AFTER INSERT ON emp FOR EACH ROW
    BEGIN
    	UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
    END$$
DELIMITER ;

2、流程语句

IF语句

DELIMITER $$
    CREATE TRIGGER tri_04 AFTER INSERT ON sale FOR EACH ROW
    BEGIN
        DECLARE _num INT;
        SELECT goodsNum INTO _num FROM goods WHERE goodsId=new.goodsId;

        IF _num>new.num THEN
       		UPDATE goods SET goodsnum=goodsnum-new.num WHERE goodsid=new.goodsId;
        END IF;
    END$$
DELIMITER ;

循环结构

DELIMITER $$
    CREATE PROCEDURE pro_01(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        WHILE _i<=num DO
            SET _sum=_sum+_i;
            SET _i=_i+1;
        END WHILE;
        SELECT _sum;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE PROCEDURE pro_02(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        a:LOOP
        	IF _i>num THEN
        		LEAVE a;
        	END IF;
        	SET _sum=_sum+_i;
        	SET _i=_i+1;
        END LOOP;
        SELECT _sum;
    END$$
DELIMITER ;

DELIMITER $$
    CREATE PROCEDURE pro_03(num INT)
    BEGIN
        DECLARE _sum INT DEFAULT 0;
        DECLARE _i INT DEFAULT 1;
        REPEAT
            SET _sum=_sum+_i;
            SET _i=_i+1;
        UNTIL _i>num END REPEAT;
        SELECT _sum;
    END$$
DELIMITER ;

3、游标

游标是处理多行数据的，游标需要开启，抓取，关闭的

DELIMITER $$
    CREATE PROCEDURE pro_04()
    BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE _typeid VARCHAR(50);
        DECLARE _typename VARCHAR(50);
        DECLARE _typedes VARCHAR(50);
        DECLARE cur_type CURSOR FOR SELECT * FROM producttype;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
        OPEN cur_type;
            loopname:LOOP
                FETCH cur_type INTO _typeid,_typename,_typedes;
                IF done=1 THEN
                LEAVE loopname;
                END IF;
                INSERT type_bak VALUES(_typeid,_typename,_typedes);
            END LOOP;
        CLOSE cur_type;
    END$$
DELIMITER ;

4、视图

数据库中的视图是一个虚拟表。同真实的表一样，视图包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据。

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。

视图特点:

简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。
逻辑数据独立性:视图可帮助用户屏蔽真实表结构变化带来的影响。

视图主要做查看，不做insert,update,delete。虽然单表创建的视图，可以执行，但不推荐。多表创建视图无法执行以上操作。

创建视图的语法：

1	CREATE VIEW viewname AS SELECT ......

十、索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL 必须从第1条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL 能快速到达某个位置去搜寻数据文件，而不必查看所有数据。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度

索引的含义和特点：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。
MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关； MyISAM和InnoDB 存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引

索引的优点：

（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

（ 2）可以大大加快数据的查询速度，这也是创建索引的主要原因。

（ 3）在实现数据的参考完整性方面，可以加速表和表之间的连接。

（ 4）在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

（ 2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到大文件尺寸。

（ 3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的设计原则：索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。

设计索引时，应该考虑以下准则：

（1）索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

（ 2）避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

（ 3）数据量小的表好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

（ 4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。

（ 5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

（ 6）在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

索引分类：

1．普通索引和唯一索引普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
2．单列索引和组合索引单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循左前缀集合。
3．全文索引全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
4．空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4 种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

索引创建：

创建普通索引，基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

如果表已经存在，创建索引的语法：

1 2	Alter table tabname add [unique\|fulltext\|spatial] index indexname (colname[length])

删除索引的语法：

1	Alter table tabname drop index indexname

创建组合索引：组合索引是在多个字段上创建一个索引。

CREATE TABLE test_db(
    vid INT ,
    vname VARCHAR(50),
    vage INT,
    vinfo VARCHAR(200),
    INDEX mul (vid,vname,vage)
);

由结果可以看到，vid、vname和vage字段上已经成功建立了一个名为 mul的组合索引。组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“左前缀”：利用索引中左边的列集来匹配行,这样的列集称为左前缀。

删除索引：

1	alter table tabname drop index indexname

十一、事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行, 要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成
事务是由一组SQL语句组成的逻辑处理单元，它的ACID特性如下：
（1）原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么都不执行。
（2）一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。
（3）隔离性（Isolation）：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
（4）持久性（Durability）：事务完成后，数据将会被持久化到数据库中。

事务有两状态一个是提交【commit】，一个是回滚【rollback】。

BEGIN ; -- 开启事务 提交 commit或 回滚rollback
UPDATE account SET abalance=abalance-2000 WHERE aid=1;
UPDATE account SET abalance=abalance+2000 WHERE aid=2;
COMMIT;
或
ROLLBACK;

还可以设置打点事务，即再保存点之前的会提交，保存点之后的会回滚

START TRANSACTION ;
UPDATE account SET abalance=abalance-10 WHERE aid=1;
SAVEPOINT A;
UPDATE account SET abalance=abalance-1000 WHERE aid=1;
UPDATE account SET abalance=abalance+1000 WHERE aid=2;
ROLLBACK TO A;

事务隔离级别：一个事务对数据库修改与并行的另一个事务的隔离程度。
两个并发事务同时访问数据库表相同的行时，可能存在以下三个问题：
1、幻想读，2、不可重复读取，3、脏读
为了处理这些问题，SQL标准定义了以下几种事务隔离级别

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

脏读：事务A读取到事务B未提交的数据。
比如账号1的账号金额是5000，此时事务B，将1账号的金额更改为8000，但是还未提交事务，此时事务A,查看1账号的金额，看到8000，然后事务B对操作做了回滚。事务A就读取到脏数据。
如果将事务的隔离级别提高到Read uncommitted，便不会产生以上情况。

不可重复读：事务A查看1账号的金额是2000，此时想再账号上减2000.但是还没操作的时候，事务B,将数据更改了，并再事务A之前提交了事务。此时事务A再次读取该数据的时候，已经改变了。
事务的隔离级别提高到Read committed时，避免了脏读，但是会造成不可重复度。大多数数据库的默认级别就是ReadCommited.可以将事务的隔离级别提高到Repeatable read。也就是事务A读取了数据，再未提交事务的时候，事务B不能对数据进行修改。

幻读：事务A查看第一次查看记录的时候为80。此时，事务B对该记录做了更改，事务A再看的时候，发现不是80，事务A就像出现环境一样。将隔离级别提高到Serializable就可以避免幻读。
查看mysql的事务隔离级别

1	SELECT @@transaction_isolation; [select @@tx_isolation;]

设置mysql的事务隔离级别

1	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

脏读：事务A读取到事务B未提交的数据。脏数据所指的就是未提交的数据设置事务A与事务B的隔离级别为Read uncommitted；

事务A[事务A读取到的8000就是脏数据]	事务B
READ UNCOMMITTED;	READ UNCOMMITTED;
select * from account ;– 5000	select * from account;–5000
	begin;
	update account set balance=8000 where accid=1;
select * from account ;– 8000
	rollback

操作：事务A和事务B都设置事务的隔离级别为READ UNCOMMITTED;

事务B开启事务，并更新记录为8000，此时并未提交

事务A查看数据，看到了8000的结果。

事务B又做了回滚，此时事务A读取到的8000，就属于
不可重复读：一个事务先后读取同一条记录，而事务在两次读取之间该数据被其它事务所修改，则两次读取的数据不同，我们称之为不可重复读。
设置事务A与事务B的隔离级别为Read committed；

事务A	事务B
READ COMMITTED;	READ COMMITTED;
select * from account ;– 5000	select * from account;–5000
	begin;
	update account set balance=balance+3000 where accid=1;
	COMMIT;
begin;
update account set balance=balance+3000 where accid=1;

COMMIT;
select * from account;– 11000??	select * from account;– 8000

幻读:一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。

设置事务A与事务B的隔离级别为REPEATABLE READ；

十二、数据库三范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
在实际开发中为常见的设计范式有三个：

1．第一范式(确保每列保持原子性)

第一范式是基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市” 部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2.第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。