mysql嵌套分组查询_Mysql入门之十分钟带你玩转Mysql（增删改查、结果分组、多表查询、嵌套查询......）...

1.基本增删改查

增：插入数据

-- 向Student表中插入一条数据

INSERT INTO Student(xxx,xxx,...)VALUES(value1,value2,...);

删：删除数据

-- 删除Student表中id字段为1的记录

DELETE FROM Student WHERE id = 1;

改：更新数据

-- 更新Student 表中id=1的学生姓名为“张三”

UPDATE Student SET name = "张三" WHERE id = 1;

查：查询数据

-- 查询Student 表中所有记录

SELECT * FROM Student;

ps：在这里演示最简单的sql语句，后续详解

2.SQL的数据查询

首先构建三个表方便进行后续操作，经典的如下：

单表查询：只涉及一个表或一个视图的查询

– 查询指定列

SELECT Sno,Sname,Ssex FROM Student;

– 消除重复的行(DISTINCT)

SELECT (DISTINCT)Sno FROM Student;

– 条件查询(WHERE)

SELECT Sno,Sname,Ssex

FROM Student

WHERE Sno = '95001';

SELECT Sno,Sname,Ssex

FROM Student

WHERE Sage BETWEEN 20 AND 30;

– 模糊查询(LIKE)

SELECT Sno,Sname,Ssex

FROM Student

WHERE Sname LIKE '李_勇';

SELECT Sno,Sname,Ssex

FROM Student

WHERE Sname LIKE '李%';

– 结果排序(升序ASC、降序DESC)

SELECT Sno,Grade

FROM SC

WHERE Cno = '1' ORDER BY Grade ASC;

SELECT Sno,Grade

FROM SC

WHERE Cno = '1' ORDER BY Grade DESC;

– 聚集函数(AVG()、MIN()、MAX()、SUM()、COUNT())

SELECT COUNT(*) FROM Student;

– 查询结果分组(细化聚集函数的作用对象，GROUP BY)

-- 查询每门课程的课程号及相应的选课人数

SELECT Cno,COUNT(*)

FROM SC

GROUP BY Cno;

– HAVING短语(可以在条件中包含聚集函数)

-- 查询至少有三门课程成绩>90的学生的学号

SELECT Cno

FROM SC

WHERE Grade>=90 GROUP BY Sno HAVING COUNT(*) > 3;

连接查询(多表查询)

– 交叉连接

-- 交叉连接：笛卡尔积的方式查询，无意义，不使用

– 自然连接：把重复的列去掉就是自然连接

SELECT Student.Sno,Sname,Ssex ,Cno,Grade

FROM Student,SC

WHERE Student.Sno = SC.Sno;

– 内连接：能匹配条件则保留

SELECT S.*,SC.Grade AS sc_grade

FROM Student AS S JOIN SC AS SC

ON s.Sno = sc.Sno;

– 外连接：不能匹配条件则将副表值置为空

-- 左外连接：左表为主表，右表为副表

SELECT S.*,SC.Grade AS sc_grade

FROM Student AS S LEFT JOIN SC AS SC

ON s.Sno = sc.Sno;

-- 右外连接：左表为副表，右表为主表

SELECT S.*,SC.Grade AS sc_grade

FROM Student AS S RIGHT JOIN SC AS SC

ON s.Sno = sc.Sno;

ps:左表始终在左边

– 嵌套查询：将一个查询块嵌入到另一个查询快的WHERE字句或HAVING中

-- 不相关子查询

SELECT Sname

FROM Student

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Cno='1'

);

-- 相关的子查询：子查询的执行依赖于外层查询

-- 带有EXISTS谓词的子查询：效率高于IN

3.补充一点

索引的建立

CREATE INDEX Snoindex ON Student(Sno);

PS：MySql事务及锁待补充…