SQL基础

1. SELECT

SELECT * FROM table_name;

2. Queries with constraints

• WHERE clause

SELECT column,...
FORM table
WHERE condition

SELECT column,...
FORM table
WHERE condition
  AND/OR another_condition

3. Filtering and sorting Query results

• Unique results

SELECT DISTINCT column, another_column, ...
FROM table_name
WHERE condition(s)

• Ordering results

% Select query with ordered results
SELECT column, another_column, ...
FROM table_name
WHERE condition(s)
ORDER BY column ASC/DESC;

• Limiting results to a subset

% Select query with limited rows
SELECT column, another_column, ...
FROM table_name
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset % offset偏移量

eg.

List the last four Pixar movies released (ordered from most recent to least)

SELECT * FROM movies
ORDER BY year DESC
LIMIT 4;

eg.

List the next five Pixar movies sorted alphabetically

SELECT * FROM movies
ORDER BY title ASC
LIMIT 5 OFFSET 5;

4. Multi-table queries with JOINs

JOIN clause

• INNER JOIN

% Select query with INNER JOIN on multiple tables
SELECT column, another_table_column, ...
FROM mytable
INNER JOIN another_table
  ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, ... ASC/DESC
LIMIT num_limit OFFSET num_offset;

A process that matchs rows from the first table and the second table with
have the same key to create a result row with combined columns from both
tables.

eg.

SELECT title, rating
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

5. OUTER JOINs

%Select query with LEFT/RIGHT/FULL JOINs on multiple tables
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;

When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

eg.

List all buildings and the distinct employee roles in each building (including empty buildings)

SELECT DISTINCT building_name, role FROM buildings
LEFT JOIN employees
  ON buildings.building_name = employees.building

6. A short note on NULLs

Test a column for NULL values in a WHERE clause by using either IS NULL or IS NOT NULL constraint.

%Select query with constraints on NULL values
SELECT column, another_column, ...
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition

eg.

Find the names of the buildings that hold no employees.

SELECT DISTINCT building_name
FROM buildings 
  LEFT JOIN employees
    ON building_name = building
WHERE role IS NULL;

此题需要仔细思考一下

7. Queries with expressions

% Select query with expression aliases
SELECT col_expression AS expr_description, ...
FROM table;

% Example query with both column and table name aliases
SELECT column AS better_column_name, ...
FROM complex_table_name AS easy_table_name;

8. Queries with aggregates(Pt.1)

% Select query with aggregate function over all rows
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, ...
FROM table
WHERE constraint_expression;

• Common aggregate functions

• Grouped aggregate functions

% Select query with aggregate functions over groups
SELECT AGG_FUNC(column_or_expression) AS aggregate_expression, ...
FROM table
WHERE constraint_expression
GROUP BY column;

The GROUP BY clause works by grouping rows that have the same value in the column specified.

9. Queries with aggregates (Pt. 2)

% Select query with HAVING constraint
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, ...
FROM table
WHERE condition
GROUP BY column
HAVING group_condition;

The HAVING clause constraints are applied to the grouped rows.

The HAVING clause constraints are written the same way as the WHERE clause constraints.

eg.

Find the total number of years employed by all Engineers

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";

result

10. Order of execution of a Query

% Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), ...
FROM table
  JOIN another_table
    ON table.column = another_table.column
  WHERE constraint_expression
  GROUP BY column
  HAVING constraint_expression
  ORDER BY column ASC/DESC
  LIMIT count OFFSET count;

11. Inserting rows

Inserting new data

• Insert statement with values for all columns

INSERT INTO table
VALUES (value_or_expr, another_value_or_expr, ...),
       (value_or_expr_2, another_value_or_expr_2, ...),
       ...;

• Insert statement with specific columns

INSERT INTO table
(column, another_column, ...)
VALUES (value_or_expr, another_value_or_expr, ...),
       (value_or_expr_2, another_value_or_expr_2, ...),
       ...;

注意string格式的双引号

12. Updating rows

Update existing data

• Update statement with values

UPDATE table
SET column = value_or_expr, 
    other_column = another_value_or_expr,
    ...
WHERE condition;

It‘s recommended that you run the constraint in a SELECT query first to ensure that you are updating the right rows.

13. Deleting rows

Delete data from a table in the database

• Delete statement with condition

DELETE FROM table
WHERE condition;

It‘s recommended that you run the constraint in a SELECT query first to ensure that you are removing the right rows.

14. Creating tables

• Create table statement

CREATE TABLE IF NOT EXISTS table_name (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    ...
);

Table data types

Table constraints

eg.

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

15. Altering tables

Adding columns

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

Removing columns

% Altering table to remove column(s)
ALTER TABLE table_name
DROP column_to_be_deleted;

Renaming the table

% Altering table name
ALTER TABLE table_name
RENAME TO new_table_name;

16. Dropping tables

• Drop table statement

DROP TABLE IF EXISTS table_name;

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.

SQL基础

标签：call   another   dir   ice   lease   exist   where   name   between