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;
比较运算（=:等于 (不是 ==);>:大于;>=:大于等于; 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 [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.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>