SQL

Mysql

/usr/local/mysql/bin/mysql -uroot -p

 

 

sql不分大小写！

 

SELECT * FROM people LIMIT 10;

SELECT DISTINCT language FROM films;

SELECT COUNT(*) FROM people;

SELECT COUNT(birthdate) FROM people;

SELECT COUNT(DISTINCT birthdate) FROM people;

SELECT title FROM films WHERE title = 'Metropolis'; must be single quot

SELECT * FROM films WHERE budget > 10000;

Use <> but not !=

SELECT title FROM films WHERE release_year > 1994 AND release_year < 2000;

SELECT title FROM films WHERE release_year > 1994 AND < 2000;

SELECT title FROM films WHERE (release_year = 1994 OR release_year = 1995) AND (certification = 'PG' OR certification = 'R');

SELECT title FROM films WHERE release_year BETWEEN 1994 AND 2000;

SELECT name FROM kids WHERE age IN (2, 4, 6, 8, 10);

SELECT name FROM people WHERE birthdate IS NOT NULL;

SELECT name FROM companies WHERE name LIKE 'Data%';  

SELECT name FROM companies WHERE name LIKE 'Data%';

SELECT AVG(budget) FROM films;

 

MAX () MIN() SUM() AVG(), but cant use after WHERE clause

 

SELECT (4.0 / 3.0) AS result;  1.333

SELECT (4 / 3); = 1

 

 Can perform + - * /

SELECT MAX(budget) AS max_budget,

       MAX(duration) AS max_duration

FROM films;

 

SELECT AVG(duration/60.0) AS avg_duration_hours

FROM films;  必须是60.0 不然不行

SELECT title FROM films

ORDER BY release_year, title DESC;

SELECT sex, count(*)

FROM employees

GROUP BY sex

ORDER BY count DESC;

SELECT release_year

FROM films

GROUP BY release_year

HAVING COUNT(title) > 10;

SELECT title, imdb_score FROM films

JOIN reviews

ON films.id = reviews.film_id

WHERE title = 'To Kill a Mockingbird';

SELECT cities.name AS city, countries.name AS country

FROM cities

  INNER JOIN countries

    ON cities.country_code = countries.code;

SELECT c1.name AS city, c2.name AS country

FROM cities AS c1

INNER JOIN countries AS c2

ON c1.country_code = c2.code; 

SELECT *

FROM left_table

  INNER JOIN right_table

    ON left_table.id = right_table.id

  INNER JOIN another_table

    ON left_table.id = another_table.id;

SELECT *

FROM countries

  INNER JOIN economies

    USING(code)

SELECT name, continent, code, surface_area,

    CASE WHEN surface_area > 2000000

            THEN 'large'

       WHEN surface_area > 350000

            THEN 'medium'

       ELSE 'small' END

       AS geosize_group

INTO countries_plus

FROM countries;

Multiple line comment /* And */

Left join, right join, full join

SELECT NAME

  FROM cities

EXCEPT / intersect / Union/ Union all

SELECT capital

  FROM countries

ORDER BY name; 

Semi-join

SELECT DISTINCT name

FROM languages

WHERE code IN

  (SELECT code

   FROM countries

   WHERE region = 'Middle East')

ORDER BY name;

SELECT max(inflation_rate) as max_inf

  FROM (

      SELECT name, continent, inflation_rate

      FROM countries

      INNER JOIN economies

      USING (code)

  WHERE year = 2015) AS subquery

GROUP BY continent;

SELECT name, continent, inflation_rate

  FROM countries

INNER JOIN economies

USING (code)

  WHERE year = 2015

    and inflation_rate IN (

        SELECT MAX(inflation_rate) AS max_inf

        FROM (

             SELECT name, continent, inflation_rate

             FROM countries

             INNER JOIN economies

             USING (code)

             WHERE year = 2015) AS subquery

        GROUP BY continent);

 

SELECT DISTINCT name, total_investment, imports

  FROM countries AS c

    LEFT JOIN economies AS e

      ON (c.code = e.code

        AND c.code IN (

          SELECT l.code

          FROM languages AS l

          WHERE official = 'true'

        ) )

  WHERE region = 'Central America' AND year = 2015

ORDER BY name;

 

 

SELECT region, continent, AVG(fertility_rate) AS avg_fert_rate

  FROM countries AS c

    INNER JOIN populations AS p

      ON c.code = p.country_code

  WHERE year = 2015

GROUP BY region, continent

ORDER BY avg_fert_rate;

 

SELECT cities.name, cities.country_code, city_proper_pop, metroarea_pop, 

      city_proper_pop / metroarea_pop * 100.0 AS city_perc

  FROM cities

  WHERE name IN

    (SELECT capital

     FROM countries

     WHERE continent = 'Europe'

        OR continent LIKE '%America')

        AND metroarea_pop IS NOT NULL

ORDER BY city_perc DESC

LIMIT 10

 

SELECT * FROM table WHERE CASE WHEN a > 5 THEN 'Keep'

WHEN a <= 5 THEN 'Exclude' END = 'Keep'; 

 

SELECT season, date, home_goal, away_goal FROM matches_italy WHERE -- Exclude games not won by Bologna CASE WHEN hometeam_id = 9857 and home_goal > away_goal THEN 'Bologna Win' WHEN awayteam_id = 9857 AND away_goal > home_goal THEN 'Bologna Win'

END IS NOT NULL; 

SELECT c.name AS country, -- Count games from the 2012/2013 season COUNT (CASE WHEN m.season = '2012/2013' THEN m.id ELSE NULL END) AS matches_2012_2013 FROM country AS c LEFT JOIN match AS m ON c.id = m.country_id -- Group by country name alias GROUP BY country;

-- percentage of game tied

SELECT c.name AS country, -- Round the percentage of tied games to 2 decimal points ROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1 WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0 END),2) AS pct_ties_2013_2014, ROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1 WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0 END),2) AS pct_ties_2014_2015 FROM country AS c LEFT JOIN matches AS m ON c.id = m.country_id GROUP BY country;

Windows:

SELECT m.away_goal, AVG(m.home_goal + m.away_goal) over() AS overall_avg FROM match AS m

SELECT id, RANK() OVER(ORDER BY home_goal) AS rank FROM match;

rank() over(ORDER BY AVG(m.home_goal + m.away_goal)) AS league_rank ORDER BY league_rank;

SELECT id, RANK() OVER(ORDER BY home_goal DESC) AS rank FROM match;

AVG(home_goal) OVER(PARTITION BY season, EXTRACT(month FROM date)) AS season_mo_home,

Sliding to the left:

SELECT date, home_goal, away_goal, -- Create a running total and running average of home goals SUM(home_goal) over(ORDER BY DATE rows between unbounded preceding and current row) AS running_total, AVG(home_goal) OVER(ORDER BY date rows between unbounded preceding and current row) AS running_avg FROM match WHERE hometeam_id = 9908 AND season = '2011/2012';

Slide to the right:

-- Create a running total and running average of home goals SUM(home_goal) over(ORDER BY DATE DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total, AVG(home_goal) OVER(ORDER BY DATE DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_avg

-- Set up the home team CTE with home AS( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Win' WHEN m.home_goal < m.away_goal THEN 'MU Loss' ELSE 'Tie' END AS outcome FROM match AS m LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id), -- Set up the away team CTE away AS( SELECT m.id, t.team_long_name, CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss' WHEN m.home_goal < m.away_goal THEN 'MU Win' ELSE 'Tie' END AS outcome FROM match AS m LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id) -- Select columns and and rank the matches by date SELECT DISTINCT m.DATE, home.team_long_name AS home_team, away.team_long_name AS away_team, m.home_goal, m.away_goal, rank() over(ORDER BY ABS(home_goal - away_goal) DESC) as match_rank -- Join the CTEs onto the match table FROM match AS m LEFT JOIN home ON m.id = home.id LEFT JOIN away ON m.id = away.id WHERE m.season = '2014/2015' AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'MU Loss') OR (away.team_long_name = 'Manchester United' AND away.outcome = 'MU Loss'));