热门标签
热门文章
- 1Elasticsearch查询文档--常见API篇（附详细代码和案例图文）_elasticsearch api文档
- 2大数据之Hadoop概念导论（新手上路必备）一_hadoop大数据绪论
- 3UI界面设计新手指南 | 零基础快速入门教程_ui界面设计怎么学习
- 4基于源码搭建运行 RocketMQ 主从架构_rocketmq一主一从同步搭建
- 5python与ai编程_AI与Python - 逻辑编程
- 6MySQL 创建表_mysql创建表t1代码
- 7Git Commit 提交规范，变更日志、版本发布自动化和 Emoji 提交标准_git 提交日志规范
- 8图像识别研究现状_图像识别技术国外研究现状
- 9我们的系统检测到您的计算机网络中存在异常流量_入侵检测技术
- 10go gin timeout
当前位置: article > 正文
mysql基础_sql怎么把字段显示为汉字
作者：很楠不爱3 | 2024-05-21 19:41:08
赞
踩
sql怎么把字段显示为汉字
1、将查询出来的字段显示为中文
select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;
- 1
注意：字符串必须添加单引号 | 双引号
可以采用 as 关键字重命名表字段，其实 as 也可以省略，如：
select empno "员工编号", ename "员工姓名", sal*12 "年薪" from emp;
- 1
2、条件查询
条件查询需要用到 where 语句，where 必须放到 from 语句表的后面支持如下运算符
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <> 或！= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between… and …. | 两个值之间，等同于 >= and <= |
| is null | 为 null（is not null 不为空） |
| and | 并且 |
| or | 或者 |
| in | 包含，相当于多个 o（r not in 不在这个范围中） |
| not | not 可以取非，主要用在 is 或 in 中 |
| like | like 称为模糊查询，支持 % 或下划线匹配 % 匹配任意个字符 下划线，一个下划线只匹配一个字符 |
2.1 、等号操作符
- 查询薪水为 5000 的员工
select empno, ename, sal from emp where sal=5000;
- 1
- 查询 job 为 MANAGER 的员工
select empno, ename from emp where job=manager;
- 1
以上查询出现错误，因为 job 为字符串，所以出现了以上错误
select empno, ename from emp where job="manager";
- 1
select empno, ename from emp where job=’manager’;
- 1
也可以使用单引号
select empno, ename from emp where job='MANAGER';
- 1
以上输出正确，Mysql 默认情况下大小写是不敏感的。
注意：
MySQL 在 windows 下是不区分大小写的，将 script 文件导入 MySQL 后表名也会自动转化为小写，结果再 想要将数据库导出放到 linux 服务器中使用时就出错了。因为在 linux 下表名区分大小写而找不到表，查了很多都是说在 linux 下更改 MySQL 的设置使其也不区分大小写，但是有没有办法反过来让 windows 下大小写敏感呢。其实方法是一样的，相应的更改 windows 中 MySQL 的设置就行了。
具体操作：
在 MySQL 的配置文件 my.ini 中增加一行：
lower_case_table_names = 0
其中 0：区分大小写，1：不区分大小写
MySQL 在 Linux 下数据库名、表名、列名、别名大小写规则是这样的：
- 数据库名与表名是严格区分大小写的；
- 表的别名是严格区分大小写的；
- 列名与列的别名在所有的情况下均是忽略大小写的；
- 变量名也是严格区分大小写的； MySQL 在 Windows 下都不区分大小写
2.2 、 <> 操作符
- 查询薪水不等于 5000 的员工
select empno, ename, sal from emp where sal <> 5000;
- 1
一下写法等同于以上写法，建议使用第一种写法
select empno, ename, sal from emp where sal != 5000;
- 1
数值也可以采用单引号引起来，如一下语句是正确的 (不建议这么写)：
select empno, ename, sal from emp where sal <> '5000';
- 1
- 查询工作岗位不等于 MANAGER 的员工
select empno, ename from emp where job <> 'MANAGER';
- 1
2.3 、between … and … 操作符
- 查询薪水为 1600 到 3000 的员工 (第一种方式，采用>= 和 <=)
select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;
- 1
- 查询薪水为 1600 到 3000 的员工 (第一种方式，采用 between … and …)
select empno, ename, sal from emp where sal between 1600 and 3000;
- 1
关于 between … and …，它是包含最大值和最小值的
2.4 、is null
- Null 为空，但不是空串，为 null 可以设置这个字段不填值，如果查询为 null 的字段，采用 is null
- 查询津贴为空的员工
select * from emp where comm=null;
- 1
以上也无法查询出符合条件的数据，因为 null 类型比较特殊，必须使用 is 来比较
select * from emp where comm is null;
- 1
以上查询正确
2.5 、 and
and 表示并且的含义，表示所有的条件必须满足
- 工作岗位为 MANAGER, 薪水大于 2500 的员工
select * from emp where job='MANAGER' and sal > 2500;
- 1
2.6 、or
or，只要满足条件即可，相当于包含
- 查询出 job 为 manager 或者 job 为 salesman 的员工
select * from emp where job='MANAGER' or job='SALESMAN';
- 1
2.7 、表达式的优先级
- 查询薪水大于 1800，并且部门代码为 20 或 30 的员工（错误的写法）
select * from emp where sal > 1800 and deptno = 20 or deptno = 30;
- 1
以上输出不是预期结果，薪水小于 1800 的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤 sal > 1800 and deptno = 20，然后再将 deptno = 30 员工合并过来，所以是不对的
- 查询薪水大于 1800，并且部门代码为 20 或 30 的（正确的写法）
select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
- 1
关于运算符的问题：不用记，没有把握尽量采用括号
2.8 、in
in 表示包含的意思，完全可以采用 or 来表示，采用 in 会更简洁一些
- 查询出 job 为 manager 或者 job 为 salesman 的员工
select * from emp where job in ('manager','salesman');
- 1
- 查询出薪水包含 1600 和薪水包含 3000 的员工
select * from emp where sal in(1600, 3000);
- 1
2.9 、not
- 查询出薪水不包含 1600 和薪水不包含 3000 的员工（第一种写法）
select * from emp where sal <> 1600 and sal <> 3000;
- 1
- 查询出薪水不包含 1600 和薪水不包含 3000 的员工（第二种写法)
select * from emp where not (sal = 1600 or sal = 3000);
- 1
- 查询出薪水不包含 1600 和薪水不包含 3000 的员工（第三种写法）
select * from emp where sal not in (1600, 3000);
- 1
- 查询出津贴不为 null 的所有员工
select * from emp where comm is not null;
- 1
2.10 、like
- Like 可以实现模糊查询，like 支持 % 和下划线匹配
- 查询姓名以 M 开头所有的员工
select * from emp where ename like 'M%';
- 1
- 查询姓名以 N 结尾的所有的员工
select * from emp where ename like '%N';
- 1
- 查询姓名中包含 O 的所有的员工
select * from emp where ename like '%O%';
- 1
- 查询姓名中第二个字符为 A 的所有员工
select * from emp where ename like '_A%';
- 1
Like 中 % 和下划线的差别？
% 匹配任意字符出现的个数下划线只匹配一个字符
Like 中的表达式必须放到单引号中 | 双引号中，以下写法是错误的：
select * from emp where ename like _A%
- 1
3、排序数据
3.1 、单一字段排序
排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面
- 按照薪水由小到大排序 (系统默认由小到大)
select * from emp order by sal;
- 1
- 取得 job 为 MANAGER 的员工，按照薪水由小到大排序 (系统默认由小到大)
select * from emp where job='MANAGER' order by sal;
- 1
如果包含 where 语句 order by 必须放到 where 后面，如果没有 where 语句 order by 放到表的后面以下写法是错误的：
select * from emp order by sal where job='MANAGER';
- 1
- 按照多个字段排序，如：首先按照 job 排序，再按照 sal 排序
select * from emp order by job,sal;
- 1
3.2 、手动指定排序顺序
- 手动指定按照薪水由小到大排序
select * from emp order by sal asc;
- 1
- 手动指定按照薪水由大到小排序
select * from emp order by sal desc;
- 1
3.3 、多个字段排序
- 按照 job 和薪水倒序
select * from emp order by job desc, sal desc;
- 1
如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序
3.4 、使用字段的位置来排序
- 按照薪水升序
select * from emp order by 6;
- 1
不建议使用此种方式，采用数字含义不明确，程序不健壮
4、数据处理函数 / 单行处理函数
| Lower | 转换小写 |
|---|---|
| upper | 转换大写 |
| substr | 取子串（substr (被截取的字符串，起始下标，截取的长度)） |
| length | 取长度 |
| trim | 去空格 |
| str_to_date | 将字符串转换成日期 |
|---|---|
| date_format | 格式化日期 |
| format | 设置千分位 |
| round | 四舍五入 |
| rand() | 生成随机数 |
| Ifnull | 可以将 null 转换成一个具体值 |
4.1 、lower
l 查询员工，将员工姓名全部转换成小写
select lower(ename) from emp;
- 1
4.2 、upper
- 查询 job 为 manager 的员工
select * from emp where job=upper('manager');
- 1
4.3 、substr
- 查询姓名以 M 开头所有的员工
select * from emp where substr(ename, 1, 1)=upper('m');
- 1
4.4 、length
- 取得员工姓名长度为 5 的
select length(ename), ename from emp where length(ename)=5;
- 1
4.5 、trim
trim 会去首尾空格，不会去除中间的空格
- 取得工作岗位为 manager 的所有员工
select * from emp where job=trim(upper('manager '));
- 1
4.6 、str_to_date (必须严格按照标准输出)
- 查询 1981-02-20 入职的员工（第一种方法，与数据库的格式匹配上）
select * from emp where HIREDATE='1981-02-20';
- 1
- 查询 1981-02-20 入职的员工（第二种方法，将字符串转换成 date 类型）
select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');
select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y');
- 1
- 2
str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)
4.7 、date_format
- 查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss
select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;
- 1
select date_format(now(),'%Y-%m-%d %H %i %s');
- 1
now () 获得当前时间
日期格式的说明
%Y：代表 4 位的年份
%y：代表 2 位的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%H：代表小时,格式为(00… …23 )
%h： 代表小时,格式为(01……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M)
%T ：代表 时间,格式为 24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
4.8 、format
- 查询员工薪水加入千分位
select empno, ename, Format(sal, 0) from emp;
- 1
- 查询员工薪水加入千分位和保留两位小数
select empno, ename, Format(sal, 2) from emp;
- 1
4.9 、round
四舍五入
select round(123.56);
- 1
4.10 、rand()
- 生成随机数
select rand();
- 1
- 随机抽取记录数
select * from emp order by rand() limit 2;
- 1
order by 必须写上。
4.11 、case … when … then ……else …end
- 如果 job 为 MANAGERG 薪水上涨 10%, 如果 job 为 SALESMAN 工资上涨 50%
select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;
- 1
其他的工资不动，需要添加 else
select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e;
e.*:取 emp 表所有的字段 , emp as e 是表的别名可以省略as emp e
- 1
- 2
4.12 、ifnull
select ifnull(comm,0) from emp;
如果 comm 为 null 就替换为 0
在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL
为了防止计算结果出现NULL，建议先使用 ifnull 空值处理函数预先处理。以下 SQL 是计算年薪的：
select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;
- 1
- 2
- 3
- 4
- 5
数据处理函数又被称为单行处理函数，特点：输入一行输出一行
- 1
5、分组函数 / 聚合函数 / 多行处理函数
| count | 取得记录数 |
|---|---|
| sum | 求和 |
| avg | 取平均 |
|---|---|
| max | 取最大的数 |
| min | 取最小的数 |
注意：分组函数自动忽略空值，不需要手动的加where 条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm 这个字段中不为空的元素总数。
- 1
- 2
- 3
注意：分组函数不能直接使用在 where 关键字后面。
mysql> select ename,sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group function
- 1
5.1 、count
- 取得所有的员工数
select count(*) from emp;
- 1
Count (*) 表示取得所有记录，忽略 null，为 null 的值也会取得
- 取得津贴不为 null 员工数
select count(comm) from emp;
- 1
采用 count (字段名称)，不会取得为 null 的记录
- 取得工作岗位的个数
select count(distinct job ) from emp;
- 1
5.2 、sum
- Sum 可以取得某一个列的和，null 会被忽略
- 取得薪水的合计
select sum(sal) from emp;
- 1
- 取得津贴的合计
select sum(comm) from emp;
- 1
null 会被忽略
- 取得薪水的合计（sal+comm）
select sum(sal+comm) from emp;
- 1
从以上结果来看，不正确，原因在于 comm 字段有 null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字段转换成 0
select sum(sal+IFNULL(comm, 0)) from emp;
- 1
5.3 、avg
取得某一列的平均值
- 取得平均薪水
select avg(sal) from emp;
- 1
5.4 、max
取得某个一列的最大值
- 取得最高薪水
select max(sal) from emp;
- 1
- 取得最晚入职得员工
select max(str_to_date(hiredate, '%Y-%m-%d')) fromemp;
- 1
5.5 、min
取得某个一列的最小值
- 取得最低薪水
select min(sal) from emp;
- 1
- 取得最早入职得员工（可以不使用 str_to_date 转换）
select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;
- 1
5.6 、组合聚合函数
可以将这些聚合函数都放到 select 中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
- 1
6、分组查询
分组查询主要涉及到两个子句，分别是：groupby 和 having
6.1 、group by
- 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;
- 1
如果使用了 order by，order by 必须放到 group by 后面
- 按照工作岗位和部门编码分组，取得的工资合计
- 原始数据
- 分组语句
select job,deptno,sum(sal) from empgroup by job,deptno;
- 1
mysql> select empno,deptno,avg(sal) from emp group by deptno;
- 1
+——-+ +– +
| empno | deptno | avg(sal) |
+——-+ +– +
| 7782 | 10 | 2916.666667 |
| 7369 | 20 | 2175.000000 |
| 7499 | 30 | 1566.666667 |
+——-+ +———– +
以上 SQL 语句在 Oracle 数据库中无法执行，执行报错。
以上 SQL 语句在 Mysql 数据库中可以执行，但是执行结果矛盾。
在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数 + 参与分组的字段。
6.2 、having
如果想对分组数据再进行过滤需要使用 having 子句取得每个岗位的平均工资大于 2000
select job, avg(sal) from emp group by job having avg(sal) >2000;
- 1
分组函数的执行顺序：
根据条件查询数据
分组
采用 having 过滤，取得正确的数据
6.3 、select 语句总结
一个完整的 select 语句格式如下
select 字段
from 表名
where ......
group by ......
having....... (就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ......
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
以上语句的执行顺序
- 首先执行 where 语句过滤原始数据
- 执行 group by 进行分组
- 执行 having 对分组数据进行操作
- 执行 select 选出数据
- 执行 order by 排序
原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。
- 1
7、连接查询
7.1 、SQL92 语法
连接查询：也可以叫跨表查询，需要关联多个表进行查询
- 显示每个员工信息，并显示所属的部门名称
SQL> select ename, dname from emp, dept;
- 1
ENAME DNAME ---------- -------------- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING SMITH RESEARCH ALLEN RESEARCH WARD RESEARCH JONES RESEARCH MARTIN RESEARCH BLAKE RESEARCH CLARK RESEARCH SCOTT RESEARCH KING RESEARCH TURNER RESEARCH ADAMS RESEARCH JAMES RESEARCH
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
以上输出，不正确，输出了 56 条数据，其实就是两个表记录的成绩，这种情况我们称为：“笛卡儿乘积”，出现错误的原因是：没有指定连接条件
指定连接条件
select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;
也可以使用别名 select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
- 1
- 2
以上结果输出正确，因为加入了正确的连接条件
以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）
- 取得员工和所属的领导的姓名
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno; SQL> select * from emp;（普通员工） EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------------------------------------------------ 7369 SMITH CLERK 7902 17-12 月-80 800 20 7499 ALLEN SALESMAN 7698 20-2 月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 月 -81 2850 30 7782 CLARK MANAGER 7839 09-6 月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 月 -87 3000 20 7839 KING PRESIDENT 17-11 月-81 5000 10 7844 TURNER SALESMAN 7698 08-9 月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 月 -87 1100 20 7900 JAMES CLERK 7698 03-12 月-81 950 30 7902 FORD ANALYST 7566 03-12 月-81 3000 20 7934 MILLER CLERK 7782 23-1 月 -82 1300 10 已选择 14 行。 SQL> select * from emp;（管理者）
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表 emp e 代码了员工表，emp m 代表了领导表，相当于员工表和部门表一样
- 1
7.2 、SQL99 语法
- （内连接）显示薪水大于 2000 的员工信息，并显示所属的部门名称
采用 SQL92 语法：
select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000;
- 1
采用 SQL99 语法：
select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
或
select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;
在实际中一般不加inner 关键字
SQL92 语法和 SQL99 语法的区别：99 语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比 SQL92更清晰。
- 1
- 2
- 3
- 4
- 5
- （外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
右连接：
select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;
左连接：
select e.ename, e.sal, d.dname from dept d left joinemp e on e.deptno=d.deptno;
以上两个查询效果相同
- 1
- 2
- 3
- 4
- 5
连接分类：
内链接：
* 表 1 inner join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* inner 可以省略
外连接：
- 左外连接
* 表 1 left outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略
- 右外连接
* 表 1 right outer join 表 2 on 关联条件
* 做连接查询的时候一定要写上关联条件
* outer 可以省略
左外连接（左连接）和右外连接（右连接）的区别：
左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显 示右连接恰恰相反，以上左连接和右连接也可以加入 outer 关键字，但一般不建议这种写法<，如：
select e.ename, e.sal, d.dname from emp e right outerjoin dept d on e.deptno=d.deptno;
select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno;
- 1
- 2
左连接能完成的功能右连接一定可以完成
8、子查询
子查询就是嵌套的 select 语句，可以理解为子查询是一张表
8.1 、在 where 语句中使用子查询，也就是在 where 语句中加入 select 语句
查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名实现思路：
1、首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;
distinct 去除重复行
- 1
- 2
2、查询员工编号包含管理者编号的
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);
- 1
- 查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水 实现思路
1、 取得平均薪水
select avg(sal) from emp;
- 1
2、 取得大于平均薪水的员工
select empno, ename, sal from emp where sal > (select avg(sal) from emp);
- 1
11.2 、在 from 语句中使用子查询，可以将该子查询看做一张表
- 查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;
- 1
将以上查询作为一张表，放到 from 语句的后面
使用 92 语法：
select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;
- 1
使用 99 语法：
select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;
- 1
- 查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号 实现思路
1、首先取得各个部门的平均薪水
select deptno, avg(sal) avg_sal from emp group by deptno;
- 1
2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级
| select deptno,avg(sal) avg_sal from emp group by deptno; |
|---|
| select * from salgrade; |
| select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal; |
8.3 、在 select 语句中使用子查询
- 查询员工信息，并显示出员工所属的部门名称第一种做法，将员工表和部门表连接
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
- 1
第二种做法，在 select 语句中再次嵌套 select 语句完成部分名称的查询
select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
- 1
9、union
9.1、union 可以合并集合（相加）
1、查询 job 包含 MANAGER 和包含 SALESMAN 的员工
select * from emp where job in('MANAGER', 'SALESMAN');
- 1
2、采用 union 来合并
select * from emp where job='MANAGER' union select * from emp where job='SALESMAN'
- 1
合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同*。
- 1
10、limit 的使用
mySql 提供了 limit ，主要用于提取前几条或者中间某几行数据
select * from table limit m,n
其中 m 是指记录开始的 index，从 0 开始，表示第一条记录
n 是指从第 m+1 条开始，取 n 条。
select * from tablename limit 2,4
即取出第 3 条至第 6 条，4 条记录
- 1
- 2
- 3
- 4
- 5
- 6
- 7
10.1 、取得前 5 条数据
select * from emp limit 5;
- 1
10.2 、从第二条开始取两条数据
select * from emp limit 1,2;
- 1
10.3 、取得薪水最高的前 5 名
select * from emp e order by e.sal desc limit 5;
- 1
11、表
11.1 、创建表
- 语法格式
create table
tableName( columnName dataType(length), ………………..
columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';
- 1
- 2
- 3
- 4
- 5
- 6
创建表的时候，表中有字段，每一个字段有：
- 字段名
- 字段数据类型
- 字段长度限制
- 字段约束
- MySql 常用数据类型
| 类型 | 描述 |
|---|---|
| Char (长度) | 定长字符串，存储空间大小固定，适合作为 主键或外键 |
| Varchar (长度) | 变长字符串，存储空间等于实际数据空间 |
| double (有效数字位数，小数位) | 数值型 |
| Float (有效数字位数，小数位) | 数值型 |
| Int (长度) | 整型 |
| bigint (长度) | 长整型 |
| Date | 日期型 |
| BLOB | Binary Large OBject（二进制大对象） |
| CLOB | Character Large OBject（字符大对象） |
| 其它………………… |
- 建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识
create table
t_student( student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3))
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 向 t_student 表中加入数据，（必须使用客户端软件，我们的 cmd 默认是 GBK 编码 , 数据中设置的编码是 UTF-8）
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001,'zhangsan', 'm','1988-01-01', 'qqq@163.com', 10)
- 1
- 向 t_student 表中加入数据（使用默认值）
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3))
insert into t_student(student_id, student_name, birthday, email,classes_id)
values (1002,'zhangsan','1988-01-01','qqq@163.com',10)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
11.2 、增加 / 删除 / 修改表结构
采用 alter table 来增加 / 删除 / 修改表结构，不影响表中的数据
11.2.1 、添加字段
如：需求发生改变，需要向 t_student 中加入联系电话字段，字段名称为：contatct_tel 类型为 varchar (40)
alter table t_student add contact_tel varchar(40);
- 1
11.2.2 、修改字段
如：student_name 无法满足需求，长度需要更改为 100
alter table t_student modify student_name varchar(100) ;
- 1
如 sex 字段名称感觉不好，想用gender 那么就需要更爱列的名称
- 1
11.2.3 、删除字段
如：删除联系电话字段
alter table t_student drop contact_tel;
- 1
11.3 、添加、修改和删除
11.3.1 、insert
添加、修改和删出都属于 DML，主要包含的语句：insert、update、delete
- Insert 语法格式
Insert into 表名(字段，。。。。) values(值,................. )
- 1
- 省略字段的插入
insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);
- 1
不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到 insert 语句
- 指定字段的插入 (建议使用此种方式)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);
- 1
出现了主键重复的错误，主键表示了记录的唯一性，不能重复
- 如何插入日期：
第一种方法，插入的日期格式和显示的日期格式一致
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);
- 1
- 2
第二种方法，采用 str_to_date
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) valu es(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);
- 1
第三种方法，添加系统日期（now ()）
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);
- 1
- 表复制
create table emp_bak as select empno,ename,sal from emp;
- 1
以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中
- 如何将查询的数据直接放到已经存在的表中，可以使用条件
insert into emp_bak select * from emp where sal=3000;
- 1
11.3.2 、update
可以修改数据，可以根据条件修改数据
- 语法格式：
update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….
- 1
- 将 job 为 manager 的员工的工资上涨 10%
update emp set sal=sal+sal*0.1 where job='MANAGER';
- 1
11.3.3 、delete
可以删除数据，可以根据条件删除数据
- 语法格式：
Delete from 表名 where 。。。。。
- 1
- 删除津贴为 500 的员工
delete from emp where comm=500;
- 1
- 删除津贴为 null 的员工
delete from emp where comm is null;
- 1
11.4 、创建表加入约束
- 常见的约束
a) 非空约束，not null
b) 唯一约束，unique
c) 主键约束，primary key
d) 外键约束，foreign key
e) 自定义检查约束，check（不建议使用）(在 mysql 中现在还不支持)
11.4.1 、非空约束，not null
非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, email varchar(30),
classes_id int(3) )
insert into t_student(student_id, birthday,
email, classes_id)
values (1002, '1988-01-01', 'qqq@163.com', 10)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
以上错误为加入的学生姓名为空。
11.4.2 、唯一约束，unique
唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, email varchar(30) unique,
classes_id int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
以上插入了重复的email，所以出现了“违反唯一约束错误”，所以 unique 起作用了同样可以为唯一约束起个约束名
- 1
- 我们可以查看一下约束
mysql> use information_schema;
mysql> select * from table_constraints where table_name = 't_student';
- 1
- 2
关于约束名称可以到 table_constraints 中查询以上约束的名称我们也可以自定义。
drop table if exists t_student;
create table t_student(student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3) ,
constraint email_unique unique(email)/*表级约束*/ )
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
11.4.3 、主键约束，primary key
每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段 构成的，复合（联合）主键是由多个字段构成的
drop table if exists t_student;
create table t_student(
student_id int(10)
primary key,/*列级约束*/
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3))
insert into t_student(
student_id, student_name, sex, birthday, email, classes_id)
values (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
向以上表中加入学号为 1001 的两条记录，出现如下错误，因为加入了主键约束
我们也可以通过表级约束为约束起个名称：
drop table if exists t_student;
create table t_student
(student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3),
CONSTRAINT p_id PRIMARY key (student_id)
)
insert into t_student(student_id, student_name ,sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
11.4.4 、外键约束，foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必
须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。建立学生和班级表之间的连接
- 首先建立班级表 t_classes
drop table if exists t_classes;
create table t_classes(
classes_id int(3),
classes_name varchar(40),
constraint pk_classes_id primary key(classes_id))
- 1
- 2
- 3
- 4
- 5
- 在 t_student 中加入外键约束
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id))
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
向 t_student 中加入数据
insert into t_student (student_id, student_name, sex, birthday, email, classes_id) values(1001,'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
- 1
出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用
存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是 学生表
以上成功的插入了学生信息，当时 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空
drop table if exists t_student;
create table t_student( student_id int(10),
student_name varchar(20), sex char(2),
birthday date,
email varchar(30),
classes_id int (3) not null,
constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)
insert into t_student(student_id, student_name, sex, birthday, email, cla sses_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 再次插入班级编号为 null 的数据
添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误：
insert into t_classes (classes_id,classes_name)
values (10,'366');
insert into t_student( student_id,
student_name, sex, birthday, email, classes_id )
values( 1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10 ) mysql> update t_classes set classes_id = 20 where classes_name = '366';
- 1
- 2
- 3
- 4
- 5
因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。 我们也可以采取以下措施 级联更新。
mysql> delete from t_classes where classes_id = 10;
- 1
- 2
因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes)中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据， 再删除父表中的数据，采用 drop table 也不行，必须先drop 子表，再 drop 父表 我们也可以采取以下措施 级联删除。
- 1
11.4.5 、级联更新与级联删除
11.4.5.1 、on update cascade;
mysql 对有些约束的修改比较麻烦，所以我们可以先删除，再添加
alter table t_student drop foreign key fk_classes_id;
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade;
- 1
- 2
- 3
我们只修改了父表中的数据，但是子表中的数据也会跟着变动。`
11.4.5.2 、on delete cascade;
mysql 对有些约束的修改时不支持的，所以我们可以先删除，再添加
alter table t_student drop foreign key fk_classes_id;
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on delete cascade;
delete from t_classes where classes_id = 20;
- 1
- 2
- 3
- 4
我们只删除了父表中的数据，但是子表也会中的数据也会删除。
11.5 、t_student 和 t_classes 完整示例
drop table if exists t_classes;
create table t_classes(
classes_id int (3),
classes_name varchar(30) not null,
constraint pk_classes_id primary key(classes_id)
)
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(50) not null,
sex char(2) not null, birthday date not null,
email varchar(30) unique,
classes_id int (3) not null,
constraint pk_student_idprimary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) )
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
11.6 、增加 / 删除 / 修改表约束
11.6.1 、删除约束
- 将 t_student
删除外键约束 alter table 表名 drop foreign key 外键（区分大小写）;
alter table t_student drop foreign key fk_classes_id;
- 1
删除主键约束：alter table 表名 drop primary key ;
alter table t_student drop primary key;
- 1
删除约束约束：alter table 表名 drop key 约束名称；
alter table t drop key uk;
- 1
11.6.2 、添加约束
- 将 t_student 中的约束
添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从表 (外键字段) references 主表 (主键字段);
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);
- 1
添加主键约束：alter table 表 add constraint 约束名称 primary key 表 (主键字段);
alter table t_student add constraint pk primary key(student_id);
- 1
添加唯一性约束：alter table 表 add constraint 约束名称 unique 表 (字段);
alter table t_student add constraint uk unique(email);
- 1
11.6.3 、修改约束，其实就是修改字段
alter table t_student modify student_name varchar(30) unique;
- 1
mysql 对有些约束的修改时不支持，所以我们可以先删除，再添加
练习题
1、取得每个部门最高薪水的人员名称
说明：
- 先查出每个部门最高薪水，作为一张临时表 b；
- b 表和 a 表连接。
- 条件：部门编号相同，并且 EMP 的 sal 大于 b 表 maxsal。
select a.ename, b.* from EMP a join (select deptno, max(sal) as maxsal from EMP group by deptno) b on a.deptno = b.deptno and b.maxsal = a.sal;
- 1
2、哪些人的薪水在部门的平均薪水之上？
说明：
- 先查出每个部门平均薪水，作为一张临时表 b；
- b 表和 a 表连接，再查哪些人的薪水在部门的平均薪水之上。
- 条件：部门编号相同，并且 EMP 的 sal 大于 b 表 avgsal。
select a.ename, a.sal from EMP a join (select deptno, avg(sal) avgsal from EMP group by deptno) b on b.deptno = a.deptno and a.sal > avgsal;
- 1
3、取得部门中（所有人的）平均的薪水等级，如下：
说明：
先找出每个人的薪资等级
select a.ename, a.deptno, b.grade from EMP a join SALGRADE b on a.sal between b.losal and b.hisal order by deptno;
- 1
select a.deptno, avg(b.grade) from EMP a join SALGRADE b on a.sal between b.losal and b.hisal group by deptno;
- 1
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）
(1) limit
select sal from EMP order by sal desc limit 1;
- 1
(2) 表自连接
select sal from EMP where sal not in (select distinct a.sal from EMP a join EMP b on a.sal < b.sal);
- 1
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
(1) limit
select deptno from EMP group by deptno order by avg(sal) desc limit 1;
- 1
(2)
-
找出每个部门平均薪水：
select deptno, avg(sal) from EMP group by deptno;- 1
-
找出最大值：
select b.deptno, max(b.avgsal) from (select deptno, avg(sal) as avgsal from EMP group by deptno) b;- 1
或者：select deptno,avg(sal) as maxsal from EMP group by deptno having maxsal= (select max(b.avgsal) from (select avg(sal) as avgsal from EMP group by deptno) b);- 1
6、取得平均薪水最高的部门的部门名称
说明：根据上题得出答案：
select c.dname from (select b.deptno, max(b.avgsal) from (select deptno, avg(sal) as avgsal from EMP group by deptno) b) a join DEPT c on a.deptno = c.deptno;
- 1
- 2
或者：
select c.dname from (select deptno from EMP group by deptno order by avg(sal) desc limit 1) a join DEPT c on a.deptno = c.deptno;
- 1
7、求平均薪水的等级最低的部门的部门名称
说明：
-
找出部门平均工资：
select d.dname, avg(sal) as avgsal from EMP a join DEPT d on a.deptno = d.deptno group by d.dname; 或者： select deptno, avg(sal) as avgsal from EMP group by deptno;- 1
- 2
- 3
-
找出平均最小值：
select min(b.avgsal) from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b;- 1
-
找出最低值部门名称：
select a.deptno, c.dname, avg(sal) as avgsal from EMP a join DEPT c on a.deptno=c.deptno group by deptno having avgsal = (select min(b.avgsal) from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b);- 1
-
薪资最低部门等级：
select d.grade from SALGRADE d join (select min(b.avgsal) minsal from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b) t on t.minsal between d.losal and d.hisal;- 1
8、取得比普通员工 (员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
-
找出员工代码没有在 mgr 的普通员工：
select max(sal) from EMP where empno not in(select distinct mgr from EMP where mgr is not null);- 1
-
找出薪资大于 1600 的员工名，薪水：
select ename, sal from EMP where sal> (select max(sal) from EMP where empno not in(select distinct mgr from EMP where mgr is not null));- 1
9、取得薪水最高的前五名员工
select ename, sal from EMP order by sal desc limit 5;
- 1
10、取得薪水最高的第六到第十名员工
select ename, sal from EMP order by sal desc limit 5,5;
- 1
11、取得最后入职的 5 名员工
select ename, HIREDATE from EMP order by HIREDATE desc limit 5;
- 1
12、取得每个薪水等级有多少员工
说明：
-
找出每个员工薪水等级：
select a.ename, a.sal, b.grade from EMP as a join SALGRADE as b on a.sal between b.losal and b.hisal;- 1
-
按 grade 分组统计
select b.grade, count(*) from EMP as a join SALGRADE as b on a.sal between b.losal and b.hisal group by b.grade;- 1
或者：
select b.grade, count(*) from EMP a,SALGRADE b where sal between losal and hisal group by grade;
- 1
13、面试题
有 3 个表 ：
S (学生表)、C（课程表）、SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
- 1
- 2
- 3
问题：
1，找出没选过 “黎明” 老师的所有学生姓名。
2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
请用标准 SQL 语言写出答案，方言也行（请说明是使用什么方言）。
-—————————————————————————-
CREATE TABLE SC ( SNO VARCHAR(200), CNO VARCHAR(200), SCGRADE VARCHAR(200) ); CREATE TABLE S ( SNO VARCHAR(200 ), SNAME VARCHAR(200) ); CREATE TABLE C ( CNO VARCHAR(200), CNAME VARCHAR(200), CTEACHER VARCHAR(200) ); INSERT INTO C (CNO, CNAME, CTEACHER) VALUES ( '1','语文', '张'); INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '2', '政治', '王'); INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '3', '英语', '李'); INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '4', '数学', '赵'); INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '5', '物理', '黎明'); commit; INSERT INTO S ( SNO, SNAME) VALUES ( '1', '学生 1'); INSERT INTO S ( SNO, SNAME) VALUES ( '2', '学生 2'); INSERT INTO S ( SNO, SNAME) VALUES ( '3', '学生 3'); INSERT INTO S ( SNO, SNAME) VALUES ( '4', '学生 4'); commit; INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '1', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '2', '30'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '3', '20'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '4', '80'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '5', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '2', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '3', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '4', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '5', '40'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '3', '1', '60'); INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '3', '3', '80'); commit;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
问题 1. 找出没选过 “黎明” 老师的所有学生姓名。
问题 2: 列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
问题 3: 即学过 1 号课程又学过 2 号课所有学生的姓名。
14、列出所有员工及领导的姓名
select a.ename '员工', ifnull(b.ename, '没有上级') '领导' from EMP a left join EMP b on a.mgr = b.empno;
- 1
15、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
说明：
select a.empno, a.ename, d.dname from EMP a join EMP b on a.mgr = b.empno join DEPT d on a.deptno = d.deptno where a.hiredate < b.hiredate;
- 1
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门.
select b.dname, a.* from EMP a right join DEPT b on a.deptno = b.deptno;
- 1
17、列出至少有 5 个员工的所有部门
不知道
select b.dname, count(empno) from EMP a left join DEPT b on a.deptno = b.deptno where (select count(empno) from EMP group by a.deptno) > 5 group by a.deptno;
- 1
18、列出薪金比”SMITH” 多的所有员工信息.
select * from EMP where sal> (select sal from EMP where ename like 'smith');
- 1
19、列出所有”CLERK”(办事员) 的姓名及其部门名称，部门的人数.
说明：
-
列出所有 “CLERK” 办事员所在部门名称及姓名
select a.ename, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = 'clerk';- 1
-
统计所有部门人数：
select deptno, count(*) deptcount from EMP group by deptno;- 1
-
把以上 2 张表做表连接：
select t1.*, t2.deptcount from (select a.deptno, a.ename,a.job, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = 'clerk') t1 join (select deptno, count(*) as deptcount from EMP group by deptno) t2 on t1.deptno = t2.deptno;- 1
- 2
- 3
- 4
- 5
- 6
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
select job, count(*) as '人数统计' from EMP a group by job having min(sal)>1500 ;
- 1
21、列出在部门”SALES”<销售部> 工作的员工的姓名，假定不知道销售部的部门编号.
select a.ename from EMP a join DEPT b on a.deptno = b.deptno where b.dname='sales';
- 1
或者：
select ename from EMP where deptno = (select deptno from DEPT where dname="sales");
- 1
22、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级.
说明：
- 先查 公司平薪资
- 高于平均薪资员工
- 列出表连接和表关系结构
select a.ename, b.dname, l.ename, c.grade
from
EMP a
join
DEPT b
on
a.deptno = b.deptno
left join EMP l
on
a.mgr = l.empno
join SALGRADE c
on
a.sal between c.losal and c.hisal
where a.sal > (select avg(sal) from EMP);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
23、列出与”SCOTT” 从事相同工作的所有员工及部门名称。
select a.ename, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = (select job from EMP where ename
='SCOTT')
and a.ename<>'scott';
- 1
- 2
- 3
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金。
select ename,sal from EMP where sal in (select sal+ifnull(comm,0) from EMP where deptno = 30) and deptno <>30;
- 1
答案：空
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。部门名称。
高于平均薪资：
select a.ename, a.sal+ifnull(comm,0) sumsal, b.dname from EMP a join DEPT b on a.deptno = b.deptno where sal > (select avg(sal) from EMP where deptno=30);
- 1
- 2
高于最高薪资：
select a.ename, a.sal+ifnull(comm,0) sumsal, b.dname from EMP a join DEPT b on a.deptno = b.deptno where sal > (select max(sal) from EMP where deptno=30);
- 1
- 2
26、列出在每个部门工作的员工数量，平均工资和平均服务期限.
说明
select b.deptno, count(a.ename), b.dname, ifnull(avg(a.sal),0) avgsal, ifnull(avg(timestampdiff(YEAR, hiredate,now())), 0) avgserviceYear
from
EMP a right join DEPT b
on
a.deptno = b.deptno
group by
a.deptno,b.dname,b.loc;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
27、列出所有员工的姓名、部门名称和工资。
select a.ename, b.dname, a.sal+ifnull(comm,0) sumsal from EMP a join DEPT b on a.deptno = b.deptno;
- 1
28、列出所有部门的详细信息和人数
select b.DEPTNO, b.DNAME, b.LOC, count(a.ename) '人数' from EMP a right join DEPT b on a.deptno = b.deptno group by b.deptno, b.dname, b.loc;
- 1
29、列出各种工作的最低工资及从事此工作的雇员姓名
select a.ename, t.*
from EMP a
join (select
job, min(sal) minsal
from
EMP
group by
job) t
on a.job = t.job and a.sal = t.minsal;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
30、列出各个部门的 MANAGER (领导) 的最低薪金
select a.ename, a.deptno, min(sal) from EMP a where job like 'MANAGER' group by deptno;
- 1
31、列出所有员工的年工资，按年薪从低到高排序
select ename, (sal+ifnull(comm,0))*12 '年薪' from EMP order by '年薪' asc;
- 1
32、求出员工领导的薪水超过 3000 的员工名称与领导名称
select a.ename, b.ename as '领导名' from EMP a join EMP b on a.mgr = b.empno where b.sal >3000;
- 1
33、求出部门名称中，带’S’字符的部门员工的工资合计、部门人数。
select b.dname, sum(a.sal+ifnull(a.comm,0)), count(a.ename) from EMP a right join DEPT b on a.deptno=b.deptno where b.dname like '%s%' group by a.deptno, b.dname, b.loc;
- 1
34、给任职日期超过 30 年的员工加薪 10%.
select ename '姓名', sal '现在月薪', sal*1.1 '调整后月薪' from EMP where timestampdiff(YEAR,hiredate, now()) >= 30;
- 1
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/604323
推荐阅读
相关标签
