mysql 增删改查操作

#修改
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 字段

语法限制：

1. 一旦出现分组，那么select后边只允许出现统计函数以及分组字段
2. 统计函数可以单独使用
3. 如果出现统计函数的嵌套，那么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;