MySQL数据管理（二）

1.DQL查询数据

• (Data Query Language) :数据查询语言
• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

SELECT 语法

• 注意 : [ ] 括号代表可选的 , { }括号代表必选得

SELECT [ALL | DISTINCT] -- 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}];
   -- 指定查询的记录从哪条至哪条

• 测试数据SQL

-- 创建一个school数据库
create  database  `school`;
-- 创建一张年级表
use `school`;
drop table if exists `grade`;
 create  table  `grade`(
     `GradeID` int(4) not null auto_increment  comment '年级编号',
       `GradeName` varchar(50) not  null comment '年级名称',
      primary  key (`GradeID`)
 )ENGINE=INNODB AUTO_INCREMENT=6  DEFAULT CHARSET=utf8;

-- 添加数据插入年级数据
insert  into `grade` (`GradeID`, `GradeName`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 创建reult 成绩表
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 `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);

-- 创建 student学生表
drop table if exists `student`;
create  table  `student`(
   `StudentNo` int(4) not null  comment '学号',
    `LoginPwd` varchar(20) not null,
    `StudentName` varchar(20) DEFAULT  NULL comment '学生姓名',
    `Sex` TINYINT(1) DEFAULT NULL  comment  '性别，取值0或1',
    `GradeID` int(4) 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=INNODB DEFAULT CHARSET=utf8;

-- 添加数据插入学生数据 
insert  into  `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`, `GradeID`,`Phone` ,`Address`,`BornDate`, `Email`, `IdentityCard`) values(1001,'123456','李四',1,3,'18786506942','山海','1999-12-11 00:00:00','test@qq.com','522428199912110812'),(1002,'123456','李林',0,3,'18786506942','西安','1999-02-11 00:00:00','test@qq.com','522428199912110832'),(1003,'123456','韩立',1,3,'1878655542','北京','1999-02-11 00:00:00','test@qq.com','412428199912110812');

-- subject  表
-- 创建科目表
drop table if exists `subject`;
create  table `subject`(
  `SubjectNO`  int(4) 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 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);

1.1 指定查询字段

• 查询指定字段

-- 查询全部的学生   SELECT 字段 FROM 表名;
  SELECT * FROM student;
  
-- 查询指定字段
  SELECT student_name, student_no FROM student;

• AS 别名

-- 别名，给结果起一个名字 AS  可以给字段起别名，也可以给表起别名
  
  SELECT student_name AS '学号', student_no AS '姓名' FROM student;
  
-- 函数 concat(a,b)
  SELECT CONCAT('姓名：', student_no)  AS '新姓名' FROM student;

• 语法 ： SELECT 字段,... FROM 表名
• 有的时候，列名字不是那么的见名知义。所以我们会对其起别名 AS 字段名 as 新名字

• 去重 distinct： 作用：去除Select 查询出来的结果中重复的数据 只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentNo` FROM result 
-- 发现重复数据，去重
SELECT DISTINCT `studentNo` FROM result 

• 数据库的列（表达式）

SELECT VERSION()  -- 查询系统版本 （函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算 （表达式）
SELECT @@auto_increment_increment  -- 查询自增的步长 （变量）

-- 学生年级 +1 查看
SELECT `name`,`gradeid` +1 AS '升学后' FROM student

• 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量
• 语法： select 表达式 from 表名

1.2 where 条件子句

• 作用：检索数据中符合条件的值
• 搜索的条件由一个或者多个表达式组成，结果布尔值

逻辑运算符

|        运算符        |        语法      |       描述      |
| :------:| :------:| :------:|
| and    && | a and b    a&&b| 逻辑与两个都为真，结果为真|
| or  $\mid$$\mid$| a or b  a$\mid$$\mid$b| 逻辑或，其中一个为真则结果为真|
| not  !| not a   !a|逻辑非，真为假，假为真|

• 尽量使用英文字母

-- ====================  where  ====================

-- 查询考试成绩在 95 ~ 100分之间

SELECT student_no,student_result FROM result;

-- and
SELECT student_no,student_result FROM result
WHERE student_result>95 AND student_result<=100;
-- && 
SELECT student_no,student_result FROM result
WHERE student_result>95 && student_result<=100;


-- 模糊查询（区间）
SELECT student_no,student_result FROM result
WHERE student_result BETWEEN 95 AND 100;

-- 除了1000号学生之外的学生的成绩
-- !=
SELECT student_no,student_result FROM result
WHERE student_no != 1000;
-- not
SELECT student_no,student_result FROM result
WHERE NOT student_no = 1000;

• 模糊查询操作符详解

模糊查询：比较运算符

|        运算符        |        语法      |       描述      |
| :------:| :------:| :------:|
| IS NULL | a is null| 如果操作符为null，结果为真|
| IS NOT NULL| a is not null|如果操作符不为null，结果为真|
| BWTWEEN…AND…| a between b and c| 若a在b和c之间，则结果为真|
| LIKE| a like b|SQL匹配，如果a匹配b,则结果为真|
| IN| a in (a1,a2,a3,…)|假设a在a1或者a2或者a3,…其中的某一个，则结果为真|

-- ====================  模糊查询  ======================
  
 -- 查询姓刘的同学

-- like 结合 
--   %（代表0到任意个字符）
--   _（代表1）

-- 查询姓刘的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘%';

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘_';

-- 查询姓刘的同学，名字后面有两个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘__';

-- 查询名字中间有嘉字的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '%%嘉%';

===================IN(具体的一个或者多个值)===========================
-- in (具体的一个或者多个值)
-- 查询学号1001,1002,1003号学号
SELECT `student_no`,`student_name` FROM `student`
WHERE student_no IN ('1001','1002','1003');

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

-- 查询在北京的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address IN ('北京');

===================NULL NOT NULL===================================
-- null
-- 查询地址为空的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address = '' OR address IS NULL;

-- not null
-- 查询有出生日期的同学 不为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NOT NULL;

-- 查询没有出生日期的同学 为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NULL;

1.3 联表查询

• JOIN对比与 七种jion理论

• 联表查询 join

-- =====================联表查询 join ==============================

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student;
SELECT * FROM result;


/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo 

*/
-- join on 连接查询
-- where 等值查询


-- inner join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student AS st
INNER JOIN result AS re ON 
st.`student_no`=re.`student_no`;

-- right join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
RIGHT JOIN result re ON 
st.`student_no`=re.`student_no`;

-- left join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
LEFT JOIN result re ON 
st.`student_no`=re.`student_no`;

-- 查询缺考的同学
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result` 
FROM student st
LEFT JOIN result re ON 
st.`student_no`=re.`student_no`
WHERE re.`student_result` IS NULL;

-- 查询了参加考试的同学信息（学号，学生姓名，科目名称，分数）
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM `student` stu
RIGHT JOIN `result` res 
ON res.`student_no`=stu.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`;

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT `student_no`,`student_name`,`grade_name`
FROM student stu
INNER JOIN `grade` gra
ON stu.`grade_id`=gra.`grade_id`;

-- 查询了参加数据结构-1考试的同学信息（学号，学生姓名，科目名称，分数）
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1';


-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

--FROM a LEFT JOIN b   左为准
--FROM a RIGHT JOIN b    右为准

|        操作        |        描述      |
| :------:| :------:|
| inner join | 如果表中至少有一个匹配，就返回行|
| left join| 会从左边中返回所有的值，即使右表中没有匹配|
| right join| 会从右边中返回所有的值，即使左表中没有匹配|

• 自连接及联表查询
• 自连接: 自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

|        pid        |        category_id      |       category_name      |
| :------:| :------:| :------:|
| 1 | 2| 信息技术|
| 1| 3|软件开发|
| 1| 5| 美术设计|

|        pid        |        category_id      |       category_name      |
| :------:| :------:| :------:|
| 3| 4| 数据库|
| 2| 8|办公信息|
| 3| 6| web开发|
| 5| 7| ps技术|

• 操作：查询父类对应的子类关系

|        父类        |        子类      |
| :------:| :------:|
| 信息技术| 办公信息|
| 软件开发| 数据库|
| 软件开发| web开发|
| 美术设计| ps技术|

• 创建自连接需要的表

-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

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

SELECT * FROM `category`;

• 自连接

-- 查询父子信息，把一张表看为两个一模一样的表
SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`category_id`=b.`pid`;

1.4 分页和排序

• 排序

-- 排序： 升序 ASC  降序 DESC
-- ORDER BY 通过那个字段排序，怎么排
-- 查询的结果根据成绩降序 排序
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC;

• 分页

-- 100w
-- 为什么要分页
-- 缓解数据库压力，给人更好的体验   瀑布流
-- 分页，每页只显示五条数据
-- 语法 ： limit 起始值，页面的大小
-- 网页应用：当前，总的页数，每页大小
-- LIMIT 0,5    1~5
-- LIMIT 1,5    2~6
-- LIMIT 6,5
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC
LIMIT 1,5;
-- 第一页 limit 0,5    (1-1)*5
-- 第二页 limit 5,5    (2-1)*5
-- 第三页 limit 10,5   (3-1)*5
-- 第N页 limit 10,5    (n-1)*pageSize,pageSize
-- pageSize,页面大小
-- (n-1)*pageSize，起始值
-- n，当前页
-- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)

-- 思考：
-- 查询科目高等数学-2，课程成绩排名前十的学生，并且分数要大于60的学生信息（学号，姓名，课程名称，分数）
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `subject` sub
ON stu.`grade_id`=sub.`grade_id`
INNER JOIN `result` res
ON sub.`subject_no`=res.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result`>60
ORDER BY res.`student_result`
LIMIT 0,10;

• 语法： limit (查询起始下标,页面大小)

1.5 子查询和嵌套查询

• where (这个值是计算出来的)
• 本质： 在where语句中嵌套一个子查询语句

/*============== 子查询和嵌套查询 ================
什么是子查询?
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等数学-2'
)

--  再次改造（由里及外）
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来(嵌套查询)
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
  )
)

1.5 分组和过滤

• 分组和过滤

 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
 */

• HAVING：再筛选

1.6 select 小结

• 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小结中文解释
顺序很重要：

select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）
XXX join 要连接的表 on 等值判断
where （具体的值，子查询语句）
Group by (通过哪个字段来分组)
Having （过滤分组后的信息，条件和where是一样的，位置不同）
Order by (通过哪个字段排序)[升序/降序]
Limit startindex，pagesize

业务层面：
查询：跨表，跨数据库