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;#降序排序,取第一条即可~

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值