学习总结

1 高级查询

1.1 多表查询

1.1.1 笛卡尔乘积现象（交叉连接）

每个表的每一行与其他表的每一行组合，假设两张表的总行数分别是x行和y行，笛卡尔乘积就会返回x*y行记录。多行表在查询是，如果定义了无效连接或者漏写了连接条件，就会产生笛卡尔乘积现象。

1.1.2 等值连接查询

通常是在存在主键和外键关系的表之间进行，并将连接条件设定为有关系的列[主键 = 外键]，使用"="连接相关的表

注意：为了避免笛卡尔乘积现象，n个表进行等值连接查询时，最少需要n-1个等值条件约束

-- 查询每个部门的所有员工
SELECT dept.dname , emp.ename FROM emp , dept WHERE emp.deptno = dept.deptno

等值连接与自然连接：

等值连接必须要有等值的条件，当条件不同时连接的结果也不相同，两个关系可以没有相同的属性列

自然连接是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中去除重复的属性列。

1.1.3 自连接查询

多表查询不仅仅在多个表之间进行，也可以在一个表之中进行多表查询

--查询当前公司员工和所属上级员工的信息
select e1.empno as 员工编号,e1.ename as 员工姓名,e2.empno as 领导编号,e2.ename as 领导
姓名 from emp as e1,emp as e2 where e1.mgr = e2.empNo;

1.1.4 内连接查询

指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

内连接查询使用 inner join关键字实现， inner 可以省略。内连接查询时，条件用 on 连接，多个条件使用() 将其括起来。

--查询每个部门的所有员工
select dept.name,emp.name from emp inner join dept on emp.deptno = dept.deptno;

等值连接与内连接：

等值连接会先进行笛卡尔积运算，生成一个新的表格，占据在电脑的内存里，当表的数据量很大时，很耗内存，效率比较低

内连接会让表格根据条件进行逐条匹配，不出现笛卡尔积现象，效率比较高。

1.1.5 外连接

外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其值 outer 可以省 略。外连接查询时，条件用 on 连接，多个条件使用 () 将其括起来。左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表数据在从表中进行匹配，如果从表的数据不满足on的条件，主表的数据也将返回到查询结果中，而从表使用null填充结果集。

--查询每个部门的所有员工
select dept.name,emp.name from emp right join dept on emp.deptno = dept.deptno;
select dept.name,emp.name from emp left join dept on emp.deptno = dept.deptno;

1.2 子查询

子查询的结果作为父查询的条件

1.2.1 单行子查询

父查询的条件是=时，子查询只允许查询一个值

单行单列

--查询软件部门下的所有员工
select * from emp e where e.deptno = (select d.deptno from dept d where d.dname =
'软件部' );

1.2.2 多行子查询

如果字查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到多行记录的操作符，如：in、all、any

多行单列

--统计所有的员工分布在那些部门的信息
select * from dept d where d.deptno in (select e.deptno from emp e);
--查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp e2);
--查询公司中比所有的助理工资高但不是助理的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.emp where
w2.joblike '%助理');

>any 表示大于子查询中的任意一个值，即大于最小值

>all 表示大于子查询中的所有值，即大于最大的值

1.2.3 多列子查询

单行多列

--查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2
where e2.ename = '张青');

1.3 分页查询limit子句

select * from emp limit 0,2;
-- 第一个参数0是表示从第几条开始查询 (这里的 0 是可以省略不写的)；
-- 第二个参数 表示查询出几条数据
-- 后面不够的，有多少写多少；

select * from emp order by empNo limit 5;
select * from emp limit 5,5;
-- 每页不同数据时可以采用下面的公式
select * from table_name limit (页码 - 1) * 每页数量, 每页数量;

1.4 distinct

在 select 语句中，可以使用 distinct 关键字对查询的结果集进行去重。去重必须结果集中每个列的 值都相同。

elect distinct 列1, ... , 列n from table_name [其他子句];

1.5 order by

order by 用于对结果进行排序显示，可以使用 ASC / DESC 两种方式进行排序，可以有多个排序条件

• ASC ：表示升序排序，如果不写即为此排序方式
• DESC ：表示降序排序

select [distinct] 列1, ... , 列n from table_name [其他子句] order by 排序列1 [DESC],
排序列2 [DESC];

1.6 聚合函数

MySql中内置了 5 种聚合函数，分别是： SUM 、 max 、 min 、 avg 、 count 。

1.7 group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。 group by 分组后的查询中， select 的列不能出现除了 group by 分组条件以及聚合函数外的其他 列。

1.8 having

having 是对 group by 分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;

1.9 综合查询

select distinct * from emp join dept on emp.deptno = dept.deptno where hridate >=
'2000-01-01' group by deptno having count(*) >= 2 order by count(*) desc limit
0,5;

SQL 语句的执行顺序 from --> on --> join --> where --> group by --> having --> select --> distinct-- > order by--> limit

2 单行函数

2.1 字符串函数

函数	示例	结果	描述
upper	select upper('sdfd');	SDFD	将字母转换为大写
lower	select lower('ABc');	abc	将字母转换为小写
concat	select concat('hello ','world');	'hello world'	字符串连接
substr /substring	select substr('hello world',2);	'ello world'	截取字符串
length	select length('hello world');	11	获取字符串长度
instr	select instr('hello world','world');	7	获取子字符串在父字符串中 的索引
trim	select trim(' hello ');	'hello'	去除两端空格
ltrim	select ltrim(' hello');	'hello'	去掉左端的空格
rtrim	select rtrim('hello ');	'hello'	去掉右端的空格
replace	select replace('hello java','java','world');	'hello world'	替换文本

注：这里的字符串是从1开始的（不是0）

2.2 数学函数

函数	示例	结果	作用
round(x, [y])	select round(5.64,1);	5.6	对指定的值进行四舍五入是可以指定数值 位数y
truncate	select truncate(5.6,0);	5	对指定的数及进行截取操作，指定保留位 数y
ceil(x)	select ceil(4.56)	5	返回不小于指定的值x得最小整数，向上 取整
floor(x)	select floor (8.5);	8	返回不大于指定的值x的最大整数，向下 取整
abs(x)	select abs(-12);	12	取绝对值

2.3 日期函数

函数	示例	结果	作用
current_timestamp()	select current_timestamp();	2019-11-07 20:53:47	获取当前时间戳
current_date()	select current_date();	2019-11-07	获取当前日期
current_time()	select current_time();	20:56:00	获取当前时间
now	select now();	2019-11-07 20:57:15	获取当前时间+日期

3 设计数据库步骤

软件开发的步骤大致上可以分为：

需求分析，概要设计，详细设计，代码编写，运行测试，部署发行

数据库是在代码编写前完成的

数据库设计可分为这几个过程：

需求分析，概念模型，物理模型，运行验证

4 创建概念模型

5 数据库设计范式

数据库的设计有五大设计范式。常用的有三大设计范式，称之为第一范式( 1NF )，第二范式( 2NF )，第 三范式( 3NF )，他们是逐步为严格的，满足第二范式，就必须满足先满足第一范式。满足第三范式时就 必须首先满足第二范式

5.1 第一范式

第一范式要求单个表中每个列必须是原子列（即每一个列都是不可再分的最小数据单元），列不存在重 复属性，每个实体的属性也不存在多个数据项。

1.原子列 2.不出现重复属性 3.不允许出现多个数据项

5.2 第二范式

第二范式是在满足第一范式的基础之上，要求数据表里的说有数据都要和改数据表的主键有完全依赖关 系。

5.3 第三范式

第三范式是在满足第二范式的基础之上，每一个非主键列都直接依赖主键列，不依赖其他非主键列，即 数据库中不能存在传递函数的依赖关系。

5.4 范式的优缺点

优点：

• 范式化的数据库更新起来更加的快
• 范式化之后只有很少的重复数据，只需要修改更少的数据；
• 范式化的表更小，可以在内存中直接执行；
• 很少的冗余数据，在查询时候需要更少的distinct后者group by语句。

缺点：

范式化的设计会产生更所的表；

在查询的时候经常需要很多的表连接查询，导致查询性能降低；

心得体会

今天学习了mysql的高级查询，完成了基础阶段了学习，单个查询感觉还比较简单，但是当条件复杂起来后，各种查询的组合可能就需要好好练习了。