BETWEEN AND 取值包括两边界 相当于>= and <=
效果上 inner join == where ==join
效率上建议使用inner join
ANY 和 MIN
ALL 和 MAX
执行顺序
from - where - group by - having - select - order by
所以where 后面不能使用select后面的别名
group by having + 聚合函数
*HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
通配
通配符 | Value |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist]或者[!charlist] | 不在字符列中的任何单一字符 |
union
union all 将符合条件的数据都列出来,无论数据值有无重复。
UNION 使用前后列的数量必须相等,然后按照上部分的列名作为列名,下半部分,按照顺序放到下面,下班部分的列名无所谓
例如以下两个结果相同
SELECT SNAME AS NAME,SSEX AS SEX,SBIRTHDAY AS BIRTHDAY
FROM STUDENT
WHERE SSEX='女'
UNION
SELECT TNAME AS NAME,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY
FROM TEACHER
WHERE TSEX='女';
NAME SEX BIRTHDAY
王丽 女 1976-01-23 00:00:00
王芳 女 1975-02-10 00:00:00
王萍 女 1972-05-05 00:00:00
刘冰 女 1977-08-14 00:00:00
SELECT SNAME AS NAME,SSEX AS SEX,SBIRTHDAY AS BIRTHDAY
FROM STUDENT
WHERE SSEX='女'
UNION
SELECT TNAME AS NAME111,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY
FROM TEACHER
WHERE TSEX='女';
NAME SEX BIRTHDAY
王丽 女 1976-01-23 00:00:00
王芳 女 1975-02-10 00:00:00
王萍 女 1972-05-05 00:00:00
刘冰 女 1977-08-14 00:00:00
join
– 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。-
SELECT
*
from
SCORE
where SCORE.CNO = '3-105'
AND
SCORE.DEGREE > (SELECT DEGREE
FROM SCORE WHERE SCORE.SNO = 109
AND SCORE.CNO = '3-105')
SELECT A.*
FROM SCORE AS A JOIN SCORE AS B
WHERE A.CNO='3-105'
AND A.DEGREE>B.DEGREE
AND B.SNO='109'
AND B.CNO='3-105';
**
错误 疑似原因 on后第一个条件 and连接的和理解的效果不一样
left join on and 与 left join on where的区别
**
SELECT
S1.SNO AS SNO1,S1.CNO AS CNO1,S2.SNO AS SNO2,S2.CNO AS CNO2
from
SCORE S1 LEFT JOIN
SCORE S2
ON S1.CNO = S2.CNO
AND S1.SNO = 109
AND S1.CNO = '3-105'
而且等于
SELECT
S1.SNO AS SNO1,S1.CNO AS CNO1,S2.SNO AS SNO2,S2.CNO AS CNO2
from
SCORE S1 LEFT JOIN
SCORE S2
ON S1.SNO = S2.SNO
AND S2.SNO = 109
AND S2.CNO = '3-105'
结果
正确
SELECT
*
from
SCORE S1 LEFT JOIN
SCORE S2
ON S1.CNO = S2.CNO
WHERE S1.SNO = 109
AND S1.CNO = '3-105'
SELECT
*
from
SCORE S1 inner JOIN
SCORE S2
ON S1.CNO = S2.CNO
AND S1.SNO = 109
AND S1.CNO = '3-105'
原因
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
也就是说 left join on 一定返回左表全部数据,
但是where把不符合的数据过滤掉了
on and 不会过滤掉,而是当做条件了,所以不管是S1.SNO = 109还是S2.SNO = 109都认为是条件不满足,把S2字段判断为null
3,所以大部分条件join其实都是选择部分需要的数据的join,也就是不建议在on后面使用过滤条件
那join where是在临时表生成好,在进行过滤,这个先join后过滤的性能影响怎么解决
SELECT
S1.SNO AS SNO1,S1.CNO AS CNO1,S2.SNO AS SNO2,S2.CNO AS CNO2
from
(select * from SCORE s where S1.SNO = 109 AND S1.CNO = '3-105' )S1
LEFT JOIN
(select * from SCORE s where S1.SNO = 109 AND S1.CNO = '3-105' ) s2
ON S1.CNO = S2.CNO
这样写岂不是更好
但是有时候不可以
比如
查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序(请注意,一个员工可能有多次涨薪的情况)
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
employees的入职日期hire_date是和入职时工资日期from_date对应,关联过滤就不能用上诉描述的优化方案
select e.emp_no,s.salary
from employees e
left join salaries s
on e.emp_no=s.emp_no
where e.hire_date = s.from_date
order by e.emp_no desc
日期函数
MAX,MIN和limit的区别
可以看到,max和limit 1 求max(SBIRTHDAY)是可以的
但是max()函数需要带其他字段结果是不对的
SELECT * FROM STUDENT ORDER BY SBIRTHDAY LIMIT 1
SNO SNAME SSEX SBIRTHDAY CLASS
103 陆君 男 1974-06-03 00:00:00 95031
错误
SELECT min(SBIRTHDAY),STUDENT.* FROM STUDENT
SNO SNAME SSEX SBIRTHDAY CLASS min(SBIRTHDAY)
108 曾华 男 1977-09-01 00:00:00 95033 1974-06-03 00:00:00
应该修改为
SELECT SNO,CNO,DEGREE
FROM SCORE
WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);