哈哈哈哈顶顶顶

最新推荐文章于 2024-06-25 15:18:35 发布

陈住气儿

最新推荐文章于 2024-06-25 15:18:35 发布

阅读量421

点赞数 25

文章标签：数据库 sql oracle

本文链接：https://blog.csdn.net/m0_74134378/article/details/139711068

版权

--数据库
CREATE DATABASE learnwell;
SHOW DATABASES;
USE learnwell;
DROP DATABASE learnwell;
--创建表
USE study;
CREATE TABLE `course` (
`Cno` VARCHAR(20) NOT NULL COMMENT '课程编号',
`cname` VARCHAR(255) DEFAULT NULL COMMENT '名称',
`category` VARCHAR(255) DEFAULT NULL,
`major` VARCHAR(255) DEFAULT NULL,
`startTM` VARCHAR(255) DEFAULT NULL,
`credits` INT DEFAULT NULL,
`weekhours` VARCHAR(255) DEFAULT NULL,
`cpno` INT DEFAULT NULL,
PRIMARY KEY (`Cno`)
) ;
CREATE TABLE `department` (
`Dno` INT NOT NULL COMMENT '院编号',
`Dname` VARCHAR(255) DEFAULT NULL COMMENT '院名称',
`Tno` INT DEFAULT NULL COMMENT '系主任老师编号',
`address` VARCHAR(255) DEFAULT NULL COMMENT '院地址',
PRIMARY KEY (`Dno`),
KEY `d_T` (`Tno`),
CONSTRAINT `d_T` FOREIGN KEY (`Tno`) REFERENCES `teacher` (`Tno`)
) ;
CREATE TABLE `sc` (
`Sno` INT NOT NULL COMMENT '学生学号',
`score` INT DEFAULT NULL COMMENT '成绩',
`Cno` VARCHAR(10) DEFAULT NULL,
KEY `sc_s` (`Sno`),
KEY `sc_c` (`Cno`),
CONSTRAINT `sc_c` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `sc_s` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE `sclass` (
`ClassNo` INT NOT NULL COMMENT '班号',
`className` VARCHAR(255) DEFAULT NULL COMMENT '班级名称',
`major` VARCHAR(255) DEFAULT NULL COMMENT '专业',
`grade` VARCHAR(255) DEFAULT NULL COMMENT '年级',
`number` INT DEFAULT NULL COMMENT '人数',
`advisor` VARCHAR(255) DEFAULT NULL COMMENT '班主任',
`Dno` INT DEFAULT NULL,
PRIMARY KEY (`ClassNo`),
KEY `className` (`className`),
KEY `sc_d` (`Dno`),
CONSTRAINT `sc_d` FOREIGN KEY (`Dno`) REFERENCES `department` (`Dno`)
);
CREATE TABLE `student` (
`Sno` INT NOT NULL COMMENT '学生编号',
`sname` VARCHAR(255) NOT NULL COMMENT '学生姓名',
`ssex` VARCHAR(255) NOT NULL COMMENT '性别',
`sbirth` DATE NOT NULL COMMENT '生日',
`ClassNo` INT DEFAULT NULL COMMENT '班级',
`age` INT DEFAULT NULL,
PRIMARY KEY (`Sno`),
KEY `scno` (`ClassNo`),
CONSTRAINT `scno` FOREIGN KEY (`ClassNo`) REFERENCES `sclass` (`ClassNo`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE `tc` (
`Tno` INT NOT NULL COMMENT '教师编号',
`Cno` VARCHAR(10) DEFAULT NULL COMMENT '课程编号',
`lacation` VARCHAR(255) NOT NULL COMMENT '地址',
`term` VARCHAR(255) DEFAULT NULL COMMENT '授课学期',
PRIMARY KEY (`Tno`),
KEY `tc_c` (`Cno`),
CONSTRAINT `tc-t` FOREIGN KEY (`Tno`) REFERENCES `teacher` (`Tno`),
CONSTRAINT `tc_c` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`)
);
CREATE TABLE `teacher` (
`Tno` INT NOT NULL COMMENT '教师编号',
`Tname` VARCHAR(255) DEFAULT NULL COMMENT '教师姓名',
`Tsex` VARCHAR(255) DEFAULT NULL COMMENT '教师性别',
`Tage` INT DEFAULT NULL COMMENT '教师年龄',
`Tworktime` DATE DEFAULT NULL COMMENT '教师参加工作时间',
`Dno` INT DEFAULT NULL COMMENT '院系',
PRIMARY KEY (`Tno`),
KEY `t_d` (`Dno`),
CONSTRAINT `t_d` FOREIGN KEY (`Dno`) REFERENCES `department` (`Dno`)
);
--由于表格建立完备，删除修改表用测试表格test
CREATE TABLE `test` (
`id` INT NOT NULL,
`tname` VARCHAR(10) DEFAULT NULL,
`tsex` CHAR(2) DEFAULT '男',
`address` VARCHAR(9) NOT NULL,
`phone` INT DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tname` (`tname`)
)
--删除表
DROP test;
--修改表
ALTER TABLE test ADD phone INT ;
ALTER TABLE test ADD CONSTRAINT pk PRIMARY KEY (phone);
--数据插入和修改
INSERT INTO student VALUES('2017210000','张三','男','1998-04-05','2017211130');
INSERT INTO course VALUES(1,'数据库技术,指选','通信工程,第2 学期',2,'2',NULL);
INSERT INTO sc(sno,cno) VALUES('2017210000','1');
UPDATE course SET startTM='每年第2学期' WHERE cno='1';
UPDATE sc SET score = 90 WHERE sno ='2017210000' AND cno =1
DELETE FROM se WHERE sno ='2017210000'AND cno=1;
--索引
--建立索引
CREATE INDEX pk_sc ON sc(sno,cno);
CREATE UNIQUE INDEX usk ON student(sno);
CREATE UNIQUE INDEX uck ON course(cno);
--delete
DROP INDEX pk_sc;
--查询
--(1)
SELECT x.sname,y.score,z.cname
FROM sc AS y
INNER JOIN student AS x ON y.Sno=x.Sno
INNER JOIN course AS z ON z.cno=y.cno
WHERE y.score IS NOT NULL AND y.cno='c1' OR y.cno='c2'AND y.score>=70;
-- y.cno='c1' or y.cno='c2'and y.score>=70 and
--(2)
SELECT sname, YEAR( from_days( datediff( now( ), sbirth))) AS age
FROM student
WHERE sname LIKE '王__';
--(3)
SELECT s.sname,c.cname
FROM sc
INNER JOIN course AS c ON sc.cno=c.cno
INNER JOIN student AS s ON s.sno=sc.sno
WHERE sc.score IS NULL;
--4
SELECT sname, age
FROM student
WHERE ssex='男'AND age>(SELECT avg(age ) FROM student WHERE ssex='女');
--视图
--1
CREATE VIEW s_21101 (Sno,sname,ssex,sbirth,ClassNo)
AS SELECT Sno,sname,ssex,sbirth,ClassNo
FROM student
WHERE ClassNo=(SELECT ClassNo
FROM sclass
WHERE Dno=(SELECT Dno
FROM department
WHERE Dname='aaaa'));
SELECT ClassNo
FROM sclass
WHERE Dno=(SELECT Dno
FROM department
WHERE Dname='aaaa');
SELECT Dno
FROM department
WHERE Dname='aaaa';
--2
CREATE VIEW aa (student.sno,student.sname ,course.cname,sc.score)
AS
SELECT student.sno,student.sname ,course.cname,sc.score
FROM sc
LEFT JOIN student ON student.sno=sc.sno
LEFT JOIN course ON sc.cno=course.cno;
--利用视图查询
SELECT *FROM aa;

陈住气儿

关注

25
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
哈哈哈哈顶顶顶

INSERT INTO student VALUES('2017210000','张三','男','1998-04-05','2017211130');INSERT INTO course VALUES(1,'数据库技术,指选','通信工程,第2 学期',2,'2',NULL);`Tsex` VARCHAR(255) DEFAULT NULL COMMENT '教师性别',`cname` VARCHAR(255) DEFAULT NULL COMMENT '名称',
复制链接

扫一扫