Mysql单表练习，带表的那种哦

小菜鸟学习Mysql的第一天（大佬看见勿喷）

DBMS：Navicat
表格示意图

建表代码

create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);

加数据代码

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

自己做的一些单表查询题目

-- 1查询部门编号为20的员工信息
SELECT *
FROM emp 
where deptno = 20
-- 2查询工作为clerk的员工号，员工名和部门号
select empno,ename,deptno
from emp
where job='clerk'
-- 3查询奖金高于工资的员工信息
select *
from emp
where comm>sal
-- 4查询奖金高于工资20%的员工信息
SELECT *
from emp
where comm>(sal*0.2)
-- 5查询部门编号是10并且工作为manager的员工和部门编号是20 并且工作是clerk的员工信息
SELECT *
from emp
where deptno=10 and job='manager' 
		or deptno=20 and job='clerk'
-- 6查询工作不是manager和clerk，并且工资大于或等于2000的员工信息
SELECT *
from emp
where sal>=2000 and job not in ('manager','clerk')
-- 7查询有奖金的员工信息
SELECT *
from emp 
where comm>0
-- 8查询没有奖金或奖金低于100的员工信息
SELECT *
from emp
where IFNULL(comm,-1)=-1
-- 9 查询员工名正好为6个字母的员工信息
SELECT *
from emp
where ename like '______'
-- 10查询员工名字中不包含S的员工信息
SELECT *
from emp
where ename not like '%s%'
-- 11查询员工名字的第二个字母为M的员工信息
select *
from emp
where ename like '_m%'
-- 12查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位
SELECT ename,hiredate,job
FROM emp
where hiredate>'1982-07-09'AND job<>'salesman'
-- 13查询员工姓名的第三个字母是a的员工姓名
SELECT ename
FROM emp
where ename LIKE '__a%'
-- 14查询除了10、20号部门以外的员工姓名、部门编号
SELECT ename ,deptno
from emp
where deptno not in (10,20)
-- 15查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序
SELECT *
from emp
WHERE deptno = 30
ORDER BY sal DESC,ename
		-- 工资降序
		SELECT *
		from emp
		WHERE deptno = 30
		ORDER BY sal DESC
		-- 姓名升序
		SELECT *
		from emp
		WHERE deptno = 30
		ORDER BY ename
-- 16查询没有上级的员工(经理号为空)的员工姓名
SELECT ename
FROM emp
where mgr is NULL
-- 17 查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号
SELECT ename,sal,deptno
from emp
WHERE sal>4500 and deptno in (10,20)

今天就是小菜鸟充实的一天，哈哈哈溜了溜了，有错的话大家也要帮我指出来呀！