MySQL基础:报错及作业讲解、SQL语句查询关键字、前期数据准备、编写SQL语句的小技巧、查询关键字之where筛选、查询关键字之group by分组、查询关键字之having过滤、查询关键字之distinct去重、多表查询的思路

目录

报错及作业讲解

报错
	1.粗心大意 单词拼写错误
	2.手忙脚乱 不会看报错思考错误的核心

作业讲解
	'''表与表中数据的关系可能会根据业务逻辑的不同发送改变 不是永远固定的'''
	服务器表与应用程序表
  	一台服务器可以运行多个应用程序
    一个应用程序也可以运行在多台服务器上
    多对多

  课程表与班级表
  	一门课程可以属于多个班级
    一个班级可以拥有多门课程
    多对多

  学生表与班级表
  	一名学生不能属于多个班级
    一个班级可以拥有多名学生
    一对多

  老师表与课程表
  	一名老师可以教授多门课程
    一门课程可以被多名老师教
    多对多

  书籍表与出版社表
  	一本书不可以多个出版社出版
    一个出版社可以出版多本书
    一对多
	ps:上述关系的确认并不是绝对
对于我们而言不单单要学会关系判断还要非常熟练的敲出SQL语句

一、SQL语句查询关键字

关键词：select和from

作用描述：from的作用是指定需要查询信息的表，select的作用是指定需要查询的字段信息，根据字段信息获取内容

select
	指定需要查询的字段信息
  	select *					查询所有字段的信息
    select name 				查询name字段的信息
    select char_length(name) = 4
    支持对字段做处理，比如这里是查找name字段中长度为4的记录

from
	指定需要查询的表信息
  	from mysql.user
    from t1

SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
	eg:
    select id,name from userinfo;
  • 比如这里就是先执行from查找到表中的信息，然后再根据select跟后面的字段名称查找对应的id和name信息。
  • 对应关键字的编写顺序和执行顺序我们没必要过多的在意，熟练之后会非常自然的编写，我们只需要把注意力放在每个关键字的功能上即可。

二、前期数据准备

  • 为后面的查询准备的数据值，cv复制执行即可
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
);

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

三、编写SQL语句的小技巧

  • 针对select后面的字段名可以先用*占位往后写 最后再回来修改
select * from 表名称
  • 在实际应用中select后面很少直接写* 因为*表示所有，当表中字段和数据都特别多的情况下非常浪费数据库资源（也就是说记录特别多的时候就会显示不下，造成数据显示混乱，但是可以用\G来分行查看）。
  • SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要反反复复的修修补补。

四、查询关键字之where筛选

关键词：where

使用描述：where后面跟上条件，根据条件查找信息

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

select * from emp where id >= 3 and id <= 6;  支持逻辑运算符（如<>表示不等于等）
select * from emp where id between 3 and 6;

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

select * from emp where salary=20000 or salary=18000 or salary=17000;

select * from emp where salary in (20000,18000,17000); 支持成员运算

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

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

select * from emp where id not between 3 and 6;

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

因为查找条件不够精确的查询，我们称之为——模糊查询。

模糊查询的关键字：

  • like

模糊查询的常用符号 ：

  • %:匹配任意个数的任意字符
  • _:匹配单个个数的任意字符
eg: 

    %o%			o jason owen loo wwoww
    %o			o	asdasdo asdo
    
    _o_			aox	wob iok 
    o_			oi ok ol
    select * from emp where name like '%o%';

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

select * from emp where name like '____';
select name,salary from emp where name like '____';

select * from emp where char_length(name) = 4;
select name,salary from emp where char_length(name)=4;

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

select * from emp where post_comment=NULL;  		不可以
select * from emp where post_comment is NULL;		可以

在MySQL中也有很多内置方法，我们可以通过查看帮助手册学习。同时我们可以使用如下方法，查看不同方法名的简介：

help 方法名

五、查询关键字之group by分组

group by介绍

关键词：group by

使用描述：group by 后面接上条件，根据条件进行筛选，同时除了5.6版本，别的版本需要把select后面的* 号改成固定的字段。

分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
将班级学生按照性别分组
将全国人民按照民族分组
将全世界的人按照肤色分组

分组的目的是为了更好的统计相关数据
eg:
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资

聚合函数

专门用于分组之后的数据统计

名称 含义
avg 表示求指定列的平均值
min 表示求指定列的最小值
max 表示求指定列的最大值
sum 表示求指定列的和
count 表示求指定列的总行数

例子

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

select * from emp group by post;
  • 小知识点

输入上面的代码的时候，MySQL5.6默认不会报错，MySQL5.7及8.0默认都会直接报错。
​ 原因是分组之后，select后面默认只能直接填写分组的依据，不能再写其他字段。

5.6版本如果需要修改成跟其他版本一样的话需要修改配置：

set global sql_mode='strict_trans_tables,only_full_group_by'

接着使用下方代码即可正确查询

select post from emp group by post;

select age from emp group by age;

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

2.获取每个部门的最高工资

要不要分组我们完全可以从题目的需求中分析出来，尤其是出现关键字：每个、平均等词的时候。

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

针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;

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

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

4.统计每个部门的人数

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

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

分组以外的字段无法直接填写，因此我们需要借助于方法。

group_concat作用：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

select post,name from emp group by post;
这里只能看到每个部门中的一个名字

select post,group_concat(name) from emp group by post;
使用了group_concat方法后就可以获得每个部门的所有名字

group_concat方法还可以获取多个字段的值。

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

group_concat方法获取的字段值可以拼接。

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

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

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

六、查询关键字之having过滤

关键词：having

作用描述：

  • having与where本质是一样的 都是用来对数据做筛选
  • 只不过where用在分组之前(首次筛选)，having用在分组之后(二次筛选)

题目：统计各部门年龄在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;

七、查询关键字之distinct去重

关键词：distinct

作用描述：类似python中的集合的去重作用。

ps：去重有一个必须的条件，也是很容易被忽略的条件——数据必须一模一样才可以去重

select distinct id,age from emp;

当我们的去重关键字后跟上多个字段后，去重的对象就会变成这些字段组合起来之后的结果，如果组合起来的结果出现了重复，才会去重。
关键字后跟上单个字段的时候就会针对单个字段内的值进行去重。

select distinct age from emp; 

select distinct age,post from emp;

八、查询关键字之order by排序

概念讲解

关键词：order by

作用描述：关键词后面跟上字段名称，会根据字段的值进行排序，默认情况下是升序，字段名称后面跟上asc也是升序，字段名称后面跟上desc就会变成降序。

举例：

1.可以是单个字段排序

select * from emp order by age;  默认升序
select * from emp order by age asc;  默认升序(asc可以省略)
select * from emp order by age desc;  降序

2.也可以是多个字段排序

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

统计各部门年龄在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分页

关键词：limit

作用描述：当表中数据特别多的情况下 我们很少会一次性获取所有的数据，limit的作用就是控制一次性显示多少跳记录。后面跟的数字就是规定显示几条数据。
ps：很多网站也是做了分页处理 一次性只能看一点点

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

查询工资最高的人的详细信息
'''千万不要惯性思维 一看到工资最高就想着用分组聚合'''

select * from emp order by salary desc limit 1;

十、查询关键字之regexp正则表达式

关键词：regexp

作用描述：关键词后面接上正则表达式进行筛选。

SQL语句的模糊匹配如果用不习惯，也可以自己写正则批量查询。

ps：8.0之前的MySQL无法理解?运算符。

select * from emp where name regexp '^j.*?(n|y)$';

select * from emp where name regexp '^j.*(n|y)$';

十一、多表查询的思路

表数据准备

create table dep(
  id int primary key auto_increment,
  name varchar(16) 
);

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;  
会将两张表中所有的数据对应一遍

这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义，应该将有关系的数据对应到一起才合理。

笛卡尔积概念

首先，先简单解释一下笛卡尔积。现在，我们有两个集合A和B。

A = {0,1} B =

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

可以得出A×B和B×A的笛卡尔积，但总体思路为用

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。

从以上的数据分析我们可以得出以下两点结论：

1，两个集合相乘，不满足交换率，既 A×B ≠ B×A;

2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;

基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来

涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定

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

基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

十二、作业

完成下列基础练习题SQL编写

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

select post '岗位名称',group_concat(name) '员工名称' from emp group by post;

img

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

select post '岗位名称',count(id) '员工个数' from emp group by post;

img

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

select gender '性别',count(id) '人数'from emp group by gender;

img

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

select post '岗位名称',avg(salary) '平均薪资' from emp group by post;

img

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

select post '岗位名称',max(salary) '最高薪资' from emp group by post;

img

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

select post '岗位名称',min(salary) '最低薪资' from emp group by post;

img

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

select gender '性别',avg(salary) '平均薪资' from emp group by gender;

img

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

select post '部门名称',avg(salary) '平均工资' from emp where age>30 group by post;

img

posted @ 2022-11-25 19:55  wwwxxx123  阅读(60)  评论(0)  编辑  收藏  举报