一、多表联查
1.笛卡尔积
将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积。结果的数量为所有表的数量的乘积。
笛卡尔集会在下面条件下产生:
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接
为了避免笛卡尔集,可以在WHERE加入有效的连接条件
2.传统连接
等值连接:
- 多表等值连接的结果为多表的交集部分
- n表连接,至少需要n-1个连接条件
- 多表的顺序没有要求
- 一般需要为表起别名
- 可以搭配排序、分组、筛选
例:查询员工名和对应的部门名
SELECT
last_name 名字,
department_name 部门名
FROM
t_mysql_employees e,
t_mysql_departments d
WHERE
e.department_id = d.department_id;
结果:
3.内连接
语法:select 列名 from 表1 别名 inner join 表2 别名 on 连接条件;
特点:
- 添加排序、分组、筛选
- inner可以省略
- 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
- inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
等值连接:
例:查询员工名、部门名
SELECT
e.last_name,
d.department_name
FROM
t_mysql_employees e
JOIN t_mysql_departments d ON e.department_id = d.department_id;
4.外连接
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
-
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值;如果从表中没有和它匹配的,则显示null。
外连接查询结果=内连接结果+主表中有而从表没有的记录。 -
左外连接,left join左边的是主表
右外连接,right join右边的是主表 -
左外和右外交换两个表的顺序,可以实现同样的效果
-
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
左外语法:
select 列名 from 主表 left join 从表 on 筛选条件;
右外语法:
select 列名 从表 right join 主表 on 筛选条件;
全外语法:
select 列名 from 表1 full join 表2 on 筛选条件;
二、常用函数
1.字符函数
length:获取字节个数
SELECT LENGTH('hello world');#结果:11
concat:拼接字符串
SELECT CONCAT('hello','world');#结果:helloworld
LOWER:转小写
UPPER:转大写
SELECT LOWER('HELLOWORLD');#结果:helloworld
SELECT UPPER('helloworld');#结果:HELLOWORLD
substr:截取
SELECT SUBSTR('helloworld',1,5);#结果:hello
instr:字符出现索引值
SELECT INSTR('helloworld','e');#结果:2
replace:替换
SELECT REPLACE('helloworld','o','a');#结果:hellawarld
2.数字函数
round:四舍五入
select round(1.55);#结果:2
ceil:向上取整
select ceil(1.02);#结果:2
floor:向下取整
select floor(1.9);#结果:1
truncate:截断
SELECT TRUNCATE(2.333333,2);#结果:2.33
mod:取余
SELECT MOD(1600,300);#结果:100
3.日期函数
SELECT NOW();返回当前系统日期+时间
SELECT CURDATE();返回当前系统日期,不包含时间
SELECT CURTIME();返回当前时间,不包含日期
#获取指定年、月、日、小时、分钟、秒
select year(now);#month/day/hour...
#STR_TO_DATE将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%m-%d');
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
4.流程控制函数
if函数:
SELECT
last_name,
commission_pct,
IF
( commission_pct IS NULL, '没奖金,呵呵', '有奖金,嘻嘻' ) 备注
FROM
t_mysql_employees;
结果:
case函数:
SELECT
salary 原始工资,
department_id,
CASE
department_id
WHEN 30 THEN
salary * 1.1
WHEN 40 THEN
salary * 1.2
WHEN 50 THEN
salary * 1.3 ELSE salary
END AS 新工资
FROM
t_mysql_employees;
结果:
三、子查询
含义:出现在其他语句中的select语句,称为子查询或内查询。
外部的查询语句,称为主查询或外查询。
例1:查询工资比Abel高的员工名字和工资
SELECT
last_name,
salary
FROM
t_mysql_employees
WHERE
salary > ( SELECT salary FROM t_mysql_employees WHERE last_name = 'Abel' );
例2:查询工种与141号员工相同、工资比143号员工高的姓名,工种,工资
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 表【jointype join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段】limit 【offset】size;
例:
#案例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;
#案例3:有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
t_mysql_employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT 10;
五、DML(数据管理语言)
简介:DML 是对数据库的增删改操作,即添加表中的数据(insert),删除表中的数据(delete),修改表中的数据(update)。
1.添加数据
关键字:INSERT
语法:INSERT INTO 表名 (列名,列名…) VALUES (值,值…);
例:添加一条数据
INSERT INTO t_mysql_departments ( department_id, department_name, manager_id, location_id )
VALUES
( 70, 'Public Relations', 100, 1700 );
2.修改数据
关键字:UPDATE
语法:UPDATE 表名 列名=新值 WHERE 筛选条件;
• 如果省略 WHERE 子句,则表中的所有数据都将被更新
例:修改张无忌的女朋友的手机号为114
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` = '张无忌';
3.删除数据
关键字:DELETE
语法:DELETE FROM 表名 WHERE 筛选条件;
• 如果省略 WHERE 子句,则表中的全部数据将被删除
例:删除张无忌的女朋友的信息
DELETE b
FROM
t_mysql_beauty b
INNER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.`id`
WHERE
bo.`boyName` = '张无忌';
delete与truncate的区别:
DELETE | TRUNCATE | |
---|---|---|
加WHERE条件 | √ | × |
删除效率 | 比TRUNCATE低 | 比DELETE高 |
如有自增,删除再插入 | 从断点开始 | 从1开始 |
返回值 | √ | × |
回滚 | √ | × |