SELECT
Sname,
Ssex,
Class
FROM
Student;
Last active
August 13, 2019 08:40
-
-
Save DaiwenZh5/7684cadc988f4c8d9a6e31a0efb25b90 to your computer and use it in GitHub Desktop.
Mysql 练习 - part 1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#建学生信息表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) | |
); | |
#建立教师表 | |
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 | |
); | |
#建立课程表course | |
create table course | |
( | |
cno varchar(20) not null primary key, | |
cname varchar(20) not null, | |
tno varchar(20) not null, | |
foreign key(tno) references teacher(tno) | |
); | |
#建立成绩表 | |
create table score | |
( | |
sno varchar(20) not null primary key, | |
foreign key(sno) references student(sno), | |
cno varchar(20) not null, | |
foreign key(cno) references course(cno), | |
degree decimal | |
); | |
#添加学生信息 | |
insert into student values('108','曾华','男','1977-09-01','95033'); | |
insert into student values('105','匡明','男','1975-10-02','95031'); | |
insert into student values('107','王丽','女','1976-01-23','95033'); | |
insert into student values('101','李军','男','1976-02-20','95033'); | |
insert into student values('109','王芳','女','1975-02-10','95031'); | |
insert into student values('103','陆君','男','1974-06-03','95031'); | |
#添加教师表 | |
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','助教','电子工程系'); | |
#添加课程表 | |
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'); | |
#添加成绩表 | |
insert into score values('103','3-245','86'); | |
insert into score values('105','3-245','75'); | |
insert into score values('109','3-245','68'); | |
insert into score values('103','3-105','92'); | |
insert into score values('105','3-105','88'); | |
insert into score values('109','3-105','76'); | |
insert into score values('103','3-105','64'); | |
insert into score values('105','3-105','91'); | |
insert into score values('109','3-105','78'); | |
insert into score values('103','6-166','85'); | |
insert into score values('105','6-166','79'); | |
insert into score values('109','6-166','81'); |
CREATE TABLE Grade(low INT(3), upp INT(3), 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');
现查询所有同学的 Sno、Cno 和 rank 列。
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment