数据库基础

一、注释

-- 注释内容

二、数据类型

数值类型：常用int

日期和时间类型：常用DATE,TIME,YEAR,DATETIME

字符串类型：常用varchar(40),

MySQL 支持多种类型，可以分为三类：

• 数值

  tinyint : 小整数型，占一个字节
  int ： 大整数类型，占四个字节
      eg ： age int
  double ： 浮点类型
      使用格式： 字段名 double(总长度,小数点后保留的位数)
      eg ： score double(5,2)   

• 日期

  date ： 日期值。只包含年月日
      eg ：birthday date ： 
  datetime ： 混合日期和时间值。包含年月日时分秒

• 字符串

  char ： 定长字符串。
      优点：存储性能高
      缺点：浪费空间
      eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
  varchar ： 变长字符串。
      优点：节约空间
      缺点：存储性能底
      eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间    

案例：

需求：设计一张学生表，请注重数据类型、长度的合理性
    1. 编号
    2. 姓名，姓名最长不超过10个汉字
    3. 性别，因为取值只有两种可能，因此最多一个汉字
    4. 生日，取值为年月日
    5. 入学成绩，小数点后保留两位
    6. 邮件地址，最大长度不超过 64
    7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
    8. 学生状态（用数字表示，正常、休学、毕业...）

语句设计如下：

create table student (
    id int,
    name varchar(10),
    gender char(1),
    birthday date,
    score double(5,2),
    email varchar(15),
    tel varchar(15),
    status tinyint
);

分类	数据类型	大小	描述	备注
数值类型	tinyint	1 byte	小整数值
	smallint	2 bytes	大整数值
	mediumint	3 bytes	大整数值
	int或integer	4 bytes	大整数值	age int
	bigint	8 bytes	极大整数值
	float	4 bytes	单精度浮点数值
	double	8 bytes	双精度浮点数值	score double(总长度,小数点后保留的位数) 0~100 2
	decimal		字符串形式的浮点数	金融计算的时候一般是用decimal
日期和时间类型	DATE	3	日期值	birthday date
	TIME	3	时间值或持续时间
	YEAR	1	年份值
	DATETIME	8	混合日期和时间值
	TIMESTAMP	4	混合日期和时间值，时间戳	“张三”
字符串类型	char	0-255 bytes	定长字符串	name char(10) 10个字符空间 存储的性能高 浪费空间
	varchar	0-65535 bytes	变长字符串	name varchar(10) 2个字符空间 存储性能低 节约空间
	tinyblob	0-255 bytes	不超过 255 个字符的二进制字符串
	tinytext	0-255 bytes	短文本字符串
	blob	0-65 535 bytes	二进制形式的长文本数据
	text	0-65 535 bytes	长文本数据
	mediumblob	0-16 777 215 bytes	二进制形式的中等长度文本数据
	mediumtext	0-16 777 215 bytes	中等长度文本数据
	longblob	0-4 294 967 295 bytes	二进制形式的极大文本数据
	longtext	0-4 294 967 295 bytes	极大文本数据

三、数据库的字段属性

3.1 Unsigned：无符号的整数

声明了该列不能被声明为负数

3.2 Zerofill：零填充

不足的位数使用0来填充

3.3 AUTO_INCREMENT：自动递增

自动在上一条记录上+1（默认），常用来设计唯一的主键

3.4 NOT NULL：非空

3.5 default：默认

如果不指定该列的值会有一个默认值

四、数据表的类型（数据引擎）

4.1INNODB：默认使用

4.2MyISAM：早些年使用的

五、数据表的约束

5.1主键约束：primary key constraint

5.2外键约束：foreign key constraint

5.3非空约束：not null constraint

5.4唯一约束：unique constraint

5.5默认约束：default constraint

六、数据库函数

6.1常用函数：

数学函数：

ABS(); -- 绝对值

version();//版本

6.2聚合函数(查询中经常用到)

七、特殊的关键字(SQL中可能用到的)

7.1distinct：去重

-- 从用户表中查找不同的名字
select distinct name from user

7.2explain：分析SQL执行的状况

八、存储过程

九、事务

4.1 概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了==一组数据库操作命令==。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令==要么同时成功，要么同时失败==。

事务是一个不可分割的工作逻辑单元。

这些概念不好理解，接下来举例说明

张三和李四账户中各有100块钱，现李四需要转换500块钱给张三，具体的转账操作为

• 第一步：查询李四账户余额

• 第二步：从李四账户金额 -500

• 第三步：给张三账户金额 +500

现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题

4.2 语法

• 开启事务

  START TRANSACTION;
  或者  
  BEGIN;

• 提交事务

  commit;

• 回滚事务

  rollback;

4.3 代码验证

• 环境准备

  DROP TABLE IF EXISTS account;
  
  -- 创建账户表
  CREATE TABLE account(
  	id int PRIMARY KEY auto_increment,
  	name varchar(10),
  	money double(10,2)
  );

  -- 添加数据
  

INSERT INTO account(name,money) values('张三',1000),('李四',1000);

• 不加事务演示问题

  -- 转账操作
  -- 1. 查询李四账户金额是否大于500
  
  -- 2. 李四账户 -500
  UPDATE account set money = money - 500 where name = '李四';
  
  出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
  -- 3. 张三账户 +500
  UPDATE account set money = money + 500 where name = '张三';

  整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。

• 添加事务sql如下：

  -- 开启事务
  BEGIN;
  -- 转账操作
  -- 1. 查询李四账户金额是否大于500
  
  -- 2. 李四账户 -500
  UPDATE account set money = money - 500 where name = '李四';
  
  出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
  -- 3. 张三账户 +500
  UPDATE account set money = money + 500 where name = '张三';
  
  -- 提交事务
  COMMIT;
  
  -- 回滚事务
  ROLLBACK;

  上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

4.4 事务的四大特征

• 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

• 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

• 隔离性（Isolation） :多个事务之间，操作的可见性

• 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

==说明：==

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;

十、索引

• 主键索引 (Primary Key)

• 唯一索引 (Unique)

• 常规索引 (Index)

• 全文索引 (FullText)

8.1创建索引：

1.在创建表的时候给字段增加索引

2.创建完毕后，增加索引

-- 给studentName字段 增加一个全文索引
-- 方法一

alter table student add fulltext index studentName

-- 方法二 (create index 索引名 on 表名(字段名))

create index id_app_user_name on app_user(name)

8.2关于索引的SQL语句：

显示所有的索引信息

-- 展示student表中的 所有的索引信息

show index from student

使用索引来查询

select * from student where match(studentName) against("刘")

8.3索引原则

1.索引不是越多越好

2.不要对经常变动的数据加索引

3.小数量的不需要加索引

4.索引一般加在常用来查询的字段上

十一、权限管理和备份(运维)

9.1用户管理

SQL语句命令

创建用户：

create user wangrui identified by '123456'

修改当前用户密码：

set password = PASSWORD('123456')

修改指定用户密码

set password for wangrui = PASSWORD('123456')

给用户重命名

rename user wangrui to wangrui2

8.2mysql备份

1.直接拷贝data文件

2.使用可视化工具 Navicate

3.使用命令行导出

十二、规范数据库设计

三大范式：

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

前提：保证第一范式

每张表只描述一件事情

第三范式(3NF)

前提：保证第一第二范式

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范与性能不可兼得。要做一个平衡。

实际开发中：性能优先，比如有时候会故意增加一些冗余字段，防止多表查询，提高效率