Java全栈（三）数据库技术：1.数据库之MysqlL上

第一章 数据库概述

1.1 数据库概述

• DBMS：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。

• DB：数据库（Database）

• SQL：结构化查询语言，（Structure Query Language），专门用来操作/访问数据库的通用语言。

• 如果把数据库比作盘子，那数据就是盘子里的菜，SQL则是你的筷子。

• 数据库类型：数据库经过几十年的发展，出现了多种类型。目前最常见的数据库模型主要是：关系型数据库和（noSQL）非关系型数据库。

• MySQL：其中的一款关系型数据库管理系统

以下是2020年DB-Engines Ranking 对各数据库受欢迎程度进行调查后的统计结果：（查看数据库最新排名:）

关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，如图所示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

非关系型数据库：

随着互联网web2.0网站的兴起，传统的关系数据库在处理web2.0网站，特别是超大规模和高并发的SNS（ 社交网络服务 ）类型的web2.0纯动态网站已经显得力不从心，出现了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。 例如：redis MongoDB

关系型数据库设计规则

• 遵循ER模型

  • E entity 代表实体的意思 对应到数据库当中的一张表
  • R relationship 代表关系的意思

• 具体体现

  • 将数据放到表中，表再放到库中。
  • 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
  • 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。
  • 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。
  • 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。

1.2 MySQL概述

MySQL数据库最初是由瑞典MySQL AB公司开发，2008年1月16号被Sun公司收购。2009年，SUN又被Oracle收购。MySQL是目前IT行业最流行的开放源代码的数据库管理系统，同时它也是一个支持多线程高并发多用户的关系型数据库管理系统。MySQL之所以受到业界人士的青睐，主要是因为其具有以下几方面优点：

1. 开放源代码

MySQL最强大的优势之一在于它是一个开放源代码的数据库管理系统。开源的特点是给予了用户根据自己需要修改DBMS的自由。MySQL采用了General Public License，这意味着授予用户阅读、修改和优化源代码的权利，这样即使是免费版的MySQL的功能也足够强大，这也是为什么MySQL越来越受欢迎的主要原因。

2. 跨平台

MySQL可以在不同的操作系统下运行，简单地说，MySQL可以支持Windows系统、UNIX系统、Linux系统等多种操作系统平台。这意味着在一个操作系统中实现的应用程序可以很方便地移植到其他的操作系统下。

3. 轻量级

MySQL的核心程序完全采用多线程编程，这些线程都是轻量级的进程，它在灵活地为用户提供服务的同时，又不会占用过多的系统资源。因此MySQL能够更快速、高效的处理数据。

4. 成本低

MySQL分为社区版和企业版，社区版是完全免费的，而企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的Oracle、DB2等也是有很大优势的。其实免费的社区版也支持多种数据类型和正规的SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了，而对数据库可靠性要求比较高的企业可以选择企业版。

1.3 Mysql软件的卸载、安装和配置

不讲解，用到时百度搜索就行

第二章 MySQL的使用

2.1 MySQL服务的启动

“我的电脑/计算机”–>右键–>“管理”–>“服务”–>启动和关闭MySQL服务

“开始菜单”–>“控制面板”–>“管理工具”–>“服务”–>启动和关闭MySQL

“任务管理器”–>“服务”–>启动和关闭MySQL

或者

命令行（使用命令行的前提需要配置mysql的环境变量）

net  start  MySQL服务名
net  stop  MySQL服务名

2.2 客户端连接MySQL

1、命令行客户端：

mysql -h 主机IP地址 -P 端口号	-u 用户名	-p回车
Enter Password:密码

如果访问本机，-h localhost可以省略

如果端口号没有修改，-P 3306可以省略

除了-p与密码之间不要空格外，其他的-h,-P,-u与后面的参数值之间可以有空格

想要连接成功，必须保证服务开启的

2、其他客户端，例如：可视化工具Navicat或SQLyog等

第三章 MySQL数据类型和运算符

3.1 MySQL数据类型

1、数值类型

• 整型系列：xxxInt3

int(M)，必须和unsigned zerofill一起使用才有意义

* 1个字节(byte) = 8比特(bit)，可以表示256个数。一个bit就是一位二进制数
* TINYINT：有符号（-128~127）；无符号（0~255）
* 其他类似
* int(4)字段仍然可以接收超过4位的整数，因为mysql默认使用的是有符号类型。而在有符号类型的字段中，整数类型设置的宽度不生效。
* int(M)，必须和`unsigned zerofill`一起使用才有意义。unsigned zerofill表示无符号字段，不符合要求的地方以0填充，例如：宽度为3，数值为10，则显示为010.当然还是可以接收超过3位的整数

• 浮点型系列：float,double（或real)

double(M,D)：表示最长为M位，其中小数点后D位

例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

• 定点型系列：decimal（底层实际上是使用字符串进行存储）

decimal(M,D)：表示最长为M位，其中小数点后D位
如果decimal没有指定宽度，则只保留整数，且会对小数四舍五入
如果指定了宽度，则按宽度输出，多出的小数也会按照四舍五入

• 位类型：bit

字节范围是：1-8，值范围是：bit(1)~bit(64)，默认bit(1) 将传入的数值转为二进制存入

用来存储二进制数。对于位字段，直接使用select命令将不会看到结果。可以使用bit()函数进行插入。插入bit类型字段时，使用bit()函数转为二进制值再插入，使用bin()函数进行读取。

2、日期时间类型

日期时间类型：year, date, datetime, timestamp

注意一下每一种日期时间的表示范围

timestamp和datetime的区别：

• timestamp范围比较小

• timestamp和时区有关

  • show variables like ‘time_zone’;
  • set time_zone = ‘+9:00’;

• 表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间

案例：

create table test2(time1 datetime,time2 timestamp); # 创建表
insert into test2 values(now(),now()); # 插入数据
select * from test2; // 查询数据
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2021-11-26 02:09:52 | 2021-11-26 02:09:52 |
+---------------------+---------------------+
1 row in set (0.01 sec)

insert into test2 values(null,null);
select * from test2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2021-11-26 02:09:52 | 2021-11-26 02:09:52 |
| NULL                | NULL                |
+---------------------+---------------------+

# 修改时区
set time_zone="+9:00";
select * from test2;
+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2021-11-26 02:09:52 | 2021-11-26 11:09:52 |   # 修改时区后，timestamp日期类型的数据也会自动改变。datetime类型的不会
| NULL                | NULL                |
+---------------------+---------------------+

3、字符串类型

MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。常见的有：

char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等

在mysql中，单引号和双引号都表示字符串

• 字符串类型char,varchar(M)

char如果没有指定宽度，默认为1个字符。定长字符串

varchar(M)，必须指定宽度。可变字符串

• binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。

• 一般在保存少量字符串的时候，我们会选择char和varchar；而在保存较大文本时，通常会选择使用text或blob系列。blob和text值会引起一些性能问题，特别是在执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期时候用optimize table功能对这类表进行碎片整理。可以使用合成的(Synthetic)索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySql提供了前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。

• enum枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对于1~255个成员的枚举需要1个字节存储；对于255`65535个成员需要2个字节存储。例如：gender enum(‘男’,‘女’)。一次只能从枚举值中选择一个。

• set集合类型，可以包含0~64个成员。一次可以从集合中选择多个成员。如果选择了1-8个成员的集合，占1个字节，依次占2个，3个。。8个字节。例如：hoppy set(‘吃饭’,‘睡觉’,‘玩游戏’,‘旅游’)，选择时’吃饭,睡觉’或’睡觉,玩游戏,旅游’

4、示例

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| eid            | int(11)      | NO   | PRI | NULL    | auto_increment |
| ename          | varchar(20)  | NO   |     | NULL    |                |
| tel            | char(11)     | NO   |     | NULL    |                |
| gender         | char(1)      | YES  |     | 男        |                |
| salary         | double       | YES  |     | NULL    |                |
| commission_pct | double(3,2)  | YES  |     | NULL    |                |
| birthday       | date         | YES  |     | NULL    |                |
| hiredate       | date         | YES  |     | NULL    |                |
| job_id         | int(11)      | YES  |     | NULL    |                |
| email          | varchar(32)  | YES  |     | NULL    |                |
| mid            | int(11)      | YES  |     | NULL    |                |
| address        | varchar(150) | YES  |     | NULL    |                |
| native_place   | varchar(10)  | YES  |     | NULL    |                |
| did            | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
# char varchar练习
create table test2(
	name1 char, # 未指定宽度，则只能放一个字符
	name2 char(20), # 指定宽度时，与varchar一样。都可以存放宽度以内的字符串，但是char底层是定长的，即只放2个字符底层仍然会占用20个字符的空间
	name3 varchar(20) # 必须指定宽度，底层是可变的，即存放多少字符就占用多少空间。只要不超过宽度
);
insert into test2 values("男","嘿嘿","哈哈哈");
select * from test2;
+-------+--------+-----------+
| name1 | name2  | name3     |
+-------+--------+-----------+
| 男    | 嘿嘿   | 哈哈哈    |
+-------+--------+-----------+

# enum练习
create table test2(
	sex enum("男","女","不男不女") # 只能在待选项中选择一个值
);
insert into test2 values("不男不女");
select * from test2;
+--------------+
| sex          |
+--------------+
| 不男不女     |
+--------------+
insert into test2 values("公");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

# set练习
create table test2(
	// 1.可以选择集合中的一个或多个待选项
	// 2.也可以使用数字选择待选项，从左到右依次是1，2，4，8，...（如果是5则表示：第一个和第三个待选项）
	habby set("吃饭","睡觉","玩游戏","运动") 
);
insert into test2 values("吃饭");
insert into test2 values("吃饭,睡觉");
select * from test2;
+---------------+
| habby         |
+---------------+
| 吃饭          |
| 吃饭,睡觉     |
+---------------+
insert into test2 values(4); # 选择第三个待选项
insert into test2 values(12); # 选择第三、第四个待选项。（12=4+8）
select * from test2;
+------------------+
| habby            |
+------------------+
| 吃饭             |
| 吃饭,睡觉        |
| 玩游戏           |
| 玩游戏,运动      |
+------------------+

3.2 数据库编码方式

1.查看当前数据库的编码方式

show variables like 'character%';

2.指定编码方式创建数据库

create database 数据库名 CHARSET "utf8";

3.一劳永逸的解决编码问题

1.找到数据库安装目录下的my.ini文件
2.将ini文件中的`default-character-set`设置为`utf8`
3.将ini文件下的`character-set-server`改为`utf8`

第四章 SQL

4.1 MySQL的语法规范和要求

（1）mysql的sql语法不区分大小写

MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

show variables like "coll%"; // 显示mysql的校验规则，查看数据值是否区分大小写
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8_general_ci    |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
/**
\_ci（大小写不敏感），\_cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）
*/

（2）命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号

（3）建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来

（4）数据库和表名、字段名等对象名中间不要包含空格

（5）同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名

（6）标点符号：

必须成对

必须英文状态下半角输入方式

字符串和日期类型可以使用单引号’’

列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略

如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。

（7）SQL脚本中如何加注释

单行注释：#注释内容

单行注释：–空格注释内容 其中–后面的空格必须有

多行注释：/* 注释内容 */

#以下两句是一样的，不区分大小写
show databases;
SHOW DATABASES;

#创建表格
#create table student info(...); #表名错误，因为表名有空格
create table student_info(...); 

#其中name使用``飘号，因为name和系统关键字或系统函数名等预定义标识符重名了。
CREATE TABLE t_stu(
    id INT,
    `name` VARCHAR(20)
);

select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略""
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""

4.2 SQL分类

DDL (Data Definition Language) ：数据定义语言，定义库，表结构等，包括create,drop,alter等

DML (Data Manipulation Language) ：数据操作语言，增删改查数据，包括insert,delete,update,select等

DQL(Data query Language):数据查询语言 select

DCL（Data Control Language） ：数据控制语言，权限，事务等管理。

4.3 DDL(v1.0)

1、查看所有数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.01 sec)

2、指定使用某个数据库

use 数据库名;

mysql> use test1;
Database changed

3、创建数据库

create database 数据库名;

create database 数据库名 charset 'utf8'; #在mysql中字符集名称不要使用utf-8

create database 数据库名 charset 'gbk';

create database test2;
Query OK, 1 row affected (0.02 sec)

4、删除数据库

drop database 数据库名;

mysql> drop database test2;
Query OK, 0 rows affected (0.02 sec)

5、查看某个库下的所有表格

show tables ;  #前提是前面有use 数据库名;的语句
show tables from 数据库名;

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1           |
| test2           |
+-----------------+
2 rows in set (0.01 sec)

6、创建表格

create table 【数据名.】表名(
	字段名1 数据类型,
	字段名2 数据类型,
	....
);

create table stu(
	id int,
	name varchar(10)
);

7、删除表格

drop table 【数据库名.]表名称;

mysql> drop table test2;
Query OK, 0 rows affected (0.04 sec)

8、查看某个表结构

describe 【数据库名.]表名称;
desc 【数据库名.]表名称;

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

9、增加一列

alter table 【数据库名.]表名称 add 【column】 字段名 数据类型; 
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

alter table stu add school char(10); # 默认增加到末尾列
alter table stu add sex enum("男","女") first; # 增加到第一列
alter table stu add age int after id; # 增加到id字段后面

mysql> desc stu;
+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| sex    | enum('男','女')   | YES  |     | NULL    |       |
| id     | int               | YES  |     | NULL    |       |
| age    | int               | YES  |     | NULL    |       |
| name   | varchar(10)       | YES  |     | NULL    |       |
| school | char(10)          | YES  |     | NULL    |       |
+--------+-------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

10、删除一列

alter table 【数据库名.]表名称 drop 【column】 字段名;

alter table stu drop school; # 删除school列字段
mysql> desc stu;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sex   | enum('男','女')   | YES  |     | NULL    |       |
| id    | int               | YES  |     | NULL    |       |
| age   | int               | YES  |     | NULL    |       |
| name  | varchar(10)       | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

11、修改列数据类型

alter table 【数据库名.]表名称 modify 【column】 字段名 新数据类型;

 alter table stu modify name char(6); # 修改name字段数据类型
 mysql> desc stu;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sex   | enum('男','女')   | YES  |     | NULL    |       |
| id    | int               | YES  |     | NULL    |       |
| age   | int               | YES  |     | NULL    |       |
| name  | char(6)           | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

12、修改列名

alter table 【数据库名.]表名称 change【column】 旧字段名 新字段名 新数据类型;

alter table stu change name sname varchar(20); # 将字段name修改为sname，且数据类型也修改
mysql> desc stu;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sex   | enum('男','女')   | YES  |     | NULL    |       |
| id    | int               | YES  |     | NULL    |       |
| age   | int               | YES  |     | NULL    |       |
| sname | varchar(20)       | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

13、修改列的位置

alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 first;
alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 after 另一个字段;

14、修改表名称

alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;

4.4 DML(v1.0)

1、添加数据

insert into 【数据库名.]表名称 values(值列表)； 
#要求值列表的顺序、个数、类型，要与表格中的字段的顺序、个数、类型一一匹配

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；
#要求列表的顺序、个数、类型，要与前面的(部分字段列表)的顺序、个数、类型一一匹配

insert into 【数据库名.]表名称 values(值列表1)，(值列表2)。。。;

insert into 【数据库名.]表名称(部分字段列表) values(值列表1)，(值列表2)。。。;

insert into [数据库名.]表名 set 字段名=值, 字段名=值......;

mysql> desc stu;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sex   | enum('男','女')   | YES  |     | NULL    |       |
| id    | int               | YES  |     | NULL    |       |
| age   | int               | YES  |     | NULL    |       |
| sname | varchar(20)       | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

# 给一条记录的所有字段赋值
insert into stu values("男",1,28,"张三");
mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
+------+------+------+--------+
1 row in set (0.01 sec)

# 给一条记录的部分字段赋值
insert into stu(sex,id,sname) values("男",2,"李四");
mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
| 男   |    2 | NULL | 李四   |
+------+------+------+--------+
2 rows in set (0.01 sec)

# 给多条记录赋值
insert into stu values("男",3,27,"王五"),("女",4,22,"小红");
insert into stu(sex,id,sname) values("男",5,"小明"),("女",6,"小花");
mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
| 男   |    2 | NULL | 李四   |
| 男   |    3 |   27 | 王五   |
| 女   |    4 |   22 | 小红   |
| 男   |    5 | NULL | 小明   |
| 女   |    6 | NULL | 小花   |
+------+------+------+--------+
6 rows in set (0.01 sec)

2、修改数据

update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】;

如果没有加where条件，表示修改所有记录中，这个字段的值

mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
| 男   |    2 | NULL | 李四   |
| 男   |    3 |   27 | 王五   |
| 女   |    4 |   22 | 小红   |
| 男   |    5 | NULL | 小明   |
| 女   |    6 | NULL | 小花   |
+------+------+------+--------+
6 rows in set (0.01 sec)

update stu set sname="小丽",age=20 where(id=6); # 将id为6的记录中，字段age值改为20，字段sname值改为小丽
mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
| 男   |    2 | NULL | 李四   |
| 男   |    3 |   27 | 王五   |
| 女   |    4 |   22 | 小红   |
| 男   |    5 | NULL | 小明   |
| 女   |    6 |   20 | 小丽   |
+------+------+------+--------+

3、删除数据

delete from 【数据库名.]表名称 【where 条件】;

如果没有where条件，表示删除整张表的数据;

truncate 【数据库名.]表名称;#删除整张表的数据，还可以使用这个语句，效率更高，但是它不能回滚

用delete删除整张表和用truncate删除整张表的数据的区别？

（1）truncate速度快

（2）truncate无法回滚

truncate因为底层是把表drop掉，然后新建了一张空表。

delete因为底层是一行一行删数据。

delete from stu where(id=5); # 删除id为5的记录数据
mysql> select * from stu;
+------+------+------+--------+
| sex  | id   | age  | sname  |
+------+------+------+--------+
| 男   |    1 |   28 | 张三   |
| 男   |    2 | NULL | 李四   |
| 男   |    3 |   27 | 王五   |
| 女   |    4 |   22 | 小红   |
| 女   |    6 |   20 | 小丽   |
+------+------+------+--------+
5 rows in set (0.01 sec)

4、简单查询数据

select * from 【数据库名.]表名称; #查询整张表的所有数据

select 字段列表  from 【数据库名.]表名称;  #查询部分列表

select * from 【数据库名.]表名称 【where 条件】;

select 字段列表  from 【数据库名.]表名称 【where 条件】;

使用distinct可以对查询结果进行去重

4.5 导入和导出数据

4.5.1 单个数据库备份

mysql5.5

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码 --database  数据名 > 文件路径/文件名.sql

例如：

C:\Windows\System32>mysqldump -hlocalhost -P3306 -uroot -p123456 --database  test > d:/test.sql

mysql5.7版

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p 数据库名 > 文件路径/文件名.sql

本机操作可以省略主机名和端口号

C:\Users\Administrator>mysqldump -uroot -p book >d:/dump.sql
Enter password: ****

不要再写–database

4.5.2 导入执行备份的sql脚本

先登录mysql，然后执行如下命令：

mysql> use 数据库名
mysql> source  sql脚本路径名.sql # 将数据导入到当前的数据库中

例如：

mysql>source d:/test.sql;

4.6 MySQL运算符

1、算术运算符

加：+
减：-
乘：*
除：/   div（只保留整数部分）
模：%   mod

2、比较运算符

大于：>
小于：<
大于等于：>=
小于等于：<=
等于：=   不能用于null判断
不等于：!=  或 <>
安全等于：<=>  可以用于null值判断

3、逻辑运算符（建议用单词，可读性来说）

逻辑与：&& 或 and
逻辑或：|| 或 or
逻辑非：! 或 not
逻辑异或： xor(前后条件相同为false，不同为true)

4、范围

区间范围：between x  and  y
	    not between x  and  y
集合范围：in (x,x,x)
	    not  in(x,x,x)

5、模糊查询和正则匹配（只针对字符串类型，日期类型）

like 'xxx'
如果想要表示任意的0~n个字符，用%
如果想要表示任意的1个字符，用_

6、位运算符（很少使用）

左移：<<
右移：>>
按位与：&
按位或：|
按位异或：^
按位取反：~

7、特殊的null值处理

#（1）判断时
xx is null
xx is not null
xx <=> null

/*一、运算符
1、算术运算符
+：加
-：减
*：乘
/：除 可以保留小数部分
div：除  如果整数与整数相除只保留整数部分
%：求余数
mod：求余数
*/
select 1+1;
select 1/2; #0.5
select 1 div 2; #0

/*
2、比较运算符
>：大于
<：小于
=：等于  注意区别，Java中是==,mysql中是=
>=：大于等于
<=：小于等于
!=：不等于
<>：不等于
<=>：安全等于  用于判断null值的比较运算符
		null值的判断，习惯上我们用is null 和is not null 。mysql中不能使用=来比较null值
*/
#查询薪资大于20000元的员工
select * from t_employee where salary > 20000;

#查询所有男员工
select * from t_employee where gender = '男';
select * from t_employee where gender != '女';
select * from t_employee where gender <> '女';

#查询奖金比例commision_pct是null的员工
select  * from t_employee where commission_pct <=> null;
select  * from t_employee where commission_pct is null;

/*
3、逻辑运算符
&&和and：逻辑与
	两个条件同时满足
||和or：逻辑或
	两个条件满足任意一个
^和xor：逻辑异或
	两个条件相同为false，不同为true
!和not：
	不满足xx条件
	*/
#查询薪资大于20000元的女员工	
select * from t_employee where salary > 20000 && gender = '女';
select * from t_employee where salary > 20000 and gender = '女';

#查询男员工
select * from t_employee where not gender = '女'; # 注意：not需要放在被判断的字段前面
select * from t_employee where !(gender = '女');

#查询薪资大于10000  异或 性别是男的，即它俩只能满足一个
#即查询薪资大于10000的女的或薪资低于10000的男的
select * from t_employee where salary>10000 ^ gender ='男';
select * from t_employee where salary>10000 xor gender ='男';
/*
4、范围
（1）区间范围：
	在[a,b]之间，between a and b
	不在[a,b]之间，not 字段名 between a and b
（2）集合范围
	in(...)
	not 字段名 in(...)
*/
#查询薪资在[15000,20000]之间的员工
select * from t_employee where salary between 15000 and 20000;
select * from t_employee where salary >= 15000 and salary <=20000;

#查询薪资在9000,10000,12000
select * from t_employee where salary in(9000,10000,12000);
select * from t_employee where salary =9000 || salary =10000 || salary =12000;
 /*
5、模糊查询
like '%x%' x代表确定的字符 %表示不确定的0~n个字符
     '_x%'  x代表确定的字符 _表示不确定的1个字符
   
*/
#查询，名字ename中包含“冰”这个字的员工
select * from t_employee where ename like '%冰%';

#查询，名字ename是张xx，三个字
select * from t_employee where ename like '张__';

#查询，名字ename是第二个字是'冰'
select * from t_employee where ename like '_冰%';

第五章 约束与索引

5.1 约束与索引的概念

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式，是逻辑约束，不会因为设置约束而额外占用空间。

1、数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2、根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束
• Not NULL约束：非空约束
• Check约束：检查约束(5.7版本不支持)
• Default约束：默认值约束
• 自增约束

3、约束（CONSTRAINTS）与索引（INDEX）

索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为插入和修改数据时，索引也要随之变动），但是可以大大提高查询速度。因此应该在键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

其中主键删除，对应的索引也会删除

删除唯一键的方式是通过删除对应的索引来实现的

删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引

5.2 DDL(v2.0)

1、查看某个表的约束

# mysql中有一个系统数据库information_schema，该数据库中有一个表table_constraints存放了所有表的约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
或
SHOW CREATE TABLE 表名;

2、查看某个表的索引

SHOW INDEX FROM 表名称;

show index from stu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| stu   |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| stu   |          0 | PRIMARY  |            2 | sname       | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

3、主键约束：primary key

主键分为单列主键和复合主键：

主键的特点：（1）唯一并且非空（2）一个表只能有一个主键约束（3）主键约束名就叫做PRIMARY（4）创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除。（5）主键约束可以用来标识该条记录与其他记录的不同

（1）如何在建表时指定主键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	....
);
或
create table 【数据名.】表名(
	字段名1 数据类型,
	....,
    primary key(字段名1)
);
或
create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表)#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);

# 创建表时指定主键
# 方式1
create table stu(
	id int primary key, # 声明字段时，同时声明主键
	sname varchar(10)
);
# 方式2
create table stu(
	id int,
	sname varchar(10),
	primary key(id) # 字段声明完成后，再声明主键
);
/*
create table stu(
	id int,
	sname varchar(10),
	# 如果将多个字段同时作为主键，则只能使用这种声明方式
	# 多个字段同时为主键，则这些字段都不能为空
	# 多个字段同时为主键，只要不是所有字段都重复，则也可以添加成功。因为主键是将所有字段的值拼接起来后唯一。
	primary key(id,sname) 
);
*/
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 插入数据
insert into stu values(1,"张三"); # 插入数据成功
insert into stu values(1,"李四"); # 报错，id作为主键，是唯一的。不能重复
insert into stu values(null,"李四"); # 报错，id作为主键不能为空

（2）如何在建表后指定主键约束

注意：建表后创建任何约束，如果表中存在不满足该约束的数据，添加都会失败

alter table 表名称 add primary key (主键字段列表);

create table stu(id int,sname varchar(10)); # 创建表时未指定主键
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

alter table stu add primary key(id); # 表创建完成后添加主键
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

（3）如何删除主键约束

alter table 表名称 drop primary key;

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
alter table stu drop primary key; # 删除主键时，索引会自动删除，但非空约束没有删除
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| sname | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4、唯一键约束：unique key

（1）唯一键约束特点

• 唯一键约束列允许为null。
• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
• MySQL会给唯一约束的列上默认创建一个唯一索引。
• 删除唯一键只能通过删除对应索引的方式删除，删除时需要指定唯一键索引名

（2）如何在建表时指定唯一键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 unique key,
	....
);

create table 【数据名.】表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型,
    字段名3 数据类型,
	....,
    unique key(复合唯一字段列表)#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
);

create table stu(
	id int primary key,
	sname char(10) not null,
	email char(20) unique key # 在声明字段时同时声明唯一约束
);
create table stu(
	id int primary key,
	sname char(10) not null,
	email char(20),
	unique key(email) # 在字段声明后再声明唯一约束
);
/*
create table stu(
	id int primary key,
	sname char(10) not null,
	email char(20),
	# 如果要给多个字段声明同一个唯一约束，在建表时只能使用这种方式
	# 多个字段组合的唯一约束，会将多个字段的值组合起来作为判断条件。也就是这些字段只要不同时重复，就满足条件
	unique key(email,sname)
);
*/
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
| email | char(20) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+

# 插入数据
insert into stu values(1,"张三","zs@qq.com"); // 数据插入成功
insert into stu values(2,"李四","zs@qq.com"); // 数据插入失败，因为email字段不能重复

（3）如何在建表后增加唯一键约束

alter table 表名称 add 【constraint 约束名】 unique 【key】 (字段名列表);
#如果没有指定约束名，(字段名列表)中只有一个字段的，默认是该字段名，如果是多个字段的默认是字段名列表的第1个字段名。可以通过show index from 表名来查看。

create table stu(
	id int primary key,
	sname char(10) not null,
	email char(20)
);
alter table stu add unique key(email,sname);

（4）如何删除唯一键约束

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过“show index from 表名称;”查看

mysql> show index from stu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| stu   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| stu   |          0 | email    |            1 | email       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| stu   |          0 | email    |            2 | sname       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
# 从上面可以得出，唯一约束对应的索引名为`email`
alter table stu drop index email; # 删除唯一约束对应的索引，唯一约束也就删除了
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
| email | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

5、非空约束：not null

• NOT NULL 非空约束，规定某个字段不能为空
• mysql中没有给非空约束设置key，所以只能在字段后面加上not null来设置非空约束

（1）如何在建表时给某个字段指定非空约束

create table 【数据名.】表名(
	字段名1 数据类型  
	# 非空约束建表时只能跟字段同时声明
	字段名2 数据类型 not null  
	....
);

create table stu(
	id int primary key,
	sname char(10) not null # 建表时非空约束只有这一种声明方式
);
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

# 插入数据
insert into stu values(1,"张三"); # 数据插入成功
insert into stu values(2,null); # 数据插入失败，非空约束的字段不能为null

（2）如何在建表后指定某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

create table stu(id int primary key,sname char(10));
alter table stu modify sname char(10) not null;
mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

（3）如何在建表后取消某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

6、默认值约束：default

• 被默认值约束的字段，如果不给它赋值，会使用默认值
• mysql中没有给默认值约束设置key，所以只能在字段后面加上default xxx来设置默认值约束
• 如果给默认值约束的字段赋值null，也不会触发默认值约束。因为mysql会认为null就是你要赋的值

（1）如何在建表时给某个字段指定默认约束

create table 【数据名.】表名(
	字段名1 数据类型 ,
	字段名2 数据类型【default 默认值】,
	....
);

create table stu(
	id int primary key,
	sname char(10) not null,
	school char(20) default "清华大学"
);
mysql> desc stu;
+--------+----------+------+-----+--------------+-------+
| Field  | Type     | Null | Key | Default      | Extra |
+--------+----------+------+-----+--------------+-------+
| id     | int      | NO   | PRI | NULL         |       |
| sname  | char(10) | NO   |     | NULL         |       |
| school | char(20) | YES  |     | 清华大学     |       |
+--------+----------+------+-----+--------------+-------+

# 插入数据
insert into stu values(1,"张三",null); # null不会触发默认值约束
insert into stu(id,sname) values(2,"李四"); # 未给school赋值，触发默认值约束
mysql> select * from stu;
+----+--------+--------------+
| id | sname  | school       |
+----+--------+--------------+
|  1 | 张三   | NULL         |
|  2 | 李四   | 清华大学     |
+----+--------+--------------+

（2）如何在建表后指定某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型  【default 默认值】 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

create table stu(
	id int primary key,
	sname char(10) not null,
	school char(20)
);
alter table stu modify school char(20) default "清华大学";

（3）如何在建表后取消某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

alter table stu modify school char(20);

7、检查约束：check

检查约束，mysql暂不支持

create table stu(
	sid int primary key,
	sname varchar(20),
	gender char check ('男'or'女')
);
insert into stu values(1,'张三','男');
insert into stu values(2,'李四','妖');

使用枚举类型解决如上问题：
create table stu(
	sid int primary key,
	sname varchar(20),
	gender enum ('男','女')
);

8、自增约束：auto_increment

（1）关于自增长auto_increment：

• 一个表最多只能有一个自增长列
• 自增长列必须是键列（主键列，唯一键列，外键列），并且要求非空。
• 自增列必须是整数类型
• InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。
• 一般我们把id字段设置为主键，自增约束
• mysql中也没有自增约束的key，只能在字段后面加上auto_increment来设置自增约束

（2）如何在建表时指定自增长列

create table 【数据名.】表名(
	字段名1 数据类型  primary key auto_increment,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....
);

或
create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key  not null】 auto_increment,
	....
);

create table stu(
	id int primary key auto_increment,
	sname char(10) not null
);
mysql> desc stu;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| sname | char(10) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

# 插入数据
insert into stu(sname) values("李白"),("杜甫");
insert into stu(id,sname) values(null,"王安石"); # 传入null值或0，也会触发自增约束
insert into stu(id,sname) values(100,"李清照");
insert into stu(sname) values("李商隐"); # 会从末尾的值开始自增，无论是否连续
mysql> select * from stu;
+-----+-----------+
| id  | sname     |
+-----+-----------+
|   1 | 李白      |
|   2 | 杜甫      |
|   3 | 王安石    |
| 100 | 李清照    |
| 101 | 李商隐    |
+-----+-----------+

（3）如何在建表后指定自增长列

alter table 【数据名.】表名 modify 自增字段名 数据类型 auto_increment;

（4）如何删除自增约束

alter table 【数据名.】表名 modify 自增字段名 数据类型;

9、外键约束：foreign key

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而***被依赖的字段必须具有主键约束或者唯一约束***。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

（1）外键特点

• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。

• 删除外键时，关于外键列上的普通索引需要单独删除。

（2）要求

• 在从表上建立外键，而且主表要先存在。

• 一个表可以建立多个外键约束

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键），推荐引用主表的主键。

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

（3）约束关系：约束是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束

• 添加了外键约束后，从表的添加和修改数据受约束

• 在从表上建立外键，要求主表必须存在

• 删除主表时，要求从表先删除，或将从表中外键引用该主表的关系先删除

（4）外键约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete子表的匹配记录

• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• Restrict方式：同no action, 都是立即检查外键约束

• 如果没有指定等级，就相当于Restrict方式

（5）如何在建表时指定外键约束

create table 【数据名.】从表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】,
	....,
    【constraint 外键约束名】 foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级】
    #外键只能在所有字段列表后面单独指定
    #如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk
);

create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级】
    #外键只能在所有字段列表后面单独指定
);

# 创建主表,班级表
create table class(
	id int primary key,
	cname char(20) not null unique key
);
# 创建从表学生表，设置外键约束
create table stu(
	id int primary key,
	sname char(10) not null,
	c_id int,
	# foreign key(c_id) references class(id) on update restrict on delete restrict 
	# 外键等级为restrict 时可以省略不写
	foreign key(c_id) references class(id)
);
desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
| c_id  | int      | YES  | MUL | NULL    |       |  # MUL表示外键约束key
+-------+----------+------+-----+---------+-------+

# 插入主表数据
insert into class values(1,"JAVA班"),(2,"大前端班"),(3,"大数据班");
select * from class;
+----+--------------+
| id | cname        |
+----+--------------+
|  1 | JAVA班       |
|  2 | 大前端班     |
|  3 | 大数据班     |
+----+--------------+
# 插入从表数据
insert into stu values(1,"张三",1),(2,"李四",1),(3,"王五",3),(4,"赵六",2);
insert into stu values(5,"张三",4); # 数据插入失败，因为主表的id中没有4这个数据
select * from stu;
+----+--------+------+
| id | sname  | c_id |
+----+--------+------+
|  1 | 张三   |    1 |
|  2 | 李四   |    1 |
|  3 | 王五   |    3 |
|  4 | 赵六   |    2 |
+----+--------+------+

（6）如何在建表后指定外键约束

alter table 从表名称 add 【constraint 外键约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名) 【on update xx】[on delete xx];

alter table stu add foreign key(c_id) references class(id);

（7）如何删除外键约束

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
#查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;
#查看索引名 show index from 表名称;

# 找到外键约束名
select * from information_schema.table_constraints WHERE table_name = "stu";
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | test2             | PRIMARY         | test2        | stu        | PRIMARY KEY     | YES      |
| def                | test2             | stu_ibfk_1      | test2        | stu        | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ # stu_ibfk_1就是外键约束名
# 删除外键约束
alter table stu drop foreign key stu_ibfk_1;
# 找到外键约束的索引名
show index from stu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| stu   |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| stu   |          1 | c_id     |            1 | c_id        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ # c_id就是外键约束对应的索引名
# 删除外键约束的索引
alter table stu drop index c_id;
 desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| sname | char(10) | NO   |     | NULL    |       |
| c_id  | int      | YES  |     | NULL    |       | # 外键约束已经没了
+-------+----------+------+-----+---------+-------+

5.3 DML(v2.0)

1、如果某列有自增约束，怎么添加该字段的值

添加数据时，对于自增列

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应自增列可以赋值为null和0

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#自增列在(部分字段列表)中不写就可以

2、如果某列有默认值约束，怎么添加、修改该字段的值

添加数据时，对于有默认值列

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应默认值列，如果想用默认值，用default

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#对应默认值列，如果想用默认值，在(部分字段列表)中不写就可以

修改数据

update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】; #对应默认值列，如果想用默认值，写字段名 = default就可以

第六章 函数

两种SQL函数

6.1单行函数

• 只对一行进行变换，每行返回一个结果
• 可以嵌套
• 参数可以是一字段或一个表达式或一个值

6.1.1 字符串函数

函数	用法
CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串
CONCAT_WS(s, S1,S2,…,Sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr，下标从1开始
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

• 举例1：大小写控制函数

函数	结果
LOWER(‘SQL Course’)	sql course
UPPER(‘SQL Course’)	SQL COURSE

这类函数改变字符的大小写。

• 举例2：字符控制函数

函数	结果
CONCAT(‘Hello’,‘World’)	HelloWorld
SUBSTR(‘HelloWorld’,1,5)	Hello
LENGTH(‘HelloWorld’)	10
INSTR(‘HelloWorld’,‘W’)	6
LPAD(salary,10,’*’)	*****24000
RPAD(salary,10, ‘*’)	24000*****
TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
REPLACE(‘abcd’,‘b’,‘m’)	amcd

# 字符串拼接
select concat("hello","world"); # 将"hello","world"两个字符串拼接起来
+-------------------------+
| concat("hello","world") |
+-------------------------+
| helloworld              |
+-------------------------+
select concat_ws("-","hello","world");# 将"hello","world"两个字符串拼接起来，且中间用`-`连接
+--------------------------------+
| concat_ws("-","hello","world") |
+--------------------------------+
| hello-world                    |
+--------------------------------+
# 字符串长度
select char_length("helloworld");
+---------------------------+
| char_length("helloworld") |
+---------------------------+
|                        10 |
+---------------------------+
select length("helloworld");
+----------------------+
| length("helloworld") |
+----------------------+
|                   10 |
+----------------------+
# 字符串替换内容
select insert("hello",2,3,"ss"); # 从下标2开始的3个字符替换为`ss`；mysql中下标从1开始
+--------------------------+
| insert("hello",2,3,"ss") |
+--------------------------+
| hsso                     |
+--------------------------+
select replace("hello","l","k"); # 将字符串中所有的`l`，替换为`k`
+--------------------------+
| replace("hello","l","k") |
+--------------------------+
| hekko                    |
+--------------------------+
# 大小写转换
select upper("hello"); # 转换为大写字母
+----------------+
| upper("hello") |
+----------------+
| HELLO          |
+----------------+
select lower("heLLO"); # 转换为小写字母
+----------------+
| lower("heLLO") |
+----------------+
| hello          |
+----------------+
# 字符串截取
select left("hello",2); # 返回左边的两个字符
+-----------------+
| left("hello",2) |
+-----------------+
| he              |
+-----------------+
select right("hello",2); # 返回右边的两个字符
+------------------+
| right("hello",2) |
+------------------+
| lo               |
+------------------+
select substring("hello",2,3); # 返回下标2开始的3个字符
+------------------------+
| substring("hello",2,3) |
+------------------------+
| ell                    |
+------------------------+
# 字符串填充
select LPAD("hello",10,"x"); # 使用`x`对字符串左边进行填充，直到长度为10
+----------------------+
| LPAD("hello",10,"x") |
+----------------------+
| xxxxxhello           |
+----------------------+
select RPAD("hello",10,"x"); # 使用`x`对字符串右边进行填充，直到长度为10
+----------------------+
| RPAD("hello",10,"x") |
+----------------------+
| helloxxxxx           |
+----------------------+
# 去掉空格
select ltrim("  hello   "); # 去掉字符串左侧的空格
+---------------------+
| ltrim("  hello   ") |
+---------------------+
| hello               |
+---------------------+
select rtrim("  hello   "); # 去掉字符串右侧的空格
+---------------------+
| rtrim("  hello   ") |
+---------------------+
|   hello             |
+---------------------+
select trim("  hello   "); # 去掉左右两侧的空格
+--------------------+
| trim("  hello   ") |
+--------------------+
| hello              |
+--------------------+
# 去掉左右指定的字符内容
select trim(leading "qaz" from "qazhelloqaz"); # 去掉"qazhelloqaz"左侧的自定义内容"qaz"
+----------------------------------------+
| trim(leading "qaz" from "qazhelloqaz") |
+----------------------------------------+
| helloqaz                               |
+----------------------------------------+
select trim(trailing "qaz" from "qazhelloqaz"); # 去掉"qazhelloqaz"右侧的自定义内容"qaz"
+-----------------------------------------+
| trim(trailing "qaz" from "qazhelloqaz") |
+-----------------------------------------+
| qazhello                                |
+-----------------------------------------+
select trim(both "qaz" from "qazhelloqaz"); # 去掉"qazhelloqaz"左右两侧的自定义内容"qaz"
+-------------------------------------+
| trim(both "qaz" from "qazhelloqaz") |
+-------------------------------------+
| hello                               |
+-------------------------------------+

6.1.2 数值函数/数学函数

函数	用法
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值 ，向上取整
FLOOR(x)	返回小于x的最大整数值，向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果，不会四舍五入
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

• 举例1：ROUND:四舍五入

ROUND(45.926, 2)     -->  45.93

• 举例2：TRUNCATE:截断

TRUNCATE(45.926)      --> 45

• 举例3：MOD:求余

MOD(1600, 300)	 --> 100

6.1.3 日期函数

函数	用法
CURDATE() 或 CURRENT_DATE q()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY…SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

其中：
（1） 当前时间

select NOW(),SYSDATE(),LOCALTIME();
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | LOCALTIME()         |
+---------------------+---------------------+---------------------+
| 2021-11-26 15:31:16 | 2021-11-26 15:31:16 | 2021-11-26 15:31:16 |
+---------------------+---------------------+---------------------+

（2）返回具体日期（年月日时分秒星期）

select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now()),week(now());
+-------------+--------------+------------+-------------+---------------+---------------+-------------+
| year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) | week(now()) |
+-------------+--------------+------------+-------------+---------------+---------------+-------------+
|        2021 |           11 |         26 |          15 |            36 |            22 |          47 |
+-------------+--------------+------------+-------------+---------------+---------------+-------------+

（2）DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

参数类型	参数类型
YEAR	YEAR_MONTH
MONTH	DAY_HOUR
DAY	DAY_MINUTE
HOUR	DAY_SECOND
MINUTE	HOUR_MINUTE
SECOND	HOUR_SECOND
	MINUTE_SECOND

举例：

select now(); # 2021-11-26 15:29:08
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); # 2022-11-26 15:27:58
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   # 2020-11-26 15:28:19
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号 2022-12-26 15:28:37

（3）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h和%I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

select date_format(now(),'%Y %m %D %H %i %s'); # 将日期转换为字符串
+----------------------------------------+
| date_format(now(),'%Y %m %D %H %i %s') |
+----------------------------------------+
| 2021 11 26th 15 37 29                  |
+----------------------------------------+

select str_to_date('2020-9-9', "%Y-%m-%D"); # 将字符串转换为日期
+-------------------------------------+
| str_to_date('2020-9-9', "%Y-%m-%D") |
+-------------------------------------+
| 2020-09-09                          |
+-------------------------------------+

6.1.4 流程函数

函数	用法
IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE expr WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

• 举例1：

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪" FROM employees;

• 举例2：

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
                   ELSE salary END  AS   "实发工资"
FROM   employees;

• 举例3：

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END   AS   "描述"
FROM employees; 

6.1.5 其他函数

函数	用法
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名
password(str)	返回字符串str的加密版本，41位长的字符串
md5(str)	返回字符串str的md5值，也是一种加密方式

6.2多行（分组）函数

• 什么是分组函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数。

• 组函数类型
  • AVG()：求平均值
  • SUM()：求和
  • MAX()：求最大值
  • MIN()：求最小值
  • COUNT() ：求记录总数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

COUNT(*)/COUNT(1)：返回表中记录总数,适用于任意数据类型。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

•COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

• 问题：用count(*)，count(1)谁好呢?

  其实，对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

  Innodb引擎的表用count(*)直接读行数，效率很低，因为innodb真的要去数一遍。

• 多行函数不会统计null值

• 具体深层次的讲解，等到查询sql语言学习后讲解

扩展：函数嵌套

# 单行函数中嵌套多行函数
select abs(avg(salary)) from employee; #求工资平均值的绝对值
# 多行函数中嵌套单行函数
select avg(pow(sqlary,2)) from employee; # 求工资平方的绝对值
# 多行函数中不能嵌套多行函数
select count(avg(salary)) from employee; # 会报错