MySQL基础 DDL DML DQL DCL

黑鹿022

已于 2025-05-14 11:57:27 修改

阅读量627

点赞数 23

文章标签： mysql 数据库

于 2025-05-13 21:27:16 首次发布

本文链接：https://blog.csdn.net/swu_Halo/article/details/147920147

版权

数据库基本概念

数据库就是用来存储和管理数据的，所谓管理就是对数据进行增删改查。

分类：
1. 关系型（SQL）数据库：mysql oracle
2. 非关系（NOT ONLY SQL）型数据库：redis hbase

可以理解为Mysql是SQL的一种方言(dialect)。

SQL

SQL全称是结构化查询语言(Structured Query Language)，分为四部分：

DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：数据库，表，列等。注意：DDL不涉及对数据表中记录的操作。

DML (Data Manipulation Language)：数据操作语言，用来对数据库中的表记录进行增、删、改。

DQL (Data Query Language)：数据查询语言，用来查询数据库中表的记录。[重点]

DCL (Data Control Language)：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。一般用不到。

本篇按照DDL、DML、DQL顺序讲解。（不包括DCL）。

DDL(Data Definition Language)

数据定义语言，用来定义数据库对象：数据库，表，列等。注意：DDL不涉及对数据表中记录的操作

数据库操作

列出当前所有数据库

# 显示所有数据库
show databases;

创建数据库：
语法：CREATE DATABASE [if not exists] 数据库名称 [charset=‘uft8’];
[ ]中的可以缺省。缺省if not exists后如果将要创建的数据库已经存在，那么会报错，否则会忽略继续执行下一步。

-- 创建数据库
CREATE DATABASE  review;
CREATE DATABASE if not exists  review;
CREATE DATABASE if not exists review charset 'utf8';

使用（切换）指定数据库

USE review;

查看建库语句

-- 查看建库语句
SHOW CREATE DATABASE review;

查看当前使用的数据库

-- 查看当前使用的数据库
SELECT DATABASE();

删除数据库(慎用)

-- 删除数据库（慎用）
DROP DATABASE review;

数据表操作

创建表
语法： create table 表名(
字段名1 类型 [约束],
字段名2 类型 [约束],
…
)

我们创建一个student表，含4个字段。

CREATE TABLE if not exists student(
    id int,
    name varchar(100),
    height decimal(3,2),
    weight float
);

查看当前库下所有的表
show tables;

-- 查看当前数据库内所有表
SHOW tables;

查看表结构
desc 表名;

-- 查看表的结构
DESC student;

结果：
在这里插入图片描述

查看建表语句
show create table 表名;

-- 查看建表语句
SHOW CREATE TABLE student;

结果：
在这里插入图片描述

修改表名
rename table 旧表名 to 新表名;

-- 重命名表名
RENAME TABLE student TO students;

RENAME TABLE students TO student;

删除表
drop table 表名; drop table IF EXISTS test1;

-- 删除表
DROP TABLE student;

对列操作

1.新增列
alter table 表名 add 列名类型 [约束];

ALTER TABLE student add phone_number varchar(20);

在这里插入图片描述

修改列名
alter table 表名 change 旧列名新列名类型 [约束]

ALTER TABLE student change phone_number phone varchar(20);

在这里插入图片描述

修改列类型
alter table 表名 modify 列名新类型 [约束];
原来：

ALTER TABLE student modify phone varchar(100);
ALTER TABLE student modify height decimal(5,2);
DESC student;

现在：
在这里插入图片描述

删除列：
alter table 表名 drop 列名;

ALTER TABLE student DROP phone;

在这里插入图片描述

DML (Data Manipulation Language)

数据操作语言，用来对数据库中的表记录进行增、删、改。

1. 插入数据

现在我们要在刚刚建立的student表中插入数据。
1.1 全字段插入
insert into 表名 values(值1,值2,…);
可以插入一条或者多条，每条数据都包含在一对括号中。

INSERT INTO student
VALUES (1, '张一', 177, 65),
       (2, '张二', 180, 80),
       (3, '张三', 170, 66);

在这里插入图片描述

1.2 指定字符插入
insert into 表名(字段名1,字段名2,…) values(值1,值2,…);
注意：values后面的值要和前面的字段名一一对应。

INSERT INTO  student(id, name) VALUES(4,'李四'),(5,'王五');

在这里插入图片描述

2. 更新操作

格式:update 表名 set 字段名1 = 新值, 字段名2 = 新值,… where 条件;
注意:update一定要加条件，不然会将表中所有的数据全部更新

-- 将张一的名字改为赵六
UPDATE student SET name='赵六' WHERE name='张一';

在这里插入图片描述

-- 将李四的身高体重改为188,90
UPDATE student SET height=188,weight=80 WHERE name='李四';

在这里插入图片描述

删除操作
2.1. 格式:delete from 表名 [where 条件];
2.2. 删除所有数据
delete from 表名;
2.3. 清空数据
truncate [table] 表名;

DELETE FROM student;

DELETE FROM student where id=1;

TRUNCATE student;

DELETE 可以指定条件，删除符合条件的内容，如果不加条件，默认删除表中所有内容。
TRUNCATE也是删除表中所有内容。

拓展：delete 和 drop 和 trucate区别？
1.delete是将表数据清空，但是表保留
2.drop是将表和数据全部删除
delete和truncate区别？
delete清空数据后，会保留原来的主键的id的值，如果后续再新增数据，会根据删除前的id的值+1
truncate清空数据后，不会保留原来的主键的id的值，如果后续再新增数据，会从1开始
truncate可以理解为是：系统帮你执行了drop table 表名，又执行了create table 表名;

约束

约束	特点
主键约束: primary key	修饰列对应的值非空唯一
主键自增: AUTO_INCREMENT	修饰主键对应的值不指定主键字段或者用0和null占位代表自动使用自增
非空约束: not null	修饰列对应的值不能为空
唯一约束: unique	修饰列对应的值不能重复
默认约束: default	修饰列对应的值提前设置默认值

注意1: 约束可以建表时添加(建议),也可以建表后添加(不建议)
注意2: 一个表中主键约束只能有且仅有1个,其他约束可以有多个

我们重新创建一个student表这次添加上约束条件：

# 添加约束
create table student_ys
(
    id     int primary key,
    name   varchar(100) not null unique ,
    height float  not null,
    weight double,
    money  decimal(10, 2) default 0
);

主键约束

关键字:primary key
特点:
1.非空
2.唯一
3.一张表有且只有一个主键
添加
1.alter table 表名 add primary key(字段名);
2.alter table 表名 change 旧字段名新字段名新类型 primary key;
3.alter table 表名 modify 字段名类型 primary key;
4.create table 表名(字段名类型 primary key);
删除
alter table 表名 drop primary key;

# 验证id主键约束
insert into student_ys (id,name,height) values (null,'张三',188.88); # 失败,非空
insert into student_ys (id,name,height) values (0,'李四',188.88); # 成功
insert into student_ys (id,name,height) values (1,'张三',188.88); # 成功
insert into student_ys (id,name,height) values (1,'张三',188.88); # 失败,唯一

非空和唯一约束

非空约束

关键字:not null
特点:
1.一张表可以有多个非空约束
2.值不能为null
添加
1.alter table 表名 change 旧字段名新字段名类型 not null;
2.alter table 表名 modify 字段名类型 not null;
3.create table 表名(字段名类型 not null);
删除
1.alter table 表名 change 旧字段名新字段名类型;
2.alter table 表名 modify 字段名类型 ;
3.alter table 表名 change/modify 字段名类型 null;

唯一约束：

关键字:unique
特点:
1.值唯一
2.一张表可以有多个唯一约束
添加
1.alter table 表名 modify 字段名类型 unique;
2.alter table 表名 change 旧字段名新字段名类型 unique;
3.create table 表名(字段名类型 unique);
删除
1.alter table 表名 drop index 索引名称 ; (注意:暂时可以理解为就是字段名，但是切记不是删除字段)
2.删除字段

-- 验证name非空和唯一约束：
insert into student_ys (id,name,height) values (2,'张三',188.88); # 失败,唯一
insert into student_ys (id,name,height) values (2,null,188.88); # 失败,非空
# 验证height的非空约束
insert into student_ys (id,name,height) values (2,'王五',null); # 失败,非空

默认值约束

关键字:default
特点:
1.一张表可以有多个默认值约束
2.如果插入数据时，可以不指定值，默认填充默认值
添加
1.alter table 表名 modify 字段名类型 default ‘默认值’;
2.alter table 表名 change 旧字段名新字段名类型 default ‘默认值’;
3.create table 表名(字段名类型 default ‘默认值’);
删除
1.alter table 表名 change 旧字段名新字段名类型;
2.alter table 表名 modify 字段名类型 ;

# 验证默认约束
# 上述所有的没有指定money字段,默认就使用默认值0.00
# 但是如果你指定了新的值,就不会使用默认值
insert into student_ys (id,name,height,money) values (2,'王五',188.88,10000000); # 成功,但是没有使用默认值

主键自增

关键字:auto_increment
特点:
1.修饰的字段类型必须是整数类型
2.值的设置是根据上一个主键的值+1
3.一张表只能有一个自增必须是主键
添加
1.alter table 表名 change 旧字段名新字段名类型 auto_increment;
2.alter table 表名 modify 字段名类型 auto_increment;
3.create table 表名(字段名类型 primary key auto_increment);
删除
1.alter table 表名 change 旧字段名新字段名类型;
2.alter table 表名 modify 字段名类型 ;
注意:
1.如果要实现自动增长，可以指定字段插入(不要自增字段)
2.0和null也是会实现自增，属于特殊值

创建一个新的student表，尝试使用主键自增：

create table student_ys_auto_i
(
    id     int primary key auto_increment,
    name   varchar(100),
    height float ,
    weight double,
    money  decimal(10, 2)
);

验证主键自增：

# 查看各个字段约束
desc student_ys_auto_i;
# TODO 验证主键约束自增效果
# 注意: 如果主键添加了自增,那么此时null和0都代表默认使用自增,从1开始每次加1
insert into student_ys_auto_i (id,name) values (null,'张三'); # 成功,默认自增
insert into student_ys_auto_i (id,name) values (0,'李四'); # 成功,默认自增
# 建议: 如果添加了自增,插入数据的时候主键就不用指定,默认使用自增
insert into student_ys_auto_i (name) values ('张三'); # 成功,默认自增

DQL (Data Query Language) 重点学习

数据查询语言，用来查询数据库中表的记录。我们大部分时间使用的就是DQL。

单表查询

格式:select distinct * from 表名;
*:所有字段
distinct:对数据进行去重
as:可以给列和表名起别名
select 和 from之间是要展示的内容
select的结果是一张虚拟表
from:是从哪个表查询

准备工作：
初始化两张表

# 创建表: create table 表名(字段名 字段类型 [约束],...);
# 建测试表
drop table if EXISTS products;
CREATE TABLE IF NOT EXISTS products
(
    id          INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID
    name        VARCHAR(24)    NOT NULL,        -- 商品名称
    price       DECIMAL(10, 2) NOT NULL,        -- 商品价格
    score       DECIMAL(5, 2),                  -- 商品评分，可以为空
    is_self     VARCHAR(8),                     -- 是否自营
    category_id INT                             -- 商品类别ID
);

drop table if EXISTS category;
CREATE TABLE IF NOT EXISTS category
(
    id   INT PRIMARY KEY AUTO_INCREMENT, -- 商品类别ID
    name VARCHAR(24) NOT NULL            -- 类别名称
);

# 插入数据: insert into 表名 (字段名,字段名) values(字段值,字段值),(字段值,字段值);
# 添加测试数据
INSERT INTO category
VALUES (1, '手机'),
       (2, '电脑'),
       (3, '美妆'),
       (4, '家居');

INSERT INTO products
VALUES (1, '华为Mate50', 5499.00, 9.70, '自营', 1),
       (2, '荣耀80', 2399.00, 9.50, '自营', 1),
       (3, '荣耀80', 2199.00, 9.30, '非自营', 1),
       (4, '红米note 11', 999.00, 9.00, '非自营', 1),
       (5, '联想小新14', 4199.00, 9.20, '自营', 2),
       (6, '惠普战66', 4499.90, 9.30, '自营', 2),
       (7, '苹果Air13', 6198.00, 9.10, '非自营', 2),
       (8, '华为MateBook14', 5599.00, 9.30, '非自营', 2),
       (9, '兰蔻小黑瓶', 1100.00, 9.60, '自营', 3),
       (10, '雅诗兰黛粉底液', 920.00, 9.40, '自营', 3),
       (11, '阿玛尼红管405', 350.00, NULL, '非自营', 3),
       (12, '迪奥996', 330.00, 9.70, '非自营', 3);

简单查询的练习：

-- 需求1: 查看所有商品
select * from products;
select
    id,
    name,
    price,
    score,
    is_self,
    category_id
from products;
-- 需求2: 查看所有商品的名称和价格
select name,price from products;
-- 需求3: 查看所有商品的名称和价格,要求给字段名起别名展示
select name as 姓名,price as 价格 from products;
select name  姓名,price  价格 from products;
-- 需求4: 查看所有商品的名称和价格,要求给表名起别名并使用
select products.name,products.price from products;
-- 如果给表起了表名,必须用别名调用字段
select p.name,p.price from products as p;
select name,price from products as p;
-- 需求5: 查看所有的分类编号,要求去重展示
select DISTINCT category_id from products;

条件查询

条件查询关键字: where

条件查询基础格式: select 字段名 from 表名 where 条件;

	      比较运算符: >  <  >=  <=  !=  <>
	      
	      逻辑运算符: and  or  not
	      
	      范围 查询: 连续范围:between x and y       非连续范围: in(x,y)
	      
	      模糊 查询: 关键字:like   %:0个或者多个字符   _:一个字符
	      
	      非空 判断: 为空: is null    不为空:is not null

比较查询

练习：

# 1.比较运算符: >  <  >=  <=  !=  <>

-- 需求1: 查询所有'自营'的商品
select * from products where is_self = '自营';
-- 需求2: 查询评分在'9.50'(不含)以上的商品
select * from products where score > 9.50;
-- 需求3: 查询评分在'9.50'(含)以上的商品
select * from products where score >= 9.50;
-- 需求4: 查询价格在999(不含)以下的商品
select * from products where price < 999;
-- 需求5: 查询价格在999(含)以下的商品
select * from products where price <= 999;
-- 需求6: 查询评分不等于9.30的商品
select * from products where score != 9.3;
select * from products where score <> 9.3;

逻辑查询

练习：

-- and: 并且  or:或者  not:取反
-- 需求1: 查询自营商品中所有价格大于2000的商品信息
select * from products where is_self = '自营' and price > 2000;
-- 需求2: 查询商品评分在9.0(含)-9.5(含)之间的商品信息
select * from products where score >= 9 and score <= 9.5;
-- 需求3: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where price >= 1000 and price <= 3000;
-- 需求4: 查询价格是999或者2199或者2399的商品
select * from products where price = 999 or price = 2199 or price = 2399;
-- 需求5: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name = '华为Mate50' or name = '荣耀80';
-- 需求6: 查询商品不是自营的商品
select * from products where not is_self = '自营';
select * from products where  is_self = '非自营';
-- 需求7: 查询商品不在1000到3000之间的商品
select * from products where not (price >= 1000 and price <= 3000);
select * from products where  price < 1000 or price > 3000;

范围查询

练习：

-- 需求1: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where  price BETWEEN 1000 and 3000;
-- 注意: 以下语法是错误的
select * from products where 1000 <= price <= 3000;
-- 需求2: 查询商品不在1000到3000之间的商品
select * from products where  price not BETWEEN 1000 and 3000;
-- 需求3: 查询价格是999或者2199或者2399的商品
select * from products where price in(999,2199,2399);
-- 需求4: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name in('华为Mate50','荣耀80');

模糊查询

练习

-- 关键字: like   符号 %:任意多个字符  _:任意1个字符
-- 需求1: 查询商品名称以'华'开头的商品信息
select * from products where name like '华%';
-- 需求2: 查询商品名称以'华'开头并且8个字符的商品信息
select * from products where name like '华_______';
-- 需求3: 查询商品名称以'66'结尾商品信息
select * from products where name like '%66';
-- 需求4: 查询商品名称中包含'兰'字的商品信息
select * from products where name like '%兰%';
-- 需求5: 查询商品名称中第3个字是'兰'字的商品信息
select * from products where name like '__兰%';

非空判断

/*
null在sql中代表空的,没有任何意义的意思
如果数据中有空字符串'',字符串'null',一定要注意,他们和sql中的null不是一回事!!!
*/
-- 需求1:查询未评分的商品信息
select * from products where score is null;
-- 注意: 以下方式是错误的
select * from products where score = null;
select * from products where score = '';
select * from products where score = 'null';
select * from products where score is 'null';

-- 为了方便演示null和'','null'的区别,可以插入部分测试数据
insert into products(name,price) values('拯救者Y9000','9999');
insert into products(name,price) values('null','99');
insert into products(name,price) values('',0);

-- 需求2:查询商品名称是'null'的商品信息
select * from products where name = 'null';
-- 需求3:查询商品名称是''的商品信息
select * from products where name = '';

排序查询

排序查询关键字: order by

排序查询基础格式: select 字段名 from 表名 order by 排序字段名 asc|desc;
asc : 升序(默认)
desc: 降序

排序查询进阶格式: select 字段名 from 表名 order by 排序字段1名 asc|desc , 排序字段2名 asc|desc;
注意: 如果order by后跟多个排序字段,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序

-- 示例1：查询所有商品，并按照评分从高到低进行排序
SELECT * FROM products ORDER BY score DESC;

-- 示例2：查询所有商品，先按照评分从高到低进行排序，评分相同的再按照价格从低到高排序
SELECT * FROM products ORDER BY score DESC, price;

聚合函数

聚合函数: 又叫统计函数,也叫分组函数

常用聚合函数: sum() count() avg() max() min()

聚合查询基础格式: select 聚合函数(字段名) from 表名; 注意: 此处没有分组默认整个表就是一个大的分组

注意: 聚合函数(字段名)会自动忽略null值,以后统计个数一般用count(*)统计因为它不会忽略null值

关于count:
count(字段名):会过滤null值
count(*):不会过滤null值
count(常量):一般使用1代替常量,不会过滤null值
count(主键)：不会过滤null值

# 需求: 统计所有商品的总价,平均价,最大价,最小价格
select
    sum(price) as 总价,
    avg(price) as 平均价格,
    max(price) as 最大价格,
    min(price) as 最小价格
from products;
# 需求: 统计商品的总个数
select count(id) from products;
select count(name) from products;
select count(price) from products;
select count(*) from products; # 推荐
select count(1) from products;
# 需求: 统计已经评分的商品的个数
# 方式1: 先筛选出已经评分,再统计个数
select count(*) from products where score is not null;
# 方式2: 直接利用忽略null值的特点
select count(score) from products;

分组查询

分组查询关键字: group by

分组查询基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;

注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错…sql_mode=only_full_group_by

分组查询进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];

where和having的区别?
书写顺序: where在group by 前,having在group by后
执行顺序: where在group by 前,having在group by后
分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)
应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having
使用别名: where后不能使用别名,having后可以使用别名

-- 示例1：统计每个分类的商品数量
SELECT
    category_id,
    -- ② 再聚合：对每一组的id进行count计数
    COUNT(id) AS cnt
FROM products
-- ① 先分组：按照category_id进行分组
GROUP BY category_id;



-- 示例2：统计每个分类中自营和非自营商品的数量

SELECT
    category_id,
    is_self,
    COUNT(id) AS cnt
FROM products
GROUP BY category_id, is_self;



-- 示例3：统计每个分类商品的平均价格，并筛选出平均价格低于1000的分类

SELECT category_id,
       -- 再聚合
       AVG(price)
FROM products
-- 先分组
GROUP BY category_id
-- 对分组聚合的结果进行筛选
HAVING AVG(price) < 1000;

-- 示例4：统计自营商品中，每个分类的商品的平均价格，并筛选出平均价格高于2000的分类
SELECT category_id,
       AVG(price) AS avg_price
FROM products
WHERE is_self = '自营' -- where在分组之前对数据进行过滤
GROUP BY category_id
HAVING AVG(price) > 2000; -- having在分组聚合之后对数据进行过滤

SELECT category_id,
       AVG(price) AS avg_price
FROM products
WHERE is_self = '自营'
GROUP BY category_id
HAVING avg_price > 2000; -- MySQL在HAVING中可以使用聚合函数结果的别名！

limit 查询

分页查询关键字: limit

分页查询基础格式: select 字段名 from 表名 limit x,y;
x: 起始索引,默认从0开始 x = (页数-1)*y
y: 本次查询的条数

注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决

- 示例1：获取所有商品中，价格最高的商品信息
SELECT
    *
FROM products
ORDER BY price DESC
LIMIT 1; -- LIMIT 0, 1;


-- 示例2：将商品数据按照价格从低到高排序，然后获取第2页内容（每页3条）
SELECT
    *
FROM products
ORDER BY price
LIMIT 3, 3;

-- 示例3：当分页展示的数据不存在时，不报错，只不过查询不到任何数据
SELECT * FROM products LIMIT 20, 10;

SQL执行顺序

书写顺序: SELECT -> DISTINCT -> 聚合函数 -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
执行顺序: FROM -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

多表查询

多表查询的本质是将多个表通过主外键关联连接（join）合并成一个大表，再去查询。

表之间的关系

关系	例子
一对一	一个人一个身份证号
一对多	一个分类下有多个商品
多对多	课程和学生一个课程有多个学生选，一个学生可以选多个课程

一般要借助中间表,把多对多变成一对多

外键约束

关键字:foreign key
通俗理解：

主表:数据少的一张表
从表:数据多的一张表

如果存在外键约束
插入数据时，要保证数据的准确性，限定的是从表
删除数据时，要保证数据的完整性，限定的是主表
注意:
从表的外键字段尽量保证和主表的主键字段类型一致

建表时添加外键约束: … CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)

建表后添加外键约束: alter table 从表名 add CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)

删除外键约束: alter table 从表名 drop FOREIGN KEY 外键约束名;
外键约束好处: 保证数据的准确性和完整性

如果数据库不支持外键约束，可以切换为innodb。

建立、删除外键约束：

# 注意:如果要删除有外键约束的主从表,先删除从表,再删除主表
drop table if exists products1;
drop table if exists category1;

# 创建分类表
CREATE TABLE category1
(
cid   VARCHAR(32) PRIMARY KEY, # 分类id
cname VARCHAR(100)             # 分类名称
);

# 商品表
CREATE TABLE products1
(
pid         VARCHAR(32) PRIMARY KEY,
pname       VARCHAR(40),
price       DOUBLE,
category_id VARCHAR(32),
-- 建表时添加外键约束:   CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)
CONSTRAINT FOREIGN KEY (category_id) REFERENCES category1 (cid)
);


# 查看存储引擎
show create table category1;
show create table products1;

-- 删除外键约束
-- alter table 从表名 drop FOREIGN KEY 外键约束名;
alter table products1 drop FOREIGN KEY products1_ibfk_1;


-- 建表后添加外键约束
-- alter table 从表名 add CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)
alter table products1 add CONSTRAINT wj FOREIGN KEY (category_id)  REFERENCES category1 (cid) ;

测试外键约束：

-- 外键约束的特点
/*
限制从表插入数据: 从表插入数据的时候如果外键值是主表主键中不存在的,就插入失败
限制主表删除数据: 主表删除数据的时候如果主键值已经被从表外键的引用,就删除失败
*/
# 限制从表插入数据: 从表插入数据的时候如果外键值是主表主键中不存在的,就插入失败
insert into products1 values('p1','小米',999,'c001'); -- 失败,报错Cannot add or update a child row: a foreign key constraint fails...
-- 等主表category1插入c001分类后,再执行上述语句,就成功了
insert into category1 values('c001','手机'); -- 成功
insert into products1 values('p1','小米',999,'c001');-- 成功

# 限制主表删除数据: 主表删除数据的时候如果主键值已经被从表外键的引用,就删除失败
delete from category1 where cid='c001'; -- 失败,报错Cannot delete or update a parent row: a foreign key constraint fails
-- 等从表products1外键不引用c001这条记录后,再执行上述语句,就会成功了
-- 注意: 不引用有两种方案: 要么直接删除从表对应记录行(不建议),要么把对应的引用改为null(建议)
update products1 set category_id = null where category_id = 'c001';
delete from category1 where cid='c001'; -- 成功

连接查询

交叉连接/笛卡尔积 [慎用]

交叉连接关键字: cross join

显式交叉连接格式: select * from 左表 cross join 右表;

隐式交叉连接格式: select * from 左表,右表;

注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积，最终数据量会变得非常大)

注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

-- 1.交叉连接(笛卡尔积): 
# 关键字: cross join
# 隐式交叉连接格式: select 字段名 from 左表,右表;
SELECT *
FROM
    products,
    category;
# 显式交叉连接格式: select 字段名 from 左表 cross join右表;
SELECT *
FROM
    products
        CROSS JOIN category;

内连接

内连接关键字: inner join … on

显式内连接格式: select * from 左表 inner join 右表 on 关联条件;

隐式内连接格式: select * from 左表 , 右表 where 关联条件;

注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表

-- 2.内连接: 两个表的交集
# 关键字: inner join on
# select 字段名 from 左表 inner join右表 on ...;
SELECT
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
FROM
    products p
        INNER JOIN category c ON p.category_id = c.id;

外连接

3.1左外连接

	关键字: left [outer] join ... on 关联条件
	语法:
		1.select * from 左表 left join 右表  on 关联条件;
		2.select * from 左表 left outer join 右表  on 关联条件;
	特点:
		1.展示左表所有数据,关联展示右表符合条件数据,右表不符合条件的数据使用null补齐;

3.2右外连接

	关键字:right [outer] join ... on 关联条件;
	语法:
		1.select * from 左表 right join 右表  on 关联条件;
		2.select * from 左表 right outer join 右表  on 关联条件;
	特点:
		1.展示右表所有数据,关联展示左表符合条件数据,左表不符合条件的数据使用null补齐;

左外连接和右外连接：

-- 为了方便演示插入一条数据
INSERT INTO
    products(name, price, category_id)
VALUES
    ('百草味紫皮腰果', 9, 5);

-- 需求: 分别使用左右连接查询每个分类下的所有商品,即使没有商品的分类要展示
-- 分析: 必须以分类表为主
-- 左外连接: left outer join
SELECT
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
FROM
    category c
        LEFT OUTER JOIN products p ON p.category_id = c.id;
-- 右外连接: right outer join
SELECT
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
FROM
    category c
        RIGHT OUTER JOIN products p ON p.category_

左外连接

右外连接

3.3全外连接

	关键字:full outer join
	语法:无
	特点:
		1.左外的查询结果 +  右外查询结果 + 内连接查询结果

3.4union关键字

	语法:select语句 union  select语句;
	特点:
		union:左外的查询结果 +  右外查询结果 + 内连接查询结果 + 去重
		union all:左外 + 右外
	union和union all区别？
		union是去重的，union all不去重
	注意:
		1.列名如果有重复的需要起别名
		2.列的数量和类型要一致

全外连接

3.5CTE表达式

	语法:with 表名 as(sql语句)select * from 表名;

-- 需求：查找出价格大于2000的手机，展示出商品id，价格和类别名称
with phone as (
    select p.id as id,p.price as price,c.name as name from products as p
           left join category as c  on p.category_id=c.id
           where c.id=1
) select * from phone where price>2000;

自关联

语法:自己关联自己，具体是内连接还是外连接，取决于使用的关键字

子查询

定义:一条select语句作为另外一条select语句的条件、表、字段(一般是用来做条件过滤)来使用

END

拓展：

快速复制表

快速复制表结构
语法:create table 表名 like 已存在表名;

已知，有一张表叫products,数据也存在

-- 需求:创建一个和products表一模一样的表
-- 思路:1.创建表
/*
    语法:create table 表名 like 已存在表名;
    注意:此语法只能复制表结构，不能复制数据
 */
create table pros like products;

在这里插入图片描述

快速插入数据
语法:insert into 表名 select语句;

-- 需求2:想将products表中的数据插入到pros表中
-- 思路1:插入数据，语法
# insert into pros vales(...);
-- 思路2:数据在哪？在products表中，如何获取该数据？？
select * from products;
-- 思路3:能不能把上述的两种思路结合起来？
-- 注意:企业常用
insert into pros select * from products;

在这里插入图片描述

快速复制表结构和数据
语法:create table 表名 select语句;

-- 需求:能不能在创建的表的同时，将数据一块复制过来？？
-- 思路1:创建表
# create table 表名 values(.....);
-- 思路2:查询之前已存在表的数据
select * from products;
-- 思路3:能不能把思路1和思路2结合起来？
create table pros2 select * from products;