MYSQL第十一次作业

1.创建数据库ts_chapter;

 

 

 create table stu
    -> (
    -> s_id int(11) primary key,
    -> s_name varchar(20) not null,
    -> addr varchar(50)  not null,
    -> tel varchar(50)  not null
    -> ); 

2.

 insert into stu
    -> values(1,'zhangpeng','hebei','13889075861'),
    -> (2,'LiXiao','Shandong','13953508223'),
    -> (3,'HuangYun','Shandong','13905350996');

3. 

 

select * from stu; 

 

4. 

 

 

 create table sign
    -> (
    -> s_id int(11)  primary key,
    ->  s_name varchar(20) not null,
    -> s_sch varchar(50)  not null,
    -> s_sign_sch  varchar(50)  not null
    -> );

5.  insert into sign
    ->  values(1,'ZhangPeng','High Schoo11','Peking University'),
    -> (2,'LiXiao','High Schoo12','Peking University'),
    ->  (3,'HuangYun','High  Schoo13','Tsinghua University');

6. 

  select*from sign;

7. 

 create table stu_mark
    -> (
    ->  s_id int(11)  primary key,
    ->   s_name varchar(20) not null,
    -> mark int not null
    -> );

 

8. 

 insert into stu_mark values
    ->  ("1","ZhangPeng","730"),
    -> ("2","LiXiao","725"),
    -> ("3","HuangYun","736");

 

9. 

 

select*from stu_mark;

 

10. 

 create view baida(id,name,mark,sch)as
    -> select  stu_mark. s_id,stu_mark. s_name,stu_mark.mark,sign.s_sign_sch
    -> from  stu_mark,sign
    -> where  stu_mark .s_id=sign.s_id
    -> and stu_mark.mark>=720
    ->  and sign.s_sign_sch='Peking University';

11. 

 

12. 

 create  view qinghua(id,name,mark,sch)as
    -> select  stu_mark. s_id,stu_mark. s_name,stu_mark.mark,sign.s_sign_sch
    ->  from  stu_mark,sign
    ->  where  stu_mark .s_id=sign.s_id
    -> and stu_mark.mark>=725
    ->  and sign.s_sign_sch='Tsinghua University';

 

 

 13.

 update stu_mark set mark=mark-10  where  stu_mark. s_name='HuangYun';

14. 

 

15.