安装教程

法则1：col

法则2：select

select col,col,col,* 找什么
FROM table 从哪找？
WHERE col 条件 条件是啥？

条件：数字（where）

当查找条件col是数字

select * from table where col = 1;

条件： 文本（where）

排序（rows）

需要对结果rows排序和筛选部分rows

select * from table where col > 1 order by col asc limit 2 offset 2

合并（INNER/LEFT/RIGHT/FULL JOIN）

SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

NULL(IS/IS NOT NULL)

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

表达式

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;

SELECT col_expression AS expr_description, …
FROM mytable;

--Example query with both column and table name aliases
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

聚合函数

Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

GROUP BY

--Select query with aggregate functions over groups
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

if the GROUP BY clause is executed after the WHEREclause, SQL allows us to do this by adding an additional HAVING clause which is used specifically with the GROUP BY clause to allow us to filter grouped rows from the result set.

--Select query with HAVING constraint
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

插入行

--Insert statement with specific columns
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

修改行

--Update statement with values
UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

删除行

--Delete statement with condition
DELETE FROM mytable
WHERE condition;

创建表格

--Create table statement w/ optional table constraint and default value
CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Table data types

Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

Table constraints

Example

--Movies table schema
CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

修改表格

添加列

--Altering table to add new column(s)
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

删除列

--Altering table to remove column(s)
ALTER TABLE mytable
DROP column_to_be_deleted;

重命名表格

--Altering table name
ALTER TABLE mytable
RENAME TO new_table_name;

其他

Documentation: MySQL, Postgres, SQLite, Microsoft SQL Server.

删除表格

--Drop table statement
DROP TABLE IF EXISTS mytable;

注：In addition, if you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.