PostgreSQL的使用2
一、约束
约束创建时间:1.表格创建阶段(Create table) 2.表格修改阶段(Alter table)
几类约束:
约束 | 作用 |
---|---|
not null | 不为空约束 |
unique | 确保某一列值的唯一性 |
pk | 主键 |
fk | 外键 |
check | 保证某列的值符合某一条件 |
其中fk与mysql有所不一致:
外键约束:
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)-->这里直接用references
);
check约束复习:
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);
删除约束:
ALTER TABLE table_name DROP CONSTRAINT some_name;
二、JOIN连接
join类型
详解:
cross join:就是mysql的join,也就是笛卡尔积
inner join:就是mysql的natural join,保存相同的
left outer join:左外连接,左表相关列的值都保存,如果右表没有则显示空
right outer join:右外连接,与左外连接反过来即可
full outer join:外连接,也就是左外和右外合起来而已
语法:** inner join/left outer join/right outer join/full outer join** on **
三、Union
一般用得比较少,几乎可以用join代替,且join比较简洁
语法:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL/UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
四、NULL和AS
NULL:
·设置某个值为空
·选择某属性为空/不为空的对象
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL
AS:与mysql一致,不赘述
五、触发器
PostgreSQL 触发器是数据库的回调函数,它会在指定的数据库事件发生时自动执行/调用
5.1 几个重要的点:
1.触发器的触发时间点:
①执行操作前(在检查约束并尝试插入、更新或删除之前)
②执行操作后(在检查约束并尝试插入、更新或删除之后)
③执行操作时(在检查约束并尝试插入、更新或删除时)
2.触发器的调用次数
FOR EACH ROW 当操作修改时每行调用一次
STAMENT 不管修改了多少行,每个语句标记的触发器执行一次
5.2 实例理解
Company 表:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
AUDIT表:每当 COMPANY 表中有一个新的记录项时,日志消息将被插入其中
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
在company表上创建触发器
CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
其中auditlogfunc()是执行函数
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
总体上来将便是:在表上创建一个触发器,当向该表插入、删除、更新操作时候,会执行触发器定义的函数,实现我们需要的相应功能,由于此过程是由插入、删除、更新操作触发的,因此叫做触发器。
删除触发器drop trigger example_trigger on company;
六、索引
加速搜索引擎检索数据的一种特殊表查询。简单地说,索引是一个指向表中数据的指针。拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。
6.1 创建索引
CREATE INDEX index_name ON table_name;
6.2 索引的类型
单例索引:一列
CREATE INDEX index_name
ON table_name (column_name);
组合索引:多列
CREATE INDEX index_name
ON table_name (column1_name, column2_name);
唯一索引:
CREATE UNIQUE INDEX index_name
ON table_name (column1_name, column2_name);
局部索引:表的子集上构建的索引
隐式索引:隐式索引 是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束
6.3 删除索引
DROP INDEX index_name;
七、ALTER TABLE命令
作用:添加、删除、修改表的列;添加、删除约束
1.添加列:
ALTER TABLE company add age int(10);
2.删除列:
ALTER TABLE company **drop** age int(10);
3.修改列名称和类型:
alter table teacher rename column age to age1
alter table teacher alter age1 type smallint using age1::smallint
注意这里不同于mysql的modify
4.添加约束
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
5.添加主键
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
6.删除约束和主键都是 DROP CONSTRAINT
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
八、TRUNCATE TABLE命令
删除表的数据但不删除表的结构,相当于Mysql的delete from
语法:RUNCATE TABLE table_name;
九、视图
和mysql一致
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;
十、事务
1、TRANSACTION:通常包含了一个序列的对数据库的读/写操作
2、目的:
3、事务的ACID原则:老生常谈了
4、事务控制
·事务的开启:BEGIN或BEGIN TRANSACTION
遇到COMMIT或ROLLBACK才停止,不过在数据库关闭或发生错误时,事务处理也会回滚
·事务提交:COMMIT
把事务调用的更改保存到数据库中的事务命令,即确认事务
·事务回滚:ROLLBACK
用于撤消尚未保存到数据库的事务命令,即回滚事务
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
十一、锁
1.作用:保持数据库数据的一致性,可以阻止用户修改一行或整个表,一般用在并发较高的数据库中。
2.分类:排它锁(Exclusive Locks)和共享锁(Share Locks)。
如果数据对象加上排它锁,则其他的事务不能对它读取和修改。
如果加上共享锁,则该数据库对象可以被其他事务读取,但不能修改。
BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;
十二、子查询和自增
子查询于mysql一致,不赘述
在PostgreSQL中,自增可以用smallserial、serial 和 bigserial来解决
CREATE TABLE runoob
(
id serial NOT NULL,
alttext text,
imgurl text
)
十三、权限
PRIVILEGES
GRANT授权,REVOKE取消权限
十四、常用函数
1.聚合函数(mysql的聚积函数)
其余函数可以到菜鸟教程看
https://www.runoob.com/postgresql/postgresql-functions.html