安装

brew install postgresql

启动

postgres -D /usr/local/var/postgres

新建用户 -s表现超管

/usr/local/Cellar/postgresql/9.5.3/bin/createuser -s root

创立数据库

/usr/local/Cellar/postgresql/9.5.3/bin/createdb root

客户端登录

psql -U root

创立表

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

);

CREATE TABLE DEPARTMENT(

ID INT PRIMARY KEY NOT NULL,

DEPT CHAR(50) NOT NULL,

EMP_ID INT NOT NULL

);

查看所有表

\d

查看某表布局

\d company

删除了表

drop table company;

创建模式 其实便是表的分组

create schema myschema;

在模式中创立表

create table myschema.company(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

删除了模式

DROP SCHEMA my schema;

强制删除了模板

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,JOIN_DATE) VALUES (2,'Allen',25, 'Texas','2007-12-13');

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3,'Teddy',23,'Norway',20000.00, DEFAULT );

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 company;

select id,name,age from company;

SELECT * FROM COMPANY WHERE SALARY = 20000;

SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

SELECT * FROM COMPANY LIMIT 2 offset 1;

SELECT * FROM COMPANY LIMIT 2;

SELECT * FROM COMPANY WHERE address::text like 'T%';

注意非string字段

SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

下划线表现一个字符

SELECT * FROM COMPANY WHERE AGE::text LIKE '2_';

改动

UPDATE COMPANY SET SALARY =15000 WHERE ID =6;

删除了

DELETE FROM COMPANY WHERE ID =2;