数据库之查询语句

查询语句

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;

 

需求 如果 总分小于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;

观光代码 忘了他吧

 

 

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中避免这个问题

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

为何?

 

2.何为聚合函数?

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

有哪些集合函数?

sum

count

avg

max/min

简单测试

 

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

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

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

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

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

理所当然的想到使用where来进行筛选 select from

select dept from emp where avg(salary) > 5000;

error: Invalid use of group function

报错原理解析

 

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的 岗位名称和平均薪资

查询 部门人数少于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 可用于分页

分页原理

 

正则表达式匹配

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

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专门用于过滤关联关系

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.全外链接查询 在内连接的基础上 增加左边表有而右边表没有的记录 增加右边表有而左边表没有的记录

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

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

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