Mysql数据库命令，可以参考。

最新推荐文章于 2024-05-10 10:22:36 发布
CSDNwlp1
最新推荐文章于 2024-05-10 10:22:36 发布
阅读量61
点赞数
文章标签：数据库 java 服务器
"""
mysql -u root -p
123456
show databases;
use sa3mall;
show tables;
select * from student;
drop table student;
"""


"""
#建学生信息表student
create table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
)char set=utf8;

"""

"""
# 插入数据
#添加学生信息

insert into student values('108','曾华','男','19770901','95033');
insert into student values('105','匡明','男','19751002','95031');
insert into student values('107','王丽','女','19760123','95033');
insert into student values('101','李军','男','19760220','95033');
insert into student values('109','王芳','女','19750210','95031');
insert into student values('103','陆君','男','19740603','95031');

"""

print("----------------2-----------------")
"""
#建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null

)char set=utf8;
"""

"""
#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
"""

print("---------------------------------------------------")

"""
#建立课程表course
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,


)char set=utf8;
"""

"""
#添加课程表

insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
"""

print("-------------------------------------------")

"""
#建立成绩表

create table score
(
sno int,
cno varchar(20) not null,
degree int

)char set=utf8;
"""

"""
insert into score values('101','3-105','64');
insert into score values('102','3-245','68');
insert into score values('103','3-245','75');
insert into score values('104','3-105','76');
insert into score values('105','3-105','78');
insert into score values('106','3-166','79');
insert into score values('107','3-166','81');
insert into score values('108','3-166','85');
insert into score values('109','3-245','86');
insert into score values('110','6-105','88');
insert into score values('111','6-105','91');
insert into score values('112','6-105','92');
"""
"""
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex, class from student;
"""

"""
2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
"""

"""
3、 查询Student表的所有记录。
select *  from student;
"""

"""
4、 查询Score表中成绩在60到80之间的所有记录。
select *  from score where degree between 60 and 80;
"""

"""
5、 查询Score表中成绩为85，86或88的记录。
select *  from score where degree in(85,86,88);
"""

"""
6、 查询Student表中“95031”班或性别为“女”的同学记录。
select *  from student where class='95031' or ssex='女';
"""

"""
7、 以Class降序查询Student表的所有记录。   降序
select *  from student order by class desc;
"""

"""
8、 以Cno升序、Degree降序查询Score表的所有记录。
     升序      降序
select *  from Score order by cno asc,degree desc;
"""

"""
9、 查询“95031”班的学生人数。
select count(*)  from student where class='95031';
"""

"""
10、     查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
limi关键字作用：第一个数字表示从多少开始，第二个数字表示查多少条
注：存在一定的bug，比如只取第一条记录，但可能有两位最高分的同学
select sno,cno  from score where degree=(select max(degree) from score);
select sno,cno  from score order by  degree desc limit 0,1;
"""

"""
11、     查询每门课的平均成绩。
select cno,avg(degree) from score group by cno;
"""


"""
12、     查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
用in 不用= 是因为可能会有多个
select avg(degree) from score where cno like '3%' and cno in (select cno from score group by cno having count(*)>=5);
简单写法：
select avg(degree) from score where cno like '3%' and group by cno having count(*)>=5;
"""


"""
13、     查询分数大于70，小于90的Sno列。
select sno from score where degree>70 and degree<90;
"""

"""
14、     查询所有学生的Sname、Cno和Degree列。
select  sname, cno,degree from score , student where score.sno=student.sno;
"""

"""
15、     查询所有学生的Sno、Cname和Degree列。
select  sno,cname,degree from score , course where score.cno=course.cno;
"""

"""
16、     查询所有学生的Sname、Cname和Degree列。
join .. on 写法：
select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
select sname,cname,degree from student join score on student.sno=score.Sno join course on course.cno=score.cno;
"""

"""
17、     查询“95033”班学生的平均分。
select avg(degree) as 'class=95033' from score where sno in (select sno from  student where class='95033' );
"""

"""
18、 假设使用如下命令建立了一个grade表：
现查询所有同学的Sno、Cno和rank列。

create table grade(low int,upp int,rank char(20))char set=utf8;

insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');



select sno,cno,rank from score,grade where degree between low and upp;
"""

"""
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
<span style="color: #000000; font-size: 15px">109同学，选修是3-105课的</span>
select * from score where cno='3-105' and degree>(select max(degree ) from score where sno='109' and cno='3-105' );

<span style="font-size: 15px">109同学，没有选修3-105课</span>
select * from score where cno='3-105' and degree>(select max(degree ) from score where sno='109');
"""

"""
20.选了多门课程并且是这个课程下不是最高分的

select * from score a where sno in (select sno from score group by sno having count(*)>1) and degree<( select max(degree ) from score b where b.cno = a.cno);
"""
"""
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

Select * from score where degree>(select degree from score where sno='109' and cno='3-245' );
"""

"""
22、查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。


select sno,sname,sbirthday  from student where year(sbirthday) = (select year(sbirthday) from student where  sno='108');

select sno,sname,sbirthday  from student where year(sbirthday) = (select year(sbirthday) from student where  sno='101');
"""

"""
23、查询“张旭“教师任课的学生成绩。


select sno,degree from score,course  where score.cno=course.cno and course.tno= (select tno from teacher where tname='张旭' );

select degree from score where cno in (select cno from course where tno= (select tno from teacher where tname='张旭'  ) );
"""

"""
24、查询选修某课程的同学人数多于5人的教师姓名。


select tname from teacher,  course where teacher.tno=course.tno and course.cno =(select cno from score group by cno having count(*)>5);

select tname from teacher where tno=( select tno from course where cno=( select cno from score group by cno having count(*)>5 ));
"""

"""
25、查询95033班和95031班全体学生的记录。

select * from  student where  class in ('95033','95031');
"""


"""
26、  查询存在有85分以上成绩的课程Cno.

select cno from score where degree > 85;
"""

"""
27、查询出“计算机系“教师所教课程的成绩表。

select * from course  where cno in (select cno from course  where tno in (select tno from teacher where depart='计算机系'));
"""

"""
28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。

select tname,prof from teacher where depart ='计算机系' and prof  not in( select prof from Teacher where depart ='电子工程系')<br> union <br>select tname,prof from teacher where depart ='电子工程系' and prof  not in( select prof from teacher where depart ='计算机系');
"""

"""
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

all:代表括号中的所有成绩

select cno,sno,degree from score where cno='3-105' and degree >all(select degree from score where cno='3-245' ) order by degree desc;

"""

"""
31、 查询所有教师和同学的name、sex和birthday.


select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student;
"""

"""
32、查询所有“女”教师和“女”同学的name、sex和birthday.

select tname,tsex,tbirthday from teacher where tsex='女' union select sname,ssex,sbirthday from student where ssex='女';
"""


"""
33、 查询成绩比该课程平均成绩低的同学的成绩表。

select * from score a  where degree < (select avg(degree) from score b where b.cno=a.cno);
"""

"""
34、 查询所有任课教师的Tname和Depart.

select tname,depart from teacher where tno in (select tno from course );
"""

"""
35 、 查询所有未讲课的教师的Tname和Depart.

select tname,depart from teacher where tno not in (select tno from course where cno in (select cno from score  ));
"""

"""
36、查询至少有2名男生的班号。

select class from student where ssex='男' group by class having count(*)>1;
"""

"""
37、查询Student表中不姓“王”的同学记录。

select * from student where sname not  like '王%%';
"""

"""

38、查询Student表中每个学生的姓名和年龄。

select sname, year(now())-year(sbirthday)  from student;
"""

"""
39、查询Student表中最大和最小的Sbirthday日期值。

select Max(sbirthday ),Min(sbirthday ) from student;
"""

"""
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from student  order by  class desc, sbirthday;
"""

"""
41、查询“男”教师及其所上的课程。

select tname,cname from course,teacher where course.tno= teacher.tno and teacher.tsex='男';

"""

"""
42、查询最高分同学的Sno、Cno和Degree列。


select sno,cno,degree from score where degree=(select max(degree) from score);
排序写法：

select sno,cno,degree from score order by degree desc limit 0,1;
"""

"""
43、查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex = (select ssex from student where sname='李军');
"""

"""
44、查询和“李军”同性别并同班的同学Sname.


select sname from student where ssex = (select ssex from student where sname='李军' ) and class=( select class from student where sname='李军');
"""

"""
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

select  sno,cno,degree from score where cno=( select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='男');
"""