创建数据库:
create database testdb;
删除数据库:
drop database testdb;
查看数据库
\l
查看语法帮助:
\help <command_name>
进入数据库:
\c testdb
创建表格:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
JOIN_DATE DATE
);
查看所有表格:
\d
查看表格信息:
\d tablename
删除表格:
drop table department, company;
创建模式
create schema myschema;
删除一个空的模式(其中所有对象已经被删除):
DROP SCHEMA myschema;
删除一个模式以及其中包含的所有对象:
DROP SCHEMA myschema CASCADE;
在表格中插入数据:
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
在表格中插入多行数据:
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
读取表中的所有数据:
SELECT * FROM table_name;
读取SALARY字段大于50000的数据:
SELECT * FROM COMPANY WHERE SALARY > 50000;
读取 SALARY 字段不为空的数据:
SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
查询 COMPANY 表的记录总数
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据:
SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
列出AGE(年龄)字段为 25 或 27 的数据:
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
列出 AGE(年龄) 字段在 25 到 27 的数据:
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
使用SQL子查询,子查询语句读取SALARY字段大于65000的数据,然后通过EXISTS运算符判断它是否返回行,如果有返回行则读取所有的AGE字段
SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
读取 AGE 字段大于 25 且 SALARY 字段大于等于 65000 的所有记录:
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
更新 COMPANY 表中 id 为 3 的 salary 字段值:
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
同时更新所有 salary 字段和 address 字段的值:
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;
删除 ID 为 2 的数据:
DELETE FROM COMPANY WHERE ID = 2;
找出 SALARY 字段中在第二和第三个位置上有 00 的数据:
select * from company where salary::text like '_00%';
找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据
select * from company where salary::text like '2%%';
只读取 4 条COMPANY数据:
select * from company limit 4;
从第三位开始提取 3 个COMPANY数据:
select * from company limit 3 offset 2;
对结果根据 AGE 字段值进行升序排列:
SELECT * FROM COMPANY ORDER BY AGE ASC;
对结果根据NAME字段值进行降序排列:
SELECT * FROM COMPANY ORDER BY NAME DESC;
根据 NAME 字段值进行分组,找出每个人的工资总额:
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
根据 name 字段值进行分组,并且名称的计数大于 1 数据:注意HAVING字句必须放在GROUP BY子句后面,ORDER BY子句前面
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
数据表中选择name字段,且重复的只出现一次:
SELECT DISTINCT name FROM COMPANY;
PostgreSQL的约束
NOT NULL: 指示某列不能存储NULL值,NULL代表未知的数据
UNIQUE: 确保某列的值都是唯一的
PRIMARY key: NOT NULL和UNIQUE的结合。确保某列(或多个列的组合)有唯一标识,有助于更快找到表中的一个特定的记录
FOREIGN key: 保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK: 保证列中的值符合指定的条件
EXCLUSION: 排他约束,保证如果将任何两行的指定列或表达式使用指定操作符进行比较,至少其中一个操作符比较将会返回false或空值
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00,
SALARY1 REAL CHECK(SALARY > 0)
);
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);
删除约束:
ALTER TABLE table_name DROP CONSTRAINT some_name;
使用交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行,则结果表有 x*y 行
SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
内连接(INNER JOIN)根据连接为此结合两个表(table1和table2)的列值来创建一个新的结果表。查询会把table1的每一行与table2中的每一行进行比较,找到所有满足连接谓词的行的匹配对
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
左外连接( LEFT OUTER JOIN )首先执行一个内连接。然后,对于表 T1 中不满足表 T2 中连接条件的每一行,其中 T2 的列中有 null 值也会添加一个连接行。因此,连接的表在 T1 中每一行至少有一行
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
右外连接( RIGHT OUTER JOIN)首先执行内部连接。然后,对于表T2中不满足表T1中连接条件的每一行,其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行,结果表总是有一行
SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
外连接:首先,执行内部连接。然后,对于表 T1 中不满足表 T2 中任何行连接条件的每一行,如果 T2 的列中有 null 值也会添加一个到结果中。此外,对于 T2 中不满足与 T1 中的任何行连接条件的每一行,将会添加 T1 列中包含 null 值的到结果中
SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
UNION操作符合并两个或多个SELECT语句的结果,注意每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个SELECT语句的列的顺序必须相同。UNION会自动消除重复的行,如果允许重复使用UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
使用别名
SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
创建单列索引:只基于表的一个列上创建的索引
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 (column_name);
局部索引:在表的子集上构建的索引,子集由一个条件表达式上定义。索引只包含满足条件的行
CRAETE INDEX index_name on table_name (conditional_expression);
隐式索引:在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束
列出数据库中所有索引:
\di
删除索引:
DROP INDEX index_name;
在一张已存在的表上添加列:
ALTER TABLE table_name ADD column_name datatype;
在一张已存在的表上删除列:
ALTER TABLE table_name DROP COLUMN column_name;
修改表中某列的数据类型:
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
给表中某列添加NOT NULL约束:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
给表中某列添加UNIQUE约束:
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE (column1, column2...);
给表中添加CHECK约束:
ALTER TABLE table_name ADD CONSTRAINT MyCheckConstraint CHECK (CONDITION);
给表添加主键:
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
删除约束:
ALTER TABLE table_name DROP CONSTRAINT MyConstraint;
mysql删除约束:
ALTER TABLE table_name DROP INDEX MyConstraint;
删除主键:
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;
mysql删除主键:
ALTER TABLE table_name DROP PRIMARY KEY;
删除表数据但不删除表结构:
TRUNCATE TABLE table_name;
从COMPANY表中选取及列创建视图:
CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
然后就可以select * from company_view;
删除视图:
DROP VIEW view_name;
事务的四个标准属性ACID是什么?
原子性(Atomicity):事务作为一个整体被执行,包含在其中的对数据库的操作要么被全部执行,要么都不执行
一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束
隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其它事务的执行
持久性(Durability):已被提交的事务对数据库的修改应该永久保存在数据库中
开始一个事务:
BEGIN;
或
BEGIN TRANSACTION;
把事务调用的更改保存到数据库中的事务命令,即确认事务
COMMIT;
或
END TRANSACTION;
撤销尚未保存到数据库的事务命令,即回滚事务
ROLLBACK;
子查询的几个规则:
1.子查询必须用括号括起来
2.子查询在SELECT子句中只能有一个列,除非在主查询中有多列,与子查询的所选列进行比较
3.ORDER BY不能用在子查询中,虽然主查询可以使用ORDER BY。可以在子查询中使用GROUP BY,功能与ORDER BY相同
4.子查询返回多于一行,只能与多值运算符一起使用,如IN运算符
5.BETWEEN运算符不能与子查询一起使用,但是,BETWEEN可在子查询内使用
子查询语法:
SELECT column_name [, column_name] FROM table1 [, table2] WHERE column_name OPERATOR (SELECT column_name, [, column_name] FROM table1 [, table2] [WHERE])
SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 4500);
GRANT语法:
GRANT privilege [, ...] ON object [, ...] TO {PUBLIC | GROUP groupname |username};
privilege: 值可以为SELECT,INSERT,UPDATE,DELETE, RULE,ALL
object: 要授予访问权限的对象名称,可以有:table, view,sequence
PUBLIC: 表示所有用户
username: 要授予权限的用户名。PUBLIC是所有用户的简短形式