MySQL

目录

1.DDL

1.1创建表

1.2字段类型

1.2.1  数字类型

1.2.2  字符类型

1.2.3  日期类型

1.3  约束

1.3.1  主键约束

1.3.2  唯一约束

1.3.3  非空约束

1.3.4  默认值约束

1.3.5  外键约束

1.4  alter & drop

2.DML

2.1  insert

2.2 update

2.3  delete

3.  DCL

4.事务

4.1  使用事务解决操作原子性

4.2 事务的四大基本特征acid

4.3  事务不隔离存在的问题

4.4  事务隔离级别

4.4.1 读未提交（read uncommitted）

4.4.2 读已提交（read committed）

4.4.3 可重复读（repeatable read）

4.4.4 串行化（serializable）

5.视图

6.数据库设计原则

6.1 第一范式

6.2 第二范式

6.3 第三范式

---

1.DDL

数据定义语言（DDL：Data Definition Language）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。

DDL 只操作数据库、表、视图、索引，但是不操作表中的数据

1.1创建表

语法：
create table 表名(
  字段名1  字段类型 [约束1 约束2 ....],
  字段名2  字段类型 [约束1 约束2 ....]
)

1.2字段类型

1.2.1  数字类型

1：区分有符号和无符号，如果有符号高位表示正数（0）和负数（1），如果无符号，那么高位是数字的一部分

2：正数型len（长度）无意义的，决定整数只有它的字节大小

3：可以用float和double去表示浮点数，但是float和double存在精度丢失

4：如果不能容忍精度丢失，强烈建议使用decimal数据类型

CREATE TABLE t_user (

id INT , age TINYINT , balance DECIMAL(4,2)

)

DECIMAL(m,n) m = 正数部分+小数部分 n：小数部分，如果小数部分超过n会自动四舍五入

1.2.2  字符类型

char：定长，例如char(5),如果数据没有超过5，会使用空格补，定长的缺点占用磁盘空间，优点是读取效率比较高

varchar：变长，例如varchar（5） ,如果数据只占用1个字符，不会使用空格补，变长的缺点读取效率比较低，优点是节省磁盘空间

text:大本文存储

1： TINYTEXT(255字符)

2：TEXT(65535字符)

3：MEDIUMTEXT（16M）

4：LONGTEXT(long最大值4G)

BLOB (binary large object)，二进制大对象，是一个可以存储二进制文件（文件、视频、音频、图片）等

1.TinyBlob 最大 255字节 2.Blob 最大 65K 3.MediumBlob 最大 16M 4.LongBlob 最大 4G

1.2.3  日期类型

datetime 与timestamp的区别

1：时间范围的区别

2：datetime 不会随着时区变化，但是timestamp随着时区自动变化

datetime : 时间日期型，格式是YYYY-mm-dd HH:ii:ss时间范围（1000-01-01 00:00:00 到 9999-12-31 23:59:59）

date：日期，就是datetime中的date部分；

time：时间，就是datetime中的time部分 ；

timestamp：格式是YYYY-mm-dd HH:ii:ss时间范围，时间范围（1970-01-01 00:00:00 到 2037-12-31 23:59:59）

year：yyyy的范围是1901-2155

1.3  约束

1.3.1  主键约束

主键（primary key）：唯一标记数据表中一行数据

主键约束：一旦一个字段被设置为主键，那么这个字段的值必须唯一而且不能为null

主键自增长 :当主键的字段类型为int类型时，可以设置主键自增

id INT PRIMARY KEY AUTO_INCREMENT

注意点：一张表中只能有一个主键，不允许多个主键 但是可以联合主键

PRIMARY KEY(username,`PASSWORD`) 

1.3.2  唯一约束

唯一约束：一旦一个字段设置了唯一约束，那么这个字段的值必须唯一，可以为null

一张表可以给多个字段设置唯一约束

username VARCHAR(20) UNIQUE 

1.3.3  非空约束

非空约束，一旦表字段设置了非空约束，那么这个字段的值必须不能为null

username VARCHAR(20)  NOT NULL

1.3.4  默认值约束

sex CHAR(2) DEFAULT '男'

1.3.5  外键约束

外键（foreign key） 简称FK ，外键的值包含在另外一张表的主键中，设计外键的目的就是为了连表查询

外键约束：一旦一个外键设置了外键约束，那么

1：外键的值必须来源于另外一张表的主键，不能无中生有

2：删除主键表中的数据是，如果有外键引用了这条数据，那么这条数据不允许被删除

1.4  alter & drop

ALTER TABLE emp  ADD email VARCHAR(200)

DROP TABLE IF EXISTS emp

2.DML

数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

2.1  insert

语法1：insert into 表名 (字段1,字段2,..........)values(字段1的值，字段2的值,........)

语法2：INSERT INTO 表名 VALUES(字段1的值，字段2的值,.......)

注意点：必须是全字段插入，而且字段的值和字段的顺序必须保持一致

语法3：批量插入

INSERT INTO 表名(字段1,字段2,..........)VALUES(字段1的值，字段2的值,........),(字段1的值，字段2的值,........),...

2.2 update

语法：update 表名 set 字段1=值 ,字段2 =值,........[where 条件]

注意：在更新操作建议要加where子句，否则会导致全表更新

2.3  delete

DELETE FROM 表名 [where]

注意：删除时建议加where子句，避免全表删除

3.  DCL

数据控制语言（DCL：Data Control Language）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

-- 创建用户(只有root用户才有权限创建)
CREATE USER jack IDENTIFIED BY '123456'

-- 授权
GRANT SELECT ON companydb.departments TO jack
GRANT INSERT ON companydb.departments TO jack

-- 取消权限
REVOKE INSERT ON  companydb.departments FROM jack
REVOKE SELECT  ON companydb.departments FROM  jack

-- 删除用户
DROP USER jack

4.事务

概念：事务是一个最小的不可再分的工作单元，通常一个事务就是一个完整的业务

完整业务：下单（向订单表插入一条订单数据insert，在商品表中修改商品剩余库存update）

完整业务：支付（扣除用户的账户余额update，修改订单状态update）

完整业务：未知（插入操作、查询操作）

事务只跟DML语句有关，或者说DML语句才有事务

4.1  使用事务解决操作原子性

当关闭自动提交功能mysql就可以支持事务

开启事务 start transaction/begin

提交事务 commit

回滚事务 rollback

4.2 事务的四大基本特征acid

• 原子性（Atomicity）：数据库中原子性强调事务是一个不可分割的整体，事务开始后所有操作要么全部成功，要么全部失败，不可能停滞在中间某个环节。如果事务执行过程中出错就会回滚到事务开始前的状态，所有的操作就像没有发生一样不会对数据库有任何影响

  mysql是通过undolog日志实现事务的原子性

• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。一致性是事务管理的最终目标。

• 持久性（Durability）：一个事务一旦被提交commit，则对数据库的所有更新将被永久保存到数据库，不能被回滚

• 隔离性（Isolation）：多个并发事务之间要相互隔离，事务之间不能相互影响，mysql默认设置了事务的隔离

4.3  事务不隔离存在的问题

1、脏读：事务B读到事务A没有提交的数据

2、不可重复读：事务B在同一个事务中，因为事务A修改(UPDATE)了数据并提交，造成事务B两次读取到的数据不一致

3、幻读：事务B在同一个事务中，因为事务A新增(INSERT)了数据并提交，如果事务B修改数据，会出现多修改了一条数据，出现幻觉

4.4  事务隔离级别

1：每一种隔离级别可以解决特定的错误读问题，当然read uncommitted例外 不能解决三类错误读问题

2：隔离级别越高数据越安全，但是效率越低

set session transaction isolation level （隔离级别）

4.4.1 读未提交（read uncommitted）

4.4.2 读已提交（read committed）

4.4.3 可重复读（repeatable read）

4.4.4 串行化（serializable）

mysql默认的事务隔离级别是 repeatable read

oracle默认的事务隔离级别是 read committed

5.视图

视图（view）：是根据用户需求定义的数据结构，是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

视图：是一张虚拟表（逻辑表），视图只有表的结构（字段信息），没有表的数据，视图的数据通过sql从物理表获取。

语法：create view 视图名称 as dql

优点：

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

6.数据库设计原则

数据库设计需要达到的目的

1：尽量避免数据的冗余

2：方便对数据的维护（增删改查）

6.1 第一范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。

6.2 第二范式

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于联合主键的一部分)

设计表的经验

1：一张表只能描述一个实体，不能描述多个实体

2：如果实体与实体之间是多对多的关系，那么必须通过中间表来维护他们之间的多对多关系

6.3 第三范式

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

1：一张表只能描述一个实体

2：如果实体与实体之间是一对多的关系，通过外键去维护一对多的关系，外键设计在多的一方

如果两个实体是一对一的关系？

通过外键来维护实体之间的一对一的关系，外键可以放在任何一方。