MySQL基本操作(增删改查)

• 1、数据库插入数据(增)

  • 语法结构：INSERT INTO 表名(列名 1，列名 2……) VALUES (值 1，值 2……)；
  • 语法解析：
    • 列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
    • 值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
  • 插入多行内容
    • insert into 表名（列1，列2….） values (值1，值2….),(值3，值4….)；

• 2、数据库删除数据

  • 语法结构：delete from 表名 where 条件；
  • truncate操作，删除全部数据!!无法回滚，高危！
    • truncate table 表名

• 3、更新数据库

  • 语法结构：update 表名 set 列名1=值,列名2=值 where 条件;

• 4、对于测试来说，最重要的是查询功能

  • 基本查询：

SELECT *|列名|表达式 
FROM 表名 
WHERE 条件 
ORDER BY 列名

- 语法解析：
  - * 表示表中的所有列。
  - 列名可以选择若干个表中的列名，各个列名中间用逗号分隔。
  - 表达式可以是列名、函数、常数等组成的表达式。
  - WHERE 子句是查询的条件。
  - ORDER BY 要求在查询的结果中排序，默认是ASC(升序), DESC(降序)。

• 操作符
  • 算数运算：+ – * /
  • 关系运算：= > < != <> >= <=
  • 逻辑运算：and、or、not
  • 字符串连接操作符 ||

select e.ename,e.sal,e.sal+100,e.sal*100,e.sal/100 
from emp e;

select e.ename, e.sal 
from emp e 
where e.sal = 2500;

select e.ename, e.sal 
from emp e 
where not e.sal = 2500;

select e.ename, e.sal, '我爱' || e.ename || ',带上工资:' || e.sal || "来表白" 
from emp e
where e.sal >= 2500
order by e.sal desc;

• null操作
  • 空值不等于0或空格，空值就是未赋值、未知、不可用的值

select sal,comm 
from emp 
where comm is null;

select sal,comm 
from emp 
where comm is not null;

• in操作
  • where子句中可以使用in操作符来查询其列值在指定的列表中的行
  • 对应in操作的还有 not in，用法一样，结果取反

select ename,sal,job,comm 
from emp 
where job in ('CLERK','MANAGER');

• between…and…
  • where子句中查询列值包含在指定区间内的行

select * 
from emp 
where sal>=1000 and sal <=2000;

• like模糊查询
  • 字符匹配操作中的通配符“%”和“_”:
    • %：表示零个或者多个任意字符。
    • _：代表一个任意字符。
    • 
• 集合运算：就是将两个或者多个结果集组合成为一个结果集。
  • intersect(交集)： 返回两个查询共同的记录。
  • union(并集)： 返回各个查询的所有记录，不包括重复记录。
  • union all(并集)： 返回各个查询的所有记录，包括重复记录。
  • minus(补集)： 返回第一个查询出的记录减去第二个查出的记录之后剩余的记录。
• 连接查询：交叉连接、内连接、外连接：左外连接、右外连接
  • 交叉连接(笛卡儿积)

select *
from emp,dept;

- 内连接
  -  在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
     - select e.ename,e.deptno,d.dname
     - from emp e,dept d
     - where e.deptno = d.deptno;
- 外连接
  - 左外连接：希望自己全部显示的，就从自己表中查，左连接对方表；
  - 右外连接：希望自己全部显示的，从对方表中查，右连接自己表；
  - 满外连接：所有的都显示。

select d.deptno,d.dname,e.empno,e.sal
from dept d
left outer join emp e 
on e.deptno=d.deptno;

select d.deptno,d.dname,e.empno,e.sal
from emp e 
right outer join dept d
on e.deptno=d.deptno;

select d.deptno,d.dname,e.empno,e.sal
from emp e 
full join dept d
on e.deptno=d.deptno;

• 子查询
  • 在select、update、delete语句内部可以出现select语句。
  • 内部的select语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表.
  • ANY子查询
    • <any：小于最高的

    • any：大于最小的

    • any 放在比较运算符后面，表示“任意”的意思
  • all子查询

    • all：大于最高的

    • <all：小于最低的

select e.ename,e.job,e.sal
from emp e
where e.deptno = (
    select deptno 
    from dept 
    where dname = 'SALES'
);

#筛选sal<1600的员工 
select e.ename,e.job,e.sal
from emp e
where sal < 1600;
--
select e.ename,e.job,e.sal
from emp e
where sal < any (
    select sal 
    from emp 
    where job = 'SALESMAN'
);

#筛选sal>1600的员工 
select e.ename,e.job,e.sal
from emp e
where sal > 1600;

select e.ename,e.job,e.sal
from emp e
where sal > all (
    select sal 
    from emp 
    where job = 'SALESMAN'
);

• 函数
  • 字符函数
    • 

select concat('Hello', ' world') from dual;

select instr('Hello world', 'or') from dual;

select length('Hello') from dual;

select lower('hElLO') from dual;

select upper('hello') from dual;

select initcap(e.ename) from emp e;

select ltrim('===HELLO===', '=') from dual;

select '==' || ltrim(' HELLO===') from dual;

select rtrim('===HELLO===', '=') from dual;

select '=' || trim(' HELLO ') || '=' from dual;

select trim('=' from '===HELLO===') from dual;

select replace('ABCDE', 'CD', 'AAA') from dual;

select substr('ABCDE', 2) from dual;

select substr('ABCDE', 2, 3) from dual;

- 数字函数
  - ![](https://www.icode9.com/i/l/?n=22&i=blog/1544134/202207/1544134-20220730204231240-1784705338.png)
  - 说明：
    ROUND(X[,Y])，四舍五入。
    在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。
    y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。
    y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

    TRUNC(x[,y])，直接截取，不四舍五入。
    在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。
    y 是正整数，就是截取到小数点后 y 位。TRUNC (5.654,2)=5.65。
    y 是负整数，截取到小数点左边|y|位。TRUNC (351.654,-2)=300。

- 常用单行函数
  - NVL(x,value)： 如果 x 为空，返回 value，否则返回 x。
    - 例如：对薪水是 2000 元以下的员工，如果没有发奖金，每人奖金 100 元
    - select ename, job, sal, nvl(comm, 100) from emp where sal < 2000;
  - NVL2(x,value1,value2)： 如果 x 非空，返回 value1，否则返回 value2。
    - 例如：对 EMP 表中薪水为 2000 元以下的员工，如果没有奖金，则奖金为 200 元，如果有奖金，则在原来的奖金基础上加 100 元
    - select ename, job, sal, nvl2(comm, comm + 100, 200) "comm" from emp where sal < 2000;

- 聚合函数
  - ![](https://www.icode9.com/i/l/?n=22&i=blog/1544134/202207/1544134-20220730204609417-488086181.png)
  - 正常情况下，聚合函数都是搭配着GROUP BY来使用的
  - 分组后，再带条件查询需要使用 having 
    - 例如：求出除了部门20之外的，部门平均薪水大于1500的部门。

select deptno, avg(sal)
from emp
where deptno != 20
group by deptno
having avg(sal) > 1500;