DML

数据操作语言。 用于添加、删除、更新和查询数据库记录，并检查数据完整性

关键字包括 CREATE 、 DROP 、 ALTER 等

命令行登录mysql

  1. //登录
  2. mysql -hlocalhost -P3306 -u user -ppassword
  4. //连接本机可以省略部分
  5. mysql -u user -ppassword
  7. //打开指定库
  8. use 库名
  10. //查看表结构
  11. desc 表名;

DML操作

myemployees.sql

  1. //查询常量值
  2. select 100;
  3. select 'string';  //字符串形式
  5. //查询表达式
  6. selct 12*12;
  8. //查询函数
  9. select version();        
  10. //起别名
  11. select version() as 版本;            //使用as
  12. select version() 版本;                //使用空格
  13. select version() as "banben";        //名字是字符串 建议双引号
  15. //concat用于拼接字符串 
  16. //如果拼接的字段有为null值 拼接后输出的结果也是null
  17. select CONCAT(last_name,first_name) 姓名 from employees;

DISTINCT关键字

  1. select DISTINCT department_id from employees;

注：DISTINCT需要放到所有列名的前面，其实是对后面所有列名的组合进行去重

条件表达式筛选

  1. //条件表达式筛选  <>表示不等于
  2. > < = != <> >= <=            
  4. // <=> 安全等于符 可以判断null值 也可以判断普通数值
  5. SELECT last_name,commission_pct from employees 
  6. where commission_pct <=> null    
  9. //逻辑表达式筛选 
  10. and or not
  11. #查询部门编号不在90到110之间或者工资高于15000的员工信息
  12. SELECT * from employees 
  13. where not(department_id between 90 and 100) or salary>15000
  14. 注:OR可以和AND一起使用，AND的优先级高于OR
  17. //模糊查询
  18. like in (is null) (is not null) (between and)
  19. 注：IN列表中存在NULL，则结果为NULL
  20.     like如果给定的值或者匹配条件为NULL，则返回结果为NULL
  22. #查询员工名中包含字符ee的员工信息    like
  23. SELECT * from employees where last_name like "%ee%";    
  24. #查询员工名中第二字符是ee的员工信息
  25. SELECT * from employees where last_name like "_ee%";
  26. #查询员工名中第二字符是 _ 的员工信息
  27. SELECT * from employees where last_name like "_\_%";
  28. SELECT * from employees where last_name like "_$_%" ESCAPE '$';
  30. #查询部门编号在90到110员工信息        between and
  31. SELECT * from employees WHERE department_id BETWEEN 90 and 100
  33. #查询员工的工种编号是it_port,ad_vp,ad_pres中的一个的员工名和工种编号    IN
  34. SELECT last_name,job_id from employees 
  35. where job_id in("it_port","ad_vp","ad_pres");
  37. #查询没有奖金的员工名和奖金率     is null判断null值
  38. SELECT last_name,commission_pct from employees 
  39. where commission_pct is null        // = 不能用来判断null值

通配符

  • % 表示任意多个字符
  • _ 表示任意单个字符
  • \或ESCAPE 表示转义字符

排序

  1. #    order by [asc 升序|desc 降序]
  3. //查询员工信息，按工资从高到底排序  ORDER BY
  4. SELECT * from employees ORDER BY salary desc;        //支持按别名排序
  6. //查询员工信息，先按工资降序 再按id降序   
  7. SELECT * from employees 
  8. ORDER BY salary desc,employee_id desc;        //支持多个字段排序

分页查询

  1. // limit offest，size    (offest 要显示条目的起始索引;size 要显示的条目个数)
  3. //查询有奖金的员工信息 并且工资较高的前10名按降序显示
  4. select * from employees 
  5. where commission_pct is not NULL
  6. ORDER BY salary DESC
  7. LIMIT 10,15
  9. MySQL 8.0中可以使用"LIMIT 3 OFFSET 3" 表示取从第三条记录开始后的三条记录
  11. #实际运用 分页显示公式
  12. 要显示的页数 page，每页显示的条目数 size
  13. limit (page-1)*size,size;

注：LIMIT 子句必须放在整个SELECT语句的最后

多表查询

当查询的字段来源于多个表时，就会用到多表查询
微信图片_20220523224325.jpg

  1. //连接分类:
  2. //内连接:等值连接、非等值连接、自连接        （交集）
  3. //外连接:左外连接、右外连接、全外连接        （交集+自己有的部分）
  4. //交叉连接                                                （笛卡尔乘积）
  1. #等值连接             取多个表之间交集的部分
  2. //查询员工的名字和部门名字
  3. select last_name,department_name from employees e,departments d
  4. where e.department_id = d.department_id
  5. 注：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
  7. //查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
  8. select department_name,d.manager_id,MIN(salary)
  9. from departments d,employees e
  10. where d.department_id=e.department_id and commission_pct is not null 
  11. GROUP BY d.department_id,d.manager_id        
  12. 注：查询两个列 不能确定两个列是否一一对应时 要把两个列字段列入分组中
  14. #非等值连接
  15. //查询员工的工资和工资级别
  16. select salary,grade_level
  17. from employees e,job_grades j
  18. where e.salary BETWEEN lowest_sal AND highest_sal
  19. ORDER BY salary desc
  21. #自连接 表内自连
  22. //查询员工名和上级的名字
  23. select e.last_name,m.last_name
  24. from employees e,employees m
  25. where e.manager_id=m.employee_id

内连接

  1. sql99语法：
  2.     select 查询列表 
  3.     from 表1 别名 [连接类型]join 表2 别名          
  4.     on 连接条件            //多表连接多个join 和on
  5.     where 筛选条件
  7. #内连接
  8. #1 等值连接
  9. //查询员工名、部门名
  10. select last_name,department_name
  11. from employees e INNER JOIN departments d
  12. on e.department_id = d.department_id
  15. //查询部门个数>3的城市名和部门个数
  16. select city,COUNT(*)
  17. from locations l INNER JOIN departments d
  18. on l.location_id = d.location_id
  19. group by city
  20. HAVING COUNT(*)>3
  23. #2 自连接
  24. //查询员工名和上级的名字
  25. select e.last_name,m.last_name
  26. from employees e  JOIN employees m
  27. on e.employee_id = m.manager_id

外连接

  1. #外连接        用于一个表中有 另一个表没有的记录
  2. //外连接的查询结果为主表中所有的记录 内连接结果+主表有从表没有的记录
  3. //左外连接左边的是主表 右外连接右边的是主表
  4. //全外连接=内连接结果+表1中有表2中没有的+表2中有表1中没有的 
  5. MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替
  7. //查询哪个部门没有员工
  8. select d.*,e.employee_id
  9. from departments d 
  10. LEFT OUTER JOIN employees e                    //把主表中有 从表中没有的数据列出了
  11. on d.department_id=e.department_id        
  12. where e.employee_id is null            //在结果集上筛选

注：左外连接会以连接条件会基准，先把符合连接条件的数据筛选出来，再讲左表(基准表)的所有数据显示出来，如果左表有的数据，右表不存在满足连接条件的数据，则左表的数据依然展示，而右表的显示为null

注：超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引

交叉连接

  1. #笛卡尔乘积
  2. select b.*,bo.*
  3. from beauty b 
  4. CROSS JOIN boys bo

联合查询

要查询的结果来源于多个表时，且多个表之间没有直接的连接关系，但查询的信息一致时

注：要求多条查询语句的查询列数是一致的；
union会自动去重，如果不想去重，就添加关键字 union ALL; UNION ALL语句时所需要的资源比UNION语句少，如果表内不存在重复数据尽量用union all 

  1. select * from employees where email like"%a%"
  2. union 
  3. select * from employees where department_id>90

子查询

一个查询语句嵌套在另一个查询语句内部的查询

按结果集的行列数不同

  • 标量子查询：结果集只有一行一列（搭配> < <= >= = <>）
  • 列子查询：结果集只有一行多列（搭配 in any/some all）
  • 行子查询：结果集有一行多列或多行多列
  • 表子查询：结果集一般为多行多列
  1. #标量子查询        
  2. //查询工资大于abel的员工
  3. select * from employees
  4. where salary>(
  5.         select salary 
  6.         from employees
  7.         where last_name="Abel"        //此处select返回的是一行一列
  8. );
  10. #列子查询            一行多列
  11. //返回location_id是1400或1700的部门中所有员工姓名
  12. select last_name
  13. from employees
  14. where department_id in(
  15.         select DISTINCT department_id from departments where location_id in(1400,1700)
  16. )
  19. #行子查询
  20. //查询员工编号最小并且工资最高的员工消息
  21. select * from employees
  22. where (employee_id,salary)=(
  23.         select MIN(employee_id),MAX(salary) from employees
  24. )        //行子查询
  27. #from后面跟子查询
  28. //查询平均工资最低的部门
  29. select MIN(avg_s)
  30. from (
  31.         select department_id,AVG(salary) avg_s
  32.         from employees
  33.         GROUP BY department_id        //将子查询充当一张表来用
  34. ) ag_dep;                                        //必须起别名 不然找不到表

相关子查询

  1. #exists 相关子查询        查询需要的东西涉及到了主查询
  2. //查询有员工的部门名
  3. select department_name
  4. from departments d
  5. where EXISTS(
  6.             select * from employees e
  7.             where d.department_id=e.department_id
  8. )

单行函数

对外暴露方法名。隐藏细节，实现代码复用性

数学函数

  1. //round 四舍五入
  2. select ROUND(1.567);
  3. select ROUND(1.567,2);        //小数点后保留两位
  5. //ceil 向上取整
  6. select CEIL(1.5);
  8. //floor 向下取整
  9. select FLOOR(1.5);
  11. //truncate 截断
  12. select TRUNCATE(1.333,1);        //小数点后一位以后的数字不管
  14. //mod 取余
  15. select MOD(10,3);

字符函数

  1. //length 获取参数值的字节个数
  2. select length("abc");    
  4. //concat 拼接字符串
  5. select CONCAT(first_name,"_",last_name) 姓名 from employees;
  7. //UPPER，LOWER实现大小写转换
  8. select UPPER("name");
  9. select LOWER("NAME");
  11. //substr 截取函数
  12. select substr("abcde",1,3) letter;
  14. #姓名首字母大写其余小写，且之间用'_'连接
  15. select CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",        //函数间可以嵌套使用
  16.               LOWER(SUBSTR(last_name,2))) from employees;
  18. //instr 返回子串第一次出现的索引 找不到就返回0
  19. select INSTR("abcd","cd");
  21. //trim  去除左右空格 或指定的字符
  22. select TRIM("   abc   ");
  23. select TRIM('M' from "MMMabcMMM");
  25. //lpad 用指定的字符实现左填充到指定长度        
  26. select LPAD("abc",5,"*");
  27. select RPAD(LPAD("abc",5,"*"),8,"*");        //rpad 右填充
  29. //replace 替换指定的字符
  30. select REPLACE("abcdefg","abc","efg")

日期函数

  1. //返回当前日期和时间
  2. select NOW();
  4. //返回当前日期
  5. select CURDATE();
  7. //返回当前时间
  8. select CURTIME();
  10. //获取指定字段的年、月、日、分、秒
  11. select YEAR(NOW());        
  12. select YEAR(hiredate) from employees;        //函数内可以是字段
  14. //str_to_date 将字符通过指定的格式转换成日期
  15. select STR_TO_DATE(NOW(),"%Y-%m-%d");
  17. //date_foramat  将日期转换成字符
  18. select DATE_FORMAT(NOW(),"%y年%m月%d日")
  20. //datediff 返回两个日期之间相差的天数
  21. select DATEDIFF(NOW(),"1999-1-1");

流程控制函数

IF(value,value1,value2)： 如果value的值为TRUE，返回value1， 否则返回value2
IFNULL(args1,args2)：如果args1不是空返回args1，否则返回返回args2
NULLIF(args1,args2)：如果args1=args2返回null，否则返回返回args1

  1. //查看奖金率  
  2. select last_name,commission_pct,
  3. IF((commission_pct IS NULL),"无奖金","有奖金") 备注 
  4. from employees ORDER BY commission_pct DESC
  6. //IFNULL(expr1,expr2)判断字段是否为null 
  7. expr1是判断的字段，expr2是如果判断成立 要给判断字段赋予的值
  8. select IFNULL(commission_pct,0) 奖金率 from employees

CASE-WHEN

  1. //case后面跟参数
  2. select salary,department_id,
  3. CASE department_id                        //类似于switch 适合做等值判断
  4. WHEN 30 THEN salary*1.1
  5. WHEN 40 THEN salary*1.2
  6. WHEN 50 THEN salary*1.5
  7. ELSE    salary
  8. END  新工资 from employees;
  10. //case后面不跟参数
  11. select salary,
  12. CASE                                                 //类似于if else-if else
  13. WHEN salary>20000 THEN 'A'
  14. WHEN salary>15000 THEN 'B'
  15. WHEN salary>10000 THEN 'C'
  16. ELSE    'D'
  17. END  工资级别 from employees;

聚合函数

用作统计，搭配group by使用

  1. //总和
  2. select SUM(salary) from employees
  3. //平均
  4. select AVG(salary) from employees
  5. //最小
  6. select MIN(salary) from employees
  7. //最大
  8. select MAX(salary) from employees
  10. //保留小数点后两位的操作
  11. CAST(AVG(salary) as DECIMAL(10,2))

count函数

  • COUNT(*)返回表中记录总数，适用于任意数据类型
  • COUNT(expr) 返回expr不为空的记录总数

注：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行 

  2. //总数        count(1) 是计算一共有多少符合条件的行
  3. select COUNT(salary) from employees
  4. 注：以上分组函数都忽略了null值

count(*)，count(1)，count(列名)区别
对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb要去数一遍。但好于具体的count(列名)

分组查询

当执行group by时，查询语句中最好不要出现不相关的查询字段；select的字段不属于group by的字段的话，sql语句就会报错(一般配合聚合函数使用)
过滤分组：HAVING子句，不能单独使用，必须要跟 GROUP BY 一起使用（ 不能在 WHERE 子句中使用聚合函数） 

  1. //查询每个部门的平均工资
  2. select department_id,AVG(salary) from employees
  3. GROUP BY department_id;
  5. //查询有奖金的每个领导手下员工的最高工资
  6. select MAX(salary),manager_id from employees
  7. where commission_pct IS NOT null            //where 用于分组前筛选
  8. GROUP BY manager_id                                            原始表中要有分组筛选条件的字段
  10. //查询哪个部门的员工数>2    
  11. select COUNT(*) 人数,department_id from employees
  12. group by department_id
  13. HAVING COUNT(*)>2                            //HAVING 用于分组后的筛选 通常用在聚合函数上
  15. //查询每个部门每个工种的员工的平均工资
  16. select AVG(salary),manager_id,job_id
  17. from employees
  18. GROUP BY manager_id,job_id                //按多个字段分组

注：GROUP BY后可使用 WITH ROLLUP 关键字，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和（即统计记录数量 ）
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的

WHERE和HAVING的对比

  • WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件
    • WHERE 在 GROUP BY 之前执行，HAVING 在 GROUP BY 之 后
  • 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选
    • WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低

SELECT 的执行流程

FROM ->ON-> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
（select之前相当于对数据表进行行筛选，select字段是对列进行筛选）
在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个 骤中作为输入。这些步骤隐含在 SQL 的执行过程中，是不可见的

插入语句

  1. #为表的指定字段插入数据
  2. insert into 表名(列名,列名..) values(值1...)
  3. 或
  4. insert into 表名 set 列名=值,列名=值...
  6. //批量插入多条数据
  7. insert into 表名(列名,列名..)     values (值1...),(值2...),(值3...);
  9. //支持子查询
  10. insert into 表名(列名,列名...)
  11. select 值1,值2,值3... from 表名;

更新语句

  1. //单表修改        *
  2. update 表名
  3. set 列=值,列=值...
  4. where 筛选条件;
  6. //多表修改
  7. update 表1 别名
  8. inner|left|right join 表2 别名
  9. on 连接条件
  10. set 列=值,列=值...

删除语句

  1. delete from 表名 where 筛选条件;     *
  3. truncate table 表名;             //删除整个表

注：假如删除的表中有自增长列，使用delete删除后，再插入数据，自增长列的值从断点开始。（如：删除一行id=1的记录，再插入语句时，插入语句的id从2开始自增）
使用truncate删除，自增长列的值从1开始；
delete删除后有一个受影响行数的返回值，truncate没有；
#delete删除可以回滚，truncate删除不能回滚
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故