PostgreSql基础语法
2019-09-30 本文已影响0人
TZX_0710
基础语法
--创建数据库
CREATE DATABASE TESTDB;
--删除数据库
DROP DATABASE TESTDB;
--选择数据库
\C TESTDB --进入TESTDB数据库
--系统命令行窗口
PSQL -H LOCALHOST -P 5432 -U POSTGRESS TESTDB
--创建表
CREATE TABLE DEPT(
ID INT PRIMARY KEY,--设置主键
DEPTNAME VARCHAR(32),--部门名称
ADDRESS VARCHAR(32)--地址
);
--查看当前数据库的表
\D --查看表详细列 \D TABLENAME
--删除表
DROP TABLE TABLENAME,TABLENAME1
模式
POSTGRESQL 模式（SCHEMA）可以看着是一个表的集合。
一个模式可以包含视图、索引、据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 SCHEMA1 和 MYSCHEMA 都可以包含名为 MYTABLE 的表。
使用模式的优势：
允许多个用户使用一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
--创建模式
CREATE SCHEMA MYSCHEMA;
--在模式里面建表
CREATE TABLE MYSCHEMA.STUDENT(
ID INT,
STUNAME VARCHAR(32),
STUNO VARCHAR(10)
);
--在模式里面创建视图
CREATE VIEW MYSCHEMA.STUDENT_VIEW
AS SELECT * FROM MYSCHEMA.STUDENT;
--删除模式
DROP SCHEMA MYSCHEMA
--当模式里面包含了一些对象 是无法删除的 强制递归删除采用以下语法
DROP SCHEMA MYSCHEMA CASCADE;
--CRUD
--插入数据
INSERT INTO DEPT VALUES(1,'开发部','二楼101'),
(2,'财务部','二楼302'); --如果想采用默认值 字段可采用default填充
--查询语句
SELECT * FROM DEPT;
SELECT ID,STUNAME FROM DEPT;
--分页查询 注意 如果要实现类似分页 和mysql区别是 mysql的规则是 limit 0,2 语法不同！！！
SELECT * FROM DEPT LIMIT 2
--查询第一页的数据 当前页数*每页数量 （当前页-1）*每页数量
SELECT * FROM DEPT LIMIT 2 OFFSET 0
--查询第二页的数据
SELECT * FROM DEPT LIMIT(2*2) OFFSET (2-1)*2
运算符
--如果a为2 b为3
/*+ 加 a + b 结果为 5
- 减 a - b 结果为 -1
* 乘 a * b 结果为 6
/ 除 b / a 结果为 1
% 模（取余） b % a 结果为 1
^ 指数 a ^ b 结果为 8
|/ 平方根 |/ 25.0 结果为 5
||/ 立方根 ||/ 27.0 结果为 3
! 阶乘 5 ! 结果为 120
!! 阶乘（前缀操作符） !! 5 结果为 120
*/
--使用语法跟mysql一样
SELECT 2+3；
--阶乘
SELECT 5!;
SELECT !!5
表达式
--POSTGRESQL 获取当前时间
SELECT CURRENT_TIMESTAMP
--WITH查询 POSTGRESQL当中提供了对大量查询中使用
--查询2个结果集合并成一个结果集
WITH CAT AS (
SELECT ID FROM DEPT
),
TK AS (
SELECT ID FROM DEPT
)
SELECT ID FROM CAT
UNION ALL
SELECT ID FROM TK; --我们可以把这个DEMO看成是2个查询结果集合并成一个查询结果集 当我们希望这个查询的结果集被用域另外一个查询结果集 可以采用WITH的方式去查询
--应用在with当中的RECURSIVE 函数 RECURSIVE 在with当中 可以理解为这个with是一个递归查询操作
WITH RECURSIVE T(n) AS
values (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT SUM(N) FROM T;
--在WITH当中也可以使用INSERT UPDATE DELETE 语句
WITH TEMPS AS (
DELETE FROM DEPT WHERE ID=2 --删除部门编号为2的数据
RETURNING * --删除之后返回该列
)INSERT INTO HISTORY_DEPT --插入历史表
SELECT * FROM TEMPS;
一些关于查询的demo
-- This is the file to create COMPANY table and to populate it with 7 records.
-- Just copy and past them on psql prompt.
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
--3条
SELECT COUNT (1) FROM DEPARTMENT
--7条
SELECT count(1) FROM COMPANY;
--交叉查询 把第一个表的每一行和第二个表的每一行进行匹配 表的数量为 x*y demo示例产生的为21条数据
SELECT count(1) FROM DEPARTMENT CROSS JOIN COMPANY
--内连接 左表的数据和右表的数据字段关联匹配 所以产生的表数量只有 2边匹配的数量 应用关联关系1对1
SELECT * FROM COMPANY A INNER JOIN DEPARTMENT B ON(A.ID=B.EMP_ID)
--左外连接 对于T1表中不满足T2表的链接条件每一行 那么T2的列中会有NULL值添加一个列行
--所以left join 如果DEPARTMENT 在左边那么是3行 如果是在右边那么是7行
SELECT * FROM DEPARTMENT A LEFT JOIN COMPANY B ON(B.ID=A.EMP_ID)
--左外连接 首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行
--T1中现在剩下4条是不满足T2的所以现在是有7条数据不存在于表2 的 T2用空行代替
SELECT * FROM COMPANY A LEFT OUTER JOIN DEPARTMENT B ON(A.ID=B.EMP_ID)
--首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。对于T2中的每一行，结果表总是有一行 因为T2现在是满足了3行所以查询出来只有3条
SELECT * FROM COMPANY A RIGHT OUTER JOIN DEPARTMENT B ON(A.ID=B.EMP_ID)
--外连接 对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
SELECT * FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
GRANT授权
--创建用户 create user 默认用户用登陆权限 cretae role是没有的
create user testuser with password ‘password‘；
--授权 授予testuer 操作company表的权限
GRANT ALL ON COMPANY TO testuer
--收回权限
REVOKE ALL ON COMPANT FROM testuser；
--删除用户
DROP USER testuser；
