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
标签:常用,sno,查询,score,student,mysql,cno,SELECT
From: https://www.cnblogs.com/guixiangyyds/p/18342044