大数据第23天-数据处理之查询-杨大伟

cd /d D:\MyWork\MySQL\mysql-5.7.28-winx64\bin rem 目的是为了执行mysql.exe
mysql -h127.0.0.1 -P3306 -uroot -p123456 rem 作用是为了让客户端能够正确的连接服务器
mysql --host=127.0.0.1 --port=3306 --user=root --password=123456 world

-- 查看服务器中有哪些数据库
show databases;

-- 创建新的数据库
create database company;

-- 切换工作数据库
use company;

-- 查看当前数据库中的表
show tables;

-- 导入.sql文件中的数据
source d:/company.sql;

-- 查看表中的数据
select * from employees;

-- 练习 : 创建world数据库, 并切换, 再导入world.sql文件中的数据到world库.
create database world;

use world;

source d:/world.sql;

-- 丢弃数据库
drop database company;

drop database world;

-- 查看当前工作数据库
select database();

-- 查看当前工作数据库的版本
select version();

-- 查看服务器所有参数
show variables;

-- 和字符集设置相关的参数 : 
character_set_client                                     | gbk
character_set_connection                                 | gbk
character_set_database                                   | utf8
character_set_filesystem                                 | binary
character_set_results                                    | gbk
character_set_server                                     | utf8
character_set_system                                     | utf8

-- 如果client,connection和result的设置不是gbk, 必须执行以下语句

-- 修改客户端编码
set names gbk;

数据库结构
    mysqld 服务器
        数据库1(目录形式)
            表1
                数据(记录)1
                数据(记录)2
                数据(记录)3
                ....
            表2 
            表3
            ....
            
        数据库2
        ......
        
-- 查看表中数据
select * from 表名; 

-- 查看表结构
describe employees;
desc employees;

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

create table customer(
    id int,
    name varchar(20),
    age int,
    phone varchar(15),
    birthday date
);

insert into customer (
    id,
    name,
    age,
    phone,
    birthday
) values (
    1,
    '张三',
    30,
    '134234234',
    '1992-5-8'
);

insert into customer (
    id,
    name,
    age,
    phone,
    birthday
) values (
    2,
    '李四',
    40,
    '1599234234',
    '1991-5-8'
);

insert into customer (
    id,
    name,
    age,
    phone,
    birthday
) values (
    3,
    '王五',
    50,
    '135234234',
    '1995-11-8'
);

-- 更新记录(修改数据)
-- 如果更新时不加where过滤, 会导致所有数据被修改
update customer set 
    name = '某人',
    age = 10;
    
update customer set 
    name = '张三',
    age = 30
where 
    id = 1;
    
-- 删除记录
-- 如果没有where过滤, 会全部删除.
delete from customer;

delete from customer 
where id = 2;


DML 数据操纵语言.
insert C
select R 
update U
delete D

+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)   国家代码                                                                            | NO   | PRI |         |       |
| Name           | char(52)  国家名称                                                                    | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)          地区                                                                    | NO   |     |         |       |
| SurfaceArea    | float(10,2)       国土面积m                                                                    | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)       独立年                                                                   | YES  |     | NULL    |       |
| Population     | int(11)           国家人口                                                              | NO   |     | 0       |       |
| LifeExpectancy | float(3,1)        平均寿命                                                                    | YES  |     | NULL    |       |
| GNP            | float(10,2)       国民生产总值                                                                    | YES  |     | NULL    |       |
| GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)          政府组织                                                                    | NO   |     |         |       |
| HeadOfState    | char(60)          领导人                                                                    | YES  |     | NULL    |       |
| Capital        | int(11)           首都                                                                    | YES  |     | NULL    |       |
| Code2          | char(2)                                                                                   | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
city
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
SELECT *
FROM   departments;

SELECT department_id, location_id
FROM   departments;

select 
    code,
    name,
    continent
from 
    country;
    
select 
    continent,
    name,
    code
from 
    country;

SQL注意点 :     
    SQL 语言大小写不敏感。 
    SQL 可以写在一行或者多行
    关键字不能被缩写也不能分行
    各子句一般要分行写。
    使用缩进提高语句的可读性。
    
-- 查询国家的人口,名称和代码及首都.
select 
    population,
    name,
    code,
    capital
from 
    country;

-- AS用于给列起别名, AS关键字可以省略
SELECT last_name AS name, commission_pct comm
FROM   employees;

-- 别名可以使用""包围, 目的是让它能原样显示或包含特殊的符号
select 
    code 国家代码,
    name,
    continent as "国家 大洲"
from 
    country;

-- 过滤行 where 条件布尔 : 工作流程就是从基本表中测试每行数据, 都经过条件布尔测试一下, 如果结果为真留下, 为假则丢弃.     
SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

-- 查询所有亚洲国家
select 
    code,
    population,
    name
from 
    country 
where 
    continent = 'asia';
    
-- 查询所有亚洲国家 , 下面的SQL是错误的, where中不可以使用列的别名, 因为where先执行.
select 
    code,
    population,
    name,
    continent cont
from 
    country 
where 
    cont = 'asia';
    
执行顺序 : from => where => select 

--查询所有中国城市的人口和名称和id号和国家代码, 给国家代码起别名, 尝试在where中使用别名.
select 
    population,
    name,
    id,
    countrycode code 
from 
    city 
where 
    countrycode = 'chn';

-- between a and b --等效于salary >= a && salary <= 3500    
SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

-- id in(a, b, c) -- 等效于id = a || id = b || id = c 
SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

select 
    code,
    continent,
    name,
    population
from 
    country 
where 
    name like 'china'; -- name = 'china' 如果模糊查询中的字符串没有通配符, 和=效果一样.
    
SELECT    first_name
FROM     employees
WHERE    first_name LIKE 'S%';

% 代表任意个任意字符
_ 代表一个任意字符
-- 查询国家名称中只要包含ch的都行

select 
    code,
    population,
    name,
    continent 
from 
    country 
where 
    name like '%ch%'
    
-- 名字中第3个和第4个是in的国家
select 
    code,
    population,
    name,
    continent 
from 
    country 
where 
    name like '__in%'
    
-- 查询城市表中, 名称的第2个和3个字母是or的城市.
select 
    id,
    name,
    countrycode 
from 
    city 
where 
    name like '_or%';
    
-- 查看哪些国家没有首都
-- null和任意的比较运算, 结果一定是false, 处理null, 必须使用特殊的is来判断
-- null值在进行统计时会被自动忽略.
select 
    code,
    name,
    continent,
    capital
from 
    country 
where 
    capital = null;
    
select 
    code,
    name,
    continent,
    capital
from 
    country 
where 
    capital is null;
    
select 
    code,
    name,
    continent,
    capital
from 
    country 
where 
    capital is not null;

-- 哪些国家尚未独立.
select 
    name,
    code,
    indepYear
from 
    country 
where 
    indepYear is null;


SELECT 
    employee_id, last_name, job_id, salary
FROM   employees
WHERE  
        salary >=10000
    AND    
        job_id LIKE '%MAN%';


SELECT 
    employee_id, last_name, job_id, salary
FROM   employees
WHERE  
        salary >= 10000
    OR     
        job_id LIKE '%MAN%';

-- 查询亚洲国家中人口大于5000万的国家.
select 
    code,
    name,
    continent,
    population
from 
    country 
where 
        continent = 'asia'
    and 
        population > 50000000;

-- 查询中国的城市人口小于20万的城市.
select 
    *
from 
    city 
where 
    countrycode = 'chn'
    and 
    population < 200000;
    
-- 去重 distinct, 后面的列最好是有重复数据的
select 
    distinct 
        continent,
        region
from 
    country;
    
-- 查看中国各有哪些不同的省.
select 
    distinct district
from 
    city 
where 
    countrycode = 'chn';

SELECT   last_name, job_id, department_id, salary
FROM     employees
ORDER BY salary asc;

SELECT   last_name, job_id, department_id, salary
FROM     employees
ORDER BY salary desc;

select 
    code,
    name,
    population pop 
from 
    country 
where 
    continent = 'asia'
order by 
    pop;

order by中可以使用列的别名, 原因是它最后执行

顺序 : from => where => select => order by 


SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id asc, salary DESC;

-- 查询亚洲人口最少的国家
select 
    code,
    name,
    continent,
    population pop 
from 
    country 
where 
    continent = 'asia'
order by 
    pop desc;

-- 查询山东省人口最多的城市
select 
    id,
    name,
    district,
    population
from 
    city 
where 
        countrycode = 'chn'
    and 
        district = 'shandong'
order by 
    population
    

    
-- 多表联接 : 会产生笛卡尔集
-- 为了学习, 创建简单表
create table city2
select * from city where name='london';
+------+--------+-------------+----------+------------+
| ID   | Name   | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
|  456 | London | GBR         | England  |    7285000 |
| 1820 | London | CAN         | Ontario  |     339917 |
+------+--------+-------------+----------+------------+

create table country2
select * from country where code in('gbr', 'can');
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------
| Code | Name           | Continent     | Region          | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------
| CAN  | Canada         | North America | North America   |  9970610.00 |      1867 |   31147000 |           79.4 |  598862.00 |  625626.00 | Canada
| GBR  | United Kingdom | Europe        | British Islands |   242900.00 |      1066 |   59623400 |           77.7 | 1378330.00 | 1296830.00 | United Kingd
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------

select * from city2, country2; 
结果集中的绝大多数都是垃圾. 必须要过滤掉
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--
| ID   | Name   | CountryCode | District | Population | Code | Name           | Continent     | Region          | SurfaceArea | IndepYear | Population | L
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--
|  456 | London | GBR         | England  |    7285000 | CAN  | Canada         | North America | North America   |  9970610.00 |      1867 |   31147000 |
| 1820 | London | CAN         | Ontario  |     339917 | CAN  | Canada         | North America | North America   |  9970610.00 |      1867 |   31147000 |
|  456 | London | GBR         | England  |    7285000 | GBR  | United Kingdom | Europe        | British Islands |   242900.00 |      1066 |   59623400 |
| 1820 | London | CAN         | Ontario  |     339917 | GBR  | United Kingdom | Europe        | British Islands |   242900.00 |      1066 |   59623400 |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--

select 
    * 
from 
    city2, 
    country2
where 
    countrycode = code;
    
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+-
| ID   | Name   | CountryCode | District | Population | Code | Name           | Continent     | Region          | SurfaceArea | IndepYear | Population |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+-
| 1820 | London | CAN         | Ontario  |     339917 | CAN  | Canada         | North America | North America   |  9970610.00 |      1867 |   31147000 |
|  456 | London | GBR         | England  |    7285000 | GBR  | United Kingdom | Europe        | British Islands |   242900.00 |      1066 |   59623400 |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+-

-- 下面的SQL是错误的, 因为列名模糊
select 
    name,
    population,
    name,
    continent,
    population
from 
    city2, 
    country2
where 
    countrycode = code;
    
select 
    city2.name as cityName,
    city2.population cityPop,
    country2.name countryName,
    country2.continent,
    country2.population countryPop
from 
    city2, 
    country2
where 
    city2.countrycode = country2.code;
    
-- 表名的使用也麻烦了. 给表起别名    
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci , 
    country2 co 
where 
    ci.countrycode = co.code;

-- 表名一旦指定了别名, 原名就不可以使用了.
select 
    city2.name as cityName,
    city2.population cityPop,
    country2.name countryName,
    country2.continent,
    country2.population countryPop
from 
    city2 as ci, 
    country2 as co 
where 
    city2.countrycode = country2.code;
    
-- 查询所有国家的名称和国家的首都的名称.
select 
    co.name country,
    ci.name captial,
    co.population countryPop,
    ci.population cityPop,
    co.continent 
from 
    country co,
    city ci 
where 
    co.capital = ci.id
    
-- where中有多个条件.
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci , 
    country2 co 
where 
        ci.countrycode = co.code -- 联接条件, 比普通过滤更重要.
    and 
        ci.population > 1000000
        
+----------+---------+----------------+-----------+------------+
| cityName | cityPop | countryName    | continent | countryPop |
+----------+---------+----------------+-----------+------------+
| London   | 7285000 | United Kingdom | Europe    |   59623400 |
+----------+---------+----------------+-----------+------------+

SQL99标准中 内联接不要用,号来写, 而是使用专门的关键字join.... on 联接条件
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci 
inner join 
    country2 co 
on 
    ci.countrycode = co.code 
where 
    ci.population > 1000000;
    
-- on 和 where 效果相同, 但是下面的写法不推荐
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci 
inner join 
    country2 co 
where 
        ci.countrycode = co.code 
    and 
        ci.population > 1000000;
    
-- on 和 where 效果相同, 但是下面的写法不推荐    
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci 
inner join 
    country2 co 
on 
        ci.countrycode = co.code 
    and 
        ci.population > 1000000;
    
-- 最好的写法是下面的.    inner关键字可以省略
select 
    ci.name as cityName,
    ci.population cityPop,
    co.name countryName,
    co.continent,
    co.population countryPop
from 
    city2 as ci 
join 
    country2 co 
on -- on后面只跟 联接条件 
    ci.countrycode = co.code 
where -- where 后面只跟  普通行过滤
    ci.population > 1000000;
    
-- 查询所有亚洲和欧洲国家的首都, 使用SQL99
select 
    co.name country,
    co.continent,
    ci.name capital
from 
    country co 
join 
    city ci 
on 
    co.capital = ci.id 
where 
    co.continent in('asia', 'europe');
    
-- 查看所有国家名称和首都及官方语言
select 
    co.name country,
    co.continent,
    ci.name capital,
    cl.language
from 
    country co 
join 
    city ci 
on 
    co.capital = ci.id 
join 
    countrylanguage cl 
on 
    cl.countrycode = co.code 
where 
    cl.isofficial = 't';
    
-- 查询简单表的国家及首都
-- 内联接的结果总是所有联接条件为真的记录. 为假的记录全部滤掉.
select 
    co.name country,
    ci.name capital,
    ci.district
from 
    country2 co 
join 
    city2 ci 
on 
    co.capital = ci.id;
    
    
-- 外联接可以保证某张表的数据完整, 即使联接条件为假, 也要保留数据
select 
    co.name country,
    ci.name capital,
    ci.district
from 
    country2 co 
left outer join 
    city2 ci 
on 
    co.capital = ci.id;
    
select 
    co.name country,
    ci.name capital,
    ci.district
from 
    country2 co 
right outer join 
    city2 ci 
on 
    co.capital = ci.id;
    
-- 查看所有国家名称和首都, 即使没有首都也要显示.
select 
    co.name country,
    co.continent,
    ci.name capital
from 
    country co 
left join 
    city ci 
on 
    co.capital = ci.id;

-- 查询哪些国家没有首都
select 
    co.name country,
    co.continent,
    ci.name capital
from 
    country co 
left join 
    city ci 
on 
    co.capital = ci.id
where 
    ci.name is null;
    
// 伪代码
// 内联接
Set leftTable;
Set rightTable;
Set resultSet = new Set();
for (int i = 0; i < leftTable.length; i++) {
    Row leftRow = leftTable[i];
    for (int j = 0; j < rightTable.length; j++) {
        Row rightRow = rightTable[j];
        if (联接条件(leftRow, rightRow)) {
            Row newRow = leftRow + rightRow;
            resultSet.add(newRow);
        }
    }
}

// 外联接 
Set leftTable;
Set rightTable;
Set resultSet = new Set();
for (int i = 0; i < leftTable.length; i++) {
    Row leftRow = leftTable[i];
    boolean flag = false;
    for (int j = 0; j < rightTable.length; j++) {
        Row rightRow = rightTable[j];
        if (联接条件(leftRow, rightRow)) {
            Row newRow = leftRow + rightRow;
            resultSet.add(newRow);
            flag = true; // 表明联接条件为真的记录保存起来
        }
    }
    
    if (!flag) {
        Row newRow = leftRow + 空行(右表);
        resultSet.add(newRow);
    }
}

-- 查询哪些国家没有官方语言
select 
    co.name country,
    cl.language,
    cl.isofficial
from 
    country co 
left join 
    countrylanguage cl 
on 
        co.code = cl.countrycode
    and 
        cl.isofficial = 'T'
where 
    cl.isofficial is null
    
-- 查询所有国家的首都和官方语言, 没有首都和官方语言的也要全部显示.

-- 单行函数, 查询结果中对每一行都单独执行的函数
select
    upper(name),
    lower(continent)
from 
    county;
    
select 
    concat(code, name),
    substr(name, 3, 2)
from 
    country;
    
-- 把国家的代码和名称及大洲用@符号连接. 如 : CHN@China@asia 
select
    concat(concat(concat(concat(code, '@'), name), '@'), continent)
from 
    country;
    
select
    concat(code, '@', name, '@', continent)
from 
    country;


-- 分组函数 : 作用于一组数据. 默认情况下虚表中的所有记录被当作一个组.
分组函数都是统计运算, 特点就是一组数据只能有一个结果. 结果也必须是所有数据处理完后才有的.

max() 最大值 
min() 最小值 
avg() 平均值 
sum() 求和
count() 计数

select 
    max(population),
    min(surfacearea)
from 
    country ;


select 
    -- name, 不可以再把普通的列放在这里
    max(name),
    max(population),
    min(surfacearea)
from 
    country ;

-- 查询亚洲国家的平均人口

-- 查询全球平均寿命最高和最低