SQL笔记

My SQL 笔记

第一节：入门语句

连接服务器之后先选择“库”test（库名臣）
use test

查看所有的库:
show datebase

选择库之后查看表
show tables

表往下就是数据

• 创建一个 库
  create database zss(name)

• 删除一个 库
  drop database zss(name)

• 给数据库改名
  表、列可以改名，但是database不可以改名
  Rename table oldname to newname

最常用选择语句

Select from在一个级中只可以使用一次，否则不知道选了谁出来
要选择两列则用 Select a，b from xx
如果选择所有列 则用 Select * from xx
命令语句不区分大小写
Select distinct a from b选出不重复的项，去掉重复项了

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

以上可以代替很多和 or

在SQL中，NULL表示缺少值或未知值。
IS NOT NULL过滤掉NULL值的行

SELECT name
FROM companies
WHERE name LIKE 'Data%'

但是含有 Data的字样都找出来了
NOT LIKE就是不包含这些词语的
还可以是‘_B’找到第二个letter是B的项目
eg:release_year

select count(deathdate) *100.0/count (*) as percentage_dead  %可以直接定义一个新变量名称
from people

可以得到还没死的人所占的百分比

SELECT title
FROM films
ORDER BY release_year DESC

如果要按降序对结果进行排序，可以使用DESC关键字。
升序 不加后缀

SELECT birthdate, name
FROM people
ORDER BY birthdate, name;       %sorts on birth dates first (从大到小) and then sorts on the names in alphabetical order. 
%The order of columns is important!

GROUP BY按一个或多个列对结果进行分组

SELECT sex, count(*)
FROM employees
GROUP BY sex
%可以知道男女分别有多少人

select release_year, country, max(budget)
from films
group by release_year,country
order by release_year,country

最后加一行 order得到的结果会整齐很多

select release_year,avg(budget) as avg_budget,avg(gross) as avg_gross
from films
where release_year > 1990
group by release_year
having avg(budget) > 60000000

疑问：和在where release_year > 1990后面加AND avg(budget) > 60000000有什么区别？？

如果您只想返回一定数量的结果，可以使用LIMIT关键字来限制返回的行数

where the country has more than 10 titles可以选出有10个入选名单的国家

having count(title) > 10

连接两个表

In SQL, this concept is known as a join, and a basic join is shown in the editor to the right.
您需要从电影表中获取电影的ID，然后使用它从评论表中获取IMDB信息。
在SQL中，此概念称为连接，基本连接在右侧的编辑器中显示。

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 countries, region
from cities
inner join countries
on country_code = code   
#两个表格中内容相同的一行，这样才可以选择有共同row的留下来
   如果两列的名称都是code 那就用  using（code）

自己join自己的用法

疑问： 可以得到2010的size和2015年的，感觉好神奇，明明没有定义

nner join

SELECT p1.country_code, 
           p1.size AS size2010,
           p2.size AS size2015
    FROM populations AS p1   ##语句不一定按顺序运行，后面的可以作用在前面
    inner JOIN populations AS p2   
    ON  p1.country_code = p2.country_code

SELECT p1.country_code,
       p1.size AS size2010,
       p2.size AS size2015
FROM populations AS p1
INNER JOIN populations AS p2
ON p1.country_code = p2.country_code
AND p1.year = p2.year - 5

会删除相同的row，好看多了

Case when then 的用法

SELECT name, continent, code, surface_area,
    CASE WHEN surface_area > 2000000 THEN 'large'
        WHEN surface_area > 350000 THEN 'medium'
        ELSE 'small' END（不要忘了END）
        AS geosize_group
FROM countries;  

可以另外添加一列geosize_group 内容是’’内的东西，但是完全不用ADD相关的命令，好神奇

SELECT country_code, size,
    CASE WHEN size > 50000000 THEN 'large'
        WHEN size > 1000000 THEN 'medium'
        ELSE 'small' END
        AS popsize_group   ##可以另外添加一列geosize_group 内容是‘’内的东西
INTO pop_plus   这个pop_plus是凭空冒出来的一个table，不用新建可以
FROM populations  
WHERE year = 2015;       ##；不可以漏掉
select *
from pop_plus

• Use LIKE to choose the Melanesia and Micronesia regions

SELECT region, avg(gdp_percapita) as avg_gdp
FROM countries AS c
LEFT JOIN economies AS e
ON c.code = e.code
WHERE year = 2010 
GROUP BY region
ORDER BY avg_gdp desc;

对比两种方法

SELECT distinct president, country, continent
FROM presidents
WHERE country IN
    (SELECT name
     FROM states
     WHERE indep_year < 1800);    ##Order 不要写在where前面，应该写在最后

设定continent的条件去select

SELECT DISTINCT continent,
    (SELECT COUNT(*)
     FROM states
     WHERE prime_ministers.continent = states.continent) AS countries_num）
FROM prime_ministers;

对比一下两个程序，实现同一个目的

/* 
SELECT countries.name AS country, COUNT(*) AS cities_num
FROM cities
INNER JOIN countries
ON countries.code = cities.country_code
GROUP BY country
ORDER BY cities_num DESC, country
LIMIT 9;
*/

SELECT countries.name AS country,
  (SELECT COUNT(*)
   FROM cities
   WHERE countries.code = cities.country_code) AS cities_num
FROM COUNTRIES
ORDER BY cities_num DESC, country
LIMIT 9; 

   
 上面两个程序的结果相同，第二个虽然没有group by但是可以起到同样的效果

建立一个table

SELECT DISTINCT monarchs.continent, subquery.max_perc
FROM monarchs,    
    (SELECT continent, MAX(women_parli_perc) AS max_perc
     FROM states
     GROUP BY continent) AS subquery
WHERE monarchs.continent = subquery.continent
ORDER BY continent;   #相当于创建了一个叫subquery的table

复杂的例子：

SELECT name,continent,inflation_rate
  FROM countries
  INNER JOIN economies
  ON countries.code = economies.code
  AND inflation_rate IN (
        SELECT MAX(inflation_rate) AS max_inf
        FROM (
             SELECT name, continent, inflation_rate
             FROM countries
             INNER JOIN economies
             ON countries.code = economies.code
             WHERE year = 2015) economies
        GROUP BY continent);

'%Republic%’ 前后都加% %