MySQL数据库 学习笔记 零基础入门 面试 整理

一、MySQL基础篇

1. 数据库技术的基本概念和方法

1.1 数据库基本概念

1】 数据

数据（Data）指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的，不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。

2】 数据库

数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。

3】 数据库管理系统

数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件，如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

4】 数据库应用程序

数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

5】 数据库管理员

数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

6】 最终用户

最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

7】 数据库系统

数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中，DBMS是数据库系统的基础和核心。

1.2. 数据库系统的结构

从数据库用户视图的视角来看，数据库系统通常采用三级模式结构，这是数据库管理系统内部的系统结构。
从数据库管理系统的角度，数据库系统的结构分为集中式结构、分布式结构、客户/服务器结构和并行结构，这是数据库系统的外部体系结构。
从数据库系统应用的角度，常见有客户/服务器结构和浏览器/服务器结构，这是数据库系统整体运行结构。

1. 客户/服务器结构
   数据库系统中，数据库的使用者可以使用命令行客户端、图形化界面管理工具或应用程序等来连接数据库管理系统，并可通过数据库管理系统查询和处理存储在底层数据库中的各种数据。即客户/服务器 （client/server，C/S）结构。

2. 浏览器/服务器结构
   浏览器/服务器（Brower/Server，B/S）结构是一种基于web应用的客户/服务器结构，也称为三层客户/服务器结构。数据库系统中，他将与数据库管理系统交互的客户端进一步细分为 表示层 和 处理层。

1.3. 数据模型

数据库中的数据是有一定结构的，这种结构用数据模型（Data Model）表示。根据不同的应用目的，数据模型可分为概念模型、逻辑模型和物理模型。

1. 概念模型
   conceptual model 用来描述现实世界的事物，与具体的计算机系统无关。现实世界是存在于人脑之外的客观世界。最典型的概念模型是实体联系（Entity-Relationship，E-R）模型。
   两个实体集之间的联系有一对一（1:1）、一对多（1：N）、多对多（M：N）三种类型。
   通常使用E-R图来描述现实世界的概念模型，即描述实体、实体的属性和实体间的联系。

2. 逻辑模型
   logical model是具体的DBMS所支持的数据模型。任何DBMS都基于某种逻辑数据模型。主要的逻辑数据模型有层次模型、网状模型、关系模型、面向对象模型等。

3. 物理模型
   物理模型用于描述数据在存储介质上的组织结构。每一种逻辑模型在实现时都有与之对应的物理数据模型。

1.4. 数据库类型和常见的关系型数据库

数据库类型根据数据的组织结构不同，主要分为网状数据库、层次数据库、关系型数据库和非关系型数据库四种。目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

1】 关系型数据库

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

1） 表（Table）也称为关系，由表名、构成表的各个列及若干行数据组成。每个表有一个唯一的表名，表中每行数据描述一个学生的基本信息。表的结构称为关系模式。

2） 列（Field）也称为字段或属性。表中每一列有一个名称，称为字段名、属性名或列名。每一列表示实体的一个属性，具有相同的数据类型。

3） 行（Row）也称作元组（Tuple）或记录。表中一行即为一个元组，每行由若干字段值组成，每个字段值描述该对象的一个属性或特征。

4） 关键字（Key）是表中能够唯一确定一个元组的属性或属性组。关键字也称作码或主键。有些情况下，需要几个属性（即属性集合）才能唯一确定一条记录。

5） 候选键，若一个表中有多个能唯一标识一个元组的属性，则这些属性称为候选键。候选键中任选一个可作为主键。

6） 外部关键字（Foreign Key）也称作外键。若表的一个字段不是本表的主键或候选键，而是另外一个表的主键或候选键，则该字段称为外键。

7） 域（Domain）表示属性的取值范围。

8） 数据类型，表中每个列都有相应的数据类型，它限制该列中存储的数据。每个字段表示同一类信息，具有相同的数据类型。

2】 非关系型数据库

NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB、redis、HBase等。

3】 常见的关系型数据库

虽然非关系型数据库优点很多，但由于其并不提供SQL支持，学习和使用成本较高且无事务处理。
常用的关系型数据库管理系统：
1）Oracle
美国甲骨文（Oracle）公司开发的世界上第一款支持SQL语言的关系型数据库。具有很好的开放性，能在所有的主流平台上运行，性能高，安全性好，风险低；但其对硬件的要求很高，管理维护和操作比较复杂且价格昂贵，一般用在满足对银行、金融、保险等行业大型数据库的需求上。
2）DB2
IBM公司著名的关系型数据库产品，无论稳定性、安全性、恢复性等等都无可挑剔，且从小规模到大规模的应用都可以使用，但用起来非常繁琐，比较适合大型的分布式应用系统。
3）SQL server
由Microsoft开发和推广的关系型数据库，功能比较全面、效率高，可以作为中型企业或单位的数据库平台。SQL server可以与windows操作系统紧密继承，无论是应用程序开发速度还是系统事务处理运行速度，都能得到大幅度提升。但是只能在windows系统下运行，毫无开放性。
4）MySQL
一种开放源代码的轻量级关系型数据库。使用最常用的结构化查询语言SQL对数据库进行管理。任何人都可在General Public License的许可下下载并根据个人需要对其缺陷进行修改。
体积小、速度快成本低、开放源码等优点，现已被广泛应用于互联网上的中小型网站，且大型网站也开始使用，如网易，新浪等。

1.5. MySQL简介

最初由瑞典 MySQL AB 公司开发，2008年1月16号被Sun公司收购。2009年Sun被Oracle收购。
MySQL是一个支持多线程高并发多用户的关系型数据库管理系统。
之所以受青睐，有以下优点：
1）开放源代码
2）跨平台
3）轻量级
4）成本低
PS：社区版和企业版的主要区别是：

• 社区版包含所有MySQL的最新功能，企业版只包含稳定之后的功能。可以理解为，社区版是企业版的测试版。
• MySQL官方的支持服务只针对企业版，若用户在使用社区版时出现问题，官方是不负责任的。

1.5.1 MySQL8的安装

查看MySQL的安装结果

1.5.2 MySQL登录、访问、退出操作

1】 登录

打开控制命令台：win+r
登录的命令：mysql -hlocalhost -uroot -p
-h：host主机名。后面跟要访问的数据库服务器的地址；若是登录本机，可以省略。
-u：user用户名。后面跟登录数据的用户名，第一次安装后以root用户来登录，是MySQL的管理员用户。
-p：password密码。一般不直接输入，而是回车后以保密方式输入。

2】 访问数据库

显示MySQL的数据库列表：show databases;
默认有四个自带的数据库，每个数据库中可以有多个数据库表、视图等对象。

切换当前数据库的命令：use mysql;

• MySQL下可以有多个数据库，若要访问那个数据库，需要将其值为当前数据库。
• 该命令的作用就是将数据库mysql（默认提供的四个数据库之一的名字）置为当前数据库。

显示当前数据库的所有数据库表：show tables;

MySQL层次：不同项目对应不同的数据库组成 - 每个数据库中有很多表 - 每个表中有很多数据

3】 退出数据库

可以使用quit 或者exit命令完成，也可使用\q;完成退出。

1.5.3 数据库的卸载

1）停止MySQL服务：在命令行模式下执行 net stop mysql 或者在windows服务窗口下停止服务（win10里搜索服务，找到MySQL右键点击停止）。
2）在控制面板中删除MySQL软件
3）删除软件文件夹：直接删除安装文件夹MySQL
4）删除数据文件夹：直接删除文件夹C:\ProgramData\MySQL，此步不要忘记，否则会影响MySQL的再次安装。（ProgramDatabase文件夹可能是隐藏的，显示出来即可）
（MySQL文件下的内容才是真正的MySQL中数据）
5）删除path环境变量中关于MySQL安装路径的配置

1.5.4 使用图形客户端navicat12连接MySQL

1】 认识Navicat

Navicat是一套快速、可靠且价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。 Navicat 是以直觉化的图形用户界面而建的，让你可以以安全且简单的方式创建、组织、访问并共用信息。

Navicat Premium 是一套数据库开发工具，从单一应用程序中同时连接 MySQL、 MariaDB、 Microsoft Azure 、Oracle Cloud、MongoDB Atlas 、 阿里云、腾讯云和华为云等云数据库兼容。可以快速轻松地创建、管理和维护数据库。

2】 安装Navicat

直接解压安装包，拷贝到你定义的目录下，双击其中的 navicat.exe，即可开始运行。打开后选择 连接工具按钮 --连接，输入四个连接连接参数，并进行测试，结果提示连接失败，报2059异常。
该错误原因是在 MySQL8 之前加密规则 mysql_native_password ，而在 MySQL8 以后的加密规则为 caching_sha2_password。
解决方法两种：一种是更新 navicat 驱动来解决，一种是将 mysql 用户登录的加密规则修改为 mysql_native_password 。
第二种操作方法：登录 mysql，
设置密码永不过期： alter user ’root’@’localhost’ identified by ’root’ password expire never;
设置加密规则为 mysql_native_password ：

alter user ‘root’@’localhost’ identified with mysql_native_password by ‘root’;

重新访问 avicat ， 提示连接成功。

2. SQL语言入门

1】 SQL语言入门

数据库管理人员（DBA）通过数据库管理系统（DBMS）对数据库（DB）中的数据进行操作。
SQL（Structure Query Language）是结构化查询语言的简称，是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。在数据库管理系统中，使用SQL语言来实现数据的存取、查询、更新等功能。SQL是一种非过程化语言，只需提出“做什么”，而不需要指明“怎么做”。

2】 SQL语言分为五个部分：

• 数据查询语言（Data Query Language，DQL）：
  DQL主要用于数据的查询，基本结构是使用

SELECT 子句、FROM 子句和 WHERE 子句的组合来查询一条或多条数据。

• 数据操作语言（Data Manipulation Language，DML）：
  DML主要用于对数据库中的数据进行增加、修改和删除的操作，主要包括：

1）INSERT：增加数据
2）UPDATE：修改数据
3）DELETE：删除数据

• 数据定义语言（Data Definition Language，DDL）：
  DDL主要针对数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

1）CREATE：创建数据库对象
2）ALTER：修改数据库对象
3）DROP：删除数据库对象

• 数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限。主要包括：

1）GRANT：授予用户某种权限
2）REVOKE：回收授予的某种权限

• 事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。主要包括：

1）START TRANSACTION：开启事务
2）COMMIT：提交事务
3）ROLLBACK：回滚事务
4）SET TRANSACTION：设置事物的属性

3. DDL_DML_创建数据库|表

3.1 数据库表列类型

1】整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值显示宽度（如，INT(4)）。显示宽度并不限制可以在列内保存的值的范围，也不限值超过列的指定宽度的值的显示。
主键自增：不使用序列，通过 auto_increment，要求是整数类型。

2】 浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。
score double(4,1)-- 小数部分为1位，总宽度4位，并且不会自动扩充。

3】 字符串类型

CHAR 和 VARCHAR 类型相似，均用于存储较短的字符串，主要的不同之处在于存储方式。
CHAR 类型长度固定， VARCHAR 类型长度可变。因为 VARCHAR 类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时，推荐使用 VARCHAR ，这样可以大大节约磁盘空间，提高存储效率。
CHAR 和 VARCHAR 表示的是字符的个数，而不是字节的个数。

BLOB 存储音频视频。

4】 日期和时间类型

TIMESTAMP 类型的数据指定方式与 DATTIME 基本相同，两者的不同之处在于以下几点：
1）数据的取值范围不同， TIMESTAMP 的取值范围更小。
2）若对 TIMESTAMP 的字段没有明确赋值，或是被赋予了 NULL值，MySQL会自动将该字段赋值为系统当前的日期和时间。
3）TIMESTAMP 还可以使用 CURRENT_TIMESTAMP 来获取系统当前时间。
4）TIMESTAMP 有一个很大的特点，就是时间是根据时区来显示的。

3.2 定义数据库

-- 创建数据库
create database [if not exists] db_name
[[default] character set gb2312
[[default] collate gb2312_chinese_ci;


-- []内为可选项
-- if not exists：在创建数据库前进行判断，只有不存在时才可以创建
-- character set：指定数据库字符集（charset）。简体中文字符集名称为gb2312
-- collate：指定字符集的校对规则。简体中文字符集的校对规则为gb2312_chinese_ci
-- default：指定默认的数据库字符集和字符集的校对规则

-- 选择数据库
-- 只有使用use命令指定某个数据库为当前数据库之后，才能对该数据库及其存储的数据对象执行各种操作
use db_name;

-- 查看当前用户可查看的数据库列表
show databases;

-- 修改数据库
-- 数据库的默认字符集为latin1，默认校对规则为latin1_swedish_ci
alter database [db_name]
[default] character set gb2312
[default] collate gb2312_chinese_ci;

-- 删除数据库
drop database [if exists] db_name;
-- 删除整个数据库，该数据库中的所有将被永久删除

3.3 定义表

1】 认识数据库表

表（Table）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示。二维表是由行和列组成的，分别都包含着数据。
每个表都是由若干行和列组成的，在数据库中，表中的行被称为记录，表中的列被称为是这些记录的字段。
记录也被称为一行数据，是表里的一行。在关系型数据库的表中，一行数据是指一条完整的记录。
字段是表里的一列，用于保存每条记录的特定信息。

2】 创建数据库表 student

-- 创建表
create table tb_name (
字段名1 数据类型 [列级完整性约束条件] [默认值]
[,字段名2 数据类型 [列级完整性约束条件] [默认值]]
[,... ...]
[,表级完整性约束条件]
)[engine=引擎类型];

• 建立一张用来存储学生信息的表
• 字段包含学号、姓名、性别、年龄、入学日期、班级、Email等信息
• 姓名不能为空
• 性别默认值是男
• Email唯一
  1）创建数据库：
  Navicat中新建数据库，字符集：utf8mb4
  2）新建查询：
  Navicat菜单点击查询，新建查询
  3）创建数据库表：
  ##、-- ：单行注释
  /* 内容 */ ：多行注释

--创建数据库表：
create table student (
 number int(6) not null primary key auto_increment, -- 6显示长度
 name varchar(10) not null，
 sex char(1) default ‘male’,
 age int(3)
 enterdate date,
 class varchar(10),
 email varchar(15) unique
)[engine=InnoDB];

navicat 中选中所写的SQL语言，右键点击运行，ok表示已创建表。

– 表的创建需要选定当前数据库，若表名称被指定为 db_name.tbl_name 的格式，则可在特定的数据库中创建表，而不论是否有当前数据库，都可以通过这种方式来创建表

– 完整性约束条件包括实体完整性约束（primary key、unique）、参照完整性约束（foreign key）和用户自定义约束（not null、default、check 约束等）。若完整性约束条件涉及该表的多个字段，则必须定义在表级上，否则既可以定义在表级，也可定义在列级。

– 关键字 null 和 not null 可以给字段自定义约束。允许null的列也允许在插入记录时不给出该列的值。null为默认设置。null值是没有值，不是空串。指定’ ’（两个单引号，中间没有字符）在not null列是允许的，因为空串是一个有效的值，并非无值。

– auto_increment将字段设置为自增属性，可以给记录一个唯一而又容易确定ID号，该字段可以唯一标识表中的每条记录。MySQL中，只有整型列才能设置。默认初始值为1，当往一个定义为auto_increment 列中插入null值或数字0时，该列的值会被设置为 value+1（默认为加1递增），其中value是当前表中该列的最大值。每个表只能定义一个自增列，且必须在该列上定义主键约束（primary key）或候选键约束（unique）

– default 设置默认值。

– show engines可以查看系统支持的引擎类型和默认引擎。InnoDB是事务型数据库的首选引擎。

-- 查看表的名称
show tables [{
  from|in} db_name];

-- 使用{from|in} db_name 可显示非当前数据库中的数据库表名称

-- 查看表的基本结构
-- describe，显示表的字段详细信息
desc tb_name;
show columns {
  from|in} tb_name [{
  from|in} db_name];

-- 查看表的详细结构，建表语句
show create table tb_name;
show create table student\G;

-- 查看表中数据
select * from student;

3.4 修改表

对已经创建的表做进一步的结构修改与调整。使用alter table 来修改原有表的结构。常用的修改表操作有：修改字段名或字段的数据类型、添加和删除字段、修改字段的排列位置、更改表的引擎类型、增加和删除表的约束等。

-- 修改表结构
-- 添加字段
alter table tb_name add [column] 新字段名 数据类型
  [约束条件][first|after 已有字段名]

-- 可选项“约束条件”用于指定字段取值不为空、字段的默认值、主键以及候选键约束等。
-- 可选项“first|after 已有字段名”用于指定新增字段在表中的位置：first表示将新添加的字段设置为表的第一个字段，after 表示将新添加的字段加到指定的已有字段名的后面。若语句中未设置，则默认将新添加的字段设置为数据表的最后一列

alter table student add id int not null unique auto_increment first;
desc student;
alter table student add department varchar(16) default ‘信息学院’ after nation;

-- 增加一列
alter table student add score double(5,2);
update student set score = 132.567 where number = 1;

-- 增加一列（放在最前面）
alter table student add score double(5,2) first;

-- 增加一列（放在sex列的后面）
alter table student add score after sex;


-- 修改字段
alter table tb_name change [column] 原字段名 新字段名 数据类型 [约束条件];
alter table tb_name modify [column] 字段名 数据类型 [约束条件] [first|after 已有字段名];
alter table tb_name alter [column] 字段名 {
  set|drop} default;

-- change可同时修改列名和列的数据类型。可同时添加多个，用逗号分隔
-- modify只会修改列的数据类型，还可指定列的位置
-- alter可修改或删除默认值

-- 修改一列
alter table student change score scores double(5,1); -- change 修改列名和列的类型
alter table student modify score float（4,1）; -- modify 修改的是列的类型，不会改变列的名字
alert table student alter department drop default;
alter table student change birthday age tinyint null default 18;
alter table student modify department varchar(10) not null after sid;
alter table student alter email set dedault ‘XX@gmail.com’;
-- 注意：修改数据类型可能会丢失原有的数据；若数据类型与原来的不兼容，命令不会执行，提示错误，兼容则数据可能会被截断。


-- 删除字段
alter table tb_name drop [column] 字段名;

alter table student drop email;


-- 重命名表
alter table tb_name rename [to] 新表名;
rename table tb_name1 to newname1 [, name2 to newname2] ... ...;

alter table student rename studentstable;
rename table studentstable to student;


-- 删除表
drop table [if exists] tb_name1[,tb_name2] ... ...;

-- 若删除的表不存在且不加if exists，会提示“Error 1051...”；加上if exists，表不存在，语句可顺利执行，但会发出警告 warning

3.5 数据的完整性约束

关系模型的完整性规则是对关系的某种约束条件。对关系模型施加完整性约束，则是为了在数据库应用中保障数据的准确性和一致性，这也是数据库服务器最重要的功能之一。

约束分类
约束从作用上可以分为两类：
1）表级约束：表中所有字段定义后添加。
可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔，必须指出要约束的列的名称。
2）列级约束：表中某个字段定义后添加。
包含在列定义中，直接跟在该列的其他定义之后，用空格分隔；不必指定列名。

3.5.1 定义实体完整性

实体完整性规则（Entity Integrity Rule）是指关系的主属性不能取空值，即主键和候选键在关系中所对应的属性都不能取空值。MySQL中实体完整性就是通过主键约束和候选键约束实现的。

1】 主键约束

主键是表中某一列或某些列所构成的一个组合。由多个列组合而成的主键也称为复合主键。

• 每个表只能定义一个主键
• 键值必须能够唯一标识表中的每一行记录，且不能为null
• 复合主键不能包含不必要的多余列
• 一个列名在复合主键的列表中只能出现一次

-- 列级约束
create table student (
 number int(6) primary key auto_increment,
 name varchar(10) not null，
 sex char(1) default ‘M’ check(sex=’M’||sex=’F’),
 age int(3) check(age>17 and age<51),
 enterdate date,
 class varchar(10),
 email varchar(15) unique
);

-- 添加数据
insert into student values (1,’wang’,’F’,28,now(),’class 1’,’wang@126.com’);
-- 部分添加
insert into student (name,enterdate) values (’weisu’,’2011/09/01’);

-- 若主键没有设定值，或者用null default 都可以完成主键自增效果
insert into student values (null,’wan’,’M’,24,now(),’class 1’,’wan@126.com’);
insert into student values (default,’liu’,’M’,33,now(),’class 1’,’liu@126.com’);

-- 若SQL报错，主键可能就浪费了，后续插入的主键是不连号的，主键不要求连号
insert into student values (null,’wan’,’M’,00,now(),’class 1’,’wan@126.com’);-- 报错，后面主键不连号


-- 表级约束
create table students (
 number int(6) auto_increment,
 name varchar(10) not null，
 sex char(1) default ‘M’,
 age int(3),
 enterdate date,
 class varchar(10),
 email varchar(15),

primary key(number)
);

create table students (
 number int(6) auto_increment,
 name varchar(10) not null，
 sex char(1) default ‘M’,
 age int(3),
 enterdate date,
 class varchar(10),
 email varchar(15)
);

-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 该错误的解决方法：去掉自增

2】 完整性约束的命名

可以对完整性约束进行添加、删除和修改等操作。其中，为了删除和修改完整性约束，首先需要在定义约束的同时对其进行命名。在各种完整性约束的定义说明之前加上关键字 constraint 和该约束的名字，

constraint<symbol>
{
  primary key(主键字段列表)
|unique(候选键字段列表)
|foreign key(外键字段列表) references tb_被参照关系(主键字段列表)
|check(约束条件表达式)
};

-- symbol为指定的约束名字，在完整性约束说明的前面被指定，其在数据库里必须是唯一的。

create table students (
 number int(6) auto_increment,
 name varchar(10) not null，
 sex char(1) default ‘M’,
 age int(3),
 enterdate date,
 class varchar(10),
 email varchar(15),

constraint pk_stu primary key(number), -- pk_stu 主键约束的名字，自行定义
constraint ck_stu_sex check (sex = ‘F’|| sex = ‘M’), -- 检查约束
constraint ck_stu_age check (age>=18 and age<=50),
constraint uq_stu_email unique (email)  -- 唯一约束
);


-- 在创建表之后 添加约束
alter table students add constraint pk_stu primary key(number); -- 主键约束
alter table students modify number int(6) auto_increment; -- 修改自增条件
alter table students add constraint ck_stu_sex check ( sex = ‘F’ || sex = ‘M’);
alter table students add constraint ck_stu_age check ( age >17 and age <51);
alter table students add constraint uq_stu_email unique(email);

-- 查看表结构
desc students;

3】 候选键约束

可以是表中的某一列，也可以是表中某些列所构成的一个组合。必须唯一且不为null。使用unique定义

create table school (
classid char(6) primary key,
classname varchar(10) not null unique,
grade smallint,
classnum tinyint
)engine=innodb;

create table school (
classid char(6) primary key,
classname varchar(10) not null,
grade smallint,
classnum tinyint,
constraint uq_class unique(classname)
)engine=innodb;

primary key 和 unique 的区别：

• 一个表中只能创建一个primary key，但可以定义若干unique
• 定义为primary key的列不允许有空值，但unique字段允许控制存在
• 定义primary key约束时，系统自动产生primary key 索引，定义unique时，系统自动产生unique索引

3.5.2 定义参照完整性

现实世界中实体之间往往存在某种联系，在关系模型中实体间的联系都是用关系来描述的，因此可能存在着关系与关系间的引用。如，学生实体和班级实体可分别用下面关系模式表示，其中主键用下划线标识。
学生（学号，姓名，性别，出生日期，籍贯，民族，班级编号）
班级（班级编号，班级名称，所属院系，年级，班级最大人数）
这两个关系间存在着属性的引用，即学生关系引用了班级关系的主键“班级编号”。在学生关系中，班级编号是外键。

外键是一个表中的一个或一组属性，不是这个表的主键，但对应另一个表的主键。定义外键后，不允许删除外键引用的另一个表中具有关联关系的记录。外键所属的表称作参照关系，相关联的主键所在的表称作被参照关系。

参照完整性规则（Referential Integrity Rule）定义的是外键与主键之间的引用规则，即外键的取值或者为空，或者等于被参照关系中某个主键的值。
定义外键时，需要遵守以下规则：

• 被参照表必须已经使用create table语句创建，或者必须是当前正创建的表。若是后一种情形，则被参照表与参照表是同一个表，这样的表称为自参照表（self-referencing table），这种结构称为自参照完整性（self-referencial integrity）
• 必须为被参照表定义主键或候选键
• 必须在被参照表的表名后面指定列名或列名的组合，这个列或列组合必须是被参照表的主键或候选键
• 尽管主键是不能够包含空值的，但允许在外建出现空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
• 外键对应列的数目必须和被参照表的主键对应列的数目相同
• 外键对应列的数据类型必须和被参照表的主键对应列的数据类型相同

-- 列级完整性约束
create table ondata.student (
stuid char(10),
stuname varchar(20) not null,
sex char(2) not null,
birthday date,
nation varchar(10) default ‘汉’,
classid char(6) references tb_class(classid),
constraint pk_stuid primary key(stuid)
);

-- 表级完整性约束
create table ondata.student (
stuid char(10),
stuname varchar(20) not null,
sex char(2) not null,
birthday date,
nation varchar(10) default ‘汉’,
classid char(6),
constraint pk_stuid primary key(stuid),
constraint fk_classid foreign key(classid) references tb_class(classid)
);

定义外键约束后，只有当某班级里没有学生时，才可以删除该班级信息。MySQL可以通过定义一个参照动作来修改这个规则，即定义外键时可以显示说明参照完整性约束的违约处理策略。
给外键定义参照动作时，需要包括两部分：一是要指定参照动作适用的语句，即update 和delete 语句；二是要指定采取的动作，即 cascade、restrict、set null、no action 和 set default，其中，restrict 为默认值。
具体策略如下：

• restrict：限制策略，即当要删除或修改被参照表中被参照列上且在外键中出现的值时，系统拒绝对被参照表的删除或修改操作。

• cascade：级联策略，即从被参照表中删除或修改记录时，自动删除或修改参照表中匹配的记录。

• set null：置空策略，即当从被参照表中删除或修改记录时，设置参照表中与之对应的外键列的值为null。这个策略需要被参照表中的外键列没有声明限定词 not null。

• no action：表示不采取实施策略，即当一个相关的外键值在被参照表中时，删除或修改被参照表中键值的动作不被允许。与restrict相同。

• set default：默认值策略，即当从被参照表中删除或修改记录时，设置参照表中与之对应的外键列的值为默认值。这个策略要求已经为该列定义了默认值。

create table ondata.student (
stuid char(10),
stuname varchar(20) not null,
sex char(2) not null,
birthday date,
nation varchar(10) default ‘汉’,
classid char(6),
constraint pk_stuid primary key(stuid),
constraint fk_classid foreign key(classid) references tb_class(classid)
on update restrict   -- 表示当某个班级里有学生时不允许修改班级表中班级编号
on delete cascade   -- 要删除班级表中某个班级编号时，若班级里有学生，就将相应学生记录级联删除
);

外键只可以用在使用存储引擎InnoDB创建的表中。

1】 外键约束
外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

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

主表（父表）：班级表 — 班级编号 — 主键
从表（子表）：学生表 — 班级编号 — 外键

2】 SQL展示

-- 先创建父表，班级表
create table class(
cid int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
);

-- 添加班级数据
insert into class values (null,’java001’,’r001’),(null,’java002’,’r345’),
(null,’python01’,’r123’);

-- 创建子表
drop table student;
create table student(
sid int(4) primary key auto_increment,
sname varchar(10) not null,
classid int(4) -- 取值参考class表中的cid字段，不要求字段名字完全重复，但是类型定义尽量要求相同！
);

-- 添加学生信息
insert into student values(null,’mark’,1),(null,’Jane’,1),(null,’Jordan’,2);

-- 查看学生表
select * from student;

-- 出现问题
-- 1.添加一个学生对应的班级编号为4
insert into student values (null,’lily’,4);
-- 2.删除班级2
delete from class where cid = 2;

-- 出现问题的原因
-- 现在的外键约束，没有用语法添加进去，只是逻辑上认为班级编号是外键

-- 解决办法：添加外键约束
-- 注意：外键约束只有表级约束！！
create table student(
sid int(4) primary key auto_increment,
sname varchar(10) not null,
classid int(4),
constraint fk_stu_classid foreign key (classid) references class (cid)
);

-- 创建表后添加外键约束
create table student(
sid int(4) primary key auto_increment,
sname varchar(10) not null,
classid int(4)
);

alter table student add constraint fk_stu_classid foreign key (classid) references class (cid);

注意：先删除主表，再删除从表。

3】 外键策略

-- 删除班级2：无法直接删除，有外键约束
-- 加入外键策略：
-- 策略1： no action 不允许操作
-- 通过操作SQL来完成：
-- 先把班级2的学生对应的班级改为NULL
update student set classid = null where classid = 2;
-- 然后再删除班级2
delete from class where cid = 2;

-- 策略2：cascade 级联操作：操作主表的时候，影响从表的外键信息
-- 先删除之前的外键约束：
alter table student drop foreign key fk_stu_classid;
-- 重新添加外键约束：
alter table student add constraint fk_stu_classid foreign key (classid) references class(cid) on update cascade on delete cascade;
-- 试试更新：
update class set cid = 5 where cid = 3;
-- 试试删除：
delete from class where cid = 5;

-- 策略3：set null 置空操作
-- 先删除之前的外键约束：
alter table student drop foreign key fk_stu_classid;
-- 重新添加外键约束：
alter table student add constraint fk_stu_classid foreign key (classid) references class(cid) on update set null on delete set null;
-- 试试更新：
update class set cid = 8 where cid = 1;

-- 注意：
-- 1. 策略2 级联操作 和策略3 删除操作可以混着使用

alter table student add constraint fk_stu_classid foreign key (classid) references class(cid) on update cascade on delete set null;

-- 2.应用场合：
1）朋友圈删除，点赞、留言都删除 -- 级联操作
2）解散班级，对应的学生班级 置为NULL就可以 -- set null

3.5.3 用户定义的完整性

不同数据库系统根据其应用环境的不同，往往还需要定义一些特殊的约束条件，即用户定义的完整性规则 （User-defined Interity Rule），它反映了某一具体应用所涉及的数据应满足的语义要求。
MySQL支持的用户自定义完整性约束：非空约束、check约束和触发器。

1. 设置非空约束

非空约束的定义可以使用create table 或alter table语句，在某个列定义后面加上关键字not null作为限定词，来约束该列的取值不能为空。

2. check约束

check约束需要指定限定条件，可分别定为列级或表级完整性约束。常用语法格式是：

check (expr);

其中，expr是一个表达式，用于指定需要检查的限定条件。如，可在限定条件中加入子查询。

create table tb_course(
courseid char(6),
coursename varchar(20) not null,
credit int not null,
coursehour int not null,
priorcourse char(6),
constraint pk_course primary key (courseid),
constraint fk_course foreign key (priorcourse) references tb_course(courseid),
constraint ck_course check(credit = coursehour/16)
);

check约束定义了字段credit和coursehour间应满足的函数关系，故只能定义为表级约束。

小结

1. 主键约束
   主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生学号等，设置为主键的字段取值（唯一）不能重复，也（非空）不能为空，否则无法唯一标识一条记录。

主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可使用列级约束；而对于多字段主键的添加，只能使用表级约束。

2. 非空约束
   非空约束（NOT NULL，缩写NK），规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段，非空约束的添加只能使用列级约束 （非空约束无表级约束）。

为已存在表中的字段添加非空约束

alter table students modify number int(5) not null;

删除非空

alter table students modify number int(5) null;

3. 唯一约束
   唯一约束（UNIQUE，缩写UK），规定了一张表中指定的某个字段的值不能重复，即这一字段的的每个值都是唯一的。

无论是单个字段还是多个字段，唯一约束的添加都能使用列级约束和表级约束。

4. 检查约束
   检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。

5. 默认值约束
   默认值约束（DEFAULT）用来规定字段的默认值。若某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

默认值约束的设置与非空约束一样，也只能使用列级约束。

6. 字段值自动增加约束
   自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个字增长字段，且该字段必须定义了约束（该约束可以使主键约束、唯一约束以及外键约束）。若自增字段没有定义约束，数据库会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的主键字段设置自增约束

-- 创建表时：
create table students(
id int(10) primary key,
name varchar(5) not null,
age int(3),
sex char(1) default ‘F’
);

-- 表中主键字段添加自增约束
alter table student modify student int(6) auto_increment;

使用ALTER TABLE语句删除自增约束

alter table student modify student int(10);

3.5.4 更新完整性约束

1. 删除约束

1） 删除外键约束
若使用constraint子句命名的表级完整性约束，则用:

alter table <表名> drop foreign key <外键约束名>;

-- 删除表tb_score在 studentid上定义的外键约束 fk_score
alter table tb_score drop foreign key fk_score;

当要删除无命名的外键约束时，可先使用 show create table 语句查看系统给外键约束指定的名称，然后再删除该约束名。

-- 在表tb_score字段studentid上定义一个无命名的外键约束，然后删除它
-- 定义一个无名外键约束
alter table tb_score add foreign key (studentid) references tb_student(studentid);
-- 查看系统给定的外键约束名称
show create table tb_score\G;
-- 删除该约束
alter table tb_score drop foreign key fk_studentid;

2） 删除主键约束
删除主键约束时，因为一个表只能定义一个主键，所以无论有没给主键约束命名，均使用：

alter table <表名> drop primary key;

3） 删除候选键约束
删除候选键约束时，MySQL实际删除的是唯一性索引，应使用drop index子句删除。若没有给约束命名，MySQL自动将字段名定义为索引名。

alter table <表名> drop {约束名|候选键字段名};

2. 添加约束

数据表定义完成后，可以使用alter table语句添加完整性约束。
1） 添加主键约束

alter table <表名> add [constraint <约束名>] primary key (主键字段);

alter table tb_student add constraint pk_student primary key(studentid);

2） 添加外键约束

alter table <表名> add [constraint <约束名>] foreign key (外键字段名) references 被参照表(主键字段名);

alter table tb_score add constraint foreign key (studentid) references tb_student(studentid);

3） 添加候选键约束

alter table <表名> add [constraint <约束名>] unique key(字段名);

alter table tb_class add constraint uq_class unique key(classname);

4. 数据查询

4.1. select 语句

select [all|distinct|distinctrow] <目标表达式1>[,<目标表达式2>] ...
from <表名1或视图名1>[,<表名2或视图名2>] ...
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]] 
[order by <列名2> [asc|desc]]
[limit [m,]n];

– all|distinct|distinctrow：用于指定是否应返回结果集中的重复行。若没有指定，默认all，即返回所有匹配行。distinct和distinctrow一样，会消除结果集中的重复行。
– select子句：用于指定要显示的字段或表达式。
– from子句：指定数据来源于哪些表或视图
– where子句：用于指定对记录的过滤条件
– group by子句：将查询结果集按指定的字段值分组
– having子句：指定分组结果集的过滤条件
– order by子句：将查询结果集按指定字段值的升序或降序排序
– limit子句：指定查询结果集包含的记录数

所有可选子句必须依照select语句的语法格式所罗列的顺序使用。

4.2. 单表查询

1. 选择字段

select 目标表达式1,目标表达式2,...,目标表达式n
from 表名;

-- 查询指定字段
select sid,classname,department from student;
-- 去重
select disctinct department from class;
-- 查询所有字段
select * from student;

-- 查询经过计算的值
select sid,sex,’age:’,’year(now())-year(birthday) from student;
-- ‘age:’字符串常量，直接以列显示在表中

-- 定义字段别名
-- select子句的目标列表达式之后添加，格式： 字段 [as] 字段别名;
select sid,sex,year(now())-year(birthday) age from student;

2. 选择指定记录

用户查询时只需要查询表中的指定数据，即对数据进行过滤。

select 目标表达式1,目标表达式2,...,目标表达式n
from 表名
where 查询条件;

常用查询条件：
比较：=，<>，!=，<，<=，>，>=，!<，!>，not+含比较运算符的表达式
确定范围：between and，not between and
确定集合：in，not in
字符匹配：like，not like
空值：is null，is not null
多重条件：and，or

-- 比较大小
select coursename,credit,coursehour from tb_cousre where coursehour>=48;
select coursename,credit,coursehour from tb_cousre where not coursehour<48;
select sname,sex,nation from tb_student where nation != ‘汉’;
select sname,sex,nation from tb_student where nation <> ‘汉’;
select sname,sex,nation from tb_student where not nation