ORDER BY 排序查询：
#语法：
SELECT {*,列名,函数}
FROM 表名
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC]; #ASC升序(升序) DESC(降序)
#示例1、查询平均成绩在80以上的学生(学号)信息,同时成绩还需要按照降序排列
SELECT StudentNo "学号",AVG(StudentResult) "平均成绩"
FROM result
GROUP BY (StudentNo)
HAVING AVG(StudentResult) > 80;
ORDER BY(AVG(StudentResult)) DESC;
LIMIT 区间查询：
#语法：
SELECT {*,列名,函数}
FROM 表名
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC] #ASC升序(升序) DESC(降序)
[LIMIT A,B];
LIMIT 连续区间查询
LIMIT A,B A表示需要查询的行的索引位 B表示所查询的容量
LIMIT 0,5 第一行---第五
LIMIT 5,5 第六行---第十
LIMIT 10,5 第十一---第十五
分页查询的前置
以baidu热搜为例
>>>>>>>>>>>>> 第一页 LIMIT 0,6
>>>>>>>>>>>>> 第二页 LIMIT 6,6
>>>>>>>>>>>>> 第三页 LIMIT 12,6
>>>>>>>>>>>>> 第四页 LIMIT 18,6
>>>>>>>>>>>>> 第五页 LIMIT 24,6
>>>>>>>>>>>>> 第六页 LIMIT 30,6
>>>>>>>>>>>>> LIMIT (当前页码数-1)*容量,容量
#示例1：求学校学生中 三甲学生的信息
#分析 学生总分 降序排列 区间取前三
SELECT StudentNo "学号",SUM(StudentResult) "总成绩"
FROM result
GROUP BY (StudentNo)
ORDER BY(SUM(StudentResult)) DESC
LIMIT 0,3;
GROUP_CONCAT 分组数据合并：
#示例1 根据班级进行分组，要求查看各班人数，以及各班学员姓名。
SELECT GradeID "班级编号",COUNT(*) "班级人数",GROUP_CONCAT(StudentName) "学员姓名"
FROM student
GROUP BY(GradeID);
注意事项：
1、使用GROUP_CONCAT()函数时必须要对数据源进行分组，如果不分组，所有数据都将合并成一行。
2、对结果集排序 查询语句执行的查询结果,数据是按照插入时顺序进行排序。
3、实际上需要按照某列大小值进行排序的话，建议只针对于数值或日期通过 ORDER BY函数进行排序
4、在语句最后也可以通过LIMIT控制容量大小
多表关联查询：
1、交叉连接查询
#示例1：查询所有的学生+所有的班级信息
SELECT *
FROM student,grade;
这样查询最终得道的数据有11*7=77条数据
通过笛卡尔积获取的数据，异常过多，无法匹配具体的内容，于是我们需要补充条件以提高查询的精度
2、等值连接查询
#示例1：查询所有的学生+所有的班级信息
#语法：SELECT * FROM 表1，表2，... WHERE 表1.列 = 表2.列 [AND...];
SELECT *
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#示例2：查询所有的学生姓名，住址，班级名称
SELECT StudentName "姓名",Address "住址",GradeName "班级名称"
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#更为规范化的写法
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s,grade g
WHERE s.GradeID = g.GradeID;
#练习查询学生姓名，参考科目，考试时间，考试成绩
#分析思路：1找到要查啥 2查的东西来自于哪 3表关系
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.ExamDate "考试时间",r.StudentResult "考试成绩"
FROM student s,subject su,result r
WHERE r.StudentNo = s.StudentNo AND r.SubjectNo = su.SubjectNo;
感想：等值链接确实能够帮助我们完成表于表之间的联系,但是WHERE这个关键字一开始是作为基础条件关键字出现的,而我们把表与表之间关系的描述通过WHERE去实施，难免大材小用.于是我们决定释放WHERE关于等值连接的操作。
3、内连接查询 INNER JOIN
#语法 SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列 [INNER JOIN 表3 ON 关系 .....][WHERE 基础条件];
#示例3：练习查询学生姓名，参考科目，考试时间，考试成绩
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.ExamDate "考试时间",r.StudentResult "考试成绩"
FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo
INNER JOIN subject su ON r.SubjectNo = su.SubjectNo;
PS 内连接查询的本质和等值实际上没有区别,但是内连接可以释放WHERE关键字,使表与表之间关系更加清晰.
4、外连接查询
4.1 左外连接 >>>>> LEFT JOIN 获取相交数据+左外关键字以左表的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM grade g LEFT JOIN student s ON s.GradeID = g.GradeID;
4.2 右外连接 >>>>> RIGHT JOIN 获取相交数据+右外关键字以右的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s RIGHT JOIN grade g ON s.GradeID = g.GradeID;
5、自然连接查询 自己和自己形成主外键关系
+------------+-----+-----------------+
| categoryId | pid | categoryName |
+------------+-----+-----------------+
| 2 | 1 | 美术设计 |
| 3 | 1 | 软件开发 |
| 4 | 3 | 数据库基础 |
| 5 | 2 | Photoshop基础 |
| 6 | 2 | 色彩搭配学 |
| 7 | 3 | PHP基础 |
| 8 | 3 | 一起学JAVA |
+------------+-----+-----------------+
假设 1 意味着是根目录
编号为2的美术设计 和编号为3的软件开发 父级都是 1 根目录
编号为3的数据库基础 是软件开发的一部分
SELECT c1.categoryName "父级目录",c2.categoryName "子栏目"
FROM category c1 INNER JOIN category c2 ON c1.categoryId = c2.pid;
子查询：
1 将一个查询语句的结果充当下一个查询语句的条件
2 核心在于通过小括号以提高优先级别
3 子查询中可以包含的关键字 IN NOT ALL
4 子查询中可以包含的运算符 逻辑+算数
具体的用法如下示例：
#示例1：查询大一的男生姓名及家庭住址
#1--大一对应的班级编号
SELECT GradeID FROM grade WHERE GradeName = '大一'; ======> 1
#2--以大一对应的班级编号作为线索，去找适配的学生信息
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE GradeName = '大一');
#3--加入我们的基础条件
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE GradeName = '大一') AND sex = 1;
#示例2：查询班级名称是大一（学生信息==>学号信息），科目是高等数学-1（科目编号） 的学生的平均分
>>>>需求1 根据班级名 找出班级编号
SELECT GradeID
FROM grade WHERE GradeName = '大一';
>>>>需求2 根据对应的班级编号 找到适配的学生学号
SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一')
>>>>需求3 根据科目名找到对应的科目编号
SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1';
>>>>编辑最后的命令
SELECT AVG(result.StudentResult)
FROM result
WHERE result.SubjectNo = (SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1')
AND result.StudentNo IN (SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一'));
聚合函数：
#聚合函数的定义
聚合函数是指对一组值进行运算，最终返回是单个值。也可以被称为 组合函数
COUNT() 统计目标行数量的函数
AVG() 求平均值
SUM() 求合
MIN() 求最大值
MAX() 求最小值
PS：除COUNT函数之外，其他的聚合函数都会忽略NULL值
数值型函数：
| 函数名称 | 作用 |
|---|
| ABS() | 求绝对值 |
| SQRT() | 求平方根 |
| POW()或POWER() | 返回参数的幂次方 |
| MOD() | 求余数 |
| CEIL()或CEILING() | 向上取整 |
| FLOOR() | 向下取整 |
| ROUND() | 四舍五入 |
| RAND | 随机生成一个数字 （0-1) 之间 |
#随机生成 0-----99999的数字
#1随机生成一个数字 （0-1) 之间
SELECT RAND();
#2将生成的随机数*100000
SELECT RAND()*100000;
#3对结果进行FLOOR向下取整
SELECT FLOOR(RAND()*100000);
字符串函数：
| 函数名称 | 作用 |
|---|
| LENGTH() | 返回字符串长度 |
| CHAR_LENGTH() | 返回字符串的字节长度 |
| CONCAT() | 合并字符串长度,返回结果为连接后新生成的字符串，参数可以是一个或多个 |
| INSERT(str,pos,len,newstr) | 替换字符串函数 |
| LOWER() | 将字符串内所有的字符转小写 |
| UPPER() | 将字符串中所有的字符转大写 |
| LEFT(str,len) | 从字符串左侧进行截取，返回字符串左边若干长度的字符 |
| RIGHT(str,len) | 从字符串右侧进行截取，返回字符串右边若干长度的字符 |
| LOCATE(substr,str) | 返回第一次出现目标字符串的索引位 |
| INSTR(substr,str) | 返回最后一次出现目标字符串的索引位 |
日期函数：
| 函数名称 | 作用 |
|---|
| CURDATE() CURRENT_DATE() CURRENT_DATE | 返回当前系统的日期值 |
| CURTIME() CURRENT_TIME() CURRENT_TIME | 返回当前系统的时间 |
| NOW() SYSDATE() | 返回当前系统的日期及时间 |
| DATE(PAREM) | 返回指定对象的日期部分 |
| TIME(PAREM) | 返回指定对象的时间部分 |
| YEAR(PAREM) | 返回指定对象的年份(1970--2069) |
| MONTH(PAREM) | 返回指定对象的月份 |
| DAY(PAREM) | 返回指定对象的日期 |
| DAYOFWEEK(PAREM) | 获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1 |
| WEEK(PAREM） | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 |
| DATEDIFF(PAREM,PAREM) | 返回两个日期之间的相差天数 |
#查询A学生生日和当前时间的天数差
SELECT DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'));
#根据生日查询其年龄
SELECT FLOOR(DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'))/365) AS "年龄";
流程控制函数：
| 函数名称 | 作用 |
|---|
| IF(条件,结果1,结果2) | 判断，如果条件=true 返回结果1 反之 返回结果2 |
| CASE | 搜索函数 |
| IFNULL(value1,value2) | 判断，如果value1不为NULL 则函数返回值就是value1 反之 返回value2 |
#示例1
SELECT IF(12,2,3);
SELECT IF(1<2,'YES','NO');
SELECT IF(STRCMP('TEST001','TEST001'),'NO','YES');
条件内 结果 true(非0的自然数) false(0)
#示例2 分别显示学生信息，有邮箱和没有邮箱的备注信息
SELECT StudentName "学生姓名",IF(Email IS NULL,'没有邮箱','存在邮箱') "是否具有邮箱"
FROM student;
#示例3 使用IFNULL,函数入参两个,如果入参不为空则返回第一个值，否则返回第二个值
SELECT IFNULL(1,2),IFNULL(NULL,2),IFNULL(9/3,2);
SELECT StudentName "学生姓名",IFNULL(Email,'没有邮箱') "邮箱地址"
FROM student;
#示例4
CASE<表达式>
WHEN<值1> THEN<结果1>
WHEN<值2> THEN<结果2>
WHEN<值3> THEN<结果3>
WHEN<值4> THEN<结果4>
ELSE <默认结果>
END
#需求 查询成绩表 限定考试科目 高等数学-1
# 要求如下 如果学号是1000 显示成绩为原成绩的 1.5倍
# 要求如下 如果学号是1001 显示成绩为原成绩的 1.3倍
# 要求如下 如果学号是1002 显示成绩为原成绩的 1.1倍
# 要求如下 其他学生成绩显示原成绩
#1-查出高数-1的科目编号
SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1';
#2-通过科目编号找到学生的考试成绩
SELECT * FROM result WHERE SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1');
#3-通过CASE语法修改并查看参数
SELECT StudentNo "学号",StudentResult "原成绩",
CASE StudentNo
WHEN 1000 THEN StudentResult*1.5
WHEN 1001 THEN StudentResult*1.3
WHEN 1002 THEN StudentResult*1.1
ELSE StudentResult
END "修改后的成绩"
FROM result
WHERE SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1');
#课堂练习 为所有成绩进行评分 要求体现的内容有 学生姓名,参考科目,考试成绩,综合评分(>=90优 >=80良好 >=70中等 >=60较差 不及格)
#通过内连接 完成3表的关联 学生 成绩 科目
#步骤1、得到学生姓名 参考科目 考试成绩
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.StudentResult "考试成绩",
CASE
WHEN r.StudentResult>=90 THEN "优秀"
WHEN r.StudentResult>=80 THEN "良好"
WHEN r.StudentResult>=70 THEN "中等"
WHEN r.StudentResult>=60 THEN "较差"
ELSE "不及格"
END "综合评分"
FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN subject su ON su.SubjectNo = r.SubjectNo;
视图的创建：
#语法：
CREATE [OR REPLACE] VIEW 视图名
[别名1，别名2，.....]
AS 必要的查询语句
[额外的声明 ===> WITH READ ONLY];
#如：创建视图，Student_V_1 包含了一号班级编号的所有学生的学号 姓名 班级名称 生日 住址
CREATE VIEW Student_V_1
AS SELECT student.StudentNo,student.StudentName,grade.GradeName,student.BornDate,student.Address
FROM student INNER JOIN grade ON student.GradeID = grade.GradeID
WHERE student.GradeID = 1;
#查询当前数据库内所有的视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';
视图的修改：
#比如XXX人创建视图时Email赋值赋的是生日 通过ALTER关键字可以实现视图中字段值的重新赋予
ALTER VIEW Student_V_1
(Email)
AS SELECT Email
FROM student;
视图的删除：
DROP VIEW 视图名;
创建索引：
创建索引是指在某个表的一列或多列上建立索引的行为。有三种方式创建索引：创建表的时候（声明字段）时创建索引，在已经存在的表上创建索引，在使用ALTER TABLE语句来创建索引。
创建表的时候可以直接创建索引,这种方式是最为简单和方便的，语法如下:
CREATE TABLE 表名(
字段名 字段类型 [约束条件]
字段名 字段类型 [约束条件]
字段名 字段类型 [约束条件]
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);
#示例1：创建表的时候创建索引
CREATE TABLE index1(
id INT,
name VARCHAR(20),
Sex BOOLEAN,
INDEX(id)
);
#修改表结构的方式添加索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
#查询某表内索引的使用情况
SHOW INDEX FROM 表名;
#删除索引
DROP INDEX 索引名 ON 表名;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
