pgsql兼容mysql建表语句

Pgadmin实践

某公司为了实现对各部门的工程项目进行业务信息管理，使用PostgreSQL创建一个工程项目数据库 ProjectDB。

该数据库包含部门表（Department）、员工表（Employee）、项目表（Project）和任务表（Assignment）。

创建表格

从运行窗口可看到，系统可以一起执行由多条 SQL 语句构成的程序，同时创建多个数据库表。

需要注意：

在创建 多个有关联表的 SQL程序中，需要按照一定的先后顺序完成数据表的创建。例如，在创建上面的4个表时，需要首先执 行SQL语句创建Department表，然后再执行SQL语句创建Employee、Project表，最后执行SQL语句创建Assignment表。其 原因是表之间有主表与子表约束关系，首先需要创建主表，然后才能创建其关联的子表。 到此为止，ProjectDB数据库的4个数据表创建完成，可以提供给用户使用了。

部门表

CREATE TABLE Department (
DepartmentCode char(3) NOT NULL,
DepartmentName varchar(30) NOT NULL,
DepartmentIntro varchar(200) NULL,
DepartmentAddr varchar(50) NULL,
DepartmentTel varchar(20) NULL,
CONSTRAINT Department_PK PRIMARY KEY (DepartmentCode)
);

职工表

CREATE TABLE Employee (
EmployeeID serial NOT NULL,
EmployeeName varchar(10) NOT NULL,
Gender char(2) NOT NULL DEFAULT '男',
Department char(3) NOT NULL,
Degree char(6) NULL CHECK (Degree IN ('本科','研究生','其他')),
BirthDay date NULL,
Phone char(11) NULL,
Email varchar(20) NOT NULL UNIQUE,
CONSTRAINT Employee_PK PRIMARY KEY (EmployeeID),
CONSTRAINT EMP_DEPART_FK FOREIGN KEY (Department)
REFERENCES Department (DepartmentCode)
ON UPDATE CASCADE
);

项目表

CREATE TABLE Project (
ProjectID serial NOT NULL,
ProjectName varchar(50) NOT NULL,
Department char(3) NOT NULL,
EstimateHours int NOT NULL,
StartDate date NULL,
EndDate date NULL,
CONSTRAINT Project_PK PRIMARY KEY (ProjectID),
CONSTRAINT PROJ_DEPART_FK FOREIGN KEY (Department)
REFERENCES Department (DepartmentCode)
ON UPDATE CASCADE
);

任务表

CREATE TABLE Assignment (
ProjectID int NOT NULL,
EmployeeID int NOT NULL,
FishedHours int NOT NULL,
Cost int NOT NULL,
CONSTRAINT Assignment_PK PRIMARY KEY (ProjectID, EmployeeID),
CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY (ProjectID)
REFERENCES Project (ProjectID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY (EmployeeID)
REFERENCES Employee (EmployeeID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);

各表格数据插入

Department 表

/***** Department 表数据插入***********************/
INSERT INTO Department VALUES('A01', '人力资源',NULL, 'A区-100', '8535-6102');
INSERT INTO Department VALUES('A02','法律部', NULL, 'A区-108', '8535-6108');
INSERT INTO Department VALUES('A03','会计部', NULL, 'A区-201', '8535-6112');
INSERT INTO Department VALUES('A04','财务部', NULL, 'A区-205', '8535-6123');
INSERT INTO Department VALUES('A05','行政部', NULL, 'A区-301', '8535-6138');
INSERT INTO Department VALUES('A06','生产部', NULL, 'B区-101', '8535-6152');
INSERT INTO Department VALUES('A07','市场部', NULL, 'B区-201', '8535-6158');
INSERT INTO Department VALUES('A08','IT部', NULL, 'C区-101', '8535-6162');

Employee表

/***** Employee表数据插入*************************/ INSERT INTO Employee(employeename,gender,department,degree,birthday,phone,email) VALUES( '潘振', '男','A07', '本科', '1985-12-10','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '张志', '男','A02', '研究生', '1973-06-23','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '刘鸿', '女','A03', '本科', '1976-02-17','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '廖宇', '男','A04', '本科', '1989-11-13','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '刘梦', '女','A05', '其他', '1987-05-19','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '朱静', '女','A08', '本科', '1978-08-30','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '谢剑', '男','A03', '研究生', '1990-02-11','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '丁成', '男','A06', '本科', '1982-09-23','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '严刚', '男','A07','本科', '1988-11-18','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '杨盛', '男','A06','本科', '1975-06-09','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '王伦', '男','A01', '本科', '1968-07-30','139********','[e-mail]'); INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES( '汪润', '女','A04', '本科', '1965-11-19','139********','[e-mail]');

Project表

/***** Project表数据插入******************************/ INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES( '新产品推荐', 'A07',220, '2014-03-12', '2014-05-08'); INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES( '第2季度经营分析', 'A04',150, '2014-06-05', '2014-07-10' ); INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES( '上年度增值税上报', 'A03', 80, '2014-02-12', '2014-03-01'); INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES( '产品市场分析', 'A07', 135, '2014-03-20', '2014-05-15'); INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES( '产品定型测试', 'A06', 185, '2014-05-12', '2014-07-15');

Assignment 表

/***** Assignment 表数据插入**************************/
INSERT INTO Assignment VALUES(1,2,50,50); 
INSERT INTO Assignment VALUES(1,4,100,50); 
INSERT INTO Assignment VALUES(2,5,60,50); 
INSERT INTO Assignment VALUES(2,7,80,50);
INSERT INTO Assignment VALUES(3,8,45,50);
INSERT INTO Assignment VALUES(3,9,75,50); 
INSERT INTO Assignment VALUES(4,10,55,60); 
INSERT INTO Assignment VALUES(4,11,70,60); 
INSERT INTO Assignment VALUES(5,2,70,60); 
INSERT INTO Assignment VALUES(5,12,30,60);

插入完成后即可查看数据

多表关联查询

【例1】管理部门希望了解各个项目参与员工的任务工时列表。

SELECT ProjectName AS 项目名称,EmployeeName AS 员工姓名,FishedHours AS 实际工时
FROM Employee AS E, Project AS P, Assignment AS A
WHERE E.EmployeeID =A.EmployeeID AND P.ProjectID=A.ProjectID
ORDER BY P.ProjectID, A.EmployeeID;

上面的多表关联 SELECT 查询分别使用 Employee 表的主键与 Assignment 表的外键、Project表的主键和Assignment 表的外键进行关联，找出符合条件的员工完成工时数据。其查询输出数据首先按项目编号进行排序，在同一项目中又 按员工编号进行排序，采用默认升序输出。

【例2】管理部门希望了解各个参与员工的总工时数据。

SELECT E.EmployeeID AS 员工编号,EmployeeName AS 员工姓名,SUM(FishedHours) AS 完成总工时
FROM Employee AS E, Assignment AS A
WHERE E.EmployeeID =A.EmployeeID
GROUP BY E.EmployeeID, EmployeeName
ORDER BY E.EmployeeID;

上面的关联表SELECT查询使用Employee 表的主键与Assignment 表的外键进行关联，找出符合条件的员工完成工时 数据，并按员工进行分组统计总工时。其列表数据按员工编号升序输出。

【例3】了解各个项目的预计成本和当前实际发生成本

SELECT ProjectName AS 项目名称， (EstimateHours * Cost) AS 预计成本，
SUM(FishedHours *Cost) AS 实际成本
FROM Project, Assignment
WHERE Project.ProjectID=Assignment.ProjectID
GROUP BY ProjectName, EstimateHours, Cost;

上面的多表关联SELECT查询使用Project表的主键和Assignment表的外键进行关联，找出匹配的数据进行计算。在计 算实际成本数据中，按项目名进行分组求和统计。由计算结果可知，“上年度增值税上报”项目的实际成本超出了预 算。

语法错误

多半是由于出现了中文标点！！“在xx附近”

视图

降低程序编程人员使用查询SQL语句的复杂度

同时也为系统数据的安全使用

【例】在工程项目管理系统数据库ProjectDB中，管理部门希望查询输出员工通信录。为了保护员工的一些隐 私信息，可以采用视图方式查询输出，其输出内容包含员工编号、员工姓名、手机、邮箱。该视图的创建SQL语句和视 图查询SQL语句如下。

CREATE VIEW ContactView AS
SELECT EmployeeID AS 员工编号,EmployeeName AS 员工姓名, Phone AS 电话, Email AS 邮箱
FROM Employee;
SELECT *
FROM ContactView
ORDER BY 员工编号;

在上面的员工通信录生成中，首先创建员工通信录信息的视图ContactView，然后对该视图进行查询，并按员工编 号升序输出

【例】在工程项目管理系统数据库ProjectDB中，管理部门希望找出工期超出预期的项目信息。这时需要关联 Project表和Assignment表进行查询处理，计算各个项目的实际开展工时，并与预期工时比较，找出工期超出预期的项目 信息。输出内容包含项目名称、预期工时、实际工时，其SQL语句如下。

SELECT ProjectName AS 项目名称,EstimateHours AS 预期工时, SUM(FishedHours ) AS 实际工时
FROM Project AS P, Assignment AS A
WHERE P.ProjectID=A.ProjectID AND SUM(FishedHours ) > EstimateHours
GROUP BY ProjectName;

当以上语句在 PostgreSQL 数据库中执行时，系统会提示错误，不允许将内置函数作为WHERE子句的一个部分。

ERROR: 错误: 聚合函数不允许出现在WHERE中

因 此，直接使用SELECT语句无法完成上述查询操作。 这里，我们可以先构建一个包含该内置函数的视图，然后在视图查询 SQL 语句中使用WHERE子句条件，检索超 期的项目，其视图创建SQL语句和视图查询SQL语句如下。

CREATE VIEW ProjectFishedHours AS
SELECT ProjectName AS 项目名称， EstimateHours AS 预期工时， SUM(FishedHours ) AS 实际工时
FROM Project AS P, Assignment AS A
WHERE P.ProjectID=A.ProjectID
GROUP BY ProjectName,EstimateHours;
SELECT *
FROM ProjectFishedHours
WHERE 实际工时 > 预期工时
ORDER BY 项目名称；

中英文引号快速替换-Word

选项——校对——自动更正——“键入时自动套用格式”——将“直引号转换成弯引号”勾选掉，然后点击“确定”。

CTRL + G ： 替换即可。

word中文引号与英文引号怎么/如何快速替换-百度经验 (baidu.com)

Pgadmin左侧窗口消失

是没有创建服务器、或者是服务器窗口崩溃

可以创建一个本地服务器，hostname：localhost即可

数据库小问题

执行后，不是没有成功，是需要REFRESH

只是创建了表格，还灭有插入数据

在对应代表i奥格内插入数据

错误

ERROR: 错误: 插入或更新表 "assignment" 违反外键约束 "assign_proj_fk" DETAIL: 键值对(projectid)=(15)没有在表"project"中出现.

--------------------------- “朝着一个既定的方向去努力，就算没有天赋，在时间的积累下应该也能稍稍有点成就吧。”