数据库创建两张表,关联表并且创建视图

创建两张关联表

打开数据库

Mysql –u root –p

查看数据库

Show databases ;

创建数据库class13201

Create database class13201;

使用数据库class13201;

Use class13201;

创建表student

Create table table_student(

StudentId varchar(11) PRIMARY KEY,

StudentName varchar(20) not null,

Sex varchar(8) not null,

Lco varchar(40),

Class varchar(40) DEFAULT ‘2013级信息工程一班’

);

创建表score 并设置StudentId为外键

create table score(

id int primary key auto_increment,

StudentId varchar(11) not null,

ClassDate varchar(10),

ARM varchar(10),

degital varchar(10),

ios varchar(10),

linux varchar(10),

constraint fk_score_StudentId foreign key(studentid) references table_student(StudentId)

);.

插入数据到表table_student;

insert into table_student(StudentId,Sex,StudentName,Lco) values(133********,'男','张三','四川'), (133********,'男','王五','成都'),

(133********,'男','李四

','成都');

插入数据到表score

insert into score(id,studentid,ClassDate,ARM,degital,ios,linux)

values(1,'133********','81','82','83','84','85');

insert into score(StudentId,ClassDate,ARM,degital,ios,linux)

values('133********','91','92','93','94','95'),

('133********','71','72','73','74','75');

insert into score(StudentId,ClassDate,ARM,degital,ios,linux)

values('133********','91','92','93','94','95'),

('133********','71','72','73','74','75');

查看插入结果

查看class_student

Select * from table_student;

查看表score

Select * from scorel;

创建视图

CREATE ALGORITHM=MERGE VIEW

StudentScore(StudentName,StudentId,ClassDate,ARM,degital,ios,linux)

AS

SELECT

table_student.StudentName,table_student.StudentId,ClassDate,ARM,degital,ios,linux FROM table_student,score

where table_student.StudentId=score.StudentId

查看视图

Select * from StudentName;

将视图按照学好的升序排列

Select * from StudentName

Order by StudentId ASC;