1.3 查询数据

使用DQL查询数据

DQL( Data Query Language 数据查询语言 )

SELECT语法

 SELECT [ALL | DISTINCT]
 {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
 FROM table_name [as table_alias]
 [left | right | inner join table_name2] -- 联合查询
 [WHERE ...] -- 指定结果需满足的条件
 [GROUP BY ...] -- 指定结果按照哪几个字段来分组
 [HAVING] -- 过滤分组的记录必须满足的次要条件
 [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
 [LIMIT {[offset,]row_count | row_countOFFSET offset}];
 -- 指定查询的记录从哪条至哪条

 

指定查询字段

语法：

 -- 查询表中所有的数据
 SELECT * FROM 表名;
 ​
 -- 查询表中指定字段的数据
 SELECT 字段名,字段名…… FROM 表名;

 

AS 子句作为别名

作用：

• 可给数据列取一个新别名

• 可给表取一个新别名

• 可把经计算或总结的结果用另一个新名称来代替

 -- 这里是为列取别名(当然as关键词可以省略)
 SELECT studentno AS 学号,studentname AS 姓名 FROM student;
 ​
 -- 使用as也可以为表取别名
 SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
 ​
 -- 使用as,为查询结果取一个新名字
 -- CONCAT()函数拼接字符串
 -- 文本需要用''括起来
 SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

 

DISTINCT关键字的使用

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

 -- # 查看哪些同学参加了考试(学号) 去除重复项
 SELECT * FROM 成绩表; -- 查看考试成绩
 SELECT 学号 FROM 成绩表; -- 查看哪些同学参加了考试
 SELECT DISTINCT 学号 FROM 成绩表; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

 

SELECT特别作用

 -- selcet查询中可以使用表达式
 ​
 SELECT @@auto_increment_increment; -- 查询自增步长
 ​
 SELECT VERSION(); -- 查询版本号
 ​
 SELECT 100*3-1 AS 计算结果; -- 表达式
 ​
 -- 学员考试成绩集体提分一分查看
 SELECT studentno,StudentResult+1 AS '提分后' FROM result;

 

where条件语句

作用：用于检索数据表中符合条件的记录

逻辑操作符

操作符名称	语法	描述
AND 或 &&	a AND b 或 a && b	逻辑与，同时为真结果才为真
OR 或 ||	a OR b 或 a||b	逻辑或，只要一个为真，则结果为真
NOT 或 ！	NOT a 或 ！a	逻辑非，若操作数为假，则结果为真

例：

 -- 满足条件的查询(where)
 SELECT Studentno,StudentResult FROM result;
 ​
 -- 查询考试成绩在95-100之间的
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult>=95 AND StudentResult<=100;
 ​
 -- AND也可以写成 &&
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult>=95 && StudentResult<=100;
 ​
 -- 模糊查询(对应的词:精确查询)
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult BETWEEN 95 AND 100;
 ​
 -- 除了1000号同学,要其他同学的成绩
 SELECT studentno,studentresult
 FROM result
 WHERE studentno!=1000;
 ​
 -- 使用NOT
 SELECT studentno,studentresult
 FROM result
 WHERE NOT studentno=1000;

 

模糊查询 ： 比较操作符

操作符名称	语法	描述
IS NULL	a IS NULL	若操作符为NULL，则结果为真
IS NOT NULL	a IS NOT NULL	若操作符不为NULL，则结果为真
BETWEE	a BETWEEN b AND c	若 a 范围在 b 与 c 之间，则结果为真
LIKE	a LIKE b	SQL 模式匹配，若a匹配b，则结果为真
IN	a IN (a1，a2，a3，......)	若 a 等于 a1,a2..... 中的某一个，则结果为真

like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)

注：

• 数值数据类型的记录之间才能进行算术运算

• 相同数据类型的数据之间才能进行比较

 -- 模糊查询 between and \ like \ in \ null
 ​
 -- =============================================
 -- LIKE
 -- =============================================
 -- 查询姓刘的同学的学号及姓名
 -- 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 '%嘉%';
 ​
 -- 查询姓名中含有特殊字符的需要使用转义符号 '\'
 -- 自定义转义符关键字: ESCAPE ':'
 ​
 -- =============================================
 -- IN
 -- =============================================
 -- 查询学号为1000,1001,1002的学生姓名
 SELECT studentno,studentname FROM student
 WHERE studentno IN (1000,1001,1002);
 ​
 -- 查询地址在北京,南京,河南洛阳的学生
 SELECT studentno,studentname,address FROM student
 WHERE address IN ('北京','南京','河南洛阳');
 ​
 -- =============================================
 -- NULL 空
 -- =============================================
 -- 查询出生日期没有填写的同学
 -- 不能直接写=NULL , 这是代表错误的 , 用 is null
 SELECT studentname FROM student
 WHERE BornDate IS NULL;
 ​
 -- 查询出生日期填写的同学
 SELECT studentname FROM student
 WHERE BornDate IS NOT NULL;
 ​
 -- 查询没有写家庭住址的同学(空字符串不等于null)
 SELECT studentname FROM student
 WHERE Address='' OR Address IS NULL;

 

连接查询

如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询

JOIN 对比

操作符名称	描述
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表中返回所有的行

连接的类型

1. 内连接 inner join（查询两个表中的结果集中的交集）

2. 外连接 outer join

3. 左外连接 left join（以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充）

4. 右外连接 right join（以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充）

5. 等值连接非等值连接

思路:

1. 确定查询的列来源于两个类

2. 确定使用哪种连接查询

例：

 -- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
 SELECT * FROM student;
 SELECT * FROM result;
 ​
 /*思路:
 (1):分析需求,确定查询的列来源于两个类,student result,连接查询
 (2):确定使用哪种连接查询?(内连接)
 */
 SELECT s.studentno,studentname,subjectno,StudentResult
 FROM student s
 INNER JOIN result r
 ON r.studentno = s.studentno
 ​
 -- 右连接(也可实现)
 SELECT s.studentno,studentname,subjectno,StudentResult
 FROM student s
 RIGHT JOIN result r
 ON r.studentno = s.studentno
 ​
 -- 等值连接
 SELECT s.studentno,studentname,subjectno,StudentResult
 FROM student s , result r
 WHERE r.studentno = s.studentno
 ​
 -- 左连接 (查询了所有同学,不考试的也会查出来)
 SELECT s.studentno,studentname,subjectno,StudentResult
 FROM student s
 LEFT JOIN result r
 ON r.studentno = s.studentno
 ​
 -- 查一下缺考的同学(左连接应用场景)
 SELECT s.studentno,studentname,subjectno,StudentResult
 FROM student s
 LEFT JOIN result r
 ON r.studentno = s.studentno
 WHERE StudentResult IS NULL

多表连接

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
-- 多表查询
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

 

自连接

自连接就是数据表与自身进行连接

例如：

/*
自连接
     数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
     查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'