超详细超级全，最新数据库SQL大全，一看就会！（小写版）

前言

1. 什么是数据？什么是数据库？

1. 数据是数据库存储的基本对象，种类包括文字、图形、图像、声音等等。
2. 数据库（database)是以某种有组织的方式存储的数据集合，保存有组织的数据的容器，通常是一个文件或一组文件。
3. 理解数据库的一种最简单的方法是将其想象为一个文件柜。此文件柜是一个存放数据的物理位置，不管数据是什么以及如何组织的。

2. 什么是数据库管理软件（DBMS）？

1. 数据库存放于文件中，管理该数据库的软件称为数据管理软件（DBMS)。
2. 对数据库的操作与管理是通过数据库管理软件来完成的。
3. 行业中主流的数据库管理软件有 oracle、sqlserver、mysql 等。
   

3. 数据库在工作中的应用

1. 部署测试环境需要安装数据库、配置数据库、导入数据库脚本。
2. 数据库中构造初始化数据。
3. 测试执行过程中需验证数据库中的数据是否正确。
4. 有些时候验证功能，需要修改数据库中数据。
5. 构造数据进行性能测试，大数据量测试。
6. 测试数据的存储过程，如报表、流量等。

4. mysql 数据库的特点

1. mysql 是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
2. mysql 所使用的 sql 语言是用于访问数据库的最常用标准化语言。
3. mysql 分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 mysql的作为网站数据库。
4. mysql 中命令不区分大小写。

5. mysql数据库架构

mysql是基于客户机-服务器模式（c/s）的数据库管理软件，客户机-服务器应用分为两个不同的部分:

1. 服务器是负责所有数据访问和处理的软体，这个软件运行在称为数据库服务器的计算机上；与数据文件打交道的只有服务器软件，数据添加、删除、更新的所有请求操作都是由服务器软体来完成。
2. 客户机是与用户打交道的软体，服务器处理客户机发送的请求，并把处理结果返回给客户机。

6. mysql 服务器与客户端

1. 为了能够使用 mysql ，需要有 mysql 服务器软件与 mysql 客户端软件。
2. 数据库和表都是创建在服务器端。

一、 SQL的简介

1. SQL的概述

1. SQL是结构化查询语言，是一种专门用来与数据库通信的语言。
2. SQL语言由国际标准委员会定义，但是不同的数据库管理系统采用的SQL语言会存在一些小的差异。数据库管理系统可以通过 SQL 管理数据库；定义和操作数据，维护数据的完整性和安全性。

2. SQL的优点

1、简单易学，具有很强的操作性
2、绝大多数重要的数据库管理系统均支持 SQL
3、高度非过程化；用 SQL 操作数据库时大部分的工作由 DBMS 自动完成

3. SQL的分类

1. DDL (Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：create、 alter、drop
2. DML (Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：insert、 update、 delete
3. DCL (Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：grant、deny
4. DQL (Data Query Language) 数据查询语言，用来查询数据 ；常用语句：select

二、 数据库的三大范式

1. 第一范式 ( 1NF ) 是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
2. 第二范式 ( 2NF ) 是在第一范式( 1NF ) 的基础上建立起来得，满足第二范式 ( 2NF ) 必须先满足第一范式( 1NF )。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
3. 第三范式 ( 3NF ) 是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式 ( 3NF ) 要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

三、 数据库的数据类型

使用 MySQL 数据库存储数据时，不同的数据类型决定了 MySQL 存储数据方式的不同。为此，MySQL 数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点 数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

1. 整数类型

根据数值取值范围的不同 MySQL 中的整数类型可分为5种，分别是tinyint、smallint、mediumint、int 和 bigint。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
tinyint	1	0~255	-128~127
smallint	2	0~65535	-32768~32768
mediumint	3	0~16777215	-8388608~8388608
int	4	0~4294967295	-2147483648~ 2147483648
bigint	8	0~18446744073709551615	-9223372036854775808~9223372036854775808

2. 浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（float) 和双精度浮点数类型（double)。而定点数类型只有一种即decimal类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
float	4	0和1.175494351E-38~3.402823466E+38	-3.402823466E+38~-1.175494351E-38
double	8	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308
decimal (M,D)	M+2	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308

注意：从上图中可以看出：decimal类型的取值范围与double类型相同。但是，请注意：decimal类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为decimal(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3. 字符串类型

在MySQL中常用 char 和 varchar 表示字符串。
两者不同的是： varchar 存储可变长度的字符串。当数据为 char(M) 类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而 varchar(M) 所对应的数据所占用的字节数为实际长度加1。

插入值	char(3)	存储需求	varchar(3)	存储需求
‘’	‘’	3个字节	‘’	1个字节
‘a’	‘a’	3个字节	‘a’	2个字节
‘ab’	‘ab’	3个字节	‘ab’	3个字节
‘abc’	‘abc’	3个字节	‘abc’	4个字节
‘abcd’	‘abcd’	3个字节	‘abcd’	4个字节

4. 字符串类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

数据类型	储存范围
tinytext	0~255字节
text	0~65535字节
mediumtext	0~16777215字节
longtext	0~4294967295字节

5. 日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：year、date、time、datetime 和 timestamp。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

数据类型	字节数	取值范围	日期格式	零值
year	1	1901~2155	YYYY	0000
date	4	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
time	3	-838：59：59~ 838：59：59	HH:MM:SS	00:00:00
datetime	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
timestamp	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

5.1 year 类型

year 类型用于表示年份，在MySQL中，可以使用以下三种格式指定 year 类型的值。

1. 使用 4 位字符串或数字表示，范围为 ’1901’—'2155’ 或 1901—2155。例如，输人 ‘2019’ 或 2019 插人到数据库中的值均为 2019。
2. 使用两位字符串表示，范围为 ’00’—‘99’。其中，‘00’—'69’ 范围的值会被转换为 2000—2069 范围的 year 值，‘70’—'99’范围的值会被转换为 1970—1999 范围的 year 值。例如，输人’19’插人到数据库中的值为 2019。
3. 使用两位数字表示，范围为 1—99。其中，1—69 范围的值会被转换为 2001— 2069 范围的YEAR值，70—99 范围的值会被转换为 1970—1999范围的 year 值。例如，输人19 插入到数据库中的值为 2019。

请注意：当使用 year 类型时，一定要区分 ’0’和0。因为字符串格式的 ’0’ 表示的 year 值是 2000 而数字格式的0表示的 year 值是 0000。

5.2 time 类型

time 类型用于表示时间值，它的显示形式一般为 HH:MM:SS，其中，HH 表示小时， MM 表示分,SS 表示秒。在 MySQL 中，可以使用以下 3 种格式指定 time 类型的值。
1、以 ’D HH:MM:SS’ 字符串格式表示。其中，D 表示日可取 0—34 之间的值, 插人数据时，小时的值等于 (DX24+HH)。例如，输入 ’2 11:30:50’ 插人数据库中的日期为 59:30:50。
2、以 ‘HHMMSS’ 字符串格式或者 HHMMSS 数字格式表示。 例如，输人’115454’或115454, 插入数据库中的日期为 11:54:54
3、使用 current_time 或 now() 输人当前系统时间。

5.3 datetime 类型

datetime 类型用于表示日期和时间，它的显示形式为 ’YYYY-MM-DD HH: MM:SS’，其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分，SS 表示秒。在 MySQL 中，可以使用以下 4 种格式指定 datetime 类型的值。
以 ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期和时间，取值范围为 ‘1000-01-01 00:00:00’—'9999-12-3 23:59:59’。例如，输人 ’2019-01-22 09:01:23’或 ‘20140122_0_90123’ 插人数据库中的 datetime 值都为 2019-01-22 09:01:23。

1. 以 ’YY-MM-DD HH:MM:SS’ 或者 ’YYMMDDHHMMSS’ 字符串格式表示的日期和时间，其中YY表示年，取值范围为 ’00’—‘99’ 。与 date 类型中的 YY 相同，‘00’— '69’ 范围的值会被转换为 2000—2069 范围的值，‘70’—'99’ 范围的值会被转换为 1970—1999 范围的值。
2. 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，插入 20190122090123 或者 190122090123,插人数据库中的 datetime 值都 为 2019-01-22 09:01:23。
3. 使用 now 来输人当前系统的日期和时间。

5.4 timestamp 类型

timestamp 类型用于表示日期和时间，它的显示形式与 datetime 相同但取值范围比 datetime小。在此，介绍几种 timestamp 类型与 datetime 类型不同的形式：
1、使用 current_timestamp 输人系统当前日期和时间。
2、输人 null 时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

6. 二进制类型

在 MySQL 中常用 blob 存储二进制类型的数据，例如：图片、PDF文档等。blob 类型分为如下四种：

数据类型	储存范围
tinyblob	0~255字节
blob	0~65535字节
mediumblob	0~16777215字节
longtext	0~4294967295字节

四、数据库和数据表的基本操作

show database; 查看所有的数据库
use 数据库名;切换到这个数据库
use 表名;上面已经切换到数据库了，所以可以进入到这个数据库下的这个表
select * from 表名;查询这个表中的所有数据
select * 数据库名.表名;加前缀可以省略上面的use 数据库名;
select 字段名 from 表名; 查询这个表中的这个字段下的所有数据
select 字段名1,字段名2 from 表名;
select distinct 字段名1 from 表名;在想要去重的字段前加上distinct来去重
select distinct 字段名1,字段名2 from 表名;当字段名1和字段名2组合在一起时为重复的情况下才进行去重
select * from 表名 order by 字段名 asc;对这个表中的这个字段进行升序排序，其中asc也可以省略
desc是降序
select * from 表名 order by 字段名1 desc,字段名2 asc;先对字段名1进行降序排序然后在这个基础上再对字段名2进行升序排序
select * from 表名 limit 3;查询这个表中的前3条数据
select * from 表名 limit 1,3;查询这个表中从第二条数据开始往后查3条数据，注意查询的是2、3、4这几条数据，limit的索引是从0开始的

desc 表名;查询这张表的表结构，可以看到字段名、字段类型、是否能为空、是否有主键

select * from 表名 order by 字段名 desc limit 3;查询这个表中的这个字段名先进行降序排序再查询前3个数据，注意先排序后筛选顺序不能变

1. 数据库的基本操作

MySQL 安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块空间存储数据。

1.1 创建一个数据库

语法：

create database <数据库名称>;

示例：

-- 创建一个叫 db1 的数据库。
create database db1;

1.2 查看这个数据库管理系统中的所有的数据库

语法：

show databases;

1.3 查看指定数据库

语法：

show <数据库名>;

示例：

--查看这个名为 db1 的数据库
show db1;

1.4 查看并创建一个数据库

语法：

show create database db1;

示例：

-- 创建一个叫 db1 的数据库。
show create database db1;

1.5 切换数据库

查看完数据库管理系统中的所有的数据库后，我们需要对某个数据库进行操作，这个时候需要进行到相应的数据库中。

语法：

use <数据库名>;

示例：

--切换到这个名为 db1 的数据库
use db1;

1.6 删除数据库

语法：

drop database <数据库名称>;

示例：

--删除这个名为 db1 的数据库
drop database db1; 

1.7 将数据库的字符集修改为gbk

语法：

alter database <数据库名> character set gbk;

示例：

--将名为 db1 的这个数据库的字符集修改为gbk
alter database db1 character set gbk;

1.8 查看当前使用的数据库

语法：

select database();

示例：

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

2. 数据表的基本操作

数据库创建成功后可在该数据库中创建数据表 (简称为表) 存储数据。请注意：在操作数据表之前应使用“use 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出 “No database selected” 错误。

2.1 创建数据表

语法：

 create table <表名>(
         <字段1> <字段类型>,
         <字段2> <字段类型>,
         …
         <字段n> <字段类型>
);

示例：

--创建一个学生表
 create table student(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date
 );

2.2 查看数据表

2.2.1 查看当前数据库中所有的表

语法：

show tables;

2.2.2 切换到这个表

先用 use 数据库名; 切换到这个数据库
语法：use 表名; 上面已经切换到数据库了，所以可以进入到这个数据库下的这个表

2.2.3 查这个表的基本信息

语法：

show create table <表名>;

示例：

--查询这个 student 表的基本信息
show create table student;

2.2.4 查询这张表的结构或字段信息

查询这张表的表结构，可以看到字段名、字段类型、是否能为空、是否有主键。

语法：

desc <表名>;

示例：

--查看这个 student 表的结构
desc student;

2.3 修改数据表

有时，希望对表中的某些信息进行修改，例如：修改表名、修改字段名、修改字段 数据类型…等等。在 MySQL中使用 alter table 修改数据表.

2.3.1 修改表名

语法：

--把 表名1 修改为 表名2
alter table <表名1> rename to <表名2>;

示例：

--把表名 student 修改为 stu
alter table student rename to stu;

2.3.2 修改字段名

语法：

alter table <表名> change <旧字段名> <新字段名> <数据类型>;

其中，“表名”是需要修改字段名的表的名称，“旧字段名”是要被修改的字段名，“新字段名”是修改后的字段名，“数据类型”是字段的数据类型。
示例：修改字段名。

示例：

-- 修改 stu 表中的 name 字段名为 sname 且数据类型为 varchar(10)
alter table stu change name sname varchar(10);

2.3.3 修改字段数据类型

语法：

alter table <表名> modify <字段名> <数据类型>;

其中
表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。

示例：

-- 修改表 stu 中的 sname 字段名的数据类型为 int
alter table stu modify sname int;

2.3.4 增加字段

语法：

alter table <表名> add <字段名> <数据类型>;

其中，<表名> 为要操作的表名，<字段名> 为要增加的字段名，<数据类型>为字段的数据类型。

示例：

-- 从 stu 表中增加这个名为 address 的字段且数据类型为 varchar(50)
alter table stu add address varchar(50);

2.3.5 删除字段

语法：

alter table <表名> drop <字段名>;

示例：

-- 从 stu 表中删除 address 这个字段
alter table stu drop address;

2.4 删除数据表

语法：

drop table <表名>;

示例：

-- 删除这个名为 stu 的数据表
drop table stu;

五、数据表的约束

前言

1. 数据表（关系表）

1. 关系数据库中的数据是保存在表里面的，存储在表中的数据是一种特定类型的数据。在数据库中，表有一个唯一的名字来标识该表，称为表名。
2. 表定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名等等。

2. 表结构

2.1 列

1. 表由列组成，列中存储着表中某部分的信息。

2. 所有表都是由一个或多个列组成的。而这个属性在数据表中也叫字段

2.1 行

1. 表中的数据是按行存储的，一行就是一条记录。
2. 如果将表想象为网格，网格中垂直的列为表列，水平行为表行。
   

为防止错误的数据被插入到数据表，MySQL 中定义了一些维护数据库完整性的规则，这些规则常称为表的约束。常见约束如下：

约束条件	说明
primary key	主键约束用于唯一标识对应的记录
foreign key	外键约束
not null	非空约束
unique	唯一性约束
default	默认值约束，用于设置字段的默认值

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

1. 主键约束

• 唯一标识表中每行的这个列或几个列（注: 这个几列一起组成主键，可以唯一标识表中的每一行）称为主键。
• 主键用来表示一个特定的行。
• 表中任何列都可以作为主键，只要满意足以下条件：
  • 任意两行都不具有相同的主键值，即主键值不重复
  • 每个行都必须具有一个主键值，即主键值不能为空（null)
    

主键约束基本语法：

<字段名> <数据类型> primary key;

1. 设置主键约束( primary key )的第一种方式

示例：

create table student(
	id int primary key,
	name varchar(20)
);

2. 设置主键约束(primary key)的第二·种方式

示例：

create table student01(
	id int
	name varchar(20),
	primary key(id)
);

2. 非空约束

非空约束即 not null 指的是字段的值不能为空，基本的语法格式如下所示：

<字段名> <数据类型> not null;

示例：

create table student02(
id int
name varchar(20) not null
);

3. 默认值约束

默认值约束即 default 用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示为：

<字段名> <数据类型> default <默认值>；

示例：

create table student03(
 id int,
 name varchar(20),
 gender varchar(10) default 'male'
);

5 唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式为：

<字段名> <数据类型> unique;

示例：

create table student04(
id int,
name varchar(20) unique
);

6. 外键约束（foreign key)

• 列A 存在于 表1 和 表2 中，如果 列A 在 表1 中是主键，那么 列A 称为 表2 的外键。
• 外键表示了两个表之间的相关联系。
• 以另一个表的外键作主键的表被称为主表，具有此外键的表被称为主表的从表。
• 外键又称作外关键字。
• 外键的值必须是在主键中存在或少于主键都行，但是不能外键有而主键没有，注：可以为空
  外键约束即 foreign key 常用于多张表之间的约束。
  

基本语法如下：

-- 在创建数据表时语法如下：
constraint <外键名> foreign key (从表外键字段) references <主表> (主键字段)
-- 将创建数据表创号后语法如下：
alter table <从表名> add constraint <外键名> foreign key (从表外键字段) references <主表> (主键字段);

示例：

-- 创建一个学生表
create table student05(
id int primary key,
name varchar(20)
);

示例：

-- 创建一个班级表
create table class(
	classid int primary key,
	studentid int
);

示例：

-- 学生表作为主表，班级表作为副表设置外键，
alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

6.1 外键的作用

• 保持数据一致性、完整性。
• 主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
• 最直观的作用是防止非法数据录入，就是说录入外键数据时必须在它所属的主表中存在相应数据，如果录入空字符或其他数据会报错。

大家知道：建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

6.2 删除外键

语法：

alter table <从表名> drop foreign key <外键名>；

示例：

-- 删除外键