MySQL三天入门教程笔记第三天

8.9 insert 插入多条记录

insert into 表名(字段名1, ...) values((...),(...), ...);

8.10 快速创建表

create table tablename as select * from ... ;

原理：将查询结果当做一个新的表来创建，这个可以快速完成表的复制。

create table tablename as select empno,ename from emp where job='MANAGE';

8.11 将查询结果插入一张表

insert into tablename select * from ...;//很少用

8.12 快速删除表中数据

delete删除方式比较慢，表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放，支持回滚。

（DML操作）

truncate语句删除效率高，表被一次截断，物理删除，但是不支持回滚。(DDL操作)

truncate table tablename;

truncate是删除数据，表还在，drop是把表删除了。

8.13 对表结构的增删改

用GUI界面改就好，如果用命令行来改，可以使用alter语句

8.14 约束

约束（constraint），在创建表的时候我们可以给表中的字段加上一些约束，来保证表中数据的完整性，有效性

约束包括：非空约束（not null）唯一性约束（unique）主键约束（primary key ：PK）外键约束（foreign key：FK）检查约束（check MySQL不支持，oracle支持）

8.14.1 非空约束：not null

非空约束字段不能为NULL。

create table tablename（

id int not null，//约束直接添加到列后面的，叫做列级约束。

….);

小插曲：xxxx.sql文件被称为MySQL脚本文件。sql文件中编写了大量的sql语句，我们执行sql文件的时候，该文件中所有的sql语句都会执行。批量执行SQL语句可以选用sql脚本文件的方式完成。

执行sql文件的方法：source sql文件的绝对路径

8.14.2 唯一性约束：unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

8.14.3 联合唯一

语法

create table tablename(
	id int,
	name varchar,
	email varchar,
	unique(name,email)//将name和email两个字段联合唯一 
    // 约束没有添加在列的后面，叫做表级约束
)

not null只有列级约束，没有表级约束。

8.14.4 not null和unique联合

在MySQL中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意oracle中不一样）

8.14.5 主键约束（PK）

主键值是每一行记录的唯一标识。

任何表都应该有主键，没有主键，表无效！

create table tablename(
	id int primary key, //列级约束 单一主键
	name varchar(255)
);

create table tablename(
	id int,
	name varchar(255),
	primary key(id)// 表级约束
);

复合主键（在实际开发中不建议使用）

create table tablename(
	id int,
	name varchar(255),
	email varchar(255),
	primary key(id,name) //id和name联合做主键
);

一张表主键约束只能添加一个，两个就报错。

主键值建议使用int, bigint, char类型，一般都是数字，一般定长

主键还可以分为自然主键和业务主键

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候,可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

在mysql中有一种机制可以帮助我们自动维护一个主键值：

create table tablename(
	id int primary key auto_increment,//以1开始自增
	name varchar(255)
);

8.14.6 外键约束（非常重要）

对对应到另一张表上的字段值进行约束，否则填入的值可能无意义。被引用的表被称为父表，引用的表被称为子表

删除表的顺序：先删子表，再删父表

创建表的顺序：先创建父表，再创建子表

删除数据的顺序：先删除子表，再删除父表

插入数据的顺序：先插入父表，再插入子表

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

create table t_class(
	classno int primary key,
	classname varchar(255)
);

create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key (cno) references t_class(classno)//创立外键
);

子表中外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束

9 存储引擎

存储引擎是MySQL特有的，oracle有但不叫这个名字。

存储引擎是一个表存储/组织数据的方式。

可以在建表的时候指定存储引擎

show create tablename; 查看建表时的sql语句以及使用的引擎等信息

在建表的时候可以在最后小括号的“）”的右边使用：

ENGINE来指定存储引擎，

CHARSET来指定字符编码方式。

MySQL的默认存储引擎是InnoDB，默认字符编码方式是UTF-8

查看MySQL支持的存储引擎 show engines \G; 一共有9个

常用引擎：

MyISAM存储引擎

它管理的表具有以下特征:

使用三个文件表示每个表:

格式文件一存储表结构的定义(mytable.frm)
数据文件一存储表行的内容(mytable.MYD)
索引文件_存储表上索引(mytable.MYI)，

可被转换为压缩、只读表来节省空间

提示一下:

对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点:

可被转换为压缩、只读表来节省空间,这是这种存储引擎的优势

MyISAM不支持事务机制，安全性低.

InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一-个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制.
InnoDB存储引擎最主要的特点是:非常安全.

它管理的表具有下列主要特征：
每个InnoDB 表在数据库目录中以. frm格式文件表示;
InnoDB表空间tablespace 被用于存储表的内容;
提供一组用来记录事务性活动的日志文件;
用COMMIT (提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理;
提供全ACID兼容;
在MySQL服务器崩溃后提供自动恢复;
多版本(MVCC) 和行级锁定;
支持外键及引用的完整性，包括级联删除和更新.

InnoDB最大的特点就是支持事务保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读,
不能很好的节省存储空间.
MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY 存储引擎非常快。

MEMORY存储引擎管理的表具有下列特征:

在数据库目录内，每个表均以.frm格式的文件表示；

表数据及索引被存储在内存中；

表级锁机制。

不能包含TEXT或BLOB字段。

MEMORY存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点:查询效率是最高的。

MEMORY引擎缺点:不安全，关机之后数据消失。因为数据和索引都是在内存当中。

10 事务(重点)

10.1 事务的定义

一个事务其实就是一个完整的业务逻辑。

是一个最小的工作单元。不可再分。

什么是一个完整的业务逻辑?

假设转账，从A账户向B账户中转账10000.

将A账户的钱减去10000(update语句)

将B账户的钱加上10000(update语句)

这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

只有DM语句才会有事务这一说，其它语句和事务无关!! !
insert delete update
只有以上的三个语句和事务有关系，其它都没有关系。
因为只有以上的三个语句是数据库表中数据进行增、删、改的.
只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题。数据安全第一位!!!

假设所有的业务，只要一条DML语句就能完成，还有必要存在事务机制吗?

正是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，

所以需要事务的存在。如果任何一件复杂的事儿都能一条DML语句搞定，那么事务则没有存在的价值了。

到底什么是事务呢?

一个事务其实就是多条DM语句同时成功，或者同时失败!

10.2 实现

InnoDB存储引擎:提供一组用来记录事务性活动的日志文件

在事务的执行过程中，每一条DM的操作都会记录到"事务性活动的日志文件"中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务：
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着,事务的结束。并且是一种全部成功的结束。
回滚事务：
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件回滚事务标志着，事务的结束。并且是一种全都失败的结束。

10.3 提交与回滚事务

提交事务:commit;语句
回滚事务:rollback;语句（回滚永远都是只能回滚到上一次的提交点!)

事务对应的英语单词是: transaction

mysql默认情况下是支持自动提交事务的。（自动提交)

什么是自动提交?
每执行一条DML语句，则提交一次!

这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。

怎么将MySQL的自动提交机制关闭掉呢?
执行这个命令:start transaction ;

之后执行多条DML语句之后就可以进行rollback或commit操作，但注意的是只能回滚到上一个提交点

10.4 特性

事务包括4个特性

A:原子性
说明事务是最小的工作单元。不可再分。

C:一致性
所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

I:隔离性
A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙，这道墙就是隔离性。
A事务在操作一张表的时候，另一个事务B也操作这张表会那样。

D:持久性
事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

10.5 事务隔离级别

读未提交:read uncommitted(最低的隔离级别)
什么是读未提交?
事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题就是:脏读现象!(Dirty Read)
我们称读到了脏薮据。
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步。
读已提交: read comnitted
什么是读已提交？
事务A只能读取到事务B提交之后的数据。

这种隔离级别解决了脏读的现象。

这种隔离级别存在什么问题?
不可重复读取数据。
什么是不可重复读取数据呢?
在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4，称为不可重复读取。
这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。

oracle数据库默认的隔离级别是:read committed
可重复读: repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》
什么是可重复读取?
事务A开启之后，不管是多久（事务A没结束），每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。

可重复读解决了什么问题?
解决了不可重复读取数据。

可重复读存在的问题是什么?
可以会出现幻影读。每一次读取到的数据都是幻象。不够真实!

早晨9点开始开启了事务，只要事务不结束，到晚上9点读到的数据还是那祥!
读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个。
序列化/串行化: serializable(最高的隔离级别)
这是最高隔离级别，效率最低。解决了所有的问题。这种隔离级别表示事务排队，不能并发!
synchronized，线程同步(事务同步)
每一次读取到的数据都是最真实的，并且效率是最低的。

查看隔离级别：select @@tx_isolation

更改隔离级别：set global transaction isolation level … ;

11 索引

11.1 索引概述

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

在mysql数据库当中索引也是需要排序的，并且这个所以的排序和Treeset数据结构相同。
Treeset (TreeMap）底层是一个自平衡的二叉树。
在MySQL当中索引是一个B-Tree数据结构。
遵循左小又大原则存放。采用中序遍历方式遍历取数据。

11.2 索引实现原理

提醒1:在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。

提醒2:在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

提醒3:在MySQL当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在。(自平衡二叉树:B-Tree)

什么条件下，我们会考虑给字段添加索引呢?
条件1:数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同)
条件2:该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描
条件3:该字段很少的DML(insert delete update)操作。(因为DML之后，索引需要重新排序)
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

11.3 索引的创建和删除

create index index_name on table_name(column_name);

drop index index_name index on table_name;

查看sql语句是否使用索引进行检索：explain select * from table_name where condition...;查看rows数

11.4 索引失效

失效的第1种情况:

select * from emp where ename like '%T';

ename 上即使添加了索引，也不会走索引，为什么?
原因是因为模糊匹配当中以"%"开头了!
尽量避免模糊查询的时候以"%"开始。
这是一种优化的手段/策略。

失效的第2种情况:
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

使用union不会这样。

失效的第3种情况:
使用复合索引的时候,没有使用左侧的列查找，索引失效

两个字段,或者更多的字段联合起来添加一个索引，叫做复合索引。

create name_job_index on emp(name,job);
select job from emp where job='police';

失效的第4种情况:

在where当中索引列参加了运算，索引失败

select name sal from emp where sal+1=1001;

失效的第5种情况:

在where中索引使用了函数

select * from emp where lower(name)='smith';

11.5 索引的分类

单一索引:一个字段上添加索引。

复合索引:两个字段或者更多的字段上添加索引。

主键索引:主键上添加索引。

唯一性索引:具有unique约束的字段上添加索引。

注意:唯一性比较弱的字段上添加索引用处不大

12 视图

view：站在不同的角度去看同一份数据。

创建视图对象：create view view_name as select * from table_name;

删除视图对象：drop view view_name

注意：只有DQL语句才能以view的形式创建。

create view view_name as DQL语句

12.1 视图的作用

我们可以面向视图对象进行增删改查（CRUD），

对视图对象的增删改查，会导致原表被操作!（视图的特点:通过对视图的操作，会影响到原表数据。)

C： create
R： retrieve
U： update
D： delete

视图对象在实际开发中到底有什么用? 方便、简化开发、利于维护

create view
	emp_dept_view
as
    select
    	e.ename ,e.sal ,d.dname
    from
    	emp e 
    join
    	dept d
    on
    	e .deptno = d.deptno ;

假设有一条非常复杂的sql语句，而这条sql语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办?
可以把这条复杂的sql语句以视图对象的形式新建。
在需要编写这条sql语句的位置直接使用视图对象，可以大大简化开发。
并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的sql语句。

再提醒一下:
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。

我们以后面向视图开发的时候，使用规图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

13 DBA命令

重点：数据的导入与导出

数据导出：
注意:在windows的dos命令窗口中:

mysqldump bjpowernode>D: lbjpowernode.sql -uroot -p123456

可以导出指定的表：

mysqldump bjpowernode emp>D: \bjpowernode.sql -uroot -p123456

数据导入：
注意：
需要先登录到mysql数据库服务器上。
然后创建数据库: create database bjpowernode ;
使用数据库:use bjpowernode
然后初始化数据库:source D: \bjpowernode.sql

14 数据库设计三范式

数据库设计范式共有3个.
第一范式:要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖
第三范式:建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖
声明:三范式是面试官经常问的,所以一定要熟记在心!

设计数据库的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

多对多设计思路：三张表，关系表两个外键。

一对多设计思路：两张表，多的表加外键。

一对一拆大表设计思路：一对一，外键唯一。

posted @ 2021-08-07 22:09 ahrismile 阅读(73) 评论(0) 收藏举报

刷新页面返回顶部

ahrismile