MYSQL 操作语法

1，查看表的语句

SELECT *FROM t_student
-- 查看表 t_student

2，对表中数据的增，删，改

-- 在t_student中增加数据

SELECT *FROM t_student
-- 查看表

-- 全字段插入
INSERT INTO t_student VALUES (1,'张三','男',18,'2022/5/8','网络工程','123@.com');
INSERT INTO t_student VALUES (2,'张四','男',19,'2023/5/8','软件工程','456@.com');

-- 不是全字段插入，要给出字段的名字
INSERT INTO t_student(snumb,sname,sex) VALUES  (3,'李四','女');

-- 修改表中的数据
-- 修改时要增加限制条件，否则修改全表数据
UPDATE t_student set sex ='女';
UPDATE t_student set sex ='男' where snumb=3;

-- 删除操作

-- 删除学号为3的学生的信息
DELETE FROM t_student WHERE snumb=3;

3，对数据库中的表进行操作

-- 查看数据
SELECT *FROM t_student;

-- 增加表的结构；

-- 增加一列；

ALTER TABLE t_student add score double(5,2);
--增加一列score，类型为double型
 
-- 删除一列操作
ALTER TABLE t_student drop score;
-- 增加一行，放在最前面的位置
ALTER TABLE t_student add score double(5,2) first;
 
-- 增加一列bbb，放在sex之后
ALTER TABLE t_student add bbb double(5,2) AFTER sex;
 
-- 修改列的类型的定义，不会改变列的名字
ALTER TABLE t_student MODIFY bbb FLOAT(4,1);

-- 修改列名（修改列名为bbb1）和类型
ALTER TABLE t_student CHANGE bbb bbb1 DOUBLE(5,2);
 
-- 删除整个表
DROP TABLE t_student;

4，对单个表的查询

-- 列出工资小于2000的职位
SELECT job,min(sal)
FROM emp
group by job
having min(sal)<2000;

-- 列出平均工资大于1200的部门和工作搭配的组合
SELECT deptno,job,avg(sal)
from emp
GROUP BY deptno,job
having avg(sal)>1200
ORDER BY deptno;

-- 统计[人数小于4的]部门的平均工资
SELECT deptno,count(1),avg(sal)
from emp
group by deptno
having count(1)<4;

-- 统计各个部门的最高工资，排除最高工资小于3000 的部门
SELECT deptno,max(sal)
FROM emp
GROUP BY deptno
having max(sal)<3000;

5，where子句的使用

SELECT *FROM emp;

-- 显示empno列
SELECT empno FROM emp;


SELECT *FROM emp WHERE sal >2000;

SELECT empno '员工编号',ename '姓名' FROM emp;
-- empno显示为员工编号，ename显示为姓名

-- 算术运算符
SELECT empno,ename,sal,sal+1000,deptno from emp WHERE sal<2500;
SELECT empno,ename,sal,comm,sal+comm FROM emp;

-- 去掉重复
SELECT DISTINCT job from emp;
SELECT job,deptno from emp;

--增加的distinct 对于job 和deptno 都起作用
SELECT distinct job,deptno from emp;

-- 排序
-- 按照工资进行排序
SELECT *FROM emp ORDER BY sal; -- 默认升序排列的
SELECT *FROM emp ORDER BY sal asc; -- 升序
SELECT *FROM emp ORDER BY sal desc; -- 降序排列

-- 在 sal 升序的排列下，deptno 降序排列
SELECT *FROM emp ORDER BY sal asc,deptno desc; 

SELECT *FROM emp;

-- WHERE 子句,将过滤条件放在where子句的后面
-- 默认情况下不区分大小写,命令也不区分大小写

SELECT *FROM emp WHERE deptno=10;
SELECT *FROM emp WHERE deptno!=10;
 
-- 不等于10的,不等于10还可以用"<>"表示
SELECT *FROM emp WHERE deptno>=10;
SELECT *FROM emp WHERE deptno<=10;

-- WHERE 子句 可以添加逻辑运算符
SELECT *FROM emp WHERE sal > 1500 and sal < 3000;
SELECT *FROM emp WHERE sal > 1500 && sal < 3000;-- 不包括1500和3000
SELECT *FROM emp WHERE sal BETWEEN 1500 and 3000;-- 包括1500和3000

SELECT *FROM emp WHERE sal > 1500 OR sal < 3000;
SELECT *FROM emp WHERE sal > 1500 || sal < 3000;

-- WHERE子句+模糊查询；
-- 查询名字中间有A的员工
SELECT *FROM emp WHERE ename like '%A%'; -- % 代表任意多个字符
-- 查询第二个字母为 A 的
SELECT *FROM emp WHERE ename like '_A%'; -- _ 代表任意一个字符

-- 关于null的查询
SELECT *FROM emp WHERE comm is NULL;
SELECT *FROM emp WHERE comm is not NULL;

-- 关于小括号() 的使用
-- 可读性，可以改变优先级

6，函数

SELECT empno,ename,lower(ename),upper(ename),sal FROM emp;
-- 函数的功能是封装了特定的一些功能，直接进行使用
-- 作用是为了提高select 的工作效率
-- 注意：函数并没有改变原来的数据，仅仅是将加工后的数据显示出来

SELECT max(sal),min(sal),count(sal),sum(sal),avg(sal) FROM emp;

-- 函数的分类：
-- 单行函数：对一条数据进行计算
-- 多行函数：对一组数据进行计算(只有上面的五种)

-- 单行函数
-- 1，字符串函数
SELECT ename,length(ename),substring(ename,2,3) FROM emp;
-- substring():大写 2：从下标为2开始 3：长度为三个字符

-- 2,数值函数
SELECT abs(-5),ceil(5.3),floor(5.9) FROM dual; -- dual 实际上就是一个伪表

-- 3，日期与时间函数
SELECT *FROM emp;
SELECT curdate(),curtime();
SELECT now(),sysdate(),sleep(3),now(),sysdate() FROM dual;
-- now(),sysdate()显示年月日时分秒

-- 4，流程函数
SELECT empno,ename,sal,if(sal>2500,'高','低') as '薪资等级' FROM emp;
SELECT empno,ename,sal,comm,sal+ifnull(comm,0) FROM emp;
-- 如果comm的取值为null的话，那么取值为0；
SELECT NULLIF(1,1),NULLIF(1,2) FROM dual;
-- NULLIF(a,b) 若a，b的值相同返回null，不相同返回第一个数字；
-- case等值判断
SELECT empno,ename,job,
CASE job
when 'CLERK' then '店员'
when'MANAGER' then '经理'
else '其他'
end,
sal FROM emp;
-- case 区间判断
SELECT empno,ename,sal,
CASE 
when sal<=1000 then 'A'
when sal<=2000 then 'B'
else 'C'
end,
deptno FROM emp;

-- 5，其他函数
SELECT DATABASE(),user(),VERSION() from dual;
-- VERSION() 查看当前MySQL 的版本号


-- 多行函数
SELECT max(sal),min(sal),count(sal),sum(sal),avg(sal) FROM emp;
-- 多行函数自动忽略null的值
-- max(),min(),count()针对所有类型有效，sum(),ayg()只针对数值类型有效

-- count() 用来计数
SELECT count(*) FROM emp; -- 推荐
SELECT count(ename) FROM emp;
SELECT count(1) FROM emp;

7，having 的使用

SELECT *FROM emp;
-- 统计平均工资
-- 字段和多行函数不同时使用

SELECT deptno,avg(sal) from emp group by deptno;
-- 字段和多行函数不同时使用,除非这个字段属于分组
SELECT deptno,avg(sal) from emp group by deptno desc;


-- 统计各个岗位的平均工资
SELECT job,avg(sal) FROM emp GROUP BY job;
SELECT job,lower(job),avg(sal) FROM emp GROUP BY job;
-- lower() 将所有大写字母转化为小写

-- 分组之后进行二次筛选（只显示平均工资大于2000的）
SELECT deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
SELECT deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资>2000;
SELECT deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资>2000 ORDER BY deptno desc;

-- 各个岗位的工资，除了MANAGER
SELECT job,avg(sal) FROM emp where job != 'MANAGER' GROUP BY job;
SELECT job,avg(sal) FROM emp GROUP BY job having job != 'MANAGER';

-- where 在分组前进行过滤
-- having 在分组后进行过滤

8，多个表的查询

-- 查询员工的编号，姓名，部门编号：
SELECT *FROM emp;
SELECT empno,ename,deptno from emp;

-- 查询员工的编号，姓名，部门编号和部门名称：
SELECT *FROM emp
SELECT *FROM dept;

-- 多表查询
SELECT *FROM emp
cross JOIN dept; -- 交叉连接查询

-- 自然连接：
SELECT *
FROM emp
NATURAL join dept;  -- 同名列只展示一次

-- 指定表名的查询
SELECT emp.EMPNO,emp.ename,emp.SAL,dept.dname,dept.LOC
FROM emp
NATURAL join dept;

-- 自然连接缺点：自动匹配所有的同名列
-- 有些时候，我们只希望匹配部分的同名列
-- 解决 ：using 子句
SELECT*
FROM emp e  -- 给emp一个别名e，便于以后的书写，下同
inner join dept d -- inner 可以省略
using(deptno) -- 这里的using 是inner 的一个内连接

-- 缺点：using 关联的字段必须是同名的
-- 解决：内连接 -on 子句(不要求关联的字段必须是同名的)
SELECT*
FROM emp e
INNER JOIN dept d 
on (e.DEPTNO=d.DEPTNO)-- 使用最为广泛
where sal>3500;

-- 条件：
-- 1，筛选条件 where having
-- 2，连接条件 on，using，natural



-- 多表连接查询的类型
-- 内连接 on使用最为广泛


-- 外连接:除了显示匹配的连接，还可以显示不匹配的数据

SELECT*
FROM emp e
INNER JOIN dept d 
on e.DEPTNO=d.DEPTNO;

SELECT *FROM emp;
SELECT *FROM dept;

-- 左外连接：left outer join ,其中 outer 可以省略
-- 左边这个表的信息即使不匹配，也可以查看出效果

SELECT*
FROM emp e
LEFT OUTER JOIN dept d
on e.DEPTNO=d.DEPTNO;

-- 右外连接：right outer join ,其中 outer 可以省略
-- 右边这个表的信息即使不匹配，也可以查看出效果

SELECT*
FROM emp e
RIGHT OUTER JOIN dept d
on e.DEPTNO=d.DEPTNO;

-- mysql 中不支持全外连接
-- 将左外连接和右外连接求并集，可以实现全外连接
SELECT*
FROM emp e
LEFT OUTER JOIN dept d
on e.DEPTNO=d.DEPTNO
-- union -- 求并集 
-- union all -- 求并集并且不去重 高效率

SELECT*
FROM emp e
RIGHT OUTER JOIN dept d
on e.DEPTNO=d.DEPTNO;

-- mysql中只支持并集，不支持其他集合的运算

-- 三表查询:将前面的两张表看作一个整体
SELECT *FROM emp;
SELECT *FROM dept;
SELECT *FROM salgrade;

SELECT e.ENAME,e.sal,e.empno,e.deptno,d.dname,s.*
FROM emp e 
right outer join dept d
on e.deptno = d.deptno
inner join salgrade s;

-- 75条数据，大多数数据是错误的

SELECT e.ename,e.sal,e.empno,e.deptno,d.dname,s.*
FROM emp e 
right outer join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal BETWEEN s.losal and s.hisal;

-- 多表查询，将前面的 n-1 张表看作是一个整体

-- 自连接,自己和自己的连接
SELECT *FROM emp;

SELECT e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导姓名,e2.ename 领导姓名
FROM emp e1
left outer JOIN emp e2
on e1.mgr=e2.empno;

9，子查询

-- 子查询

-- 不相关子查询
-- 两条命令完成一个任务
-- 第二个命令依靠第一条命令，先进行内层，再进行外层
-- 内层命令可以独立执行


-- 单行子查询
-- 查询所有比'tom'工资高的员工的信息
SELECT sal FROM emp WHERE ename = 'tom';
SELECT *FROM emp where sal > 2000;


SELECT *FROM emp where sal >(SELECT sal FROM emp WHERE ename = 'tom');
-- 效率高
-- 先执行子查询，在进行外部查询
-- 其中内部可以独立运行，称为不相关子查询

SELECT ename,sal FROM emp
WHERE sal>(SELECT avg(sal) from emp);


SELECT ename,sal FROM emp
where deptno=(SELECT deptno from emp WHERE ename='tom') 
and sal<(SELECT sal from emp WHERE ename= 'tom');


-- 多行子查询
-- 子查询的结果是多行

SELECT *FROM emp 
where DEPTNO = 20
and job in (SELECT job from emp where deptno = 10);
-- 或者可以使用下面的语句

SELECT *FROM emp 
where DEPTNO = 20
and job = any(SELECT job from emp where deptno = 10);

SELECT empno,ename,sal FROM emp
SELECT sal FROM emp where job = 'tom'
-- 查询工资高于 tom 的员工编号，姓名，工资信息
SELECT empno,ename,sal 
FROM emp
where sal > (SELECT max(sal) FROM emp where job = 'tom');

-- 相关子查询
-- 子查询不可以独立运行,先执行外层，再执行内层
-- 内层循环的进行需要外层循环的数据


-- 查询本部门最高工资的员工(不相关子查询实现)
SELECT *FROM emp where DEPTNO = 10 and sal = (SELECT max(sal) FROM emp where deptno = 10)
union
SELECT *FROM emp where DEPTNO = 20 and sal = (SELECT max(sal) FROM emp where deptno = 20)
union
SELECT *FROM emp where DEPTNO = 30 and sal = (SELECT max(sal) FROM emp where deptno = 30)

-- 缺点，语句比较多，分为多少个部分未知

SELECT *FROM emp  e where sal = (SELECT max(sal) FROM emp where deptno = e.deptno) ORDER BY DEPTNO

-- 相关子查询,内层数据需要先计算外层数据

-- 查询工资高于其所在岗位的平均工资的员工

SELECT *FROM emp e WHERE sal >=(SELECT avg(sal) FROM emp WHERE job=e.job)