Msql查询

mysql查询

修改表中的某一个学生的信息：(通过学号修改姓名)  update student set sname="姓名" where sno = '学号';

(1) 查询student表中学生的学号，姓名，性别；

SELECT sno,sname,sex FROM student;

(2) 选择student表的所有列；

SELECT * FROM student;

(3) 计算student表中的总行数，使用函数，总行数起别名；

SELECT COUNT(*) AS lineCount FROM student;

(4) 查找student表中学生的最大年龄，查询结果列起别名，使用函数；

SELECT MAX(age) AS maxAge FROM student;

(5) 计算学生的平均年龄；

SELECT AVG(age) AS avgAge FROM student;

(6) 给出服务器当前的系统日期与时间；

SELECT NOW();

(7) 查询所有女同学的信息；

SELECT * FROM student WHERE sex=’女’;

(8) 查询年龄在18和20之间的学生信息；

SELECT * FROM student WHERE age BETWEEN 18 AND 20;

(9) 查询所有学生的信息并按学号降序排列；

SELECT * FROM student ORDER BY sno DESC;

(10) 返回课程表的前两条记录；

SELECT * FROM course LIMIT 0,2;

(11) 查询姓王且名字为两个字的学生信息；

SELECT * FROM student WHERE sname LIKE ‘王__’;

(12) 查询“计算机”系的学生学号和姓名；

SELECT s.sno,s.sname FROM student s WHERE dno=’**计算机**’;

(13) 求选修了课程的学生学号；

SELECT DISTINCT s.sno FROM student s,student_course sc WHERE s.sno=sc.sno;

或者

SELECT DISTINCT(sno) FROM student_course;

(14) 求选修“c01001”课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；

SELECT sno,score FROM student_course WHERE cno=’c01001’ ORDER BY score DESC,sno ASC;

(15) 求“计算机”系和“信电”系的姓“张”的学生的信息；

SELECT * FROM student s WHERE s.dno IN(‘**计算机‘,’信电‘) AND s.sname LIKE ‘张**%’

(16) 求缺少了成绩的学生的学号和课程号;

SELECT sc.sno,sc.cno FROM student_course sc WHERE sc.score IS NULL

上面问题的答案

SELECT sno 学号,sname,sex FROM student;
SELECT COUNT(*) 总行数 ,age 最大年龄 FROM student;
SELECT AVG(age) FROM student;
SELECT NOW() 当前时间;
SELECT * FROM student WHERE sex=’女’;
SELECT * FROM student WHERE age BETWEEN 18 AND 20;
SELECT * FROM student GROUP BY sno DESC;
SELECT * FROM course limit 0,2;
SELECT * FROM student WHERE sname LIKE ‘王__’;
SELECT sno,sname FROM student WHERE dno=’计算机’;
SELECT DISTINCT sno FROM student_course WHERE sno= sno;
SELECT DISTINCT(sno) FROM student_course;
SELECT sno,score FROM student_course WHERE cno=’c01001’ GROUP BY score desc,sno ASC;
SELECT * FROM student WHERE dno IN(‘计算机’,’信电’) AND sname LIKE ‘张%’;
SELECT sno,cno FROM student_course WHERE score is NULL;