[大数据]mysql操作、约束、索引

概述

存储数据的方式

• Java程序存储数据（变量、对象、数组、集合），其实数据保存在内存中，属于瞬时状态存储
• 文件存储数据，保存在硬盘上，属于持久化存储

分类

DDL：数据定义语言

创建数据库	创建表		修改表		删除表

DML：数据操作语言

数据新增	删除	修改

DQL：数据查询语言

查询数据	表单查询	多表查询	子查询		关联查询

TCL：事务控制语言

比如：新增一个管理员（admin基本信息 admin_role管理员对应的角色信息）
	addAdmin();成功
	addAdminRole();失败
涉及到DML操作时需要添加事务控制，事务管理方式：commit();[数据提交，永久性入库] rollback();
[数据回滚]

DCL：数据控制语言

给用户分配权限，回收权限（保护隐私）
grant 授权
revoke 回收权限

连接数据库

mysql -h host -u user -p	-- host是主机名，user是mysql的用户名
mysql -u user -p		-- 连接本地数据库

DDL

-- 创建数据库
create database 库名;
create database 库名 default character set utf8;	-- 创建数据库并且规定编码方式
create database 库名 default character set utf8 collate utf8_bin; -- 创建数据库，并且规定编码方式、是否区分大小写

-- 查询所有数据库
show databases;

-- 查询数据库创建语句
show create database 库名;	--  CREATE DATABASE `db05` /*!40100 DEFAULT CHARACTER SET utf8 */

-- 修改数据库编码格式
alter database 库名 character set utf8;

-- 删除库
drop database 库名;

-- 使用库
use 库名;

-- 查看当前使用库
select database();


-- 创建表
create table 表名(
    字段名	字段类型[约束],
    字段名	字段类型[约束]
);
-- 示例
create table user(
	name varchar (20),
    age int (4)
);


-- 查看表设计
describe 表名;
-- 简写
desc 表名；

-- 添加列
alter table 表名 add 列名 类型;

-- 修改列类型
alter table 表名 modify [column] 列名 类型;

-- 修改列名
alter table 表名 change 原列名 新列名 新类型;

-- 删除列
alter table 表名 drop [column] 列名;

-- 修改表名
rename table 原表名 to 新表名;

-- 修改表字符集
alter table 表名 character set 字符集;

-- 删除表
drop table 表名;

DML

-- 新增
insert into 表名 values()；-- 插入所有列数据
insert into 表名 (列名) values ('数据');	-- 在指定列插入数据
insert into 表名 (列名) values ('数据'),('数据');	-- 一次性插入多条数据
-- 示例
-- insert into user (username,gender,salary,birthday,introduce) values ('rose','F',8888,'2018-07-06','good girl');
-- insert into user (username,gender,salary,birthday,introduce) values ('lucy','F',4985,'2014-03-16','good girl'), ('tom','T',6988,'2019-01-06','good boy');


-- 修改
update user set 字段名=字段值;	-- 修改该列的所有值为指定值
update 表名 set 字段名=字段值 where 字段名=字段值;	-- 根据where条件修改某一些记录
-- 示例
-- update user set salary=5000;
-- update user set salary=5000 where id=4;
-- update user set salary=4000.00,id=1 where gender='F';
-- update user set salary=salary+500 where gender='F';


-- 删除
delete from 表名;		-- 删除表中所有的记录
delete from 表名 where 列名=值;	-- 根据条件删除记录
truncate table 表名;	-- 删除表中所有的记录

DQL

简单查询

-- 单列查询
select id from 表名;

-- 多列查询
select 列名1，列名2... from 表名;

-- All查询
select * from 表名;
select 所有列名 from 表名;	-- 推荐使用

-- 定义列别名
select 列名 [as] 新列名 from 表名;		-- select username '姓名' from user;

-- 查询结果去重
select distinct 列名 from 表名;
select distinct 列名1，列名2 from 表名;	-- 联合(列名1，列名2)去重，distinct要放在select后面

-- 排序
select 列名 from 表名 order by 列名 asc/desc;
asc:升序(默认)
desc:降序
-- select * from user order by salary desc,id asc;优先薪资降序查询，然后id升序查询

条件查询

-- where查询
select 列名 from 表名 where 条件;

-- 区间判断 and 在...和...之间
select * from user where salary between 5000 and 10000;	-- 闭区间
select * from user where salary not between 5000 and 10000;		-- 开区间

-- 判断是否为null	null值参与运算还是null
select * from user where birthday is null;
select * from user where birthday is not null;

-- in查询 可以多个数据  需要排除空，不然如果not in里面的数据有null，结果没有数据
select * from user where id=1 or id=2;  -- 等价于下面
select * from user where id in (1,2);
select * from user where id!=1 and id!=2;	-- 等价于下面
select * from user where id not in (1,2);
select * from emp_ju where deptno in (select deptno from dept_ju);
select * from emp_ju where deptno not in (select deptno from  dept_ju where deptno is not null);

-- 模糊查询 %任意长度的任意字符	_一个字符
 select * from 表名 where 列名 like '%条件%';
 select * from 表名 where 列名 like '小_';
 
 -- ifnull  判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值
select IFNULL(deptno,0) from dept_ju; -- 查看deptno不为空的数据，如果为空，返回0
-- 和select * from emp_ju where deptno not in (select deptno from  dept_ju where deptno is not null);作用一致
select * from emp_ju where deptno in (SELECT IFNULL(deptno,0)FROM dept_ju);

常用函数

数字函数

-- round(数字，位数) ：四舍五入
select round(bnum,1) from math where num=15; 

-- truncate(数字,位数)	返回被舍去至小数点后D位的数字X。D为 0, 则结果不带有小数点或不带有小数部分。
select truncate(bnum,1)from math where num=54454;

-- abs(数字)：返回数据的绝对值
select abs(bnum)from math where num=54454;

-- rand() 返回一个随机浮点值 v ，范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。
select rand();

-- sqrt(数字) 平方根
select sqrt(num) from math where bnum=3.29;

-- pow(x,y) x的y次方
select pow(num,2) from math where bnum=3.29;

-- mod(x,y)	x/y的余数
select mod(num,2) from math where bnum=3.29;

字符串函数

-- trim(str);去除首尾的空格
select trim(str) from math where num=20;

-- concat();字符串拼接
select concat('First name',str,'Last Name')from math where num=20;

-- upper(str):转成大小
select upper(str)from math where num=16;

-- lower(str):转换小写
select lower(str)from math where num=16;

-- substring(str,1,2);-->下标从1开始（java是0）,截取2个
select substring(str,2,5)from math where num=16;

时间函数

-- date(now());返回当前日期
select date(now());

-- time(now());返回当前时间
select time(now());
-- 上面两个拼接
select concat(date(now()),'  ',time(now()));

-- -- date_format(时间,'%x-%m-%d %h:%i:%s'); yyyy-MM-dd
select date_format('2020-11-11 18:16:10','%x-%m-%d %h:%i:%s');

-- adddate('2020-10-10',-5);-->往前推5天   5往后推	interval：间隔
select adddate('2020-11-11 18:16:10',-5);
select date_add('2020-11-11 18:16:10',interval 3 day);

组函数

-- count()求记录数，如果count(列名)会忽略空值	 COUNT(*) 函数返回表中的记录数	
select count(num) from math; 

-- sum():求和，忽略空值无影响
select sum(num) from math;

-- avg():求平均数，忽略空值，对结果有影响
select avg(num) from math;

-- max():求最大，忽略空值无影响
select max(num) from math;

-- min():求最小，忽略空值无影响
select min(num) from math;

分组查询

-- 查询每一个部门的最高薪水和最低薪水，要求没有部门的不在计算范围内
select deptno,max(salary),min(salary) from emp_ju where deptno is not null group by deptno;
-- 查询每一个部门下有哪一些员工
select deptno,group_concat(ename) from emp_ju where deptno is not null group by deptno;
-- 查询每一个部门的薪水总和与平均薪水，没有部门的员工不在计算范围内
select sum(salary) '薪资总和',avg(ifnull(salary,0)) '平均薪资' from emp_ju where deptno is not null group by deptno;
-- 查询平均薪水大于5000的部门
select deptno,avg(ifnull(salary,0)) avg from emp_ju where deptno is not null group  by deptno having avg>5000;
-- 查询哪些部门的人数超过2个人，计算部门的平均薪水，且按照薪水的升序排序
select deptno, count(deptno)as c ,avg(ifnull(salary,0)) avg from emp_ju where deptno is not null group by deptno having c>2 order by avg;

? 注:凡是和组函数(聚合函数)同时出现的列名，则一定要写在group by之后或者使用 group_concat(列名 separator ‘:’)拼接，否则数据会有遗漏。

-- 查询-----表----条件----分组------筛选-----排序-----分页
  select->from->where->group by->having->order by->limit
  
 where与having的区别
 1.where在分组前对数据进行过滤，而having是在分组后对数据进行过滤
 2.having后面可以使用聚合函数，而where不可以

分页查询

-- limit(当前页,每页显示的记录数);	
-- 从第几条记录开始抓取，下标(从0开始)（page-1）*pageSize		pageSiz:抓取几条记录 
select * from emp_ju limit ?,?;
-- 比如:查询第一页的数据，每页展示10条记录 limit 0，10
-- 查询第二页的数据，每页展示10条记录 limit 10，10

子查询

1. 子查询可以区分为关联子查询和非关联子查询，他们的外层查询之间的执行顺序和关系不同
2. 一个select语句中包含另外一个完整的select语句，即如果一条语句中包含两个或两个以上的select，则是子查询。
3. from:主表查询 where:子表查询

非关联子查询

1. 不依赖于主查询可以单独执行，并且只执行1次
2. 执行过程:先执行子查询(独立的sql)->返回查询结果作为主查询的条件->最后执行主查询
3. 非关联子查询比较运算符，根据子查询的结果而定:
   • 单值(一条记录):> < =…
   • 多值(多条记录):>any() >all() in() 将子查询记录使用组函数处理

-- 查询哪些部门的人数比30号部门人数多		先查询每个部门的人数，然后查询30号部门的人数，最后拼接
select deptno, count(deptno) c from emp_ju group by deptno having  c > (select count(*) from emp_ju where deptno=30);
-- 查询哪些部门的平均薪水比20号部门的平均薪水高
select deptno,avg(ifnull(salary,0)) a from emp_ju group by deptno having a > (select avg(ifnull(salary,0)) from emp_ju where deptno=20);
-- 查询员工所在部门的平均薪水大于5000的员工姓名和职位
select deptno,ename,position,avg(ifnull(salary,0)) from emp_ju where deptno in (select deptno from emp_ju)group by deptno having avg(ifnull(salary,0))>5000;
-- 查询每个部门拿最高薪水的人是谁？		(a,b)in(a,b)
select * from emp where (deptno,salary) in (select deptno,max(salary) from emp group by deptno);

关联子查询

1. 子查询不是独立的语句，依赖主查询执行
2. 执行过程:–>先执行主查询–>结果传递给子查询–>执行子查询–>主查询筛选子查询返回的结果

-- 查询哪些员工的薪水比本部门的平均薪水低
select * from emp e where salary < (select avg(ifnull(salary,0)) from emp where deptno=e.deptno);
-- 查询哪些人有下属
select * from emp_ju where empno in (select distinct leader from emp_ju)

-- exists:用于判断子查询有没有数据返回，如果满足关系则有数据返回，不满足则没有数据返回。exists不关心子查询返回的值，只关心是否有数据返回，因此子查询select后面写什么都可以，通常直接用1表示
select * from emp_ju e where not exists (select 1 from emp_ju where leader=e.empno);

in和exisit的使用

• 非关联子查询：in
• 关联子查询：exsist
• left 大 right 小 in

表关联查询

• 内连接：表1 [inner] join 表2 on 条件;
  内连接的结果集中的数据一定是在两张表中都能找到匹配的记录
• 左连接：表1 left join 表2 on 条件;
  以左边为驱动表，驱动表中的数据在结果集中都有对应的记录
• 右连接：表1 right join 表2 on 条件;
  以右边为驱动表
• 全连接：不支持full out join，使用 left join union right join 实现相同的效果
  外连接特点(左外/右外):
  结果集=内连接结果集(匹配上)+驱动表在匹配表中匹配不上的记录(匹配不上)
  如果驱动表在匹配表中找不到匹配的记录，则匹配一行空行

-- 内连接  查询员工姓名及其部门的名称（数据在两张表，deptno为共有数据） 
select ename,dname from emp e inner join dept d on e.deptno=d.deptno;

-- 左连接 查询员工姓名及其部门的名称，要求没有部门的员工也要被查询出来
select ename,dname from emp e left join dept d on e.deptno=d.deptno;
-- 右外连接		以右边的表为主表，所有信息都会显示
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

-- 全连接
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno
union
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

DCL

数据控制语言：控制不同用户对于数据库、表访问权限和安全级别

-- 创建用户 	username：自定义账号用户名	host:数据库地址 pwssword：自定义用户密码
create user 'username'@'host' identified by 'password';
-- create user 'jack'@'localhost' identified by '1234';

-- 分配权限	
grant privileges on database.table to 'username'@'host';
-- grant select,insert,update,delete on db2105.emp_ju to 'jack'@'localhost';  全部权限可以使用 * 

-- 回收权限
revoke privileges on database.table from 'username'@'host';
-- revoke all on db2105.emp_ju from 'jack'@'localhost';

-- 删除用户
drop user 'username'@'host';
-- drop user 'jack'@'localhost';

约束

1. 主键约束

   -- 创建表并且添加主键约束		primary key auto_increment : 主键 自动增长   如果设置成自动增长，主键无法删除
   create table aa(
   id int primary key,
   name varchar(20)
   );
   
   -- 新增主键约束
   alter table 表名 add primary key (列名);
   
   -- 删除主键约束
   alter table aa add primary key (列名);
    
   -- 修改字段为主键约束   （先将原来的主键删除，然后将字段设置为主键）
   alter table aa add primary key (列名);
   

2. 唯一约束

   -- 添加唯一约束		alter table 表名 add unique(列名);
   alter table aa add unique(name);
   
   -- 删除唯一约束		alter tbale 表名 drop index 列名;
   alter table aa drop index name;
   

3. 非空约束

   -- 添加非空约束	not null
   alter table aa modify name varchar(30) not null;
   

4. 默认约束

   -- 添加默认约束	default '默认值'
   alter table aa modify name varchar(30) default 'jack';
   

5. 外键约束

   -- 添加外键约束	alter table 表名1 add foreign key(列名1) reference 表名2(列名2);
   alter table bb add foreign key (a_id)  references aa(id);
   

TCL

1. 事务控制

   ? 一组（DML：操作数据）操作的逻辑单元，需要保证数据的一致，要么一起成功，要不一起失败。

   比如：转账：A转给B 100，那么A-100，B+100

2. mysql引擎

   • 主要引擎如下：

     ? myisam ：mysql 5.5之前，data文件是三个文件：.frm（存储表结构） .myd（存储数据） .myi（存储索引）

     ? innodb：data文件是两个：.frm（存储表结构） .idb（数据、索引）

   • 事务的基本命令

     -- 查看mysql数据库的默认引擎
     show engines;
     
     -- 开启事务：两种方式	当事务被提交、事务回滚或者断开连接时，事务结束，否则一直处于开启事务状态
     begin;
     start transaction;
     
     -- 提交事务	
     commit;
     
     --回滚事务
     rollback
     

3. 事务的四个特性: ACID

   • 原子性（Atomicity）：每项操作都是一次执行，只有两个状态：成功、失败
   • 一致性（Consistency）：不能破话数据的安全性，需要数据一致，比如转账的业务
   • 隔离性（Isolation）：并发环境中，事务是相互隔离的，事务之间不能相互影响
   • 持久性（Durability）：事务提交后，数据将被永久保存

4. 数据库的隔离级别

   • ? 查看和设置隔离级别

     -- 查看隔离级别
     select @@tx_isolation;
     
     -- 设置隔离级别
     set session transaction isolation level 隔离级别;
     

   • sql支持的隔离级别

     • 读未提交（read uncommitted）：事务B读到了事务A未提交的数据 --------> 脏读

     • 读提交（read committed）：事务B读到了事务A提交的数据 Oracle -------->不可重复读

     • 重复读(repeatable read):事务B读不到事务A提交的数据 —>幻读

     • 串行化（serializable）：使用锁的方式保证数据读写，写写都是串行（等先写的写完再写后面的）

       下面是重复读的演示图：

       

   • 如何实现数据的隔离？

     ? 数据会创建一个视图，访问的时候以视图的逻辑为准。

     • 可重复读：启动事务并执行一个sql语句时创建视图，会一直使用这个视图的数据，直到视图结束
     • 读提交：每一个sql语句执行都会创建视图
     • 读未提交：直接返回记录的最新值，无视图
     • 串行化：使用锁避免并行访问

索引

1. 为什么有索引：提升查询效率，主要减少的是IO的读取次数

2. 基本命令

   -- 查看内部执行
   explain select * from emp where empno=1002;
   
   -- 添加索引		create index 索引名称 on 表(列);
   create index empno_index on emp(empno);
   
   -- 删除索引		alter table 表名 drop index 索引名称;
   alter table emp drop index empno_index;
   

3. 为什么添加索引之后查询效率提高

   ? 背景:A表10条记录，查找col2=89记录
   ? 如果不加索引，查找数据的次数是6次I/O，加了索引之后，查询列按照特定的数据结构保存索引值，查找次数是2次I/O，减少了查询次数。

4. mysql数据索引用的是什么数据结构保存的

   有序数组、hash表、搜索树

   • 采用的是b+树数据结构，不是二叉树(树高太高，特殊情况会发生数据倾斜),红黑树(特殊的平衡二叉树，会自动平衡,当数据量比较大时，数据的高度会很高，I/O查找的次数就会很多);

   • b树与b+树

     • b+树非叶子节点不存储data，只存储key(可以增大节点中存储的索引值)

   • b+树叶子节点与叶子节点之间有链指向，提升范围查找的效率

   有序数组：

二叉树

红黑树：

B树

B+树

5. 索引分类

   • 聚集索引：数据和指针在一起 innodb
   • 普通索引（二级索引）：除主键外的索引

6. 索引使用场景

   • 经常出现在where子句中的字段，特别是大表的字段，应该建立索引

   • 索引应该建在小字段上，对于大的字段甚至超长字段不要建立索引

   • 经常与其他表建立连接的字段上

   • 索引应该建在选择性高的字段上(离散度越高越好)

   • 某些字段经常以and方式出现在where子句中，单子段查询极少甚至没有，考虑创建复合索引

     select * from A where a=? and b=?
     --对于上面的情况使用复合索引  create index a_b_index on 表名(a,b);
     create index empno_sal_index on emp_ju (empno,salary);  -- 创建复合索引
     

   • 频繁进行数据操作的表，不要建立太多的索引

7. 索引失效场景

   • where中索引字段列使用了运算符!=

   • 模糊查询like以%开头

   • where中索引字段使用了函数

   • 需要类型转换，包括隐式转换

   • 复合索引未遵循最左原则

     -- c1,c2,c3
     select * from emp where c1=1 and c2=2;	-- 走
     select * from emp where c2=2 and c1=1;	-- 走
     select * from emp where c2=2 and c3=3;	-- 不走
     

   • mysql觉得全表扫描更快时

8. 索引创建的越多越好吗？

   • 索引越多，占用的空间越
   • DML操作时，会增加索引的开销，不好维护