数据定义SQL语句
数据库创建、修改、删除
-- 执行SQL语句创建一个选课管理数据库CourseDB
create database CourseDB
-- 将选课管理数据库CourseDB名称修改为 CourseManageDB
alter database CourseDB rename to CourseManageDB
-- 执行SQL语句删除数据库CourseManageDB
drop database CourseManageDB
数据库表对象的定义
-
数据库表创建SQL语句
字段名称 字段编码 数据类型 字段大小 必填字段 是否为键 备注 课程编号 CourseID 文本 4 是 主键 课程名 CourseName 文本 20 是 否 取值唯一 课程类别 CourseType 文本 10 否 否 “基础课”、“专业课”、“选修课” 学分 CourseCredit 数字 短整型 否 否 学时 CoursePeriod 数字 短整型 否 否 考核方式 TestMethod 文本 10 否 否 缺省值“闭卷考试” -- 创建课程信息表 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) NOT NULL DEFAULT '闭卷考试' );
最后一行末尾,不能加逗号,多个约束之间不用加逗号
列完整性约束关键词
PRIMARY KEY——主键
NOT NULL——非空值
NULL——空值
UNIQUE——值唯一
CHECK——有效性检查
DEFAULT——缺省值
-
表约束关键词
定义由多个列构成的复合主键,需要使用表约束方式来定义。
使用代理键定义复合主键。
constraint 约束名 primary key(主键列), constraint 约束名 primary key(代理键列名),
在数据库中,一些关系表之间存在关联。在一个表中作为主键的列,在另外的关联表中则作为外键。
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 );
-
数据库表修改SQL语句
--学生信息表增加email列 alter table student add email varchar(20); --删除学生电话列 alter table student drop column StudentPhone; --删除表 drop table student; --其他 ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名>; ALTER TABLE <表名> RENAME TO <新表名>; ALTER TABLE <表名> RENAME <原列名> TO <新列名>; ALTER TABLE <表名> ALTER COLUMN <列名> TYPE<新的数据类型>;
数据库表索引对象的定义
--为出生日期Birthday列创建索引
create index birthday_idx on student(brithday);
--修改索引名字
alter index birthday_idx rename to bday_idx;
--删除
drop index bday_idx
数据操纵SQL语句
--数据插入SQL语句,省略列名表
insert into Student
values('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');
--添加列名表
insert into Student(studentID, name, gender, birthday, major, email)
values('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');
--数据更新SQL语句
update Student
set Email='zhaodong@163.com'
where name='赵东';
--数据删除SQL语句
delete from Student
where name='张亮'
数据查询SQL语句
单表数据查询
-
查询语句基本结构
SELECT [ALL|DISTINCT] <目标列>[,<目标列>…] [ INTO <新表> ] FROM <表名|视图名>[,<表名|视图名>…] [ WHERE <条件表达式> ] [ GROUP BY <列名> [HAVING <条件表达式> ]] [ ORDER BY <列名> [ ASC | DESC ] ];
-
从单表读取指定行和列
--读取姓名,学号,专业 select StudentID, name, major from student --读取所有列数据 select * from student --消除重复行 select distinct major from student --性别为男,专业为软件工程 select StudentID, name, gender, major from student where major = '软件工程' and geneder = '男'
在WHERE子句中可以使用如下方式,指定范围数据。
1)使用BETWEEN…AND关键词来限定列值范围,还可以使用关键词LIKE与通配符来限定查询条件。
2)使用通配符来限定字符串数据范围。下划线(_)通配符用于代表一个未指定的字符。百分号(%)通配符用于代表一个或多个未指定的字符。
select * from student where Email like ’%@163.com’;
-
查询结果排序
--对生日降序排列 select * from student order by Birthday desc; --首先按出生日期降序排列,然后按姓名升序排列, select * from student order by Birthday desc, name asc;
-
内置函数的使用
聚合函数是一些对关系表中数值属性列进行计算并返回一个结果数值的函数。
聚合函数 功能 AVG() 计算结果集指定列数据的平均值 COUNT() 计算结果集行数 MIN() 找出结果集指定列数据的最小值 MAX() 找出结果集指定列数据的最大值 SUM() 计算结果集指定列数据的总和 --统计学生人数 select count(*) AS 学生人数 from student; --找出最大年龄和最小年龄 select min(birthday) as 最大年龄, max(birthday) as 最小年龄 from student;
-
内置函数与分组统计结合
在SQL语言中,可使用内置函数对查询结果集进行分组数据统计。这是通过在SELECT语句中加入Group By子语句来实现。
--分专业统计Student表中的学生人数 select major as 专业, count(studentid) as 学生人数 from student group by major; --分专业统计student表中各专业男生人数,但限定只显示人数大于2的人数,其查询SQL语句如下 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;
-
SQL 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;
-
外连接
在一些特殊情况下,如关联表中一些行的主键与外键不匹配,查询结果集就会丢失部分数据。
--在选课管理数据库中,希望查询所有开设课程的学生选课情况,包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER。 --使用 LEFT 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;
视图SQL语句
视图——是一种通过基础表或其它视图构建的虚拟表。它本身没有自己的数据,而是使用了存储在基础表中的数据。
视图创建、查询与删除:
-- 建立一个查看基础课数据的视图BasicCourseView
create view basiccourseview as
select coursename, coursecredit, courseperiod, testmethod
from course
where coursetype='基础课';
-- 使用SELECT语句查询该视图数据,并按课程名称排序输出
select *
from basiccourseview
order by coursename;
-- 删除视图
drop view BasicCourseView;
数据库管理
数据库用户管理
-
用户创建SQL语句,不写逗号
-- 创建一个新用户,其账号名字为“userA”,密码为“123456”。该用户具有登录权限(Login)和角色继承权限(Inherit)系统权限,但它不是超级用户(SuperUser),不具有创建数据库权限(CreateDB)、创建角色权限(CreateRole)、数据库复制权限(Replication),此外数据库连接数(Connection Limit)不受限。 create user "userA" with login nosuperuser nocreatedb nocreaterole inherit noreplication connection limit -1 password '123456';
备选项:
SUPERUSER | NOSUPERUSER CREATEDB | NOCREATEDB CREATEROLE | NOCREATEROLE INHERIT | NOINHERIT LOGIN | NOLOGIN REPLICATION | NOREPLICATION BYPASSRLS | NOBYPASSRLS CONNECTION LIMIT connlimit [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL VALID UNTIL 'timestamp' IN ROLE role_name [, ...]
-
用户修改、删除SQL语句
-- 修改用户“userA”的账号密码为“gres123”。同时也限制该用户的数据库连接数为10。 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"
数据库权限管理
--权限管理SQL语句
grant <权限名> on <对象名> to {数据库用户名|用户角色名};
revoke <权限名> on <对象名> from {数据库用户名|用户角色名};
deny <权限名> on <对象名> to {数据库用户名|用户角色名};
-- 对用户“userA”实现授权SQL程序如下
grant select on department to usera;
grant select on employee to usera;
grant select on project to usera;
grant select on assignment to usera;
数据库角色管理
在DBMS中,为了方便对众多用户及其权限进行管理,通常将一组具有相同权限的用户定义为角色(Role)。
create role <角色名> [ [ with ] option [ ... ] ]; --创建角色
alter role <角色名> [ [ with ] option [ ... ] ]; --修改角色属性
alter role <角色名> rename to <新角色名>; --修改角色名称
alter role <角色名> set <参数项> { to | = } { value | default };--修改角色参数值
alter role <角色名> reset <参数项>; --复位角色参数值
drop role <角色名>; --删除指定角色
--在工程项目管理系统中,假定需要在ProjectDB数据库内创建经理角色Role_Manager。该角色具有登录权限(Login)和角色继承权限(Inherit)系统权限,但它不是超级用户(SuperUser),不具有创建数据库权限(CreateDB)、创建角色权限(CreateRole)、数据库复制权限(Replication),此外数据库连接数(Connection Limit)不受限。
create role "role_manager" with
login
nosuperuser
nocreatedb
nocreaterole
inherit
noreplication
connection limit -1;
--在创建好经理角色Role_Manager后,还需要赋予该角色对数据库表Department、Employee、Project、Assignment的插入、修改、删除、查询权限。
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";
数据库存储过程
PostgreSQL使用CREATE FUNCTION命令创建函数或存储过程。
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; --$$ 表明代码的结束, 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 ();
--删除存储过程
drop function if exists countRecords ();
特别的,在 PostgreSQL 中,当函数定义了输出参数时,不能使用 RETURN
语句来返回结果。相反,应该在函数体内使用 OUT
参数来指定输出结果。
create or replace function Pro_CurrentSale(out number numeric, out allmoney money) as $count$
begin
select sum(Sale_Number) into number from Sale where Sale_Date = '2023-04-21';
select sum(Sale_Amount) into allmoney from Sale where Sale_Date = '2023-04-21';
end;
$count$ LANGUAGE plpgsql;
plpgsql语法:
--声明局部变量
declare
count intger;
rec RECORD;--RECORD不是真正的数据类型,只是一个占位符。
--条件语句
IF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSE
statements
END IF;
--循环语句1
LOOP
count=count+1;
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
count=count+1;
END LOOP;
--循环语句2
WHILE amount_owed > 0 AND balance > 0 LOOP
--do something
END LOOP;
--循环语句3
FOR i IN 1...10 LOOP
RAISE NOTICE 'i IS %', i;
END LOOP;
--遍历命令结果
declare
rec RECORD ;
FOR rec IN SELECT sid,sname FROM student LOOP
raise notice ‘%-,%-’,rec.sid, rec.sname;
END LOOP;
数据库触发器
触发器是特殊类型的存储过程,主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。
CREATE TRIGGER 触发器名
{ BEFORE | AFTER | INSTEAD OF } { EVENT [ OR ...] }
ON 表名
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE 存储过程名 ( 参数列表 )
创建触发器被触发时所要执行的触发器函数,该函数的类型必须是TRINGER型,是触发器的执行函数。
触发器相关的特殊变量:
- NEW :数据类型是RECORD。对于行级触发器,它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器,它的值是NULL。
- OLD :数据类型是RECORD。对于行级触发器,它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器,它的值是NULL。
- TG_OP:数据类型是text;是值为INSERT、UPDATE、DELETE 的一个字符串,它说明触发器是为哪个操作引发。
--为了防止非法修改stu_score表的课程成绩,创建audit_score表记录stu_score表的成绩变化,创建触发器函数score_audit()
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;
--接下来在stu_score表上创建触发器score_audit_triger
CREATE TRIGGER score_audit_triger
AFTER INSERT OR UPDATE OR DELETE ON stu_score
FOR EACH ROW EXECUTE PROCEDURE score_audit();