数据库-Mysql

1 数据库简介

数据库就是存储数据，管理数据的仓库。
常见的数据库分为：

关系型数据库：Oracle、MySQL、SQLServer、Access
非关系型数据库：MongoDB、Redis、ElasticSearch、Hive、Solr、HBase

1.1 Mysql组成模块

视图：
视图本质就是一个查询，和我们自己查询的区别是，它执行完会有缓存，下次查询就直接使用。

视图只需创建一次，后面就可以类似表来使用，只是用来查询不能更新和删除

1.2 表设计

PowerDesinger设计工具。

1.3 表结构

1.3.1 字段类型

字符串类型：
(1)char(n)在保存数据时，如果存入的字符串长度小于指定的长度n，后面会用空
格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。
因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比
后两者还快！
(2)varchar(n)保存数据时，按数据的真实长度存储，剩余的空间可以留给别的数
据用，因此varchar不会浪费空间。
因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

数字

tinyint：占用1个字节，相对于java中的byte 
smallint：占用2个字节，相对于java中的short 
int：占用4个字节，相对于java中的int 
bigint：占用8个字节，相对于java中的long
其次是浮点类型即：float和double类型
float：4字节单精度浮点类型，相对于java中的float 
double：8字节双精度浮点类型，相对于java中的doubl

日期

date 包含年月日
datetime包含年月日和时分秒
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计，但目前主流都不会直接存储这样的数据，而只存储其访问路径，文件放在磁盘上

1.3.2 约束

主键约束
如果为一个列添加了主键约束，那么这个列就是主键，主键的特点
是唯一且不能为空。

create table stu( id int primary key, ... );

唯一约束
如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的
（即不能重复），但可以为空。

create table user( username varchar(50) unique not null, ... );

非空约束
如果为一个列添加了非空约束，那么这个列的值就不能为空，但可
以重复。

create table user( password varchar(50) not null, ... );

默认约束
外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。

2 数据库简单命令

2.1 CRUD

增加：

自增
insert into tb_stu(id,name,sex) values(1,'tony',1)

修改：

修改，修改字段，必须设置where条件
update tb_stu set name='jack',sex=1
where id =1

删除：

删除一条记录，必须有where条件
delete from student where id=3;
删除所有数据
delete from student;

查询:

#查询所有数据
SELECT * FROM student;
#id为1的记录
SELECT * FROM student WHERE id=1;

2.2 简单函数

UPPER()变大写 LOWER()变小写

SELECT ename,UPPER(ename),LOWER(ename) FROM emp

LENGTH()查长度

SELECT ename,LENGTH(ename)FROM emp

SUBSTR 子串，截串第一个元素，要截的原串，从第几个开始截，截多少

SELECT ename,SUBSTR(ename,1,2)FROM emp

CONCAT 拼串从哪里开始拼，拼什么

SELECT CONCAT(dname,'(',loc,')')FROM dept

replace 替换

SELECT loc,REPLACE(loc,'区','区域') FROM dept;

round 四舍五入/ceil 向上进位/floor 向下去掉

SELECT ename,
comm,ROUND(comm,1),ROUND(comm,2),
CEIL(comm),
FLOOR(comm) FROM emp;

uuid 生成一个唯一36位字符串

2.3 日期函数

SELECT NOW()

2020-02-10 12:20:00

SELECT CURRENT_DATE()

2020-02-10

SELECT CURRENT_TIME()

12:20:00

LAST_DAY(hiredate) 每月最大日期

SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp

YEAR MONTH DAY

SELECT hiredate,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp

date_format 日期转字符串，注意格式的大小写

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

str_to_date()字符串转日期

SELECT STR_TO_DATE('2020-05-08','%Y-%m-%d')

2.4 条件查询

distinct 去重

SELECT DISTINCT * FROM emp;

where 过滤数据

SELECT * FROM emp WHERE empno=100;

like 通配符%代表0到n个字符，通配符下划线_代表1个字符

SELECT * FROM emp WHERE ename LIKE 't%';        #t字母开头，效率高
SELECT * FROM emp WHERE ename LIKE '%n%';        #中间含有n，惯用
SELECT * FROM emp WHERE ename LIKE 't___';        #3个下划线
SELECT * FROM emp WHERE ename LIKE '__n%';        #2个下划线

and&or 并且，或者

SELECT * FROM dept WHERE dname='accounting' AND loc='一区';
SELECT * FROM dept WHERE dname='accounting' OR loc='二区';

null 空

SELECT * FROM emp WHERE mgr IS NULL;
SELECT * FROM emp WHERE mgr IS NOT NULL;

nvl 如果为空进行替换，mysql不支持，可替换ifnull使用

SELECT ename,sal*13+nvl(comm,0)*13 FROM emp
SELECT mgr,nvl(mgr,'空')FROM emp

between-and 查询范围上面的包括自身，下面更灵活

SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000
SELECT * FROM emp WHERE sal>=5000 AND sal<=10000

union 把多个结果集合并，前提条件，两个结果集列对应，类型可以不一致，个数需要一致

SELECT empno AS NO,job AS DNAME FROM emp
UNION
SELECT deptno AS NO,loc AS DNAME FROM dept

limit 列表分页，不是返回所有数据，当前页数据

SELECT * FROM emp LIMIT 1    返回一条记录
SELECT * FROM emp LIMIT 0,2  第一页数据
SELECT * FROM emp LIMIT 2,2  第二页数据
SELECT * FROM emp LIMIT 4,2  第三页数据

2.4 子查询

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。
单行子查询 = 后面查询结果只为一个值

SELECT * FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE     ename = 'tony')

多行子查询 in

SELECT * FROM emp WHERE job IN('副总','总监','经理')

3 数据库高级命令

3.1 聚合

count 记录总数，习惯使用*的方式，推荐使用后两种方式

select * from emp;
select count(*) from emp;
select count(*) from emp where ename like 't%';
select count(1) from emp;
select count(empno) from emp;

max 最大值；min 最小值；avg 平均值；sum 总和

SELECT MAX(sal) FROM emp;
SELECT min(sal) FROM emp;
SELECT avg(sal) FROM emp;
SELECT sum(sal) FROM emp;

3.2 分组和排序

group by 用于对查询的结果进行分组统计

每个部门，每个岗位的最高工资和平均工资，并排序
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job
ORDER BY MAX(sal)

统计时非统计字段必须分组

排序order by

order by ename  ASC默认升序/DESC降序，
字符串按ascii顺序，数字按大小，日期字符串

3.3 集合

union 并集去重：

select * from dept where loc='一区'
union
select * from dept where loc='二区'

union all 并集：

会有重复数据，不进行合并
select * from dept
union all
select * from dept

intersect 交集：

select * from dept where loc is not null
intersect
select * from dept

3.4 多表联查

INNER JOIN 内链接

SELECT dept.dname,emp.empno,emp.ename FROM emp INNER JOIN dept ON emp.deptno=dept.deptno

LEFT JOIN 左链接，左边表为主，左边出现就列出，如果右边表没有对应记录，展示null
right join 右链接

SELECT d.dname,e.empno,e.ename FROM emp e LEFT JOIN dept d  ON e.deptno=d.deptno

4 事务

4.1 事务的条件

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

4.2 事务的隔离级别

脏读：事务A读取了事务B未提交的数据。

不可重复度：事务A多次读取同一份数据，事务B在此过程中对数据修改并提交，导致事务A多次读取同一份数据的结果不一致。

幻读：事务A修改数据的同时，事务B插入了一条数据，当事务A提交后发现还有数据没被修改，产生了幻觉。

不可重复读侧重于update操作，幻读侧重于insert或delete。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

5 索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的种类：

聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。
非聚集索引：就是给普通字段加上索引。
联合索引：就是好几个字段组成的索引，称为联合索引。
B+树索引。