数据库与表的建立

1. 建立数据库

  CREATE DATABASE practice CHARSET=utf8;

2. 建立四张表

  # 学生表 Student：
  --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
CREATE TABLE Student(
	SId VARCHAR(10) ,
	Sname VARCHAR(10),
	Sage DATETIME,
	Ssex VARCHAR(10)
);



# 教师表 Teacher
--T# 教师编号,Tname 教师姓名
CREATE TABLE Teacher(
	TId VARCHAR(10),
	Tname VARCHAR(10)
); 



# 科目表 Course
--C# --课程编号,Cname 课程名称,T# 教师编号
CREATE TABLE Course(
	CId VARCHAR(10),
	Cname NVARCHAR(10),
	TId VARCHAR(10)
); 


# 成绩表 SC
--S# 学生编号,C# 课程编号,score 分数
CREATE TABLE SC(
	SId VARCHAR(10),
	CId VARCHAR(10),
	score DECIMAL(18,1)
); 

3.往四张表中分别插入数据

# 学生表 Student：

INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2017-12-30' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2017-01-01' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2018-01-01' , '女');


# 教师表 Teacher

INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四'); 
INSERT INTO Teacher VALUES('03' , '王五'); 


# 科目表 Course

INSERT INTO Course VALUES('01' , '语文' , '02'); 
INSERT INTO Course VALUES('02' , '数学' , '01'); 
INSERT INTO Course VALUES('03' , '英语' , '03'); 


# 成绩表 SC

INSERT INTO SC VALUES('01' , '01' , 80); 
INSERT INTO SC VALUES('01' , '02' , 90); 
INSERT INTO SC VALUES('01' , '03' , 99); 
INSERT INTO SC VALUES('02' , '01' , 70); 
INSERT INTO SC VALUES('02' , '02' , 60); 
INSERT INTO SC VALUES('02' , '03' , 80); 
INSERT INTO SC VALUES('03' , '01' , 80); 
INSERT INTO SC VALUES('03' , '02' , 80); 
INSERT INTO SC VALUES('03' , '03' , 80); 
INSERT INTO SC VALUES('04' , '01' , 50); 
INSERT INTO SC VALUES('04' , '02' , 30); 
INSERT INTO SC VALUES('04' , '03' , 20); 
INSERT INTO SC VALUES('05' , '01' , 76); 
INSERT INTO SC VALUES('05' , '02' , 87); 
INSERT INTO SC VALUES('06' , '01' , 31); 
INSERT INTO SC VALUES('06' , '03' , 34); 
INSERT INTO SC VALUES('07' , '02' , 89); 
INSERT INTO SC VALUES('07' , '03' , 98); 

4.查看表中数据

• 学生表

SELECT * FROM student;

• 教师表

SELECT * FROM Teacher;

• 科目表

SELECT * FROM Course;

• 成绩表

SELECT * FROM SC;