SQL

SQL

結構式查詢語言 (SQL) 是一種用於在關聯式資料庫中儲存和處理資訊的程式設計語言

sqliteviz

papaya

Foreign Key: a reference in one table’s records to the primary key of another table

Primary Key: used to uniquely identify each record in that table

AND/OR

SELECT model
FROM cars
WHERE color = 'blue'
  AND year > 2014;

AS(rename)

SELECT name AS 'movie_title'
FROM movies;

LIKE / %

'%a'特定結尾 '%a%'特定中間 'a%'特定開頭

SELECT name
FROM movies
WHERE name LIKE 'The%';

_(底線)

SELECT name
FROM movies
WHERE name LIKE '_ove'

ORDER BY

SELECT *
FROM contacts
ORDER BY birth_date DESC;

DISTINCT

SELECT DISTINCT city
FROM contact_details;

BETWEEN(The range of alues can be text, numbers, or date data.)

SELECT *
FROM movies
WHERE year BETWEEN 1980 AND 1990;

LIMIT/OFFSET

SELECT *
FROM movies
LIMIT 5 OFFSET 2; 

NULL

SELECT address
FROM records
WHERE address IS NOT NULL;

CREATE TABLE

CREATE TABLE student (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
grade INTEGER NOT NULL,
age INTEGER DEFAULT 10
);

Insert into

-- Insert into columns in order:
INSERT INTO table_name
VALUES (value1, value2);
-- Insert into columns by name:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype;

DELETE

DELETE FROM table_name
WHERE some_column = some_value;

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

Aggregate Functions

GROUP BY

SELECT COUNT(*) AS 'total_movies',
rating
FROM movies
GROUP BY 2
ORDER BY 1;

MAX/MIN/AVG/SUM

SELECT MAX(amount)
FROM transactions;

COUNT

SELECT COUNT(*)
FROM employees
WHERE experience < 5;

HAVING

SELECT year,
COUNT(*)
FROM movies
GROUP BY year
HAVING COUNT(*) > 5;

ROUND()

SELECT year,
ROUND(AVG(rating), 2)
FROM movies
WHERE year = 2015;

Outer Join

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name =
table2.column_name;

Inner Join

SELECT *
FROM books
JOIN authors
ON books.author_id = authors.id;

WITH

WITH temporary_movies AS (
SELECT *
FROM movies
)
SELECT *
FROM temporary_movies
WHERE year BETWEEN 2000 AND 2020;

UNION

SELECT name
FROM first_names
UNION
SELECT name
FROM last_names

CROSS JOIN

SELECT shirts.shirt_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;

字串長度

CHAR_LENGTH()

COALESCE

SELECT COALESCE(
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1),
    NULL
) AS SecondHighestSalary;