第四章_DQL查询数据

重点

4.1、DQL

Data Query Language： 数据查询语言

• 所有查询都用它 Select
• 简单、复杂的查询都可以用它
• 最核心的语言
• 使用频率最高

建立数据库

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;


-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
    `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
    `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);



-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');


-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

4.2、指定查询字段

SELECT * FROM student;  --查询所有学生
SELECT * FROM result; --查询所有成绩

-- 查询指定字段
SELECT `studentno`, `studentname` FROM student;

studentno   studentname
1000    张伟
1001    赵强

-- 使用别名  给结果起一个名字 列名换 -- 也可以给表起别名
SELECT `studentno` AS  学号, `studentname` AS 学生姓名 FROM student as s;

学号  学生姓名
1000    张伟
1001    赵强


-- 函数 concat(a,b)
SELECT CONCAT('姓名：', `studentname`) AS 新名字 FROM student;

新名字
姓名：张伟
姓名：赵强

去重复

distinct

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result;
-- 有重复数据  需要去重
SELECT DISTINCT `studentno` FROM result;

去除select 查询出来的结果中重复的数据，只显示一条

select 表达式 from 表

表达式可以为：文本值，列，Null，函数，计算表达式，系统变量

4.3、where条件字句

作用：检索数据中符合条件的值

逻辑运算符

SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 100;

模糊查询

比较运算符

like

• % 0 到任意个字符

• _一个字符

-- 以张开头
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '张%';

-- 中间有某个字  %张%

in

SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('广东深圳');

4.4、连表查询

JOIN

简单的jon理论

-- 查询参加了考试的同学(学号，姓名，科目编号，分数)

SELECT * FROM student;
SELECT * FROM result;



-- 1. 查那些字段，来自那个表
-- 2. 确定使用的查询
-- 3. 交集的确定
-- 4. 判断的条件， 表1 xx = 表2 xx


SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE   s.`studentno` = r.`studentno`;


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


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

左右表？

FROM student AS s
RIGHT JOIN result AS r   
-- 左表  s， 右表 r， 返回右表所有,即r的所有

FROM student AS s
LEFT JOIN result AS r
-- 左表s，右表r，返回左表所有的，即学生的信息，即使他没参加考试

那么如何查询缺考的学生呢？

-- 查询缺考的学生
SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` IS NULL;

• 查询的是学生
• left join
• where条件

join on和where

• join （连接的表） on 连接查询
• where 等值查询

查询：参考考试的同学信息：学号，学生姓名，科目名，分数

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`

套路：

1. 查询啥 select …
2. 那几个表 …
3. 那个表基准。Left，right，inner
4. 交叉条件
5. 一张一张表连

a left join b 以a为基准

a right join b以b为基准

自连接查询

自己的表和自己的表连接，核心：一张表拆为两张一样的表

建立测试表数据

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', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

查询父类对应的子类关系

SELECT p.`categoryname` AS '父栏目',s.`categoryname` AS '子栏目'
FROM `category` AS p, `category` AS s
WHERE p.`categoryid` = s.`pid`;

核心：把一张表拆为两张表，别名是关键

4.5 分页和排序

分类limit和排序order by

排序：升序asc，降序desc

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`
ORDER BY studentresult DESC

分页limit

为啥要分页呢？缓解数据库压力

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`
ORDER BY studentresult DESC
LIMIT 0,3

第1~3条数据

limit 起始值 页面大小

-- 第一页
limit 0,5
-- 第二页
limit 5,5
-- 第三页 
limit 10,5

-- 第n页
limit (n-1)*5,5

limit (n-1)*pagesize,pagesize 
-- n代表当前页，pagesize代表页面大小
-- (n-1)*pagesize 起始值
-- 数据总数/页面大小 = 总页数

4.6 子查询

在where语句中嵌套一个子查询语句

where( select xxx)

关联查询的效率更高