create database school;
use school;

create table student(
  sno varchar(20) not null primary key,-- 學(xué)號
    sname varchar(20) not null, -- 姓名
    ssex varchar(20) not null, -- 性別
    sbirthday datetime, -- 出生年月
    class varchar(20)  -- 班級
);

create table course(
 cno varchar(20) not null primary key, -- 課程號
 cname varchar(20) not null, -- 課程名稱
 tno varchar(20) not null references tearcher(tno) -- 教工編號
);

create table score(
 sno varchar(20) not null references student(sno),  -- 學(xué)號
 cno varchar(20) not null references course(cno),  -- 課程號
 degree decimal(4,1) -- 成績
);

create table tearcher(
 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 -- 教工所在部門
 
 
);

insert into student values ('108','曾華','男','1977-09-01','95033'),('105','匡明','男','1975-10-02','95031'),
('107','王麗','女','1976-01-23','95033'),
('101','李軍','男','1976-02-20','95033'),
('109','王芳','女','1975-02-10','95031'),
('103','陸軍','男','1974-06-03','95031')

select * from student;

insert into course values ('3-105','計算機導(dǎo)論','825'),
('3-245','操作系統(tǒng)','804'),
('6-166','數(shù)字電路','856'),
('9-888','高等數(shù)學(xué)','831')
select * from course;

insert into score values ('103','3-245','86'),
('105','3-245','75'),
('109','3-245','68'),
('103','3-105','92'),
('105','3-105','88'),
('109','3-105','76'),
('101','3-105','64'),
('107','3-105','91'),
('108','3-105','78'),
('101','6-166','85'),
('107','6-166','79'),
('108','6-166','81')
select * from score;

insert into tearcher values ('804','李誠','男','1958-12-02','副教授','計算機系'),
('856','張旭','男','1969-03-12','講師','電子工程系'),
('825','王萍','女','1972-05-05','助教','計算機系'),
('831','劉兵','女','1977-08-14','助教','電子工程系')
select * from tearcher;

-- 1.查詢student 表中的所有記錄的sname , ssex, 和 class列（查詢指定列）
select sname as 姓名,ssex as 性別,class as 班級 from student;


-- 2.查詢教師所有的單位即不重復(fù)的depart列
select distinct depart from tearcher;

-- 3.查詢student 表的所有記錄
select * from student;

-- 4.查詢score 表中成績在60到80之間的所有記錄殊校。
select * from score where degree between 60 and 80;
select * from score where degree >=60 and degree <=80;

-- 5.查詢score表中成績?yōu)?5甚颂，86或88的記錄双肤。
select * from score where degree=85 || degree=86 || degree=88;
select * from score where degree in (85,86,88);

-- 6.查詢student表中"95031"班或性別為"女"的同學(xué)記錄
select * from student where class='95031' || ssex = '女';

-- 7.以class降序查詢student表的所有記錄
select * from student order by class desc;

-- 8.以cno升序、degree降序查詢score表的所有記錄。
select * from score order by cno,degree desc;

-- 9.查詢"95031"班的學(xué)生人數(shù)凳干。
select count(*) 人數(shù) from student where class='95031';

-- 10.查詢score表中的最高分的學(xué)生學(xué)號和課程號
select sno,cno from score  order by degree desc limit 1;
select sno,cno from score where degree=(select max(degree) from score);