一、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