mysql increament_MySQL加强

1、分组函数

1、count 计数

2、sum求和

3、avg平均值

4、max最大值

5、min最小值

注意：

所有的分组都是对“某一组”数据进行操作的

特点：输入多行，输出一行

自动忽略NULL

不可以直接使用在where字句中

count()和count(具体某个字段)，他们的区别

1、count()，是统计总记录条数，不是统计某个字段中数据的个数

2、count(具体某个字段)，统计某个字段中不为NULL的数据总数量

2、单行处理函数：输入一行，输出一行

所有数据库都是这样规定的，只要有null参与的运算结果一定是NULL；

ifnull()，空处理函数：ifnull(可能为NULL的数据，被当做什么处理)：属于单行处理函数

3、group by 和 having

1、group by ：按照某个字段或某些字段进行分组

2、having：对分组之后的数据进行再次过滤(where)

3.1 group by

注意：

分组函数一般都会和group by联用，任何一个分组函数都是在group by执行之后执行；

如果一条SQL

语句没有group by的话，整张表会自成一组。

3、分组函数不能用在where字句中的原因是：分组函数的执行在group by之后，而group by在where之后执行，大概的执行顺序是：where --> group by --> 分组函数(sum,max,count,avg,min)

4、当一条语句中有group by时，select后面只能跟分组函数和参与分组的字段

5、多字段分组查询

例如：找出每个部门不同工作岗位的最高薪资

mysql> select deptno,job,max(sal) from emp group by deptno,job;

3.2 having

能用where过滤的尽量用where

必须配合group by一起使用

4、完整的DQL语句及执行顺序

select … 5

from … 1

where … 2

group by … 3

having … 4

order by … 6

5、查询结果集的去重

关键字 distinct 在去重字段前面

6、连接查询

6.1 连接查询的分类

1、内连接

等值连接

条件是等量关系

select e.ename,d.dname

from emp e inner(可以省略) join dept d

on e.deptno = d.deptno

where ....

非等值连接

条件不是等量关系

自连接(同一个表)

查找员工对应的领导

select e.empno,e.ename,e.mgr,ea.ename as 领导名

from

emp e join emp ea

on

e.mgr = ea.empno;

2、外连接

左连接：

left outer(可以省略) join

如果有匹配的就输出出来，没有匹配的，以左表为标准，左表的数据都会输出

右连接

right outer(可以省略) join

3、全连接(很少用)

6.2 多表连接查询

7、子查询

select … (select)

from … (select)

where … (select)

7.1 where子句中使用子查询

案例：找出高于平均薪资的员工信息

1、查出平均薪资

select avg(sal) from emp;

2、查出高于平均薪资的员工信息

select * from emp where sal > (select avg(sal) from emp);

7.2 from后面嵌套子查询

案例：找出每个部门平均薪水的薪资等级

1、找出每个部门平均薪水

2、根据薪水等级表找出每个部门的平均薪资等级

select e.*,s.grade

from (select deptno,avg(sal) as avgsal from emp group by deptno) as e

join salgrade s

on e.avgsal between s.losal and s.hisal;

案例：找出每个部门平均的薪水等级

1、找出每个员工的薪水等级

select e.ename,e.sal,e.deptno,g.grade

from emp e

join salgrade g

on e.sal between g.losal and g.hisal;

select e.deptno,avg(g.grade) avggrade

from emp e

join salgrade g

on e.sal between g.losal and g.hisal

group by e.deptno;

7.3 select后面嵌套子查询

8、union(可以将查询结果集相加)

9、limit(重点:分页查询)

mysql特有，Oracle有个相同的机制：rownum

作用：取结果集中的部分数据

SQL语句中最后执行

语法：

limit startIndex(起始位置,0开始),length(取的个数)

案例：取出工资前5名的员工

每页显示pageSize条记录，第pageNo页

1页，0，3条

2页，3，3条

3页，6，3条

公式： (pageNo-1)*pageSize,pageSize

10、数据类型

char 与 varchar如何选择

1、在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，都是采用char

2、当一个字段的数据长度不确定的时候，采varchar

blob和clob的使用

表名在数据库中建议以：t_或者tbl_开始

11 、约束

a) 非空约束，not null，非空约束，针对某个字段设置其值不为空

b) 唯一约束，unique ，唯一性约束，它可以使某个字段的值不能重复

c) 主键约束，primary key，不能重复，不能为空

d) 外键约束，foreign key，

e) 自定义检查约束，check(不建议使用)(在mysql中现在还不支持)

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合(联合)主键，单一主键是由一个字段构成的，复合(联合)主键是由多个字段构成的

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。

12、存储引擎

查看当前服务器中有哪些存储引擎可用，可使用SHOW ENGINES语句：

mysql> SHOW ENGINES\G

在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。

CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;

如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎

默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。

现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;

为确定某表所使用的存储引擎，可以使用SHOW CREATE TABLE或SHOW TABLE STATUS语句：

mysql> SHOW CREATE TABLE emp\G

mysql> SHOW TABLE STATUS LIKE ‘emp’ \G

12.1 常用的存储引擎

MyISAM存储引擎

MyISAM存储引擎是MySQL最常用的引擎。

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

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

格式文件 — 存储表结构的定义(mytable.frm)

数据文件 — 存储表行的内容(mytable.MYD)

索引文件 — 存储表上索引(mytable.MYI)

灵活的AUTO_INCREMENT字段处理

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

InnoDB存储引擎

InnoDB存储引擎是MySQL的缺省引擎。

它管理的表具有下列主要特征：

每个InnoDB表在数据库目录中以.frm格式文件表示

InnoDB表空间tablespace被用于存储表的内容

提供一组用来记录事务性活动的日志文件

用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理

提供全ACID兼容

在MySQL服务器崩溃后提供自动恢复

多版本(MVCC)和行级锁定

支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

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

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

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

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

表级锁机制。

不能包含TEXT或BLOB字段。

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

12.2 选择合适的存储引擎

MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。

如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。

可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

13、事务

1、 什么是事务：一个事务是一个完整的业务单元，不可再分。多条SQL处理一业务时，要么都执行成功，一条出错，整个操作就不能成功，需要回滚该事务。

2、执行了增删改之后，只是记录了历史操作，只有提交事务，才真正修改数据

3、和事务相关的语句只有：DML语句。insert delete update。因为这三个语句都是与“数据”相关的，事务的存在是为了保证数据的完整性，安全性。

4、通常一个业务需要多条DML语句完成。

13.1 事务的自动提交模式

自动提交模式用于决定新事务如何及何时启动。

启用自动提交模式：

如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。

如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。

如果语句执行失败，事务将自动回滚，并取消该语句的结果。

在自动提交模式下，仍可使用start transaction语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。

禁用自动提交模式：

如果禁用自动提交，事务可以跨越多条语句。

在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。

自动提交模式可以通过服务器变量AUTOCOMMIT来控制。

例如：

mysql> SET AUTOCOMMIT = OFF；

mysql> SET AUTOCOMMIT = ON；

或

mysql> SET SESSION AUTOCOMMIT = OFF；

mysql> SET SESSION AUTOCOMMIT = ON；

show variables like ‘%auto%’; – 查看变量状态

13.2 事务的特性

四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分

C：一致性：事务必须保证多条DML语句，都执行失败或成功

I： 隔离性：事务与事务之间，互相隔离，互不影响

D：持久性：事务一旦提交，数据将永久保存在硬盘。

13.3 事务的隔离级别

隔离级别

事务的隔离级别决定了事务之间可见的级别。

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读取(Dirty Read)

一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。

不可重复读(Non-repeatable Read)

在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。

– 幻像读(Phantom Read)

幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

四个隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

读未提交(READ UMCOMMITTED)

允许一个事务可以看到其他事务未提交的修改。存在脏读现象

读已提交(READ COMMITTED)

允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。解决了脏读，但不可重复读

可重复读(REPEATABLE READ)

确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。 (银行总账)。解决了不可重复读问题，但读取到的数据是幻象。

该隔离级别为InnoDB的缺省设置。

串行化(SERIALIZABLE) 【序列化】

将一个事务与其他事务完全地隔离。 解决了所有问题，但效率低，需要事务排队

Oracle默认是：读已提交

MySQL默认是：可重复读

隔离级别与一致性问题的关系

设置服务器缺省隔离级别

通过修改配置文件设置

可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

该选项值可以是：

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

例如：

[mysqld]

transaction-isolation = READ-COMMITTED

通过命令动态设置隔离级别

隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。

其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

其中的可以是：

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别的作用范围

事务隔离级别的作用范围分为两种：

全局级：对所有的会话有效

会话级：只对当前的会话有效

例如，设置会话级隔离级别为READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；

或：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

设置全局级隔离级别为READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMM ITTED；

查看隔离级别

服务器变量tx_isolation(包括会话级和全局级两个变量)中保存着当前的会话隔离级别。

为了查看当前隔离级别，可访问tx_isolation变量：

查看会话级的当前隔离级别：

mysql> SELECT @@tx_isolation;

或：

mysql> SELECT @@session.tx_isolation;

查看全局级的当前隔离级别：

mysql> SELECT @@global.tx_isolation;

14、索引

什么是索引

索引相当于书的目录，可以通过目录快速的找到对应资源

在数据库方面，查询一张表有两种检索方式：

全表扫描

根据索引检索(效率高)

应用场景

数据量庞大(根据客户的需求和线上的环境)

该字段很少进行DML操作(因为一旦修改数据，索引就需要重新排序，进行维护。)

该字段经在where子句中出现

注意：根据主键进行检索

使用explain查看sql执行计划

创建索引对象和删除

创建

create index 索引名称 on 表明(字段名)

删除

drop index 索引名称 on 表名

索引原理详情

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找(Binary Search)，这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是用B+Tree作为索引结构(主键，unique 都会默认的添加索引)

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

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

索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加1个索引

主键索引：主键上回自动添加索引

唯一索引：有unique约束的字段上回自动添加索引

索引什么时候失效

比如：select ename from emp where ename like ‘%A%’;

15、视图

1、 什么是视图

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

2、 创建视图、删除视图

创建

create view myview as select empno,ename from emp;

删除

drop view myview;

3、对视图进行增删改查，会影响到原表数据。(通过视图影响原表数据的，不是直接操作的原表)，可以对视图进行crud操作

4、面向视图操作。

mysql> delete from myview where empno = 7934;

删除视图里面的某数据，原表会跟着一起删掉

5、视图的作用

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

DBA命令

将数据库当中的数据导出

在Windows的dos命令窗口中执行

指定库mysqldump studytest>D:\studytest.sql -u root -p

指定表

导入数据

source sql文件路径

数据库设计三范式

1、什么是设计范式

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

2、三范式

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

第二范式：建立在第一范式之上，所有非主键字段完全不依赖主键，不能产生部分依赖

多对多，三张表，关系表+两个外键

第三范式：建立在第二范式之上，所有非主键字段直接依赖主键，不能产生传递依赖

一对多：两张表，多的表加外键

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

3、一对一设计

主键共享

外键唯一

数据库——mysql如何获取当前时间

获得当前日期+时间(date + time)函数：now()

除了 now() 函数能获得当前的日期时间外，MySQL 中还有下面的函数：

current_timestamp() current_timestamp

localtime() localtime

localtimestamp() localtimestamp

这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。

获得当前日期+时间(date + time)函数：sysdate()

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。

获得当前日期(date)函数：curdate()

其中，下面的两个日期函数等同于 curdate()： current_date(),current_date

获得当前时间(time)函数：curtime()

其中，下面的两个时间函数等同于 curtime()：current_time(),current_time

获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()

时间差

语法：timestampdiff(interval, datetime1,datetime2)

结果：返回(时间2-时间1)的时间差，结果单位由interval参数给出。

frac_second 毫秒(低版本不支持，用second，再除于1000)

second 秒

minute 分钟

hour 小时

day 天

week 周

month 月

quarter 季度

year 年

注意：MySQL 5.6之后才支持毫秒的记录和计算，如果是之前的版本，最好是在数据库除datetime类型之外的字段，再建立用于存储毫秒的int字段，然后自己进行转换计算。

乐观锁和悲观锁