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://img2022.cnblogs.com/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://img2022.cnblogs.com/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;

posted @ 2022-07-30 20:54 Tony_xiao 阅读(724) 评论(0) 编辑收藏举报

刷新页面返回顶部

Tony_xiao

MySQL基本操作(增删改查)

公告