MYSQL数据库查询语句

文章最后更新时间为:2018年12月21日 20:51:24

今天我们不来脱库(留下了没技术的眼泪),而来学习一番数据库的查询。什么?数据库的查询语句还不是小菜一碟,高手请直接绕过。

本文章主要介绍单表查询(条件,排序,统计,分组)和多表查询(内连接,外连接,合并查询,子查询)。如果只是简单浏览,可以跳过建立数据库这一部分。

1 建立数据库

1.1 确定数据库表

这里采用的是建立经典的数据库,学生教务系统信息。参考的是《数据库原理与技术》清华大学出版社前三章用的数据库,很简单但是也很实用。

student(sno,sname,age,sex,dept,place) 学生表 学号/姓名/年龄/性别/所在院系/籍贯
course(cno,cname,credit) 课程表 课程号/课程名/学分 s_c(sno,cno,grade) 成绩表
学号/课程号/成绩

具体描述如下:
1 stutent学生表

字段名描述数据类型限制
sno学号numeric(6)PRIMARY KEY
sname姓名varchar(8)NOT NULL
age年龄numeric(2)
sex性别varchar(2)'男'/'女'
dept所在院系varchar(20)
place籍贯varchar(20)

2 course课程表

字段名描述数据类型限制
cno课程号varchar(3)PRIMARY KEY
cname课程名varchar(20)NOT NULL
credit学分numeric(2)

3 s_c选课表

字段名描述数据类型限制
sno学号numeric(6)PRIMARY KEY
cno课程号varchar(3)PRIMARY KEY
grade成绩numeric(3)

1.2 创建数据库

1 创建数据库(设置编码和排序规则)

create database if not exists edu_sys CHARACTER SET utf8 COLLATE utf8_general_ci;

2 进入数据库

use edu_sys;

3 创建student表

CREATE TABLE student 
( 
    sno NUMERIC(6) COMMENT '学号',
    sname VARCHAR(8) NOT NULL COMMENT '姓名', 
    age NUMERIC(2)  COMMENT '年龄',
    sex  VARCHAR(2) COMMENT '性别',
    dept VARCHAR(20) COMMENT '所在院系',
    place VARCHAR(20) COMMENT '籍贯',
    PRIMARY KEY(sno),
    CHECK(sex='男'or sex='女')
);

4 创建课程表

CREATE TABLE course 
( 
    cno VARCHAR(3) COMMENT '课程编号', 
    cname VARCHAR(20) NOT NULL COMMENT '课程名', 
    credit NUMERIC(2) COMMENT '学分', 
    PRIMARY KEY(cno)
);

6 创建选课表

CREATE TABLE s_c 
( 
    sno NUMERIC(6) COMMENT '学号', 
    cno VARCHAR(3) COMMENT '课程号',  
    grade NUMERIC(3) COMMENT '成绩',
    PRIMARY KEY(sno,cno)
);

1.3. 插入数据

先给student表插入数据

insert into student values
    ('200101','王箫虎','18','男','信息院','北京'),
    ('200102','李玉刚','20','女','信息院','上海'),
    ('210101','郭敏星','18','女','英语系','湖北'),
    ('210102','高灵','21','女','英语系','湖南'),
    ('220101','王瑞','19','男','计算机学院','湖北'),
    ('220102','吴迪源','18','女','计算机学院','四川'),
    ('220103','王凌','19','男','计算机学院','湖北');

再给课程插入数据

insert into course values
    ('001','数学','6'),
    ('002','英语','4'),
    ('003','高级语言','4'),
    ('004','数据结构','4'),
    ('005','数据库','3'),
    ('006','操作系统','3');

最后插入成绩

insert into s_c values
    ('200101','001','90'),
    ('200101','002','87'),
    ('200101','003','72'),
    ('210101','001','85'),
    ('210101','002','62'),
    ('220101','003','92'),
    ('220101','005','88');

结果:
student
source
s_c
大功告成,这样我们的数据库就建立完成了。

2 单表查询

2.1 简单数据记录查询

2.1.1 简单数据记录查询

SELECT field1, field2, …, fieldn
FROM table_name;

mysql> SELECT sno,sname,age FROM student;
+--------+--------+-----+
| sno    | sname  | age |
+--------+--------+-----+
| 200101 | 王箫虎 | 18  |
| 200102 | 李玉刚 | 20  |
| 210101 | 郭敏星 | 18  |
| 210102 | 高灵   | 21  |
| 220101 | 王瑞   | 19  |
| 220102 | 吴迪源 | 18  |
| 220103 | 王凌   | 19  |
+--------+--------+-----+
7 rows in set (0.05 sec)

2.1.2 查看表中所有数据

SELECT * FROM table_name;

mysql> SELECT  *  FROM student;
+--------+--------+-----+-----+------------+-------+
| sno    | sname  | age | sex | dept       | place |
+--------+--------+-----+-----+------------+-------+
| 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 200102 | 李玉刚 | 20  | 女  | 信息院     | 上海  |
| 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 210102 | 高灵   | 21  | 女  | 英语系     | 湖南  |
| 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
| 220102 | 吴迪源 | 18  | 女  | 计算机学院 | 四川  |
| 220103 | 王凌   | 19  | 男  | 计算机学院 | 湖北  |
+--------+--------+-----+-----+------------+-------+
7 rows in set (0.07 sec)

2.1.3 避免重复数据查询--DISTINCT

SELECT DISTINCT field1, field2, …, fieldn
FROM table_name;

mysql> SELECT DISTINCT age FROM student;
+-----+
| age |
+-----+
| 18  |
| 20  |
| 21  |
| 19  |
+-----+
4 rows in set (0.07 sec)

mysql> SELECT age FROM student;
+-----+
| age |
+-----+
| 18  |
| 20  |
| 18  |
| 21  |
| 19  |
| 18  |
| 19  |
+-----+
7 rows in set (0.07 sec)

2.1.4 实现四则运算数据查询

SELECT sname, age, age+20 newage
FROM student;

mysql> SELECT  sname, age, age+20  newage  
FROM  student;
+--------+-----+--------+
| sname  | age | newage |
+--------+-----+--------+
| 王箫虎 | 18  | 38     |
| 李玉刚 | 20  | 40     |
| 郭敏星 | 18  | 38     |
| 高灵   | 21  | 41     |
| 王瑞   | 19  | 39     |
| 吴迪源 | 18  | 38     |
| 王凌   | 19  | 39     |
+--------+-----+--------+
7 rows in set (0.05 sec)

2.2 条件记录数据查询

一般格式为:

SELECT field1, field2, …, fieldn
FROM table_name
WHERE condition;

条件查询语句包括:

  • 带关系运算符的条件查询(>, <, =, !=, <>, >=, <=)
  • 带逻辑运算符的条件查询(AND(&&), OR(||), XOR, NOT(!))
  • 带BETWEEN AND关键字的条件查询
  • 带IS NULL 关键字的条件查询
  • 带IN 关键字的条件查询
  • 带LIKE 关键字的条件查询

2.2.1 带关系运算符和逻辑运算符

举例:

mysql> SELECT  sno, sname
FROM  student
WHERE  sex='女' &&  age>19;
+--------+--------+
| sno    | sname  |
+--------+--------+
| 200102 | 李玉刚 |
| 210102 | 高灵   |
+--------+--------+
2 rows in set (0.07 sec)

2.2.2 BETWEEN AND范围查询

SELECT field1, field2, …, fieldn
FROM table_name
WHERE field BETWEEN VALUE1 AND VALUE2;

例子:

mysql> SELECT  sno, sname
       FROM  student
       WHERE  age  BETWEEN 19 AND 21;
+--------+--------+
| sno    | sname  |
+--------+--------+
| 200102 | 李玉刚 |
| 210102 | 高灵   |
| 220101 | 王瑞   |
| 220103 | 王凌   |
+--------+--------+
4 rows in set (0.06 sec)

2.2.3 IS NULL 空值查询

判断属性的数值是否为空的条件查询:
SELECT field1, field2, …, fieldn
FROM table_name
WHERE field IS NULL;

不是空值的条件查询:
… WHERE field IS NOT NULL;
… WHERE NOT field IS NULL;

2.2.4 IN集合查询

判断属性的数值是否在指定集合中的条件查询:
SELECT field1, field2, …, fieldn
FROM table_name
WHERE field IN (value1, value2, …, valuen);

不在指定集合的条件查询:
… WHERE field NOT IN (value1, value2, …, valuen);
… WHERE NOT field IN (value1, value2, …, valuen);

例子:

mysql> SELECT   sno, sname,age
       FROM  student
       WHERE  age  IN (19, 21);
+--------+-------+-----+
| sno    | sname | age |
+--------+-------+-----+
| 210102 | 高灵  | 21  |
| 220101 | 王瑞  | 19  |
| 220103 | 王凌  | 19  |
+--------+-------+-----+
3 rows in set (0.05 sec)

2.2.5 LIKE模糊查询

判断属性的值是否与指定的值相匹配:
SELECT field1, field2, …, fieldn
FROM table_name
WHERE field LIKE value;

判断属性的值是否与指定的值不匹配:
SELECT field1, field2, …, fieldn
FROM table_name
WHERE field NOT LIKE value;

其中LIKE关键字支持的通配符:
_通配符:匹配单个字符
%通配符:匹配任意长度的字符,可以是0个、1个或者多个

如果想查询所有记录,用%%
SELECT field1, field2, …, fieldn
FROM table_name
WHERE field LIKE '%%';

例子:

mysql> SELECT   sno, sname
       FROM  student
       WHERE  sname  LIKE  '王%';
+--------+--------+
| sno    | sname  |
+--------+--------+
| 200101 | 王箫虎 |
| 220101 | 王瑞   |
| 220103 | 王凌   |
+--------+--------+
3 rows in set (0.06 sec)

mysql> SELECT   sno, sname
       FROM  student
       WHERE  sname  LIKE  '王_';
+--------+-------+
| sno    | sname |
+--------+-------+
| 220101 | 王瑞  |
| 220103 | 王凌  |
+--------+-------+
2 rows in set (0.07 sec)

mysql> SELECT   sno, sname
       FROM  student
       WHERE  sname  LIKE  '%%';
+--------+--------+
| sno    | sname  |
+--------+--------+
| 200101 | 王箫虎 |
| 200102 | 李玉刚 |
| 210101 | 郭敏星 |
| 210102 | 高灵   |
| 220101 | 王瑞   |
| 220102 | 吴迪源 |
| 220103 | 王凌   |
+--------+--------+
7 rows in set (0.06 sec)

2.3 排序数据记录查询

ORDER BY 关键字排序查询:
SELECT field1, field2, …, fieldn
FROM table_name
WHERE condition
ORDER BY fieldm1 [ASC|DESC] [, fieldm2] [ASC|DESC];
例子:

单属性排序查询:
mysql> SELECT  sno, sname, age  FROM  student
       WHERE  sname  LIKE  '王%'
       ORDER  BY  age; 
+--------+--------+-----+
| sno    | sname  | age |
+--------+--------+-----+
| 200101 | 王箫虎 | 18  |
| 220101 | 王瑞   | 19  |
| 220103 | 王凌   | 19  |
+--------+--------+-----+
3 rows in set (0.06 sec)

多属性排序查询:
mysql> SELECT  sname, age  FROM  student
       WHERE  sname  LIKE  '王%'
       ORDER  BY  age,  sno DESC; 
+--------+-----+
| sname  | age |
+--------+-----+
| 王箫虎 | 18  |
| 王凌   | 19  |
| 王瑞   | 19  |
+--------+-----+
3 rows in set (0.06 sec)

2.4 统计函数数据记录查询

MySQL支持的统计函数:

  • COUNT( ) :统计表中记录的条数
  • AVG( ): 计算属性值的平均值
  • SUM( ): 计算属性值的总和
  • MAX( ): 查询属性值的最大值
  • MIN( ): 查询属性值的最小值
    统计函数经常与分组一起使用

统计函数查询格式为:
SELECT function(field)
FROM table_name
WHERE condition;

例子:

COUNT()函数
mysql> SELECT   COUNT(sno)  number
       FROM  student; 
+--------+
| number |
+--------+
|      7 |
+--------+
1 row in set (0.05 sec)

AVG()函数
mysql> SELECT   AVG(age)  avg_age
       FROM  student;  
+---------+
| avg_age |
+---------+
| 19.0000 |
+---------+
1 row in set (0.04 sec)

SUM()函数
mysql> SELECT   SUM(age)  total_age
       FROM  student; 
+-----------+
| total_age |
+-----------+
| 133       |
+-----------+
1 row in set (0.04 sec)

MAX()/MIN() 函数
mysql> SELECT   MAX(age)  max_age,  MIN(age)  min_age
       FROM  student;  
+---------+---------+
| max_age | min_age |
+---------+---------+
| 21      | 18      |
+---------+---------+
1 row in set (0.04 sec)

2.5 分组数据记录查询

2.5.1单分组查询

格式如下:
SELECT function(field)
FROM table_name
WHERE condition
GROUP BY field;

例子:

mysql> SELECT   *
       FROM  student; 
+--------+--------+-----+-----+------------+-------+
| sno    | sname  | age | sex | dept       | place |
+--------+--------+-----+-----+------------+-------+
| 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 200102 | 李玉刚 | 20  | 女  | 信息院     | 上海  |
| 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 210102 | 高灵   | 21  | 女  | 英语系     | 湖南  |
| 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
| 220102 | 吴迪源 | 18  | 女  | 计算机学院 | 四川  |
| 220103 | 王凌   | 19  | 男  | 计算机学院 | 湖北  |
+--------+--------+-----+-----+------------+-------+
7 rows in set (0.06 sec)
mysql> SELECT   *
       FROM  student
       GROUP  BY  dept; 
+--------+--------+-----+-----+------------+-------+
| sno    | sname  | age | sex | dept       | place |
+--------+--------+-----+-----+------------+-------+
| 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
+--------+--------+-----+-----+------------+-------+
3 rows in set (0.05 sec)

如果想显示每个分组中的字段,通过函数GROUP_CONCAT():

mysql> SELECT  GROUP_CONCAT(sname)
    FROM  student
    GROUP  BY  dept; 
+---------------------+
| GROUP_CONCAT(sname) |
+---------------------+
| 王箫虎,李玉刚       |
| 郭敏星,高灵         |
| 王瑞,吴迪源,王凌    |
+---------------------+
3 rows in set (0.07 sec)

2.5.2 多分组查询

一般格式:
SELECT function(field), GROUP_CONCAT(field)
FROM table_name
WHERE condition
GROUP BY field1, field2, …, fieldn;
先根据filed1进行分组,然后对每组根据field2进行分组,....

mysql> SELECT   *
    FROM  student
    GROUP  BY  dept,  place; 
+--------+--------+-----+-----+------------+-------+
| sno    | sname  | age | sex | dept       | place |
+--------+--------+-----+-----+------------+-------+
| 200102 | 李玉刚 | 20  | 女  | 信息院     | 上海  |
| 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 210102 | 高灵   | 21  | 女  | 英语系     | 湖南  |
| 220102 | 吴迪源 | 18  | 女  | 计算机学院 | 四川  |
| 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
+--------+--------+-----+-----+------------+-------+
6 rows in set (0.06 sec)

2.5.3 HAVING限定分组查询

一般格式:
SELECT function(field)
FROM table_name
WHERE condition1
GROUP BY field1, field2, …, fieldn
HAVING conditon2;
分组后,满足HAVING条件的记录

mysql> SELECT   *
    FROM  student
    GROUP  BY  dept , place
    HAVING age>19;
+--------+--------+-----+-----+--------+-------+
| sno    | sname  | age | sex | dept   | place |
+--------+--------+-----+-----+--------+-------+
| 200102 | 李玉刚 | 20  | 女  | 信息院 | 上海  |
| 210102 | 高灵   | 21  | 女  | 英语系 | 湖南  |
+--------+--------+-----+-----+--------+-------+
2 rows in set (0.08 sec)

3 多表查询

首先我们需要知道基本的关系数据操作:

并(UNION)
笛卡尔积(CARTESIAN PRODUCT)
连接(JOIN)
内连接(INNER JOIN)
自然连接(NATURAL JOIN)
等值连接(=)
不等连接(!=)
外连接(OUTER JOIN)
左外连接 (LEFT JOIN)
右外连接 (RIGHT JOIN)
全外连接(OUTER JOIN)

关于上面的

3.1 内连接查询

方式一:
SELECT field1, field2, …, fieldn
FROM table_name1, table_name2
WHERE join_condition;

方式二(推荐):
SELECT field1, field2, …, fieldn
FROM table_name1 INNER JOIN table_name2
ON joint_conditon;

3.1.1 自连接

-- 方式一
mysql> SELECT  *   
FROM  s_c  t1,  s_c  t2
WHERE  t1.cno=t2.cno;  
+--------+-----+-------+--------+-----+-------+
| sno    | cno | grade | sno    | cno | grade |
+--------+-----+-------+--------+-----+-------+
| 200101 | 001 | 90    | 200101 | 001 | 90    |
| 210101 | 001 | 85    | 200101 | 001 | 90    |
| 200101 | 002 | 87    | 200101 | 002 | 87    |
| 210101 | 002 | 62    | 200101 | 002 | 87    |
| 200101 | 003 | 72    | 200101 | 003 | 72    |
| 220101 | 003 | 92    | 200101 | 003 | 72    |
| 200101 | 001 | 90    | 210101 | 001 | 85    |
| 210101 | 001 | 85    | 210101 | 001 | 85    |
| 200101 | 002 | 87    | 210101 | 002 | 62    |
| 210101 | 002 | 62    | 210101 | 002 | 62    |
| 200101 | 003 | 72    | 220101 | 003 | 92    |
| 220101 | 003 | 92    | 220101 | 003 | 92    |
| 220101 | 005 | 88    | 220101 | 005 | 88    |
+--------+-----+-------+--------+-----+-------+
13 rows in set (0.09 sec)

-- 方式二
mysql> SELECT  *   
FROM  s_c  t1  INNER  JOIN  s_c  t2
ON  t1.cno=t2.cno;
+--------+-----+-------+--------+-----+-------+
| sno    | cno | grade | sno    | cno | grade |
+--------+-----+-------+--------+-----+-------+
| 200101 | 001 | 90    | 200101 | 001 | 90    |
| 210101 | 001 | 85    | 200101 | 001 | 90    |
| 200101 | 002 | 87    | 200101 | 002 | 87    |
| 210101 | 002 | 62    | 200101 | 002 | 87    |
| 200101 | 003 | 72    | 200101 | 003 | 72    |
| 220101 | 003 | 92    | 200101 | 003 | 72    |
| 200101 | 001 | 90    | 210101 | 001 | 85    |
| 210101 | 001 | 85    | 210101 | 001 | 85    |
| 200101 | 002 | 87    | 210101 | 002 | 62    |
| 210101 | 002 | 62    | 210101 | 002 | 62    |
| 200101 | 003 | 72    | 220101 | 003 | 92    |
| 220101 | 003 | 92    | 220101 | 003 | 92    |
| 220101 | 005 | 88    | 220101 | 005 | 88    |
+--------+-----+-------+--------+-----+-------+
13 rows in set (0.06 sec)

3.1.2 等值连接

-- 方式一
mysql> SELECT   *   
     FROM  s_c ,  course 
     WHERE  s_c.cno=course.cno;  

+--------+-----+-------+-----+----------+--------+
| sno    | cno | grade | cno | cname    | credit |
+--------+-----+-------+-----+----------+--------+
| 200101 | 001 | 90    | 001 | 数学     | 6      |
| 200101 | 002 | 87    | 002 | 英语     | 4      |
| 200101 | 003 | 72    | 003 | 高级语言 | 4      |
| 210101 | 001 | 85    | 001 | 数学     | 6      |
| 210101 | 002 | 62    | 002 | 英语     | 4      |
| 220101 | 003 | 92    | 003 | 高级语言 | 4      |
| 220101 | 005 | 88    | 005 | 数据库   | 3      |
+--------+-----+-------+-----+----------+--------+
7 rows in set (0.06 sec)

-- 方式二
mysql> SELECT   *   
     FROM  s_c   INNER  JOIN  course 
     ON  s_c.cno=course.cno;
+--------+-----+-------+-----+----------+--------+
| sno    | cno | grade | cno | cname    | credit |
+--------+-----+-------+-----+----------+--------+
| 200101 | 001 | 90    | 001 | 数学     | 6      |
| 200101 | 002 | 87    | 002 | 英语     | 4      |
| 200101 | 003 | 72    | 003 | 高级语言 | 4      |
| 210101 | 001 | 85    | 001 | 数学     | 6      |
| 210101 | 002 | 62    | 002 | 英语     | 4      |
| 220101 | 003 | 92    | 003 | 高级语言 | 4      |
| 220101 | 005 | 88    | 005 | 数据库   | 3      |
+--------+-----+-------+-----+----------+--------+
7 rows in set (0.06 sec)

-- 多个表
mysql> SELECT * FROM s_c,course,student WHERE course.cno=s_c.cno AND s_c.sno=student.sno;
+--------+-----+-------+-----+----------+--------+--------+--------+-----+-----+------------+-------+
| sno    | cno | grade | cno | cname    | credit | sno    | sname  | age | sex | dept       | place |
+--------+-----+-------+-----+----------+--------+--------+--------+-----+-----+------------+-------+
| 200101 | 001 | 90    | 001 | 数学     | 6      | 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 200101 | 002 | 87    | 002 | 英语     | 4      | 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 200101 | 003 | 72    | 003 | 高级语言 | 4      | 200101 | 王箫虎 | 18  | 男  | 信息院     | 北京  |
| 210101 | 001 | 85    | 001 | 数学     | 6      | 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 210101 | 002 | 62    | 002 | 英语     | 4      | 210101 | 郭敏星 | 18  | 女  | 英语系     | 湖北  |
| 220101 | 003 | 92    | 003 | 高级语言 | 4      | 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
| 220101 | 005 | 88    | 005 | 数据库   | 3      | 220101 | 王瑞   | 19  | 男  | 计算机学院 | 湖北  |
+--------+-----+-------+-----+----------+--------+--------+--------+-----+-----+------------+-------+
7 rows in set (0.09 sec)

3.1.3 自然连接(NATURAL JOIN)

mysql> SELECT   *   
FROM  s_c  
NATURAL  JOIN  course; 
+-----+--------+-------+----------+--------+
| cno | sno    | grade | cname    | credit |
+-----+--------+-------+----------+--------+
| 001 | 200101 | 90    | 数学     | 6      |
| 002 | 200101 | 87    | 英语     | 4      |
| 003 | 200101 | 72    | 高级语言 | 4      |
| 001 | 210101 | 85    | 数学     | 6      |
| 002 | 210101 | 62    | 英语     | 4      |
| 003 | 220101 | 92    | 高级语言 | 4      |
| 005 | 220101 | 88    | 数据库   | 3      |
+-----+--------+-------+----------+--------+
7 rows in set (0.09 sec)

3.2 外连接查询

SELECT field1, field2, …, fieldn
FROM table_name1 LEFT|RIGHT|FULL JOIN table_name2
ON joint_conditon;

举例:

-- 全外连接
mysql> SELECT   *   
     FROM  s_c  FULL  JOIN  course; 

+--------+-----+-------+-----+----------+--------+
| sno    | cno | grade | cno | cname    | credit |
+--------+-----+-------+-----+----------+--------+
| 200101 | 001 | 90    | 001 | 数学     | 6      |
| 200101 | 001 | 90    | 002 | 英语     | 4      |
| 200101 | 001 | 90    | 003 | 高级语言 | 4      |
| 200101 | 001 | 90    | 004 | 数据结构 | 4      |
| 200101 | 001 | 90    | 005 | 数据库   | 3      |
| 200101 | 001 | 90    | 006 | 操作系统 | 3      |
| 200101 | 002 | 87    | 001 | 数学     | 6      |
| 200101 | 002 | 87    | 002 | 英语     | 4      |
| 200101 | 002 | 87    | 003 | 高级语言 | 4      |
| 200101 | 002 | 87    | 004 | 数据结构 | 4      |
| 200101 | 002 | 87    | 005 | 数据库   | 3      |
| 200101 | 002 | 87    | 006 | 操作系统 | 3      |
| 200101 | 003 | 72    | 001 | 数学     | 6      |
| 200101 | 003 | 72    | 002 | 英语     | 4      |
| 200101 | 003 | 72    | 003 | 高级语言 | 4      |
| 200101 | 003 | 72    | 004 | 数据结构 | 4      |
| 200101 | 003 | 72    | 005 | 数据库   | 3      |
| 200101 | 003 | 72    | 006 | 操作系统 | 3      |
| 210101 | 001 | 85    | 001 | 数学     | 6      |
| 210101 | 001 | 85    | 002 | 英语     | 4      |
| 210101 | 001 | 85    | 003 | 高级语言 | 4      |
| 210101 | 001 | 85    | 004 | 数据结构 | 4      |
| 210101 | 001 | 85    | 005 | 数据库   | 3      |
| 210101 | 001 | 85    | 006 | 操作系统 | 3      |
| 210101 | 002 | 62    | 001 | 数学     | 6      |
| 210101 | 002 | 62    | 002 | 英语     | 4      |
| 210101 | 002 | 62    | 003 | 高级语言 | 4      |
| 210101 | 002 | 62    | 004 | 数据结构 | 4      |
| 210101 | 002 | 62    | 005 | 数据库   | 3      |
| 210101 | 002 | 62    | 006 | 操作系统 | 3      |
| 220101 | 003 | 92    | 001 | 数学     | 6      |
| 220101 | 003 | 92    | 002 | 英语     | 4      |
| 220101 | 003 | 92    | 003 | 高级语言 | 4      |
| 220101 | 003 | 92    | 004 | 数据结构 | 4      |
| 220101 | 003 | 92    | 005 | 数据库   | 3      |
| 220101 | 003 | 92    | 006 | 操作系统 | 3      |
| 220101 | 005 | 88    | 001 | 数学     | 6      |
| 220101 | 005 | 88    | 002 | 英语     | 4      |
| 220101 | 005 | 88    | 003 | 高级语言 | 4      |
| 220101 | 005 | 88    | 004 | 数据结构 | 4      |
| 220101 | 005 | 88    | 005 | 数据库   | 3      |
| 220101 | 005 | 88    | 006 | 操作系统 | 3      |
+--------+-----+-------+-----+----------+--------+
42 rows in set (0.19 sec)

-- 左外连接
mysql> SELECT  * 
       FROM  s_c  LEFT  JOIN  course
       ON  s_c.cno=course.cno;
+--------+-----+-------+-----+----------+--------+
| sno    | cno | grade | cno | cname    | credit |
+--------+-----+-------+-----+----------+--------+
| 200101 | 001 | 90    | 001 | 数学     | 6      |
| 200101 | 002 | 87    | 002 | 英语     | 4      |
| 200101 | 003 | 72    | 003 | 高级语言 | 4      |
| 210101 | 001 | 85    | 001 | 数学     | 6      |
| 210101 | 002 | 62    | 002 | 英语     | 4      |
| 220101 | 003 | 92    | 003 | 高级语言 | 4      |
| 220101 | 005 | 88    | 005 | 数据库   | 3      |
+--------+-----+-------+-----+----------+--------+
7 rows in set (0.10 sec)

--右外连接
mysql> SELECT   *   
     FROM  s_c  RIGHT  JOIN  course
     ON  s_c.cno=course.cno;
+--------+------+-------+-----+----------+--------+
| sno    | cno  | grade | cno | cname    | credit |
+--------+------+-------+-----+----------+--------+
| 200101 | 001  | 90    | 001 | 数学     | 6      |
| 210101 | 001  | 85    | 001 | 数学     | 6      |
| 200101 | 002  | 87    | 002 | 英语     | 4      |
| 210101 | 002  | 62    | 002 | 英语     | 4      |
| 200101 | 003  | 72    | 003 | 高级语言 | 4      |
| 220101 | 003  | 92    | 003 | 高级语言 | 4      |
| NULL   | NULL | NULL  | 004 | 数据结构 | 4      |
| 220101 | 005  | 88    | 005 | 数据库   | 3      |
| NULL   | NULL | NULL  | 006 | 操作系统 | 3      |
+--------+------+-------+-----+----------+--------+
9 rows in set (0.09 sec)
-- 

3.3 合并查询数据记录

将查询结果合并在一起
SELECT field1, field2, …, fieldn
FROM table_name1
UNION| UNION ALL
SELECT field1, field2, …, fieldn
FROM table_name2
UNION| UNION ALL
SELECT field1, field2, …, fieldn
FROM table_name3

注意:UNION 会去掉重复项 UNION ALL 不会去掉重复项

mysql> SELECT   sno, sname FROM  student WHERE  sex='男'
       UNION
    SELECT  sno, sname FROM  student WHERE  sex='女';
+--------+--------+
| sno    | sname  |
+--------+--------+
| 200101 | 王箫虎 |
| 220101 | 王瑞   |
| 220103 | 王凌   |
| 200102 | 李玉刚 |
| 210101 | 郭敏星 |
| 210102 | 高灵   |
| 220102 | 吴迪源 |
+--------+--------+
7 rows in set (0.09 sec)

3.4 子查询

虽然连接查询可实现多表查询,但效率太低,不建议使用,建议使用子查询
使用COUNT() 函数查看表的数据记录数目,如果数目少,可使用连接查询,如果数目太大,使用子查询

子查询经常出现在WHERE和FROM子句中

  • WHERE 子句中的子查询,一般返回单行单列、多行单列、单行多列数据
  • FROM 子句中的子查询,一般返回多行多列数据

例子:

-- 查询年龄比王瑞大的学生信息
mysql> SELECT   sno, sname, age
       FROM  student
       WHERE  age> (
            SELECT  age
            FROM  student
            WHERE  sname='王瑞');
+--------+--------+-----+
| sno    | sname  | age |
+--------+--------+-----+
| 200102 | 李玉刚 | 20  |
| 210102 | 高灵   | 21  |
+--------+--------+-----+
2 rows in set (0.07 sec)

-- 查询年龄和姓名跟王睿一样的学生信息

mysql> SELECT   sno, sname, age, sex
       FROM  student
       WHERE  (age, sex) =(
            SELECT  age, sex
            FROM  student
            WHERE  sname='王瑞');
+--------+-------+-----+-----+
| sno    | sname | age | sex |
+--------+-------+-----+-----+
| 220101 | 王瑞  | 19  | 男  |
| 220103 | 王凌  | 19  | 男  |
+--------+-------+-----+-----+
2 rows in set (0.06 sec)

-- 查询年龄不小于任何女生年龄的学生信息:
mysql> SELECT   sno, sname, age, sex
       FROM  student
       WHERE  age>=ANY (
            SELECT  age
            FROM  student
            WHERE  sex='女');
+--------+--------+-----+-----+
| sno    | sname  | age | sex |
+--------+--------+-----+-----+
| 200101 | 王箫虎 | 18  | 男  |
| 200102 | 李玉刚 | 20  | 女  |
| 210101 | 郭敏星 | 18  | 女  |
| 210102 | 高灵   | 21  | 女  |
| 220101 | 王瑞   | 19  | 男  |
| 220102 | 吴迪源 | 18  | 女  |
| 220103 | 王凌   | 19  | 男  |
+--------+--------+-----+-----+
7 rows in set (0.07 sec)

4 结束语

本文到此结束,感谢您的收看!

1 + 1 =
快来做第一个评论的人吧~