SQL 练习的前期准备
2017-10-30 本文已影响69人
叨叨侠爱叨叨
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,'无')