Mysql基础概念

一.数据库设计三范式

  1.第一范式

   概念: 原子性, 做到列不可拆分 第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个 字段都是不可再分的最小数据单元，则满足第一范式。

  2. 第二范式

  在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。 一张表只能描述一件事。例如:不要把学员信息和成绩信息建立在一个表，会导致数据冗余，如果删除学员信息，成绩的信息也被删除了。

  3.第三范式

   消除传递依赖表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放。

例如:商品的价格，和商品的数量可以得到总金额，所以总金额这个字段可以省略,节省数据的空间(三范式 空间最省)。 

二.数据库反三范式

  概念: 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能浪费存储空间,节省查询时间 (以空间换时间)。

  总结: 创建一个关系型数据库设计，我们有两种选择。

  1，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅。

  2，合理的加入冗余字段这个润滑剂，减少join(表连接)，让数据库执行性能更高更快。

三.sql通用语法

 1） SQL语句可以单行 或者 多行书写，以分号 结尾 ; （Sqlyog中可以不用写分号）

 2） 可以使用空格和缩进来增加语句的可读性。

 3） MySql中使用SQL不区分大小写，一般关键字大写，数据库名 表名列名 小写。

 4） 注释方式  - - 单行注释  /**/多行注释 #MySql特有的单行注释。

四.sql约束  

约束名	约束关键字	作用特点
主键	primary key	不可重复,唯一非空。用于表示数据库的每一条记录
唯一	unique	表中的某一列的值不能重复(对null不做唯一的判断)
非空	not null	某一列不允许为空
外键	foreign key	使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

1.主键约束与唯一约束的区别:

 1.1.主键约束 唯一且不能为空。

 1.2.唯一约束，唯一但是可以为空。

 1.3.一个表中只能有一个主键，但是可以有多个唯一约束.

2.什么是外键

  外键指的是在从表中与主表的主键对应的那个字段,

3.创建外键约束

  3.1.新建表时添加外键后附如下格式的代码

  [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

  3.2已有表添加外键

  ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名)      REFERENCES 主表(主 键字段名);

  3.3删除外键约束

  删除employee 表中的外键约束, ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;(外键约束名 emp_dept_fk)

  3.4外键约束的注意事项

  从表外键类型必须与主表主键类型一致 否则创建失败。  

五.事务基础

  1.什么是事务？

  事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有 一条SQL出现异常,整个操作就会回滚,整个业务执行失败。

  2.事务分类

  MYSQL 中可以有两种方式进行事务的操作： 手动提交事务 自动提交事务。

  手动提交事务语法格式:

功能	语句	描述
开启事务	start transaction; 或者 BEGIN;	这个语句显式地标记一个事务的起始点。
提交事务	commit;	表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写 到磁盘上的物理数据库中，事务正常结束。
回滚事务	rollback;	表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执

  3.自动提交事务

     MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句 执行完毕 自动提交事务，MySQL 默认开始自动提交事务 MySQL默认是自动提交事务。

 SHOW VARIABLES LIKE 'autocommit';  查看事务状态

on ：自动提交

off : 手动提交

如果把 autocommit 改成 off; SET @@autocommit=off;就不能自动提交事务，需要手动提交事务。

4.事务的四大特性

特性	含义
原子性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都 失败。
一致性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额 是 2000，转账后 2 个人总金额也是 2000.
隔离性	事务与事务之间不应该相互影响，执行时保持隔离的状态
持久性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的.

5.mysql事务隔离级别

  5.1数据并发方位

   一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能 被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性。

  5.2并发访问会产生的问题

   事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问 同一个 数据。可能引发并发访问的问题。

并发访问的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一 致的. 这是进行 update 操作时引发的问题
幻读	一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务 操作. 查询得到的数据状态不准确,导致幻读.

5.3四种隔离级别

   通过设置隔离级别，可以防止上面的三种并发问题。

   mysql数据库有四种隔离级别如下:

   (上面级别最低,下面级别最高，✔ 会出现问题，✘ 不会出现问题)

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库的默认隔离级 别
1	读未提 交	read uncommitted	✔	✔	✔
2	读已提 交	read committed	✘	✔	✔	Oracle和SQLServer
3	可重复 读	repeatable read	✘	✘	✔	MySql
4	串行化	serializable	✘	✘	✘

5.4隔离级别相关命令

  5.4.1查看隔离级别

   select @@tx_isolation;

  5.4.2) 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

   set global transaction isolation level 级别名称; read uncommitted 读未提交 read committed 读已提交 repeatable read 可重复读 serializable 串行化。

例如: 修改隔离级别为读未提交

set global transaction isolation level read uncommitted;

六.索引

 1.什么是索引?

  在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和 运行更加高效。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目 录（索引）快速查找到需要的字。

 2.常见索引分类

索引名称	说明
主键索引 (primary key)	主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一 条记录。一个表中可以没有主键，但最多只能有一个主键，并且主键不能为null。
唯一索引 (unique)	唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一..。唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了 提高访问速度，而只是为了避免数据出现重复。
普通索引 (index)	最常见的索引,作用就是加快对数据的访问。唯一任务是加快对数据的访问速度。因此，应该只 为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建 索引。速度

3.索引的优缺点

  添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。

索引的优点

 . 大大的提高查询速度; 可以显著的减少查询中分组和排序的时间。

 索引的缺点

 创建索引和维护索引需要时间，而且数据量越大时间越长;当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度。

4.索引创建的基本语法

  4.1创建表的时候直接添加主键索引

CREATE TABLE 表名( - -添加主键 (主键是唯一性索引,不能为null,不能重复,)   字段名 类型 PRIMARY KEY, );

  修改表结构,添加唯一索引

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

  4.2创建表的时候直接添加唯一键索引

CREATE TABLE 表名( 列名 类型(长度), -- 添加唯一索引 UNIQUE [索引名称] (列名)  );

使用create语句创建:在已有的表上创建索引

create unique index 索引名 on 表名(列名[长度])

修改表结构添加索引

ALTER TABLE 表名 ADD UNIQUE ( 列名 )

  4.3普通索引

   使用create index语句创建:在已有的表上创建索引

create index 索引名 on 表名(列名[长度])

   修改表结构添加索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名)

   4.4删除索引

     语法格式:

ALTER TABLE demo01 DROP INDEX dname_indx;

七.视图

  1.什么是视图?

   1.1视图是一种虚拟表。

   1.2视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

   1.3 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句。

   1. 4视图向用户提供基表数据的另一种表现形式。

   2.视图作用

    视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图 就可以获取想要得到的信息(不需要再编写复杂的SQL)，视图主要就是为了简化多表的查询。

    2.1语法格式:  

create view 视图名 [column_list] as select语句;  view: 表示视图 column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询 的属性相同 as : 表示视图要执行的操作 select语句: 向视图提供数据内容

 例子:

#1. 先编写查询语句 #查询所有商品 和 商品的对应分类信息 SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`; #2.基于上面的查询语句,创建一张视图 CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

   3.视图与表的区别

   3.1视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示 。

   3.2通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）。

   3.3删除视图，表不受影响，而删除表，视图不再起作用

八.存储过程

   1.什么是存储过程?

   MySQL 5.0 版本开始支持存储过程。

    存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据 库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过 指定存储过程的名字并给定参数(需要时)来调用执行。

   通俗的讲其实就是一堆sql语句的合并，加了一些逻辑控制。

   2.存储过程的优缺点

    优点:

   存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）    

   存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，    

   与 数据库服务器不在同一个地区）

    缺点:

    在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较

少使用，并且互联网行业需求变化较快也是原因之一 尽量在简单的逻辑中使用，存储过  程移植十分困难，数据库集群环境，保证各个库之间存储 过程变更一致也十分困难。

  3.存储过程的创建方式

     3.1方式一

        语法:    

DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$ CREATE PROCEDURE 过程名称() -- 声明存储过程 BEGIN -- 开始编写存储过程 -- 要执行的操作 END $$ -- 存储过程结束

      例子需求:

DELIMITER $$ CREATE PROCEDURE goods_proc() BEGIN select * from goods; END $$

  调用存储过程

call 存储过程名 -- 调用存储过程 查询goods表所有数据  call goods_proc;

3.2方式二

   3.2.1IN 输入参数：表示调用者向存储过程传入值

语法格式：

CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)

例子: 需求: 接收一个商品id, 根据id删除数据

DELIMITER $$ CREATE PROCEDURE goods_proc02(IN goods_id INT) BEGIN  DELETE FROM goods WHERE gid = goods_id ; END $$

调用方式:

# 删除id2的商品 CALL goods_proc02(2);

3.3方式3

先给变量赋值  SET @变量名=值，然后OUT输出参数: 表示存储过程向调用者传出值

OUT 变量名 数据类型

代码例子:

# 创建存储过程 接收参数插入数据, 并返回受影响的行数  DELIMITER $$ CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) BEGIN -- 执行插入操作 INSERT INTO orders VALUES(o_oid,o_gid,o_price); -- 设置 num的值为 1 SET @out_num = 1; -- 返回 out_num的值  SELECT @out_num; END $$

调用存储过程:

# 调用存储过程插入数据,获取返回值  CALL orders_proc(1,2,30,@out_num);

九.触发器 

概念: 当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句。

 1.触发器创建的四个要素

  1. 1. 监视地点（table） 1.2. 监视事件（insert/update/delete） 1.3. 触发时间（before/after） 4. 触发事件（insert/update/delete）

  2.创建触发器

delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误  CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的 before/after（insert/update/delete） -- 触发的时机 和 监视的事件 on table_Name -- 触发器所在的表  for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行  begin -- begin和end之间写触发事件 end $ -- 结束标记

十.cmd命令还原和备份mysql

1.备份
一.输入mysqldump -uroot -p test_db>test_db.sql命令，其中，test_db是数据库名称，test_db.sql是备份后的文件名
二.输入密码即可

2.还原（source  还原到指定的目录） 
source E:\test_db.sql