数据库介绍
定义
数据库是一种存储并管理数据的软件系统
DataBase 简称:DB
DataBaseManagement System 简称:DBMS
存储：持久化
管理：增删改查
常用的存储数据的方式：
1、Java中的变量：生命周期短，不能实现持久化
2、序列化：管理数据时依赖于Java中的反序列化
3、txt，办公软件：没有统一的方式管理数据
4、数据库
数据库分类
关系型数据库
采用关系模型来组织数据，简单来说，关系模型指的就是二维表格模型。类似于Excel工作表。
特点：
所存储的数据有一定的关系
将数据存储在磁盘上
重在持久化
Mysql Oracle sqlServer DB2
非关系型数据库（NoSQL,not only sql），
可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，通过减少很少用的功能，来提高性能。
特点：
以key-value或json的方式存储数据
将数据存储在内存中
重在性能，读取速度快 MongoDB Redis Hbase neo4j
关系型数据库设计规则
遵循ER模型
E entity 代表实体的意思 对应到数据库当中的一张表 R relationship 代表关系的意思 具体体现
将数据放到表中，表再放到库中。 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。 表由列组成，我们也称为字段。
创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名
每个字段类似java 或者python中的“实例属性”。
表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。
select
-- select 列1,列2,列3 .... from 表
-- sql是不严格区分大小写
select empno,ename,sal from emp
SELECT EMPNO,ename,sal From Emp
-- select * from 表
-- *代表所有列
select * from emp
-- 列的计算
select empno,ename,sal,sal+1000 from emp
select empno,ename,sal,sal*12 from emp
-- 列的别名
-- 1. 直接空格 新的列名
select empno 员工编号,ename 员工名字 from emp
-- 2. as
select empno as 员工编号,ename as 员工名字 from emp
-- 列的拼接
select concat(empno,ename) from emp
-- 去重distinct
select distinct(job) from emp
-- where 筛选数据
-- select 列... from 表 where 条件
-- 查询员工编号是7788的信息
select * from emp where empno=7788
-- 查询工资大于2000的员工信息
select * from emp where sal>2000
-- 查询名字是ALLEN的员工信息
-- 字符串用单引号
select * from emp where ename='ALLEN'
-- 字符串的大小写会有区分 mysql可以智能忽略 oracle不可以
select * from emp where ename='allen'
-- 查询入职日期是1981-02-20的员工信息
select * from emp where hiredate = '1981-02-20'
select * from emp where hiredate = '1981/02/20'
-- 逻辑运算 与或非
-- and or not
-- 查询员工部门编号是10并且工资大于1500的信息
select * from emp where deptno=10 and sal >1500
-- 查询员工部门编号是10或者工资大于1500的信息
select * from emp where deptno=10 or sal >1500
-- 查询员工部门编号不是10的信息
-- 不等号 <>
select * from emp where deptno <> 10
select * from emp where not deptno=10 -- not 是取反
-- between and 包含边界，包含1250
-- 查询工资在1000和2000之间的员工信息
select * from emp where 1250<=sal and sal <=2000
select * from emp where sal between 1250 and 2000
-- 多个值
-- in(值1,值2,值3...)
-- 查询工作是SALESMAN 或者是CLERK 或者是MANAGER的员工信息
select * from emp where job='SALESMAN' or job='CLERK' or job='MANAGER'
select * from emp where job in ('SALESMAN' ,'CLERK','MANAGER')
-- null计算
-- null 默认 不占内存
-- null和任何值计算结果都是空
-- 查询工资和奖金的和
select empno,ename,sal,comm,sal+comm from emp
-- 判断为空 is null is not null
-- 查询工资构成没有奖金项的
select * from emp where comm is null
select * from emp where comm is not null
-- 模糊查询
-- like '%key%' %表示0个或者多个字符
-- 查询名字中含有字母A的员工信息
select * from emp where ename like '%A%'
-- 查询名字中以字母A开头的员工信息
select * from emp where ename like 'A%'
-- 查询名字以字母N结尾的员工信息
select * from emp where ename like '%N'
-- 查询名字不含有字母A的员工信息
select * from emp where ename not like '%A%'
-- _下划线表示1个字符
-- 查询名字中第二个字母是M的员工信息
select * from emp where ename like '_M%'
-- 查询名字中第三个字母是M的员工信息
select * from emp where ename like '__M%'
-- 查询名字中倒数第二个字母是E的员工信息
select * from emp where ename like '%E_'
-- 排序 放在最后
-- order by 列 asc|desc asc默认 升序排列 可以省略 desc是降序排列
-- 以工资从大到下排序显示员工信息
select * from emp order by sal desc
-- 以员工入职早晚排序显示员工信息
select * from emp order by hiredate asc
-- 查询工资从大到小 然后入职从早到晚排序显示员工信息
select * from emp order by sal desc, hiredate asc
-- 部门10的员工以工号从小到大排序
select * from emp where deptno=10 order by empno
-- 查询工资大于1500或者部门是30的员工信息以入职早晚排序
select * from emp where sal>1500 or deptno=30 order by hiredate asc
-- order by 1 默认以第一列排序 order by 2第二列
select * from emp order by 1
select * from emp order by 2
-- 可以对列的重命名进行排序
select empno,ename,sal*12 as yearsal from emp order by yearsal desc
-- 查询年薪超过30000的员工信息以降序排列
-- 错误 在where中不识别yearsal
-- where后面不能用列的别名 排序可以使用
select empno,ename,sal*12 as yearsal from emp where yearsal>30000 order by yearsal desc
select empno,ename,sal*12 as yearsal from emp where sal*12>30000 order by yearsal desc
-- 数字函数
-- dual 伪表
-- mod 取余
select mod(12,5) from dual
-- 向上取整
select ceil(12.34)
-- 向下取整
select floor(12.94)
-- 四舍五入
select round(12.34)
select round(12.54)
select round(12.54123,2)
select round(12.54623,2)
-- abs 绝对值
select abs(-12)
-- sqrt开方
select SQRT(16)
-- TRUNCATE(X,D) 截断
select TRUNCATE(12.345,2)-- 12.34
-- POW(X,Y)
select pow(2,10)-- 1024
-- 字符函数
-- concat 拼接
-- length计算长度
-- 查询名字长度是5的员工信息
select empno,ename,sal from emp where length(ename)=5
-- LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.
-- 查询名字中不含有字母A
select empno,ename ,sal,locate('A',ename) from emp
select empno,ename ,sal from emp where locate('A',ename)=0
-- 查询字符首次出现的位置
select empno,ename ,sal,instr(ename,'A') from emp
-- LEFT(str,len):返回字符串str的最左面len个字符。
select ename, left(ename,3) from emp
-- RIGHT(str,len):返回字符串str的最右面len个字符
select ename, right(ename,3) from emp
-- 获取手机号后四位
select right('13589679696',4)
-- 获取中间四位
select left(right('13589679696',8),4)
-- 截取字符串SUBSTRING
select ename, SUBSTRING(ename,2) from emp
-- substring(字符串,截取的位置,长度)
select ename, SUBSTRING(ename,2,4) from emp
-- 截取电话中间四位
select SUBSTRING('13589679696',4,4)
-- replace 替换
-- trim去首尾空格
-- REVERSE 颠倒
-- 日期函数
-- 获取当前系统时间2023-02-22 15:51:40
select now()
-- DAYOFWEEK(date) 返回星期 (1=星期天，2=星期一, …7=星期六
select DAYOFWEEK(now())
-- WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select WEEKDAY(now())
-- DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。
select DAYOFMONTH(now())
select DAYOFMONTH('2020-06-08')
-- DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
select DAYOFYEAR(now())
-- MONTH(date):返回date的月份，范围1到12。
select MONTH(now())
-- DAYNAME(date):返回date的星期名字。
-- MONTHNAME(date) :返回date的月份名字。
-- QUARTER(date):返回date一年中的季度，范围1到4。
select DAYNAME(now()) -- Wednesday
select QUARTER(now())
/*
WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，
返回date的周数，范围在0到52。2个参数形式WEEK()允许你指
定星期是否开始于星期天或星期一。如果第二个参数是0，
星期从星期天开始，如果第二个参数是1，从星期一开始。
YEAR(date):返回date的年份，范围在1000到9999。
HOUR(time):返回time的小时，范围是0到23。
MINUTE(time):返回time的分钟，范围是0到59。
SECOND(time):回来time的秒数，范围是0到59。
DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒
DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒
*/
SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);
SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-- 获取日期
select CURRENT_DATE
-- 获取时间
select CURTIME();
-- 加密函数 MD5加密
select password('123456')
-- 格式化函数FORMAT
-- 将date转换成字符串
-- DATE_FORMAT(date,format) date:需要转换的日期 format:格式化的样式
select DATE_FORMAT(now(),"%Y/%m/%d %H:%i:%s %p")
-- STR_TO_DATE(str,format) 字符串转换成date
select STR_TO_DATE('1992/04/12','%Y/%m/%d')
-- 计算今天到1992年4月12号间隔多少天
-- datediff 两个日期间隔多少天
select (CURRENT_DATE, STR_TO_DATE('1992/04/12','%Y/%m/%d') )
select datediff(now(),STR_TO_DATE('2001-04-04','%Y-%m-%d') )
-- ifnull(值1,值2) 如果值1不是null,则返回值1,如果值1是空,则返回值2
-- 工资加奖金
select ename,sal,comm,sal+comm from emp
select ename,sal,comm,sal+ifnull(comm,0) from emp
-- 工作是销售的月薪涨100，是经理的涨500，其他的涨800
select empno,ename,sal,job，
case job when 'SALESMAN' then sal+100
when 'MANAGER' then sal+500
else sal+800
end 涨薪后
FROM emp
-- 对字符串加密
select MD5('888')
/*
DML操作
数据库操纵语言
insert update delete
*/
-- 插入数据
-- insert into 表名(列1,列2,列3....) values (值1,值2,值3.....)
insert into class(classno,classname,classloc) values (1,'三年二班','一楼')
insert into class(classno,classname,classloc) values (2,'三年三班','一楼')
-- Duplicate entry '1' for key 'PRIMARY' 再次运行出现问题
-- 列不写 默认是按顺序的所有列
insert into class values (3,'一年四班','三楼')
insert into class values (12,'六年一班','三楼')
-- 创建表结构和数据和emp相同的
-- 只有表名，数据copy过来，没有约束
create table emps as select * from emp
-- 保证两个表的结构一致
-- 向emps表中插入emp表中部门10的数据
insert into emps select * from emp where deptno=10
-- 提交回滚
insert into class values (14,'六年一班','三楼')
commit;
rollback;
-- update 更新
-- update 表名 set 列1=值1,列2=值2.... where 条件过滤
-- 修改班级表信息
update class set classloc='二楼' where classno=12
-- 修改7788的员工编号工资改为1000 工作改为销售
update emps set sal=1000,job='SALESMAN' where empno=7788
-- 修改7788的工资改为原来的二倍
update emps set sal = sal*2 where empno=7788
-- 修改7788的入职日期是今天
update emps set hiredate='2023-02-23' where empno=7788
update emps set hiredate=CURRENT_DATE where empno=7876
-- 删除
-- delete from 表 where 条件
-- 删除emps表中员工号是7788的信息
delete from emps where empno=7788
-- 删除emps表中部门编号是10的员工信息
delete from emps where deptno=10
-- 删除表的三种方式
/*
delete from emps 删除所有行 表结构存在 可以回滚
truncate table emps 截断表 删除所有行 表结构存在 不可以回滚
drop table emps 删除表对象 结构和数据都不存在
*/
-- 分页
-- limit 起始行,长度
-- 当前第n页,每页显示m条数据
-- 起始行和终了行end
start = (n-1)*m+1 n*m- (m-1)
end = n*m
6-11
select * from emp limit 5,6
/*
n=1 m=5 start 1 end 5
n=2 m=5 start 6 end 10
n=3 m=5 start 11 end 15
*/
-- 查询员工表前5行数据
select * from emp limit 5
-- 第一行从0开始
select * from emp limit 0,5
-- 查询第二页数据 每页显示5条
select * from emp limit 5,5
select * from emp limit 10,5
-- 以工资从大到小排序 每页显示5条数据,查询第一页的数据 5和(0,5)一样
select * from emp order by sal desc limit 5
-- 查询工资前5名的员工信息
select * from emp order by sal desc limit 0,5
select * from emp order by sal desc limit 5,5
-- 查询工资大于等于1500的员工信息以升序排列,前三名的员工信息
select * from emp where sal>=1500 order by sal asc limit 3
-- 查询工资最高的员工信息 ?最大工资相同
select * from emp order by sal desc limit 1
/*
多表连接
*/
-- 内连接 (等值连接)
-- inner join
-- select 列 from 表1 inner join 表2 on 条件
select
empno,ename,sal,hiredate,emp.deptno ,dname,loc
-- 共通的列要明确声明是哪个表的列 emp.deptno
from
emp
inner join
dept
on
emp.deptno = dept.deptno
-- 对表的重命名
-- 直接空格 简化表名
select
empno,ename,sal,hiredate,e.deptno ,dname,loc
from
emp e -- 对表起别名
inner join
dept d
on
e.deptno = d.deptno
/*
左外连接
以左表为基础,左表数据完全显示,右表匹配显示
select 列... from 左表 left join 右表 on 条件
*/
select
empno,ename,hiredate,sal,e.deptno,dname,loc
from emp e
left join
dept d
on
e.deptno=d.deptno
-- 查询部门所有信息以及该部门下的所有员工
select
empno,ename,hiredate,sal,d.deptno,dname,loc
from dept d
left join
emp e
on
e.deptno=d.deptno
/*
内连接(等值连接)和左外的区别
内连接不相等该数据都不显示
左外连接不管值是否相等,都会左表所有数据.
*/
/*
右外连接
以右表为基础,右表数据完全显示,左表匹配显示
select 列... from 左表 right join 右表 on 条件
*/
select
empno,ename,hiredate,sal,e.deptno,dname,loc
from emp e
right join
dept d
on
e.deptno=d.deptno
create table emp10 as select * from emp where deptno=10
/*
/*
范式:对表设计的规则规范
NF
第一范式:1NF 表中的列不可再分割(一个格存放一个数据)
第二范式:2NF 基于一范式基础上,表中必须有唯一标识的列(用来分区行)，主键
第三范式:3NF 基于二范式基础上,表中出现的列必须由主键列决定，外键
主键:primary key 用来区分唯一标识的列 非空且唯一 emp表中empno列是主键 dept表中deptno
外键:foreign key 主表的表的主键 被从表引用 emp表中deptno是外键 dept表中的deptno是主键
主表:数据被从表引用 dept表是主表 数据被emp引用
从表:引用主表中的数据 emp是从表 引用了dept表的数据
表和表之间的关系:
1V1 一对一 一个员工对应一个部门
1VN 一对多 一个部门下有多个员工
NVN 多对多 多个部门下有多个员工
*/
drop table emptest;
create table emptest (
empno int,
ename varchar(20) NOT NULL , -- 不为空
job varchar(20) CHECK(length(job)>=2) ,
deptno int,
CONSTRAINT un_ename UNIQUE KEY(ename), -- 唯一
CONSTRAINT pk_emptest PRIMARY KEY(empno),
CONSTRAINT fk_emptest FOREIGN KEY(deptno) REFERENCES dept(deptno)
)
/*
/*
集合操作
交 并 差
*/
-- union 做并集操作 不包含重复的行 同时进行默认排序
select * from emp
union
select * from emp10
-- union all 做并集操作 包含重复的行
select * from emp
union all
select * from emp10
-- intersect 交集 minus 差集 mysql不支持
select * from emp
intersect
select * from emp10
/*
分组函数
avg 平均
max 最大
min 最小
count 统计
sum 求和
*/
-- 查询所有员工的平均工资
select avg(sal) from emp
select avg(comm) from emp -- 不包含null在内
select avg(ifnull(comm,0)) from emp -- 所有人的平均
-- 查询工资最高的金额是多少
select max(sal) from emp
-- 查询工资最低是多少
select min(sal) from emp
-- 查询最早来公司的入职日期是多少
select min(hiredate) from emp
-- 查询所有员工的工资总和
select sum(sal) from emp
-- 查询所有员工的奖金总和
select sum(comm) from emp
-- count(列)
-- 查询一共有多少员工
select count(empno) from emp
select count(distinct(job)) from emp
select count(comm) from emp -- comm空值的不统计
select count(*) from emp
select count(1) from emp
-- 查询部门10的员工人数
select count(empno) from emp where DEPTNO=10
/*
count(empno):会忽略null 索引扫描
count(*) : 包含所有列 相当于查询表的所有行数 不会忽略null 全表扫描 效率低
count(1):1代表代码行 不会忽略null值,会过滤掉所有列都是null的行 扫描索引
count(列) 列设置主键 比count(1)快
列不是主键 count(1)快
表没有主键列 count(1)比count(*)快
如果表只有一列 count(*)最快
count(*)包含所有的列 相当于查询表的所有行 不会忽略空 全表扫描 查询慢
总结:有主键 count(主键) 没有主键 count(1)
*/
-- sum(1)相当于count(*) sum(n) 相当于行数*n
select sum(1) from emp
select sum(2) from emp
-- 设置不自动提交
show variables like 'autocommit'
set autocommit=0
-- 查询平均工资超过1500的工作 分组可以去重
select job,avg(sal) from emp group by job HAVING avg(sal)>1500
-- 查询平均工资超过1500的工作,工作不等于MANAGER
select job,avg(sal)
from emp
where job<>'MANAGER'
group by job
HAVING avg(sal)>1500
-- 查询每种工作的最高工资
select job,max(sal) from emp group by job
select max(sal) from emp group by job
-- 查询每个部门每种工作的的最低工资
select job,deptno,min(sal) from emp group by deptno , job
-- 查询每个部门的人数
select deptno,count(empno) from emp group by deptno
-- 统计除了部门10的每个部门的平均工资
select deptno,avg(sal) from emp where deptno<>10 group by deptno
-- having
-- 用来过滤分组条件
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000
-- 查询最高工资超过2500的工作
select job,max(sal) from emp group by job having max(sal)>2500
-- 查询最高工资超过2500的工作并且工作不是PRESIDENT
select job,max(sal) from emp where job<>'PRESIDENT' group by job having max(sal)>2500
-- 查询部门人数超过3的人的部门
select deptno,count(empno) from emp group by deptno having count(empno)>3
-- 查询每个部门的详细信息以及部门的人数
select
count(empno) as 部门人数,d.deptno ,dname,loc
from
dept d
left join
emp e
on
e.deptno = d.deptno
group by d.deptno,dname,loc
-- 查询每个部门详细并且平均工资超过1500
select
avg(sal) as 平均工资,d.deptno ,dname,loc
from
dept d
left join
emp e
on
e.deptno = d.deptno
group by d.deptno,dname,loc
HAVING avg(sal)>1500
-- 子查询
-- where条件中select语句(嵌套)
-- 查询和scott一个部门的员工信息
select * from emp where deptno=(
select deptno from emp where ename='scott')
-- 查询工资高于scott的员工信息
select * from emp where sal>(
select sal from emp where ename='scott')
-- 查询工资高于平均工资的员工信息
select * from emp where sal>(
select avg(sal) from emp)
-- 多个值 in any all
-- 查询工资大于部门20所有员工工资的信息
-- 1.找到20最大工资
select * from emp where sal>(
select max(sal) from emp where deptno=20)
-- 2.一个一个比
select * from emp where sal >all(
select sal from emp where deptno=20)
-- 查询工资大于部门10随意员工的工资的信息
select * from emp where sal >(
select min(sal) from emp where deptno=10) and deptno<>10
select * from emp where sal > any(
select sal from emp where deptno=10) and deptno<>10
-- 查询工资和部门20相等员工的信息
select * from emp where sal in(
select sal from emp where deptno=20) and deptno<>20
-- 查询king所在部门的平均工资
select avg(sal) from emp where deptno=(
select deptno from emp where ename='king')
-- 查询king所在部门入职最早的员工信息
select *,min(HIREDATE) from emp where deptno=(
select deptno from emp where ename='king')
select * from emp where deptno=(
select deptno from emp where ename='king')
--
-- 自然连接 NATURAL
-- 特殊的等值连接
-- 两个列相同值相等产生连接
-- 共通的列只显示一列 不用明确属于哪个表
select * from emp NATURAL join dept
-- 自连接 自己连接自己
-- mgr 员工领导的工号
-- emp表当做两个表 一个是员工表 一个是领导表
-- 员工的mgr是领导的empno
select
worker.empno,worker.ename,worker.mgr,worker.ename
from
emp worker,emp leader
where worker.mgr=leader.empno
-- 非等值连接
-- 查询员工的工资等级
select
e.*,s.*
from
emp e
INNER join
salgrade s
on e.sal BETWEEN s.LOSAL and s.HISAL
-- 查询部门10中的工资超过2000的员工信息
select * from emp where deptno=10 and sal>2000
-- 换个思路 1.先查询部门10的信息 作为一个新的临时表
-- 叫newtable 2.在该表的基础上再去查询数据
select * from(select * from emp where deptno=10) newtable
where sal>2000
-- 查询部门人数超过3的详细信息
-- 第一种
select * from(
select
d.deptno ,dname,loc,count(empno) as countemp
from
dept d
left join
emp e
on
e.deptno = d.deptno
group by d.deptno,dname,loc)newtable
where countemp>3
-- with关键字
-- 第二种
with countemptable as(
select
d.deptno ,dname,loc,count(empno) as countemp
from
dept d
left join
emp e
on
e.deptno = d.deptno
group by d.deptno,dname,loc)
select * from countemptable where countemp>3
-- 创建视图
create or replace view empcountview
as
select
d.deptno ,dname,loc,count(empno) as countemp
from
dept d
left join
emp e
on
e.deptno = d.deptno
group by d.deptno,dname,loc
-- 查询视图
select * from empcountview
-- 直接查询视图筛选数据
-- 第三种
select * from empcountview where countemp>3
-- 创建一个视图保存部门20的员工详细信息包含部门信息
create or replace view dept20view
as
select dname,loc,e.*
from
emp e
left join
dept d
on e.deptno = d.deptno
select * from dept20view
select * from dept20view where deptno=20
-- 原表数据更改视图随之更改
-- 查询语法
select 列
from 表
inner left join
表
on 条件
where 条件过滤
group by 列
having 过滤分组函数
order by 列 asc desc
limit start,length
-- 查询部门是10的员工人数
select d.deptno,loc,dname,count(empno) from emp e inner join dept d on e.deptno=d.deptno where d.deptno=10
-- 一共多少条数据
select count(empno) as countrow from emp
521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
