MySQL综合实操练习题_代码007(未授权)

本文介绍: 【代码】MySQL综合实操练习题。

CREATE TABLE dept (
	deptno INT(2) NOT NULL COMMENT '部门编号',
	dname VARCHAR (15) COMMENT '部门名称',
	loc VARCHAR (20) COMMENT '地理位置' 
);

-- 添加主键
ALTER TABLE dept ADD PRIMARY KEY (deptno);

-- 添加数据
INSERT INTO dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');
INSERT INTO dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');
INSERT INTO dept (deptno,dname,loc)VALUES (30,'销售部','长安区');
INSERT INTO dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');

-- ---------------------------------------------------------------
CREATE TABLE emp(
	empno INT(4) NOT NULL COMMENT '员工编号',
	ename VARCHAR(10) COMMENT '员工名字',
	job VARCHAR(10) COMMENT '职位',
	mgr INT(4) COMMENT '上司',
	hiredate DATE COMMENT '入职时间',
	sal INT(7) COMMENT '基本工资',
	comm INT(7) COMMENT '补贴',
	deptno INT(2) COMMENT '所属部门编号'
);

-- 添加主键
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 添加外键约束
ALTER TABLE emp ADD CONSTRAINT f_ed_key
FOREIGN KEY (deptno) 
REFERENCES dept(deptno);

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');
INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');
INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');
INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');
INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');
INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');
INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');
INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');
INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');
INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');
INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');
INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');
INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');
INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

-- ---------------------------------------------------------------------
  
CREATE TABLE salgrade(
grade INT (10) COMMENT '工资等级',
losal INT (10) COMMENT '最低限额',
hisal INT (10) COMMENT '最高限额'
)

INSERT INTO salgrade (grade, losal, hisal)VALUES (1, 700, 1200);
INSERT INTO salgrade (grade, losal, hisal)VALUES (2, 1201, 1400);
INSERT INTO salgrade (grade, losal, hisal)VALUES (3, 1401, 2000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (4, 2001, 3000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (5, 3001, 9999);

select * from emp where deptno = 30;

select ename, empno, deptno from emp where job = "办事员";

select * from emp where comm > sal;

select * from emp where comm is null;

select * from emp where comm > sal * 0.6;

select * from emp 
where ((deptno = 10 and job = "人事部长") or (deptno = 20 and job = "办事员"));

select * from emp where comm is not null;

select * from emp where comm is null or comm < 100;

select * from emp where ename like "张%";

select ename, hiredate from emp order by hiredate desc;

select * from emp
order by job, sal;

select * from emp where ename not like "刘%";

select * from emp where ename like "李%";

select empno, ename, job, dname, loc
from emp e
join dept d
on e.deptno = d.deptno;

select a.ename, a.job, b.ename leader
from emp a
join emp b
on a.mgr = b.empno;

select c.empno, c.ename, c.sal, c.job, c.leader, dname, loc
from (
    select a.*, b.ename leader
    from emp a
    join emp b
    on a.mgr = b.empno) c
join dept d
on c.deptno = d.deptno;

select ename
from emp e
join dept d
on e.deptno = d.deptno
where dname = "销售部";

select * 
from emp
where job = (
    select job from emp where ename = "李兴"
);

select ename, sal
from emp
having sal > (select max(sal) from emp where deptno = 30);

select a.ename , a.hiredate, b.ename, b.hiredate 
from emp a
join emp b
on a.mgr = b.empno
where a.hiredate < b.hiredate;

select deptno, sum(empno) 人数, avg(sal) 平均工资
from emp
group by deptno;

select job, max(sal), min(sal)
from emp
group by job;

select job, avg(sal)
from emp
group by job;

select dname, count(*), avg(sal)
from emp e
left join dept d
on e.deptno = d.deptno
group by e.deptno;

select d.*, avgsal
from (
    select deptno, avg(sal) avgsal
    from emp
    group by deptno
    having avgsal > 2000) a
join dept d
on d.deptno = a.deptno;

select *
from emp
where sal > (select sal from emp where ename = "华仔");

select *
from emp
where sal > (select avg(sal) from emp);

select d.deptno, dname, loc, count(*), avg(sal)
from dept d
join emp e
on d.deptno = e.deptno
group by d.deptno;

select dname, count(*) cnt
from emp e
join dept d
on d.deptno = e.deptno
group by d.deptno
having cnt >= 1;

select grade, count(*)
from emp
join salgrade
where sal between losal and hisal
group by grade;

select dname, grade
from(
	select dname, avg(sal) avg_s
	from emp e
	join dept d
	on e.deptno = d.deptno
	group by d.deptno) c
join salgrade
on avg_s between losal and hisal;

显示所有内容

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。