mysql实验分析_MySQL 查询优化器实验分析(一)

最新推荐文章于 2023-04-07 20:11:56 发布

weixin_39517400

最新推荐文章于 2023-04-07 20:11:56 发布

阅读量132

点赞数

文章标签： mysql实验分析

本文链接：https://blog.csdn.net/weixin_39517400/article/details/113459494

版权

本文详细介绍了SQL查询的基本用法，包括查询所有字段、特定字段、去重、主键过滤、普通字段筛选等。同时，深入探讨了多表联合查询的各种方式，如JOIN、LEFT JOIN、NATURAL JOIN、STRAIGHT_JOIN以及子查询的运用，展示了如何通过这些方法进行数据的聚合、过滤和排序。此外，还涉及了IN条件、LIMIT限制、GROUP BY与HAVING的联合使用以及UNION操作。

摘要由CSDN通过智能技术生成

单表操作

1)查询student中的所有字段

SELECT * FROM student;

2)查询student中的指定字段

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student;

3)查询student中没有重复的字段

SELECT DISTINCT std_spec, std_***, std_age FROM

student;

select std_spec, std_***, std_age FROM student

GROUP BY std_spec, std_***, std_age;

4)查询student中主键字段

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_id = 2012072306;

5)查询student中普通字段

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_name="fff";

注意：为std_name添加索引，查看查询计划。

6)查询student中多个普通字段(包含常量条件)

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_name="fff" AND std_age > 20 AND

1=1;

7)查询student中limit条件

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_spec = "computer" LIMIT 5;

8)查询student中in条件(索引和普通字段)

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_name in

("bbb","ccc","ddd");

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_spec in

("math","information");

9)查询student中group by、order by、having联合条件

SELECT std_spec, COUNT(std_spec) cnt FROM student

GROUP BY std_spec HAVING cnt > 3 ORDER BY cnt;

10)查询student中union条件

SELECT std_id, std_name, std_spec, std_***,

std_age FROM student WHERE std_id=2012072306 UNION SELECT std_id, std_name,

std_spec, std_***, std_age FROM student WHERE std_name="bbb" UNION

SELECT std_id, std_name, std_spec, std_***, std_age FROM student WHERE

std_spec="math";

复合查询

1)多表联合查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student,course,std_cur WHERE

student.std_id = std_cur.std_id AND course.cur_id = std_cur.cur_id AND

course.cur_id = 101;

2)Join查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student JOIN(course, std_cur) ON

(student.std_id=std_cur.std_id AND std_cur.cur_id=course.cur_id AND

course.cur_id = 101);

3)Join嵌套查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student JOIN std_cur ON student.std_id = std_cur.std_id

JOIN course ON std_cur.cur_id=course.cur_id WHERE course.cur_id = 101;

4)Join嵌套查询(普通字段)

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student JOIN (std_cur JOIN course ON

std_cur.cur_id=course.cur_id) ON student.std_id = std_cur.std_id WHERE

course.cur_name = 'PHP';

5)Left join嵌套(join)查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student LEFT JOIN(course JOIN std_cur ON

std_cur.cur_id=course.cur_id) ON (student.std_id=std_cur.std_id) WHERE

course.cur_name = 'C';

6)Natural join查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student NATURAL JOIN std_cur NATURAL JOIN course WHERE

course.cur_id = 101;

7)Straight_join查询

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours,score FROM student STRAIGHT_JOIN(course STRAIGHT_JOIN std_cur ON

std_cur.cur_id=course.cur_id) ON (student.std_id=std_cur.std_id) WHERE

course.cur_name = 'C';

SELECT student.std_id,std_name,std_spec,std_***,std_age,cur_name,cur_credit,cur_hours, score FROM student STRAIGHT_JOIN(std_cur

STRAIGHT_JOIN course ON std_cur.cur_id=course.cur_id) ON

(student.std_id=std_cur.std_id) WHERE course.cur_name = 'C';

8)子查询

SELECT student.std_id, std_name, student.std_spec,

std_***, std_age, SUM(cur_credit) total FROM student, course, std_cur,

(SELECT std_spec, AVG(score) savg FROM student LEFT JOIN std_cur ON

(student.std_id = std_cur.std_id) GROUP BY std_spec) tmp WHERE student.std_id

= std_cur.std_id AND course.cur_id = std_cur.cur_id AND student.std_spec = tmp.std_spec

AND score > tmp.savg GROUP BY std_id ORDER BY total;