数据库MySQL（三）SQL的基础语句

缺乏、安全感 2024-03-17 16:09 101阅读 0赞

文章目录

  • 一、MySQL数据表简单查询
    • 1.1、简单查询概述
    • 1.2、查询所有字段（方法不唯一只是举例）
    • 1.3、查询指定字段（sid、sname）
    • 1.4、常数的查询
    • 1.5、从查询结果中过滤重复数据
    • 1.6、算术运算符（举例加运算符）
  • 二、函数
    • 2.1、聚合函数
      • (1)、count（）
      • (2)、max（）
      • (3)、min（）
      • (4)、sum（）
      • (5)、avg（）
    • 2.2、其他函数
      • (6)、时间函数
      • (7)、字符串函数
      • (8)、数学函数
  • 三、条件查询
    • 3.1、使用关系运算符查询
    • 3.2、使用IN关键字查询
    • 3.3、使用BETWEEN AND关键字查询
    • 3.4、使用空值查询
    • 3.5、使用AND关键字查询
    • 3.6使用OR关键字查询
    • 3.7、使用LIKE关键字查询
      • (1)、普通字符串
      • (2)、含有%通配的字符串
      • (3)、含有_通配的字符串
    • 3.8、使用LIMIT限制查询结果的数量
    • 3.9、使用GROUP BY进行分组查询
      • (1)、GROUP BY和聚合函数一起使用
      • (2)、GROUP BY和聚合函数以及HAVING一起使用
    • 3.10、使用ORDER BY对查询结果排序
  • 四、别名设置
    • 4.1、为表取别名
    • 4.2、为字段取别名
  • 五、表的关联关系
    • 5.1、关联查询
    • 5.2、关于关联关系的删除数据
  • 六、多表连接查询
    • 6.1、交叉连接查询
    • 6.2、内连接查询
    • 6.3、外连接查询
      • (1)、内连接与外连接的区别
      • (1)、左（外）连接查询
      • (2)、右（外）连接查询
  • 七、子查询
    • 7.1、带比较运算符的子查询
    • 7.2、带EXISTS关键字的子查询
    • 7.3、带ANY关键字的子查询
    • 7.4、带ALL关键字的子查询
  • 八、总结

一、MySQL数据表简单查询

1.1、简单查询概述

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。
在此，先准备测试数据，代码如下：

  1. -- 创建数据库
  2. DROP DATABASE IF EXISTS mydb;
  3. CREATE DATABASE mydb;
  4. USE mydb;
  6. -- 创建student表
  7. CREATE TABLE students (
  8.     sid CHAR(6),
  9.     sname VARCHAR(50),
  10.     age INT,
  11.     gender VARCHAR(50) DEFAULT 'male'
  12. );
  14. -- 向student表插入数据
  15. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
  16. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
  17. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
  18. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
  19. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
  20. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
  21. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
  22. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
  23. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
  24. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
  25. INSERT INTO students (sid,sname,age,gender) VALUES ('S_1011', NULL, 17, NULL);

1.2、查询所有字段（方法不唯一只是举例）

查询所有字段MySQL命令：

  1. select * from students;

运行结果：
查询\*

1.3、查询指定字段（sid、sname）

查询指定字段（sid、sname） MySQL命令：

  1. select sid,sname from students;

运行结果：
sid,

1.4、常数的查询

在SELECT中除了书写列名，还可以书写常数。可以用于标记
常数的查询日期标记 MySQL命令：

  1. select sid,sname,'2021-07-11' from students;

运行结果：
常数

1.5、从查询结果中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
MySQL命令：

  1. select distinct gender from students;

运行结果：
distinct

1.6、算术运算符（举例加运算符）

在SELECT查询语句中还可以使用加减乘除运算符。
查询学生10年后的年龄 MySQL命令：

  1. select sname,age+10 from students;

运行结果：
算术

二、函数

2.1、聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、 平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。
聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。
接下来，我们学习常用聚合函数。

(1)、count（）

统计表中数据的行数或者统计指定列其值不为NULL的数据个数
查询有多少该表中有多少人
MySQL命令：

  1. select count(*) from students;

运行结果：
count

(2)、max（）

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算
查询该学生表中年纪最大的学生

  1. select max(age) from students;

运行结果：
max

(3)、min（）

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算
查询该学生表中年纪最小的学生 MySQL命令

  1. SELECT sname, age FROM students WHERE age=(SELECT MIN(age) FROM students);

运行结果：
min

(4)、sum（）

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0
查询该学生表中年纪的总和 MySQL命令

  1. select sum(age) from students;

运行结果：
sum

(5)、avg（）

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为
查询该学生表中年纪的平均数 MySQL命令：

  1. select avg(age) from students;

运行结果：
avg

2.2、其他函数

(6)、时间函数

  1. SELECT NOW();
  2. SELECT DAY (NOW());
  3. SELECT DATE (NOW());
  4. SELECT TIME (NOW());
  5. SELECT YEAR (NOW());
  6. SELECT MONTH (NOW());
  7. SELECT CURRENT_DATE();
  8. SELECT CURRENT_TIME();
  9. SELECT CURRENT_TIMESTAMP();
  10. SELECT ADDTIME('14:23:12','01:02:01');
  11. SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
  12. SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
  13. SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
  14. SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
  15. SELECT DATEDIFF('2019-07-22','2019-05-05');

(7)、字符串函数

  1. --连接函数
  2. SELECT CONCAT ('X','X','X','X')
  3. --返回字符串第一次出现位置索引
  4. SELECT INSTR('hello world hello', 'llo');
  5. --统计长度
  6. SELECT LENGTH('XXXX');
  7. --大写转换
  8. select upper('apple');
  9. --小写转换
  10. select lower('TOM');
  11. --字符串替换
  12. --第一个参数为传入的字符串，第二个参数是需要替换的字符串，第三个参数是替换后的字符串
  13. select replace('apple 123 apple 456', 'apple', 'hello world');

(8)、数学函数

  1. -- 绝对值
  2. SELECT ABS(-136);
  3. -- 向下取整
  4. SELECT FLOOR(3.14);
  5. -- 向上取整
  6. SELECT CEILING(3.14);

三、条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

3.1、使用关系运算符查询

在WHERE中可使用关系运算符进行条件查询，常用的关系运算符如下所示：






































关系运算符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于

查询年龄等于或大于17的学生的信息 MySQL命令：

  1. select * from students where age>=17;

运行结果：
where

3.2、使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。
查询sid为S_1002和S_1003的学生信息 MySQL命令：

  1. select * from students where sid in ('S_1002','S_1003');

运行结果：
in
查询sid为S_1001以外的学生的信息 MySQL命令：

  1. select * from students where sid not in ('S_1001');

运行结果：
notin

3.3、使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
查询15到18岁的学生信息 MySQL命令：

  1. select * from students where age between 15 and 18;

运行结果：
范围
查询不是15到18岁的学生信息 MySQL命令：

  1. select * from students where age not between 15 and 18;

运行结果：
范围

3.4、使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串
查询sname为空值的学生信息 MySQL命令：

  1. select * from students where sname is null;

运行结果：
is
查询sname不为空值的学生信息 MySQL命令：

  1. select * from students where sname is not null;

运行结果：
isnot

3.5、使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
查询年纪大于15且性别为male的学生信息 MySQL命令：

  1. select * from students where age>15 and gender='male';

运行结果：
and

3.6使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来
查询年纪大于15或者性别为male的学生信息 MySQL命令：

  1. select * from students where age>15 or gender='male';

运行结果：
or

3.7、使用LIKE关键字查询

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配

(1)、普通字符串

查询sname中与wang匹配的学生信息 MySQL命令：

  1. select * from students where sname like 'wang';

运行结果：
like

(2)、含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串
查询学生姓名以li开始的记录 MySQL命令：

  1. select * from students where sname like 'li%';

运行结果：
%
查询学生姓名以p结尾的记录 MySQL命令：

  1. select * from students where sname like '%p';

运行结果：
like

查询学生姓名包含s的记录 MySQL命令：

  1. select * from students where sname like '%s%';

运行结果：
like

(3)、含有_通配的字符串

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如”abcd”、”atud”等等。
查询学生姓名以zx开头且长度为4的记录 MySQL命令：

  1. select * from students where sname like 'zx__';

运行结果：
like
查询学生姓名以g结尾且长度为4的记录 MySQL命令：

  1. select * from students where sname like '___g';

运行结果：
like

3.8、使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条
查询学生表中年纪最小的3位同学 MySQL命令：

  1. select * from students order by age asc limit 3;

运行结果：
limit
LIMIT 子句：MySQL查询语句中使用LIMIT子句限制结果集

  1. 语法：limit 开始的索引,每页查询的条数;

  2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

    — 每页显示3条记录
    SELECT FROM students LIMIT 0,3; — 第1页
    SELECT     FROM students LIMIT 3,3; — 第2页
    SELECT * FROM students LIMIT 6,3; — 第3页

3.9、使用GROUP BY进行分组查询

MySQL数据库中的GROUP BY语句用于将查询结果按照指定的列进行分组，然后对每个分组进行聚合计算，例如求和、平均值等。GROUP BY语句通常与聚合函数一起使用，如SUM、AVG、COUNT等。使用GROUP BY语句可以方便地对数据进行分类汇总，从而更好地了解数据的特征和趋势。
接下来，我们通过一个例子开始学习GROUP BY，代码如下

  1. -- 创建数据库
  2. DROP DATABASE IF EXISTS mydb;
  3. CREATE DATABASE mydb;
  4. USE mydb;
  6. -- 创建员工表
  7. CREATE TABLE employee (
  8.     id int,
  9.     name varchar(50),
  10.     salary int,
  11.     departmentnumber int
  12. );
  14. -- 向员工表中插入数据
  15. INSERT INTO employee values(1,'tome',2000,1001); 
  16. INSERT INTO employee values(2,'lucy',9000,1002); 
  17. INSERT INTO employee values(3,'joke',5000,1003); 
  18. INSERT INTO employee values(4,'wang',3000,1004); 
  19. INSERT INTO employee values(5,'chen',3000,1001); 
  20. INSERT INTO employee values(6,'yukt',7000,1002); 
  21. INSERT INTO employee values(7,'rett',6000,1003); 
  22. INSERT INTO employee values(8,'mujk',4000,1004); 
  23. INSERT INTO employee values(9,'poik',3000,1001);

(1)、GROUP BY和聚合函数一起使用

统计各部门员工个数 MySQL命令：

  1. select count(*), departmentnumber from employee group by departmentnumber;

运行效果展示:
group
统计部门编号大于1001的各部门员工个数 MySQL命令：

  1. select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

运行结果：
group

(2)、GROUP BY和聚合函数以及HAVING一起使用

统计工资总和大于8000的部门 MySQL命令：

  1. select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

运行结果：
having

3.10、使用ORDER BY对查询结果排序

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示：

  1. SELECT 字段名1,字段名2,…
  2. FROM 表名
  3. ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

（1）在MySql中，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
（2）尤其非常特别重要：默认按升序（ASC）排列。
（3）order by 后可加2个（或多个）字段，字段之间用英文逗号隔开。
（4）若A用升序，B用降序，SQL该这样写：order by A ASC, B DESC; 默认同理，也可以这样写：order by A, B DESC;
（5）若A、B都用降序，必须用两个DESC，order by A DESC, B DESC;
（6）多个字段时，优先级按先后顺序而定。
查询所有学生并按照年纪大小升序排列 MySQL命令：

  1. select * from students order by age asc;

运行结果：
order

四、别名设置

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。

4.1、为表取别名

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。语法格式如下所示：

  1. SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

将student改为’学生表’查询整表 MySQL命令：

  1. select * from student as 学生表;

运行结果：
别名

4.2、为字段取别名

在查询操作时，假若字段名很长使用起来就不太方便，此时可该字段取一个別名，用该别名来代替字段的名称。语法格式如下所示：

  1. SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;

将student中的name取别名为“姓名” 查询整表 MySQL命令：

  1. select name as '姓名',id from student;

运行结果：
别名

五、表的关联关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。
多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。
接下来，我们来学习在一对多的关联关系中如果添加和删除数据。先准备一些测试数据，代码如下：

  1. DROP TABLE IF EXISTS student;
  2. DROP TABLE IF EXISTS class;
  4. -- 创建班级表
  5. CREATE TABLE class(
  6.     cid int(4) NOT NULL PRIMARY KEY,
  7.     cname varchar(30) 
  8. );
  10. -- 创建学生表
  11. CREATE TABLE student(
  12.     sid int(8) NOT NULL PRIMARY KEY,
  13.     sname varchar(30),
  14.     classid int(8) NOT NULL
  15. );
  17. -- 为学生表添加外键约束
  18. ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
  19. -- 向班级表插入数据
  20. INSERT INTO class(cid,cname)VALUES(1,'Java');
  21. INSERT INTO class(cid,cname)VALUES(2,'Python');
  23. -- 向学生表插入数据
  24. INSERT INTO student(sid,sname,classid)VALUES(1,'tom',1);
  25. INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
  26. INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
  27. INSERT INTO student(sid,sname,classid)VALUES(4,'kangkang',2);

5.1、关联查询

查询Java班的所有学生 MySQL命令：

  1. select * from student where classid=(select cid from class where cname='Java');

运行结果：
关联

5.2、关于关联关系的删除数据

请从班级表中删除Java班级。在此，请注意：班级表和学生表之间存在关联关系；要删除Java班级，应该先删除学生表中与该班相关联的学生。否则，假若先删除Java班那么学生表中的cid就失去了关联
删除Java班 MySQL命令：

  1. delete from student where classid=(select cid from class where cname='Java');
  2. delete from class where cname='Java';

六、多表连接查询

6.1、交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

  1. SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

6.2、内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

  1. SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

准备数据，代码如下：

  1. -- 若存在数据库mydb则删除
  2. DROP DATABASE IF EXISTS mydb;
  3. -- 创建数据库mydb
  4. CREATE DATABASE mydb;
  5. -- 选择数据库mydb
  6. USE mydb;
  8. -- 创建部门表
  9. CREATE TABLE department(
  10.   did int (4) NOT NULL PRIMARY KEY, 
  11.   dname varchar(20)
  12. );
  14. -- 创建员工表
  15. CREATE TABLE employee (
  16.   eid int (4) NOT NULL PRIMARY KEY, 
  17.   ename varchar (20), 
  18.   eage int (2), 
  19.   departmentid int (4) NOT NULL
  20. );
  22. -- 向部门表插入数据
  23. INSERT INTO department VALUES(1001,'财务部');
  24. INSERT INTO department VALUES(1002,'技术部');
  25. INSERT INTO department VALUES(1003,'行政部');
  26. INSERT INTO department VALUES(1004,'生活部');
  27. -- 向员工表插入数据
  28. INSERT INTO employee VALUES(1,'张三',19,1003);
  29. INSERT INTO employee VALUES(2,'李四',18,1002);
  30. INSERT INTO employee VALUES(3,'王五',20,1001);
  31. INSERT INTO employee VALUES(4,'赵六',20,1004);

查询员工姓名及其所属部门名称 MySQL命令：

  1. select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

运行结果：
inner

6.3、外连接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

  1. SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

(1)、内连接与外连接的区别

外连接： 包括左外连接（Left Outer Join）和右外连接（Right Outer Join）
左连接（Left Join）：以左表为基础，将左表中的所有记录和右表中符合条件的记录连接起来，如果右表中没有符合条件的记录，则右表中的字段值为NULL。
右连接（Right Join）：以右表为基础，将右表中的所有记录和左表中符合条件的记录连接起来，如果左表中没有符合条件的记录，则左表中的字段值为NULL。
内连接（Inner Join）：只连接左表和右表中符合条件的记录，不包括左表或右表中没有符合条件的记录。

准备数据，代码如下：

  1. -- 若存在数据库mydb则删除
  2. DROP DATABASE IF EXISTS mydb;
  3. -- 创建数据库mydb
  4. CREATE DATABASE mydb;
  5. -- 选择数据库mydb
  6. USE mydb;
  8. -- 创建班级表
  9. CREATE TABLE class(
  10.   cid int (4) NOT NULL PRIMARY KEY, 
  11.   cname varchar(20)
  12. );
  14. -- 创建学生表
  15. CREATE TABLE student (
  16.   sid int (4) NOT NULL PRIMARY KEY, 
  17.   sname varchar (20), 
  18.   sage int (2), 
  19.   classid int (4) NOT NULL
  20. );
  21. -- 向班级表插入数据
  22. INSERT INTO class VALUES(1001,'Java');
  23. INSERT INTO class VALUES(1002,'C++');
  24. INSERT INTO class VALUES(1003,'Python');
  25. INSERT INTO class VALUES(1004,'PHP');
  27. -- 向学生表插入数据
  28. INSERT INTO student VALUES(1,'张三',20,1001);
  29. INSERT INTO student VALUES(2,'李四',21,1002);
  30. INSERT INTO student VALUES(3,'王五',24,1002);
  31. INSERT INTO student VALUES(4,'赵六',23,1003);
  32. INSERT INTO student VALUES(5,'田七',23,1003);
  33. INSERT INTO student VALUES(6,'小兰',23,1001);
  34. INSERT INTO student VALUES(7,'Jack',22,1009);
  35. INSERT INTO student VALUES(8,'小芳',23,1008);

(1)、左（外）连接查询

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

  1. select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

运行结果：
左
展示结果分析：
1、分别找出Java班、C++班、Python班的学生
2、右表的Jack不满足查询条件故其没有出现在查询结果中
3、虽然左表的PHP班没有学生，但是任然显示了PHP的信息；但是，它对应的学生名字为NULL

(2)、右（外）连接查询

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。
查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

  1. select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

运行结果：
右
展示结果分析：
1、分别找出Java班、C++班、Python班的学生
2、左表的PHP班不满足查询条件故其没有出现在查询结果中
3、虽然右表的jack，小芳没有对应班级，但是任然显示Jack，小芳的信息；但是，它对应的班级以及班级编号均为NULL

七、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

准备数据，代码如下：

  1. DROP TABLE IF EXISTS student;
  2. DROP TABLE IF EXISTS class;
  4. -- 创建班级表
  5. CREATE TABLE class(
  6.   cid int (4) NOT NULL PRIMARY KEY, 
  7.   cname varchar(20)
  8. );
  10. -- 创建学生表
  11. CREATE TABLE student (
  12.   sid int (4) NOT NULL PRIMARY KEY, 
  13.   sname varchar (20), 
  14.   sage int (2), 
  15.   classid int (4) NOT NULL
  16. );
  18. -- 向班级表插入数据
  19. INSERT INTO class VALUES(1001,'Java');
  20. INSERT INTO class VALUES(1002,'C++');
  21. INSERT INTO class VALUES(1003,'Python');
  22. INSERT INTO class VALUES(1004,'PHP');
  23. INSERT INTO class VALUES(1005,'Android');
  25. -- 向学生表插入数据
  26. INSERT INTO student VALUES(1,'张三',20,1001);
  27. INSERT INTO student VALUES(2,'李四',21,1002);
  28. INSERT INTO student VALUES(3,'王五',24,1003);
  29. INSERT INTO student VALUES(4,'赵六',23,1004);
  30. INSERT INTO student VALUES(5,'小明',21,1001);
  31. INSERT INTO student VALUES(6,'小红',26,1001);
  32. INSERT INTO student VALUES(7,'小亮',27,1002);

7.1、带比较运算符的子查询

比较运算符前面我们提到过得，就是>、<、=、>=、<=、!=等
查询张三同学所在班级的信息 MySQL命令：

  1. select * from class where cid=(select classid from student where sname='张三');

运行结果：
=
查询比张三同学所在班级编号还大的班级的信息 MySQL命令：

  1. select * from class where cid>(select classid from student where sname='张三');

运行结果：
>

7.2、带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行
假如王五同学在学生表中则从班级表查询所有班级信息 MySQL命令：

  1. select * from class where exists (select * from student where sname='王五');

运行结果：
exists

7.3、带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。
查询比任一学生所属班级号还大的班级编号 MySQL命令：

  1. select * from class where cid > any (select classid from student);

运行结果：
any

7.4、带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。
查询比所有学生所属班级号还大的班级编号 MySQL命令：

  1. select * from class where cid > all (select classid from student);

运行结果：
all

八、总结

重要（从关键字分析）：
查询语句的书写顺序
select —>from—>where —> group by —> having —> order by —> limit
查询语句的执行顺序
from—>where —> group by —> having —> select—>order by—>limit

上述如果都学完了的话，可以来做个小测验：
SQL 测验，看看掌握的怎么样！

发表评论

表情：
评论列表 （有 0 条评论，101人围观）

还没有评论，来说两句吧...

相关阅读