mysql常用命令
SQL命令语句的分类：
- 数据查询语言(DQL-Data Query Language) ：代表关键字:select
- 数据操纵语言(DML-Data Manipulation Language)：代表关键字:insert,delete,update
- 数据定义语言(DDL-Data Definition Language)：代表关键字:create ,drop,alter,
- 事务控制语言(TCL-Transactional Control Language)：代表关键字:commit ,rollback;
- 数据控制语言(DCL-Data Control Language)：代表关键字:grant,revoke.
DDL命令（操作数据库和表）
操作数据库命令
-- 查看msyql版本
mysql --version;
mysql -V;
select version();
-- 创建数据库
create database 数据库名称;
create database if not exists 数据库名称;
create database 数据库名称 character set 字符集名;
-- 删除数据库
drop database 数据库名称;
drop database if exists 数据库名称;
-- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
-- 查询某个数据库的创建语句
show create database 数据库名称;
-- 使用数据库
use 数据库名称;
-- 查询当前使用的数据库
select database();
-- 查看现有的数据库
show database();
-- 查看当前使用的数据库
select database();
操作表命令
-- 查看表的结构
desc 表名称;
-- 查看表的创建语句
show create table 表名称;
-- 查看当前库中的表
show tables();
-- 查看其它库中的表
shoa tables from 数据库名称;
-- 创建表,注意：最后一列，不需要加逗号
create table tableName(
columnName dataType(length),
………………,
columnName dataType(length)
);
-- 复制表：
create table 表名 like 被复制的表名;
-- 修改表名
alter table 表名 rename to 新的表名;
-- 修改表的字符集
alter table 表名 character set 字符集名称;
-- 添加字段
alter table 表名 add 列名 数据类型;
-- 修改列名称 or 类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;
-- 删除列
alter table 表名 drop 列名;
-- 删除表
drop table 表名;
drop table if exists 表名 ;
数据类型
mysql常用数据类型：
- Char(长度) 定长字符串，存储空间大小固定，适合作为主键或外键
- Varchar(长度) 变长字符串，存储空间等于实际数据空间
- double(有效数字位数，小数位) 数值型
- Float(有效数字位数，小数位) 数值型
- Int( 长度) 整型
- bigint(长度) 长整型
- Date 日期型 年月日
- DateTime 日期型 年月日 时分秒 毫秒
- time 日期型 时分秒
- BLOB Binary Large OBject（二进制大对象）
- CLOB Character Large OBject（字符大对象）
约束
创建表加入约束：
-
常见的约束
- 非空约束，not null =》 针对某个字段设置其值不为空，如：学生的姓名不能为空
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) )- 唯一约束，unique =》唯一性约束，它可以使某个字段的值不能重复，如：email不能重复
create table t_student( student_id int(10), student_name varchar(20) not null, sex char(2) default 'm', birthday date, email varchar(30) unique, classes_id int(3) )- 主键约束，primary key =》每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的
-- 列级约束 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) ) -- 表级约束 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) )- 外键约束，foreign key =》外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。
-- 首先建立班级表t_classes drop table if exists t_classes; create table t_classes( classes_id int(3), classes_name varchar(40), constraint pk_classes_id primary key(classes_id) ) -- 在t_student中加入外键约束 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) not null, constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) )
DML命令（增删改表中数据）
简单增删改
-- 添加记录
-- 注意事项：1.列名和值要一一对应。2.如果表名后，不定义列名，则默认给所有列添加值insert into 表名 values(值1,值2, ... ,值n);3.除了数字类型，其他类型需要使用引号(单双都可以)引起来
insert into 表名(列名1,列名2,...列名n) values(值1,值2, ... ,值n);
-- 删除记录
-- 注意事项：1. 如果不加条件，则删除表中所有记录。2. 如果要删除所有记录，有两种方式
-- 1.不推荐使用。有多少条记录就会执行多少次删除操作
delete from 表名 [where 条件];
-- 2.推荐使用，效率更高 先删除表，然后再创建一张一样的表。
truncate table 表名 [where 条件];
-- 修改记录
-- 注意事项：注意：1. 如果不加任何条件，则会将表中所有记录全部修改。
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
级联更新与级联删除
-- mysql对有些约束的修改比较麻烦，所以我们可以先删除，再添加
alter table t_student drop foreign key fk_classes_id;
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade;
-- mysql对有些约束的修改时不支持的，所以我们可以先删除，再添加
alter table t_student drop foreign key fk_classes_id;
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on delete cascade;
delete from t_classes where classes_id = 20;
DQL命令（查询表中的记录）
演示数据的表结构:
简单查询
-- 查询员工姓名
select ename from emp;
-- 查询表中数据
select * from 表名称;
-- 列出员工的编号，姓名和年薪
select empno, ename, sal*12 from emp;
-- 为字段添加别名，as也可以省略
select ename as '姓名' from emp;
条件查询
- 条件查询需要用到where语句，where必须放到from语句表的后面
-- 查询薪水为5000的员工
select empno, ename, sal from emp where sal=5000;
-- 查询job为MANAGER的员工，注意引号
select empno, ename from emp where job = 'manager';
-- 查询薪水不等于5000的员工，建议使用第一种写法
select empno, ename, sal from emp where sal <> 5000;
select empno, ename, sal from emp where sal != 5000;
-- 查询薪水为1600到3000的员工,between … and …，它是包含最大值和最小值的
select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;select empno, ename, sal from emp where sal between 1600 and 3000;
-- 查询津贴为空的员工，null类型比较特殊，必须使用is来比较
select * from emp where comm is null;
-- 工作岗位为MANAGER,薪水大于2500的员工
select * from emp where job='MANAGER' and sal > 2500;
-- 查询出job为manager或者job为salesman的员工
select * from emp where job='MANAGER' or job='SALESMAN';
-- 查询薪水大于1800，并且部门代码为20或30的员工
-- 错误的写法：原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的
select * from emp where sal > 1800 and deptno = 20 or deptno = 30;
-- 正确写法：尽量采用括号
select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
-- 查询出job为manager或者job为salesman的员工
select * from emp where job in ('manager','salesman');
-- 查询出薪水包含1600和薪水包含3000的员工
select * from emp where sal in(1600, 3000);
-- 查询出薪水不包含1600和薪水不包含3000的员工
select * from emp where sal <> 1600 and sal <> 3000;select * from emp where not (sal = 1600 or sal = 3000);
select * from emp where sal not in (1600, 3000);
-- 查询出津贴不为null的所有员工
select * from emp where comm is not null;
-- Like可以实现模糊查询，like支持%和下划线匹配，%匹配任意字符出现的个数，下划线只匹配一个字符
-- 查询姓名以M开头所有的员工
select * from emp where ename like 'M%';
-- 查询姓名以N结尾的所有的员工
select * from emp where ename like '%N';
-- 查询姓名中包含O的所有的员工
select * from emp where ename like '%O%';
-- 查询姓名中第二个字符为A的所有员工
select * from emp where ename like '_A%';
排序查询
-
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔
-
order by默认采用升序
-
如果存在where子句那么order by必须放到where语句的后面
-- 按照薪水由小到大排序(系统默认由小到大)
select * from emp order by sal;
-- 取得job为MANAGER的员工，按照薪水由小到大排序
select * from emp where job='MANAGER' order by sal;
-- 按照多个字段排序，如：首先按照job排序，再按照sal排序
select * from emp order by job,sal;
-- 手动指定按照薪水由大到小排序
select * from emp order by sal desc;
-- 按照job和薪水倒序，多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序
select * from emp order by job desc, sal desc;
函数查询
-
分组函数自动忽略空值，不需要手动的加where条件排除空值。
-
分组函数不能直接使用在where关键字后面
-- 取得所有的员工数，Count(*)表示取得所有记录，忽略null，为null的值也会取得
select count(*) from emp;
-- 取得津贴不为null员工数
select count(comm) from emp;
-- 取得工作岗位的个数，工作岗位有重复需要去除重复
select count(distinct job ) from emp;
-- 取得薪水的合计,null会被忽略
select sum(sal) from emp;
-- 取得薪水的合计（sal+comm）
-- 错误写法：原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0
select sum(sal+comm) from emp;
-- 正确写法
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 max(emp.HIREDATE) from emp;
-- 取得最低薪水
select min(sal) from emp;
-- 可以将这些聚合函数都放到select中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
分组查询
-
分组查询主要涉及到两个子句，分别是：group by和having。
-
order by必须放到group by后面。
-
如果想对分组数据再进行过滤需要使用having子句。
-
分组函数的执行顺序：根据条件查询数据 =》分组 =》采用having过滤 =》取得正确的数据
-- 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;
-- 按照工作岗位和部门编码分组，取得的工资合计
-- 在Oracle数据库中无法执行，执行报错。在Mysql数据库中可以执行，但是执行结果矛盾。
-- 原因：在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。
select job,deptno,sum(sal) from emp group by job,deptno;
-- 取得每个岗位的平均工资大于2000
select job, avg(sal) from emp group by job having avg(sal) >2000;
连接查询
- 也可以叫跨表查询，需要关联多个表进行查询
-- 显示每个员工信息，并显示所属的部门名称
-- 错误语句：输出了56条数据，其实就是两个表记录的成绩，这种情况我们称为：“笛卡儿乘积”。原因：没有指定连接条件
SELECT emp.ENAME,dept.DNAME from emp,dept;
-- 正确语句，这种查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）
SELECT emp.ENAME,dept.DNAME from emp,dept where emp.DEPTNO = dept.DEPTNO;
-- 查询员工姓名和所属领导的姓名
-- 这种连接查询叫"自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，
-- 如以下示例：第一个表emp e代码了员工表，emp m代表了领导表，相当于员工表和部门表一样
-- 注意：连接条件是e.MGR = m.EMPNO
SELECT e.ENAME as '员工姓名',m.ENAME as '领导姓名' from emp e,emp m where e.MGR = m.EMPNO;
-- （内连接）显示薪水大于2000的员工信息，并显示所属的部门名称，inner关键字可加可不加
select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;
-- （外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
-- 这种写法也叫右连接：以右边的表为基础，不管满不满足条件都会显示，左边满足条件的会显示，不满足条件的不显示
select emp.ENAME,emp.DEPTNO,dept.DNAME from emp right join dept on emp.DEPTNO = dept.DEPTNO;
select emp.ENAME,emp.DEPTNO,dept.DNAME from emp right outer join dept on emp.DEPTNO = dept.DEPTNO;
-- 左连接：以左边的表为基础，不管满不满足条件都显示，右边的表需要满足条件才会显示，不满足条件的不显示
-- 左连接能完成的功能右连接一定可以完成
SELECT emp.ENAME,emp.DEPTNO,dept.DNAME from dept LEFT JOIN emp on emp.DEPTNO = dept.DEPTNO;
SELECT emp.ENAME,emp.DEPTNO,dept.DNAME from dept LEFT outer JOIN emp on emp.DEPTNO = dept.DEPTNO;
子查询
-
子查询就是嵌套的select语句，可以理解为子查询是一张表。
-
在where语句中使用子查询，也就是在where语句中加入select语句。
-
在from语句中使用子查询，可以将该子查询看做一张表。在select语句中使用子查询
-- 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
-- 1、首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;
-- 2、查询员工编号包含管理者编号的
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);
-- 查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水
-- 1、 取得平均薪水
select avg(sal) from emp;
-- 2、 取得大于平均薪水的员工
select empno, ename, sal from emp where sal > (select avg(sal) from emp);
-- 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
-- 首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;
-- 将以上查询作为一张表，放到from语句的后面
SELECT e.EMPNO,e.ENAME from
(select distinct mgr from emp where mgr is not null) m,emp e
WHERE m.mgr = e.EMPNO;
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 deptno, avg(sal) from emp group by deptno;
-- 查询薪水等级表
SELECT * from salgrade;
-- 将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级
SELECT a.deptno,a.avg_sal,salgrade.GRADE FROM
(select emp.DEPTNO, avg(sal) as avg_sal from emp group by deptno) a
join salgrade
on a.avg_sal between salgrade.LOSAL and salgrade.HISAL;
-- 查询员工信息，并显示出员工所属的部门名称
-- 第一种做法，将员工表和部门表连接
SELECT emp.ENAME,emp.DEPTNO,dept.DNAME from emp,dept where emp.DEPTNO = dept.DEPTNO;
-- 第二种做法，在select语句中再次嵌套select语句完成部分名称的查询
SELECT emp.ENAME,emp.DEPTNO,(SELECT dept.DNAME from dept WHERE emp.DEPTNO = dept.DEPTNO) as 部门名称 from emp;
union查询
- 合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。
-- 查询job包含MANAGER和包含SALESMAN的员工
-- 第一种方式
SELECT emp.ENAME,emp.JOB from emp where (emp.JOB = 'manager') or (emp.job = 'salesman');
-- 使用union合并
select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN';
limit分页查询
-
mySql提供了limit ，主要用于提取前几条或者中间某几行数据。
-
select * from 表名 limit m,n =》其中m是指记录开始的index，从0开始，表示第一条记录，n是指从第m+1条开始，取n条。limit 2,4 =》即取出第3条至第6条，4条记录
-
limit 是一个MySQL"方言"
-- 取得前5条数据
SELECT * from emp limit 0,5;
-- 0可以省略
select * from emp limit 5;
-- 从第二条开始取两条数据
SELECT * from emp limit 1,2;
-- 取得薪水最高的前5名
SELECT * from emp ORDER BY emp.SAL desc LIMIT 0,5;
注意事项
完整的select语句格式：
select 字段 from 表名 where ……. group by ……… having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现) order by ………
执行顺序：
原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。
-
首先执行where语句过滤原始数据
-
执行group by进行分组
-
执行having对分组数据进行操作
-
执行select选出数据
-
执行order by排序
大小写问题：
MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写，结果再 想要将数据库导出放到linux服务器中使用时就出错了。因为在linux下表名区分大小写而找不到表，查了很多都是说在linux下更改MySQL的设置使其也不区分大小写，但是有没有办法反过来让windows 下大小写敏感呢。其实方法是一样的，相应的更改windows中MySQL的设置就行了。
具体操作：
在MySQL的配置文件my.ini中增加一行：
lower_case_table_names = 0
其中 0：区分大小写，1：不区分大小写
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的； MySQL在Windows下都不区分大小写
存储引擎
数据库中的各表均被（在创建表时）指定的存储引擎来处理。
服务器可用的引擎依赖于以下因素：
- MySQL的版本
- 服务器在开发时如何被配置
- 启动选项
为了解当前服务器中有哪些存储引擎可用，可使用语句：show engines。
- 在创建表时，可使用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语句：
常用的存储引擎
MyISAM存储引擎：MyISAM存储引擎是MySQL最常用的引擎
它管理的表具有以下特征：
-
使用三个文件表示每个表。
-
灵活的AUTO_INCREMENT字段处理。
-
可被转换为压缩、只读表来节省空间
每个表特征：
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）
memory存储引擎：memory存储引擎非常快，因为它的数据存储在内存中，且行的长度固定。
memory存储引擎以前被称为heap引擎，具有下列特征：
- 在数据库目录内，每个表均以.frm格式的文件表示。
- 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含TEXT或BLOB字段。
InnoDB存储引擎：InnoDB存储引擎是MySQL的缺省引擎
它管理的表具有下列主要特征：
- 每个InnoDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
- 提供全ACID兼容
- 在MySQL服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
选择合适的存储引擎
-
MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
-
如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
-
可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。
事务
事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。
事务具有四个特征（ACID）：
- 原子性（Atomicity）：整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
- 一致性（Consistency）：在事务开始之前与结束之后，数据库都保持一致状态。
- 隔离性(Isolation)：一个事务不会影响其他事务的运行。
- 持久性(Durability)：在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
事务中存在一些概念：
- 事务（Transaction）：一批操作（一组DML）
- 开启事务（Start Transaction）
- 回滚事务（rollback）
- 提交事务（commit）
- set autocommit：禁用或启用事务的自动提交模式
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚。
自动提交模式：自动提交模式用于决定新事务如何及何时启动。
启用自动提交模式：
- 如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。
- 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
- 如果语句执行失败，事务将自动回滚，并取消该语句的结果。
- 在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。
禁用自动提交模式：
- 如果禁用自动提交，事务可以跨越多条语句。
- 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。
- 自动提交模式可以通过服务器变量AUTOCOMMIT来控制
-- 开启/关闭自动提交模式
SET AUTOCOMMIT = OFF； SET AUTOCOMMIT = ON；
-- 或
SET SESSION AUTOCOMMIT = OFF； SET SESSION AUTOCOMMIT = ON；
-- 查看变量状态
show variables like '%auto%';
事务的隔离级别
事务的隔离级别决定了事务之间可见的级别。
脏读取（Dirty Read） ：一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
不可重复读（Non-repeatable Read）：在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
幻像读（Phantom Read）：幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。
四个隔离级别
InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
- 读未提交（READ UMCOMMITTED） ：允许一个事务可以看到其他事务未提交的修改。
- 读已提交（READ COMMITTED） ：允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
- 可重复读（REPEATABLE READ） ：确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。 （银行总账）
- 串行化（SERIALIZABLE） 【序列化】： 将一个事务与其他事务完全地隔离
例如：A可以开启事物,B也可以开启事物
A在事物中执行DML语句时,未提交
B不以执行DML,DQL语句
隔离级别与一致性问题的关系:
设置服务器缺省隔离级别
通过修改配置文件设置：在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。 该选项值可以是：
- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ
- SERIALIZABLE
例如：transaction-isolation = READ-COMMITTED
隔离级别的作用范围
事务隔离级别的作用范围分为两种：
- 全局级：对所有的会话有效，会话级隔离级别为READ COMMITTED =》 SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
- 会话级：只对当前的会话有效，设置全局级隔离级别为READ COMMITTED =》 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；
查看会话级的当前隔离级别
mysql> SELECT @@tx_isolation;
或：
mysql> SELECT @@session.tx_isolation;
查看全局级的当前隔离级别：
mysql> SELECT @@global.tx_isolation;
索引
索引原理：索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是用B+Tree作为索引结构（主键，unique 都会默认的添加索引）
什么时候需要给字段添加索引：索引等同于一本书的目录（主键会自动添加索引，所以尽量根据主键查询效率较高）
- 表中该字段中的数据量庞大
- 经常被检索，经常出现在where子句中的字段
- 经常被DML操作的字段不建议添加索引
如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：
1、create unique index 索引名 on 表名(列名);
create unique index u_ename on emp(ename);
2、alter table 表名 add unique index 索引名 (列名);
create index test_index on emp (sal);
3、查看、使用、删除索引命令
-- 查看索引
show index from emp;
-- 使用索引，注意一定不可以用select * … 可以看到type!=all了，说明使用了索引
explain select sal from emp where sal > 1500;
-- 两条语句是等价的，删除掉table_name中的索引index_name。
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
-- 只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，
ALTER TABLE table_name DROP PRIMARY KEY;
使用索引时：
- 假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出rows = 7行，我们知道不会再有匹配的记录，可以退出了。
- 如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
- 这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。
删除索引后：
- 删除索引后再进行查询，结果得出 rows = 14行，因为它查询不再使用索引，而是执行全盘扫描。
视图
什么是视图？
- 视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
- 视图有时也被成为“虚拟表”。
- 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
相对于从基表中直接获取数据，视图有以下好处：
- 访问数据变得简单
- 可被用来对不同用户显示不同的表的内容
- 用来协助适配表的结构以适应前端现有的应用程序
视图作用：
-
提高检索效率
-
隐藏表的实现细节【面向视图检索】
创建视图
-- 例如：查询员工的姓名，部门，工资入职信息等信息
select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;
-- 为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题-- 注意mysql不支持子查询创建视图
create view v_dept_emp as
select ename,dname,sal,hiredate,e.deptno from emp e,dept d where
e.deptno = e.deptno and e.deptno = 10;
-- 添加视图 示例2-- 查询每个部门的平均薪资，要求显示部门编号、平均薪资、薪资等级--
create view v_dept_avg_sal_grade as
select a.deptno, a.avg_sal, b.grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b
where a.avg_sal between b.losal and b.hisal;
-- 修改视图
alter view v_dept_emp as
select ename,dname,sal,hiredate,e.deptno from emp e,dept d
where e.deptno = 20;
-- 删除视图
drop view if exists v_dept_emp;
数据库设计的三范式
三大范式：
- 第一范式：
- 数据库表中不能出现重复记录，每个字段是原子性的不能再分
| 学生编号 | 学生姓名 | 联系方式 |
|---|---|---|
| 1001 | 张三 | zs@gmail.com,1359999999 |
| 1002 | 李四 | ls@gmail.com,13699999999 |
| 1001 | 王五 | ww@163.net,13488888888 |
- 每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示
- 关于列可不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。
- 第二范式
- 第二范式是建立在第一范式基础上的，要求所有非主键字段完全依赖主键，不能产生部分依赖
- 如果产生了部分依赖，那么此表会出现大量的冗余记录。
例如：学生表中，每个学生对应多个老师，如果把学生信息和对应老师的信息记录在一张表中，同一个学生会产生多条冗余记录。此时需要使用外键关联主键，老师信息和学生信息分别设计一个表来存储，将学生信息的id关联老师信息的id，此时由原来的一张表变为三张表就不会产生冗余记录了。
- 第三范式
- 第三范式是建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）
例如：在一张student 表中，学生表包含学生id、name、class_id、class_name字段，由于班级名称依赖于班级编号，班级编号又依赖于学生编号，所以在这张表中，存在着冗余字段，我们需要将这个冗余字段抽取出来单独成立一张表。将这张表变为两张表，一张表记录学生的id、name、class_id，另一张表记录班级编号、班级名称。
三范式总结
- 第一范式：有主键，具有原子性，字段不可分割
- 第二范式：完全依赖，没有部分依赖
- 第三范式：没有传递依赖
数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求
一对一设计，有两种设计方案：
- 第一种设计方案：主键共享
- 第二种设计方案：外键唯一
典型的一对多的设计：
- 一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键
- 第三范式的例子
典型的多对多的设计：
- 第二范式中的例子
1752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
