安装
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;