Mysql基础

Mysql操作语句

• 登陆mysql数据库管理系统
  在终端输入mysql -uroot -p
  【注】没写u报错：Ignoring query to other database解决方法

• 查看有哪些数据库

  show databases;
  

  【注】别忘了分号

• 创建数据库

  create database Test;
  

• 使用Test数据库

  use Test;   
  

• 查看数据库表的信息

  show tables;
  

• 初始化信息（建完表保存为.sql文件后）

   source xxxx 
  

  【注】xxxx为.sql文件路径

当一个文件的拓展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本

• 删除数据库

  drop database XXX;
  

• 查看表结构

  desc 表名;
  

• 查看表中数据

  select * from 表名;
  

• 常用命令：
  • select version(); 查看版本号
  • select database(); 查看当前使用哪个数据库
  • exit 退出mysql

sql语句

【注】不区分大小写

简单的查询语句（DQL）

• select 字段名1 ，字段名2 … from 表名;
  【注】字段可以参与数学运算

  • 查询结果集的去重

  select distinct birthDate from passenger;
  

  【注】distinct 只能出现在所有字段的最前面，是后面所有字段联合起来去重。

  e.g 统计岗位的数量:

  select count(distinct job) from passenger;
  

• 给查询结果的列重命名
  select 字段名1 ，字段名2 as xxx from 表名;
  【注】别名中有中文，用 ‘ ’

条件查询

语法格式

select
		   字段，字段...
	from 
		   表名
	where
		   条件

查找在区间范围内的值

select ID from depoit where deposit between 100 and 200;

【注】 between 为闭区间

is null & is not null

空不是一个值，不能用等号衡量，必须使用is null 或者is not null

and 和 or 联合起来用

注意 and 的优先级最高，需要加括号，否则查询出错。

in 等同于 or

select ename,job from emp where job = 'teacher' or job = 'manager';
select ename,job from emp where job in('teacher','manager');

not in 不在这几个值当中

模糊查询 like

% 代表任意多个字符

找出名字中有Z的：

select ename form emp where ename like '%Z';

_ 代表任意一个字符

找出名字中第三个字符为Z的：

select ename from emp where ename like '_ _Z%';

找出数据中包含下划线的：

select ename from emp where ename like '%\_%'

找出数据中最后一个字母为 S 的：

select ename from emp where ename like '%S'

排序

语法格式

select
		   字段，字段...     3
	from 
		   表名             1
    where
          条件              2
	order by
		   条件             4

注意：默认是升序。order by 是最后执行的。

• asc表示升序
• desc表示降序

e.g

select ename,sal from emp order by sal ;//默认升序
select ename,sal from emp order by sal desc;//降序

查询时可以不写字段值，写该字段值所在的列数，但不推荐

多个字段排序

越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。
e.g 按照工资的降序排列，当工资相同时再按照名字的升序排列。

select ename,sal from emp order by sal desc,ename asc ;

分组函数/聚合函数/多行处理函数

分组函数（多行处理函数）

• count 计数
• sum 求和
• avg 平均
• max 最大值
• min 最小值

所有的分组函数都是对“某一组” 数据进行操作的。输入多行，最终输出一行。且 分组函数自动忽略 null

【注】分组函数不能直接出现在 where 语句中
e.g 找出工资高于平均工资的员工

• 第一步：找出平均工资

select avg(val) from emp;

• 第二步：找出高于平均工资的员工

select ename,sal from emp where sal > xxx;

• 整合（子查询）

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

select
		   字段，字段...     5
	from 
		   表名             1
    where
           条件             2
	group by              
	       ..              3
	having 
	       ..              4
	order by
		   条件             6

• count（*）：不是统计某个字段数据的个数，而是统计总记录条数。（和某个字段无关）
• count（某个字段）：表示统计该字段中不为 null 的数据总数量。
• 分组函数也能组合起来用

单行处理函数

单行处理函数：输入一行，输出一行

只要有 null 参与的运算，结果一定为 null

• ifnull(可能为 null 的数据，被当作什么处理)

group by & having

• group by：按照某个字段或者某些字段进行分组
• having：分组之后的数据进行再次过滤
  e.g 找出每个工作岗位的最高薪资

select max(sal)  from emp group by job;

【注】分组函数一般都会和 group by 联合使用。
任何一个分组函数（count sum avg max min）都是在 group by 语句执行完之后才会执行。当一条 sql 语句没有group by 的话，整张表的数据会自成一组。
【规则】：当一条语句中有 group by 的话，select 后面只能跟分组函数和参与分组的字段。

• 多字段分组查询
  e.g 找出每个部门 不同工作岗位的最高薪资

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

• having 的使用
  e.g 找出每个部门薪资大于2900的

select max(sal),deptno from emp group by deptno having max(sal) > 2900;     效率低
select max(sal),deptno from emp where sal > 2900 group by deptno ;          效率高

【注：有时候必须要使用 having 】
e.g 找出每个部门的平均薪资，要求显示薪资大于 2000 的数据。

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

连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

连接查询的分类

【根据表的连接方式来划分】

• 内连接：
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接
  • 右外连接
• 全连接

笛卡尔乘积现象

当两张表进行连接查询的时候，没有任何条件进行限制，最终查询结果条数是两张表记录条数的乘积。

内连接之等值连接

最大特点：条件是等量关系

语法：

select 
   ...
from
   A
join
   B
on
   连接条件
where
   ...

e.g 查询每个员工的部门名称，要求显示员工名和部门名

select 
   e.name,d.dname
from
   emp e
inner join        //inner可以省略
   dept d
on
   e.deptno = d.deptno

内连接之非等值连接

on 后的条件是一个不等关系

自连接

一张表看作两张表。自己连接自己。

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

select 
   a.ename as '员工名' , b.ename as '领导名'
from 
   emp a
join 
   emp b
on 
   a.mgr = b.empno;

外连接

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

• 左外连接（左连接）：表示左边这张表是主表
• 右外连接（右连接）：表示右边这张表是主表
• 左连接有右连接的写法，右连接也有左连接的写法

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

select 
   a.ename as '员工名' , b.ename as '领导名'
from 
   emp a       //以a为主表，若a中有 null，自动模拟出 null 匹配
left join 
   emp b
on 
   a.mgr = b.empno;

• 外连接最重要的特点是：主表的数据无条件的全部查询出来

多表连接查询

语法结构：

...
from 
   A
join 
   B
join 
   C
on 
   ...;

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

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

子查询

select 语句当中嵌套 select 语句，被嵌套的 select 语句是子查询

• where 后嵌套子查询
• from 后嵌套子查询

e.g 找出每个部门平均薪水的薪资等级
第一步：找出每个部门的平均薪水（按部门编号分组，求 sal 的平均值）

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

第二步：将以上查询结果当作临时表 t ，让 t 表和 salgrade s 表连接，连接的条件是：t.avgsal between s.losal and s.hisal

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

• 在 select 后面嵌套子查询

union

union 可以将结果集相加

e.g 找出工作岗位是 SALESMAN 和MANAGER 的员工

select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';

limit分页查询

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

语法机制：

limit startIndex,length //startIndex 表示起始位置，从0开始，0表示第一条数据
                        //length 表示取几个

【注】limit 是 sql 语句执行的最后一个环节
e.g 找出工资排名在第4到第9名的员工

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

• 通用的标准分页 sql
  每页显示 pageSize 条记录：
  第 pageNo 页：（pageNo - 1）* pageSize，pageSize

数据定义语言DDL

创建表

• 创建表的语句

create table 表名(
    字段名1 数据类型 约束
    字段名2 数据类型 约束
    字段名3 数据类型 约束
    ...);

• 常见的字段的数据类型

数据类型	含义
int	整数型
bigint	长整型（java 中的 long）
float	浮点型
char	定长字符串
varchar	可变字符串
date	日期类型（对应 java 中的 java.sql.Date 类型）
BLOB	进制大对象（存储图片、视频等流媒体信息）
CLOB	字符大对象（存储较大文本，比如，可以存储4G的字符串）

• char 和 varchar 怎么确定
  • 若某个字段的数据长度不发生改变的时候，是定长的，例如：性别、生日，采用 char
  • 若某个字段的数据长度不确定，例如：简介、姓名等都是采用 varchar
• 表名在数据库当中一般建议以：t_ 或者 tbl_ 开始

create table t_student(
     num bigint,
     name varchar(255),
     birth char(10)
);   

插入数据 insert

• 语法格式：

insert into 表名(字段名1，字段名2，...）values(值1，值2，...）

【注】字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(num,name,birth) values(1,'wangwu','1980-9-10');

【注】

• 可以调换字段的顺序、或者只添加一个字段。但是待 insert 语句执行成功之后，记录当中其他字段是则是 null，后期也没有办法在执行 insert 语句插入数据了，只能用 update 进行更新。
• 若字段省略，value 就要按照顺序写

insert into t_student values(1,'wangwu','1980-9-10');

• 一次插入多行

insert into t_student(num,name,birth) values(1,'wangwu','1980-9-10'),(2,'zhangsan','1981-9-10');

表的复制

• 语法

create table 表名 as select 语句;   //将查询结果当作表创建

• 将查询结果插入到一张表中

insert into 表名1 select * from 表名2;

修改数据 update

• 语法格式

update 表名 set 字段名1 = 值1,字段名1 = 值1... where 条件; //若全改，则不加where

删除数据 delete

• 语法格式

delete from 表名 where 条件; //若删除全部，则不加where

• 删除大表 （重点）

truncate table 表名; //表被截断，不可回滚，永久丢失

约束

什么是约束

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

常见的约束

• 非空约束 （not null）：约束的字段不能为空
• 唯一约束 （unique）：约束的字段不能重复
• 主键约束 （primary key）：约束的字段既不能为 null ，也不能重复（简称PK）
• 外键约束（foreign key）：…（简称FK）
• 检查约束（check）：Oracle 数据库有，mysql 没有

唯一性约束

• 唯一约束修饰的字段具有唯一性，不能重复。但可以为 null
• 给多个列添加 unique

drop table if exists t_user;
create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255)
    unique(usercode,username) //多个字段联合起来添加1个约束【表级约束】
);

【注】和在usercode、username 后分别加 unique 的区别！（列级约束）分别加只要有一个相同即重复，而联合则是判断连在一起是否重复。

主键约束

主键字段中的数据不能为 null ，也不能重复

drop table if exists t_user;
create table t_user(
    id int primary key,
    usercode varchar(255),
    username varchar(255)
);

• mysql提供主键值自增

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,
    usercode varchar(255),
    username varchar(255)
);

外键约束

A 表中的字段引用 B 表中的字段，则称 A 表为子表，B 表叫父表

• 顺序要求
  1、删除数据时，先删除子表，再删除父表。
  2、添加数据时，先添加父表，再添加子表。
  3、创建表时，先创建父表，再创建子表。
  4、删除表时，先删除子表，再删除父表。

create table t_user(
    id int ,
    usercode varchar(255),
    foreign key(classno) references t_class(cno)
);

【注】

• 外键值可以为 null
• 被引用的字段不一定是主键，但至少有 unique 约束

存储引擎

存储引擎这个名字只有在 mysql 中存在。mysql 支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式

• 查看当前的存储引擎

show engines XX

事务

什么是事务

一个事务是一个完整的业务逻辑单元，不可再分
事务的存在是为了保证数据的完整性，安全性

【注】和事务相关的语句只有 DML 语句 （insert delete update）
因为他们三个语句都是和数据库表当中的“数据”相关

事务的特性

事务包括四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分
C：一致性：事务必须保持多条DML语句同时成功或者失败
I：隔离性：事务A与事务B之间具有隔离性
D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功

事务的隔离性

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

• 第一级别：读未提交（read uncommiteed）
  我们当前事务可以读到对方未提交的数据
  读未提交存在脏读现象：表示读到了脏数据
• 第二级别：读已提交
  对方事务提交之后的数据我方可以读到
  存在的问题：不可重复读
• 第三级别：可重复读（repeatable read）
  这种隔离级别解决了 不可重复读
  存在的问题：读取到的数据是幻想
• 第四级别：序列化/串性化读
  解决了所有问题
  存在的问题：效率低

oracle 数据库默认的隔离级别是：读已提交
mysql 数据库默认的隔离级别是：可重复读

演示事务

【注】mysql 事务默认情况下是自动提交的（只要执行任意一条DML语句则提交一次）
如何关闭：start transaction

索引

什么是索引

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

• 全表扫描
• 根据索引检索（效率高）

索引采用的底层数据结构是：B + Tree

怎么创建、删除索引对象

创建：

create index 索引名称 on 表名（字段名）;

删除

create index 索引名称 on 表名（字段名）;

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

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

【注】主键和具有 unique 的约束字段会自动添加索引

视图（view）

站在不同的角度去看待数据

怎么创建、删除视图

create view myview as select name from student;
drop view myview;

【注】只有 DQL 语句才能以视图对象的方式创建出来

对视图进行增删改查，会影响到原表数据

视图的作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图。

作业题

部门表

员工表

薪资水平表

1. 取得每个部门最高薪水的人员名称

• 取得每个部门最高薪水

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

• 将以上结果当作临时表 t ，t 表和 emp e 表进行连接，条件是 t.deptno = e.deptno and t.maxsal = e. sal

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

2. 哪些人的薪水在部门的平均薪水之上

• 找出部门的平均薪水

select deptno,avg(sal) as avg from emp  group by deptno t

• 将以上查询结果当做t表，t 和 emp 表连接
  条件：部门编号相同，并且 emp 的 sal 大于 t 表的 avg

select 
    -> e.ename,e.deptno,e.sal
    -> from 
    -> emp e
    -> join 
    -> (select deptno,avg(sal) as avg from emp  group by deptno) t
    -> on e.deptno = t.deptno and e.sal > t.avg ;

3. 取得部门中（所有人的）平均的薪水等级

• 找出每个人的薪水等级 条件：e.sal between s.losal and s.hisal

select
    -> e.ename,e.sal,e.deptno,s.grade
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal;

• 基于以上的结果继续按照deptno分组，求grade的平均值。

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;

4. 不准用组函数（Max ），取得最高薪水

• 第一种 sal降序，limit 1

select ename,sal from emp order by sal desc limit 0,1; //0可以省略

• 第二种 用 max 函数

select max(sal) from emp;

• 第三种 表的自连接

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

5. 取得平均薪水最高的部门的部门编号

• 找出每个部门的平均薪水（表 t）

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

• 找出以上 avg 的最大值

select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

• 整合

select 
		deptno,avg(sal) as avgsal 
	from 
		emp 
	group by 
		deptno
	having
		avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

6. 取得平均薪水最高的部门的部门名称

• 连接两个表