PostgreSQL
2021/7/16 19:08:41
本文主要是介绍PostgreSQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
PostgreSQL练习
建表
--department(dNo,dName,officeRoom,homepage)
--student(sNo,sName,sex,age,dNo)
--course(cNo,cName,cPNo,credit,dNo)
--sc(sNo,cNo,score,recordDate)
CREATE TABLE department(
dNo CHAR(2) NOT NULL UNIQUE,
dName VARCHAR(20),
officeRoom VARCHAR(40),
homePage VARCHAR(80),
PRIMARY KEY(dNo)
);
CREATE TABLE student(
sNo CHAR(6) NOT NULL UNIQUE,
sName VARCHAR(20) NOT NULL,
sex CHAR(2) CHECK (sex IN('男','女')),
age INT,
dNo CHAR(2),
PRIMARY KEY(sNo),
FOREIGN KEY (dNo) REFERENCES department(dNo)
);
CREATE TABLE course(
cNo CHAR(6) NOT NULL UNIQUE,
cName VARCHAR(20) NOT NULL,
cPNo CHAR(6),
credit INT,
dNo CHAR(2),
PRIMARY KEY(cNo),
FOREIGN KEY (cPNo) REFERENCES course(cNo),
FOREIGN KEY (dNo) REFERENCES department(dNo)
);
CREATE TABLE sc(
sNo CHAR(6) NOT NULL,
cNo CHAR(6) NOT NULL,
score INT,
recordDate date DEFAULT current_date,
PRIMARY KEY(sNo,cNo),
FOREIGN KEY (sNo) REFERENCES student(sNo),
FOREIGN KEY (cNo) REFERENCES course(cNo)
);
插入数据
INSERT INTO department VALUES('01','信息学院','行政楼409','www.xxx.edu.cn');
INSERT INTO department VALUES('02','软件学院',null,null);
INSERT INTO department VALUES('03','理学院',null,null);
INSERT INTO department VALUES('04','文学院',null,null);
INSERT INTO department VALUES('05','外国语学院',null,null);
INSERT INTO student VALUES('170101','宁灿', '女',19,'01');
INSERT INTO student VALUES('170102','尹江月','女',19,'01');
INSERT INTO student VALUES('170103','杨佳伟','男',null,null);
INSERT INTO student VALUES('170104','杨何宇','男',19,'01');
INSERT INTO student VALUES('170105','胡耀斌','男',19,null);
INSERT INTO student VALUES('170106','李杨阳','女',20,'01');
INSERT INTO student VALUES('170107','杜利俊','女',18,'01');
INSERT INTO student VALUES('170108','钱多多','女',17,'01');
INSERT INTO student VALUES('170109','李佳伟','女',null,'01');
INSERT INTO student VALUES('170110','吴莫愁','女',21,'01');
INSERT INTO student VALUES('170201','安相成','男',19,'02');
INSERT INTO student VALUES('170202','曹师好','男',null,'02');
INSERT INTO student VALUES('170203','雷霆', '男',18,'02');
INSERT INTO student VALUES('170204','刘书敏','男',20,'02');
INSERT INTO student VALUES('170205','王兵', '男',21,'02');
INSERT INTO student VALUES('170206','李佳成','男',19,null);
INSERT INTO student VALUES('170207','唐玉迎','女',17,'02');
INSERT INTO student VALUES('170208','杨曼婷','女',19,'02');
INSERT INTO student VALUES('170301','张望', '男',21,'03');
INSERT INTO student VALUES('170302','王芳', '女',18,'03');
INSERT INTO student VALUES('170303','赵四海','男',19,'03');
INSERT INTO student VALUES('170401','孙敏', '女',null,null);
INSERT INTO student VALUES('170402','李忠国','男',null,'04');
INSERT INTO student VALUES('170403','钱紧', '男',17,'04');
INSERT INTO student VALUES('170404','钱多多','女',20,'04');
INSERT INTO student VALUES('170405','管八方','男',21,'04');
INSERT INTO student VALUES('170406','王兵', '男',19,'04');
INSERT INTO student VALUES('170407','张三丰','男',100,null);
INSERT INTO course VALUES('030101','高等数学',null,2,'03');
INSERT INTO course VALUES('030102','线性代数',null,2,'03');
INSERT INTO course VALUES('030201','矩阵论','030102',3,'03');
INSERT INTO course VALUES('030202','概率论','030101',2,'03');
INSERT INTO course VALUES('030301','数理统计','030202',3,'03');
INSERT INTO course VALUES('010101','信号与系统',null,2,'01');
INSERT INTO course VALUES('010102','数字电路','010101',2,'01');
INSERT INTO course VALUES('010103','数字信号处理','030301',3,'01');
INSERT INTO course VALUES('010201','模式识别','010103',3,'01');
INSERT INTO course VALUES('020101','离散数学',null,2,'02');
INSERT INTO course VALUES('020102','程序设计基础',null,2,'02');
INSERT INTO course VALUES('020201','计算机组成原理','020101',3,'02');
INSERT INTO course VALUES('020202','面向对象程序设计','020102',2,'02');
INSERT INTO course VALUES('020203','编译原理','020101',2,'02');
INSERT INTO course VALUES('020301','数据结构','020202',3,'02');
INSERT INTO course VALUES('020302','操作系统','020201',3,'02');
INSERT INTO course VALUES('020401','数据库系统','020301',3,'02');
INSERT INTO course VALUES('020402','算法设计与分析','020301',3,'02');
INSERT INTO course VALUES('020403','形式语言与自动机','020203',3,'02');
INSERT INTO course VALUES('020501','移动计算','020102',2,'02');
INSERT INTO course VALUES('040101','中国传统文化',null,1,'04');
INSERT INTO course VALUES('040102','近代世界史',null,1,'04');
INSERT INTO course VALUES('040103','现代文学',null,1,'04');
INSERT INTO course VALUES('040201','古典文学欣赏','040101',2,'04');
INSERT INTO course VALUES('040202','世界文学','040103',2,'04');
INSERT INTO course VALUES('040301','中国诗词欣赏','040201',2,'04');
INSERT INTO sc VALUES ('170101','030101',91,to_date('2016-01-08','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','030102',83,to_date('2016-07-10','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','020101',88,to_date('2016-07-02','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','020102',92,to_date('2017-01-10','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','020201',70,to_date('2017-01-10','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','020202',80,to_date('2017-01-10','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170101','020203',null,null);
INSERT INTO sc VALUES ('170101','020301',null,null);
INSERT INTO sc VALUES ('170101','020302',null,null);
INSERT INTO sc VALUES ('170101','020401',null,null);
INSERT INTO sc VALUES ('170101','020402',null,null);
INSERT INTO sc VALUES ('170102','030101',88,to_date('2016-01-08','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170102','030102',86,to_date('2016-07-10','yyyy-mm-dd'));
INSERT INTO sc VALUES ('170102','030201',58,null);
INSERT INTO sc VALUES ('170102','030202',90,null);
INSERT INTO sc VALUES ('170102','030301',70,null);
INSERT INTO sc VALUES ('170102','010101',85,null);
INSERT INTO sc VALUES ('170102','010102',68,null);
INSERT INTO sc VALUES ('170102','010103',80,null);
INSERT INTO sc VALUES ('170102','010201',78,null);
INSERT INTO sc VALUES ('170103','030101',88,default);
INSERT INTO sc VALUES ('170103','030102',80,default);
INSERT INTO sc VALUES ('170103','030202',76,default);
INSERT INTO sc VALUES ('170103','010101',90,default);
INSERT INTO sc VALUES ('170103','010102',83,null);
INSERT INTO sc VALUES ('170103','010103',55,null);
INSERT INTO sc VALUES ('170103','010201',77,null);
INSERT INTO sc VALUES ('170104','030101',null,null);
INSERT INTO sc VALUES ('170105','030101',null,null);
INSERT INTO sc VALUES ('170108','030101',90,null);
INSERT INTO sc VALUES ('170108','030102',87,null);
INSERT INTO sc VALUES ('170108','030202',78,null);
INSERT INTO sc VALUES ('170108','010101',57,null);
INSERT INTO sc VALUES ('170108','010102',56,null);
INSERT INTO sc VALUES ('170108','010103',54,null);
INSERT INTO sc VALUES ('170108','010201',50,null);
INSERT INTO sc VALUES ('170110','030101',90,default);
INSERT INTO sc VALUES ('170110','030102',90,default);
INSERT INTO sc VALUES ('170110','010101',88,default);
INSERT INTO sc VALUES ('170110','010102',88,default);
INSERT INTO sc VALUES ('170110','010103',77,default);
INSERT INTO sc VALUES ('170201','030101',88,null);
INSERT INTO sc VALUES ('170201','030102',83,null);
INSERT INTO sc VALUES ('170201','020101',79,null);
INSERT INTO sc VALUES ('170201','020102',84,null);
INSERT INTO sc VALUES ('170201','020201',75,null);
INSERT INTO sc VALUES ('170201','020202',80,null);
INSERT INTO sc VALUES ('170201','020301',82,null);
INSERT INTO sc VALUES ('170201','020302',53,null);
INSERT INTO sc VALUES ('170201','020401',78,null);
INSERT INTO sc VALUES ('170201','020402',58,null);
INSERT INTO sc VALUES ('170202','030101',86,null);
INSERT INTO sc VALUES ('170202','030102',82,null);
INSERT INTO sc VALUES ('170202','020101',90,null);
INSERT INTO sc VALUES ('170202','020102',85,null);
INSERT INTO sc VALUES ('170202','020201',80,null);
INSERT INTO sc VALUES ('170202','020202',78,null);
INSERT INTO sc VALUES ('170202','020203',75,null);
INSERT INTO sc VALUES ('170202','020301',70,null);
INSERT INTO sc VALUES ('170202','020302',68,null);
INSERT INTO sc VALUES ('170202','020401',80,null);
INSERT INTO sc VALUES ('170202','020402',56,null);
INSERT INTO sc VALUES ('170203','030101',null,null);
INSERT INTO sc VALUES ('170203','030102',null,null);
INSERT INTO sc VALUES ('170205','030101',80,null);
INSERT INTO sc VALUES ('170205','030102',55,null);
INSERT INTO sc VALUES ('170205','020101',50,null);
INSERT INTO sc VALUES ('170205','020102',96,null);
INSERT INTO sc VALUES ('170205','020201',80,null);
INSERT INTO sc VALUES ('170205','020202',85,null);
INSERT INTO sc VALUES ('170205','020203',60,null);
INSERT INTO sc VALUES ('170205','020301',88,null);
INSERT INTO sc VALUES ('170205','020302',92,null);
INSERT INTO sc VALUES ('170205','020401',65,null);
INSERT INTO sc VALUES ('170205','020402',70,null);
INSERT INTO sc VALUES ('170205','020501',80,null);
INSERT INTO sc VALUES ('170207','030101',null,null);
INSERT INTO sc VALUES ('170207','030102',null,null);
INSERT INTO sc VALUES ('170208','030101',60,null);
INSERT INTO sc VALUES ('170208','030102',null,null);
INSERT INTO sc VALUES ('170208','020101',null,null);
INSERT INTO sc VALUES ('170208','020102',null,null);
INSERT INTO sc VALUES ('170208','020201',null,null);
INSERT INTO sc VALUES ('170208','020202',null,null);
INSERT INTO sc VALUES ('170208','020301',null,null);
INSERT INTO sc VALUES ('170208','020302',null,null);
INSERT INTO sc VALUES ('170208','020401',null,null);
INSERT INTO sc VALUES ('170208','020402',null,null);
INSERT INTO sc VALUES ('170208','020501',null,null);
INSERT INTO sc VALUES ('170303','030101',90,null);
INSERT INTO sc VALUES ('170303','030102',88,null);
INSERT INTO sc VALUES ('170303','030201',92,null);
INSERT INTO sc VALUES ('170303','030202',93,null);
INSERT INTO sc VALUES ('170303','030301',94,null);
INSERT INTO sc VALUES ('170401','040101',82,null);
INSERT INTO sc VALUES ('170401','040102',80,null);
INSERT INTO sc VALUES ('170401','040103',83,null);
INSERT INTO sc VALUES ('170401','040201',89,null);
INSERT INTO sc VALUES ('170401','040202',70,null);
INSERT INTO sc VALUES ('170401','040301',null,null);
INSERT INTO sc VALUES ('170403','040201',95,null);
INSERT INTO sc VALUES ('170403','040202',88,null);
INSERT INTO sc VALUES ('170403','040301',80,null);
INSERT INTO sc VALUES ('170404','040101',95,null);
INSERT INTO sc VALUES ('170404','040102',91,null);
INSERT INTO sc VALUES ('170404','040103',89,null);
INSERT INTO sc VALUES ('170404','040201',93,null);
INSERT INTO sc VALUES ('170404','040202',92,null);
INSERT INTO sc VALUES ('170404','040301',88,null);
INSERT INTO sc VALUES ('170405','040102',70,null);
INSERT INTO sc VALUES ('170405','040201',58,null);
INSERT INTO sc VALUES ('170405','040202',66,null);
INSERT INTO sc VALUES ('170405','040301',77,null);
INSERT INTO sc VALUES ('170406','040101',55,null);
INSERT INTO sc VALUES ('170406','040102',65,null);
INSERT INTO sc VALUES ('170406','040301',75,null);
练习1 单表查询
- 查询所有年龄大于等于20岁的学生学号、姓名；
select sno,sname from student where age >= 20;
- 查询所有姓钱的男生学号、姓名、出生年份；
select sno,sname,2020-age from student where sname like '钱%' and sex = '男';
LIKE操作符的模式匹配
- 查询所有学分大于3的课程名称；
select cname from course where credit > 3;
- 查询所有没有被分配到任何学院的学生姓名；
select sname from student where dno is null;
- 查询所有尚未设置主页的学院名称；
select dname from department where homepage is null;
练习2 聚集函数
- 查询各个学院的平均年龄；
select avg(age) from student group by dno;
- 查询每个学生选修课程的平均分；
select sno,avg(score) from sc group by sno having avg(score) is not null order by avg(score) desc;
order by 列名 序；
- 查询各课程的平均分；
select cno,avg(score) from sc group by cno having avg(score) is not null order by avg(score) desc;
- 查询各学院开设的课程门数；
select dno,count(cno) from course where cno is not null group by dno order by count(cno) desc;
- 查询各门课程选修人数；
select cno,count(sno) from sc where cno is not null and sno is not null group by cno order by count(sno) desc;
练习3 多表查询
- 查询“信息学院”所有学生学号与姓名；
- 查询“软件学院”开设的所有课程号与课程名称；
- 查询与“陈丽”在同一个系的所有学生学号与姓名；
- 查询与“张三”同岁的所有学生学号与姓名；
- 查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名；
- 查询学分大于“离散数学”的所有课程名称；
- 查询选修了课程名为“组合数学”的学生人数；
- 查询没有选修“离散数学”的学生姓名；
- 查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称；
- 查询平均分大于等于90分的所有课程名称；
- 查询选修了“离散数学”课程的所有学生姓名与成绩；
- 查询“王兵”所选修的所有课程名称及成绩；
- 查询所有具有不及格课程的学生姓名、课程名与成绩；
- 查询选修了“文学院”开设课程的所有学生姓名；
- 查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称；
练习4 综合查询
- 查询所有学生及其选课信息（包括没有选课的学生）；
- 查询“形式语言与自动机”先修课的课程名称；
- 查询“形式语言与自动机”间接先修课课程名称；
- 查询先修课为编译原理的课程名称；
- 查询间接先修课为离散数学的课程名称；
- 查询所有没有先修课的课程名称；
- 查询所有没选修“形式语言与自动机”课程的学生姓名；
- 查询所有选修了“形式语言与自动机”但没选修其先修课的学生姓名；
- 查询选修课程总学分大于等于28的学生姓名及其选修课程总学分；
- 查询选修了3门以上课程且成绩都大于85分的学生学号与姓名；
- 查询恰好选修了3门课并且都及格的学生姓名；
- 查询人数多于6的学院名称及其学生人数；
- 查询平均成绩高于王兵的学生姓名；
- 查询所有选修了离散数学并且选修了编译原理课程的学生姓名；
- 查询软件学院离散数学课程平均分；
- 查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院；
- 查询各学院选修同一门课人数大于4的学院、课程及选课人数；
- 查询仅仅选修了“高等数学”一门课程的学生姓名；（学号、姓名及所在学院名称）
- 查询平均学分积小于70分的学生姓名。
- 查询选修了“信息学院”开设全部课程的学生姓名。
- 查询选修了“杨佳伟”同学所选修的全部课程的学生姓名；
练习5 DDL练习
- 创建2张表，信息如下：
图书（编号，书名，作者，ISBN，出版社编号，版本，出版日期）。主码为编号，ISBN唯一。出版社编号为外 码，参照出版社表的编号。
出版社（编号，名称，地址，电话）。主码为编号。
要求：(1)创建表的同时创建约束；
(2)删除所创建的表;
(3)重新创建表，在表创建之后增加约束。
create table press(
pno text primary key,
pname text,
address text,
pn text
);
create table books(
bno text primary key,
bname text,
aname text,
isbn text unique,
pno text references press(pno),
ver text,
pdate date
);
drop table books;
drop table press;
--外键中发起引用的表先删，被引用的表后删
create table press(
pno text,
pname text,
address text,
pn text
);
create table books(
bno text,
bname text,
aname text,
isbn text,
pno text,
ver text,
pdate date
);
alter table press
add primary key(pno);
alter table books
add primary key(bno),
add unique(isbn),
add foreign key(pno) references press(pno);
- 分别向两张表中各插入2行数据。将其中一个出版社地址变更一下。删除所插入数据。
insert into press values('某出版社1','某出版社','某地','20001023'),
('某出版社2','某出版社','某地','20001023');
insert into books values('某编号1','某书','某人','某ISBN号1','某出版社1','某版本','2000-10-23'),
('某编号2','某书','某人','某ISBN号2','某出版社1','某版本','2000-10-23');
update press set address = '中国' where pno = '某出版社1';
delete from books;
delete from press;
- 创建一个软件学院所有选修了“离散数学”课程的学生视图，并通过视图插入一行数据。创建一个各门课程平均分视图。
对视图的更新可以唯一的转化为对于基本表的更新，那么更新是可以的
视图涉及聚集函数，那么更新一般是不被允许的
create view st as (select student.sname
from student,department,sc,course
where student.dno in (select dno
from department
where department.dname = '软件学院'
)
and
sc.sno = student.sno
and
sc.cno in (select cno
from course
where cname = '离散数学'
)
)
with check option;
insert into st values('刘铭帅');
这种更新即为非法的，为什么呢？因为我在这里给view加入了with check option的条件，这里view插入一个新的数据后，这个数据在表中的其他字段实际上是null，是不符合view的筛选条件的，所以不应该出现在view中。
运行结果：
这里按照题意就取消掉with check option了
create view avgs as (select course.cno,t1.avgg
from course left join (select sc.cno,avg(sc.score) as avgg
from sc
group by sc.cno
) t1
on
course.cno = t1.cno
);
- 创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插
在数据库中，向数据库中插入数据，使用insert into关键字。在数据库中插入数据有三种方法。
1）插入单个字段的情况
insert into table_name(列名) values(值);
2）插入多个字段的情况
2.1） 插入两个字段以上
insert into table_name(列名1，列名2，列名3,) values(值1,值2，值3);
2.2）插入全部所有字段
insert into table_name values(值1，值2，值3);//值要和表中的字段顺序类型一致
3）使用子查询向表中插入数据
insert into table_name 子查询select语句;//需要注意的是不能违反表table_name的约束条件，以及需要和table_name的字段一致，或者少于它，但是不能多于它。
create table s_score(sNo text unique not null,
sName text,
avgscore numeric(6,4)
);
insert into s_score (select student.sno,student.sname,t1.avgg
from student left join (select sc.sno,avg(sc.score) as avgg
from sc
group by sc.sno
) t1
on
t1.sno = student.sno
);
select * from s_score;
练习6 DCL
尝试将多条SQL语句组成一个事务执行，体验提交和回滚操作。
一个事务transaction由begin…commit包含的语句构成，回滚可以使用rollback语句。
这篇关于PostgreSQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格，让你的数据库重新焕然一新！
- 2024-01-04在PostgreSQL中创建角色：判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL：修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数
