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

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