Mysql自连接、分页排序、子查询、常用函数、聚合函数

 

自连接：

数据表与自身进行连接

 

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)

-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)

 

排序 ：

语法 : ORDER BY

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照ASC升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

 

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)

-- 按成绩降序排序

 

分页： 

语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

好处 : (用户体验,网络传输,查询压力)

推导:

第一页 : limit 0,5

第二页 : limit 5,5

第三页 : limit 10,5

......

第N页 : limit (pageNo-1)*pageSzie,pageSzie

[pageNo:页码,pageSize:单页面显示条数]

 

子查询：

什么是子查询?

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句

嵌套查询可由多个子查询组成,求解的方式是由里及外;

子查询返回的结果一般都是集合,故而建议使用IN关键字;

 

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列

-- 方法一:使用连接查询 
SELECT studentno,r.subjectno,StudentResult 
FROM result r 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo` 
WHERE subjectname = '数据库结构-1' 
ORDER BY studentresult DESC; 

-- 方法二:使用子查询(执行顺序:由里及外) 
SELECT studentno,subjectno,StudentResult FROM result 
WHERE subjectno=( SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1' )
ORDER BY studentresult DESC;

常用函数 ：

数据函数

SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

 

字符串函数

SELECT CHAR_LENGTH('坚持就能成功');/*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替 换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/ 
SELECT UPPER('KuangShen'); /*大写*/ 
SELECT LEFT('hello,world',5); /*从左边截取*/
 SELECT RIGHT('hello,world',5);/*从右边截取*/ 
SELECT REPLACE('坚持就能成功','坚持','努力');/*替换字符串*/
 SELECT SUBSTR('坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('坚持就能成功');/*反转 
-- 查询姓周的同学,改成邹 

SELECT REPLACE(studentname,'周','邹') AS 新名字 
FROM student WHERE studentname LIKE '周%';

 

 

日期和时间函数

SELECT CURRENT_DATE(); /*获取当前日期*/
 SELECT CURDATE(); /*获取当前日期*/
 SELECT NOW(); /*获取当前日期和时间*/ 
SELECT LOCALTIME(); /*获取当前日期和时间*/ 
SELECT SYSDATE(); /*获取当前日期和时间*/ 
-- 获取年月日,时分秒 
SELECT YEAR(NOW());
 SELECT MONTH(NOW()); 
SELECT DAY(NOW());
SELECT HOUR(NOW()); 
SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

 

 

系统信息函数

SELECT VERSION(); /*版本*/
 SELECT USER(); /*用户*/

 

聚合函数

 

-- 聚合函数 
/*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student; 
SELECT COUNT(1) FROM student; /*推荐*/ 
-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。 
-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。 
-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录； 
-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。 
/*很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1) 可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的 条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不 同，它会统计该字段不为null的记录条数。 下面它们之间的一些对比： 
1）在表没有主键时，count(1)比count(*)快 
2）有主键时，主键作为计算条件，count(主键)效率最高； 
3）若表格只有一个字段，则count(*)效率较高。 */S
ELECT SUM(StudentResult) AS 总和 FROM result; 
SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result; 
SELECT MIN(StudentResult) AS 最低分 FROM result;