MySQL

1. sql，DB，DBMS的关系

　　DB: DataBase,数据库，实际上是在硬盘上以文件的形式存在。

　　DBMS: DataBase Management System, 数据库管理系统，常见的有MySQL,Oracle等。

　　SQL: 结构化查询语言，sql语句在执行时也会先进行编译，编译工作由DBMS完成。

　　总的来说，就是DBMS执行SQL语句，通过执行SQL语句来操作DB当中的数据。

2. 表（table）

　　表是数据库的基本组成单元，所有的数据都是以表格的形式组织，因为表的可读性强。

　　一个表包括了行和列（column）,列又叫做字段，每个字段包括了字段名，数据类型，相关约束。

　　2.1 数据库3范式

　　　　第一范式：任何一张表都应该有主键，并且每个字段不可再分。

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

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

　　2.2表的创建

　　　　语法：

　　　　create table 表名(

　　　　字段名1   数据类型   约束,

　　　　字段名1   数据类型   约束,

　　　　字段名1   数据类型   约束,

　　　　...

　　　　);

　　　　

CREATE TABLE `t_student` (
  `stuId` bigint,
  `stuName` varchar(255),
  `sex` char(1),
  `classNo` varchar(255)L,
  `birth` char(10)
) ;

　　2.3MySQL中常见的数据类型

　　　　int             整数型（对应java中的int）

　　　　bigint　　 长整型（对应java中的long）

　　　　float　　   浮点型（对应java中的 double  float）

　　　　char　　   定长字符串（string）

　　　　varchar     可变字符串（stringbuffer/stringbuilder）

　　　　date　　   日期类型（java.sql.Date）

　　　　BLOB        二进制大对象（储存图片，视频等流媒体信息）

　　CLOB　　字符大对象（储存较大的文本）

　　2.4 表的删除　　　　

DROP TABLE t_student1;

　　2.5 约束

　　　　在创建表的时候可以为字段添加相应的约束，来保证数据的合法性，完整性，有效性。

　　　　常见的约束：

　　　　　　   非空约束（not null） 不能为空

CREATE TABLE t_user(
    id INT,
    uname VARCHAR(255) not NULL
);

　　　　　　   唯一约束（unique）  不能重复，但可以为null

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
    id INT,
    uname VARCHAR(255) UNIQUE
);
uname这一列为唯一约束 【列级约束】

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
    id INT,
    uname VARCHAR(255),
    UNIQUE(id,uname)
);
id+uname 联合起来为唯一约束  【表级约束】

 

　　　　　　   主键约束（primary key） 既不能为空，也不能重复

　　　　　　　　　一张表只能有1个主键，该字段的每个值都是其对应的一条数据的的唯一标识，就像每个人的身份证号码一样

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
    id INT PRIMARY KEY,
    uname VARCHAR(255),
    sex    CHAR(1)
);

　　　　　　      根据字段数量划分：

　　　　　　　　   单一主键：推荐

　　　　　　　　   复合主键：多个列组合起来组成的，不推荐

　　　　　　　  根据主键性质划分：

　　　　　　　　   自然主键：和业务没关系的单独的一个字段，推荐

　　　　　　　　   业务主键：主键值与业务挂钩，不推荐，因为业务改变可能需要改变该列的值，可能会导致重复，而主键是不能重复的

　　　　　　　　主键自增：

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
    id INT PRIMARY KEY auto_increment ,
    uname VARCHAR(255),
    sex    CHAR(1)
);

　　　　　　   外键约束（foreign key）

3. sql语句

　　sql语句的分类：

1. DQL:数据查询语言，所有Select语句都是查询语句。
2. DML:数据操作语言，insert  delete  update，对数据进行增删改。
3. DDL:数据定义语言，create  drop  alter，对表结构的增删改。
4. TCL:事务控制语言，commit提交事务，rollback回滚事务。
5. DCL:数据控制语言，grant授权，revoke取消授权。

4. 数据库基本操作

　　4.1 常用命令

1. 1. 登录MySQL数据管理系统， dos命令窗口：mysql -u名称 -p密码
   2. 查看有哪些数据库，show databases;
   3. 创建数据库， create database 数据库名;
   4. 使用数据库，use 数据库名;
   5. 查看当前使用的数据库有哪些表，show tables;
   6. 初始化数据，导入以.sql结尾的sql脚本， source 脚本的路径；
   7. 查看表结构， desc 表名； 
   8. 删除数据库，drop database 数据库名;

　　4.2 简单的查询语句

  　　　　附带表：

　　　　  emp表：

　

　　　　 dept表：

　　

　　　　salgrade表：

　　

        　　语法：

　　　　　　select 字段名1,字段名2...  from 表名;

　　　　　　注意：1.一条sql语句以";"结尾

　　　　　　　　　2.sql语句不区分大小写

　　　　　　　　　3.字段可以参与数学运算

SELECT EMPNAME, SAL*12 FROM emp;

　　　　　　　　　4.重命名(as 可以省略)　　　　

SELECT EMPNAME, SAL*12 AS '年薪' FROM emp;

　　　　　　  　　 5.sql语句中的字符串最好用单引号

　　　　　　　　　6.查询所有字段(实际工作中不建议，效率太低)

select * from emp;

　　4.3 条件查询

　　　　条件查询常用的符号和关键字：

　　　　<    >  =  <=   >=  !=   and  or  in between...and... like（模糊查询中%代表匹配多个字符，_代表匹配一个字符）  is null  \(转义字符)

 /*找出员工编号为7902的员工姓名和工资*/
  SELECT EMPNAME,SAL FROM emp WHERE EMPNO=7902;
  /*找出工资小于的员工姓名和工资*/
  SELECT EMPNAME,SAL FROM emp WHERE SAL<3000;
  /*找出工资大于3000的员工姓名和工资*/
  SELECT EMPNAME,SAL FROM emp WHERE SAL>3000;
  /*找出工资在1000到3000之间的员工姓名和工资*/
  SELECT EMPNAME,SAL FROM emp WHERE SAL BETWEEN 1000 AND 3000;
  SELECT EMPNAME,SAL FROM emp WHERE SAL>=1000 AND SAL<=3000;
 /*找出工资为1000或者3000的员工姓名和工资*/
 SELECT EMPNAME,SAL FROM emp WHERE SAL=1000 OR SAL=3000;
 SELECT EMPNAME,SAL FROM emp WHERE SAL IN (1000,3000);
 /*找出员工姓名中有张字的员工姓名和工资*/
 SELECT EMPNAME,SAL FROM emp WHERE EMPNAME LIKE '%张%';
/*找出员工姓名第二个字是飞的员工姓名和工资*/
 SELECT EMPNAME,SAL FROM emp WHERE EMPNAME LIKE '%_飞%';
 /*找出员工姓名中有_的员工姓名和工资(转义符号\把_转为普通字符)*/
 SELECT EMPNAME,SAL FROM emp WHERE EMPNAME LIKE '%\_飞%';
 /*找出员工补助为null的员工*/
SELECT * FROM emp WHERE COM is NULL;
 /*找出员工补助不为null的员工*/
SELECT * FROM emp WHERE COM is NOT NULL;

 

　　4.4 排序（升序或降序）

　　　　使用order by 子句，ASC表示升序(默认)，DESC表示降序。可以根据多个字段排，越靠近order by 的字段权重越大。

1/*按照工资升序（由低到高）查出员工姓名和工资*/
 SELECT EMPNAME,SAL FROM emp ORDER BY SAL ASC;
 /*按照工资降序（由高到底）查出员工姓名和工资*/
 SELECT EMPNAME,SAL FROM emp ORDER BY SAL DESC;
 /*按照工资降序（由高到底）查出员工姓名和工资,如果工资相同时按照名字的升序排列*/
 SELECT EMPNAME,SAL FROM emp ORDER BY SAL DESC,EMPNAME ASC;

 

　　4.5 分组函数

　　　　count（计数） sum（求和） max（最大）min（最小） avg（平均） 

　　　　注意：1.分组函数自动忽略null，而且数据库中null如果参与了运算那么结果一定为null。

　　　　　　　2. 分组函数不可以直接使用在where子句当中。

/*求出员工工资总和*/
SELECT SUM(SAL) FROM emp ;
/*求出员工最高工资*/
SELECT MAX(SAL) FROM emp ;
/*求出员工最低工资*/
SELECT MIN(SAL) FROM emp ;
/*求出员工平均工资*/
SELECT AVG(SAL) FROM emp ;
/*求出员工总人数（注意该列中不能有null）*/
SELECT COUNT(SAL) FROM emp ;

 

 　　4.6 分组查询group by 和 having

　　　　group by : 对某个字段或多个字段分组。

　　　　having : 对分组过后的数据进行过滤。

　　　　注意：一条语句中有group by,那么select 后面只能跟分组函数和参与分组的字段。

/* 查询每个工作岗位的平均工资*/
SELECT JOB,AVG(sal) FROM emp GROUP BY JOB;
/* 找出每个部门不同工作岗位的最高工资*/
SELECT DEPTNO,JOB,MAX(sal) FROM emp GROUP BY DEPTNO,JOB;

/*找出每个部门的最高工资，并显示出薪资大于1000的数据*/
SELECT DEPTNO,MAX(SAL) FROM emp GROUP BY DEPTNO HAVING MAX(SAL)> 1000;/*不推荐，效率太低*/

SELECT DEPTNO,MAX(SAL) FROM emp WHERE SAL>1000 GROUP BY DEPTNO;/*效率较高，能使用where的尽量用where*/

/*找出每个部门的平均工资，并显示出薪资大于1300的数据*/
SELECT DEPTNO,AVG( SAL) FROM emp  GROUP BY DEPTNO HAVING AVG( SAL)>1300; 

　　4.7 SQL语句的执行顺序

SELECT   5
    ...
FROM     1
    ...
WHERE    2
    ...
GROUP BY 3
    ...
HAVING   4
    ...
ORDER BY 6
    ...
limit　　 7
　　 ...

 　　4.8 查询结果集去重

　　　　distinct  只能放在所有查询字段的最前面

/*查询出员工表中有哪几种工作岗位*/
SELECT DISTINCT JOB FROM emp;
/*查询出员工表中工作岗位有几种*/
SELECT COUNT(DISTINCT JOB)FROM emp;

 　　4.9 向表中插入数据

　　　　　　语法：  insert into 表名（字段名1,字段名2,字段名3,...） values（值1,值2,值3,...）;

　　　　　　注意：值要和字段相匹配

　　　　　　

INSERT INTO t_student(stuId,stuName,sex,classNo,birth) VALUES(10729702,'小红','女','1132','2002-11-03');

 　　　　　　下面这种写法插入的数据就要和表的列顺序相一致

INSERT INTO t_student VALUES(10729703,'小花','女','1132','2002-12-03');

　　　　　　一次插入多条数据

INSERT INTO t_student VALUES(10729703,'小花1','女','1132','2002-12-03'),(10729703,'小花2','女','1132','2002-12-03');

　　4.10 表的复制及批量插入

　　　　　　复制表（把查询出来的数据创建为新的表）

CREATE TABLE t_student1 AS SELECT * FROM t_student;

　　　　　　把查询结果插入表中（查询结果必须和要插入的表的数据相对应）

INSERT INTO t_student1 SELECT * FROM t_student;

　　4.11 修改数据

　　　　　　语法：update 表名 set 字段名1=值1,字段名2=值2,...where  条件；（不添加条件的话，整张表都会更新）

UPDATE t_student1 SET sex='男',classNo='1111' WHERE stuName='小花2';

　　4.12 删除数据

　　　　　　语法：delete from 表名 where 条件；（没有条件的话，删除所有数据）

DELETE FROM t_student1 WHERE sex='男';

　　　　　　删除大表数据：

　　　　　　truncate table 表名;(这种删除了之后，数据无法恢复)

5 连接查询

　　连接查询： 多张表联合起来一起查询

　　5.1  连接查询分类

　　　　按照表的连接方式分：

　　　　　　内连接：假设表A和表B进行连接，使用内连接的话，凡是表A和表B能匹配上的数据看都查询出来，两张表是平等的，没有主副之分。

　　　　　　　　select ***,*** from A inner(可省略) join B on 连接条件  where ***

　　　　　　　　等值连接

　　　　　　　　非等值连接

　　　　　　　　自连接

　　　　　　外连接：假设表A和表B进行连接，使用外连接的话，两张表中有一张是主表，另外一张是副表，主要查询的是主表中的数据，捎带查询副表的数据，当副表中的数据没有与主表匹配上时会　　　　　　　　　　　　　　                                   自动模拟出null与之匹配。

　　　　　　　　select ***,*** from A  right/left outer(可省略) join B on 连接条件  where ***

　　　　　　　　左外连接： 左边的表示主表

　　　　　　　　右外连接：右边的表是主表

　　　　全连接：（不怎么使用）

　　5.2  笛卡尔积现象

　　笛卡尔积的出现是在多表进行联合查询的时候会出现的一种情况。

　　比如： 有两张表：员工表emp，里面存有10条数据。部门表dept，里面存有3条数据。需要查询出员工姓名和其部门名称

　　在执行查询语句：SELECT EMPNAME,DEPTNAME FROM emp,dept;  得到的结果会有10*3共30条数据。

　　加上限制条件：  SELECT DISTINCT e.EMPNAME,d.DEPTNAME FROM emp AS e,dept AS d WHERE e.DEPTNO = d.DEPTNO;

　　注意：1.加上限制条件后得到有效的数据，但是在底层还是执行了30次。

　　　　    2.表取别名，为了执行效率更高，可读性更好。

　　5.3 内连接

　　　　5.3.1 等值连接

　　　　　　特点：条件是等量关系

　　　　　　比如：查询员工的姓名和其部门名称

SELECT e.EMPNAME,d.DEPTNAME FROM emp AS e JOIN dept as d ON e.DEPTNO = d.DEPTNO;

　　　　5.3.2 非等值连接

　　　　　　特点：条件是非等量关系

　　　　　　比如：查询员工姓名，工资和工资等级

SELECT e.EMPNAME,e.SAL,s.GRADE FROM emp AS e JOIN salgrade as s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;

　　　　5.3.3 自连接

　　　　　　特点：一张表看做两张表，自己连自己

　　　　　　比如：查询员工姓名和其上级领导的姓名　

SELECT a.EMPNAME as '员工',b.EMPNAME as '领导' FROM emp as a JOIN emp as b on a.MGR = b.EMPNO;

注意：这里查询出来只有9条数据，因为董事长没有上级领导。

　　5.4 外连接

　　　　如：查询员工姓名和其上级领导的姓名　

　　　　左外连接：

SELECT a.EMPNAME AS '员工',b.EMPNAME AS '领导' FROM emp as a LEFT JOIN emp as b ON a.MGR=b.MGR;
注意：这里查询出来是10条数据，因为左边的表是主表。

　　　　右外连接：

SELECT b.EMPNAME AS '员工',a.EMPNAME AS '领导' FROM emp as a RIGHT JOIN  emp as b ON a.MGR=b.MGR;

 　　　　比如：查询出没有员工的部门

SELECT dept.* FROM emp RIGHT JOIN dept ON emp.DEPTNO=dept.DEPTNO WHERE emp.EMPNO is NULL;

 　　5.5  3表及以上的连接查询

　　　　　　比如： 查询出每个员工的部门，工资等级和上级领导

SELECT 
e.EMPNAME as '员工姓名',d.DEPTNAME as '部门',s.GRADE as '工资等级',b.EMPNAME as '上级领导'
FROM emp AS e 
left JOIN dept AS d 
on e.DEPTNO = d.DEPTNO
left JOIN salgrade as s
on e.SAL BETWEEN s.LOSAL AND s.HISAL
left JOIN emp as b
ON e.MGR = b.EMPNO;

　　5.6 子查询

　　　　5.6.1 什么是子查询

　　　　　　select语句中嵌套select语句，嵌套的select语句就叫子查询。可以用在select，from，where后面。

　　　　5.6.2 where后跟子查询

　　　　　　比如：找出高于平均工资的员工信息

SELECT * FROM emp WHERE emp.SAL > (SELECT AVG( SAL) FROM emp);

　　　　5.6.3 from后跟子查询

　　　　　　比如：找出每个部门的平均工资等级

SELECT a.DEPTNO,a.pj,s.GRADE FROM (SELECT DEPTNO,AVG(SAL) as pj FROM emp GROUP BY DEPTNO) as a JOIN salgrade as s ON a.pj BETWEEN s.LOSAL AND s.HISAL;
每条查询语句都可以看成是一张临时表

　　　　5.6.4 select后跟子查询

　　

SELECT emp.EMPNAME,dept.DEPTNAME FROM emp LEFT JOIN dept on emp.DEPTNO = dept.DEPTNO;


SELECT emp.EMPNAME,(SELECT dept.DEPTNAME FROM dept where emp.DEPTNO = dept.DEPTNO) as '部门' FROM emp;
/*这种很少用*/

　　5.7 union

　　　　union 可以把查询结果集相加，但是要注意每个结果集的列数要相同。

　　　　比如：查询出工作岗位是员工和经理的人有哪些

SELECT EMPNAME,JOB FROM emp WHERE JOB='员工' OR JOB='经理';

SELECT EMPNAME,JOB FROM emp WHERE JOB in ('员工','经理');

SELECT EMPNAME,JOB FROM emp where JOB='员工' UNION SELECT EMPNAME,JOB FROM emp where JOB='经理';

 　　5.8 limit及通用分页SQL

　　　　limit是MySQL特有的，其他数据库中没有，不通用。它的作用是取结果集中的部分数据。

　　　　　5.8.1语法机制

　　　　　　limit startindex（起始位置） length（长度）

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

SELECT * FROM emp ORDER BY SAL DESC LIMIT 0,5;
/*limit后跟一个数据时，默认是把0省略掉了*/
SELECT * FROM emp ORDER BY SAL DESC LIMIT 5;

 　　　　5.8.2分页SQL

　　　　　　页数：pageNo   每页显示条数： pageSize

　　　   　　 limit (pageNo-1)*pageSize,pageSize

 6  存储引擎

　　　　不同的存储引擎有不同的存储方式。  show engines \G(可查看当前版本MySQL支持的存储引擎)

　　6.1 InnoDB

　　　　MySQL默认的存储引擎是InnoDB,它是支持事务，可以保证数据的安全，表的结构存储在.frm文件中，数据存储在tablespace这样的表空间中，无法被压缩，无法转换为只读。InnoDB存储引擎在MySQL崩溃之后提供　　　　              自动恢复机制。它还支持级联删除和级联更新。

　　6.2 MyISAM

　　　　它不支持事务，表结构存储在.frm文件中，数据存储在.MYD文件中，索引储存在.MYI文件中，可以被压缩，节省存储空间，而且可以转换为只读表，提高检索效率

7 事务（Transaction）

　　7.1 事务的概念

　　　　一个事务是一个完整的业务逻辑单元，不可再分。MySQL事务默认情况下是自动提交的，就是只要执行的任意一条DML语句就提交一次（start transcation；可以关闭自动提交）

　　　　比如：银行账户转账，重A账户向B账户转账1000元，需要同时执行两条SQL语句：

　　　　update t_act set balance = balance - 1000 where actno = 'act-A';

　　　　update t_act set balance = balance + 1000 where actno = 'act-B';

　　　　以上两条DML语句必须同时成功或失败，不允许一条成功一条失败，这时就需要数据库的事务机制。

　　　　和事务相关的语句只有DML语句，因为事务的存在就是为了保护数据的完整性和安全性。

　　7.2 事务原理

　　　　第一步：开启事务

　　　　第二部：执行SQL语句，这时执行成功会保存为历史记录，还不会去修改文件中的数据。

　　　　第三步：提交事务（commit）或回滚事务（rollback）。

　　7.3 事务的4大特性（ACID）

　　　　A:原子性,事务是最小的工作单位，不可再分.

　　　　C:一致性，事务必须保证多条DML语句同时成功或者同时失败。

　　　　I:隔离性，事务A和事务B之间具有隔离。

　　　　　　事务隔离性存在隔离级别，理论上分为4个隔离级别：

　　　　　　　　第一级别：读未提交（read uncommitted）。对方的事务还未提交，我们可以读取到对方未提交的数据。

　　　　　　　　　　　　　这个会存在脏读现象，表示读到了脏的数据。

　　　　　　　　第二级别：读已提交（read committed）。对方事务提交之后的数据我们可以读到。

　　　　　　　　　　　　　这个解决了脏读现象，但会存在不可重复读现象。

　　　　　　　　第三级别：可重复读(repeatable read)。，对方事务提交了，但是我们读取的还是原来的数据

　　　　　　　　　　　　　这个解决了不可重复读，但会存在幻读。MySQL默认的事务隔离级别是可重复读。

　　　　　　　　第四级别：系列化读（serializable read） 有事务还未提交时，别的事务不能进行。

　　　　　　　　　　　　　这个解决了所有问题，但效率低，事务需要排队。

　　　　　　　　设置事务隔离级别： set global transcation isolation level 隔离级别；

　　　　D:持久性，最终数据必须持久化到硬盘文件中，事务才算成功结束。

8 索引

　　8.1 索引的定义

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

　　　　数据库在查询一张表的时候有两种检索方式：

　　　　　　1.全表扫描

　　　　　　2.根据索引检索（效率高，因为缩小了检索范围）

　　8.2 索引的分类

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

　　　　　　复合索引：几个字段联合起来添加索引

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

　　　　　　唯一索引：添加unique约束的字段也会自动添加索引

　　8.3 创建索引

　　　　　　create index 索引名 on 表名（字段名）; 注意：创建索引之后，索引会自动排序

　　　　　　删除索引： drop index 索引名 on 表名;

　　8.4 什么时候创建索引

　　　　1.数据量庞大

　　　　2.该字段很少有DML操作（因为字段修改了，索引也需要维护）

　　　　3.该字段经常出现在where子句中

　　　　注意：主键和具有unique约束的字段会自动添加索引。

　　　　查看SQL语句的执行计划：explain sql语句; 

　　8.5 索引失效

　　　　模糊查询的时候，索引会失效

9 视图（view）

　　9.1 什么是视图

　　　　视图就是在不同的角度看数据，可以隐藏表的实现细节。对于保密级别高的系统，数据库只会对外提供相关的视图。

　　9.2 创建视图

　　　　create view myview as select语句; 只有DQL语句才能以视图对象的方式创建出来，但可以对视图进行CRUD操作来修改原表的数据。

　　　　删除视图：drop view myview；

10 数据库数据的导入和导出

　　在DOS命令窗口执行：

　　导出：mysqldump mytext（数据库名）>d:\mytext.sql（导出的位置） -uroot -p111（账号密码）；

　　　　   mysqldump mytext（数据库名） 表名>d:mytext.sql（导出的位置） -uroot -p111（账号密码）；

　　导入：create database （数据库名）; use 数据库名; source d:\mytext.sql（导出的位置）