15、数据库应用

create database xscjgldata;
1.系部信息表：
create table department(deptno char(2) primary key,deptname varchar(50) not null);
insert into department values('00','生态建设与环境保护学院'),('01','园林与建筑学院'),('02','工业与设计学院'),('03','商学院'),('04','通讯与信息学院'),('05','机电学院'),('06','旅游与外语学院'),('07','汽车学院'),('08','家具学院');
2.班级表
create table class(classid char(7) primary key,classname varchar(50) not null,deptno char(2) not null,foreign key(deptno) references department(deptno) on update cascade on delete cascade);
insert into class values('04923B1','2023级物联网工程 1班','04'),('04923B2','2023级物联网工程 2班','04'),('04923B3','2023级物联网工程 3班','04'),('04923B4','2023级物联网工程 4班','04');
3.课程信息表
create table course(cid char(6) primary key,cname varchar(30) not null);
insert into course values('049001','数据库原理与技术'),('049002','软件综合开发课程'),('049003','JavaWeb程序设计'),('049004','数据结构'),('049005','Linux操作系统及应用'),('049006','中国近现代史纲要'),('049007','形势与政策'),('049008','计算机网络技术'),('049009','现代传感器技术');
4.教师信息表
create table teacher(tid char(4) primary key,tname varchar(10) not null);
insert into teacher values('0401','张三');
5.学生信息表
create table student(sid char(8) primary key,sname varchar(10) not null,classid char(7) not null,sage datetime not null,ssex enum('男','女') not null,foreign key(classid) references class(classid) on update cascade on delete cascade);
drop table student;
CREATE TABLE `student` (  `sid` char(8) NOT NULL,  `sname` varchar(10) NOT NULL,  `classid` char(7) NOT NULL,  `ssex` enum('男','女') NOT NULL,  PRIMARY KEY (`sid`),  KEY `classid` (`classid`),  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`) ON DELETE CASCADE ON UPDATE CASCADE);
INSERT INTO `student` VALUES('20234658','李四','04923B3','男'));
6.开课信息表
create table tea_course(tid char(4) not null,cid char(6) not null,term varchar(30) not null,classid char(7) not null,primary key(tid,cid,term,classid),foreign key(tid) references teacher(tid) on update cascade on delete cascade,foreign key(cid) references course(cid) on update cascade on delete cascade,foreign key(classid) references class(classid) on update cascade on delete cascade);

7.成绩信息表
create table sc(sid char(8) not null,cid char(6) not null,score float not null,primary key(sid,cid),foreign key(sid) references student(sid) on update cascade on delete cascade,foreign key(cid) references course(cid) on update cascade on delete cascade);

8.备份数据库
C:\Windows\system32>mysqldump -u root -p xscjgldata>d:\db_bak\xscjgldata_bak.sql
9.还原数据库
mysql> create database xscjgldata2;
C:\Windows\system32>mysqldump -u root -p xscjgldata2<d:\db_bak\xscjgldata_bak.sql
C:\Windows\system32>mysqldump -u root -p xscjgldata<d:\db_bak\table_student_sc_bak.sql

select * from sc;
select * from tea_course;
select * from student;

10.视图【虚拟的表】
连接查询：
Select sc.sid as 学号,student.sname as 姓名 ,sc.cid as 课程编号,Course.cname as 课程名称,sc.score as 成绩 from sc,student,course Where sc.sid=student.sid and sc.cid=course.cid;
行转换为列显示：
SELECT sc.sid 学号,student.sname 姓名,student.classid 班级,max(case sc.cid  when '049001' then sc.score else 0 end) 数据库,max(case sc.cid  when '049002' then sc.score else 0 end) 软件综合,max(case sc.cid  when '049003' then sc.score else 0 end) javaweb,max(case sc.cid  when '049004' then sc.score else 0 end) 数据结构,max(case sc.cid  when '049005' then sc.score else 0 end) Linux,max(case sc.cid  when '049006' then sc.score else 0 end) 近现代史,max(case sc.cid  when '049007' then sc.score else 0 end) 形势与政策,max(case sc.cid  when '049008' then sc.score else 0 end) 计算机网络,max(case sc.cid  when '049009' then sc.score else 0 end) 传感器技术 FROM  sc,course,student where sc.sid=student.sid and sc.cid=course.cid group by sc.sid,student.sname,student.classid;
创建视图查询：
create view sc_view as SELECT sc.sid 学号,student.sname 姓名,student.classid 班级,max(case sc.cid  when '049001' then sc.score else 0 end) k1,max(case sc.cid  when '049002' then sc.score else 0 end) k2,max(case sc.cid  when '049003' then sc.score else 0 end) k3,max(case sc.cid  when '049004' then sc.score else 0 end) k4,max(case sc.cid  when '049005' then sc.score else 0 end) k5,max(case sc.cid  when '049006' then sc.score else 0 end) k6,max(case sc.cid  when '049007' then sc.score else 0 end) k7,max(case sc.cid  when '049008' then sc.score else 0 end) k8,max(case sc.cid  when '049009' then sc.score else 0 end) k9 FROM  sc,course,student where sc.sid=student.sid and sc.cid=course.cid group by sc.sid,student.sname,student.classid;
select * from sc_view;
11.创建远程访问的账号“student”，并分配查询sc_view视图的权限
create user student@'%' identified by'chaxun';
grant select on table xscjgldata.sc_view to student@'%';