SQL笔记

最新推荐文章于 2024-05-10 14:06:22 发布

喝醉的cincin

最新推荐文章于 2024-05-10 14:06:22 发布

阅读量203

点赞数

本文链接：https://blog.csdn.net/qq_39543783/article/details/84995529

版权

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%’ 前后都加% %