MySql笔记之 DML

mysql笔记之一DML

DML DML用于查询与修改数据记录，包括如下SQL语句：

SELECT/select：选择（查询）数据

INSERT/insert：添加数据到数据库中

UPDATE/update：修改数据库中的数据

DELETE/delete：删除数据库中的数据

DML—select

select–基础

基本查询

select * from employees;

列查询

select employee_id, first_name from employees;

使用别名

select employee_id as employee, first_name as name from employees;

select employee_id employee, first_name name from employees;

过滤—where

select * from employees where department_id = 90;

比较运算（=:等于 (不是 ==);>:大于;>=:大于等于;<:小于; <=:小于、等于; <>:不等于 (也可以是 !=)）

select * from employees where salary > 10000;

其它比较运算

BETWEEN…AND… 在两个值之间 (包含边界)

select * from employees where salary between 5000 and 10000;
IN(set) 等于值列表中的一个

select * from employees where department_id in(90, 100);

LIKE 模糊查询

select * from employees where first_name like’%s%';

IS NULL 空值

select * from employees where manager_id is null;

逻辑运算（AND 逻辑并;OR 逻辑或;NOT 逻辑否）

select * from employees where salary > 10000 and manager_id is null;

select * from employees where salary > 10000 OR manager_id is null;

select * from employees where department_id not in(90,50,100);

使用ordey by子句排序,默认升序(asc（ascend）: 升序;desc（descend）: 降序)

select * from employees order by salary asc;

select * from employees order by salary desc;

按照别名排序

select employee_id, salary*12 salaryyear from employees order by salaryyear asc;

多列排序（先判定第一个列）

select * from employees order by department_id,salary asc;

select–组函数

组函数类型avg()平均count()计数max()最大min最小sum()总和

select avg(salary), max(salary), min(salary), sum(salary), count(salary) from employees;

select–分组函数

单列分组：部门平均薪资

select department_id, avg(salary) from employees group by department_id;

多列分组：部门岗位平均薪资

select department_id, job_id, avg(salary) from employees group by department_id, job_id;

select–过滤分组

having子句------过滤部门最高薪资大于10000的

select department_id, max(salary) from employees group by department_id having max(salary) > 10000;

select–多表查询

数据量相乘

select name, boyName from beauty, boys;

id相同，等值连接

select beauty.name, boys.boyName from beauty, boys where beauty.id = boys.id;

使用别名

select bt.name, bs.boyName from beauty bt, boys bs where bt.id = bs.id;

连接多个表（需要n-1个连接条件）

on子句创建多表连接（关联条件department_id、location_id）

select employee_id, last_name, department_name, city from employees e join departments d on d.department_id = e.department_id join locations l on l.location_id = d.location_id;

左外连接 left [outer] join on 右交集

select bt.name, bs.boyName from beauty bt left join boys bs on bt.boyfriend_id = bs.id;

内连接 [inner] join on 交集

select bt.name, bs.boyName from beauty bt inner join boys bs on bt.boyfriend_id = bs.id;

右外连接 right [outer] join on 左交集

select bt.name, bs.boyName from beauty bt right join boys bs on bt.boyfriend_id = bs.id;

其它特殊情况

SELECT FROM A LEFT JOIN B ON A.key=B.key WHERE B.key is null;

SELECT FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key is null;

SELECT FROM A FULL JOIN B ON A.key=B.key;

SELECT FROM A FULL JOIN B ON A.key=B.key WHERE A.key is null OR B.key is null;

select–常用函数

字符函数

lower() 小写控制函数 upper()大写控制函数

select lower(e.first_name) from employees e;

select upper(e.first_name) from employees e;

concat() 字符连接函数

select concat(e.first_name, e.last_name) from employees e;

substr() 截取指定字符

select substr(e.first_name, 1, 4) from employees e;

instr() 查询指定字符位置

select instr(e.first_name, ‘s’) from employees e;

length() 统计字符长度函数

select length(e.first_name) from employees e;

lpad() 显示10位不足前补*

select lpad(e.first_name, 10, ‘*’) from employees e;

rpad() 显示10位不足后补*

select rpad(e.first_name, 10, ‘*’) from employees e;

trim() 移除指定首尾 或者 默认不指定移除首尾空格

select trim(‘N’ from e.first_name) from employees e;

select trim(e.first_name) from employees e;

replace() 替换指定字符 示例：wq 替换字符 e

select replace(e.first_name, ‘e’, ‘wq’) from employees e;

数学函数

round()四舍五入 保存小数点后两位

select round(45.33445, 2)

truncate()截断 保存十位

select truncate(45.33445, -1)

mod()求余 除示例余3

select mod(45, 7)

日期函数

now() 获取当前时间

select now();

str_to_date: 将日期格式的字符转换成指定格式的日期

select str_to_date(‘2021/6/25 16:05:30’, ‘%Y/%m/%d %H:%i:%s’);

date_format:将日期转换成字符

select date_format(hiredate, ‘%Y年%m月%d日 %H时%i分%s秒’) from employees e;

备注：

%Y 四位的年份
%y 2位的年份
%m 月份（01,02…11,12)
%c 月份（1,2,…11,12）
%d 日（01,02,…）
%H 小时（24小时制)
%h 小时（12小时制)
%i 分钟（00,01…59)
%s 秒（00,01,…59）

其他函数【补充】

流程控制函数【补充】

条件表达式if-then-else

case表达式使用

示例：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印 其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印 其工资的 1.3 倍数

select first_name, department_id, salary,
case department_id when ‘10’ then 1.1salary
when ‘20’ then 1.2salary
when ‘30’ then 1.3*salary
else salary end ‘revised_salary’
from employees;

select–子查询

DML—insert

insert插入一条数据(要求主键之前不存在)

insert into departments(department_id, department_name, manager_id, location_id)
values(280, ‘pur’, 199, 1000);

向表中插入空值

insert into departments(department_id, department_name, manager_id, location_id)
values(280, ‘pur’, NULL, NULL);

DML—update

update–更新数据

set autocommit = false; 配置可回滚数据

update departments set department_id = 290 where location_id =1000;

DML—delete

delete–删除数据

delete from departments where department_id = 290;

DML—子查询

子查询–查询表departments中location_id = 1700的department_id中员工first_name 的值

select first_name
from employees
where department_id in (
select department_id
from departments
where location_id = 1700
);

单行子查询比较操作符

= 等于; > 大于;>= 大于等于;< 小于;<= 小于等于;<> 不等于

单行子查询 查询薪资大于Abel的人

select last_name
from employees
where salary >
(select salary
from employees
where last_name = ‘Abel’);

多行子查询比较操作符

IN/NOT IN 等于列表中的任意一个;ANY|SOME 和子查询返回的某一个值比较;ALL 和子查询返回的所有值比较

多行子查询

小于某一个

select employee_id, last_name,job_id, salary
from employees
where salary < any
(select salary
from employees
where job_id = ‘IT_PROG’);

小于任意一个

select employee_id, last_name,job_id, salary
from employees
where salary < all
(select salary
from employees
where job_id = ‘IT_PROG’);