mysql:MySQL所有基本操作详解 2024-04-25 00:04:35 0 0 文章目录一.MySQL的基本操作1.库操作1.1 查看数据库1.2 创建数据库1.3 使用数据库1.4 删除数据库2.表操作2.1 创建表2.2 查看数据库中的表结构2.3删除数据库中的表结构2.4查询某个数据库内的所有表名3.SQL中的数据类型3.1数值类型3.2字符串类型3.3日期类型4.MySQL的增删改查4.1新增插入数据4.1.1基础的插入4.1.2指定列的的插入4.1.3多次数据的的插入4.1.4时间日期类型的插入4.2查询数据SELECT4.2.1全列查找4.2.2指定列查找4.2.3 查询可以是表达式4.2.4指定别名4.3去重DISTINCT4.4 查询结果排序ORDER BY4.4.1order by 子句4.4.2使用表达式及别名排序4.4.3可以对多个字段进行排序,排序优先级随书写顺序4.5条件查询WHERE4.5.1比较运算4.5.2逻辑运算4.5.3between and4.5.4 in4.5.5模糊查询:LIKE4.5.6NULL 的查询:IS [NOT] NULL4.5分页查询LIMIT4.6 修改语句UPDATE4.7删除DELETE5.数据库约束5.1 约束类型5.2 NULL 约束和 NOT NULL约束5.3 UNIQUE:唯一约束5.4 DEFAULT:默认值约束5.5 PRIMARY KEY:主键约束5.6 FOREIGN KEY:外键约束6.SQL的进阶操作6.1新增操作6.2聚合查询6.2.1 count6.2.2 SUM6.2.3 聚合函数搭配where表达式查询6.3 group by 子句6.3.1分组6.3.2 分组之前条件筛选6.3.3 分组之后条件筛选having条件6.3.4 分组前查询与分组后查询结合7.联合查询7.1多表查询一般实现的步骤7.2 内连接7.2.1通过from 表名1 ,表名2实现笛卡尔积7.2.2通过join.... on实现笛卡尔积7.3 外连接7.4多表(三张以上)联合查询7.5 自连接7.6子查询7.6.1单行子查询7.6.2多行子查询7.7 合并查询 一.MySQL的基本操作 首先sql操作中的关键字的是大小写不敏感的,create 和CREATE是一样的。 1.库操作 1.1 查看数据库 语法:show databases; 示例: 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 大写的表示关键字 [] 是可选项 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; 2.如果系统没有db_test2的数据库,则创建一个名叫db_test2的数据库,如果有则不创建CREATE DATABASE IF NOT EXISTS db_test2; 3.如果系统没有db_test的数据库,则创建一个使用utf8mb4字符集的db_test数据库,如果有则不创建CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4; 1.3 使用数据库use 数据库名; 示例 使用该数据库后会有相应的提示,表明数据库已经切换了。 1.4 删除数据库 语法:DROP DATABASE [IF EXISTS] db_name; 删除操作是非常危险的!一旦删除,数据就没了,难以恢复。 数据库删除以后,内部看不到对应的数据库,里边的表和数据全部被删除 示例drop database if exists db_test1;drop database if exists db_test2; 如果database 里有db_test1表就删除database. 2.表操作 需要操作数据库中的表时,需要先使用该数据库 2.1 创建表 语法:CREATETABLE table_name ( field1 datatype, field2 datatype, field3 datatype); 示例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); comment 相当于注释,这个不太好用,只能在创建表的时候使用,所以一般来说我们更推荐使用#或者 – 来表示注释 2.2 查看数据库中的表结构 语法:use 数据库名;desc 表名; 示例:desc stu_test; desc是describe的缩写。查看结果经常会有int(11),这表示这一列哭护短查询显示的时候最多显示11个字符。这只是显示宽度,这与实际存储是没有关系的。NULL这一列表示的是否可以为空,如果是YES表示可以。Default 表示默认值。 2.3删除数据库中的表结构 语法:use database_name;drop table 表名; 2.4查询某个数据库内的所有表名 语法:use database_name;show tables; 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 changedmysql> 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) 4.1.1基础的插入insert [into] 表名 values [列名] (值1,值2,值3.....); 实例mysql> insert into student values(123,4456,'张三','123456@qq.com');Query OK, 1 row affected (0.00 sec) mysql> insert student (id,sn,name,qq_mail) values (123,4456,'张三','123456@qq.com');Query OK, 1 row affected (0.00 sec) 注意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,......) 示例:mysql> insert student(id,qq_mail) values (213,'34567@qq.com');Query OK, 1 row affected (0.00 sec) 注意:在前面的表结构定义里面定义了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) 4.1.3多次数据的的插入 示例:mysql> insert into student values(1,1,"李四","1@qq.com"),(2,2,"李四","2@qq.com"); 结果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)一次插入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) 同时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) 4.2查询数据SELECT 4.2.1全列查找select * from 表名 在实际操作中执行select * 是非常危险的,因为实际项目中的数据量是非常大的,如果数据全部从服务器读取到客户端,数据带宽会拥堵。 4.2.2指定列查找select 列名,列名 from 表名; 示例:mysql> select id,name from student;+-----+--------+| id | name |+-----+--------+| 123 | 张三 || 123 | 张三 || 213 | NULL || 1 | 李四 || 2 | 李四 |+-----+--------+5 rows in set (0.00 sec) 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: 0mysql> 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) 我们可以查询所有人数学成绩+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) 但是注意:经过上述这样的查询,数据库服务器硬盘里面的数据是没有变化的。因为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) 4.2.4指定别名 为查询结果中的列指定别名,表示返回的结果集中,以别名作为该列的名称SELECT 表达式 [AS] 别名 [...] FROM table_name; 示例: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) 4.3去重DISTINCTmysql> 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) 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 1mysql> 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 4.4 查询结果排序ORDER BY 4.4.1order by 子句 语法:-- ASC 为升序(从小到大)-- DESC 为降序(从大到小)-- 默认为 ASCSELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...]; 示例: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) 注意:对于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) 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) 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) 注:这行代码的底层是这样的,针对数据库的表,进行遍历,取出每一行的数据,把数据带入到条件中,看是否满足条件,如果为真就保留,如果为假,就不保留。查询语文成绩好于英语成绩的同学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)查询总分在 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) 注意 看下面的代码mysql> select name,chinese+math+english as 总分 from exam_result where 总分 > 200;ERROR 1054 (42S22): Unknown column '总分' in 'where clause' 别名是不可以作为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) 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) 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) 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) 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) 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) 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) 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) 注意 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; 示例: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) 注意: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) 4.6 修改语句UPDATE 语法:UPDATE table_name SET 列名1 = 数值1 [, 列名2 = 数值2 ...] [WHERE ...] [ORDER BY ...] [LIMIT ...] 示例: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: 0mysql> -- 将曹孟德同学的数学成绩变更为 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: 0mysql> -- 将总成绩倒数前三的 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 这里有几个点需要注意一下,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 ...] 示例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 ; 注意 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) 在设置了该列的约束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' 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) 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)对于整数类型的主键,常配搭自增长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) 5.6 FOREIGN KEY:外键约束 语法:foreign key (字段名) references 主表(列) 示例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)此时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`)) 其中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: 0mysql> 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: 0mysql> select * from student;+-----------+-------------+---------+| studentId | studentName | classId |+-----------+-------------+---------+| 100 | 张三 | 1 || 101 | 李四 | 2 |+-----------+-------------+---------+2 rows in set (0.00 sec) 不但插入会影响,修改也是会影响,如果修改的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: 0mysql> select * from student;+-----------+-------------+---------+| studentId | studentName | classId |+-----------+-------------+---------+| 100 | 张三 | 2 || 101 | 李四 | 2 |+-----------+-------------+---------+2 rows in set (0.00 sec)同样如果我去删除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`)) 那么此时如果我此时确实是有删除这个班级记录的需求的呢? 比如说现在班级改制,将二班这个记录删除,但是这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) 6.1新增操作 插入查询结果INSERT INTO table_name [(column [, column ...])] SELECT ... 示例 创建一张用户表,设计有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: 0mysql> insert into user(userName,studentId) select studentName,studentId from student;Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> 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)这种方式列名可以不一致,但是数量和参数类型得一致 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)使用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)记录为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) 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: 0mysql> 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)sum操作只作用于数据类型是数值类型的列。mysql> select sum(name) from exam_result;+-----------+| sum(name) |+-----------+| 0 |+-----------+1 row in set, 8 warnings (0.00 sec) 这里有8个warningsm,可以看一下warningmysql> 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)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) 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) 6.3 group by 子句 6.3.1分组 SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中。 语法select column1, sum(column2), .. from table group by column1,column3; 示例: 准备测试表及数据:职员表,有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: 0mysql> 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) 查询每个角色的最高工资、最低工资和平均工资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) 最后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) 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) 注意:此时的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) 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) 实际上 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) 7.联合查询 实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。 如上图实际上笛卡尔积得到的是一张更大的表,笛卡尔积的列数是两个表的列数之和,而行数是两个表的行数之积。由于笛卡尔积是 排列组合出来的,所以有些数据是么有意义的。有意义的数据一定是两表里面的相同列(属性)的值一定是相同的。 示例: 建立三个表,班级表calsses、学生表student、课程表coursemysql> -- 创建班级表,有使用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) 插入数据为测试做准备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: 0mysql> 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: 0mysql> insert into course(name) values -> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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: 0mysql> 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) 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) 但是这里面有很多的无效数据,我们需要将无效数据剔除,一般是添加两个表的外键相等。 通过“表名.列名”来访问每个表的某个列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) 最后查询许仙同学的成绩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) 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) 查询所有同学的总成绩,及同学的个人信息。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) 注意上述查询的是每个同学对应课程的对应分数。而不是总分,那么要查询总分还需要进行分组结合聚合函数 去筛选出最后的总分数。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) 三个表也可以连接,显示学生所有课程的成绩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) 7.3 外连接 外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完 全显示我们就说是右外连接。 内连接与外连接大体是相同的,但是如果两个表的数据并不是一一对应的,此时进行内连接。结果只会显示两个表里面都有体现的数据。 如果进行左外连接。就是以左侧表为准,左侧表的数据都能体现出来。如果是右链接,就是以右侧表为准,右侧表数据都能体现出来。-- 左外连接,表1完全显示select 字段名 from 表名1 left join 表名2 on 连接条件;-- 右外连接,表2完全显示select 字段 from 表名1 right join 表名2 on 连接条件; 示例: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) 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 示例: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) 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) 比如说我要查询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) 注意:自连接一定要将表重新命名,保证连接的两个表的名字是不同的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) 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) 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) 用一行代码来完成就是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) 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;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) SQL的基本操作的详解就到这里了。如果真的有一字不漏看到这里的小伙伴,相信你是有收获的。 收藏(0)