mysql笔记之一DML
mysql笔记之一DMLDML 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;
比较运算（=:等于 (不是 ==);>:大于;>=:大于等于; 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, salary12 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 join on 右交集
select bt.name, bs.boyName from beauty bt left join boys bs on bt.boyfriend_id = bs.id;
内连接 join on 交集
select bt.name, bs.boyName from beauty bt inner join boys bs on bt.boyfriend_id = bs.id;
右外连接 right join on 左交集
select bt.name, bs.boyName from beauty bt right join boys bs on bt.boyfriend_id = bs.id;
其它特殊情况
SELECTFROM A LEFT JOIN B ON A.key=B.key WHERE B.key is null;
SELECTFROM A RIGHT JOIN B ON A.key=B.key WHERE A.key is null;
SELECTFROM A FULL JOIN B ON A.key=B.key;
SELECTFROM 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.1*salary
when '20' then 1.2*salary
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
);
单行子查询比较操作符
=等于; > 大于;>= 大于等于;< 小于; (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');
如果对您有所帮助请《点赞》、《收藏》、《转发》，您的支持是我持续更新的动力，有疑问请留言
</div>
文档来源：51CTO技术博客https://blog.51cto.com/u_13241097/2981588
页:
[1]