【MySQL】笔记（4）— 创建表；插入，修改，删除数据；主键，外键约束；事务；索引；视图；三范式；

一.创建表：

1.1 建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
….

);

1.2 关于MySQL当中字段的数据类型？以下只说常见的：
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型 （对应Java中的java.sql.Date）
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）
……

1.3 char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

1.4 BLOB和CLOB类型的使用？
电影表: t_movie
id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班
2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班
3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班
4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班
5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班
缺点：冗余。【不推荐】

第二种方案：两张表(班级表和学生表)[推荐]

t_class 班级表
cno(pk) cname
1 zs1 101
2 zs2 101
3 zs3 102
4 zs4 102
5 zs5 102

• 写下上表的造表语句：
  
  [En]

  write the table-building statements of the above table:*

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做 子表。t_class表叫做 父表。

顺序要求：
删除数据时，先删除子表，再删除父表

添加数据时，首先添加父表，然后添加子表

创建表时，请先创建父表，然后再创建子表

删除表时，请先删除子表，然后再删除父表

drop table if exists t_student;
drop table if exists t_class;

create table t_class( //先创建父表
cno int,
cname varchar(255),
primary key(cno)
);

create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)//引用
);

insert into t_class values(101,’xx’); //先添加父表

insert into t_student values(1,’zs1′,101);

insert into t_student values(7,’lisi’,103); //报错：不能添加父表中的cno所没有的classno

6.3 注意：外键可以为NULL；
外键字段引用其他表的某个字段的时候，被引用的字段不一定是主键，但至少具有unique约束;

七.事务（Transaction）:

7.1、一个事务是一个完整的业务逻辑单元，不可再分。

银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance – 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败，而要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的”事务机制”；

7.2、和事务相关的语句只有：DML语句（insert delete update）
为什么？因为这三条语句都与数据库表中的数据相关；事务的存在是为了保证数据的完整性和安全性。

7.3、假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
那就不需要了；但实际情况不是这样的，通常一个”事儿（事务/业务）”需要多条DML语句共同联合完成。

7.4、事务的特性？
事务包括四大特性：ACID
A: 原子性：事务是最小的工作单元，不可再分；
C: 一致性：事务必须保证多条DML语句同时成功或者同时失败；
I：隔离性：事务A与事务B之间具有隔离；
D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束；

7.5、关于事务之间的隔离性
有用于事务隔离的隔离级别，理论上包括四个：

第一级别： 读未提交（read uncommitted）
对方的交易尚未提交，我们当前的交易可以读取对方的未提交数据。

读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
第二级别： 读已提交（read committed）
我们可以在提交对方的事务后读取数据；这种隔离级别解决了“脏读”现象。

提交阅读的问题是它不能重复阅读。

第三级别： 可重复读（repeatable read）
这种隔离级别解决了”不可重复读问题”；
这种隔离级别的问题在于，读取的数据是一种错觉。

第四级别： 序列化读/串行化读（serializable）
解决了所有的问题；但效率低下。交易需要排队。

oracle数据库默认的隔离级别 — 读已提交（2）；
mysq l数据库默认的隔离级别 — 可重复读（3）；

7.6、演示事务
* mysql事务默认情况下是自动提交的。
（什么是自动提交？只要执行任意一条DML语句则提交一次）怎么关闭自动提交 — start transaction;

• 准备表：
  drop table if exists t_user;
  create table t_user(
  id int primary key auto_increment,
  username varchar(255)
  );

• 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
  mysql> insert into t_user(username) values(‘zs’);

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+

mysql> rollback; // 回滚（撤回未提交的操作）

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
* 演示：使用start transaction 关闭自动提交机制：
mysql> start transaction;

mysql> insert into t_user(username) values(‘lisi’);

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 2 | lisi |
+—-+———-+

mysql> insert into t_user(username) values(‘wangwu’);

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 2 | lisi |
| 3 | wangwu |
+—-+———-+

mysql> rollback;

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 张三
2 李四
3 王五

t_teacher 讲师表
tno(pk) tname
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3

第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
公式：一对多？两个表、多个表和外键

班级t_class
cno(pk) cname
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2

注：在实际开发中，主要是为了满足客户的需求，有时会以冗余换取执行速度。

7.3、一对一怎么设计？

第一种设计方案 — 主键共享
t_user_login 用户登录表
id(pk) username password
1 张三 1111111111
2 李四 1111415621

第二种设计方案 — 外键唯一
t_user_login 用户登录表
id(pk) username password
1 张三 1111111111 2
2 李四 1111415621 1

随笔：

1.增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）

2.复合主键（不需要掌握）：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)
);
insert …….

3、DBA命令
3.1、将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333

在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123

3.2、导入数据
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql

4.存储引擎：（了解）
4.1、完整的建表语句
CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

创建表时，可以指定存储引擎或字符集。

mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8

4.2、什么是存储引擎呢？
存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，
就是”表的存储方式”）

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每个存储引擎都有自己的优缺点，在正确的时间选择正确的存储引擎是必要的。

4.3、查看当前mysql支持的存储引擎？
show engines \G

mysql 5.5.36版本支持的存储引擎有9个：
***** 1. row ****
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*** 2. row ***
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*** 3. row ***
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 4. row ***
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*** 5. row ***
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 6. row ***
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*** 7. row ***
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 8. row ***
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*** 9. row ******
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO

4.4、常见的存储引擎？

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点：可压缩，节省存储空间。并且可以将其转换为只读表，以提高检索效率。

缺点：不支持事务。

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

缺点：不支持交易。数据很容易丢失。因为所有数据和索引都存储在内存中。

优点：查询速度最快。
以前叫做HEPA引擎。

5.索引实现原理：

Original: https://www.cnblogs.com/Burning-youth/p/15680722.html
Author: 猿头猿脑的王狗蛋
Title: 【MySQL】笔记（4）— 创建表；插入，修改，删除数据；主键，外键约束；事务；索引；视图；三范式；