SQL数据库操作与学习笔记-优快云博客

本文链接：https://blog.youkuaiyun.com/Jack_williamjbcl/article/details/115460914

1，把学习的东西在这里记录，以后也方便自己回想，纯粹是当成网上笔记，若能帮到一些网友，我也是十分乐意的。

2，

在这里插入图片描述

CREATE DATABASE study2;
USE study;
CREATE TABLE S
(	Sno VARCHAR(5) ,
    Sname VARCHAR(3),
    Ssex VARCHAR(1),
	Sage NUMERIC(2,0),
    Sdept VARCHAR(10),
    PRIMARY KEY(Sno)
);
CREATE TABLE SC(
	Sno VARCHAR(5) ,
    Cno VARCHAR(1),
    Grade NUMERIC(2,0),
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES S(Sno)
);
CREATE TABLE C(
    Cno VARCHAR(1),
    Cname VARCHAR(20),
    Cpno VARCHAR(1),
    Ccredit NUMERIC(1,0),
    PRIMARY KEY(Cno)
);
SET SQL_SAFE_UPDATES = 0;
INSERT INTO S VALUES('95001','李勇','男',20 ,'CS');
INSERT INTO S VALUES('95002','刘晨','女',19 ,'IS');
INSERT INTO S VALUES('95003','王丽','女',18 ,'MA');
INSERT INTO S VALUES('95004','刘军','男',19 ,'IS');
INSERT INTO Sc VALUES('95001','1',92 );
INSERT INTO Sc VALUES('95001','2',85 );
INSERT INTO Sc VALUES('95001','3',88);
INSERT INTO Sc VALUES('95002','2',90);
INSERT INTO Sc VALUES('95002','3',80 );
INSERT INTO c VALUES('1','数据库','5',4 );
INSERT INTO c VALUES('2','数学',NULL,2 );
INSERT INTO c VALUES('3','信息系统','1',4 );
INSERT INTO c VALUES('4','操作系统','6',3);
INSERT INTO c VALUES('5','数据结构','7',4 );
INSERT INTO c VALUES('6','计算机导论',NULL,2);
INSERT INTO c VALUES('7','C语言','6',4 );


SELECT sno,sname FROM s;
SELECT *FROM c;
SELECT sname,sage FROM s WHERE sdept='is' AND ssex='女';
SELECT sname,cno FROM s NATURAL JOIN sc WHERE ssex='男';
SELECT sname,cname FROM s NATURAL JOIN sc NATURAL JOIN c;
SELECT C.cname AS '课程',cc.cname AS '先修课程' FROM c,c AS cc WHERE c.cpno=cc.cno;
SELECT  DISTINCT sno FROM sc;
SELECT sno FROM s WHERE sname LIKE '李_';
SELECT cno,cname FROM c WHERE cname LIKE '%据%';
SELECT * FROM c ORDER BY ccredit DESC,cno ASC;

在这里插入图片描述
|

在这里插入图片描述

-- 3.1
select title from course where dept_name='Comp. Sci.' and credits=3;
select distinct student.id from (teaches natural join instructor)   join (student natural join takes) where instructor.name='Einstein';
select max(salary) from instructor;
select id,name from instructor where salary=(select max(salary) from instructor);
select course_id,sec_id,count(id) from takes where year='2009'and semester='fall'  group by course_id,sec_id;
select max(cnt) from( select count(id) as cnt from takes where year='2009'and semester='fall'  group by course_id,sec_id) as a;
with a as(select course_id,sec_id,count(id) as cnt from takes where year='2009'and semester='fall'  group by course_id,sec_id)
select course_id,sec_id from a where (select max(cnt) from a)=cnt;
-- 3.11
with a as(select * from course natural join section natural  join takes where dept_name='Comp. Sci.')
select distinct name from student,a where student.id=a.id;
with a as(select * from course natural join section natural  join takes)
select name  from student where name not in(select name from student,a where student.id=a.id and year<2009);
select max(salary) as most,dept_name from instructor group by dept_name;
with a as(select max(salary) as most,dept_name from instructor group by dept_name) select * from a where most=(select min(most) from a)

知识点
一，ER图
1，这里讲的是映射基数
在这里插入图片描述

2，双线是强（完全参与），单线是弱（部分参与）

作业6，实验三
执行作业附件"uni.sql"中的语句，创建数
据库uni的完整模式，并插入相应数据。（注
意：如果之前已建uni数据库，需要先drop
它）
 在这个uni数据库中，完成P111的3.12题
要求的更新语句，并依次执行它们。每执行
一个更新语句，就针对被更新的关系表，查
询其完整内容，并且全部都按course_id升序
排列，便于观察比对。然后对查询结果进行
截屏，保存为图片。
`

任务2
执行作业附件"emp.sql"中的语句，
创建数据库emp的完整模式，并插入相
应数据。观察emp数据库的结构。
在这个emp数据库中，完成P112的
3.17题要求的更新语句，并依次执行它
们。
最后在这个emp数据库中执行语句：
select * from works;
对执行的结果截屏，保存为图片。
任务 3
逐个执行"join.sql"中的语句，熟悉
不同的连接运算，注意注释文字。
任务 4
逐个执行"view.sql"中的语句，理解
每个命令的作用，注意注释文字。
将3.12和3.17题的 SQL 语句都保存到
一个文本文件中（可以用记事本打开的
文件），用注释语句（"/…/"）标
上任务号和题号；
将所有截屏保存的图片标上题号，依次
保存到一个word文件中；
将一个文本文件和一个word文件都提
交到对分易。

这个作业让我头疼的地方就是对应麻烦。

312
select * from course natural join section natural join takes;
insert into course values('CS-001','Weekly Seminar',null,0);
insert into section values('CS-001', '1', 'Fall', 2009, null, null, null);
insert into takes select id,'CS-001', '1', 'Fall', 2009,null from student where dept_name='Comp. Sci.';
delete from takes where course_id='CS-001' and id in (select id from student where name='Chavez');
delete from takes where course_id in (select course_id from course where title like '%database%');
select * from course order by course_id

317
update works set salary=salary*1.1 where company_name='First Bank Corporation';
update works set salary=salary*1.1 where company_name='First Bank Corporation' and employee_name in (select manager_name from manages);
delete from works where company_name='Small Bank Corporation';
select* from works



实验四
//1鐨勭瓟妗�
SELECT dept_name,COUNT(DISTINCT NAME) AS instr_count
FROM department NATURAL JOIN instructor
GROUP BY dept_name;


//2鐨勭瓟妗�
CREATE VIEW failed AS SELECT ID,course_id,sec_id,semester,YEAR,grade FROM takes;

//3
SELECT id FROM failed WHERE grade='F' OR grade IS NULL GROUP BY id HAVING COUNT(id)>=2;

//4*
CREATE VIEW tot_credits AS WITH aa(YEAR,credits,course_id) AS (SELECT DISTINCT YEAR,course_id,credits FROM course NATURAL JOIN takes)
SELECT DISTINCT YEAR,SUM(credits) AS  num_credits FROM aa GROUP BY YEAR;
SELECT * FROM tot_credits;

--  5
 CREATE TABLE GPA
	(grade			VARCHAR(1),
	 POINT			NUMERIC(2,0),
	 PRIMARY KEY (grade)
	);
INSERT INTO GPA VALUES ('A',10);
INSERT INTO GPA VALUES ('B',8);
INSERT INTO GPA VALUES ('C',6);
INSERT INTO GPA VALUES ('D',4);
INSERT INTO GPA VALUES ('F',0);
-- 6
SELECT id,SUM(POINT) AS sum_point FROM student  NATURAL LEFT OUTER JOIN takes NATURAL LEFT OUTER JOIN gpa GROUP BY id;
-- 7
CREATE USER '20191003147' IDENTIFIED BY '123';
GRANT SELECT ON tot_credits TO '20191003147';
/*鍒锋柊MySQL鐨勭郴缁熸潈闄愮浉鍏宠〃*/
FLUSH PRIVILEGES;
/*鍐嶆煡璇1鐨勬潈闄�*/
SHOW GRANTS FOR '20191003147';

；实验五
//1鐨勭瓟妗�
SELECT dept_name,COUNT(DISTINCT NAME) AS instr_count
FROM department NATURAL JOIN instructor
GROUP BY dept_name;

SET GLOBAL log_bin_trust_function_creators=TRUE;
-- 1
DELIMITER //
CREATE FUNCTION func_dept_count2(d_name VARCHAR(20))
RETURNS INT
BEGIN
	DECLARE d_count INT;
	SELECT SUM(salary) INTO d_count FROM instructor WHERE instructor.dept_name = d_name;
	RETURN d_count;
END;
//
DELIMITER ;
SELECT func_dept_count2("finance");
-- 2
DELIMITER //
CREATE PROCEDURE proc_tot_sal2(IN dept_name VARCHAR(20),OUT ss INTEGER)
BEGIN
	SELECT SUM(salary)  FROM instructor WHERE instructor.dept_name=dept_name;
END//
DELIMITER ;
CALL proc_tot_sal2('Comp. Sci.',@ss);

-- 3
DELIMITER 
CREATE TRIGGER trig_budget AFTER INSERT ON instructor FOR EACH ROW
BEGIN
    IF(new.salary)
    THEN
	UPDATE department SET budget=budget+new.salary WHERE dept_name=new.dept_name;
END IF;
END		;
INSERT INTO instructor VALUES ('98778', 'Pig', 'Elec. Eng.', 40000);
INSERT INTO instructor VALUES ('98998', 'Fox', 'Comp. Sci.',NULL );
-- DROP TRIGGER trig_budget鐢ㄨ繖涓潵鍒犻櫎瑙﹀彂鍣�;
SELECT * FROM instructor;

数据库的笔记