数据库总结

一、数据库简介

1、数据库简介
  • 数据库（Database，DB）是按照数据结构来组织，存储和管理数据的仓库。
  • 典型特征：数据的结构化、数据间的共享、减少数据的冗余度、数据的独立性
  • 关系型数据库：使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。
  • 常见的关系型数据库产品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。
  • 数据表：数据表是关系数据库的基本存储结构，二维数据表有行(Row),和列(Column)组成，也叫作记录(行)和字段(列)。
2、数据库概念

DB：数据库（database）：存储数据的“仓库”。他保存了一系列有组织的数据。

RDBMS:数据库管理系统（Database Management System）数据库是通过DBMS创建和操作的容器

SQL：结构化查询语言（Structure Query language）：专门用来与数据库通信的语言

二、MySQL数据类型

1、常见的类型

数值类型

Java中

MySQL中

整型

byte

tinyint

short

smallint

int

int

long

bigint

浮点型

float

float

double

double

字符串类型

String

定长char() ;可变长varchar()

时间日期

date

date/time/datetime

三、SQL语句

1、SQL语句简介

SQL：结构化查询语言

SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言，可以用来查询数据，操纵数据，定义数据，控制数据。

2、SQL可以分为

数据定义语言（DDL），用来对数据库和表结构进行操作

-- create，alter，drop
1、创建数据库
create database +数据库名
2、创建数据库并指定字符集
CREATE DATABASE +数据库名 CHARACTER SET UTF8;
3、查看所有数据库
show databases
4、查看当前使用的数据库
select database()
5、修改数据库
ALTER DATABASE +数据库名
6、删除数据库
drop database +数据库名
7、切换数据库
use +数据库名
--------------------------------------
1、创建表
create table +表名（
字段 数据类型 约束
字段 数据类型 约束
...
）
2、查看数据库中所有的表
show tables；
3、查看表结构
desc+表名
4、表的删除
drop table +表名
5、表的修改
-- 修改表之添加列
alter table+表名  add +列名 数据类型 约束
-- 修改表之修改列类型
alter table + 表名 modify 列名 列的新类型
-- 修改表之列名称列类型一起修改
alter table + 表名 change 原列名 新列名 列名类型
-- 修改表之删除列:
ALTER TABLE 表名 DROP 列名;
-- 修改表之修改表名:
ALTER TABLE 表名 RENAME TO 新表名

数据操作语言（DML），对数据的操作

-- update insert delete
1、插入数据
insert into 表名 (列名1，列名2，列名3) values (列值1，列值2，列值3)
2、修改数据（往往都是添加条件）
update 表名 set 列名1=列值1(，列名2=列值2);
3、删除数据（往往都是添加条件）
delete from 表名 (where 条件);
delete、truncate、drop的区别
delete、truncate、只是删除表记录，而drop会直接删除表

事务控制语言（TCL）

数据查询语言（DQL），对数据的查询，不会修改数据库和表结构

-- 全表查询
select * from 表名
-- 查询部分字段
 SELECT 字段,字段,字段... FROM 表
 -- 过滤重复字段行。去重
 SELECT [DISTINCT] *|列名 FROM 表
 -- 查询字段起别名: 
 SELECT 字段 AS 新字段名,字段 新字段名 FROM 表
 -- 查询指定字段
 SELECT 字段,字段,字段... FROM 表 where=""
 -- 7.模糊查询
SELECT * FROM exam WHERE NAME LIKE '小_';
SELECT * FROM exam WHERE NAME LIKE '%%';
_代表匹配任意一个字符，%代表匹配0~n个任意字符
-- 排序 从上往下排列 order by（默认升序）
select * form 表名 order by 列名 (ASC) -- 升序 ()里面的内容为缺省值
select * from 表名 order by 列名 DESC  -- 降序
-- 使用多列作为排序条件: 当第一列排序条件相同时，根据第二列排序条件排序
select * from 表名 order by 列名1 ASC, 列名2 DESC
-- limit 子句 
-- 可以有一个参数(n) 代表 查询到第n行，|| 二个参数(n,m) 代表 从n行起(不包括) 往后查询m条记录
SELECT id,name,age,gander FROM student limit 3
SELECT id,name,age,gander FROM student limit 3,4
-- 聚合函数  max(最大值：该列的最大值)
-- min(最小值：该列的最小值) 
-- count(汇总：记录行数)  
-- avg(平均值：该列的平均值)
-- sum(求和：该列的总和)
select count(列名) from 表名; 
select max(列名) from 表名;    
select min(列名) from 表名;
select avg(列名) from 表名;
select sum(列名) from 表名;
-- 分组查询  分组查询的信息都是组的信息，不能查到个人的信息： 查询列需要满足的是有重复值。
group by
/**查询的列必须是分组的列*/
select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名
select gander,avg(age) from stu group by gander;
-- 分组查询条件用  having
select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名 having
select gander,avg(age) from stu group by gander having gander='男'

数据控制语言（DCL），创建用户和定义访问权限

-- 创建用户
用户只能在指定ip地址上登录mysql:create user 用户名@IP地址 identified by ‘密码’；
用户可以在任意ip地址上登录:create user 用户名@‘％’ identified by ‘密码’；
-- 给用户授权
grant 权限1，…，权限n on 数据库.* to 用户名@IP地址；其中权限1、2、n可以直接用all关键字代替。
权限例：create,alter,drop,insert,update,delete,select
-- 撤销授权
revoke 权限1，…，权限n on 数据库.* from 用户名@ ip地址；撤销指定用户在指定数据库上的指定权限。
-- 查看权限
show grants for 用户名@ip地址；
-- 删除用户
drop user 用户名@ip地址；

四、完整性约束

建表习惯：如果之前的表没用，加上drop table+表名，或者在创建表的时候加if not exists。例如：

drop table +表名
create table if not exists +表名（
。。。。。。。

）
主键约束：primary key (默认就是唯一非空的)
主键只能有一个，但可以由多个字段构成联合主键
外键约束: foreign key 用于在两个表之间建立关系，需要指定引用主表的哪一列。

如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表).
外键是用来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败
唯一约束：unique
非空约束：not null
默认约束：default 若插入时没有值，数据库便会存默认值
check：逻辑表达式约束
自增：auto_increment(必须是主键，非主键不能自增)

五、MySQL常用函数

1、数值型函数

函数

详情

CEILING(x)

返回大于x的最小整数值，向上取整

FLOOR(x)

返回小于x的最大整数值，向下取整

ROUND(x,y)

返回参数x的四舍五入的有y位小数的值 四舍五入

TRUNCATE(x,y)

返回数字x截短为y位小数的结果 PI() 返回pi的值（圆周率）

RAND()

返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值

2、字符串函数

函数

详情

LENGTH(s)

计算字符串长度函数，返回字符串的字节长度

CONCAT(s1,s2…,sn)

合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个

LOWER(str)

将字符串中的字母转换为小写

UPPER(str)

将字符串中的字母转换为大写

LEFT(str,x)

返回字符串str中最左边的x个字符

RIGHT(str,x)

返回字符串str中最右边的x个字符

TRIM(str)

删除字符串左右两侧的空格

REPLACE

字符串替换函数，返回替换后的新字符串 REPLACE(name,‘白’,‘黑’)

SUBSTRING

截取字符串，返回从指定位置开始的指定长度的字符换

REVERSE(str)

返回颠倒字符串str的结果

3、日期函数

函数

详情

CURDATE 和 CURRENT_DATE

两个函数作用相同，返回当前系统的日期值

CURTIME 和 CURRENT_TIME

两个函数作用相同，返回当前系统的时间值

NOW 和 SYSDATE

两个函数作用相同，返回当前系统的日期和时间值

UNIX_TIMESTAMP

获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数

FROM_UNIXTIME

将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数

MONTH

获取指定日期中的月份

MONTHNAME

获取指定日期中的月份英文名称

DAYNAME

获取指定曰期对应的星期几的英文名称

DAYOFWEEK

获取指定日期对应的一周的索引位置值

WEEK

获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53

DAYOFYEAR

获取指定曰期是一年中的第几天，返回值范围是1~366

DAYOFMONTH

获取指定日期是一个月中是第几天，返回值范围是1~31

YEAR

获取年份，返回值范围是 1970〜2069

DATE_ADD 和 ADDDATE

两个函数功能相同，都是向日期添加指定的时间间隔

DATE_SUB 和 SUBDATE

两个函数功能相同，都是向日期减去指定的时间间隔

ADDTIME

时间加法运算，在原始时间上添加指定的时间

SUBTIME

时间减法运算，在原始时间上减去指定的时间

DATEDIFF

获取两个日期之间间隔，返回参数 1 减去参数 2 的值

DATE_FORMAT

格式化指定的日期，根据参数返回指定格式的值

4、流程控制函数

函数

详情

IF(test,t,f)

如果test是真，返回t；否则返回f

IFNULL(arg1,arg2)

如果arg1不是空，返回arg1，否则返回arg2

NULLIF(arg1,arg2)

如果arg1=arg2返回NULL；否则返回arg1

CASE WHEN[test1] THEN [result1]…ELSE [default] END

如果testN是真，则返回resultN，否则返回default

CASE [test] WHEN[val1] THEN [result]…ELSE [default]END

如果test和valN相等，则返回resultN，否则返回default

列转行案例

CREATE TABLE `mystudent` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
)

insert into mystudent(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

SELECT user_name ,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM mystudent
GROUP BY USER_NAME;

select * from mystudent
6、加密函数

MD5() 计算字符串str的MD5校验和，生成一个不可逆的定长的字符串，字符串匹配，密码就正确。

六、MySQL视图

1、概念

视图其相当于从原来的数据表中获取部分数据，然后新建一个只可创建、查询和删除的新表来存放这些
数据。可以理解成把想要的数据部分截图下来保存，供以后查询用，此时视图只是为了满足某些数据查
询而建立的对象

2、与表的区别
  1. 表直接将数据存储在磁盘上，视图是将sql语句存储到磁盘上
  2. 视图是建立在表的基础上，表存储数据库中的数据，而视图显示已经在表中的数据的外观
  3. 表是静态的，而视图是动态的，意思是表中数据发生了改变，其建立在表基础的视图跟着改变
  4. 通过视图不能改变表中数据（一般情况下）
  5. 删除视图，表不受影响，而删除表，视图不再起作用
  6. 视图本身没有数据，只保存了sql语句（本质）
3、创建视图
create view 视图名(列名1, 列名2, ...) as select语句;
create view mystu (id,name,age) as select id,name,age from student where age>10;
3.1显示视图
show create view 视图名;
show create view mystu;
select * from  mystu;
3.2删除视图
drop view 视图名;
drop view mystu;

七、MySQL事物

1、什么是事物

（1）不可分割的操作，每条sql语句都是一个事物

（2）事物只对修改数据库有效，对查询数据库无效。所以查询操作不需要设置事物

（3）在MySQL中只有使用了innodb（最常用的存储引擎）数据库引擎的数据库或表才支持事物

（4）事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

2、事物的四大特征（必须满足的4个条件ACID）

（1）原子性(Atomicity)：原子性 是指事物包括的所有操作要么全部成功，要么全部失败回滚。

（2）一致性(Consistency)：一致性是指事物必须使数据库从一个一致性状态转换到另一个一致性状态，也就是说一个事物在执行之前和执行之后都必须处于一致性状态；让数据保持一定上的合理；

（3）隔离性(Isolation)：隔离性 是指当多个用户并发访问数据库时。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比如：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）

（4）持久性(Durability):持久性 是指一个事物一旦被提交了，就不能再回滚了，已经把数据保存到数据库中了。

3、事物有关的操作

-- MySQL里默认不开启事物
1、开启事物 bengin/start transaction
2、提交事物 commit (所有语句全部执行完毕，没有发生异常，提交数据，更新数据库)
3、回滚事物：rollback (当遇到突发情况，撤销执行的sql语句)
-- 事物的提交、回滚
 -- 创建 user表
  create table user(
	id int primary key auto_increment,
	name VARCHAR(20),
	balance DECIMAL(10,2) UNSIGNED
);
-- 添加数据

insert into user VALUES (1,'楠哥',200);
insert into user VALUES (2,'楠哥老婆',50000);
-- 开启事物 begin/START TRANSACTION

-- 转账业务，必须都成功，或者都失败，所以不能一句一句执行，万一执行了一半，断电了咋办
-- 所以要编程一个整体
-- 都成功
-- begin;
-- 提交一个事物
start transaction;    
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
commit;

-- 事物的回滚（回滚到事物开启前的状态）
start transaction; 
UPDATE user set balance = balance + 200 where id = 2;
ROLLBACK;

-- 都失败
start transaction;
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
rollback;

4、事物的隔离性

隔离强调的是两个或两个以上同时发生（并发）的业务同时操作一个数据库，为了让两个事务一方面能都看到、得到正确的结果，一方面还要保证一定的效率而产生的不同的隔离级别。

4.1隔离性有隔离级别（4个）

（1）TRANSACTION_READ_UNCOMMITTED（读未提交）

（2）TRANSACTION_READ_COMMITTED （读已提交）

（3）TRANSACTION_REPEATABLE_READ （可重复读）

（4） TRANSACTION_SERIALIZABLE （串行化）

**注意：**MySQL中默认隔离级别是TRANSACTION_REPEATABLE_READ （可重复读）

我们可以在sql命令行中使用select @@tx_isolation来查看当前数据库的事物隔离级别。

使用 set transaction isolation level 隔离级别名 来设置数据库的事物隔离级别。

set session transaction isolation level repeatable read;
SET  transaction isolation level read uncommitted;
SET  transaction isolation level read committed;
set  transaction isolation level repeatable read;
SET  transaction isolation level serializable;

SET GLOBAL transaction isolation level read uncommitted;
SET GLOBAL transaction isolation level read committed;
set GLOBAL transaction isolation level repeatable read;
SET GLOBAL transaction isolation level serializable;
其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。
4.2事物的并发问题

1、读未提交（脏读）

一个事务读到另一个事务未提交的更新数据，就是有A,B两个事物，B事物做了更新的操作，此时B事物还没有提交，但是在A事物中，能够读到B事物中提交的结果。

-- 例子
老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交
实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

解决办法：Read committed读提交，能解决脏读问题

2、读已提交（不可重复读）

不可能重复读是在同一个事物内，多次查询表中的同一行数据，发现多次查询的结果不一致。

不可重复读和脏读的区别在于，不可重复读读取的是同一个事物中已提交的数据，脏读读取的是不同事物中未提交的数据。

程序员拿着工资卡（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了。程序员就会很郁闷，明明卡里是有钱的…
一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读

解决办法：Repeatable read

3、可重复读（幻读）

幻读指的是：读取同一张表中的数据，发现多次读取的结果集不一致（也就是表中的数据总行数不一致），也就说读取到了另外一个事物插入的数据。

不可重复读和幻读的区别在于：不可重复读读取的是同一张表同一行的数据，而幻读读取的是同一张表中的数据。

-- 重复读
程序员拿着工资卡（卡里还是有3.6万），当他买时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了
-- 幻读
程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

解决办法：Serializable

5、串行化

同时开启事务A和事务B，事务A在操作数据库时，事务B只能排队等待

八、数据库设计

1、数据库设计三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。目的：减少数据冗余

第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

2、常见的设计范式

（1）第一范式（有主键，确保每列保持原子性）

第一范式是最基本的范式。数据库表中的所有字段值都是不可分解的原子值，数据库表中不能出现重复记录

（2）第二范式（确保表中的每列都和主键有关）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中

（3）第三范式（确保每列都和主键列直接相关，而不是间接相关）

3、数据表关系

（1）一对多

举例：用户和订单

一个用户可以有多个订单

（2）多对多

举例：订单和商品

一个订单可以有多个商品，一个商品也可以对应多个订单

（3）一对一

举例：人和身份证

每个人对一个身份证号，同时一个身份证号也只能唯一对应那个人