SQL语句查询

SQL语句查询

查询产生一个虚拟表
看到的是表形式显示的结果，但结果并不真正存储
每次执行查询只是从数据表中提取数据，并按照表的形式显示出来

查询语法

SELECT    <列名> 
FROM      <表名> 
[WHERE    <查询条件表达式>] 

SELECT 	    SCode,StudentName,Address 
FROM 		Students 
WHERE       SSEX = 0 

查询语句

基础查询

查询全部的行和列

SELECT * FROM Students 
SELECT * FROM result 

查询部分列

SELECT StudentName, Address  FROM Students 

查询部分行和列

// 查询 “北京”地区的学生姓名和年级
SELECT StudentName ,Grade  FROM Students
WHERE Address = '北京'

查询女学生姓名和地址
SELECT StudentName,Address FROM Students
WHERE sex=1

列别名

列别名等同于数据名的外号

使用AS命名列

实例

SELECT StudentNo AS 学生编号,StudentName  AS 学生姓名,
        Address AS 学生地址  
FROM Students
WHERE Address = '河南新乡'

空行、常量列

查询空行

//没有添加数据
SELECT StudentName FROM Students WHERE Email IS NULL
//添加有数据后被删除
SELECT StudentName FROM Students WHERE Email=''

注意:

null是空值，表示未被赋值。
‘ ’是空字符串，表示非空，只不过值是一个非空字符串而已。

使用常量列

SELECT 姓名=StudeentName,地址= Address , '北京新兴桥' AS 学校名称 FROM Students

MySQL 中的函数

MySQL中常用的几类函数：
字符串函数
用于控制返回给用户的字符串
日期函数
用于操作日期值
数学函数
用于对数值进行代数运算

字符串函数

函数名：

实例：

-- 拼接字符串
SELECT CONCAT('糯米','阿萨大大','删掉');
-- 字符长度
SELECT LENGTH('aaaaaaasssssssssssssssaa');
-- 转大写
SELECT UPPER('asdf');
-- 转小写
SELECT LOWER('ASDF');
-- 去除左边空格
SELECT LTRIM(' asdasdasd ');
-- 去除右边空格
SELECT RTRIM(' asdasdasd ');
-- 去除两端空格
SELECT TRIM(' asdasdasd ');
-- 替换
SELECT REPLACE('hello word','word','世界');
-- 有条件的插入
SELECT INSERT('hello word',6,1,'世界');

字符串的获取

-- 翻转
SELECT REVERSE('123@qq.com');
-- 获取字符的位置
SELECT INSTR('moc.qq@321','.');
-- 截取字符串
SELECT RIGHT('1234@qq.com',3);

日期函数

函数名：

实例：

-- 系统日期
SELECT CURDATE();
-- 日期时间
SELECT NOW();
-- 时间
SELECT CURTIME();
-- 添加日期
SELECT DATE_ADD(NOW(),INTERVAL 5 MONTH);
-- 减少 
SELECT DATE_SUB(NOW(),INTERVAL 5 DAY);
-- 日期的格式化	Y完整年	y两位数的年
-- 年月日
SELECT DATE_FORMAT(CURDATE(),'%Y年%m月%d日');
-- 年月日时分秒
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点%i分%s秒');

数学函数

函数名：

实例：

-- 随机数
SELECT RAND();
-- 绝对值
SELECT ABS(-43);
-- 最小整数
SELECT CEILING(43.5);
-- 最大整数
SELECT FLOOR(43.5);
-- 幂值
SELECT POWER(5,2);
-- 四舍五入
SELECT ROUND(43.543,1);
-- 返回值
SELECT SIGN(-43);
-- 平方根
SELECT SQRT(9);

随机生成

下例是生成10000~9999999之间的随机数

-- 10000~9999999
SELECT RAND()*(9999999-10000)+10000;

通过与其他函数的结合可以实现随机生成qq邮箱之类的效果

随机邮箱

-- 10000~9999999
SELECT RAND()*(9999999-10000)+10000;
-- 取整
SELECT FLOOR(RAND()*(9999999-10000)+10000);
-- 拼接
SELECT CONCAT(FLOOR(RAND()*(9999999-10000)+10000),'@qq.com');

**注意：**随机数不能出现小数时要取整

获取特定字符串

// 使用right()函数可以截取
SELECT RIGHT('123456@qq.com',4);
// 使用REVERSE()反转字符串
SELECT REVERSE('123456@qq.com');-- 倒叙
// 使用INSTR()确定截取位数
SELECT INSTR(REVERSE('123456@qq.com'),'.')-1;
// 示例
SELECT RIGHT('12345@qq.cn',INSTR(REVERSE('123456@qq.cn'),'.')-1);

实际应用

-- 翻转
SELECT REVERSE('123@qq.com');
-- 获取字符的位置
SELECT INSTR('moc.qq@321','.');
-- 截取字符串
SELECT RIGHT('1234@qq.com',3);
-- 获取com
SELECT RIGHT(email,INSTR(REVERSE(email),'.')-1) FROM student;

聚合函数

总数 sum

-- 总分
SELECT SUM(student_result) FROM result;

平均数 avg

-- 平均分 avg
SELECT AVG(student_result) FROM result;

最大值 max 最小值 min

-- 最大值 max 最小值 min
SELECT MAX(student_result),MIN(student_result) FROM result;

计数 count

-- 计数 count
SELECT COUNT(*) FROM student;

注意:使用COUNT()计数包含空值的行

通过成绩(或其他数据)进行等级区分

SELECT result_id,student_result,
CASE
WHEN student_result<60 AND student_result>=0 THEN '不及格'
WHEN student_result>=60 AND student_result<80 THEN '及格'
WHEN student_result>=80 AND student_result<=100 THEN '优秀'
ELSE '作弊'
END AS '成绩等级'
FROM result;

分组查询

语句:

SELECT …… FROM  <表名>  
WHERE  ……
GROUP BY ……

实例:

SELECT COUNT(*) AS 人数, GradeId AS 年级
FROM  Students
GROUP BY GradeId

**注意:SELECT列表中只能包含：
1、被分组的列
2、为每个分组返回一个值的表达式，如聚合函数 **

**3. group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,
必须出现在 group by 后面 **

多列分组

当单独一个条件的分组已经无法实现时就需要通过多列分组来完成

比如:统计每学期男女同学的人数时,就要对学生进行分组
和计算每组学生的总人数

语句:

SELECT COUNT(*) AS 人数,gradeId AS 年级,sex AS   性别
 FROM StudentS 
GROUP BY gradeId,sex 
ORDER BY gradeId

实例:

# 班级编号和性别分组 并通过班级编号升序
SELECT grade_id,sex,COUNT(*) FROM student GROUP BY grade_id,sex ORDER BY grade_id;

分组筛选

分组筛选语句:

SELECT …… FROM  <表名>
WHERE ……
GROUP BY ……
HAVING……

实例:

# 班级的学生的总人数>15的班级
SELECT grade_id,COUNT(*) FROM student GROUP BY grade_id;

通过分组查询与聚合函数的组合可以完成如下操作

# 分组查询
# 每个班级的学生的总人数
SELECT grade_id,COUNT(*) FROM student GROUP BY grade_id;

# 班级编号和性别分组 并通过班级编号升序
SELECT grade_id,sex,COUNT(*) FROM student GROUP BY grade_id,sex ORDER BY grade_id;

-- 每个科目平均分
SELECT AVG(student_result) FROM result;
SELECT subject_id,AVG(student_result) FROM result GROUP BY subject_id;

# 班级的学生的总人数>15的班级
SELECT grade_id,COUNT(*) FROM student GROUP BY grade_id;

SELECT grade_id,COUNT(*) FROM student GROUP BY grade_id
HAVING COUNT(*)>3;
# 先分组后筛选
SELECT grade_id,COUNT(*),sex FROM student GROUP BY grade_id,sex
HAVING COUNT(*)>3 AND sex='男';
# 先筛选后分组
SELECT grade_id,COUNT(*) FROM student WHERE sex='男' GROUP BY grade_id
HAVING COUNT(*)>3;

WHERE与HAVING对比

WHERE子句：
	用来筛选 FROM 子句中指定的操作所产生的行 
GROUP BY子句：
	用来分组 WHERE 子句的输出 
HAVING子句：
	用来从分组的结果中筛选行 

子查询

子查询，就是在一个查询中嵌套了其他若干查询，即在一个SELECT查询语句的FROM或WHERE字句中包含另一个SELECT查询语句.

子查询语句

SELECT … FROM 表1 WHERE 字段1  比较运算符(子查询)

比如:

如何查看年龄比“李斯文”小的学生，要求显示这些学生的信息 ？

第一步：查询得到“李斯文”的出生日期
第二步：利用WHERE语句，筛选出生日期比“李斯文”大的学生

采用子查询实现:

首先，执行小括号中的子查询，返回的结果是所有来自子查询的结果
其次，才开始执行外围的父查询，返回查询的最终结果

-- 1.李斯文的出生日期
SELECT birthday FROM student WHERE student_name='李斯文';
-- 2.根据日期查询剩余学生信息
SELECT * FROM student WHERE birthday>'2003-11-24';
-- 3.子查询 
-- 比较运算符时,查询结果不能大于1
-- 查询的字段只能有一个
SELECT * FROM student WHERE birthday>(
SELECT birthday FROM student WHERE student_name='李斯文'
);

注意:子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

通过子查询来查找某一科成绩的最高分和最低分

-- 查询最近一次某科考试成绩最高分和最低分
-- 1.获取考试科目编号
SELECT subject_id FROM subjects WHERE subject_name='css';
-- 2.根据结果进行查询
SELECT MAX(examDate) FROM result;
-- 3.完整子查询
SELECT MAX(student_result),MIN(student_result) FROM result
WHERE examDate=(SELECT MAX(examDate) FROM result)
AND subject_id=(SELECT subject_id FROM subjects WHERE subject_name='css')

in子查询

有时运用子查询是子查询的返回值不是一个而是多个,这就要通过 **INin 来实现

原因:

IN后面的子查询可以返回多条记录
常用IN替换等于（＝）的子查询

-- 删除 ‘鸿蒙一班’的所有考试成绩
DELETE FROM result WHERE student_no in(
SELECT student_no FROM student WHERE grade_id=(SELECT grade_id FROM grade WHERE grade_name='鸿蒙1班')
);

如上是通过子查询班级是鸿蒙1班的学生的学号来实现的,当子查询返回出来的学号不是一个时,运用in就可以同时返回多个学号

not in

not 就是非的意思,

-- 删除除了 ‘鸿蒙一班’ 以外的所有考试成绩
DELETE FROM result WHERE student_no not in(
SELECT student_no FROM student WHERE grade_id=(SELECT grade_id FROM grade WHERE grade_name='鸿蒙1班')
);

in 返回的是多条数据,not in就是指除了返回的数据以外的所有数据

EXISTS子查询

语句

SELECT ...FROM 表名 WHERE  EXISTS (子查询)

作用:

如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false) EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False

多表联查

一般的查询是无法如何同时从这两个表中取得数据,要想同时从两个表中取得数据,就要运用到多表联查

常用的多表联查

内连接(INNER JOIN)
外连接
左外连接 (LEFT JOIN)
右外连接 (RIGHT JOIN)

内连接

内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行

内连接语句

SELECT 	…… 
FROM 	表1
INNER JOIN 	表2
ON 	……

实例

SELECT Students.StdentName, Result.StdentNo,Result.StdentResult
FROM 	Students,StdentResult
WHERE  Students.studentNo = StdentResult.StudentNo

三表内连接

以上语法是两张表之间的内连接,三张表之间的内连接语句如下

语句

SELECT 
S.StdentName AS 姓名, CS.subjectName AS 课程, C.StdentResult AS 成绩
FROM Students AS S 
INNER JOIN Result AS C ON (S.StdentNo = C.StdentNo)
INNER JOIN subject AS CS ON (CS.subjectId= C.StdentNo)

实例

SELECT s.student_name,sj.subject_name,r.student_result,r.examDate FROM result r
INNER JOIN student s ON r.student_no=s.student_no
INNER JOIN subjects sj ON r.subject_id=sj.subject_id
WHERE sj.subject_name='java初级';

外连接

左外链接(LEFT JOIN)

主表（左表）Students中的数据逐条匹配表Result中的数据
1、匹配，返回到结果集
2、无匹配，NULL值返回到结果集

实例

SELECT 	S.StdentName,C.StdentNo,C.StdentResult 
FROM 		Students AS S
LEFT JOIN 	Result AS C
ON 		C.StdentNo = S.StdentNo

右外链接(RIGHT JOIN)

右外连接的原理与左外连接相同
右表逐条去匹配记录；否则NULL填充

实例

SELECT 图书编号,图书名称,出版社名称
FROM 图书表
RIGHT OUTER JOIN 出版社表 
ON 图书表.出版社编号 = 出版社表.出版社编号

-- 查询所有学生的考试成绩
SELECT * 	FROM result r 
RIGHT 		JOIN student s 
ON			s.student_no=r.student_no;

自连接

自连接:通过起别名的方式把一张表 当做多个表使用

如

-- 查找比张三年龄大的学生
SELECT s2.* FROM student s1,student s2
WHERE s1.student_name='咳咳咳' AND s1.birthday>s2.birthday;

注意:自连接也可以是 内连接,也可以是外连接
自己与一张与自己完全一样的从表建立关系 进行连接查询

自然连接

通过MySql自己的判断完成连接过程，不需要指定连接条件。
MySql会使用表内的，相同的字段，作为连接条件
自然连接分为内外之分。
内：natural join
外：左外natural left join、右外natural right join

实例与对比

-- 查看学生姓名和考试成绩
-- 没有自连接
SELECT s.student_name,r.student_result FROM student s
INNER JOIN result r
ON s.student_no=r.student_no;

-- 使用自连接
SELECT s.student_name,r.student_result FROM student s
NATURAL JOIN result r;