数据库4 待修

1.数据的增删改
增加:
insert[into] 表名[(可选字段名)] values(一堆值1),(一堆值2)...
into 可以省略
表明后的字段可选
如果写了 后面的values中的值必须与表明后的字段一一对应
如果没写 后面的values中的值必须与表的所有字段一一对应
values后面可以给多组值 用逗号隔开

删除:
delete from 表明[where 条件]
条件不写 是删除所有记录 是一行一行删除 注意自增id 不会归零
truncate 重建表 先记录表结构 删除整个表再重新建出来表 自增id 会归零

更新数据
update 表明 set 字段名=值[,字段2=值2],[where 条件]
可以一次性修改多个字段的值用逗号隔开
条件如果不写 修改所有记录



2.单表查询
不带关键字的查询
select{1.*|2.字段名|3.四则运算|4.聚合函数} from 表名[where 条件]=
1.*表示查询所有字段
2.可以手动要查询所有的字段
3.字段的值可以进行加减乘除运算
4.聚合函数,用于统计
where 是可选的




select的完整语法:
select 的完整语法
数据准备
create table stu(id int primary key auto_increment,name
char(10),math float,english float);
insert into stu values(null,"赵云",90,30);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"大乔",10,70);
insert into stu values(null,"李清照",100,100);
insert into stu values(null,"铁拐李",20,55);
insert into stu values(null,"小李子",20,55);




关键字的作用:
1. from 用于打开文件

2. distinct 去除重复数据 所有数据全部重复才叫重复
select distinct {1.*|2.字段名|3.四则运算|4.聚合函数} from 表名[where 条件]
例子:select distinct name from stu;

3. where 在逐行读取数据时的一个判断条件
BETWEEN...AND... 显示在某一区间的值
IN(set) 显示在in列表中的值 例:in(100,200)
like"张%或_" 模糊查询 %代表零个或任意字符,_代表一个字符,例first_name like"_a";
IS NULL 判断是否为空
and 多个条件同时成立
or 多个条件任一成立
not 不成立 例:where not(salary>100);

4. group by 对数据分组
select 段名(可以是多个) from 表名 group by 段名(可以是多个);#把表按照xx分类

5. having 对分组后的数据进行过滤
作用与where相同 用于过滤;
不同点:where是从文件读取数据时的过滤条件,这也导致了where中不能使用聚合函数
因为数据读取工作都没有完成 不可能统计出数据
having 实在分组后进行的过滤条件 分组的执行顺序是在where之后 此时数据已经完全读取了
所以可以使用聚合函数来进行统计

6. order by 对结果排序
select * from emp order by salary; 默认为升序
select * from emp order by salary asc; 指定为升序
select * from emp order by salary desc;指定为降序
select * from emp order by salary,id desc; ⼯工资相同时按照id排序

7. limit 指定获取数据条数
⽤用于限制显示的记录数
limit [start,] count;
start 开始位置
count 显示条数
不不指定start 时 则从第⼀一条开始显示

8. conca:mysql中的字符串串拼接函数
例子:1.select concat("姓名:",name),
concat("英语:",english),
concat("数 学:",math) from stu;

2.select concat("姓名:",name) name,
concat("英语:",english) english,
concat("数学:",math) math from stu;




完整的select 语句 语法 :
select [distinct] * from 表名
[where
group by
having
order by
limit
]
注意 在书写时 必须按照这个顺序来写 但是书写顺序不代表执行顺序


数据库伪代码:
def from():
打开文件
def where():
对读取的数据进行过滤
def group_by():
对数据分组
def having():
对分组后的数据进行过滤
def distinct():
去除重复数据
def order by():
对结果排序
def limit():
指定获取数据条数


select 语句的执行顺序 *****
def select(sql):
data = from()
data = where(data)
data = group by(data)
data = having(data)
data = distinct(data)
data = orderby(data)
data = limit(data)
return data;


举例:
简单查询
指定显示格式:
concat()函数用于拼接字符串
select
(
case
when english + math > 120 then
concat(name," nice")
when english + math <= 130 then
concat(name," shit")
end
) ,english,math from stu; *完全不重要

where 条件
create table emp (id int,name char(10),sex char,dept char(10),job
char(10),salary double);
insert into emp values
(1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);



group by 分组查询:
什么是分组
把一个整体 分割为多个部分

为什么分组
在数据库中分组为了统计

分组后 组里的详细记录被隐藏起来了 不能直接查看
dept 一分组 编程三条记录 每个组中却包含多条记录 没有办法显示
一定要显示的话
可以使用group by dept(字段名)
可以将多个值拼接成一个字符串

注意:1.只有出现在group by 后面的字段 才可以被显示 其他都被隐藏了
2.聚合函数不能写在where后面 where最先执行 它的作用硬盘读取数据并过滤
以为数据还没有读完 此时不能进行统计

什么样的字段适合用于分组
重复性高的字段
请查询每种性别的平均工资
性别分组
请查询每种岗位的平均工资
岗位分组
"每"后面的就是分组的依据

select dept from emp group by dept;
(所要查看的数据必须出现在group by后 (即from前与group by一致))
select dept,group_concat(name) from emp group by dept;

了解:
在mysql 5.6中 分组后会默认显示 每组的第一条记录
5.7中不显示 是因为5.7中 sql_mode中不显示
group by 后面可以有多个分组与一句 会按照顺序执行


聚合函数:
将多个数据进行计算 并得到一个结果 称之为聚合
有哪些聚合函数?
sum
count
avg
max
min
简单测试



order by 排序用的
asc 表示升序 是默认的
desc 表示降序
by 后面可以有多个排序依据


limit 限制显示条数
limit a,b
limit 1,5
从1开始 不包含1 取5条

分页查询:
每页显示3条 用有10条数据
if 10%3==0:
10/3
else:
10/3+1
总页数4

第一页
select *from emp limit(0,3)
第二页
select *from emp limit(3,3)
第三页
select *from emp limit(6,3)
起始位置的算法
页数-1*条数
1 - 1 = 0 * 3 = 0
2 - 1 = 1 * 3 = 3



3.正则表达式
由于like 只能使用% 和_ 不塌灵活
可以将like转换为regexp 来使用正则表达式



4.多表查询
数据准备:
不存在外键关联的两张表
一张表示员⼯表
存在⼀些不不存在的部⻔id
create table emp (id int,name char(10),sex char,dept_id int);
insert emp values(1,"⼤大⻩黄","m",1);
insert emp values(2,"⽼老老 王","m",2);
insert emp values(3,"⽼老老 李李","w",30);

一张表示部门表
存在一些没有员⼯的的部门
create table dept (id int,name char(10));
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"⾏行行政");



1.笛卡儿积查询
select *from 表一,表n ;
查询结果是
将坐标中的每条记录 与右表中的每条记录都关联一遍
因为 它不知道什么样的对应关系是正确的 只能帮你都对一遍
a表有m条记录 b表有n条记录
需要自己筛选出正确结果的关联关系
select *from emp,dept where emp.dept_id=dept.id;

2.内连接查询 就是笛卡尔积查询
select *from emp[inner] join dept;
select *from emp inner join dept where emp.dept_id=dept.id;



on 关键字:
where 都是用于条件过滤 没有本质区别
在单表查询中where的作用是筛选过滤条件
在多表中 where 连接多表 满足条件就连接 不满足就不连接
为了区分是单表还是多表 从新设立一个名字on
只要是连接多表的条件 就用on

3.左外连接查询
select *from emp left join dept where emp .dept_id=dept.id;(是错误的)
select *from emp left join dept on emp .dept_id=dept.id;
左表全部显示 右表只显示匹配上的

4.右表连接查询
select *from emp right join dept where emp .dept_id=dept.id;(是错误的)
select *from emp right join dept on emp .dept_id=dept.id;
右表全部显示 左表只显示匹配上的

内和外的理解 内指的是匹配上的数据 外指的是没有匹配上的数据

5.全外连接
select *from emp full join dept on emp.dapt_id;(mysql不支持)
union 合并查询结果
select *from emp left join dept on emp .dept_id=dept.id;
union
select *from emp right join dept on emp .dept_id=dept.id;

union 去除重复数据
union all 不会去除重复数据
















5.子查询
当一个查询的结果是另一个查询的条件时 这个查询称之为子查询(内层查询)
什么时候使用子查询
当一次查询无法得到想要的结果时 需要多次查询
解决问题的方式是把一个复杂的问题拆分为若干个简单的问题
如何使用?
首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可



解决问题的思路:
给你部门名称
#查到部门有哪些人
1.查到部门id
2.拿着id取员工表查询

案例分析:
准备数据:
create table emp (id int,name char(10),
sex char,age int,dept_id int,job char(10),salary double);
insert into emp values (1,"刘备","男",26,1,"总监",5800),
(2,"张⻜飞","男",24,1,"员⼯工",3000),
(3,"关⽻羽","男",30,1,"员⼯工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员⼯工",5000),
(6,"⼩小乔","⼥女女",31,2,"员⼯工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司⻢马懿","男",24,3,"员⼯工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"⾏行行政"),(3,"财务");

需求:财务部有哪些: 数据在两张表中 可以使⽤用链接查询
select emp.name from emp inner join dept on dept.id = emp.dept_id where dept .name = "财务";
⼦子查询⽅方式: 数据在两张表中,先查询那张?
emp? 不⾏ 不知道部门名 查dept
第一步 需要知道财务部的id select id from dept where name = "财务";
第二步 ⽤查询的到的id作为判断条件查询emp select name from emp where dept_id = 3;
3不能写死 是上⼀一个查询的结果 所以直接写在后⾯面 加上括号就变成了了⼦子查询
select name from emp where dept_id = (select id from dept where name = "财务");

多表查询⽅方式:
先把数据拼接到⼀起 在加以筛选
select dept.name from emp inner join dept on emp.dept_id = dept.id group by dept.name having avg(age) >25;

exists关键字⼦查询
exists 后跟⼦查询 子查询有结果是为True 没有结果时为False
为true时外层执⾏为false外层不执⾏
select *from emp where exists (select *from emp where salary > 1000);
查看exists 的返回结果: 只有 0 和 1
select (exists (select *from emp where salary > 10000));
一个查询结果也是一个表 既然是表就能链接起来

综合练习:
查询每个部⻔⼯资最高的员⼯信息
先查询每个部⻔的最⾼高⼯资
将查询结果与员⼯表联合起来
在加条件判断部⻔门id相同并且
最⾼高⼯工资相同 则显示
select *from emp inner join
(select dept_id,max(salary) m from emp group by dept_id)
t2 on emp.dept_id = t2.dept_id where emp.dept_id = t2.dept_id and emp.salary = t2.m;



扩展:
举例:
三表联查
create table stu(id int primary key auto_increment,name char(10));
create table tea(id int primary key auto_increment,name char(10));

create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(s_id) references stu(id));
insert into stu values(null,"张三"),(null,"李李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);

egon老师教过哪些⼈?
select *from stu join tea join tsr on stu.id = tsr.s_id and tea.id = tsr.t_id where tea.name = "egon";
原文地址:https://www.cnblogs.com/yanhui1995/p/10003453.html