MySQL语言
MySQL数据库DQL语言
多表查询
1、笛卡尔集
语法:select name,boyName from beauty,boys;
笛卡尔集会在下面条件下产生
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集, 可以在 WHERE 加入有 效的连接条件。
2、等值/连接连接
1、等值连接
① 多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时,在列名之前加上表名前缀
区分重复的列名
• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名,则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度,但建议越少越
好
表的别名
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如:连接
三个表,至少需要两个连接条件。
连接查询案例
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅仅支持内连接
sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
2、非等值连接
#案例1:查询员工的工资和工资级别
SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
3、join链接
分类
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
特点:
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
外连接
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
常见函数
一、字符函数
1.length 获取参数值的字节个数
SELECT LENGTH('john');
2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名
3.upper、lower
SELECT UPPER('john');转大写
SELECT LOWER('joHn');转小写
4.substr、substring
注意:索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put;
5.instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
6.trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
二、数学函数
1.ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
2.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);
3.truncate 截断
SELECT TRUNCATE(1.69999,1);
4.mod取余
mod(a,b) : a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*(-3)=-1
SELECT MOD(10,-3);
SELECT 10%3;
三、日期函数
1.now 返回当前系统日期+时间
SELECT NOW();
2.curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
3.curtime 返回当前时间,不包含日期
SELECT CURTIME();
4.可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM t_mysql_employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
5.str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
6.date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
7.查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;
四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行) √
列子查询 (多行) √
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一、where或having后面
#查询员工的信息,满足 salary>①结果
SELECT *
FROM t_mysql_employees
WHERE salary>(
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'
);
查询员工的姓名,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
);
查询最低工资>id=50的最低工资和id
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后面
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM t_mysql_employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM t_mysql_departments d;
from后面
查询每个部门的平均工资
1、SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id;
2、SELECT * FROM t_mysql_job_grades;
2连接1的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
exists后面
SELECT department_name
FROM t_mysql_departments d
WHERE EXISTS(
SELECT *
FROM t_mysql_employees e
WHERE d.`department_id`=e.`department_id`
);
分页查询
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点:
①limit语句放在查询语句的最后
②公式
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20