数据库原理与应用

数据库增删改查

//创建数据库
CREATE DATABASE CourseDB
//修改数据库
ALTER DATABASE CourseDB RENAME TO CourseDBNew
//删除数据库
DROP DATABASE CourseDBNew

数据库表增删改查

增

//创建学生表
CREATE TABLE Student
( StudentID char(13) PRIMARY KEY,
 StudentName varchar(10) NOT NULL,
 StudentGender char(2) NULL,
 BirthDay date NULL,
 Major varchar(30) NULL,
 StudentPhone char(11) NULL
);

-----------------------------------------------------
PRIMARY KEY：主键
NOT NULL  ：非空值
NULL :空值
UNIQUE :值唯一
CHECK : 有效值检查
DEFAULT ：缺省值

//创建课程表
CREATE TABLE Course
(
    CourseID char(4) PRIMARY KEY,
    CourseName varchar(20) NOT NULL UNIQUE,
    CourseType varchar(10) NULL CHECK(CourseType IN (“基础课”,“专业课”,“选修课”
)),
    CourseCredit smallint NULL,
    CoursePeriod smallint NULL,
    TestMethod char(4) NULL DEFAULT "闭卷考试"
)；

//创建课程计划表
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)
);

//表约束定义主键
CREATE TABLE  <表名>
 （ <列名1>  <数据类型>  [列完整性约束]，
	<列名2>  <数据类型>   [列完整性约束]，	
    <列名3>  <数据类型>  [列完整性约束]，
    …
    CONSTRAINT  <约束名>  PRIMARY Key(主键列)
   )；

//表约束定义代理键
CREATE TABLE  <表名>
 （ <代理键列名>  <Serial数据类型>  NOT NULL，
	<列名2>  <数据类型>  [列完整性约束]，	
    <列名3>  <数据类型>  [列完整性约束]，
    …
    CONSTRAINT  <约束名>  PRIMARY Key(代理键列名)
   )；

//表约束定义外键
CREATE TABLE  <表名>
 （ <列名1>  <数据类型>  [列完整性约束]，
	<列名2>  <数据类型>   [列完整性约束]，	
    <列名3>  <数据类型>  [列完整性约束]，
    …
    CONSTRAINT  <约束名>  FOREIGN Key(外键列)
   )；

//创建注册表
CREATE TABLE Register
(
CourseRegID serial NOT NULL,
CoursePlanID Int NOT NULL ,
StudentID char(13),
Note varchar(30),
CONSTRAINT Regiser_PK PRIMARY KEY(CourseRegID),
CONSTRAINT Plan_FK FOREIGN KEY(CoursePlanID)
    REFERENCES Plan(CoursePlanID) 
    ON DELETE CASCADE,
CONSTRAint Student_FK FOREIGN KEY(StudentID)
    REFERENCES Student(StudentID)
    ON DELETE CASCADE,
);

改：

//修改数据库表
ALTER TABLE <表名> <修改方式>;
1）ADD修改方式，用于增加新列或列完整性约束
ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束]
2）DROP修改方式，用于删除指定列或列的完整性约束条件
ALTER TABLE<表名> DROP  COLUMN <列名>；
ALTER TABLE<表名> DROP  CONSTRAINT<完整性约束名>；
3）RENAME修改方式，用于修改表名称、列名称
ALTER TABLE <表名> RENAME TO <新表名>；
ALTER TABLE <表名> RENAME <原列名> TO <新列名>；
4）ALTER修改方式，用于修改列的数据类型
ALTER TABLE <表名> ALTER  COLUMN <列名> TYPE<新的数据类型>；

ALTER TABLE Student ADD email varchar(10);

删：

DROP TABLE <表名>;
注意: 该语句将删除该表的所有数据及其结构

DROP TABLE Register

数据库索引创建、修改、删除

创建

CREATE INDEX  <索引名>  ON <表名><（列名）>;

//实例
CREATE INDEX Birthday_idx ON STUDENT (Birthday);

修改

ALTER  INDEX  <索引名> <修改项>;
//例如
ALTER  INDEX  <索引名> RENAME TO <新索引名>;

//修改实例
//例 在学生信息表Student中，将原索引Birthday_Idx更名为Bday_Idx，其索引修改SQL语句如下：
ALTER  INDEX Birthday_Idx  RENAME TO Bday_Idx;

删除

DROP  INDEX  <索引名> ;

//索引删除实例
DROP INDEX bday_idx;

数据操纵SQL语句

插入语句

INSERT  INTO  <表名|视图名>[<列名表>]  VALUES （列值表）;

//实例
INSERT INTO Student VALUES('2022090916018','pluto','男','2005-01-26','软件工程','[email protected]');

//多条数据插入
INSERT INTO Student VALUES('2017220101106','张亮','男','1999-11-21','软件工程','[email protected]');
INSERT INTO Student VALUES('2017220101107','谢云','男','1999-08-12','软件工程','[email protected]');
INSERT INTO Student VALUES('2017220101108','刘亚','女','1999-06-20','软件工程',NULL);

更新语句

UPDATE  <表名|视图名>
SET  <列名1>=<表达式1> [，<列名2>=<表达式2>...]
[WHERE   <条件表达式>]；

//实例
UPDATE Student SET Email = '[email protected]' WHERE StudentName = 'pluto';

删除语句

DELETE 
FROM  <表名|视图名>
[WHERE   <条件表达式>]；

//实例
DELETE FROM Student WHERE StudentName = '张三';

数据查询SQL语句

SELECT  [ALL|DISTINCT]  <目标列>[，<目标列>…]
[ INTO <新表> ]
FROM  <表名|视图名>[，<表名|视图名>…]
[ WHERE  <条件表达式> ]
[ GROUP BY  <列名> [HAVING <条件表达式> ]]
[ ORDER BY  <列名> [ ASC | DESC ] ];

看PPT吧。过于多了

内置函数

典型SQL内置函数类型如下：
聚合函数
算术函数
字符串函数
日期时间函数
数据类型转换函数

聚合函数

聚合函数	功能
AVG（）	计算结果集指定列数据的平均值
COUNT（）	计算结果集行数
MIN（）	找出结果集指定列数据的最小值
MAX（）	找出结果集指定列数据的最大值
SUM（）	计算结果集指定列数据的总和

看PPT吧。过于多了

多表查询和子查询

子查询SQL语句基本格式：
SELECT    <目标列>[，<目标列>…]
FROM  <表名>
WHERE  <条件中嵌套另一关系表的SELECT 查询结果集>

该操作需要关联教师信息表Teacher和学院信息表College，才能获得这些数据。这里可采用子查询方法实现两表关联查询，其查询SQL语句如下：

//实例
SELECT  TeacherID,  TeacherName,  TeacherTitle
FROM  Teacher
WHERE  CollegeID  IN
        (SELECT  CollegeID  
     FROM  College
     WHERE  CollegeName=’计算机学院’);

连接关联多表查询SQL语句基本格式：
SELECT    <目标列>[，<目标列>…]
FROM  <表名1>，<表名2>，…, <表名n>，
WHERE  <关系表之间的连接关联条件>

//实例
例 在选课管理系统数据库中，希望获得各个学院的教师信息列表，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

SELECT  B.CollegeName 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;

JOIN …ON连接查询语句
在SQL语言中，实现多表连接关联查询还可以使用JOIN…ON关键词的语句格式。其中两表连接关联查询的JOIN…ON语句格式如下：

SELECT  <目标列>[，<目标列>…]
FROM  <表名1>  JOIN  <表名2>  ON <连接条件>;

例 在选课管理系统数据库中，希望获得各个学院的教师信息，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

实例
SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM  TEACHER  AS  A  JOIN  COLLEGE  AS  B
ON  A.CollegeID=B.CollegeID 
ORDER  BY  B.CollegeName, A.TeacherID;

在SQL 应用中，有时候也希望输出那些不满足连接条件的元组数据。这时，可使用JOIN…ON外连接方式实现。其实现方式有三种形式，具体如下：

LEFT JOIN: 左外连接，即使没有与右表关联列值匹配，也从左表返回所有的行。
RIGHT JOIN: 右外连接，即使没有与左表关联列值匹配，也从右表返回所有的行。
FULL JOIN: 全外连接，同时进行左连接和右连接，就返回所有行。

数据控制SQL语句

//GRANT 权限授予语句
GRANT  <权限列表>  ON  <数据库对象>  TO  <用户或角色> [ WITH GRANT OPTION ]；

//实例
//例 在选课管理系统数据库中，将课程注册表REGISTER的数据插入、数据修改、数据删除、数据查询访问权限赋予学生角色RoleS。
GRANT  SELECT, INSERT, UPDATE, DELETE  ON  REGISTER  TO  RoleS;

//REVOKE 权限收回语句
REVOKE语句是一种由数据库对象创建者或管理员将赋予其它用户或角色的权限进行收回语句，它可以收回原授予给其他用户或角色的权限。

REVOKE  <权限列表>  ON  <数据库对象>  FROM  <用户或角色> ;

//实例
//例 在选课管理系统数据库中，收回学生角色RoleS在课程注册表REGISTER的数据删除访问权限。
REVOKE  DELETE  ON  REGISTER  FROM  RoleS;

//DENY权限拒绝语句
//DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

DENY  <权限列表>  ON  <数据库对象>  TO  <用户或角色> ;

//实例
//例 在选课管理系统数据库中，若拒绝教师角色RoleT对教师表TEACHER的数据删除访问权限。
DENY  DELETE  ON  TEACHER  TO  RoleT;

视图SQL语句

创建视图

CREATE  VIEW  <视图名>[(列名1)，(列名2)，…]  AS  <SELECT查询>；

//实例
//例 在选课管理系统数据库中，若需要建立一个查看基础课数据的视图BasicCourseView，其创建SQL语句如下。

CREATE  VIEW  BasicCourseView  AS
SELECT  CourseName,  CourseCredit,  CoursePeriod,  TestMethod
FROM    COURSE
WHERE  CourseType=‘基础课’;

当视图在数据库中创建后，用户可以像访问关系表一样去操作访问视图。

例 使用SELECT语句查询该视图数据，并按课程名称排序输出，其SQL语句如下：

SELECT   *
FROM  BasicCourseView
ORDER  BY  CourseName;

删除视图

DROP  VIEW  <视图名>；
其中DROP VIEW 为删除视图语句的关键词。<视图名>为将被删除的视图名称。

//实例
例 在数据库中，若需要删除名称为BasicCourseView的视图对象，其删除该视图的SQL语句如下：
DROP  VIEW  BasicCourseView;

视图应用

1.数据库开发人员可以将复杂的SQL查询语句封装在视图内，外部程序只需要使用简单的视图访问方式，便可获取所需要的数据。

2.通过视图可以将数据表中敏感数据隐藏起来，外部用户无法得知数据表的完整数据，降低数据库被攻击的风险。此外，还可以保护用户隐私数据。

3.提供一定程度的数据逻辑独立性。通过视图，可提供一定程度的数据逻辑独立性。当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改。

4.集中展示用户所感兴趣的特定数据。通过视图，可以将部分用户不关心的数据进行过滤，仅仅提供他们所感兴趣的数据。

数据库增删改查#

数据库表增删改查#

增#

改：#

删：#

数据库索引创建、修改、删除#

创建#

修改#

删除#

数据操纵SQL语句#

插入语句#

更新语句#

删除语句#

数据查询SQL语句#

内置函数#

多表查询和子查询#

数据控制SQL语句#

视图SQL语句#

创建视图#

删除视图#

视图应用#