linux mysql进阶_mysql进阶之MySQL查询

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