文章详情

登录

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql常用语句 3

2015-07-19 03:01

关注

mysql常用语句 3

找出每个部门平均薪水的薪资等级，from后面嵌套子查询
第一步先求出每个部门的平均薪水

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步把第一步的结果当做一个表，再和salgrade结合查询
mysql> select s.grade ,t.* from ( select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-------+--------+-------------+
| grade | deptno | avgsal |
+-------+--------+-------------+
| 3 | 30 | 1566.666667 |
| 4 | 10 | 2916.666667 |
| 4 | 20 | 2175.000000 |
+-------+--------+-------------+
3 rows in set (0.00 sec)

找出每个部门薪资等级的平均值

第一步先找每个部门的员工薪水等级
mysql> select s.grade,e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal;
+-------+--------+--------+---------+
| grade | ename | deptno | sal |
+-------+--------+--------+---------+
| 1 | SMITH | 20 | 800.00 |
| 3 | ALLEN | 30 | 1600.00 |
| 2 | WARD | 30 | 1250.00 |
| 4 | JONES | 20 | 2975.00 |
| 2 | MARTIN | 30 | 1250.00 |
| 4 | BLAKE | 30 | 2850.00 |
| 4 | CLARK | 10 | 2450.00 |
| 4 | SCOTT | 20 | 3000.00 |
| 5 | KING | 10 | 5000.00 |
| 3 | TURNER | 30 | 1500.00 |
| 1 | ADAMS | 20 | 1100.00 |
| 1 | JAMES | 30 | 950.00 |
| 4 | FORD | 20 | 3000.00 |
| 2 | MILLER | 10 | 1300.00 |
+-------+--------+--------+---------+
14 rows in set (0.00 sec)

第二步求平均值
mysql> select t.deptno,avg(t.grade) from (select s.grade,e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal) t join salgrade s group by t.deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

或者
mysql> select s.grade,avg(s.grade),e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
+-------+--------------+-------+--------+---------+
| grade | avg(s.grade) | ename | deptno | sal |
+-------+--------------+-------+--------+---------+
| 4 | 3.6667 | CLARK | 10 | 2450.00 |
| 1 | 2.8000 | SMITH | 20 | 800.00 |
| 3 | 2.5000 | ALLEN | 30 | 1600.00 |
+-------+--------------+-------+--------+---------+
3 rows in set (0.00 sec)

找出每个员工所在的部门名称，要求显示员工名和部门名。(使用嵌套查询)

mysql> select e.ename ,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

嵌套查询可以使用在select，from，where后面。

union(将结果集相加)，找出工作岗位是salesman,manager的员工
mysql> select ename,job from emp where job = "manager" union select ename,job from emp where job = "salesman";
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

mysql> select ename,job from emp where job = "manager";
+-------+---------+
| ename | job |
+-------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
3 rows in set (0.01 sec)

mysql> select ename,job from emp where job = "salesman";
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
4 rows in set (0.00 sec)

使用union要求两张表列数量必须一致。

limit 0(startIndex),8(length)

mysql> select ename from emp limit 0,8;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
+--------+
8 rows in set (0.00 sec)

运行顺序
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7

mysql> select ename from emp limit 2,5;
+--------+
| ename |
+--------+
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
+--------+
5 rows in set (0.00 sec)

mysql> select ename from emp limit 5;默认前面下标为0
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
+--------+
5 rows in set (0.00 sec)

找出工资排名在第四到第七的员工
mysql> select ename,sal from emp order by sal desc limit 3,3;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
+-------+---------+
3 rows in set (0.00 sec)

创建一个学生表：
create table t_student(
stu_name varchar(10),
stu_num varchar(10),
stu_teacher varchar(10),
stu_house varchar(10)

);

mysql> create table t_student(
-> stu_name varchar(10),
-> stu_num varchar(10),
-> stu_teacher varchar(10),
-> stu_house varchar(10)
->
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_cqust_db |
+--------------------+
| dept |
| emp |
| salgrade |
| t_student |
+--------------------+
4 rows in set (0.00 sec)

向表中插入数据。
insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
values ("hch","2019465335","laoyu","3210");

mysql> insert into t_student (stu_name,stu_num,stu_teacher,stu_house)//可以省略前面字段括号，后面必须和表一一对应。
-> values ("hch","2019465335","laoyu","3210");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+----------+------------+-------------+-----------+
| stu_name | stu_num | stu_teacher | stu_house |
+----------+------------+-------------+-----------+
| hch | 2019465335 | laoyu | 3210 |
+----------+------------+-------------+-----------+
1 row in set (0.00 sec)

插入多条数据。
insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
values ("qwe","2019456123","laoyu","3211"),("asd","2019123456","laoyu","3122");
mysql> insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
-> values ("qwe","2019456123","laoyu","3211"),("asd","2019123456","laoyu","3122");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t_student;
+----------+------------+-------------+-----------+
| stu_name | stu_num | stu_teacher | stu_house |
+----------+------------+-------------+-----------+
| qwe | 2019456123 | laoyu | 3211 |
| asd | 2019123456 | laoyu | 3122 |
+----------+------------+-------------+-----------+
2 rows in set (0.00 sec)

表的复制
mysql> create table emp1 as select ename,sal from emp;
Query OK, 14 rows affected (0.03 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> show tables;
+--------------------+
| Tables_in_cqust_db |
+--------------------+
| dept |
| emp |
| emp1 |
| salgrade |
| t_student |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from emp1;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

将查询的结果插入到表中。
mysql> insert into emp1 select * from emp1;
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> select * from emp1;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
28 rows in set (0.00 sec)

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     813人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     435人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

mysql常用语句 3

mysql常用语句 3
数据库2015-07-19

MySQL常用语句

MySQL常用语句
数据库2016-10-18

Mysql常用SQL语句

Mysql常用SQL语句
数据库2020-08-31

MySQL 常用操作语句

MySQL 常用操作语句
数据库2022-02-16

mysql和mysqldb常用语句

数据库2024-04-02

Mysql常用语句整理

Mysql常用语句整理
数据库2022-01-27

Mysql 常用 SQL 语句大全

数据库2022-05-24

mysql 一些常用sql语句

mysql 一些常用sql语句
数据库2020-11-06

mysql常用语句或函数

mysql常用语句或函数
数据库2014-05-26

mysql常用的语句整理

数据库2024-04-02

mysql sql常用语句大全

数据库2022-06-18

mysql基础（二） 常用SQL语句

数据库2024-04-02

mysql常用语句有多少条

mysql常用语句有多少条
数据库2024-08-01

mysql dba常用的查询语句

数据库2024-04-02

mysql常用的语句有哪些

数据库2024-04-02

MySQL数据库常用SQL语句

数据库2024-04-02

MYSQL中常见DDL语句

数据库2023-10-24

MySQL 常用的拼接语句汇总

数据库2022-05-31

mysql常用的一些语句整理

数据库2024-04-02

MySQL一些常用高级SQL语句

数据库2022-05-11
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯