一. SQL语句
知识点分立：
创建数据库：CREATE DATABASE <database_name>
删除数据库：DROP DATABASE <database_name>
创建表：
CREATE TABLE <table name> (
<attribute name 1> <data type 1>,
...
<attribute name n> <data type n>
PRIMARY KEY(__,__),
FOREIGN KEY(__) REFERENCES <table> (_),
);
删除表：
DROP TABLE <table name>
增加列：
ALTER TABLE <tablename> ADD (column_name datatype);
删除列：
ALTER TABLE table_name DROP column_name;
修改列数据类型：
ALTER TABLE table_name MODIFY column_name datatype;
修改表的约束：
eg1. ALTER TABLE employee ADD CONSTRAINT pk_student PRIMARY KEY (SSN)
eg2. ALTER TABLE student DROP CONSTRAINT pk_student
选择语句整体结构：
SELECT select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
注意：聚合函数不能直接跟在where语句后面，所以得在分组后，使用having语句进行进一步限制；一般search_condition可以表示为 aggregate_function(column_name) operator value。
匹配符：% 匹配多个字符，_匹配单个字符
eg1. SELECT SNAME FROM STUDENT WHERE SNAME LIKE 'a_%c';
eg2. 使用‘20#%’ ESCAPE ‘#’来表示20%
聚合函数：count计数，avg数值平均，max最大值，min最小值，sum数值求和
连接：
inner join：（隐式，内连接，自然连接）
eg. SELECT * FROM student, department WHERE student.DeptID = department.DeptID
outer join：（显式，外连接）
1.left outer join：（左外连接，保留左边独有的列）
eg. SELECT * FROM student LEFT OUTER JOIN department
ON student.DEPTID = department.DEPTID
2.right outer join：（右外连接，保留右边独有的列）
eg. SELECT * FROM student RIGHT OUTER JOIN department
ON student.DEPTID = department. DEPTID;
3.full outer join：（全外连接，保留左右独有的列）
eg. SELECT * FROM student FULL OUTER JOIN student
ON student.DEPTID = department.DEPTID;
集合操作：
1. 并集union
eg. (select SName from student where ssn=any
(select ssn from sc where cno=21001001) )
union
(select SName from students where ssn=any
(select ssn from sc where cno=22003002) )
2. 交集Intersect
eg. (select SName from student where ssn=any
(select ssn from sc where cno=21001001) )
intersect
(select SName from student where ssn=any
(select ssn from sc where cno=22003002) )
3. 差 except/minus
eg. (select SName from student where ssn=any
(select ssn from sc where cno=21001001) )
minus
(select SName from student where ssn=any
(select ssn from sc where cno=22003002) )
注意：此处的“ = any ”实际上和“ in ”是一样的意思，可以相互替换。
子查询（多select语句）：
eg. SELECT e1. SNAME FROM student e1,student e2
WHERE e1. DEPTNO = e2. DEPTNO AND e2. SNAME = 'JONES'
any和all对比：
exists语句 和 not exists：
练笔题目：
1.找出每个学生超过他选课课程平均成绩的课程号.
Select sno,cno From sc x
Where grade>=(select avg(grade) From sc y Where y.sno=x.sno)
2. 找出每个学生超过该选课课程平均成绩的课程号.
Select sno,cno From sc x
Where grade>=(select avg(grade) From sc y Where y.cno=x.cno)
3. Find all students who do not take 21003001.
select * from Student s where not exists
(select * from sc
where ssn= s.ssn
and Cno = '21003001.')
This query is equivalent to:
select * from Student where ssn not in
(select ssn from sc
where Cno = '21003001.')
4. Find the names of all students who study in the same department with a student with sname= ‘liucheng’ .
SELECT ssn，Sname，DEPTNO
FROM Student S1
WHERE EXISTS
SELECT *
FROM Student S2
WHERE S2.DEPTNO = S1.DEPTNO AND
S2.Sname = ‘ liucheng '；
5. Find all the students who take all courses.
select * from Student s where not exists
(select * from Course c where not exists
(select * from sc where ssn= s.ssn and Cno = c.Cno))
注意：此题最重要的是使用双重否定表示肯定，分为全部课程和选的课程相同两部分，对后者进行否定表示选的课程有不同，再从整体进行剔除，表示上了全部课程。
6. Find the names and GPAs of those students who take all courses taken by a student with SID = 2015002001.
select Name, GPA from Student s
where not exists
(select * from Course c where Cno in
(select Cno from sc where ssn= 2015002001)
and not exists
(select * from sc e where ssn=e.ssn and Cno = c.Cno))
7. exists 、not exists扩展练习
Consider relations:
Employees(Eid, Name, Age, Salary)
Projects(Proj_no, Name, Manager_name)
Works(Eid, Proj_no, Start_Date)
（1）Find the names of those employees who participate all projects.
select name from Employees e where not exists
(select * from Projects p where not exists
(select * from Works w
where e.Eid = w.Eid
and w.Proj_no = p.Proj_no))
（2）Find the names of those projects managed by Smith that are participated by all employees under 40.
select name from Projects p where manager_name = ‘Smith’ and not exists
(select * from Employees e where Age < 40 and not exists
(select * from Works w where e.Eid = w.Eid and w.Proj_no = p.Proj_no))
其他补充：
视图：（与游标不同，不占用内存，虚拟表）
CREATE （OR REPLACE）VIEW view_name AS
SELECT column_list
FROM table_name [WHERE condition];
插入操作例子：
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7500, 'CAMPBELL', 'ANALYST', 7566, ‘1992-3-5', 24500,0, 40);
更新操作例子：
UPDATE emp
SET sal = sal* 1.15
WHERE (job = 'ANALYST' OR job = 'CLERK')AND deptno = 20;
删除操作例子：
DELETE FROM emp
WHERE job = 'SALESMAN'
AND comm < 100;
二. 关系代数
练笔：
1. Get supplier names for suppliers who supply part P2:
2. Get supplier names for suppliers who supply at least one red part:
3. Get supplier names for suppliers who supply all parts:
4. Get supplier numbers for suppliers who supply at least all those parts supplied by supplier S2:
----- 以上为本人学习数据库这门课总结出的一些知识点，有错误或者疑问可以评论区交流，欢迎指正！！！