MySQL数据库学习笔记二：MySQL的常用操作小练习，内含干货！

本文链接：https://blog.csdn.net/weixin_42322406/article/details/99891950
CREATE TABLE EMP
(EMPNO INT,      ##职工编号
	ENAME VARCHAR(10),   #职工名称
	JOB VARCHAR(9),   #职工职位
	MGR INT,          #上级领导编号
	HIREDATE DATE,    #入职日期
	SAL DOUBLE(7,2),  #工资
	COMM DOUBLE(7,2),   #奖金
	DEPTNO INT         #部门编号
);

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-2-4',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-1-5',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-9-6',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-6-13',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-8-9',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-6-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-3-12',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-3-12',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-1-2',1300,NULL,10);

#查询表中所有的数据
SELECT * FROM EMP;
#查询emp表中ename,job,sal几列,其中sal列在结果集中以别名salary查询
SELECT ENAME,JOB,SAL salary FROM EMP;
#查询emp表中deptno,ename,年工资,并将年工资命名别名total salary
SELECT EMPNO,ENAME,(SAL+IFNULL(comm,0))*12 'total salary' FROM emp;
#查看emp表的结构
SELECT * FROM emp WHERE 1!=1;
#查询emp表中雇员号,雇员的名字,工作岗位,入职日期
SELECT EMPNO,ENAME,JOB,HIREDATE DATE FROM emp;
#查询员工表中部门编号,重复的部门编号只查询一次  DISTINCT 用来去除重复数据
SELECT DISTINCT DEPTNO FROM emp;
#查询员工表所有数据 
SELECT * FROM emp;
#在结果集中雇员名和工作岗位相连用"空格逗号空格"分隔,并将列头查询为Employee and Title
#SELECT ENAME + '	' +JOB FROM emp;这样是不行的，不能这样做字符串的连接
#字符串的连接应该用concat
SELECT CONCAT(ENAME,' ',JOB) 'Employee and Title' from emp;
#查询职位(JOB)为'PRESIDENT'的员工的工资
SELECT SAL FROM emp WHERE JOB='PRESIDENT';
#查询佣金(COMM)为0 或为NULL 的员工信息
select * FROM emp WHERE COMM=0 or COMM is NULL;
-- #查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息
SELECT * FROM emp WHERE HIREDATE  BETWEEN '1981-5-1' and '1981-12-31';
SELECT * FROM emp WHERE hiredate < '1981-12-31' AND hiredate >= '1981-5-1';
#显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息
SELECT * FROM emp WHERE DEPTNO=10 and JOB='MANAGER' or DEPTNO=20 and JOB='CLERK';
#显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
SELECT * FROM emp WHERE ENAME not LIKE '%L%' or ENAME LIKE '%SM%';
#显示各个部门经理('MANAGER')的工资
SELECT SAL FROM emp WHERE JOB='MANAGER';
#查询所有名字长度为4 的员工的员工编号,姓名
SELECT * FROM emp WHERE LENGTH(ename)=4;
#显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
SELECT * FROM emp WHERE IFNULL(comm,0)> SAL;
#为所有薪水小于1000的员工涨薪%20
UPDATE emp SET SAL=SAL+SAL*0.2 WHERE SAL <1000;
#把'ALLEN'的直接领导人修改为'KING'
UPDATE emp set MGR='7839' WHERE ENAME='ALLEN';
#选择部门30中的雇员 
SELECT * FROM emp WHERE DEPTNO=30;
#列出所有职员(CLERK)的姓名、编号和部门 
SELECT ENAME,EMPNO,DEPTNO FROM emp WHERE JOB='CLERK';
#找出奖金高于薪金的雇员 
SELECT *FROM emp where IFNULL(comm,0)>SAL;
#找出奖金高于薪金60%的雇员 
SELECT*FROM emp WHERE comm>sal*0.6;
#找出部门10中所有经理和部门20中的所有职员的详细资料 
SELECT * FROM emp WHERE DEPTNO=10 and JOB='MANAGER' or DEPTNO=20 and JOB='CLERK';
#找出部门10中所有经理、部门20中所有职员，以及既不是经理又不是职员但其薪金>=2000的所有雇员的详细资料 
SELECT * FROM emp WHERE DEPTNO=10 and JOB='MANAGER' or DEPTNO=20 and JOB='CLERK' or (JOB != 'MANAGER' and JOB != 'CLERK' and SAL>=2000);
#找出收取奖金的雇员的不同工作职位 每种职位显示一次 
SELECT DISTINCT job from emp WHERE comm is not null;
#找出不收取奖金或收取的奖金低于100的雇员 
SELECT * FROM emp WHERE COMM=0 or comm<=100;
#显示不带有'R'的雇员姓名 
SELECT * FROM emp WHERE ENAME not LIKE '%R%' and job='CLERK';
#显示雇员的详细资料，按姓名排序 buxie
#显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序 buxie
#显示在一个月为30天的情况下所有雇员的日薪金
SELECT (SAL+COMM)/30 FROM emp
#显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
SELECT * FROM emp WHERE ENAME LIKE '%A%';
#查询二月份入职的所有员工信息
SELECT * FROM emp WHERE MONTH(hiredate) = '02';
SELECT * from emp where DATE_FORMAT(hiredate,'%m')='02';
#查询入职到现在为止超过38年的所有员工信息
 SELECT * FROM emp WHERE DATE_ADD(hiredate,INTERVAL 38 year)<CURDATE();
 
 #排序 在where条件后添加 ORDER BY 排序列 ASC 升序 DESC 降序
 #根据职工的工资由低到高进行排序
 SELECT * FROM emp ORDER BY SAL ASC;#升序排列 ASC
 SELECT * FROM emp ORDER BY SAL DESC;#降序排列 DESC
 #多列排序，如果第一列的字段值相同，则可以参考第二个列进行排序(第一个列是主排序字段，第二个列是次排序字段)
 SELECT * FROM emp ORDER BY SAL DESC,IFNULL(COMM,0)ASC;
 
 #统计函数，count()统计符合条件的个数，sum()汇总，avg()求平均数，max()求最大值，min()求最小值
 #统计函数一般用于统计纯数字类型，日期类型。统计函数会忽略null值
 #在查询语句中，如果使用了统计函数，则在查询的过程中不能出现其他列的信息，除非按其他列分组
 #查询最高的工资和姓名（统计函数只能出现一个值，一个最高工资有可能对应多个名字）
 #SELECT ename,MAX(sal) FROM emp;
 
 #获取emp表中一共存在多少条记录
 SELECT COUNT(*) FROM emp;
 #一般常用count（1）做统计，而不使用列名做统计，统计函数会忽略null值
 SELECT COUNT(1) FROM emp;
 SELECT count(empno) FROM emp;#14
 SELECT COUNT(comm) FROM emp;#4 忽略null
 #统计所有员工的一个月总工资
 SELECT SUM(sal) zgz FROM emp;
 SELECT SUM(sal+IFNULL(comm,0)) zgz FROM emp;
 #统计10部门员工的总工资
 SELECT SUM(sal+IFNULL(comm,0)) zgz FROM emp WHERE DEPTNO = 10;
 #统计全公司的所有员工的最高工资
 SELECT MAX(sal+IFNULL(comm,0)) maxzgz FROM emp;
 #统计 30部门 职位是SALESMAN 的员工的最高工
 SELECT MAX(sal+IFNULL(comm,0)) maxzgz FROM emp WHERE DEPTNO=30 and job = 'SALESMAN';
 #统计年级最小的人的年龄
 SELECT min(hiredate) FROM emp;
 #求平均数
 SELECT AVG(sal) FROM emp;
 #截取小数
 SELECT TRUNCATE(AVG(sal),2) FROM emp;
 #截取小数，正数表示保留几位小数，负数表示按哪一位取整
 SELECT TRUNCATE(113.456455654,2);
 SELECT TRUNCATE(113.456455654,-2);