脚本宝典收集整理的这篇文章主要介绍了mysql学习9.17，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

-- 创建表CREATE TABLE emp( eid INT, ename VArchAR(10), esex CHAR(5), birth DATE, sal DOubLE, comn DOUBLE, jixiao DOUBLE, did INT)

CREATE TABLE dept( did INT, dname VARCHAR(10), mgr VARCHAR(10))INSERT INTO dept VALUES(1,'开发部','tom');INSERT INTO dept VALUES(2,'测试部','jerry');INSERT INTO dept VALUES(3,'产品部','tony');

INSERT INTO emp VALUES(1,'海绵宝宝','男','1999-09-09',3000,500,200,1);INSERT INTO emp VALUES(2,'派大星','男','1988-03-09',4000,200,300,1);INSERT INTO emp VALUES(3,'珍妮','女','1986-12-03',6500,100,100,2);

-- 查询所有数据SELECT * From emp-- 查询部分字段SELECT ename,birth,sal,did From emp-- 查询时添加常量列SELECT ename AS e,birth AS b,sal,did FROM emp-- 查询每个员工的员工姓名和总工资SELECT ename,sal+comn+jixiao AS number FROM emp-- 查询每个员工的员工编号和（奖金加绩效）的平均数SELECT eid,(comn+jixiao)/2 AS num FROM emp-- 查询员工都有哪些性别SELECT DISTINCT esex FROM empSELECT DISTINCT(esex)FROM emp-- 查询每个女员工的工资SELECT sal FROM emp WHERE esex='女'-- 查询工资大于3000的员工姓名SELECT ename FROM emp WHERE sal>3000-- 查询1部门的工资大于3000的员工姓名和工资SELECT ename,sal FROM emp WHERE did=1 AND sal>3000-- 查询2部门或者工资小于3000的员工姓名和工资SELECT ename,sal FROM emp WHERE did=2 OR sal<3000-- 查询奖金不是200的员工编号和姓名SELECT eid,ename FROM emp WHERE comn<>200-- 查询总工资在3000到5000以内的员工姓名和总工资SELECT ename,sal+comn+jixiao AS num FROM emp WHERE (sal+comn+jixiao)>=3000 AND (sal+comn+jixiao)<=5000SELECT ename,sal+comn+jixiao AS num FROM emp WHERE sal+comn+jixiao BETWEEN 3000 AND 5000-- 新增数据INSERT INTO emp(eid,esex,birth,sal,comn,jixiao,did) VALUES(4,'女','2021-09-17',2000,500,200,3)INSERT INTO emp VALUES(5,'','女','2020-09-17',3000,500,200,3)-- 查询姓名不为空的员工信息SELECT * FROM emp WHERE ename <> ''SELECT * FROM emp WHERE ename IS NOT NULL AND ename <> ''

-- 模糊查询-- 查询姓海的员工信息SELECT * FROM emp WHERE ename LIKE '海%'-- 查询名字中带'大'字的员工信息SELECT * FROM emp WHERE ename LIKE '%大%'-- 查询名字为三个字的员工信息SELECT * FROM emp WHERE ename LIKE '___'-- 查询所有员工的奖金的总和SELECT SUM(comn) FROM emp-- 查询员工总工资SELECT SUM(sal+comn+jixiao) AS num FROM emp-- 查询所有员工的平均工资SELECT AVG(sal+comn+jixiao) AS num FROM emp-- 查询所有员工的最高绩效和最低奖金SELECT MAX(jixiao),MIN(comn) FROM emp-- 查询员工的总人数SELECT COUNT(*) FROM emp-- 查询女员工的人数SELECT COUNT(*) FROM emp WHERE esex='女'-- 分页查询 当前页 每页显示的条数-- 第一页 每页显示两条 limIT 起始行，每页显示的条数-- limit (当前页-1)*每页显示的条数SELECT * FROM emp LIMIT 0,2-- 第二页SELECT * FROM emp LIMIT 2,2-- 第三页SELECT * FROM emp LIMIT 4,2 -- 按照工资从大到小排序SELECT * FROM emp ORDER BY sal DESC-- 按照奖金从小到大排序INSERT INTO emp VALUES(6,'小红帽','女','2001-02-02',2000,150,150,3)-- 按照工资从大到小排，按照奖金从小到大排序SELECT * FROM emp ORDER BY sal DESC,comn ASC-- 查询男女（每个性别）的人数-- 按谁分组就查谁SELECT esex,COUNT(*) FROM emp group by esex-- 查询每个部门各多少人SELECT did,COUNT(*) FROM emp GROUP BY did-- 查询总人数大于2的性别SELECT esex FROM emp GROUP BY esex HAVING COUNT(*)>2-- 查询平均工资大于3000的部门SELECT did FROM emp GROUP BY did HAVING AVG(sal)>3000

脚本宝典总结

以上是脚本宝典为你收集整理的mysql学习9.17全部内容，希望文章能够帮你解决mysql学习9.17所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。