~~~
create database if not exists stu default character set utf8;
use stu;
create table if not exists s_student(
sno int unsigned key,
sname varchar(250),
ssex enum('男','女','保密') default '保密',
sbirthday int,
class varchar(50)
)engine=innodb charset=utf8;
create table if not exists s_course(
cno text,
cname varchar(250),
tno int
)engine=innodb;
create table if not exists s_score(
sno int unsigned key,
cno text,
degree tinyint
)engine=innodb;
create table if not exists s_teacher(
tno int unsigned key,
tname varchar(250) not null,
tssex enum('男','女','保密') default '保密',
tbirthday int,
title varchar(250),
depart varchar(250)
)engine=innodb;
-- 一、每張表使用SQL語句插入至少10條數據。
insert s_student(sno,sname,ssex,sbirthday,class)
values(0001,'neo1','男','20010101',95033),
(0002,'王neo2','女','20010102',95031),
(0003,'neo3','男','20010103',95033),
(0004,'王neo4','女','20010104',95033),
(0005,'neo5','男','20010105',95031),
(0006,'王neo6','女','20010106','4A'),
(0007,'neo7','男','20010107','1A'),
(0008,'neo8','女','20010108','3A'),
(0009,'neo9','男','20010109',95031),
(0010,'neo10','女','20010110','1A');
insert s_course(cno,cname,tno)
values(201601,'電腦1',201501),
(201602,'計算機導論',201503),
('3-102','計算機導論123',201503),
(201604,'電腦4',201501),
(201605,'計算機導論234',201501),
(201606,'計算機導論345',201503),
(201607,'電腦7',201503),
(201608,'電腦8',201503),
(201609,'電腦9',201503),
(201610,'電腦10',201502);
insert s_score(sno,cno,degree)
values(0001,'3-102',75),
(0002,'3-102',85),
(0003,'201602',70),
(0004,'3-105',86),
(0005,'3-102',90),
(0006,201602,50),
(0007,'3-102',60),
(0008,'3-105',70),
(0009,'3-102',80),
(0010,'3-102',90);
insert s_teacher(tno,tname,tssex,tbirthday,title,depart)
values(201501,'老師1','男',19910101,'教職員1','IT組'),
(201502,'老師2','女',19910102,'教職員2','總務組'),
(201503,'張旭','男',19910103,'教職員3','IT組'),
(201504,'老師4','女',19910104,'教職員4','總務組'),
(201505,'老師5','男',19910105,'教職員5','IT組'),
(201506,'老師6','女',19910106,'教職員6','總務組'),
(201507,'老師7','男',19910107,'教職員7','IT組'),
(201508,'老師8','女',19910108,'教職員8','總務組'),
(201509,'老師9','男',19910109,'教職員9','IT組'),
(201510,'老師10','女',19910110,'教職員10','總務組');
update s_teacher set tssex='男' where tno in(201501,201503,201504,201505);
update s_teacher set tssex='女' where tno in(201502,201506,201507,201508);
update s_teacher set tssex='保密' where tno in(201510,201509);
-- 二、完成以下查詢題目：
-- 1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。
select sname,ssex,class from s_student;
-- 2、 查詢教師所有的單位即不重復的Depart列。
select * from s_teacher group by depart;
-- 3、 查詢Student表的所有記錄。
select * from s_student;
-- 4、 查詢Score表中成績在60到80之間的所有記錄。
select * from s_score where degree between 60 and 80;
-- 5、 查詢Score表中成績為85，86或88的記錄。
select * from s_score where degree in(85,86,88);
-- 6、 查詢Student表中“95031”班或性別為“女”的同學記錄。
select * from s_student where class in(95031) or ssex in('女');
-- 7、 以Class降序查詢Student表的所有記錄。
select * from s_student order by class desc;
-- 8、 以Cno升序、Degree降序查詢Score表的所有記錄。
select * from s_score order by cno asc,degree desc;
-- 9、 查詢“95031”班的學生人數。
select count(*) as '95031班人數' from s_student where class in(95031);
-- 10、查詢Score表中的最高分的學生學號和課程號。
select sno,cno,degree as '最高分' from s_score where degree>=all(select degree from s_score);
-- select sno,cno,degree as '最高分'
-- from s_score
-- order by degree desc
-- limit 0,2;
-- 11、查詢‘3-105’號課程的平均分。
select avg(degree) as '3-105的平均分' from s_score where cno in('3-105');
-- 12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。
select avg(degree) as '以3開頭的課程的平均分數' from s_score where cno like '3%';
-- 13、查詢所有學生的Sname、Cno和Degree列。
select s_st.sname,s_sc.cno,s_sc.degree
from s_student as s_st
join s_score as s_sc
on s_st.sno=s_sc.sno;
-- 14、查詢“95033”班所選課程的平均分。
select avg(degree) as '“95033”班所選課程的平均分'
from s_score as sc
join s_student as st
on sc.sno=st.sno
where class in(95033);
-- 15、假設使用如下命令建立了一個grade表：
create table grade(low int,upp int,rank char(1));
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');
commit;
-- 現查詢所有同學的Sno、Cno和rank列。
select c.sno,c.cno,g.rank
from s_score as c
join grade as g
where c.degree between low and upp;
-- 16、查詢"張旭"教師任課的學生成績。
select t.tname,s.sname,c.degree
from s_score as c
join s_student as s
on c.sno=s.sno
join s_course as r
on c.cno=r.cno
join s_teacher as t
on t.tno=r.tno
where t.tname in('張旭');
-- select r.tno,t.sname,c.degree
-- from s_score as c
-- join s_student as t
-- on c.sno=t.sno
-- join s_course as r
-- on c.cno=r.cno
-- where r.tno in(201503);
-- 17、查詢選修某課程的同學人數多于5人的教師姓名。
select t.tname as '同學人數多于5人的教師'
from s_teacher as t
join s_course as c
on t.tno=c.tno
join s_score as r
on c.cno=r.cno
group by c.cno
having count(*)>5;
-- select c.tno as '同學人數多于5人的教師'
-- from s_score as r
-- join s_course as c
-- on r.cno=c.cno
-- group by c.cno
-- having count(*)>5;
-- 18、查詢所有教師和同學的Name、Sex和Birthday。
select sname as name,ssex as sex,sbirthday as birthday from s_student union all select tname,tssex,tbirthday from s_teacher;
-- 19 查詢所有未講課的教師的Tname和Depart。
select t.tname,t.depart
from s_teacher as t
join s_score as c
where t.tno not in(select tno from s_course)
group by t.tname;
-- 20、查詢至少有2名男生的班號。
select class as '至少有2名男生的班號'
from s_student
where ssex='男'
group by class
having count(*)>=2;
-- 21、查詢Student表中不姓“王”的同學記錄。
select * from s_student where sname not like '王%';
-- 22、查詢所有選修“計算機導論”課程的“男”同學的成績表。
select t.sno,t.sname,t.ssex,c.cname,r.degree
from s_score as r
join s_course as c
on r.cno=c.cno
join s_student as t
on r.sno=t.sno
where c.cname='計算機導論' and t.ssex='男';
~~~
<hr/>
Mysql基礎練習效果圖：
