SQL语句查询关键字

SQL语句查询关键字

SQL语句查询关键字

本文数据来源，数据库来源db4

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,d
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

数据表格展示：

select 查询

指定需要查询的字段信息

select *    查询所有字段
select name  查 name 字段
select char_length(name)  支持对字段做处理

from

指定需要查询的表信息

from mysql.user
from t1

eg: select id ,name from userinfo

SQL语句中关键字的执行顺序和编写顺序并不是一直的，可能会错乱。我们先写的`select在写的from,但是执行的时候是先执行的from再执行select，所以对关键字的顺序可以不在意，但是重点在于关键字的功能

where 筛选

查询 id大于等于3，小于等于6的数据

1. select * from emp where id >=3 and id <=6;  # 支持逻辑运算符
2. select * from emp where id >=3 and id <=6;

查询薪资是20000或者18000或者17000的数据

1. select * from emp where salary=20000 or salary=18000 or salary=17000;  # 支持逻辑运算符
2. select * from emp where salary in (20000,18000,17000);  # 支持成员运算

查询id小于3大于6的数据

1.select * from emp where id<3 or id>6;  # 支持逻辑运算符
2.select * from emp where id not between 3 and 6;

查询员工姓名中包含字母o的员工姓名与薪资

模糊查询：条件不够精确的查询

关键字：like 符号：%：匹配任意个数的任意字符，_:匹配单个个数的任意字符
```
eg:
	%o%  o jason open lol tomato rose loo
	%o   o  asdasdo  adso loo

eg: 
	_o_   o  aoc wob iok
	o_    o  ok oi ol
```
```
1.select * from emp where name like '%o%';
```

查询员工姓名是由四个字符组成的员工姓名与其薪资

1.select * from emp where name like '____';
2.select * from emp where char_length(name)=4;

6.查询岗位描述为空的员工名与岗位名针对null不能用等号，只能用is

1.select * from emp where post_comment=NULL; # 报错
2.select * from emp where post_comment is NULL; # 正常查询

group by 分组

分组：按照指定的条件将单个单个的数据组成一个个整体

分组的目的：为了更好的统计相关数据

eg：如：将班级学生按照性别分组，统计男女比例；将全国人民按照民族分组，统计民族的比例；将全世界的人按照肤色分组，统计全球的肤色比例等。

聚合函数：专门用于分组之后的数据统计，聚合函数有max / min / sum /avg /count

将员工数据按照部门分组

select * from emp group by post;

MySQL5.6默认不会报错
	set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
	原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
		select post from emp group by post;
		select age from emp group by age;		
	分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段

获取每个部门的最高工资

1.根据每个部门，需要先分组再获取最高工资
select post,max(salary) from emp group by post;
2.我们也可以修改字段名称，使用关键字 as (也可省略)
select post as '部门',max(salary) as '最高薪' from emp group by post;

一次获取部门薪资相关统计

 select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '总共月支出' from emp group by post;

统计每个部门的人数

 select post,count(id) from emp group by post;

统计每个部门的部门名称以及部门下的员工姓名

有由题目可知，需要在分组之外的字段获取员工名字，此时我们只能借助于方法group_concat

方式一：
    select post,name from emp group by post;

方式2：
    select post,group_concat(name) from emp group by post;
    select post,group_concat(name,age) from emp group by post;
    select post,group_concat(name,'|',age) from emp group by post;
    select post,group_concat('AB_',name,'_ING') from emp group by post;

having 过滤

having和where的本质都是对数据做筛选的，where是分组之前首次筛选，只不过having是在where分组之后做二次筛选

1.统计各部门年龄在30岁以上的员工平均工资并且保留大于10000的数据

步骤1:先筛选出所有年龄大于30岁的员工数据
     select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
     select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
     select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

distinct去重

distinct：必备条件是数据必须是一模一样的，很容易被忽略掉的条件


select distinct id,age from emp;
select distinct age from emp;

上述这两种都不能去重，关键字是针对的多个字段组合的结果，如下面这种
select distinct age,post from emp;

order by 排序

单个字段排序

select * from emp order by age;  # 默认升序
select * from emp order by age asc; # asc 升序
select * from emp order by age desc;  # desc 降序

多个字段排序

select * from emp order by age,salary desc;
   先按照年龄升序排列，相同的年龄再按照薪资降序排序

eg:
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
1.先筛选出所有年龄大于10岁的员工
  	select * from emp where age > 10;
2.再对他们按照部门分组统计平均薪资
  	select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
  	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
 	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
 	
 """
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""

limit 分页

对表中数据一次性获取想要所有数据，网页上也做了分页处理，一般是页数展示给客户观览

select * from emp limit 5;  # 直接限制展示的条数
select * from emp limit 5,5;  # 从第5开始往后读取5条

查询工资最高的员工详情。首先我们可以会出错的点，我们想着用聚合函数来分组获取工资最高的，按照下面第一方式，我们拿到的只有薪资的最高值，拿不到最高薪的详细信息。

select post,max(salary) from emp group by post; # 这种是错误的

select * from emp order by salary desc limit 1; # 对薪资降序取第一条

regexp 正则表达式

SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
	select * from emp where name regexp '^j.*?(n|y)$';

编写SQL语句的小技巧

针对select 后面的字段名可以先用 * 占位符往后写，最后再回来修改，但是实际上我们不写*，因为 *号有自己的定义，在MySQL里面代表是所有,当表中字段和数据都特别多的情况下非常浪费数据库资源的。编写SQL语句也是要反反复复的修修补补，不能一蹴而就的。

在MySQL中也有很多内置方法，我们可以通过查看手册学习，使用help+ 方法名

小知识点补充

1.concat 与 concat_ws

concat

用于分组之前的字段拼接操作

select concat(name,'$',sex) from work;

concat_ws

拼接多个字段并且中间的连接符一致

select concat_ws('|',name,sex,age,dep_id)from work;   # 连接符可随意

2.exists

sql1 exists sql2
	sql2 有结果的情况下才会执行sql1,否则不执行sql1，返回空数据

3.表相关SQL补充

1.修改表名
  alter table 表名 rename 新表名
 	eg: alter table work rename worker;
2.添加字段
  alter table 表名 add 字段名  字段类型（数字）约束条件；
	eg:alter table worker add hobby varchar(32);
3.在已有的字段后面添加字段
  alter table 表名 add 字段名  字段类型（数据）约束条件 after 已有字段；
	eg:alter table worker add hobby varchar(32);
4.添加主键
  alter table 表名 add 字段名  字段类型（数据）约束条件 first;
5.修改字段名和类型
  alter table 表名 change 旧字段名 新字段名 字段类型（数字） 约束条件；
  alter table t1 change   name    username  char(32)；
  alter table t1 change pwd pwd varchar(32);  # 原本pwd int
	eg:alter table worker change name username ;
	   alter table worker change name username ;
	   
6.修改字段类型
  alter table 表名 modify 字段名 新字段类型（数字） 约束条件；
  alter table t1 modify   age  tinyint;  # 原本 age int
	eg: alter table worker change name username ;
7.删除字段
  alter table 表名 drop 字段名；
	eg: alter table worker change name username ;

多表查询的思路

数据表

create table department(
    id int primary key auto_increment,
    name varchar(32)
);

create table work(
    id int primary key auto_increment,
    name varchar(32),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into department values
(200,'技术部'),
(201,'人力资源'),
(202,'销售部'),
(203,'运营部'),
(205,'财务部');

insert into work(name,sex,age,dep_id) values
('kiki','female',18,200),
('json','female',48,201),
('kevin','male',18,201),
('rose','female',28,202),
('lili','male',18,203),
('jerry','female',18,204);

笛卡尔积

将两张表中的所有数据对应一边

 select * from work,department;

应该将有关系的数据对应才合理，基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来，我们需要在字段前面加上表名来指定，就可以将多张表合并到一起然后一次性获取更多的数据

select * from work,department where work.dep_id =department.id;

多表查询的两种方法

方式一：连表操作

内连接 inner join

以往的 select * from work,department where work.dep_id = department.id
	select * from work inner join department on work.dep_id = department.id
	下面的底层要比上述的SQL语句要简单，只连接连两个表中共有的数据展示

左连接 left join

select * from work left join department on work.dep_id = department.id
	以左表为基准，展示左表所有的数据，如果没有对应的数据则用NULL填充

右连接 right join

select * from work right join department on work.dep_id = department.id
	以右表为基准，展示右表所有的数据，如果没有对应的数据则用NULL填充

全连接 union

select * from work left join department on work.dep_id = department.id
union
select * from work right join department on work.dep_id = department.id;
	以左右表为基准，展示所有的数据，各自没有的全部NULL填充

学会连表操作就可以连接N多张表，将拼接之后起别名当成一张表再去与其他表拼接，再起别名当一张表与其他表拼接，依次往复即可

方式二：子查询

将一条SQL语句用括号括起来当作另外一条SQL语句的查询条件

eg: 求姓名是kimi的员工部门名称

分步操作：

步骤1：先根据kimi获取部门编号

select dep_id from work where name='rose';

步骤2：再根据部门编号获取部门名称

select name from department where id = 202;

整合

select name from department where id = (select dep_id from work where name = 'rose');

很多时候多表查询需要结合实际情况判断用哪一种，很多时候是相互配合使用

posted @ 2022-11-25 18:00 魔女宅急便阅读(173) 评论(0) 收藏举报

刷新页面返回顶部

kimi

SQL语句查询关键字

SQL语句查询关键字

select 查询

from

where 筛选

group by 分组

having 过滤

distinct去重

order by 排序

limit 分页

regexp 正则表达式

编写SQL语句的小技巧

小知识点补充

多表查询的思路

多表查询的两种方法

公告