备案控制台
探索云世界
开发者社区 数据库 文章 正文

MySQL进阶

2022-09-04 111
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： mysql


📦个人主页：楠羽

🏆简介：一个大二的科班出身，主要研究Java后端开发

⏰座右铭：成功之前我们要做应该做的事情，成功之后才能做我们喜欢的事

💕 过客的你，可以给博主留下一个小小的关注吗？这是给博主最大的支持。以后博主会更新大量的优质的作品！！！！

一、约束

1.外键约束

  • 外键约束概念
  • 让表和表之间产生关系，从而保证数据的准确性！
  • 建表时添加外键约束
  • 为什么要有外键约束
-- 创建db2数据库
CREATE DATABASE db2;
-- 使用db2数据库
USE db2;
-- 创建user用户表
CREATE TABLE USER(
  id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
-- 创建orderlist订单表
CREATE TABLE orderlist(
  id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  number VARCHAR(20) NOT NULL,          -- 订单编号
  uid INT                               -- 订单所属用户
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2),
(NULL,'hm005',3),(NULL,'hm006',3);
-- 添加一个订单，但是没有所属用户。这合理吗？
INSERT INTO orderlist VALUES (NULL,'hm007',8);
-- 删除王五这个用户，但是订单表中王五还有很多个订单呢。这合理吗？
DELETE FROM USER WHERE NAME='王五';
-- 所以我们需要添加外键约束，让两张表产生关系
  • 外键约束格式
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  • 创建表添加外键约束
-- 创建user用户表
CREATE TABLE USER(
  id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
-- 创建orderlist订单表
CREATE TABLE orderlist(
  id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  number VARCHAR(20) NOT NULL,          -- 订单编号
  uid INT,                              -- 订单所属用户
  CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2),
(NULL,'hm005',3),(NULL,'hm006',3);
-- 添加一个订单，但是没有所属用户。无法添加
INSERT INTO orderlist VALUES (NULL,'hm007',8);
-- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除
DELETE FROM USER WHERE NAME='王五';
  • 删除外键约束
-- 标准语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
-- 删除外键
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  • 建表后添加外键约束
-- 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
-- 添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

2.外键的级联更新和级联删除(了解)

  • 什么是级联更新和级联删除
  • 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
  • 当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改
  • 添加级联更新和级联删除
-- 添加外键约束，同时添加级联更新  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
-- 添加外键约束，同时添加级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
-- 添加外键约束，同时添加级联更新和级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
-- 将王五用户的id修改为5    订单表中的uid也随之被修改
UPDATE USER SET id=5 WHERE id=3;
-- 将王五用户删除     订单表中该用户所有订单也随之删除
DELETE FROM USER WHERE id=5;

二、多表设计

1.一对一(了解)

  • 分析
  • 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
  • 实现原则
  • 在任意一个表建立外键，去关联另外一个表的主键
  • SQL演示
-- 创建db5数据库
CREATE DATABASE db5;
-- 使用db5数据库
USE db5;
-- 创建person表
CREATE TABLE person(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');
-- 创建card表
CREATE TABLE card(
  id INT PRIMARY KEY AUTO_INCREMENT,
  number VARCHAR(50),
  pid INT UNIQUE,
  CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 添加外键
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);
  • 图解

2.一对多

  • 分析
  • 用户和订单。一个用户可以有多个订单！
  • 商品分类和商品。一个分类下可以有多个商品！
  • 实现原则
  • 在多的一方，建立外键约束，来关联一的一方主键
  • SQL演示
/*
  用户和订单
*/
-- 创建user表
CREATE TABLE USER(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist表
CREATE TABLE orderlist(
  id INT PRIMARY KEY AUTO_INCREMENT,
  number VARCHAR(20),
  uid INT,
  CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);
/*
  商品分类和商品
*/
-- 创建category表
CREATE TABLE category(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');
-- 创建product表
CREATE TABLE product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(30),
  cid INT,
  CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);
  • 图解

3.多对多

  • 分析
  • 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
  • 实现原则
  • 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键
  • SQL演示
-- 创建student表
CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
-- 创建course表
CREATE TABLE course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
-- 创建中间表
CREATE TABLE stu_course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  sid INT, -- 用于和student表的id进行外键关联
  cid INT, -- 用于和course表的id进行外键关联
  CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
  CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
  • 图解

三、多表查询

1.多表查询-数据准备

  • SQL语句
-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;
-- 创建user表
CREATE TABLE USER(
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 用户id
  NAME VARCHAR(20),             -- 用户姓名
  age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);
-- 订单表
CREATE TABLE orderlist(
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 订单id
  number VARCHAR(30),         -- 订单编号
  uid INT,    -- 外键字段
  CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);
-- 商品分类表
CREATE TABLE category(
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
  NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');
-- 商品表
CREATE TABLE product(
  id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
  NAME VARCHAR(30),                    -- 商品名称
  cid INT, -- 外键字段
  CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);
-- 中间表
CREATE TABLE us_pro(
  upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
  uid INT, -- 外键字段。需要和用户表的主键产生关联
  pid INT, -- 外键字段。需要和商品表的主键产生关联
  CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
  CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
  • 架构器图解

2.多表查询-笛卡尔积查询(了解)

  • 有两张表，获取这两个表的所有组合情况
  • 要完成多表查询，需要消除这些没有用的数据
  • 多表查询格式
SELECT
  列名列表
FROM
  表名列表
WHERE
  条件...
  • 笛卡尔积查询
-- 标准语法
SELECT 列名 FROM 表名1,表名2,...;
-- 查询user表和orderlist表
SELECT * FROM USER,orderlist;

3.多表查询-内连接查询

  • 查询原理
  • 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
  • 显式内连接
-- 标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;
-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;
-- 查询用户姓名，年龄。和订单编号
SELECT
  u.`name`, -- 姓名
  u.`age`,  -- 年龄
  o.`number`  -- 订单编号
FROM
  USER u          -- 用户表
JOIN
  orderlist o     -- 订单表
ON 
  u.`id` = o.`uid`;
  • 隐式内连接
-- 标准语法
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
-- 查询用户姓名，年龄。和订单编号
SELECT
  u.`name`, -- 姓名
  u.`age`,  -- 年龄
  o.`number`  -- 订单编号
FROM
  USER u,   -- 用户表
  orderlist o     -- 订单表
WHERE
  u.`id`=o.`uid`;

4.多表查询-外连接查询

  • 左外连接
  • 查询原理
  • 查询左表的全部数据，和左右两张表有交集部分的数据
  • 基本演示
-- 标准语法
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有用户信息，以及用户对应的订单信息
SELECT
  u.`name`, -- 姓名
  u.`age`,  -- 年龄
  o.`number`  -- 订单编号
FROM
  USER u          -- 用户表
LEFT OUTER JOIN
  orderlist o     -- 订单表
ON
  u.`id`=o.`uid`;
  • 右外连接
  • 查询原理
  • 查询右表的全部数据，和左右两张表有交集部分的数据
  • 基本演示
-- 基本语法
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有订单信息，以及订单所属的用户信息
SELECT
  u.`name`, -- 姓名
  u.`age`,  -- 年龄
  o.`number`  -- 订单编号
FROM
  USER u          -- 用户表
RIGHT OUTER JOIN
  orderlist o     -- 订单表
ON
  u.`id`=o.`uid`;

5.多表查询-子查询

  • 子查询介绍
  • 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！
  • 子查询-结果是单行单列的
  • 可以作为条件，使用运算符进行判断！
  • 基本演示
-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);
-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM USER;              -- 查询出最高年龄
SELECT NAME,age FROM USER WHERE age=26; -- 根据查询出来的最高年龄，查询姓名和年龄
SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);
  • 子查询-结果是多行单列的
  • 可以作为条件，使用运算符in或not in进行判断！
  • 基本演示
-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
-- 查询张三和李四的订单信息
SELECT id FROM USER WHERE NAME='张三' OR NAME='李四';   -- 查询张三和李四用户的id
SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根据id查询订单
SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');
  • 子查询-结果是多行多列的
  • 可以作为一张虚拟表参与查询！
  • 基本演示
-- 标准语法
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

6.多表查询练习

  • 查询用户的编号、姓名、年龄。订单编号
/*
分析：
  用户的编号、姓名、年龄  user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
FROM
  USER t1,       -- 用户表
  orderlist t2   -- 订单表
WHERE
  t1.`id` = t2.`uid`;
  • 查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
  查询所有用户，使用左外连接
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
FROM
  USER t1        -- 用户表
LEFT OUTER JOIN
  orderlist t2   -- 订单表
ON
  t1.`id` = t2.`uid`;
  • 查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid
  查询所有订单，使用右外连接
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
FROM
  USER t1         -- 用户表
RIGHT OUTER JOIN
  orderlist t2    -- 订单表
ON
  t1.`id` = t2.`uid`;
  • 查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.age > 23 AND user.id = orderlist.uid
*/
/*
select
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
from
  user t1,     -- 用户表
  orderlist t2 -- 订单表
where
  t1.`age` > 23
  and
  t1.`id` = t2.`uid`;
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
FROM
  USER t1       -- 用户表
LEFT OUTER JOIN
  orderlist t2  -- 订单表
ON
  t1.`id` = t2.`uid`
WHERE
  t1.`age` > 23;
  • 查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析：
  用户的编号、姓名、年龄 user表     订单编号 orderlist表
  条件：user.id = orderlist.uid AND user.name IN ('张三','李四');
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户姓名
  t1.`age`, -- 用户年龄
  t2.`number` -- 订单编号
FROM
  USER t1,        -- 用户表
  orderlist t2    -- 订单表
WHERE
  t1.`id` = t2.`uid`
  AND
  -- (t1.`name` = '张三' OR t1.`name` = '李四');
  t1.`name` IN ('张三','李四');
  • 查询商品分类的编号、分类名称。分类下的商品名称
/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
*/
SELECT
  t1.`id`,  -- 分类编号
  t1.`name`,  -- 分类名称
  t2.`name` -- 商品名称
FROM
  category t1,  -- 商品分类表
  product t2      -- 商品表
WHERE
  t1.`id` = t2.`cid`;
  • 查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
  查询所有的商品分类，使用左外连接
*/
SELECT
  t1.`id`,  -- 分类编号
  t1.`name`,  -- 分类名称
  t2.`name` -- 商品名称
FROM
  category t1 -- 商品分类表
LEFT OUTER JOIN
  product t2  -- 商品表
ON
  t1.`id` = t2.`cid`;
  • 查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*
分析：
  商品分类的编号、分类名称 category表     分类下的商品名称 product表
  条件：category.id = product.cid
  查询所有的商品信息，使用右外连接
*/
SELECT
  t1.`id`,  -- 分类编号
  t1.`name`,  -- 分类名称
  t2.`name` -- 商品名称
FROM
  category t1 -- 商品分类表
RIGHT OUTER JOIN
  product t2  -- 商品表
ON
  t1.`id` = t2.`cid`;
  • 查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析：
  用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
  条件：us_pro.uid = user.id AND us_pro.pid = product.id
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户名称
  t1.`age`, -- 用户年龄
  t2.`name` -- 商品名称
FROM
  USER t1,  -- 用户表
  product t2, -- 商品表
  us_pro t3 -- 中间表
WHERE
  t3.`uid` = t1.`id`
  AND
  t3.`pid` = t2.`id`;
  • 查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析：
  用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
  条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四')
*/
SELECT
  t1.`id`,  -- 用户编号
  t1.`name`,  -- 用户名称
  t1.`age`, -- 用户年龄
  t2.`name` -- 商品名称
FROM
  USER t1,  -- 用户表
  product t2, -- 商品表
  us_pro t3 -- 中间表
WHERE
  (t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
  AND
  -- (t1.`name` = '张三' or t1.`name` = '李四');
  t1.`name` IN ('张三','李四');

7.多表查询-自关联查询

  • 自关联查询介绍
  • 同一张表中有数据关联。可以多次查询这同一个表！
  • 自关联查询演示
-- 创建员工表
CREATE TABLE employee(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  mgr INT,
  salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
  员工姓名 employee表        直接上级姓名 employee表
  条件：employee.mgr = employee.id
  查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT
  t1.name,  -- 员工姓名
  t1.mgr,   -- 上级编号
  t2.id,    -- 员工编号
  t2.name     -- 员工姓名
FROM
  employee t1  -- 员工表
LEFT OUTER JOIN
  employee t2  -- 员工表
ON
  t1.mgr = t2.id;

四、视图

1.视图的概念

  • 视图是一种虚拟存在的数据表
  • 这个虚拟的表并不在数据库中实际存在
  • 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
  • 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

2.视图的好处

  • 简单
  • 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
  • 安全
  • 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
  • 数据独立
  • 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

3.视图数据准备

-- 创建db7数据库
CREATE DATABASE db7;
-- 使用db7数据库
USE db7;
-- 创建country表
CREATE TABLE country(
  id INT PRIMARY KEY AUTO_INCREMENT,
  country_name VARCHAR(30)
);
-- 添加数据
INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'俄罗斯');
-- 创建city表
CREATE TABLE city(
  id INT PRIMARY KEY AUTO_INCREMENT,
  city_name VARCHAR(30),
  cid INT, -- 外键列。关联country表的主键列id
  CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'纽约',2),(NULL,'莫斯科',3);

4.视图的创建

  • 创建视图语法
-- 标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
  • 普通多表查询，查询城市和所属国家
-- 普通多表查询，查询城市和所属国家
SELECT
  t1.*,
  t2.country_name
FROM
  city t1,
  country t2
WHERE
  t1.cid = t2.id;
-- 经常需要查询这样的数据，就可以创建一个视图
  • 创建视图基本演示
-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
  city_country
AS
  SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
  • 创建视图并指定列名基本演示
-- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
  city_country2 (city_id,city_name,cid,country_name) 
AS
  SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

5.视图的查询

  • 查询视图语法
-- 标准语法
SELECT * FROM 视图名称;
  • 查询视图基本演示
-- 查询视图。查询这张虚拟表，就等效于查询城市和所属国家
SELECT * FROM city_country;
-- 查询指定列名的视图
SELECT * FROM city_country2;
-- 查询所有数据表，视图也会查询出来
SHOW TABLES;
  • 查询视图创建语法
-- 标准语法
SHOW CREATE VIEW 视图名称;
  • 查询视图创建语句基本演示
SHOW CREATE VIEW city_country;

6.视图的修改

  • 修改视图表中的数据
-- 标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;
-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';
-- 查询视图
SELECT * FROM city_country;
-- 查询city表,北京也修改为了北京市
SELECT * FROM city;
-- 注意：视图表数据修改，会自动修改源表中的数据
  • 修改视图表结构
-- 标准语法
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
-- 查询视图2
SELECT * FROM city_country2;
-- 修改视图2的列名city_id为id
ALTER
VIEW
  city_country2 (id,city_name,cid,country_name)
AS
  SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

7.视图的删除

  • 删除视图
-- 标准语法
DROP VIEW [IF EXISTS] 视图名称;
-- 删除视图
DROP VIEW city_country;
-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

8.视图的总结

  • 视图是一种虚拟存在的数据表
  • 这个虚拟的表并不在数据库中实际存在
  • 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
  • 视图的好处
  • 简单
  • 安全
  • 数据独立
文章标签：
云数据库 RDS MySQL 版
Java
SQL
关系型数据库
MySQL
安全
数据库
关键词：
云数据库 RDS MySQL 版进阶
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
楠羽
目录
相关文章
秋说
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路丨第十四篇】一文带你精通MySQL重复数据及SQL注入
【MySQL进阶之路丨第十四篇】一文带你精通MySQL重复数据及SQL注入
秋说
48 0
秋说
|
2月前
|
存储 关系型数据库 MySQL
【MySQL进阶之路丨第十三篇】一文带你精通MySQL之ALTER命令及序列使用
【MySQL进阶之路丨第十三篇】一文带你精通MySQL之ALTER命令及序列使用
秋说
40 0
秋说
|
2月前
|
机器学习/深度学习 SQL 关系型数据库
【MySQL进阶之路丨第十一篇】一文带你精通MySQL NULL值处理、正则表达式
【MySQL进阶之路丨第十一篇】一文带你精通MySQL NULL值处理、正则表达式
秋说
35 0
海拥
|
3月前
|
关系型数据库 MySQL 数据库
深入MySQL数据库进阶实战：性能优化、高可用性与安全性
深入MySQL数据库进阶实战：性能优化、高可用性与安全性
海拥
136 0
秋说
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
秋说
51 0
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
失重外太空.
|
3月前
|
SQL 关系型数据库 MySQL
MySQL进阶之性能优化与调优技巧
MySQL进阶之性能优化与调优技巧
失重外太空.
48 0
遇事问春风乄
|
2月前
|
SQL 关系型数据库 MySQL
【JavaEE进阶】 数据库连接池与MySQL企业开发规范
【JavaEE进阶】 数据库连接池与MySQL企业开发规范
遇事问春风乄
121 0
一只牛博
|
2月前
|
SQL 监控 关系型数据库
MySQL Binlog深度解析：进阶应用与实战技巧【进阶应用】
MySQL Binlog深度解析：进阶应用与实战技巧【进阶应用】
一只牛博
44 0
秋说
|
2月前
|
关系型数据库 MySQL 数据库
【MySQL进阶之路丨第十七篇（完结）】一文带你精通MySQL运算符
【MySQL进阶之路丨第十七篇（完结）】一文带你精通MySQL运算符
秋说
22 0
秋说
|
2月前
|
SQL 机器学习/深度学习 关系型数据库
【MySQL进阶之路丨第十六篇】一文带你精通MySQL函数
【MySQL进阶之路丨第十六篇】一文带你精通MySQL函数
秋说
35 0

热门文章

最新文章

  • 1
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 2
    MySQL全局库表查询准确定位字段
  • 3
    MySQL学习（三）
  • 4
    安装Docker&镜像容器操作&使用Docker安装部署MySQL，Redis，RabbitMQ，Nacos，Seata，Minio
  • 5
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 6
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 7
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 8
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 9
    MySQL学习（五）
  • 10
    MySQL学习（七）
  • 1
    [MySQL]事务原理之redo log,undo log
    45
  • 2
    [MySQL]SQL优化之索引的使用规则
    35
  • 3
    [MySQL] SQL优化之性能分析
    30
  • 4
    【mysql】—— 用户管理
    31
  • 5
    【mysql】—— 视图
    30
  • 6
    【mysql】—— 函数的基本介绍
    25
  • 7
    【mysql】—— 表的增删改查
    40
  • 8
    【mysql】—— 表的约束
    31
  • 9
    【MYSQL】—— MySQL 在 Centos 7环境安装
    52
  • 10
    MySQL模糊查询二三事
    24

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）