数据库复习

SELECT 列表

FROM 表1

JOIN 表2 ON 表1.列 = 表2.列;

SELECT 列表

FROM 表1

NATURAL JOIN 表2;

CREATE DATABASE CourseDB;

ALTER DATABASE CourseDB RENAME TO CourseManageDB;

DROP DATABASE CourseManageDB；

CREATE  TABLE  Student

(StudentID      char(13)    PRIMARY  KEY,

 StudentName    varchar(10)	NOT NULL UNIQUE,

 StudentGender  char(2)     NULL     CHECK(StudentGender IN('男','女')),

 BirthDay       date        NULL,

 Major          varchar(30) NULL     DEFAULT '本科生',

 StudentPhone	char(11)    NULL

 -- 或者

 CONSTRAINT Student_PK PRIMARY Key(StudentID,StudentName)

);

ALTER TABLE STUDENT ADD <.><.>[..];

ALTER TABLE STUDENT DROP COLUMN<.>;

ALTER TABLE STUDENT DROP CONSTRAINT<.>;

ALTER TABLE STUDENT RENAME TO<>;

ALTER TABLE STUDENT RENAME <> TO <>;

ALTER TABLE STUDENT ALTER COLUMN<> TYPE<>;

DROP TABLE <>;

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)

);

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

);

CREATE INDEX Birthday_Idx ON  STUDENT(Birthday);

ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;

DROP INDEX bday_idx;

INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程', '[email protected]');

UPDATE  Student

SET  Email='[email protected]'

WHERE   StudentName='赵东';

DELETE 

FROM  STUDENT

WHERE   StudentName='张亮';

-- 通用结构

SELECT  [ALL|DISTINCT]  <目标列>[，<目标列>…]

[ INTO <新表> ]

FROM  <表名|视图名>[，<表名|视图名>…]

[ WHERE  <条件表达式> ]

[ GROUP BY  <列名> [HAVING <条件表达式> ]]

[ ORDER BY  <列名> [ ASC | DESC ] ];

-- BETWEEN AND 限制列值范围

SELECT  *

FROM  STUDENT

WHERE BirthDay BETWEEN ‘2000-01-01’ AND ‘2000-12-30’;

-- LIKE/NOT LIKE 通配符 '_' 代表一个字符 '%' 代表一个或多个字符 

SELECT  *

FROM  STUDENT

WHERE  Email  LIKE  ’%@163.com’;

-- AND\OR\NOT 逻辑运算符

SELECT  StudentID, StudentName, StudentGender, Major

FROM  STUDENT

WHERE  Major=’软件工程’  AND  StudentGender=’男’;

-- IN 限定范围

SELECT  StudentID, StudentName, StudentGender, Major

FROM  STUDENT

WHERE  Major IN  ('计算机应用');

-- ORDER BY <> ASC/DESC 默认升序 ASC

-- 多个列排序时，写在前面排序的基础上满足后面的

SELECT  *

FROM  STUDENT

ORDER  BY  Birthday DESC ,  StudentName  ASC;

-- 内置函数

SELECT  COUNT（*） AS  学生人数

FROM  Student;

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

FROM Student;

SELECT  Min（Birthday） AS 最大年龄，Max（Birthday） AS 最小年龄

FROM  Student;

-- GROUP BY <> HAVING 

-- 专业统计 STUDENT 表中专业男生人数，但限定只显示人数大于 2 的人数

SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数

FROM  Student

WHERE  StudentGender=’男’

GROUP  BY  Major

HAVING  COUNT(*)>2;

SELECT  TeacherID, TeacherName, TeacherTitle

FROM  Teacher

WHERE  CollegeID  IN

        (SELECT  CollegeID  

     FROM  College

     WHERE  CollegeName='计算机学院');

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 内连接

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;

-- LEFT JOIN/RIGHT JOIN/FULL JOIN 外连接

SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, 

COUNT  (R.CoursePlanID)  AS 选课人数

FROM  COURSE  AS  C  JOIN  PLAN  AS  P  

ON  C.CourseID=P.CourseID 

JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID

LEFT  JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID

GROUP  BY C.CourseName, T.TeacherName;

START TRANSACTION;

INSERT  INTO college( collegeID, collegename)VALUES ('004', '外语学院');

INSERT  INTO college( collegeID, collegename)VALUES ('005', '数学学院');

INSERT  INTO college( collegeID, collegename)VALUES ('006', '临床医学院');

COMMIT;

-- 创建用户

CREATE USER "USERA" WITH

 LOGIN

 INHERIT

 NOSUPERUSER

 NOCREATEDB

 NOCREATEROLE

 NOREPLICATION

 CONNECTION LIMIT -1

 PASSWORD '123456';

-- 修改用户

ALTER USER "USERA"

  CONNECTION LIMIT 10

  PASSWORD 'GRES123';

ALTER USER  <用户名>  [ [ WITH ] option [ ... ] ];              	-- 修改用户的属性

ALTER USER  <用户名>  RENAME TO <新用户名>;                          -- 修改用户的名称

ALTER USER  <用户名>  SET <参数项> { TO | = } { value | DEFAULT };  -- 修改用户的参数值  

ALTER USER  <用户名>  RESET <参数项>;			                      -- 重置用户参数值

-- 删除用户

DROP USER "USERA";

GRANT  SELECT  ON  Department  TO  userA;

GRANT  SELECT  ON  Employee    TO  userA;

GRANT  SELECT  ON  Project     TO  userA;

GRANT  SELECT  ON  Assignment  TO  userA;

GRANT  <权限名> ON  <对象名>  TO {数据库用户名|用户角色名}；

REVOKE  <权限名> ON  <对象名>  FROM {数据库用户名|用户角色名}；

DENY  <权限名> ON  <对象名> TO {数据库用户名|用户角色名}；

CREATE  ROLE  <角色名> [ [ WITH ] option [ ... ] ]; 	               -- 创建角色

ALTER  ROLE  <角色名>  [ [ WITH ] option [ ... ] ]; 	               -- 修改角色属性

ALTER  ROLE  <角色名>  RENAME TO <新角色名>; 		                    -- 修改角色名称 

ALTER  ROLE  <角色名>  SET <参数项> { TO | = } { value | DEFAULT };	 -- 修改角色参数值

ALTER  ROLE  <角色名>  RESET <参数项>;	                    		 -- 复位角色参数值

DROP  ROLE  <角色名>;					                           	   -- 删除指定角色

CREATE  ROLE  "Role_Manager"  WITH

 LOGIN

 NOSUPERUSER

 NOCREATEDB

 NOCREATEROLE

 INHERIT

 NOREPLICATION

 CONNECTION LIMIT -1;

GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Department  TO  "Role_Manager";

GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Employee  TO  "Role_Manager";

GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Project  TO  "Role_Manager";

GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Assignment  TO  "Role_Manager";

CREATE USER  "StudentUser"  WITH

  LOGIN

  CONNECTION LIMIT -1

  IN ROLE "R_Student"

  PASSWORD '123456';

CREATE USER  "TeacherUser"  WITH

  LOGIN

  CONNECTION LIMIT -1

  IN ROLE "R_Teacher"

  PASSWORD '123456';

BACKUP DATABASE SAMPLE   

TO DISK = ‘G:\Sample.bak';

RESTORE DATABASE SAMPLE

FROM DISK = ‘G:\Sample.bak';

create or replace function price ()

return integer as $price$

declare

	price integer;

begin

	select price into price from goods_price

	where good_id = 123;

	return price;

end;

$price$ language plpgsql;

CREATE [ OR REPLACE ] FUNCTION/PROCEDURE  name

    ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )

    [ RETURNS retype | RETURNS TABLE ( column_name  column_type [, ...] ) ]

AS $$         //$$ 用于声明存储过程的实际代码的开始

DECLARE

        -- 声明段

BEGIN

        -- 函数体语句

END;

$$ LANGUAGE lang_name;  //$$ 表明代码的结束，LANGUAGE 后面指明所用的编程语言

-- 创建一个名为 countRecords () 的存储过程统计 STUDENT 表的记录数。

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;

-- 执行存储过程

SELECT * FROM countRecords();

-- 存储过程调用其他存储过程:select  into 自定义变量  from 存储过程名（参数）；

CREATE OR REPLACE FUNCTION testExec()  

returns integer AS $$ 

declare

   rec integer;  

BEGIN  

   select  into rec countRecords();  

     // 如果不关心 countRecords () 的返回值，则可用 PERFORM countRecords () 代替；

   return  rec;  

END;  

$$ LANGUAGE plpgsql;

-- 删除存储过程

DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ CASCADE | RESTRICT ]

DROP FUNCTION IF EXISTS testExec();

drop function if exists price();

客户银行余额信息表accounts；

drop table if exists accounts;

create table accounts (

    id SERIAL,                       		-- 客户 ID 号

    username varchar(100) not null,	-- 客户姓名

    balance dec(15,2) not null,	-- 账户余额

    primary key(id)

);

客户转账信息表translists；

drop table if exists translists;

create table translists (

id SERIAL primary key,		-- 转账记录 ID

sendid int,				-- 转出客户的 ID

sendname varchar(100) not null,	-- 转出客户的姓名

recid int,				-- 转入客户的 ID

recname varchar(100) not null,	-- 转入客户的姓名

transdate timestamp(0),		-- 转账时间

transamount dec(15,2) not null	-- 转账金额

);

使用存储过程实现银行转账，客户银行余额信息存在accounts表中，转账记录信息存在translists表中；银行转账包括三个主要操作：

首先检查转出账户的余额是否充足，如果大于转出金额，将客户的余额减去转账金额；

然后给转入账户的余额加上转账金额；

最后记录转账信息，以供后期备查。

create or replace procedure send (money integer)

as $$

declare 

leftOfSender integer;

leftOfReceider integer;

log text;

begin

if money < select balace from accounts where id

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;

CREATE  TRIGGER  触发器名   

    { BEFORE | AFTER | INSTEAD OF }

     ON 表名

     [ FOR [ EACH ] { ROW | STATEMENT } ]

     EXECUTE PROCEDURE 存储过程名 ( 参数列表 )

-- 创建表

CREATE TABLE stu_score

( 

    sid character(10) NOT NULL,

    cid character(10) NOT NULL,

    score numeric(5,1),

    CONSTRAINT stu_score_pkey PRIMARY KEY (sid, cid)

)

CREATE TABLE audit_score

(    username character(20) ,  -- 用户名

    sid character(10) ,

    cid character(10) ,

    updatetime text ,            -- 修改的时间

    oldscore numeric(5,1),   -- 修改前的成绩

    newscore numeric(5,1)  -- 修改后的成绩

)

-- 创建函数

CREATE OR REPLACE FUNCTION score_audit RETURNS TRIGGER 

AS $score_sudits$

BEGIN

	IF (TG_OP = 'DELETE') THEN

		INSERT INTO audit_score SELECT user,old.sid,old.cid,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_audits$ LANGUAGE plpgsql;

-- 创建触发器

CREATE TRIGGER score_audit_triger

AFTER INSERT OR UPDATE OR DELETE ON stu_score

FOR EACH ROW EXECUTE PROCEDURE score_audit();

-- 修改触发器

ALTER TRIGGER score_audit_trigger ON stu_score  RENAME TO score_audit_trig;

-- 删除触发器

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

DROP TRIGGER IF EXISTS score_audit_trig ON stu_score CASCADE;

-- 声明游标

curStudent CURSOR FOR SELECT * FROM student;

curStudentOne CURSOR (key integer)7y IS SELECT * FROM student WHERE SID = key;