5.数据库基础
数据库简述
数据库就是各种数据的集合，一个项目可能有一个或者多个数据库。
一个数据库下面会有很多张表，表就是同一类相同结构数据的集合。
这个固定的结构，就是一个个的字段
表之间有联系，存在关系
数据库类型
1.关系型数据库
表和表之间存在关系，通过sql语言操作方便，表结构固定
Mysql
最常用的数据库，目前多数项目使用，必须掌握基本设计，操作
2.非关系型数据库
数据以键值对的形式存储，性能较高，容易扩展
Redis
数据存在内存中，速度快，常用的高性能数据库，经常用作缓存，保存关键数据
Memchache
数据库设计
几个重要原则，设计时注意使用
引擎选择
Mysql数据库分为两种引擎
一般情况可以选择MyISAM存储引擎，通常查询性能相比较好
如果需要事务支持必须使用InnoDB存储引擎
事务就是多个语句同时执行，作为一个整体操作，保证多条语句要么都成功，要么任何一个失败都撤销不执行。
举例：银行转账，你给你朋友B转账，转了100块钱，第一步扣你余额100，第二步是不是给B的余额加100。一共两条语句，没有事务的情况下，如果第一条扣你100执行成功了，第二条给B加100执行失败了，那你这个钱不就是白扣了
所以事务可以保证该范围内的操作要么都成功，要么都失败撤销什么也不做，不会有任何改动，常用于订单或者支付，涉及到钱的操作
命名规则
数据库名，表名，字段名
请使用英文，不要出现拼音，尽量使用和业务相对应的简单易懂的单词
存在关系的表，字段，使用部分单词作为前缀，如用户表user，和用户地址表，一个用户是不是多个收货地址，用户地址表可以命名为user_address。如字段，用户地址表user_address，怎么知道这个地址是哪个用户的，得有个用户id吧，可以命名user_id
字段设计
字段包括，字段名，字段类型，字段长度
就是这字段是存的什么数据，叫什么，是数字，小数还是字符串，多长，存5位数，还是10位数
常用字段类型
数字类
整数类
1.INT，整数型，保存123456这种数
实用例子：
保存id，长度建议11，int 11，每个表的主键id，主键是啥，你这一个表存了很多数据吧，每一个数据都是一行，怎么知道这是哪一条，得有个标志，像你的身份证号码id。又比如excel表，每一行数据开头是不是经常有个标号，123456这种。注意一张表主键只能有一个，因为是唯一的。如用户表id标志这是哪个用户，商品表id这是哪个商品
保存各种整数数字，如商品销量，
保存时间戳，int 长度11，时间戳是什么，就是时间平常 年Y 月m 日d 小时h 分钟m 秒s
2019.10.16。 时间戳也是标记时间的，这个是怎么算的， 定义为从格林威治时间1970年01月01日00时00分00秒起，到现在这个时间一共走了多少秒
2019-10-16 13:42:28，对应时间戳1571229748，也就是2019年这个时间比1970那个时间，一共走了1571229748秒
时间戳现在10位，建议11，好像到2048年左右这个秒数就到11位了，当然估计那时候项目早没了
2.TINYINT，保存很小的整数，最大范围0-255或者-128到127。通常设计保存状态status之类的变化范围很小的整数。可以节省数据库存储空间
tinyint 长度1
保存用户状态，你想能有几个状态，等于1正常，等于0不正常没有验证邮箱电话，再或者等于2可能被封号了
以及各种状态，如商品状态，0上架，1下架。或者订单状态，就是这些变化范围很小，就几个固定值的
保存is_deleted，是不是被删除，一共0 1两种选择，1是被删了，0没被删。
以及保存其他逻辑类字段is_xxxx，是不是被xxx了
为什么存在is_deleted这种字段，这叫逻辑删除，比如这个商品没有被真的删除，还存在数据库里面，查询的时候加上条件，可以筛选掉。因为如果执行sql语句真的删除了，数据库就没有这个数据了，不能恢复了。所以这种假装删除了
3.SMALLINT 保存大一点的整数
小数类
4.DECIMAL以及FLOAT单精度，DOUBLE双精度，这几个都是保存小数，精度就是小数的位数
常用来保存金额
decimal 12,2 小数，长度12，小数位2（小数点后面2位）
保存金额amount，价格price等小数，付钱是不是有几分几毛这样，所以是个小数吧，如商品价格20.99
字符串类
1.VARCHAR
保存大多数字符串的情况
用户名username varchar 长度30
密码password varchar 30
手机号，不要用int，看着都是数字是吧，一般建议用varchar当作一共字符串。int之类的数字类通常进行计算的，比如这个商品价格price，你买了好几个是不是加起来算算一共多少钱。手机号这种虽然是数字但不需要计算，建议选择varchar
以及各种
商品名称goods_name
商品描述description varchar 255
商品图片路径pic_url varchar 255
内容长度长一点的，就用varchar 200 255之类长一些
2.CHAR
char长度是固定的，占固定空间。varchar是可变的长度，通常可以使用varchar
3.TEXT
保存长文本，很长的数据
一段文字之类
4.LONGTEXT
特别特别长，比如一大段文字，文章的内容
要是更长的数据，很大的，别用数据库存了，考虑考虑别的存储方式，文件存储
日期时间类
大多数情况可以使用int 11时间戳保存的，int类型计算起来比较快，只是展示的时候需要转换成正常形式 年月日，总不能给用户看时间戳多少秒
1.DATETIME
YYYY-MM-DD HH:MM:SS
2.TIMESTAMP
YYYYMMDD HHMMSS
索引
索引就是某一列或者多列的值，相当于书的目录，用索引可以加快查询速度
就好比你买了本书，是不是有个目录，你想看那一章比着目录找是不是比你一页页翻快多了
通常经常查询的字段需要加索引index，加快查询速度。
比如，商品表，category_id，商品分类id
作为用户，你买东西是不是经常会选择一个分类，你想买电脑，选中电脑分类。
又或者根据订单状态，status。看淘宝里面你那些订单，未付款的，已付款等发货的，待收货的，数据库查询是不是得根据这个status查
商城数据库设计例子
举例子：
商城项目基本功能为例，采用Mysql数据库
1.用户登录注册
2.商品购买下单
先分析这个功能
大概涉及到哪些信息，分成几个表
这一个数据是什么结构，每一个表哪些字段
分析：
1.用户登录注册
用户表user
要保存用户信息吧，这个得有
保存的什么信息，用户是不是得有用户名密码，可能还有头像，个性签名。
想不全没事，后面可以再修改表的结构，加字段。尽量想丰富
主键id，标识这个身份的int 11，代表用户id
用户名username varchar 30
密码password varchar 50， 通常密码经过md5加密
头像avatar varchar 255，保存头像图片的url地址
个性签名description varchar 50
状态status tinyint 1， 俩情况0被禁用 1正常能登录
创建时间create_time int 11 什么时候创建的
更新时间update_time int 11，什么时候改过，比如哪天改了密码了
建议每张表必备字段
id主键 int 11,自增 id代表当前表的身份，用户id，商品id等，自增代表id自动增加，插入一条数据id加一
status状态 tinyint 1
is_deleted tinyint 1 是否逻辑删除
create_time int 11 创建时间
update_time int 11 更新时间
2.分析商品购买下单
比较复杂，买东西的过程，一点点分析
商品表goods
先知道是个什么商品，这个商品有什么商品
首先必备字段，这里id代表商品id，
商品名称name varchar 50
商品分类category_id int 11， 对应商品分类表
商品简介description varchar 200
价格decimal 12,2 金额用小数
销量volume int 11 用于计算的整数
等等等
订单表order
你买的啥该付多少钱，该给你发到什么地方去，发给谁，是不是得有个
必备字段，id标识订单id
用户身份user_id int 11， 标识用户，是哪个用户下的单
订单状态status tinyint 1， 各种状态，0未付款，1付款了，2发货了，3确认收货了等
收货地址相关
联系人username varchar 30
联系电话telephone varchar 20
地址address varchar 100， 地址通常会拆分，省province市city县county+具体街道门牌号address，其中省市县会用code代码标识，123这种，一个标识代表一个地名
等等比如用户备注，快递信息，快递名，单号
订单商品关联表order_goods
你这个一个订单，可能同时买了好几个商品，一个订单包含多个商品
这是不是要保存多行，一行是一个订单对应的商品
必备字段，id标识id
订单order_id int 11， 标识订单，买的这个商品是哪个订单里面的
用户身份user_id int 11， 标识用户，是哪个用户下的单
商品goods_id int 11， 标识商品，这一个商品是个具体什么商品
通过这么个关联关系，是不是把订单表order和商品goods连接到了一起。
关联和外键
涉及到关联和外键的知识
关联
关联就是存在一定关系
主要是三种关系，一对一，一对多，多对多
1.一对一是什么，就是唯一一个与之对应。好比一个学生student只能是一个班级class的，这个班级是唯一的。举例子，用户user和简介资料Profile，一个用户只能有一个简介，一个简介也只能对应一个用户。商品goods和供应商supplier，一个商品只有一个供应商，商品对供应商，一对一关系。商品分类，一个商品goods属于一个分类category。
2.一对多，就是一个对应多个，好比一个班级class有很多学生student，班级和学生，一对多。举例一个供应商supplier能供应很多商品goods，供应商-商品也是一对多关系。
3.多对多，学生student和课程course，一个学生能上好几节课，一节课也有很多学生上，通常采用一个中间表，student_course表保存学生和课程的对应关系
外键
关联关系之中，一个表里面存的，代表另一个表信息id的叫做外键。是一个约束条件。比如订单表order，里面是不是有个user_id，这个user_id就是外键，因为这个user_id代表的是另一个user表的信息。简单说就表和表之间通过这个字段关联。
付款表payment
你的付款记录是不是保存一下，以后方便对账算钱。
必备字段，id标识支付id
用户身份user_id int 11， 标识用户，是哪个用户付的钱
付款方式type tinyint 1，是余额付款，还是微信，还是支付宝
金额amount decimal 12,2 付了多少钱
以上完成了一个基本的数据库设计，从功能分析，到设计
想功能有哪些信息，这个信息结构是什么。当然一个完整的商城还包含很多功能，很多其他表。比如用户地址表user_address，用户收藏表user_collect等等
基本使用
掌握基本的sql语句，sql语句是什么，就是和数据库打交道的语言，一种指令，告诉数据库做什么。
虽然有各种图形化工具Navicat，并且框架都封装了增删改查，但是基本知识还是要掌握
数据库操作
创建数据库
create database mydb;
删除数据库
drop database mydb;
使用数据库
use mydb;
注意
sql语句以分号; 结尾
表操作
创建表
CREATE TABLE user (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
level int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户等级',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意
AUTO_INCREMENT自增，COMMENT是注释
ENGINE 引擎
DEFAULT 默认值
utf8是字符编码格式，常用utf8 general ci
删除表
drop table user;
修改表结构alter
增加一个username字段
alter table user add username varchar(20);
修改username字段，使其长度为30。
alter table user modify username varchar(30);
删除username字段
alter table user drop username;
增删改查
增加
insert into user values(1,'test');
insert into user(id,username) values(1,'test');
删除
删除username为test的数据
delete from user where username='test';
清空表数据
truncate table user;
修改
update user set password=123456 where username='test';
查询
查询表中所有user的信息
select * from user;
查询表中所有用户的姓名和密码
select username,password from user;
查询余额大于10000的所有用户
select * from user where balance>200;
聚合查询
统计个数
select count(* ) from user where balance>100;
统计总数
select sum(balance) from user;
此外
统计某字段平均值avg
统计某字段最大值max
统计某字段最小值min
查询条件
where是表示查询的条件，数据库 存的数据很多，你要筛选，加条件选择你想要的。就像你买东西，你筛选价格低于200的，where price < 200。除了 >大于 <小于 =等于
between两者之间，常用统计日期时间范围内的数据
WHERE create_time BETWEEN xxxxx AND xxxxxx;
like，常用于 名称搜索， 搜索商品名称关键词，返回包含关键词的商品 %like%,百分号%字符来匹配
WHERE name LIKE '%xxx%';
in,查询在某个范围内也常用
WHERE id IN (1,2,3); 查询id是1，2，3里的
与或非
and 条件都满足
or 条件满足一个
not 条件取反
其他常用
as临时更改替代名称，某字段 a as b，本来字段叫做a，输出结果名称改成b
group by分组查询，返回的结果按照某特定字段列出，GROUP BY
limit限制查询数目，LIMIT 2;查两条数据。常用来分页
order by按照某顺序排序，ORDER BY price DESC按照商品价格desc降序，asc升序
数据库优化
查询缓存
对于经常查询的数据，可以增加查询缓存，先存到缓存里面，用到直接取。比起你从数据库再查一遍是不是快多了。
一般后端如php，各种框架都有查询缓存的设计，检测到使用相同sql语句自然是相同的查询条件，自动从缓存中取出数据并返回，一定时间段缓存时间如5分钟之内，返回缓存中数据，超出时间后，重新查询并且缓存该查询结果，周而复始。
善用索引
查询频繁的字段，一个或多个，使用索引index
Not Null
数据库尽量不要保存Null空数据，就算这个值没有，可以设置一个初始值default值
避免Select *
建议指定字段，查询只返回指定的字段
1减少性能浪费
2数据安全，只返回相关字段
拆分
分库分表
水平拆分
纵向拆分
小项目不用考虑，几万数据，对于Mysql性能影响不大
十几万，几十万数据，通过索引之类的基本优化操作也能优化
