sql查询关键字()

今日内容概要

今日内容概要

SQL语句查询关键字

select
	指定需要查询的字段信息
    select *		查所有字段
    select name		查所有name字段
    select char_length(name) 支持对字段做处理
  
from
	指定需要查询的表信息
    from mysql.user
    from t1
    
SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
     select id,name from userinfo;
    我们先写的select再写的from 但是执行顺序是先执行from再执行select
    
#对于代码的执行顺序不需要过分的在意 

前期数据准备

create table emp(
  id int primary key auto_increment,  # 设置id为主键
  name varchar(20) not null, # 做一个名字不为空的处理
  gender enum('male','female') not null default 'male', # 大部分是男的(枚举 设置默认hi)
  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后面很少直接写* *表示所有 考虑到如果字段和数据都特别多的情况下 一次性取出所有的数据非常浪费数据库资源

查询关键字之where筛选

利用上面准备好的数据来试着写一下下面的题吧

# 1.查询id大于3小于等于6的数据
select * from emp where id >=3 and id <=6;
'''where 用作筛选数据 有点if的感觉 并且支持逻辑运算符号、取反'''
select * from emp where id between 3 and 6; #使用关键字也可以‘
'''between关键字专门用于数字间的范围查找 闭区间'''

# 2.查找薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
'''不是每次查找都要一个一个or的'''
select * from emp where salary in (20000,18000,17000);
'''支持成员运算符'''

# 3.查询员工姓名中包括o字母的员工员工姓名与薪资
select * from emp where name like '%o%';
	条件不够精确的查询 称之为 模糊查询
    模糊查询的关键字是	like
    模糊查询的常用符号	%:用于匹配任意个数的任意字符
        			  _:用于匹配单个个数的任意字符
    eg:
        %o%:	jason  owen loo
        _o_:	aoa bob
         
# 4.查询员工姓名是由四个字符组成的员工
select * from emp where name like '____'; #_ * 4
select * from emp where char_length(name) = 4;

# 5.查询岗位描述为空的员工名与岗位名 针对null不能等号 只能用is
select * from emp where post_comment=NULL;  # 直接报错
select * from rmp where post_comment is NULL;
'''
help 在这也可以适用 
	help 方法名
'''

查询关键字之group by分组

分组:按照指定的条件将单个单个的数据组成一个整体
    
# 聚合函数
	专门用于分组之后的数据统计
    max\min\sum\avg\count
    最大值、最小值、求和、平均值、计数
    
#1.将员工数据按照部门分组
select * from emp group by post;
'''
在mysql5.6默认不会报错 但在5.7及8.0版本都会直接报错
原因是分组后 select后面默认只能直接填写分组的依据 不能再写其他字段
改一下模式就行
set global
sel_mode='strict_trans_tables,only_full_group_by'
别忘记写上之前的模式 
分组之后默认最小的单位就是组 而不再是组内的单个数据的单个字段
'''

#2.获取每个部门的最高工资
select post,max(salary) from emp group bu post;
'''
针对sql语句执行后的结果我们可以起别名>>> as 别名 
'''

#3.一次性获取部门薪资相关统计
select post,max(salary) '最高薪' ,min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
'''起别名的时候可以不写as 但最好写一下 增加代码的可读性'''

#4.统计每一个部门的人数
select pos,count(id) from emp group by post;
'''使用count来计算表中的个数时候要想清楚填什么数据 如果是计算人数则需要一个唯一的数据(id)'''

#5.统计每个部门的部门名称及部门下的员工姓名
'''分组以外的字段无法直接填写 需要借助于方法'''
select post,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;
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与where本质是一样的 都是用来对数据做筛选处理的
	where用在分组之前(首次筛选)
    having用在分组之后(二次筛选)
    
#统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''首次:年龄30以上 二次:平均工资要在10000以上'''
	1.步骤一: 先筛选出所有年龄大于30的员工数据
	select * from emp where age > 30;
	2.步骤二: 再对筛选出来的数据按照部门计算平均薪资
	select post,avg(salary) from emp where age > 30 group bu post
	3.步骤三:针对分组之后的数据再进行一次筛选
	select post,avg(salary) from emp where age > 30 group bu post having avg(salary) > 10000;
'''
	每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''

查询关键字之distinct去重

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

select distinct id,age from emp; 
# id和age要一样才能去重
select distinct age from emp; 
select distinct age,post from emp;

查询关键字之order by排序

#1.可以是单个字段排序
select * from emp order by age;     # 默认升序
select * from emp order bu age asc; # 默认升序(asc可以省略)
select * from emp order by age decs;# 降序

#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分页

当表中数据特别多的情况下 我们很少会一次性获取所有的数据
	很多网站也是做了分页处理 一次性只能看一点点
  
select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5;  从第5条开始往后读取5条

查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;

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

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

多表查询思路

表数据准备
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;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
————————

今日内容概要

今日内容概要

SQL语句查询关键字

select
	指定需要查询的字段信息
    select *		查所有字段
    select name		查所有name字段
    select char_length(name) 支持对字段做处理
  
from
	指定需要查询的表信息
    from mysql.user
    from t1
    
SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
     select id,name from userinfo;
    我们先写的select再写的from 但是执行顺序是先执行from再执行select
    
#对于代码的执行顺序不需要过分的在意 

前期数据准备

create table emp(
  id int primary key auto_increment,  # 设置id为主键
  name varchar(20) not null, # 做一个名字不为空的处理
  gender enum('male','female') not null default 'male', # 大部分是男的(枚举 设置默认hi)
  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后面很少直接写* *表示所有 考虑到如果字段和数据都特别多的情况下 一次性取出所有的数据非常浪费数据库资源

查询关键字之where筛选

利用上面准备好的数据来试着写一下下面的题吧

# 1.查询id大于3小于等于6的数据
select * from emp where id >=3 and id <=6;
'''where 用作筛选数据 有点if的感觉 并且支持逻辑运算符号、取反'''
select * from emp where id between 3 and 6; #使用关键字也可以‘
'''between关键字专门用于数字间的范围查找 闭区间'''

# 2.查找薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
'''不是每次查找都要一个一个or的'''
select * from emp where salary in (20000,18000,17000);
'''支持成员运算符'''

# 3.查询员工姓名中包括o字母的员工员工姓名与薪资
select * from emp where name like '%o%';
	条件不够精确的查询 称之为 模糊查询
    模糊查询的关键字是	like
    模糊查询的常用符号	%:用于匹配任意个数的任意字符
        			  _:用于匹配单个个数的任意字符
    eg:
        %o%:	jason  owen loo
        _o_:	aoa bob
         
# 4.查询员工姓名是由四个字符组成的员工
select * from emp where name like '____'; #_ * 4
select * from emp where char_length(name) = 4;

# 5.查询岗位描述为空的员工名与岗位名 针对null不能等号 只能用is
select * from emp where post_comment=NULL;  # 直接报错
select * from rmp where post_comment is NULL;
'''
help 在这也可以适用 
	help 方法名
'''

查询关键字之group by分组

分组:按照指定的条件将单个单个的数据组成一个整体
    
# 聚合函数
	专门用于分组之后的数据统计
    max\min\sum\avg\count
    最大值、最小值、求和、平均值、计数
    
#1.将员工数据按照部门分组
select * from emp group by post;
'''
在mysql5.6默认不会报错 但在5.7及8.0版本都会直接报错
原因是分组后 select后面默认只能直接填写分组的依据 不能再写其他字段
改一下模式就行
set global
sel_mode='strict_trans_tables,only_full_group_by'
别忘记写上之前的模式 
分组之后默认最小的单位就是组 而不再是组内的单个数据的单个字段
'''

#2.获取每个部门的最高工资
select post,max(salary) from emp group bu post;
'''
针对sql语句执行后的结果我们可以起别名>>> as 别名 
'''

#3.一次性获取部门薪资相关统计
select post,max(salary) '最高薪' ,min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
'''起别名的时候可以不写as 但最好写一下 增加代码的可读性'''

#4.统计每一个部门的人数
select pos,count(id) from emp group by post;
'''使用count来计算表中的个数时候要想清楚填什么数据 如果是计算人数则需要一个唯一的数据(id)'''

#5.统计每个部门的部门名称及部门下的员工姓名
'''分组以外的字段无法直接填写 需要借助于方法'''
select post,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;
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与where本质是一样的 都是用来对数据做筛选处理的
	where用在分组之前(首次筛选)
    having用在分组之后(二次筛选)
    
#统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''首次:年龄30以上 二次:平均工资要在10000以上'''
	1.步骤一: 先筛选出所有年龄大于30的员工数据
	select * from emp where age > 30;
	2.步骤二: 再对筛选出来的数据按照部门计算平均薪资
	select post,avg(salary) from emp where age > 30 group bu post
	3.步骤三:针对分组之后的数据再进行一次筛选
	select post,avg(salary) from emp where age > 30 group bu post having avg(salary) > 10000;
'''
	每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''

查询关键字之distinct去重

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

select distinct id,age from emp; 
# id和age要一样才能去重
select distinct age from emp; 
select distinct age,post from emp;

查询关键字之order by排序

#1.可以是单个字段排序
select * from emp order by age;     # 默认升序
select * from emp order bu age asc; # 默认升序(asc可以省略)
select * from emp order by age decs;# 降序

#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分页

当表中数据特别多的情况下 我们很少会一次性获取所有的数据
	很多网站也是做了分页处理 一次性只能看一点点
  
select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5;  从第5条开始往后读取5条

查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;

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

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

多表查询思路

表数据准备
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;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据