mysql练习

目录

#-- mysql  1. 基础查询

#-- mysql  2. 分组查询

#emp,dept表（供参考）

##emp表

##dept表

---

#-- mysql  1. 基础查询

        

 -- 1.查询职员表中工资大于 1600 的员工姓名和工资
  select ename,sal from emp where sal > 1600;
  -- 2.查询职员表中员工号为 7369 的员工的姓名和部门号码
  select ename,deptno from emp where empno=7369;
  -- 3.选择职员表中工资不在 4000 到 5000 的员工的姓名和工资
  select ename,sal from emp where sal  not between 4000 and 5000;
  -- 4.选择职员表中在 20 和 30 号部门工作的员工姓名和部门号
  select ename,deptno from emp where deptno in(20,30);
  -- 5.选择职员表中没有管理者的员工姓名及职位，按职位排序
  select ename,job from emp where mgr is null order by job desc;
 --  6.选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列
  select ename,sal,comm from emp
  where comm is not null and comm <>0 order by sal desc;
  -- 7.选择职员表中员工职位的第二个字母是 a 的员工姓名
  select ename from emp where job like '-a%';
  -- 8.查询那些尚未分配部门的员工的姓名dept
  select ename from emp where deptno  is null;
  -- 9.列出部门表中的部门名字和所在城市；
  select dname,loc from dept;
  -- 10.显示出职员表中的不重复的职位；
  select distinct job from emp;
  -- 连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成 OUT_PUT
  select concat(ename,', ',job,', ',sal) as OUT_PUT from emp;
  -- ROUND(sal * 1.2)函数用于计算工资提高百分之20%后的结果，并将结果四舍五入到元为单位。
  select empno,ename,round(sal * 1.2) as salary from emp;
  -- 13.查询员工的姓名和工资数,条件限定为工资数必须大于 2200，并对查询结果按入职时间进行排序，早入职排在前面，晚入职排在后面
  select ename,sal from emp where
  sal > 2200 order by hiredate asc;
  -- 14.列出部门表中除了 ACCOUNTING 部门还有哪些部门。
  select * from dept where dname <> 'accounting';
  -- 15.已知员工的收入为：薪资+绩效*0.8，如果绩效为null，则表示
  -- 绩效为0。查询员工的姓名以及月收入（列名为money），并按照月收入升序排序
  select ename,sal + ifnull(comm*0.8,0) as salmonth from emp
  order by salmonth asc;
  -- 16.将员工的姓名按首字母排序，并列出姓名的长度（length）
  select ename,length(ename) as length
  from emp order by ename asc;
  -- 17.查询各员工的姓名 ename，并显示出各员工在公司工作的月份数 (即：和当前日期比较，该员工已经工作了几个月, 用整数表示)。
  select ename,timestampdiff(month,hiredate,now()) month from emp;
  select ename,timestampdiff(month,hiredate,curdate()) as month
  from emp;
  -- TIMESTAMPDIFF(MONTH, hire_date, CURDATE())函数用于计算入职日期（hire_date）到当前日期（CURDATE()）之间的月份数
/*
18.现有数据表 Customer，其结构如下所示：
 cust_id int(4)   Primary Key,    --客户编码
cname VARCHAR(25) Not Null,      --客户姓名
birthday DATE,                   --客户生日
account int.                     --客户账户余额
1.构造 SQL 语句，列出 Customer 数据表中每个客户的信息。如果客户生日未提供，则该列值显示“not available” 。如果没有余额信息，则显示“no account”。
2.构造 SQL 语句，列出生日在 1987 年的客户的全部信息。
3.构造 SQL 语句，列出客户帐户的余额总数。
*/
create table Customer (
    cust_id int(4)   Primary Key,
    cname VARCHAR(25) Not Null,
    birthday DATE,
    account int
);
select cust_id,cname, ifnull(birthday,'not available'),
ifnull(account,'no account') from customer;
select * from customer where year(birthday) = 1987;
select sum(account) from customer;
-- 19.按照”2009-4-11 20:35:10”格式显示系统时间。
select date_format(now(),'%Y-%c-%e %H:%i:%s') AS formatted_time;
-- 20.构造 SQL 语句查询员工表 emp 中员工编码 empno，姓名 ename，以及月收入(薪水 + 奖金)
select empno,ename,sal + ifnull(comm,0) salary from emp;
-- 21.查找员工姓名的长度是 5 个字符的员工信息。
select ename from emp where length(ename) = 5;
-- 查询员工的姓名和工资，按下面的形式显示：（提示：使用 lpad 函数)
select ename,lpad(sal,15,'$') from emp;
SELECT CONCAT(LPAD(ename, 10, ' '), ' - $', LPAD(sal, 10, ' ')) AS employee_info
FROM emp;
-- LPAD(name, 10, ' ')函数用于将姓名左侧填充空格，使其长度为10个字符。
-- LPAD(salary, 10, ' ')函数用于将工资左侧填充空格，使其长度为10个字符。
-- CONCAT(LPAD(name, 10, ' '), ' - $', LPAD(salary, 10, ' '))函数用于将姓名和工资拼接在一起，并添加分隔符和前缀
-- 23.查询薪水大于 4000 元的员工的姓名和薪水，薪水值显示为"$5000.00"这种形式，并对查询结果按薪水的降序方式进行排列；
SELECT ename, CONCAT('$', FORMAT(sal, 2)) AS formatted_salary
FROM emp
WHERE sal > 4000
ORDER BY sal DESC;
-- CONCAT('$', FORMAT(salary, 2))函数用于将薪水值格式化为"$5000.00"的形式

-- 24. hiredate列显示的格式为“1980-12-17”；再将hiredate列以“1980年12月17日”格式显示，并且显示的列名为“REVIEW”。
SELECT ename,DATE_FORMAT(hiredate, '%Y-%m-%d') AS hiredate,
 DATE_FORMAT(hiredate, '%Y年%m月%d日') AS REVIEW
FROM emp;

#-- mysql  2. 分组查询

-- 1.查询公司员工工资的最大值，最小值，平均值，总和
select sum(sal),max(sal),min(sal),avg(sal) from emp;
-- 2.查询每个部门中各个职位的最高薪水。
select max(sal),deptno,job from emp group by deptno,job;
-- 3.选择具有各个 job 的员工人数（提示：对 job 进行分组）
select count(ename),job from emp group by job;
-- 4.查询员工最高工资和最低工资的差距，列名为 DIFFERENCE
select max(sal)-min(sal) 'max-min(sal)' from emp;
-- 5.查询各个管理者属下员工的最低工资，其中最低工资不能低于 2000，没有管理者的员工不计算在内
select min(sal),mgr from emp where mgr is not null
group by mgr having min(sal)>=2000;
-- 6.查询各个部门中工资大于1500的员工人数
select deptno,count(ename) from emp where sal > 1500 group by deptno;
-- 7.查询各部门的平均绩效，如果绩效为null，则按数值0进行统计
select avg(ifnull(comm,0)) avg_comm,deptno from emp group by deptno;
-- 8.哪一子句可实现 SELECT 语句查询员工平均工资小于 5000 的部门信息 ?
--  A. GROUP BY dept_id WHERE AVG(sal) < 5000
-- B. GROUP BY AVG(sal) HAVING AVG(sal) < 5000
-- C. GROUP BY dept_id HAVING AVG(sal) < 5000
-- D. GROUP BY AVG(sal) < 5000
  -- C
-- 9.下列SQL语句出错的原因是（）。
--  select classid, avg(months_between(sysdate, entertime))
-- from student
-- where avg(months_between(sysdate, entertime)) > 12
-- group by classid
-- order by avg(months_between(sysdate, entertime));
--  A. select 短语中不能出现组函数
-- B. where 短语中不能限制分组函数
-- C. order by 子句中不能包含组函数
-- D. 组函数中不能包含单行函数
--  B

drop table student;
create table Student(
id int(4) primary key auto_increment,
name varchar(20) not null,
subject varchar(20),
score double(8,2),
classid int(2)
);
insert into student values
(null, '张三', '语文', 81,11),
(null, '张三', '数学', 75,11),
(null, '李四', '语文', 76,12),
(null, '李四', '数学', 90,12),
(null, '王五', '语文', 81,11),
(null, '王五', '数学', 100,12),
(null, '王五', '英语', 90,11);
--  查询每班中每个科目的最高成绩
select classid,subject,max(score) from student
group by classid,subject;
-- 11.列出职员表中所有薪水高于平均薪水值的员工信息，有SQL语句如下：
--  select ename, job from emp where sal > avg(sal);
--  上述语句是否正确？如果有错，写出正确的SQL语句。
select avg(sal) from emp;
select ename,job from emp where sal > (select avg(sal) from emp);
-- 12. 编写一条SQL语句，查询出每门课都大于80分的学生姓名。
select name,subject from student group by name,subject having min(score)>80;

#emp,dept表（供参考）

##emp表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `job` varchar(30) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(6,0) DEFAULT NULL,
  `comm` decimal(6,0) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');

##dept表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` varchar(30) DEFAULT NULL,
  `loc` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');