来了)"/>
数据库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 son 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表中符合要求的字段
+-----+---------------+-----+--------
更多推荐
数据库SQL高级语言(后续来了)
发布评论