MySQL的学习
- MySQL
- 分组函数
- 分组查询
- group by
- having
- 去除重复记录
- select总结
- 连接查询
- 连接查询的分类
- 连接查询原理及笛卡尔积现象
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 内连接和外连接的语法
- 3张表以上表的连接查询
- 子查询
- where后嵌套子查询
- from后嵌套子查询
- select后嵌套子查询
- union
- limit和分页
- 表的操作
- 创建表create
- 插入数据insert
- 修改数据update
- 删除数据delete
- 增删改表的结构
- 约束
- 什么是约束?
- 唯一性约束(unique)
- 主键约束(primary key)
- 相关术语:
- 主键分类:
- 主键值自增:
- 外键约束
- 相关术语
- 举例理解外键约束
- 注意点
- 事务
- 什么是事务?
- 事务的四个特征CAID
- 事务之间的隔离性
- MySQL中的事务
- 隔离级别与一致性问题的关系
- 索引
- 什么是索引?
- 添加/删除索引
- 索引的实现原理
- 索引的分类
- 索引什么时候失效?
- 视图
- 什么是视图
- 创建/删除/修改视图
- DBA
- 将数据库当中的数据导出
- 导入数据
- 数据库设计三范式
- 什么是设计范式
- 第一范式
- 第二范式
- 第三范式
- 表的设计
- 一对一
- 一对多
- 多对多
MySQL
分组函数
- count 取得记录数
- sum 求和
- avg 取平均值
- max 取最大值
- min 取最小值
注意:分组函数将自动忽略null值,因此在where中不需要手动添加条件排除空值
分组函数不能用于where后!!!(因为分组在group by执行后执行)
分组函数可以一起使用,例:
select count(name),avg(age),max(grade) from student;
分组查询
- group by 按照某个(些)字段进行分组
- having 对分组之后的数据进行再次过滤
group by
取得每个工作岗位的工资合计, 要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;
若使用了order by,排序一定在分组之后
在 SQL 语句中若有 group by 语句, 那么在 select 语句后面只能跟分组函数+参与分组的字段。
例:
select ename max(sal),job from emp group by job
该语句在Oracle数据库下会报语法错误,在MySQL下不会,但是会随机选取几个ename值,和查询结果无法对应。
多个字段联合分组,例 :查询每个部分不同工作岗位的最高薪资?
select job,deptno,max(sal) from emp group by job,deptno;
having
例:找出每个部门的最高薪资,要求显示薪资大于2500的数据
select max(sal),deptno from emp group by deptno having max(sal)>2500;
上面的语句执行效率低,建议使用where
select max(sal),deptno from emp where sal>2900 group by deptno;
去除重复记录
distinct
select distinct job from emp;
distinct 只能出现在字段最前,后面的字段联合起来去重。
select总结
一个完整的select语句如下:
select 字段
from 表名
where ...
group by ...
having ...(不可单独出现,和分组共同使用)
order by ...
以上语句的执行顺序
- 首先执行 where 语句过滤原始数据
- 执行 group by 进行分组
- 执行 having 对分组数据进行操作
- 执行 select 选出数据
- 执行 order by 排序
**原则:**能在 where 中过滤的数据, 尽量在 where 中过滤, 效率较高。
连接查询
也可叫做跨表查询,表示从多表联合中进行查询,取出最终结果。
连接查询的分类
根据语法出现的年代来划分,包括:
- SQL92
- SQL99
根据表的连接方式来划分,包括:
内连接:
- 等值连接
- 非等值连接
- 自连接
外连接:
- 左(外)连接
- 右(外)连接
全连接(很少用)
连接查询原理及笛卡尔积现象
例:找出每一个员工的部门名称,要求显示员工名和部门名
select ename,dname from emp,dept
以上结果输出为56条记录,为两张表记录之积,这种未指定连接条件出现的现象称为笛卡尔积现象。
当指定条件后:
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;
别名的优点:
- 执行效率高
- 可读性高
内连接
等值连接
特点:等量关系,只查询连接相等的数据。
以上例子的sql语句属于SQL92,现已不常用。
常用SQL99:
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
语法为:
...
A
(inner)join
B
on
连接条件
where
...
表连接条件和连接之后的where消息过滤分离。
非等值连接
特点:连接条件中的关系为非等量关系。
例:找出每个员工的工资等级,要求显示员工名、工资、工资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
自连接
特点:一张表看作两张表,只用一张表连接
例:找出每个员工的上级领导,要求显示员工姓名和对应的领导名。
select e.ename,m.ename from emp e join emp m where e.mgr = m.empno;
外连接
和内连接的区别?
内连接:A和B 进行连接查询,内连接的方式下,查询两张表中能够匹配条件的记录,无主副之分
外连接:A和B 进行连接查询,AB两张表有一张是主表,主要查询主表中的数据。当副表中的数据没有和主表上的数据匹配,副表自动模拟出NULL匹配。主表的数据无条件的全部查询出来。
分类:
- 左(外)连接:左边是主表
- 右(外)连接:右边是主表
例:查询所有员工的上级领导,最高级也显示出来。
select e.ename,m.ename from emp e left join emp m on e.mgr = m. empno;
内连接和外连接的语法
- 内连接
...
A
(inner)join
B
on
连接条件
where
...
- 外连接
...
A
left/right(outer)join
B
on
连接条件
where
...
3张表以上表的连接查询
例:找出每一个员工的部门名称、工资等级、以及上级领导。
select
e.ename,d.dname,s.grade,m.ename as marname
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 m
on
e.mgr = m .empno;
select davg.deptno,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) davg join salgrade s on davg.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;
select deptno,avg(everygrade) from (select e.ename,s.grade as everygrade from emp e join salgrade s on e.sal between s.losal and s.hisal) group by deptno;
子查询
where后嵌套子查询
例:找出高于平均薪资的员工信息
1.找出平均薪资 2.找出高于该值的员工信息
select * from emp where sal > (select avg(sal) from emp);
from后嵌套子查询
例:找出每个部门平均薪水的等级。
第一步:找出每个部门的平均薪水
select avg(sal) as avgsal from emp group by deptno;
第二步:将以上的查询结果当做临时表t,连接等级表查询平均薪水是何等级
select t.*,s.grade
from salgrade s
join (select avg(sal) as avgsal from emp group by deptno) t
on t.avgsal between s.losal and s.hisal;
select后嵌套子查询
例:找出每个员工所在的部门名称,要求显示员工名和部门
select e.ename,(select d.dname from dept d where e.deptno = d.deptno)
from emp e;
union
合并查询结果集
如何将两张不相干的表中的数据拼在一起显示?–union
select ename from emp
union
select grade from salgrade;
合并结果集的时候, 需要查询字段对应个数相同。 Oracle 中更严格, 不但要求个数相同,而且还要求类型对应相同
limit和分页
limit:取结果集中的部分数据,limit是MySQL中特有的。Oracle中相同的机制是rownum。
select * from table limit m,n
其中 m 是指记录开始的 index,从 0 开始,表示第一条记录,n 是指从第 m+1 条开始, 取 n 条。
例:取出工资前5名的员工
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;
limit语句是sql语句执行的最后环节。完整的sql语句执行过程:
select ... 5
from ... 1
where... 2
group by ... 3
having ... 4
order by... 6
limit ...7
标准分页sql:
limit (pageNo-1)*pageSize, pageSize;
表的操作
创建表create
建表语句的语法格式
create table 表名(
字段名1 数据类型 (约束)
字段名2 数据类型 (约束)
字段名3 数据类型 (约束)
...
);
MySQL当中字段的数据类型?
- int 整数型(java中的int)
- bigint 长整型(java中的long)
- float 浮点型(java中的float double)
- char 定长字符串(String)
- varchar 可变长字符串(StringBuffer/StringBuilder)
- date 日期类型 (对应Java中的java.sql.Date类型)
- BLOB 二进制大对象(存储图片、视频等流媒体信息) Binary Large OBject (对应java中的Object)
- CLOB 字符大对象(存储较大文本,比如,可以存储4G的字符串。) Character Large OBject(对应java中的Object)
char和varchar怎么选择?
- 在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。
- 当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar。
表名在数据库当中一般建议以:t_或者tb_开始。如 t_user 或者tb_user
表的复制:
create table 表名 as select语句
插入数据insert
语法格式:
insert into 表名
(字段名1,字段名2,字段名3...)
values
(值1,值2,值3,...),(值1,值2,值3,...),...;
字段的数量和值的数量相同,并且数据类型要对应相同。
注意:当一条insert语句执行成功之后,表格当中必然会多一行记录,即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行insert语句插入数据了,只能使用update进行更新。
修改数据update
语法格式
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
注意:没有条件整张表数据全部更新
删除数据delete
语法格式:
delete from 表名 where 条件;
怎么删除大表中的数据?(重点)
truncate table 表名; // 表被截断,不可回滚。永久丢失。
怎么删除表?
drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法。
增删改表的结构
采用 alter table 来增加/删除/修改表结构, 不影响表中的数据 。以下以例子来说明,请注意在实际的开发中对表结构的修改一般很少,确实需要修改的也可以使用工具来完成。
增加字段:需要向 t_student 中加入联系电话字段, 字段名称为: contatct_tel 类型为 varchar(40)
alter table t_student add contact_tel varchar(40);
修改字段:student_name 无法满足需求, 长度需要更改为 100
alter table t_student modify student_name varchar(100);
删除字段:删除联系电话字段
alter table t_student drop contact_tel;
增删改查有一个术语:CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)
约束
什么是约束?
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。常见的约束有:
- 非空约束(not null):约束的字段不能为NULL,not null约束只有列级约束。没有表级约束。
- 唯一约束(unique):约束的字段不能重复,但可以为NULL
- 主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)
- 外键约束(foreign key):外键主要是维护表之间的关系的, 主要是为了保证参照完整性, 如果表中的某个字段为外键字段, 那么该字段的值必须来源于参照的表的主键(简称FK)
- 检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。
唯一性约束(unique)
- 例:给某一列添加unique
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique // 列级约束
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
- 例:给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
insert into t_user values(4,'111','zs');
报错:ERROR 1062 (23000): Duplicate entry ‘111-zs’ for key ‘usercode’
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255) unique,
username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
报错:ERROR 1062 (23000): Duplicate entry ‘111’ for key ‘usercode’
主键约束(primary key)
相关术语:
- 主键字段 : id字段添加primary key之后,id叫做主键字段
- 主键值 : id字段中的每一个值都是主键值。
主键值是这行记录在这张表当中的唯一标识。
主键分类:
- 单一主键(推荐的,常用的。)
- 复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
- 自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)
- 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)
注意: 最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
一张表的主键约束只能有1个
主键值自增:
mysql中:auto_increment
oracle: 序列(sequence)对象
外键约束
相关术语
- 外键字段:添加有外键约束的字段
- 外键值:外键字段中的每一个值。
举例理解外键约束
请设计数据库表,用来维护学生和班级的信息?
- 第一种方案:一张表存储所有数据
no(pk) | name | classno | classname |
1 | aaa | 101 | 巨野一中高一1班 |
2 | bbb | 101 | 巨野一中高一1班 |
3 | ccc | 201 | 巨野一中高二1班 |
缺点:冗余。【不推荐】
- 第二种方案:建立学生表和班级表 应用外键
t_class(父表):
cno(pk) | classname |
101 | 巨野一中高一1班 |
201 | 巨野一中高二1班 |
t_student(子表):
sno(pk) | sname | classno(添加外键约束fk) |
1 | aaa | 101 |
2 | bbb | 101 |
3 | ccc | 201 |
该例的创表语句:
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
顺序要求:
删除数据的时候,先删除子表,再删除父表。
添加数据的时候,先添加父表,在添加子表。
创建表的时候,先创建父表,再创建子表。
删除表的时候,先删除子表,在删除父表。
注意点
外键可以为NULL。
外键字段引用其他表的某个字段的时候,被引用的字段不一定是主键,但至少具有unique约束。
事务
什么是事务?
一个事务是一个完整的业务逻辑单元,不可再分。事务可以保证多个操作原子性, 要么全成功, 要么全失败。 对于数据库来说事务保证批量的DML 要么全成功, 要么全失败。
和事务相关的语句只有:DML语句。(insert delete update)事务的存在是为了保证数据的完整性,安全性。
事务的四个特征CAID
- 原子性(Atomicity) :整个事务中的所有操作, 必须作为一个单元全部完成(或全部取消)
- 一致性(Consistency) :事务必须保证多条DML语句同时成功或者同时失败。
- 隔离性(Isolation) :一个事务不会影响其他事务的运行
- 持久性(Durability) :在事务完成以后, 该事务对数据库所作的更改将持久地保存在数据库之中, 并
不会被回滚。
事务之间的隔离性
事务隔离性存在隔离级别,理论上隔离级别包括4个:
- 第一级别:读未提交(read uncommitted):对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。 - 第二级别:读已提交(read committed):对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是:不可重复读。 - 第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。 - 第四级别:序列化读/串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。
MySQL中的事务
MySQL事务默认情况下是自动提交的,只要执行任意一条DML语句则提交一次。
关闭自动提交机制:start transaction
提交事务:commit
回滚事务:rollback
读未提交:
set global transaction isolation level read uncommitted;
读已提交:
set global transaction isolation level read committed;
可重复读:
set global transaction isolation level repeatable read;
隔离级别与一致性问题的关系
索引
什么是索引?
索引被用来快速找出在一个列上用一特定值的行。(主键, unique 都会默认的添加索引 )
作用:提高检索效率
什么时候需要给字段添加索引:
- 表中该字段中的数据量庞大
- 经常被检索, 经常出现在 where 子句中的字段
- 经常被 DML 操作的字段不建议添加索引
根据主键查询效率较高。尽量根据主键检索。
添加/删除索引
创建索引对象:
create index 索引名称 on 表名(字段名);
删除索引对象:
drop index 索引名称 on 表名;
查看索引:
show index from emp;
查看sql的执行计划:
explain sql语句
索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址 = 0x3;
索引的分类
- 单一索引:给单个字段添加索引
- 复合索引: 给多个字段联合起来添加1个索引
- 主键索引:主键上会自动添加索引
- 唯一索引:有unique约束的字段上会自动添加索引
索引什么时候失效?
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
select ename from emp where ename like '%A%';
视图
什么是视图
视图是一种根据查询(也就是 SELECT 表达式) 定义的数据库对象, 用于获取想要看到和使用的局部数据
视图有时也被成为“虚拟表”
视图可以被用来从常规表(称为“基表”) 或其他视图中查询数据。
相对于从基表中直接获取数据, 视图有以下好处 :
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。
创建/删除/修改视图
create view myview as select empno,ename from emp;
drop view myview;
alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = 20;
注意:只有DQL语句才能以视图对象的方式创建出来。
DBA
将数据库当中的数据导出
在windows的dos命令窗口中执行:(导出整个库)
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333
在windows的dos命令窗口中执行:(导出指定数据库当中的指定表)
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123
导入数据
登录 MYSQL 数据库管理系统之后执行 :
source D:\bjpowernode.sql
数据库设计三范式
什么是设计范式
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
第一范式
数据库表中必须有主键, 每个字段是原子性的不能再分
第二范式
第二范式是建立在第一范式基础上的, 另外要求所有非主键字段完全依赖主键, 不能产生部分依赖
第三范式
建立在第二范式基础上的, 非主键字段不能传递依赖于主键字段。(不要产生传递依赖)
表的设计
一对一
- 主键共享
- 外键唯一
一对多
一存储在一张表中, 多存储在一张表中, 在多的那张表中添加外键指向一的一方的主键
(两张表,多的表加外键)
多对多
三张表,关系表中加外键。