MySQL

Data Query Language

Data Manipulation Language

Data Definition Language

Transaction Control Language

Data Control Language

分组函数

分组函数不可直接使用再where子句当中

找出工资高与平均工资的员工

错误：无效使用分组函数

select ename, sal from emp where sal > avg(sal); 

​ 因为where比group by先执行

​ 所以不能用分组函数，因为分组函数一定需要先分组

正确：

select ename,sal from emp where sal > (select avg(sal) from emp);

count(*) 与count(某个字段的区别)

​ count(*)：总记录条数

​ count(某个字段)：字段中不为NULL的个数

分组函数可以组合使用

Group By 和 having

​ group by: 按照某个字段或者某些字段进行分组

​ having: having 是对分组之后的数据进行再次过滤

Group by

案例：找出每个工作岗位的最高薪资

select max(sal),job from emp group by job;

分组函数一般都会和group by联合使用，所以叫分组函数

分组函数一定在group by 之后执行

没有group by 的话，整张表的数据会自成一组

有group by的 话 select只能有参加了分组的字段和分组函数

错误：

select ename,max(sal),job from emp group by job;

每个岗位的平均薪资

select job,avg(sal) from emp group by job

找出不同工作岗位的最高薪资

联合分组deptno,job.

select deptnom,job,max(sal)from emp group by deptnom,job

Having

找出每个部门的最高薪资，要求显示薪资大于2500的数据

第一步

select max(sal) , deptno form emp group by deptno;

第二步 找出薪资大于2900

select max(sal) , deptno from emp group  by deptno having max(sal) >2900;

但是这种效率低

select max(sal) , deptno from emp where sal>2500 group by deptno;

先用where过滤后效率高

找出每个部门的平均薪资

select deptno, avg(sal) from rmp group by deptno having avg(sal) > 2000;

总结一个完整的DQL语句

有规定的执行顺序

select 5

from 1

where 2

group by 3

having 4

order by 6

连接查询

去除重复记录

select distinct job from emp

distinct只能出现在所有字段的最前方

可以联合去重

select distinct deptno,job from emp;

统计岗位的数量

select count (distinct job) from emp;

连接查询

按照连接查询的连接方式划分分类：

内连接

​ 等值连接

​ 非等值连接

​ 自链接

​ 外连接

​ 左外连接

​ 右外连接

​ 全连接

在表的连接查询中有种现象：笛卡尔乘积现象

找出每个员工部门名称，要求是显示员工和部门名称

select e.ename d.dname from emp e, dept d where e.deptno = d.deptno

这样是sql92语法，匹配次数还是笛卡尔乘积14*4=56次

内连接之等值连接

条件是等量关系

查询每个员工的部门名称

SQL92:

selcet e.name,d.name from emp e, dept d where e.deptno=d.deptno;

SQL99:

selcet e.name,d.name from emp e join dept d on e.deptno=d.deptno;

表1 join 表2 on 连接语法 where …

SQL 结构更清晰，表连接条件和where过滤条件分离了

省略了inner join的inner

内连接之非等值连接

连接条件是非等量关系

找出每个员工的工资等级，要求显示工名、工资、工资等级。

select e.name, e.sal, s.grade 
from emp e
join salgrade s
on e.sal between s.losal and s.hisal

自连接

最大特点是一张表看成两张表。自己连接自己

找出每个员工的上级领导，要求显示员工名和对应的领导名

select a.name,b.name 
from emp a
join emp b
on a.mgr=b.name

外连接

内连接：把AB两张表能匹配的记录查询出来，没有主副之分

外连接：有主表和副表，当副表无法匹配，副表自动模拟出NULL与之匹配

外连接的分类？

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

右外连接：表示右边的表是住表

左连接有右连接的血法，右连接也有对应左连接的写法

找出所有员工的上级领导？（所有员工都要显示出来）

select a.name, b.name
from emp a
left join emp b
on a.mgr=b.name

left outer join , outer 可以省略

外连接最重要的特点是主表属于无条件查出来

找出哪个部门没有员工

select d.*
from dept d
left join emp e
on d.deptno=e.deptno
where e.empno is NULL;

还有一个全连接，比较少用

三张表怎么连

找出每个员工的部门名称以及工资等级

如果复杂就先分步拆解

内连接

select e.name,d.dname,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;

… join A join B join C on …

表示AB表先连接，结果再和C连接

找出每个员工的部门名称以及工资等级 以及上级领导

外连接

select e.name,d.dname,s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
left join emp e1
on e.mgr = e1.empno;

子查询

子查询就是被嵌套的语句

找出薪资高于平均薪资的员工

where后面嵌套子查询

select avg(sal) from emp;

select * from emp where sal > 2073.214286

合并两条语句

select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询

找出每个部门平均薪水(按照部门编号分组, 求sal的平均值)

select deptno,avg(sal) from emp group by deptno;

将以上表当做临时表t, 让这张表和salgrade s表链接, 条件是: t.avgsal between s.losal and s.hisal

然后进行嵌套

select t.*, s.grade
from (select deptno,avg(sal) from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;

找出每个部门都薪资等级的平均值

找出每个员工的薪资等级

select e.ename, s.grade 
from emp e 
join salgrade s 
on e.sal between s.LOSAL and s.HISAL

Kalen的答案:

select d.deptno, avg(t.grade)
from 
(select e.ename, e.DEPTNO, s.grade 
from emp e 
join salgrade s 
on e.sal between s.LOSAL and s.HISAL) t
join dept d
where d.deptno = t.deptno
group by deptno

视频里的答案

select e.deptno, avg(s.grade)
from emp e 
join salgrade s 
on e.sal between s.LOSAL and s.HISAL
group by 
e.deptno;

不需要嵌套的时候不要嵌套

在select后面嵌套

找出每个员工所在部门名称, 要求显示员工名部门名

select e.ename, d.dname
from emp e
join dept d
on e.deptno = d.deptno;

select e.name, e.deptno,
	(select d.dname from dept d where e.deptno=d.deptno) as dname
from emp e;

Union (可以将查询结果集想加)

找出工作岗位是salesman和manager的员工

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

Limit (分页查询, MySQL特有)

不通用,oracle中有个相同机制叫rownum

语法机制:

​ limit startindex, length

​ startIndex 表示起始位置

​ length 表示取几个

取出工资前五名的员工

select ename, sal from emp order by sal desc limit 0,5;

limit 是SQL语句中最后执行的环节

找出工资排名第四到第九的员工

select ename, sal from emp order by sal desc limit 3,6;

6代表第几个, 而不是最后的index

通用的分页标准

每页三条记录:

第一页: 0, 3

第二页: 3, 3

第三页: 6, 3

每页起始都是3*(n-1)

表的创建

建标语句的格式

create table 表名(

​ 字段名1 数据类型,

​ 字段名2 数据类型,

​ 字段名3 数据类型,

​ ……

);

MySQL中的字段数据类型

​ 常见的:

​ int bigint 整数型

​ float double 浮点类型

​ char varchar 定长字符串/不定长字符串

​ date datetime 日期/带秒日期

​ BLOB 二进制大对象 (存储图片, 视频等流媒体) Binary Large Object

​ CLOB 字符大对象 (存储大文本,比如4G的字符串) Character Large Object

类型的选择

char和varchar:

​ 比如name名字如果是char(6) 底层就只分配6个空间, 不能超出6个字符长度,超出报错,不够自动补到6

​ 而varchar动态存储分配, 但也可以规定长度,根据实际长度动态分配长度

​ 实际开发中当某个字段数据长度不发生改变的时候, 比如性别, 生日 就用char

​ 会变比如简介就用varchar

BLOB和CLOB:

​ 电影表: t_movie

​ id(int) name(varchar) playtime(date/char) poster(BLOB) plot(CLOB)

BLOB 和 CLOB 不能用insert 插入, 一般Java IO 流

一般比较少见, 一般大文件都是放地址在表里面

建一个学生表

create table t_student(
	no bigint,
    name varchar(255),
    sex char(1),
    classno varchar(255),
    birth char(10)
);

insert 插入数据

语法格式:

​ insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

​ 要求字段数量与值数量相同

​

insert into t_student(no,name,sex,classno,birth) values (1,'zhangsan', 1, 'gaosan1ban', '1950-10-12');

只写一个字段的话, 剩下字段自动NULL

create table t_student(
	no bigint,
    name varchar(255),
    sex char(1) defalut 1,
    classno varchar(255),
    birth char(10)
);

可以用default 加上默认值, 不然默认值为NULL

insert执行完以后表格中必然多一条数据, insert不能用于修改数据

只能使用update进行修改

insert into t_student values(1, 'value','0','高三二班','1999-12-18');

前面的括号内容可以省略, 但是这样后面values字段数量和顺序必须与列数量吻合

一次插入多行数据

逗号隔开

insert into t_student(no,name,sex,classno,birth) 
values (1,'zhangsan', 1, 'gaosan1ban', '1950-10-12'),
values (2,'zhang', 1, 'gaosan3ban', '1950-10-12');

表的复制

语法

​ create table 表名 as select 语句;

​ 讲查询结果复制成一张表

将查询结果插入表

语法

insert into 表名 select 语句

修改数据

语法格式:

​ update 表名 set 字段名=值1, 字段名2=值2,… where 条件;

注意没有约束条件会整张表全部更新

删除数据

语法

​ delete from 表名 where 条件

没有where约束全部删除

怎么删除大表

truncate table 表名, 表被截断不可回滚

修改表结构

实际开发中很少修改表结构, 因为这是对之前设计的否定

java中不会出现表结构的语句

一般只有CRUD

create retriece update delete

约束

常见约束

1. 唯一性约束 (unique) 不能重复, 但可以为NULL

   drop table if exists t_user;
   create table t_user{
   	id int,
   	username varchar(255) unique
   };

   给多个列添加unique

   create table t_user{
   
   	id int,
   	usercode varchar(255),
   	username varchat(255),
   	unique(usercode, username)
   };

   以上代表 usercode, username 联合起来不重复, 只是一个约束

   在每个列后面单独加unique是列级约束

   unique()是表级约束

2. 非空约束 (not null) 不能为null

   drop table if exists t_user;
   create table t_user(
   	id int,
       username varchar(255) not null,
       password varchar(255)
   );
   insert into t_user(id,password) val(1,'123')

   以上代码插入错误, 因为username没有默认值, 不能为空

   insert into t_user(id,username,password) val(1,'list','123')

   not null只有列级约束, 没有表级约束

3. 主键约束 (primary key) , 约束字段既不能为null, 也不能重复

   create table t_user(
   	id int primary key,
   	username varchar(255),
   	email varchar(255)
   );

   id是主键, 不能为null, 也不能重复

   列级约束

   主键的作用

   • 表的设计三范式中有要求, 第一范式就要求任何一张表都应该有主键
   • 主键的作用: 主键值是这行记录在这张表中的唯一标识. (e.g. 身份证号)

   主键的分类:

   • 单一主键
   • 复合主键(多个一段联合起来添加一个主键约束)
     • 复合主键违背三范式, 所以不建议使用

   根据主键的性质来划分:

   • 自然主键
   • 业务主键: 主键值跟系统业务挂钩 (不推荐)– 因为业务一旦改变, 主键不好修改

   使用表级约束定义主键

   create table t_user(
   	id int, 
   	username varchar(255),
   	primary key(id)
   );

   复合主键

   create table t_user(
   	id int, 
   	username varchar(255),
   	primary key(id, username)
   );

   MySQL提供的主键自增

   create table t_user(
   	id int primary key auto_increment, 
   	username varchar(255)
   );

   如果插入不给值, 就默认自增

   insert into t_user(username) value('a')

   Oracle 的自增机制叫做序列(sequence)

4. 外键约束 (foreign key)

   业务背景:

   设计数据库表, 用来维护学生和班级的信息?

   1. 第一种, 一张表储存所有数据—–冗余

   2. 第二种, 两张表

      t_class

      cno(primary) cname

      ---

      t_student

      sno(primary) sname cno(foreign key)

      ---

   t_student 中的classno字段引用t_class中的cno字段, 此时t_student叫子表, t_class叫父表

   删除数据的时候, 先子后父

   添加数据的时候, 先父后子

   创建表的时候, 先父后子

   删表的时候, 先子后父

   create table t_class(
   	cno int,
   	cname varchar(255),
   	primary key(cno)
   );
   
   create table t_class(
   	sno int, 
   	sname varchar(255),
   	classno int,
       primary key(sno),
   	foreign key(classno) reference t_class(cno)
   );

   外键可以为NULL

   外键引用的字段可以不是主键, 但是必须有唯一性约束

5. 检查约束 (check) —– Oracle数据库有check约束, mysql没有

存储引擎

完整的建表语句:

create table t_use(
	id int DEFAULT NULL,
)ENGINE = InnoDB DEFAULT CHARSET=utf-8;

InnoDB引擎是MySQL的默认建表存储引擎

Oracle里的对应的功能就叫存储方式, 不叫存储引擎

每个存储引擎各有优缺点

查看当前MySQL支持的存储引擎:

show engines \G

常见的存储引擎

• MyISAM
  • 不支持事务—Transaction: NO
  • 是最常用的存储引擎, 但不是默认的
  • 使用三个文件表示每个表
    • 格式文件(.frm)
    • 数据文件(.MYD)
    • 索引文件(.MYI)
  • 可以压缩, 节省空间, 转换为只读
• InnoDB
  • 支持事务
  • 这种存储引擎最安全
  • 行级锁
  • 无法被压缩
  • 支持级联删除和级联更新
• MEMORY存储引擎
  • 不支持事务
  • 数据容易丢失
  • 数据存储在内存中
  • 查询数据快

​

事务

概念:

一个事务是一个完整的业务逻辑单元, 不可再分

想要保证两条或以上的DML语句同时成功或者同时失败, 那么就需要事务机制

原理:

执行语句, 成功之后, 记录到历史操作, 不会真正修改硬盘上的数据

知道事务提交或者回滚, 事务就结束了, 硬盘上的内容就修改了, 所有历史清空

事务的特性:

ACID:

A:原子性–事务是最小的工作单元

C:一致性–事务必须同时成功或者同时失败

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

D:持久性–持久性说的是最终数据必须持久化到硬盘中, 事务才算成功的结束

事务之间的隔离性

隔离性存在隔离级别, 理论上隔离级别包括4个

1. 读未提交(read uncommited) —对方事务还没提交, 我们当前事务可以读取到对方未提交的数据, 读未提交存在赃读(Dirty Read)现象

2. 读以提交(read commited)—对方事务提交之后我方可以读取到

   • 问题是不可重复读 (读的到别人提交后的数据)
   • 解决了赃读现象

3. 可重复读(repeatable read)

   • 解决了不可重复读(读不到的事别人)
   • 问题是, 读取到的数据是幻象

4. 序列化/串行化 (serializable)

   • 解决了所有问题
   • 效率低, 要排队

oracle默认级别是2, MySQL默认是3

演示事务

• MySQL事务默认情况下是自动提交的

  • (只要执行一条DML则语句提交一次) start transcation (关闭自动提交)

• 准备表

  create table t_user(
  	id int primary key auto_increment,
  	username varchat(255)
  );

• 演示

  start transcation;
  insert into t_user(username) value(lizi);
  insert into t_user(username) value(zhangsan);
  rollback;

设置隔离级别

set global transaction isolation level read uncommitted

查看隔离级别

select @@global.tx.isolation;

索引

索引就是目录, 在数据库方面, 查询一张表时有两种方法

​ 第一种: 全表扫描

​ 第二种: 根据索引检索(效率很高)

索引提速的原理是缩小了扫描范围

索引是给某个字段, 或者说某些字段添加索引

select ename, sal from emp where ename= 'SMITH';

以上语句在ename没有索引的时候会进行全表扫描, 扫描ename字段所有值

ename有索引以后, sql会根据索引扫描, 快速定位

什么时候考虑给字段添加索引

• 数据量庞大
• 该字段很少DML操作
• 改字段经常出现在where语句中

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

根据主键检索效率较高, 尽量根据主键检索

添加索引

create index emp_sal_index on emp(sal)

索引底层采用的数据结构是 B + Tree, 然后通过物理地址定位表中的数据

索引分类:

• 单一索引
• 复合索引
• 主键索引
• 唯一索引

索引什么时候失效

select ename from emp where ename like '%A%';

模糊查询, 第一个通配符的是%, 这个时候索引是失效的.

视图

同一张表的数据, 通过不同角度去看

create view myview as select empno, ename, from emp;
drop view myview

对视图进行增删改查, 会影响到原表数据 (通过视图影响原表数据的, 不是直接操作的原表)

可以对视图进行CRUD操作

视图可以隐藏表的实现细节, 保密级别较高的系统, 数据库只对外提供相关视图, java程序员只对视图对象进行CRUD

DBA命令

导入导出

mysqldump bjpowernode>D:\bipowernode.sql -uroot -p333

create database bjpowernode;
use bjpowernode;
source D:\bjpoernode.sql

数据库设计三范式

为了解决数据冗余

三范式:

1. 第一范式: 任何一张表都应该有主键, 并且每个字段原子性不可再分

2. 第二范式: 每个非主键字段都应该完全依赖主键, 不产生部分依赖

   1. 多对多? 三张表, 关系表两个外键

3. 第三范式: 建立在第三范式的基础之上, 所有非主键字段直接依赖主键字段, 不能产生传递依赖

   1. 一对多? 两张表, 多的表加外键

提醒: 实际的开发中, 以满足客户的需求为主, 有的时候会拿冗余换执行速度

一对一怎么设计:

两种方案:

• 主键共享, 附表的主键同时也是外键
• 外键唯一, 加一个外键, 外键加上unique约束

作业题

第一题: 取得每个部门最高薪水的人员名称

我的答案:

select ename, deptno, max(sal) from emp group by deptno

标准答案:

第一步

select deptno, max(sal) as maxsal 
from emp 
group by deptno

第二步

select e.ename 
from (select deptno, max(sal) from emp group by deptno) t
join emp e
on e.deptno = t.deptno and e.sal = t.maxsal

第二题: 哪些人的薪水在部门平均薪水之上

select emp.name, t.*
from emp
join (select deptno, avg(sal) from emp group by dep) t
on emp.sal>t.sal and emp.deptno>t.deptno

第三题: 取得部门中所有人的平均的薪水等级

select avg(s.grade), e.ename
from emp e
join salgrade s
on e.sal between s.losal and s.hisal 
order by e.deptno

基于以上结果按deptno分组, 求 grade平均值

第四题, 不能用组函数max, 取得最高薪水

1. 第一种方案: 降序, limit 1

2. max函数

3. 表自连接:

select sal from emp where sal not in
(select distinct a.sal from emp a
join emp b 
on a.sal<b.sal)

第五题: 取得平均薪水最高的部门的部门编号

select avg(sal) from emp group by deptno

select t. deptno, max(t.sal) //可能不对
from (select avg(sal) as sal from emp group by deptno) t

第二种方案：

select deptno, avg(sal) as avgsal from emp group by deptno
order by avgsal DESC limit 1

第六题: 取得平均薪水最高的部门的部门名称

select e.deptno, avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname
order by avgsal desc limit 1