MySQL学习小记_mysql小记

MySQL

分组函数

• count 取得记录数
• sum 求和
• avg 取平均值
• max 取最大值
• min 取最小值

注意：分组函数将自动忽略null值，因此在where中不需要手动添加条件排除空值

分组函数不能用于where后！！！（因为分组在group by执行后执行）

分组函数可以一起使用，例：

select count(name),avg(age),max(grade) from student;
1

分组查询

• group by 按照某个（些）字段进行分组
• having 对分组之后的数据进行再次过滤

group by

取得每个工作岗位的工资合计， 要求显示岗位名称和工资合计

select job, sum(sal) from emp group by job;
1

若使用了order by，排序一定在分组之后

在 SQL 语句中若有 group by 语句， 那么在 select 语句后面只能跟分组函数+参与分组的字段。

例：

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

该语句在Oracle数据库下会报语法错误，在MySQL下不会，但是会随机选取几个ename值，和查询结果无法对应。

多个字段联合分组，例 ：查询每个部分不同工作岗位的最高薪资？

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

having

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

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

上面的语句执行效率低，建议使用where

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

去除重复记录

distinct

select distinct job from emp;
1

distinct 只能出现在字段最前，后面的字段联合起来去重。

select总结

一个完整的select语句如下：

select 字段
from 表名
where ...
group by ...
having ...(不可单独出现，和分组共同使用)
order by ...
1
2
3
4
5
6

以上语句的执行顺序

1. 首先执行 where 语句过滤原始数据
2. 执行 group by 进行分组
3. 执行 having 对分组数据进行操作
4. 执行 select 选出数据
5. 执行 order by 排序

**原则：**能在 where 中过滤的数据， 尽量在 where 中过滤， 效率较高。

连接查询

也可叫做跨表查询，表示从多表联合中进行查询，取出最终结果。

连接查询的分类

根据语法出现的年代来划分，包括：

• SQL92
• SQL99

根据表的连接方式来划分，包括：

内连接：

• 等值连接
• 非等值连接
• 自连接

外连接：

• 左(外)连接
• 右(外)连接

全连接（很少用）

连接查询原理及笛卡尔积现象

例：找出每一个员工的部门名称，要求显示员工名和部门名

select ename,dname from emp,dept
1

以上结果输出为56条记录，为两张表记录之积，这种未指定连接条件出现的现象称为笛卡尔积现象。

当指定条件后：

select ename,dname from emp,dept where emp.deptno = dept.deptno;
1

表的别名：

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

别名的优点：

• 执行效率高
• 可读性高

内连接

等值连接

特点：等量关系，只查询连接相等的数据。

以上例子的sql语句属于SQL92,现已不常用。

常用SQL99：

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

语法为：

...
	A
（inner）join
	B
on
	连接条件
where
...
1
2
3
4
5
6
7
8

表连接条件和连接之后的where消息过滤分离。

非等值连接

特点：连接条件中的关系为非等量关系。

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

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

自连接

特点：一张表看作两张表，只用一张表连接

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

select e.ename,m.ename from emp e join emp m where e.mgr = m.empno;
1

外连接

和内连接的区别？

内连接：A和B 进行连接查询，内连接的方式下，查询两张表中能够匹配条件的记录，无主副之分

外连接：A和B 进行连接查询，AB两张表有一张是主表，主要查询主表中的数据。当副表中的数据没有和主表上的数据匹配，副表自动模拟出NULL匹配。主表的数据无条件的全部查询出来。

分类：

• 左（外）连接：左边是主表
• 右（外）连接：右边是主表

例：查询所有员工的上级领导，最高级也显示出来。

 select e.ename,m.ename from emp e left join emp m on e.mgr = m. empno;
1

内连接和外连接的语法

• 内连接

...
	A
（inner）join
	B
on
	连接条件
where
...
1
2
3
4
5
6
7
8

• 外连接

...
	A
left/right（outer）join
	B
on
	连接条件
where
...
1
2
3
4
5
6
7
8

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
1
2

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;
1
2

子查询

where后嵌套子查询

例：找出高于平均薪资的员工信息

1.找出平均薪资 2.找出高于该值的员工信息

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

from后嵌套子查询

例：找出每个部门平均薪水的等级。

第一步：找出每个部门的平均薪水

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

第二步：将以上的查询结果当做临时表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;
1
2
3
4

select后嵌套子查询

例：找出每个员工所在的部门名称，要求显示员工名和部门

select e.ename,(select d.dname from dept d where e.deptno = d.deptno)
from emp e;
1
2

union

合并查询结果集

如何将两张不相干的表中的数据拼在一起显示？–union

select ename from emp
union
select grade from salgrade;
1
2
3

合并结果集的时候， 需要查询字段对应个数相同。 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;
1
2

limit语句是sql语句执行的最后环节。完整的sql语句执行过程：

select ... 5
from ... 1
where... 2
group by ... 3
having ... 4
order by... 6
limit ...7
1
2
3
4
5
6
7

标准分页sql：

limit (pageNo-1)*pageSize, pageSize;
1

表的操作

创建表create

建表语句的语法格式

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

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语句
1

插入数据insert

语法格式：

insert into 表名
	(字段名1，字段名2，字段名3...)
values
	(值1，值2，值3，...),(值1，值2，值3，...),...;
1
2
3
4

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

注意：当一条insert语句执行成功之后，表格当中必然会多一行记录，即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。

修改数据update

语法格式

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

​ 注意：没有条件整张表数据全部更新

删除数据delete

语法格式：

delete from 表名 where 条件;
1

怎么删除大表中的数据？（重点）

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

怎么删除表？

		drop table 表名; // 这个通用。
		drop table if exists 表名; // oracle不支持这种写法。
1
2

增删改表的结构

采用 alter table 来增加/删除/修改表结构， 不影响表中的数据 。以下以例子来说明，请注意在实际的开发中对表结构的修改一般很少，确实需要修改的也可以使用工具来完成。

增加字段：需要向 t_student 中加入联系电话字段， 字段名称为： contatct_tel 类型为 varchar(40)

alter table t_student add contact_tel varchar(40);
1

修改字段：student_name 无法满足需求， 长度需要更改为 100

alter table t_student modify student_name varchar(100);
1

删除字段：删除联系电话字段

alter table t_student drop contact_tel;
1

增删改查有一个术语：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);	
1
2
3
4
5
6
7
8
9
10
11
12

• 例：给两个列或者多个列添加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');
1
2
3
4
5
6
7
8
9
10
11
12

报错：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');
1
2
3
4
5
6
7
8

报错：ERROR 1062 (23000): Duplicate entry ‘111’ for key ‘usercode’

主键约束（primary key）

相关术语：

• 主键字段 : id字段添加primary key之后，id叫做主键字段
• 主键值 : id字段中的每一个值都是主键值。

主键值是这行记录在这张表当中的唯一标识。

主键分类：

• 单一主键（推荐的，常用的。）

• 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

• 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
• 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

注意： 最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有1个

主键值自增：

mysql中：auto_increment

oracle: 序列（sequence）对象

外键约束

相关术语

• 外键字段：添加有外键约束的字段
• 外键值：外键字段中的每一个值。

举例理解外键约束

请设计数据库表，用来维护学生和班级的信息？

• 第一种方案：一张表存储所有数据

​ 缺点：冗余。【不推荐】

• 第二种方案：建立学生表和班级表 应用外键

t_class（父表）：

t_student(子表):

该例的创表语句：

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)
		);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。

注意点

外键可以为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;
1

读已提交：

set global transaction isolation level read committed;
1

可重复读：

set global transaction isolation level repeatable read;
1

隔离级别与一致性问题的关系

索引

什么是索引？

索引被用来快速找出在一个列上用一特定值的行。（主键， unique 都会默认的添加索引 ）

作用：提高检索效率

什么时候需要给字段添加索引：

• 表中该字段中的数据量庞大
• 经常被检索， 经常出现在 where 子句中的字段
• 经常被 DML 操作的字段不建议添加索引

根据主键查询效率较高。尽量根据主键检索。

添加/删除索引

创建索引对象：

create index 索引名称 on 表名(字段名);
1

删除索引对象：

drop index 索引名称 on 表名;
1

查看索引：

show index from emp;
1

查看sql的执行计划：

explain sql语句
1

索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

		select ename from emp where ename = 'SMITH';
		通过索引转换为：
		select ename from emp where 物理地址 = 0x3;
1
2
3

索引的分类

• 单一索引：给单个字段添加索引
• 复合索引: 给多个字段联合起来添加1个索引
• 主键索引：主键上会自动添加索引
• 唯一索引：有unique约束的字段上会自动添加索引

索引什么时候失效？

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

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

视图

什么是视图

视图是一种根据查询（也就是 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;
1
2
3

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

DBA

将数据库当中的数据导出

在windows的dos命令窗口中执行：（导出整个库）

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

在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123
1

导入数据

登录 MYSQL 数据库管理系统之后执行 :

source D:\bjpowernode.sql
1

数据库设计三范式

什么是设计范式

设计表的依据。按照这个三范式设计的表不会出现数据冗余。

第一范式

数据库表中必须有主键， 每个字段是原子性的不能再分

第二范式

第二范式是建立在第一范式基础上的， 另外要求所有非主键字段完全依赖主键， 不能产生部分依赖

第三范式

建立在第二范式基础上的， 非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

表的设计

一对一

• 主键共享
• 外键唯一

一对多

一存储在一张表中， 多存储在一张表中， 在多的那张表中添加外键指向一的一方的主键

（两张表，多的表加外键）

多对多

三张表，关系表中加外键。