操作表的SQL语句、表查询关键字、多表查询

您所在的位置：网站首页 › 查找表格中的关键字 › 操作表的SQL语句、表查询关键字、多表查询

操作表的SQL语句、表查询关键字、多表查询

2022-10-26 02:59| 来源: 网络整理| 查看: 265

目录操作表的SQL语句表查询关键字一、数据准备二、select 与 from1.SQL语句的关键字编写顺序与执行数据是不一致的！！2.编写SQL语句针对select和from可以先写个固定模板三、where筛选1.查询id大于等于3小于等于6的数据2.查询薪资是20000或者18000或者17000的数据3.查询员工姓名中包含o字母的员工姓名和薪资4.查询员工姓名是由四个字符组成的员工姓名与其薪资5.查询id小于3或者大于6的数据6.查询薪资不在20000,18000,17000范围的数据7.查询岗位描述为空的员工名与岗位名三、group by 分组1.介绍2.写SQL时是否需要分组，根据需求分析分配组合常见的有聚合函数3.以组为单位统计组内数据3.1.每个部门的最高工资 max3.2.每个部门的最低工资 min3.3.每个部门的平均工资 avg3.4.每个部门的工资总和 sum3.5.每个部门的人数 count计数3.6.分组之后的每个部门名称和每个部门下的员工姓名 group_concat(字段名)四、having 过滤1.介绍2.统计各部门年龄大于30岁的员工的平均薪资，并保留平均工资大于10000的部门五、distinct 去重1.前提：数据必须一模一样六、older by 排序1.升序2.降序3.先按照age降序排，在年龄相同的情况下再按照薪资升序排4.统计各部门年龄在10岁以上的员工平均薪资，并且保留平均薪资大于3000的部门，然后对平均工资进行排序七、limit 分页1.限制展示条数2.查询工资最高的人的详细信息3.分页展示八、regexp 正则1.正则表达式回顾1.1.不同字符组的意义1.2.特殊符号1.3.量词2.实践使用regexp多表查询一、多表查询思路1.子查询2.连表操作3.代码演示作业添加salary字段，并修改数据值

操作表的SQL语句 1.修改表名 alter table 表名 rename 新表名; 2.新增字段 alter table 表名 add 字段名字段类型（数字）约束条件; alter table 表名 add 字段名字段类型（数字）约束条件 after 已经存在的字段; # 在某个字段后面添加字段 alter table 表名 add 字段名字段类型（数字）约束条件 first; # 添加在最前面 3.修改字段 alter table 表名 change 旧字段名新字段名字段类型（数字）约束条件; # 修改字段名、字段类型 alter table 表名 modify 字段名新的字段类型（数字）约束条件; # 只能修改字段类型 4.删除字段 alter table 表名 drop 字段名; 表查询关键字一、数据准备 create table emp( id int not null unique auto_increment, name varchar(20) not null, sex 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), #以下是教学部 ('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); 二、select 与 from 1.SQL语句的关键字编写顺序与执行数据是不一致的！！ eg: select name from emp; 肯定是线支持from确定表，之后执行select确定字段

2.编写SQL语句针对select和from可以先写个固定模板 select * from 表名其他操作; # select后的字段可能是实际的，也可能是通过SQL动态产生的，所以可以先用*占位最后再修改 select 自定义查询表中字段对应的数据 from 指定操作的对象（到底是哪张表，也可能多张表）

三、where筛选 1.查询id大于等于3小于等于6的数据 select * from emp where id>=3 and id>>聚合查询（聚集到一起合成为一个结果）

3.1.每个部门的最高工资 max select post,max(salary) from emp group by post; # 用as取别名 select post as '部门',max(salary) as '薪资' from emp group by post;

3.2.每个部门的最低工资 min select post,min(salary) from emp group by post; # 可以综合使用，更精准的查找数据 select post,min(salary) from emp where post is not null group by post; # 排除post是null的数据

3.3.每个部门的平均工资 avg select post as '部门',avg(salary) as '平均薪资' from emp group by post; # as取别名

3.4.每个部门的工资总和 sum select post as '部门',sum(salary) as '总薪资' from emp group by post;

3.5.每个部门的人数 count计数 select post as '部门',count(id) as '总人数' from emp group by post;

3.6.分组之后的每个部门名称和每个部门下的员工姓名 group_concat(字段名) select post,group_concat(name) from emp group by post;

# group_concat(字段名) 分组之后用，不仅可以用来显示除分组外的字段，还可以拼接字符串 select post,group_concat(name,'|',post) from emp group by post; select post,group_concat(name,'|','666') from emp group by post;

四、having 过滤 1.介绍

where和having的功能其实是一样的，都是用来筛选数据

只不过where用于分组之前的筛选，而having用于分组之后的筛选

为了人为的区分，所以叫where是筛选，having是过滤

2.统计各部门年龄大于30岁的员工的平均薪资，并保留平均工资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000;

五、distinct 去重 1.前提：数据必须一模一样 select distinct age from emp;

六、older by 排序 1.升序 select * from emp order by salary; # 默认升序 # 也可以用asc设定 select * from emp order by salary asc; # 升序

2.降序 select * from emp order by salary desc; # 降序

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

4.统计各部门年龄在10岁以上的员工平均薪资，并且保留平均薪资大于3000的部门，然后对平均工资进行排序 select post as '部门',avg(salary) from emp where age >10 # 筛选年龄大于10 group by post # 按部门分组 having avg(salary) > 3000 # 过滤平均薪资小于3000 order by avg(salary); # 按平均薪资升序排列

七、limit 分页 1.限制展示条数 select * from emp limit 3; # 限制展示3条记录

2.查询工资最高的人的详细信息 select * from emp order by salary # 按薪资升序排列 limit 1; # 限制展示一条

3.分页展示 select *from emp limit 0,5; # 展示 1~5 5条记录（不包含0） select * from emp limit 5,5; # 展示 6~10 5条记录（不包含5） ''' 第一个参数：表示起始位置第二个参数：表示展示数量 '''

八、regexp 正则 1.正则表达式回顾 1.1.不同字符组的意义

在没有量词修饰的情况下，一次只会针对一个数据值

字符组意义 [0-9] 0~9任意数字 [A-Z] A~Z任意字母 [a-z] a~z任意字母 [0-9a-zA-Z] 任意数字、大写字母或小写字母 1.2.特殊符号

在没有量词修饰的情况下，一个符号一次只会针对一个数据值

特殊符号意义 . 匹配除换行符以外的任意字符 \w 匹配字母、数字或下划线 \W 匹配非(字母、数字或下划线)的任意字符 \d 匹配数字 ^ 匹配字符串开头 $ 匹配字符串结尾 ^...$ 精确匹配具体数值，只能匹配^和$质按进度数据值 a|b 匹配字符a或b () 给正则表达式分组 [] 匹配[ ]内的所有字符 [^] 匹配除了[ ]内的所有字符 1.3.量词

不能单独使用，必须跟在表达式后面，只能影响紧挨着的左边那个，默认贪婪匹配

量词意义 * 重复零次或多次 + 重复一次或多次 ? 重复零次或一次 {n} 重复n次 {n,} 至少重复n次 {n,m} 重复n到m次 2.实践使用regexp # 查询name是j开头，n或y结尾的数据 select * from emp where name regexp '^j.*(n|y)$';

多表查询一、多表查询思路 1.子查询

将一张表的查询结果括号括起来当做另外一条SQL语句的条件

eg：类似于日常生活中解决问题的方式第一步做…… 第二步基于第一步的结果做…… 2.连表操作

先将所有涉及到结果的表全部都拼接到一起，形成一张大表，然后从大表中查询数据

3.代码演示 # 1.建表 create table dep1( -> id int primary key auto_increment, -> name varchar(32)); create table emp1( -> id int primary key auto_increment, -> name varchar(32), -> gender enum('male','female')not null default 'male', -> age int, -> dep_id int); # 2.添加数据 insert into dep1 values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'), -> (204,'安保'); insert into emp1(name,gender,age,dep_id)values -> ('jason','male',18,200), -> ('jassi','female',28,201), -> ('tony','female',18,201), -> ('kevin','female',18,202), -> ('kello','male',28,203), -> ('nicole','male',18,203), -> ('jerry','male',48,204);

# 3.子查询先获取jason的部门编号 select dep_id from emp1 where name = 'jason'; 根据部门编号获取部门名称 select name from dep1 where id = 200; 整合 select name from dep1 where id = (select dep_id from emp1 where name = 'jason');

# 4.连表操作 select * from emp1,dep1; # 笛卡尔积 '''笛卡尔积效率求数据，效率太低，连表有专门的语法''' 4.1 inner join 内连接拼接两边都有的字段数据 4.2 left join 左连接以左表为基准，展示所有数据，没有对应数据用null填充 4.3 right join 右连接以右表为基准，展示所有数据，没有对应数据用null填充 4.4 union 全连接 select * from emp1 left join dep1 on emp1.dep_id = dep1.id union select * from emp1 right join dep1 on emp1.dep_id = dep1.id;

作业 1. 查询岗位名以及岗位包含的所有员工名字 select dep1.name,group_concat(emp1.name) from emp1 left join dep1 on emp1.dep_id = dep1.id group by dep1.name;

2. 查询岗位名以及各岗位内包含的员工个数 select dep1.name,count(emp1.name) from emp1 left join dep1 on emp1.dep_id = dep1.id group by dep1.name;

3. 查询公司内男员工和女员工的个数 select gender as '性别',count(name) as '数量' from emp1 group by gender;

添加salary字段，并修改数据值

4. 查询岗位名以及各岗位的平均薪资 select dep1.name,avg(salary) from emp1 left join dep1 on dep_id = dep1.id group by dep1.name;

5. 查询岗位名以及各岗位的最高薪资 select dep1.name,max(salary) from emp1 left join dep1 on dep_id = dep1.id group by dep1.name;

6. 查询岗位名以及各岗位的最低薪资 select dep1.name,min(salary) from emp1 left join dep1 on dep_id = dep1.id group by dep1.name;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select gender,avg(salary) from emp1 group by gender;

8. 统计各部门年龄在30岁以上的员工平均工资 select avg(salary) from emp1 where age>30;

9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序 select dep1.name,avg(emp1.salary) from emp1 left join dep1 on dep_id = dep1.id where age>10 group by dep1.name;

【本文地址】

公司简介

联系我们