idea连接postgresql数据库操作和对university和全国地图数据库的建立以及函数建立。

怪我平庸至极—xyw

已于 2023-10-27 17:23:39 修改

阅读量318

点赞数 1

文章标签：数据库 postgresql java

于 2023-10-26 23:20:03 首次发布

本文链接：https://blog.csdn.net/weixin_65080186/article/details/134045937

版权

发布背景

因为空间数据库原理的上机，得以有机会亲手建立自己的数据库并连接进而完成题目。在之前数据库原理的上机时，完成了对oracle的初步了解和使用,但是oracle的卸载和密码的设置还是给我留下了很深的印象。因此在本次课程选用postgresql完成。老师给的答案基本是基于mysql和oracle，因此在此记录自己完成的第一次上机答案，方便其他有需要的人或者学弟学妹的查阅。

题目一

阅读《空间数据库实验教程》第9章，建立 University数据库。在数据库中录入至少4个系的信息；每个系至少150名教师信息，200名学生信息，200门课程信息；每个学生至少选修20门课程。

有之前oracle的基础，University数据库的建立是很简单的。正好老师给出了代码，在这里直接引用。

/*use University*/;
drop table if exists SC ;
drop table if exists  TC;
drop table if exists  Course;
drop table if exists  Teacher;
drop table if exists Student;
drop table if exists Department;

/*系的信息表 Department（Dno,Dname,Daddress）*/
create table Department(
	Dno int,
	Dname char(50),
	Daddress char(50),
	primary key (Dno)
	);
insert into Department(Dno,Dname,Daddress) values(1,'地球科学学院','主楼东');
insert into Department(Dno,Dname,Daddress) values(2,'资源学院','主楼西');
insert into Department(Dno,Dname,Daddress) values(3,'材化学院','材化楼');
insert into Department(Dno,Dname,Daddress) values(4,'环境学院','文华楼');
insert into Department(Dno,Dname,Daddress) values(5,'工程学院','水工楼');
insert into Department(Dno,Dname,Daddress) values(6,'地球物理与空间信息学院','物探楼');
insert into Department(Dno,Dname,Daddress) values(7,'机械与电子信息学院','教二楼');
insert into Department(Dno,Dname,Daddress) values(8,'经济管理学院','经管楼');
insert into Department(Dno,Dname,Daddress) values(9,'外语学院','北一楼');
insert into Department(Dno,Dname) values(10,'信息工程学院');
insert into Department(Dno,Dname,Daddress) values(11,'数学与物理学院','基委楼');
insert into Department(Dno,Dname,Daddress) values(12,'珠宝学院','珠宝楼');
insert into Department(Dno,Dname,Daddress) values(13,'政法学院','政法楼');
insert into Department(Dno,Dname,Daddress) values(14,'计算机学院','北一楼');
insert into Department(Dno,Dname) values(15,'远程与继续教育学院');
insert into Department(Dno,Dname) values(16,'国际教育学院');
insert into Department(Dno,Dname,Daddress) values(17,'体育部','体育馆');
insert into Department(Dno,Dname,Daddress) values(18,'艺术与传媒学院','艺传楼');
insert into Department(Dno,Dname,Daddress) values(19,'马克思主义学院','保卫楼');
insert into Department(Dno,Dname,Daddress) values(20,'江城学院','江城校区');

/*学生信息表Student（Sno, Sname, Ssex, Sage, Dno）*/
create table Student(
	Sno char(11) ,
	Sname char (8),
	Ssex char(2),
	Sage int ,
	Dno int,
	primary key (Sno),
	foreign key (Dno) references Department(Dno)
	);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000231','吕岩','M',18,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091004391','颜荣','M',19,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001598','王海涛','M',20,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091003085','袁恒','M',18,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000863','孟飞','M',17,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000934','罗振俊','M',19,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000961','曾雪君','F',18,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000983','巴翔','M',19,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001175','周雷','M',18,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001261','马欢','M',17,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001384','陈亮','M',20,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081003492','易家新','M',19,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001197','李子聪','M',17,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001266','蔡景学','F',19,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001888','赵林云','F',20,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000481','姜北','M',17,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000199','孙中孝','M',18,11); 
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000424','杨光','M',17,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000481','张永强','M',16,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000619','陈博','M',20,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000705','汤文盼','M',18,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000802','苏海恩','M',17,11);


/*教师信息表Teacher （Tno, Tname, Ttitle, Dno）*/
create table Teacher(
	Tno int primary key,
	Tname char (8),
	Ttitle char (8),
	Dno int ,
	foreign key (Dno) references Department(Dno)
	);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(1,'何小峰','副教授',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(2,'刘刚才','教授',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(3,'李星星','教授',11);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(4,'翁平正','讲师',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(5,'李川川','讲师',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(6,'王媛媛','讲师',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(7,'孔夏芳','副教授',14);

/*课程信息表 Course  （Cno, Cname, Cpno, Ccredit)*/
create table Course(
	Cno int primary key ,
	Cname char (50),
	Cpno int ,
	CCredit int,
	foreign key(Cpno) references Course(Cno)
	);
insert into Course(Cno, Cname, Ccredit) values(2,'高等数学',8);
insert into Course(Cno, Cname, Ccredit) values(6,'C语言程序设计',4);
insert into Course(Cno, Cname, Ccredit) values(7,'大学物理',8);
insert into Course(Cno, Cname, Ccredit) values(8,'大学化学',3);
insert into Course(Cno, Cname, Ccredit) values(10,'软件工程',2);
insert into Course(Cno, Cname, Ccredit) values(12,'美国简史',2);
insert into Course(Cno, Cname, Ccredit) values(13,'中国通史',6);
insert into Course(Cno, Cname, Ccredit) values(14,'大学语文',3);
insert into Course(Cno, Cname, Cpno, Ccredit) values(5,'数据结构',6,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(4,'操作系统',5,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(1,'数据库原理',5,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(3,'信息系统',1,2);
insert into Course(Cno, Cname, Cpno, Ccredit) values(9,'汇编语言',6,2);
insert into Course(Cno, Cname, Cpno, Ccredit) values(11,'空间数据库',1,3);


/*学生选课表SC（Sno,Cno,Grade）*/
create table SC(
	Sno char(11),
	Cno int, 
	Grade int,
	primary key(Sno, Cno),
	foreign key(Sno) references Student(Sno),
	foreign key (Cno) references Course(Cno)
	);
insert into SC values('20091003085',1,90);
insert into SC values('20091000863',1,98);
insert into SC values('20091000934',1,89);
insert into SC values('20091000961',1,85);
insert into SC values('20081001197',1,79);
insert into SC values('20081001266',1,97);
insert into SC values('20081001888',1,60);
insert into SC values('20091000481',1,78);
insert into SC values('20101000199',1,65);
insert into SC values('20101000424',1,78);
insert into SC values('20101000481',1,69);
insert into SC values('20091000863',6,90);
insert into SC values('20091000934',6,90);
insert into SC values('20091000961',6,87); 
 
/*教师授课表TC（Tno,Cno,Site）*/
create table TC(
	Tno int ,
	Cno int,
	Site char(50),
	primary key (Tno,Cno),
	foreign key(Tno) references Teacher(Tno),
	foreign key (Cno) references Course(Cno)
	);
insert into TC values(1,1,'教一楼407');
insert into TC values(1,6,'教一楼307');
insert into TC values(2,10,'教二楼217');
insert into TC values(3,2,'教三楼507');
insert into TC values(4,5,'教三楼208');
insert into TC values(6,3,'综合楼207');
insert into TC values(7,4,'教二楼817');
insert into TC values(5,9,'教一楼207');

题目二

采用PL/SQL语言编程实现下列任务： 1）定义一个带输入参数的存储过程 Increase_Grade，将课程号为1的所有学生成绩提升5%；要求课程号作为存储过程参数传入，并调用该存储过程。 2）定义一个带有输入和输出参数的存储过程 Average_Student_Grade，计算一个学生的所有选修课程的平均成绩。

在建立完数据库之后，使用postgresql直接建立存储过程即可。在postgresql中，存储过程和函数的建立都是如下代码所示：

create or replace function name(paramNAME dataType)

二者的不同是存储过程有返回值，函数无返回值。后续可以继续深入了解，我在此时也仅仅知道这些而已。下面给出具体代码，代码的的含义需要自己去了解，我就不在此深入。

CREATE OR REPLACE FUNCTION Average_Student_Grade 
(
    IN student_id char(11)
)
RETURNS TABLE (average_grade DECIMAL(10,2))
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT AVG(grade)
    FROM sc
    WHERE sno = student_id;
END;
$$;

DO $$
DECLARE
    average DECIMAL(10,2);
BEGIN
    SELECT average_grade INTO average
    FROM Average_Student_Grade('20091003085');
    RAISE NOTICE 'Average Grade: %', average;
END
$$;

CREATE OR REPLACE FUNCTION Increase_Grade(course_id INT)
RETURNS VOID AS $$
BEGIN
    UPDATE sc
    SET Grade = Grade * 1.05
    WHERE Cno = course_id;
END;
$$ LANGUAGE plpgsql;

SELECT Increase_Grade(1);

题目三：采用编程语言（Java、Python、C#、 C++），开发一个基于JDBC的数据库应用程序，连接University数据库，输出每个学生选修课程总成绩

1.加载驱动器

在此是我遇到的困难最多的一道题，因此详细记录下过程，首先下载idea。之后下载驱动器。地址为https://jdbc.postgresql.org/download/。

我下载的是42.6.0的版本。下载完之后不要动，在idea中新建一个项目即可。

建立完成后，如下图所示：

之后将下载好的驱动器添加到项目中。

点击file->project structure。之后进入moduls。

点击加号，点击第一个。选择自己下载好的驱动器。

点击小蓝框，点击apply，点击okj即可完成配置。

2.连接数据库

点击右上角的database，之后点击加号，选择data Source中的postgresql。

之后填写配置，若为本机则不需要更改任何东西，只需填写username和密码即可。如果没有做其他操作，user填postgres即可。在这里展示初始配置界面和我自己的数据库以及配置。其中10.22为university所在的数据库。

初始配置界面：

初始配置界面

我的数据库配置和idea配置界面。

点击Test Connection,显示成功即可。第一次连接需要下载驱动文件。点击Download下载完成即可。如果要展示数据库中全部内容，选择Schemas，勾选数据可，选择下面的Show temolete databases之后，点击apply和ok即可。

3.代码

之后就是连接代码，老师已经给出，将导入的包、类名称、url、username和密码改为自己的即可。下面代码是我改好的，如果要用的话更改url、username和密码就可以了。

import org.postgresql.Driver;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;



public class Main
{
    public static void main( String[] args )
    {
        String sql = "select sno , sum(grade) sg from SC group by sno";

        Connection connection = getPostgreSQLConnection();

        try {
            Statement statement =connection.createStatement();
            ResultSet resultSet =   statement.executeQuery(sql);
            while (resultSet.next()){
                String sno = resultSet.getString("sno");
                double sg = resultSet.getDouble("sg");
                System.out.println(sno);
                System.out.println(sg);
            }
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }


    /**
     * 获取PostgreSQL的Connection
     * @return
     */
    public static Connection getPostgreSQLConnection(){
        Connection conn=null;
        try {
            Class.forName("org.postgresql.Driver");//找到PostgreSQL驱动器所在的类
            String url="jdbc:postgresql://localhost:5432/10.22"; //URL地址
            String username="postgres";
            String password="123";
            conn= DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

运行就完成本题。

题目四

下载中国2020行政区划编码表，构建数据表guo_adcode，采用PL/SQL开发一个函数，输入一个行政区名称，返回该行政区的所有下一级行政区的名称。

建表的代码老师已经给出，值得一提的是我模仿老师建立了一个模式。其他并无不同。

CREATE SCHEMA IF NOT EXISTS chinamap;

题目代码：

CREATE OR REPLACE FUNCTION "chinamap"."province_sub_adnames"(pname VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    pcode VARCHAR;
    flag BOOLEAN := TRUE;
    r VARCHAR := '';
    rec RECORD;
BEGIN
    SELECT "code" INTO pcode
    FROM guo_adcode
    WHERE guo_adcode.name LIKE CONCAT(pname, '%');

    pcode := SUBSTRING(pcode FROM 1 FOR LENGTH(pcode) - 4);

    -- 处理直辖市
    IF pcode = '11' OR pcode = '12' OR pcode = '31' OR pcode = '50' THEN
        pcode := CONCAT(pcode, '%');
    ELSE
        pcode := CONCAT(pcode, '__00');
    END IF;

    -- 动态SQL
    FOR rec IN EXECUTE 'SELECT name FROM chinamap.guo_adcode WHERE code LIKE ''' || pcode || '''' LOOP
        IF flag THEN
            flag := FALSE;
            CONTINUE;
        END IF;

        r := CONCAT(r, rec.name);
    END LOOP;

    RETURN r;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION "chinamap"."city_sub_adnames"(pname VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    pcode VARCHAR;
    cur CURSOR (pcode VARCHAR) FOR SELECT name FROM chinamap.guo_adcode WHERE code LIKE pcode ORDER BY code;
    rec RECORD;
    r VARCHAR := '';
    pname1 VARCHAR := '%' || pname;
    flag BOOLEAN := TRUE;
BEGIN
    SELECT "code" INTO pcode
    FROM chinamap.guo_adcode
    WHERE chinamap.guo_adcode.name LIKE CONCAT(pname1, '%');
    pcode := SUBSTRING(pcode FROM 1 FOR LENGTH(pcode) - 2);
    -- 处理直辖市
    IF pcode = '1100' OR pcode = '1200' OR pcode = '3100' OR pcode = '5000' THEN
        RAISE NOTICE '直辖市是省级行政区，请调用province_sub_adnames';
        RETURN '';
    ELSE
        pcode := CONCAT(pcode, '%');
        
        OPEN cur(pcode);
        LOOP
            FETCH cur INTO rec;
            EXIT WHEN NOT FOUND;
            
            IF flag THEN
                flag := FALSE;
                CONTINUE;
            END IF;
            
            r := CONCAT(r, rec.name);
        END LOOP;
        CLOSE cur;
    END IF;
    
    RETURN r;
END;
$$ LANGUAGE plpgsql;

在使用postgresql可能遇到的问题。

一个是右键点击数据库，选择create 脚本即可编写代码。

另一个是，如果出现下面问题，进入services.msc，启动一项服务即可，在最后给出。

怪我平庸至极—xyw

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
idea连接postgresql数据库操作和对university和全国地图数据库的建立以及函数建立。

因为空间数据库原理的上机，得以有机会亲手建立自己的数据库并连接进而完成题目。在之前数据库原理的上机时，完成了对oracle的初步了解和使用,但是oracle的卸载和密码的设置还是给我留下了很深的印象。因此在本次课程选用postgresql完成。老师给的答案基本是基于mysql和oracle，因此在此记录自己完成的第一次上机答案，方便其他新手查阅。
复制链接

扫一扫