SQL入门

2019-01-19 约 2428 字预计阅读 5 分钟次阅读

SQL语句分类

DQL(Data Query Language)：数据查询语言，基本就是select语句，用于数据查询。
DML(Data Manipulation Language)：数据操纵语言，包含insert、update、delete三种语句。
DDL(Data Definition Language)：数据定义语言，主要用于创建、删除或修改表、索引等数据库对象。

创建表(create table)

使用\h create table查看帮助

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


postgres=# \h create table
postgres=# create table score (
postgres(# student_name varchar(40),
postgres(# chinese_score int,
postgres(# math_score int,
postgres(# test_date date
postgres(# );
CREATE TABLE
postgres=# \d score
                          Table "public.score"
    Column     |         Type          | Collation | Nullable | Default
---------------|-----------------------|-----------|----------|---------
 student_name  | character varying(40) |           |          |
 chinese_score | integer               |           |          |
 math_score    | integer               |           |          |
 test_date     | date                  |           |          |

其中int是integer别名，varchar(40)是character varying(40)别名。在建表的时候，可以在列定义后面使用primary key来指定这一列为主键：

1

postgres=# create table student(no int primary key, name varchar(40), age int, class_no int);

PostgreSQL会自动为主键创建索引student_pkey。

1
2
3
4
5


postgres=# \di
                 List of relations
 Schema |     Name     | Type  |  Owner   |  Table
--------|--------------|-------|----------|---------
 public | student_pkey | index | postgres | student

插入语句(insert)

使用\h insert查看帮助

1
2
3
4
5
6
7
8
9


postgres=# \h insert
postgres=# insert into student values(1, 'lily', 14);
INSERT 0 1
postgres=# insert into student(no, age, name) values(2, 13, 'lucy');
INSERT 0 1
postgres=# insert into student(no, name) values(3, 'bob');
INSERT 0 1
postgres=# insert into student values(4, 'kate', 12), (5, 'jobs', 15), (6, 'bill', 13);
INSERT 0 3

查询语句(select)

使用\h select查看帮助

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51


postgres=# \h select
postgres=# select no, name, age from student;
 no | name | age
----|------|-----
  1 | lily |  14
  2 | lucy |  13
  3 | bob  |
  4 | kate |  12
  5 | jobs |  15
  6 | bill |  13
(6 rows)
postgres=# select name, age+5 from student;
 name | ?column?
------|----------
 lily |       19
 lucy |       18
 bob  |
 kate |       17
 jobs |       20
 bill |       18
(6 rows)
postgres=# select no, 3+5 from student;
 no | ?column?
----|----------
  1 |        8
  2 |        8
  3 |        8
  4 |        8
  5 |        8
  6 |        8
(6 rows)
postgres=# select 55+88;
 ?column?
----------
      143
(1 row)
postgres=# select 10*2, 3*5+2;
 ?column? | ?column?
----------|----------
       20 |       17
(1 row)
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  14 |
  2 | lucy |  13 |
  3 | bob  |     |
  4 | kate |  12 |
  5 | jobs |  15 |
  6 | bill |  13 |
(6 rows)

条件过滤(where)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


postgres=# select * from student where no = 3;
 no | name | age | class_no
----|------|-----|----------
  3 | bob  |     |
(1 row)
postgres=# select * from student where age > 13;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  14 |
  5 | jobs |  15 |
(2 rows)

排序(order by)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


postgres=# select * from student order by age;
 no | name | age | class_no
----|------|-----|----------
  4 | kate |  12 |
  2 | lucy |  13 |
  6 | bill |  13 |
  1 | lily |  14 |
  5 | jobs |  15 |
  3 | bob  |     |
(6 rows)
postgres=# select * from student where age > 12 order by age;
 no | name | age | class_no
----|------|-----|----------
  2 | lucy |  13 |
  6 | bill |  13 |
  1 | lily |  14 |
  5 | jobs |  15 |
(4 rows)
postgres=# select * from student order by age, name;
 no | name | age | class_no
----|------|-----|----------
  4 | kate |  12 |
  6 | bill |  13 |
  2 | lucy |  13 |
  1 | lily |  14 |
  5 | jobs |  15 |
  3 | bob  |     |
(6 rows)
postgres=# select * from student order by age desc;
 no | name | age | class_no
----|------|-----|----------
  3 | bob  |     |
  5 | jobs |  15 |
  1 | lily |  14 |
  2 | lucy |  13 |
  6 | bill |  13 |
  4 | kate |  12 |
(6 rows)
postgres=# select * from student order by age desc, name;
 no | name | age | class_no
----|------|-----|----------
  3 | bob  |     |
  5 | jobs |  15 |
  1 | lily |  14 |
  6 | bill |  13 |
  2 | lucy |  13 |
  4 | kate |  12 |
(6 rows)

分组查询(group by)

1
2
3
4
5
6
7
8
9


postgres=# select age, count(*) from student group by age;
 age | count
-----|-------
     |     1
  15 |     1
  14 |     1
  13 |     2
  12 |     1
(5 rows)

表连接(join)

表连接也称为多表关联查询。为了演示，现在创建一张班级表，并插入一些测试数据：

1
2
3
4
5
6
7
8


postgres=# create table class (no int primary key, name varchar(40));
CREATE TABLE
postgres=# insert into class values(1, 'grade 1');
INSERT 0 1
postgres=# insert into class values(2, 'grade 2');
INSERT 0 1
postgres=# insert into class values(3, 'grade 3');
INSERT 0 1

修改student表class_no字段用于测试：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


postgres=# update student set class_no = 1 where no < 3;
UPDATE 2
postgres=# update student set class_no = 2 where no >=3 and no < 5;
UPDATE 2
postgres=# update student set class_no = 3 where no >=5;
UPDATE 2
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  14 |        1
  2 | lucy |  13 |        1
  3 | bob  |     |        2
  4 | kate |  12 |        2
  5 | jobs |  15 |        3
  6 | bill |  13 |        3
(6 rows)

若想查出每个学生与班级的关系，那么就需要关联查询两张表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


postgres=# select s.name, c.name from student s, class c where s.class_no = c.no;
 name |  name
------|---------
 lily | grade 1
 lucy | grade 1
 bob  | grade 2
 kate | grade 2
 jobs | grade 3
 bill | grade 3
(6 rows)

更新语句(update)

使用\h update查看帮助

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


postgres=# \h update
postgres=# update student set age = 15;
UPDATE 6
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  15 |        1
  2 | lucy |  15 |        1
  3 | bob  |  15 |        2
  4 | kate |  15 |        2
  5 | jobs |  15 |        3
  6 | bill |  15 |        3
(6 rows)
postgres=# update student set age = 14 where no = 3;
UPDATE 1
postgres=# update student set age = 12, name = 'tom' where no = 4;
UPDATE 1
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  15 |        1
  2 | lucy |  15 |        1
  5 | jobs |  15 |        3
  6 | bill |  15 |        3
  3 | bob  |  14 |        2
  4 | tom  |  12 |        2
(6 rows)

删除语句(delete)

使用\h delete查看帮助

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


postgres=# \h delete
postgres=# delete from student where no = 3;
DELETE 1
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  15 |        1
  2 | lucy |  15 |        1
  5 | jobs |  15 |        3
  6 | bill |  15 |        3
  4 | tom  |  12 |        2
(5 rows)
postgres=# delete from student;
DELETE 5
postgres=# select * from student;
 no | name | age | class_no
----|------|-----|----------
(0 rows)

删除表

使用\h drop table查看帮助

1
2
3


postgres=# \h drop table
postgres=# drop table student;
DROP TABLE

其他SQL语句

insert into … select语句

使用INSERT INTO… SELECT语句可以把数据从一张表插入到另一张表中，这个语句属于DML语句。创建一张学生表的备份表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


postgres=# create table student_bak(no int primary key, name varchar(40), age int, class_no int);
CREATE TABLE
postgres=# insert into student_bak select * from student;
INSERT 0 6
postgres=# select * from student_bak;
 no | name | age | class_no
----|------|-----|----------
  5 | jobs |  15 |        3
  6 | bill |  13 |        3
  3 | bob  |     |        2
  4 | kate |  12 |        2
  1 | lily |  14 |        1
  2 | lucy |  13 |        1
(6 rows)

union语句

可以将从两张表查询出来的数据整合在一个结果集下：

1
2
3
4
5
6


postgres=# select * from student where no = 1 union select * from student_bak where no = 2;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  14 |        1
  2 | lucy |  13 |        1
(2 rows)

语法比较简单，把两个SQL语句用union关键字连接起来就可以了。注意union会把结果集中相同的两条记录合并成一条。如果不想合并，请使用union all：

1
2
3
4
5
6


postgres=# select * from student where no = 1 union all select * from student_bak where no = 1;
 no | name | age | class_no
----|------|-----|----------
  1 | lily |  14 |        1
  1 | lily |  14 |        1
(2 rows)

truncate table语句

truncate table语句用于清空表，与不带where子句的delete一样清空表数据，但两者在实现原理上是不一样的。truncate table是DDL语句，相当于用重新定义一个新表的方法把原先表的内容直接丢弃了，所以 truncate table执行起来很快。而delete是DML 语句，它一条一条地删除数据。如果删除很多行数据，就会比较慢。

1
2


postgres=# truncate table student_bak;
TRUNCATE TABLE

目录