psql -h localhost -p 5432 -U postgres
2 数据库
创建数据库: create databse xxx;
删除数据库: drop databse xxx;
查看数据库: \l
选择数据库: \c xxx;
创建schema: create schema yyy;
删除schema: drop schema yyy;
创建数据表:
CREATE TABLE learn.student(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
salary REAL
);
删除数据表: drop table zzz;
查看全部数据表: \d
查看某个数据表信息: \d zzz;
导入sql文件：\i nnn.sql;
3 数据库操作
3.1 增
全部column信息一次性增加：insert into student values ()
部分column信息增加：inset into student (c1, c2) values (v1, v2)
3.2 删
delete from learn.student where id=1
3.3 改
update learn.student set name=’john’ where id=1;
3.4 查
select * from learn.student where id=1;
3.5 AND OR
select * from learn.student where name=’john’ and address=’beijing’;
select * from learn.student where name=’john’ or address=’tianjing’;
3.6 LIKE
select * from learn.student where name like ‘to%’;
%: 占位多个字符
_: 占位一个字符
3.7 LIMIT
select * from learn.student limit 1;
3.8 ORDER BY
select * from learn.student order by salary asc; 升序
select * from learn.student order by salary desc; 降序
3.9 GROUP BY
GROUP BY需要接查询函数一起使用。
select name, sum(salary) from learn.student group by name;
3.10 HAVING
HAVING在GROUP BY之后接条件
select name, sum(salary) from learn.student group by name having sum(salary)>5000 order by sum(salary) asc;
4 数据库高级操作
4.1 约束
4.2 JOIN
cross join : left table x * right table y = x*y
inner join: 保留left和right表的公共value。
left join: 保留left表的value，缺失的right表数据使用none填充。
right join: 保留right表的value，缺失的left表数据使用none填充。
outer join: 保留left和right表column交集和两表公共value(缺失使用none填充)
练习：
创建company表：
DROP TABLE learn.COMPANY;
CREATE TABLE learn.COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO learn.COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO learn.COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
创建department表
DROP TABLE learn.DEPARTMENT;
CREATE TABLE learn.DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
INSERT INTO learn.DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO learn.DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO learn.DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
inner join:
crabboss=# select emp_id, name, age, dept from learn.company as a inner join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
left join:
crabboss=# select emp_id, name, age, dept from learn.company as a left join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
| David | 27 |
| Kim | 22 |
| Mark | 25 |
| Teddy | 23 |
crabboss=# select emp_id, name, age, dept from learn.company as a right join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
outer join:
crabboss=# select emp_id, name, age, dept from learn.company as a full outer join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
| David | 27 |
| Kim | 22 |
| Mark | 25 |
| Teddy | 23 |
4.3 UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table2 [, table2 ]
[WHERE condition]
UNION ALL允许重复行出现
练习：
union：
crabboss=# select emp_id, name, age, dept from learn.company as a inner join learn.department as b on a.id=b.emp_id union select emp_id, name, age, dept from learn.company as a left join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
| David | 27 |
| Mark | 25 |
1 | Paul | 32 | IT Billing
| Teddy | 23 |
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
| Kim | 22 |
union all：
crabboss=# select emp_id, name, age, dept from learn.company as a inner join learn.department as b on a.id=b.emp_id union all select emp_id, name, age, dept from learn.company as a left join learn.department as b on a.id=b.emp_id;
emp_id | name | age | dept
--------+-------+-----+----------------------------------------------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
| David | 27 |
| Kim | 22 |
| Mark | 25 |
| Teddy | 23 |