Database Programming

发表于2020-06-07更新于2020-07-03字数统计2.9k阅读时长24分阅读次数

数据定义SQL语句

基本数据类型

  1. 字符串型varchar(n)、char(n)

varchar长度可变、char固定长度

  1. 整数型int、smallint
  2. 定点数型numerica(p,d)

numeric(p,d)为定点数，p 为定点数的总位数，d 为定点数的小数位数。该数据类型可以表示带小数的数值。

  1. 浮点数型real、double(n,d)
  2. 货币型money
  3. 逻辑性bit
  4. 日期型date

数据库定义

创建数据库

1
2
CREATE DATABASE <NAME>;
> CERATE DATABASE HR;

修改数据库

1
2
ALTER DATABASE <NAME>[[WITH] option[...]];
> ALTER DATABASE demoDB RENAME TO <MyDemoDB>;

删除数据库

1
2
DROP DATABASE <NAME>;
> DROP DATABASE demoDB;

数据库表对象的定义

数据库表创建

1
2
3
4
5
6
7
8
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 
）;
  1. 列约束关键词
1
2
3
4
5
6
7
8
9
10
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(10)　　NOT　NULL　DEFAULT　’闭卷考试’ 
);
  1. 表约束关键词
1
2
3
4
5
6
7
8
9
10
CONSTRAINT 复合主键名_PK 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) 
);
  1. 表约束定义代理键
1
2
3
4
5
6
7
8
9
10
11
serial 代理键
> 
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) 
);
  1. 表约束定义外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CONSTRAINT 外键名_FK FOREIGN KEY(列名)
REFERENCES 关联的表名(列名)
    ON DELETE CASCADE
    ON UPDATE CASCADE
>
CREATE　TABLE　Register( 
    CourseRegID　　　serial　　NOT　NULL, 
    CoursePlanID　　 int　　　 NOT　NULL, 
    StudentID　　　　char(13), 
    Note　　　　　　 varchar(30), 
    CONSTRAINT　　　 CourseRegID_PK　　PRIMARY Key(CourseRegID), CONSTRAINT　　　 CoursePlanID_FK　 FOREIGN Key(CoursePlanID) REFERENCES　Plan(CoursePlanID) 
        ON DELETE CASCADE, 
    CONSTRAINT　　　 StudentID_FK　FOREIGN KEY(StudentID) REFERENCES　Student(StudentID) 
        ON DELETE CASCADE 
);

数据库表修改

1
ALTER TABLE <表名> <修改方式>;
  1. ADD
1
2
3
4
用于增加新列或列完整性约束
ALTER TABLE <表名> ADD <新列名称><数据类型>[完整性约束];
>
ALTER TABLE Student ADD Email varchar(20);
  1. DROP
1
2
3
4
5
用于删除指定列或列的完整性约束条件
ALTER TABLE<表名> DROP COLUMN <列名>;
ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名>; 
>
ALTER TABLE Student DROP COLUMN StudentPhone;
  1. RENAME
1
2
3
用于修改表名称、列名称
ALTER TABLE <表名> RENAME TO <新表名>; 
ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
  1. ALTER
1
2
用于修改列的数据类型，
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE<新的数据类型>;

数据库删除

1
DROP TABLE <表名>;

需注意：DROP TABLE 不能直接删除由 FOREIGN KEY 约束引用的表。只有先删除FOREIGN KEY约束或引用的表后，才能删除本表。

数据表索引对象的定义

索引： 是一种针对表中指定列的值进行排序的数据结构，使用它可以加快表中数据的查询。

补充知识:
给表加上了主键，就相当于是有了聚集索引
不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。
非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。
索引的数据结构有很多，包括平衡树，哈希桶等
参考资料

创建索引对象

1
2
3
4
CREATE INDEX <索引名> ON <表名><(列名[,..,])>;
CREATE UNIQUE INDEX <索引名> ON <表名><（列名[,...]）>;
>
CREATE INDEX BirthDay_Idx ON Student (BirthDay);

修改索引对象

1
2
3
ALTER INDEX <索引名> RENAME TO <新索引名>;
>
ALTER INDEX BirthDay_Idx RENAME TO BDay_Idx

删除索引对象

1
2
3
DROP INDEX <索引名>;
>
DROP INDEX BirthDay_Idx;

数据操纵(插入、更新、删除)

数据插入

1
2
3
INSERT INTO <基本表>[<列名表>] VALUES(列值表);
>
INSERT INTO Student VALUES('2017220101105',’柳因’,’女’,'1999-04-23',’软件工程’, 'liuyin@163.com');

在INSERT INTO插入数据语句中，使用的interger和numeric等类型数值不使用引号标注，但char、varchar、date 和datetime等类型必须使用单引号。

数据更新

1
2
3
4
5
6
7
UPDATE <基本表> 
SET <列名1>=<表达式1> [,<列名2>=<表达式2>...] 
[WHERE <条件表达式>];
>
UPDATE Student 
SET Email='zhaodong@163.com' 
WHERE StudentName=’赵东’;

数据删除

1
2
3
4
5
DELETE FROM <表名> 
[WHERE <条件表达式>];
>
DELETE FROM Student 
WHERE StudentName=’张亮’;

数据查询

1
2
3
4
5
6
SELECT [ALL|DISTINCT] <目标列>[,<目标列>…] 
[ INTO <新表> ] 
FROM <表名>[,<表名>…] 
[ WHERE <条件表达式> ] 
[ GROUP BY <列名> [HAVING <条件表达式> ] 
[ ORDER BY <列名> [ ASC | DESC ] ];

（1）SELECT子句：作为SELECT语句的必要子句，用来指明从数据库表中需要查询的目标列。ALL关键词是查询 默认操作，即从表中获取满足条件的所有数据行；DISTINCT关键词用来去掉结果集中的重复数据行；<目标列>为被 查询表的指定列名，可以有多个。若查询表中所有列，一般使用*号表示。
（2）INTO子句：用来将被查询的结果集数据插入新表。
（3）FROM子句：用来指定被查询的数据来自哪个表或哪些表。若有多表，使用逗号分隔。
（4）WHERE子句：用来给出查询的检索条件。只有满足条件的数据行才允许被检索出来。
（5）GROUP BY子句：用来对查询结果进行分组，并进行分组统计等处理。在分组中，还可以使用HAVING关键 词定义分组条件。
（6）ORDER BY子句：用来对查询结果集进行排序。ASC关键词约定按指定列的数值升序排列查询结果集。DESC 关键词约定按指定列的数值降序排列查询结果集。若子句中没有给出排序关键词，默认按升序排列查询结果集

内置函数

  1. 聚合函数
    t71ljg.png 
1
2
3
4
5
6
7
SELECT COUNT(*) FROM Student;
SELECT COUNT(*) AS 学生人数 FROM Student;

SELECT COUNT(Major) AS 学生专业数 FROM Student;
SELECT COUNT(DISTINCT Major) AS 学生专业数 FROM Student;

SELECT MIN(BirthDay) AS 最大年龄出生日期， Max(BirthDay) AS 最小年龄出生日期 FROM Student;
  1. 算数函数
    t78nl8.png
  2. 字符串函数
  3. 时间日期函数
  4. 数据类型转换函数

子查询

1
2
3
4
5
6
7
SELECT TeacherID, TeacherName, TeacherTitle 
FROM Teacher 
WHERE CollegeID IN 
(SELECT CollegeID 
FROM College WHERE 
CollegeName=’计算机学院’) 
ORDER BY TeacherID；

数据控制

GRANT赋予权限

1
2
3
GRANT <权限列表> ON <数据库对象> TO <用户或角色> [ WITH GRANT OPTION ];
>
GRANT SELECT, INSERT, UPDATE, DELETE ON Register TO RoleS;

REVOKE收回权限

1
2
3
REVOKE <权限列表> ON <数据库对象> FROM <用户或角色> ;
>
REVOKE DELETE ON Register FROM RoleS;

DENY拒绝授权

1
2
3
DENY <权限列表> ON <数据库对象> TO <用户或角色> ;
>
DENY DELETE ON Teacher TO RoleT;

视图

视图创建

1
2
3
4
5
6
CREATE VIEW <视图名>[(列名1),(列名2),…] AS <SELECT查询>;
>
CREATE VIEW BasicCourseView AS SELECT CourseName, 
CourseCredit, CoursePeriod, TestMethod 
FROM Course 
WHERE CourseType=’基础课’;

视图删除

1
2
3
DROP VIEW <视图名>;
>
DROP VIEW BasicCourseView;

视图使用

1
2
3
4
5
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. CourseName=’数据库原理及应用’ AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S.StudentID; 
>
SELECT * FROM DataBaseCourseView;

数据库应用编程

存储过程编程

1
2
3
4
5
6
7
8
CREATE [ OR REPLACE ] FUNCTION 
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) 
    [ RETURNS retype | RETURNS TABLE ( column_name column_type [, ...] ) ] 
AS $$ DECLARE 
    -- 声明段 
BEGIN --函数体语句 
END; 
$$ LANGUAGE lang_name;

（1）OR REPLACE：如果没有该名称，则创建存储过程。当数据库中存在该存储过程时，如果创建存储过程的语 句没有关键字OR REPLACE，数据库将给出类似“该存储过程已经存在，不能创建该存储过程”的警示信息；如果创 建存储过程的语句有关键字OR REPLACE，则将旧的存储过程替换为新创建的存储过程。
（2）name：要创建的存储过程名。
（3）argmode：存储过程参数的模式可以为IN、OUT或INOUT，默认值是IN。IN声明参数为输入参数，向存储过 程内部传值；OUT声明参数为输出参数，存储过程对参数值的修改在存储过程之外是可见的，类似其他语言将函数的 形式参数声明为引用；INOUT声明该参数既是输入参数，同时又是输出参数。
（4）argname：形式参数的名称。
（5）argtype：该函数返回值的数据类型。可以是基本类型，也可以是复合类型、域类型或者与数据库字段相同的 类型。字段类型用 table_name.column_name%TYPE 表示，使用字段类型声明变量的数据类型，数据库表的类型变化不 会影响存储过程的执行。
（6）default_expr：指定参数默认值的表达式，该表达式的类型必须是可转化为参数的类型。只有IN和INOUT模式 的参数才能有默认值，具有默认值的输入参数必须出现在参数列表的最后。
（7）retype：指示 RETURNS 返回值的数据类型。可以声明为基本类型、复合类型、域类型或者表的字段类型。 如果存储没有返回值，可以指定void作为返回类型。如果存在OUT或INOUT参数，那么可以省略RETURNS子句。
（8）RETURNS TABLE：指示存储过程返回值的类型是由多列构成的二维表，表的列名由 column_name 指定，每 个列的数据类型由 column_type 指明；如果存储过程返回值由RETURNS TABLE指定，存储过程就不能有OUT和 INOUT模式的参数。 （9）AS $$：用于声明存储过程的实际代码的开始，当编译器扫描遇到下一个 的时候，则表明代码的结束。
（10）DECLARE:PL/pgSQL 指示声明存储过程的局部变量，后续内容将介绍如何定义存储过程的局部变量。
（11）BEGIN…END：用来定义存储过程的执行体语句。
（12）LANGUAGE：在关键字后面由lang_name指明存储过程所使用的编程语言，同时标志存储过程的结束。例 如：LANGUAGE plpgsql 告诉编译器该存储过程是使用PL/pgSQL实现的。

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION countRecords () 
RETURNS integer AS $count$ 
DECLARE 
    count integer; 
BEGIN 
    SELECT count(*) INTO count FROM Student; 
    RETURN count; 
END; 
$ count $ LANGUAGE plpgsql;

触发器编程

1
2
3
4
5
6
7
CREATE [CONSTRAINT] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ...] } 
ON table_name 
[ FROM referenced_table_name ] 
[ FOR [ EACH ] { ROW | STATEMENT } ] 
[ WHEN (condition) ]
EXECUTE PROCEDURE function_name ( arguments )

创建触发器

  1. 存在所依赖的表或视图
  2. 编写触发器函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION score_audit() 
    RETURNS TRIGGER AS $score_audit$ 
    BEGIN 
        IF (TG_OP = 'DELETE') THEN 
            INSERT INTO Audit_score 
            SELECT user, old.sid, old.cid, now(), OLD.score ; 
            RETURN OLD; 
        ELSIF (TG_OP = 'UPDATE') THEN 
            INSERT INTO Audit_score 
            SELECT user, old.sid, old.cid, now(), OLD.score , new.score
            where old.sid=new.sid and old.cid=new.cid; 
            RETURN NEW; 
        ELSIF (TG_OP = 'INSERT') THEN 
            INSERT INTO Audit_score 
            SELECT user, new.sid, new.cid, now(),null, new.score; RETURN NEW; 
        END IF; 
        RETURN NULL; 
    END; 
$score_audit$ LANGUAGE plpgsql;
  1. 指明触发的条件信息
1
2
3
CREATE TRIGGER score_audit_trigger 
AFTER INSERT OR UPDATE OR DELETE ON Stu_score 
FOR EACH ROW EXECUTE PROCEDURE score_audit();