Mysql之查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql之查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9541字,纯文字阅读大概需要14分钟。
内容图文
一、语法
select distinct 查询字段1,查询字段2,...... from 表名
where 分组之前的过滤条件
group by分组依据
having 分组之后的过滤条件
order by 排序字段
limit 显示的条数
二、where 过滤
表数据如下:
company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, name varchar(20) notnull, sex enum(‘male‘,‘female‘) notnulldefault‘male‘, #大部分是男的 age int(3) unsigned notnulldefault28, hire_date date notnull, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql>desc employee; +--------------+-----------------------+------+-----+---------+----------------+| Field | Type |Null|Key|Default| Extra |+--------------+-----------------------+------+-----+---------+----------------+| id |int(11) | NO | PRI |NULL| auto_increment || name |varchar(20) | NO ||NULL||| sex | enum(‘male‘,‘female‘) | NO || male ||| age |int(3) unsigned | NO ||28||| hire_date | date | NO ||NULL||| post |varchar(50) | YES ||NULL||| post_comment |varchar(100) | YES ||NULL||| salary |double(15,2) | YES ||NULL||| office |int(11) | YES ||NULL||| depart_id |int(11) | YES ||NULL||+--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门:教学,销售,运营 insertinto employee(name,sex,age,hire_date,post,salary,office,depart_id) values (‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部 (‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1), (‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1), (‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1), (‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1), (‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1), (‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1), (‘成龙‘,‘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> select * from employee;
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
要求:查询员工姓名中包含i字母的员工与其薪资
alter table employee rename emp; select name,salary from db1.emp where name like‘%i%‘ #得到的结果如下: +------------+----------+| name | salary |+------------+----------+| wupeiqi |8300.00|| liwenzhou |2100.00|| jingliyang |9000.00|| jinxin |30000.00|+------------+----------+
要求:查询员工姓名是由四个字符组成的员工姓名和薪资
select name,salary from db1.emp where name like‘____‘; orselect name,salary from db1.emp where char_length(name) =4; #得到的结果如下 mysql>select name,salary from emp where name like‘____‘; +------+------------+| name | salary |+------+------------+| egon |7300.33|| alex |1000000.31|+------+------------+
要求:查询岗位描述为空(非空)的员工名与岗位名
select name,post from emp where post_comment isNULL; select name.post from emp where post_comment isnotNULL; #得到的结果如下: +------------+----------------------------+| name | post |+------------+----------------------------+| egon | 老男孩驻沙河办事处外交大使 || alex | teacher || wupeiqi | teacher || yuanhao | teacher || liwenzhou | teacher || jingliyang | teacher || jinxin | teacher || 成龙 | teacher || 歪歪 | sale || 丫丫 | sale || 丁丁 | sale || 星星 | sale || 格格 | sale || 张野 | operation || 程咬金 | operation || 程咬银 | operation || 程咬铜 | operation || 程咬铁 | operation |+------------+----------------------------+
三、group by 分组
#设置sql_mode为only_full_group_by,意味着以后但凡分组,只能取到分组的依据 mysql > set global sql_mode="strict_trans_tables,only_full_group_by"; #每个部分的最高工资 select post,max(salary) from emp groupby post; +----------------------------+-------------+| post |max(salary) |+----------------------------+-------------+| operation |20000.00|| sale |4000.33|| teacher |1000000.31|| 老男孩驻沙河办事处外交大使 |7300.33|+----------------------------+-------------+ #每个部门最低工资 select post,min(salary) from emp groupby post; +----------------------------+-------------+| post |min(salary) |+----------------------------+-------------+| operation |10000.13|| sale |1000.37|| teacher |2100.00|| 老男孩驻沙河办事处外交大使 |7300.33|+----------------------------+-------------+ #每个部门的平均工资 select post,avg(salary) from emp groupby post; +----------------------------+---------------+| post |avg(salary) |+----------------------------+---------------+| operation |16800.026000|| sale |2600.294000|| teacher |151842.901429|| 老男孩驻沙河办事处外交大使 |7300.330000|+----------------------------+---------------+ #每个部门工资的总和 select post,sum(salary) from emp groupby post; +----------------------------+-------------+| post |sum(salary) |+----------------------------+-------------+| operation |84000.13|| sale |13001.47|| teacher |1062900.31|| 老男孩驻沙河办事处外交大使 |7300.33|+----------------------------+-------------+ 每个部门的人数 select post,count(id) from emp groupby post; +----------------------------+-----------+| post |count(id) |+----------------------------+-----------+| operation |5|| sale |5|| teacher |7|| 老男孩驻沙河办事处外交大使 |1|+----------------------------+-----------+
补充 group_concat
select post,group_concat(name) from emp groupby post; +----------------------------+-------------------------------------------------------+| post | group_concat(name) |+----------------------------+-------------------------------------------------------+| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 || sale | 格格,星星,丁丁,丫丫,歪歪 || teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex || 老男孩驻沙河办事处外交大使 | egon |+----------------------------+-------------------------------------------------------+select post,group_concat(name ‘:‘ salary) from emp groupby post; +----------------------------+--------------------------------------------------------------------------------------------------------------------+| post | group_concat(name,‘:‘,salary) |+----------------------------+--------------------------------------------------------------------------------------------------------------------+| operation | 程咬铁:17000.00,程咬铜:18000.00,程咬银:19000.00,程咬金:20000.00,张野:10000.13|| sale | 格格:4000.33,星星:3000.29,丁丁:1000.37,丫丫:2000.35,歪歪:3000.13|| teacher | 成龙:10000.00,jinxin:30000.00,jingliyang:9000.00,liwenzhou:2100.00,yuanhao:3500.00,wupeiqi:8300.00,alex:1000000.31|| 老男孩驻沙河办事处外交大使 | egon:7300.33|+----------------------------+--------------------------------------------------------------------------------------------------------------------+
补充concat,单独使用时:
mysql>select concat(name,‘:‘,salary) from emp where age=18; +-------------------------+| concat(name,‘:‘,salary) |+-------------------------+| egon:7300.33|| jingliyang:9000.00|| jinxin:30000.00|| 丁丁:1000.37|| 星星:3000.29|| 程咬金:20000.00|| 程咬银:19000.00|| 程咬铜:18000.00|| 程咬铁:17000.00|+-------------------------+ mysql>select concat(name,‘:‘,salary) as‘薪水‘from emp where age=18; +--------------------+| 薪水 |+--------------------+| egon:7300.33|| jingliyang:9000.00|| jinxin:30000.00|| 丁丁:1000.37|| 星星:3000.29|| 程咬金:20000.00|| 程咬银:19000.00|| 程咬铜:18000.00|| 程咬铁:17000.00|+--------------------+
#补充:连表操作 获取一个部门最高薪资的人的信息
#获取post表 select post,max(salary) from emp groupby post; #连接两表 select t1.name,t1.salary from emp as t1 innerjoin (select post,max(salary) as ms from emp groupby post) as t2 on t1.salary = t2.ms; +--------+------------+| name | salary |+--------+------------+| egon |7300.33|| alex |1000000.31|| 格格 |4000.33|| 程咬金 |20000.00|+--------+------------+
原文:https://www.cnblogs.com/guanzhicheng/p/9021074.html
内容总结
以上是互联网集市为您收集整理的Mysql之查询全部内容,希望文章能够帮你解决Mysql之查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。