数据库——DQL（数据查询语言）

DOL(Date Query Language:数据查询语言)

所有的查询都用它 Select -- DOL（数据查询语言) 基本的查询语句 -- 查询全部的信息 SELECT* FROM student SELECT* FROM grade -- 查询指定字段 SELECT id ,`name` FROM student -- 给结果起别名，用AS 可以给字段起，也可以给表起别名 SELECT id AS 学号, `name` AS 姓名 FROM student AS s -- 拼接字符串函数 Concat SELECT CONCAT('姓名：',`name`)AS 名字 FROM student -- 去重distinct -- 查询学生来自哪些城市， SELECT DISTINCT `address` FROM student -- 查询系统版本 SELECT VERSION() -- 用来计算 SELECT 100*3/4 AS 结果 -- 查询自增的步长 SELECT @@auto_increment_increment -- 学生年龄+1 直接在年龄字段后面+1 SELECT `name`AS 姓名, age+1 AS '过年后' FROM student -- 概括 -- select 表达式 from 表 -- 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

联表查询

关键词 join

语法join（连接的表）on（判断的条件）

思路 1，分析查询的字段来自哪些表 2，确定两个表的交叉点（相同的数据） 3 ,判断的条件：一个表中的字段 = 另一个表中的 字段

-- 两张表 -- INNER JOIN -- 利用连表查询查询学生的学号，姓名，性别，年级，地址,分数，课程编号 SELECT s. studentno,studentname,sex,gradeid,address,studentresult,subjectno FROM student AS s INNER JOIN result AS r -- 取别名为s,r as可以省略 WHERE s.studentno = r.studentno -- 也可以不取别名,直接用表名.字段名 SELECT student. studentno,studentname,sex,gradeid,address,studentresult,subjectno FROM student INNER JOIN result WHERE student.studentno = result.studentno -- LEFT JOIN SELECT s. studentno,studentname,sex,gradeid,address,studentresult FROM student AS s -- 左表 LEFT JOIN result AS r -- 右表 ON s . studentno = r . studentno-- 会查询出左表中所有的值即使右表没有，以左表为基准 -- RIGHT JOIN SELECT s. studentno,studentname,sex,gradeid,address,studentresult FROM student AS s -- 右表 RIGHT JOIN result AS r -- 左表 ON s . studentno = r . studentno-- 会查询出右表中所有匹配的值即使左表没有，以右表为基准 -- WHERE 等值查询 join on连接查询，是一个语法 join（连接的表）on（判断的条件） -- LEFT JOIN和RIGHT JOIN 的区别 -- 假设student表中有个同学王五没有参加考试，因此他在result表中就不会有数据，如果是通过LEFT JOIN查询后得到的结果中还是会有王五的数据，但分数值为null，但如果通过RIGHT JOIN查询后得到的结果就不存在王五的数据 -- 案例 -- 查询缺考的同学 SELECT s. studentno,studentname,sex,gradeid,address,studentresult FROM student AS s -- 左表 LEFT JOIN result AS r -- 右表，以左表为基准 ON s . studentno = r . studentno WHERE studentresult IS NULL -- ===================多表查询======================== -- 三表联接查询 -- 查询参加考试学生的学号，姓名，课程编号，分数，课程名 SELECT s.studentno, studentname,r.subjectno ,studentresult,subjectname FROM student s RIGHT JOIN result r -- 因为要查询的是参加了考试的学生信息，所以要以右表result为基准 ON s.studentno = r.studentno -- 先查询出student和 result表中匹配的信息 LEFT JOIN `subject` sub -- 再连接subject表 ON sub.subjectno= r.subjectno -- 判断条件，得到最终查询结果 -- 四表查询 -- 查询参加考试学生的学号，姓名，课程编号，分数，课程名，年级名 SELECT s.studentno, studentname,r.subjectno ,studentresult,subjectname,gradename FROM student s RIGHT JOIN result r -- 因为要查询的是参加了考试的学生信息，所以要以右表result为基准 ON s.studentno = r.studentno -- 先查询出student和 result表中匹配的信息 LEFT JOIN `subject` sub -- 再连接subject表 ON sub.subjectno= r.subjectno -- 判断条件，得到出student，result和subject的查询结果 INNER JOIN grade g -- 再连接grade表 ON g.gradeid=sub.gradeid -- 判断条件，得到最终查询结果 INNER JOIN返回表中都匹配的值LEFT JOIN返回左表中返回所有匹配的值，即使右表中没有匹配 ，以左表为基准RIGHT JOIN返回右表中返回所有匹配的值，即使左表中没有匹配 ，以右表为基准

自连接

一个表与其自身连接（核心：拆分为两个一模一样的表）

SELECT a.`categoryid`AS '父编号',a.`categoryname`, b.`categoryid`AS'子编号',b.`categoryname` -- 把category表拆为两个表， FROM category AS a,category AS b -- 给两个表取别名 WHERE a.`categoryid`=b.`pid`-- 等值条件

分页和排序

排序：用ORDER BY字句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序，对于空值，排序时显示的次序由具体系统实现决定。

注意：ORDER BY字句只能对最终查询结果进行排序

SELECT s.`studentno`,studentname,studentresult ,subjectname ,`gradename` FROM student s INNER JOIN result r ON s.studentno=r.studentno INNER JOIN `subject` sub ON r. subjectno=sub.subjectno INNER JOIN `grade` g ON sub.`gradeid`=g.`gradeid` ORDER BY studentresult DESC -- 分页 limit 语法：初始查询页, 每一页的大小（pagesize） SELECT s.`studentno`,studentname,studentresult ,subjectname ,`gradename` FROM student s INNER JOIN result r ON s.studentno=r.studentno INNER JOIN `subject` sub ON r. subjectno=sub.subjectno INNER JOIN `grade` g ON sub.`gradeid`=g.`gradeid` ORDER BY studentresult DESC LIMIT 0,5 -- 初始值为0（第0行），每一页大小为6（行） -- 只查一个属性 SELECT studentresult FROM result ORDER BY studentresult DESC LIMIT 0,10 /* 第一页 limit 0,5 第二页 limit 5,5 第三页 limit 10,5 ... 第n页 limit (n-1)*每一页的大小（pagesize）,pagesize 数据总数/页面大小=总页数 */

嵌套查询

将一个查询块嵌套在另一个查询块的where字句或HAVING短语的条件中的查询称为嵌套查询

-- ==========================嵌套查询================================== -- 查询大三，大一学生的学号，姓名 SELECT studentno,studentname FROM student WHERE gradeid IN (SELECT gradeid FROM grade WHERE gradename IN('大三','大一')); -- 查询考 'Java第一学年'的学生的学号，科目代号，分数,，按降序排列 -- 1.联表完成 SELECT `studentno`,r.`subjectno`,`studentresult`,`subjectname` FROM result r INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` WHERE `subjectname`='Java第一学年' ORDER BY `studentresult` LIMIT 0,2 -- 2.嵌套完成 SELECT `studentno`,`subjectno`,`studentresult` FROM result WHERE `subjectno` = ( SELECT `subjectno` FROM `subject` WHERE `subjectname`='Java第一学年' ) ORDER BY `studentresult` -- 查询高等数学分数不小于80分的学号和姓名 -- 1、用嵌套完成· SELECT DISTINCT s.`studentno`,`studentname` ,`studentresult` FROM`student` s INNER JOIN result r ON s.`studentno`=r.`studentno` WHERE `studentresult`>= 80 AND `subjectno`=( SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-2' ) -- 2、用联表来做 SELECT DISTINCT s.`studentno`,`studentname` ,`studentresult` FROM`student` s INNER JOIN result r ON s.`studentno`=r.`studentno` INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` WHERE `subjectname`='高等数学-2' AND`studentresult`>= 80 -- 查询考 'Java第一学年'的学生的学号，姓名，分数,且分数大于50，按降序排列，一页展示5个 -- 1、嵌套完成 SELECT s.`studentno`,`studentname`,`studentresult` FROM student s INNER JOIN result r WHERE s.`studentno`=r.`studentno` AND`subjectno`=( SELECT`subjectno` FROM `subject` WHERE `subjectname`='Java第一学年' AND `studentresult`>=50 ) ORDER BY `studentresult`DESC LIMIT 0,5 -- 2、联表完成 SELECT s.`studentno`,`studentname`,`studentresult`,r.`subjectno` FROM student s INNER JOIN result r ON s.`studentno`=r.`studentno`AND`studentresult`>=50 INNER JOIN `subject` sub ON sub.`subjectno`=r.`subjectno` AND `subjectname`='Java第一学年' ORDER BY `studentresult`DESC LIMIT 0,5

常用函数

-- =============================常用函数============================= -- 数学运算 SELECT ABS(-9) -- 绝对值 9 SELECT CEILING(9.8) -- 向上取整 10 SELECT FLOOR(9.8) -- 向下取整 9 SELECT RAND() -- 随机数0-1之间 SELECT SIGN(7) -- 判断一个数的正负 正数为1 SELECT SIGN(-7)-- 负数为-1 -- 字符串函数 SELECT CHAR_LENGTH('万，里顾一程') -- 返回字符串长度 SELECT CONCAT('万里','顾','一','程')-- 拼接字符串 SELECT INSERT('helloworld',2,4,'b')-- 2.被替换字符的起始位置 4.被替换字符的长度 'b'.替换字符 SELECT LOWER('HELLO') -- 转换成小写字母 hello SELECT UPPER('hello') -- 转换成大写字母 HELLO SELECT INSTR('hellworld','wo') -- 返回第一次出现的字符串的索引 SELECT REPLACE('java高级工程师','高级','究极')-- 替换出现的指定字符串 java究极工程师 SELECT SUBSTR('java高级工程师',1,4)-- 截取指定的字符串 1：开始截取的位置 4：截取字符串的长度 SELECT REVERSE('赵兄托我办点事')-- 反转字符串 -- 查询姓虞的同学 SELECT * FROM student WHERE studentname LIKE'虞%' -- 函数应用 SELECT REPLACE(studentname,'虞','张') FROM student WHERE studentname LIKE'虞%' -- 时间和日期函数 SELECT CURRENT_DATE()-- 获取当前日期 SELECT CURRENT_TIME()-- 获取当前时间 SELECT NOW() -- 获取当前日期,时间 SELECT LOCALTIME()-- 获取本地时间 SELECT SYSDATE()-- 获取系统时间 -- 系统当前用户 SELECT SYSTEM_USER()-- 等价于SELECT USER() SELECT VERSION()

聚合函数及分组过滤

-- ========================聚合函数========================= SELECT COUNT(sex)FROM `student`-- count(字段) 会忽略所有的null值，不统计NULL值 SELECT COUNT(*)FROM `student`-- 不会忽略所有的null值 SELECT COUNT(6)FROM`student`-- 不会忽略所有的null值 SELECT COUNT(DISTINCT`studentno`)FROM `result` SELECT SUM(`studentresult`)AS 总分 FROM `result` SELECT AVG(`studentresult`)AS 平均分 FROM `result` SELECT MAX(`studentresult`)AS 最大值 FROM`result` SELECT MIN(`studentresult`)AS 最小值 FROM`result` -- 查询不同课程的平均分，最高分，最低分，平均分 SELECT`subjectname`,MAX(`studentresult`)AS 最大值, MIN(`studentresult`)AS 最小值,AVG(`studentresult`)AS 平均分 FROM `result` r INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` GROUP BY sub.`subjectno`-- 分组 HAVING 平均分>=50-- 过滤分组后的信息 /* where字句和having字句的区别 1.作用对象不同：where字句作用于基本表或视图，从中选择满足条件的元组（表中的行） HAVING字句作用于组，从中选择满足条件的组（表中的列） 2.where字句中是不能用聚集函数作为条件表达式的，HAVING字句可以 3.having是在group by之后，group by是在where之后 */

select小结

-- SELECT小结 /* select 去重 要查询的字段 from 表（注意：表和字段可以取别名） xxxx join 要连接的表 on 等值判断(顺序：先on再where) where （具体的值/子查询） group by(通过那个子段来分组) having （过滤分组后的信息，条件和where一样，位置不同） order by（通过哪个字段排序） limit （分页） */