MYSQL语句查询关键字

目录

SQL语句查询关键字

select 
	指定要查询的字段信息
	select * 查询所有字段
	select 字段名字		查询指定字段名字
	select char_lengh(字段名)  对查询后的字段统计字符长度   也就是支持对查询后的字段做操作
	
from
	指定需要查询的表名   可以由上一个查询语句做这个查询表需要用括号括起来
	(查询语句) as 一个表名
	完整语法
select * from (select 字段 from 表名) as 表名;

SQL语句中关键字的执行顺序和编写顺序并不是一致的可能会错乱
	eg:
	select id,name from userinfo;
	我们先写的select在写的from 但是执行的时候是先执行的from在执行select

image

前期数据准备

create table emp(
	id int primary key auto_increment comment '员工id',
    name varchar(20) not null comment '员工姓名',
    gender enum('male','female') not null default 'male' comment '员工姓名',
    age int(3) unsigned not null default 28 comment '员工年龄',
    hire_date date not null comment '入职时间',
    post varchar(50) comment '部门名称',
    post_comment varchar(100) comment '部门详情',
    salary float(15,2) comment '员工薪资',
    office int comment '办公室编号',
    depart_id int comment '部门对应的id'
);

# 插入记录
# 三个部门：教学，销售，运营
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);

image

插入数据

image

查看表详情以及表数据

image

编写SQL语句的小技巧

  1. 怎对select后面的字段名可以先用*占位往后写 最后在回来修改
  2. 在实际的应用中select后面很少直接写* 因为*表示所有当表中字段和数据都特别多的情况下非常浪费数据库资源
  3. SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补
  4. 要善于查看帮助 help
  5. 也要多尝试几次，在小白阶段并不是一下就能完整写出
  6. 最好是根据题目意思，把复杂的题目拆分成简单小的题目
    一步一步去解决。

查询关键字之where筛选

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

    select * from emp where id>=3 and id<=6;

select * from emp where id between 3 and 6;

select * from emp where id in(3,4,5,6);

select * from emp where not id not in(3,4,5,6);


![image](https://img2022.cnblogs.com/blog/2987368/202211/2987368-20221126174305610-1634134489.png)


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

```mysql
 select * from emp where salary in(20000,18000,17000);  # 成员运算
 
 select * from emp where salary=20000 or salary=18000 or salary=17000;

image

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

    select * from emp where id<3 or id>6;

select * from emp where id not in(3,4,5,6);

    image

模糊查询

条件不够精确的查询 称之为 模糊查询

关键字 like

匹配字符

  • %匹配任意个数的任意字符
  • _匹配任意一个字符
  1. 查询员工姓名中包含字母o的员工姓名与薪资
 select * from emp where name like '%o%';

image

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

     select name,salary from emp where name like '____';
 
  select name,salary from emp where char_length(name) =4;

    image

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

    select name,post from emp where post_comment=null;		# 错误的
select name,post from emp where post_comment='null';   # 错误的
 select name,post from emp where post_comment is null;

    image

查询关键字之group by分组

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

  • 将班级按照性别分组
  • 将全国人民按照民族分组
  • 将全世界的人按照肤色分组

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

  • 每个班级的男女比例
  • 每个民族的总占比
  • 每个部门的平均薪资

聚合函数：专门用于分组之后的数据统计

  • max 最大值
  • min 最小值
  • sum 求和
  • avg 平均值
  • count 计数

  1. 将员工数据按照部门分组

    select * from emp group by post

    image

    mysql5.6之前默认不会报错，因为没有设置严格模式

    mysql5.7及8.0默认设置了严格模式所有都会报错

    原因是分组后 select后面默认只能直接填写分组的依据不能再写其他字段

image

select post from emp group by post;

分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段

  1. 获取每个部门的最高工资
    要不要分组我们完全可以从题目的需求中分析出来尤其是出现关键字 每个 平均
    针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略

    select post,max(salary) from emp group by post; 
select post as '部门',max(salary) as '最高薪资' from emp group by post;

    image

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

    select post,max(salary),min(salary),avg(salary),sum(salary) from emp group by post;

    image

  3. 统计每个部门的人数

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

    image

  4. 统计每个部门的部门名称以及部门下的员工姓名
    '''分组以外的字段无法直接填写 需要借助于方法'''

    select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|') from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;

    image

查询关键字之having过滤

having于where本质是一样的 都是用来对数据做筛选

区别

  • where用在分组之前进行筛选
  • having用在分组之后进行筛选

  1. 统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
    '''
    稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
    每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
    '''步骤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;

    image

查询关键字之distinct去重

去重有一个必须的条件也是很容易被忽略的条件

数据必须一模一样才可以去重

如果使用了去重，就不能再于其他字段关联例如

select name distinct age from emp;

image

select distinct id,age from emp;#关键字针对的是多个字段组合的结果

select distinct age from emp;

image

查询关键字之order by排序

  1. 单个字段排序

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

# 也可以进行分组后再排序,但是排序字段必须是分组字段，或者是使用group_concat(字段名)获取排序
select age from emp group by age order by age;
select age,group_concat(name) as name from emp group by age order by name;# 起别名后可以按照别名排序
select age,group_concat(name) as name from emp group by age order by group_concat(name);

    image

    image

    image

  2. 多个字段排序

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

    image

    统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

    • .先筛选出所有年龄大于10岁的员工

        	select * from emp where age > 10;

    • 再对他们按照部门分组统计平均薪资

      select post,avg(salary) from emp where age > 10 group by post;

    • 针对分组的结果做二次筛选

      select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;

    • 最后按照指定字段排序

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

      image

查询关键字之limit分页

当表中数据特别多的情况下 我们很少会一次性获取所有数据
很多网站也都做了分页处理 一次只能看一点点

select * from emp limit 5;# 从头开始只展示5条
select * from emp limit 5,5;# 从第五条往后展示5条
#查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;

image

查询关键字regexp正则表达式

sql语句模糊匹配如果用不习惯 也可以字节写正则批量查询

select * from emp where name regexp '^j.*?(n|y)$'#报错 #意思是以j开头向后匹配多次多个字符以n或y结尾的字符串
MySQL使用Henry Spencer实现的正则表达式，旨在与POSIX 1003.2保持一致

POSIX正则表达式不支持将问号?用作星形的非贪婪（懒惰）修饰符以及像PCRE（Perl Compatible Regular Expressions）这样的量词。这意味着你不能使用+?和*?
select * from emp where name regexp '^j.*?(n|y)$' #正确

image

多表查询的思路

# 表数据的准备
create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

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

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

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);


select * from emp,dep;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

作业

  1. 查询岗位名以及岗位包含的所有员工名字

    select post,group_concat(name) from emp group by post;

    image

  2. 查询岗位名以及各岗位内包含的员工个数

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

    image

  3. 查询公司内男员工和女员工的个数

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

    image

  4. 查询岗位名以及各岗位的平均薪资

    select post,avg(salary) from emp group by post;

    image

  5. 查询岗位名以及各岗位的最高薪资

    select post,max(salary) from emp group by post;

    image

  6. 查询岗位名以及各岗位的最低薪资

    select post,min(salary) from emp group by post;

    image

  7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

    select gender,avg(salary) from emp group by gender;

    image

  8. 统计各部门年龄在30岁以上的员工平均工资

    select post,avg(salary) from emp where age>30 group by post;

    image

posted @ 2022-11-26 17:48  clever-cat  阅读(317)  评论(0)  编辑  收藏  举报