关于mysql我真的有好多话想多
比如mysql的事务机制啊,mysql的隔离等级的,mysql的索引机制啊,mysql的常用函数啊,mysql的六大范式啊,mysql的视图啊,mysql的触发器啊等等,太多了,今天我仅仅和大家分享一下mysql的三大语言类,,,中的一种,DML数据操作语言,增删改查。
先给两张表给大家做例子
给两张表是让大家知道我接下来的sql语句用的是哪些字段,更好的理解其中逻辑
1,增
往销售部增加一名员工
INSERT INTO t_emp(empno,ename,deptno) VALUES(8001,'妹子',(SELECT deptno FROM t_dept WHERE dname = 'SALES'));
因为部门表与员工表是外键关联的关系,也就是一对多的关系,所以在员工表插入部门这个字段的时候,使用了一个子查询
insert into 表名(字段1,字段2…) values(值1,值2…);
这种插入格式我们使用的太多了,其实呢,还有其他的插入语法
这里给大家介绍一下mysql的方言
insert into 表名 set 字段1=值1,字段2=值2…;
这个into可加可不加
insert 表名 set 字段1=值1,字段2=值2…;
这样也行
是不是感觉这样插入逻辑上面好理解很多哈哈
当我们有海量低价值数据需要插入的时候,我们可能会执行批量插入,这时候我们并不希望因为一两条损坏数据的插入失败而触发事务机制,导致全部的数据插入操作都被回滚,这时候我们仅需要在插入语句中添加一个参数
insert ignore into…
好了好了,插入就讲到这里了
2,改
把员工ALLEN调往RESEARCH部门,职位改为ANALYST,并把RESEARCH部门的地址改为北京
根据上面的表,这个语句该怎么写?这就得用到update语句的表连接咯
UPDATE t_emp e JOIN t_dept d SET e.deptno = d.deptno,e.job = 'ANALYST',d.loc='北京' WHERE e.ename = 'ALLEN' AND d.dname = 'RESEARCH';
表连接的表,也可以是查询出来的子表,当然,这里就不给例子了。。。。算了,还是给个例子吧:
把底薪低于公司平均底薪的员工,底薪增加150元:
UPDATE t_emp e,(SELECT AVG(sal) AS avg from t_emp) t SET e.sal = e.sal +150 WHERE e.sal <t.avg;
除了这里可以加子查询,set中也可以用子查询,where中也可以用子查询,子查询可以用到很多地方,你们可以多写写摸索摸索,但是向这里申明一下,子查询最好还是用在表连接这里,能在表连接用子查询解决的问题就不要用在where等地方,加入在where中使用子查询,每对一条数据进行条件筛选的时候,都会查询一次,会对性能造成严重的影响
同样,update也能用ignore
语法:
UPDATE [ignore] 表名 SET 字段1=值1,字段2=值2......[WHERE 条件...] [ORDER BY ...] [LIMIT.....];
3,删
删除也可以用ignore,
DELETE [ignore] FROM 表名 [WHERE 条件...] [ORDER BY ...] [LIMIT.....];
没错,删除也有表连接
给个例子,删除每个低于部门平均底薪的员工记录
DELETE e FROM t_emp e JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) AS t on e.deptno=t.deptno WHERE e.sal<t.sal;
当然,mysql中还有一个特别的删除操作,不在三界内,跳出五行中,delete操作在事务机制下删除时其实是把删除的文件先备份到日志文件中,面对海量数据时效率不高
truncate直接绕开事务机制,删除速度远超delete
比如删除一个表
truncate table 表名;
这个只能对单张表操作
那么删除也就到这里了
4,查
这个是最最最最最最最最重要的操作了,可以说是占据了我们日常业务中半壁江山,接下来就为大家带来几个查询的案例
题目1:找出工龄20年以上,年薪15000美金以上的员工
SELECT empno,ename FROM t_emp WHERE deptno = 10 AND (sal+IFNULL(comm,0))*12>15000 AND DATEDIFF(NOW(),hiredate)/365 >= 20;
题目2:查询公司工资最高的五位员工
SELECT empno,ename FROM t_emp ORDER BY sal DESC LIMIT 0,5;
题目3:查询每个部门的平均薪资
SELECT deptno,AVG(sal) FROM t_emp GROUP BY deptno;
题目4:查询每个部门里,每种职位的人数以及平均底薪
SELECT deptno,job,COUNT(*),ROUND(AVG(sal)) FROM t_emp GROUP BY deptno,job ORDER BY deptno;
题目5:查询每个部门的总人数,平均薪资,最高薪资,最低薪资,以及做一个汇总
SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal) FROM t_emp GROUP BY deptno WITH ROLLUP;
题目6:查询每个部门内,底薪超过2000的人数和员工人数
SELECT deptno,GROUP_CONCAT(name),COUNT(*) FROM t_emp WHERE sal>2000 GROUP BY deptno;
题目7:查询每个部门中的人数
SELECT deptno,COUNT(*) FROM t_emp GROUP BY 1 HAVING deptno in (10,20);
这个语法中,group by那个1指的是查询的第一个字段
having只能跟在group by后面使用,并且having中不能有字段之间的比较
我这个案例中having后面跟了一个deptno的筛选,其实是不建议这么做的,应该使用where去进行条件筛选的判断,因为where的执行顺序比group by高,先筛选后分组的效率比先分组后筛选要高很多
题目8:查询每个底薪超过部门平均底薪的员工信息
SELECT ename,empno FROM t_emp e JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) t ON e.deptno = t.deptno AND e.sal > t.avg;
题目9:查询每个部门的员工数以及部门名称
SELECT d.dname,COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno;
这是一个外连接查询,逻辑上面需要大家好好琢磨琢磨,为什么要用e.deptno,为什么要左关联员工表,因为查询人数这一块,肯定需要用员工表去查
题目10:查询每个部门员工人数以及部门名称,包括临时无部门员工人数
(SELECT d.dname,COUNT(*) FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno GROUP BY d.deptno)
UNION (SELECT d.dname,COUNT(e.deptno) FROM t_emp e RIGHT JOIN t_dept d ON e.deptno = d.deptno GROUP BY d.deptno);
这是一个union合并关键字,可以合并查询的结果集,不过需要注意的是,结果集的字段必须相同才可合并
上面这段代码的逻辑就是,上面一个会以员工表为主表,对于没有部门的员工数,也会在结果集显示出来,而下面一个会以部门表为主,对于一个没有员工的部门,在以员工表为主表的结果集是显示不出来的,而以部门表为主表的部门则会显示出来,所以合并两张表,获得一个完整的结果
题目11:查询员工的姓名以及部门名称
SELECT e.ename,(SELECT dname FROM t_dept WHERE deptno = e.deptno) FROM t_emp e;
在最后给出这个题就是来强调一下子查询,首先子查询会增加查询次数,在能不用的情况下最好不用,其次,在使用子查询的时候,子查询语句内是可以接收到外部的别名的,但是子查询内定义的别名外部是接收不到的,这个小点也会经常用到。
最后,在where条件判断的时候,有很多sql语句的关键字可以使用,增加方便度,如all,exists,any,in等等。。。算了,还是给大家再举一个例子吧
查询底薪比FORD和MARTIN都高的员工信息
SELECT e.ename FROM t_emp e WHERE sal > ALL(SELECT sal FROM t_emp WHERE ename in ('FORD','MARTIN'));
好了,增删改查的知识都分享到这里了,涉及到的知识都有些强调逻辑性,如果有不懂的地方还希望大家把sql语句的理解再加强一下,接下来有机会会和大家分享一下其他mysql的知识