MySQL学习笔记
MySQL实战应用
根据老杜mysql的课程内容整理的学习笔记
命令行基本操作
登录mysql(cmd)：
//显示密码的形式
mysql -uroot -pabc123
//隐藏密码的形式
mysql -uroot -p
******
mysql常用命令
注意：mysql不见分号不执行，分号表示一条语句的结束
\c 可以用来终止当前命令的输入
另外：sql语句不区分大小写
查看mysql版本号：
select database();
退出mysql：
exit
查看数据库：
show databases;
选择使用某个数据库：
use 数据库名;
创建数据库：
create database 数据库名;
查看某个数据库下有哪些表（先用use选择）：
show tables;
导入数据库文件：
source .sql文件名
表——数据库中最基本的单元
任何一张表都有行（row）和列（column），行被称为数据/记录，列被称为字段。
每一个字段都有：字段名，数据类型，约束等属性。
SQL语句的分类
DQL：数据查询语言（带有select语句的都是查询语句）
DML：数据操作语言（增insert，删delete，改update）
DDL：数据定义语言（带有create，drop，alter的语句，主要操作的是表的结构，而不是表的数据）
TCL：事务控制语言（包括事务提交commit，事务回滚rollback）
DCL：数据控制语言（授权grant和撤销权限revoke等）
查看表中的数据和结构
查看数据：
select * from 表名;
不看数据只看结构：
desc 表名;
//或
describe 表名;
DQL语句
1.查询一个字段：
select 字段名 from 表名;
2.查询多个字段，使用逗号隔开：
select 字段名,字段名,... from 表名;
3.查询所有字段（效率比较低，且可读性差，不建议使用）：
select * from 表名;
实际开发中查询所有字段的方式：用查询多个字段的方式将字段全都写出来
4.给查询的列起别名（as关键字，或省略as用空格）：
select 字段名 as 别名, 字段名 别名, ... from 表名;
只是将显示的查询结果列名起一个别名，select语句不会进行修改操作
如果起的别名里有空格，不符合语法，编译报错，如果一定要有空格，那么需要将别名用单引号括起来，有中文的话也要用单引号括起来
5.字段参与数学运算
//字段可以使用数学表达式
select ename,sal*12 from emp;
//别名
select ename,sal*12 as yearsal from emp;
6.条件查询
什么是条件查询：不是将所有数据都查出来，而是查询出来符合条件的
语法格式：
select
字段1,字段2,字段3,...
from
表名
where
条件;
条件：
=,<,>,!=（不等于，也可以用<>这个符号）,<=,>=,
between ... and ... （两个值之间的闭区间，等同于 >= and <=，也就是说左边的数字必须比右边小），
is null （为空，null代表什么也没有，不能用等号判断）， is not null （不为空），
and（并且）， or（或者）， in（包含，相当于多个or，都是具体的值） ，not in（不在这个范围），
（注意：and和or同时出现，and优先级较高，在开发中不确定优先级，直接加小括号就行）
not（取非，主要用在is或in中），
like （like称为模糊查询，支持%或下划线匹配，%匹配任意个字符，一个下划线只匹配一个字符）
（格式举例：以k开始：k%，以k结束：%k，含有k：%k%）
（第二个字母是A：'_A%'）
7.排序
select
ename,sal
from
emp
order by
sal;//默认是升序
-------------------
select
ename,sal
from
emp
order by
sal desc;//用desc指定降序
--------------------
select
ename,sal
from
emp
order by
sal asc;//用asc指定升序
---------------------
//按照多个字段排序
select
ename,sal
from
emp
order by
sal asc, ename asc;//前面的字段起主导，前面相同时才按照后面的规则排序
单行处理函数/数据处理函数
单行处理函数的特点：一个输入对应一个输出。（多行处理函数是多个输入对应一个输出）
常见的单行处理函数：
1.lower 转换小写
select lower(ename) from emp;
select lower(ename) as ename from emp;
2.upper 转换大写
3.substr 取子串
select substr(ename, 1, 1) as ename from emp;
substr（被截取的字符串，起始下标，截取的长度）;
注：起始下标从1开始
4.length 取长度
5.trim 去空格
6.str_to_date 将字符串转化为日期
字符串varchar类型转化为date
7.date_format 格式化日期
date类型转化为varchar
8.format 设置千分位
9.round 四舍五入
select round(1236.567, 0) from emp;//保留0位小数
select round(1236.567, 1) from emp;//保留1位小数
select round(1236.567, -1) from emp;//保留到十位
10.rand() 生成随机数
11.ifnull 可以将null转化为一个具体值
数据库中只要有NULL参与的数学运算，最终结果就是NULL
select ename, (sal + ifnull(comm, 0)) * 12 from emp;
//如果comm为null，当作0处理
用法：ifnull（数据，被当作的值）
12.case...when...then...when...then...else...end
例如：需求：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALEMAN的时候，工资上调50%。（不修改数据库，只将查询结果显示为工资上调）
select
ename,
job,
sal as oldsal,
(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
from
emp;
分组函数/多行处理函数
多行处理函数的特点：输入多行，最终输出一行
注意：分组函数在使用的时候必须先进行分组，然后才能用。如果没有对数据进行分组，整张表默认为一组。
1.count 计数
2.sum 求和
3.avg 平均值
4.max 最大值
5.min 最小值
分组函数在使用的时候需要注意哪些：
1.分组函数自动忽略NULL，不需要提前对null进行处理
2.分组函数中count(*)和count具体字段的区别：
count(具体字段)表示在这个字段上不为null的元素的总数
count(*)统计表当中的总行数（因为不可能存在每一个字段都是NULL的行）
3.分组函数不能直接使用在where子句中
原因：分组函数在使用的时候必须先分组才能使用，where执行的时候还没有分组
4.所有的分组函数可以组合起来一起用
分组查询
什么是分组查询：
在实际应用中，可能有这 样的需求，需要先进行分组，然后对每一组的数据进行操作。这个时候就需要使用分组查询。
select
...
from
...
group by
...
关键字的书写顺序和执行顺序：
书写顺序：
select
...
from
...
where
...
group by
...
having
...
order by
...
执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by
举例：找出每个工作岗位的工资和
思路：按照工作岗位分组，然后对工资求和。
select
job, sum(sal)
from
emp
group by
job;
注意：在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数，其他的一律不能跟（数量对不上没有意义，在oracle里会报错）
having
使用having可以对分完组之后的数据进一步过滤
having不能单独使用，having不能代替where，having必须和group by联合使用。
举例：要求找出每个部门最高薪资，并显示最高薪资大于3000的
select
deptno, max(sal)
from
emp
group by
deptno
having
max(sal) > 3000;
但是这样的sql语句执行效率比较低，可以先筛选出工资大于3000的。
优化策略：where和having，优先选择where，where实在完成不了，再选择having
select
deptno, max(sal)
from
emp
where
sal > 3000
group by
deptno;
例如：对部门分组，显示平均工资高于3000的部门，这时无法使用where筛选，只能使用having
查询结果去除重复记录
原表数据不会被修改，只是查询结果去重。
去重需要的关键字：distinct
select distinct job from emp;
distinct只能出现在所有字段的最前方。后跟多个字段时，表示多个字段联合起来去重。
连接查询
从一张表中单独查询，称为单表查询。
多张表联合起来查询数据，这种跨表查询被称为连接查询。
连接查询的分类：
根据语法的年代分类：SQL92，SQL99
根据表连接的方式分类：内连接（分为：等值连接，非等值连接，自连接），外连接（左外连接，右外连接 ），全连接（不常用）
笛卡尔积现象
当两张表进行连接查询，没有任何条件的限制，条数是两张表行数相乘，这种现象被称为笛卡尔积现象。
连接时加条件才能避免笛卡尔积现象，满足条件的记录会被筛选出来。
查询的语法：
select 字段1，字段2
from 表1，表2
where 条件
//举例子：
select
ename, dname
from
emp, dept
where
emp.deptno = dept.deptno;
实际开发中，可以通过声明字段所在的表提高一些效率，并且给表起别名方便查看，如上面的代码应该改为：
select
e.ename, d.dname
from
emp e, dept d
where
e.deptno = d.deptno;//SQL92语法
注意：最终查询的结果条数正常了，但是查询过程中匹配的次数没有减少，只不过没有全部显示出来。
连接查询的具体方式
内连接之等值连接：
SQL92语法已在上面给出。
SQL99语法：
select
e.ename, d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
SQL99语法的优点：表连接的条件是独立的，连接之后如果要进一步筛选，可以添加where，可读性更好。（92语法需要在全部写在where后）
内连接之非等值连接：
就是on条件后面并不是相等的判断，可能是一个between...on...的范围
内连接之自连接：
就是一张表看成两张表。
比如：员工和领导，让员工表的领导编号等于领导表的员工编号。
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno
//但是最大的领导没有领导编号，所以查不出来，会少一条记录
外连接
内连接的特点：完全能够匹配上这个条件的数据查询出来，两张表是平等的。
外连接的特点：主表中没匹配上的记录也查询出来，匹配的数据应该是null，表示什么也没有。两张表有主次关系。
//右外连接
select
e.ename, d.dname
from
emp e right join dept d
on
e.deptno = d.deptno;
//左外连接
select
e.ename, d.dname
from
dept d left join emp e
on
e.deptno = d.deptno;
right代表join关键字右边的这张表看成主表，也就是这张表的全部数据都要查询出来。
left代表join关键字左边的这张表看成主表，也就是这张表的全部数据都要查询出来。
任何一个右连接都有左连接的写法。
多张表连接
语法：
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
...
子查询
select语句中嵌套select语句，被嵌套的语句称为子查询。
子查询可以出现在：select，from，where后面。
where子句中的子查询：
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.avg(sal) between s.losal and s.hisal;
select子句中的子查询：
union合并查询结果集
案例：查询工作岗位是manager和salesman的员工：
//使用or
select ename, job from emp where job = 'manager' or job = 'salesman';
//使用in
select ename, job from emp where job in('manager', 'salesman');
//使用union
select ename, job from emp where job = 'manager'
union
select ename, job from emp where job = 'salesman';
union的效率更高，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，但是union可以减少匹配的次数（其实就是把乘法变成了加法）
union使用时需要注意：union在进行结果集合并的时候，要求两个结果集的列数相同。
limit
limit是将查询结果集的一部分取出来，通常使用在分页查询中。
//limit用法
limit 起始下标，长度 //起始下标默认为0
//limit使用举例
select
ename, sal
from
emp
order by
sal desc
limit 0, 5;
注意：mysql中limit在order by之后执行。
表的创建（建表）
DDL语句包括：create，drop，alter
create table 表名 （字段名1 数据类型，字段名2 数据类型 default 默认值，...）;
表名建议以 t_
或 tbl_ 开始，可读性强。
指定默认值的方法：default
mysql中的数据类型
varchar 可变长度的字符串，会根据实际的数据长度动态分配空间
char 定长字符串
int 整数型
bigint 长整型
float 单精度浮点型
double 双精度浮点型
date 短日期类型：年月日
datetime 长日期类型：年月日时分秒
clob 字符大对象，最多可以存储4G的字符串
blob 二进制大对象
删除表
drop table if exists 表名;//如果这张表存在，就删除
也可以不写if exists，但是这样写的话如果表不存在就会报错。
往表中插入数据insert（DML）
语法格式：
insert into 表名(字段名1，字段名2，字段名3，...) values (值1，值2，值3，...);
注意：字段名和值要一一对应，数量要对应，数据类型也要对应。
insert语句中的字段名部分可以省略，但是省略的意义为全部字段，所以后面的值也要都写上。因为可读性差所以不建议这样写。
insert插入多条记录的方法：values后面写多次值
insert into 表名(字段名1, 字段名2) values
(值1.1，值2.1),
(值1.2，值2.2),
(值1.3，值2.3);
修改update（DML）
语法格式：
update 表名 set 字段名1=值1，字段名2=值2，... where 条件;
注意：没有条件限制会导致所有数据全部更新。
删除数据delete（DML）
delete from 表名 where 条件;
快速创建表
create table 建立的表名 as select * from 查询的表名;
//或
create table 建立的表名 as select 字段1，字段2 from 查询的表名;
相当于把后面的表复制了一份，原理是将一个查询结果当作一张表新建，也可以将查询结果插入到其他表中。
快速删除表中数据（不可回滚）
delete删除数据的方式比较慢，删除以后存储空间不会释放，所以delete支持回滚（rollback）。
truncate删除效率比较高，表被一次截断，但不支持回滚。
delete属于DML语句，truncate属于DDL语句。
对表结构的增删改
在实际的开发中，需求一旦确定之后，表设计好以后，很少进行表结构的修改。因为开发进行中，修改表结构成本比较高。如果真的要修改表结构，可以使用工具。
约束
约束（constraint）：在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性，有效性。
约束包括哪些：
非空约束：not null
唯一性约束：unique
主键约束：primary key (PK)
外键约束：foreign key (FK)
检查约束：check（mysql不支持，Oracle支持）
非空约束
非空约束not null约束的字段不能为NULL
create table t_vip(
id int,
name varchar(255) not null
);
唯一性约束
unique约束的字段不能重复，但是可以为NULL。
create table t_vip(
id int,
name varchar(255) unique, //直接写在列后称为列级约束
email varchar(255)
);
多个字段联合唯一：
需求：name和email两个字段联合起来具有唯一性。
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name, email) //这样写可以实现联合唯一，这样的约束称为表级约束
);
not null和unique可以联合，联合以后的字段默认被当作主键字段
主键约束
主键字段：添加了主键约束的字段
主键值：主键字段中的每一个值
主键值是每一行记录的唯一标识，主键的特征：not null + unique
强调：任何一张表都应该有主键，如果没有主键，表无效。并且主键数量只能有一个。
create table t_vip(
id int primary key, // id为主键
name varchar(255),
);
注意：主键也可以使用表级约束，多个字段形成一个主键，称为复合主键
但是在实际开发中不建议使用复合主键，因为主键值存在的意义就是这一行记录的唯一标识。
主键值建议使用int，bigint，char等类型。不建议使用varchar。
自然主键：主键值是一个自然数，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号当作主键值。
在实际开发中，自然主键使用的多，因为主键不需要有意义，不重复就行，如果主键和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以尽量使用自然主键。
自动维护主键值：id int primary key auto_increment //从1开始，以1递增
外键约束
两张表有关联时，其中一张表的某个字段和另一张表有关系，如果不对这个字段的值进行约束，那么可能在另一张表中不存在这样的值，为了保证值有效，需要添加外键约束。
被引用字段的表称为父表，引用父表中字段的称为子表。
删除表的顺序：先删子表
创建表的顺序：先创建父表
删除数据的顺序：先删子表
插入数据的顺序：先插入父表
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
id int primary key auto_increment,
name varchar(255),
classid int,
foreign key(classid) refrences t_class(classno) //外键约束
);
子表中外键引用的父表中的某个字段，这个字段不一定是父表的主键，但一定是具有唯一性的。也就是说：一定是unique，但值可以为NULL。
存储引擎
存储引擎是mysql中特有的一个术语。实际上存储引擎是一个表存储和组织数据的方式。不同的存储引擎，表存储数据的方式不同。
在建表的时候可以在最后小括号的右边使用：
ENGINE 来指定存储引擎（默认为InnoDB），
CHARSET 来指定这张表的字符编码方式（默认为UTF-8）
怎么查看mysql支持哪些存储引擎：
show engines \G
mysql常用的存储引擎：
MyISAM：
使用三个文件表示每个表：格式文件，数据文件，索引文件
特点：可被转换为压缩、只读表来节省空间。
InnoDB：
mysql默认的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制，它的最主要的特点就是支持事务，非常安全。 不能压缩，不能转换为只读，不能很好的节省存储空间。
MEMORY：
使用MEMORY的表，数据存储在内存中，切行的长度固定，这两个特点使得MEMORY的速度非常快，查询效率非常高。但是数据不安全，保存在内存中，所以关机以后数据就会消失。
事务（transaction）
一个事务就是一个完整的业务逻辑。一个完整的业务逻辑是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。（如a账户向b账户转账10000，a账户-10000，b账户+10000就是一个业务逻辑，必须同时成功或同时失败）
只有DML语句（insert, update, delete）才会有事务这一说，其他语句和事务无关。
因为只有这三个语句是对数据库表中数据进行增删改的。只要操作涉及到数据的增删改，那就一定要考虑到安全问题。
正是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，所以需要事务。说到底，一个事务其实就是多条DML语句同时成功，或者同时失败。
事务怎么做到同时成功同时失败：
InnoDB存储引擎提供了一组用来记录事务性活动的日志文件。
在事务执行过程中，每一条DML的操作都会记录到事务性活动的日志文件中。
在事务执行过程中，我们可以提交事务，也可以回滚事务。
提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着事务的结束，并且是一种全部成功的结束。
回滚事务：将之前DML的所有操作全部撤销，并且清空事务性活动的日志文件。
回滚事务标志着事物的结束，并且是一种全部失败的结束。
提交和回滚事务
mysql中默认情况下支持自动提交事务，每执行一条DML语句，提交一次。
提交事务（commit）：start transaction; 关闭自动提交机制。
start transaction;
回滚事务（rollback）：rollback; 回滚只能回滚到上一次的提交点。
rollback;
事务特性
事务包括4个特性：
A：原子性：说明事务是最小的工作单元，不可再分。
C：一致性：所有事物要求，在同一个事务当中，所有操作必须同时成功，或者同时失败。
I：隔离性：A事务和B事务之间具有一定的隔离。A事务在操作一张表的时候，另一个事务B也操作这张表，相当于多线程并发访问同一张表。
D：持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。
事务隔离级别
A事务与B事务中间的隔离可以很厚也可以很薄，这就是事务的隔离级别。隔离级别越高，表示中间的距离越远。
事务和事务之间的隔离级别有哪些：
1.读未提交：read uncommitted（最低的隔离级别）
事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题就是脏读现象（Dirty Read），我们称读到了脏数据。
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都不是脏读。
2.读已提交：read commited
事务A只能读取到事务B提交之后的数据。这种隔离级别解决了脏读的现象。
这种隔离级别不可重复读取数据。在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到了4条数据，这就称为不可重复读取。
这种隔离级别是比较真实的数据，每次读到的数据是绝对的真实。
Oracle数据库默认的隔离级别就是read committed。
3.可重复读：repeatable read
事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的，即使事务B将数据修改并提交了，事务A读取到的数据还是没有发生改变。也就是说永远读取的都是开启事务时的数据。
这种隔离级别存在的问题是：幻影问题。每一次读取的数据不够真实。
mysql中默认的事务隔离就是repeatable read
4.序列化/串行化： serializable（最高的隔离级别）
这是最高隔离级别，效率最低，解决了所有的问题。
这种隔离级别表示事务排队，不能并发。
每一次读取到的数据都是真实的，并且效率是最低的。
索引
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，可以缩小扫描范围。
一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。索引是各种数据库进行优化的重要手段。
索引是需要排序的，只有排序才会有区间查找一说，这个排序和TreeSet数据结构相同。底层是一个自平衡的二叉树，B-Tree。遵循左小右大原则存放，采用中序遍历方式遍历取数据。
索引实现原理：
在任何数据库当中，主键上都会自动添加索引对象。另外，在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
在任何数据库当中，任何一张表的其中一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在mysql当中，索引是一个单独的对象，在不同的存储引擎中以不同的形式存在。MyISAM中，索引存储在一个.MYI文件中；InnoDB中，索引被存储在一个逻辑名称叫做tablespace的当中；在MEMORY存储引擎中，索引被存储在内存当中。
什么条件下，我们会考虑给字段添加索引呢：
1.数据量庞大。
2.该字段经常出现在where后面。
3.该字段很少的DML操作（insert，delete，update）。因为DML之后，索引需要重新排序。
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能，建议通过主键查询，建议通过unique约束的字段进行查询，效率比较高。
索引的创建和删除
创建索引：
create index 索引名 on 表名(字段名);
删除索引：
drop index 索引名 on 表名;
在mysql中，怎么看一条select语句是否使用了索引进行检索：
explain select语句;//检索语句，肯定有where，type=ALL则没有添加索引
索引的失效：
1.尽量避免模糊查询的时候，以'%'开头，这样会使得索引失效。这是一种优化的策略。
2.使用or的时候会失效，如果使用or需要or两边的条件字段都要有索引，才会走索引。所以实际开发中，建议尽量少使用or，建议使用union。union每句都是单独的sql语句。
3.使用复合索引的时候，没有使用左侧的列查找，索引失效。
4.在where的条件中，索引列参与了运算，索引失效。
5.在where的条件中，索引列使用了函数，索引失效。
索引的分类：
单一索引
复合索引
主键索引
唯一性索引
视图
view：站在不同的角度去看待同一份数据。
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。
创建视图：
create view 视图对象名 as select 字段名 from 表名;
删除视图：
drop view 视图名;
注意：只有DQL语句才能以view的形式创建。
视图在开发中的作用：
把一个使用次数多，长度长的sql语句以视图对象的形式新建。不仅方便使用，后期维护修改的时候也只需要修改原sql语句，不用到处找代码。
DBA命令
新建用户
授权
回收权限
导出导入
数据库设计范式
第一范式：要求任何一张表必须有主键，每一个字段都是原子性不可再分。（最核心，最重要的范式，所有表的设计都需要满足。）
第二范式：要求所有非主键字段完全依赖主键，不要产生部分依赖。（建立在第一范式的基础上）
第三范式：要求所有非主键字段直接依赖主键，不要产生传递依赖。（建立在第二范式的基础上）
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，减少空间浪费。
多对多的设计方式：三张表，关系表+两个外键（满足第二范式）
一对多的设计方式: 两张表,一对多中多的那张表加一列外键(满足第三范式)
标签：语句,事务,sql,查询,索引,emp,MySQL,主键,select From： https://www.cnblogs.com/utage/p/16838629.html