MYSQL表的使用

创建表

建表句的语法根式：
create table 表名(字段名1 数据类型, 字段明2 数据类型, 字段名3 数据类型, …);

关于MYSQL当中字段的数据类型？
常见：
int（整数型）（java中的int）
bigint（长整型）（java中的long）
float（浮点型）（java中的float double）
char（定长长字符串）（string）
varchar（可变长字符串最多255个）（StringBuffer、StringBuilder）
date（日期类型）（对应java中的java.sql.date类型）
BLOB（Binary Large OBject 二进制大对象，如存储图片、视频等流媒体信息）（对应java中的object）
CLOB（Character Large OBject 存储较大文本，比如，可以存储4G的字符串。）（对应java中的object）

char和varchar怎么选择

　　在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都采用char。

　　当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

BLOB和CLOB类型的使用？
　　电影表：t_movie
　　id(int) 　　name(varchar)　　playtime(date/char)　　poster(BLOB)　　history(CLOB)

————————————————————————————————————————————
　　1　　　
　　2
　　3

表名在数据库当中一般建议以： t＿或者tbl＿开始

创建学生表：
　　学生信息包括：
　　　　学号、姓名、性别、班级编号、生日
　　　　学号：bigint
　　　　姓名：varchar
　　　　性别：char
　　　　班级编号：int
　　　　生日：char

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
);

insert语句查询数据

语法格式：
insert into 表名(字段名1,字段名2,……) values(值1,……)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values('2','lisi','1','高三','2001-12-29');
mysql> select * from t_student;

+——+———-+——+———+————+
| no | name | sex | classno | birth |
+——+———-+——+———+————+
| 1 | zhangsan | 1 | 高三 | 2001-12-29 |
+——+———-+——+———+————+
1 row in set (0.00 sec)

insert into t_student(name) values('wangwu');//除name字段之外，剩下的所有字段自动插入NULL。

mysql> select * from t_student;

+——+———-+——+———+————+
| no | name | sex | classno | birth |
+——+———-+——+———+————+
| 1 | zhangsan | 1 | 高三 | 2001-12-29 |
| 2 | lisi | 1 | 高三 | 2001-12-29 |
| NULL | wangwu | NULL | NULL | NULL |
+——+———-+——+———+————+

insert into t_student(no) values('3');

mysql> insert into t_student(no) values(‘3’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+——+———-+——+———+————+
| no | name | sex | classno | birth |
+——+———-+——+———+————+
| 1 | zhangsan | 1 | 高三 | 2001-12-29 |
| 2 | lisi | 1 | 高三 | 2001-12-29 |
| NULL | wangwu | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL | NULL |
+——+———-+——+———+————+
4 rows in set (0.00 sec)

drop table if exists t_student;
create table t_student(
	no bigint,
	name varchar(255),
	sex char(1) default 1,
	classno varchar(255),
	birth char(10)
);

insert into t_student(name) values('zhang');

mysql> insert into t_student(name) values('zhang');

mysql> select * from t_student;
+——+——-+——+———+——-+
| no | name | sex | classno | birth |
+——+——-+——+———+——-+
| NULL | zhang | 1 | NULL | NULL |
+——+——-+——+———+——-+
1 row in set (0.00 sec)

需要注意的地方：
　　当一条insert语句执行成功之后，表格当中必然会多一行记录。
　　即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了
　　只能使用update进行更新。

insert into t_student values(1,'jack','0','gaosan','1986-10-23');

mysql> insert into t_student values(1,'jack','0','gaosan','1986-10-23');

Query OK, 1 row affected (0.00 sec)
//字段可以省略不写，但是后面的value对数量和顺序都有要求。
mysql> select * from t_student;
+——+——-+——+———+————+
| no | name | sex | classno | birth |
+——+——-+——+———+————+
| NULL | zhang | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan | 1986-10-23 |
+——+——-+——+———+————+

//一次插入多行数据
insert into t_student
    (no,name,sex,classno,birth) 
values
    (3,'rose','1','gaosi2','2002-02-10'),
    (4,'se','0','gaosi2','2202-02-10');

mysql> select * from t_student;

+——+——-+——+———+————+
| no | name | sex | classno | birth |
+——+——-+——+———+————+
| NULL | zhang | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan | 1986-10-23 |
| 3 | rose | 1 | gaosi2 | 2002-02-10 |
| 4 | se | 0 | gaosi2 | 2202-02-10 |
+——+——-+——+———+————+

表的复制

mysql> create table emp1 as select * from emp;

Query OK, 14 rows affected (0.05 sec)
Records: 14 Duplicates: 0 Warnings: 0

语法：
create table 表名 as select语句;
将查询结果当做表创建出来

将查询结果插入到一张表中

mysql> insert into dept1 select * from dept;
mysql> select * from dept1;

+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+
8 rows in set (0.00 sec)

修改数据：update

语法格式：
update 表名 set 字段名1=值1,字段名2=值2…… where 条件;

注意：没有条件整张表数据全部更新

案例：将部门10的loc修改为shanghai，将部门名称修改为人事部

update dept1 set loc='shanghai', dname='renshibu' where deptno=10;
mysql> update dept1 set loc='shanghai', dname='renshibu' where deptno=10;

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from dept1;
+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | renshibu | shanghai |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | renshibu | shanghai |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+
8 rows in set (0.00 sec)

更新所有记录
    update dept1 set loc='x', dname='y';
    mysql> select * from dept1;

+——–+——-+——+
| DEPTNO | DNAME | LOC |
+——–+——-+——+
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
+——–+——-+——+

删除数据？

语法格式：

delete from 表名 where 条件;

注意：没有条件全部删除

删除10部门数据？

**delete from dept1 where deptno = 10;**

删除所有记录？

**delete from dept1;**

怎么删除大表中的数据？（重点）

truncate table 表名; //表被截断，不可回滚。永久丢失

删除表？

drop table 表名;//这个通用。
drop table if exists 表名;//oracle不支持这种写法

对于表结构的修改

设计好了之后，对表结构的修改是很少的
需要修改表结构，我们也可以直接使用工具操作。
出现在java代码当中的sql包括：insert delete update select(这些都是表中的数据操作)

增删改查有一个术语： CRUD操作
create(增) retrieve（检索） update（改） delete（删除）

约束（constraint）

什么时约束？常见的约束有哪些呢？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
**合法性、有效性、完整性**
常见的约束有哪些？
  非空约束(not null)：约束的字段不能为NULL。
  唯一约束(unique)：约束的字段不能重复
  主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
  外键约束(foreign key)：……（简称FK）
  检查约束(check):注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

非空约束 not null

drop table if exists t_user;
create table t_user(
   id int,
   username varchar(255) not null,
   password varchar(255)
 );

insert into t_user(id,password) values(1,'123');

唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
案例：给某一列添加unique

drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique  //列级约束
);
insert into t_user values(1,'zhangsan');
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);

案例：给两个列或者多个列添加unique

drop table if exists t_user;
create table t_user(
  id int,
  usercode varchar(255),
  username varchar(255),
  unique(usercode,username) //联合添加一个约束 【表级约束】
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;

drop table if exists t_user;//两个唯一约束
create table t_user(
  id int,
  usercode varchar(255) unique,
  username varchar(255) unique,
);

注意：not null只有列级约束，没有表级约束

主键约束

怎么给一张表添加主键约束呢？

drop table if exists t_user;
create table t_user(
  id int primary key,
  username varchar(255),
  email varchar(255)
 );
 insert into t_user(id,username,email) values(1,'lis','lis@123.com');
 insert into t_user(id,username,email) values(2,'zs','zs@123.com');
 insert into t_user(id,username,email) values(3,'ww','ww@123.com');
 select * from t_user;

+—-+———-+————-+
| id | username | email |
+—-+———-+————-+
| 1 | lis | lis@123.com |
| 2 | zs | zs@123.com |
| 3 | ww | ww@123.com |
+—-+———-+————-+

mysql> desc t_user;
+———-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+———-+————–+——+—–+———+——-+

· 根据以上的测试得出，id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
· 主键的特点：不能为NULL，也不能重复

· 主键相关的术语

主键约束：primary key
主键字段：id字段添加primary key之后，id称为
主键值：id字段中的每一个值都是主键值

主键有什么作用
　表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
　主键的作用：主键值是这行记录在这张表当中的唯一标识（就像一个人的身份证号一样。）

主键的分类？
　根据主键字段的字段数量来划分：
单一主键（推荐，常用的）
复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）
　根据主键性质来划分
自然主键：主键值最好就是一个和业务没有任何关系的自然数（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键（不推荐用）
最好不要拿着和业务挂钩的字段作为主键。因为以后业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候后没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有一个。（重点）

使用表级约束定义主键

drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255),
  primary key(id)
 );
 insert into t_user(id,username) values(1,'cs');
 insert into t_user(id,username) values(2,'as');
 insert into t_user(id,username) values(3,'w');
 insert into t_user(id,username) values(4,'s');
 select * from t_user;

以下内容是演示一下符合主键，不需要掌握

drop table if exists t_user;
 create table t_user(
   id int,
   username varchar(255),
   password varchar(255),
   primary key(id,username)
  );
  insert into t_user(id,username) values(1,'cs');
  insert into t_user(id,username) values(2,'as');
  insert into t_user(id,username) values(3,'w');
  insert into t_user(id,username) values(4,'s');
  select * from t_user;

mysql提供主键值自增：

drop table if exists t_user;
create table t_user(
 id int primary key auto_increment, //id字段自动维护一个自增的数字，从1开始，以1递增
 username varchar(255)
 );
 insert into t_user(username) values('a');
 insert into t_user(username) values('b');
 insert into t_user(username) values('c');
 insert into t_user(username) values('d');
 insert into t_user(username) values('e');
 insert into t_user(username) values('f');
 select * from t_user;

提示：oracle当中也提供了一个自增机制，叫做序列（sequence）对象

外键约束

*　关于外键约束的相关术语*
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。

业务背景：
*　请设置数据库表，用来维护学生和班级的信息*

• 第一种方案：一张表存储所有数据*
  缺点：冗余。（不推荐）

• 第二种方案：两张表（班级表和学生表）*
  t_class 班级表
  t_student学生表

  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,'xxxxxxxxxxxxx');
  insert into t_class values(102,'yyyyyyyyyyyyy');
  
  insert into t_student values(1,'zs1',101);
  insert into t_student values(2,'zs2',101);
  insert into t_student values(3,'zs3',102);
  insert into t_student values(4,'zs4',102);
  select * from t_class;
  select * from t_student;

+—–+—————+
| cno | cname |
+—–+—————+
| 101 | xxxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyy |
+—–+—————+
2 rows in set (0.00 sec)

+——+——-+———+
| sno | sname | classno |
+——+——-+———+
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
+——+——-+———+
4 rows in set (0.00 sec)

外键值可以为NULL？
　可以为NULL

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
　注意：被引用的字段不一定是主键，但至少具有unique约束。

存储引擎（整个内容属于了解内容）

完整的建表语句

CREATE TABLE `t_x` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在mysql当中，凡是标识符是可以使用飘号括起来的。'`'

建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式
默认采用的字符集是UTF-8

什么时存储引擎？

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

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

———–+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

常见的存储引擎

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

---

　InnoDB
*　优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

*　表的结构存储在xxx.frm文件中*
*　数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换只读。*
*　这种InnoDB存储引擎在Mysql数据库崩溃之后提供自动恢复机制*
*　InnoDB支持级联删除和级联更新*

---

　MEMORY
　缺点：不支持事务。数据容易丢失，因为所有数据和索引都是存储在内存当中的。
　优点：查询速度最快
*以前叫做HEPA引擎**

事务（Transaction）

什么是事务

　一个事务是一个完整的业务逻辑单元，不可再分。
例如：银行账户转账，从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语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

和事务相关的语句只有：DML语句。（insert delete update）

*这三个语句都是和数据库表当中“数据”相关的。*

　 事务的存在是为了保证数据的完整性，安全性

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

　不需要事务。
　但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

事务的特性？

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

关于事务之间的隔离性

　事务隔离性存在隔离级别，理论上隔离级别包括4个：
　 第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了：脏读现象没有了
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复读问题
这种隔离级别存在的问题是：读取到的数据是幻想。
第四级别：序列化读/串行化读
解决了所有问题
效率低，需要事务排队

oracle数据默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。

演示事务

*　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，则提交一次。

insert into t_user(username) values('zs');
select * from t_user;
rollback;
select * from t_user;

mysql> insert into t_user(username) values(‘zs’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

　演示:使用start transaction;关闭自动提交机制。

start transaction;
insert into t_user(username) values('lisi');
select * from t_user;
insert into t_user(username) values('wangwu');
select * from t_user;
rollback;
select * from t_user;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values(‘lisi’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | lisi |
+—-+———-+
2 rows in set (0.00 sec)

mysql> insert into t_user(username) values(‘wangwu’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | lisi |
| 5 | wangwu |
+—-+———-+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

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

start transaction;
insert into t_user(username) values('wangwu');
insert into t_user(username) values('rose');
insert into t_user(username) values('jack');
select * from t_user;
commit;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values(‘lisi’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | lisi |
+—-+———-+
2 rows in set (0.00 sec)

mysql> insert into t_user(username) values(‘wangwu’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 4 | lisi |
| 5 | wangwu |
+—-+———-+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
+—-+———-+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values(‘wangwu’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(username) values(‘rose’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username) values(‘jack’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 6 | wangwu |
| 7 | rose |
| 8 | jack |
+—-+———-+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 6 | wangwu |
| 7 | rose |
| 8 | jack |
+—-+———-+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+—-+———-+
| id | username |
+—-+———-+
| 1 | zs |
| 6 | wangwu |
| 7 | rose |
| 8 | jack |
+—-+———-+
4 rows in set (0.00 sec)

　演示两个事务，假设隔离级别
　　演示级别1：读未提交

set global transation isolation level read uncommitted;
select @@global.tx_isolation;

　　演示级别2：读已提交

set global transaction isolation level read commiteed;
select @@global.tx_isolation;

　　演示级别3：可重复读

set global transaction isolation level repeatable read;

　　演示级别4：serializeble

set global transaction isolation level serializeble;

mysql远程登录： mysql -h192.168.151.18 -uroot -p444

索引

什么时索引？有什么用？

　索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
　在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全表扫描

• 第二种方式：根据索引检索（效率很高）*

• 索引为什么可以提高检索效率呢？*

• 　根本的原理是缩小了扫描的范围。

  索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，
  也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
  这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

  添加索引是给某一个字段，或者说某些字段添加索引

  select ename,sal from emp from where ename = ‘SMITH’;
  当ename字段上没有添加索引的时候，以上SQL语句会进行全表扫描，扫描ename字段中所有的值。
  当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

怎么创建索引对象？怎么删除索引对象？

创建索引对象
create index 索引名称 on 表名（字段名）;
删除索引对象：
drop　index 索引名称 on 表名

什么时候考虑给字段添加索引？（满足什么条件）

*　数据量庞大。（根据客户的需求，根据线上的环境）
*　该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
*　该字段经常出现在where子句中。（经常根据哪个字段查询）

注意：主键和具有unique约束的字段自动会添加索引。

　　根据主键查询效率较高。尽量根据主键检索

select ename,sal from emp where sal = 5000;

查看sql语句的执行计划：

    mysql> explain select ename, sal from emp where sal =5000;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.01 sec)

给薪资sal字段添加索引：
    create index emp_sal_index on emp(sal);

    mysql> explain select ename, sal from emp where sal =5000;
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

索引底层采用的数据结构是：B+Tree

索引的实现原理？

　通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
　最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename='SMITH';
通过索引转换为：
select ename from emp where 物理地址=0x3;

索引的分类？

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

索引什么时候失效？

　    select ename from emp where ename like '&A&';
模糊查询的时候，第一个通配符使用的是%，这个时候后索引是会失效的。

视图（view）

什么时视图?

**站在不同的角度去看待数据。（同一张表的数据，通过不同的角度去看待）。**

怎么创建视图？怎么删除视图？

create view myview as select empno,ename from emp;
drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来。

对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）

可以对视图进行CRUD

面向视图操作

mysql> select * from myview
-> ;
+——-+——–+
| empno | ename |
+——-+——–+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+——-+——–+
14 rows in set (0.01 sec)

create table emp_back as select * from emp;
create view myview1 as select  empno,ename,sal from emp_back;
update myview1 set ename = 'hehe', sal = 1 where empno = 7369 ;//通过视图原表数据
delete from myview1 where empno=7369;//通过视图删除原表数据

视图的作用？

　　视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD操作。

DBA命令

将数据库当中的数据导出

　　在windows的dos命令窗口中执行：（导出整个库）
mysqldump dongjun>d:\dongjun.sql -uroot -p333333

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

导入数据

create database dongjun;
use dongjun;
source d:\dongjun.sql

数据库设计三范式（重点内容，面试经常问）

什么时设计范式？

　　设计表的一句。按照这个三范式设计的标不会出现数据冗余

三范式都是那些？

第一范式：任何一张表有应该有主键，并且每一个字段原子性不可再分

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖
    多对多？三张表，关系表两个外键。
    t_student学生表
    sno(pk)            sname
    ------------------------------
    1            张三
    2            李四
    3            王五

    t_teacher 讲师表
    tno(pk)    tname
    -------------
    1    王老师
    2    张老师
    3    李老师

    t_student_teacher_relation 学生讲师关系表
    id(pk)        sno(fk)        tno(fk)
    -----------------------------------------
    1        1        3
    2        1        1
    3        2        2    2
    4        2        3
    5        3        1
    6        3        3

    第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
    一对多？两张表，多的表加外键。
    班级t_class
    cno(pk)        cname
    ------------------------
    1        班级1
    2        班级2

    学生t_student
    sno(pk)        sname        classno(fk)
    --------------------------------------------
    101        张1        1
    102        张2        1
    103        张3        2
    104        张4        2
    105        张5        2

    提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

一对一怎么设计？

一对一设计有两种方案：主键共享
    t_user_login 用户登录表
    id(pk+fk)        username    password
    -----------------------------------------------
    1        zs        123
    2        ls        456

    t_user_detail 用户详细信息表
    id(pk)        realname    tel
    -------------------------------------------
    1        张三        1111111
    2        李四        1111123

一对一设计有两种方案：外键唯一
    t_user_login 用户登录表
    id(pk)        username    password
    -----------------------------------------------
    1        zs        123
    2        ls        456

    t_user_detail 用户详细信息表
    id(pk)        realname    tel        userid(fk+unique)
    -------------------------------------------------------------------
    1        张三        1111111        1
    2        李四        1111123        2