数据库重点知识-表操作(CRUD)

1.数据增删改
2.单表查询
3.正则表达式
4.多表查询
​ 笛卡尔积
​ 内连接
​ 外链接
​ 子查询

一.数据的增删改

为什么不说查 因为查询语句 有很多细节 所以先从简单的说起

添加数据:

1.insert into table_name[column,[column2.....]] values(value1...)[,(value1...)];

如果表名后没制定字段 则插入的数据必须和表格结构顺序匹配 每一个字段都必须给出值 当然 数据类型也必须一致

如果在表名后制定了字段名 可以选择性的插入指定字段 插入的数据必须和前面声明的字段匹配

2.insert into table_name select *from another_table;

该方式用于插入另一个表的查询结果

要求查询出的结构必须与当前表结构相同

上述方法 into 都可以省略

删除数据:

delete from table_name [where condition];

没有条件则删除所有数据 逐条删除

truncate table table_name;

要删除全部数据最好的方式是重建表 他是直接删除文件

修改数据:

update table_name set column = value[,column2 = value2] [where condition]

可以一次性修改多个字段

没有条件则全部修改

二.单表查询

1.完整的查询语句语法

select  distinct (* or 字段名 or 四则运算) from 表名
where 条件
group by   字段
having  条件
order by  排序
limit   控制从哪里开始显示以及 显示几条

以上是书写顺序,必须按照这个顺序来书写sql语句

书写顺序代表执行顺序吗?

并不是!

2.关键字的执行顺序

注意：书写顺序错误将报错

伪代码:
  
第一步找到对应的文件
def from(name):
    open (file)
    pass

第二步 读取并筛选数据
def where(条件):
读取每一行数据 判断是否满足条件
  for line in file:
        if xxxxxx

def group():
  将数据 按照某个字段进行分组
  pass

def having():
  对分组后的数据进行筛选
  pass

def distinct():
  对数据进行去重处理
    pass

def order():
    对数据进行排序
    pass

def limit()
    选取一部分数据
    pass

def select()
  from()
  wher()
  group()
  having()
  distinct()
  order()
  limit()
    return data;

3.简单查询

上述的关键字大多数是可选的 必选的是哪些?

select distinct from 表名;

distinct是可选的 用于取出重复记录

只有当显示的所有列的数据都重复时才去除

当字段名太长获取不容易理解时 可使用as来取别名

1.*表示通配符 显示所有字段

2.可以指定任意个字段名

3.可以对字段的数据进行四则运算

4.聚合函数 接下来讲

案例:

准备数据
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);


查看所有数据
select *from stu;
查看 英语成绩
select name,english from stu;
查看所有人的数学成绩 并去除项姓名相同的数据
select distinct name,mathenglish from stu;
统计每个人的总分
select name,english+math 总分 from stu;
为每个为的英语加10分显示
select name,english+10 总分 from stu;


调整显示的格式:
需要 在字段的数据前加上字段名: 
name:赵云   english:90  math:30

如果是python怎么实现?   字符串拼接
mysql中的字符串拼接函数  concat()

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

取别名时  可以省略 as
select concat("姓名:",name) name,concat("英语:",english) english,concat("数学:",math) math from stu;

concat_ws()  类似python的join



需求 如果 总分小于150  在名字后面加上shit 大于等于 加上nice
如果 那就意味有逻辑判断 (了解)
select 
(case 
when english + math > 120 then
concat("name"," nice")
when english + math <= 130 then
concat("name"," shit")
end),
english,math from stu;

select if(english+math > 120,concat(name,"nice"),concat(name,"shit")) from stu;

4.where关键字

支持的表达式

练习:

1.查询英语分数在 80－90之间的同学。

2.查询数学分数为89,90,91的同学。

3.查询所有姓李的学生成绩。

4.查询所有姓名带有李的学生成绩。

5.查询数学分>80并且英语分>80的同学。

6.查询数学分<60并且英语分<60的同学。

where 是如何找到你要的数据的?

where语句条件在没有索引的情况下 是挨个遍历判断 效率非常低

如果有索引 则可以忽略掉大多数无用数据

5.group by

分组查询

准备数据

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);

分组,需要一个字段作为分组依据,把一个整体分割为不同部分

作用,用于将数据以组为单位进行统计,例如上述表中 统计每个部门的员工数量

1.将数据按照部门分组

select *from emp group by dept;

注意:mysql5.6 默认分组后可以查看每个分组的第一条记录的所有字段 但这个数据有意义吗? 没有

我们可以添加 only_full_group_by 到sql_mode中避免这个问题 5.7后默认

分组后:就只能查看分组的字段 以及对组内记录的统计结果 (通过聚合函数来统计)

为何?

2.何为聚合函数?

将多个数据进行计算 并得到一个结果 称为聚合

有哪些集合函数?

sum
count
avg
max/min

简单练习:

1.查询每个部门有几个人

2.计算每个部门的平均工资

3.计算每个岗位的平均工资

4.计算每个部门每个岗位的平均工资

5.查询平均工资大于5000的部门

#理所当然的想到使用where来进行筛选  
select dept from emp where avg(salary) > 5000;
error: invalid use of group function

#报错原理解析
where 条件中不允许使用分组函数

#解决方案:
group_concat函数
用于拼接组内记录的某个字段  拼接为一个字符串

#用法:
select dept,group_concat(name) from emp group by dept;

#注意事项：
分组后，分组下的内容被隐藏 只能查询分组字段

6.having

用于对分组后的数据进行筛选

作用与where相同 用于过滤

不同点在于:where 是从文件读取数据时的过滤条件

这导致了where中不能使用聚合函数

因为数据读取工作都没有完成 不可能统计出数据

having是在分组后进行的过滤条件

分组的执行顺序是在where之后

此时数据已经全部读取了

所以可以使用聚合函数来进行统计

为什么 不分组的时候在select 后面可以使用聚合函数呢?

select sum(salary) from where;

因为你where比select 后面的字段筛选更早执行 此时数据全都已经读取了 所以可以进行统计

练习:

#查询 岗位平均薪资高于6000的 岗位名称和平均薪资
select dept,avg(salary) from emp group by dept having avg(salary) > 6000 ;
#查询 部门人数少于3的 部门名称 人员名称 人员个数
select dept,group_concat(name),count(*) from emp group by dept having count(name) < 3;

7.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排序

8.limit

用于限制显示的记录数

limit [start,] count;

start 开始位置

count 显示条数

不指定start 时 则从第一条开始显示

查看前三人
select *from emp limit 3;

查看工资最高的那个人信息
select *from emp order by salary desc limit 1;

指定起始位置
查看id为3-6的人
select *from emp  limit 2,4; 


limit 可用于分页
分页原理:先查询总数据条数 设为a
确定每页数量b
总页数为c = a / b 如果除不尽则需要加1  例如 10 / 3 正确页数为4
查询语句的起始位置为s = 当前页数d 减去1 乘以每页数量
即  s =  (d - 1) * b
语句为:select*from table_name limit s,b

三 正则表达式匹配

正则表达式用于模糊查询,模糊查询已经讲过了

like 仅支持 % 和 _ 远没有正则表达式灵活

当然绝大多数情况下 like足够使用

#准备数据:
insert into emp values(1,"laowangba","男",26,1,"总监",5800);
insert into emp values(1,"laoliba","男",26,1,"总监",5800);
insert into emp values(1,"laocheng","男",26,1,"总监",5800);


#语法:
select *from table where name regexp "正则表达式";

四 多表查询

数据准备:

#不存在外键关联的两张表
#一张表示员工表 
#存在一些不正确的部门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.笛卡尔积查询:

是两张表相乘的结果,若左边有m条 右边有n条 查询结果为m*n条; 往往包含大量错误数据

  select *from dept,emp;  
  select *from dept,emp where dept.id = dept_id;  

2.链接查询

1.内连接查询 查询出两张表都有匹配关系的记录

select *from dept,emp where dept.id=emp.dept_id;  
 #where用于筛选数据,而在多多表查询中要筛选的是两边的关系 on用于过滤关联关系
 #而where单独做条件过滤，这样sql看起来可以更清晰明确，当然where依然可以代替on  

select *from dept join emp on dept.id=emp.dept_id;
inner可以省略

2.左外链接查询

左表中记录的无论是否有匹配关系都全部显示，右表中仅显示匹配成功的记录

select *from dept left join emp on dept.id=emp.dept_id;

3.右外链接查询

右表中记录的无论是否有匹配关系都全部显示，左表中仅显示匹配成功的记录

select *from dept right join emp on dept.id=emp.dept_id;

4.全外链接查询

无论是否匹配成功，两边表中的记录都要全部显示

##mysql 不支持
select *from dept full join emp on dept.id = emp.dept_id;  

#mysql中可以使用合并查询结果 在所有语句最后写分号
select *from dept left join emp on dept.id=emp.dept_id
union
select *from dept right join emp on dept.id=emp.dept_id;

union 只能用于字段数量相同的两个表 会自动去除重复的记录

union all 则保留所有记录

练习：查询1号部门的名称和其所有员工名称;

select dept.name 部门,emp.name 姓名 from dept join emp on dept.id=emp.dept_id where dept.id = 1;

多表查询的思路是 先将多张表的数据连在一起 在使用条件来过滤

五 子查询

什么是子查询?

当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)

什么时候用?

当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时,

例如:给出一个部门名称,需要获得该部门所有的员工信息

需要先确定部门的id,

然后才能通过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;
# id3不能写死 是上一个查询的结果 所以直接写在后面  加上括号就变成了子查询
select name from emp where dept_id = (select id from dept where  name = "财务");

in 关键字子查询

"查询平均年龄大于25的部门名称
子查询方式:
平均年龄大于25的部门id有哪些?
先要求出每个部门的平年龄! 筛选出平均年龄大于25的部门id
拿着部门id 去查询部门表查询"

select name from dept where id in  (select dept_id from emp group by dept_id having avg(age) > 25);


"多表查询方式:
先把数据拼接到一起 在加以筛选"

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.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(t_id) references tea(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";


'tea表中查得egon的 id 为1
关系表中查的 123和egon有关系
学生表中查得 123的名字为abc'