数据库详细复习第三章SQL语句

第三章：SQL语句

1、其他表中的主键，在这个表里做外键，好几个外键的时候，CONSTRAINT不能写在一起，要分开写，因为还有REFERENCES 表（主键），

4. 删除：DROP （列或完整性约束）

ALTER TABLE <表名> DROP COLUMN<列名>；
ALTER TABLE <表名> DROP CONSTRANIT<完整性约束名>；

删除姓名为张亮的学生数据

DELETE FROM Student
WHERE StudentName='张亮';

3.1 SQL概述

3.1.3 SQL 语句类型

1、数据定义语句

用于创建，修改，删除数据库对象的语句

1. 创建数据库：CREATE DATABASE
2. 修改数据库属性:ALTER DATABASE
3. 删除数据库:DROP DATABASE
4. 创建表:CREATE TABLE
5. 修改数据表结构:ALTER TABLE
6. 删除表:DROP TABLE
7. 创建索引:CREATE INDEX
8. 删除索引:DROP INDEX

2、数据操纵语言

1. 向数据库表中插入数据：INSERT
2. 更新数据库表中的数据：UPDATE
3. 从数据库表中删除数据：DELETE

3、数据查询语言

SELECT

4、数据控制语言

对数据库对象访问权进行控制

1. 授予用户对数据库对象的权限：GRANT
2. 拒绝授予用户对数据库对象的权限:DENY
3. 撤销用户对数据库对象的权限:REVOKE

5、事务处理语言

1. 开始事物：BEGIN TRANSACTION
2. 提交事物：COMMIT
3. 回滚事物：ROLLBACK
   

3.1.4 SQL数据类型

1、字符串型

可变长度字符串：varchar(n)
固定长度字符串：char(n)

2、整数型

Int
smallint

3、浮点数型

real:单精度浮点数
double（n,d） 双精度浮点数。eg.DOUBLE(8,2) – 这里的 (8,2) 指定了总共 8 位数，其中小数部分占 2 位

4、货币型

money

5、日期型

date:表示年/月/日

3.2 数据定义语句

3.2.1 数据库的定义

例 将选课管理数据库CourseDB名称修改为 CourseManageDB

ALTER DATABASE CourseDB RENAME TO CourseManageDB;

3.2.2 数据库表对象的定义

一、数据库表创建SQL语句

1、列完整性约束关键词

1. 主键：PRIMARY KEY
2. 非空值：NOT NULL
3. 空值：NULL
4. 值唯一：UNIQUE
5. 有效性检查：CHECK
6. 缺省值：DEFAULT
   

CREATE TABLE Student
(    StudentID  char(13)   PRIMARY KEY,StudentName varchar(10) NOT NULL,StudentGender char(2)  NULL,BirthDay  data  NULL,Major  varchar(30)  NULL,StudentPhone char(11)  NULL
);

列约束关键词：值唯一，有效性检查，缺省值的应用

CREATE TABLE Course
(    CourseID char(4) PRIMARY KEY,CourName varchar(20) NOT NULL UNIQUE,CourseType varchar(10) NULL CHECK(CourseType IN('基础课'，'专业课'，'选修课'），CourseCredit smallint NULL,CoursePeriod smallint NULL,TestMethod varchar(10) NULL DEFAULE '闭卷考试'
);

2、表约束关键词

用来定义由多个列构成的复合主键，primary key只能定义单列主键

CREATE  TABLE  Plan
( CourseID            char(4)          NOT  NULL,TeacherID           char(4)          NOT  NULL,CourseRoom          varchar(30),CourseTime          varchar(30),Note                varchar(50),CONSTRAINT     CoursePlan_PK        PRIMARY Key(CourseID,TeacherID)
);

3、表约束定义代理键

为了方便数据处理，用代理键代替复合主键

CREATE TABLE Plan
( CoursePlanID       serial                   NOT NULL,CourseID           char(4)                  NOT  NULL,TeacherID          char(4)                  NOT  NULL,CourseRoom         varchar(30),CourseTime         varchar(30),Note               varchar(50),CONSTRAINT        CoursePlan_PK        PRIMARY Key(CoursePlanID)

上一个是用课程编号和教师编号复合键来作为主键。
这个是创建了一个属性开课编号，数据类型为自动编号，在最后一行约束

4、表约束定义外键

CONSTRAINT 外键约束名字 FOREIGN KEY (作为外键的列名） REFERENCES 外键来自的表名（列名）
ON DELETE CASCANE
关系表之间存在关联，在一个表中作为主键的列，在另外的关联表中作为外键
是主键的列，如果在其他表中也有这个列，就要做外键

CREATE TABLE Register
(    CourseRegID   serial        NOT NULL,CoursePlanID  int           NOT NULL,StudentID     char(13)      NOT NULL,Note          varchar(30)   NULL,CONSTRAINT    CourseRegID_PK   PRIMARY KEY(CourseRegID),CONSTRAINT    CoursePlanID_FK  FOREIGN KEY(CoursePlanID)  REFERENCES  Plan(CursePlanID) ON DELETE CASCANE,CONSTRAINT    StudentID_PK     FOREIGN KEY (StudentID)  REFERENCES  Student(StudentID)  ON DELETE  CASCANE)

定义了另一个外键约束 StudentID_FK，它关联到当前表中的 StudentID 列，并引用了 Student 表中的 StudentID 列，并且也使用了 ON DELETE CASCADE，表示当 Student 表中与当前表中的 StudentID 列关联的行被删除时，与之相关的行也将被删除。ON DELETE CASCADE: 这是一个关键字，表示当引用表中的行被删除时，与其相关联的行也将被删除
CONSTRAINT 外键约束名字 FOREIGN KEY (作为外键的列名） REFERENCES 外键来自的表名（列名）
ON DELETE CASCANE

二、数据库表修改SQL语句

1、语句类型

1. 增加新列或列完整性约束:ADD
   ALTER TABLE <表名> ADD <新列名称> <数据类型> 或[完整性约束];
2. 删除：DROP （列或完整性约束）
   ALTER TABLE <表名> DROP COLUMN<列名>；
ALTER TABLE <表名> DROP CONSTRANIT<完整性约束名>；
3. 修改表名称，列名称：RENAME TO
   ALTER TABLE <表名> RENAME TO <新表名>;
ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
4. 修改列的数据类型：ALTER
   ALTER TABLE <表名> ALTER COLUMN<列名> TYPE<新的数据类型>;

2、应用

ALTER TABLE Student ADD email varchar(20) ;

三、删除表结构SQL语句

3.2.3 数据表索引对象的定义

1、索引对象创建

CREATE INDEX Birthday_Idx ON Student (Birthday);

2、索引修改

ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;

3、索引删除

3.3 数据操纵语句

3.3.1 数据插入

INSERT INTO <表名> VALUES ('201710098','刘因','女');

3.3.2数据更新

数据更新中要有WHERE条件，否则会更新所有行中该列的值
在学生信息表中Student,“赵东”原有的Email数据为空，现在需要更新为“zhaodong@163.com”，出生日期更新为‘1999-11-15’

UPDATE Student
SET Email='zhaodong@163.com'，Birthday='1999-11-15'
WHERE StudentName='赵东';

3.3.3 数据删除(删除一行）

WHERE条件
删除姓名为张亮的学生数据

DELETE FROM Student
WHERE StudentName='张亮';

3.4 数据查询语句

3.4.1 查询语句基本结构

6种子语句

3.4.2 从单表读取指定列(最简单，因为直接投影）

SELECT studentid,studentname,major
FROM Student;SELECT *
FROM Student;  --查询所有

DISTINCT(distinct清楚的） 用于过滤重复的数据

3.4.3 从单表读取指定行（需要WHERE）

SELECT *
FROM Student
WHERE StudentGender='男';

3.4.4 从单表读取指定列和行

SELECT studentid,studentname,studentgender,major
FROM Student
WHERE studentgender='男';

3.4.5 WHERE子句条件

1、between…and 和通配符 % _

SELECT *
FROM STUDENT
WHERE birthday BETWEEN '2000-01-01' AND '2000-12-30';

通配符% 匹配任意长度的字符，_匹配单个字符

SELECT *
FROM STUDENT
WHERE Email LIKE '%@163.com';

2、逻辑运算符：AND ,OR, NOT连接操作；IN,NOT IN 限定范围

SELECT *
FROM STUDENT
WHERE studentgender='男'  AND major='软件工程';

SELECT *
FROM STUDENT
WHERE Major IN （'计算机应用'）;

要有括号？？？？为什么？？？

3.4.6 查询结果排序：ORDER BY

DESC:降序
ASC：升序

SELECT *
FROM STUDENT
ORDER BY Birthday DESC,StudentName ASC;

3.4.7 内置函数的使用

1、聚合函数

COUNT()统计的是行数

SELECT COUNT(*) AS 学生人数
FROM Student;

SELECT Min(Birthday) AS 最大年龄，Max(Birthday) AS 最小年龄
FROM Student;

3.4.8 查询结果分组处理

SELECT  Major AS 专业，COUNT(StudentID) AS 学生人数
FROM Student
GROUP BY Major;

SELECT 后面都是创建一列
思路，我最后要呈现的是 |专业 | 人数| ，专业怎么来的，Major AS 专业
人数怎么来的，COUNT(StudentID) AS 学生人数

思路：最后要呈现： 专业 |男生人数
需要用HAVING过滤分组后的结果，使用HAVING前需要提前分组，以专业分组！

//我自己写的是错误的
SELECT Major AS 专业，COUNT(StudentID)>2 AS 男生人数
FROM Student
WHERE studentgender='男'; //正确的写法
SELECT Major AS 专业，COUNT（StudentID） AS 男生人数
FROM Student
WHERE studentgender='男';
GROUP BY Major
HAVING COUNT(*)>2

1. SELECT Major AS 专业, COUNT(StudentID) AS 男生人数: 这部分选择了学生的专业（使用 Major 列），并计算了每个专业男生的人数。使用 COUNT(StudentID) 来计算每个专业男生人数，并且将结果列重命名为 男生人数。
2. FROM Student: 这部分指定了查询的来源表是 Student 表。
3. WHERE studentgender=‘男’: 这部分过滤了只选择性别为男性的学生。
4. GROUP BY Major: 这一行对结果进行分组，以便按照专业进行统计男生人数。
5. HAVING COUNT(StudentID) > 2: 这部分限定了只显示男生人数大于2的专业。使用 HAVING 子句来过滤分组后的结果，以确保只显示符合条件的记录。

WHERE和HAVING都是用来限定的
WHERE用来限定列，如性别为男，生日在…和…之间
HAVING用来限定分组后的结果，如分组后的男生数量>2

3.4.9 使用子查询处理多表

思路：最终是找老师名单，要从Teacher表里面找
为什么这么写？
Teacher表里面只有CollegeID，并不知道ID代表哪个学院
在College表里面，有ID,和学院名字这一列
原本可以直接 WHERE CollegeIDName=‘计算机学院’;
但是不知道，所以就想嵌套一下，去找在college表里对应计算机学院的ID

SELECT TeacherID,  TeacherName,  TeacherTitle
FROM Teacher
WHERE CollegeID IN(SELECT CollegeIDFROM CollegeWHERE CollegeIDName='计算机学院';）

3.4.10 使用连接查询多表

思路：输出的表中，有给出的这5列，所以SELECT里面要AS这五种
分别有老师表和学院表，为了好表示，命名为A，B

SELECT B.CollegeIDName AS 学院名称，A.TeacherID AS 教师编号，A.TeacherName AS 教师姓名，A.Teachergender AS 教师性别，A.TeacherTitle AS 教师职称
FROM Teacher AS A,College AS B
WHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName,A.TeacherID;

3.4.11 SQL JOIN…ON 连接（还可以使用，是等价的）

SELECT B.CollegeIDName,A.TeacherID,A.TeacherName,A.Teachergender,A.TeacherTitle
FROM TEACHER AS A JOIN COLLEGE AS B 
ON A.CollegeID=B.CollegeID
ORDER  BY  B.CollegeName, A.TeacherID;

把WHERE替换成了JOIN ON

3.5 数据控制SQL语句

控制用户的访问权限

1、GRANT 授权语句

GEANT SELECT,INSERT,UPDATE,DELETE ON REGISTER TO PoleS;

2、REVOKE 权限回收语句

REVOKE DELETE ON REGISTER FROM PoleS;

3、DENY 权限拒绝语句

DENY DELETE ON TEACHER TO RoleT;

3.6 视图SQL语句

3.6.1 视图的概念

3.6.2 视图的创建与删除

1、视图创建

CREATE VIEW BasicCourse AS
SELECT CourseName,CourseCredit,  CoursePeriod,  TestMethod
FROM COURSE
WHERE CourseType='基础课';

2、查询视图

SELECT *
FROM BasicCourseView
ORDER BY CoueseName;

3、视图删除

3.6.3 视图的使用

1、使用视图进行复杂的SQL查询操作

最后要得到的是学生名单： 课程名称 姓名 学号
就是 SELECT 要的列名
FROM 涉及到了哪些表
WHERE 表中一样的数据全让它相等，然后还要写上挑选的条件

SELECT C.CourseName AS 课程名称，S.StudentID AS 学号，S.StudentName AS 姓名
FROM COURSE AS C,PLAN AS P,REGISTER AS R,STUDENT AS S
WHERE C.CourseID=P.CourseID AND C.Course Name='数据库原理与应用' AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S.StudentID;

目的是：把视图先弄成这样的，所以外部就可以直接查询视图了

CREATE VIEW DatabaseCourseView AS 
SELECT C.CourseName AS 课程名称，S.StudentID AS 学号，S.StudentName AS 姓名
FROM COURSE AS C,PLAN AS P,REGISTER AS R,STUDENT AS S
WHERE C.CourseID=P.CourseID AND C.Course Name='数据库原理与应用' AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S.StudentID;

2、使用视图提高数据访问的安全性

就是创建一个可以被看到的信息的视图，让用户不用去总的数据库里面去查询

CREATE VIEW BasicTeacherInfoView AS
SELECT T.TeacherID AS 编号, T.TeacherName  AS 教师姓名, T.TeacherGender  AS 性别, T. TeacherTitle  AS 职称,  C.CollegeName  AS 所属学院
FROM  TEACHER  AS  T,  COLLEGE  AS  C
WHERE  T.CollegeID=C. CollegeID;

3.7 项目实践

1、创建数据库

CREATE DATABASE ProjectDB;

2、创建各个表

CREATE TABLE Department(
DepartmentCode char(3) PRIMARY KEY,
DepartmentName varchar(30)  NOT NULL,
DepartmentIntro varchar(200) NULL,
。。。。。
）；

CREATE TABLE Employee(
EmployeeID    Serial PRIMARY KEY,
EmlpoyeeName  varchar(10)  NOT NULL,
Gender        char(2)      NULL DEFAULT "男",
Department    char(3)      NULL,
Degree        char(4)      NULL CHECK IN("本科","研究生","其他"),
Birthday       date        NULL,
Phone         char(11)     NULL,
Email         varcher(20)  NULL  UNIQUE
CONSTRAINT    Department_FK  FOREIGN KEY(Department)  
REFERENCES  Department(DepartmentCode) ON DELETE CASCANE
);

CREATE TABLE Project(
ProjectID   serial     NOT NULL,
Department  char(30)   NOT NULL,
.....
CONSTRAINT  ProjectID_PK  PRINMARY KEY(ProgectID),
CONSTRAINT  Department_FK  FOREIGN KEY(Department) 
REFERANCES  Department(DepartmentCode) ON DELETE CASE
);

CREATE TABLE Assignment(
PrejectID    int  NOT NULL,
EmployeeID   int  NOT NULL,
.....
CONSTRAINT   ProgectID_

需要各个项目员工的任务工时列表： 项目名称 | 员工姓名 | 实时工时

SELECT P.ProjectName AS 项目名称,E.EmployeeName AS 员工姓名，A.FinishedHours AS  完成工时
FROM Employee AS E ,Project AS P,Assignment AS A
WHERE E.EployeeID=A.EployeeID AND P.ProjectID=A.ProjectID //注意中间写AND
ORDER BY P.Project,A.EmployeeID; //没有要求，这个一定要写吗？

CREATE VIEW INFORMATION AS
SELECT eployeeID AS 员工编号,eployeeName AS 员工姓名,phone AS 电话,email AS 邮箱
FROM Employee;
查询的时候别忘了AS+中文