MySQL笔记

1.DB，DBMS与SQL

数据库：
　　 英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。
　　 顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了
　　 具有特定格式的数据。
　　 数据库管理系统：
　　 DataBaseManagement，简称DBMS。
　　 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以
　　 对数据库当中的数据进行增删改查。
　　
　　 常见的数据库管理系统：
　　 MySQL、Oracle、MS SqlServer、DB2、sybase等…
　　 SQL：结构化查询语言
　　 程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL
　　 语句，最终来完成数据库中数据的增删改查操作。
　　
　　 SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，
　　 同时在Oracle中也可以使用，在DB2中也可以使用。
　　
　　 三者之间的关系？
　　 DBMS–执行–> SQL --操作–> DB
　　 mysql数据库启动的时候，这个服务占有的默认端口号是3306，这是大家都知道的事儿。记住。

2.SQL命令

2.1启动与关闭

net start 服务名
net stop 服务名

2.2mysql常用命令

登录：
mysql -u[name] -p[pwd]
退出：
exit
查看数据库：
show databases；
使用数据库：
use [db]
创建数据库：
create datebase [db];
查看表：
show tables;
查看数据库版本号:
select version();
查看当前数据库:
select database();

3.SQL语句

3.1 分类(DQL,DML,DDL,TCL,DCL)

DQL:
　　 数据查询语言（凡是带有select关键字的都是查询语句）
　　 select…　
DML：
　　 数据操作语言（凡是对表当中的数据进行增删改的都是DML）
　　 insert delete update
　　 insert 增
　　 delete 删
　　 update 改　
　　 这个主要是操作表中的数据data。　　
DDL：
　　 数据定义语言
　　 凡是带有create、drop、alter的都是DDL。
　　 DDL主要操作的是表的结构。不是表中的数据。
　　 create：新建，等同于增
　　 drop：删除
　　 alter：修改
　　 这个增删改和DML不同，这个主要是对表结构进行操作。　　
TCL：
　　 不是王牌电视。
　　 是事务控制语言
　　 包括：
　　 事务提交：commit;
　　 事务回滚：rollback;　　
DCL：
　　 是数据控制语言。
　　 例如：授权grant、撤销权限revoke…

3.2 导入数据

导入sql
source [sql文件全路径]

3.3DQL查询语句

一般查询

查询一个表
select * from [table];
查看表结构 describe(描述)
desc [table];
按字段名查询:
select [字段], from [table]
起别名 as 或者空格
别名有空格时,需要引号括起来

条件查询(where)

= 等于
查询薪资等于800的员工姓名和编号？
　 select empno,ename from emp where sal = 800;
<>或!= 不等于
　　 查询薪资不等于800的员工姓名和编号？
　　 select empno,ename from emp where sal != 800;
< > <= >=
　　 查询薪资小于2000的员工姓名和编号？
　　 mysql> select empno,ename,sal from emp where sal < 2000;
and or 并且 或者
and优先级比or高。　
between … and …. 两个值之间, 等同于 >= and <=
　　 查询薪资在2450和3000之间的员工信息？包括2450和3000
　　 select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
　　 注意：
　　 使用between and的时候，必须遵循左小右大。
　　 between and是闭区间，包括两端的值。
is null 为 null 或者=null （is not null 不为空）
　　 查询哪些员工的津贴/补助为null？
　　 select empno,ename,sal,comm from emp where comm = null;
in 包含，相当于多个 or （not in 不在这个范围中）
　　 查询工作岗位是MANAGER和SALESMAN的员工？
　　 select empno,ename,job from emp where job in(‘MANAGER’, ‘SALESMAN’);
like 称为模糊查询，支持%或下划线匹配
　　 %匹配任意多个字符
　　 下划线：任意一个字符。（%是一个特殊的符号，_ 也是一个特殊符号,查询时需要加\转义字符）
　　 找出名字中含有O的？
　　 mysql> select ename from emp where ename like ‘%O%’;

排序

排序默认升序 order by:
select ename,sal from emp order byl sa;
指定升序order by [字段] asc:
select ename,sal from emp order by sal asc;
指定降序:order by [字段] desc
select ename,sal from emp order by sal desc;
多字段排序:order by [字段1] asc/desc,[字段2] asc/desc
查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，
　　 再按照名字升序排列。
　　 select
　　 ename,sal
　　 from
　　 emp
　　 order by
　　 sal asc, ename asc;
了解：根据字段的位置也可以排序
　　 select ename,sal from emp order by 2; // 2表示第二列。第二列是sal
　　 按照查询结果的第2列sal排序。
第一步：from
　　 第二步：where
　　 第三步：select
　　 第四步：order by（排序总是在最后执行！）

数据处理函数(单行处理函数)

常用的:
lower
upper
substr
concat
length
tirm
round
rand
ifnull
单行处理函数的特点：一个输入对应一个输出
常见的有:
lower 转小写
　　 mysql> select lower(ename) as ename from emp;
upper 转换大写
　　 mysql> select upper(name) as name from t_student;
substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
　　 select substr(ename, 1, 1) as ename from emp;
　　 注意：起始下标从1开始，没有0.
concat函数进行字符串的拼接
　　 select concat(empno,ename) from emp;
　　 首字母大写？
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
length 取长度
　　 select length(ename) as enamelength from emp;
trim 去空格
　　 mysql> select * from emp where ename = ’ KING’;
数据格式设置函数
str_to_date 将字符串转换成日期
　　 date_format 格式化日期
　　 format 设置千分位
case…when…then…when…then…else…end
　　 当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
　　 （注意：不修改数据库，只是将查询结果显示为工资上调）
　　 select
　　 ename,
　　 job,
　　 sal as oldsal,
　　 (case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 else sal end) as newsal
　　 from
　　 emp;
round四舍五入
结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）
select round(1236.567, 1) as result from emp; //保留1个小数
rand()生成随机数
mysql> select round(rand()*100,0) from emp; // 100以内的随机数
ifnull函数用法：ifnull(数据, 被当做哪个值)
补助为NULL的时候，将补助当做0
　　 select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

分组函数(多行处理函数)

5个：
** count 计数
　　 sum 求和
　　 avg 平均值
　　 max 最大值
　　 min 最小值**
分组函数在使用的时候需要注意哪些？
　　 第一点：分组函数自动忽略NULL，你不需要提前对NULL进行处理。
　　 第二点：分组函数中count(*)表示行数, count(具体字段)表示该字段非空行数.
第三点：分组函数不能够直接使用在where子句中,需要分组 group by.

分组查询(重点)

书写顺序select … from … where … group by… order by …
　　
　　 以上关键字的顺序不能颠倒，需要记忆。
　　 执行顺序是什么？
　　 1. from
　　 2. where
　　 3. group by
　　 4. select
　　 5. order by
为什么分组函数不能直接使用在where后面？
　　 select ename,sal from emp where sal > min(sal);//报错。
　　 因为分组函数在使用的时候必须先分组之后才能使用。
　　 where执行的时候，还没有分组。所以where后面不能出现分组函数。
　　 select sum(sal) from emp;
　　 这个没有分组，为啥sum()函数可以用呢？
　　 因为select在group by之后执行。
重点结论：
　　 在一条select语句当中，如果有group by语句的话，
　　 select后面只能跟：参加分组的字段，以及分组函数。
　　 其它的一律不能跟。
使用having可以对分完组之后的数据进一步过滤
　　 having不能单独使用，having不能代替where，having必须
　　 和group by联合使用。
　　 找出每个部门最高薪资，要求显示最高薪资大于3000的？
　　 select deptno,max(sal)
　　 from emp
　　 group by deptno
　　 having max(sal)>3000
　　 优化策略：
　　 select deptno,max(sal)
　　 from emp
　　 where sal >3000
　　 group by deptno
　　 where和having，优先选择where，where实在完成不了了，再选择 having。

去重函数distinct

distinct 只能出现在所有字段的最前方。
　　　　 mysql> select ename,distinct job from emp;　
distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重。
　　　　 mysql> select distinct job,deptno from emp;

连接查询(重点)

根据表连接的方式分类：
　　　　 内连接：
　　　　 等值连接
　　　　 非等值连接-
　　　　 自连接
　　　　 外连接：
　　　　 左外连接（左连接）
　　　　 右外连接（右连接）
　　　　 全连接（不讲）
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象

内连接(SQL99语法)

等值连接
select
　　　　 …
from
　　　 a
(inner)join
　　　 b
on
　　　 a和b的连接条件 // 条件是等量关系，所以被称为等值连接。
where 筛选条件
非等值连接
找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级
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 as ‘员工名’, b.ename as ‘领导名’
　　　　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;
　　　　
　　　　right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将
　　　　这张表的数据全部查询出来，捎带着关联查询左边的表。
　　　　在外连接当中，两张表连接，产生了主次关系。
左外连接
select
　　　　 e.ename,d.dname
　　　　from
　　　　 dept d
　　　　left join
　　　　 emp e
　　　　on
　　　　 e.deptno = d.deptno;
　　　　// outer是可以省略的，带着可读性强。
带有right的是右外连接，又叫做右连接。
　　　　带有left的是左外连接，又叫做左连接。
　　　　任何一个右连接都有左连接的写法。
　　　　任何一个左连接都有右连接的写法。
思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？
　　　　 正确。

子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。
select
　　　　 …(select).
　　　　 from
　　　　 …(select).
　　　　 where
　　　　 …(select)
　　　　 找出比最低工资高的员工姓名和工资？
　　　　 select ename,sal from emp where sal > (select min(sal) from emp);
from子句中的子查询
　　　　 注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
出每个岗位的平均工资的薪资等级
select
　　　　 t.*, s.grade
　　　　 from
　　　　 (select job,avg(sal) as avgsal from emp group by job) t
　　　　 join
　　　　 salgrade s
　　　　 on
　　　　 t.avgsal between s.losal and s.hisal;

union合并查询结果集

查询工作岗位是MANAGER和SALESMAN的员工？
select ename,job from emp where job = ‘MANAGER’
　　　　 union
　　　　 select ename,job from emp where job = ‘SALESMAN’;
union的效率要高一些。对于表连接来说，每连接一次新表，
　　　　 则匹配的次数满足笛卡尔积，成倍的翻。。。
　　　　 但是union可以减少匹配的次数。在减少匹配次数的情况下，
　　　　 还可以完成两个结果集的拼接。
// MYSQL可以，oracle语法严格 ，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

limit（非常重要）

limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
完整用法：limit startIndex, length
起始下标从0开始。
缺省用法：limit 5; 这是取前5.
mysql当中limit在order by之后执行！！！！！！

limit分页(重点)

每页显示pageSize条记录
　　　　 第pageNo页：limit (pageNo - 1) * pageSize , pageSize
select
　　　　 …
　　　　 from
　　　　 …
　　　　 where
　　　　 …
　　　　 group by
　　　　 …
　　　　 having
　　　　 …
　　　　 order by
　　　　 …
　　　　 limit
　　　　 …
　　　　
　　　　 执行顺序？
　　　　 1.from
　　　　 2.where
　　　　 3.group by
　　　　 4.having
　　　　 5.select
　　　　 6.order by
　　　　 7.limit…

3.4 DDL数据定义语言

建表create

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
create table 表名(
　　　　 字段名1 数据类型(大小),
　　　　 字段名2 数据类型(大小),
　　　　 字段名3 数据类型(大小)
　　　　 );

数据类型

char 定长字符串 255
varcher 可变长字符串 255
int 11
bigint
float
double
date短日期
datetime长日期
clob大字符串
blob大二进制(存图片)

删表drop

drop table t_student; // 当这张表不存在的时候会报错！
drop table if exists t_student;// 如果这张表存在的话，删除

truncate表截断

大表非常大，上亿条记录？？？？
　　 删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
　　 可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。

alter字段修改

3.5DML数据操作语言

insert

insert into t_student(no,name,sex,age,email)
values(1,‘zhangsan’,‘m’,20,‘zhangsan@123.com’);
注意：字段名可省略,但字段名和值要一一对应。什么是一一对应？
　　　　 数量要对应。数据类型要对应。
插入时,可以用格式函数来改变格式
str_to_date：将字符串varchar类型转换成date类型
date_format：将date类型转换成具有一定格式的varchar字符串类型。
format(数字, ‘格式’):格式化数字

update

语法格式：
　　　　 update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;
注意：没有条件限制会导致所有数据全部更新。

delete

语法格式:
　　　　 delete from 表名 where 条件;
注意：没有条件，整张表的数据会全部删除！

4.约束

4.1五种约束

约束包括哪些
非空约束：not null
　　 唯一性约束: unique
　　 主键约束: primary key （简称PK）
　　 外键约束：foreign key（简称FK）
　　 检查约束：check（mysql不支持，oracle支持）

4.2唯一性约束

唯一性约束unique约束的字段不能重复，但是可以为NULL
**如何是两个字段联合起来唯一呢**
怎么创建这样的表，才能符合新需求呢？

drop table if exists t_vip;
　　 create table t_vip(
　　 id int,
　　 name varchar(255),
　　 email varchar(255),
　　 unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
在mysql当中，如果一个字段同时被not null和unique约束的话，
　　 该字段自动变成主键字段。

4.3主键约束

一张表，主键约束只能添加1个。（主键只能有1个。）
不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！
主键除了：单一主键和复合主键之外，还可以这样进行分类？
　　 自然主键：主键值是一个自然数，和业务没关系。
　　 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！
auto_increment表示自增，从1开始，以1递增！　
id int primary key auto_increment

4.4外键约束(重要)

创建表的顺序？
　　 先创建父，再创建子。
删除数据的顺序？
　　 先删子，再删父。
插入数据的顺序？
　　 先插入父，再插入子。
　　思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
　　 不一定是主键，但至少具有unique约束。
　　 测试：外键可以为NULL吗？
　　 外键值可以为NULL。

5.存储引擎

5.1 概念

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

5.2 给表添加存储引擎

	CREATE TABLE `t_student` (
　　	  `no` int(11) NOT NULL AUTO_INCREMENT,
　　	  `name` varchar(255) DEFAULT NULL,
　　	  `cno` int(11) DEFAULT NULL,
　　	  PRIMARY KEY (`no`),
　　	  KEY `cno` (`cno`),
　　	  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
　　	) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

		ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

在建表的时候可以在最后小括号的")"的右边使用：
　　 ENGINE来指定存储引擎。
　　 CHARSET来指定这张表的字符编码方式。
　　 结论：
　　 mysql默认的存储引擎是：InnoDB
　　 mysql默认的字符编码方式是：utf8
查看支持的存储引擎:show engines \G
mysql支持九大存储引擎

5.3各大存储引擎优点

InnoDB:支持事务,数据库崩溃后悔自动恢复
MyISAM:可压缩,省空间,不支持事务
MEMORY:查询效率最高,但关机后数据消失

6.事务

6.1 概况

一个事务其实就是一个完整的业务逻辑。
是一个最小的工作单元。不可再分。
本质上，一个事务其实就是多条DML语句同时成功，或者同时失败！

6.2 事务开启,提交与回滚

start transaction开启事务(停止自动提交)
commit提交
rollback回滚
事务对应的英语单词是：transaction

6.2事务的隔离性四个级别

读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》
读已提交：read committed《提交之后才能读到》
可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》
序列化/串行化：serializable（最高的隔离级别）synchronized

7. 索引

7.1概述

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

7.2索引的实现

提醒1：在任何数据库当中 主键上都会自动添加索引对象，id字段上自动有索引，
因为id是PK。另外在mysql当中， ** 一个字段上如果有unique约束**的话，也会自动
创建索引对象。

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

提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式
存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中
索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引
被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式
存在。（自平衡二叉树：B-Tree）‘’

7.3 索引应用场景

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

7.4索引创建

创建索引：
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索引对象删除。

7.5索引失效

失效的第1种情况：%开头
select * from emp where ename like ‘%T’;
失效的第2种情况： or只有一个有索引
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有
索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个
字段上的索引也会实现。
失效的第3种情况：
使用复合索引的时候，没有使用左侧的列查找，索引失效
什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
失效的第4种情况：
在where当中索引列参加了运算，索引失效。
mysql> create index emp_sal_index on emp(sal);
失效的第5种情况：
在where当中索引列使用了函数

8.视图

view:站在不同的角度去看待同一份数据

8.1 视图创建与删除

创建视图对象：
create view dept2_view as select * from dept2;

删除视图对象：
drop view dept2_view;

注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;

8.1视图的作用

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

//面向视图查询*
select * from dept2_view;
// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,‘SALES’, ‘BEIJING’);
// 查询原表数据
mysql> select * from dept2;
…

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
可以把这条复杂的SQL语句以视图对象的形式新建。
在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
修改视图对象所映射的SQL语句。
个人理解:为了方便维护和修改select语句查询到的内容

9.DBA数据库管理常用命令

9.1 数据的导入导出(会用)

数据导出？
注意：在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

10 数据库三范式

10.1三范式介绍

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X

10.2 数据库设计

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

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

一对一：
一对一放到一张表中不就行了吗？为啥还要拆分表？
在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。
最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，