数据库复习

Student(Sno,Sname,Ssex,Sage,Sdept)
Course(Cno,Cname,Cpno,Credit)
SC(Sno,Cno,Grade)

Student

Sno(学号)	Sname(姓名)	Sex	Sage	Sdept(所在系)
201215121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215123	王敏	女	18	MA
201215125	张立	男	20	IS

Course

Cno	Cname	Cpno	Ccredit(先修课)
1	数据库	5	4
2	数学		2
3	信息系统	1	4

SC

Sno	Cno	Grade
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

创建数据库

创建表

CREATE TABLE Student 
    (Sno CHAR(9) PRIMARY KEY, /*约束主键*/
    Sname CHAR(20) UNIQUE,  /* Sname取唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20));
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL, /*不能取空值*/
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)) /*Cpno是外码，被参照表的是Course,被参照的列是Cno*/;

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAE(4),
Grade SMALLINT,
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno));

1. 向Student表增加“入学时间”列，其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE;

2. 删除Student表

DROP TABLE Student CASCADE

3. 删除表中某一列

 ALTER TABLE Student  DROP COLUMN Sage;

查询

4. 数据查询

SELECT 目标列 
FROM 表/视图名 AS 别名 
WHERE 条件表达式
GROUP BY 列名 [HAVING 条件表达式] /*按列名分组，该属性列值相等的元组为一个组 通常会在每组中作用聚集函数 加上HAVING短语则按指定条件的组输出*/
ORDER BY 列名 ASC  /*升序*/ DESC  /*降序*/ /*关键字用于对结果集按照一个列或者多个列进行排序*/

5. DISTINCT 消除取值重复的行

SELECT DISTINCT Sno FROM SC

确定范围

• 判断语句
• BETWEEN 低值 AND 高值 在什么范围
• NOT BETWEEN 低值 AND 高值 不在什么范围

确定集合

• IN 查找属性值属于指定集合的元组
• NOT IN

SELECT Sname.Ssex From Student Where Sdept IN('CS','MA')
SELECT Sname.Ssex From Student Where Sdept NOT IN('CS','MA')

字符匹配

LIKE 用来匹配字符串 通配符有%和_

• % 表示任意长度 可以是0个
• _ 表示任意单个字符

SELECT Sname FROM Student WHERE Sname LIKE '_阳%'

涉及空值查询

SELECT Sno,Cno FROM SC WHERE Grade IS NULL

聚集函数

COUNT(列名)	统计元组个数
SUM(列名)	计算列值总和
AVG(列名)	计算列值平均值
MAX(列名)	求一列中的最大值
MIN(列名)	求一列中的最小值

SELECT COUNT(DISTINCT Sno) FROM SC /*查询选修了课程的学生人数*/
SELECT MAX(Grade) FROM SC WHERE Cno='1' /*查询选修1号课程的学生最高分数*/
SELECT SUM(Credit) FROM SC,Course WHERE Sno='201215012' AND SC.Cno = Course.Cno 
/*查询学生选修课程的总学分数*/

7. 查询选修了三门以上课程的学生学号

SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3 
/*先分组再用聚集函数对每一组计数 HAVING 短语给出了选择组的条件，只有满足条件的才会被显示出来*/

连接查询

• 等值连接

SELECT Student.Sname,SC.* FROM Student,SC WHERE Student.Sno = SC.Sno

• 外连接

1. INNER JOIN：如果表中有至少一个匹配，则返回行
   

SELECT column FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name

2. LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
   

SELECT column FROM TABLE1 LEFT JOIN TABLE2 ON table1.column_name = table2.column_name

3. RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
   

SELECT column FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

5. FULL JOIN：只要其中一个表中存在匹配，则返回行
   

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;		

• 多表连接

SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

• 嵌套查询

SELECT Sname FROM Student WHERE Sno IN 
(SELECT Sno FROM SC WHERE Cno='2')  /*上层称为外层查询或父查询  下层陈内查询或子查询*/

• 带有比较运算符的子查询

SELECT Sno,Sname,Sdept FROM Student WHERE Sdept= 
(SELECT Sdept FROM Student WHERE Sname = '刘晨')

• 集合查询(查询结果列数必须相等)

1. 并操作 UNION
2. 交操作 INTERSECT
3. 差操作

/*查询计算机系的学生及年龄不大于19的学生*/
SELECT * FROM Student WHERE Sdept ='CS' UNION SELECT * FROM Student WHERE Sage<=19

插入

INSERT INTO SC(Sno,Cno) VALUES('201215128','1')