【数据分析】【MySQL】快速入门+案例+代码+命令整理+GIF

2020-04-18 本文已影响0人卯月七

1.SQL的语句顺序（理论）

SELECT [ALL|DISTINCT] <目标表达式>....
FROM <表名或者视图名>..
[WHERE <条件表达式>]
[GROUP BY <列名 1> [HAVING] <条件表达式>]
[ORDER BY <列名 2> [ASC|DESC]]

2.SQL数据库的选择命令

2.1 数据库的选择（实操）

作用	命令
查看当前有哪些数据库	show databases;
选择数据库	use database_name;
查看当前使用的是哪个数据库	select database();
新建数据库	create database database_name;
删除数据库	drop database database_name;

数据库选择演示

2.2 数据库的表格选择命令（实操）

作用	命令
查看当前数据库有哪些表格	show tables;
在当前数据库新建表格	create table table_name(字段名1 字段类型, 字段名2 字段类型);
为当前数据库的表格插入数据	insert into 表格名 values ('字段名1的值',字段名2的值,'字段名3的值');
更新当前数据库某个表的数据	update 表格名 set 字段名1="值1" where 字段名1="值2";
删除当前数据库某个表的数据	delete from 表名 where name="值1";
删除当前数据库的某个表格	drop table table_name;

表格选择演示

3. 数据库建表约束条件（理论+说明）

作用	详细	说明	命令
主键约束	单一主键	给某个字段添加约束，使得该字段不重复且不为空	create table user( id int primary key, name varchar(4));
	联合主键	多个字段加起来唯一	create table user2( id int, name varchar(4), password varchar(20), primary key(id,name));
	自增约束	控制某个值自动增长	create table user3( id int primary key auto_increment, name varchar(4));
唯一约束	单一约束	约束字段的值不能重复	create table user4( id int, name varchar(4) unique);
	联合唯一约束	多个字段加起来的值唯一	create table user5( id int, name varchar(4), unique(id,name));
非空约束	非空约束	修饰字段的值不能为空	create table user6( id int, name varchar(4) not null);
默认约束	默认约束	当我们传入字段值的时候，如果没有传值，就会使用默认值	create table user7( id int, name varchar(4), age int default 23);
外键约束	外键约束	针对两张表：主表和附表，附表会参考主表的某个字段作为两张表的连接	create table coutries( id int primary key, name varchar(10)); create table provinces( id int primary key, name varchar(10), country_id int, foreign key(country_id) references country(id));
修改约束	添加约束	为字段加上新的约束	alter table user4 add primary key(id);
	修改约束	修改字段的约束（也可视作添加约束）	alter table user6 modify id int primary key auto_increment;
删除约束	删除约束	删除某个字段的约束	alter table user3 drop primary key;

4. 数据库的设计范式（理论+实例）

第一范式
数据表中的所有字段都是不可分割的原子值。字段值还可以继续拆分的，就不满足第一范式。

-- 不满足第一范式
create table student2(
      id int primary key,
      name varchar(20),
      address varchar(30));

  insert into student2 values(1,'卯月','中国四川省成都市武侯区武侯大道100号');
  insert into student2 values(2,'竹秋','中国四川省成都市武侯区武侯大道90号');
  insert into student2 values(3,'花朝','中国四川省成都市武侯区武侯大道80号');

-- 满足第一范式
create table student3(
      id int primary key auto_increment,
      name varchar(20),
      country varchar(20),
      province varchar(20),
      city varchar(20),
      street varchar(20),
      num varchar(10));
  insert into student3 values(1,'卯月','中国','四川省','成都市','武侯区','武侯大道100号');
  insert into student3 values(2,'竹秋','中国','四川省','成都市','武侯区','武侯大道90号');
  insert into student3 values(3,'花朝','中国','四川省','成都市','武侯区','武侯大道80号');

第二范式
必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键。如果要出现不完全依赖，只可能发生在联合主键的情况下。

-- 不满足第二范式
create table myorder(
      product_id int,
      customer_id int,
      product_name varchar(20),
      customer_name varchar(20),
      primary key(product_id,customer_id));

-- 满足第二范式
-- order id 和 product_id,customer_id 拆分开，可以保证下面两个表里面的name满足第二范式
create table myorder(
      order_id int primary key,
      product_id int,
      customer_id int
      );
  create table product(
      id int primary key,
      name varchar(20));
  create table customer(
      id int primary key,
      name varchar(20));

第三范式
必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

-- 不满足第三范式
create table myorder(
          order_id int primary key,
          product_id int,
          customer_id int,
          customer_phone varchar(11)
          ); # 错误，customer_phone可能还依赖于customer_id

-- 满足第三范式
-- customer_phone 只和 customer表里面的id有关
create table myorder(
          order_id int primary key,
          product_id int,
          customer_id int
          ); 

create table customer(
          id int primary key,
          name varchar(20),
          customer_phone varchar(11));

注意
三大范式并不是必须要满足，可以基于我们的业务需求进行调整。但是在设计mysql表的时候要尽量考虑到我们的三大范式规则。

5. 数据库查询（重点+实操）

5.1 创造数据（案例+演示）

5.1.1 说明

首先使用MySQL创建三个简单的表模拟一个跨国公司的人员薪资表（极简版本），会用到Python自动生成数据的一个自定义算法，因此需要确保自己安装有python，同时安装有pandas、sqlalchemy、pymysql、jupyter notebook三方包。（后续写数据分析的总结也会用到，因此建议直接安装一个Anaconda到电脑上，就可以一次性解决上述问题）

5.1.2 创建表

-- 国家表
create table countries(
  id int ,
  name varchar(10),
  primary key(id)
);

-- 部门表
create table departments(
  id int primary key,
  name varchar(10)
);

-- 人员薪资表
create table staff(
  id int primary key,
  name varchar(10),
  job varchar(10),
  age int(3),
  salary decimal,
  country_id int,
  department_id int,
  foreign key(country_id) references countries(id),
  foreign key(department_id) references departments(id)
);

5.1.3 填充数据

首先使用mysql填充国家表和部门表

-- 国家表
insert into countries values
  (1,'中国'),
  (2,'日本'),
  (3,'韩国'),
  (4,'柬埔寨'),
  (5,'越南'),
  (6,'泰国'),
  (7,'缅甸'),
  (8,'印度尼西亚'),
  (9,'马来西亚'),
  (10,'美国'),
  (11,'英国'),
  (12,'德国'),
  (13,'意大利'),
  (14,'俄罗斯'),
  (15,'斯洛文尼亚'),
  (16,'捷克'),
  (17,'波兰'),
  (18,'乌克兰'),
  (19,'加拿大'),
  (20,'印度'),
  (21,'巴基斯坦');

-- 部门表
insert into departments values
  (1,'董事会'),
  (2,'行政办公室'),
  (3,'人力资源部'),
  (4,'财务部'),
  (5,'生产技术部'),
  (6,'计划营销部'),
  (7,'安全监察部'),
  (8,'后勤部'),
  (9,'组织部'),
  (10,'保卫部');

使用jupyter notebook填充staff表

import random
import pandas as pd

last_names = ['卯月','竹秋','花朝','殷正','赵','钱','孙','李','周','吴','郑','王','毛','胡','董','杨','鲍','崔','袁']
first_names = ['七','初一','十五','德','智','体','美','全','面','发','展']
ages = [i for i in range(18,40)]
careers = ['实习生','UI','前端','后端','开发','测试','算法','数据分析','老板','主管']
salarys = random.sample(range(10000,100000),100)

def generateRow(num_id,last_names,first_names,ages,careers,salarys):
    last_name = random.choice(last_names)
    first_name = random.choice(first_names)
    name = last_name + first_name
    age = str(random.choice(ages))
    career = random.choice(careers)
    salary = str(random.choice(salarys))
    country_id = random.choice(range(1,22))
    department_id = random.choice(range(1,11))
    return [num_id,name,career,age,salary,country_id,department_id]

datas = []
for i in range(10000):
    row = generateRow(i+1,last_names,first_names,ages,careers,salarys)
    datas.append(row)
  
infos = pd.DataFrame(datas,columns=['id','name','job','age','salary','country_id','department_id'])
infos.to_sql(name='staff',con='mysql+pymysql://root:123456@localhost:3306/summary',
            if_exists='append',index=False)

5.1.4 生成数据

复制上方代码到对应的mysql命令行，jupyter notebook中，可以自行生成。

生成数据演示

5.2 初级查询（代码+不演示）

查询顺序

SELECT [ALL|DISTINCT] <目标表达式>....  -- 查询
FROM <表名或者视图名>..  -- 数据从哪来
[WHERE <条件表达式>]  -- 有什么筛选条件
[GROUP BY <列名 1> [HAVING] <条件表达式>]  -- group by通过什么分组，having 筛选条件
[ORDER BY <列名 2> [ASC|DESC]]  -- 通过什么进行怎样的排序

查询country表中的所有信息
```
select * from countries;
```
查询contry表里有哪些国家
```
select name from countries;
```
查询国家中不重复的name
```
select distinct name from countries;
```

查询编号在1~10的国家

select * from countries where id between 0 and 11;

select * from countries where id < 11;

查询countries表中中国，日本、韩国的所有信息

select * from countries where name in ('中国','日本','韩国');

查询staff表中，做算法工作或者年龄在23岁的人的所有信息
```
select * from staff where job='算法' or age=23;
```
查询staff表中，做算法工作并且年龄为23岁的人的所有信息
```
select * from staff where job='算法' and age=23;
```
id降序排列countries表中的所有信息
```
select * from countries order by id desc;
```
以id升序，name降序排列countries表中的所有信息
```
select * from countries order by id asc,name desc;
```

查询staff表中算法工作的人数

select count(*) from staff where job='算法';

5.3 子查询（实例+代码+演示）

5.3.1 说明

子查询可以理解为多个查询的堆叠，将上一个查询的结果作为下一个查询的筛选条件或者作为下一个查询的数据来源。

5.3.2 实例和演示

查询staff表中年龄最大的人们的姓名和薪资

-- step 1，首先找到最大年龄
select max(age) from staff;
-- step 2，根据找到的最大年龄，让它作为下一个查询的条件
select name,salary from staff where age = (select max(age) from staff);

 -- 完整版
select name,salary from staff where age = (select max(age) from staff);

查询staff表中年龄最大的人们的姓名和薪资

查询每个部门的平均薪资

-- step 1 按照部门分组
select * from staff group by department_id;
-- step 2 求平均薪资
select avg(salary) from staff group by department_id;

-- 完整版
select avg(salary) from staff group by department_id;

求每个部门的平均薪资

查询以卯月为姓的各个工作人员的部门平均薪资、部门id

select department_id,avg(salary) from staff where name like '卯月%' group by department_id;

各部门以卯月为姓的平均薪资和id

5.4 多表链接查询

查询各部门的平均薪资及部门名称

-- step1 部门名称在departments表里面，薪资在staff表里面。可以先查看各自的内容
select name from departments;
select salary from staff;

-- step2 在我们建表的时候，使用了department_id作为外键连接了departments表。因此可以使用为连接
select departments.name,avg(salary)
from departments,staff
where departments.id = staff.department_id
group by staff.department_id;

-- 注意，一定不能弄混淆各个表的列名，可以使用表名.列名的方式进行指定，防止混淆

查询各部门的平均薪资及部门名称

查询所有以卯月为姓的人所在的国家名称、部门名称、姓名、年龄、工作和薪资

select countries.name,departments.name,staff.name,age,job,salary
from countries,departments,staff
where countries.id = staff.country_id and departments.id = staff.department_id
having staff.name like '卯月%';

-- 详解
首先，我们要获得国家名称和部门名称，就必须要从countries和departments表中获得名字。
因此是一个三表链接的查询。之后，我们根据三表间的外键进行连接。
使用where语句连接了三表，having语句进行以卯月为姓的筛选，最终得到结果。

查询以卯月为姓的人所在的国家、部门、姓名、年龄、工作和薪资

查询在中国工作的，以卯月为姓的，在组织部发展的员工的姓名、年龄、工作和薪资

select * 
from (select countries.name country_name,departments.name department_name, staff.name staff_name,age,job,salary
from countries,departments,staff
where countries.id = staff.country_id and departments.id = staff.department_id
having staff.name like '卯月%') a
where a.country_name = '中国' and department_name = '组织部';

-- 详解
首先借助上一次查询我们获得的信息，作为一个数据来源，取个别名叫做a。
同时，为了防止name之间的混淆，我们将country_name,department_name,staff_name分别作为三个别名添加上
最后我们再利用前面命令好的别名直接进行指代数据库查找中国，组织部的姓卯月的人的信息

查找在中国工作的，组织部的姓卯月的员工的信息

5.5 综合查询

查询薪资在5万以上，7万以下员工中以卯月为姓的员工信息

select *
from (select departments.name department_name,staff.name staff_name,age,job,salary
from departments,staff
where departments.id = staff.department_id) a
where a.staff_name like '卯月%' and a.salary between 50000 and 70000;

-- 详解
首先拿到员工算法部门和员工信息的合表，作为数据来源，之后再根据条件查找薪资和姓氏。

-- 注意
查询方法不唯一，可以有更多方式，只要符合内在语法逻辑即可。因此，mysql调优也很好玩，不展开。

查询薪资在5万及7万间的员工信息

查询薪资在5万以上，7万以下员工中以卯月为姓的员工各部门平均薪资

select department_name,avg(salary)
from (select departments.name department_name,staff.name,salary
from departments,staff
where departments.id = staff.department_id and salary between 50000 and 70000
having staff.name like '卯月%') a
group by a.department_name;


-- 详解
首先我们可以先取出部门名称，以卯月为姓，薪资在50000到70000之间的员工信息作为下一次查询的数据来源；
之后我们再对数据按照部门名称进行排序。注意，因为staff.name行数明显不重复数目大于department_name 行数，所以无法显示staff.name。

薪资在5万以上，7万以下员工中以卯月为姓的员工各部门平均薪资

-- 查询高于财务部以卯月为姓的员工的平均薪资的员工信息

-- 首先，我们可以找到财务部以卯月为姓的员工的平均薪资
select avg(salary)
from staff,departments 
where departments.id = staff.department_id and departments.name = '财务部' and staff.name like '卯月%';


-- 之后，将平均薪资作为查询条件，找薪资高于平均薪资的员工
select * 
from staff
where salary > (
  select avg(salary)
  from staff,departments 
  where departments.id = staff.department_id and departments.name = '财务部' and staff.name like '卯月%');

查询高于财务部以卯月为姓的员工的平均薪资的员工信息

显示国家和部门的联合信息

select name from countries
union
select name from departments;

国家和部门的联合信息

5.6 连接查询

作用	详细	说明	命令
内连接	内连接	查找两张表都有的内容	inner join join
外连接	左连接	只显示左边表有的相关数据	left join left outer join
	右连接	只显示右边表有的相关数据	right join right outer join
	完全外连接	两边表都显示	full join full outer join

5.6.1 创建数据

创建两张表（极简版本)：
一张表是课程表，包含课程编号，课程名称；
一张表是教师表，包含教师编号、教师姓名，课程编号；

create table courses(
  id int,
  c_name  varchar(10)
);

insert into courses values
  (1,'语文'),
  (2,'数学'),
  (3,'英语'),
  (4,'政治'),
  (5,'历史'),
  (6,'地理'),
  (7,'物理'),
  (8,'生物'),
  (9,'化学');

create table teachers(
  id int,
  c_id int,
  t_name varchar(10)
);

insert into teachers values
  (1,3,'卯月七'),
  (2,5,'卯月初一'),
  (3,6,'卯月初二'),
  (4,1,'卯月初三'),
  (5,2,'卯月初四'),
  (6,5,'卯月初五'),
  (7,1,'卯月初六'),
  (8,2,'卯月初七'),
  (9,3,'卯月初八'),
  (10,4,'卯月初九'),
  (11,3,'卯月十五');

5.6.2 内连接

查询两张表的交叉数据

-- 对于courses表和teachers表来说，在teachers表中没有7、8、9的课程编号，因此不会出现物理、生物、化学三科。
select * from teachers inner join courses on teachers.c_id = courses.id;

内连接

5.6.3 外连接

左连接
以左边为基准显示相关数据

-- teachers表左连接courses表
-- 以teachers表为基准显示
select * from teachers left join courses on teachers.c_id = courses.id;

-- courses表左连接teachers表
-- 以courses表为基准
select * from courses left join teachers on teachers.c_id = courses.id;

左连接

右连接
以右边为基准显示相关数据

select * from teachers right join courses on teachers.c_id = courses.id;

select * from courses right join teachers on teachers.c_id = courses.id;

右连接

全连接
mysql 数据库不支持全连接，可以使用union达到要求
```
select id,c_name from courses
union
select id,t_name from teachers; 
```

mysql全连接的实现

说明

本文由我本人原创，发布于简书卯月七账号、知乎卯月七账号、CSDN卯月七账号。
本文允许转载、学习，转载请注明出处，谢谢。
作者邮箱mcj6989@163.com,有问题可以联系。
本文为我整理的MySQL的入门文章，更多知识可以购买专业书籍学习。
创作不易，如果对你有帮助，希望能给我一些反馈，包括不限于点赞，评论，转发，非常感谢！！