#修改
UPDATE bonus SET sal=5000;
#添加
INSERT INTO bonus VALUES("ALLEN","SALESMAN",3000,900);
INSERT INTO bonus VALUES("WARD","SALESMAN",4500,1200);
#删除
DELETE FROM bonus WHERE ename="ALLE";
UPDATE bonus SET comm=2000 WHERE job="clerk";
#查找
SELECT*FROM bonus;
SELECT ename,job FROM emp;
#查询所有并起别名
SELECT empno '员工编号',ename AS '员工姓名'FROM emp WHERE sal=1300
#查询并去重
SELECT DISTINCT job FROM emp;
#带条件查询
SELECT * FROM emp WHERE sal >=1000 AND sal<3000; //并且
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000; //区间内
SELECT * FROM emp WHERE sal >=2500 OR sal<1500; //或者
SELECT * FROM emp WHERE comm IS NULL OR comm = 0; //判断非空
SELECT * FROM emp WHERE comm IS NOT NULL; //判断不为空
模糊匹配:
%:占位符 代表0个或者多个字符
_:占位符 代表一个字符
查询员工姓名中带有S的员工信息
SELECT * from emp where ename like '%S%';
排序查询
格式:select 字段名 from 表名 where 条件 order by 排序依据字段
升序 asc(默认))
降序 desc
SELECT*FROM t_student ORDER BY "age" DESC;
SELECT*FROM emp WHERE comm>0 ORDER BY sal DESC,hiredate ASC;
SELECT ename,job,sal FROM emp ORDER BY sal;
多表联查:
格式:select 表名1/别名1.字段名1,表名2/别名2.字段名2 from 表名1 别名1,表名2 别名2 where 关联条件
SELECT*FROM emp,dept WHERE emp.deptno = dept.deptno;
SELECT e.empno,e.ename,e.job,e.sal,p.dname,p.loc FROM emp e,dept p WHERE e.deptno = p.deptno;
连接查询
Select*from 表名 【连接查询】【限定条件】【排序规则】
左连接(以左表为主,左表所有信息都显示,右表为辅助,符合左表的信息才显示,否则不显示)
SELECT e1.empno,e1.ename,e1.job,e2.ename,e2.job FROM emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.empno; //可以将同一张表当作两张表查询
右连接:
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno
分组查询
分组前提:需要分组的列出现了重复数据
SELECT * FROM 表名称 [连接查询][限定条件][分组查询][排序条件] Group by 字段 |
语法限制:
- 一旦出现分组,那么select后边只允许出现统计函数以及分组字段
- 统计函数可以单独使用
- 如果出现统计函数的嵌套,那么select后边只允许出现统计函数
#求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select emp.deptno,dept.dname,avg(sal) from emp,dept where emp.deptno = dept.deptno group by emp.deptno,dept.dname;
#通过右连接将没有展示的数据进行展示:
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname;
#展示平均工资高于2000的部门信息
#错误写法:
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname where avg(sal) > 2000;
#解决办法:
#如果在分组之后需要再次筛选,不可以使用where,需要使用Having
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname having avg(sal) > 2000;
HAVING 关键字和 WHERE 关键字都可以用来过滤数据,且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异:
1.一般情况下,WHERE 用于过滤数据行,而 HAVING 用于过滤分组。
2.WHERE 查询条件中不可以使用聚合函数,而 HAVING 查询条件中可以使用聚合函数。
3.WHERE 在数据分组前进行过滤,而 HAVING 在数据分组后进行过滤 。
4.WHERE 针对数据库文件进行过滤,而 HAVING 针对查询结果进行过滤。也就是说,WHERE 根据数据表中的字段直接进行过滤,而 HAVING 是根据前面已经查询出的字段进行过滤。
5.WHERE 查询条件中不可以使用字段别名,而 HAVING 查询条件中可以使用字段别名。
单行函数查询
在MySQL中,单行函数是一种对单个输入值执行操作的函数。它接受一个输入参数并返回一个值。单行函数可以用于处理数据,并在查询中进行数据转换、计算、日期和时间处理等操作。
以下是MySQL中常见的单行函数示例:
字符串函数:
CONCAT:连接两个或多个字符串。
SUBSTRING:返回字符串的子串。
LENGTH:返回字符串的长度。
LOWER:将字符串转换为小写。
UPPER:将字符串转换为大写。
数值函数:
ABS:返回数值的绝对值。
CEIL:返回大于或等于给定数值的最小整数。
FLOOR:返回小于或等于给定数值的最大整数。
RAND:返回一个随机数。
日期和时间函数:
CURDATE:返回当前日期。
CURTIME:返回当前时间。
NOW:返回当前日期和时间。
DATE_FORMAT:按照指定格式格式化日期或时间。
条件函数:
IF:根据条件返回不同的值。
CASE:根据条件执行不同的操作。
其他函数:
MD5:计算字符串的MD5哈希值。
SHA1:计算字符串的SHA1哈希值。
REVERSE:反转字符串的顺序。
TRIM:删除字符串开头和结尾的空格或指定字符。
子查询
Where子查询 单行单列 多行单列
Form子查询 多行多列
#查询比SMITH工资高的人
select * from emp where sal > (select sal from emp where ename = 'SMITH');
#查询比平均工资高的人
select * from emp where sal > (select avg(sal) from emp);
#查询和经理工资相同的员工信息
select * from emp where sal in (select sal from emp where job = 'MANAGER');
查询部门编号,部门位置,部门人数,部门平均工资
第一步:查询每个部门的编号,名称,位置
Dept d
第二步:统计部门人数,平均工资
emp e
e.deptno deptno, count(e.empno) num,avg(e.sal) sal
Group by e.deptno
select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno;
#将查到的数据和dept组合
select temp.deptno,temp.num,temp.sal,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp,dept d where temp.deptno = d.deptno;
#通过右连接处理边界数据
select d.deptno,IFNULL(temp.num,0) 部门人数 ,IFNULL(temp.sal,0) 平均工资 ,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp right join dept d on temp.deptno = d.deptno;
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
分页查询
LIMIT 关键字 参数 参数1为索引值从哪开始,第二个参数为每页显示几个
SELECT * FROM product LIMIT 30;// 从0开始,显示30条
SELECT * FROM product LIMIT 10,10;//从10开始 显示10条
由此,我们可以分析出分页查询的SQL语句格式
select * from my_book limit (page-1)*pageSize,pageSize;
其中,page是页码,pageSize是每页显示的条数
当数据库中数据量较大时,使用limit函数会存在性能问题,我们可以用主键来代替limit函数实现数据分页查询
select * from my_book where id > (page-1)*pageSize limit pageSize;
select * from my_book where id > 10 limit 5;