1. 约束

1.1 外键约束

  • 为什么要有外键约束？

当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的真确性！

  • 外键约束的作用

让表与表之间产生关联关系，从而保证数据的准确性！

  • 外键约束格式CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  • 建表时添加外键约束(下面订单表中的uid是外键) ```sql — 创建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=’王五’;

  2. - 删除外键约束
  3. ```sql
  4. -- 标准语法
  5. ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  7. -- 删除外键
  8. ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  • 建表后单独添加外键约束(很好记，无非是多了ALTER TABLE 表名 ADD) ```sql — 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

— 添加外键约束 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

  1. <a name="mAdFj"></a>
  2. ## 1.2 外键的级联更新和级联删除(了解，因为这种牵一发动全身的操作我们一般不用)
  4. - 什么是级联更新和级联删除
  5.    - 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
  6.    - 当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改
  7. - 添加级联更新和级联删除
  8. ```sql
  9. -- 添加外键约束，同时添加级联更新  标准语法
  10. ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
  12. -- 添加外键约束，同时添加级联删除  标准语法
  13. ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
  15. -- 添加外键约束，同时添加级联更新和级联删除  标准语法
  16. ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
  19. -- 删除外键约束
  20. ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  22. -- 添加外键约束，同时添加级联更新和级联删除
  23. ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
  25. -- 将王五用户的id修改为5    订单表中的uid也随之被修改
  26. UPDATE USER SET id=5 WHERE id=3;
  28. -- 将王五用户删除     订单表中该用户所有订单也随之删除
  29. DELETE FROM USER WHERE id=5;

2. 多表操作

2.1 多表介绍

  • 多表概念

说白了就是多张数据表，而表与表之间是可以有一定的关联关系，这种关联关系通过外键约束实现。

  • 多表的分类

一对一
一对多
多对多

2.2 多表关系

一对一

  • 分析
    • 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
  • 实现原则
    • 在任意一个表建立外键，去关联另外一个表的主键
  • SQL演示 ```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. - 图解
  4. ![01.png](https://cdn.nlark.com/yuque/0/2021/png/12786164/1635243915021-155c5030-9ee7-41ef-8cad-496d445ba943.png#clientId=u1efe19f5-7ed8-4&from=drop&id=uf07098ec&margin=%5Bobject%20Object%5D&name=01.png&originHeight=504&originWidth=1274&originalType=binary&ratio=1&size=19730&status=done&style=none&taskId=u830d147f-e62d-47f2-859b-c4b81a473d2)
  5. <a name="NoRyg"></a>
  6. ### 一对多
  8. - 分析
  9.    - 用户和订单。一个用户可以有多个订单！
  10.    - 商品分类和商品。一个分类下可以有多个商品！
  11. - 实现原则
  12.    - 在多的一方，建立外键约束(**由此可见外键既可以表示一对一，也可以表示一对多**)，来关联一的一方主键
  13. - SQL演示
  14. ```sql
  15. /*
  16.     用户和订单
  17. */
  18. -- 创建user表
  19. CREATE TABLE USER(
  20.     id INT PRIMARY KEY AUTO_INCREMENT,
  21.     NAME VARCHAR(20)
  22. );
  23. -- 添加数据
  24. INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
  26. -- 创建orderlist表
  27. CREATE TABLE orderlist(
  28.     id INT PRIMARY KEY AUTO_INCREMENT,
  29.     number VARCHAR(20),
  30.     uid INT,
  31.     CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- 添加外键约束
  32. );
  33. -- 添加数据
  34. INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);
  37. /*
  38.     商品分类和商品
  39. */
  40. -- 创建category表
  41. CREATE TABLE category(
  42.     id INT PRIMARY KEY AUTO_INCREMENT,
  43.     NAME VARCHAR(10)
  44. );
  45. -- 添加数据
  46. INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');
  48. -- 创建product表
  49. CREATE TABLE product(
  50.     id INT PRIMARY KEY AUTO_INCREMENT,
  51.     NAME VARCHAR(30),
  52.     cid INT,
  53.     CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 添加外键约束
  54. );
  55. -- 添加数据
  56. INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
  57. (NULL,'联想电脑',2),(NULL,'苹果电脑',2);
  • 图解

02.png

多对多

  • 分析
    • 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
  • 实现原则
    • 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键(由此可见，外键也可以解决多对多)
  • SQL演示 ```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);

  2. - 图解
  4. ![03.png](https://cdn.nlark.com/yuque/0/2021/png/12786164/1635244183272-4938aaba-f0d3-4de2-bb6d-7d601e65de5d.png#clientId=u1efe19f5-7ed8-4&from=drop&id=u51ec6b82&name=03.png&originHeight=687&originWidth=1206&originalType=binary&ratio=1&size=34263&status=done&style=none&taskId=udd06f920-28a8-47ea-aaf7-c1dc337da1e)
  5. <a name="NdXL4"></a>
  6. ## 2.3 多表查询
  8. - 多表查询分类
  10. 内连接查询<br />外连接查询<br />子查询<br />自关联查询
  12. - 多表查询数据准备
  13. ```sql
  14. -- 创建db6数据库
  15. CREATE DATABASE db6;
  16. -- 使用db6数据库
  17. USE db6;
  19. -- 创建user表
  20. CREATE TABLE USER(
  21.     id INT PRIMARY KEY AUTO_INCREMENT,    -- 用户id
  22.     NAME VARCHAR(20),                    -- 用户姓名
  23.     age INT                             -- 用户年龄
  24. );
  25. -- 添加数据
  26. INSERT INTO USER VALUES (1,'张三',23);
  27. INSERT INTO USER VALUES (2,'李四',24);
  28. INSERT INTO USER VALUES (3,'王五',25);
  29. INSERT INTO USER VALUES (4,'赵六',26);
  32. -- 订单表
  33. CREATE TABLE orderlist(
  34.     id INT PRIMARY KEY AUTO_INCREMENT,    -- 订单id
  35.     number VARCHAR(30),                    -- 订单编号
  36.     uid INT,    -- 外键字段
  37.     CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
  38. );
  39. -- 添加数据
  40. INSERT INTO orderlist VALUES (1,'hm001',1);
  41. INSERT INTO orderlist VALUES (2,'hm002',1);
  42. INSERT INTO orderlist VALUES (3,'hm003',2);
  43. INSERT INTO orderlist VALUES (4,'hm004',2);
  44. INSERT INTO orderlist VALUES (5,'hm005',3);
  45. INSERT INTO orderlist VALUES (6,'hm006',3);
  46. INSERT INTO orderlist VALUES (7,'hm007',NULL);
  49. -- 商品分类表
  50. CREATE TABLE category(
  51.     id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
  52.     NAME VARCHAR(10)                    -- 商品分类名称
  53. );
  54. -- 添加数据
  55. INSERT INTO category VALUES (1,'手机数码');
  56. INSERT INTO category VALUES (2,'电脑办公');
  57. INSERT INTO category VALUES (3,'烟酒茶糖');
  58. INSERT INTO category VALUES (4,'鞋靴箱包');
  61. -- 商品表
  62. CREATE TABLE product(
  63.     id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
  64.     NAME VARCHAR(30),                    -- 商品名称
  65.     cid INT, -- 外键字段
  66.     CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
  67. );
  68. -- 添加数据
  69. INSERT INTO product VALUES (1,'华为手机',1);
  70. INSERT INTO product VALUES (2,'小米手机',1);
  71. INSERT INTO product VALUES (3,'联想电脑',2);
  72. INSERT INTO product VALUES (4,'苹果电脑',2);
  73. INSERT INTO product VALUES (5,'中华香烟',3);
  74. INSERT INTO product VALUES (6,'玉溪香烟',3);
  75. INSERT INTO product VALUES (7,'计生用品',NULL);
  78. -- 中间表
  79. CREATE TABLE us_pro(
  80.     upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
  81.     uid INT, -- 外键字段。需要和用户表的主键产生关联
  82.     pid INT, -- 外键字段。需要和商品表的主键产生关联
  83.     CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
  84.     CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
  85. );
  86. -- 添加数据
  87. INSERT INTO us_pro VALUES (NULL,1,1);
  88. INSERT INTO us_pro VALUES (NULL,1,2);
  89. INSERT INTO us_pro VALUES (NULL,1,3);
  90. INSERT INTO us_pro VALUES (NULL,1,4);
  91. INSERT INTO us_pro VALUES (NULL,1,5);
  92. INSERT INTO us_pro VALUES (NULL,1,6);
  93. INSERT INTO us_pro VALUES (NULL,1,7);
  94. INSERT INTO us_pro VALUES (NULL,2,1);
  95. INSERT INTO us_pro VALUES (NULL,2,2);
  96. INSERT INTO us_pro VALUES (NULL,2,3);
  97. INSERT INTO us_pro VALUES (NULL,2,4);
  98. INSERT INTO us_pro VALUES (NULL,2,5);
  99. INSERT INTO us_pro VALUES (NULL,2,6);
  100. INSERT INTO us_pro VALUES (NULL,2,7);
  101. INSERT INTO us_pro VALUES (NULL,3,1);
  102. INSERT INTO us_pro VALUES (NULL,3,2);
  103. INSERT INTO us_pro VALUES (NULL,3,3);
  104. INSERT INTO us_pro VALUES (NULL,3,4);
  105. INSERT INTO us_pro VALUES (NULL,3,5);
  106. INSERT INTO us_pro VALUES (NULL,3,6);
  107. INSERT INTO us_pro VALUES (NULL,3,7);
  108. INSERT INTO us_pro VALUES (NULL,4,1);
  109. INSERT INTO us_pro VALUES (NULL,4,2);
  110. INSERT INTO us_pro VALUES (NULL,4,3);
  111. INSERT INTO us_pro VALUES (NULL,4,4);
  112. INSERT INTO us_pro VALUES (NULL,4,5);
  113. INSERT INTO us_pro VALUES (NULL,4,6);
  114. INSERT INTO us_pro VALUES (NULL,4,7);

数据说明：用户表和订单表是一对多关系，其中用户4(赵六)没有订单，订单7不属于任何用户；
：商品分类和商品表属于多对多关系，其中商品分类4(鞋靴箱包)没有对应的商品，商品7不属于任何分类；
：用户表和商品表是多对多关系。

  • 用Sqlyog的架构器图解

04.png

多表查询之笛卡尔积查询(了解)

  • 有两张表，获取这两个表的所有组合情况
  • 要完成多表查询，需要消除这些没有用的数据

  • 多表查询格式

    1. SELECT
    2.   列名列表
    3. FROM
    4.   表名列表
    5. WHERE
    6.   条件...

  • 笛卡尔积查询 ```sql — 标准语法 SELECT 列名 FROM 表名1,表名2,…;

— 查询user表和orderlist表 SELECT * FROM USER,orderlist;

  1. <a name="IELFE"></a>
  2. #### 多表查询之内连接查询(分为显式和隐式)
  4. - 查询原理
  5.    - 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
  6. - 显式内连接
  7. ```sql
  8. -- 标准语法
  9. SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
  11. -- 查询用户信息和对应的订单信息
  12. SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
  13. SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;
  15. -- 查询用户信息和对应的订单信息，起别名
  16. SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;
  18. -- 查询用户姓名，年龄。和订单编号
  19. SELECT
  20.     u.`name`,    -- 姓名
  21.     u.`age`,    -- 年龄
  22.     o.`number`    -- 订单编号
  23. FROM
  24.     USER u          -- 用户表
  25. JOIN
  26.     orderlist o     -- 订单表
  27. ON 
  28.     u.`id` = o.`uid`;
  • 隐式内连接 ```sql — 标准语法 SELECT 列名 FROM 表名1,表名2 WHERE 条件;

— 查询用户姓名，年龄。和订单编号 SELECT u.name, — 姓名 u.age, — 年龄 o.number — 订单编号 FROM USER u, — 用户表 orderlist o — 订单表 WHERE u.id=o.uid;

  1. <a name="WsQ1O"></a>
  2. #### 多表查询之外连接查询
  3. 左外连接
  5. - 查询原理
  6.    - 查询左表的全部数据，和左右两张表有交集部分的数据
  7. - 基本演示
  8. ```sql
  9. -- 标准语法
  10. SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
  12. -- 查询所有用户信息(包括没有订单的)，以及用户对应的订单信息
  13. SELECT
  14.     u.`name`,    -- 姓名
  15.     u.`age`,    -- 年龄
  16.     o.`number`    -- 订单编号
  17. FROM
  18.     USER u          -- 用户表
  19. LEFT OUTER JOIN
  20.     orderlist o     -- 订单表
  21. ON
  22.     u.`id`=o.`uid`;

image.png
右外连接

  • 查询原理
    • 查询右表的全部数据，和左右两张表有交集部分的数据
  • 基本演示 ```sql — 基本语法 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;

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12786164/1635247770326-c200afb6-7e41-4980-af77-c728888aaaf2.png#clientId=u1efe19f5-7ed8-4&from=paste&id=ue442c605&name=image.png&originHeight=144&originWidth=294&originalType=binary&ratio=1&size=56299&status=done&style=none&taskId=u7bd08ac7-b1e9-40ec-a65b-ad90a0a4844)<br />其实左外连接和右外连接是相对概念，我们只需要将左外连接中两个表的位置换一下，就变成了右外连接一样的含义。因此记住一个即可。如下：
  2. ```sql
  3. -- 查询所有用户信息(包括没有订单的)，以及用户对应的订单信息
  4. SELECT
  5.     u.`name`,    -- 姓名
  6.     u.`age`,    -- 年龄
  7.     o.`number`    -- 订单编号
  8. FROM
  9.     USER u          -- 用户表
  10. LEFT OUTER JOIN
  11.     orderlist o     -- 订单表
  12. ON
  13.     u.`id`=o.`uid`;
  16. -- 查询所有订单信息(包括没有对应用户的)，以及该订单说属的用户信息
  17. SELECT
  18.     u.`name`,    -- 姓名
  19.     u.`age`,    -- 年龄
  20.     o.`number`    -- 订单编号
  21. FROM
  22.     orderlist o     -- 订单表
  23. LEFT OUTER JOIN
  24.     USER u          -- 用户表
  25. ON
  26.     u.`id`=o.`uid`;

多表查询之子查询

  • 子查询介绍
    • 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！
  • 子查询-结果是单行单列的
    • 可以作为条件，使用运算符进行判断！（=、>、<等）
    • 基本演示 ```sql — 标准语法 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);

  1. 子查询-结果是多行单列的
  3. - 可以作为条件，使用运算符in或not in进行判断！
  4. - 基本演示
  5. ```sql
  6. -- 标准语法
  7. SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
  9. -- 查询张三和李四的订单信息
  10. SELECT id FROM USER WHERE NAME='张三' OR NAME='李四';   -- 查询张三和李四用户的id
  11. SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根据id查询订单
  12. SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');

子查询-结果是多行多列的

  • 可以作为一张虚拟表参与查询！
  • 基本演示 ```sql — 标准语法 SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

— 查询订单表中id大于4的订单信息和所属用户信息 SELECT FROM USER u,(SELECT FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

  1. 注：上面`查询订单表中id大于4的订单信息`相当于查询订单表，得到一个列表，我们把其视作表。然后与用户表做~~内~~连接查询(这里应该是外连接，是左外连接，即该订单没有用户也要查出来，我觉得上面的sql有问题。其是隐式内连接)。
  2. <a name="UpIbG"></a>
  3. #### 多表查询之自关联查询(下面例子中上级也在当前表)
  5. - 自关联查询介绍
  6.    - 同一张表中有数据关联。可以多次查询这同一个表！
  7. - 自关联查询演示
  8. ```sql
  9. -- 创建员工表
  10. CREATE TABLE employee(
  11.     id INT PRIMARY KEY AUTO_INCREMENT,
  12.     NAME VARCHAR(20),
  13.     mgr INT,  -- 上级
  14.     salary DOUBLE
  15. );
  16. -- 添加数据
  17. INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
  18. (1002,'猪八戒',1005,8000.00),
  19. (1003,'沙和尚',1005,8500.00),
  20. (1004,'小白龙',1005,7900.00),
  21. (1005,'唐僧',NULL,15000.00),
  22. (1006,'武松',1009,7600.00),
  23. (1007,'李逵',1009,7400.00),
  24. (1008,'林冲',1009,8100.00),
  25. (1009,'宋江',NULL,16000.00);
  27. -- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询(因此是左外连接)
  28. /*
  29. 分析：
  30.     员工姓名 employee表        直接上级姓名 employee表
  31.     条件：employee.mgr = employee.id
  32.     查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
  33. */
  34. SELECT
  35.     t1.name,    -- 员工姓名
  36.     t1.mgr,        -- 上级编号
  37.     t2.id,        -- 员工编号
  38.     t2.name     -- 员工姓名
  39. FROM
  40.     employee t1  -- 员工表
  41. LEFT OUTER JOIN
  42.     employee t2  -- 员工表
  43. ON
  44.     t1.mgr = t2.id;

多表查询之练习

image.png

  • 查询用户的编号、姓名、年龄。订单编号

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄  user表     订单编号 orderlist表
    4.   条件：user.id = orderlist.uid
    5. */
    6. SELECT
    7.   t1.`id`,    -- 用户编号
    8.   t1.`name`,    -- 用户姓名
    9.   t1.`age`,    -- 用户年龄
    10.   t2.`number`    -- 订单编号
    11. FROM
    12.   USER t1,       -- 用户表
    13.   orderlist t2   -- 订单表
    14. WHERE
    15.   t1.`id` = t2.`uid`;

  • 查询所有的用户。用户的编号、姓名、年龄。订单编号

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表     订单编号 orderlist表
    4.   条件：user.id = orderlist.uid
    5.   查询所有用户，使用左外连接
    6. */
    7. SELECT
    8.   t1.`id`,    -- 用户编号
    9.   t1.`name`,    -- 用户姓名
    10.   t1.`age`,    -- 用户年龄
    11.   t2.`number`    -- 订单编号
    12. FROM
    13.   USER t1        -- 用户表
    14. LEFT OUTER JOIN
    15.   orderlist t2   -- 订单表
    16. ON
    17.   t1.`id` = t2.`uid`;

  • 查询所有的订单。用户的编号、姓名、年龄。订单编号

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表     订单编号 orderlist表
    4.   条件：user.id = orderlist.uid
    5.   查询所有订单，使用右外连接
    6. */
    7. SELECT
    8.   t1.`id`,    -- 用户编号
    9.   t1.`name`,    -- 用户姓名
    10.   t1.`age`,    -- 用户年龄
    11.   t2.`number`    -- 订单编号
    12. FROM
    13.   USER t1         -- 用户表
    14. RIGHT OUTER JOIN
    15.   orderlist t2    -- 订单表
    16. ON
    17.   t1.`id` = t2.`uid`;

  • 查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号（我感觉下面两个sql的含义都不一样，不知道结构一样不，没有执行）

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表     订单编号 orderlist表
    4.   条件：user.age > 23 AND user.id = orderlist.uid
    5. */
    6. /*
    7. select
    8.   t1.`id`,    -- 用户编号
    9.   t1.`name`,    -- 用户姓名
    10.   t1.`age`,    -- 用户年龄
    11.   t2.`number`    -- 订单编号
    12. from
    13.   user t1,     -- 用户表
    14.   orderlist t2 -- 订单表
    15. where
    16.   t1.`age` > 23
    17.   and
    18.   t1.`id` = t2.`uid`;
    19. */
    20. SELECT
    21.   t1.`id`,    -- 用户编号
    22.   t1.`name`,    -- 用户姓名
    23.   t1.`age`,    -- 用户年龄
    24.   t2.`number`    -- 订单编号
    25. FROM
    26.   USER t1       -- 用户表
    27. LEFT OUTER JOIN
    28.   orderlist t2  -- 订单表
    29. ON
    30.   t1.`id` = t2.`uid`
    31. WHERE
    32.   t1.`age` > 23;

  • 查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表     订单编号 orderlist表
    4.   条件：user.id = orderlist.uid AND user.name IN ('张三','李四');
    5. */
    6. SELECT
    7.   t1.`id`,    -- 用户编号
    8.   t1.`name`,    -- 用户姓名
    9.   t1.`age`,    -- 用户年龄
    10.   t2.`number`    -- 订单编号
    11. FROM
    12.   USER t1,        -- 用户表
    13.   orderlist t2    -- 订单表
    14. WHERE
    15.   t1.`id` = t2.`uid`
    16.   AND
    17.   -- (t1.`name` = '张三' OR t1.`name` = '李四');
    18.   t1.`name` IN ('张三','李四');

  • 查询商品分类的编号、分类名称。分类下的商品名称

    1. /*
    2. 分析：
    3.   商品分类的编号、分类名称 category表     分类下的商品名称 product表
    4.   条件：category.id = product.cid
    5. */
    6. SELECT
    7.   t1.`id`,    -- 分类编号
    8.   t1.`name`,    -- 分类名称
    9.   t2.`name`    -- 商品名称
    10. FROM
    11.   category t1,    -- 商品分类表
    12.   product t2        -- 商品表
    13. WHERE
    14.   t1.`id` = t2.`cid`;

  • 查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称

    1. /*
    2. 分析：
    3.   商品分类的编号、分类名称 category表     分类下的商品名称 product表
    4.   条件：category.id = product.cid
    5.   查询所有的商品分类，使用左外连接
    6. */
    7. SELECT
    8.   t1.`id`,    -- 分类编号
    9.   t1.`name`,    -- 分类名称
    10.   t2.`name`    -- 商品名称
    11. FROM
    12.   category t1    -- 商品分类表
    13. LEFT OUTER JOIN
    14.   product t2    -- 商品表
    15. ON
    16.   t1.`id` = t2.`cid`;

  • 查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称

    1. /*
    2. 分析：
    3.   商品分类的编号、分类名称 category表     分类下的商品名称 product表
    4.   条件：category.id = product.cid
    5.   查询所有的商品信息，使用右外连接
    6. */
    7. SELECT
    8.   t1.`id`,    -- 分类编号
    9.   t1.`name`,    -- 分类名称
    10.   t2.`name`    -- 商品名称
    11. FROM
    12.   category t1    -- 商品分类表
    13. RIGHT OUTER JOIN
    14.   product t2    -- 商品表
    15. ON
    16.   t1.`id` = t2.`cid`;

  • 查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
    4.   条件：us_pro.uid = user.id AND us_pro.pid = product.id
    5. */
    6. SELECT
    7.   t1.`id`,    -- 用户编号
    8.   t1.`name`,    -- 用户名称
    9.   t1.`age`,    -- 用户年龄
    10.   t2.`name`    -- 商品名称
    11. FROM
    12.   USER t1,    -- 用户表
    13.   product t2,    -- 商品表
    14.   us_pro t3    -- 中间表
    15. WHERE
    16.   t3.`uid` = t1.`id`
    17.   AND
    18.   t3.`pid` = t2.`id`;

  • 查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

    1. /*
    2. 分析：
    3.   用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
    4.   条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四')
    5. */
    6. SELECT
    7.   t1.`id`,    -- 用户编号
    8.   t1.`name`,    -- 用户名称
    9.   t1.`age`,    -- 用户年龄
    10.   t2.`name`    -- 商品名称
    11. FROM
    12.   USER t1,    -- 用户表
    13.   product t2,    -- 商品表
    14.   us_pro t3    -- 中间表
    15. WHERE
    16.   (t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`)
    17.   AND
    18.   -- (t1.`name` = '张三' or t1.`name` = '李四');
    19.   t1.`name` IN ('张三','李四');

    3. 视图

    3.1 视图的概念

  • 视图是一种虚拟存在的数据表

  • 这个虚拟的表并不在数据库中实际存在
  • 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可

  • 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

    3.2 视图的好处

  • 简单

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

      3.3 视图数据准备

      ```sql — 创建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);

  1. <a name="JCPVO"></a>
  2. ## 3.4 视图的创建
  4. - 创建视图语法
  5. ```sql
  6. -- 标准语法
  7. CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
  • 普通多表查询，查询城市和所属国家 ```sql — 普通多表查询，查询城市和所属国家 SELECT t1.*, t2.country_name FROM city t1, country t2 WHERE t1.cid = t2.id;

— 经常需要查询这样的数据，就可以创建一个视图

  2. - 创建视图基本演示
  3. ```sql
  4. -- 创建一个视图。将查询出来的结果保存到这张虚拟表中
  5. CREATE
  6. VIEW
  7.     city_country
  8. AS
  9.     SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

  • 创建视图并指定列名基本演示

    1. -- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中
    2. CREATE
    3. VIEW
    4.   city_country2 (city_id,city_name,cid,country_name) 
    5. AS
    6.   SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

    3.5 视图的查询

  • 查询视图语法

    1. -- 标准语法
    2. SELECT * FROM 视图名称;

  • 查询视图基本演示 ```sql — 查询视图。查询这张虚拟表，就等效于查询城市和所属国家 SELECT * FROM city_country;

— 查询指定列名的视图 SELECT * FROM city_country2;

— 查询所有数据表，视图也会查询出来 SHOW TABLES;

  2. - 查询视图创建语法
  3. ```sql
  4. -- 标准语法
  5. SHOW CREATE VIEW 视图名称;

  • 查询视图创建语句基本演示

    1. SHOW CREATE VIEW city_country;

    3.6 视图的修改

  • 修改视图表中的数据 ```sql — 标准语法 UPDATE 视图名称 SET 列名=值 WHERE 条件;

— 修改视图表中的城市名称北京为北京市 UPDATE city_country SET city_name=’北京市’ WHERE city_name=’北京’;

— 查询视图 SELECT * FROM city_country;

— 查询city表,北京也修改为了北京市 SELECT * FROM city;

— 注意：视图表数据修改，会自动修改源表中的数据

  2. - 修改视图表结构
  3. ```sql
  4. -- 标准语法
  5. ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
  7. -- 查询视图2
  8. SELECT * FROM city_country2;
  10. -- 修改视图2的列名city_id为id
  11. ALTER
  12. VIEW
  13.     city_country2 (id,city_name,cid,country_name)
  14. AS
  15.     SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

3.7 视图的删除

  1. -- 标准语法
  2. DROP VIEW [IF EXISTS] 视图名称;
  4. -- 删除视图
  5. DROP VIEW city_country;
  7. -- 删除视图2，如果存在则删除
  8. DROP VIEW IF EXISTS city_country2;

3.8 视图的总结

  • 视图是一种虚拟存在的数据表
  • 这个虚拟的表并不在数据库中实际存在
  • 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
  • 视图的好处
    • 简单
    • 安全
    • 数据独立

      4. 数据库备份和恢复

      4.1 命令行方式

      ```sql

      备份

      登录到MySQL服务器，输入：mysqldump -u root -p 数据库名称 > 文件保存路径

恢复

1.登录MySQL数据库； 2.删除已备份的数据库； 3.重新创建名称相同的数据库； 4.使用该数据库； 5.导入文件执行：source备份文件全路径。 ``` 演示如下：
备份：
image.png
恢复：
前四步截图略！
image.png

4.2 图形化界面方式

  • 备份

05.png
06.png
07.png

  • 恢复

08.png
09.png
10.png
11.png