sql语句
时间：2021-07-01 10:21:17
帮助过：5人阅读
,
‘张老大‘,
‘男‘,
1985,
‘计算机系‘,
‘北京市海淀区‘);
INSERT INTO student VALUES( 902,
‘张老二‘,
‘男‘,
1986,
‘中文系‘,
‘北京市昌平区‘);
INSERT INTO student VALUES( 903,
‘张三‘,
‘女‘,
1990,
‘中文系‘,
‘湖南省永州市‘);
INSERT INTO student VALUES( 904,
‘李四‘,
‘男‘,
1990,
‘英语系‘,
‘辽宁省阜新市‘);
INSERT INTO student VALUES( 905,
‘王五‘,
‘女‘,
1991,
‘英语系‘,
‘福建省厦门市‘);
INSERT INTO student VALUES( 906,
‘王六‘,
‘男‘,
1988,
‘计算机系‘,
‘湖南省衡阳市‘);
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901,
‘计算机‘,
98);
INSERT INTO score VALUES(NULL,901,
‘英语‘,
80);
INSERT INTO score VALUES(NULL,902,
‘计算机‘,
65);
INSERT INTO score VALUES(NULL,902,
‘中文‘,
88);
INSERT INTO score VALUES(NULL,903,
‘中文‘,
95);
INSERT INTO score VALUES(NULL,904,
‘计算机‘,
70);
INSERT INTO score VALUES(NULL,904,
‘英语‘,
92);
INSERT INTO score VALUES(NULL,905,
‘英语‘,
94);
INSERT INTO score VALUES(NULL,906,
‘计算机‘,
90);
INSERT INTO score VALUES(NULL,906,
‘英语‘,
85);
#2、查询Student表的第2到第4条数据
select *
from student WHERE id>=
902 AND id<=
904;
#3、从Student表查询所有学生的学号，姓名和院系
SELECT id,name,depatment FROM student;
#4、从Student表中查询计算机系和英语系的学生
select *
from student WHERE department=
‘计算机系‘or department=
‘英语系‘;
#5、从Student表中查询年龄在18~
22岁的学生信息
select *
from student WHERE
2019-birth>=
18 and
2019-birth<=
22;
#6、从student表中查询每个院系有多少人
SELECT department, COUNT(id) FROM student GROUP BY department;
#7、从Score表中查询每个科目的最高分
SELECT c_name,MAX(grade) from score GROUP BY c_name;
#8、查询李四的考试科目
select c_name,grade
from score
where stu_id = (SELECT id
from student
where name =
‘李四‘);
#9、用连接的方式查询所有学生的姓名、院系、科目和考试成绩
SELECT student.id,name,department,c_name,grade FROM student,score WHERE student.id=
score.stu_id;
#10、计算每个学生的总成绩
SELECT name,student.id,SUM(grade) from student,score
where score.stu_id =
student.id GROUP BY id;
#11、计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) from score GROUP BY c_name;
#12、查询计算机成绩低于95分的学生信息
select * FROM student WHERE id IN(SELECT stu_id FROM score
where c_name =
‘计算机‘and grade<
95);
#13、查询同时参加计算机和英语考试的学生信息
SELECT * FROM STUDENT WHERE id
in(
select stu_id
from score
where stu_id
in(
select stu_id
from score
where C_NAME=
‘计算机‘) and C_NAME=
‘英语‘) ;
#14、将计算机考试成绩按照从高到低进行排序
SELECT C_NAME ‘科目‘,GRADE
‘考试成绩‘ FROM SCORE WHERE C_NAME=
‘计算机‘ ORDER BY(GRADE) DESC;
#15、从student表和score表中查询出学生的学号，然后合并查询结果
SELECT DISTINCT NAME ‘姓名‘,ST.ID+STU_ID
‘学号‘ FROM STUDENT ST,SCORE SC WHERE ST.ID=
SC.STU_ID;
#16、查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名 selec *
from 表名
where name like ‘A%
’）
SELECT NAME ‘姓名‘,DEPARTMENT
‘院系‘,C_NAME
‘科目‘,GRADE
‘考试成绩‘ FROM STUDENT stu,SCORE sc WHERE stu.ID=sc.STU_ID AND NAME LIKE OR
‘王%‘;
#17、查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
SELECT NAME ‘姓名‘,
2019-BIRTH
‘年龄‘,DEPARTMENT
‘院系‘,C_NAME
‘科目‘,GRADE
‘考试成绩‘ FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND ADDRESS LIKE
‘湖南%‘;
sql语句
标签：like 厦门 考试成绩 ima select 模糊查询 目的 com src
