mysql实践知识学习

上边分别是 员工表emp，工资等级表salgrade，部门表dept

简单查询

1.查询员工的月薪
SELECT ename,sal from emp

2.查询全部字段
SELECT * from emp

条件查询

1查询工资等于5000的员工姓名
SELECT ename,sal FROM emp where sal =5000

2.查询姓名是Smith的员工工资
SELECT ename,sal from emp where ename = “smith”

3.查询工资高（小于等于）于3000的员工和工资
SELECT ename,sal from emp where sal >= 3000

4.找出工资在1100和3000之间的员工
SELECT ename,sal from emp where sal >=1100 and sal <=3000
SELECT ename,sal from emp where sal BETWEEN 1100 and 3000

5.找出哪些人没有经贴
SELECT ename,comm from emp where comm is null

6.找出哪些人有经贴
SELECT ename,comm from emp where comm is not null

7.找出哪些人没有经贴或者为0
SELECT ename,comm from emp where comm is null || comm =0;

8.找出工作岗位是manager和salesman的员工
SELECT ename,job from emp where job = ‘manager’ || JOB = ‘salesman’
SELECT ename,job from emp where job in (“manager”,“salesman”)

9.找出薪资大于3000的并且部门编号是20或者30的员工（注意括号的使用）
SELECT ename,sal,deptno from emp where sal >=3000 AND (deptno = “20” or deptno = “30”)

10.找出名字中含有o的
SELECT ename from emp where ename like ‘%o%’

11.找出名字中第二个字母是A的
SELECT ename FROM emp where ename like ‘_A%’

12.找出名字中最后一个字母是T的
SELECT ename FROM emp where ename like ‘%T’

13.按照工资升序，找出员工名和薪资 (DESC降序 ASC升序)
SELECT ename,sal from emp ORDER BY sal DESC

14.按照工资的降序排列，当工资相同再按照名字的升序排序
SELECT ename,sal from emp ORDER BY sal desc,ename asc

15.找出工作岗位是salesman的员工，并且要求按照薪资的降序排列
SELECT ename,sal,job from emp where job = “salesman” ORDER BY sal desc

分组函数 是对某一组数据进行操作，先分组再函数.

null，分组函数自动忽略null，有null参与的运算结果都为null

count（*）不是统计某个字段数据的个数，而是统计总记录条数.count（comm）统计某个字段的个数

分组函数不能用在where只后

count（comm）统计某个字段的个数
1.找出工资总和
SELECT SUM(sal) from emp

2.最高工资
SELECT MAX(sal) from emp

3.最低工资
SELECT MIN(sal) from emp

4.总人数
SELECT COUNT(*) from emp

5.找出工资高于平均工资的员工
SELECT ename,sal from emp WHERE sal > (SELECT AVG(sal) from emp);

6.找出每个工作岗位的最高薪资
SELECT job,MAX(sal) from emp GROUP BY JOB

7.每个工作岗位的平均工资
SELECT job,AVG(sal) from emp GROUP BY JOB

8.找出每个部门不同工作岗位的最高薪资
SELECT DEPTNO,JOB,MAX(sal) from emp GROUP BY DEPTNO,JOB

9.找出每个部门的最高薪资，要求显示薪资大于2500的
SELECT DEPTNO,job,MAX(sal) FROM emp where SAL > 2500 GROUP BY deptno

10.找出每个部门的平均薪资，要求显示薪资大于2000的
SELECT deptno,AVG(sal) FROM emp GROUP BY DEPTNO HAVING AVG(sal)>2000

11.统计岗位的数量
SELECT COUNT(DISTINCT JOB) FROM emp

内连接

等值连接

1.找出每一个员工的部门名称，要求显示员工名和部门名（笛卡尔积现象）
SELECT e.ename,d.dname from emp e,dept d
这条语句会产生笛卡尔积现象，每一个ename(14个员工)都会和dname(4个部门) 进行匹配 总共会产生56条记录
SELECT e.ename,d.dname from emp e,dept d where e.DEPTNO = d.DEPTNO
这样 就不会产生笛卡尔积现象了，每一个员工对应一个部门，总共14条记录
但是，虽然结果只有14条，但是依然还是匹配了56次，只不过把不符合的给过滤掉了，不会减少匹配次数

2.查询每个员工的部门名称，要求显示员工名和部门名
SELECT e.ename,d.dname from emp e,dept d where e.DEPTNO = d.DEPTNO
SELECT e.ename,d.dname from emp e JOIN dept d on e.DEPTNO = d.DEPTNO

！等值连接

找出每个员工的工资等级，要求显示员工名，工资，工资等级
SELECT e.ename,e.sal,s.grade from emp e,salgrade s where e.sal BETWEEN s.LOSAL AND s.HISAL
SELECT e.ename,e.sal,s.grade from emp e JOIN salgrade s on e.SAL BETWEEN s.LOSAL AND s.HISAL

自连接(自己连接自己)

找出每个员工的上级领导，要求显示员工名和对应的领导名
SELECT a.ename,b.ename FROM emp a,emp b where a.MGR = b.EMPNO
SELECT a.ename,b.ename FROM emp a join emp b on a.MGR = b.EMPNO

左(右)外连接

外连接解决的问题，一个部门表，一个员工表。员工属于部门，假如一个部门没有员工，外连接会自动添加为null 而不是让此部门丢失

1.找出每个员工的上级领导
SELECT a.ename,b.ename from emp a left join emp b on a.MGR = b.EMPNO

2.找出哪个部门没有员工
SELECT d.dname,e.ename from dept d left JOIN emp e on d.DEPTNO = e.DEPTNO
上边这条语句找出了有员工和没有员工的，根据题目我们加个过滤
SELECT d.dname,e.ename from dept d left JOIN emp e on d.DEPTNO = e.DEPTNO where e.ename is null

3张表连接查询

3.找出每一个员工的部门名称以及工资等级
SELECT e.ename,d.dname,s.grade
from emp e
JOIN dept d
ON e.DEPTNO = d.DEPTNO
JOIN salgrade s
ON e.SAL
BETWEEN s.LOSAL and s.HISAL；

4.找出工作岗位是salesman和manager的员工
SELECT ename,job FROM emp where JOB in (“salesman”,“manager”)

5.取出工资前5的员工
SELECT ename,sal from emp ORDER BY sal desc LIMIT 0,5

6.找出工资排名再4-9的员工
SELECT ename,sal FROM emp ORDER BY sal desc LIMIT 3,6

创建表

老师表
create table teacher(
tno int PRIMARY KEY,
tname VARCHAR(255)
);

学生表
create table student(
sno int PRIMARY KEY,
sname VARCHAR(255),
sex char(1),
tno INT
FOREIGN KEY(tno) REFERENCES teacher(tno)
);

1.向老师表添加老师
INSERT into teacher(tno,tname) VALUES (1,“张三”)
INSERT into teacher(tno,tname) VALUES (2,“lisi”)

2.向学生表添加学生
INSERT into student(sno,sname,sex,tno) VALUES (1,“小明”,“男”,1);
INSERT into student(sno,sname,sex,tno) VALUES (2,“小张”,“女”,2);
INSERT into student(sno,sname,sex,tno) VALUES (3,“小红”,“女”,1);
INSERT into student(sno,sname,sex,tno) VALUES (4,“小小”,“男”,2);

3.修改
UPDATE teacher set tname = “zhaoliu” where tno = 1
UPDATE student set sname = “xiaoxiao”,tno = 2 where sno = 1

4.删除
DELETE FROM student where sno = 3