一、MySQL多表查询和子查询
别名:as
表别名
字段别名
查询结果亦可命名别名
联结查询:事先将两张或多张表join,根据join的结果进行查询。
交叉联结(cross join):第一张表的每一行与第二张表的每一行做交叉相乘。
自然联结,也叫等值联结、内联结。两张表中的字段做等值关联。
自联结:
外联结:
左外联结:只保留出现在左外联结运算之前左侧的关系中的元组。以左表为尊,右侧没有,右侧留空。
右外联结:只保留出现在左外联结运算之前右侧的关系中的元组。以右表为尊,左侧没有,左侧留空。
mysql> select s.name,c.class from students as
s LEFT JOIN classes as c on s.classid=c.classid;
+---------------+----------------+
| name| class |
+---------------+----------------+
| Shi Zhongyu| Emei Pai |
| Shi Potian| Shaolin Pai |
| Xie Yanke| Emei Pai |
| Ding Dian| Wudang Pai |
| Yu Yutong| QingCheng Pai |
| Shi Qing| Riyue Shenjiao |
| Xi Ren| QingCheng Pai |
| Lin Daiyu| Ming Jiao |
| Ren Yingying| Lianshan Pai |
| Yue Lingshan| QingCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing| Shaolin Pai |
| Tian Boguang| Emei Pai |
| Lu Wushuang| QingCheng Pai |
| Duan Yu| Wudang Pai |
| Xu Zhu| Shaolin Pai |
| Lin Chong| Wudang Pai |
| Hua Rong| Ming Jiao |
| Xue Baochai| Lianshan Pai |
| Diao Chan| Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao| Shaolin Pai |
| Ma Chao| Wudang Pai |
| Xu Xian| NULL |
| Sun Dasheng| NULL |
+---------------+----------------+
25 rows in set (0.00 sec)
mysql> select s.name,c.class from students as s RIGHT JOIN classes as c on s.classid=c.classid;
+---------------+----------------+
| name | class |
+---------------+----------------+
| Shi Potian| Shaolin Pai |
| Wen Qingqing| Shaolin Pai |
| Xu Zhu| Shaolin Pai |
| Xiao Qiao| Shaolin Pai |
| Shi Zhongyu| Emei Pai |
| Xie Yanke| Emei Pai |
| Tian Boguang| Emei Pai |
| Yu Yutong| QingCheng Pai |
| Xi Ren| QingCheng Pai |
| Yue Lingshan| QingCheng Pai |
| Lu Wushuang| QingCheng Pai |
| Ding Dian| Wudang Pai |
| Duan Yu| Wudang Pai |
| Lin Chong| Wudang Pai |
| Ma Chao| Wudang Pai |
| Shi Qing| Riyue Shenjiao |
| Ren Yingying| Lianshan Pai |
| Yuan Chengzhi | Lianshan Pai |
| Xue Baochai| Lianshan Pai |
| Huang Yueying | Lianshan Pai |
| Lin Daiyu| Ming Jiao |
| Hua Rong| Ming Jiao |
| Diao Chan| Ming Jiao |
| NULL| Xiaoyao Pai |
+---------------+----------------+
24 rows in set (0.00 sec)
子查询:在查询中嵌套查询,mysql子查询性能优化一般,应尽量避免使用,可使用联结查询替代。
1、用于比较表达式(WHERE)中的子查询,其返回值只能唯一。
2、用于EXISTS中的子查询,判断存在与否
3、用于IN(IN(list))中的子查询,判断存在与指定列表中。
4、用于FROM中的子查询
select
Alias_name.col1,Alias_name.col2… from (selectclause ) As Alias_name Where condition;
MySQL视图(view):存储下来的select语句,用于限定查询结果,隐藏表中的某些信息。将限定的查询结果从原表中获取,客户的查询 被限制在限定的结果中。
mysql> create VIEW testview as select
name,age from students;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
|testview |
| toc |
+-------------------+
8 rows in set (0.01 sec)
查看视图中
mysql> select * from testview;
+---------------+-----+
| name| age |
+---------------+-----+
| Shi Zhongyu| 22 |
| Shi Potian| 22 |
| Xie Yanke| 53 |
| Ding Dian| 32 |
| Yu Yutong| 26 |
| Shi Qing| 46 |
| Xi Ren| 19 |
| Lin Daiyu| 17 |
| Ren Yingying| 20 |
| Yue Lingshan| 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing| 19 |
| Tian Boguang| 33 |
| Lu Wushuang| 17 |
| Duan Yu| 19 |
| Xu Zhu| 21 |
| Lin Chong| 25 |
| Hua Rong| 23 |
| Xue Baochai| 18 |
| Diao Chan| 19 |
| Huang Yueying | 22 |
| Xiao Qiao| 20 |
| Ma Chao| 23 |
| Xu Xian| 27 |
| Sun Dasheng| 100 |
+---------------+-----+
视图的状态与table的状态是有区别的,但其可以被当作表来使用,在插入数据或更新数据时,不应该基于view来实现。
mysql> show table status like
"testview"\G
*************************** 1. row
***************************
Name: testview
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
删除视图,使用drop
select name,age from students where age > (select avg(age) from students);
select students.name,courses.course from students,courses,scores where students.StuID = scores.StuID
and scores.CourseID = courses.CourseID and courses.courseid in (1,2,4,7);
select s.name,s.classid from (
select classid from students group by classid having count(ClassID)>=3) as
c,students as s where c.classid=s.classid and age>(select avg(age) from
students);
select s.name,s.classid,s.age
from students as s where classid is not null and s.age>(select avg(age) from
students) ;
原创文章,作者:M20-1钟明波,如若转载,请注明出处:http://www.178linux.com/59639