MySQL(DQL查询数据)

---

DQL查询数据

1 DQL

• Data Query Language：数据查询语言
• 所有数据查询工作都要用SELECT
• 是最核心、使用频率最高的语言

2 查询指定字段

SELECT学习的数据库sql代码
提取码：kksj

-- 简单查询 SELECT 字段(表达式) FROM 表

SELECT * FROM `student`; -- 查询全部学生
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;
SELECT `studentno` AS 学号,`studentname` FROM `student` AS stu; -- 可以给字段和表起别名(不会实际改变字段和表的名称)
-- 函数 Concat(a,b)拼接
SELECT CONCAT('姓名: ', studentname) AS 新名字 FROM student;

-- 去重DISTINCT 去除重复数据
SELECT distinct `studentno` FROM result; -- 学号重复的只会显示一个

-- 学员考试成绩全加一
SELECT `studentno`,`studentresult`,`studentresult`+1 AS 新成绩 FROM result; 

SELECT VERSION(); -- 查询系统版本(函数)
SELECT 100*3 AS 计算结果; -- 计算表达式
SELECT @@auto increment increment; -- 查询自增步长(变量) 

3 使用WHERE条件子句查询

3.1 逻辑运算符

操作符	作用
AND OR NOT	逻辑与或非，返回结果为布尔值

-- WHERE子句
SELECT `studentno`,`studentresult` FROM result 
WHERE studentresult>=95 AND studentresult<=100; -- 也可用between...and...

SELECT `studentno`,`studentresult` FROM result WHERE NOT studentno=1000;

3.2 模糊查询(比较运算符)

操作符	作用
IS NULL、IS NOT NULL	字段为空或不为空则返回true
= 、<>或!=、>等比较运算符	满足条件会返回布尔值并执行前面的修改语句
字段 BETWEEN from AND to	在[from,to]闭区间内的值会执行修改语句
LIKE	SQL匹配，若a匹配b(a LIKE b)，返回真
IN	a IN (a1,a2,a3…)，若a在a1或a2或a3中，返回真

-- 模糊查询 --

-- 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 '%强%';

-- IN 不能用通配符,后面可嵌套SELECT语句
-- 查询1000，1001，1002号学员
SELECT `studentno`,`studentname` FROM `student` 
WHERE `studentno` IN (1000,1001,1002);

-- NULL,NOT NULL
SELECT `studentno`,`studentname` FROM `student` 
WHERE `studentno` IS NULL;

4 联表查询(JOIN)

7种SQL JOIN查询：

当我们使用连接操作，关联两张或多张表来返回记录时，数据库都会生成一张临时表，最后将这张临时表返回给用户。
以 LEFT JOIN 为例：在使用 LEFT JOIN 时，ON 和 WHERE 过滤条件的区别如下：

• ON 条件是在生成临时表时使用的条件，它不管 ON 中的条件是否为真，都会返回左边表中的记录；
• WHERE 条件是在临时表已经生成后，对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义（必须返回左侧表的记录）了，所以如果 WHERE 条件不为真的记录就会被过滤掉。

摘自(SQL中的ON和WHERE的区别)

看个例子更好理解：
student表

result(成绩)表

可看到只有1000号同学有成绩。下面以student表为左表，result表为右表

-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s INNER JOIN result AS r -- -- 取别名是因为两张表都有`studentno`
ON s.`studentno` = r.`studentno`;
-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`;

上面两段代码执行结果均为

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`;

因此，ON 条件是在生成临时表时使用的条件，它不管 ON 中的条件是否为真，都会返回左边表中的记录。

-- 查询没有成绩同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL; -- 对临时表再进行筛选(WHERE)

联多张表进行查询，在上面的基础上将科目序号改为科目名称

subject表：

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s RIGHT JOIN result r -- 起别名可以不加AS，中间用空格隔开
ON s.`studentno` = r.`studentno`
INNER JOIN `subjects` su 
ON r.`subjectno` = su.`subjectno`;

5 自连接

-- 建张新表
CREATE TABLE `school`.`category`( 
	`categoryid` INT(3) NOT NULL COMMENT 'id', 
	`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
	`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
	PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

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

解决自连接(树形结构)的核心是将一张表拆为两张：
父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	PS技术

SELECT p.`categoryname` AS '父类',c.`categoryname` AS '子类'
FROM `category` AS p, `category` AS c
WHERE p.`categoryid` = c.`pid`;

6 分页和排序

-- 分页LIMIT 和 排序ORDER BY(升序 ASC，降序 DESC) --

-- 排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subjects` su 
ON r.`subjectno` = su.`subjectno`
ORDER BY `studentresult` DESC -- 将成绩降序排列
-- 分页(必须在ORDER BY下面) 格式：LIMIT (n-1)*pageSize,pageSize（第n页数据）
LIMIT 0,3;

7 子查询和嵌套查询

在where语句中嵌套子查询

-- 查询某科目的所有考试结果

-- 方式一：join查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r INNER JOIN `subjects` s
ON r.`subjectno` = s.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC

-- 方式二：子查询（由里到外）
SELECT `studentno`,`subjectno`,`studentresult` FROM `result`
WHERE `subjectno` = (
	SELECT `subjectno` FROM `subjects`
	WHERE `subjectname`='C语言-1'
)ORDER BY `studentresult` DESC;

8 MySQL函数

8.1 常用函数

汇总

8.2 聚合函数(常用)

函数名	作用
MAX()	查询指定列的最大值
MIN()	查询指定列的最小值
COUNT()	统计查询结果的行数
SUM()	求和，返回指定列的总和
AVG()	求平均值，返回指定列数据的平均值