SQL基础笔记

CREATE TABLE celebs 
    (
    id INTEGER, 
    name TEXT, 
    age INTEGER 
    ); # 第一列id，数据类型整数；第二列name，数据类型文本；第三列age，数据类型整数

INSERT INTO celebs ( id, name, age)
    VALUES ( 1, 'Alan Mathison Turing', 42); # 在celebs表最下方插入数据：id列为1，name列为Alan Mathion Turing，age列为42

SELECT 
    *
FROM
    celebs; # 显示celebs表所有数据

UPDATE celebs 
SET 
    age = 22
WHERE
    id = 1; # 将celebs表中id=1的行的age改为22

ALERT TABLE celebs
ADD COLUMN twitter_handle TEXT AFTER id; # 在celebs表的id列后增加twitter_handle列

ALERT TABLE celebs
DROP email, DROP instagram; # 同时删除celebs的email和instagram两列

ALERT TABLE 'test'.'data'
CHANGE COLUMN 'Mobile' 'Mobile' BLOB NULL DEFAULT NULL; # 将表test.data的Mobile列的数据类型改为BLOB，该列数据默认为NULL

DELETE FROM celebs 
WHERE
    twitter_handle IS NULL; # 删除表celebs中twitter_handle为NULL的行

DELETE FROM course
WHERE
    Cno IN (8,9);

SELECT DISTINCT
    genre
FROM
    movies; # 查询movies表中genre列的所有不重复值

SELECT 
    *
FROM
    movies
WHERE
    imdb_rating > 8; # 查询movies表中imdb_rating大于8的行

SELECT 
    *
FROM
    movies
WHERE
    name LIKE ' Se_en';

SELECT 
    *
FROM
    movies
WHERE
    name LIKE 'a%';

SELECT 
    *
FROM
    KPI
WHERE
    count_process LIKE '%50\%%';

SELECT 
    *
FROM
    movies
WHERE
    name BETWEEN 'A' AND 'J'; # 查询movies中name以A至J开头的所有行

SELECT 
    *
FROM
    movies
WHERE
    year BETWEEN 1990 AND 2000; # 查询movies中year在1990至2000年间的行

SELECT 
    *
FROM
    movies
WHERE
    year BETWEEN 1990 AND 2000
        AND genre = 'comedy'; # 查询movies中year在1990至2000间，且genre为comedy的行

SELECT 
    *
FROM
    movies
WHERE
    genre = 'comedy' OR year < 1980; # 查询movies中genre为comedy，或year小于1980的行

SELECT 
    *
FROM
    movies
ORDER BY imdb_rating DESC; # 查询movies中的行，结果以imdb_rating降序排列

SELECT 
    *
FROM
    movies
ORDER BY imdb_rating ASC
LIMIT 3;  # 查询movies中的行，结果以imdb_rating升序排列，仅返回前3行

SELECT 
    COUNT(*)
FROM
    fake_apps
WHERE 
    price = 0; # 返回fake_apps中price=0的行数

SELECT 
    price, COUNT(*)
FROM
    fake_apps
WHERE
    downloads > 2000
GROUP BY price; # 查询fake_apps表中downloads大于2000的行，将结果集根据price分组，返回price和行数

SELECT 
    category, SUM(downloads)
FROM
    fake_apps
GROUP BY category;

SELECT 
    name, category, MAX(downloads)
FROM
    fake_apps
GROUP BY category;

SELECT 
    name, category, MIN(downloads)
FROM
    fake_apps
GROUP BY category;

SELECT 
    price, AVG(downloads)
FROM
    fake_apps
GROUP BY price;

SELECT 
    price, ROUND(AVG(downloads), 2)
FROM
    fake_apps
GROUP BY price;

CREATE TABLE  artists
    (
    id INTEGER PRIMARY KET,
    name TEXT
    );

SELECT 
    *
FROM
    albums
WHERE
    artist_id = 3;

SELECT 
    albums.name, albums.year, artists.name
FROM
    albums,
    artists;

SELECT 
    *
FROM
    albums
        JOIN
    artists ON albums.artist_id = artists.id; # INNER JOIN等价于JOIN，写JOIN默认为INNER JOIN

SELECT 
    *
FROM
    albums
        LEFT JOIN
    artists ON albums.artist_id = artists.id;

SELECT 
    albums.name AS 'Album',
    albums.year,
    artists.name AS 'Artist'
FROM
    albums
        JOIN
    artists ON albums.artist_id = artists.id
WHERE
    albums.year > 1980;

SELECT 
    *
FROM
    flights
WHERE
    origin IN (SELECT 
            code
        FROM
            airports
        WHERE
            elevation > 2000);

SELECT 
    a.dep_month,
    a.dep_day_of_week,
    AVG(a.flight_count) AS average_flights
FROM
    (SELECT 
        dep_month,
            dep_day_of_week,
            dep_date,
            COUNT(*) AS flight_count
    FROM
        flights
    GROUP BY 1 , 2 , 3) a
WHERE
    a.dep_day_of_week = 'Friday'
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中，每个星期五的平均航班数量

SELECT 
    a.dep_month,
    a.dep_day_of_week,
    AVG(a.flight_distance) AS average_distance
FROM
    (SELECT 
        dep_month,
    dep_day_of_week,
    dep_date,
    SUM(distance) AS flight_distance
    FROM
        flights
    GROUP BY 1 , 2 , 3) a
GROUP BY 1 , 2
ORDER BY 1 , 2; # 返回每个月中，每个周一、周二……至周日的平均飞行距离

SELECT 
    id
FROM
    flights AS f
WHERE
    distance > (SELECT 
            AVG(distance)
        FROM
            flights
        WHERE
            carrier = f.carrier); # the list of all flights whose distance is above average for their carrier

SELECT 
    carrier,
    id,
    (SELECT 
            COUNT(*)
        FROM
            flights f
        WHERE
            f.id < flights.id
                AND f.carrier = flights.carrier) + 1 AS flight_sequence_number
FROM
    flights; # 结果集为航空公司，航班id以及序号。相同航空公司的航班，id越大则序号越大

SELECT 
    item_name
FROM
    legacy_products 
UNION SELECT 
    item_name
FROM
    new_products;

SELECT 
    AVG(sale_price) 
FROM
    (SELECT 
        id, sale_price
    FROM
        order_items UNION ALL SELECT 
        id, sale_price
    FROM
        order_items_historic) AS a;

SELECT category FROM new_products
INTERSECT
SELECT category FROM legacy_products;

SELECT DISTINCT
    category
FROM
    new_products
        INNER JOIN
    legacy_products USING (category);

SELECT DISTINCT
    category
FROM
    new_products
WHERE
    category IN (SELECT 
            category
        FROM
            legacy_products);

SELECT 
    category, COUNT(*)
FROM
    (SELECT 
        category
    FROM
        new_products UNION ALL SELECT 
        category
    FROM
        legacy_products) a
GROUP BY category
HAVING COUNT(*) > 1;

SELECT category FROM legacy_products
EXCEPT # 在Oracle中为MINUS
SELECT category FROM new_products;

SELECT DISTINCT
    category
FROM
    legacy_products
        LEFT JOIN
    new_products USING (category)
WHERE
    new_products.category IS NULL;

SELECT DISTINCT
    category
FROM
    legacy_products
WHERE
    category NOT IN (SELECT 
            category
        FROM
            new_products);

SELECT 
    COUNT(*)
FROM
    flights
WHERE
    arr_time IS NOT NULL
        AND destination = 'ATL';

SELECT
    CASE
        WHEN elevation < 250 THEN 'Low'
        WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
        WHEN elevation >= 1750 THEN 'High'
        ELSE 'Unknown'
    END AS elevation_tier
    , COUNT(*)
FROM airports
GROUP BY 1;

SELECT 
    state,
    COUNT(CASE
        WHEN elevation < 1000 THEN 1
        ELSE NULL
    END) AS count_low_elevaton_airports
FROM
    airports
GROUP BY state; 

SELECT 
    origin,
    SUM(distance) AS total_flight_distance,
    SUM(CASE
        WHEN carrier = 'DL' THEN distance
        ELSE 0
    END) AS total_delta_flight_distance
FROM
    flights
GROUP BY origin; 

SELECT 
    origin,
    100.0 * (SUM(CASE
        WHEN carrier = 'DL' THEN distance
        ELSE 0
    END) / SUM(distance)) AS percentage_flight_distance_from_delta
FROM
    flights
GROUP BY origin; 

SELECT 
    state,
    100.0 * COUNT(CASE
        WHEN elevation >= 2000 THEN 1
        ELSE NULL
    END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
    airports
GROUP BY 1;

SELECT 
    state,
    100.0 * SUM(CASE
        WHEN elevation >= 2000 THEN 1
        ELSE 0
    END) / COUNT(elevation) AS percentage_high_elevation_airports
FROM
    airports
GROUP BY 1;

CREATE TABLE Orders (
    OrderId INT NOT NULL,
    ProductName VARCHAR(50) NOT NULL,
    OrderDate DATETIME NOT NULL DEFAULT NOW (),
    PRIMARY KEY (OrderId)
);

SELECT 
    id,
    carrier,
    origin,
    destination,
    DATE_FORMAT(NOW(), '%Y-%c-%d %T') AS datetime
FROM
    flights;

SELECT 
    DATE(delivery_time), TIME(delivery_time)
FROM
    baked_goods;

SELECT 
    DATE_ADD(delivery_time,
        INTERVAL '2 5:20:00' DAY_SECOND) AS package_time
FROM
    baked_goods; 

SELECT 
    id, GREATEST(cook_time, cool_down_time)
FROM
    baked_goods;

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM
    bakeries;

SELECT 
    state, GROUP_CONCAT(DISTINCT (city)) AS cities
FROM
    bakeries
WHERE
    state IN ('California' , 'New York', 'Texas')
GROUP BY state;

SELECT 
    CONCAT(CAST(distance AS CHAR), ' ', city)
FROM
    bakeries;

SELECT 
    id,
    REPLACE(ingredients,
        'enriched_flour',
        'flour')
FROM
    baked_goods;

SELECT 
    DATE(ordered_at), ROUND(SUM(amount_paid), 2)
FROM
    orders
        JOIN
    order_items ON orders.id = order_items.order_id
WHERE
    name = 'kale-smoothie'
GROUP BY 1
ORDER BY 1;

SELECT 
    name, ROUND(SUM(amount_paid), 2)
FROM
    order_items
GROUP BY name
ORDER BY 2 DESC;

SELECT 
    name,
    ROUND(SUM(amount_paid) / (SELECT 
                    SUM(amount_paid)
                FROM
                    order_items) * 100.0,
            2) AS PCT
FROM
    order_items
GROUP BY 1
ORDER BY 2 DESC;

SELECT 
    *,
    CASE name
        WHEN 'kale-smoothie' THEN 'smoothie'
        WHEN 'banana-smoothie' THEN 'smoothie'
        WHEN 'orange-juice' THEN 'drink'
        WHEN 'soda' THEN 'drink'
        WHEN 'blt' THEN 'sandwich'
        WHEN 'grilled-cheese' THEN 'sandwich'
        WHEN 'tikka-masala' THEN 'dinner'
        WHEN 'chicken-parm' THEN 'dinner'
        ELSE 'other'
    END AS category
FROM
    order_items
ORDER BY id;

SELECT 
    CASE name
        WHEN 'kale-smoothie' THEN 'smoothie'
        WHEN 'banana-smoothie' THEN 'smoothie'
        WHEN 'orange-juice' THEN 'drink'
        WHEN 'soda' THEN 'drink'
        WHEN 'blt' THEN 'sandwich'
        WHEN 'grilled-cheese' THEN 'sandwich'
        WHEN 'tikka-masala' THEN 'dinner'
        WHEN 'chicken-parm' THEN 'dinner'
        ELSE 'other'
    END AS category,
    ROUND(1.0 * SUM(amount_paid) / (SELECT 
                    SUM(amount_paid)
                FROM
                    order_items) * 100,
            2) AS PCT
FROM
    order_items
GROUP BY 1
ORDER BY 2 DESC;

SELECT 
    name,
    ROUND(1.0 * COUNT(DISTINCT order_id) / COUNT(DISTINCT delivered_to),
            2) AS reorder_rate
FROM
    order_items
        JOIN
    orders ON orders.id = order_items.order_id
GROUP BY 1
ORDER BY 2 DESC;

SELECT 
    DATE(created_at), ROUND(SUM(price), 2)
FROM
    purchases
GROUP BY 1
ORDER BY 1;

SELECT 
    DATE(created_at), ROUND(SUM(price), 2) AS daily_rev
FROM
    purchases
WHERE
    refunded_at IS NULL
GROUP BY 1
ORDER BY 1;

SELECT 
    DATE(created_at), COUNT(DISTINCT user_id) AS DAU
FROM
    gameplays
GROUP BY 1
ORDER BY 1;

SELECT 
    DATE(created_at), platform, COUNT(DISTINCT user_id) AS DAU
FROM
    gameplays
GROUP BY 1 , 2
ORDER BY 1 , 2;

SELECT 
    DATE(created_at),
    ROUND(SUM(price) / COUNT(DISTINCT user_id), 2) AS ARPPU
FROM
    purchases
WHERE
    refunded_at IS NULL
GROUP BY 1
ORDER BY 1;

WITH daily_revenue AS 
    (
    SELECT 
        date(created_at) AS dt,
        ROUND(SUM(price), 2) AS rev
    FROM
        purchases
    WHERE refunded_at IS NULL
    GROUP BY 1
    )
SELECT 
    * 
FROM 
    daily_revenue 
ORDER BY dt;

WITH daily_revenue AS (
  SELECT
    DATE(created_at) AS dt,
    ROUND(SUM(price), 2) AS rev
  FROM purchases
  WHERE refunded_at IS NULL
  GROUP BY 1
), 
daily_players AS (
  SELECT
    DATE(created_at) AS dt,
    COUNT(DISTINCT user_id) AS players
  FROM gameplays
  GROUP BY 1
)
SELECT
  daily_revenue.dt,
  daily_revenue.rev / daily_players.players
FROM daily_revenue
  JOIN daily_players USING (dt);

SELECT 
    DATE(g1.created_at) AS dt, g1.user_id
FROM
    gameplays AS g1
        JOIN
    gameplays AS g2 ON g1.user_id = g2.user_id
ORDER BY 1
LIMIT 100;

SELECT 
    DATE(g1.created_at) AS dt,
    ROUND(100 * COUNT(DISTINCT g2.user_id) / COUNT(DISTINCT g1.user_id)) AS retention
FROM
    gameplays AS g1
        LEFT JOIN
    gameplays AS g2 ON g1.user_id = g2.user_id
        AND DATE(g1.created_at) = DATE(DATE_SUB(g2.created_at, INTERVAL 1 DAY))
GROUP BY 1
ORDER BY 1;

SELECT 
    COUNT(DISTINCT amount_paid)
FROM
    order_items;

SELECT 
    COUNT(1)
FROM
    (SELECT 
        1
    FROM
        order_items
    GROUP BY amount_paid) a;

SELECT 
    SUM(1)
FROM
    (SELECT 
        1
    FROM
        order_items
    GROUP BY amount_paid) a;

SELECT 
    *
FROM
    orders
WHERE
    EXISTS( SELECT 
            1
        FROM
            orders o
                JOIN
            order_items i ON o.id = i.order_id);

MySqlStr="SELECT * FROM table WHERE";

　　IF(Age.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+"Age="+"'Age.Text'";
　　}

　　IF(Address.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+"AND Address="+"'Address.Text'";
　　}

SELECT 
    *
FROM
    table
WHERE
    1 = 0; 

CREATE TABLE newtable AS SELECT * FROM
    oldtable
WHERE
    1 = 0;  

CREATE TABLE newtable AS SELECT * FROM
    oldtable;  

SELECT 
    Customer, SUM(OrderPrice)
FROM
    Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000;

SELECT 
    Customer, SUM(OrderPrice)
FROM
    Orders
WHERE
    Customer = 'Bush' OR Customer = 'Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500;

SELECT 
    *
FROM
    world.City
        JOIN
    world.Country ON (City.CountryCode = Country.Code) 
WHERE ...

SELECT 
    film.title, film_id # film_id is not prefixed
FROM
    film
        JOIN
    film_actor USING (film_id)
WHERE ...

SELECT 
    film.title, film.film_id # film.film_id is required here
FROM
    film
        JOIN
    film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

SELECT
    ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM
    Products;

SELECT
    ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM
    Products;

SELECT
    ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM
    Products;

SELECT 
    LCASE(first_name) AS first_name,
    last_name,
    city,
    UCASE(state) AS state
FROM
    bakeries;

SELECT 
    MID(column_name, start, length)
FROM
    table_name;

SELECT 
    MID(state, 1, 3) AS smallstate
FROM
    bakeries;

SELECT 
    LENGTH(City) AS LengthOfCity
FROM
    bakeries;

SELECT 
    Sname
FROM
    student
WHERE
    EXISTS( SELECT 
            *
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno);

SELECT 
    Cname
FROM
    course
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            sc
        WHERE
            Sno = 200215123
                AND Cno = course.Cno);

SELECT 
    Sname
FROM
    student
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            course
        WHERE
            NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Sno = student.Sno AND Cno = course.Cno));

SELECT 
    Cname
FROM
    course
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            student
        WHERE
            NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Cno = course.Cno AND Sno = student.Sno));

SELECT 
    DISTINCT Sno
FROM
    sc scx
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            sc scy
        WHERE scy.Sno = 200215123
           AND NOT EXISTS( SELECT 
                    *
                FROM
                    sc
                WHERE
                    Sno = scx.Sno AND Cno = scy.Cno));

SELECT 
    Sname
FROM
    student
WHERE
    EXISTS( SELECT 
            *
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno
                AND course.Cname = '操作系统');

SELECT 
    Sname
FROM
    student
WHERE
    Sno IN (SELECT 
            Sno
        FROM
            sc,
            course
        WHERE
            sc.Sno = student.Sno
                AND sc.Cno = course.Cno
                AND course.Cname = '操作系统');