2023_10_09_MYSQL_DAY_01_笔记

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