MySQL基础

数据库管理系统

SQL语句分类:DQL语句(数据查询语言,select)、DML语句(数据库操作语言,insert、delete、update)、DDL语句(数据库定义语言，create、drop、alter)、TCL语句(事务控制语句，commit、rollback)。

命令行导入SQL

creata database bjpowernode;
use bjpowenode;
source bjpowernode.sql
desc dept;(表描述)

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
 
CREATE TABLE DEPT
	(DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13) ,
	primary key (DEPTNO)
	);
CREATE TABLE EMP
	(EMPNO int(4) not null ,
	ENAME VARCHAR(10) ,
	JOB VARCHAR(9) ,
	MGR INT(4) ,
	HIREDATE DATE DEFAULT NULL ,
	SAL DOUBLE(7,2) ,
	COMM DOUBLE(7,2) ,
	primary key (EMPNO) ,
	DEPTNO INT(2)
	);
CREATE TABLE SALGRADE
	(GRADE INT ,
	LOSAL INT ,
	HISAL INT 
	);
	
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YOURK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
 
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7396, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
commit;
 
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
5, 3001, 9999);
commit;

数据库操作

跨表查询&子查询&杂七杂八(持续更新中)

说明:因为有一些操作比较简单就不做详细描述，主要记录比较常用的。
跨表查询:年代分类(SQL92、SQL99)、连接方式分类(内连接[等值连接、非等值连接、自连接]、外连接[左外连接、右外连接]、全连接)
SQL92:[select ename, dname from emp as e, dept as d where e.deptno = d.deptno]
SQL99:[select e.ename, d.dname from emp as e join dept as d on e.deptno = d.deptno]
内连->等值连接:[select e.ename, d.dname from emp as e inner join dept as d on e.deptno = d.deptno][这里inner写不写都可以，为了程序的可读性可以加上]
内连->非等值连接:[select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal][查询员工薪水所对应的薪水等级:显示员工名称、薪水、薪水等级]
内连->自连接:[select a.ename, b.ename as leadername from emp a join emp b on a.mgr = b.empno][查询员工所对应的领导名称:显示员工姓名和领导姓名]
外连接->右外连接:[select e.ename, d.dname from emp e right outer join dept d on e.deptno = d.deptno]
外连接->左外连接:[select a.ename, b.ename leadername from emp a left outer join emp b on a.mgr = b.empno]
多表查询(查询员工的部门名称，员工的领导名称和薪水等级)[select d.dname, e.ename, b.ename as leadername, s.grade from emp e join dept d on e.deptno = d.deptno join emp b on e.mgr = b.empno join salgrade s on e.sal between s.losal and s.hisal]
[select ename, sal from emp where sal > (select avg(sal) as avgsal from emp)][找出薪水比公司平均薪水高的员工，要求显示员工名和薪水]
(from关键字后的嵌套查询，找出每个部门的平均薪水，并且要求显示平均薪水的薪水等级)[select t.avgsal,s.grade from (select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno) t join salgrade s on t.avgsal between s.losal and s.hisal]
(合并查询Union，查询工作岗位为manager和salesman员工)[select ename, job from emp where job = ‘manager’ or job = ‘salesman’][select ename, job from emp where in(‘manager’,‘salesman’)][select ename, job from emp where job = ‘manager’ union select ename, job from emp where job = ‘salesman’]
表约束字段唯一性(不为空不重复)[create table t_user (id int(4), name varchar(32) not null unique);]

存储引擎

基本介绍

存储引擎是:Mysql特有的，其他数据库没有的。
存储引擎的本质:1.通过采用不同的技术将数据存储在文件或内存中。2.每一种技术都有不同的存储机制，不同的存储机制提供不同的功能和能力。3.通过选择不同技术，可以获得额外的速度或功能，改善我们的应用。
可以通过[show create table emp][show table status like emp\G]来查看数据表使用的存储引擎。
可以通过[show show engines\G]数据库支持哪些存储引擎，并且默认支持的存储引擎。

常用的存储引擎

MyISAM存储引擎
MyISAM存储引擎是MySQL数据库最常使用的；
使用三个文件表示每个表:
a)、格式文件-存储表的结构(mytable.frm)
b)、数据文件-存储表的数据(mytable.MYD)
c)、索引文件-存储表的索引(mytable.MYI)
可转换为压缩、只读表来节省空间

InnoDB存储引擎
InnoDB存储引擎是MySQL数据库的缺省引擎；
它管理的表具有以下特征:
a)、每个InnoDB表在数据库目录中以.frm格式文件表示。
b)、InnoDB表空间tablespace被用于存储表的内容。
c)、提供一组用来记录事务性活动的日志文件。
d)、用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理。
e)、提供全部的ACID兼容。
f)、在MySQL服务器奔溃后提供自动恢复。
g)、多版本(MVCC)和行级锁定
h)、支持外键及引用的完整性，包括级联更新和删除。

MEMORY存储引擎
使用MEMORY存储引擎的表，因为数据库存储在内存中，且行的长度固定，所以使得MEMORY存储引擎非常快；
MEMORY存储引擎管理的表具有下列特征:
a)、在数据库目录内，每个表均以.frm格式文件表示。
b)、表数据及索引被存储在内存中。
c)、表级锁机制。
d)、字段属性不能包括TEXT或BLOB字段。
MEMORY存储引擎以前被称为HEAP引擎。

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

索引

基本介绍

索引对应的单词是index，索引的作用:相当于一本字段目录，提高程序的检索/查询效率；表中每个字段都可以添加索引。默认情况下主键自动添加索引，在实际项目开发中能够通过主键查询的尽量通过主键查询，效率较高。索引和表相同，都是一个对象，表存储在硬盘中，索引是表的一部分，所以也是存储在硬盘文件中。
在MySQL数据库中表的检索方式有两种:
1)、第一种:全表扫描(效率低)
a)、举例:查询ename = ‘KING’
假设有一张表:emp员工表，select * from emp where ename = ‘KING’；若ename没有添加索引，那么通过ename过滤数据的时候，ename字段会全表扫描；假设有一张表，dept部门表，select * from dept where dname = ‘ACCOUNTTING’；若dname没有添加索引，那么通过dname过滤数据的时候，dname字段会全表扫描(这里有点重复，但是为了表明索引的重要性，还是跟着实际作者再三强调一下，假装很努力的样子~)。
2)、第二种:通过索引检索(提高查询效率)。

什么情况下适合给表添加索引

1)、该字段数据量庞大。
2)、该字段很少的DML操作(由于索引也需要维护，DML操作的话比较影响索引效率)。
2)、该字段经常出现在where条件中。

基本操作

创建索引:语法结构[create index 索引名 on 表名(列名)]、[create unique index 索引名 on 表名(列名)]。
注:添加unique表示在该表中的的该列添加一个唯一性约束。示例:[create index dept_dname_index on dept(dname)]。

查看索引:语法结构[show index from 表名]，示例:[show index from dept]。

删除索引:语法结构[drop index 索引名 on 表名]，示例:[drop index dept_dname_index on dept]

视图

基本介绍

视图对应的英文单词是view，视图在数据库管理系统中也是一个对象，也是以文件形式存在的。视图也是对应一个查询结果，只是从不同的角度查看。

基本操作

创建视图:语法结构[create view 视图名称 as 查询语句]。
查询视图:[show create view 视图名称]。
删除视图:[drop view 视图名称]。

视图的作用

1、隐藏表的细节。例子:[create view myview as select empno as a, ename as b from emp][select * from myview]，字段名称为a、b。
2、提高检索的效率。例子:[create view myview_find_name as select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno][select * from myview_find_name]。

数据库设计三范式

第一范式:主键、字段不能再分

定义:要求有主键，数据库中不能出现重复记录，每一个字段是原子性不能再分。
遵循以下规则:
1、每一行必须唯一，也就是没一张表必须有主键。
2、主键通常采用数值型或定长字符串表示。
3、关于列不可再分，应根据具体情况来定。如联系方式，为了开发上的便利可能采用一个字段。

第二范式:非主键字段完全依赖主键

定义:第二范式是建立在第一范式基础上，要求数据库中所有非主键字段完全依赖主键，不能产生部分依赖。(严格意义上来说:尽量不要使用联合主键)。
结论:可以用"多对多"的设计解决如上问题。

第三范式:非主键字段不能产生传递依赖与主键字段

定义:建立在第二范式基础上，要求非主键字段不能产生传递依赖于主键字段。
结论:可以用"一对多"的设计解决如上问题。

事务Transaction

基本介绍

事务是什么:1、一个最小的不可再分的工作单元。2、通常一个事务对应一个完整的业务。3、而一个完整的业务需要批量的DML(insert、update、delete)语句共同完成。4、事务只和DML语句有关系，或者说只有DML语句才有事务。5、以上所述的批量DML语句共有多少DML语句，这个和业务逻辑有关系，业务逻辑不同DML语句不同。

典型案例

银行转账业务:是一个完整的业务，最小的单元，不可再分，也就是说银行转账业务是一个完整的事务。
示例:账户转账。t_act账户表:actno、balance

actno	balance
act-001	50000.0
act-002	10000.0

act-001转10000.0给act-002，操作如下:
update t_act set balance = 40000.0 where actno = ‘act-001’
update t_act set balance = 20000.0 where actno = ‘act-002’

分析:
1、以上两条DML语句必须同时成功或者同时失败，因为它为最小业务单元，不可拆分。
2、当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下，这个记录是在内存中完成的。
3、当第二条DML语句成功之后，和底层数据库文件中的数据同步完成。
4、若第二条语句执行失败，将清空所有的历史操作记录。
结论:要完成以上功能，必须借助事务transaction。

事务具有的四个特性ACID

1、原子性(Atomicity):事务是最小的单元，不可再分。
2、一致性(Consistency):事务要求所有的DML语句操作的时候，必须保证同时成功或同时失败。
3、隔离性(Isolation):一个事务不会影响其他事务的运行。
4、持久性(Durability):在事务完成之后，该事务对数据库所做的更改将持久的保存在数据库中，并不会被回滚。

事务基本概念

开启事务:start transaction
结束事务:end transaction
提交事务:commit transaction
回滚事务:rollback transaction

MySQL事务的提交和回滚的演示

MySQL默认事务:自动提交[show variables like ‘%commit%’]
在MySQL数据库管理系统中，默认情况下，事务是自动提交的；也就是说，只要执行一条DML语句，就开启的事务，并且提交了事务。
1、事务成功用法:start transaction、commit;
第一步:start transaction，手动开启事务；
第二步:DML语句，执行批量DML语句；
第三步:commit，手动提交事务[事务成功结束];
2、回滚提交方法:start transaction、rollback;
第一步:start transaction，手动开启事务；
第二步:DML语句，执行批量DML语句；
第三步:rollback，手动回滚事务[事务失败结束];

事务和存储过程的区别

这两个概念可以说是两个范畴的概念，事务是数据库操作范畴的概念，保证数据库数据的完整性和一致性；存储过程是高级程序设计中模块化设计思想的重要内容。
事务是包含一组修改（插入、更新和删除）的工作的逻辑单位。事务的操作要么被保存到数据库commit，要么回滚rollback，事务中的所有修改要么全部提交，要么什么也不做，这样保证了数据库中数据的完整性和一致性。
数据库操作中为了完成一个完整的数据库任务，从而引进高级程序的设计要素。过程就是高级程序设计语言中的模块概念，将一些内部联系的命令组成一个个过程，通过参数在过程间传递数据来完成一个完整的数据库任务，这就是模块化设计思想的重要内容。
有的时候可以把一个过程看作一个事务，但是有的过程运行过程中因为满足某些条件而从过程中跳出，这时就不能把过程看作事务；反之，一个事务可能是一个过程，也可能一个事务中包含对一个或多个过程的调用。
二者概念所述的范畴不同，在数据库中，是相互联系相互区别的；而且两者都是具体的，不是抽象的，因为都可以拿出一段代码，说它是事务或是存储过程。

事务的隔离级别

隔离性有四个隔离级别:
1、read uncommitted 读未提交
2、read committed 读已提交
3、repeatable read 可重复读
4、serializable 串行化