MYSQL实验二报告

实验要求：

控制台语句：

create database wangmouren default charset utf8mb4;use wangmouren；create table IF NOT EXISTS student(sno char(20) comment '学号',sname varchar(20) comment '姓名',ssex char(2) comment '性别',sdept varchar(20) comment '院系',sbirthday date not null comment '出生日期')comment '学生表';alter table student add primary key (sno);insert into student values('10086','王某人1','男','软件系','2003-03-07'),('10085','王某人','女','软件系','2023-09-20');select * from student;create table IF NOT EXISTS course (cno char(20) comment '课程号',cname varchar(20) comment '课程名',cpno char(20) comment '先行课',credit int comment '课程学分',primary key(cno) ) comment '课程表';insert into course values('1001','数据库系统','0321',4),('9857','电影影评','1333',1);select * from course;create table sc (sno char(20) comment '学号',cno char(25) comment '课程号',grade int comment '成绩',primary key(sno,cno))comment '课学生选表';insert into sc values('10086','1001',90),('10085','9857',95);select * from sc;insert into student values ('121002','李佳慧','女','计算机系','2001-07-02'),('121001','刘鹏翔','男','计算机系','2000-02-15'),('121004','周仁超','男','计算机系','1999-03-02'),('124001','林琴','女','通信学院','2004-02-23'),('124002','杨春容','女','通信学院','1999-10-05'),('124003','徐良成','男','通信学院','2001-03-18'),('124004','刘良成','男','通信学院','2000-12-12'),('121005','王小红','女','计算机系','2001-12-01'),('121006','刘晨','男','计算机系','2003-02-14');insert into course (cno,cname,cpno,credit) values ('1004','数据库系统','1024',4),('1012','计算机网络','4002',3),('4002','数字电路','8001',3),('8001','高等数学',null,6),('1201','英语',null,5),('1204','程序设计基础','8001',3);insert into sc values ('121001','1004',92),('121002','1004',85),('121004','1004',56),('124001','4002',34),('124002','4002',74),('124003','4002',87),('121001','8001',94),('121002','8001',32),('121004','8001',81),('124001','8001',58),('124002','8001',73),('124003','8001',21),('121001','1201',93),('121002','1201',67),('121004','1201',63),('124001','1201',92),('124002','1201',null),('124003','1201',86),('121002','1204',50),('121001','1204',null),('121004',1204,90),('124001',1204,89),('121006',1204,78),('121005',1012,68);update student set sbirthday='2000-01-01' where sname Like '刘%';select * from student; update sc set grade=0 where sno Like '121___';select * from sc;delete from student where sname Like '王%'&& sdept='计算机系';select * from student;//基本查询select * from student where ssex='女'&& sdept='通信学院';select * from student  where sname Like '刘%';select sdept,count(*) from student group by sdept;select sno,avg(grade) from sc group by sno having avg(grade)>=80;select a.sno,a.cno,b.cno from sc a,sc b where (a.cno='8001' && b.cno='1004')&&(a.sno=b.sno);select distinct sno from sc where cno='8001'||cno='1004' group by sno;select sno from sc group by sno having avg(grade)<=80&& count(*)=1;select sno,count(grade) from sc where grade<60 group by sno having count(grade)>1;select s.sname,sc.cno,sc.grade from student s,sc where sc.sno=s.sno;select s.sno,s.sname,c.cname,sc.grade from student s,course c,sc where sc.sno=s.sno&&c.cno=sc.cno;select s.sno,s.sname,c.cname from student s,course c,sc where sc.cno='4002'&&c.cno='4002'&&s.sno=sc.sno;select s.sno,sname,ssex,sdept,sbirthday,cno,grade from student s,sc where sc.sno=s.sno;select a.cno,b.cpno from course a,course b where a.cpno=b.cno;select * from student s left outer join sc on s.sno=sc.sno;select sname from student where sdept=(select sdept from student where sname='刘晨');select sno,avg(grade) from sc group by sno having avg(grade)>(select avg(grade) from student s,sc where s.sname='杨春荣'&&s.sno=sc.sno);select * from student where sdept!='计算机系'&&sbirthday>(select min(sbirthday) from student where sdept='计算机系');select *,(select DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),student.sbirthday)),'%Y'+0)as age from student where sdept!='计算机系'&&sbirthday>(select max(sbirthday) from student where sdept='计算机系');select * from student sno not in(select sc.sno from course c,sc where c.cname='英语'&&c.cno=sc.cno);select s.*,sc.grade from student s,sc where  s.sno not in(select sno from sc where grade is null)&&s.sno=sc.sno;select s.sno,s.name from student s where s.sno in(select sno from sc group by sno having count(*)>=2);//视图部分create or replace view computer_view as select * from student where sdept='计算机系';create or replace view computer_view as select * from student where sdept='计算机系' with cascaded check option;create or replace view computer_view1 as select s.*,sc.cno from student s,sc where s.sdept='计算机系'&&s.sno=sc.sno&&sc.cno='1004';create or replace view computer_view2 as select s.*,avg(sc.grade) from student s,sc where s.sno=sc.sno group by group by sc.sno;select * from computer_view where ssex='男';select cv.*,sc.cno from computer_view cv,sc where sc.cno='1201'&&sc.sno=cv.sno;create or replace view computer_view3 as select s.*,sc.cno from student s,sc where sdept='计算机系'&&sc.cno='1004'&&sc.sno=s.sno;update computer_view set sname='王某人1' where sno='121001';insert into computer_view values ('_10085','王某人','男','计算机系','2023-09-20');delete from computer_view where sno='_10085';

完成收工，good！

虽然“天不生无用之人，地不长无名之草”。但仍谨记“冰冻三尺，非一日之寒”。

执长剑纵马，执妙笔生花，我王某人在此邀请诸位与我共身！

来源地址：https://blog.csdn.net/weixin_73692078/article/details/130246676