mysql查询（学⽣表、课程表、选课表）
************************************************************
为sc表中的sno和cno创建外键
alter table sc add foreign key(sno) references student(sno);
alter table sc add foreign key(cno) references course(cno);
************************************************************
a)  插⼊如下学⽣记录（学号：95030，姓名：李莉，年龄：18）
insert into student(sno,sname,sage) values('95030','李莉',18);
b)  插⼊如下选课记录（95030，1）
insert int sc(sno,cno) values('95030','001');
c)  计算机系学⽣年龄改成20
update student set sage = 20where sdept = 'cs';
d)  数学系所有学⽣成绩改成0
update sc set grade = 0where cno = '002';
e)  把低于总平均成绩的⼥同学成绩提⾼5分
f)  修改2号课程的成绩，若成绩⼩于75分提⾼5％，成绩⼤于75时提⾼
4％（两个语句实现，注意顺序）
update sc set grade=grade+grade*0.05where cno='002'and grade<75;
update sc set grade=grade+grade*0.04where cno='002'and grade>75;
g)  删除95030学⽣信息
delete from student where sno = '95030';
h)  删除SC表中⽆成绩的记录
delete from sc where grade is null;
i)  删除张娜的选课记录
delete from sc where sno = (select sno from student where sname = '张娜');
j)  删除数学系所有学⽣选课记录
delete from sc where cno = '002';
k)  删除不及格的学⽣选课记录
delete from sc where grade < 60;
mysql语句顺序l)  查询每⼀门课程成绩都⼤于等于80分的学⽣学号、姓名和性别，把值送往另⼀个已经存在的基本表STU(SNO,SNAME,SSEX)中insert into stu(sno,sname,ssex)
select student.sno,student.sname,student.ssex
from student,sc
where student.sno=sc.sno ade>=80;
m)  把所有学⽣学号和课程号连接追加到新表中
insert into STUsc(sno,cno)
select student.sno,sco from student,sc
where student.sno = sc.sno;
n)  所有学⽣年龄增1
update student set sage = sage+1;
o)  统计3门以上课程不及格的学⽣把
相应的学⽣姓名、系别追加到另外⼀个表中
insert into stu(sname,sdept) --插⼊表中
select sname,sdept from student,sc --选择出列名
where --条件
student.sno=(select sno from sc
where grade<60group by sno having count(*)>3);
查询每个学⽣及其选课情况；
select sno,cno from sc;
将STUDENT,SC进⾏右连接
查询有不及格的学⽣姓名和所在系
select a.sname,a.sdept from student a right join sc b on a.sno = b.sno;
查询所有成绩为优秀（⼤于90分）的学⽣姓名
ade,b.sname ,a.sno
from sc a right join student b ade > 90 and a.sno = b.sno;
+-------+--------+-------+
| grade | sname  | sno  |
| grade | sname  | sno  |
+-------+--------+-------+
| NULL  | 丽丽  | NULL  |
| NULL  | 赵海  | NULL  |
|    97 | 刘晨  | 08005 |
|    93 | 刘丹丹 | 08006 |
| NULL  | 刘⽴  | NULL  |
| NULL  | 王江  | NULL  |
| NULL  | ⾼晓  | NULL  |
|  100 | 张丽  | 08010 |
| NULL  | NULL  | NULL  |
+-------+--------+-------+
ade,b.sname ,a.sno from sc a
join student b ade > 90 and a.sno = b.sno;
+-------+--------+-------+
| grade | sname  | sno  |
+-------+--------+-------+
|    97 | 刘晨  | 08005 |
|    93 | 刘丹丹 | 08006 |
|  100 | 张丽  | 08010 |
+-------+--------+-------+
查询既选修了2号课程⼜选修了3号课程的
学⽣姓名、学号；
select sname,sno from student where sno in(
select sno from sc
where cno = '003'
and
sno in(select sno from sc where cno = '006'));
----或者
select a.sname,a.sno from student a join sc b where a.sno = b.sno and cno = '003' and b.sno in(select sno from sc where cno = '006')；查询和刘晨同⼀年龄的学⽣
select sno ,sname from student where sage = (select sage from student where sname = '刘晨');
选修了课程名为“数据库”的学⽣姓名和年龄
select a.sname,a.sage from student a join sc b on a.sno = b.sno and bo = '001
查询其他系⽐IS系任⼀学⽣年龄⼩的学⽣名单
select sname,sage from student where sage < any
(select sage from student where sdept = 'is');
查询其他系中⽐IS系所有学⽣年龄都⼩的学⽣名单
select sname,sage from student where sage < all
(select sage from student where sdept = 'is');
查询选修了全部课程的学⽣姓名
select sname,snojson默认采用什么处理非西文字符
from student where sno in(select sno from sc);
查询计算机系学⽣及其性别是男的学⽣
select *from student where sdept = 'cs' and ssex = '男';
查询选修课程1的学⽣集合和选修2号课程学⽣集合的差集
select sno from sc where cno  =  '001' and sno not
in(select sno from sc where cno = '002');
查询李丽同学不学的课程的课程号
select cno from course where cno not in(select ao from sc a join student b on a.sno = b.sno and b.sname = '李丽');
查询选修了3号课程的学⽣平均年龄
select avg(sage) from student where sno in(select sno from sc where cno = '003');
求每门课程学⽣的平均成绩
select avg(grade) from sc group by cno;
统计每门课程的学⽣选修⼈数（超过3⼈的才统计）。要求输出课程号和选修⼈数，结果按⼈数降序排列，若⼈数相同，按课程号升序排列    select cno ,count(*)from sc group by cno having count(sno) > 3;
查询学号⽐刘晨⼤，⽽年龄⽐他⼩的学⽣姓名。
select sname from student where
sno > (select sno from student where sname = '刘晨')
and sage < (select sage from student where sname = '刘晨')；
求年龄⼤于⼥同学平均年龄的男同学姓名和年龄
大数据可视化工具tableau如何操作select sname,sage from student where ssex = '男'and
sage > (select avg(sage) from student where ssex = '⼥');
sage > (select avg(sage) from student where ssex = '⼥');
求年龄⼤于所有⼥同学年龄的男同学姓名和年龄
select sname,sage from student where ssex = '男'and
sage > all (select sage from student where ssex = '⼥');
查询95001和95002两个学⽣都选修的课程的信息
select *from sc where sno in ('95001','95002');
-----------------------------------------------------
alter table student add test varchar(20);      -
alter table student drop test;                  -
-----------------------------------------------------
为学⽣表按学号建⽴唯⼀索引
mysql> create UNIQUE INDEX stusno ON student(sno);
Query OK, 10 rows affected (0.61 sec)
Records: 10  Duplicates: 0  Warnings: 0
为course表按课程号升序（默认）建⽴唯⼀索引
mysql> create UNIQUE INDEX coucno ON course(cno);
Query OK, 7 rows affected (0.38 sec)
Records: 7  Duplicates: 0  Warnings: 0
为sc表按学号升序和课程号降序建⽴唯⼀索引
遍历二叉树的三种方法代码
mysql> create UNIQUE INDEX scno ON sc(sno asc,cno desc);
Query OK, 11 rows affected (0.34 sec)
Records: 11  Duplicates: 0  Warnings: 0
//删除索引
drop index scno on sc;
/
/插⼊学⽣信息
insert into student (sno,sname,sage) values('95030','；李莉',18);
a)  查询全体学⽣的学号和姓名
select sno,sname from student;
b)  查询全体学⽣的详细记录
select *from student;
c)  查询所有选修过课程的学⽣学号
select distinct sno from sc ;
d)  查询考试有不及格的学⽣学号
select sno from sc where grade < 60;
e)  查询不是信息系（IS）、计算机系（CS）的学⽣性别、年龄、系别select sname,ssex,sdept from student where sdept not in('is','cs');
f)  查询选修了4号课的学⽣学号和成绩，结果按成绩降序排列
select sno,grade from sc where cno = '004'order by grade desc;
g)  查询每个课程号和相应的选课⼈数
select cno,count(cno) as cnonumed from sc group by cno;
h)  查询计算机系（CS）的学⽣姓名、年龄、系别
select sname,sage,sdept from student where sdept = 'cs';
i)  查询年龄18－20岁的学⽣学号、姓名、系别、年龄；
select sname,sage,sdept from student where sage between 18and20;    j)  查询姓刘的学⽣情况
balabolka怎么用select *from student where sname like '刘%';
k)  查询既选修3号课程，⼜选修6号课程的学⽣学号和成绩select sno ,grade from sc
where cno = '003'
and
sno in(select sno from sc where cno = '006');
l)  查询学⽣的姓名和出⽣年份（今年2003年）
select sname,sbirthday from student;
m)  查询没有成绩的学⽣学号和课程号
select sno,cno from sc where grade is null;
n)  查询总成绩⼤于200分的学⽣学号
select sno,sum(grade) from sc
group by sno having sum(grade)  > 200;
o)  查询每门课程不及格学⽣⼈数
select cno,count(sno) from sc
where grade < 90
group by cno;
p)  查询不及格课程超过3门的学⽣学号
select cno,count(sno) from sc
where grade < 60
group by cno
having count(sno) > 3;
q)  查询年龄在10到19岁之间的学⽣信息
select *from student
where sage between 10and19;
r)  查询全体学⽣情况，按所在系升序排列，同⼀个系的学⽣按年龄降序排列select *from student order by sdept asc,sage desc;
s)  查询选了1号课程的学⽣平均成绩
select cno,avg(grade) from sc where cno = '001'group by cno;
+-----+------------+
| cno | avg(grade) |
+-----+------------+
| 001 |    92.6667 |
+-----+------------+
1 row in set (0.00 sec)
mysql> select cno,avg(grade) from sc group by cno having cno = '001';    +-----+------------+
| cno | avg(grade) |
+-----+------------+
建设银行操作员代码是什么意思
| 001 |    92.6667 |
+-----+------------+
t)  查询选了3号课程的学⽣的最⾼分
select cno, max(grade)
from sc where cno = '003'group by cno;
u)  查询每个同学的总成绩
select sno,sum(grade)
from sc
group by sno;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。