MySql之DQL语言补充及DML语言

一.DQL(数据查询语言)

1.多表查询

分类：
等值连接：两个表都有的字段
非等值连接：两个相关联的的字段连接
自连接：把一个表分成两个表来查询所需要的数据
外连接：用于查询一个表中有，另一个表没有的记录

join关键字
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
-使用ON 子句创建连接

join 语法：

select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】

1.等值连接：

#案例1.查询员工名、部门名

SELECT last_name,department_name
FROM t_mysql_departments d
 JOIN  t_mysql_employees e
ON e.`department_id` = d.`department_id`;

2.非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM t_mysql_employees e
 JOIN t_mysql_job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

3.自连接

`#查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM t_mysql_employees e
 JOIN t_mysql_employees m
 ON e.`manager_id`= m.`employee_id`;

4.外连接

#案例1：查询哪个部门没有员工
 #左外
 SELECT d.*,e.employee_id
 FROM t_mysql_departments d
 LEFT OUTER JOIN t_mysql_employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 #右外
 
  SELECT d.*,e.employee_id
 FROM t_mysql_employees e
 RIGHT OUTER JOIN t_mysql_departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 #全外
 
 
 USE girls;
 SELECT b.*,bo.*
 FROM t_mysql_beauty b
 FULL OUTER JOIN t_mysql_boys bo
 ON b.`boyfriend_id` = bo.id;

2.常见函数

字符函数

select LOWER('SQL Course')//转小写
select UPPER('SQL Course')//转大写
select CONCAT('Hello', 'World')//拼接
select SUBSTR('HelloWorld',1,5)//截取
select LENGTH('HelloWorld')//长度
select INSTR('HelloWorld', 'W')//字符出现索引值
select TRIM('H' FROM 'HelloWorld')//字符截取后半段
select REPLACE('abcd','b','m')//字符替换

数字函数

select ROUND(45.926, 2)//四舍五入
select TRUNC(45.926, 2)//截断
select MOD(1600, 300)//求余

日期函数

select now()//获取系统当前时间
select STR_TO_DATE('9-13-1999','%m-%d-%Y')//将日期格式的字符转换成指定格式的日期
select DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)//将日期转换成字符

3.子查询

概念：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

语法:SELECT 查询列段 FROM 表名 WHERE 过滤条件 (SELECT 查询列段 FROM 表名 WHERE 过滤条件 )

分类：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

案例：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资
SELECT  MIN(salary)
FROM t_mysql_employees
WHERE department_id = 50
#②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM t_mysql_employees
	WHERE department_id = 50
);

4.分页查询

概念：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

案例：有奖金的员工信息，并且工资较高的前10名显示出来

SELECT 
    * 
FROM
    t_mysql_employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

二.DML(数据管理语言)

概念：

– 向表中插入数据
– 修改现存数据
– 删除现存数据

1.新增

语法：INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

案例：为每一列添加一个新值。

INSERT INTO t_mysql_departments(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);//字符和日期型数据应包含在单引号中

案例：从其它表中拷贝/备份数据

新建备份表：

create table t_mysql_departments_bak//新建用来存放备份数据的表
as
select * from t_mysql_departments_bak //需要备份的表
where 1=2;

拷贝数据：不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应

insert into t_mysql_departments_bak//需要写入的表
select * from t_mysql_departments;//拷贝的数据源
//如果只需拷贝部分数据则加上where过滤
//where 过滤条件；

2.修改

语法：UPDATE table SET column = value [, column = value, ...] [WHERE condition];

案例：一次修改多表的数据

//修改张无忌的女朋友的手机号为114，魅力值改为1000；
update t_mysql_boys bo //需要修改的表
inner join t_mysql_beauty b //内连接表
on bo.id = b.boyfriend_id//连接条件
set b.phone = 114,bo.userCP = 1000//需要修改的值
where bo.boyName = '张无忌'；//过滤条件

3.删除

delete pk truncate
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
一般常使用delete

语法：DELETE FROM t_mysql_departments WHERE department_name = 'Finance';
如果省略where不写则删除当前表所有数据

案例：一次删除多表的数据

//案例：删除张无忌的女朋友的信息

delete b	//连接两个表后删除b表中过滤后名字为张无忌的的信息
from t_mysql_beauty b 
inner t_mysql_boys bo
on b.boyfriend_id = bo.id
where bo.boyname = '张无忌'；

以上语法操作基于win10及MySQL8