mysql常用的查询

mysql常用的查询

建表末尾

必加上

ENGINE=InnoDB DEFAULT CHARSET=utf8

跨表一列比较，多列查询

SELECT sno,cno,rank from score JOIN grade on score.degree>low && score.degree<upp;

模糊查询，字符转化的筛选查询，分组统计查询

SELECT cno from score WHERE CAST(cno AS char) LIKE '3%' GROUP BY cno HAVING COUNT(*) >=5;

join单词跨表查询

SELECT sname,cno,degree from score JOIN student on score.sno = student.sno;
SELECT * FROM teacher JOIN course on course.tno=teacher.tno WHERE tname ='张旭';

join三表查询

select sname,degree,sname FROM score JOIN student on score.sno=student.sno JOIN course on score.cno=course.cno

双select单查询（第二种跨表查询）

SELECT degree FROM score where sno in (select sno from student where class='95033');

由此引申：

SELECT AVG(degree) AS average FROM score where sno in (select sno from student where class='95033');

含排除最大值查询

SELECT * FROM score WHERE degree NOT IN (SELECT MAX(degree) FROM score) AND sno in (SELECT sno from score GROUP BY sno HAVING COUNT(cno)>1);

同年同月year+month查询

SELECT sno,sname,sbirthday from student WHERE YEAR(sbirthday)=(SELECT year(sbirthday) FROM student WHERE sno='108') AND MONTH(sbirthday)=(SELECT month(sbirthday) FROM student WHERE sno='108')

join连续跨表查询

SELECT sname,degree from score JOIN student on score.sno=student.sno WHERE cno IN(SELECT cno FROM teacher JOIN course on course.tno=teacher.tno WHERE tname ='张旭');

select连续跨表查询

SELECT tname FROM teacher WHERE tno in (SELECT tno from course WHERE cno in (SELECT cno from score GROUP BY cno HAVING COUNT(sno)>5));

小技巧：

distinct去重

select distinct Cno from Score where degree > 85