第三章：SQL语句
1、其他表中的主键，在这个表里做外键，好几个外键的时候，CONSTRAINT不能写在一起，要分开写，因为还有REFERENCES 表（主键），
- 删除：DROP （列或完整性约束）
ALTER TABLE <表名> DROP COLUMN<列名>；
ALTER TABLE <表名> DROP CONSTRANIT<完整性约束名>；
删除姓名为张亮的学生数据
DELETE FROM Student
WHERE StudentName='张亮';
3.1 SQL概述
3.1.3 SQL 语句类型
1、数据定义语句
用于创建，修改，删除数据库对象的语句
- 创建数据库：CREATE DATABASE
- 修改数据库属性:ALTER DATABASE
- 删除数据库:DROP DATABASE
- 创建表:CREATE TABLE
- 修改数据表结构:ALTER TABLE
- 删除表:DROP TABLE
- 创建索引:CREATE INDEX
- 删除索引:DROP INDEX
2、数据操纵语言
- 向数据库表中插入数据：INSERT
- 更新数据库表中的数据：UPDATE
- 从数据库表中删除数据：DELETE
3、数据查询语言
SELECT
4、数据控制语言
对数据库对象访问权进行控制
- 授予用户对数据库对象的权限：GRANT
- 拒绝授予用户对数据库对象的权限:DENY
- 撤销用户对数据库对象的权限:REVOKE
5、事务处理语言
- 开始事物：BEGIN TRANSACTION
- 提交事物：COMMIT
- 回滚事物：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、列完整性约束关键词
- 主键：PRIMARY KEY
- 非空值：NOT NULL
- 空值：NULL
- 值唯一：UNIQUE
- 有效性检查：CHECK
- 缺省值：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、语句类型
- 增加新列或列完整性约束:ADD
ALTER TABLE <表名> ADD <新列名称> <数据类型> 或[完整性约束]; - 删除：DROP （列或完整性约束）
ALTER TABLE <表名> DROP COLUMN<列名>；
ALTER TABLE <表名> DROP CONSTRANIT<完整性约束名>； - 修改表名称，列名称：RENAME TO
ALTER TABLE <表名> RENAME TO <新表名>;
ALTER TABLE <表名> RENAME <原列名> TO <新列名>; - 修改列的数据类型：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
- SELECT Major AS 专业, COUNT(StudentID) AS 男生人数: 这部分选择了学生的专业（使用 Major 列），并计算了每个专业男生的人数。使用 COUNT(StudentID) 来计算每个专业男生人数，并且将结果列重命名为 男生人数。
- FROM Student: 这部分指定了查询的来源表是 Student 表。
- WHERE studentgender=‘男’: 这部分过滤了只选择性别为男性的学生。
- GROUP BY Major: 这一行对结果进行分组，以便按照专业进行统计男生人数。
- 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 CollegeID
FROM College
WHERE 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+中文
扫一扫
644
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
