MySQL查询关键数据方法
MySQL查询关键数据方法
操作表的SQL语句补充
1.修改表名
alter table 表名 reame 新表名；
2.新增字段名
alter table 表名 add 字段名 字段类型（数字） 约束条件； #默认队尾添加
alter table 表名 add 字段名 字段类型（数字） 约束条件 after 已经存在的字段； # 在某个已存在的字段后方添加字段
alter table 表名 add 字段名 字段类型（数字） 约束条件 frist； # 在队列首添加字段
3.修改字段
alter table 表名 change 旧字段 新字段 字段类型（数字） 约束条件；
alter table 表名 modify 字段名 新的字段类型（数字） 约束条件;
4.删除字段
altern table 表名 drop 字段名;
查询关键字
- 模板
1.数据准备(直接拷贝)
create table emp(
id int not null unique 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,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('joseph','male',78,'20010429','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),
('ALice','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('Cindy','female',38,'20101101','sale',2000.35,402,2),
('Kety','female',18,'20110312','sale',1000.37,402,2),
('zoe','female',18,'20160513','sale',3000.29,402,2),
('Emily','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('法外狂徒张三','male',18,'19970312','operation',20000,403,3),
('孤高游侠李四','male',18,'20130311','operation',19000,403,3),
('大刀王五','male',18,'20150411','operation',18000,403,3),
('三姓家奴吕布','male',18,'20140512','operation',17000,403,3);
查询关键字之select 与from
1.在SQL语句的关键字编写顺序与执行顺序是不一致的，但是我们可以实现针对其编写一个固定模板。
例如：select * from 表名 其他操作
2.我们可以确定数据肯定是先到表中然后才能从表中拿到数据，而在select后面的字段也可能是实际的例如我们经常使用的id等，也有可能是通过SQL动态产生的，所以可以先利用*号展位之后再进行修改
3.含义
select
自定义查询表中对应的数据
from
指定操作的对象（从哪张表拿数据，也可能是很多张表中拿取数据）
查询关键字之where筛选
1.按照编号查询编号范围内的数据
select id,name from 表名 where id >= id范围 and id <= id范围; # 查看范围内编号和姓名
select * from 表名 where id between id范围 and id范围; # 查看范围内的所有数据
2按照工资查询数据信息
select * from 表名 where salary = 薪资 or salary = 薪资 or salary = 薪资； # 查询对应薪资的所有数据信息
select * from 表名 where salary in (薪资，薪资，薪资); # 简化书写
select * from 表名 where salary not in (薪资，薪资，薪资); # 查询不在他们之间的薪资
3.诀窍
我们在编写查询类SQL语句的时候可以根据查询顺序来编写，例如我们从哪个表中取值 （from 表名），然后我们需要取哪些数据 （where name like '%数据%'），最后是对查询出来的数据筛选展示部分 (select name ,数据)
4.查询员工姓名中包含某字母的员工姓名和薪资
利用模糊查询：% 或者 _ 来查询我们所需要的信息
like 像 模糊查询
% 匹配任意个数的任意字符
_ 匹配单个个数的任意字符
show variables like '%内置文件名%' 含有该内置文件的全都取出
select * from 表名 where name like '%字母%'; # 名称内含有该字母的表内数据全部取出
5.查询名称是由几个字符组成的人信息
select name,其他信息项 from 表名 where name like '____'; # 方法一
select name,其他信息项 from 表名 where char_length(name) = 4; # 方法二
6.查询某id到某id范围内的数据
select * from 表名 where id not between 存在的范围 and 存在的范围; # 查询不在这个范围内的数据信息
select * from 表名 where id between 存在范围 and 存在范围; # 查询在这个范围内的数据信息
7.查询某个范围为空的姓名与位置 # 在SQL中查询NULL不能使用等号，只能使用is
select name,位置 from 表名 where 位置_comment = NULL; # 错误示范
select name,位置 from 表名 where 位置_comment is NULL； # 查询表内不相批也就是空的信息
select name,位置 from 表名 where 位置_comment not is NULL; # 查询表内不为空
查询关键字之group by分组
1.分组：按照指定的条件将单个并且在别的地方有重复行或者自己独有的个体数据分为一个个整体
分组之后我们所查询的单位就会变成以组为单位，不再按照个体来取单独的个体数据，如果获取了应该直接报错，select后面可以直接填写的字段名只能是分组的证据（其他字段需要借助于一些方法才可以获取）
set global sql_mode='strict_trans_tables,only_full_group_by';
2.关键字group by 将我们设置的目标群体进行分组
select 目标群体名 from 表名 group by 群体名；
3.常见的聚合函数
max 最大值
min 最小值
sum 总和
count 计数
avg 平均
4.以部门为单位获取每个部门的最高工资
select post,max(salary) from 表名 group by post; # 获取每个部门最高工资
select post,min(salary) from 表名 group by post; # 获取每个部门最低工资
select post,avg(salary) from 表名 group by post; # 获取每个部门的工资总和
select post,sum(salary) from 表名 group by post; # 获取每个部门的平均工资
select post,count(id) from 表名 group by post; # 获取每个部门的人数
5.查询以部门为单位下每个部门的人名
select post,group_concat(name) from 表名 group by post; # 查询每个部门下人名
select post,group_concat(name,'_SB') from 表名 group by post; # 给他后面加东西
select post group_concat(name,':',salary) from 表名 proup by post; # 展示部门下员工名字加薪资
select post group_concat(salary) from 表名 group by post; # 之战时薪资
查询关键字之having过滤
1.where 和 having 的功能其实是一样的都是进行分组和筛选数据，只不过where用于大范围筛选，而having是用于分组之后的筛选，为了我们不讲他们混淆所以我们叫where为筛选，而having叫做过滤
2.实践
统计各部门年龄在20岁以上的平均工资并且保留平均工资大于一万元的部门
select post,avg(salary) from 表名
where age >= 20
group by post
having avg(salary) > 10000；
查询关键字之distinc去重
1.去处一模一样的数据值
select distinct age from 表名；
查询关键字之order by排序
1.order by 内排序关键字
asc 升序
desc 降序
2.实例演示
select * from 表名 order by salary asc; # 升序
select * from 表名 order by salary desc; # 降序
select * from 表名 order by age asc; # 升序
select * from 表名 order by age desc; # 降序
3.延申
select * from 表名 order by age asc,salary desc; # 年龄升序，薪资降序进行筛选
4.拓展使用
select post,avg(salary) from 表名
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary)
; # 查找15岁以上薪资超过一千的分组的平均值进行分组
查询关键字之limit分页
1.限制展示的条数
select * from 表名 limit 3; # 限制展示行数为三行
2.只查看工资最高的一个人的详细信息
select * from 表名 order by salary desc limit 1; # 查看最高工资的人
select * from 表名 order by salary asc limit 1; # 最低工资的人
3.浏览器分页显示
select * from 表名 limit 0,5; # 第一表示起始位置，第二个则表示截至条数信息，就是以多少行进行展示
select * from 表名 limit 5,5; # 分页
select * from 表名 limit 10,5; # 分页
查询关键字之regexp正则
1.关键字 regexp 调用正则表达式
2.展示
select * from 表名 where name repexp'j.*(n|y)'; # 筛选前置为j中间不计后方要有n或y即可
多表查询思路
子查询
将一张表的查询结果括号括起来当做另外一条SQL语句的条件
eg:类似以日常生活中解决问题的方式
第一步干什么
第二步基于第一步的结果在做操作 ...
连表操作
先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据
#建表
create table 表名(
id int primary key auto_increment,
name varchar(20)
);
create table 表名(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into 表名 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;
insert into 表名(name,gender,age,dep_id) values
('jason','male',18,200),
('joseph','male',48,201),
('kevin','male',18,201),
('Alice','female',28,202),
('kity','female',18,203),
('jerry','female',18,204);
子查询
1.首先就像我们登录mysql，我们肯定得先打开cmd才可以使用mysql，我们进入mysql就需要找到路径并且运行才会进入mysql，然后我们登录还需要账号密码，然后进入数据库，然后我们才能操作我们的表，这上面的每一步都必须要有上一步做铺垫否则是几乎不可能完成的。
2.操作
首先我们获取某个人的部门编号
select dep_id from 表名 where name = '人物名称';
然后我们根据刚刚获取的部门编号获取他的部门名称
select name from 表名 where id = 200;
子查询（将上方的两部拼接起来）
select name from 表名 where id = (select dep_id feom 表名 where name = '任务名称')；
连表操作
1.笛卡尔积
select * from 表名1，表名2； # 笛卡尔积（将表内所有的数据全都一一对应一遍，特别浪费资源，并且效率较低）
select * from 表名1，表名2 where emp1.dep_id = dep1.id; # 拼接所有表共同都有的数据
2.实践
inner join 内连接
只拼接两边都有的字段数据，如果没有直接不理会
select * from 表名1 inner join 表名2 on 表名1.表名2_id = 表名2.id;
left join 左连接
只按照左边的为基准然后展示所有数据，如果没有那么就会使用NULL来填充，右边独有的并不会记录
select * from 表名1 lrft join 表名2 on 表名1.表名2_id = 表名2.id;
right join 右连接
只按照右边的为基准然展示所有数据，如果没有那么就会使用NULL来填充，左边独有的并不会记录
select * from 表名1 right join 表名2 on 表名1.表名2_id = 表名2.id;
union 全连接
只要是有的数据那么久全拼接起来，没有那么还是使用NULL来填充，独有数据也都存在
select * from 表名1 left join 表名2 on 表名1.表名2_id = 表名2.id
union
select * from 表名1 right join 表名2 on 表名1.表名2_id = 表名2.id;
作业
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from t1 group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from t1 group by post;
3. 查询公司内男员工和女员工的个数
select gender,count(id) from t1 group by gender;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from t1;
5. 查询岗位名以及各岗位的最高薪资
select post,avg(salary) from t1 group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from t1 group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select gender,avg(salary) from t1 group by gender;
8. 统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from t1 where age > 30 group by post;
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from t1 where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
