大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说重新学习数据库（1）,希望您对编程的造诣更进一步.
数据库学习
1.简单查询 2022-07-13
drop TABLE emp;
drop TABLE dept;
drop TABLE salgrade;
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
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);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, “ACCOUNTING”, “NEW YORK”);
insert into DEPT (DEPTNO, DNAME, LOC) values (20, “RESEARCH”, “DALLAS”);
insert into DEPT (DEPTNO, DNAME, LOC) values (30, “SALES”, “CHICAGO”);
insert into DEPT (DEPTNO, DNAME, LOC) values (40, “OPERATIONS”, “BOSTON”);
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);
— 下面要特别注意，%d-%m-%Y 里的 d 和 m 一定要小写
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);
单元概述
通过本章的学习能够了解MySQL结构查询语言的概念，掌握SELECT查询语句的基本语法，掌握SELECT查询语句中过滤条件的使用，掌握过滤条件中比较运算符和逻辑运算符的使用，掌握查询结果的排序等
单元练习：
1. 查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ename,hiredate,job
FROM emp
WHERE hiredate>”1982-7-9″
AND job<> “SALESMAN”;
2. 查询员工姓名的第三个字母是a的员工姓名。
SELECT ename
FROM emp
WHERE ename
LIKE “__a%”;
3. 查询除了10、20号部门以外的员工姓名、部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno
not in (10,20);
4. 查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
SELECT *
FROM emp
WHERE deptno = 30
ORDER BY sal DESC, ename ASC;
5. 查询没有上级的员工(经理号为空)的员工姓名。
SELECT ename
FROM emp
WHERE mgr is null;
6. 查询工资大于等于4500并且部门为10或者20的员工的姓名工资、部门编号。
SELECT ename,sal,deptno
FROM emp
WHERE sal > 4500
AND deptno in (10,20);
课后练习：
-
-
查询月薪在3000到5000的员工姓名，月薪。
-
查询部门编号为10或者20的员工姓名，部门编号。
-
查询经理编号为7902, 7566, 7788的员工姓名，经理编号
SELECT ename,hiredate FROM emp WHERE hiredate BETWEEN “1982-01-01” AND “1985-12-31”
SELECT ename,sal FROM emp WHERE sal BETWEEN 3000 and 5000
SELECT ename,deptno FROM emp WHERE deptno IN (10,20)
SELECT ename,mgr FROM emp WHERE mgr in (7902,7566,7788)
-
查询员工姓名以W开头的员工姓名。
-
查询员工姓名倒数第2个字符为T的员工姓名。
-
查询奖金为空的员工姓名，奖金。
SELECT ename FROM emp WHERE ename LIKE “W%”
SELECT ename FROM emp WHERE ename LIKE “%T_”
SELECT ename,comm FROM emp WHERE comm is null
1.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资
SELECT ename,job,sal
FROM emp WHERE hiredate >2000AND job IN (“MANAGER”,”SALESMAN”)
-
-
查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
-
查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
-
查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
SELECT ename,hiredate,salSELECT ename,hiredate,salFROM emp WHERE deptno in (10,20)and sal BETWEEN 3000 and 5000
SELECT ename,hiredate,jobFROM emp WHERE hiredate LIKE “1981%”and job not like “SALES%”
SELECT ename,job,deptnoFROM empWHERE job in (“SALESMAN”,”MANAGER”)AND deptno IN (10,20)AND ename LIKE “%A%”AND ename LIKE “%A%”
1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。
SELECT ename,deptno FROM emp WHERE deptno IN (20,30) ORDER BY sal ASC
2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
SELECT ename,deptno,sal FROM emp WHERE sal BETWEEN 2000 AND 3000 AND deptno <> 10 ORDER BY deptno ASC,sal DESC
3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序
SELECT ename,hiredate,job FROM emp WHERE hiredate BETWEEN “1982-01-01” AND “1982-12-31”AND (job like “SALES%” or job like “MAN%”) ORDER BY hiredate DESC
1.查询入职日期最早的前5名员工姓名，入职日期
SELECT ename,hiredate FROM emp ORDER BY hiredate ASC LIMIT 0,5;
2.查询20号部门下入职日期最早的前2名员工姓名，入职日期。
SELECT ename,hiredate FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.deptno=20 ORDER BY hiredate ASC LIMIT 0,2;
3.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门编号 。
SELECT ename,hiredate,deptno FROM emp LIMIT 0,5
SELECT ename,hiredate,deptno FROM emp LIMIT 5,5
SELECT ename,hiredate,deptno FROM emp LIMIT 10,5
原文地址：https://www.cnblogs.com/Aegeansea666/archive/2022/07/18/16484379.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5005.html
