# PostgreSQL : your first relational database

Exercises: SQL Commands
To learn more about SQL and do some fun exercises, follow this free tutorial which gives some fun interactive exercises for you:

https://www.khanacademy.org/computing/computer-programming/sql

https://www.codecademy.com/learn/learn-sql

![Snipaste_2020-04-23_19-06-16.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1587640008911/UB50I0TyX.png)
psql -U postgres 'test'

CREATE TABLE users (name text, age smallint, birthday date);

\d, \q

test=# INSERT INTO users (name,age,birthday) VALUES ('chuck',31,'1988-11-11');

SELECT * FROM users;

test=# ALTER TABLE users ADD score smallint;


test=# UPDATE users SET score = 50 WHERE name='alice';
UPDATE 1
test=# select * from users;

```
 name  | age |  birthday  | score
-------+-----+------------+-------
 chuck |  31 | 1988-11-11 |
 alice |  28 | 1990-08-15 |    50
``` 

test=# UPDATE users SET score = 100 WHERE name='chuck' OR name='tom';
UPDATE 2
test=# select * from users;

```
 name  | age |  birthday  | score
-------+-----+------------+-------
 alice |  28 | 1990-08-15 |    50
 chuck |  31 | 1988-11-11 |   100
 tom   |  40 | 1970-08-15 |   100
``` 

test=# SELECT * FROM users WHERE name LIKE 'c%';
```
 name  | age |  birthday  | score
-------+-----+------------+-------
 chuck |  31 | 1988-11-11 |   100
(1 行记录)
``` 

test=# SELECT * FROM users ORDER BY score DESC;

```
 name  | age |  birthday  | score
-------+-----+------------+-------
 chuck |  31 | 1988-11-11 |   100
 tom   |  40 | 1970-08-15 |   100
 alice |  28 | 1990-08-15 |    50
(3 行记录)

``` 

test=# SELECT * FROM users ORDER BY score ASC;
```
 name  | age |  birthday  | score
-------+-----+------------+-------
 alice |  28 | 1990-08-15 |    50
 chuck |  31 | 1988-11-11 |   100
 tom   |  40 | 1970-08-15 |   100
(3 行记录)
``` 

test=# SELECT AVG(score) FROM users;
```
         avg
---------------------
 83.3333333333333333
(1 行记录)
``` 

test=# SELECT SUM(age) FROM users;
```
 sum
-----
  99
(1 行记录)
``` 

test=# SELECT COUNT(name) FROM users;
```
 count
-------
     3
(1 行记录)
``` 


CREATE TABLE login (
	ID serial NOT NULL PRIMARY KEY,
	secret VARCHAR (100) NOT NULL,
	name text UNIQUE NOT NULL
);

INSERT INTO login (secret,name) VALUES ('abc','chuck');
INSERT INTO login (secret,name) VALUES ('xyz','alice');

test=# \d
```
                  关联列表
 架构模式 |     名称     |  类型  |  拥有者
----------+--------------+--------+----------
 public   | login        | 数据表 | postgres
 public   | login_id_seq | 序列数 | postgres
 public   | users        | 数据表 | postgres
(3 行记录)
``` 

test=# SELECT * FROM users JOIN login ON users.name = login.name;
```
 name  | age |  birthday  | score | id | secret | name
-------+-----+------------+-------+----+--------+-------
 alice |  28 | 1990-08-15 |    50 |  2 | xyz    | alice
 chuck |  31 | 1988-11-11 |   100 |  1 | abc    | chuck
(2 行记录)
``` 

test=# DELETE FROM users WHERE name='tom';
DELETE 1
test=# SELECT * FROM users;
```
 name  | age |  birthday  | score
-------+-----+------------+-------
 alice |  28 | 1990-08-15 |    50
 chuck |  31 | 1988-11-11 |   100
(2 行记录)
``` 

test=# DROP TABLE login;
DROP TABLE
test=# \d
```
               关联列表
 架构模式 | 名称  |  类型  |  拥有者
----------+-------+--------+----------
 public   | users | 数据表 | postgres
```