1.SELECT DISTINCT age,name FROM...
只有age,name两个字段的值完全相同的记录才会被过滤；
2. GROUP BY
不加聚合函数时，group by 返回结果集的第一条记录。
3. HAVING
由于where 和count不可连用，可以由group by 后再用having count(字段)来统计。例如：select Email from Person group by Email having count(Email)>1;//查找重复邮箱
4.FORMAT(): 格式化数据内容
4. IS NULL
查询某字段为空的记录，不可使用WHERE 字段名=NULL， 要用WHERE 字段名 IS NULL
5. IFNULL
语法： IFNULL(expr1,expr2) 解释：假如expr1不为NULL，则函数返回值为 expr1; 否则，如果如expr1为NULL，函数返回值为expr2。
举例：
6. NULLIF
语法：NULLIF(expr1,expr2) 解释：如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1
3. LIMIT+OFFSET
当SELECT...FROM...WHERE...的结果为
SELECT...FROM...WHERE...LIMIT 2；
SELECT...FROM...WHERE...LIMIT 2,1； SELECT...FROM...WHERE...LIMIT N,M；//舍弃前N条，限制选择接下去的前M条
SELECT...FROM...WHERE...LIMIT 2 OFFSET 1； SELECT...FROM...WHERE...LIMIT N OFFSET M；//舍弃前M条，选择接下去的前N条
PS: 在选择第二的时候，若表里只有一条数据，期望返回NULL，但是执行结果为空。
3.查第二
①select max(Salary) as SecondHighestSalary
from Employee
where Salary<(select max(Salary) from Employee);
②SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary；
③CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M INT;
SET M=N-1;
RETURN (
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1 ); END
8. group by与max连用是需要注意的地方
#找出部门中薪水最高的人 select d.Name Department,e.Name Employee,e.Salary from Employee e join Department d on e.DepartmentId=d.Id group by e.DepartmentId having max(e.Salary); //错误，max失效，只会返回group by后第一条记录
select d.Name Department,e.Name Employee,max(e.Salary) from Employee e join Department d on e.DepartmentId=d.Id group by e.DepartmentId; //错误，Salary可以最大，但前两个字段还是返回group by后第一条记录的内容，导致记录的字段内容不匹配
9. REPLACE
REPLACE函数 语法：replace(object,search,replace) 意思：把object中出现search的全部替换为replace 案例： update `news`set `content`=replace(`content`,' ','');//清除news表中content字段中的空格
REPLACE INTO 语法：replace into table_name ( col1, col2, col3 ) values ( val1, val2, val3 ) ; 语义：向table表中col1, col2, col3列replace数据val1，val2，val3
使用REPLACE插入一条记录时，如果不重复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。
使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。
在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。
3. 分页优化
SELECT a.* FROM table as a,
(selectid from table
where condition1 = 0
andcondition2 = -1
order by id asc
LIMIT 2000 OFFSET 50000) as b
wherea.id = b.id
先获取主键列表，再通过主键查询目标数据，即使offset很大，也是获取了很多的主键，而不是所有的字段数据，相对而言效率会提升很多。
转载自https://www.cnblogs.com/a9999/p/7218942.html
网友评论