sql学习笔记

本文档数据库框架为:postgreSQL

笔记内容与UNSW（COMP9311）相关

1. Basic Command

1. Populating a database

$ psql {target path} -f {source path}

-f: tells server to read its input from a file

2. Check the database exits

$ psql -l

3. Copy source from the origin file to the target file:

$ cp -r {origin path} {target path}

e.g. 

• if we just have one file, we can use ‘.’ to replace the {target file} to tell server there is not a empty path

$ cp -r /home/cs9311/web/24T1/lab/02 demo

$ cp -r /home/cs9311/web/24T1/lab/02 . 

4. Access the database 

$ psql {database name}

5. Check what’s in the database

$ {database name}=# \d {database name} is a default prefix

6. Read in commands from the specific files

$ {database name}=# -i {sql name}

e.g. company=# -i data.sql

6. recreate or remove table:

$ {database name}=# DROP TABLE tablename;

2. Useful Command

1. GROUP BY

distinct和group by都可以用来去重， 不同之处，distinct针对全部字段去重，而group by可以针对全部字段中的单一字段去重（对column_name去重）

SELECT column_name, aggregate_function(column_name)  
FROM table_name  
WHERE column_name operator value  
GROUP BY column_name;

company=# select tfn, max(hourspweek) from employees GROUP BY tfn;