Mysql DQL讲解(二)以及DML

Mysql DQL讲解(二)以及DML

DQL

多表查询

1.笛卡尔积：避免笛卡尔积，因为笛卡尔积的产生会造成数据变多，处理效率下降。
2.自链接：相当于把一个数据表拆分成两个表链接使用，有利于树形菜单加载
FROM t_mysql_employees e,t_mysql_employees m

3.join链接
1.内连接：[inner] join on
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件


外连接：
应用场景：用于查询一个表中有，另一个表没有的记录
外连接查询结果=内连接结果+主表中有而从表没有的记录，可以显示null值
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
左外连接 left [outer] join on 右外连接 right [outer] join on
inner和outer可以省略，left左边表示主表，right右边表示主表。

多表查询的综合使用：
SELECT last_name,d.department_id,department_name
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id = d.department_id;

SELECT last_name , department_name , l.location_id , city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;

常见函数

概念：对列表类型值进行加工处理
1.字符函数
转小写：LOWER(‘SQL Course’)

转大写：UPPER(‘SQL Course’)
拼接：CONCAT(‘Hello’, ‘World’)

截取：SUBSTR(‘HelloWorld’,1,5)
长度：LENGTH(‘HelloWorld’)
字符出现索引值：INSTR(‘HelloWorld’, ‘W’)
字符截取后半段：TRIM(‘H’ FROM ‘HelloWorld’)
字符替换：REPLACE(‘abcd’,‘b’,‘m’)

2.数字函数
四舍五入：ROUND(45.926, 2)

截断：TRUNCATE(45.926, 2)

求余：MOD(1600, 300)

3.日期函数
获取当前日期：now()

将日期格式的字符转换成指定格式的日期：
STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’)

子查询

概念：出现在其他语句中的select语句，称为子查询或内查询，内部嵌套其他select语句的查询，称为外查询或主查询。
注意：需要仔细看清楚逻辑
例如：
SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE job_id = (
SELECT job_id
FROM t_mysql_employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM t_mysql_employees
WHERE employee_id = 143
);

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;

offset要显示条目的起始索引（起始索引从0开始），如果开始索引为0可以省略0
size 要显示的条目个数，limit语句放在查询语句的最后，原始查询的记录<offset，那么查询的结果必然是原始查询的记录数

DML(Data Manipulation Language)

概念：可以在表中删除，插入，修改数据。

1.插入

语法：INSERT INTO table [(column [, column…])] VALUES (value [, value…]);
在表中插入值：
INSERT INTO t_mysql_departments (department_id, department_name )
VALUES (30, ‘Purchasing’);
在指定列插入值：
INSERT INTO t_mysql_employees (employee_id, first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id,department_id)
VALUES (113, ‘Louis’, ‘Popp’, ‘LPOPP’, ‘515.124.4567’,
NOW(), ‘AC_ACCOUNT’, 6900, NULL, 205, 100);

拷贝数据：为了保护数据丢失，通常需要拷贝数据，不需要values语句，子查询中的值列表应与 INSERT 子句中的列名对应。

INSERT INTO emp2
SELECT *
FROM t_mysql_employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM t_mysql_employees
WHERE job_id LIKE ‘%REP%’;

2.修改

语法：UPDATE table SET column = value [, column = value, …]
[WHERE condition];
可以一次更新多条数据
例子：
UPDATE t_mysql_employees
SET department_id = 70
WHERE employee_id = 113;

UPDATE t_mysql_boys bo
RIGHT JOIN t_mysql_beauty b ON bo.id=b.boyfriend_id
SET b.boyfriend_id=2
WHERE bo.id IS NULL;

3.删除

语法：DELETE FROM table [WHERE condition];
例子：
DELETE FROM t_mysql_departments
WHERE department_name = ‘Finance’;

DELETE b
FROM t_mysql_beauty b
INNER JOIN t_mysql_boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName=‘张无忌’;

Delete 和 Truncate区别(面试可能考到)

1.delete 可以加where 条件，truncate不能加

2.truncate删除，效率高一丢丢

3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。

4.truncate删除没有返回值，delete删除有返回值（用Java操作的时候会用到）

5.truncate删除不能回滚，delete删除可以回滚.（回滚可以使数据返回）