目录
1.创建表
编辑
create table stu(
-> s_id int(11) not null unique primary key,
-> s_name varchar(20) not null,
-> addr varchar(50) not null,
-> tel varchar(50) not null
-> );
create table sign(
-> s_id int(11) not null unique primary key,
-> s_name varchar(20) not null,
-> s_sch varchar(50) not null,
-> s_sign_sch varchar(50) not null
-> );
create table stu_mark(
-> s_id int(11) not null unique primary key,
-> s_name varchar(20) not null,
-> mark int(11) not null
-> );
编辑
内容解析：使用命令语句创建三个表，其中注意添加约束就行
2.添加数据
编辑
编辑
insert into stu values
-> (1,"ZhangPeng","Hebei",13889075861),
-> (2,"LiXiao","Shandong",13953508223),
-> (3,"HangYun","Shandong",13905350996);
insert into sign values
-> (1,"ZhangPeng","High_School1","Peking_University"),
-> (2,"LiXiao","High_School2","Peking_University"),
-> (3,"HuangYun","High_School3","Tsinghua_University");
insert into stu_mark values
-> (1,"ZhangPeng",730),
-> (2,"LiXiao",725),
-> (3,"HuangYun",736);
编辑
编辑
内容解析：给上面创建的表里面添加数据，其中注意区分是个别字段还是所有字段，这样方便选择命令语句，提高效率和保证正确率，以上是所有字段添加数据
3.查看数据
编辑
编辑
4.创建视图
编辑
create view beida as
-> select a.s_id,a.s_name,c.mark,b.s_sign_sch from
-> stu a inner join stu_mark c on a.s_id = c.s_id
-> inner join sign b on c.s_id = b.s_id where
-> c.mark>=720 and
-> b.s_sign_sch ="Peking_University";
编辑
编辑
create view qinghua as
-> select a.s_id,a.s_name,c.mark,b.s_sign_sch from
-> stu a inner join stu_mark c on a.s_id = c.s_id
-> inner join sign b on c.s_id = b.s_id where
-> c.mark>=725 and
-> b.s_sign_sch ="Tsinghua_University";
编辑
编辑
update stu_mark set
-> mark=mark-10
-> where stu_mark.s_name ="HuangYun";
编辑
内容解析：创建视图是为了更加方便的查看数据，其中的命令语句要注意的就是不要漏了as ，还有一个要注意的点，就是各个表之间连接的逻辑要理清楚
