安徽工程大学数据库实验四，SQL的数据更新

我是蒟蒻，觉得不对的可以直接指出，想要的可以自取，最好点个赞hh

首先把四张表格Cj Student Course Class的信息插入

数据库的名字是test

create database test;
use test;
create table Student
( Sno char(7) not null primary key,
  Sname char(10)not null,
  Ssex char(2) not null check(Ssex='男' or Ssex='女') default('男'),
  Sage smallint check(Sage>=14 and Sage<=65),
  Clno char(5) not null,
  );
  create table Course
  ( Cno char(1) not null primary key,
    Cname char(20) not null,
	Credit smallint check(Credit>=1 and Credit<=6)
	)
	create table Class
	( Clno char(5) not null primary key,
	Speciality char(20) not null,
	Inyear datetime not null,
	Number smallint not null check(Number>=1 and Number<=60),
	Monitor char(7)
	)
	create table Cj
	(Sno char(7) not null,
	Cno char(1),
	Grade decimal(4,1) check(Grade>0 and Grade<100),
	primary key(Sno,Cno)
	);
	alter table Course add unique(Cname);
	insert into Student values
	 ('2000101','李勇','男',20,'00311'),
	 ('2000102','刘诗曼','女',19,'00311'),
	 ('2000103','王一鸣','男',20,'00311'),
	 ('2000104','张婷婷','女',21,'00311'),
	 ('2001101','李勇敏','女',19,'00311'),
	 ('2001102','贾阿东','男',22,'00311'),
	 ('2001103','陈宝玉','男',20,'00311');
	 insert into Course values
	  ('1','数据库',4),
	  ('2','离散数学',3),
	  ('3','管理信息系统',2),
	  ('4','操作系统',4),
	  ('5','数据结构',4),
	  ('6','数据处理',2),
	  ('7','C语言',4);
	  insert into Class values
	  ('00311','计算机软件','2000-01-01',45,'2000101'),
	  ('00312','计算机应用','2000-01-01',42,'2000103'),
	  ('01311','计算机软件','2000-01-01',39,'2000103');
	  insert into Cj values 
	  ('2000101','1',92),
	   ('2000101','3',88),
	    ('2000101','5',86),
		 ('2000102','1',78),
		  ('2000102','6',55),
		   ('2001101','2',70),
		    ('2001101','4',65),
			 ('2001102','2',80),
			  ('2001102','4',90),
			   ('2001102','6',83);

1. 使用insert命令向Student表种插入一条新的学生记录（2000105，刘辉，男，20，00311）

2. insert into Student values ('2000105','刘辉','男',20,'00311');

3. 使用insert命令向Student表中从插入一条新的学生纪录（2001105，范冰冰，女，01311）。

4. insert into Student values ('2001105','范冰冰','女',20,'01311');

5. 范冰冰同学，“数据库“考了85分，”管理信息系统“考了92分，将两门课成绩登记到数据库中。

6. insert into Cj values ('2001105','1',85);
   insert into Cj values ('2001105','3',85);
   

7. 将学号为“2000105“的学生的班级号改为”01312”。

8. update Student set Clno='01312' where Sno='2000105';

9. 将选秀“6”号课程的学生成绩加5分。

10. update Cj set Grade+=5 where Cno='6';

11. 删除学号为“2000105”的学生记录。

12. delete from Student where Sno='2000105';

13. 删除学分为2分的所有课程记录。

14. delete from Course where Credit=2;