By CaesarChang张旭                 

~关注我  带你看更多精品技术和面试必备

创建表设定主键:

create table tablename1(
id int auto_increment primary key ,
name char(32)
);

⼦查询(嵌套sql)


Alter :

1：删除列

ALTER TABLE 【表名字】 DROP 【列名称】

2：增加列

ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL 

3：修改列的类型信息

ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL  

4：重命名列

ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL

5：重命名表

ALTER TABLE 【表名字】 RENAME

6：删除表中主键

Alter TABLE 【表名字】 drop primary key

7：添加主键

ALTER TABLE 表明 ADD  PRIMARY KEY (resid,resfromid)

8：添加索引

ALTER TABLE 表明 add index

9: 添加唯一限制条件索引

ALTER TABLE 表名 charges add unique

10: 删除索引

alter table tablename drop index emp_name;

join:

INNER JOIN(join)

内连接INNER JOIN是最常用的连接操作。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。

LEFT JOIN

左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录, 赋值为NULL

OUTER JOIN

外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录

日期:

now()：当前具体的时间和日期

curdate()：当前日期

curtime()：当前时间

举例:

select  year( now( ) ) - sage

也可以select now( )

格式化日期:

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')   2018-08-30 09:51:45

获取明天、昨天

当前时间 是8月30日

SELECT date_sub(CURDATE(),interval -1 day)  2018-08-31

SELECT date_sub(CURDATE(),interval 1 day)   2018-08-29

获取上一个月 下一个月

SELECT date_sub(CURDATE(),interval 1 month)

SELECT date_sub(CURDATE(),interval -1 month)  2018-09-30

时间计算差值(来计算精确年龄)

TIMESTAMPDIFF(year,开始时间,'结束时间(当前));  

他可以返回一个 差值

MySQL事务:


MySQL 事务 概述

事务（Transaction）是由⼀系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执⾏逻辑单

元。


 


 


事务的语法

MySQL进阶知识(最全)(精美版)_sql

 

 


 


 


事务的特性


1. 原⼦性（Atomicity）

 

事务的原⼦性是指事务必须是⼀个原⼦的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只

允许出现两种状态之⼀。

全部执⾏成功

全部执⾏失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，

会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，就

像化学中学过的原⼦，是物质构成的基本单位。


 

2. ⼀致性（Consistency）

 

事务的⼀致性是指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之

后，数据库都必须处以⼀致性状态。

⽐如：如果从 A 账户转账到 B 账户，不可能因为 A 账户扣了钱，⽽ B 账户没有加钱。


3. 隔离性（Isolation）

 

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数

据时，每个事务都有各⾃完整的数据空间。

⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。

隔离性分 4 个级别，下⾯会介绍。

 

4. 持久性（Duration）

 

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服

务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。


 


 


 


 


事务并发问题


脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的

数据是脏数据。

 

不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同⼀数据，事务 B 在事务A

多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同⼀数据时，结果 不⼀致。

 

幻读：重复查询的过程中，数据就发⽣了量的变化（insert， delete）。

 


 

事务隔离级别

MySQL进阶知识(最全)(精美版)_mysql_02

 

 


4 种事务隔离级别从上往下，级别越⾼，并发性越差，安全性就越来越⾼。 ⼀般数据默认级别是

读以提交或可重复读。

 

不同隔离级别的锁的情况(了解)


隐式提交(了解)


DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)

DDL都是隐式提交


 

MySQL中的特性-扩展

MySQL存储过程

什么是存储过程 ?

 

存储过程简单来说，就是为以后的使⽤⽽保存 的⼀条或多条 MySQL 语句的集合。

储存过程是⼀组为了完成特定功能的 SQL 语句集，经过编译之后存储在数据库中，在需要时直接调

⽤。

存储过程就像脚本语⾔中函数定义⼀样。

为什么要使⽤存储过程 ?

优点 :

可以把⼀些复杂的 sql 进⾏封装 , 简化复杂操作

保证了数据的完整性 , 防⽌错误

简单的变动只需要更改存储过程的代码即可

提⾼性能。因为使⽤存储过程⽐使⽤单独的 SQL 语句要快。 ( 预先编译 )

缺点 :

存储过程的编写⽐ SQL 语句复杂

⼀般可能还没有创建存储过程的权限 , 只能调⽤

个⼈观点 : 业务逻辑不要封装在数据库⾥⾯ , 应该由应⽤程序 (JAVA 、 Python 、 PHP) 处理。

让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。

维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动 A 影响 B 。

⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少。使⽤困难。

在电信、银⾏业、⾦融⽅⾯以及国企都普遍使⽤存储过程来熟悉业务逻辑，但在互联⽹中相对较少。

创建存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。

create procedure 创建语句

BEGIN 和 END 语句⽤来限定存储过程体

-- 定义存储过程

\d //

create procedure p1()

begin

set @i= 10 ;

while @i< 90 do

insert into users values ( null ,concat( 'user:' ,@i),@i, 0 );

set @i=@i+ 1 ;

end while;

end;

//

执⾏储存

call p1()

查看存储过程

show create procedure p1\G

删除存储过程

drop procedure p1

MySQL的触发器

如果你想要某条语句(或某些语句)在事件发⽣时⾃动执⾏，怎么办呢?

 

触发器的定义

触发器是 MySQL 响应写操作 ( 增、删、改 ) ⽽⾃动执⾏的⼀条或⼀组定义在 BEGIN 和 END 之间的

MySQL 语句

 

或可理解为：提前定义好⼀个或⼀组操作 , 在指定的 SQL 操作前或后来触发指定的 SQL ⾃动执⾏

触发器就像是 JavaScript 中的事件⼀样

举例 : 定义⼀个 update 语句 , 在向某个表中执⾏ insert 添加语句时来触发执⾏ , 就可以使⽤触发器

触发器语法 ：

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

说明：

# trigger_name ：触发器名称

# trigger_time: 触发时间，可取值： BEFORE 或 AFTER

# trigger_event ：触发事件，可取值： INSERT 、 UPDATE 或 DELETE 。

# tb1_name ：指定在哪个表上

# trigger_stmt ：触发处理 SQL 语句。

-- 查看所有的 触发器

show triggers\G;

-- 删除触发器

drop trigger trigger_name;

触发器 Demo

注意 : 如果触发器中 SQL 有语法错误 , 那么整个操作都会报错

-- 创建⼀个删除的触发器 , 在 users 表中删除数据之前 , 往 del_users 表中添加⼀个数据

-- 1, 复制当前的⼀个表结构

create table del_users like users;

-- 2, 创建 删除触发器 注意在创建删除触发器时 , 只能在删除之前才能获取到 old( 之前的 ) 数据

\d //

create trigger deluser before delete on users for each row

begin   insert into del_users values (old .id ,old .name ,old .age ,old .account );

end;

//

\d ;

-- 3 删除 users 表中的数据去实验

tips ：

在 INSERT 触发器代码内，可引⽤⼀个名为 NEW 的虚拟表，访问被 插⼊的⾏ ;

在 DELETE 触发器代码内，可以引⽤⼀个名为 OLD 的虚拟表，访问被删除的⾏ ;

OLD 中的值全都是只读的，不能更新。

在 AFTER DELETE 的触发器中⽆法获取 OLD 虚拟表

在 UPDATE 触发器代码中

可以引⽤⼀个名为OLD 的 虚拟表 访问更新以前的值

可以引⽤⼀个名为NEW 的虚拟表 访问新 更新的值;

 

MySQL中的视图

什么是视图？

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。

视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

 

视图的作⽤

1. 重⽤ SQL 语句。

2. 简化复杂的 SQL 操作。在编写查询后，可以⽅便地重⽤它⽽不必知道它的基本查询细节。

3. 使⽤表的组成部分⽽不是整个表。

4. 保护数据。可以给⽤户授予表的特定部分的访问权限⽽不是整个表的访问权限。

5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

6. 注意 : 视图不能索引，也不能有关联的触发器或默认值。

视图的基础语法

创建视图:

create view v_users as select id,name,age from users where age >= 25 and age

<= 35;

-- Query OK, 0 rows affected (0.00 sec)

 

ALTER VIEW

CREATE VIEW

DROP VIEW

查看当前库中所有的视图

show tables; -- 可以查看到所有的表和视图

show table status where comment= 'view' ; -- 只查看当前库中的所有视图

删除视图 v_t1:

mysql> drop view v_t1;

MySQL索引原理以及优化

什么是索引?

索引是存储引擎用于快速找到记录的一种数据结构.

索引优点:

  • 减少查询需要扫描的数据量(加快了查询速度)
  • 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
  • 将服务器的随机IO变为顺序IO(加快查询速度).

缺点:

  • 索引占用磁盘或者内存空间
  • 减慢了插入更新操作的速度

MySQL主要有以下几种索引:

  • B-树索引/B+树索引
  • 哈希索引
  • 空间数据索引
  • 全文索引

 

B+树

B+树是B-树的进阶版本,在B-树的基础上又做了如下的限制:

  1. 每个中间节点不保存数据,只用来索引,也就意味着所有非叶子节点的值都被保存了一份在叶子节点中.
  2. 叶子节点之间根据自身的顺序进行了链接.

这样可以带来什么好处呢?

  1. 中间节点不保存数据,那么就可以保存更多的索引,减少数据库磁盘IO的次数.
  2. 因为中间节点不保存数据,所以每一次的查找都会命中到叶子节点,而叶子节点是处在同一层的,因此查询的性能更加的稳定.
  3. 所有的叶子节点按顺序链接成了链表,因此可以方便的话进行范围查询.


MySQL进阶知识(最全)(精美版)_触发器_03

⽽MyISAM的索引⽅案虽然也使⽤树形结构，但 是却将索引和数据分开存储：

MySQL进阶知识(最全)(精美版)_java_04

所以在MyISAM中所有的索引都是⾮聚簇索引，也叫⼆级索引

MySQL进阶知识(最全)(精美版)_sql_05