|
准语法结构:编写DQL时一定要严格按照此语法的顺序来实现!
- /*
- SELECT [ALL | DISTINCT] ALL表示查询出所有的内容 DISTINCT 去重
- {* | 表名.* | 表名.字段名[ AS 别名][,...]} 指定查询出的字段的
- FROM
- 表名[AS 别名][,表1... AS 别名]
- [INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]
- [WHERE 条件]
- [GROUP BY 分组字段[,...]]
- [HAVING 给分组后的数据进行条件筛选]
- [ORDER BY 排序字段[,...]]
- [LIMIT [startIndex,]pageSize]
复制代码
DQL是SQL中最核心的部分!
一、MySQL的标准查询语句结构
SELECT [ ALL | DISTINCT ]
{ * | 表名.* | 表名.字段名…… [ AS 别名] }
FROM 要查询的表名 [ AS 别名 ]
[ LEFT | RIGHT | INNER JOIN 连接表名 [ AS 别名] ]
SELECT 后 FROM 前的这一块内容,它是用来筛选查询字段的
简单理解就是你想查看那些信息
1.查询数据库列表
2.查询数据表列表
- -- 查询当前连接的数据库的数据表列表
- show tables;
- -- 如果没有连接
- show tables from 数据库名;
复制代码
3.查询数据表的字段
- desc/describe table 数据库名.表名;
- show columns from 数据表名 from 数据库名;
- show columns from 数据库名.表名;
- -- 查询当前已连接的数据表列表
- desc 表名;
- 示例:
- mysql> desc table person_db.student;
- mysql> show columns from student from person_db;
- mysql> show columns from person_db.student;
- mysql> desc student;
- -- 查询指定字段名
- desc/describe 数据库名.表名 字段名;
- -- 查询当前已连接的数据表
- desc 表名 字段名;
- 示例:
- describe person_db.student0 sex;
- desc student0 sex;
复制代码
4.查询当前数据库的数据表内容
- select * from 表名;
- 示例:
- select * from student0;
复制代码
5.查询学生表中的学生姓名和性别
- SELECT stuName,gender FROM student;
- SELECT stuName AS '姓名',gradeId AS '年级' FROM student;
- -- AS 关键字可以省略 ' '也可以省略
- SELECT stuName '姓名',gradeId + 1 '年级' FROM student;
- -- 如果SQL过长可以进行美化
- SELECT stuName AS '姓名',
- gradeId + 1 '年级',
- FROM
- student;
复制代码
二、ALL和DISTINCT关键字
ALL所有的、全部; DISTINCT直接的、明显的 (去重)
- -- ALL 是默认的
- -- SELECT ALL stuName,gradeId FROM student;
- -- DISTINCT 用来在指定的查询字段值范围内,去除重复数据
- 示例:
- SELECT DISTINCT stuName,gradeId FROM student;
- SELECT DISTINCT stuName,gender,gradeId FROM student;
复制代码
2.1 where 查询条件
在修改和删除时用过 目的是为了防止修改/删除全表
用于检索数据表中符合条件的记录的
简单理解:上方的操作是用来筛选列的 where是用来筛选行的
在Where条件语句中,可以有一个或者多个逻辑表达式组成,结果一般为真或假
2.2<关系/比较运算符和逻辑运算符>
- SELECT * FROM student;
- -- 查询年级时大于1的学生信息
- SELECT * FROM student WHERE gradeId > 1;
- -- 复杂条件的处理:逻辑运算符 与AND 或OR 非NOT
- -- 查询姓名为张三且性别为女的学生信息
- SELECT * FROM student WHERE stuName = '张三' AND gerder = '女';
- -- 查询性别是女的,或者年级为3的
- SELECT * FROM student WHERE gender = '女' OR gradeId = 3;
- -- 查询性别不是女的
- SELECT * FROM student WHERE NOT gender = '女';
复制代码
2.3特殊的比较运算符
IS NULL(查询空) IS NOT NULL(查询不为空)
- -- 查询地址为空的学生姓名 不能使用=
- SELECT stuName FROM student WHERE address IS NULL;
- SELECT stuName FROM student WHERE address IS NOT NULL;
- -- BETWEEN … AND 在… 之间/范围内 它等价于>= and <=
- -- 查询年级在2-3之间的学生姓名
- SELECT stuName FROM student WHERE gradeID >= 2 AND gradeID <= 3;
- -- 更加简洁
- SELECT stuName FROM student WHERE gradeId BETWEEN 2 AND 3;
- -- IN查询 在…内/里面
- -- 查询年级为1或年级为3的学生信息
- SELECT * FROM student WHERE gradeId = 1 OR gradeId = 3;
- SELECT * FROM student WHERE gradeId IN (1,3);
复制代码
三、模糊查询
LIKE 像…一样 喜欢
%表示任意单个/多个字符 _表示任意单个字符
- -- 查询姓为张的学生信息
- SELECT * FROM student WHERE stuName LIKE '张';
- -- 查询姓张的两个字的学生信息
- SELECT * FROM student WHERE stuName LIKE '张_';
- -- 查询名称中带有三的学生信息
- SELECT * FROM student WHERE stuName LIKE '%张%';
- -- 查询三是姓名第二个字符的学生信息
- SELECT * FROM student WHERE stuName LIKE '_三%';
复制代码
四、连接查询(多表连查)
连接查询:内连接查询、外连接查询、【自连接查询】
内连接:显式内连接、隐式内连接
- -- 查询学生的姓名和所在年级
- SELECT stuName,gradeId FROM student;
- -- 采用显式内连接[推荐给多表起别名,区分清楚,防止出现模糊不清错误]
- -- 内连接可以理解为交集
- SELECT
- stu.stuName,g.gradeName
- FROM
- student stu
- INNER JOIN
- grade g
- ON
- stu.gradeId = g.gradeId;
- -- 笛卡尔积
- SELECT
- stu.stuName,g.gradeName
- FROM
- student stu,grade g;
- -- 采用隐式内连接查询
- SELECT
- stu.stuName,g.gradeName
- FROM
- student stu,grade g
- WHERE
- stu.gradeId = g.gradeId;
- -- 查询所有学生姓名、课程名称、考试成绩
- SELECT
- stu.stuName,sub.subjectName,r.result
- FROM
- student stu,'subject' sub, result r
- WHERE
- stu.stuId = r.stuId
- AND
- sub.subjectId = r.subjectId;
- -- 外连接:左外链接、右外连接
- -- 查询学生的姓名和所在年级
- -- 采用左外连接 以左表为主 LEFT JOIN前面的就是左表
- SELECT
- stu.stuName,g.gradeName
- FROM
- student stu
- LEFT OUTER JOIN grade g
- ON stu.gradeId = g.gradeId;
- -- 采用右外连接
- SELECT
- stu.stuName,g.gradeName
- FROM
- student stu
- RIGHT OUTER JOIN grade g
- ON stu.gradeId = g.gradeId;
- -- 自连接(内连接)
- -- 查询游戏名称和所属分类
- SELECT
- c1.categoryName ,c2.categoryName
- FROM
- category c1,category c2
- WHERE
- c1.pid = c2.id;
复制代码
五、排序查询 ORDER BY
排序:学生成绩、游戏排行榜(金额、战力、等级…)、音乐播放热度、淘宝销量
价格、评分、美团外卖距离…
ORDER BY 默认情况下为升序排序即ASC(Ascend) 降序DESC(Descend)
- -- 需求:查询出学生姓名、课程名称、成绩 并且按照成绩进行降序排序
- SELECT
- -- 要查询的字段名
- stu.stuName,sub.subjectName,r.result
- FROM
- -- 表名
- result r,student stu,`subject` sub
- WHERE
- -- 关联条件
- r.stuId = stu.stuId AND r.subjectId = sub.subjectId
- -- 排序字段
- ORDER BY r.result DESC;
复制代码
需求:在上方基础进行改动 要求按照成绩进行降序 且如果成绩相同按照时间降序
- -- 需求:只想看前两条学生信息
- SELECT
- *
- FROM
- student
- LIMIT 0,2; -- 如果offset是从0开始 那么可以省略 LIMIT 2
复制代码- SELECT
- stu.stuName,sub.subjectName,r.result,r.examDate
- FROM
- result r,student stu,`subject` sub
- WHERE
- r.stuId = stu.stuId AND r.subjectId = sub.subjectId
- ORDER BY
- r.result DESC , r.examDate DESC; (X) ORDER BY r.result,r.examDate DESC;
复制代码
六、限制查询(分页) LIMIT
LIMIT offset,row;
LIMIT startIndex起始索引<从0开始>,pageSize 分页场景
分页:因为数据量比较大的时候,如果把所有数据都显示在一页上
既不利于阅读也不利于定位查找。所以要对数据进行分页。
假设有20条数据 现在可以将数据拆分成4条每页 则有5页(在Java Web有对其进行业务的实现)
假设有21条数据 现在可以将数据拆分成4条每页 则有6页(多余一条自己占一页)
LIMIT可以在我们的MySQL中实现分页的数据查询/指定页码的数据查询
需求:现在学生信息要求每页显示2条 想查看第一页的数据
- SELECT * FROM student LIMIT 0,2;
- 要求查看第二页的数据
- SELECT * FROM student LIMIT 2,2;
- 要求查看第三页的数据
- SELECT * FROM student LIMIT 4,2;
- 页码和起始索引的计算公式:(页码 - 1) * 显示条数
- startIndex = (currentPage - 1) * pageSize;
复制代码
七、MySQL子查询 ()
子查询:在一个查询中又嵌套了其他的查询,那么嵌套的查询就被称为子查询,而外层的查询被称为父查询。
子查询可以任意嵌套!可以出现在INSERT UPDATE DELETE WHERE等中…
建议在初期写子查询时 先将查询进行步骤化
需求:查询在高等数学考试中,成绩比张三高的学生的姓名信息
如果不考虑连表查询
- -- 先写出比张三高的学生ID
- SELECT
- stuId
- FROM
- result
- WHERE
- subjectId = 1
- AND
- result > 86;
复制代码- -- 1.查询高等数学的课程编号
- SELECT
- subjectId
- FROM
- `subject`
- WHERE
- subjectName = '高等数学';
- -- 整合
- SELECT
- stuId
- FROM
- result
- WHERE
- subjectId = (
- SELECT
- subjectId
- FROM
- `subject`
- WHERE
- subjectName = '高等数学'
- )
- AND
- result > 86;
- -- 2.查询张三的高数考试成绩
- SELECT
- result
- FROM
- result
- WHERE
- stuId = (SELECT stuId FROM student WHERE stuName = '张三')
- AND subjectId = (SELECT subjectId FROM `subject` WHERE subjectName = '高等数学');
- -- 整合
- SELECT
- r.stuId,stu.stuName
- FROM
- result r,student stu
- WHERE
- r.stuId = stu.stuId
- AND
- subjectId = (
- SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'
- )
- AND
- result > (
- SELECT result FROM result WHERE stuId = (
- SELECT stuId FROM student WHERE stuName = '张三'
- )
- AND subjectId = (
- SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'
- )
- );
- -- 使用子查询解决连表查询
- -- 要求查询学生姓名、年级名称
- -- 原来
- SELECT stu.stuName,g.gradeName FROM student stu,grade g WHERE stu.gradeId = g.gradeId;
- -- 子查询
- SELECT
- stu.stuName,
- (SELECT g.gradeName FROM grade g WHERE g.gradeId = stu.gradeId) AS gradeName
- FROM
- student stu;
复制代码
八、MySQL常用函数
- -- 常用的字符串函数
- -- 4.1 字符串拼接 CONCAT(str1,str2,...)
- SELECT CONCAT('这是','MySQL','数据库');
- -- 4.2 字符串内容替换 REPLACE(str,from_str,to_str)
- SELECT REPLACE('这是MySQL数据库','MySQL','Oracle');
- -- 4.3 去除左侧空格
- SELECT LTRIM(' Hello World ');
- -- 去除右侧空格
- SELECT RTRIM(' Hello World ');
- -- 4.4 获取字符串长度
- SELECT LENGTH('Hello');
- -- 4.5 截取字符串SUBSTR(str,pos) 索引从1开始
- SELECT SUBSTR('Hello World',5);
- -- SUBSTR(str,pos,len)
- SELECT SUBSTR('Hello World',5,3);
复制代码- -- 常用的数学函数
- -- 4.6 获取随机数 Math.random();
- SELECT RAND();
- -- 4.7 向上取整
- SELECT CEIL(20.4);
- -- 向下取整
- SELECT FLOOR(20.7);
- -- 4.8 四舍五入
- SELECT ROUND(20.5);
- SELECT ROUND(20.76,1)
复制代码- -- 常用的时间函数
- -- 4.9 获取当前时间
- SELECT NOW();
- SELECT CURRENT_DATE();
- SELECT CURRENT_TIME();
复制代码- -- 4.10 获取各种时间信息的函数
- SELECT MONTH(NOW());
- SELECT YEAR(NOW());
- SELECT DAY(NOW());
- SELECT HOUR(NOW());
- SELECT MINUTE(NOW());
- SELECT SECOND(NOW());
复制代码- -- 4.11 日期转换
- SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
- -- 4.12 计算时间差额
- SELECT DATEDIFF(NOW(),'2019-05-25');
复制代码- -- 常用的聚合/统计函数
- -- 求年级编号最大的
- SELECT MAX(gradeId) FROM grade;
- -- 求年级编号最小的
- SELECT MIN(gradeId) FROM grade;
- -- 求平均分
- SELECT AVG(result) FROM result WHERE subjectId = 1;
- -- 求和
- SELECT SUM(result) FROM result WHERE subjectId = 1;
- -- 计算学生总人数
- SELECT COUNT(*) FROM student;
- SELECT COUNT(stuId) FROM student;
- SELECT COUNT(1) FROM student;
复制代码
九、分组查询GROUP BY
注意事项:在分组查询的查询字段中 不要出现与分组查询中无关的字段值
- -- 需求:计算各个年级的学生人数
- SELECT gradeId,COUNT(1) FROM student GROUP BY gradeId;
- -- 如果我希望查看 各个年级的总人数 且要求查看对应的学生姓名列表
- SELECT gradeId,COUNT(1),GROUP_CONCAT(stuName) FROM student GROUP BY gradeId;
- -- 计算各个年级各个性别的人数
- SELECT gradeId,gender,COUNT(1) FROM student GROUP BY gradeId,gender;
- -- 需求:查询年级的学生人数大于1人的年级名称
- SELECT
- stu.gradeId,count(1),g.gradeName
- FROM
- student stu,grade g
- WHERE
- stu.gradeId = g.gradeId
- GROUP BY
- gradeId
- HAVING
- count(1) > 1;
复制代码- /*
- HAVING和WHERE的区别?
- 相同点:都可以用来进行条件判断 筛选行数
- 不同点:
- 位置不同:WHERE是在分组之前 HAVING是在分组之后
- 条件筛选不同:WHERE是给FROM查询到的数据进行条件筛选 而HAVING是对分组后的数据进行筛选
- */
复制代码 |
|