SQL 练习的前期准备

1、安装工具：mysql+SQLyog
2、建表+插入数据
学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
表结构如下：
（1）Student (学生表)

CREATE TABLE student
(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5)
)

（2）Course（课程表）

CREATE TABLE course 
  ( 
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL
  ) 

（3）Score(成绩表)

CREATE TABLE score
  ( 
sno  VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL 
  ) 

（4）Teacher(教师表)

CREATE TABLE teacher 
  ( 
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL, 
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL, 
prof VARCHAR(6),
depart VARCHAR(10) NOT NULL
  )

表的数据如下：
（1）student

INSERT INTO student(sno,sname,ssex,sbirthday,class) 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)

（2）course

 INSERT INTO course (cno,cname,tno) values
 ('3-105', '计算机导论','825'),
 ('3-245', '操作系统','804'),
 ('6-166', '数字电路','856'),
 ('9-888', '高等数学','831')

（3）score

 INSERT INTO score (sno,cno,degree) 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')

（4）teacher

INSERT INTO teacher (tno,tname,tsex,tbirthday,prof,depart) values
 ('804', '李诚','男','21521','副教授','计算机系'),
 ('856', '张旭','男','25274','讲师','电子工程系'),
 ('825', '王萍','女','26424','助教','计算机系'),
 ('831', '刘冰','女','28351','助教','电子工程系')

新增数据：

 INSERT INTO score
 SELECT 1,1,56 UNION ALL 
 SELECT 1,2,78 UNION ALL 
 SELECT 1,3,67 UNION ALL 
 SELECT 1,4,58 UNION ALL 
 SELECT 2,1,79 UNION ALL 
 SELECT 2,2,81 UNION ALL 
 SELECT 2,3,92 UNION ALL 
 SELECT 2,4,68 UNION ALL 
 SELECT 3,1,91 UNION ALL 
 SELECT 3,2,47 UNION ALL 
 SELECT 3,3,88 UNION ALL 
 SELECT 3,4,56 UNION ALL 
 SELECT 4,2,88 UNION ALL 
 SELECT 4,3,90 UNION ALL 
 SELECT 4,4,93 UNION ALL 
 SELECT 5,1,46 UNION ALL 
 SELECT 5,3,78 UNION ALL 
 SELECT 5,4,53 UNION ALL 
 SELECT 6,1,35 UNION ALL 
 SELECT 6,2,68 UNION ALL 
 SELECT 6,4,71
 INSERT INTO course 
 SELECT 1,'语文',1 UNION ALL
 SELECT 2,'数学',2 UNION ALL
 SELECT 3,'英语',3 UNION ALL
 SELECT 4,'物理',4
 INSERT INTO teacher (sno,sname,ssex,sbirthday,class) VALUES
 ('1', '叶平', '男', NULL, NULL,'无'),
 ('2', '贺高', '男', NULL, NULL,'无'),
 ('3', '杨艳','女', NULL, NULL,'无'),
 ('4', '周磊','男', NULL, NULL,'无')
 INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES
 ('1', '刘一','男', NULL,'无'),
 ('2', '钱二','女', NULL,'无'),
 ('3', '张三','男', NULL,'无'),
 ('4', '李四','女', NULL,'无'),
 ('5', '王五','男', NULL,'无'),
 ('6', '赵六','女', NULL,'无')

sql 练习(一)
sql 练习(二)
sql 练习(三)
sql 练习(四)