MySQL基本操作2

多表查询

1.自连接
案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;

2、join链接

分类

– 内连接 [inner] join on
– 外连接
	• 左外连接 left [outer] join on
	• 右外连接 right [outer] join on

使用ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
#女生对应的男朋友（以左为主表，主表全显示）

```sql
SELECT
*
FROM
t_mysql_beauty bt
LEFT JOIN t_mysql_boys b ON b.id=bt.boyfriend_id

以右为主

SELECT
*
FROM
t_mysql_beauty bt
RIGHT JOIN t_mysql_boys b ON b.id=bt.boyfriend_id

#案例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;

常见函数

主要是针对列段类型值进行后期加工
1、字符函数
作用 函数 结果
转小写 LOWER(‘SQL Course’) sql course
转大写 UPPER(‘SQL Course’) SQL COURSE
拼接 CONCAT(‘Hello’, ‘World’) HelloWorld
截取 SUBSTR(‘HelloWorld’,1,5) Hello
长度 LENGTH(‘HelloWorld’) 10
字符出现索引值 INSTR(‘HelloWorld’, ‘W’) 6
字符截取后半段 TRIM(‘H’ FROM ‘HelloWorld’) elloWorld
字符替换 REPLACE(‘abcd’,‘b’,‘m’) amcd

SELECT
	LOWER( 'sql mysql' ),
	REPLACE ( 'abcd', 'b', 'm' ),
	SUBSTR( 'helloworld', 1, 5 ),
	INSTR( 'helloworld','l' ),
	TRIM( 'e' FROM 'helloworld' );

2、数字函数
作用 函数 结果
四舍五入 ROUND(45.926, 2) 45.93
截断 TRUNC(45.926, 2) 45.92
求余 MOD(1600, 300) 100

SELECT
	ROUND(466.13,2),
	mod(582,24);

3、日期函数
作用 函数 结果
获取当前日期 now()
将日期格式的字符转换成指定格式的日期 STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13
将日期转换成字符 DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) 2018年06月06日

SELECT 
DATE_FORMAT(b.releaseDate,'%Y年%m月')str,
count(*)
FROM
t_p1_blog b
GROUP BY
str;

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

< >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
#案例1：谁的工资比 Abel 高?

#①查询Abel的工资

SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'

#②查询员工的信息，满足 salary>①结果

SELECT *
FROM t_mysql_employees
WHERE salary>(

	SELECT salary
	FROM t_mysql_employees
	WHERE last_name = 'Abel'

);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

#①查询141号员工的job_id

SELECT job_id
FROM t_mysql_employees
WHERE employee_id = 141

#②查询143号员工的salary

SELECT salary
FROM t_mysql_employees
WHERE employee_id = 143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

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

);

#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询公司的 最低工资

SELECT MIN(salary)
FROM t_mysql_employees

#②查询last_name,job_id和salary，要求salary=①

SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE salary=(
	SELECT MIN(salary)
	FROM t_mysql_employees
);

#案例4：查询最低工资大于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
);

分页查询

语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
从0开始，可以省略0 如： limit 0,5=limit 5
#案例1：查询前五条员工信息

SELECT * FROM  t_mysql_employees LIMIT 0,5;
SELECT * FROM  t_mysql_employees LIMIT 5;

#案例2：查询第11条——第25条

SELECT * FROM  t_mysql_employees LIMIT 10,15;

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

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

DML（数据管理语言）

1、简介
• DML(Data Manipulation Language –
数据操纵语言) 可以在下列条件下执行: – 向表中插入数据
– 修改现存数据
– 删除现存数据
• 事务是由完成若干项工作的DML语句组成的

2、插入数据
• 使用 INSERT 语句向表中插入数据。
• 使用这种语法一次只能向表中插入一条数据。

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

INSERT INTO t_mysql_departments(department_id, department_name, manager_id, location_id)
VALUES (800, 'Ptions', 100, 1700);
SELECT * from t_mysql_departments

3、更新数据

#案例 1：修改张无忌的女朋友的手机号为119

UPDATE t_mysql_boys bo
INNER JOIN t_mysql_beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

UPDATE t_mysql_departments
	SET department_id =271,
	department_name = 'ds' 
WHERE
	department_id =270;

4、删除数据
使用 DELETE 语句从表中删除数据。
DELETE FROM table
[WHERE condition];

• 使用 WHERE 子句删除指定的记录。
DELETE FROM t_mysql_departments
WHERE department_name = ‘Finance’;

• 如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM copy_emp;
delete pk truncate
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列， 如果用delete删除后，再插入数据，自增长列的值从断点开始， 而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
#案例：删除张无忌的女朋友的信息

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