2023_10_09_MYSQL_DAY_01
#运算符的优先级
SELECT ename, job, sal
FROM emp
WHERE ( job='SALESMAN'
OR job='PRESIDENT')
AND sal>1500;
SELECT ename, job, sal
FROM emp
WHERE job='SALESMAN'
OR job='PRESIDENT'
AND sal>1500;
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL;
SELECT ename,sal
FROM emp
WHERE sal NOT BETWEEN 3000 AND 5000;
NOT BETWEEN .. AND .. :不在某个区间
NOT IN (集合）：不在某个集合内
NOT LIKE ：不像.....
IS NOT NULL: 不是空
#课堂练习11
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
#课堂练习10
SELECT *
FROM emp
WHERE ename LIKE '%T_';
#课堂练习9
SELECT *
FROM emp
WHERE ename LIKE 'W%';
# IS NULL
SELECT ename,mgr
FROM emp
WHERE mgr IS NULL;
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
#Like运算符
SELECT ename
FROM emp
WHERE ename LIKE 'S%';
SELECT ename
FROM emp
WHERE ename LIKE 'S_';
SELECT ename
FROM emp
WHERE ename LIKE '%A%';
SELECT ename
FROM emp
WHERE ename LIKE '__A%';
#课堂练习8
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 3000 AND 5000;
#课堂练习7
SELECT ename,hiredate
FROM emp
WHERE hiredate BETWEEN '1982-1-1' AND '1985-12-31';
#IN运算符
select empno,ename,deptno
from emp
where deptno in (10,20);
#使用BETWEEN .. AND.. 可以查询出某列的值在某个范围内(包括边界值)的数据行
SELECT empno,ename,sal
FROM emp
WHERE sal BETWEEN 1250 AND 1600;
#课堂练习6
SELECT empno,ename,deptno
FROM emp
WHERE deptno <> 10;
SELECT empno,ename,deptno
FROM emp
WHERE deptno != 10;
#课堂练习5
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate < '1985-12-31';
#课堂练习4
SELECT empno,ename,job
FROM emp
WHERE job='SALESMAN';
#带条件查询2 非等值情况
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate > '1985-01-01';
#带条件查询2
SELECT empno,ename,job
FROM emp
WHERE job='CLERK';
SELECT empno,ename,job
FROM emp
WHERE job="CLERK";
#带条件查询1
SELECT empno,ename,deptno
FROM emp
WHERE deptno=20;
#课堂练习3
SELECT DISTINCT job FROM emp
#排除重复数据的查询
SELECT DISTINCT deptno FROM emp
#课堂练习2
SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+comm 总收入 FROM emp;
#课堂练习1
SELECT ename,sal 涨薪前,sal*1.2 涨薪后 FROM emp;
#空值参与算术运算，运算后的结果仍为NULL ==待解决?
SELECT ename,sal,comm,sal+comm 月总收入 FROM emp
#数值类型的字段可以做数学运算
SELECT empno,ename,sal,sal*12 年薪 FROM emp
#按字段别名查询 单双引号的使用
SELECT empno 编号,ename "姓 名",sal '工 资',comm "奖,金" FROM emp
#按字段别名查询
SELECT empno 编号,ename 姓名,sal 工资,comm 奖金 FROM emp
#查询指定字段
SELECT empno,ename,sal,comm FROM emp
#查询所有字段
SELECT * FROM emp