数据库SQL高级语言（后续来了）

目录

1，连接查询

2，视图

3，联集

4，case

5，日期时间函数

6，空值和无值

7，regexp正则表达式

8，运算符

9，存储过程

10，常见的错误代码

11，死锁

---

1，连接查询

inner join on（内连接）只返回两个表中联结字段的相等的行
left join on(左连接): 返回包括左表中的所有记录和右表中联结字段相等的记录
right join on(右连接): 返回包括右表中的所有记录和左表中联结字段相等的记录

语法：
select 字段 from 表1 inner join 表2 on 条件
select 字段 from 表1 left join 表2 on 条件
select 字段 from 表1 right join 表2 on 条件

例；

环境两张表

(root@localhost) [hellodb]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

内连接：

(root@localhost) [hellodb]> select *from teachers inner join students on 
students.teacherid=teachers.tid;  ##显示 teacher表的所有字段，采用内连接要求teacgerid=tid
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name        | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|   4 | Lin Chaoying  |  93 | F      |     4 | Ding Dian   |  32 | M      |       4 |         4 |
|   1 | Song Jiang    |  45 | M      |     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

可以采用别名

(root@localhost) [hellodb]> select *from teachers t inner join studentts s
 on s.teacherid=t.tid; 
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name        | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|   4 | Lin Chaoying  |  93 | F      |     4 | Ding Dian   |  32 | M      |       4 |         4 |
|   1 | Song Jiang    |  45 | M      |     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

左连接

(root@localhost) [hellodb]> select *from teachers t left join studentss s 
on s.teacherid=t.tid; ##左连接显示teachers表所有字段和students表中符合要求的字段
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name        | Age  | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu |   22 | M      |       2 |         3 |
|   4 | Lin Chaoying  |  93 | F      |     4 | Ding Dian   |   32 | M      |       4 |         4 |
|   1 | Song Jiang    |  45 | M      |     5 | Yu Yutong   |   26 | M      |       3 |         1 |
|   2 | Zhang Sanfeng |  94 | M      |  NULL | NULL        | NULL | NULL   |    NULL |      NULL |
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
4 rows in set (0.00 sec)

右连接

(root@localhost) [hellodb]> select *from teachers t right join studentts s 
on s.teacherid=t.tid;  #显示teachers表中符合要求的字段和students表中所有字段
+------+---------------+------+--------+-------+---------------+-----+--------+---------+-----------+
| TID  | Name          | Age  | Gender | StuID | Name          | Age | Gender | ClassID | TeacherID |
+------+---------------+------+--------+-------+---------------+-----+--------+---------+-----------+
|    1 | Song Jiang    |   45 | M      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    3 | Miejue Shitai |   77 | F      |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|    4 | Lin Chaoying  |   93 | F      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
| NULL | NULL          | NULL | NULL   |     2 | Shi Potian    |  22 | M      |       1 |         7 |
| NULL | NULL          | NULL | NULL   |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
| NULL | NULL          | NULL | NULL   |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
| NU