热门标签
热门文章
- 1并发的线程入门到并发安全原理解析，offer拿到手软
- 2Conda历史版本下载地址和python对应关系
- 3树莓派安装python3.8_在树莓派(Raspberry Pi)上编译安装更新版本的Python
- 4java 日期是否合法_java判断是否是合法日期的方法
- 5vue+element UI 学习总结笔记(一)_vue+elementui一点
- 6使用marked和highlight.js对GPT接口返回的代码块渲染，高亮显示，支持复制，和选择不同的高亮样式_marked api
- 7react项目服务器地址配置,react项目前端配置build打包不同环境服务器地址
- 8SpringBoot 2.2.5 整合EasyExcel 2.1.6，附带Excel操作工具类，Excel文件转CSV格式工具类_easyexcel工具类
- 9如何使用MCSM搭建我的世界Java版服务器并实现远程联机游戏
- 10python解释器下载安装、VSCode安装python扩展插件以及配置python开发环境、在VSCode上调试python代码的方法与技巧、创建虚拟环境，在虚拟环境中安装，使用第三方包_vscode安装python插件
当前位置: article > 正文
MySQL所有基本操作详解_mysql操作
作者：不正经 | 2024-02-09 13:35:39
赞
踩
mysql操作
文章目录
一.MySQL的基本操作
首先sql操作中的关键字的是大小写不敏感的，create 和CREATE是一样的。
1.库操作
1.1 查看数据库
语法：
show databases;
- 1
示例：
- show 和databases 之间有一个或者多个空格
- 注意是databases而不是database
- 结尾分号是英文形式，分号在SQL中是表示一行执行+
- 代码的，如果语句后面么有分号，那么默认是要一句代码分多行来写（如下图）
- 下图中是每次执行完一个sql语句之后，会得到的一个反馈，反馈会告诉我们，当前结果有多少行记录，以及消耗了多少时间。在下图中set表示集合，所以意思就是在当前集合里有四行，执行共花费0.01秒（sec = second 秒）。有时会显示0.00 sec.,这表示小于10毫秒，所以不显示。
1.2 创建数据库
语法：
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
- 1
- 2
- 3
- 4
- 5
-
大写的表示关键字
-
[] 是可选项
-
CHARACTER SET: 指定数据库采用的字符集
-
COLLATE: 指定数据库字符集的校验规则
-
数据库名字可由数字，字母，下划线组成，数字不能开头（和java变量名一样），名字也不能是sql关键字（例如 show 、database）
-
如果就是想拿关键词作为数据库名，可以使用反引号`把数据库名引起来。
这里面的错误是ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘databese’ at line 1。
syntax是句法的意思，就理解成语法就行。manual——手册
对我们比较重要是最后 near 'databese' at line 1这一句，表面错误在第一行的database附近
- 创建数据库的时候可以指定字符集和校验规则
当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci
注意：MySQL的utf8编码不是真正的utf8，没有包含某些复杂的中文字符。MySQL真正的utf8是使用utf8mb4，建议大家都使用utf8mb4
示例
1.创建名为db_test1的数据库
CREATE DATABASE db_test1;
- 1
2.如果系统没有db_test2的数据库，则创建一个名叫db_test2的数据库，如果有则不创建
CREATE DATABASE IF NOT EXISTS db_test2;
- 1
3.如果系统没有db_test的数据库，则创建一个使用utf8mb4字符集的db_test数据库，如果有则不创建
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;
- 1
1.3 使用数据库
use 数据库名;
- 1
示例
使用该数据库后会有相应的提示，表明数据库已经切换了。
1.4 删除数据库
语法：
DROP DATABASE [IF EXISTS] db_name;
- 1
删除操作是非常危险的！一旦删除，数据就没了，难以恢复。
数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除
示例
drop database if exists db_test1;
drop database if exists db_test2;
- 1
- 2
如果database 里有db_test1表就删除database.
2.表操作
需要操作数据库中的表时，需要先使用该数据库
2.1 创建表
语法：
CREATETABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
);
- 1
- 2
- 3
- 4
- 5
示例1：
create table stu_test (
id int,
name varchar(20) comment '姓名',
password varchar(50) comment '密码',
age int,
sex varchar(1),
birthday timestamp,
amout decimal(13,2),
resume text
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- comment 相当于注释，这个不太好用，只能在创建表的时候使用，所以一般来说我们更推荐使用#或者 – 来表示注释
2.2 查看数据库中的表结构
语法：
use 数据库名;
desc 表名;
- 1
- 2
示例：
desc stu_test;
- 1
- desc是describe的缩写。
- 查看结果经常会有int(11),这表示这一列哭护短查询显示的时候最多显示11个字符。这只是显示宽度，这与实际存储是没有关系的。
- NULL这一列表示的是否可以为空，如果是YES表示可以。
- Default 表示默认值。
2.3删除数据库中的表结构
语法：
use database_name;
drop table 表名;
- 1
- 2
2.4查询某个数据库内的所有表名
语法：
use database_name;
show tables;
- 1
- 2
3.SQL中的数据类型
3.1数值类型
- 数值类型可以指定为无符号（unsigned），表示不取负数。
- 对于整型类型的范围：有符号范围：-2^（类型字节数8-1）到2^（类型字节数8-1）-1，如int是4字节，就是-2^31到2^31-1；无符号范围：0到2^（类型字节数*8）-1，如int就是2^32-1。
- 在设计的时候尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。
- BIT常用来表示二进制数字串。
- DOUBLE(3,1)表示三位有效数字，小数位数位为1位，所以10.2合法，10.20就不合法了。
DECIMA的进一步说明
FLOAT和DOUBLE有一个很严重的问题，表示有些数据的时候，不能精确表示（存在误差），我们都知道FLOAT,DOUBLE在底层是通过多少位的底数多少位指数这种形式去表示数据的，这样带来的好处是计算速度快，存储空间小，但是会有误差。所以此时SQL提供DECIMAL(英文原意是十进制)来保存精确小数，而其底层的保存方式类型与字符串。
3.2字符串类型
- VACHAR是一个可变字符串，SIZE可以指定最大长度，单位是“字符”，所以VACHAR(10)如果表示名字的话，一个名字最多有可以存十个字，而不是五个字。同时VACHAR(10)也并不是在一开始就占10个字符的存储空间，这是动态变化的。
- BLOB 存储的是二进制串，注意与BIT区别，BIT最多存64个二进制数，BLOB更长。比如我要存一个小一些的图片或者音频文件，就可以使用BLOB（大概64Kb）。
3.3日期类型
- TIMESTAMP是时间戳
4.MySQL的增删改查
CRUD 增删改查（Create（增） Restrieve（查） Updata（改） Delete（删除））；
4.1新增插入数据
先创建一个student表
mysql> create database base1 character set utf8mb4;; Query OK, 1 row affected (0.00 sec) mysql> use base1; Database changed mysql> DROP TABLE IF EXISTS student; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE student ( -> id INT NOT NULL, -> sn INT, -> name VARCHAR(20), -> qq_mail VARCHAR(20) -> ); mysql> desc student; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | sn | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | qq_mail | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) Query OK, 0 rows affected (0.02 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
4.1.1基础的插入
insert [into] 表名 values [列名] (值1，值2，值3.....);
- 1
实例
mysql> insert into student values(123,4456,'张三','123456@qq.com');
Query OK, 1 row affected (0.00 sec)
- 1
- 2
mysql> insert student (id,sn,name,qq_mail) values (123,4456,'张三','123456@qq.com');
Query OK, 1 row affected (0.00 sec)
- 1
- 2
注意
- SQL没有字符类型，所以‘’ ""都是表示字符串类型。
- 假如遇到这种错误
Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1显示的是不正确是字符值，往往可能是表格字符集的问题，需要将整个数据库都删除，去重新建立数据库和表格，并且一定要在创建数据库时指定字符集为utf8mb4(utf8mb4比utf8更加完整，多了对emoji表情的编码)。 - insert into里面into可以省略
4.1.2指定列的的插入
insert [into] 表名 (列名1,列名2,列名3......) values(值1,值2,值3,......)
- 1
示例：
mysql> insert student(id,qq_mail) values (213,'34567@qq.com');
Query OK, 1 row affected (0.00 sec)
- 1
- 2
注意：
- 在前面的表结构定义里面定义了
id INT NOT NULL,所以id不能为空，所以id是不能系统默认创建的
结果
mysql> select * from student;
+-----+------+--------+---------------+
| id | sn | name | qq_mail |
+-----+------+--------+---------------+
| 123 | 4456 | 张三 | 123456@qq.com |
| 123 | 4456 | 张三 | 123456@qq.com |
| 213 | NULL | NULL | 34567@qq.com |
+-----+------+--------+---------------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
4.1.3多次数据的的插入
示例：
mysql> insert into student values(1,1,"李四","1@qq.com"),(2,2,"李四","2@qq.com");
- 1
结果
mysql> select * from student;
+-----+------+--------+---------------+
| id | sn | name | qq_mail |
+-----+------+--------+---------------+
| 123 | 4456 | 张三 | 123456@qq.com |
| 123 | 4456 | 张三 | 123456@qq.com |
| 213 | NULL | NULL | 34567@qq.com |
| 1 | 1 | 李四 | 1@qq.com |
| 2 | 2 | 李四 | 2@qq.com |
+-----+------+--------+---------------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 一次插入N个记录是比一次插入一个记录，分N次插入效率要高，因为MySQL是c/s模式，每次请求都是需要客户端和服务器交互一次的。前者交互了一次，后者交互了多次。
4.1.4时间日期类型的插入
插入时间是通过特定的时间日期来表示时间日期的.
形如
‘2023-02-17 21:25:20’
示例
mysql> create table homework(id int,createTime datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into homework values(1,'2023-12-25 18:32:16');
Query OK, 1 row affected (0.00 sec)
mysql> select * from homework;
+------+---------------------+
| id | createTime |
+------+---------------------+
| 1 | 2023-12-25 18:32:16 |
+------+---------------------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
同时SQL还提供now()函数来返回当前时间。
示例：
mysql> insert into homework values(2,now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from homework;
+------+---------------------+
| id | createTime |
+------+---------------------+
| 1 | 2023-12-25 18:32:16 |
| 2 | 2023-06-02 12:04:26 |
+------+---------------------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
4.2查询数据SELECT
4.2.1全列查找
select * from 表名
- 1
在实际操作中执行select * 是非常危险的，因为实际项目中的数据量是非常大的，如果数据全部从服务器读取到客户端，数据带宽会拥堵。
4.2.2指定列查找
select 列名，列名 from 表名;
- 1
示例：
mysql> select id,name from student;
+-----+--------+
| id | name |
+-----+--------+
| 123 | 张三 |
| 123 | 张三 |
| 213 | NULL |
| 1 | 李四 |
| 2 | 李四 |
+-----+--------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
4.2.3 查询可以是表达式
示例：
先来创建一个考试成绩表
mysql> DROP TABLE IF EXISTS exam_result; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE exam_result ( -> id INT, -> name VARCHAR(20), -> chinese DECIMAL(3,1), -> math DECIMAL(3,1), -> english DECIMAL(3,1) -> ); Query OK, 0 rows affected (0.03 sec) mysql> -- 插入测试数据 mysql> INSERT INTO exam_result (id,name, chinese, math, english) VALUES -> (1,'唐三藏', 67, 98, 56), -> (2,'孙悟空', 87.5, 78, 77), -> (3,'猪悟能', 88, 98.5, 90), -> (4,'曹孟德', 82, 84, 67), -> (5,'刘玄德', 55.5, 85, 45), -> (6,'孙权', 70, 73, 78.5), -> (7,'宋公明', 75, 65, 30); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from exam_result; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
我们可以查询所有人数学成绩+10后的结果
mysql> -- 查询所有人的数学成绩+10分的结果
mysql> select math+10 from exam_result;
+---------+
| math+10 |
+---------+
| 108.0 |
| 88.0 |
| 108.5 |
| 94.0 |
| 95.0 |
| 83.0 |
| 75.0 |
+---------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
但是注意：经过上述这样的查询，数据库服务器硬盘里面的数据是没有变化的。因为mysql是C/S模式，用户在客户端输入的sql，通过请求发送给服务器，服务器在解析并执行sql把查询结果从硬盘里读取出来，通过网络响应还给客户端，客户端把这些数据以临时表的形式显示出来。
其实细心的同学可以发现实际在定义exam_result是math DECIMAL(3,1),也就是说math这一列的数据应该是三个有效数字，且小数点后一位，但是math+10不难发现，这里第一行就是108.0这是因为这里的表是临时表。
还可以将多个列放在一起计算
比如查询每个同学的平均成绩
mysql> select name,(math+chinese+english)/3 from exam_result;
+-----------+--------------------------+
| name | (math+chinese+english)/3 |
+-----------+--------------------------+
| 唐三藏 | 73.66667 |
| 孙悟空 | 80.83333 |
| 猪悟能 | 92.16667 |
| 曹孟德 | 77.66667 |
| 刘玄德 | 61.83333 |
| 孙权 | 73.83333 |
| 宋公明 | 56.66667 |
+-----------+--------------------------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
4.2.4指定别名
为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称
SELECT 表达式 [AS] 别名 [...] FROM table_name;
- 1
示例：
mysql> SELECT id, name, (chinese + math + english)/3 as 平均分 FROM exam_result;
+------+-----------+-----------+
| id | name | 平均分 |
+------+-----------+-----------+
| 1 | 唐三藏 | 73.66667 |
| 2 | 孙悟空 | 80.83333 |
| 3 | 猪悟能 | 92.16667 |
| 4 | 曹孟德 | 77.66667 |
| 5 | 刘玄德 | 61.83333 |
| 6 | 孙权 | 73.83333 |
| 7 | 宋公明 | 56.66667 |
+------+-----------+-----------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
4.3去重DISTINCT
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98.0 |
| 78.0 |
| 98.5 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
distinct 也可以实现多列的去重，但是只有每列的元素值都是相同的，才会去掉，有一列不同是sql认为是不可以去重的。
此外，下面这种写法也不对
mysql> select name,(distinct mat)h from exam_result; -- 错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct mat)h from exam_result' at line 1
mysql> select name, distinct math from exam_result; -- 错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct math from exam_result' at line 1
- 1
- 2
- 3
- 4
4.4 查询结果排序ORDER BY
4.4.1order by 子句
语法：
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
- 1
- 2
- 3
- 4
- 5
示例：
mysql> select * from exam_result order by math asc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
注意：
- 对于mySql而言，当我们没有指定order by （查询顺序）的人时候，此时显示的查询数据是顺序是不可预期的，代码的逻辑是不能依赖于此的。
- NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。
4.4.2使用表达式及别名排序
mysql> select *,(math+chinese+english) as 总分 from exam_result order by math desc; +------+-----------+---------+------+---------+--------+ | id | name | chinese | math | english | 总分 | +------+-----------+---------+------+---------+--------+ | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | 276.5 | | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | 221.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | 185.5 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | 233.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | 242.5 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | 221.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | 170.0 | +------+-----------+---------+------+---------+--------+ 7 rows in set (0.00 sec) mysql> select *,(math+chinese+english) as 总分 from exam_result order by 总分 desc; +------+-----------+---------+------+---------+--------+ | id | name | chinese | math | english | 总分 | +------+-----------+---------+------+---------+--------+ | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | 276.5 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | 242.5 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | 233.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | 221.5 | | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | 221.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | 185.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | 170.0 | +------+-----------+---------+------+---------+--------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
4.4.3可以对多个字段进行排序，排序优先级随书写顺序
示例：
mysql> select * from exam_result order by math asc,chinese desc,english asc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
4.5条件查询WHERE
比较运算符
逻辑运算符
注意：
- WHERE条件可以使用表达式，但不能使用别名。
- AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分
4.5.1比较运算
示例：
- 查询英语成绩小于60的同学信息
mysql> select * from exam_result where english < 60;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
注：这行代码的底层是这样的，针对数据库的表，进行遍历，取出每一行的数据，把数据带入到条件中，看是否满足条件，如果为真就保留，如果为假，就不保留。
- 查询语文成绩好于英语成绩的同学
mysql> select name,english,chinese from exam_result where english < chinese;
+-----------+---------+---------+
| name | english | chinese |
+-----------+---------+---------+
| 唐三藏 | 56.0 | 67.0 |
| 孙悟空 | 77.0 | 87.5 |
| 曹孟德 | 67.0 | 82.0 |
| 刘玄德 | 45.0 | 55.5 |
| 宋公明 | 30.0 | 75.0 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 查询总分在 200 分以下的同学
mysql> select * from exam_result where english+chinese+math > 200;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
注意
看下面的代码
mysql> select name,chinese+math+english as 总分 from exam_result where 总分 > 200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'
- 1
- 2
别名是不可以作为where条件的，这和sql的执行顺序有关，本身也是sql语法规定。上述代码的执行过程是：
1.遍历每一行；
2.把这一行带入到where的条件里去；
3.符合条件的结果，在根据select这里指定的列进行查询、计算。
但是order by 这个关键字是可以的
mysql> select name,chinese+math+english as 总分 from exam_result order by 总分 ;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 宋公明 | 170.0 |
| 刘玄德 | 185.5 |
| 唐三藏 | 221.0 |
| 孙权 | 221.5 |
| 曹孟德 | 233.0 |
| 孙悟空 | 242.5 |
| 猪悟能 | 276.5 |
+-----------+--------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
4.5.2逻辑运算
示例：
mysql> -- 查询语文成绩大于80分，且英语成绩大于80分的同学 mysql> SELECT * FROM exam_result WHERE chinese > 80 and english > 80; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | +------+-----------+---------+------+---------+ 1 row in set (0.00 sec) mysql> -- 查询语文成绩大于80分，或英语成绩大于80分的同学 mysql> SELECT * FROM exam_result WHERE chinese > 80 or english > 80; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | +------+-----------+---------+------+---------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
mysql> -- 观察AND 和 OR 的优先级： mysql> SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | +------+-----------+---------+------+---------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | +------+-----------+---------+------+---------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分.
4.5.3between and
示例：
mysql> -- 查询语文成绩在 [80, 90] 分的同学及语文成绩 mysql> SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90; +-----------+---------+ | name | chinese | +-----------+---------+ | 孙悟空 | 87.5 | | 猪悟能 | 88.0 | | 曹孟德 | 82.0 | +-----------+---------+ 3 rows in set (0.00 sec) mysql> -- 使用 AND 也可以实现 mysql> SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese -> <= 90; +-----------+---------+ | name | chinese | +-----------+---------+ | 孙悟空 | 87.5 | | 猪悟能 | 88.0 | | 曹孟德 | 82.0 | +-----------+---------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
4.5.4 in
示例：
mysql> -- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩 mysql> SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99); +-----------+------+ | name | math | +-----------+------+ | 唐三藏 | 98.0 | +-----------+------+ 1 row in set (0.00 sec) mysql> -- 使用 OR 也可以实现 mysql> SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math -> = 98 OR math = 99; +-----------+------+ | name | math | +-----------+------+ | 唐三藏 | 98.0 | +-----------+------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
4.5.5模糊查询：LIKE
like相对于正则表达式只支持两种用法
- 使用%代表任意N个字符（包括0个字符）；
- 使用_代表任意一个字符。
示例：
mysql> select * from exam_result where name like '孙%';
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
+------+-----------+---------+------+---------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
mysql> select * from exam_result where name like '孙_';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
4.5.6NULL 的查询：IS [NOT] NULL
在使用null作为查询条件即删选出某列为空的数据的时候可以使用 is null 和< = > null，这样的语句，但是此时要注意 = null、与< = >的区别
示例：
mysql> select * from exam_result; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | | 1 | 贾宝玉 | NULL | NULL | NULL | +------+--------+---------+------+---------+ 8 rows in set (0.00 sec) mysql> select * from exam_result where chinese = null; Empty set (0.00 sec) mysql> select * from exam_result where chinese <=> null; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 贾宝玉 | NULL | NULL | NULL | +------+--------+---------+------+---------+ 1 row in set (0.00 sec) mysql> select * from exam_result where chinese is not null; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | +------+--------+---------+------+---------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
注意
select * from exam_result where Chinese = null执行后并不会返回Chinese列等于null的行，这是因为chinese =null 执行成功后返回的就是null也就是false。也就是默认查询条件不成立，所以根本就不会筛选。
4.5分页查询LIMIT
语法：
-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
示例：
mysql> select * from exam_result limit 3; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | +------+--------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from exam_result limit 3 offset 2; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | +------+--------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from exam_result limit 3,2; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | +------+--------+---------+------+---------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
注意：limit s,n 与limit n offset s 这里面的s n 的顺序是相反的。s表示从哪开始显示，n表示显示多少行。
limit语句常常和其他查询条件语句一起用达到组合效果
示例
寻找总分前三名的学生
mysql> select id,name,chinese+math+english as 总分 from exam_result order by 总分 desc limit 3;
+------+--------+-------+
| id | name | 总分 |
+------+--------+-------+
| 3 | 猪悟能 | 276.5 |
| 2 | 孙悟空 | 242.5 |
| 4 | 曹孟德 | 233.0 |
+------+--------+-------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
4.6 修改语句UPDATE
语法：
UPDATE table_name SET 列名1 = 数值1 [, 列名2 = 数值2 ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
- 1
- 2
示例：
mysql> -- 将孙悟空的数学成绩变更为80分
mysql> update exam_result set math = 10 where name = "孙悟空";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> -- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> update exam_result set math = 60,chinese = 70 where name = "曹孟德";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> -- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> update exam_result set math = math+30 order by math+chinese+english limit 3;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 3 Changed: 2 Warnings: 0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
这里有几个点需要注意一下，
- null 与数值运算的时候返回值依旧为null ，比如在这里面，将总成绩倒数前三的 3 位同学的数学成绩加上 30 分，这里面总成绩倒数的第一名是贾宝玉 math Chinese English 全部是null ,在执行完这一句后并不会全部变成30,而是依旧是null，这是因为 null +10 = null。
- update 更改数据如果超出既有类型的范围，那么直接会报错并且并不会更改
- math = math +10;在SQL中并不支持简写为mate+= 10;
4.7删除DELETE
语法
DELETEFROM table_name [WHERE ...] [ORDERBY ...] [LIMIT ...]
- 1
示例
mysql> -- 删除孙悟空同学的考试成绩
mysql> delete from exam_result where name = "孙悟空";
Query OK, 1 row affected (0.01 sec)
mysql> -- 删除姓孙的同学的考试成绩
mysql> delete from exam_result where name like "孙%";
Query OK, 1 row affected (0.01 sec)
mysql> -- 删除数学第一名的同学的考试成绩
mysql>- delete from exam_result order by math desc limit 1;
mysql> -- 删除整张exam_result表
mysql> delete from exam_result ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
注意
delete from 表名 与 drop 表名前者是表里面的内容删除，但是表还在，而后者是表整个都直接删除了。
5.数据库约束
5.1 约束类型
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。
5.2 NULL 约束和 NOT NULL约束
创建表时，可以指定某列不为空：
示例
mysql> create table student (id int not null,name varchar(20),qq_email varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| qq_email | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
在设置了该列的约束NOT NULL后，此行一旦插入id = null 就会报错。
5.3 UNIQUE：唯一约束
插入数据或者修改数据的时候，就会先查询，先看看数据是否已经存在，如果不存在，就能够插入、修改成功，如果存在就插入或者修改失败。
示例：
mysql> create table student (id int unique,name varchar(10) not null); Query OK, 0 rows affected (0.02 sec) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into student values(1,"zhangsan"); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql> insert into student values(1,"lisi"); ERROR 1062 (23000): Duplicate entry '1' for key 'id'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
duplicate 重复的，entry 条目，入口
5.4 DEFAULT：默认值约束
- 默认值是insert指定列插入的时候其他未被指定到的列就是按照默认值来填充。
- 我们在建表的时候，如果不指定默认值，那么SQL也会自动设置默认值为null。
示例：
指定插入数据时，name列为空，默认值“无名氏”
mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) mysql> create table student(id int unique,name varchar(20) default "无名氏"); Query OK, 0 rows affected (0.02 sec) mysql> insert into student(id) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+--------+ | id | name | +------+--------+ | 1 | 无名氏 | +------+--------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
5.5 PRIMARY KEY：主键约束
- 主键是一条记录在表中的身份标识，唯一标记每一条数据
- mySQL要求主键所标识的列（属性）是唯一的（unique）、且不能为空（not null）
- 一个表里只能有一个主键
- 创建主键的时候，可以使用一个列作为主键，但是也可以用两个或者更多的列作为主键（复合主键）。但是一般项目中，一个表里就以一个列作为主键。
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> create table student (id int primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。也就是说mySQl自己会维护一个类似全局变量的自增主键，在我们设置该属性为整数类型的主键后，该属性插入是可以不赋值（即可以为null）此时，MySQL会根据当前主键的最大值加1来赋值为当前这一行记录的主键值。
- 自增主键一般只适用于数据是单机部署的，此时自增主键一般是够用的，但是如果自增主键是分布式部署，一般是不适用自增主键的。
示例
mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) ysql> create table student (id int primary key auto_increment,name varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into student(name) values("张三"); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+ | id | name | +----+------+ | 1 | 张三 | +----+------+ 1 row in set (0.00 sec) mysql> insert into student(id,name) values(null,"李四"); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+ | id | name | +----+------+ | 1 | 张三 | | 2 | 李四 | +----+------+ 2 rows in set (0.00 sec) mysql> insert into student values(100,"王五"); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +-----+------+ | id | name | +-----+------+ | 1 | 张三 | | 2 | 李四 | | 100 | 王五 | +-----+------+ 3 rows in set (0.00 sec) mysql> select * from student; +-----+------+ | id | name | +-----+------+ | 1 | 张三 | | 2 | 李四 | | 100 | 王五 | | 101 | 六六 | +-----+------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
5.6 FOREIGN KEY：外键约束
语法：
foreign key (字段名) references 主表(列)
- 1
示例
mysql> create table class (classId int primary key auto_increment ,className varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) mysql> create table student(studentId int primary key ,studentName varchar(20),classId int, -> foreign key (classId) references class(classId)); Query OK, 0 rows affected (0.02 sec) mysql> desc student; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | studentId | int(11) | NO | PRI | NULL | | | studentName | varchar(20) | YES | | NULL | | | classId | int(11) | YES | MUL | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc class; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | classId | int(11) | NO | PRI | NULL | auto_increment | | className | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 此时class表是空的，如果我在student表里面新增一个记录（这个记录中classId为1），那么这就会报错
示例：
mysql> insert into student values(123,"zhangsan",1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`base1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
- 1
- 2
其中Cannot add or update a child row: a foreign key constraint fails (base1.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (classId) REFERENCES class (classId))
student中的classId受到class表里面的classId约束，在MySQL中我们将student表称之为子表，class表称之为父表。这句话翻译过来“不能增加或者修改一个子行：一个外键约束（constraint）失败”
mysql> insert into class values(null,"一班"),(null,"二班"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from class; +---------+-----------+ | classId | className | +---------+-----------+ | 1 | 一班 | | 2 | 二班 | +---------+-----------+ 2 rows in set (0.00 sec) mysql> insert into student values(100,"张三",1),(101,"李四",2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +-----------+-------------+---------+ | studentId | studentName | classId | +-----------+-------------+---------+ | 100 | 张三 | 1 | | 101 | 李四 | 2 | +-----------+-------------+---------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
不但插入会影响，修改也是会影响，如果修改的classId的值并不在class表中，那么就会报错。
mysql> select * from student; +-----------+-------------+---------+ | studentId | studentName | classId | +-----------+-------------+---------+ | 100 | 张三 | 1 | | 101 | 李四 | 2 | +-----------+-------------+- mysql> update student set classId = 2 where studentId = 100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +-----------+-------------+---------+ | studentId | studentName | classId | +-----------+-------------+---------+ | 100 | 张三 | 2 | | 101 | 李四 | 2 | +-----------+-------------+---------+ 2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 同样如果我去删除class表中的数据依旧会报错，因为这里面的数据在student表中关联着，不能轻易删除。
mysql> select * from student;
+-----------+-------------+---------+
| studentId | studentName | classId |
+-----------+-------------+---------+
| 1 | zhangsan | 1 |
| 2 | lisi | 2 |
+-----------+-------------+---------+
2 rows in set (0.00 sec)
mysql> delete from class where classId = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`base1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
那么此时如果我此时确实是有删除这个班级记录的需求的呢？
比如说现在班级改制，将二班这个记录删除，但是这student表不变，作为存根。那么这样我们就在class表里面在增加一个列，标记为删除，实际上是逻辑删除了。
6.SQL的进阶操作
示例：
先定义一些表用于测试数据。
mysql> create table course(courseId int primary key,curseName varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> create table student(studentId int primary key,studentName varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> create table test(testId int primary key auto_increment,studentId int,courseId int,foreign key (studentId) references student(studentId), foreign key (courseId) references course(courseId)); Query OK, 0 rows affected (0.03 sec) mysql> desc student; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | studentId | int(11) | NO | PRI | NULL | | | studentName | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc course; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | courseId | int(11) | NO | PRI | NULL | | | curseName | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> desc test; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | testId | int(11) | NO | PRI | NULL | auto_increment | | studentId | int(11) | YES | MUL | NULL | | | courseId | int(11) | YES | MUL | NULL | | +-----------+---------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
6.1新增操作
插入查询结果
INSERT INTO table_name [(column [, column ...])] SELECT ...
- 1
示例
创建一张用户表，设计有userId用户ID、name姓名、studentId学号、sex性别。需要把已有的学生数据复制进来，可以复制的字段为name,studentId字段
mysql> create table user(userId int primary key auto_increment,userName varchar(20),studentId int,sec varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql> desc user; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | userId | int(11) | NO | PRI | NULL | auto_increment | | userName | varchar(20) | YES | | NULL | | | studentId | int(11) | YES | | NULL | | | sec | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into student values(1,"a"),(2,"b"),(3,"c"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into user(userName,studentId) select studentName,studentId from student; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student; +-----------+-------------+ | studentId | studentName | +-----------+-------------+ | 1 | a | | 2 | b | | 3 | c | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select * from user; +--------+----------+-----------+------+ | userId | userName | studentId | sec | +--------+----------+-----------+------+ | 1 | a | 1 | NULL | | 2 | b | 2 | NULL | | 3 | c | 3 | NULL | +--------+----------+-----------+------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 这种方式列名可以不一致，但是数量和参数类型得一致
6.2聚合查询
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有
6.2.1 count
示例：
mysql> select * from student; +-----------+-------------+ | studentId | studentName | +-----------+-------------+ | 1 | a | | 2 | b | | 3 | c | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql> insert into student values(4,"a"); Query OK, 1 row affected (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 使用
DISTINCT去重关键字，可以避免将相同记录计数。
mysql> insert into student values(4,"a"); Query OK, 1 row affected (0.00 sec) mysql> select count(name) from student; ERROR 1054 (42S22): Unknown column 'name' in 'field list' mysql> select count(studentName) from student; +--------------------+ | count(studentName) | +--------------------+ | 4 | +--------------------+ 1 row in set (0.00 sec) mysql> select count( distinct studentName) from student; +------------------------------+ | count( distinct studentName) | +------------------------------+ | 3 | +------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 记录为null时是不参与count计数的。
mysql> insert into student(studentId) values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +-----------+-------------+ | studentId | studentName | +-----------+-------------+ | 1 | a | | 2 | b | | 3 | c | | 4 | a | | 5 | NULL | +-----------+-------------+ 5 rows in set (0.00 sec) mysql> select count(studentName) from student; +--------------------+ | count(studentName) | +--------------------+ | 4 | +--------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
6.2.2 SUM
示例
mysql> DROP TABLE IF EXISTS exam_result; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE exam_result ( -> id INT, -> name VARCHAR(20), -> chinese DECIMAL(3,1), -> math DECIMAL(3,1), -> english DECIMAL(3,1) -> ); Query OK, 0 rows affected (0.02 sec) mysql> -- 插入测试数据 mysql> INSERT INTO exam_result (id,name, chinese, math, english) VALUES -> (1,'唐三藏', 67, 98, 56), -> (2,'孙悟空', 87.5, 78, 77), -> (3,'猪悟能', 88, 98.5, 90), -> (4,'曹孟德', 82, 84, 67), -> (5,'刘玄德', 55.5, 85, 45), -> (6,'孙权', 70, 73, 78.5), -> (7,'宋公明', 75, 65, 30); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from exam_result; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | +------+--------+---------+------+---------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- sum操作只作用于数据类型是数值类型的列。
mysql> select sum(name) from exam_result;
+-----------+
| sum(name) |
+-----------+
| 0 |
+-----------+
1 row in set, 8 warnings (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
这里有8个warningsm,可以看一下warning
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '唐三藏' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '猪悟能' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '曹孟德' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '刘玄德' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '宋公明' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '贾宝玉' |
+---------+------+--------------------------------------------+
8 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- sum操作与+不同,可以自动跳过值为null进行累加，而不会返回null；
示例：
mysql> select sum(chinese) from exam_result; +--------------+ | sum(chinese) | +--------------+ | 525.0 | +--------------+ 1 row in set (0.00 sec) mysql> insert into exam_result values(null,"贾宝玉",null,null,52.1); Query OK, 1 row affected (0.00 sec) mysql> select * from exam_result; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 | | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 | | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | | NULL | 贾宝玉 | NULL | NULL | 52.1 | +------+--------+---------+------+---------+ 8 rows in set (0.00 sec) mysql> select sum(chinese) from exam_result; +--------------+ | sum(chinese) | +--------------+ | 525.0 | +--------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
6.2.3 聚合函数搭配where表达式查询
示例：
返回 > 70 分以上的数学最低分
mysql> select min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
| 73.0 |
+-----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
6.3 group by 子句
6.3.1分组
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
语法
select column1, sum(column2), .. from table group by column1,column3;
- 1
示例：
准备测试表及数据：职员表，有employerId（主键）、name（姓名）、role（角色）、salary（薪水）
mysql> create table emp( -> id int primary key auto_increment, -> name varchar(20) not null, -> role varchar(20) not null, -> salary numeric(11,2) -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into emp(name, role, salary) values -> ('马云','服务员', 1000.20), -> ('马化腾','游戏陪玩', 2000.99), -> ('孙悟空','游戏角色', 999.11), -> ('猪无能','游戏角色', 333.5), -> ('沙和尚','游戏角色', 700.33), -> ('隔壁老王','董事长', 12000.66); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from emp; +----+----------+----------+----------+ | id | name | role | salary | +----+----------+----------+----------+ | 1 | 马云 | 服务员 | 1000.20 | | 2 | 马化腾 | 游戏陪玩 | 2000.99 | | 3 | 孙悟空 | 游戏角色 | 999.11 | | 4 | 猪无能 | 游戏角色 | 333.50 | | 5 | 沙和尚 | 游戏角色 | 700.33 | | 6 | 隔壁老王 | 董事长 | 12000.66 | +----+----------+----------+----------+ 6 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
查询每个角色的最高工资、最低工资和平均工资
mysql> select role,min(salary) as "最低工资",max(salary) as "最高工资",avg(salary) as "平均工资" from emp group by role; +----------+----------+----------+--------------+ | role | 最低工资 | 最高工资 | 平均工资 | +----------+----------+----------+--------------+ | 服务员 | 1000.20 | 1000.20 | 1000.200000 | | 游戏角色 | 333.50 | 999.11 | 677.646667 | | 游戏陪玩 | 2000.99 | 2000.99 | 2000.990000 | | 董事长 | 12000.66 | 12000.66 | 12000.660000 | +----------+----------+----------+--------------+ 4 rows in set (0.00 sec) mysql> select name,min(salary) as "最低工资",max(salary) as "最高工资",avg(salary) as "平均工资" from emp group by role; +----------+----------+----------+--------------+ | name | 最低工资 | 最高工资 | 平均工资 | +----------+----------+----------+--------------+ | 马云 | 1000.20 | 1000.20 | 1000.200000 | | 孙悟空 | 333.50 | 999.11 | 677.646667 | | 马化腾 | 2000.99 | 2000.99 | 2000.990000 | | 隔壁老王 | 12000.66 | 12000.66 | 12000.660000 | +----------+----------+----------+--------------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
最后name作为select 查询是没哟意义的。只有role才有意义。
6.3.2 分组之前条件筛选
示例：
求去除孙悟空同学后的每个岗位的平均薪资。
mysql> select role,avg(salary) from emp where name != "孙悟空" group by role;
+----------+--------------+
| role | avg(salary) |
+----------+--------------+
| 服务员 | 1000.200000 |
| 游戏角色 | 516.915000 |
| 游戏陪玩 | 2000.990000 |
| 董事长 | 12000.660000 |
+----------+--------------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
where在group by 之前执行，先筛选出name中没有孙悟空的所有记录，再将这些记录分组，再去求这些记录的平均薪资。
6.3.3 分组之后条件筛选having条件
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING
示例
mysql> select role,avg(salary) from emp group by role having name != "孙悟空";
ERROR 1054 (42S22): Unknown column 'name' in 'having clause'
mysql> select role,avg(salary) from emp group by role having role != "董事长";
+----------+-------------+
| role | avg(salary) |
+----------+-------------+
| 服务员 | 1000.200000 |
| 游戏角色 | 677.646667 |
| 游戏陪玩 | 2000.990000 |
+----------+-------------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
注意：此时的having筛选对象一定是分组后的表里面有的列（属性），比如在上述中，name就不在分完组后的表中，所以会报错。
显示平均工资低于1500的角色和它的平均工资
mysql> select role,max(salary),min(salary),avg(salary) from emp group by role
-> having avg(salary)<1500;
+----------+-------------+-------------+-------------+
| role | max(salary) | min(salary) | avg(salary) |
+----------+-------------+-------------+-------------+
| 服务员 | 1000.20 | 1000.20 | 1000.200000 |
| 游戏角色 | 999.11 | 333.50 | 677.646667 |
+----------+-------------+-------------+-------------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
6.3.4 分组前查询与分组后查询结合
去除孙悟空同学后分组，求每个角色的平均薪资，并去除老板这一角色进行查询
mysql> select role,avg(salary) from emp where name != "孙悟空" group by role having role != "董事长";
+----------+-------------+
| role | avg(salary) |
+----------+-------------+
| 服务员 | 1000.200000 |
| 游戏角色 | 516.915000 |
| 游戏陪玩 | 2000.990000 |
+----------+-------------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
实际上 where 和having 可以理解为一个执行在聚合函数之前，一个执行在聚合函数之后
mysql> select role,avg(salary) from emp where name != "孙悟空" having role != "董事长"; +--------+-------------+ | role | avg(salary) | +--------+-------------+ | 服务员 | 3207.136000 | +--------+-------------+ 1 row in set (0.00 sec) mysql> select role,avg(salary) from emp where name != "孙悟空" ; +--------+-------------+ | role | avg(salary) | +--------+-------------+ | 服务员 | 3207.136000 | +--------+-------------+ 1 row in set (0.00 sec) mysql> select role,avg(salary) from emp where name != "孙悟空" having role != "服务员"; Empty set (0.00 sec) mysql> select role from emp where name != "孙悟空" having role != "服务员"; +----------+ | role | +----------+ | 游戏陪玩 | | 游戏角色 | | 游戏角色 | | 董事长 | +----------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
7.联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
- 如上图实际上笛卡尔积得到的是一张更大的表，笛卡尔积的列数是两个表的列数之和，而行数是两个表的行数之积。
- 由于笛卡尔积是 排列组合出来的，所以有些数据是么有意义的。有意义的数据一定是两表里面的相同列（属性）的值一定是相同的。
示例：
建立三个表,班级表calsses、学生表student、课程表course
mysql> -- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识 mysql> DROP TABLE IF EXISTS classes; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE classes ( -> id INT PRIMARY KEY auto_increment, -> name VARCHAR(20), -> `desc` VARCHAR(100) -> ); Query OK, 0 rows affected (0.03 sec) mysql> DROP TABLE IF EXISTS student; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE student ( -> id INT PRIMARY KEY auto_increment, -> sn INT UNIQUE, -> name VARCHAR(20) DEFAULT 'unkown', -> qq_mail VARCHAR(20), -> classes_id int, -> FOREIGN KEY (classes_id) REFERENCES classes(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> -- 创建课程表 mysql> DROP TABLE IF EXISTS course; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE course ( -> id INT PRIMARY KEY auto_increment, -> name VARCHAR(20) -> ); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE score ( -> id INT PRIMARY KEY auto_increment, -> score DECIMAL(3, 1), -> student_id int, -> course_id int, -> FOREIGN KEY (student_id) REFERENCES student(id), -> FOREIGN KEY (course_id) REFERENCES course(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc classes; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | desc | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc student; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sn | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | | unkown | | | qq_mail | varchar(20) | YES | | NULL | | | classes_id | int(11) | YES | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> desc course; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> desc score; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | score | decimal(3,1) | YES | | NULL | | | student_id | int(11) | YES | MUL | NULL | | | course_id | int(11) | YES | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
插入数据为测试做准备
mysql> insert into classes(name, `desc`) values -> ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), -> ('中文系2019级3班','学习了中国传统文学'), -> ('自动化2019级5班','学习了机械自动化'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into student(sn, name, qq_mail, classes_id) values -> ('09982','黑旋风李逵','xuanfeng@qq.com',1), -> ('00835','菩提老祖',null,1), -> ('00391','白素贞',null,1), -> ('00031','许仙','xuxian@qq.com',1), -> ('00054','不想毕业',null,1), -> ('51234','好好说话','say@qq.com',2), -> ('83223','tellme',null,2), -> ('09527','老外学中文','foreigner@qq.com',2); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into course(name) values -> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> DROP TABLE IF EXISTS score; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE score ( -> id INT PRIMARY KEY auto_increment, -> score DECIMAL(3, 1), -> student_id int, -> course_id int, -> FOREIGN KEY (student_id) REFERENCES student(id), -> FOREIGN KEY (course_id) REFERENCES course(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc score; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | score | decimal(3,1) | YES | | NULL | | | student_id | int(11) | YES | MUL | NULL | | | course_id | int(11) | YES | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into course(name) values -> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into score(score, student_id, course_id) values -> -- 黑旋风李逵 -> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -> -- 菩提老祖 -> (60, 2, 1),(59.5, 2, 5), -> -- 白素贞 -> (33, 3, 1),(68, 3, 3),(99, 3, 5), -> -- 许仙 -> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -> -- 不想毕业 -> (81, 5, 1),(37, 5, 5), -> -- 好好说话 -> (56, 6, 2),(43, 6, 4),(79, 6, 6), -> -- tellme -> (80, 7, 2),(92, 7, 6); Query OK, 20 rows affected (0.01 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> select * from classes; +----+-------------------+-----------------------------------------------+ | id | name | desc | +----+-------------------+-----------------------------------------------+ | 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 | | 2 | 中文系2019级3班 | 学习了中国传统文学 | | 3 | 自动化2019级5班 | 学习了机械自动化 | +----+-------------------+-----------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from student; +----+-------+------------+------------------+------------+ | id | sn | name | qq_mail | classes_id | +----+-------+------------+------------------+------------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | | 3 | 391 | 白素贞 | NULL | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 7 | 83223 | tellme | NULL | 2 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | +----+-------+------------+------------------+------------+ 8 rows in set (0.00 sec) mysql> select * from course; +----+--------------+ | id | name | +----+--------------+ | 1 | Java | | 2 | 中国传统文化 | | 3 | 计算机原理 | | 4 | 语文 | | 5 | 高阶数学 | | 6 | 英文 | | 7 | Java | | 8 | 中国传统文化 | | 9 | 计算机原理 | | 10 | 语文 | | 11 | 高阶数学 | | 12 | 英文 | +----+--------------+ 12 rows in set (0.00 sec) mysql> select * from score; +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 1 | 70.5 | 1 | 1 | | 2 | 98.5 | 1 | 3 | | 3 | 33.0 | 1 | 5 | | 4 | 98.0 | 1 | 6 | | 5 | 60.0 | 2 | 1 | | 6 | 59.5 | 2 | 5 | | 7 | 33.0 | 3 | 1 | | 8 | 68.0 | 3 | 3 | | 9 | 99.0 | 3 | 5 | | 10 | 67.0 | 4 | 1 | | 11 | 23.0 | 4 | 3 | | 12 | 56.0 | 4 | 5 | | 13 | 72.0 | 4 | 6 | | 14 | 81.0 | 5 | 1 | | 15 | 37.0 | 5 | 5 | | 16 | 56.0 | 6 | 2 | | 17 | 43.0 | 6 | 4 | | 18 | 79.0 | 6 | 6 | | 19 | 80.0 | 7 | 2 | | 20 | 92.0 | 7 | 6 | +----+-------+------------+-----------+ 20 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
7.1多表查询一般实现的步骤
- 分析清楚需求中，涉及到的信息在哪些表里；
- 针对对个表进行笛卡尔积
- 筛选出其中的有效信息（往往以两个表的外键作为关联条件）
- 结合需求中的条件，进一步筛选。
7.2 内连接
7.2.1通过from 表名1 ，表名2实现笛卡尔积
示例：
查询许仙同学的成绩
在查询之前先对score表和student表笛卡尔积
即 select * from score,student;
mysql> select * from student,score; +----+-------+------------+------------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | +----+-------+------------+------------------+------------+----+-------+------------+-----------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 1 | 70.5 | 1 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 1 | 70.5 | 1 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 1 | 70.5 | 1 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 1 | 70.5 | 1 | 1 | | 7 | 83223 | tellme | NULL | 2 | 1 | 70.5 | 1 | 1 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 2 | 835 | 菩提老祖 | NULL | 1 | 2 | 98.5 | 1 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 2 | 98.5 | 1 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 5 | 54 | 不想毕业 | NULL | 1 | 2 | 98.5 | 1 | 3 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 2 | 98.5 | 1 | 3 | | 7 | 83223 | tellme | NULL | 2 | 2 | 98.5 | 1 | 3 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 2 | 835 | 菩提老祖 | NULL | 1 | 3 | 33.0 | 1 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 3 | 33.0 | 1 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 5 | 54 | 不想毕业 | NULL | 1 | 3 | 33.0 | 1 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 3 | 33.0 | 1 | 5 | | 7 | 83223 | tellme | NULL | 2 | 3 | 33.0 | 1 | 5 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 4 | 98.0 | 1 | 6 | | 3 | 391 | 白素贞 | NULL | 1 | 4 | 98.0 | 1 | 6 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 4 | 98.0 | 1 | 6 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 4 | 98.0 | 1 | 6 | | 7 | 83223 | tellme | NULL | 2 | 4 | 98.0 | 1 | 6 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 4 | 98.0 | 1 | 6 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 5 | 60.0 | 2 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 5 | 60.0 | 2 | 1 | | 7 | 83223 | tellme | NULL | 2 | 5 | 60.0 | 2 | 1 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 5 | 60.0 | 2 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 6 | 59.5 | 2 | 5 | | 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 6 | 59.5 | 2 | 5 | | 5 | 54 | 不想毕业 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 6 | 59.5 | 2 | 5 | | 7 | 83223 | tellme | NULL | 2 | 6 | 59.5 | 2 | 5 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 6 | 59.5 | 2 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 7 | 33.0 | 3 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 7 | 33.0 | 3 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 7 | 33.0 | 3 | 1 | | 7 | 83223 | tellme | NULL | 2 | 7 | 33.0 | 3 | 1 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 7 | 33.0 | 3 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 8 | 68.0 | 3 | 3 | | 2 | 835 | 菩提老祖 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 8 | 68.0 | 3 | 3 | | 5 | 54 | 不想毕业 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 8 | 68.0 | 3 | 3 | | 7 | 83223 | tellme | NULL | 2 | 8 | 68.0 | 3 | 3 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 8 | 68.0 | 3 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 9 | 99.0 | 3 | 5 | | 2 | 835 | 菩提老祖 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 9 | 99.0 | 3 | 5 | | 5 | 54 | 不想毕业 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 9 | 99.0 | 3 | 5 | | 7 | 83223 | tellme | NULL | 2 | 9 | 99.0 | 3 | 5 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 9 | 99.0 | 3 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 10 | 67.0 | 4 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 10 | 67.0 | 4 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 10 | 67.0 | 4 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 10 | 67.0 | 4 | 1 | | 7 | 83223 | tellme | NULL | 2 | 10 | 67.0 | 4 | 1 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 10 | 67.0 | 4 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 2 | 835 | 菩提老祖 | NULL | 1 | 11 | 23.0 | 4 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 5 | 54 | 不想毕业 | NULL | 1 | 11 | 23.0 | 4 | 3 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 11 | 23.0 | 4 | 3 | | 7 | 83223 | tellme | NULL | 2 | 11 | 23.0 | 4 | 3 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 11 | 23.0 | 4 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 2 | 835 | 菩提老祖 | NULL | 1 | 12 | 56.0 | 4 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 5 | 54 | 不想毕业 | NULL | 1 | 12 | 56.0 | 4 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 12 | 56.0 | 4 | 5 | | 7 | 83223 | tellme | NULL | 2 | 12 | 56.0 | 4 | 5 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 12 | 56.0 | 4 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 13 | 72.0 | 4 | 6 | | 3 | 391 | 白素贞 | NULL | 1 | 13 | 72.0 | 4 | 6 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 13 | 72.0 | 4 | 6 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 13 | 72.0 | 4 | 6 | | 7 | 83223 | tellme | NULL | 2 | 13 | 72.0 | 4 | 6 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 13 | 72.0 | 4 | 6 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 14 | 81.0 | 5 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 14 | 81.0 | 5 | 1 | | 7 | 83223 | tellme | NULL | 2 | 14 | 81.0 | 5 | 1 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 14 | 81.0 | 5 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 15 | 37.0 | 5 | 5 | | 2 | 835 | 菩提老祖 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 15 | 37.0 | 5 | 5 | | 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 15 | 37.0 | 5 | 5 | | 7 | 83223 | tellme | NULL | 2 | 15 | 37.0 | 5 | 5 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 15 | 37.0 | 5 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 16 | 56.0 | 6 | 2 | | 2 | 835 | 菩提老祖 | NULL | 1 | 16 | 56.0 | 6 | 2 | | 3 | 391 | 白素贞 | NULL | 1 | 16 | 56.0 | 6 | 2 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 16 | 56.0 | 6 | 2 | | 5 | 54 | 不想毕业 | NULL | 1 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 7 | 83223 | tellme | NULL | 2 | 16 | 56.0 | 6 | 2 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 17 | 43.0 | 6 | 4 | | 2 | 835 | 菩提老祖 | NULL | 1 | 17 | 43.0 | 6 | 4 | | 3 | 391 | 白素贞 | NULL | 1 | 17 | 43.0 | 6 | 4 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 17 | 43.0 | 6 | 4 | | 5 | 54 | 不想毕业 | NULL | 1 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 7 | 83223 | tellme | NULL | 2 | 17 | 43.0 | 6 | 4 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 18 | 79.0 | 6 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 18 | 79.0 | 6 | 6 | | 3 | 391 | 白素贞 | NULL | 1 | 18 | 79.0 | 6 | 6 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 18 | 79.0 | 6 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 18 | 79.0 | 6 | 6 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 18 | 79.0 | 6 | 6 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 19 | 80.0 | 7 | 2 | | 2 | 835 | 菩提老祖 | NULL | 1 | 19 | 80.0 | 7 | 2 | | 3 | 391 | 白素贞 | NULL | 1 | 19 | 80.0 | 7 | 2 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 19 | 80.0 | 7 | 2 | | 5 | 54 | 不想毕业 | NULL | 1 | 19 | 80.0 | 7 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 19 | 80.0 | 7 | 2 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 20 | 92.0 | 7 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 20 | 92.0 | 7 | 6 | | 3 | 391 | 白素贞 | NULL | 1 | 20 | 92.0 | 7 | 6 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 20 | 92.0 | 7 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 20 | 92.0 | 7 | 6 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 20 | 92.0 | 7 | 6 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | 20 | 92.0 | 7 | 6 | +----+-------+------------+------------------+------------+----+-------+------------+-----------+ 160 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
但是这里面有很多的无效数据，我们需要将无效数据剔除，一般是添加两个表的外键相等。
通过“表名.列名”来访问每个表的某个列
mysql> select * from student,score where student.id = score.student_id; +----+-------+------------+-----------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | +----+-------+------------+-----------------+------------+----+-------+------------+-----------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | +----+-------+------------+-----------------+------------+----+-------+------------+-----------+ 20 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
最后查询许仙同学的成绩
mysql> select student.name,score.score from student,score where student.id = score.student_id and student.name = "许仙";
+------+-------+
| name | score |
+------+-------+
| 许仙 | 67.0 |
| 许仙 | 23.0 |
| 许仙 | 56.0 |
| 许仙 | 72.0 |
+------+-------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
7.2.2通过join… on实现笛卡尔积
此外我们也可以通过join来实现笛卡尔积，此时后续条件也不是使用where关键字，而是 on
示例
mysql> select student.name,score.score from student join score on student.id = score.student_id and student.name = "许仙";
+------+-------+
| name | score |
+------+-------+
| 许仙 | 67.0 |
| 许仙 | 23.0 |
| 许仙 | 56.0 |
| 许仙 | 72.0 |
+------+-------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查询所有同学的总成绩，及同学的个人信息。
mysql> select student.*,score.score from student,score where student.id = score.student_id; +----+-------+------------+-----------------+------------+-------+ | id | sn | name | qq_mail | classes_id | score | +----+-------+------------+-----------------+------------+-------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 70.5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 98.5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 33.0 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 98.0 | | 2 | 835 | 菩提老祖 | NULL | 1 | 60.0 | | 2 | 835 | 菩提老祖 | NULL | 1 | 59.5 | | 3 | 391 | 白素贞 | NULL | 1 | 33.0 | | 3 | 391 | 白素贞 | NULL | 1 | 68.0 | | 3 | 391 | 白素贞 | NULL | 1 | 99.0 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 67.0 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 23.0 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 56.0 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 72.0 | | 5 | 54 | 不想毕业 | NULL | 1 | 81.0 | | 5 | 54 | 不想毕业 | NULL | 1 | 37.0 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 56.0 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 43.0 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 79.0 | | 7 | 83223 | tellme | NULL | 2 | 80.0 | | 7 | 83223 | tellme | NULL | 2 | 92.0 | +----+-------+------------+-----------------+------------+-------+ 20 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
注意上述查询的是每个同学对应课程的对应分数。而不是总分，那么要查询总分还需要进行分组结合聚合函数
去筛选出最后的总分数。
mysql> select student.id,student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;
+----+------------+------------------+
| id | name | sum(score.score) |
+----+------------+------------------+
| 1 | 黑旋风李逵 | 300.0 |
| 2 | 菩提老祖 | 119.5 |
| 3 | 白素贞 | 200.0 |
| 4 | 许仙 | 218.0 |
| 5 | 不想毕业 | 118.0 |
| 6 | 好好说话 | 178.0 |
| 7 | tellme | 172.0 |
+----+------------+------------------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
三个表也可以连接，显示学生所有课程的成绩
mysql> select * from student,course,score where student.id = score.student_id and course.id = score.course_id; +----+-------+------------+-----------------+------------+----+--------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | name | id | score | student_id | course_id | +----+-------+------------+-----------------+------------+----+--------------+----+-------+------------+-----------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | Java | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 计算机原理 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 5 | 高阶数学 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 6 | 英文 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 1 | Java | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 高阶数学 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 1 | Java | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 3 | 计算机原理 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 5 | 高阶数学 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 1 | Java | 10 | 67.0 | 4 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 3 | 计算机原理 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 5 | 高阶数学 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 6 | 英文 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 1 | Java | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 5 | 高阶数学 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 2 | 中国传统文化 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 4 | 语文 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 6 | 英文 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 2 | 中国传统文化 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 6 | 英文 | 20 | 92.0 | 7 | 6 | +----+-------+------------+-----------------+------------+----+--------------+----+-------+------------+-----------+ 20 rows in set (0.01 sec) mysql> select student.name,course.name,score.score from student,course,score where student.id = score.student_id and course.id = score.course_id; +------------+--------------+-------+ | name | name | score | +------------+--------------+-------+ | 黑旋风李逵 | Java | 70.5 | | 黑旋风李逵 | 计算机原理 | 98.5 | | 黑旋风李逵 | 高阶数学 | 33.0 | | 黑旋风李逵 | 英文 | 98.0 | | 菩提老祖 | Java | 60.0 | | 菩提老祖 | 高阶数学 | 59.5 | | 白素贞 | Java | 33.0 | | 白素贞 | 计算机原理 | 68.0 | | 白素贞 | 高阶数学 | 99.0 | | 许仙 | Java | 67.0 | | 许仙 | 计算机原理 | 23.0 | | 许仙 | 高阶数学 | 56.0 | | 许仙 | 英文 | 72.0 | | 不想毕业 | Java | 81.0 | | 不想毕业 | 高阶数学 | 37.0 | | 好好说话 | 中国传统文化 | 56.0 | | 好好说话 | 语文 | 43.0 | | 好好说话 | 英文 | 79.0 | | tellme | 中国传统文化 | 80.0 | | tellme | 英文 | 92.0 | +------------+--------------+-------+ 20 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
7.3 外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完
全显示我们就说是右外连接。
内连接与外连接大体是相同的，但是如果两个表的数据并不是一一对应的，此时进行内连接。结果只会显示两个表里面都有体现的数据。
如果进行左外连接。就是以左侧表为准，左侧表的数据都能体现出来。如果是右链接，就是以右侧表为准，右侧表数据都能体现出来。
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
- 1
- 2
- 3
- 4
示例：
mysql> -- "老外学中文"同学 没有考试成绩（course—id和studentid都是空的），也显示出来了 mysql> select * from student stu left join score sco on stu.id=sco.student_id; +----+-------+------------+------------------+------------+------+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | +----+-------+------------+------------------+------------+------+-------+------------+-----------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | NULL | NULL | NULL | NULL | +----+-------+------------+------------------+------------+------+-------+------------+-----------+ 21 rows in set (0.00 sec) mysql> -- 对应的右外连接为： mysql> select * from score sco right join student stu on stu.id=sco.student_id; +------+-------+------------+-----------+----+-------+------------+------------------+------------+ | id | score | student_id | course_id | id | sn | name | qq_mail | classes_id | +------+-------+------------+-----------+----+-------+------------+------------------+------------+ | 1 | 70.5 | 1 | 1 | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 2 | 98.5 | 1 | 3 | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 3 | 33.0 | 1 | 5 | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 4 | 98.0 | 1 | 6 | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | | 5 | 60.0 | 2 | 1 | 2 | 835 | 菩提老祖 | NULL | 1 | | 6 | 59.5 | 2 | 5 | 2 | 835 | 菩提老祖 | NULL | 1 | | 7 | 33.0 | 3 | 1 | 3 | 391 | 白素贞 | NULL | 1 | | 8 | 68.0 | 3 | 3 | 3 | 391 | 白素贞 | NULL | 1 | | 9 | 99.0 | 3 | 5 | 3 | 391 | 白素贞 | NULL | 1 | | 10 | 67.0 | 4 | 1 | 4 | 31 | 许仙 | xuxian@qq.com | 1 | | 11 | 23.0 | 4 | 3 | 4 | 31 | 许仙 | xuxian@qq.com | 1 | | 12 | 56.0 | 4 | 5 | 4 | 31 | 许仙 | xuxian@qq.com | 1 | | 13 | 72.0 | 4 | 6 | 4 | 31 | 许仙 | xuxian@qq.com | 1 | | 14 | 81.0 | 5 | 1 | 5 | 54 | 不想毕业 | NULL | 1 | | 15 | 37.0 | 5 | 5 | 5 | 54 | 不想毕业 | NULL | 1 | | 16 | 56.0 | 6 | 2 | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 17 | 43.0 | 6 | 4 | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 18 | 79.0 | 6 | 6 | 6 | 51234 | 好好说话 | say@qq.com | 2 | | 19 | 80.0 | 7 | 2 | 7 | 83223 | tellme | NULL | 2 | | 20 | 92.0 | 7 | 6 | 7 | 83223 | tellme | NULL | 2 | | NULL | NULL | NULL | NULL | 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | +------+-------+------------+-----------+----+-------+------------+------------------+------------+ 21 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
7.4多表（三张以上）联合查询
语法：
select 列名1，列名2.... from 表名1，表名2，表名3 where 条件1 and 条件2；
select 列名1，列名2.... from 表名1 join 表名2 on 条件1 join 表名3 on 条件2
- 1
- 2
示例：
mysql> -- 学生表、成绩表、课程表3张表关联查询 mysql> SELECT -> stu.id, -> stu.sn, -> stu.NAME, -> stu.qq_mail, -> sco.score, -> sco.course_id, -> cou.NAME -> FROM -> student stu -> LEFT JOIN score sco ON stu.id = sco.student_id -> LEFT JOIN course cou ON sco.course_id = cou.id -> ORDER BY -> stu.id; +----+-------+------------+------------------+-------+-----------+--------------+ | id | sn | NAME | qq_mail | score | course_id | NAME | +----+-------+------------+------------------+-------+-----------+--------------+ | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 70.5 | 1 | Java | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 98.5 | 3 | 计算机原理 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 33.0 | 5 | 高阶数学 | | 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 98.0 | 6 | 英文 | | 2 | 835 | 菩提老祖 | NULL | 60.0 | 1 | Java | | 2 | 835 | 菩提老祖 | NULL | 59.5 | 5 | 高阶数学 | | 3 | 391 | 白素贞 | NULL | 33.0 | 1 | Java | | 3 | 391 | 白素贞 | NULL | 68.0 | 3 | 计算机原理 | | 3 | 391 | 白素贞 | NULL | 99.0 | 5 | 高阶数学 | | 4 | 31 | 许仙 | xuxian@qq.com | 67.0 | 1 | Java | | 4 | 31 | 许仙 | xuxian@qq.com | 23.0 | 3 | 计算机原理 | | 4 | 31 | 许仙 | xuxian@qq.com | 56.0 | 5 | 高阶数学 | | 4 | 31 | 许仙 | xuxian@qq.com | 72.0 | 6 | 英文 | | 5 | 54 | 不想毕业 | NULL | 81.0 | 1 | Java | | 5 | 54 | 不想毕业 | NULL | 37.0 | 5 | 高阶数学 | | 6 | 51234 | 好好说话 | say@qq.com | 56.0 | 2 | 中国传统文化 | | 6 | 51234 | 好好说话 | say@qq.com | 43.0 | 4 | 语文 | | 6 | 51234 | 好好说话 | say@qq.com | 79.0 | 6 | 英文 | | 7 | 83223 | tellme | NULL | 80.0 | 2 | 中国传统文化 | | 7 | 83223 | tellme | NULL | 92.0 | 6 | 英文 | | 8 | 9527 | 老外学中文 | foreigner@qq.com | NULL | NULL | NULL | +----+-------+------------+------------------+-------+-----------+--------------+ 21 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
7.5 自连接
自连接是在特殊情况下的特殊操作，不是一般用法。
SQL 中进行条件查询，都是指定某一列或者多个列之间进行关系运算，无法进行行与行之间的关系运算。有时候为了实现这种行之前的比较，就需要将行关系转换为列关系。
示例：
mysql> select * from score; +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 1 | 70.5 | 1 | 1 | | 2 | 98.5 | 1 | 3 | | 3 | 33.0 | 1 | 5 | | 4 | 98.0 | 1 | 6 | | 5 | 60.0 | 2 | 1 | | 6 | 59.5 | 2 | 5 | | 7 | 33.0 | 3 | 1 | | 8 | 68.0 | 3 | 3 | | 9 | 99.0 | 3 | 5 | | 10 | 67.0 | 4 | 1 | | 11 | 23.0 | 4 | 3 | | 12 | 56.0 | 4 | 5 | | 13 | 72.0 | 4 | 6 | | 14 | 81.0 | 5 | 1 | | 15 | 37.0 | 5 | 5 | | 16 | 56.0 | 6 | 2 | | 17 | 43.0 | 6 | 4 | | 18 | 79.0 | 6 | 6 | | 19 | 80.0 | 7 | 2 | | 20 | 92.0 | 7 | 6 | +----+-------+------------+-----------+ 20 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
比如说我要查询course_id为1的课程比course_id为3的课程分数高的学生的学生id。
mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
| 1 | 70.5 | 1 | 1 | 2 | 98.5 | 1 | 3 |
| 7 | 33.0 | 3 | 1 | 8 | 68.0 | 3 | 3 |
| 10 | 67.0 | 4 | 1 | 11 | 23.0 | 4 | 3 |
+----+-------+------------+-----------+----+-------+------------+-----------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
注意：自连接一定要将表重新命名，保证连接的两个表的名字是不同的
mysql> select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3 and s1.score> s2.score;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
| 10 | 67.0 | 4 | 1 | 11 | 23.0 | 4 | 3 |
+----+-------+------------+-----------+----+-------+------------+-----------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
7.6子查询
子查询在实际开发过程中也要慎重。因为嵌套层次多了，对于代码可读性而言会变差。
7.6.1单行子查询
示例：
查询与“不想毕业”同学同班的同学
mysql> select * from student where student.classes_id = (select classes_id from student where student.name = "不想毕业");
+----+------+------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+------------+-----------------+------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素贞 | NULL | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想毕业 | NULL | 1 |
+----+------+------------+-----------------+------------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
7.6.2多行子查询
查询“语文”或“英文”课程的成绩信息
mysql> select id from course where name = "英文" or name = "语文"; +----+ | id | +----+ | 4 | | 6 | | 10 | | 12 | +----+ 4 rows in set (0.00 sec) mysql> select * from score where score.id in (4,6,10,12); +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 4 | 98.0 | 1 | 6 | | 6 | 59.5 | 2 | 5 | | 10 | 67.0 | 4 | 1 | | 12 | 56.0 | 4 | 5 | +----+-------+------------+-----------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
用一行代码来完成就是
mysql> select * from score where course_id in(select id from course where name = "英文"or name = "语文");
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 17 | 43.0 | 6 | 4 |
| 4 | 98.0 | 1 | 6 |
| 13 | 72.0 | 4 | 6 |
| 18 | 79.0 | 6 | 6 |
| 20 | 92.0 | 7 | 6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
7.7 合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。
语法：
select 列名1、列名2..... from 表名1 where条件1 union select 列名3、列名4.....from 表名2 where 条件2；
select 列名1、列名2..... from 表名1 where条件1 union all select 列名3、列名4.....from 表名2 where 条件2；
- 1
- 2
- union的实际步骤是先执行前一句select 得出一个查询结果的临时表，然后执行后一句select得到一个查询结果的临时表，两个表进行上下拼接。union在此过程中去重，union在此过程不去重。
- 使用UNION 和UNION ALL时，前后查询的结果集中，字段（列）数量需要一致。
- 该操作符用于取得两个结果集的并集。(和or的使用时类似的，但or是对列操作，union是对行操作)当使用该操作符时，会自动去掉结果集中的重复行。
- UNION查询时会去重，UNION ALL查询的时候不会去重。
mysql> select * from course where id<3 or name = "英文"; +----+--------------+ | id | name | +----+--------------+ | 1 | Java | | 2 | 中国传统文化 | | 6 | 英文 | | 12 | 英文 | +----+--------------+ 4 rows in set (0.01 sec) mysql> select * from course where id<3 union select * from course where name = '英文'; +----+--------------+ | id | name | +----+--------------+ | 1 | Java | | 2 | 中国传统文化 | | 6 | 英文 | | 12 | 英文 | +----+--------------+ 4 rows in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
SQL的基本操作的详解就到这里了。如果真的有一字不漏看到这里的小伙伴，相信你是有收获的。
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/72130
推荐阅读
相关标签
