数据库

一、数据库介绍

1. 在python基础班，想要把数据做到永久储存，使用的是文件；数据库也是做永久存储的。
2. 数据库分类：关系型数据库（以二维表格形式存储数据）；非关系行数据库即nosql（以key-value的方式存储）
   数据库是用来管理和存储数据的。读写速度比文件快。
3. 如果想使用数据库，就需要安装数据库管理系统（就是一个应用软件）。例如想要使用mysql数据库，就要安装mysql数据库管理系统
   关系型数据库管理系统分为：
   关系型数据库服务端软件：用来管理多个数据库，各个数据库都有多个数据文件…
   关系型数据库客户端软件：用来跟服务端软件进行通信，获取服务端的数据
   
   说明：关系型数据库分为服务端和客户端，那么底层通信使用的是TCP协议

二、关系型数据库的语言：SQL

SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS（即关系型数据库）的数据库的语言。用来实现数据库客户端和服务端的通信。
SQL语言主要分为：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback-----（事务可以理解为一个团体，这个团体是要么都执行，要么都不执行）
DCL：数据控制语言，进行授权与权限回收，如grant、revoke-----（是对用户权限进行设置，例如该用户对这张表只能查询，对另一张表可以进行增删改等。）
DDL：数据定义语言，进行数据库、表的管理等，如create、drop

三、mysql数据库：

多表关联：多张表通过相同的字段关联
多表关联一定是左表的每条数据都和右表的每条数据进行对比，满足条件的按照要求保留（如inner join 则是两边表都有该字段的才保留，left join则是左边的表内容都表里，右边的表包含坐标中关系字段的才保留）

需求：学生在生日当天放假，查出考试安排冲突的情况
方法一：
select a.user_id,a.name,b.course,b.test from user a inner join test b on a.user_id=b.user_id where a.birth=b.test_date;
方法二：可以将条件一起写在on条件里
select a.user_id,a.name,b.course,b.test from user a inner join test b on a.user_id=b.user_id and a.birth=b.test_date;
注意：
5. on条件一定只能是等于=，如果条件是不等于、大于之类的都不能放在on条件里
6. join … on … 等价于 inner join … on …

MySQL

MySQL是一个关系型数据库管理系统
使用MySQL系统，需要安装服务端和客户端

sudo apt-get install mysql-server     #服务端安装命令（ubantu）
sudo apt-get install mysql-client    #命令行客户端mysql的安装

apt-cache show mysql-server  #显示MySQL服务端安装包信息效果图

查看MySQL服务效果图:

ps -aux | grep myslq 
	ps 查看当前系统中的进程
	-a 表示所有用户
	-u 表示显示用户名
	-x 表示显示所有的执行程序

mysql终端命令

sudo service mysql status   #查看MySQL服务状态
sudo service mysql stop     #停止MySQL服务
sudo service mysql start   #启动MySQL服务
sudo service mysql restart   #重启MySQL服务

MySQL配置文件的介绍:
配置文件路径为: /etc/mysql/mysql.conf.d/mysqld.cnf
主要配置信息说明:

port表示端口号，默认为3306
bind-address表示服务器绑定的ip，默认为127.0.0.1
datadir表示数据库保存路径，默认为/var/lib/mysql
log_error表示错误日志，默认为/var/log/mysql/error.log

数据类型和约束

常用数据类型如下:

整数：int，bit
小数：
	decimal-----浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
字符串：
	varchar：小型数据，表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数；使用场景：姓名、地址、品牌、型号
	char：小型数据，表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数；	使用场景：身份证号、手机号、电话、密码
	text：可变长度，表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.
日期、时间:
	date：	'2020-01-01'
	time： 	'12:29:59'
	datetime：	'2020-01-01 12:29:59'
	YEAR：	'2017'
	TIMESTAMP：'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC	
枚举类型(enum)

**对于图片、音频、视频等文件**不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.

常见的约束

主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
非空 not null: 此字段不允许填写空值.
惟一 unique: 此字段的值不允许重复.
默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

mysql客户端的使用

1，客户端需要先连接服务端：登录数据库

mysql -uroot -p        #连接服务端，登录数据库
	-u: 表示MySQL服务端的用户名
	-p: 表示MySQL服务端的密码
quit 或者 exit 或者 ctr + d        #表示登出(退出)数据库

**数据库操作的SQL语句**
select now();       #显示当前时间
show databases;      #查看所有数据库
create database 数据库名 charset=utf8;   #创建数据库
use 数据库名;      #使用数据库
select database();     #查看当前使用的数据库
drop database 数据库名;   #删除数据库-慎重
**表结构操作的SQL语句**
show tables;    #查看当前数据库中所有表

**创建表**
create table 表名(
字段名称 数据类型  可选的约束条件,
column1 datatype contrai,
...
);
	例如：
	create table students(
	 id int unsigned primary key auto_increment not null,
	 name varchar(20) not null,
	 age tinyint unsigned default 0,
	 height decimal(5,2),
	 gender enum('男','女','人妖','保密')
	);

二. 数据分析中的mysql使用

1.显示行号

本质是：为当前表格增加一列（该列显示行号），增加的一列需要使用会话变量进行命名

会话变量定义和赋值：
select @名字:=值（或者表达式）
set @名字:=值（或者表达式）
存在的问题：会话变量初始值没有定义，则默认是null，遍历后结果均为null---->
解决：定义初始值：
方法一：先增加新列（定义初始值列），以增加新列后的表作为查询表进行操作

select *,@rownum:=@rownum+1 from products a,(select @rownum:=1) b order by p_view 

方法二：提前定义好会话变量的初始值

set @rownum=1;
select *,@rownum:=@rownum+1 from products order by p_view 

方法三：使用IFNULL(expr1,expr2)函数，表示如果返回值是null则返回@rownum:=1 ，如果不是则返回@rownum:=@rownum+1 （存在问题，运行一次rownum就会更改，只有第一次的初始值为1）

select *,ifnull(@rownum:=@rownum+1,@rownum:=1) from products order by p_view

2. 分组后在分组内排序（加行号）

本质：先进行排序，先按照类别排序（将同类放一起；再按点击数排序），然后对排序后的表的每组添加行号
难点：对每组分别写行号，需要判断当前的行跟上一行是不是同类，同类行号+1；不同类行号从1重新开始，
解决：定义新的会话变量，初始值为null，进行if判断，之后定义会话变量=当前行的类别值，然后进行遍历查询

SELECT *,if(@pt=p_type,@rownum:=@rownum+1,@rownum:=1),@pt:=p_type 
FROM 
(select * from products a order by a.p_type desc, a.p_view desc) a,(select @rownum:=0,@pt:=null) b 
# 注意：不是对同类分组，因为分组返回的结果同一组只会显示一个，不会全部显示信息

3. 纯SQL也能实现小算法（辅助决策）计算商品评分、及时补货

4. mysql中自连接的使用

5. 不等连接

6. 删除重复数据

7. 有重复数据不插入或更新的处理方法

8. update表子查询、多条件判断

9. 利用order by 实现"排名作弊"

10. 获取连续签到X天用户列表— 百度面试题

11. 子查询去重、获取商品分类最新销售情况

12. 多表关联update（用户积分奖励)