SQL语句

一、SQL概述

1、SQL介绍

结构化查询语言（Structured Query Language）简称SQL，是关系型数据库管理系统都要遵循的规范，是数据库认识的语句，不同的数据库产商都支持SQL语句，但都有其特点。

举例：

普通话：各数据库厂商都遵循的ISO标准。

方言：数据库特有的关键字。

2、SQL语句分类

DDL：

数据定义语言：简称为DDL（Data Definition Language)

用来定义数据库对象：数据库、表、列等

关键字：create、alter、drop等

DML：

数据操作语言：简称DML（Data Manipulate Language）

用来对数据库中表的记录进行更新

关键字：insert、delete、update等

DQL：

数据查询语言：简称DQL（Data Query Language）

用来查询数据库中表的记录

关键字：select、from、where等

DCL：

数据控制语言：简称DCL（Data Control Language）

用来定义数据库的访问权限和安全级别，以及创建用户。

3、SQL基本语法

① SQL语句可以单行或多行书写，以分号 ; 结尾

select * from students;

② 可使用空格或缩进来增强语句的可读性

select 
	*
from students;

③ MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

SELECT * FROM students;
等价于
select * from students；

④ 可以使用单行或多行注释

#  单行注释
-- 单行注释，注意：--后面有一个空格
/*
	...
	多行注释
	...
*/

4、小结

结构化查询语言（SQL），是关系型数据库管理系统都需要遵循的规范，。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

SQL语句分为：数据定义语言（DDL），数据操作语言（DML），数据查询语言（DQL），数据控制语言（DCL）。

SQL通用语法：结构灵活，使用分号结尾；不区分大小写；可以使用/**/，#，--来进行注释。

二、DDL数据库操作

1、MySQL的组成结构

注：我们平常说的MySQL，其实主要指的是MySQL数据库管理软件。

一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库、商城项目shop数据库、微信项目wechat数据库。

一个数据库中还可以同时包含多个数据表，而数据表才是真正用于存放数据的位置。（类似我们Office软件中的Excel表格），理论上一个功能就对应一个数据表。如博客系统中的用户管理功能，就需要一个user数据表、博客中的文章就需要一个article数据表、博客中的评论就需要一个message数据表。

一个数据表又可以拆分为多个字段，每个字段就是一个属性。

一个数据表除了字段以外，还有很多行，每一行都是一条完整的数据（记录）。

2、数据库的基本操作

① 创建数据库

创建 + 数据库 = create database 数据库名称（字母 + 数字+ 下划线）以字母开头

# create database 数据库名称 [设置编码格式];
create database students default charset utf8;

• 注意：在MySql中，当一条SQL语句编写结束后，一定要使用分号;结尾，否则系统认为这条语句还没结束

案例：

-- 1、创建数据库
# ① 只创建数据库，使用默认的编码格式
create database students;
# ② 创建数据库使用指定的编码格式
create database user default charset utf8;
# ③ 创建数据库之前先判断数据库是否存在 (if not exists)
create database if not exists students default charset utf8;

• 默认使用latin1编码格式，gbk（中国）、utf8（国际通用）

② 查询数据库

基本语法：

-- 2、查看数据库（显示所有数据库）
# ① 查看所有的数据库
show databases;
# ② 查看数据库的创建过程
show create database 数据库名称;

③ 删除数据库

基本语法：

drop database 数据库名称

案例：

-- 3、删除数据库(数据库的名称不能修改)
# 慎重，这个过程是不可逆的
drop database students;
drop database user;

• 删除数据库的过程是不可逆的，慎重使用

④ 选择数据库

从数据库列表中查找需要使用的数据库

格式：

use students;

查看正在使用的数据库（8.0以后版本需要基于select查询来获取当前数据库）

 select database();

三、DDL数据表操作

1、数据表的基本操作

（1） 数据表的创建

创建 + 数据表 = create table 数据表名称

基本语法：

-- 6、创建数据表
create table tb_user(
    字段名称 字段类型 [字段约束],
    字段名称 字段类型 [字段约束],
    字段名称 字段类型 [字段约束]  最后一个不需要加逗号结束

) engine = innodb # 使用的引擎
  default charset utf8; # 使用的编码格式

整数类型: int
小数类型: decimal(数据总长度,小数后保留几位) => decimal(5,2) => 999.99
字符串类型: 255个字符以内使用char/varchar，char固定长度、varchar变化长度、大于255个字符一般使用text文本
日期类型数据: data类型（年-月-日） time类型（小时:分钟:秒） datatime类型（年-月-日 小时:分钟:秒）

• 注意：创建表必须有一个前提，首先要明确使用哪个数据库use 数据库名称

案例1：

# 创建一个会员表 id编号、username用户名、password
use students; # 使用students数据库
create table tb_user(
    id tinyint,
    username varchar(20),
    password char(32)
)engine=innodb default charset=utf8;

• tinyint：微整形，范围-128~127，无符号类型，则表示0 ~ 255

案例2：

# 在user数据库中创建一个article文章表，拥有四个字段（编号、标题、作者、内容）
use user;
create table tb_article(
    id int,
    title varchar(50),
    author varchar(20),
    content text
)engine=innodb charset=utf8;

• text文本类型，一般情况下，用varchar存储不了的字符串信息，都建议使用text文本进行处理

varchar存储的最大长度，理论是65535个字节（字符）。但是实际上，有几个字符是用于存放内容的长度的，所以真正可以使用的不足65535个字符，另外varchar类型存储的字符长度还和编码格式有关。一个gbk格式占用2个字节长度，一个utf8格式占用3个字节长度。GBK = 65532~65533/2，UTF8 = 65532~65533 / 3

（2）查询已创建数据表

显示所有数据表：

use students; # 使用当前数据库
show tables; # 展示数据表

查看表的创建过程（编码格式、引擎，字段）：

use students;
# 查看tb_user 表的创建过程
show create table tb_user;

查看表的字段信息：

# 查看tb_user表的字段信息
describe tb_user;
# 一般使用describe缩写
desc tb_user;

反撇号``与双引号的区别：

反撇号``: 主要用于标注数据库的名称、数据表名以及字段名称 = > 把反撇号中的内容当作普通文本来处理

双引号' ' : 主要用于数据的增删改查中，用于表示文本型的数据

• 在创建数据库中，如果想使用中文或关键字可以使用反撇号中文

（3）修改数据表信息

① 数据表字段添加

基本语法：

alter table 数据表名称 add 新字段名称 字段类型 first|after 表中字段名称;
选项说明：
first : 把新字段放在第一位
alter : 把新字段放在指定字段后面

案例： 在tb_user表中添加一个 addtime 新字段，类型为date（年-月-日）

use students;

# 把新添加的字段放在第一位
alter table tb_user
    add addtime date first;
    
# 把新添加的字段password字段后面
alter table tb_user
    add addtime date after password;

② 修改字段的名称或字段的类型

• change ： 既可以修改字段的名称，也可以修改字段的类型
• modify ： 只能修改字段类型

基本语法：

alter table 数据表名称 change 原数据表名称 修改后数据表名称 修改或不变数据类型;
alter table 数据表名称 modify 数据表名称 修改后的数据类型;

案例：

# 把tb_user表中的username字段名称修改为user，类型改为varchar(40)
alter table tb_user change username user varchar(40);

# 将tb_user表中 user 字段的类型修改为varchar(20)
alter table tb_user modify user varchar(20);

desc tb_user;

③ 删除某个字段

基本语法：

alter table 数据表名称 drop 要删除字段名称;

案例：

alter table tb_user drop addtime;
desc tb_user;

④ 修改数据表的名称

基本语法：

rename table 旧名称 to 新名称;

案例：

# 数据表的重命名操作 将tb_user 重命名为tb_member
rename table tb_user to tb_member;

⑤ 数据表的删除

基本语法：

drop table 要删除的数据表名称

案例：

drop table tb_member;
show table;

2、字段类型详解

① 整数类型

unsigned 无符号型，代表只有整数，没有负数的情况，tinyint unsigned 表示 0 ~ 255

• 更大的范围使用bigint，再大的范围只能使用浮点数（float）了

② 浮点类型

浮点类型（精度失真情况）和定点类型（推荐使用定点类型）底层使用文本保存，故不会失真。

decimal(10 ,2 )代表总长度为10 = 整数长度 + 小数长度，2代表小数点后保留2位

③ 日期类型

日期类型的选择主要看格式

④ 文本类型

255个字符以内，固定长度使用char（字符长度），变化长度使用varchar（字符长度）

假设：有一个数据，一共有5个字符，char(11), 实际占用11个字符，相同长度数据，char类型查询要比varchar类型要快（因为varchar类型还要查询存储的实际长度）；变化长度varchar(11)，实际占用5个字符长度。

超过255个字符，选择text文本类型。

四、DML数据操作语言

1、数据的增加操作

基本语法：

insert into 数据表名称([字段1，字段2，字段3，...]) values (字段1的值,字段2的值,字段3的值...)(...);

首先准备一个数据表：

use students;
# 创建一个学生表tb_student,表中包含id编号，name姓名、age年龄、gender性别、address住址
create table tb_student
(
    id      int,
    name    varchar(20),
    age     tinyint unsigned,
    gender  enum ('男', '女'),
    address varchar(255)
) engine = innodb  default charset = utf8;

• enum():枚举类型，多选一，从多个选项中选择其中一个

使用insert语句插入数据：

# 向tb_student表中一一对应插入单条数据
insert into tb_student values (6884, '七落', 23, '男', '广州天河');

# 向tb_student中插入指定字段的数据
insert into tb_student(id, name, age) values (6883, '莫航', 22);

批量插入数据：

# 向tb_student表中批量插入多条数据
insert into tb_student values (6882, '马瑨', 22, '男', '广州番禺'), (6880, '刘谦', 21, '男', '广州花都');
select * from tb_student;

2、数据的更新操作

基本语法：

update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,...[where 更新条件];

• 特别说明，如果在更新数据的时候，不指定更新条件，则其会把这个数据表的所有记录全部更新一遍

案例：

# 把tb_student中的莫航的gender更新为 男
update tb_student set gender= '男' where id=6883;

# 把莫航的年龄增大一岁，地址修改为广州海珠
update tb_student set age = age + 1,address = '广州海珠' where id = 6883;

# 把所有人的年龄都添加1岁
update tb_student set age = age+1;

select * from tb_student;

3、数据的删除操作

基本语法：

delete from 数据表名称 [where 删除条件];

• 这里删除的是一个对象，或个体

案例：

# 删除name = 莫航这条数据
delete from tb_student where name = '莫航';

清空数据表：delete 或 truncate

delete from 数据表名;
或
truncate 数据表名;

truncate tb_student;

delete与truncate的区别在哪里呢？

• delete ：删除数据记录

  • 数据操作语言（DML）
  • 删除大量记录速度慢，只删除数据，主键自增序列不清零
  • 可以带条件删除=

• truncate : 删除所有数据记录

  • 数据定义语言（DDL）
  • 清除大量数据速度快，主键自增序列清零
  • 不能带条件删除

五、SQL约束

1、主键约束

1. PRIMARY KEY 约束唯一标识数据库表中的每条记录。
2. 主键必循包含唯一的值
3. 主键列不能含有NULL值
4. 每个表中都必须含有一个主键，并且每个表只能有一个主键（有且仅有一个主键）

遵循原则：

1）主键应当是对用户没有意义的
2）永远也不要更新主键。
3）主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
4） 主键应当由计算机自动生成。

1）主键应当是对用户没有意义的
2）永远也不要更新主键。
3）主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
4） 主键应当由计算机自动生成。

① 创建主键约束：创建表时，在字段描述处，声明指明字段为主键。

创建方式1：

# ①主键有且仅有一个 ② 主键列的值不能为NULL ③ 主键的值必须是唯一的
use user;
create table tb_article1(
    id int primary key, # 主键约束
    title varchar(50),
    author varchar(20),
    content text
)engine=innodb default charset=utf8;

创建方式2：

create table tb_article2(
    id int ,
    title varchar(50),
    author varchar(20),
    content text,
    primary key (id)
)engine=innodb default charset=utf8;

# 查看主键约束，desc不仅可以查看表结构，有哪些字段，每个字段都是什么类型，是否为空，是否为主键
desc tb_article2;

② 数据表主键的添加与删除方式：

# 删除数据表主键
alter table tb_article2 drop primary key ;

# 如果数据表存在，添加主键的方式
alter table tb_article2 add primary key(id);

③ 自动生长列：

我们通常希望每次在插入新记录时，数据自动生成字段的值。

我们可以在表中使用auto_increment(自动增长列) 关键字 ，自动增长列必须是 整形，自动增长列必须是键(一般是主键)

使用auto_increment自动生成列，并设定为主键

# 要求设置字段 id 为主键并且自动增长
use user;
create table tb_member(
    id int auto_increment primary key,
    name varchar(20),
    password varchar(32)
)engine=innodb default charset=utf8;

desc tb_member;

先tb_member表中添加数据，可以不为id设置值，也可以设置为NULL，数据库将自动维护主键值

# 密码使用MD5()加密数据
# ① 不输入id的值
insert into tb_member(name, password) values ('test1', md5('12345'));
# ② 设置id的值为空
insert into tb_member values (null,'test2',MD5('123444'));

select * from tb_member;

删除带自动增长的主键：先修改字段类型，去掉自动增长auto_increment

不能直接删除主键primary key 会报错，因为自动增长列必须有键

# 删除带有自动增长的主键，首先要移除auto_increment
alter table tb_member modify id int;

# 删除主键
alter table tb_member drop primary key;
desc tb_member;

2、非空约束

非空约束——强调某一列不允许设置为空值

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

# 创建一个商品表，其中商品名称和价格设置不能为空值
create table tb_goods(
    id int auto_increment primary key,
    name varchar(40) not null,
    price decimal(11,2) not null
)engine= innodb default charset=utf8;

desc tb_goods;

3、唯一约束

UNIQUE约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY拥有自动定义的UNIQUE约束

unique约束：

1. 唯一约束：要求此列每一个值都必须是唯一的（不能重复）
2. 唯一约束：要求此列可以为空（允许多个为空）
3. 唯一约束：在一个表中可以同时存在多个！

第二、三点是与 primary key主键约束的不同点

# 创建一个person信息表
create table tb_person(
    id int unique ,  # 唯一约束
    name varchar(20) not null,
    mobile char(11) unique not null , # 唯一约束，且不能为空
    address varchar(50)
)engine=innodb default charset=utf8;

desc tb_person;

4、默认值约束

默认值约束：就是让我们的某一列在插入的过程中拥有默认值

案例：性别字段设置默认值 为男

create table tb_student
(
    id      int auto_increment primary key,
    name    varchar(20),
    age     tinyint unsigned,
    gender  enum ('男', '女') default '男', # 设置默认值为 男
    address varchar(255)
) engine = innodb
  default charset = utf8;

desc students.tb_student;

添加数据的几种方式：

insert into students.tb_student values(null,'七落','20',default,'广州天河');

insert into students.tb_student(id,name,age,address) values(null,'莫航',19,'广州海珠');

# 不使用默认值
insert into tb_student values(null,'张薇',20,'女','广州花都');

select * from students.tb_student;

5、外键约束

外键约束（多表关联使用）

比如：有两张数据表，这两个数据表之间有联系，通过了某个字段可以建立连接，这个字段在其中一个表中是主键，在另外一张表中，我们就把其称之为外键。

六、DQL数据查询语言

五字句查询

select * from 数据表 [where子句] [group by分组子句][having子句][order by 子句][limit 子句]

① where子句
② group by分组子句
③ having子句
④ order by子句
⑤ limit 子句
注意：在以上5个子句中，五子句的顺序不能颠倒！

1、数据集准备

CREATE TABLE product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32)
);

2、select查询

# 根据某些条件从某个表中查询指定字段的内容
格式：select [distinct]*| 列名,列名 from 表where 条件
# * 代表查询所有的列

distinct：针对某列进行去重操作

select category_id from product ;

# 进行去重操作
select distinct category_id from product;

• 这里效果和group by分组是一样的，所以说分组查询也可以达到去重的效果

3、简单查询

# 1.查询所有的商品.
select * from product;
# 2.查询商品名和商品价格.
select pname, price from product;
# 3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname, price + 10 from product;

4、条件查询（where子句）

• 比较查询中 <> 等价于 ！=
• 范围查询中 between and 语句显示在某一区间中的值，头尾都包含[] in 后面接的是一个集合(set)
• 模糊查询中 关键字like 两个字符 ① _ 下划线代表任意一个字符， ② % 代表任意长度的字符
• 非空查询不能使用= ，只能使用is null 或 is not null
• not(条件语句) 代表取条件相反的值

① 比较查询

# 查询商品名称为“花花公子”的商品所有信息：
select *  from product where pname = '花花公子';
# 查询价格为800商品
select * from product where price = 800;
# 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
# 查询商品价格大于60元的所有商品信息
select * from product where price > 60;
# 查询商品价格小于等于800元的所有商品信息
select * from product where price <= 800;

② 范围查询

# 查询商品价格在200到1000之间所有商品
select * from product where  price between 200 and 1000;
# 查询商品价格是200或800的所有商品
select * from product where price in (200, 1000);

查询价格为200或1000的商品：

• 注意between and 语句遵循一个从小到大的范围

③ 逻辑查询

# 查询商品价格在200到1000之间所有商品
select * from product where price > 200 and price < 1000;
# 查询商品价格是200或800的所有商品
select * from product where price = 200 or price =1000;
# 查询价格不是800的所有商品
select * from product where price >800 or price < 800;
select * from product where not (price = 800);

④ 模糊查询

关键字 like 两个字符 _ 和 %

① _ 下划线：代表匹配任意某个字符（只能匹配1个字符）

② % 百分号：代表匹配任意多个字符（匹配的字符没有数量限制）

# 查询以'香'开头的所有商品
select * from product where pname like '香%';
# 查询第二个字为'想'的所有商品
select * from product where pname like '_想%';

⑤ 非空查询

需要注意：在非空查询中，不能使用= ，只能使用 is null 和 is not null

# 查询没有分类的商品
select * from product where category_id is null;
# 查询有分类的商品
select * from product where category_id is not null;

5、聚合查询

上述所做的查询都是横向查询，它们是都是根据条件一行一行的进行判断，而使用聚合函数查询都是纵向查询，它是对一列的值进行计算，然后返回单一的值；特别注意聚合函数会忽略空值

聚合函数：

案例分析：

# 1、查询商品的总条数
select count(pid) from product; # pid是主键，不可能为空
select count(*) from product; # * 代表所有列的汇总
# 2、查询价格大于200商品的总条数
select count(price) from product where price > 200; # where子句优先级更高
# 3、查询分类为'c001'的所有商品价格的总和
select sum(price) from product where category_id = 'c001';
# 4、查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';
# 5、查询商品的最大价格和最小价格
select max(price),min(price) from  product;

• 注意：count（）函数不统计空值

6、分组查询(group by)与having子句

① SQL分组查询的基本语法：

SELECT 列1[, 列2, 聚合函数(列3)]
FROM 表名
GROUP BY 列1[, 列2(字段2)]
[HAVING 条件]
[WITH ROLLUP]

• 特别说明：在MySQL5版本及以后，group by分组字段必须出现在select 语句中，否则会自动报错

说明：

• SELECT 语句：用于选择要显示的列，可以是分组列、聚合函数或其他列。
• FROM 子句：指定要查询的表名。
• GROUP BY 子句：指定一个或多个列，用于分组数据。查询的结果将按照这些列的值进行分组。
• 聚合函数：对于每个分组，可以应用一个或多个聚合函数（如 SUM、AVG、COUNT、MAX、MIN 等）来计算汇总值。
• HAVING 子句：可选的，用于筛选分组后的结果。只有符合条件的分组才会被包括在结果中。
• WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

通过性别分组的演示：（先分组再统计）

group by可以用于单个字段分组，也可以用于多个字段分组：

-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;

• group by分组单独使用的作用或意义并不大

② group by + 聚合函数的使用：

-- 统计不同性别的人的平均年龄
select gender,avg(age) from tb_student group by gender;
-- 统计不同性别的人的个数
select gender, count(*) from tb_student group by gender;

• 分组在实际工作中就是为了更好实现数据统计（先分组再统计）

③ group by + having子句的使用：

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

-- 根据gender字段进行分组，统计分组条数大于1的
select gender,count(*) from tb_student group by gender having count(*)>1;

having子句与where的区别：

• ① having是在分组后对数据进行过滤（分组后） ，where是在分组前对数据进行过滤（分组前）
• ② having后面可以使用分组函数（聚合函数) ，where后面不可以使用分组函数
• ③ where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束

对于没有分组查询中，having也代替where子句使用：

select gender, name from tb_student having gender = '男';

案例演示：

#1 统计各个分类商品的个数
select category_id , count(*) from product group by category_id;

#2 统计各个分类商品的个数,且只显示个数大于1的信息
select category_id,count(*) from product group by category_id having count(*) > 1;

#3 统计哥哥分类商品的个数，且只显示个数大于1的商品信息
select category_id,count(*) , group_concat(pname) from product group by category_id having count(*) >1;

④ group_concat()关键字

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

# 以性别分组，将name字段的值打印在一行，默认使用逗号分隔
select gender, group_concat(name) from tb_student group by gender;

⑤ 回溯统计 （with rollup）

回溯统计：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

比如：在统计学生表中男女同学的人数信息的同时，我还想统计学生表中的总数据量

select gender,count(*)  from tb_student group by gender with rollup ;

7、排序查询（order by）

通过 order by 语句，可以将查询出来的结果进行排序。要放在having子句的后面。

基本语法：

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

• ASC：升序默认
• DESC：降序

# 1.使用价格排序(降序)
select * from product order by price desc;
# 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc, category_id desc;

• 首先按照第一个字段进行排序，在第一个字段值相同的情况下，再对第二个字段进行排序

8、limit 分页查询

分页查询在项目开发中很常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

limit子句属于MySQL的方言

基本语法：

SELECT 字段1，字段2... FROM 表名 LIMIT M,N
M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据

select * from 数据表 limit N; # 代表只查询数据表中的前N条数据，常常配合order by使用

select * from 数据表 limit M,N; # M代表偏移量(索引)，默认从0开始；N代表从M开始，查询N条记录（数据

案例：

# 查询价格最高的前三条商品记录
select * from product order by price desc limit 3;

分页查询原理：

-- 假设我们有11条数据，每页显示2条数据
-- 一共有多少页？6页
-- 第一页数据: 第1条和第2条 =》select * from 数据表 limit 0, 2;
-- 第二页数据: 第3条和第4条 =》select * from 数据表 limit 2, 2;
-- 第三页数据: 第5条和第6条 =》select * from 数据表 limit 4, 2;
-- 第四页数据: 第7条和第8条 =》select * from 数据表 limit 6, 2;

-- 第n页数据：select * from 数据表 limit (n-1)*pagesize , pagesize;
-- 分页公式 ： select * from 数据表 limit (当前页数-1)*每页显示数量, 每页显示数据;

# 案例：每页查询2条数据，求第5页查询的SQL语句
select * from product limit 8,2;

七、多表查询

1、交叉连接

交叉连接意义并不大，但是它是所有连接的基础。其功能就是将表1和表2的每一条数据进行连接。

结果：

字段数 = 表1字段 + 表2字段

记录数：表1中的总数量 * 表2中的总数量（笛卡尔积）

笛卡尔积：
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：

两个集合相乘，不满足交换率，即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

基本语法：

select *[字段名] from 表1 cross join 表2 [where子句]
或
select *[字段名] from 表1, 表2 [where子句]
# 多个表交叉连接时，在from后面连续使用cross join 或 ,即可

• 当连接的表没有关系的时候，我们会忽略WHERE子句，这时返回的就是两个表的笛卡尔积

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积：

# 交叉连接
select * from tb_goods cross join tb_classes;

• 多表查询遵循的算法就是笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应该避免使用笛卡尔积，因为笛卡尔积中容易存在大量不合理数据，简单来说就是容易导致查询结果重复、混乱。

2、内连接查询

内连接与外连接就是在交叉连接的基础上，去除大量不合理数据而获取我们想要的数据

• 比如在两表中的cid（商品类别）相同的数据并不冗余，是合理的数据

内连接：查询两个表中符合条件的共有记录

• 内连接根据连接查询的条件取出两个表的“交集”

内连接查询语法格式：

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段1;

说明：

• inner join ：内连接的关键字
• on ：连接查询的条件

案例：

select * from tb_goods inner join tb_classes on tb_classes.cid = tb_goods.cid;

# 在多表查询中，由于字段较多，并不推荐使用*查询，而是指定我们想要读取的字段查询
select id,title,price,tc.cid,name 
	from tb_goods as tb 
		inner join tb_classes as tc on tb.cid = tc.cid;

• 内连接查询就是在交叉连接查询的基础上增加了一个关联条件，查询结果只保存了关联条件的内容。

扩展关键字：as（起别名）

as：关键字可以为数据表或字段起别名，以后的数据筛选都可以通过别名实现。

select id,title,price,tc.cid,name from tb_goods as tb inner join tb_classes tc on tb.cid = tc.cid;

# 起别名，as关键字可以忽略，直接一个空格即可（可以使用中文，不推荐）
select id as 编号, title 标题, price 价格 from tb_goods;

内连接查询属于默认的多表查询方式，如果在多表查询中，直接使用join关键字，则代表使用的也是inner join内连接查询。

select * from tb_goods tg join tb_classes tc on tg.cid = tc.cid;
# 效果等同
select * from tb_goods tg inner join tb_classes tc on tg.cid = tc.cid;

多表连接的标准写法：（添加缩进和换行提高可读性）

select
    id,
    title,
    price,
    tg.cid,
    name
from tb_goods tg
inner join tb_classes tc
    on tg.cid = tc.cid;

内连接的另外一种写法：

# select *  from 表A,表B where 表A.字段1 = 表B.字段1;
select id,
       title,
       price,
       tg.cid,
       name
from tb_goods tg,
     tb_classes tc
where tg.cid = tc.cid;

3、外连接查询

内连接查询只会查询满足关联条件的结果。

外连接查询有主表这样的一个概念，默认情况下会返回主表中的所有数据，不管其是否满足关联条件。

外连接查询又分为两种情况：

左外连接查询（语法左边数据表就是主表）：查询结果会保留主表中的所有数据，然后去右边表进行匹配，如果有与之匹配的结果则显示，如果没有与之匹配的结果，则右表关联字段设置为NULL！

右外连接查询（语法右边数据表就是主表）：查询结果会保留主表中的所有数据，然后去左边表进行匹配，如果有与之匹配的结果则显示，如果没有与之匹配的结果，则左表关联字段设置为NULL！

① 左外连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在则使用null值填充

左连接查询基本语法：

select 字段 from 表1 left [outer] join 表2 on 表1.字段1 = 表2.字段2

说明：

• left join : 左连接查询的关键字（outer可以忽略）
• on ：连接查询条件（关联条件）
• 表1：左表
• 表2：右表

案例：

# 左外连接查询：要求查询商品表中每一个商品所属分类名称，即使商品没有分类也要显示出来
select 
	id,title,
	price,
	tg.cid ,
	name 
from tb_goods tg 
left join tb_classes tc 
	on tg.cid = tc.cid;

• 要显示所有的商品，主表要选择商品表

② 右外连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

右连接查询语法格式:

select 字段 from 表1 right [outer] join 表2 on 表1.字段1 = 表2.字段2

说明:

• right join 就是右连接查询关键字
• on 就是连接查询条件
• 表1 是左表
• 表2 是右表

案例：

# 右外连接查询：要求查询商品表中每一个商品所属分类名称，即使商品没有分类也要显示出来
select
    name,
    tc.cid,
    id,
    title,
    price
from tb_classes tc
right join tb_goods tg on tc.cid = tg.cid;

• 要显示所有的商品所属分类，主表要选择商品表

左外连接与右外连接的不同在于参照不一样

③ 全连接(full join)

MySQL暂不支持这种语句，不过可以使用union将两个结果集堆在一起，利用左、右连接分两次将数据取出，然后使用union将数据去重。

select id,title,price,tg.cid ,name from tb_goods tg left join tb_classes tc on tg.cid = tc.cid
union
select id,title,price,tg.cid ,name from tb_goods tg left join tb_classes tc on tg.cid = tc.cid;

4、内连接与外连接的区别

内连接只会保留满足关联条件的数据，而外连接中，主表不管是否满足关联条件都会被保留，没有满足匹配结果的其字段设置为空值。

注意：如果两张表中，其中一张数据表在另外一张数据表中都有与之对应的结果，则内连接和外连接两种查询方式产生的效果是一致的。

5、自连接查询

自连接是指使用表的别名实现表与自身连接的查询方法。

自连接：数据表自己连接自己，前提：连接操作时必须为数据表定义别名！

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。

准备数据集：tb_area表（地域表）

pid（parent id）代表父级id，如果pid为null则代表本身就是父级，如果pid是一个具体的数值，则代表其属于子级。

什么情况下应该使用自连接查询？

答：其实就是一张表中，数据与数据之间存在层级关系

理解一下这个过程，查看一下自身表的交叉连接：

select * from tb_area city cross join tb_area province;

• 由此观察，查询条件可以为 city.pid = province.aid 或city.pid = 1 and province ='广东省'

使用自连接查询：

select 
	city.aid,
	city.atitle,
	province.atitle
from tb_area city 
inner join tb_area province 
	on city.pid=province.aid
	[and|where province = ’广东省]; # 在on 语句后面可以接and或where

6、表与表之间的关系

在SQL语句中，如果想使用多表查询的前提必须是两张表甚至多张表必须有关联关系。在实际业务中，表与表之间通常有3种关系：

① 1 对 1关系

所谓的1对1关系就是在数据表A中的每一条记录在数据表B中都有一个与之对应的结果。、

举个例子：比如用户表与用户详情表

② 1 对 多关系

• 从分类表的角度来看，这是1对多关系，在分类表中的每一条数据在商品表中有多个数据与之对应
• 从商品表的角度来看，这是1对1关系，在商品表中的每一条数据在分类表中都有与一条数据之对应

③ 多 对 多 关系

• 多对多关系，建立临时表，通过临时表查询数据

八、外键约束（foreign key）

外键：foreign key 外键是用来让两张表的数据建立连接，从而保证数据的一致性和完整性。

原则：在一张数据表中，其是主键。但是在另外一张数据表中，其是从键（非主键），但是这个字段是两个表的关联字段，我们把这个字段称为外键。

1、外键约束作用

外键约束：对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入失败，保证数据的有效性。如果合法就会建立关联关系，当我们在外键所在主键的表中进行操作时，另外一个表中的数据也会收到关联。

MySQL的外键约束用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。

其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。

外键约束主要作用就是能够让表与表之间的数据建立关联，使数据更加完整，关联性更强。

关于完整性，可以通过一个例子来说明：

假如有两种表，一张用户账户表（用于存储用户账户），一张是账户信息表（用于存储账户中的信息）。

1）我不小心将用户账户表中的某个用户删除了，那么账户信息表中与这个用户有关的数据就变成无源数据了，找不到其属于哪个用户账户，导致用户信息不完整。

2）我在账户信息表中随便添加了一条数据，而其在用户账户表中没有对应的用户，这样用户信息也是不完整的。

如果有了外键约束，将用户账户表设为主表，账户信息表设为从表，那么就无法直接往账户信息表中插入数据，在用户账户表中删除用户，账户信息表中的用户信息也会被删除。外键约束的方式可以使两张紧密的联系在一起，保障数据完整性和一致性的同时，日常维护也更加方便。

再举个例子：

dage表(父表）：

bro表（子表）：

外键设置原则：保证两张表的关联关系，保证数据的一致性。在选择时，一般在一个表中是关联字段，在另外一个表中是主键，则这个字段建议设置为外键。

• 保证数据的一致性：比如有一天乌鸦哥被干掉了，那么在bro表中乌鸦的小弟也会被删除掉

2、外键约束的使用

设置外键：

create table 数据表(
    字段名称 字段类型 [字段约束],
    [constraint] [外键名称] foreign key(外键字段) references 主表(主表列名)
    )engine = innodb;

• 不设置外键名称的情况下，系统会自动设置一个外键名称，可以通过show create table 数据表查看

参数说明：

• constraint：用于设置外键约束名称，可以省略
• foreign key ：外键设置，用于指定外键字段
• references ： 主表及主键设置，用于指定主键和主表

添加外键：

alter table 数据表 add [constraint `外键名称`]foreign key (外键字段) references 数据表(主键)
[on delete cascade| set null] [on update cascade | set null];
# on delete 代表删除时
# on update 代表更新时

cascade：级联，代表多个对象之间的映射关系

set null ： 设置为空值

删除外键：

alter table 表名 drop foreign key 外键名称;

删除或更新属性：

语法：

alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名)
on update cascade|set null on delete cascade | set null;

• 表示设置在更新时(on updata) cascade，在删除时(on delete) cascade

案例：

create table class(
xuehao int primary key,
name varchar(6))engine=innodb;

create table students(
id int auto_increment primary key,
uid int not null,
name varchar(6) not null,
foreign key(uid) references class(xuehao)
on update cascade on delete cascade)engine=innodb;

insert into class values(111,'张三'),(222,'李四'),(333,'王五');
insert into students values(1,111,'张三'),(2,222,'李四'),(3,333,'王五');

select * from class;
select * from students;

class（主表）：

students（从表）：

# 验证外键约束的特性 ，删除从表中的id为记录
delete from students where id = 1;
select * from class;
select * from students;
# 结论1：从表中删除删除记录不会对主表造成影响（从删主不删）

# 删除主表中 学号为111的记录
delete from class where xuehao = 222;
select * from students;
# 结论2： 主表的修改根据属性会对从表造成影响（主删从不见）

insert into students value (4,444,'赵六');
# 结论3：无法插入主键关联不存在的记录（class表中的学号字段没有444字段，所以与之关联的students表uid为444的记录无法插入。只有主表存在，从表才可插入，解决方案就是给主表插入xuehao为444的字段

# 查看外键名称
show create table students;
# 删除外键
alter table students drop foreign key students_ibfk_1;
# 在创建foreign key的时候使用constraint 命名一个外键名可以方便操作

• 注意：外键设置必须要使用innodb引擎

查看引擎：

show engines;

九、子查询

1、子查询（嵌套查询）

在一个select查询语句中，嵌入了另外一个select语句，那么被嵌套的select语句被称为子查询语句，外部那个select语句则被称为主查询。

select * from (select * from 数据表) as A;

主查询和子查询的关系：

1. 子查询嵌套在主查询中
2. 子查询是辅助主查询的，要么充当条件（where等），要么充当数据源（需要起别名）
3. 子查询是可以独立存在的查询语句，是一条完整的select查询语句

什么时候使用到子查询？就是当我们的查询语句需要使用到另外一个SQL查询时，就可以使用子查询。

2、子查询的使用

例1. 查询学生表中大于平均年龄的所有学生:

需求：查询年龄 > 平均年龄的所有学生

前提：① 查询所有学生的平均年龄

 ② 查询表中的所有记录，判断哪个同学 > 平均年龄值

第一步：写子查询

select avg(age) from students;

第二步：写主查询

select * from students where age > (平均值);

第三步：第一步和第二步进行合并

select * from students where age > (select avg(age) from students);

例2. 查询出所有具有商品的分类名称

需求分析：

第一步：编写子查询（查询商品表中有哪些分类（cid）还要去掉重复的）

select distinct cid from tb_goods;

第二步：编写主查询（判断分类表中的分类是否出现在商品表中）

select * from tb_category where cid in (cid分类列表);

第三步：把主查询和子查询合并

select * from tb_category where cid in (select distinct cid from tb_goods);

例3. 查找年龄最小,成绩最低的学生:

第一步：获取年龄最小值和成绩最小值

select min(age), min(score) from student;

第二步：查询所有学员信息（主查询）

select * from students where (age, score) = (最小年龄, 最少成绩);

第三步：把第一步和第二步合并

select * from students where (age, score) = (select min(age), min(score) from students);

本栏目推荐文章
• SQL Server 检测是不是数字型数据的两种方法
• Python教程(23)——Python类中常用的特殊成员
• SQL Join的一些总结
• SQL Server 高性能写入的一些总结
• 网络攻击技术开篇——SQL Injection
• 50个常用的Qt开源库
• GitHub常用搜索技巧
• JS常用工具：Webpack 和 JShaman分别有什么用？
• 4- if 流程语句和案例
• SQL SERVER日期时间转字符串

语句 常用 大全 SQL语句 常用 大全sql 语句 常用server sql 语句 常用sql 备忘录 语句 常用sql 语句sql手工 大全 语句sqlserver大全 小结 语句 常用sql 语句sqlsever大全 函数 大全sql 语句sql