MySql
MySql
MySql概述
DB:数据库
DBMS:数据库管理系统
SQL:
SQL语句分类
DQL:查询语句
DML:对表中的数据进行增删改, insert,delete,updata
DDL:对表结构的增删改
TCL:
DCL:grant授权,revoke撤销权限等
导入数据
什么是sql脚本?
文件的扩展名是.sql,这样的文件为sql脚本
直接使用source命令可以执行sql脚本
常用命令
select database(); 查看当前使用的是哪个数据库
select version(); 查看mysql版本号
\c 结束一条语句
exit ,quit,ctrl+c : 退出
show database
show tables
show creat table emp 查看创建表的语句
creat datebase 创建数据库
DQL语句
查询
简单的查询语句
select 字段名1,字段名2,… from 表名;
任何一条sql语句以“;”结尾
不区分大小写,但表中数据区分
查询后重命名可用as或者空格
字符串用单引号‘’
查询所有字段:select * from emp;(实际开发中不建议使用 )
条件查询:
格式:select 字段,字段,… from 表名 where 条件;
执行顺序:先from,然后where,最后select
between a and b : [a,b] ,若是字符,则左闭右开
<> 或者 != 不等于
< <= > >=
and 并且
or 或者
in (等同于or): in(‘a’,‘b’); 不是区间,是具体的值
not in: 不在这几个值之间
注意优先级,不确定加括号
NULL ?
在数据库中NULL不是一个值,代表什么也没有,为空
不是一个值,不能用等号衡量
必须使用is null 或者 is not null
模糊查询
用 like
% 代表任意多个字符
_ 代表任意一个字符
例:
找名字含o的:like ‘%o%’
找第二个字母是A的:like ‘_A%’
找含下划线的:like ‘%_%’ ; 使用转义字符
找最后一个字母是T的: like ‘%T’
排序
越靠前越起主导作用
select 字段,字段,… from 表名 order by 字段 ; //默认升序
select 字段,字段,… from 表名 order by 字段 asc; //asc表示升序排
select 字段,字段,… from 表名 order by 字段 desc; //desc表示降序
select 字段1,字段2,… from 表名 order by 字段2 desc,字段1 asc ; //先按字段1降序,相等时再按字段2升序
select * from 表名 where 条件 order by … ; 执行顺序:from—>where—>select—>order
分组函数
也叫:多行处理函数。输入多行,输出一行。
count 计数
sum
avg
max
min
所有的分组函数都是对“某一组”数据进行操作的
都自动忽略null
分组函数不可直接出现在where中,一般和group by连用(因为group by是在where之后才会执行)
单行处理函数:输入一行,输出一行。
只要有NULL参与的运算,结果都是NULL(所有数据库中)
ifnull()空处理函数?
用法:ifnull(可能为NULL的数据,被当做什么处理)
属于单行处理数据
count(*) 和 count(具体某个字段)?
count(*):不是统计某个字段中数据的个数,而是统计总记录条数
count(字段):统计字段中不为null的数据总量
分组函数可以组合使用
select count(*),sum(字段),avg(字段),max(字段),min(字段),from…;
分组函数一般和group by 联合使用,并且一般在group by之后执行
当一组数据没有group by时,整个表的数据自成一组
group by 和 having ?
group by :按照某个字段或者某些字段进行分组
having:是对分组之后的数据进行再次过滤,和group by连用
例:找每个工作岗位的最高薪资:select job, max(sal) from emp group by job;
规则:当一条语句中有group by的话,select后面只能跟分组函数与参与分组的字段
select中可以嵌套
一个完整的DQL语句书写
select…from…where…group by…having…order by…
顺序:from->where->group by->having->select->order by
多个字段能不能联合起来一块分组?
例:找不同部门不同岗位的最高工资:
select 部门,岗位,max(薪资) from 表名 group by 部门,岗位;
having 和 where ?
1、例:找每个部门的最高薪资,要求显示薪资大于2500的数据
第一步:select max(薪资),部门 from 表名 group by 部门;
第二步:select max(薪资),部门 from 表名 group by 部门 having max(薪资) > 2500; //效率低
改: select max(薪资),部门 from 表名 where 薪资 > 2500 group by 部门; //效率较高一般能用where就尽量用这个
having 是对分组之后的数据再次过滤,是group by的搭档2、例:找每个部门的平均薪资,要求显示薪资大于2500的数据
第一步:select 部门,avg(薪资) from 表名 group by 部门;
第二步:select 部门,avg(薪资) from 表名 group by 部门 having avg(薪资) > 2500;
关于查询结果的去重? distinct关键字
只需在查询字段前面加distinct
例: select distinct job from emp;
distinct只能出现在所有字段的最前面,表示后面字段联合去重
连接查询
概念
在实际查询中,一般都会有多张表联合查询
分类
按语法出现的年代划分:
SQL92
SQL99根据表的连接方式划分:
内连接:等值连接、非等值连接、自连接
外连接:左外连接、右外连接
全连接:
笛卡尔积现象
若是两张表连接查询没有条件限制,结果显示是两张表记录条数的乘积
例:找出每一个员工的部门名称select e.员工名,d.部门名 from 员工表 e,部门表 d;
加别名:效率高,可读性高如何避免?
加条件过滤,次数不变,显示会变成有效记录
select e.员工名,d.部门名 from 员工表 e,部门表 d where e.部门名=d.部门名 ; //SQL92,日常不用
内连接
内连接的等值连接
特定:条件是等量关系
例:select e.员工名,d.部门名 from 员工表 e inner join 部门表 d on e.部门名=d.部门名 ; // SQL99 常用 ,inner可省略,带着可读性好,知道是内连接
语法:… A join B on 连接条件 where …
好处:表连接条件和where条件分离了
内连接的非等值连接
特点:连接条件中的关系是非等量关系
例:找出每一个员工的工资等级,显示员工名,工资,工资等级select e.员工名,e.工资,s.等级 from 员工表 e inner join 工资等级表表 s on e.工资 between s.最低工资 and s.最高工资;
内连接的自连接
特点:一张表看做两张表,自己连接自己
例:找出员工的上级领导,要求显示员工名和对应的领导名员工表 emp a; 领导表 emp b
select a.ename,b.ename from emb a inner join emb b on a.mgr=b.empno;
外连接
内连接 、外连接 的区别?
内连接:假设A和B表进行连接,使用内连接的话,反射A和B能够匹配上的记录查出来,就是内连接。没有主副之分,是平等的
外连接:假设A和B表进行连接,使用外连接的话,AB两个表中有主副之分,主要查主表的数据,当副表的数据没有和主表的匹配上,副表自动模拟出NULL和他匹配
分类
左(外)连接:表示左边的这张表是主表
右(外)连接:表示右边的这张表是主表
左连接有右连接的写法,右也有左的写法
例:找每个员工的上级领导
员工表 emp a; 领导表 emp b
select a.ename’员工’,b.ename’领导’ from emb a left outer join emb b on a.mgr=b.empno; //left表示左边是主表 若换成right是右连接,outer表示外连接,可省略
注:区分内外连接靠的是left 和 right
外连接特点:主表数据无条件查询出来
3张以上表连接查询
例:找出每个员工的部门名称以及工资等级
注:… a join b join c on…表示a和b先进行连接,连接之后a继续和c连接
select
e.ename,d.dname,s.qrade
from
emp e
join
dept d
on
e.depton=d.deptno
join
salqrade s
on
e.sal between s.losal and s.hisal;
子查询
select语句当中嵌套select
可以出现在哪里?
select…(select). from…(select). where…(select).
where中使用子查询
例:找出高于平均薪资的员工信息
select * from emp where sal>(select avg(sal) from emp);
from 后嵌套
例:找出每个部门平均薪水的等级
第一步:select deptno,avg(sal) from emp group by deptno;
第二步:例:找出每个部门薪资等级的平均值
第一步:select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
第二步:select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and hisal group by e.deptno;
在select后嵌套
例:找出每个员工所在的部门名称,要求显示员工名和部门名
select e.ename,e.deptno,(select d.dname from dept d where e.empno=d.deptno ) as d.dname from emp e;
union (可以将查询结果集相加)
例:找出工作岗位是A和B的员工?
第一种:select e.ename,job from emp where job=‘A’ or ‘B’;
第二种:select e.ename,job from emp where job in(‘A’ , ‘B’);
第三种:select ename,job from emp where job=‘A’
union
select ename,job from emp where job=‘B’;
两张不相干的表中的数据连接可用union;
注:上下两个查询语句的列数应相同
limit(分页查询)
mysql查询专有
取结果集中的部分数据
语法:
limit startIndex,length
其中:startIndex表示起始位置,length表示取几个
例:取出工资前5名的员工
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5; //只写一个数字,则起始默认0;
limit是最后执行的
通用的标准分页sql?
例:每页显示pageSize条记录
第pageNo页:pageSize*(pageNo-1),pageSize;
创建表
建表语句的语法格式:
creat table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,...
);
mysql字段的数据类型
int 整数型
bigint 长整形
float 浮点型
char 定长字符串
varchar 可变长字符串
date 日期类型
BLOB 二进制大对象(存储图片,视频等) Binary Large OBject
CLOB 字符大对象(存储较大文本)Character Large OBject
char 和varchar ?
当某个字段中的数据长度不发生改变的时候,是定长的
BLOG 和 CLOB 的使用?
BLOG:
CLOB:
数据库中,表名一般以t_或tbl_开始。
创建学生表: 学号:bigint ,姓名:varchar ,性别:char 班级编号:int ,生日:char
creat table t_student(
no bigint,
name varchar,
sex char,
classno varchar(255),
birth char(10)
);
DML
插入数据 insert
语法: insert into 表名(字段名1,字段名2,......)values(值1,值2,......)
要求:字段的数量和值得数量相同,并且数据类型要对应相同
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan 1 ban ','1997-10-15');
insert into t_student(name) values('zhangsan'); //若只写一个字段,则其他字段自动插入NULL;
drop table if exists t_student; //若表存在则删除
creat table t_student(
no bigint,
name varchar,
sex char(1) defalt 1, //sex 的默认值是1
classno varchar(255),
birth char(10)
);
//
insert into t_student(name) values('zhangsi'); //此时sex也有值,是1;
注:当一条语句insert语句执行成功之后,表格当中必然会多一行记录,后期只能用update更新
insert into t_student values(2,'jack','0','gaosan 2 ban','1996-01-05'); //若前面字段省略了,则后面不可缺省
//一次插入多行
insert into t_student(no,name,sex,classno,birth) values(3,'zhangwu','1','gaosan 3 ban ','1995-10-15'),(4,'zhangliu','1','gaosan 4 ban ','1995-11-15');
表的复制:
语法:
creat table 表名 as select 语句;
将查询结果当做表创建出来。
将查询结果插入表中
语法:
insert into dept1 select *from dept;
修改数据 update
语法:update 表名 set 字段名=值1,字段名2=值2,... where 条件;
注:若没有条件,则整张表数据全部更新
更新所有记录,不加where就行
删除 delete
语法:delete from 表名 where 条件;
没有条件则全部删除
删除所有记录:delete from 表名;不加where
怎么删除大表?
语法:
truncate table emp1;
//表被截断,永久删除
删除表?
语法:
drop table 表名;
对于表结构的修改,一般使用工具完成即可,不会出现在java代码中
增删改查的术语:CRUD操作
Creat Retrieve(检索) Update Delete
约束(Constraint)
约束?
保证表中数据的合法性,有效性,完整性
有哪些?
非空约束(not null):约束的字段不能为空
唯一约束(unique):不能重复
主键约束(primary key):既不能为空,也不能重复(简称PK)
外键约束(foreign key):…简称FK)
检查约束(check):
非空约束
只能在字段后面加
drop table if exists t_student;
creat table t_student(
no bigint,
name varchar(255) not null, //则插入时name必须传入
sex char(1) defalt 1, //sex 的默认值是1
classno varchar(255),
birth char(10)
);
唯一约束 unique
字段具有唯一性,不能重复,但可以为NULL
可以给两个列或多个列加unique
多个字段联合添加unique
drop table if exists t_student;
creat table t_student(
no bigint,
name varchar(255),
sex char(1) ,
unique(n0,name) //两个联合不可重复 (表级约束)
);
drop table if exists t_student;
creat table t_student(
no bigint unique,
name varchar(255) unique, //列后添加, (列级约束)
sex char(1) ,
);
主键约束
drop table if exists t_student;
creat table t_student(
no bigint primary key, //(列级约束)
name varchar(255) ,
sex char(1) ,
);
insert into t_student(no,name,sex) value(1,'zhang',1);
主键特点:不能为空,也不能重复
相关术语:
主键约束:primary key
主键字段:no
主键值:主键字段添加的值
作用?
表的设计三范式中,第一范式就要求任何一张表都有
唯一标识
分类?
根据主键字段的字段数量划分:
单一主键(常用)
复合主键根据主键性质划分:
自然主键(推荐)
业务主键:主键值和系统的业务挂钩
最好不要拿着和业务挂钩的字段作为主键一张表的主键约束只能有1个
使用表级约束方式定义主键
drop table if exists t_student;
creat table t_student(
no bigint ,
name varchar(255) ,
primary key(no)
);
mysql提供主键值自增:auto_increment
drop table if exists t_student;
creat table t_student(
no bigint primary key auto_increment, //no字段自动维护一个自增的数字,从1开始,以1自增
name varchar(255) ,
);
外键约束
相关术语:
外键约束:foreign key
外键字段:
外键值:外键字段添加的值
先删子表,再删父表
先创父表,再创子表
drop table if exist t_student;
drop table if exist t_class;
creat table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
creat table t_student(
sno int,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)//子表外键引用父表主键
);
外键值可以为NULL;
外键字段引用其他表的某个字段时,被引用的表不一定是主键,但要具有唯一性;
存储引擎(了解就行)
是表的一种存储方式
mysql默认使用的存储引擎是InnoDB
默认采用的字符集是UTF-8
mysql支持很多存储引擎
查看当前支持的存储引擎?
show engines \G
常见的?
MyISAM
最常用的,但不是默认的,采用三个文件组织一张表
优点:可可被压缩,节省空间
缺点:不支持事物InnoDB
优点:支持事物,行级锁,支持级联删除、级联更新,
MEMORY
缺点:不支持事物,数据容易丢失
优点:查询速度快
事物
概念?
一个事物,是一个完整的业务逻辑单元,不可再分
多条语句同时成功,同时失败
和事物有关的语句:insert,delete,update
原理:
commit:提交
rollback:回滚
四大特性?ACID
原子性A:是最小的工作单元,不可再分
一致性C:必须保证多条DML语句同时成功或同时失败
隔离性I:事物A与事物B之间具有隔离
持久性D:最终必须持久化到硬盘文件中
隔离性存在隔离级别,理论上包括4个:
第一级别:读未提交(read uncommitted)
对方事物还没有提交,当前事物可以读取到对方未提交的数据
存在脏读现象:表示读到了脏的数据第二级别:读已提交(read committed)
对方事物提交后的事物可以读取
解决了脏读现象,但不可重复读第三级别:可重复读(repeatable read )
解决了不可重复读的问题,但读取到的数据是幻想
第四级别:序列化读/串行化读
解决了所有问题,但效率低
mysql事物默认自动提交
怎么关闭?
start transaction;
怎么提交?commit:
演示
第一级别:
设置事物的全局隔离级别
set global transaction isolation level read uncommitted;
查看事物的全局隔离级别:
mysql>select @@global.tx isolation;
第二级别:
第三级别:
第四级别:
索引
概念、作用
相当于目录 ,是给某些字段添加索引
两种检索方式:全表扫描
根据索引检索 (效率高,根本原理是缩小了扫描的范围)
怎么创建索引
格式:creat index 索引名称 on 表名(字段名);
例: creat index emp_sal_index on emp(sal);
怎么删除
格式:drop index 索引名称 on 表名;
什么时候给字段添加索引
数据量庞大
该字段很少DML操作 (因为字段进行修改操作,索引也需维护)
该字段经常出现在where语句中(经常根据哪个字段查询)
主键和具有unique约束的字段会自动添加索引
底层采用的数据结构是:B树
实现原理:
通过B树缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的
分类:
单一索引:
复合索引:多个字段联合添加一个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
…
索引什么时候失效?
select ename from emp where ename like’%A%’;
模糊查询的时候,第一个通配符使用%
视图(view)
同一张表的数据,通过不同的角度去看待
怎么创建?怎么删除?
creat view myview as select empno,ename from emp;
drop view myview;
只有DQL语句可以创建视图
对视图进行增删改查,会影响到原表数据
可以对视图进行CRUD操作
面向视图操作?
select * from myview;
update myview set 字段名=值,…; //通过视图修改原表数据
delete from myview1 where 字段名=值; //通过视图删除原表数据
视图的作用?
可以隐藏表的实现细节。保密级别较高的系统,只对外提供视图
导入导出
数据导出
在windows的dos命令窗口中执行
mysqldump xxx>D:\xxx.sql -uroot -p (导出整个库)
mysqldump xxx emp>D:\xxx.sql -uroot -p (导出指定数据库当中的指定表)
数据导入
creat database xxx;
use xxx;
source D:\xxx.sql
数据库设计三范式
概念?
设计表的依据。不会出现数据冗余
第一范式:
任何一张表都应该有主键,并且每个字段具有原子性
第二范式:
第一范式的基础上
所有非主键字段完全依赖主键,不能产生部分依赖
多对多?三张表,关系表两个外键
第三范式:
建立在第二范式的基础上
非主键字段不能传递依赖于主键字段,不能产生传递依赖
一对多?两张表,多的表加外键
实际上,以满足客户的需求为主,有的时候会拿冗余换执行速度
一对一怎么设计?
t_user_login 用户登录表
t_user_detail 用户详细信息表