MySQL数据库

第一章 数据库理论

1.1 数据库基本概念

数据库（Database DB）
数据库系统（DBS）
数据库管理系统（DBMS）
数据库管理员（DBA）

1.2 数据库分类

两大类：

1.关系型数据库（RDBMS 二维表形式存储）

1. mysql（开源的小巧灵活的数据库，数据移植性强，跨平台，适合于中小型企业开发，数据量不会更大，数据量小，安全性弱，并发性较弱）
2. oracle（非开源数据库，跨平台大型网络数据库，数据存储量大，并发强，安全性高， DBA操作繁琐 ）
3. sqlserver（微软的数据库，网络型数据库，适合微软技术开发（c# ）受限于操作系统，不能跨平台）
4. DB2（ IBM大型数据库 ，适合大型数据，功能强大，细节不足）

2.非关系型数据库（键值对）

1. redis
2. mongo

1.3 SQL语言

SQL (Structured Query Language) 结构化的查询语言，适合于关系型数据库的数据操作。

1.3.1 SQL的分类：

DQL 数据查询语言

（查询数据 SELECT ）

DML 数据操作语言（增删改数据 ）

（INSERT增加 DELETE删除 UPDATE修改 COMMIT提交 ROLLBACK 回滚）

DDL 数据定义语言（增删改表）

（创建 CREATE 删除 DROP 添加 ADD 修改 ALTER）

DCL 数据控制语言

（授权 GRANT 撤回 REVOKE）

1.4 数据建模（设计表）

将现实事物，需求转化为计算机当中数据表，这个过程是建模。

美团平台系统：商品 商家 会员 骑手

1.4.1 企业开发流程：

项目调研：了解行业相关信息，以业务为导向
    参与人员可以是产品经理、非常熟悉业务人、项目经理
需求分析：做什么？做成什么样？不涉及具体技术
    用户提出来的：显式需求，由客户提出，这种需求很明确，比较好沟通
    系统主动包含：隐式需求，系统自带，不是由客户提出，这个比较麻烦，需要更多的经验
    参与人员：产品、项目经理，开发（架构师）
方案设计：
    概设:需要哪些功能，具有哪些模块
    详设:需求详设，以及技术详设（接口、表、类型）等等，但是几乎所有的国内公司的技术详细设计是后期补充的，。
    系统设计：如可不可以报警、是否能定制化、是否能HA、是否能迁移。 这些并属于属于某个功能模块的设计。
    参与人员：产品、项目经理，开发（架构师）、普通开发
功能开发 : 
    开发：将详细设计的内容代码化，故普通的程序员真的只是一个码农，仅仅只做填代码的工作
    测试：自己进行单元测试
    参与人员：开发（架构师）、普通开发
测试:功能、联调、性能、稳定性、用户试用
    参与人员：开发人员、测试人员
部署上线:
    试运行  DIFF  稳定性
    正式上线      灰度
后期:2、3、4 版本，运维保障、功能开发、bug修复

1.现实世界 分析 > 概念世界（ 实体关系模型图 E-R 图 ）

实体关系模型图 E-R 图

E : 实体 矩形

R: 关系 菱形

属性：椭圆

关系类型 : 1对1 1对多 多对多

之间使用实线连接，标注关系类型（1:1 1:n m:n）

2.概念世界 转化 > 机器世界

将E-R转化为数据库中表。

1.实体作为表，实体的属性表的列（学生，课程）

2.关系也是表，列需要分析（选课表）

3.主属性会作为表的主键（不能空，不能重复）

1.4.2 要求（满足数据库设计的三大范式）：

1NF ： 数据表中列都是原子级别，不可分割 （联系方式 手机/QQ）

2NF : 基于第一范式，所有非主属性依赖于主属性

3NF : 基于第二范式， 表中列之间不能存在传递依赖

1.5 数据库安装

mysql 安装包

navicat 开发图形工具

1.6 数据库创建

右键 > 新建数据库

建表：

表 ： 关系

列 ： 字段 ，属性 ，分量

行 ： 记录 ，元组 ，数据

主键： 主属性 （每个表必须要有一个主键）

域 ： 列的值取值范围（age > 1 gender ）

表名，列名 ： 字母，数字 _ $ #不能数字开头，建议字母开头，小写 ，1-30位

1.7 列的数据类型：

基本类型：

1. 数值型 int double
2. 字符型 varchar （可变长） char（定长）
   varchar(20)最大长度20
3. 日期型 date datetime

sql的导入导出：

数据库 右键

第二章 数据库数据查询

mysql 注释：

ctrl + / ctrl+shift + /

单行 –

多行 /* */

2.1 简单查询

语法规则：

SELECT  * 或  列名1,列名2...  或  表达式  或   DISTINCT 列  
FROM   表名 ;

注意：

 sql语句不区分大小写， 建议 关键字大写，表列名小写：
 
 (SELECT查询) (FROM从哪张表) (DISTINCT去重)

 sql 使用空格隔开，每条语句使用;结束 ，每个子句建议一行
 
 * 表示查询所有列

-- 查询员工所有列
-- select * 
-- from emp;

-- 查询员工编号 姓名 职位
-- select empno,ename,job
-- from emp;

-- 查询员工年薪sal 
-- select empno,ename,sal,(sal+comm)*12 年薪
-- from emp;

-- 员工表有几种职位 (DISTINCT  去重)
select distinct job
from emp

特殊情况：

1.包含空值的运算，都会变成空值，空值是需要处理的

-- ifnull(列，替换值);
select (sal+ifnull(comm,0))*12 年薪
from emp

-- oracle: nvl()  
 
-- 1   
-- dual是虚表的意思
select ifnull(1,2)
from dual;  

-- 2
select ifnull(null,2)
from dual;  

2.mysql中字符串常量（concat 合并的意思）

'abc'
concat('','','',''....) 字符串使用单引号连接 

3.给表定义别名

列  别名
列  AS 别名

-- info 是别名
select  concat(ename,'赚',sal,'元，在 ' ,deptno,'部门') as info
from emp

2.2 条件查询（数据排序）

带有筛选条件的查询

语法规则：

SELECT  * 或  列名1,列名2...  或  表达式  或   DISTINCT 列  
FROM   表名 
WHERE  列 比较 值  (AND / OR) 条件2 ...

注意：

1，where是横向查询，记录中筛选

2，列和值比较：

等值比较 = （一个 = 就是比较是否相等的意思，不是赋值），数值直接比较，字符串和日期比较使用引号‘ ’ 括起来

-- 查询工资5000元的员工姓名，薪资，职位
select ename, sal,job
from emp
where sal = 5000;

-- 查询名字是nao的员工信息
select *
from emp
where ename = 'nao';

-- 查询入职时间是2022-8-31 入职员工的信息
select *
from emp
where hiredate = '2022-8-31';

不等值比较

运算符	含义
>	使用在数值和日期型
>=	使用在数值和日期型
<	使用在数值和日期型
<=	使用在数值和日期型
!= 或 <>	不等于，数值，日期，字符串
=	等于，数值，日期，字符串

特殊的比较运算符

运算符	含义
BETWEEN 值1 AND 值2	处于值1和值2之间，包含边界，数值和日期
LIKE ’ ’	模糊查询，(像…) ; 通配符 _ 占位1个 % 占位0或n ，字符串或日期当中用的比较多
IN(值1，值2…)	满足等于IN中的任意一个值（不是范围）
IS NULL	某个列是否是空的
NOT BETWEEN … AND	不处于值1值2之间
NOT LIKE …	不是 / 像 …样
NOT IN()	不在…集合
IS NOT NULL	非空的

​ 逻辑运算符：

运算符	含义
NOT	非
AND	与，做条件连接，同时满足
OR	或，做条件连接，满足一个即可

NOT>AND>OR

2.2.1 between … and 用法：

-- 查询薪资在3000-5000的员工信息
select *
from emp
where sal between 3000 and 5000;

-- 查询2022-8月份入职员工
select *
from emp
where hiredate between '2022-8-2' and '2022-8-31';

2.2.2 like 用法：

-- 查询 姓沈的员工信息
select *
from emp
where ename like '沈%';

-- 查询名字中包含师傅的员工信息
select *
from emp
where ename like '%师傅%';

-- 查询名字第二个字是 小 字
select *
from emp
where ename like '_小%';

2.2.3 in 用法：

-- 查询 10 或 30 部门的员工信息
select *
from emp
where deptno in (10,30);

-- 拆解
select *
from emp
where deptno = 10 or deptno = 30;

2.2.4 is null 用法：

-- 查询没有奖金的员工信息 comm
select *
from emp
where comm is null;

总结：

数据类型：

数据类型
数值	int,double,…
日期	date
字符串	varchar，char
…

运算符：

数据类型
算术运算符	+ - * / (%) ()
比较运算符	> >= < <= != =
特殊比较	(between…and) (like) (in) (is null)
逻辑运算符	not > and > or

-- 10或30部门，7月份侯入职
-- 必须使用()
select *
from emp
where hiredate >= '2022-7-1' and (deptno = 10 or deptno = 30);-- 拆解 or

select *
from emp
where hiredate >= '2022-7-1' and deptno in(10,30);-- in

2.2.5 数据排序语法：

SELECT  * 或  列名1,列名2...  或  表达式  或   DISTINCT 列  
FROM   表名 
WHERE  条件1  AND/OR  条件2 ...
ORDER  BY  列1 [ASC/DESC], 列2 [ASC/DESC]

注意：

1.排序子句永远是整个查询的最后一步
2.在排序时，可以使用多个列排序，当列1相同情况下，按照列2排序
3.ASC 是升序，DESC 是降序，默认是升序
4.ORDER BY 可以使用别名来排序

-- 查询10或40部门有奖金的员工信息，按照薪资降序排序
select *
from emp
where deptno in(10,40) and comm is not null
order by sal desc;

-- 查询10或40部门有奖金的员工信息，按照薪资降序排列,薪资相同，按照名字升序
select *
from emp
where deptno in(10,40) and comm is not null
order by sal desc,ename;

-- 查询8月份入职的员工姓名和年薪，按照年薪降序排列
select ename,(sal+ifnull(comm,0)) * 12 ySal
from emp
where hiredate between '2022-8-1' and '2022-8-31'
order by ySal desc;

2.3 MYSQL函数

2.3.1 函数的意义

mysql中函数可以帮助实现某些功能，有助于进一步查询。

-- 函数：多个输入值，最后获得一个输出结果
y = f(x);

-- mysql中的函数
函数名(参数1，参数2...参数n)

-- 嵌套的函数
函数名(函数名(),参数2...参数n)

2.3.2 函数的分类

单行函数：对一行数据进行操作，返回一个结果

数值函数
字符串函数
日期函数
格式转换函数
通用函数

多行函数：对多行数据进行操作，返回一个结果

求和函数
最大值函数
最小值函数
累计个数函数
平均值函数
…

2.3.3 数值函数

-- 圆周率（3.141593）
pi()

-- 开平方（5）
sqrt(25)

-- 向上 向下 取整
ceil(3.1546)  -- 向上
floor(-3.1546)  -- 向下


-- 四舍五入(和java不同)
round(-2.5); -- 结果是-3，保留到整数位
round(数值，指定位); -- 将数值保留到指定位，0,1，2，-1，-2...
select round(63.67567,-2)   
from dual;-- (结果是100)

-- mod(a,b) 求余数
select mod(10,3)
from dual;-- (结果是1)

-- TRUNCATE(x,y)截断，不考虑四舍五入
select truncate(46.326234,2)
from dual;-- (结果是46.32)


-- 随机数  rand() 一般用于数据表中随机抽取n条记录(0-1)随机小数
select rand()
from dual;

-- 绝对值 abs()

2.3.4 字符串函数

-- 长度问题  有两种
1. char_length(字符串) -- 字符长度
2. length(字符串) -- 字节长度，一个汉字是3字节

-- 查询员工表中名字两个字的员工信息
select *
from emp
where char_length(ename) = 2;

-- 字符串拼接 concat(字符串1，字符串2...)
select concat(eanme,'赚', sal, '元')
from emp;

-- substring(字符串，起始位置，截取个数)
-- 注意：字符串起始下标是从1开始，0相当于没有下标
select substring('welcome to study java',1,5)
from dual;

-- 查询10部门每个员工姓氏
select ename,substring(ename,1,1)
from emp
where deptno = 10;

-- instr(原串，子串) 字符串查找 原串中是否包含子串  !=0就是包含
select *
from emp
where instr(ename,'a')!= 0 ;

-- left(字符串，个数) right() 从左侧或右侧获取xxx个
-- select left(ename,2)
-- from emp;

select right(ename,2)
from emp;

-- lpad()  rpad(字符串，总长度，填充内容)
-- 查询员工姓名，名字左侧填充星星，个数名字长度
select lpad(ename,char_length(ename)*2,'*')
from emp;

-- trim('  ssd 324234 ') 去掉字符串两侧空格
select concat('aaaa',trim('  ssd 324234 '),'bbbb')
from dual;

-- trim(字符 from 字符串) 将字符串中左侧的字符去掉
select trim('ab' from 'ababaawerabszadaaaab')
from dual;

-- ltrim()  rtrim()  去掉左侧或右侧空格

2.3.5 日期函数

-- 获得系统当前时间函数 CURRENT_TIMESTAMP(),LOCALTIME(),NOW()和SYSDATE()
select now()
from dual;


-- month(日期)
-- year(日期)
-- QUARTER(date) 季度
-- 查询员工表中7月份入职的员工
select *
from emp
where month(hiredate) = 7;


-- DAYOFWEEK(d) 周几
-- 函数返回d对应的一周中的索引(位置)，1表示周日，2表示周1，... 7表示周六。


-- datediff(日期1，日期2)计算两个日期相差天数
-- 每个员工入职多少天
select ename,datediff(now(),hiredate)
from emp;

-- 可设置的时间差
SELECT TIMESTAMPDIFF(day,now(),hiredate)
from emp;

-- 获得某个日期的最后一天  last_day()
-- 查询员工表中每个月最后一天入职的员工
select *
from emp
where (hiredate) = last_day(hiredate);

2.3.6 格式转换函数

-- DATE_FORMAT(date,format)根据format 指定的格式显示date值。
select date_format(now(),'%Y年%m月%d日')
from dual;

-- STR_TO_DATE(varchar,format)根据format 指定的格式显示date值。
select STR_TO_DATE('2000-5-1','%Y-%m-%d') info
from dual;

2.3.7 通用函数

统一处理问题的函数叫通用函数

-- 处理空值
-- ifnull(参数1，参数2);如果参数1是空的，使用参数2替换

-- 表达式(类似java中switch，根据条件分情况执行)case表达式
case 列
when 值1 then 结果1
when 值2 then 结果2
when 值3 then 结果3
else 结果
end ;

-- 条件匹配
case 列
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
...
else 结果
end ;

-- 员工信息工资  2000-5000 低   5001 - 10000 中  10001-30000 高  其他
select ename,sal,case
when sal between 2000 and 5000 then '低'
when sal between 5001 and 10000 then '中'
when sal between 10001 and 30000 then '高'
else '其他' end  薪资信息
from emp;

2.4 表的连接查询

之前是查询一张表，
如果要查询的信息来源于两张表。
需求：查询员工的编号，姓名，部门编号，部门位置？
必须要从两张表当中连接到一起做联合查询。

分类：

1. 笛卡尔集
2. 等值连接
3. 不等值连接
4. 自我连接
5. 外连接

2.4.1笛卡尔集

将两张或多张表进行，没有任何连接条件，将它们完全交叉连接到一起

select *
from 表1，表2 
-- 数据总量：表1 * 表2

-- sql1999  的写法
select *
from 表1 cross 表2

2.4.2 等值连接查询

将两张或多张表，根据等值的连接条件做连接查询。

select *
from 表1 , 表2
where 表1.列 = 表2.列 and 条件+;

-- 别名的写法
select 别名 1.*, 别名.列名
from 表1 别名1, 表2 别名2
where 别名1.列 = 别名2.列 and 条件+;

-- sql1999的写法   使用共同的列来实现两个表的连接
select 别名.列名
from 表1 join 表2
using(共同的列)

示例：

-- 查询员工所有信息，包括部门
select *
from emp,dept
where emp.deptno = dept.deptno;-- （2表的连接条件）

-- 查询员工编号，姓名部门编号，部门位置
select empno,ename,emp.deptno,loc
from emp,dept
where emp.deptno = dept.deptno;

-- 为了高效查询，每个列前加表名.列（加快效率）
select emp.empno,emp.ename,emp.deptno,dept.loc
from emp,dept
where emp.deptno = dept.deptno;

-- 连接查询 是 给表定义别名（一旦加上别名，建议使用别名，不能使用原表名）别名查询
select e.empno,e.ename,e.deptno,d.loc
from emp as e,dept d
where e.deptno = d.deptno;

2.4.3 不等值连接查询

将两张表或多张表，添加不等值的连接条件，2张表至少有一个连接条件，N张表至少有N-1个连接条件。

需求：查询员工的姓名，薪资，薪资对应的等级

select 别名1.* , 别名.列名
from 表1 别名1, 表2 别名2
where 别名1.列 不等符号 列...

示例：

-- 查询每个员工姓名，薪资，等级（等级表）使用between  and 来获取薪资等级范围
select e.ename,e.sal,s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;--（2表的连接条件）

-- 名字中有a或c 姓名，等级，部门位置
select e.ename,s.grade,d.loc
from emp e,dept d,salgrade s
-- 2表的连接条件
where e.deptno = d.deptno 
-- 2表的连接条件
and e.sal between s.losal and s.hisal 
-- 条件
and (e.ename like '%a%' or e.ename like '%c%' );

注意：

第三个条件必须用括号括起来

2.4.4 自我连接

一张表有两个身份，自己和字节连接。

语法：

select 别名1.列
from 表 别名1 , 表 别名2
where 别名1.列 = 别名2.列

需求：查询员工表的编号，姓名，上司的编号，上司的姓名

-- 需求： 查询员工表编号，姓名，上司编号，上司姓名
select e.empno,e.ename, m.empno,m.ename
from emp e,emp m
-- （把自己看做两张表，进行自我连接条件）
where e.mgr = m.empno

-- 1， 查询有奖金的10或40部门每个员工姓名，上司姓名
select e.ename,m.ename
from emp e,emp m
where e.comm is not null and e.deptno in(10,40) and e.mgr = m.empno;


-- 2. 查询员工薪资等级，上司薪资等级
select e.ename,s1.grade,m.ename,s2.grade
from emp e,emp m,salgrade s1, salgrade s2
where e.mgr = m.empno 
and e.sal between s1.losal and s1.hisal 
and m.sal between s2.losal and s2.hisal;

2.4.5 外连接查询

外连接用于查询不满足连接条件的数据。

左外连接 ： 左侧表为主表，即使没有满足连接条件，左侧表数据也要完全显示。

select 表.列
from 表1 left join 表2
on(连接条件)
where 查询条件;

右外连接 ： 右侧表为主表，即使没有满足连接条件，右侧表数据也要完全显示。

select 表.列
from 表1 right join 表2
on(连接条件)
where 查询条件;

示例：

-- 需求： 查询员工表编号，姓名，上司编号，上司姓名(没有领导也要显示)
-- 
select e.empno,e.ename, m.empno,m.ename
from emp e left join emp m
on(e.mgr = m.empno);
where e.deptno=10


-- 需求： 查询每个员工的部门名称（没有员工的部门显示）
select *
from emp e right join dept d
on(e.deptno=d.deptno )

2.5 分组查询

将表中记录，分成几组查询。

需求： 统计获得全公司总支出（成本）

组函数： 对多行数据进行操作，返回一个结果（多行函数）

-- 求和
sum(字段)

-- 平均值
avg(字段)

--最大最小
max(字段)  min(字段)

-- 累计个数
count(*)
count(字段)
count(DISTINCT 字段)

注意：

组函数自动把空值忽略，空值需要注意处理

示例：

-- 查询公司的总成本sum()
select sum(sal+ifnull(comm,0))
from emp;

-- 30部门平均薪资avg()
select avg(sal+ifnull(comm,0))
from emp
where deptno=30;

-- 查询有奖金员工最高薪资 max()
select max(sal)
from emp
where comm is not null;


-- 查询30，40部门中最早入职时间 min()
select min(hiredate)
from emp
where deptno in(30,40);

-- 统计公司人数
select count(*)
from emp;


-- 统计公司部门数
select count(*)
from dept;

-- 统计有奖金员工个数(count 自动忽略空值)
select count(comm)
from emp;

-- 统计员工表中几个职位
select count(distinct job)
from emp;

练习：

-- 1、统计薪资等级是1的员工个数
select count(e.empno)
from emp e,salgrade s
where e.sal between s.losal and s.hisal and s.grade =1;
-- 2.查询名字中有a的员工个数 
select count(*)
from emp
where ename like '%a%';
-- 3，查询8月份入职的员工最高薪资   
select max(sal)
from emp
where month(hiredate) = 8;
-- 4.查询部门位置在大连的员工个数
select count(empno)
from emp e,dept d
where e.deptno = d.deptno and d.loc = '大连';

需求： 按照部门分组，统计每个部门的平均工资

分组语法：

SELECT 别名1.* , 别名.列名 
FROM 表1 别名1, 表2 别名2
WHERE 别名1.列 = 别名2.列 AND 条件+
GROUP BY 分组字段1,分组字段2...
[HAVING 组函数条件]
ORDER BY 排序字段 ;

注意：

GROUP BY 根据列实现分组，根据多个列，同时不一样的，新的一组

GROUP BY后分组列，才能出现在SELECT后

重点>顺序： WHERE筛选(可有可无) > GROUP BY 分组 > HAVING 组分完后条件(可有可无)

示例:

-- 每个部门的平均薪资(有奖金),平均薪资在5000以上
select deptno,avg(sal) a
from emp
where comm is not null
group by deptno
having a > 5000
order by a desc;

示例2：（带有表连接）

-- 各个部门有奖金的员工个数
select deptno,count(empno)
from emp
where comm is not null
group by deptno;


-- 查询各个部门位置下员工个数
select loc,count(empno)
from emp e right join dept d
using(deptno) --using 两个表做连接的时候，列是同一个名字，才可以使用using（共同的列）
group by loc;

-- 查询各个领导者手下的员工个数（没有领导的员工也要显示）
select m.ename,count(e.empno)
from emp e left join emp m
on e.mgr = m.empno
group by m.ename;

2.6 子查询

在得到查询结果后，进一步实现再查询，叫做子查询。

需求： 查询公司入职最早的员工信息

1，查询最早入职时间
2，谁是这个入职时间

语法规则1：

SELECT 别名1.* , 别名.列名 
FROM 表1 别名1, 表2 别名2
WHERE 别名1.列 = 别名2.列 AND  列  比较  (子查询)

注意：

1，外层查询叫做主查询，（）里的是子查询

2，先做子查询，子查询会把结果给主查询使用

3，比较多种方式： ①单行子查询 ② 多行子查询

示例：

select *
from emp
where hiredate=(select min(hiredate) from emp);

练习：
1，查询和沈师傅相同部门的员工信息
2，查询比30或40部门平均薪资高的员工信息
3，查询公司工资最高的员工信息

-- 1， 和沈师傅相同部门的员工信息
select*
from emp
where deptno=(select deptno from emp where ename='沈师傅');

-- 2，查询比30或40部门平均薪资高的员工信息
select *
from emp
where sal>(select avg(sal) from emp where deptno in (30,40));

-- 3.查询公司工资最高的员工信息
select *
from emp
where sal=(select max(sal) from emp);

①单行子查询 子查询结果是一行数据，主查询比较的时候： = > < >= <= …

② 多行子查询 子查询的结果是多行数据，主查询比较的时候 : IN >ALL < ALL >ANY <ANY

运算符	含义
>ALL	大于最大的
<ALL	小于最小的
>ANY	大于最小的
<ANY	小于最大的

示例：

--  查询和名字中有'c'员工相同部门的信息
select *
from emp
where deptno in (select deptno from emp where ename like '%c%');

-- 查询比10或30部门平均薪资还高的员工信息
select *
from emp
where sal >ALL (select avg(sal) from emp where deptno in(10,30) group by deptno);

-- 查询比自己所在部门平均工资高的员工信息
SELECT *
FROM emp e
WHERE e.sal>(SELECT AVG(a.sal) FROM emp a WHERE e.deptno = a.deptno);

语法规则2：

SELECT 别名1.* , 别名.列名 
FROM 表1 别名1, (子查询)  别名2
WHERE 别名1.列 = 别名2.列;

示例：

select *
from emp e, (SELECT deptno,AVG(a.sal) a FROM emp a group by deptno) m
where e.deptno=m.deptno and e.sal > m.a;

2.7 分页查询和TOP_N查询

2.7.1 分页查询

为了控制数据量，将所有的数据分成几页显示。分页：真分页和假分页。

真分页： 从数据层实现了分页查询。（mysql 使用的是limit ; oracle使用的rownum）

假分页： 前端获取到的后端的所有数据，前端控制显示。

-- 总共几条数据 , 一页几个 , 一共几页 ,  第几页 ...
-- 第一页 (数据起始行，终止行)

语法规则：

SELECT ....
FROM ....
WHERE ....
LIMIT 起始位置,查询个数（一页几个）

示例：

-- 第一页数据
SELECT *
from emp
limit 0,3;


-- 第二页数据
SELECT *
from emp
limit 3,3;

注意： 下标从0开始

2.7.2 TOP—N查询

TOP-N解决的是xxx 的前几名，xx的第几名， 第几名~第几名.

用白话来讲：工资最高的前两个人是谁，工资最高的是谁，工资排第3的人是谁，入职最早的前两个人是谁。

需求： 查询工资排在前两位的员工信息

语法规则：

SELECT..
FROM ...
WHERE ...
GROUP BY ...
HAVING...
ORDER BY ...
LIMIT 起始位置，查询个数

注意：

having和group by必须一起使用，或者只使用group by不使用having。

但是有having的时候必须出现group by。

示例：

-- 需求：  查询工资排在前两位的员工信息
select *
from emp
order by sal desc
limit 0,2;

练习：

-- 1， 查询有奖金的且10或40部门的员工薪资前两位（考虑奖金）
select *
from emp
where comm is not null and deptno in (10,40)
order by sal+ifnull(comm,0) desc
limit 0,2;

-- 2. 查询人数最多的两个部门的信息（包含部门位置等）
select e.deptno, d.dname,d.loc,count(empno) c
from emp e ,dept d
where d.deptno = e.deptno 
group by deptno
order by c desc
limit 0 ,2;

-- 3. 查询成本最高的职位
select job ,sum(sal+ifnull(comm,0)) as qian
from emp
group by job
order by qian desc
limit 0,1;

总结：

SELECT...DISTINCT / COUNT()/ SUM()/
FROM ...
WHERE AND/OR 
GROUP BY ...
HAVING ...  -- 主函数  分组
ORDER BY ...   -- 排序
LIMIT ...   -- 进行限制

第三章 数据库数据操作语言

DML：数据库的操作语言

1.增加数据
2.修改数据
3.删除数据
4.事务操作

3.1 增加数据

向表中添加数据的过程。每次只能向表增加一条数据。

语法规则：

INSERT INTO 表名[(列名1,列名2...)]
VALUES (值1，值2...)

注意：

1，表后面列可以省略，此时需要将表的所有列数据都插入（按照顺序）

2，值对应列给定，列和值的类型个数对应的

示例：

-- 向部门表添加数据  50 部门 财务部  沈阳
insert into dept
values(50,'财务部','沈阳');


-- 向员工表添加数据 ： 编号8989 于小闹   80000  20000 入职时间明天  50 
insert into emp(empno,ename,sal,comm,hiredate,deptno)
values(8989,'于小闹',80000,20000,DATE_ADD(now(),INTERVAL 1 DAY),50);

练习：

-- 1 新增部门 60部门    管理部  深圳
insert into dept(deptno,dname,loc)
values (60,'管理部','深圳')
select * from dept;

-- 2. 新增员工  9898 王大仙   本月最后一天入职  60部门  薪资60000
insert into emp(empno,ename,sal,hiredate,deptno)
values(9898,'王大仙',60000,last_day(now()),60);
select * from emp;

-- 3.新增工资等级  4级
insert into salgrade(grade)
values(4)
select * from salgrade;

特殊的： 数据批量增加到表

-- 复制表
create table emp2
as 
select * from emp where deptno=30;

-- 批量增加，将emp中10,50部门员工增加到emp2

-- INSERT INTo 表名  子查询
insert into emp2
select * from emp where deptno in(10,50);

3.2 修改数据

语法规则：

UPDATE 表名
SET 列=值 , 列=值 ....
WHERE 条件 AND...

示例：

需求： 30部门的员工薪资提高2000元

-- update emp
-- set sal = sal+2000
-- where deptno=30;

-- 修改于小闹的职位为 CFO
update emp
set job='CFO'
where ename='于小闹';

-- 设置沈师傅的领导为于小闹
update emp
set mgr = (select empno from emp2 where ename='于小闹')
where ename='沈师傅';

3.3 删除数据

DELETE FROM 表名
[WHERE 条件;]

注意：

没有WHERE条件，相当于删除表中所有数据，注意事务。

3.4 事务处理（重点）

3.4.1 什么事事务？

数据库中DML一组操作集合单元（一组动作单元，一个逻辑单元）。

如： 五次操作，手里有500元，每次向卡中存100 , 五次动作是一组动作一个逻辑单元。

如 ： 事务动作，存钱 ： 第1步机器识别金额， 第2步银行确认存入

如： 购买商品 ： 1. 删除购物车 2. 加入订单

以上这些都是一个事务（一个业务单元）。

3.4.2 事务的存在意义

保证数据的一致，协调和安全。（要么都做，要么都不做）。

3.4.3 事务的特征

ACID：

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）
事务前后数据的完整性必须保持一致。

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

3.4.4 事务的开始和结束

事务的开始： 从发生第一个DML动作

事务的结束:
提交数据： COMMIT；
一个事务所有操作被执行，保留到数据库。

回滚数据： ROLLBACK；
一个事务所有操作全都认为没有执行。

-- 事务启动
start transaction;
-- 第一个DML
delete from emp2 where ename='侯哥';
-- 第二个DML
delete from emp2 where ename='nao';
-- 回滚信息
-- rollback;
-- 提交数据
commit;
--查询验证
select * from emp2;

第四章 数据库的定义语言

DDL：数据库的定义语言，创建表，修改表，删除表，约束。

4.1 创建表

CREATE TABLE 表名(
	列名  数据类型(大小)  [DEFAULT 默认值 ] [约束],
    列名  数据类型(大小)  [DEFAULT 默认值 ] [约束],
    ...
    列名  数据类型(大小)  [DEFAULT 默认值 ] [约束]
)

示例：

需求：创建一个学生表（学号 姓名 年龄 入学时间）

-- 需求：创建一个学生表（学号 姓名 体重 入学时间）
-- create table student(
-- 		-- 学号 主键
-- 		sno int(10) primary key,
-- 		-- 姓名
-- 		sname varchar(30),
-- 		-- 体重
-- 		sweight double(4,1),
-- 		-- 入学时间
-- 		sdate date
-- 	
-- );

-- insert into student
-- values(1,'张三',120.5,now());
-- insert into student
-- values(2,'李四',220.5,now());


select * from student;

4.2 修改表（了解）

当修改表的时候，表中数据都会影响，其他的表也会有影响（关联）。

需求不断变化，表信息随着改变，修改表。

1.修改列的大小

ALTER TABLE 表名
MODIFY 列 数据类型(大小);

2.新增列

ALTER TABLE 表名
ADD(列 数据类型(大小));

3.删除列

ALTER TABLE 表名
DROP 列;

示例：

-- 修改学生名字长度为50
alter table student
modify sname varchar(50);


-- 新增列 性别(默认值)  串
alter table student
add(sgender varchar(8) default '男');


-- 删除性别列
alter table student
drop sgender;

4.3 删除表

-- 删除表数据 : 可以回滚，只删除数据，保留表结构，表空间
DELETE FROM 表 ; 

-- 删除表 : 不可以回滚，全部删除
DROP TABLE 表 ; 

-- 截断表 : 不可以回滚，删除数据，保留表结构，表空间
TRUNCATE TABLE 表 ; 

4.4 约束

对表，或者对列添加限制条件。

4.4.1 约束的类型

1. 非空约束   NOT NULL
2. 唯一约束   UNIQUE
3. 主键约束   PRIMARY KEY
4. 外键约束	  FOREIGN KEY
5. 检查约束   CHECK

4.4.2 约束定义时间和位置

添加约束的时间：

1，建表 CREATE

​2，改表 ALTER

添加约束的位置：

1，列级约束

​2，表级约束

CREATE TABLE 表名(
	列名  数据类型(大小)  [DEFAULT 默认值 ] [列级约束],
    列名  数据类型(大小)  [DEFAULT 默认值 ] [列级约束],
    ...
    列名  数据类型(大小)  [DEFAULT 默认值 ] [列级约束],
    表级约束(列)
)

4.4.3 约束的详解

1， 非空约束 （列插入数据时，不允许为空，设置列级）

CREATE TABLE 表名(
	列名  数据类型(大小)  NOT NULL ,
	...
)

示例：

-- 新建表 学生表 
create table student(
		-- 学号 主键
		sno int(10) primary key,
		-- 姓名
		sname varchar(30) not null,  -- 非空约束
		-- 体重
		sweight double(4,1),
		-- 入学时间
		sdate date
	
);

2，唯一约束 （列插入数据时，不允许重复，可以列级，可以表级别）

CREATE TABLE 表名(
	列名  数据类型(大小)  UNIQUE ,
	UNIQUE(列)
)

-- 新建表 学生表 
create table student(
		-- 学号 主键
		sno int(10) primary key,
		-- 姓名
		sname varchar(30) not null,  -- 非空约束
		-- 体重
		sweight double(4,1),
		-- 入学时间
		sdate date
		-- 手机号
		stel varchar(11),
		unique(stel)  -- 表级约束 唯一约束
);

3，主键约束（表的唯一标志）
强调：

1.任何一个表都要有主键，且只有一个主键

2.表的主键不一定是一个列，可以是多个列(联合主键)

3.主键=非空+唯一

create table 表名(
列名 数据类型(大小) primary key,
-- 表级联合主键
primary key(列1，列2)
)

示例：

-- 1. 学生表 学号（主键） 姓名（非空）   手机（唯一）
CREATE TABLE student(
	stuno int(8) PRIMARY KEY,
	sname varchar(20) NOT NULL,
	tel varchar(11) UNIQUE
);
-- 2. 课程  课程号（主键）  课程名  学分（double）
CREATE TABLE course(
	cno int(8) PRIMARY KEY,
	cname varchar(10),
	credit double(3,1)
);
-- 3. 选课 （学生号  课程号  地点）
CREATE TABLE course_selection(
	stuno int(8),
	cno int(8),
	cloc varchar(10),
	PRIMARY KEY(stuno,cno)
);

4，外键约束（表和表之间的关系）

A表 B表

当A表中某个列参照于B表中某个列，此时需要的是外键约束。（员工的部门号参照部门表，选课表学号参照于学生表）

A表 从表或子表 B表主表或父表。

create table A表(
...
-- 表级外键
foreign key(外键列) references B表(主键列)
)

示例：

drop table course_selection;
CREATE TABLE course_selection(
	stuno int(8),
	cno int(8),
	cloc varchar(10),
	PRIMARY KEY(stuno,cno),
	-- 外键
	foreign key(stuno) references student(stuno),
	foreign key(cno) references course(cno)
);

如果需要删除父项数据，子表有所占用，此时不允许删除父项数据（1号学生选课了，1号学生就不能删除）

级联删除：删除父项数据时，自动子表数据全部删除

CREATE TABLE A表(
	...
	-- 表级外键（级联删除）
	FOREIGN KEY (外键列) REFERENCES  B表(主键列) ON DELETE CASCADE
)

示例：

drop table course_selection;
CREATE TABLE course_selection(
	stuno int(8),
	cno int(8),
	cloc varchar(10),
	PRIMARY KEY(stuno,cno),
	-- 外键
	foreign key(stuno) references student(stuno) on delete cascade,
	foreign key(cno) references course(cno) on delete cascade
);

5，检查约束

检查表中列的域值，检查列的取值范围

CREATE TABLE 表(
	...
	-- 表级 
	CHECK (列的条件[等同WHERE])
)

示例：

-- 客户表
-- 编号，姓名，年龄，性别
drop table customer;
create table customer(
cno int(10) primary key,
cname varchar(30) not null,
cage int(3),
csex varcahr(6),
check(cage>=1 and cage<=150)
);

第五章 其他的数据库对象

5.1 视图

5.1.1 什么是视图？

将表当中逻辑上相关的数据抽取，形成一个独立的视图对象，当使用的时候，只去关注操作需要的部分。

注意： 操作视图的时候，操作的是原表。

5.1.2 视图的好处？

1.可以提前预览数据
2.将逻辑相关的数据提取
3.方便简化数据的操作

5.1.3 视图如何创建？

CREATE [OR REPLACE] VIEW 视图名()-- 括号里可以写别名
AS 
子查询

示例：

-- 创建一个视图
create view emp_sal30
as
select empno,ename,sal,comm
from emp
where deptno = 30;

5.1.4 视图如何使用？

-- 查询视图数据
select * from emp_sal30;

-- 增加数据
insert into emp_sal30
values(9999,'rose',6666,1111);

-- 视图操作的是原表
select * from emp;

5.1.5 视图删除

DEOP VIEW 视图名;

5.2 索引

5.2.1 什么是索引？

索引类似与目录，为我们查询列的时候提供导向，方便快速做查询该列，快速通道。

5.2.2 索引的作用？

1，加快查询效率
2，减少磁盘IO，不需要向数据库的服务器发出请求

5.2.3 何时适合使用

1. 什么情况不适合索引？

​ ① 数据量小，查询量很小
​ ② 表经常更新，改变

2. 什么情况适合索引？

​ ① 数据量大

​ ② 查询量大，查询中经常出现空值

​ ③ 表连接多

​ ④ 表中某列经常查询

​ …

5.2.4 索引的创建

CREATE INDEX 索引名
ON 表名(列)

示例：

create index emp_sal
on emp(sal);

5.2.5 索引的删除

DROP INDEX 索引名;

5.3 自动增长列

-- 只有加自动增长列，忽略主键
INSERT INTO dept(dname)
VALUES('保洁部');