数据库sql语句

data types

• INTEGER
• DECIMAL
  • 可以用来保存具有小数点而且数值确定的数值。而不像是float或者real保存近似值。
  • 声明方式decimal[(p[,s])]。例如decimal(10,3)表示共有七位整数3位小数，精度为10位。
• SERIAL
  • 自动递增的integer
• VARCHAR
• TIMESTAMP
• BOOLEAN
• ENUM
• NOT NULL
• UNIQUE
• PRIMARY KEY
• DEFAULT
• CHECK
  • 约束
  • e.g. CHECK（id>0）

Basic Operations

Creating a table:

 CREATE TABLE flights (
      id SERIAL PRIMARY KEY,
      origin VARCHAR NOT NULL,
      destination VARCHAR NOT NULL,
      duration INTEGER NOT NULL
  );

Inserting data into a table:

INSERT INTO flights
      (origin, destination, duration)
      VALUES ('New York', 'London', 415);

注意没有id这个field是因为id是SERIAL类型，自动递增。

Reading data from a table:

  SELECT * FROM flights;
  SELECT origin, destination FROM flights;
  SELECT * FROM flights WHERE id = 3;
  SELECT * FROM flights WHERE origin = 'New York';
  SELECT * FROM flights WHERE duration > 500;
  SELECT * FROM flights WHERE destination = 'Paris' AND duration > 500;
  SELECT * FROM flights WHERE destination = 'Paris' OR duration > 500;
  SELECT AVG(duration) FROM flights WHERE origin = 'New York';
  SELECT * FROM flights WHERE origin LIKE '%a%';
  SELECT * FROM flights LIMIT 2;
  SELECT * FROM flights ORDER BY duration ASC;
  SELECT * FROM flights ORDER BY duration ASC LIMIT 3;
  SELECT origin, COUNT(*) FROM flights GROUP BY origin;
  SELECT origin, COUNT(*) FROM flights GROUP BY origin HAVING COUNT(*) > 1;

COUNT(*)返回行数

LIMIT 设置了返回数据的行的最大值

ORDER BY 排序，默认升序排序（ASC），降序排列（DESC）

GROUP BY 分组

HAVING 限制GROUP BY，作用与WHERE类似

数据库查询语句模板：

select
from
where
group by
having
order by

updating data in a table:

  UPDATE flights
      SET duration = 430
      WHERE origin = 'New York'
      AND destination = 'London';

deleting data from a table:

      DELETE FROM flights
      WHERE destination = 'Tokyo'

Relating Tables and Compound Queries

demonstrating tables related by foreign keys:

CREATE TABLE passengers (
      id SERIAL PRIMARY KEY,
      name VARCHAR NOT NULL,
      flight_id INTEGER REFERENCES flights
  );

querying simultaeously:

  SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id;
  SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id WHERE name = 'Alice';
  SELECT origin, destination, name FROM flights LEFT JOIN passengers ON passengers.flight_id = flights.id;

JOIN 使得flights和passengers两个表可以同时被查询

JOIN：inner join 只有两个表中的行都存在时才能被连接

LEFT JOIN：返回左表中所有的行，即使右表中没有匹配

RIGHT JOIN：返回右边表中所有的行，即使左表没有匹配

FULL JOIN：只要其中某个表存在匹配，就会返回行

Nested queries:

 SELECT * FROM flights WHERE id IN
  (SELECT flight_id FROM passengers GROUP BY flight_id HAVING COUNT(*) > 1);

补充知识

select * from table limit 2,1;
//跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据


select * from table limit 2 offset 1;
//从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条