MySQL知识点总结

MySQL

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

名称	作用
count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

分组函数自动忽略空值，不需要手动的加where条件排除空值。

distinct：去除重复的

select count(distinct job) from emp;

count(字段名称)，不会取得为null的记录,表示统计该字段下所有不为NULL的元素的总数。

取得薪水的合计(sal+comm)

select sum(sal+comm) from emp;

上面这种写法是错误的，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

select sum(sal+IFNULL(comm,0)) from emp;

---

取得最晚入职的员工

select max(str_to_date (hiredate,'%Y-%m-%d')) from emp;

分组查询

group by和having

如果使用了order by，order by 必须放到group by 后面

一个完整的select语句格式如下：

select 字段名
from 表名
where 
group by
having  (就是为了过滤分组后的数据而存在的，不可以单独的出现)
order by 

使用having可以对分完组之后的数据进一步过滤。having不能单独使用，having不能代替where，having必须和group by联合使用

优化策略：where和having，优先选择where，where实在完成不了的再选用having

数据处理函数/单行处理函数

名称	作用
lower	转换小写
upper	转换大写
substr	取子串（substr（被截取的字符串，起始下标，截取的长度）
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体值
concat	进行字符串的拼接

select lower(ename) from emp;

substr

select substr(ename,1,1) as ename from emp;

注意：起始下标从1开始，没有0

找出员工名字第一个字母是A的员工信息？

​ 第一种方式：模糊查询

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

​ 第二种方式：substr函数

select ename from emp where sbustr(ename,1,1) = 'A';

concat

select concat(empno,ename) from emp;

round

select round(1236.567,0) as result from emp;

0表示保留0位小数

1 保留一位小数

-1 保留到十位

rand()

select round(rand()*100,0) from emp;//100以内的随机数

format

格式化数字：format(数字，‘格式’)

select ename,format(sal,'$999,999') as sal from emp;

str_to_date

将字符串varchar类型转换成date类型

语法格式：

​ str_to_date(‘字符串日期’,’日期格式‘);

mysql的日期格式：

字符表示	含义
%Y	年
%m	月
%d	日
%h	时
%i	分
%s	秒

str_to_date函数通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。

如果要是%Y-%m-%d就不用作类型转换

date_format

将date类型转换成具有一定格式的varchar字符串类型

date_format(日期类型数据，‘日期格式’);

这个函数通常使用在查询日期方面，设置展示的日期格式。

ifnull

ifnull是空处理函数，专门处理空的。

在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL

ifnull(数据，被当作哪个值)

case…when…then…when…then…else…end

当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESAMAN的时候，工资上调50%，其他正常。（注意：不修改数据库，只是将查询结果显示为工资上调）

select
	ename,
	job,
	sal as oldsal,
	(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
from
	emp;

琐碎知识点

假设起别名的时候有空格怎么办？

加单引号或者双引号都可以

注意：在所有的数据库当中，字符串统一使用单引号括起来，单引号是标准，双引号在Oracle数据库中用不了。但是在MySQL数据库中可以使用。

null

注意：在数据库当中null不能使用等号进行衡量。需要使用is null，因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号衡量。

in

注意：in不是一个区间，in后面跟的是具体的值。

not in

order by总是写在最后

命名规范

数据库中的有一条命名规范：所有标识符都是全部小写，单词和单词之间使用下划线进行衔接。

date和datetime两个类型的区别

date是短日期：只包括年月日信息。

datetime是长日期：包括年月日时分秒信息。

MySQL短日期默认格式：%Y-%m-%d

MySQL长日期默认格式：%Y-%m-%d %h:%i:%s

在MySQL当中，**now()**函数，获取当前时间，带有时分秒信息，是datetime类型的。

union

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。

但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

a连接b连接c

a 10条记录

b 10条记录

c 10条记录

匹配次数是：1000

a连接b一个结果：10*10->100次

a连接c一个结果：10*10->100次

使用union的话是：100次+100次=200次。（union把乘法变成了加法运算）

注意事项：union在进行结果集合并的时候，要求两个结果集的列数相同。

limit

完整用法： limit startIndex,length

​ startIndex是起始下标，length是长度

​ 起始下标从0开始

缺省用法：limit 5；取前5。

数据类型

名称	描述
varchar	可变长度的字符串；比较智能，节省空间；会根据实际的数据长度动态分配空间。优点：节省空间；缺点：需要动态分配空间，速度慢。
char	定长字符串；不管实际的数据长度是多少，分配固定长度的空间去存储数据；使用不恰当的时候，可能会导致空间的浪费。优点：不需要动态分配空间，速度快；缺点：使用不恰当可能会导致空间的浪费。
int	数字中的整数型。等同于Java中的int
bigint	数字中的长整型。等同于Java中的long
float	单精度浮点型数据
double	双精度浮点型数据
date	短日期类型
datetime	长日期类型
clob	字符大对象；最多可以存储4G的字符串；比如：存储一篇文章，存储一个说明；超过255个字符的都要采用CLOB字符大对象来存储；Character Large OBject:CLOB​
blob	二进制大对象；Binary Large OBject;专门用来存储图片、声音、视频等流媒体数据；往BLOB类型的字段上插入数据的时候，需要使用IO流才行。

insert

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

注意：字段名和值要一一对应

字段名可以省略，字段名省略的话就相当于都写上了。

insert into 表名 values(值1，值2，值3,…);

插入多条语句：inset into 表名 values (值1，值2，值3,…),(值1，值2，值3,…)…;

update

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

注意：没有条件限制会导致所有数据全部更新。

delete

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除。

快速创建表

create table emp2 as select * from emp;

原理：将一个查询结果当做一张表新建，这个可以完成表的快速复制。表创建出来，同时表中的数据也存在了。

将查询结果插入一个表中：insert into dept_bak select * from dept;

快速删除表中的数据

delete

delete from dept_bak;//这种删除数据的方式比较慢。

delete语句删除数据的原理？

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。

这种删除的缺点是：删除效率比较低。

这种删除的优点是：支持回滚，后悔了可以再恢复数据。

truncate

truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。

这种删除的缺点是：不支持回滚。

这种删除的优点是：快速。

truncate table  dept_bak;

约束

约束包括哪些

非空约束	not null
唯一性约束	unique
主键约束	primary key(简称PK)
外键约束	foreign key(简称FK)
检查约束	check(mysql不支持，oracle支持)

如何让两个字段联合起来具有唯一性？

在建表语句下方加入，unique (name,email);

在MySQL中，如果一个字段同时被not null和unique 约束的话，该字段自动变成主键字段。(注意：Oracle中不一样！)

主键(primary key)的特征：not null + unique(主键值不能是NULL，同时也不能重复！)

一张表，主键约束只能添加一个。

主键值建议使用：int ,bigint,char等类型，不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的。

auto_increment表示自增，从1开始，以1递增。

外键约束：

foreign key (cno) references t_class(classno);

表级约束和列级约束

表级约束主要是给多个字段联合起来添加约束。

事务

读未提交	read uncommitted(最低的隔离级别)
读已提交 oracle默认	read committed
可重复读 mysql默认	repeatable read
序列化/串行化	serializable(最高的隔离级别)

查看隔离级别：

select @@tx_isolation;

设置事务隔离级别：

set global transaction isolation level read uncommitted;

索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

创建索引

create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，起名：emp_ename_index

删除索引

drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引对象删除

索引失效

一

select * from emp where ename like '%T';

enam上即使添加了索引，也不会走索引，为什么？

​ 原因是因为模糊匹配当中以%开头了，应该尽量避免模糊查询的时候以%开始，这是一种优化的手段。

二

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。

三

使用复合索引的时候，没有使用左侧的列查找，索引失效

复合索引：两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

create index emp_job_sal_index on emp(job,sal);

四

在where当中索引列参加了运算，索引失效。

五

在where当中索引列使用了函数。

视图

创建和删除视图对象

create view dept_view as select * from dept;

drop view dept_view;

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。（视图的特点：通过对视图的操作，会影响到原表的数据）

视图在开发中的作用

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个SQL语句的时候都需要重新编写，很长很麻烦，怎么办？

可以把这条负责的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且有利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

DBA命令

create user zgy identified by '123456';

数据导出

mysqldump power>D:power.sql -uroot -p123456

mysqldump 数据库名>D:文件名 -用户名 -密码

mysqldump 数据库名 表名>D:文件名 -用户名 -密码

数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖逐渐，不要产生部份依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生依赖传递。

一对多：一对多，两张表，多的表加外键

多对多：多对多，三张表，关系表两个外键

一对一：一对一，外键唯一（庞大的表可以拆分）