MySQL数据库:7、SQL常用查询语句

时间:2022-11-25 22:05:30

一、SQL语句常用查询方法

前期数据准备

​ 为了更加直观的展示、演示SQL语句查询关键字,需导入下列表格与记录(数据)

​ 模拟公司,导入创建公司员工表,表内包含:ID、姓名、年龄、工作时间、岗位

创建人员表格:

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  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
);

MySQL数据库:7、SQL常用查询语句

生成人员信息:

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

MySQL数据库:7、SQL常用查询语句

1、基本查询

  • 关键字:select

  • 功能:基本、最常用的查询方法,可以通过关键字查询表内所有或指定的数据

select : 指定需要查询的字段信息
	select *					 查询所有字段
	select 字段名		 		  查询指定字段记录
	select 方法(指定的字段)	  查询处理后的字段记录
    
from 
	指定需要查询的表信息
	from 库名.表名
	from 表名   
   
'''
注意事项:
	1、SQL语句中关键字的执行顺序和编写顺序并不是一致的
		eg:
			select * from t1;
      	我们先写的是select,执行的顺序却是from t1 ——>  select *
	2、对于执行的顺序我们不用过多在意,只需要注意功能,熟练之后会编写的很自然
'''

2、编写SQL语句的小技巧

  • 对于查询用法,针对‘select’后面的字段我们可以先使用‘*’占位,然后往后面写,写到需要查询的字段时回来补全

  • 在实际应用中‘select’后面很少直接写‘*’ , 因为星号表示所有,在当前表中数据量非常庞大时会非常浪费数据库资源

  • SQL语句的编写类似于代码的编写,不是一蹴而就的,也需要缝缝补补

  • 在查询的字段后使用‘as’的方式可以用来修改展示的字段名,不会影响表的结构,只用来当前打印下的展示

MySQL数据库:7、SQL常用查询语句

3、查询之where筛选

3、1.功能介绍

  • 关键字:where
    • 功能:SQL语句中最常用的关键字,用于筛选数据,支持成员运算符,逻辑运算符号、身份运算符、模糊查询
  • 模糊查询:
    • 功能:当条件不足时可使用模糊查询,特征搭配模糊查询字符
    • 关键词:like
字符 方法 功能
% 模糊查询 搭配字符前后,匹配任意字符
_ 模糊查询 搭配字符前后,匹配单个字符

3、2.实际应用

  • 1.查询id大于等于3,小于等于6的数据
1、方式一:
    select * from emp where id>3 and id<=6;
    
2、方式二: 搭配关键词:between
   	select * from emp where id between 3 and 6;

MySQL数据库:7、SQL常用查询语句

MySQL数据库:7、SQL常用查询语句

  • 2.查询薪资是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);

MySQL数据库:7、SQL常用查询语句

  • 3.查询id小于三和大于6的数据
1、方式一:
	select * from emp where id<3 or id>6;

2、方式二:
	select * from emp where id not between 3 and 6;

MySQL数据库:7、SQL常用查询语句

  • 4.查询姓名中包含o的员工姓名和薪资

​ 条件不够时通常使用模糊查询,搭配查询字符

select * from emp where name like %o%;

MySQL数据库:7、SQL常用查询语句

  • 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
1、方式一:
	selsect * from emp where name like '____';
    
2、方式二:
	select * from emp where char_length(name)=4;

MySQL数据库:7、SQL常用查询语句

  • 6.查询岗位描述为空的员工名与岗位名---针对null不能用等号,只能用is
select * from emp where post_comment is null;

MySQL数据库:7、SQL常用查询语句

4、查询之group by分组

4、1.功能介绍

  • 关键词:group by
    • 功能:可以将指定的条件进行分组,例如将多个相同部门的员工按部门分组
    • 可搭配聚合函数:
      • max():取最大值
      • min():取最小值
      • sum():求和
      • avg():平均值
      • count():计数

4、2.实际应用

  • 1.统计表内公司的总类

​ 在使用,group by 的时候,可能会出现以下这种用法

MySQL数据库:7、SQL常用查询语句

​ 这种方法在MySQL5.5之前,并不会报错,但是打印的数据并不利于观看,因为它直接将部门的某一行打印出来,我们应该将需要打印的字符名放在selsct后:

​ select post from group by post;

MySQL数据库:7、SQL常用查询语句

​ 在MySQL5.5之后,使用这种方法将会报错,可以通过在配置内添加严格模式来改正这种方法

# 将下列代码拷贝至my.ini文件的MySQLd下,重启系统环境中的MySQL服务端
	sql_mode='strict_trans_tables,only_full_group_by'
  • 2.获取每个部门的最高工资
推导流程:
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资
	select post,max(salary) from emp group by post;

MySQL数据库:7、SQL常用查询语句

  • 3.一次性获取部门薪资的所有数据,最高、最低、平均、薪资总和
推导流程:
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资的综合信息
	select post as '部门' ,
		max(salary) as '最高薪资',
		min(salary) as '最低薪资',
		avg(salary) as '平均薪资',
		sum(salary) as '薪资总和'
       from emp group by post;

MySQL数据库:7、SQL常用查询语句

  • 4.统计每个部门的人数
推导流程:
	# 1、先获取部门信息
	select post from emp group by post;
	# 2、获取部门下人员工资的综合信息
	select post as '部门' ,
		count(id) as '部门人数总合'
       from emp group by post;

MySQL数据库:7、SQL常用查询语句

  • 5.统计每个部门的部门名称及员工姓名
    • 使用group by后,不可直接展示多条字段名,想要展示多条字段名只能使用方法的方式来展示
    • 关键词:group_concat()
      • 支持在后方阔内填入多条字段名,需要用逗号隔开,同时也支持数据展示自定义
1、打印部门名称及部门下人员姓名
select post,group_concat(name) from emp group by post;

2、打印部门名称,部门下人员姓名、年龄
select post,group_concat(name,age) from emp group by post;

3、自定义输出内容
select post,group_concat('姓名:',name,'年龄:',age) from emp group by post;

MySQL数据库:7、SQL常用查询语句

MySQL数据库:7、SQL常用查询语句

MySQL数据库:7、SQL常用查询语句

5、关查询之having过滤

5、1.功能介绍

  • 关键词:having
    • 功能:having的功能和where功能的本质是一样的,都是用来对数据进行筛选
      • 区别是having用来对数据进行二次筛选

5、2.实际应用

  • 1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
推导流程:
	# 1、筛选大于30的员工
	select * from emp where age > 30;
	# 2、对获取的信息进行部门分类
	select post from where age > 30 group by;
	# 3、对获取的信息取工资的平均值
	select post.vag(salary) from emp where age > 30 group bay;
	# 4、对平均工资进行过滤,保留大于10000的数据
	select post,vag(salary) from emp where age > 30 group by having vag(salary)>10000;

MySQL数据库:7、SQL常用查询语句

6、查询之distinct去重

6、1.功能介绍

  • 关键词:distinct

  • 功能:可以去除表中重复的数据,但是数据必须要一样才可以,也可以多列使用

    • 多列使用时,满足的条件是多个字段的组合发生重复才会去重

6、2.实际应用

  • 去除重复的年龄
1、单列使用
select distinct age from emp;

2、多列使用
select distinct name,age from emp;

MySQL数据库:7、SQL常用查询语句

MySQL数据库:7、SQL常用查询语句

7、查询之order by排序

7、1.功能介绍

  • 关键词:order by
  • 功能:在查看表时,可将指定的字段下的数据排序,支持多列排序(例如先按照年龄排序,再将相同年龄段的用户按照收入排序),默认为升序,可修改为降序
  • 升降序
    • 升序:
      • select * from 表名 order by 字段名;
      • select * from 表名 order by 字段名 asc;
    • 降序:
      • select * from 表名 order by 字段名 desc;

7、2.实际应用

  • 统计各部门年龄在10岁以上的员工平均工资,并且保留平均工资大于1000的部门,然后对平均工资进行排序
推导流程:
	# 1、先筛选出年龄大于10的人员平均工资
	select avg(salary) from emp where age>10;
	# 2、将各部门人员信息分开
	select post,avg(salary) from emp where age>10 group by post;
	# 3、进行二次筛选,保留平均工资大于1000的部门
	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);

MySQL数据库:7、SQL常用查询语句

8、查询之Limit分页

8、1.功能介绍

  • 关键词:limit
  • 功能:当我们再打开一个数据量较为庞大的表时,会占用很多内存,limit可以帮助我们将表分页,再后方阔内输入参数可控制表打开条数,也可支持打开的范围

8、2.实际应用

  • 常规用法
1、限制表打开的条数
select * from emp limit 5;

2、控制表打开的范围
select * from emp limit 5,5;

MySQL数据库:7、SQL常用查询语句

MySQL数据库:7、SQL常用查询语句

  • 查询工资最高的人的信息
推导流程:
	# 1、将表内所有人员的工资进行排序 设置为降序
	select * from empor order by salary desc limit 1;

MySQL数据库:7、SQL常用查询语句

9、查询之regexp正则

9、1.功能介绍

  • 关键词:regexp
  • 功能:可以按照指定正则条件,对表内的数据进行搜索,是模糊搜索的一种

9、2.实际应用

  • 搜索j开头n或y结尾的用户信息
select * from emp where name regexp '^j.*(y|n)$';

MySQL数据库:7、SQL常用查询语句

二、多表查询思路

数据准备

创建部门表:

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

MySQL数据库:7、SQL常用查询语句

1、实际应用

​ 以上两个表,类似于公司的员工信息,和公司部门表,两张表为关系型表,通过之前的学习,我们了解到想要查询数据可以通过‘select * from 表名’的方式进行查询,其实这个方法也可以一次性查询多张表,方法如下

select * from emp,dep;

MySQL数据库:7、SQL常用查询语句

​ 由上图可以看出,select 方法一次性读出了两张表,但是表的数据发生了错乱,字段发生了冲突由此我们可以通过指定表名的方式进行查询,避免发生错乱

select * from emp,dep where emp.dep_id=dep.id;

MySQL数据库:7、SQL常用查询语句