MySQL练习代码

SELECT* FROM dept;
SELECT* FROM dept WHERE deptno=2;
SELECT* FROM dept WHERE loc='二区';
SELECT loc FROM dept WHERE deptno<3;
SELECT dname FROM dept WHERE loc='二区' AND deptno=3;
SELECT dname FROM dept WHERE deptno=1 OR deptno=2;
SELECT dname FROM dept WHERE deptno IN (1,2); #效果同上
SELECT dname FROM dept WHERE loc='一区' OR deptno=3;  #userpassword;
#user表
UPDATE USER SET pwd='xyz' WHERE id=3;
UPDATE USER SET NAME='anglebaby' WHERE pwd=123 AND age=20;
DELETE FROM USER WHERE id=5;
#3.like模糊查询%是通配符（0~n个字符），_是通配符（1个字符）
#练习1：查询名字里以t开头的员工姓名和岗位
SELECT ename,job FROM emp WHERE ename LIKE 't%';
SELECT ename,empno FROM emp WHERE ename LIKE '%o%';
#4.null
SELECT ename,job FROM emp WHERE comm IS NULL;
SELECT ename,job FROM emp WHERE comm IS NOT NULL;
#4.between and区间范围
#练习14：查询工资在[5000~10000]员工姓名和入职日期
SELECT ename,hiredate FROM emp WHERE sal>=5000 AND sal<=10000;
SELECT ename,hiredate FROM emp WHERE sal BETWEEN 5000 AND 10000;
#练习15：查询在2019年入职的员工姓名
SELECT ename FROM emp WHERE hiredate BETWEEN '2019-1-1' AND '2019-12-31';
SELECT ename FROM emp WHERE YEAR(hiredate)=2019;
#5.limit分页  
#练习16：展示前两个员工的信息
SELECT* FROM emp LIMIT 3;#从第一条开始取，取3条
# 1,3从第1+1条开始取，取3条
SELECT* FROM emp LIMIT 1,3; 
#6.order by 字典顺序排序，默认升序，非要降序就写明DESC
#练习17：按照工资排序
SELECT* FROM emp ORDER BY sal;#升序
SELECT* FROM emp ORDER BY sal DESC;#降序
#练习18：按照名字排序
SELECT* FROM emp ORDER BY ename;#升序
SELECT* FROM emp ORDER BY ename DESC;#降序
#练习19：按照日期排序
SELECT* FROM emp ORDER BY hiredate;#升序
SELECT* FROM emp ORDER BY hiredate DESC;#降序
#练习20：按照岗位排序
SELECT* FROM emp ORDER BY job; #中文排序规则？？？
#练习21：查询最高薪的员工的名字和岗位
SELECT* FROM emp ORDER BY sal DESC LIMIT 1;#降序排序，取第一条即可~