前言

重新温习一下MySQL，将以前的知识系统的总结。

扩展

Liunx 全操作过程 【JDK1.8配置、MySQL、Docker、mariaDB、nginx、tomcat、redis、各种问题解决总结】

mysql基础语句

登录mysql

mysql -u root -p 敲回车，输入密码 初始密码查看：cat /var/log/mysqld.log 在root@localhost: 后面的就是初始密码

修改mysql登录密码

  1. set global validate_password_policy=0;
  2. set global validate_password_length=1;
  3. set password=password('密码')

授予远程连接权限

  1. //授权
  2. grant all privileges on *.* to 'root' @'%' identified by '密码';
  3. //刷新
  4. flush privileges;

关闭Linux系统防火墙

  1. systemctl stop firewalld.service

数据库的注释：

  • 单行注释：— 注释内容 #注释内容(mysql特有)
  • 多行注释：/ 注释内容 /

    SQL分类

    DDL(Data Definition Language)数据定义语言
    用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
    DML(Data Manipulation Language)数据操作语言
    用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
    DQL(Data Query Language)数据查询语言
    用来查询数据库中表的记录(数据)。关键字：select, where 等
    DCL(Data Control Language)数据控制语言(了解)
    用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

    DDL-操作数据库

    ```sql — 查询所有数据库 SHOW DATEBASES;

— 查询某个数据库的创建语句 SHOW CREATE DATABASE 数据库名称;

— 查看mysql数据库的创建格式 SHOW CREATE DATABASE mysql;

— 创建数据库 CREATE DATABASE 数据库名称;

— 创建数据库（判断数据库是否存在，不存在则创建） CREATE DATABASE IF NOT EXISTS 数据库名称;

— 创建数据库、并指定字符集 CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

— 删除数据库 DROP DATABASE 数据库名称;

— 删除数据库（判断数据库是否存在，如果存在则删除） DROP DATABASE IF EXISTS 数据库名称;

— 查询正在使用的数据库名称 SELECT DATABASE();

— 使用数据库 USE 数据库名称;

  1. <a name="ttgze"></a>
  2. ## DDL-操作数据表
  3. ```sql
  4. -- 查询数据库中所有的数据表
  5. SHOW TABLES;
  7. -- 查询表结构
  8. DESC 表名;
  10. -- 查询表字符集
  11. SHOW TABLE STATUS FROM 数据库名 LIKE '表名';
  13. -- 创建数据表
  14. CREATE TABLE 表名(
  15.     列名1 数据类型1,
  16.     列名2 数据类型2,
  17.     ...
  18.     列名n 数据类型n,
  19. );
  21. 数据类型
  22. 1. int ：整数类型
  23.     eg ：age int  -- 年龄
  24. 2. double ： 小数类型
  25.     eg : score double(5,2) -- 成绩
  26. 3. date : 日期，只能包含年月日 -- yyyy-MM-dd
  27. 4. datetime : 日期，包含年月日时分秒 -- yyyy-MM-dd HH:mm:ss
  28. 5. timestamp : 时间戳类型，包含年月日时分秒 -- yyyy-MM-dd HH:mm:ss
  29.     注意：时间戳类型可以为空，默认使用当前系统时间
  30. 6. varchar : 字符串
  31.     eg : name varchar(20)
  33. -- 创建临时数据表(关闭数据库，临时表就不存在）
  34. CREATE TEMPORARY TABLE 表名(
  35.     列名1 数据类型1,
  36.     列名2 数据类型2,
  37.     ...
  38.     列名n 数据类型n,
  39. );
  41. -- 复制表
  42. CREATE TABLE 表名 LIKE 被复制的表名;
  44. -- 修改表名
  45. ALTER TABLE 表名 RENAME TO 新表名;
  47. -- 添加字段
  48. ALTER TABLE 表名 ADD 列名 数据类型;
  50. -- 修改数据类型
  51. ALTER TABLE 表名 MODIFY 列名 新数据类型;
  53. -- 修改列名和数据类型
  54. ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
  56. -- 删除列
  57. ALTER TABLE 表名 DROP 列名;
  59. -- 删除数据表
  60. DROP TABLE 表名;
  62. -- 删除数据表（判断数据表是否存在，存在则删除）
  63. DROP TABLE IF EXISTS 表名;

DML-操作数据表数据

  1. -- 增加数据（对应列名的数据）
  2. INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);
  4. -- 增加所有的数据
  5. INSERT INTO 表名 VALUES (值1,值2,...);
  7. -- 批量增加数据
  8. INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
  10. -- 修改数据
  11. UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... [where 条件];
  13. -- 删除数据
  14. DELETE FROM 表名 [where 条件];

DQL-单表查询

  1. -- 查询
  2. select
  3.     字段列表
  4. from
  5.     表名列表
  6. where
  7.     条件列表
  8. group by
  9.     分组字段
  10. having
  11.     分组之后的条件
  12. order by
  13.     排序
  14. limit
  15.     分页限定
  17. -- 查询全部
  18. SELECT * FROM 表名;
  20. -- 去除重复查询
  21. SELECT DISTINCT 列名1,列名2,... FROM 表名;
  23. -- 计算列的值(四则运算)
  24. SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名;
  26.     -- 查看商品名称，商品库存数量加10
  27.     eg : SELECT name,stock+10 FROM product;
  28.     -- 查看商品名称，商品库存数量加10，并进行非空判断
  29.     eg : SELECT name,IFNULL(stock,0)+10 FROM product;
  31. -- 起别名
  32. SELECT 列名1,列名2,... AS 别名 FROM 表名;

条件查询

符号 功能
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN … AND … 在某个范围之内(都包含)
IN(…) 多选一
LIKE 占位符 模糊查询 _单个任意字符 %多个任意字符
IS NULL 是NULL
IS NOT NULL 不是NULL
AND 或 && 并且
OR 或 || 或者
NOT 或 ! 非，不是

例子：

  1. -- 标准语法
  2. SELECT 列名 FROM 表名 WHERE 条件;
  4. -- 查询库存大于20的商品信息
  5. SELECT * FROM product WHERE stock > 20;
  7. -- 查询品牌为华为的商品信息
  8. SELECT * FROM product WHERE brand='华为';
  10. -- 查询金额在4000 ~ 6000之间的商品信息
  11. SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
  12. SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
  14. -- 查询库存为14、30、23的商品信息
  15. SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
  16. SELECT * FROM product WHERE stock IN(14,30,23);
  18. -- 查询库存为null的商品信息
  19. SELECT * FROM product WHERE stock IS NULL;
  20. -- 查询库存不为null的商品信息
  21. SELECT * FROM product WHERE stock IS NOT NULL;
  23. -- 查询名称以小米为开头的商品信息
  24. SELECT * FROM product WHERE NAME LIKE '小米%';
  26. -- 查询名称第二个字是为的商品信息
  27. SELECT * FROM product WHERE NAME LIKE '_为%';
  29. -- 查询名称为四个字符的商品信息
  30. SELECT * FROM product WHERE NAME LIKE '____';
  32. -- 查询名称中包含电脑的商品信息
  33. SELECT * FROM product WHERE NAME LIKE '%电脑%';

聚合函数

  • 将一列数据作为一个整体，进行纵向的计算
    - 聚合函数分类
函数名 功能
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值 
  1. -- 标准语法
  2. SELECT 函数名(列名) FROM 表名 [WHERE 条件];
  4. -- 计算product表中总记录条数
  5. SELECT COUNT(*) FROM product;
  7. -- 获取最高价格
  8. SELECT MAX(price) FROM product;
  9. -- 获取最高价格的商品名称
  10. SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);
  12. -- 获取最低库存
  13. SELECT MIN(stock) FROM product;
  14. -- 获取最低库存的商品名称
  15. SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);
  17. -- 获取总库存数量
  18. SELECT SUM(stock) FROM product;
  19. -- 获取品牌为苹果的总库存数量
  20. SELECT SUM(stock) FROM product WHERE brand='苹果';
  22. -- 获取品牌为小米的平均商品价格
  23. SELECT AVG(price) FROM product WHERE brand='小米';

排序查询

注意：多个排序条件，当前边的条件值一样时，才会判断第二条件

  1. -- 排序
  2. SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
  4. -- 按照库存升序排序
  5. SELECT * FROM product ORDER BY stock ASC;
  7. -- 查询名称中包含手机的商品信息。按照金额降序排序
  8. SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
  10. -- 按照金额升序排序，如果金额相同，按照库存降序排列
  11. SELECT * FROM product ORDER BY price ASC,stock DESC;

分组查询

  1. -- 标准语法
  2. SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
  4. -- 按照品牌分组，获取每组商品的总金额
  5. SELECT brand,SUM(price) FROM product GROUP BY brand;
  7. -- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
  8. SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
  10. -- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
  11. SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
  13. -- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
  14. SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000 ORDER BY getSum DESC;

分页查询

  1. -- 标准语法
  2. SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式] LIMIT 开始索引,查询条数;
  3. -- 公式：开始索引 = (当前页码-1) * 每页显示的条数
  5. -- 每页显示2条数据
  6. SELECT * FROM product LIMIT 0,2;  -- 第一页 开始索引=(1-1) * 2
  7. SELECT * FROM product LIMIT 2,2;  -- 第二页 开始索引=(2-1) * 2
  8. SELECT * FROM product LIMIT 4,2;  -- 第三页 开始索引=(3-1) * 2
  9. SELECT * FROM product LIMIT 6,2;  -- 第四页 开始索引=(4-1) * 2

外键

约束的概念

  • 对表中的数据进行限定，保证数据的正确性、有效性、完整性！

    约束的分类

    | 约束 | 说明 | | —- | —- | | PRIMARY KEY | 主键约束 | | PRIMARY KEY AUTO_INCREMENT | 主键、自动增长 | | UNIQUE | 唯一约束 | | NOT NULL | 非空约束 | | FOREIGN KEY | 外键约束 | | FOREIGN KEY ON UPDATE CASCADE | 外键级联更新 | | FOREIGN KEY ON DELETE CASCADE | 外键级联删除 |

主键约束

  1. -- 建表时添加主键约束
  2. CREATE TABLE 表名(
  3.     列名 数据类型 PRIMARY KEY,
  4.     列名 数据类型,
  5.     ...
  6. );
  8. -- 创建student表
  9. CREATE TABLE student(
  10.     id INT PRIMARY KEY  -- 给id添加主键约束
  11. );
  13. -- 添加数据
  14. INSERT INTO student VALUES (1),(2);
  15. -- 主键默认唯一，添加重复数据，会报错
  16. INSERT INTO student VALUES (2);
  17. -- 主键默认非空，不能添加null的数据
  18. INSERT INTO student VALUES (NULL);
  20. -- 删除主键
  21. ALTER TABLE 表名 DROP PRIMARY KEY;
  23. -- 建表后单独添加主键
  24. ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

主键自动增长约束

  1. -- 建表时添加主键自增约束
  2. CREATE TABLE 表名(
  3.     列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
  4.     列名 数据类型,
  5.     ...
  6. );
  8. -- 创建student2表
  9. CREATE TABLE student2(
  10.     id INT PRIMARY KEY AUTO_INCREMENT    -- 给id添加主键自增约束
  11. );
  13. -- 添加数据
  14. INSERT INTO student2 VALUES (1),(2);
  15. -- 添加null值，会自动增长
  16. INSERT INTO student2 VALUES (NULL),(NULL);
  18. -- 删除自动增长
  19. ALTER TABLE 表名 MODIFY 列名 数据类型;
  21. -- 建表后单独添加自动增长
  22. ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

唯一约束

  1. -- 建表时添加唯一约束
  2. CREATE TABLE 表名(
  3.     列名 数据类型 UNIQUE,
  4.     列名 数据类型,
  5.     ...
  6. );
  8. -- 创建student3表
  9. CREATE TABLE student3(
  10.     id INT PRIMARY KEY AUTO_INCREMENT,
  11.     tel VARCHAR(20) UNIQUE    -- 给tel列添加唯一约束
  12. );
  14. -- 添加数据
  15. INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666');
  16. -- 添加重复数据，会报错
  17. INSERT INTO student3 VALUES (NULL,'18666666666');
  19. -- 删除唯一约束
  20. ALTER TABLE 表名 DROP INDEX 列名;
  22. -- 建表后单独添加唯一约束
  23. ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

非空约束

  1. -- 建表时添加非空约束
  2. CREATE TABLE 表名(
  3.     列名 数据类型 NOT NULL,
  4.     列名 数据类型,
  5.     ...
  6. );
  8. -- 创建student4表
  9. CREATE TABLE student4(
  10.     id INT PRIMARY KEY AUTO_INCREMENT,
  11.     NAME VARCHAR(20) NOT NULL    -- 给name添加非空约束
  12. );
  14. -- 添加数据
  15. INSERT INTO student4 VALUES (NULL,'张三'),(NULL,'李四');
  16. -- 添加null值，会报错
  17. INSERT INTO student4 VALUES (NULL,NULL);
  19. -- 删除非空约束
  20. ALTER TABLE 表名 MODIFY 列名 数据类型;
  22. -- 建表后单独添加非空约束
  23. ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

外键约束

  1. -- 建表时添加外键约束
  2. CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  4. CREATE TABLE USER(
  5.     id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  6.     NAME VARCHAR(20) NOT NULL             -- 姓名
  7. );
  9. -- 添加用户数据
  10. INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
  12. -- 创建order订单表
  13. CREATE TABLE order(
  14.     id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  15.     number VARCHAR(20) NOT NULL,          -- 订单编号
  16.     uid INT,                              -- 订单所属用户
  17.     CONSTRAINT user_order FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
  18. );
  21. -- 添加订单数据
  22. INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1);
  24. -- 添加一个订单，但是没有所属用户。无法添加
  25. INSERT INTO orderlist VALUES (NULL,'hm007',8);
  26. -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除
  27. DELETE FROM USER WHERE NAME='王五';
  29. -- 删除外键约束
  30. ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  32. -- 建表后添加外键约束
  33. ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

外键的级联更新和级联删除

什么是级联更新和级联删除

  • 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
  • 当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改
    ```sql — 添加外键约束，同时添加级联更新 标准语法 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 user_order;

— 添加外键约束，同时添加级联更新和级联删除 ALTER TABLE orderlist ADD CONSTRAINT user_order 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. <a name="NaYo5"></a>
  2. ## 多表设计
  3. <a name="K25Cd"></a>
  4. ### 一对一
  5. **业务**<br />人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！<br />**实现原则**<br />在任意一个表建立外键，去关联另外一个表的主键<br />**示意图**<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/22478710/1645872988995-2fac54ad-6007-44a4-8c3b-dc9187d0a217.png#clientId=ue45e56b8-7177-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=ubae6dd1d&margin=%5Bobject%20Object%5D&name=image.png&originHeight=406&originWidth=440&originalType=url&ratio=1&rotation=0&showTitle=false&size=19309&status=done&style=none&taskId=ud5ee17d3-2c90-424e-9b24-9ba692665f5&title=)<br />**增删改查的影响范围**<br />**增**：数据可以不分先后顺序增加，也不需要必要的对应关系，一个人没有身份证的数据也是可以存在，一个无效身份证的数据也是可以存在。<br />在业务角度上讲，需要程序员严谨的增加一对一的数据，不然会产生脏数据。<br />**删**：数据可以任意删除，没有任何限制。<br />**改**：两张表的数据可以任意修改，将约束外键修改其他值，也没有任何限制。<br />当程序想要进行修改，那么一定不能直接操作外键约束，最好只操作对应的信息。<br />**查**：查询两张表的数据，当使用内连接时，可以查出一对一的关系，建议使用。<br />当使用左连接时，根据左侧表为主体，查询出左侧表的全部数据，右侧表中有对应关系的数据才会查出，反之全部null值显示。<br />当使用右连接时，与上面相反，以右侧表为主体。<br />当使用合并连接时，会将重复的数据排除掉。<br />当使用子查询时，可以查询出脏数据。
  6. ```sql
  7. -- 创建db5数据库
  8. CREATE DATABASE db5 CHARACTER SET utf8;
  10. -- 使用db5数据库
  11. USE db5;
  13. -- 创建person表
  14. CREATE TABLE person(
  15.     id INT PRIMARY KEY AUTO_INCREMENT,
  16.     NAME VARCHAR(20)
  17. )ENGINE = InnoDB DEFAULT CHARSET = utf8;
  19. -- 添加数据
  20. INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');
  22. -- 创建card表
  23. CREATE TABLE card(
  24.     id INT PRIMARY KEY AUTO_INCREMENT,
  25.     number VARCHAR(50),
  26.     pid INT UNIQUE,
  27.     CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 添加外键
  28. )ENGINE = InnoDB DEFAULT CHARSET = utf8;
  30. -- 添加数据
  31. INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',3);
  33. -- 查询还没有身份证的公民
  34. select * from person where id not in (select pid from card);
  35. +----+------+
  36. | id | NAME |
  37. +----+------+
  38. |  2 | 李四 |
  39. +----+------+
  40. 1 row in set
  42. -- 查询有身份证的公民
  43. select * from person join card on person.id = card.pid;
  44. +----+------+----+--------+-----+
  45. | id | NAME | id | number | pid |
  46. +----+------+----+--------+-----+
  47. |  1 | 张三 |  1 | 12345  |   1 |
  48. +----+------+----+--------+-----+
  49. 1 row in set

一对多

业务
用户和订单。一个用户可以有多个订单！
实现原则
在多的一方，建立外键约束，来关联一的一方主键
示意图
image.png
增删改查的影响范围
增：增加两张表的数据，可以任意增加数据，但是不建议没有先创建主表数据，按照一对多原则，必须有一才能有多的关系。多则可以不用创建。
删：可以随便删除数据，但是不允许删除主表数据。
改：不可以修改两张表任意的外键约束，但是可以修改业务数据。
查：内连接和左连接都可以查询出一对多关联的数据，右连接会查询出没有一对多关联的数据，也会查询出一对多关联的数据。根据业务进行选择使用那种方式。

  1. /*
  2.     用户和订单
  3. */
  4. -- 创建user表
  5. CREATE TABLE USER(
  6.     id INT PRIMARY KEY AUTO_INCREMENT,
  7.     NAME VARCHAR(20)
  8. )ENGINE = InnoDB DEFAULT CHARSET = utf8;
  10. -- 添加数据
  11. INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
  13. -- 创建orderlist表
  14. CREATE TABLE orderlist(
  15.     id INT PRIMARY KEY AUTO_INCREMENT,
  16.     number VARCHAR(20),
  17.     uid INT,
  18.     CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)  -- 添加外键约束
  19. )ENGINE = InnoDB DEFAULT CHARSET = utf8;
  21. -- 添加数据
  22. INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);

多对多

业务
学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
实现原则
需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键
示意图
image.png
增删改查的影响范围
增：除了中间表建立联系，另外两张表可以任意增加数据，中间表也可以随便增加数据，但是在业务中不允许出现中间表，胡乱增加不存在的关系，增加脏数据的存在。
删：可以删除这三张表的所有数据，根据实际业务判断。
改：可以随意修改这三张表的数据，包括外键约束都可以修改，按照实际业务中，外键约束不能进行随意修改。
查：

  1. -- 创建student表
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(20)
  5. );
  6. -- 添加数据
  7. INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
  9. -- 创建course表
  10. CREATE TABLE course(
  11.     id INT PRIMARY KEY AUTO_INCREMENT,
  12.     NAME VARCHAR(10)
  13. );
  15. -- 添加数据
  16. INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
  18. -- 创建中间表
  19. CREATE TABLE stu_course(
  20.     id INT PRIMARY KEY AUTO_INCREMENT,
  21.     sid INT, -- 用于和student表的id进行外键关联
  22.     cid INT, -- 用于和course表的id进行外键关联
  23.     CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
  24.     CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
  25. );
  27. -- 添加数据
  28. INSERT INTO stu_course VALUES (NULL,1,1),(NULL,6,2),(NULL,2,1),(NULL,2,2);
  30. -- 子查询
  31. select * from (select * from student ) s ,(select * from stu_course) sc,(select * from course) c where s.id=sc.sid and c.id = sc.cid;
  32. +----+------+----+-----+-----+----+------+
  33. | id | NAME | id | sid | cid | id | NAME |
  34. +----+------+----+-----+-----+----+------+
  35. |  1 | 张三 |  1 |   1 |   1 |  1 | 语文 |
  36. |  2 | 李四 |  3 |   2 |   1 |  1 | 语文 |
  37. |  2 | 李四 |  4 |   2 |   2 |  2 | 数学 |
  38. +----+------+----+-----+-----+----+------+
  39. 3 rows in set
  41. -- 内连接
  42. select * from student s join stu_course sc on sc.sid = s.id join course c on sc.cid = c.id;
  43. +----+------+----+-----+-----+----+------+
  44. | id | NAME | id | sid | cid | id | NAME |
  45. +----+------+----+-----+-----+----+------+
  46. |  1 | 张三 |  1 |   1 |   1 |  1 | 语文 |
  47. |  2 | 李四 |  3 |   2 |   1 |  1 | 语文 |
  48. |  2 | 李四 |  4 |   2 |   2 |  2 | 数学 |
  49. +----+------+----+-----+-----+----+------+
  50. 3 rows in set
  52. -- 左连接
  53. select * from student s left join stu_course sc on sc.sid = s.id left
  54.  join course c on sc.cid = c.id;
  55. +----+------+------+------+------+------+------+
  56. | id | NAME | id   | sid  | cid  | id   | NAME |
  57. +----+------+------+------+------+------+------+
  58. |  1 | 张三 |    1 |    1 |    1 |    1 | 语文 |
  59. |  2 | 李四 |    3 |    2 |    1 |    1 | 语文 |
  60. |  2 | 李四 |    4 |    2 |    2 |    2 | 数学 |
  61. |  3 | 王五 | NULL | NULL | NULL | NULL | NULL |
  62. +----+------+------+------+------+------+------+
  63. 4 rows in set
  65. -- 右连接
  66. select * from student s right join
  67.  stu_course sc on sc.sid = s.id right join course c on sc.cid = c.id;
  68. +------+------+------+------+------+----+------+
  69. | id   | NAME | id   | sid  | cid  | id | NAME |
  70. +------+------+------+------+------+----+------+
  71. |    1 | 张三 |    1 |    1 |    1 |  1 | 语文 |
  72. |    2 | 李四 |    3 |    2 |    1 |  1 | 语文 |
  73. | NULL | NULL |    2 |    6 |    2 |  2 | 数学 |
  74. |    2 | 李四 |    4 |    2 |    2 |  2 | 数学 |
  75. | NULL | NULL | NULL | NULL | NULL |  3 | 英语 |
  76. +------+------+------+------+------+----+------+
  77. 5 rows in set

多表查询

  1. -- 笛卡尔积查询
  2. SELECT 列名 FROM 表名1,表名2,...;
  4. -- 内连接查询[内连接查询的是两张表有交集的部分数据(有主外键关联的数据)]
  5. SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
  7. -- 隐式内连接
  8. SELECT 列名 FROM 表名1,表名2 WHERE 条件;
  10. -- 外连接查询[查询左表的全部数据，和左右两张表有交集部分的数据]
  11. SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
  13. -- 右外连接[查询右表的全部数据，和左右两张表有交集部分的数据]
  14. SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
  16. -- 子查询[查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！]
  17. -- 子查询-结果是单行单列
  18. SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);
  20. -- 子查询-结果是多行单列
  21. SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
  23. -- 子查询-结果是多行多列
  24. SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];