蓝蓝站点

Java读书笔记

13.2.12 多表连接查询 2. SQL99的连接查询

发表于 2019-05-28 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 9.1k 阅读时长 ≈ 8 分钟

13.2.12 多表连接查询 2. SQL99的连接查询

SQL99的连接查询与SQL92的连接查询原理基本相似,不同的是SQL99连接查询的可读性更强——查询用的多个数据表显式使用xxx join连接,而不是直接依次排列在from之后,from后只需要放一个数据表;连接条件不再放在where之后,而是提供了专门的连接条件子句。

交叉连接

交叉连接(cross join):交叉连接效果就是SQL92中的广义笛卡儿积,所以交叉连接无须任何连接条件,如下SQL语句所示:

select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
#cross join交叉连接,相当于广义笛卡尔积
cross join teacher_table t;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
#cross join交叉连接,相当于广义笛卡尔积
cross join teacher_table t;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          1 | 张三         |            1 | Leegang      |
|          1 | 张三         |            1 | Martine      |
|          2 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Leegang      |
|          2 | 张三         |            1 | Martine      |
|          3 | 李四         |            1 | Yeeku        |
|          3 | 李四         |            1 | Leegang      |
|          3 | 李四         |            1 | Martine      |
|          4 | 王五         |            2 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          4 | 王五         |            2 | Martine      |
|          5 | _王五        |            2 | Yeeku        |
|          5 | _王五        |            2 | Leegang      |
|          5 | _王五        |            2 | Martine      |
|          6 | NULL         |            2 | Yeeku        |
|          6 | NULL         |            2 | Leegang      |
|          6 | NULL         |            2 | Martine      |
|          7 | 赵六         |            3 | Yeeku        |
|          7 | 赵六         |            3 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+

自然连接

自然连接(natural join):自然连接表面上看起来也无须指定连接条件,但自然连接是有连接条件的,自然连接会以两个表中的同名列作为连接条件;如果两个表中没有同名列,则自然连接与交叉连接效果完全一样——因为没有连接条件。如下SQL语句所示:

select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join teacher_table t;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join teacher_table t;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          1 | 张三         |            1 | Leegang      |
|          1 | 张三         |            1 | Martine      |
|          2 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Leegang      |
|          2 | 张三         |            1 | Martine      |
|          3 | 李四         |            1 | Yeeku        |
|          3 | 李四         |            1 | Leegang      |
|          3 | 李四         |            1 | Martine      |
|          4 | 王五         |            2 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          4 | 王五         |            2 | Martine      |
|          5 | _王五        |            2 | Yeeku        |
|          5 | _王五        |            2 | Leegang      |
|          5 | _王五        |            2 | Martine      |
|          6 | NULL         |            2 | Yeeku        |
|          6 | NULL         |            2 | Leegang      |
|          6 | NULL         |            2 | Martine      |
|          7 | 赵六         |            3 | Yeeku        |
|          7 | 赵六         |            3 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
21 rows in set

using子句

using子句连接:using子句可以指定一列或多列,用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列,如果使用natural join,则会把所有的同名列当成连接条件;使用using子句,就可显式指定使用哪些同名列作为连接条件。如下SQL语句所示:

select s.*,teacher_name
from student_table s
# join 连接另一个表
join teacher_table t
using(teacher_id);

运行结果:

mysql> select s.*,teacher_name
from student_table s
# join 连接另一个表
join teacher_table t
using(teacher_id);
1054 - Unknown column 'teacher_id' in 'from clause'

运行上面语句将出现一个错误,因为student_table表中并不存在名为teacher_id的列。也就是说,如果使用using子句来指定连接条件,则两个表中必须有同名列,否则就会出现错误。

on子句连接

on子句连接:这是最常用的连接方式,SQL99语法的连接条件放在on子句中指定,而且每个on子句只指定一个连接条件。这意味着:如果需要进行N表连接,则需要有N-1个join...on对。如下SQL语句所示:

select s.*,teacher_name
from student_table s
join teacher_table t
on s.java_teacher=t.teacher_id;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_name
from student_table s
join teacher_table t
on s.java_teacher=t.teacher_id;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Yeeku        |
|          3 | 李四         |            1 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          5 | _王五        |            2 | Leegang      |
|          6 | NULL         |            2 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
7 rows in set

使用on子句的连接完全可以代替SQL92中的等值连接、非等值连接,因为on子句的连接条件除了等值条件之外,也可以是非等值条件。如下SQL语句就是SQL99中的非等值连接。

select s.*,teacher_name
from student_table s
join teacher_table t
# 使用on来指定连接条件:非等值连接
on s.java_teacher >t.teacher_id;

运行效果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_id,teacher_name
from student_table s
join teacher_table t
# 使用on来指定连接条件:非等值连接
on s.java_teacher >t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
|          4 | 王五         |            2 |          1 | Yeeku        |
|          5 | _王五        |            2 |          1 | Yeeku        |
|          6 | NULL         |            2 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          2 | Leegang      |
+------------+--------------+--------------+------------+--------------+
5 rows in set

外连接

左、右、全外连接:这三种外连接分别使用left[outer]join、right[outer]join和full[outer]join,这三种外连接的连接条件一样通过on子句来指定,既可以是等值连接条件,也可以是非等值连接条件。

右外连接

下面使用右外连接,连接条件是非等值连接。

select s.*,teacher_id,teacher_name
from student_table s
right join teacher_table t
on s.java_teacher<t.teacher_id;

运行效果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_id,teacher_name
from student_table s
right join teacher_table t
on s.java_teacher<t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
| NULL       | NULL         | NULL         |          1 | Yeeku        |
|          1 | 张三         |            1 |          2 | Leegang      |
|          2 | 张三         |            1 |          2 | Leegang      |
|          3 | 李四         |            1 |          2 | Leegang      |
|          1 | 张三         |            1 |          3 | Martine      |
|          2 | 张三         |            1 |          3 | Martine      |
|          3 | 李四         |            1 |          3 | Martine      |
|          4 | 王五         |            2 |          3 | Martine      |
|          5 | _王五        |            2 |          3 | Martine      |
|          6 | NULL         |            2 |          3 | Martine      |
+------------+--------------+--------------+------------+--------------+
10 rows in set

左外连接

下面使用左外连接,连接条件是非等值连接。

select s.*,teacher_name
from student_table s
left join teacher_table t
on s.java_teacher>t.teacher_id;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_id,teacher_name
from student_table s
left join teacher_table t
on s.java_teacher>t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
|          1 | 张三         |            1 | NULL       | NULL         |
|          2 | 张三         |            1 | NULL       | NULL         |
|          3 | 李四         |            1 | NULL       | NULL         |
|          4 | 王五         |            2 |          1 | Yeeku        |
|          5 | _王五        |            2 |          1 | Yeeku        |
|          6 | NULL         |            2 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          2 | Leegang      |
+------------+--------------+--------------+------------+--------------+
8 rows in set

运行上面两条外连接语句并查看它们的运行结果,不难发现SQL99外连接与SQL92外连接恰好相反,SQL99左外连接将会把左边表中所有不满足连接条件的记录全部列出;SQL99右外连接将会把右边表中所有不满足连接条件的记录全部列出。

全外连接

下面的SQL语句使用全外连接,连接条件是等值连接。

select s.*,teacher_name
from student_table s
full join teacher_table t
on s.java_teacher=t.teacher_id;

运行错误:

1
2

1064 - 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 'full join teacher_table t
on s.java_teacher=t.teacher_id' at line 3

SQL99的全外连接将会把两个表中所有不满足连接条件的记录全部列出。
注意运行上面查询语句时会出现错误,这是因为**MySQL并不支持全外连接**。

13.2.12 多表连接查询 1. SQL92的连接查询

发表于 2019-05-28 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 7.7k 阅读时长 ≈ 7 分钟

13.2.12 多表连接查询

很多时候,需要选择的数据并不是来自一个表,而是来自多个数据表,这就需要使用多表连接查询。例如,对于上面的student_table和teacher_table两个数据表,如果希望查询出所有学生以及他的老师名字,这就需要从两个表中取数据。

两种连接规范

SQL92

多表连接查询有两种规范,较早的SQL92规范支持如下几种多表连接查询。

等值连接。
非等值连接。
外连接。
广义笛卡儿积。

SQL99

SQL99规范提供了可读性更好的多表连接语法,并提供了更多类型的连接查询。SQL99支持如下几种多表连接查询。

交叉连接。
自然连接。
使用using子句的连接。
使用on子句的连接。
全外连接或者左、右外连接。

1. SQL92的连接查询

SQL92的多表连接语法比较简洁,这种语法把多个数据表都放在from之后,多个表之间以逗号隔开;连接条件放在where之后,与查询条件之间用and逻辑运算符连接。

如果连接条件要求两列值相等,则称为等值连接,否则称为非等值连接;
如果没有任何连接条件,则称为广义笛卡儿积。

语法格式

SQL92中多表连接查询的语法格式如下:

1
2
3

select column1,column2,...
from table1,table2....
[where join_condition]

列同名的情况

多表连接查询中可能出现两个或多个数据列具有相同的列名,则需要在这些同名列之间使用表名前缀或表别名前缀作为限制,避免系统混淆。
实际上,所有的列都可以增加表名前缀或表别名前缀。只是进行单表查询时,绝不可能出现同名列,所以系统不可能混淆,因此通常省略表名前缀。
如下SQL语句查询出所有学生的资料以及对应的老师姓名。

select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件
where s.java_teacher=t.teacher_id;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件
where s.java_teacher=t.teacher_id;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Yeeku        |
|          3 | 李四         |            1 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          5 | _王五        |            2 | Leegang      |
|          6 | NULL         |            2 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
7 rows in set

上面的查询结果正好满足要求,可以看到每个学生以及他对应的老师的名字。

伪代码

实际上,多表查询的过程可理解成一个嵌套循环,这个嵌套循环的伪码如下:

//依次遍历teacher_table表中的每条记录
for t in teacher_table
{
    // 遍历 student_table表中的每条记录
    for s in student_table
    {
        if(s.java_teacher=t.teacher_id)
        {
            output s+t
        }
    }
}

广义笛卡尔积

理解了上面的伪码之后,接下来即可很轻易地理解多表连接查询的运行机制。如果求广义笛卡儿积,则where子句后没有任何连接条件,相当于没有上面的if语句,广义笛卡儿积的结果会有n×m条记录。只要把where后的连接条件去掉,就可以得到广义笛卡儿积,如下SQL语句所示:

#不使用连接条件,得到广义笛卡尔积
select s.*,teacher_name
#指定多个数据表,并指定表别名
from student_table s,teacher_table t;

运行效果如下:

| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> #不使用连接条件,得到广义笛卡尔积
select s.*,teacher_name
#指定多个数据表,并指定表别名
from student_table s,teacher_table t;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          1 | 张三         |            1 | Leegang      |
|          1 | 张三         |            1 | Martine      |
|          2 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Leegang      |
|          2 | 张三         |            1 | Martine      |
|          3 | 李四         |            1 | Yeeku        |
|          3 | 李四         |            1 | Leegang      |
|          3 | 李四         |            1 | Martine      |
|          4 | 王五         |            2 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          4 | 王五         |            2 | Martine      |
|          5 | _王五        |            2 | Yeeku        |
|          5 | _王五        |            2 | Leegang      |
|          5 | _王五        |            2 | Martine      |
|          6 | NULL         |            2 | Yeeku        |
|          6 | NULL         |            2 | Leegang      |
|          6 | NULL         |            2 | Martine      |
|          7 | 赵六         |            3 | Yeeku        |
|          7 | 赵六         |            3 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
21 rows in set

与此类似的是,非等值连接的执行结果可以使用上面的嵌套循环来计算,如下SQL语句所示:

select s.*,teacher_id,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 非等值连接
where s.java_teacher>t.teacher_id;

运行结果：

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_id,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 非等值连接
where s.java_teacher>t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
|          4 | 王五         |            2 |          1 | Yeeku        |
|          5 | _王五        |            2 |          1 | Yeeku        |
|          6 | NULL         |            2 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          2 | Leegang      |
+------------+--------------+--------------+------------+--------------+
5 rows in set

上面SQL语句的执行结果相当于if条件换成了s.java_teacher>t.teacher_id。
如果还需要对记录进行过滤,则将过滤条件和连接条件使用and连接起来,如下SQL语句所示:

select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件,并指定student_name列不能不为null
where s.java_teacher=t.teacher_id and student_name is not null;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件,并指定student_name列不能不为null
where s.java_teacher=t.teacher_id and student_name is not null;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Yeeku        |
|          3 | 李四         |            1 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          5 | _王五        |            2 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
6 rows in set

外连接

虽然**MySQL不支持SQL92中的左外连接、右外连接**,但本书还是有必要了解一下SQL92中的左外连接和右外连接。SQL92中的外连接就是在连接条件的列名后增加括号包起来的外连接符(+或*,不同的数据库有一定的区别),当外连接符出现在左边时称为左外连接,出现在右边时则称为右外连接。如下SQL语句所示:

select s.*,teacher_name
from student_table s,teacher_table t
# 右外连接
where s.java_teacher=t.teacher_id(*);

外连接就是在外连接符所在的表中增加一个”万能行”,这行记录的所有数据都是null,而且该行可以与另一个表中所有不满足条件的记录进行匹配,通过这种方式就可以把另一个表中的所有记录选出来,不管这些记录是否满足连接条件。

自连接

除此之外,还有一种自连接,正如前面介绍外键约束时提到的自关联,如果同一个表中表中的不同记录之间存在主、外键约束关联,例如把员工、经理保存在同一个表里,则需要使用自连接查询。
注意
自连接只是连接的一种用法,并不是一种连接类型,不管是SQL92还是SQL99都可以使用自连接查询。自连接的本质就是把一个表当成两个表来用。
下面的SQL语句建立了一个自关联的数据表,并向表中插入了4条数据。

create table emp_table
(
    emp_id int auto_increment primary key,
    emp_name varchar(255),
    manager_id int,
    # 自关联
    foreign key(manager_id) references emp_table(emp_id)
);
# 简写形式insert,要给所有列赋值,
# 第一列自增长,可以随意赋值,这里赋值null
insert into emp_table
values(null,'唐僧',null);
insert into emp_table
values(null,'孙悟空',1);
insert into emp_table
values(null,'猪八戒',1);
insert into emp_table
values(null,'沙僧',1);

如果需要查询该数据表中的所有员工名,以及每个员工对应的经理名,则必须使用自连接查询。所谓自连接就是把一个表当成两个表来用,这就需要为一个表起两个别名,而且查询中用的所有数据列都要加表别名前缀,因为两个表的数据列完全一样。下面的自连接查询可以查询出所有的员工名,以及对应的经理名。

1
2
3

select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manager_id=mgr.emp_id;

查询结果:

mysql> select * from emp_table;
+--------+----------+------------+
| emp_id | emp_name | manager_id |
+--------+----------+------------+
|      1 | 唐僧     | NULL       |
|      2 | 孙悟空   |          1 |
|      3 | 猪八戒   |          1 |
|      4 | 沙僧     |          1 |
+--------+----------+------------+
4 rows in set
mysql> select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manager_id=mgr.emp_id;
+--------+--------+--------+
| emp_id | 员工名 | 经理名 |
+--------+--------+--------+
|      2 | 孙悟空 | 唐僧   |
|      3 | 猪八戒 | 唐僧   |
|      4 | 沙僧   | 唐僧   |
+--------+--------+--------+
3 rows in set

SQL99的连接查询

13.2.11 分组和组函数

发表于 2019-05-27 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 8.5k 阅读时长 ≈ 8 分钟

13.2.11 分组和组函数

组函数也就是前面提到的多行函数,组函数将一组记录作为整体计算,每组记录返回一个结果,而不是每条记录返回一个结果。常用的组函数有如下5个。

avg([distinct|all]表达式):计算多行表达式的平均值,其中,表达式可以是变量、常量或数据列,但其数据类型必须是数值型。还可以在变量、列前使用distinct或all关键字,如果使用distinct,则表明不计算重复值;all用和不用的效果完全一样,表明需要计算重复值。
count({*|[distinct|all]表达式}):计算多行表达式的总条数,其中,表达式可以是变量、常量或数据列,其数据类型可以是任意类型;用星号(*)表示统计该表内的记录行数;distinct表示不计算重复值。
max(表达式):计算多行表达式的最大值,其中表达式可以是变量、常量或数据列,其数据类型可以是任意类型。
min(表达式):计算多行表达式的最小值,其中表达式可以是变量、常量或数据列,其数据类型可以是任意类型。
sum([distinct|all]表达式):计算多行表达式的总和,其中,表达式可以是变量、常量或数据列,但其数据类型必须是数值型;distinct表示不计算重复值。

实例

sum函数

统计某列的总和

统计所有student_id的总和

1 2	select sum(student_id) from student_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select sum(student_id)
from student_table;
+-----------------+
| sum(student_id) |
+-----------------+
| 28              |
+-----------------+
1 row in set

每行都加上一个常数

1 2	select sum(20) from student_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select sum(20)
from student_table;
+---------+
| sum(20) |
+---------+
| 140     |
+---------+
1 row in set

不累加值相同的行

因为sum里的表达式是常量34,所以每行的值都相同使用 distinct强制不计算重复值,所以下面计算结果为34

1 2	select sum(distinct 34) from student_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select sum(distinct 34)
from student_table;
+------------------+
| sum(distinct 34) |
+------------------+
| 34               |
+------------------+
1 row in set

max函数

选出student_table表中student_id最大的值

1 2	select max(student_id) from student_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select max(student_id)
from student_table;
+-----------------+
| max(student_id) |
+-----------------+
|               7 |
+-----------------+
1 row in set

min函数

选出teacher_table表中teacher_id最小的值

1 2	select min(teacher_id) from teacher_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select min(teacher_id)
from teacher_table;
+-----------------+
| min(teacher_id) |
+-----------------+
|               1 |
+-----------------+
1 row in set

count方法

使用count统计记录行数时,null不会被计算在内

1 2	select count(student_name) from student_table;

统计结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select count(student_name)
from student_table;
+---------------------+
| count(student_name) |
+---------------------+
|                   6 |
+---------------------+
1 row in set

avg方法

对于可能出现null的列,可以使用ifnull函数来处理该列。
计算java_teacher列所有记录的平均值

1 2	select avg(ifnull(java_teacher,0)) from student_table;

计算结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select avg(ifnull(java_teacher,0))
from student_table;
+-----------------------------+
| avg(ifnull(java_teacher,0)) |
+-----------------------------+
| 1.7143                      |
+-----------------------------+
1 row in set

值得指出的是,distinct和*不能同时使用,如下SQL语句有错误。

1 2	select count(distinct *) from student_table;

错误:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select count(distinct *)
from student_table;
1064 - 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 '*)
from student_table' at line 1

分组的情况

在默认情况下,组函数会把所有记录当成一组,为了对记录进行显式分组,可以在select语句后使用group by子句,group by子句后通常跟一个或多个列名,表明查询结果根据一列或多列进行分组——当一列或多列组合的值完全相同时,系统会把这些记录当成一组。如下SQL语句所示:

根据某一列进行分组

count(*)将会对每组得到一个结果:

1
2
3

select java_teacher,count(*)
from student_table
group by java_teacher;

查询结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select java_teacher,count(*)
from student_table
group by java_teacher;
+--------------+----------+
| java_teacher | count(*) |
+--------------+----------+
|            1 |        3 |
|            2 |        3 |
|            3 |        1 |
+--------------+----------+
3 rows in set

根据多列分组

如果对多列进行分组,则要求多列的值完全相同才会被当成一组。如下SQL语句所示:

select student_name,java_teacher,count(*)
from student_table
# 要求java_teacher,student_name这两列的值都相同的记录作为一组
group by java_teacher,student_name;

查询结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select student_name,java_teacher,count(*)
from student_table
# 要求java_teacher,student_name这两列的值都相同的记录作为一组
group by java_teacher,student_name;
+--------------+--------------+----------+
| student_name | java_teacher | count(*) |
+--------------+--------------+----------+
| 张三         |            1 |        2 |
| 李四         |            1 |        1 |
| NULL         |            2 |        1 |
| _王五        |            2 |        1 |
| 王五         |            2 |        1 |
| 赵六         |            3 |        1 |
+--------------+--------------+----------+
6 rows in set

处理不在group by以及组函数中的列

对于很多数据库而言,分组计算时有严格的规则——如果查询列表中使用了组函数,或者select语句中使用了group by分组子句,则要求出现在select列表中的字段,要么使用组函数包起来,要么必须出现在group by子句中。这条规则很容易理解,因为一旦使用了组函数或使用了group by子句,都将导致多条记录只有一条输出,系统无法确定输出多条记录中的哪一条记录。
对于MySQL来说,并没有上面的规则要求,如果某个数据列既没有出现在group by之后,也没有使用组函数包起来,则MySQL会输出该列的第一条记录的值。图13.15显示了MySQL的处理结果。
这里有一张图片

# student_name这列没有出现在group by子句中,
# 也没有出现在分组函数count之中,
# MySQL将显示遇到的第一条记录时student_name的值。
select student_name,java_teacher,count(java_teacher)
from student_table
group by java_teacher;

运行结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> # student_name这列没有出现在group by子句中,
# 也没有出现在分组函数count之中,
# MySQL将显示遇到的第一条记录时student_name的值。
select student_name,java_teacher,count(java_teacher)
from student_table
group by java_teacher;
+--------------+--------------+---------------------+
| student_name | java_teacher | count(java_teacher) |
+--------------+--------------+---------------------+
| 张三         |            1 |                   3 |
| 王五         |            2 |                   3 |
| 赵六         |            3 |                   1 |
+--------------+--------------+---------------------+
3 rows in set

having子句

如果需要对分组进行过滤,则应该使用having子句,having子句后面也是一个条件表达式,只有满足该条件表达式的分组才会被选出来。having子句和where子句非常容易混淆,它们都有过滤功能,但它们有如下区别。

不能在where子句中过滤组,where子句仅用于过滤行。过滤组必须使用having子句。
不能在where子句中使用组函数,having子句才可使用组函数。

如下SQL语句所示:

select java_teacher,count(*)
from student_table
group by java_teacher
having count(*)>2;

查询结果:

mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select java_teacher,count(*)
from student_table
group by java_teacher
having count(*)>2;
+--------------+----------+
| java_teacher | count(*) |
+--------------+----------+
|            1 |        3 |
|            2 |        3 |
+--------------+----------+
2 rows in set

13.2.10 数据库函数

发表于 2019-05-26 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 7.7k 阅读时长 ≈ 7 分钟

13.2.10数据库函数

正如前面看到的连接字符串使用的concat函数,每个数据库都会在标准的SQL基础上扩展一些函数,这些函数用于进行数据处理或复杂计算,它们通过对一组数据进行计算,得到最终需要的输出结果。函数一般都会有一个或者多个输入,这些输入被称为函数的参数,函数内部会对这些参数进行判断和计算,最终只有一个值作为返回值。函数可以出现在SQL语句的各个位置,比较常用的位置是select之后和where子句中。
根据函数对多行数据的处理方式,函数被分为单行函数和多行函数,单行函数对每行输入值单独计算,每行得到一个计算结果返回给用户;多行函数对多行输入值整体计算,最后只会得到一个结果。单行函数和多行函数的示意图如图13.13所示。
这里有一张图片
SQL中的函数和Java语言中的方法有点相似,但SQL中的函数是独立的程序单元,也就是说,调用函数时无须使用任何类、对象作为调用者,而是直接执行函数。执行函数的语法如下:

1	function_name(arg1,arg2,...)

多行函数也称为聚集函数、分组函数,主要用于完成一些统计功能,在大部分数据库中基本相同。但不同数据库中的单行函数差别非常大,

单行函数

MySQL中的单行函数具有如下特征。

单行函数的参数可以是变量、常量或数据列。单行函数可以接收多个参数,但只返回一个值。
单行函数会对每行单独起作用,每行(可能包含多个参数)返回一个结果。
使用单行函数可以改变参数的数据类型。单行函数支持嵌套使用,即内层函数的返回值是外层函数的参数。

MySQL的单行函数分类如图13.14所示。
这里有一张图片

单行函数分类

MySQL数据库的数据类型大致分为数值型、字符型和日期时间型,所以MySQL分别提供了对应的函数。转换函数主要负责完成类型转换,其他函数又大致分为如下几类。

位函数
流程控制函数
加密解密函数
信息函数

每个数据库都包含了大量的单行函数,这些函数的用法也存在一些差异,但有一点是相同的——每个数据库都会为一些常用的计算功能提供相应的函数,这些函数的函数名可能不同,用法可能有差异,但所有数据库提供的函数库所能完成的功能大致相似,读者可以参考各数据库系统的参考文档来学习这些函数的用法。下面通过一些例子来介绍MySQL单行函数的用法。

char_length函数

选出teacher_table表中teacher_name列的字符长度

1 2	select char_length(teacher_name) from teacher_table;

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select char_length(teacher_name)
from teacher_table;
+---------------------------+
| char_length(teacher_name) |
+---------------------------+
|                         5 |
|                         7 |
|                         7 |
+---------------------------+
3 rows in set

sin函数

计算teacher_name列的字符长度的sin值

# 长度,sin(长度)
select char_length(teacher_name) 长度,
sin(char_length(teacher_name)) sin
from teacher_table;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> # 长度,sin(长度)
select char_length(teacher_name) 长度,
sin(char_length(teacher_name)) sin
from teacher_table;
+------+---------------------+
| 长度 | sin                 |
+------+---------------------+
|    5 | -0.9589242746631385 |
|    7 |  0.6569865987187891 |
|    7 |  0.6569865987187891 |
+------+---------------------+
3 rows in set

计算1.57的sin值,约等于1

1	select sin(1.57);

查询结果:

mysql> select sin(1.57);
+--------------------+
| sin(1.57)          |
+--------------------+
| 0.9999996829318346 |
+--------------------+
1 row in set

在这种用法下interval是关键字,需要一个数值,还需要一个单位

1	SELECT DATE_ADD('1998-01-02', interval 2 MONTH);

运行结果:

mysql> SELECT DATE_ADD('1998-01-02', interval 2 MONTH);
+------------------------------------------+
| DATE_ADD('1998-01-02', interval 2 MONTH) |
+------------------------------------------+
| 1998-03-02                               |
+------------------------------------------+
1 row in set

获取当前日期

1	select CURDATE();

运行结果:

mysql> select CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-05-26 |
+------------+
1 row in set

获取当前时间

1	select curtime();

运行结果:

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:02:32  |
+-----------+
1 row in set

MD5加密函数

1	select MD5('testing');

运行结果:

mysql> select MD5('testing');
+----------------------------------+
| MD5('testing')                   |
+----------------------------------+
| ae2b1fca515949e5d54fb22b8ed95575 |
+----------------------------------+
1 row in set

处理null的函数

MySQL提供了如下几个处理nul的函数。

ifnull(表达式1,表达式2):如果表达式1为null,则返回表达式2,否则返回表达式1。
nullif(表达式1,表达式2):如果表达式1和表达式2相等,则返回null,否则返回表达式1。
if(表达式1,表达式2,表达式3):有点类似于?:三目运算符,如果表达式1为true,不等于不等于0,且不等于null,则返回表达式2,否则返回表达式3。
isnull(表达式1):判断表达式1是否为null,如果为null则返回true,否则返回false。

ifnull函数

如果 student_name列为null,则返回’没有名字

1 2	select ifnull(student_name,'没有名字') from student_table;

查询结果:

mysql> select * 
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select ifnull(student_name,'没有名字')
from student_table;
+---------------------------------+
| ifnull(student_name,'没有名字') |
+---------------------------------+
| 张三                            |
| 张三                            |
| 李四                            |
| 王五                            |
| _王五                           |
| 没有名字                        |
| 赵六                            |
+---------------------------------+
7 rows in set

nullif函数

如果student_name列等于’张三’,则返回null

1 2	select nullif(student_name,'张三') from student_table;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select nullif(student_name,'张三')
from student_table;
+-----------------------------+
| nullif(student_name,'张三') |
+-----------------------------+
| NULL                        |
| NULL                        |
| 李四                        |
| 王五                        |
| _王五                       |
| NULL                        |
| 赵六                        |
+-----------------------------+
7 rows in set

if函数

如果student_name列为null,则返回’没有名字’,否则返回有名字

1 2	select if(isnull(student_name),'没有名字','有名字') from student_table;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select if(isnull(student_name),'没有名字','有名字')
from student_table;
+----------------------------------------------+
| if(isnull(student_name),'没有名字','有名字') |
+----------------------------------------------+
| 有名字                                       |
| 有名字                                       |
| 有名字                                       |
| 有名字                                       |
| 有名字                                       |
| 没有名字                                     |
| 有名字                                       |
+----------------------------------------------+
7 rows in set

case函数

MySQL还提供了一个case函数,该函数是一个流程控制函数。case函数有两个用法,case函数第一个用法的语法格式如下:

第一种语法

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

case函数用value和后面的compare_value1、compare_value2、…依次进行比较,如果value和指定的compare_value1相等,则返回对应的result1,否则返回else后的result。例如如下SQL语句:

select student_name,case java_teacher
when 1 then 'Java老师'
when 2 then 'C老师'
else '其他老师'
end 老师类型
from student_table;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select student_name,case java_teacher
when 1 then 'Java老师'
when 2 then 'C老师'
else '其他老师'
end 老师类型
from student_table;
+--------------+----------+
| student_name | 老师类型 |
+--------------+----------+
| 张三         | Java老师 |
| 张三         | Java老师 |
| 李四         | Java老师 |
| 王五         | C老师    |
| _王五        | C老师    |
| NULL         | C老师    |
| 赵六         | 其他老师 |
+--------------+----------+
7 rows in set

case函数第二个用法的语法格式如下:

case
when condition1 then result1
when condition2 then result2
...
else result
end

第二种用法

在第二个用法中,condition1、condition2都是一个返回boolean值的条件表达式,因此这种用法更加灵活。例如如下SQL语句:

#id小于3的为初级班,3~6的为中级班,其他的为高级班
select student_name,case
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else '高级班'
end 班级类型
from student_table;

查询效果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> #id小于3的为初级班,3~6的为中级班,其他的为高级班
select student_name,case
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else '高级班'
end 班级类型
from student_table;
+--------------+----------+
| student_name | 班级类型 |
+--------------+----------+
| 张三         | 初级班   |
| 张三         | 初级班   |
| 李四         | 初级班   |
| 王五         | 中级班   |
| _王五        | 中级班   |
| NULL         | 中级班   |
| 赵六         | 高级班   |
+--------------+----------+
7 rows in set

小结

虽然此处介绍了一些MySQL常用函数的简单用法,但通常不推荐在Java程序中使用特定数据库的函数,因为这将导致程序代码与特定数据库耦合;如果需要把该程序移植到其他数据库系统上时,可能需要打开源程序,重新修改SQL语句。

13.2.9 单表查询2 where子句 order by子句

发表于 2019-05-25 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 11k 阅读时长 ≈ 10 分钟

where子句

前面已经看到了**where子句的作用——可以控制只选择指定的行。因为where子句里包含的是一个条件表达式，所以可以使用>、>=、<、<=、=和<>等基本的比较运算符。SQL中的比较运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小。
注意
SQL中判断两个值是否相等的比较运算符是单等号**,判断不相等的运算符是<>;SQL中的赋值运算符不是等号,而是冒号等号(:=)。
除此之外，SQL还支持如表13.3所示的特殊的比较运算符。

表13.3特殊的比较运算符

运算符	含义
`表达式1 between表达式2 and 表达式3`	要求`表达式1>=表达式2`,并且`表达式1<=表达式3`
`表达式1 in(表达式2,表达式2,表达式3,表达式4,...)`	要求`表达式1`等于后面括号里任意一个表达式的值就行,括号中的表达式可以有多个,但只要与其中一个相等即可
`like`	字符串匹配,`like`后面的字符串支持通配符
`is null`	要求制定值等于`null`

between运算符

下面的SQL语句选出student_id大于等于2，且student_id小于等于4的所有记录。

1 2	select * from student_table where student_id between 2 and 4;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_id between 2 and 4;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
+------------+--------------+--------------+
3 rows in set

使用between value1 and value2必须保证value1小于value2，否则将选不出任何记录。除此之外，between value1 and value2中的两个值不仅可以是常量，也可以是变量，或者是列名也行。
如下SQL语句选出java_teacher小于等于2，student_id大于等于2的所有记录。

select * from student_table
# 要求java_teacher<=2,并且2<=student_id
# 也就是java_teacher<=2,并且student_id>=2
where 2 between java_teacher and student_id;

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 要求java_teacher<=2,并且2<=student_id
# 也就是java_teacher<=2,并且student_id>=2
where 2 between java_teacher and student_id;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
+------------+--------------+--------------+
5 rows in set
mysql>

in运算符

使用in比较运算符时，必须在in后的括号里列出一个或多个值，它要求指定列必须与in括号里任意一个值相等。如下SQL语句所示：

1
2
3

select * from student_table
# 要求student_id等于2或者student_id等于4即可
where student_id in(2,4);

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 要求student_id等于2或者student_id等于4即可
where student_id in(2,4);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          4 | 王五         |            2 |
+------------+--------------+--------------+
2 rows in set

与之类似的是，in括号里的值既可以是常量，也可以是变量或者列名，如下SQL语句所示：

select * from student_table
# 要求2等于student_id或者2等于java_teacher
# 也就是student_id等于2或者java_teacher等于2
where 2 in(student_id,java_teacher);

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where 2 in(student_id,java_teacher);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
+------------+--------------+--------------+
4 rows in set

like运算符

like运算符主要用于进行模糊查询，例如，若要查询名字以“张”开头的所有记录，这就需要用到模糊查询，在模糊查询中需要使用like关键字。

通配符

SQL语句中可以使用两个通配符：下画线（_）和百分号（%），其中**下画线可以代表一个任意的字符，百分号可以代表任意多个字符**。如下SQL语句将查询出所有学生中名字以“张”开头的学生。

查询性张的学生

1 2	select * from student_table where student_name like '张%';

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '张%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
+------------+--------------+--------------+
2 rows in set

两个名字的学生

下面的SQL语句将查询出名字为两个字符的所有学生。

1 2	select * from student_table where student_name like '__';

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '__';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
5 rows in set

转义字符

反斜线转义

在某些特殊的情况下,查询的条件里需要使用下画线或百分号,不希望SQL把下画线和百分号当成通配符使用,这就需要使用转义字符,MySQL使用反斜线(\)作为转义字符,如下SQL语句所示:

1
2
3

#选出所有名字以下划线开头的学生.
select * from student_table
where student_name like '\_%';

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '\_%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          5 | _王五        |            2 |
+------------+--------------+--------------+
1 row in set

使用escape关键字自定义转义字符

标准SQL语句并没有提供反斜线(\)的转义字符,而是使用escape关键字显式进行转义。例如,为了实现上面功能需要使用如下SQL语句:

#选出所有名字以下划线开头的学生
select * from student_table
# 自定义`/`作为转义字符
where student_name like '/_%' escape '/';

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 自定义`/`作为转义字符
where student_name like '/_%' escape '/';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          5 | _王五        |            2 |
+------------+--------------+--------------+

is null

is null用于判断某些值是否为空,判断是否为空不要用=null来判断,因为SQL中null=null返回null。如下SQL语句将选择出student_table表中student_name为null的所有记录。

1 2	select * from student_table where student_name is null;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name is null;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          6 | NULL         |            2 |
+------------+--------------+--------------+
1 row in set

and or not运算符

如果where子句后有多个条件需要组合,SQL提供了and和or逻辑运算符来组合两个条件,并提供了not来对逻辑表达式求否。如下SQL语句将选出学生名字为2个字符,且student_id大于3的所有记录。

1 2	select * from student_table where student_name like '__' and student_id >3;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '__' and student_id >3;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
2 rows in set

下面的SQL语句将选出student_table表中姓名不以下画线开头的所有记录。

1 2	select * from student_table where not student_name like '\_%';

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where not student_name like '\_%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
5 rows in set

比较运算符逻辑运算符的优先级

当使用比较运算符、逻辑运算符来连接表达式时,必须注意这些运算符的优先级。SQL中比较运算符、逻辑运算符的优先级如表13.4所示。

表13.4 `SQL`中比较运算符、逻辑运算符的优先级

运算符	优先级(越小越优先)
所有的比较运算符	1
`not`	2
`and`	3
`or`	4

如果SQL代码需要改变优先级的默认顺序,则可以使用括号,括号的优先级比所有的运算符高。如下SQL语句使用括号来改变逻辑运算符的优先级。

select * from student_table
# 使用括号强制先计算or运算
where (student_id>3 or student_name>'张')
    and java_teacher>1;

order by子句

执行查询后的查询结果默认按插入顺序排列;如果需要查询结果按某列值的大小进行排序,则可以使用order by子句。order by子句的语法格式如下:

1	order by column_name1 [desc],column_name2...

升序排列

进行排序时默认按升序排列

降序排列

如果强制按降序排列,则需要在列后使用**desc关键字**(升序排列的是asc关键字,用不用该关键字的效果完全一样,因为默认是按升序排列)。
上面语法中**设定排序列时可采用列名、列序号和列别名**。如下SQL语句选出student_table表中的所有记录,选出后按java_teacher列的升序排列。

1 2	select * from student_table order by java_teacher;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
order by java_teacher;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

按多列排序

如果需要按多列排序,则每列的asc、desc必须单独设定。如果指定了多个排序列,则第一个排序列是首要排序列,只有当第一列中存在多个相同的值时,第二个排序列才会起作用。如下SQL语句先按java_teacher列的降序排列,当java_teacher列的值相同时按student_name列的升序排列。

1 2	select * from student_table order by java_teacher desc,student_name;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
order by java_teacher desc,student_name;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          7 | 赵六         |            3 |
|          6 | NULL         |            2 |
|          5 | _王五        |            2 |
|          4 | 王五         |            2 |
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
7 rows in set

13.2.9 单表查询1

发表于 2019-05-25 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 11k 阅读时长 ≈ 10 分钟

13.2.9单表查询

select语句的功能就是查询数据。select语句也是SQL语句中功能最丰富的语句，select语句不仅可以执行单表查询，而且可以执行多表连接查询，还可以进行子查询，select语句用于从一个或多个数据表中选出特定行、特定列的交集。select语句最简单的功能如图13.10所示。
这里有一张图片

测试数据

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50508
Source Host           : localhost:3306
Source Database       : select_test

Target Server Type    : MYSQL
Target Server Version : 50508
File Encoding         : 65001

Date: 2019-05-25 15:51:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `student_table`
-- ----------------------------
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table_ibfk_1` FOREIGN KEY (`java_teacher`)
  REFERENCES `teacher_table` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_table
-- ----------------------------
INSERT INTO `student_table` VALUES ('1', '张三', '1');
INSERT INTO `student_table` VALUES ('2', '张三', '1');
INSERT INTO `student_table` VALUES ('3', '李四', '1');
INSERT INTO `student_table` VALUES ('4', '王五', '2');
INSERT INTO `student_table` VALUES ('5', '_王五', '2');
INSERT INTO `student_table` VALUES ('6', null, '2');
INSERT INTO `student_table` VALUES ('7', '赵六', '3');

-- ----------------------------
-- Table structure for `teacher_table`
-- ----------------------------
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher_table
-- ----------------------------
INSERT INTO `teacher_table` VALUES ('1', 'Yeeku');
INSERT INTO `teacher_table` VALUES ('2', 'Leegang');
INSERT INTO `teacher_table` VALUES ('3', 'Martine');

语法格式

单表查询的select语句的语法格式如下：

1
2
3

select column1,column2,...
from 数据源
[where condition]

数据源可以是表、视图等。
select后的列表用于确定选择哪些列
where条件用于确定选择哪些行

选择所有行

只有满足where条件的记录才会被选择出来;如果没有where条件,则默认选出所有行。

选择所有列

如果想选择出所有列,则可使用星号(*)代表所有列。

选择特定行特定列

如果增加where条件，则只选择出符合where条件的记录。如下SQL语句将选择出student_table表中java_teacher值大于3的记录的student_name列的值。

1
2
3

select student_name 
from student_table 
where java_teacher>3;

运行结果:

mysql>
select student_name 
from student_table 
where java_teacher>2;
+--------------+
| student_name |
+--------------+
| 赵六         |
+--------------+
1 row in set

选择所有行所有列

下面的SQL语句将会选择出teacher_table表中的所有行、所有列的数据。

1	select * from teacher_table;

运行效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

使用算术运算符常亮变量

当使用select语句进行查询时,还可以在select语句中使用算术运算符(+、-、*、/),从而形成算术表达式。使用算术表达式的规则如下。

对**数值型数据列**、变量、常量可以使用算术运算符(+、-、*、/)创建表达式;
对**日期型数据列、变量、常量可以使用部分算术运算符(+、-)创建表达式,两个日期之间可以进行减法运算,日期和数值之间可以进行加、减运算**;
运算符不仅可以在列和常量、变量之间进行运算，也可以在两列之间进行运算。不论从哪个角度来看，数据列都很像一个变量，只是这个变量的值具有指定的范围——逐行计算表中的每条记录时，数据列的值依次变化。因此能使用变量的地方，基本上都可以使用数据列。
下面的select语句中使用了算术运算符。

实例

把数据列当成一个变量

1 2	select teacher_id+5 from teacher_table;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select teacher_id+5
from teacher_table;
+--------------+
| teacher_id+5 |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set

查询出teacher_table表中teacher_id*3>4的记录

1
2
3

select * from
teacher_table
where teacher_id*3>4;

运行结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select * from
teacher_table
where teacher_id*3>4;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
2 rows in set

select后写常亮变量

需要指出的是，select后的不仅可以是数据列，也可以是表达式，还可以是变量、常量等。例如，如下语句也是正确的。

1 2	select 3*5,20 from teacher_table;

运行结果:

mysql> select 3*5,20
from teacher_table;
+-----+----+
| 3*5 | 20 |
+-----+----+
|  15 | 20 |
|  15 | 20 |
|  15 | 20 |
+-----+----+
3 rows in set

运算符优先级

SQL语句中算术运算符的优先级与Java语言中的运算符优先级完全相同，乘法和除法的优先级高于加法和减法，同级运算的顺序是从左到右，表达式中使用括号可强行改变优先级的运算顺序。

字符串连接运算

MySQL中没有提供字符串连接运算符，即无法使用加号（+）将字符串常量、字符串变量或字符串列连接起来。MySQL使用concat函数来进行字符串连接运算。

1 2	select concat(teacher_name,'-教授') from teacher_table;

运行结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select concat(teacher_name,'-教授')
from teacher_table;
+------------------------------+
| concat(teacher_name,'-教授') |
+------------------------------+
| Yeeku-教授                   |
| Leegang-教授                 |
| Martine-教授                 |
+------------------------------+
3 rows in set

与null运算

对于MySQL而言，
**如果在算术表达式中使用null，将会导致整个算术表达式的返回值为null**；
**如果在字符串连接运算中出现null，将会导致连接后的结果也是null**。
如下SQL语句将会返回null。

1 2	select concat(teacher_name,null) from teacher_table;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select concat(teacher_name,null)
from teacher_table;
+---------------------------+
| concat(teacher_name,null) |
+---------------------------+
| NULL                      |
| NULL                      |
| NULL                      |
+---------------------------+
3 rows in set
mysql>

而对某些数据库而言，如果让字符串和null进行连接运算，它会把null当成空字符串处理。

取别名

给列取别名

如果不希望直接使用列名作为列标题，则可以为数据列或表达式起一个别名，为数据列或表达式起别名时，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开。如下SQL语句所示：

1 2	select teacher_id+5 as MY_ID from teacher_table

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 as MY_ID
from teacher_table;
+-------+
| MY_ID |
+-------+
|     6 |
|     7 |
|     8 |
+-------+
3 rows in set
mysql> select teacher_id+5
from teacher_table;
+--------------+
| teacher_id+5 |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set
mysql>

别名中有特殊字符的情况: 添加双引号

为列起别名，可以改变列的标题头，用于表示计算结果的具体含义。如果列别名中使用特殊字符（例如空格），或者需要强制大小写敏感，都可以通过为别名添加双引号来实现。如下SQL语句所示：

1 2	select teacher_id+5 "MY`'#@! &%ID" from teacher_table;

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 "MY`'#@! &%ID"
from teacher_table;
+--------------+
| MY`'#@! &%ID |
+--------------+
|            6 |
|            7 |
|            8 |
+--------------+
3 rows in set
mysql>

多列取别名

如果需要选择多列，并为多列起别名，则列与列之间以逗号隔开，但列和列别名之间以空格隔开。如下SQL语句所示：

1 2	select teacher_id+5 MY_ID,teacher_name 老师名 from teacher_table;

查询效果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_ID,teacher_name 老师名
from teacher_table;
+-------+---------+
| MY_ID | 老师名  |
+-------+---------+
|     6 | Yeeku   |
|     7 | Leegang |
|     8 | Martine |
+-------+---------+
3 rows in set

给表取别名

不仅可以为列或表达式起别名，也可以为表起别名，为表起别名的语法和为列或表达式起别名的语法完全一样，如下SQL语句所示：

1
2
3

select teacher_id+5 MY_ID,teacher_name 老师名
#为teacher_table起别名t
from teacher_table t;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_ID,teacher_name 老师名
#为teacher_table起别名t
from teacher_table t;
+-------+---------+
| MY_ID | 老师名  |
+-------+---------+
|     6 | Yeeku   |
|     7 | Leegang |
|     8 | Martine |
+-------+---------+
3 rows in set

多列运算

前面已经提到，列名可以当成变量处理，所以运算符也可以在多列之间进行运算，如下SQL语句所示：

select teacher_id+5 MY_TD,
concat(teacher_name,teacher_id) teacher_name_id
from teacher_table
where teacher_id*2>3;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select teacher_id+5 MY_TD,
concat(teacher_name,teacher_id) teacher_name_id
from teacher_table
where teacher_id*2>3;
+-------+-----------------+
| MY_TD | teacher_name_id |
+-------+-----------------+
|     7 | Leegang2        |
|     8 | Martine3        |
+-------+-----------------+
2 rows in set

不出现列名

甚至可以在select、where子句中都不出现列名，如下SQL语句所示：

1
2
3

select 5+4
from teacher_table
where 2<9;

查询结果:

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select 5+4
from teacher_table
where 2<9;
+-----+
| 5+4 |
+-----+
|   9 |
|   9 |
|   9 |
+-----+
3 rows in set

这种情况比较特殊：where语句后的条件表达式总是true，所以会把teacher_table表中的每条记录都选择出来——但SQL语句没有选择任何列，仅仅选择了一个常量，所以SQL会把该常量当成一列，teacher_table表中有多少条记录，该常量就出现多少次。

选择常量

对于选择常量的情形，指定数据表可能没有太大的意义，所以MySQL提供了一种扩展语法，允许select语句后没有from子句，即可写成如下形式：

1	select 5+4;

查询结果:

mysql> select 5+4;
+-----+
| 5+4 |
+-----+
|   9 |
+-----+
1 row in set

上面这种语句并不是标准SQL语句。例如，Oracle就提供了一个名为dual的虚表（最新的MySQL数据库也支持dual虚表），它没有任何意义，仅仅相当于from后的占位符。如果选择常量，则可使用如下语句：

1	select 5+4 from dual;

结果:

mysql> select 5+4 from dual;
+-----+
| 5+4 |
+-----+
|   9 |
+-----+
1 row in set

清除重复行

select默认会把所有符合条件的记录全部选出来，即使两行记录完全一样。如果想去除重复行，则可以使用distinct关键字从查询结果中清除重复行。比较下面两条SQL语句的执行结果：

#包括重复行
select student_name,java_teacher
from student_table;
#去除重复行
select distinct student_name,java_teacher
from student_table;

查询结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select student_name,java_teacher
from student_table;
+--------------+--------------+
| student_name | java_teacher |
+--------------+--------------+
| 张三         |            1 |
| 张三         |            1 |
| 李四         |            1 |
| 王五         |            2 |
| _王五        |            2 |
| NULL         |            2 |
| 赵六         |            3 |
+--------------+--------------+
7 rows in set
mysql> #去除重复行
select distinct student_name,java_teacher
from student_table;
+--------------+--------------+
| student_name | java_teacher |
+--------------+--------------+
| 张三         |            1 |
| 李四         |            1 |
| 王五         |            2 |
| _王五        |            2 |
| NULL         |            2 |
| 赵六         |            3 |
+--------------+--------------+
6 rows in set

注意
使用distinct去除重复行时,distinct紧跟在select关键字后面。它的作用是去除后面字段组合的重复值,而不管对应记录在数据库里是否重复。例如,(1,ˈaˈ,ˈbˈ)和(2,ˈaˈ,ˈbˈ)两条记录在数据库里是不重复的,但如果仅选择后面两列,则distinct会认为两条记录重复。

13.2.8 DML语句语法 3.delete from语句

发表于 2019-05-24 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 8.2k 阅读时长 ≈ 7 分钟

13.2.8 DML语句语法 3.delete from语句

delete from语句用于删除指定数据表的记录。使用delete from语句删除时不需要指定列名，因为总是整行地删除。
使用delete from语句可以一次删除多行，删除哪些行采用where子句限定，只删除满足where条件的记录。
当没有where子句限定时将会把表里的全部记录删除。

语法格式

delete from语句的语法格式如下：

1 2	delete form table_name [where condition];

部分删除

可以使用where条件来限定只删除指定记录，如下SQL语句所示：

1 2	delete from student_table2 where student_id<10;

运行结果:

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
|         13 | 可以插入     | NULL         |
|         15 | 可以插入     |            1 |
+------------+--------------+--------------+
6 rows in set

mysql> delete from student_table2
where student_id<10;
Query OK, 2 rows affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
|         13 | 可以插入     | NULL         |
|         15 | 可以插入     |            1 |
+------------+--------------+--------------+
4 rows in set
mysql>

全部删除

如下SQL语句将会把student_table2表中的记录全部删除：

1	delete from student_table2;

运行结果:

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
|         13 | 可以插入     | NULL         |
|         15 | 可以插入     |            1 |
+------------+--------------+--------------+
4 rows in set

mysql> delete from student_table2;
Query OK, 4 rows affected

mysql> select * from student_table2;
Empty set

mysql>

存在外键约束的情况

当主表记录被从表记录参照时，主表记录不能被删除，只有先将从表中参照主表记录的所有记录全部删除后，才可删除主表记录。

删除主表记录时级联删除从表对应记录

定义外键约束时定义了主表记录和从表记录之间的级联删除on delete cascade,这样删除主要记录时,从表的相关记录也被删除。

删除主表记录将从表对应记录设为null

on delete set null用于指定当主表记录被删除时，从表中参照该记录的从表记录把外键列的值设为null。

修改表定义以支持级联删除

表的定义如下:

show create table student_table2;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table2_ibfk_1`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
            ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

这个表目前还不支持级联删除

其中KEY java_teacher (java_teacher),表示索引.

删除原有的外键

1 2	alter table student_table2 drop foreign key student_table2_ibfk_1;

结果如下:

mysql> alter table student_table2
drop foreign key student_table2_ibfk_1;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table student_table2;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                            |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到外键定义已经删除掉了,KEY这项还没删除掉,我猜它是索引(因为可以用删除索引的语法删除)

重新添加外键使用默认索引

alter table student_table2
add foreign key(java_teacher)
        references teacher_table2(teacher_id)
        on delete cascade ;#也可以用on delete set null

运行结果:

mysql> alter table student_table2
add foreign key(java_teacher)
        references teacher_table2(teacher_id)
        on delete cascade ;#也可以用on delete set null
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
    -> show create table student_table2;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table2_ibfk_1`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
            ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql>

好的现在级联删除设置完毕

疑问

修改外键约束时,一定要先删除原来的外键约束,然后在添加新的外键约束来实现吗

测试级联删除

mysql> select * from student_table2;
Empty set

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | 孙悟空       |
|          2 | 美猴王       |
+------------+--------------+
2 rows in set

mysql> insert into student_table2
 values(null,'小明',1);
Query OK, 1 row affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|         16 | 小明         |            1 |
+------------+--------------+--------------+
1 row in set
mysql>

由于表student_table2的java_teacher属性参照teacher_table2的teacher_id属性.
当删除表teacher_table2中teacher_id=1的记录时,将会级联删除student_table2表中java_teacher=1的记录。
效果如下:

mysql> delete from teacher_table2
where teacher_id=1;
Query OK, 1 row affected

mysql> select * from student_table2;
Empty set

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          2 | 美猴王       |
+------------+--------------+
1 row in set
mysql>

13.2.8 DML语句语法 2.update语句

发表于 2019-05-24 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 1.6k 阅读时长 ≈ 1 分钟

13.2.8 DML语句语法 2.update语句

update语句用于修改数据表的记录，每次可以修改多条记录，通过使用where子句限定修改哪些记录。where子句是一个条件表达式，该条件表达式类似于Java语言的if，只有符合该条件的记录才会被修改。没有where子句则意味着where表达式的值总是true，即该表的所有记录都会被修改。

语法格式

update语句的语法格式如下：

1
2
3

update table_name
set column1=value [,column2=value2,...]
[where condition];

使用update语句不仅可以一次修改多条记录，也可以一次修改多列。修改多列都是通过在set关键字后使用column1=value1,column2=value2…来实现的,修改多列的值之间以英文逗号(,)隔开。

更新列中的所有内容

下面的SQL语句将会把teacher_table2表中所有记录的teacher_name列的值都改为'孙悟空'。

1 2	update teacher_table2 set teacher_name='孙悟空';

运行效果:

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
+------------+--------------+
2 rows in set

mysql> update teacher_table2
set teacher_name='孙悟空';
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | 孙悟空       |
|          2 | 孙悟空       |
+------------+--------------+
2 rows in set

更新指定记录

也可以通过添加where条件来指定只修改特定记录，如下SQL语句所示:

1
2
3

update teacher_table2
set teacher_name='美猴王'
where teacher_id>=2;

运行效果:

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | 孙悟空       |
|          2 | 孙悟空       |
+------------+--------------+
2 rows in set

mysql> update teacher_table2
set teacher_name='美猴王'
where teacher_id>=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | 孙悟空       |
|          2 | 美猴王       |
+------------+--------------+
2 rows in set

13.2.8 DML语句语法 1.insert into语句

发表于 2019-05-24 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 9.4k 阅读时长 ≈ 9 分钟

13.2.8 DML语句语法

与DDL操作数据库对象不同，DML主要操作数据表里的数据，使用DML可以完成如下三个任务。

插入新数据(insert into)
修改已有数据(update)
删除不需要的数据(delete from)

1.insert into语句

insert into用于向指定数据表中插入记录。对于标准的SQL语句而言，每次只能插入一条记录。insert into语句的语法格式如下：

1 2	insert into 表名 [(属性1名[,属性2名,...])] values (属性1值 [,属性2值,...])

执行插入操作时，表名后可以用括号列出所有需要插入值的列名，而values后用括号列出对应需要插入的值。

如果省略了表名后面的括号及括号里的列名列表，默认将为所有列都插入值，则需要为每一列都指定一个值。如果既不想在表名后列出列名，又不想为所有列都指定值，则可以为那些无法确定值的列分配null。下面的SQL语句示范了如何向数据表中插入记录。

注意

要先创建表然后才可以向数据表中插入记录。下面的SQL语句以前面介绍外键约束时所创建的teacher_table2和student_table2为例来介绍数据插入操作。

CREATE TABLE `teacher_table2` (
`teacher_id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`teacher_id`)
)

插入指定列

在表名后使用括号列出所有需要插入值的列：

1 2	insert into teacher_table2(teacher_name) values('xyz');

这种方式关键在于可以插入部分列的值,其他列使用默认值.

插入所有列

如果不想在表后用括号列出所有列，则需要为所有列指定值；如果某列的值不能确定，则为该列分配一个null值。

1 2	insert into teacher_table2 values(null,'abx');

使用这种方式,必须给所有列赋值,也就是说下面的写法是错误的:

1 2	insert into teacher_table2 values('abx');

虽然第一列是MySQL自己生成的,虽然我们传入的null这个值没有用到,但是这个null相当于一个占位符,不过既然是占位符,那把null换成其他的也是可以的,例如这里不使用null占位,而是使用空字符串占位也是可以的,如下所示:

1	insert into teacher_table2 values('','haha');

插入效果:

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
|          4 | abx          |
+------------+--------------+
4 rows in set

mysql> insert into teacher_table2 values('','haha');
Query OK, 1 row affected

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
|          4 | abx          |
|          5 | haha         |
+------------+--------------+
5 rows in set
mysql>

实例

插入主表

经过两条插入语句后，可以看到teacher_table2表中的数据如下所示.

mysql> insert into teacher_table2(teacher_name)
values('xyz');
Query OK, 1 row affected

mysql> insert into teacher_table2
values(null,'abx');
Query OK, 1 row affected

mysql> select *from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
+------------+--------------+
2 rows in set
mysql>

可以看到abc记录的主键列的值是2，而不是SQL语句插入的null，因为该主键列是自增长的，系统会自动为该列分配值。

自动增长列不需要赋值

自动增长列不需要赋值,就算你赋值了,也是不起作用的,例如,下面再插入一条记录:

1 2	insert into teacher_table2 values('aaa','abx');

然后再次显示表:

mysql> select *from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

可以看到第三行的第一列的值是3,而不是我们填入的'aaa'这个字符串.因此没必要给自动增长列赋值。

向从表插入数据

从表:定义外键的表

CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
)

根据前面介绍的外键约束规则：外键列里的值必须是被参照列里已有的值，**所以向从表中插入记录之前，通常应该先向主表中插入记录，否则从表记录的外键列只能为null**。现在主表teacher_table2中已有了2条记录，现在可以向从表student_table2中插入记录了，如下SQL语句所示：

1 2	insert into student_table2 values(null,'张三',2);

需要注意的是第三列java_teacher的值,这个值必须在teacher_table2表中的teacher_id列中存在.如果不存在则插入错误,现在插入一个错误的数据如下:

1 2	insert into student_table2 values(null,'张三',7);

运行结果:

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

mysql> insert into student_table2
values(null,'张三',7);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test_engine`.`student_table2`, CONSTRAINT `student_teacher_fk` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table2` (`teacher_id`))
mysql>

设置外键不能插入null

外键约束保证被参照的记录必须存在，但并不保证必须有被参照记录，即外键列可以为null。如果想保证每条从表记录必须存在对应的主表记录，则应使用非空、外键两个约束。

带子查询的插入语句

在一些特别的情况下，可以使用带子查询的插入语句，带子查询的插入语句可以一次插入多条记录，如下SQL语句所示：

1 2	inser into student_table2(student_name) select teacher_name from teacher_table2;

运行结果如下所示:

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
+------------+--------------+--------------+
1 row in set
mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

mysql> insert into student_table2(student_name)
select teacher_name from teacher_table2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
+------------+--------------+--------------+
4 rows in set
mysql>

这里的自动增长列没有从2开始,是因为我有好几条insert语句插入失败了,但是自动增长列可不管你插入是否成功,它只知道你使用一次insert语句,它就加一

子查询插入要求

正如上面的SQL语句所示，带子查询的插入语句甚至不要求查询数据的源表和插入数据的目的表是同一个表，它只要求子查询选择出来的数据列和插入目的表的数据列个数相等、数据类型匹配即可。

插入失败自动增长列也会增长

虽然insert语句插入可能会失败,但是自动增长列依旧会增长。
可以通过显示表创建语句来查看自动增长列的值:

1	show create table student_table2;

显示效果:

mysql> show create table student_table2;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到此时自动增长列的值是12,这个值表示下次插入语句时MySQL填给自动增长列的值.
现在故意输错一个insert语句,然后再次查看自动增长列的值,如下所示:

mysql> insert into student_table2 values(null,'插不进',8);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test_engine`.`student_table2`, CONSTRAINT `student_teacher_fk` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table2` (`teacher_id`))
mysql> show create table student_table2;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到虽然插入失败了,但是自动增长列还是自动加一,此时下一个自动增长列的值默认为13,如下所示:

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
+------------+--------------+--------------+
4 rows in set

mysql> insert into student_table2 value(null,'可以插入',null);
Query OK, 1 row affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
|         13 | 可以插入     | NULL         |
+------------+--------------+--------------+
5 rows in set

13.2.7 视图

发表于 2019-05-24 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 915 阅读时长 ≈ 1 分钟

13.2.7 视图

视图看上去非常像一个数据表，但它不是数据表，因为它并不能存储数据。视图只是一个或多个数据表中数据的逻辑显示。

使用视图的好处

使用视图有如下几个好处。

可以限制对数据的访问。
可以使复杂的查询变得简单。
提供了数据的独立性。
提供了对相同数据的不同显示。

创建视图

因为视图只是数据表中数据的逻辑显示——也就是一个查询结果，所以创建视图就是建立视图名和查询语句的关联。创建视图的语法如下：

1
2
3

create of replace view 视图名
as
subquery

从上面的语法可以看出，创建、修改视图都可使用上面语法。上面语法的含义是，如果该视图不存在，则创建视图；如果指定视图名的视图已经存在，则使用新视图替换原有视图。后面的subquery就是一个查询语句，这个查询可以非常复杂。

视图的本质

通过建立视图的语法规则不难看出，所谓视图的本质，其实就是一条被命名的SQL查询语句。

通常只查询视图

一旦建立了视图以后，使用该视图与使用数据表就没有什么区别了，但通常只是查询视图数据，不会修改视图里的数据，因为视图本身没有存储数据。

实例

如下SQL语句就创建了一个简单的视图：

create or replace view view_test
as
select teacher_name,teacher_pass
from teacher_table

强制不允许修改视图的数据

通常不推荐直接改变视图的数据，因为视图并不存储数据，它只是相当于一条命名的查询语句而已。为了强制不允许改变视图的数据，MySQL允许在创建视图时使用with check option子句，使用该子句创建的视图不允许修改，如下所示：

create or replace view view_test
as
select teacher_name form teacher_table
with check option

数据库区别

大部分数据库都采用with check option来强制不允许修改视图的数据，但Oracle采用with read only来强制不允许修改视图的数据。

删除视图

删除视图使用如下语句：

1	drop view 视图名

如下SQL语句删除了前面刚刚创建的视图：

1	drop view view_test;

13.2.12 多表连接查询 2. SQL99的连接查询

交叉连接

自然连接

using子句

on子句连接

外连接

右外连接

左外连接

全外连接

13.2.12 多表连接查询

两种连接规范

SQL92

SQL99

1. SQL92的连接查询

语法格式

列同名的情况

伪代码

广义笛卡尔积

外连接

自连接

13.2.11 分组和组函数

实例

sum函数

统计某列的总和

每行都加上一个常数

不累加值相同的行

max函数

min函数

count方法

avg方法

分组的情况

根据某一列进行分组

根据多列分组

处理不在group by以及组函数中的列

having子句

13.2.10数据库函数

单行函数

单行函数分类

char_length函数

sin函数

获取当前日期

获取当前时间

MD5加密函数

处理null的函数

ifnull函数

nullif函数

if函数

case函数

第一种语法

第二种用法

小结

where子句

between运算符

in运算符

like运算符

通配符

查询性张的学生

两个名字的学生

转义字符

反斜线转义

使用escape关键字自定义转义字符

is null

and or not运算符

比较运算符 逻辑运算符的优先级

order by子句

升序排列

降序排列

按多列排序

13.2.9单表查询

测试数据

语法格式

选择所有行

选择所有列

选择特定行特定列

选择所有行所有列

使用算术运算符 常亮 变量

实例

select后写常亮变量

运算符优先级

字符串连接运算

比较运算符逻辑运算符的优先级

使用算术运算符常亮变量