20201015 王维

学习总结

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的高级查询,完成了基础阶段了学习,单个查询感觉还比较简单,但是当条件复杂起来后,各种查询的组合可能就需要好好练习了。

标签

评论

this is is footer