MySQL命令大全、合集

MySQL命令大全

######------------------------

其中用到的数据库er图

######------------------------

基本指令

net start mysql 启动MySQL服务

net stop mysql 停止MySQL服务

mysql -u root -p + 密码登录进入mysql>

mysql>内输入指令都需要用;结束

mysql --version mysql -V 查看MySQL版本

sleect version(); 查看MySQL版本

\c ctrl+c 终止一条正在编写的语句

exit \q QUIT 退出mysql

对database的操作

show databases; 显示所有数据库

select database(); 查询当前使用的数据库

create batabase dbName 创建数据库

drop database dbName 删除数据库

use dbName; 使用数据库

对table的操作

show tables 显示数据库中的表

show tables from dbName; 查看指定数据库的表

source D:/Desktop/learn/MySQL/bjpowernode.sql; 导入sql文件，并执行里面的sql语句

select* from tableName 查看表中的全部数据

desc tableName 查看表的结构 desc→describe

show create table tableName; 查看表的创建语句

简单的查询语句

select 字段名 from tableName; 查询某个字段的数据

select 字段名,字段名 from tableName;查询多个字段可用 , 将不同字段间隔

select* from tableName 查看表中的全部数据

select empno as'员工编号',ename as '员工姓名',sal*12 as '年薪' from emp; as 'xxx' 可以将指定的要查询字段用另一个名字显示

条件查询语句

要使用到where语句

select empno,ename,sal from emp where sal>3000; 查询薪水>5000的员工

select empno,ename,sal from emp where sal<>5000; 查询薪水≠5000的员工

select empno,ename,sal from emp where sal>= 1600 and sal<=3000;

select empno,ename,sal from emp where sal between 1600 and 3000; between…and…闭区间

select * from emp where comm is null;

select * from emp where job='MANAGER' and sal > 2500; and表且，即查询符合多个条件的数据

select * from emp where job='MANAGER' or job='SALESMAN'; or表并列，只要满足其中一个条件即可

select * from emp where sal > 1800 and (deptno = 20 or deptno = 30); 用()来限定表达式的优先级

select * from emp where job in('manager','salesman'); in 表包含

select * from emp where sal not in (1600, 3000); 不包含这个条件的数据

select * from emp where comm is not null; 查询某子段不为空的数据

like模糊查询，支持%和下划线匹配

select * from emp where ename like 'M%'; 查询所有姓名以M开头的数据

select * from emp where ename like '%N'; 查询所有姓名以N结尾的数据

select * from emp where ename like '%O%'; 查询所有姓名中包含O的数据

select * from emp where ename like '_A%';查询姓名中第二个字符为A的所有员工

单一字段排序

排序采用order by子句，后面跟上排序字段，排序字段可以放多个，用逗号间隔，order by 默认升序排序，若存在where 子句，那么order by必须在where后面

select * from emp order by sal;

select * from emp where job='MANAGER' order by sal;

select * from emp order by sal asc; 手动指定从小到大排序 升序

select * from emp order by sal desc; 手动指定从大到小排序 降序

select * from emp order by job desc, sal desc; 采用多个字段排序，如果根据第一个字段重复了会根据第二个字段排序

数据处理函数/单行处理函数

数据处理函数又被称为单行处理函数，特点是：输入一行输出一行

select lower(ename) from emp; lower()将指定的内容转化为小写

select * from emp where job=upper('manager'); 将指定内容转化为答谢

select * from emp where substr(ename, 1, 1)=upper('m'); substr(字段,start,end) 截取指定字段的某一段字符

select length(ename), ename from emp where length(ename)=5; length(字段) 得到字段的长度

select * from emp where job=trim(upper(' manager ')); trim()去除字段或字符串首位的空格

str_to_date(将字符串转化为date数据类型 只包含年月日) 下面是使用举例

select * from emp where HIREDATE='1981-02-20'; 正常查询，查符合条件的date数据的字符串

select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d'); 将要查询的时间字符串转化为date数据，Y是大写，要一一对应了

select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y'); 顺序要对应

date_format(将date类型字段转化为年月日 时分秒的形式，且可以控制显示顺序) 下面是示例

select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp; 查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

select date_format(now(),'%Y-%m-%d %H %i %s'); now() 函数获取当前时间

%Y	代表 4 位的年份		%H	代表小时,格式为(00……23)
%y	代表 2 位的年份		%h	代表小时,格式为(01……12)
%m	代表月, 格式为(01……12)		%i	代表分钟, 格式为(00……59)
%c	代表月, 格式为(1……12)		%S	代表秒,格式为(00……59)
%d	代表日		%s	代表秒,格式为(00……59)
%r	代表时间,格式为 12小时(hh:mm:ss [AP]M)		%T	代表时间,格式为 24小时(hh:mm:ss)

Format(字段，n) 函数给字段查询到的数据加上千分位逗号分隔符，并保留n位小数

select empno,ename,Fromat(sal,0) from emp; 查询员工薪水加入千分位

select empno,ename,Format(sal,2) from emp; 查询员工薪水加入千分位和保留两位小数

select round(123.56); 四舍五入

select rand(); 生成随机数

select * from emp order by rand() limit 4; 随机抽取记录数 ?

case...when...then...else...end... 举例：如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%

select empno,ename,job,sal,case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;

select e.,sal,case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end as newsal from emp e; from emp e 给emp取别名 e ，e.*表示表下的所有字段

ifnull(字段,n) 字段中值为null 则替换成 n

select ifnull(comm,0) from emp;

分组函数/聚合函数/多行处理函数

count()	取得记录数		avg()	取平均
sum()	求和		max()	取最大的数		min()	取最小的数

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值

select count(*) from emp where xxx; 符合条件的所有记录总数

select count(comm) from emp; comm这个字段中不为空的元素总数

注意：分组函数不能直接在where后面使用 如：

mysql>select ename sal from emp where sal>avg(sal); ERROR 1111(HY000): Invalid use of group funvtion 是错误的使用

select count(*) from emp; 取得所有的员工数

select count(distinct job)from emp; 取得工作岗位的个数 判断字段的数据有几种

select sum(sal) from emp; 求某一列的和 忽略null值

select sum(sal+comm) from emp; 此方式假如一个字段是null，那么这行数据都会被忽略 所以要将null替换成0 ，如下：

select sum(sal+ifnull(comm,0) from emp;

select avg(sal) from emp; 取得某一列的平均值

select max(sal) from emp; 取得某一列的最大值

select max(str_to_date(hiredate,'%Y-%m-%d')) from emp; 取得日期中最大的

select min(sal) from emp; 取最小值

select min(str_to_date(hiredate,'%Y-%m-%d')) from emp;

组合聚合函数

select count(*),sum(sal),avg(sal),min(sal) from emp;

分组查询

主要涉及到两个子句，分别是 group by 和 having 如果使用了order by，那么必须放在group by 后面

select job,sum(sal) from emp group by job; 根据job字段分组，得出每种job的sal总和

select job,avg(sal) from emp group by job; 根据job字段分组，得出，每种job的平均sal

select job,deptno,sum(sal) from emp group by job,deptno; 按job和deptno共同分组，即两个字段数据相同的一组，得出每组sal的和

select empno,deptno avg(sal) from emp group by deptno; 报错 如果sql语句中有group by 语句，那么select 语句后面只能跟分组函数和参与分组的字段

如果要对分组数据再进行过滤，就要使用having子句

select job,avg(sal) from emp group by job having avg(sal)>2000; 取得平均工资大于2000的岗位

总结：分组函数执行顺序：根据条件查询数据→分组→采用having过滤，取得正确的数据

---

select 语句总结：

一个完整的select语句：select 字段 from 表名 where ...... group by 字段 having ...... order by 字段

原则：能在where中过滤的数据尽量在where中过滤，效率较高。having是专门对分组之后的数据进行过滤的

连接查询

SQL92语法

select ename,dname from emp,dept; 这里输出不正确，产生的情况称为笛卡尔乘积，原因是没指定连接条件

select e.ename,d.dname from emp e,demp d where e.deptno=d.deptno; 指定了连接条件 “内连接”：只查询连接条件相等的数据

select e.ename,m.ename from emp e,emp m where e.mgr=m.empno; “自连接”：把一张表看成两张表

SQL99语法

查询薪水大于2000的员工姓名和所属部门名

select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000; SQL92语法

select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal e.sal>2000; SQL99语法

或者select e.ename,e.sal,d.dname from emp e,dept d``inner``join dept d on e.deptno=d.deptno where e.sal>2000; 实际使用中一般不加inner关键字。

Sql92语法和SQL99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表进行连接时，会比92语法更清晰

select e.ename,e.sal,d.dname from emp e right join dept d on e.deptno=d.deptno; 右连接

select e.ename,e.sal,d.dname from emp e left join dept d on e.deptno=d.deptno; 左连接

以上的左连接和右连接查询效果相同 都叫 “外连接”

连接分类：

内连接： →表1 inner join 表2 on 关联条件 →做连接查询时一定要写关联条件 →inner可以省略

外连接： *左外连接： →表1 left outer join 表2 on 关联条件 →做连接查询一定要写关联条件 →outer 可以省略 *右连接 →表1 right outer join 表2 on 关联条件 →做连接查询一定要写关联条件 →outer 可以省略 *左外连接和右外连接的区别： →左外连接以左面的表为准和右边的表比较，和左表不相等的都会显示出来，右表符合条件的显示，不符合条件的不显示 →右外连接恰恰相反 outer关键字不建议写

将上面的左右外连接的sql语句加上一个条件之后查询结果完全不同，也体现出两种查询的区别，如下所示：

select e.ename,e.sal,d.dname from emp e left join dept d on e.deptno=d.deptno and e.sal>2000;

select e.ename,e.sal,d.dname from emp e RIGHT join dept d on e.deptno=d.deptno and e.sal>2000;

左连接能完成的功能右连接一定可以完成

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

在where语句中使用子查询：也就是在where语句中加入select 语句

select distinct mgr from emp where mgr is not null; 先取得管理者编号，然后去除重复的 distinct 去除重复行

select empno,ename from emp where empno in(select distinct mgr from emp where mgr is not null); 查询员工编号包含管理者的

select empno,ename,sal from emp where sal>(select avg(sal) from emp); 查询薪水大于平均薪水的员工信息

在from 语句中使用子查询，即可以将该子查询看做一张表

92语法 select e.empno,e.ename from emp e,(select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr; 找出员工中的管理者

99语法 select e.empno,e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

查询每个部门的平均薪资所属水平：

select a.deptno,a.avg_sal,s.grade from (select deptno,avg(sal) avg_sal from emp group by deptno) a join salgrade s on a.avg_sal between s.losal and s.hisal;

在select 语句中使用子查询

查询员工信息，并显示所属部门名称

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; 方法一 将emp表和dept表连接查询

select e.ename,(select d.dname from dept d where d.deptno=e.deptno) as dname from emp e; 方法二 在select语句中嵌套select语句完成部分名称的查询

union

union可以合并集合 （相加）

select * from emp where job in('manager','salesman'); 不适用union

select * from emp where job='manager' union select * from emp where job='salesman'; 使用union

合并结果集的时候，需要查询的字段相对应的个数相同

limit的使用

MySQL提功力limit ，主要用于提取前几条或者中间某几行数据

select * from tableName limit m,n; m是指开始的index ，index从0开始 n是指从第m+1条(下标m)开始，取n条

select * from emp limit 1,3; 从第2条数据(下标1)开始，取3条数据

select * from emp limit 4; 取4条数据，默认从下标0开始即从第一条数据开始取

select * from emp e order by e.sal limit 5; 取薪水最高的前五名，即先按薪水排序，取前五个

表

创建表 表名建议t_ 或tbl_ 开头，可读性强

create tabel tableName(
  columnName dateType(length),
  ...........
  columuName dateType(length)
);
set character_set_results='utf8';

show variables like '%char%'; 查询MySQL数据库编码

创建表时，表中字段有 字段名、数据类型、长度限制、约束

MySQL常用数据类型

Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键		bigint(长度)	长整型
Varchar(长度)	变长字符串，存储空间等于实际数据空间		Date	日期型
double(有效数字位数，小数位)	数值型		BLOB	Binary Large OBject（二进制大对象）
Float(有效数字位数，小数位)	数值型		CLOB	Character Large OBject（字符大对象）
Int(长度)	整型		其他…

创建学生表

drop table if exists t_student;
create table t_student(
    student_id int(10),
    student_name varchar(20),
    sex char(2),
    birthday date,
    email varchar(30),
    classes_id int(3)
);

向学生表中插入数据

insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values(1001,'vangsj','m','1998-01-01','qqq.@163.com',10);

增加/删除/修 改表结构 DDL

使用alter table来增加删除修改表结构

→添加字段

alter table t_student add contacr_tel varchar(40); alter table t_Name ``add`` 字段名 类型(长度);

→修改字段

alter table t_student modify student_name varchar(100); alter table t_student ``modify`` 字段名 类型(长度); 修改已有字段的类型或类型长度

alter table t_student change sex gender char(2) not null; change 可以修改字段名 且指定not null 之类的

alter table t_studnet drop contact_tel; drop 删除字段

添加/修改/删除 表数据 DML

添加数据

insert into t_name(字段,...)vlaues(值,...); 添加数据一般格式

insert into emp values(999,'vhsj','MANAGER',null,null,3000,500,10); 可以省略字段，直接写values，但不建议此方式，因为当数据库表的字段位置发生变化时，此语句会失效

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9999,'vhsj','MANAGER',null,null,3000,500,10),(...),(...);可插入多条数据

如何插入日期

方法一 插入日期格式和显示的日期格式相同 insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9999,'vhsj','MANAGER',null,'1981-06-12',3000,500,10);

方法二 采用str_to_date() insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9999,'vhsj','MANAGER',null,date_to_str('1981-06-12','%Y-%m-%d'),3000,500,10);

方法三 添加系统日期now()insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9999,'vhsj','MANAGER',null,now(),3000,500,10);

复制表数据

create table emp_bak as select empno,ename,sal from emp; 连带着select的字段中数据也会一并复制

create tbale emo_bak as select * from emp where sal>2500; 也可以添加条件，将符合条件的数据复制

修改表数据

update 表名 set 字段名=新值,字段名=新值 ... where ....

update emp set sal=sal*1.1 where job='MANAGER';

删除数据

delete from t_name where ..... 一般格式

delete from emp where comm=500;

delete from emp where comm is null; 注意 判断是不是null 要用 is null is not null

delete from t_name; 删除所有数据， 属于DML语句 表中的数据被删除了，但是在硬盘上的真实存储空间不会被释放 缺点：删除效率比较低 优点：支持回滚，后悔了可以恢复数据

truncate table t_name; 属于DDL语句 这种删除数据效率比较高，表被一次截断，物理删除 缺点：不支持回滚 优点：快速

创建表加入约束

常见约束 重点只有四个 非空，唯一，主键，外键

非空约束	not null
唯一约束	unique		自定义检查约束	MySQL不支持
主键约束	primary key		外键约束	foreign key

use information_schema;

select * from table_constraints where table_name='emp';

drop table if exists t_student; 
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) ,
classes_id int(3) ,
constraint email_unique unique(email)/*表级约束*/
)

drop table if exists t_student; 
create table t_student()
student_id int(10) primary key,/*列级约束*/
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) ,
classes_id int(3)
)

constraint 别名 约束(字段名) primary key auto_increment 主键自增

unique(student_name,email); 联合唯一

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

unique约束的字段不能重复，但是可以为null

如果没有主动设置主键，而且有一个字段设置为not null unique 就会被默认认为是主键

drop table if exists t_student; 
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) ,
classes_id int(3),
 CONSTRAINT p_id PRIMARY key (student_id) /*表级约束*/
)

创建表时加入外键

drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) 
)

constraint 别名 foreign key(字段名) references 其它表名(主键字段名) 设置外键

存在外键字的的表就是子表，参照表就是父表，外键起到约束作用 比如向student表中插入的数据中classes_id在classes_id中不存在，那么插入失败，如果插入null是可以的但不合理，所以设置student表中classes_id为not null

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现错误:

insert into t_classes (classes_id,classes_name) values (10,'366'); /*添加班级 编号10*/
insert into t_student(
student_id, student_name, sex, birthday, email, classes_id
) values(
1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10    /*插入学生数据，班级编号10*/
)
mysql> update t_classes set classes_id = 20 where classes_name = '366'; /*直接修改编号为10的班级编号出错*/

delete from t_classes where classes_id=10; 直接删除编号为10的班级数据也会出错

因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以要删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用 drop table 也不行，必须先 drop 子表，再 drop 父表

当然我们也可以采取以下措施 级联删除。

级联更新与级联删除

更新

update cascade 对于父表主键的修改，可以先删除子表的外键再添加回来，再修改父表的主键内容，此时子表中的外键对应的值也会修改

alter table t_student drop foreign key fk_classes_id; 先删除外键约束 alter table t_student add contraint fk_classe_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade; 再添加外键约束，条件 on update cascade update t_classes set classes_id=20 where classes_name='366'; 修改父表主键 select * from t_student; 再次查看子表，发现子表外键内容已随父表主键的修改而改变

删除

on delete cascade 对于删除父表主键的数据，子表外键有对应数据，可以先删除外键约束以再此条件添加回来，再删除父表主键数据，子表外键对应数据也会被删除

alter table t_student drop foreign key fk_classes_id; 先删除外键约束 alter table t_student add contraint fk_classe_id_1 foreign key(classes_id) references t_classes(classes_id) on delete cascade; 再添加外键约束，条件 on delete cascade delete from t_classes where classes_id=20; select * from t_student; 查看子表，发现对应数据被删除了

增加/删除/再去改表约束

表级操作 DDL alter drop、add、modify

alter table 表名 drop foreign key 外键名; 删除外键约束

alter table 表名 drop primary key; 删除主键约束

alter table 表名 drop key 约束名; 删除约束约束

存储引擎

数据库中的表在创建时，均被指定的存储引擎来处理

实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同

服务器可用的引擎依赖于以下因素： MySQL版本、服务器在开发时如何被配置、启动选项

创建表时在 ) 后面用engine= 指定存储引擎 default charset= 指定字符编码格式，如下所示

 create table t_product(
     id int primary key,
     name varchar(25) 
     )engine=InnoDB default charset=utf8; /*指定存储引擎  和字符编码格式*/  

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

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

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

为确定某表所使用的存储引擎，可以使用 show create table t_name; 或 show table status like 't_name'; 语句：

show engines; 查看当前服务器中有哪些存储引擎可用 mysql支持九大存储引擎，当前版本5.7.35支持8个，如下所示

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

常用的存储引擎

MyISAM存储引擎

MyISAM是MySQL最常用的引擎，它管理的表有以下特征： →使用三个文件表示每个表： ——格式文件 — 存储表结构的定义(mytable.frm) ——数据文件 — 存储表行的内容 (mytable.MYD) ——索引文件 — 存储表上的索引(mytable.MYI) 索引是一本书的目录，缩小扫描范围，提高查询效率的一种极致 →灵活的AUTO_INCREMENT字段处理 →可被转换为压缩、只读表来节省空间

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

MySIAM的优点是可被转换为压缩、只读来节省空间 MyISAM的缺点是不支持事务机制，安全性低

InnoDB存储引擎

InnoDB支持事务机制、支持数据库崩溃后自动恢复机制，主要特点是非常安全。效率不是很高，也不能压缩，不能转换为只读，不能很好的节省空间

InnoDB存储引擎是MySQL的缺省引擎(就是默认)，它管理的表具有下列主要特征： →每个InnoDB表在数据库目录中以 .frm 格式文件表示 →InnoDB 表空间 tablespace 被用于存储表的内容 （表空间是一个逻辑名称。表空间存储数据+索引） →提供一组用来记录事务性活动的日志文件 →用commit、savepoint及rollback(回滚)支持事务处理 →提供全ACID兼容 →在MySQL服务器崩溃后提供自动恢复 →多版本(MVCC)和行级锁定 →支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

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

MEMORY存储引擎管理的表具有以下特征： →在数据库目录中，每个表均以 .frm 格式的文件表示 →表数据及索引被村春在内存中 （目的就是快，查询快） →表级锁机制 →不能包含text或blob字段

MEMORY引擎以前被称为HEAP引擎 MEMORY引擎的优点是：查询效率是最高的。不需要和硬盘交互 MEMORY引擎的缺点是：不安全，关机之后数据消失。

选择合适的存储引擎

MyISAM表适合于大量的数据读而少量数据更新的混合操作。另一种使用情况是使用压缩的只读表 如果查询中包含较多的数据更新操作，应该使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于此盘的表中重新生成的数据

事务

概述

一个事务其实就是一个完整的业务逻辑 是一个最小的工作单元，不可再分

一个事务中的操作要么同时成功，要么同时失败，不可再分。

只有DML语句才有事务一说，其它语句和事务无关

只有insert、delete、update三个语句和事务有关 因为只有这三个语句是对数据库表中数据进行增、删、改的 一旦涉及到增、删、改就一定要考虑安全问题

事务具有四个特征 ACID： 原子性(Atomicity) ：整个食物的所有操作，必须作为一个单元全部完成或全部取消 一致性(Consistency) ：在事务开始之前与结束之后，数据库都保持一致状态 隔离性(Isolation) ：一个事务不会影响其它事务的运行 持久性(Durability) ：在事务完成以后，该事物对数据库所做的更改将持久地保存在数据库之中，并不会被回滚

事务中存在的一些概念： 事务(Transactiom) ：一批操作 (一组DML) 开启事务(Start Transaction) 回滚事务(roolback) 提交事务(commit) SET AUTOCOMMIT

注意： rollback或者commit后事务就结束了

在事务的执行过程中，每一条DML的操作都会被记录到"事务性活动的日志文件"中。 事务的执行过程中，可以提价事务，也可以回滚事务

事务的提交与回滚演示

回滚事务

1.创建表

create table user(
    id int(11) primary key not null auto_increment,
    username varchar(30),
    password varchar(30)
)ENGINE=InnoDB default charset=utf8;

2.查询表中数据

select * from user;

3.开启事务START TRANSACTION

start transaction

4.插入数据

insert into user(username,password) values('vhsj','123');

$$

$$

5.查看数据

select * from user;

6.修改数据

update user set username='lisi' where id=1;

7.查看数据

select * from user;

8.回滚事务

rollback

9.查看数据

select * from user;

提交事务

1. start transaction;

2. insert into user(username,password) values('vhsj','123');

3. commit;

4. rollback;

5. select*from user;

自动提交模式

mysql默认情况下是支持自动提交事务的即每执行一条DML语句则提交一次，这种自动提交实际上是不符合我们的开发习惯的，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条,start transaction; 即可关闭自动提交

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

mysql>set autocommit=off;关闭自动事务提交，也可以用 mysql>start transaction; mysql>set autocommit=on;让事务可以自动提交 show variables like '%auto%'; 查看变量状态

事务的隔离级别

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

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

– 脏读取（Dirty Read）

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

– 不可重复读（Non-repeatable Read）

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

– 幻像读（Phantom Read）

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

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

– 读未提交（READ UMCOMMITTED）

允许一个事务可以看到其他事务未提交的修改。 事务A可以读取到事务B未提交的数据。 这种隔离级别存在的问题就是：脏读现象！(Dirty Read) 我们称读到了脏数据。 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

– 读已提交（READ COMMITTED）

允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。 事务A只能读取到事务B提交之后的数据。 这种隔离级别解决了什么问题？解决了脏读的现象。 这种隔离级别存在什么问题？不可重复读取数据。 什么是不可重复读取数据呢？在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。 oracle数据库默认的隔离级别是：read committed

– 可重复读（REPEATABLE READ）《提交之后也读不到，永远读取的都是刚开启事务时的数据》

事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。 可重复读解决了什么问题？解决了不可重复读取数据。 可重复读存在的问题是什么？可以会出现幻影读。每一次读取到的数据都是幻象。不够真实！ 早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！读到的是假象。 不够绝对的真实。mysql中默认的事务隔离级别就是这个！！！该隔离级别为 InnoDB 的缺省设置。

– 串行化（SERIALIZABLE） 【序列化】（最高的隔离级别）

将一个事务与其他事务完全地隔离

这是最高隔离级别，效率最低。解决了所有的问题。 这种隔离级别表示事务排队，不能并发！synchronized，线程同步（事务同步）每一次读取到的数据都是最真实的，并且效率是最低的。

隔离级别	脏读取	不可重复读	幻象读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	对InnoDB不可能
串行化	不可能	不可能	不可能

设置服务器缺省隔离级别

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

选项值有 —READ-UNCOMMITTED —READ-COMMITED —REPEATABLE-READ —SERIALIZABLE

例如： [mysqld] transaction-isolation=READ-COMMITED

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

mysql>set transaction isolation level REPEATABLE READ;

事务隔离级别的作用范围分为两种：全局级：对所有会话有效 会话级：只对当前会话有效

mysql>set transaction isolation level read committed; —会话级 mysql>set global transaction isolation level read committed; 全局级

mysql>select @@tx_isolation; 或 mysql>select @@session.tx_isolation; 查看会话级隔离级别 mysql>select @@global.tx_isolation; 查看全局级会话隔离级别

事务隔离级别演示

• 打开一个终端，进入数据库后，输入 mysql>select @@global_tx.isolation; mysql>select @@tx_isolation; 查看隔离级别应该是一样的 比如默认是repeatable-read 再打开一个终端，同样，再输入mysql>set global transaction isolation level READ UNCOMMITTED; 此时此终端的当前会话还是 repeatable-read，其它已打开未关闭的会话终端也是它们自己原有的隔离级别不变，但是查找全局终端都是新的了，打开一个新的会话终端，那么当前隔离级别和全局隔离级别都是新的了。

• 读未提交(read uncommitted)演示，打开两个终端，进入数据库后创建一个t_user表，只有一个name字段，两个终端暂命名为左/右终端，默认时，两个都是repeatable-read，左终端输入mysql>set global transaction isolation level read uncommitted;两个终端都重新连接MySQL数据库，此时他们的隔离级别都是read uncommitted，左终端 start transaction; select * from t_user; 没有数据 右终端 start transaction; insert into t_user(name) values('vhsj');左终端 select * from t_user; 显示了刚才插入的数据，这就是读未提交隔离级别。 右终端 roll back; 左终端 select * from t_user; 没有数据了

• 读已提交(read committed) 左终端 mysql>set global transaction isolation level read committed;左右终端重新进入MySQL，左右终端都输入 start transaction; 左终端 select * from t_user;无数据，右终端insert into t_user(name) values('ii'); 左终端 select * from t_user; 无数据，右终端 commit; 左终端 select * from t_user;显示刚插入的数据;

• 后面的就不演示了，很好懂；

---

索引

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

什么时候需要给字段添加索引： -表中该字段中的数据量庞大 -经常被检索，经常出现在 where 子句中的字段 -经常被 DML 操作的字段不建议添加索引 索引等同于一本书的目录主键会自动添加索引，所以尽量根据主键查询效率较高。

mysql>create unique index 索引名 on 表名(列名); 创建索引一般格式

mysql>reate unique index u_ename on emp(ename);

mysql>create index test_index on emp(sal);

show index from emp; 查看表emp的索引

MySQL在查询方面主要就是两种方式：

    第一种方式：全表扫描

    第二种方式：根据索引检索。

注意：

    在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，

    为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围

    其实就是扫描某个区间罢了！）

    在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet

    数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql

    当中索引是一个B-Tree数据结构。

    遵循左小又大原则存放。采用中序遍历方式遍历取数据。

    

索引实现的原理

提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，

因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动

创建索引对象。

提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有

一个硬盘的物理存储编号。

提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式

存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中

索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引

被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式

存在。（自平衡二叉树：B-Tree）

在mysql当中，主键上，以及unique字段上都会自动添加索引的！！！！

什么条件下，我们会考虑给字段添加索引呢？

    条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）

    条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。

    条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

    建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

    建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

索引怎么创建？怎么删除？语法是什么？

    创建索引：

        mysql> create index emp_ename_index on emp(ename);

        给emp表的ename字段添加索引，起名：emp_ename_index

    删除索引：

        mysql> drop index emp_ename_index on emp;

        将emp表上的emp_ename_index索引对象删除。

在mysql当中，怎么查看一个SQL语句是否使用了索引进行检索？

  mysql> explain select * from emp where ename = 'KING';
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  扫描14条记录：说明没有使用索引。type=ALL

  mysql> create index emp_ename_index on emp(ename);

  mysql> explain select * from emp where ename = 'KING';
  +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  | id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
  +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  |  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
  +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ 

索引有失效的时候，什么时候索引失效呢？

    失效的第1种情况：

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

        ename上即使添加了索引，也不会走索引，为什么？

            原因是因为模糊匹配当中以“%”开头了！

            尽量避免模糊查询的时候以“%”开始。

            这是一种优化的手段/策略。

    mysql> explain select * from emp where ename like '%T';
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第2种情况：

    使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有

    索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个

    字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
    +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

失效的第3种情况：

    使用复合索引的时候，没有使用左侧的列查找，索引失效

    什么是复合索引？

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

    create index emp_job_sal_index on emp(job,sal);
    
    mysql> explain select * from emp where job = 'MANAGER';
    +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
    +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
    |  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |
    +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
    
    mysql> explain select * from emp where sal = 800;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第4种情况：

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

mysql> create index emp_sal_index on emp(sal);

    explain select * from emp where sal = 800;
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
    |  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

    mysql> explain select * from emp where sal+1 = 800;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  失效的第5种情况：
    在where当中索引列使用了函数
    explain select * from emp where lower(ename) = 'smith';
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+


  失效的第6...
  失效的第7...

索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。

索引在数据库当中分了很多类？

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

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

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

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

    …

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

视图

2.1、什么是视图？
  view:站在不同的角度去看待同一份数据。

2.2、怎么创建视图对象？怎么删除视图对象？

  表复制：
  mysql> create table dept2 as select * from dept;

  dept2表中的数据：
  mysql> select * from dept2;
  +--------+------------+----------+
  | DEPTNO | DNAME      | LOC      |
  +--------+------------+----------+
  |     10 | ACCOUNTING | NEW YORK |
  |     20 | RESEARCH   | DALLAS   |
  |     30 | SALES      | CHICAGO  |
  |     40 | OPERATIONS | BOSTON   |
  +--------+------------+----------+

  创建视图对象：
    create view dept2_view as select * from dept2;
  
  删除视图对象：
    drop view dept2_view;
  
  注意：只有DQL语句才能以view的形式创建。
    create view view_name as 这里的语句必须是DQL语句;

2.3、用视图做什么？

  我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致
  原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）

  //面向视图查询
  select * from dept2_view; 

  // 面向视图插入
  insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

  // 查询原表数据
  mysql> select * from dept2;
  +--------+------------+----------+
  | DEPTNO | DNAME      | LOC      |
  +--------+------------+----------+
  |     10 | ACCOUNTING | NEW YORK |
  |     20 | RESEARCH   | DALLAS   |
  |     30 | SALES      | CHICAGO  |
  |     40 | OPERATIONS | BOSTON   |
  |     60 | SALES      | BEIJING  |
  +--------+------------+----------+

  // 面向视图删除
  mysql> delete from dept2_view;

  // 查询原表数据
  mysql> select * from dept2;
  Empty set (0.00 sec)
  

  // 创建视图对象
  create view 
    emp_dept_view
  as
    select 
      e.ename,e.sal,d.dname
    from
      emp e
    join
      dept d
    on
      e.deptno = d.deptno;

  // 查询视图对象
  mysql> select * from emp_dept_view;
  +--------+---------+------------+
  | ename  | sal     | dname      |
  +--------+---------+------------+
  | CLARK  | 2450.00 | ACCOUNTING |
  | KING   | 5000.00 | ACCOUNTING |
  | MILLER | 1300.00 | ACCOUNTING |
  | SMITH  |  800.00 | RESEARCH   |
  | JONES  | 2975.00 | RESEARCH   |
  | SCOTT  | 3000.00 | RESEARCH   |
  | ADAMS  | 1100.00 | RESEARCH   |
  | FORD   | 3000.00 | RESEARCH   |
  | ALLEN  | 1600.00 | SALES      |
  | WARD   | 1250.00 | SALES      |
  | MARTIN | 1250.00 | SALES      |
  | BLAKE  | 2850.00 | SALES      |
  | TURNER | 1500.00 | SALES      |
  | JAMES  |  950.00 | SALES      |
  +--------+---------+------------+

  // 面向视图更新
  update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';

  // 原表数据被更新
  mysql> select * from emp;
  +-------+--------+-----------+------+------------+---------+---------+--------+
  | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
  +-------+--------+-----------+------+------------+---------+---------+--------+
  |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
  |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
  |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
  |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
  |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
  |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
  |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
  |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
  |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
  |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
  |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
  |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
  +-------+--------+-----------+------+------------+---------+---------+--------+

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

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

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

    小插曲：
      增删改查，又叫做：CRUD。
      CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
      一般都说CRUD。

      C:Create（增）
      R:Retrive（查：检索）
      U:Update（改）
      D:Delete（删）

DBA命令

DBA常用命令？

  重点掌握：
    数据的导入和导出（数据的备份）
    其它命令了解一下即可。（这个培训日志文档留着，以后忘了，可以打开文档复制粘贴。）
  
  数据导出？
    注意：在windows的dos命令窗口中：
      mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
    
    可以导出指定的表吗？
      mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

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