MySQL（三）Select查询详解及常用函数

1.基本select语句和别名使用

需要导入的sql包

SELECT * FROM student

SELECT *FROM result
查询指定字段
SELECT `studentno`,`studentname` FROM student
起别名，给结果起名字
SELECT `studentno` AS 学号,`studentname` as 学生姓名 FROM student
函数concat（a,b）
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

2.去重及据库的表达式（DISTINCT）

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

案例

SELECT studentno FROM result
去重：将查出来的重复数据，只显示一条
SELECT DISTINCT studentno FROM result

//学员成绩+1分查看
SELECT studentno,studentresult AS 提分后 FROM result

3.模糊查询

LIKE结合 %（表示0到任意个字符） _表示一个字符

案例：查询名字中带有关键字的

查询姓刘的同学 
SELECT studentno ,studentname FROM student
WHERE studentname LIKE '刘%'

查询姓刘的同学 名字后面只有一个字 
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_'

查询姓刘的同学 名字后面有两个字 
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__'

查询 中间夹字的 
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%丽%'

IN （具体的一个或多个值）

查询1001 1002 1003 号学员 IN()在这个范围内
SELECT studentno ,studentname FROM student
WHERE studentno IN(1001,1002,1003)

查询在广东深圳学生 （）里面的数据需要一一对应
SELECT studentno ,studentname FROM student
WHERE address in('广东深圳')

NULL与NOT NULL的用法 （其中某一些数据为null值，没有值的时候将其查询出来）

查询地址为空的学生 
SELECT studentno ,studentname FROM student
WHERE address ='' OR address IS NULL

查询手机号为空的学生
SELECT studentno,studentname FROM student
WHERE phone IS NULL

查询手机号不为空的学生
SELECT studentno,studentname FROM student
WHERE phone IS NOT NULL

4.JOIN ON联表查询

思路重要！！！

分析需求 分析查询的字段来自哪些表
确定使用哪种链接查询
确定交叉点(这两个表中哪个数据是相同的)
判断的条件 :学生表中的 studentno=成绩表 studentno

查询参加了考试的同学 （学号 姓名 科目编号 分数 ）
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
INNER JOIN result AS r 
WHERE s.studentno=r.studentno

两张表共有的

RIGHT JOIN     右表有什么查询什么出来
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s 
RIGHT JOIN result r 
on s.studentno=r.studentno

LEFT JOIN查询     即使右表没有数据 也给查询出来
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s 
LEFT JOIN result r 
on s.studentno=r.studentno

LEFT JOIN：根据左表的studentno查询，不管成绩有没有都查询出来

案例：查询缺考的同学（没有成绩） 左查询

查询缺考的同学（没有成绩） 左查询 
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s 
LEFT JOIN result r 
ON s.studentno =r.studentno
WHERE studentresult IS NULL

JOIN（连接的表） ON（判断的条件） 连接查询 WHERE 等值查询

案例（难）

查询参加考试同学信息 ：学号 学生姓名 科目名 分数 
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s 
RIGHT JOIN result r 
ON r.studentno=s.studentno
INNER JOIN `subject` sub 
ON r.subjectno=sub.subjectno

5.排序和分页

排序（DESC降序 ASC升序 ）

语法 : ORDER BY ORDER BY 语句用于根据指定的列对结果集进行排序。 ORDER BY
语句默认按照ASC升序对记录进行排序。 如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

排序 DESC降序 ASC升序 
SELECT s.studentno,studentname,studentresult
FROM student s 
INNER JOIN result r 
ON s.studentno =r.studentno
ORDER BY studentresult ASC

分页

语法 : 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:单页面显示条数]

SELECT s.studentno,studentname,studentresult
FROM student s 
INNER JOIN result r 
ON s.studentno =r.studentno
ORDER BY studentresult ASC
LIMIT 0,5

练习1：查询c语言 课程成绩排名前十的学生 分数大于80的学生信息 （学号 姓名 课程名称 分数）

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s 
INNER JOIN result r 
ON s.studentno =r.studentno
INNER JOIN `subject` sub 
ON sub.subjectno = r.subjectno
WHERE subjectname='C语言-1' AND studentresult>=30
ORDER BY studentresult DESC
LIMIT 0,10

练习2： 查询C语言-1 所有考试结果 （学号 科目编号 成绩 ） 降序

SELECT studentno ,r.subjectno,studentresult
FROM result r 
INNER JOIN `subject` sub 
ON r.subjectno =sub.subjectno
WHERE subjectname ='C语言-1'
ORDER BY studentresult DESC

练习3:分数不小于60分的学生学号和姓名

SELECT DISTINCT s.studentno,studentname
FROM student s 
INNER JOIN result r 
ON s.studentno =r.studentno
WHERE studentresult>=60

练习4
查询姓王的同学,改成刘

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

6. 子查询

什么是子查询?

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句 嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;

7.常用函数

数据函数

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

字符串函数

 SELECT CHAR_LENGTH('我爱祖国'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','祖国');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱祖国',1,2,'非常爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('select'); /*小写*/
 SELECT UPPER('select'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('我想吃包子','包子','烧麦');  /*替换字符串*/
 SELECT SUBSTR('我想吃包子',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('我想吃包子'); /*反转
 

日期时间函数

 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();     /*用户*/