Mysql学习（三）：数据查询语⾔--DQL（内含数据库，期末复习必备）⽂章⽬录
DQL语⾔:
数据查询语⾔：主要⽤于查询数据库中的数据。
查询数据库中的数据，常使⽤的关键字是 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]-- 联合查询
[]-- 指定需要满⾜的条件
[]-- 指定结果按照哪⼏个字段来分组
[HAVING]-- 过滤分组的记录必须满⾜的次要条件
[ORDER BY]-- 指定查询记录按照⼀个或多个条件排序
[LIMIT {[OFFSET,]ROW_COUNT | row_countoffset OFFSET}];-- 分页查询
[ ] 代表可选,{} 代表必选。
创建4张表,⽤于接下来的各种操作:
student
-- 学⽣表
CREATE TABLE`student`(
`studentno`INT(4)NOT NULL COMMENT'学号',
`loginpwd`VARCHAR(20)DEFAULT'NULL'COMMENT'密码',
`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
INSERT INTO student VALUES(1000,'123456','张三',0,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888010');
INSERT INTO student VALUES(1001,'123456','李四',1,2,'138********','天津武清',CURRENT_TIME,'panxiaolan@qq','12345567888020');
INSERT INTO student VALUES(1002,'123456','王五',1,2,'138********','北京朝阳',CURRENT_TIME,'panxiaolan@qq','12345567888030');
INSERT INTO student VALUES(1003,'123456','赵六',0,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888040');
INSERT INTO student VALUES(1003,'123456','赵六',0,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888040'); INSERT INTO student VALUES(100
4,'123456','⽥七',0,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888050'); INSERT INTO student VALUES(1005,'123456','赵⼋',1,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888060'); INSERT INTO student VALUES(1006,'123456','潘九',0,2,'138********','河南安阳',CURRENT_TIME,'panxiaolan@qq','12345567888070');
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 DEFAULT CHARSET=utf8
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('1','⾼等数学-1','110','1');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('2','⾼等数学-2','130','2');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('3','⾼等数学-3','120','3');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('4','⾼等数学-4','90','4');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('5','C语⾔-1','150','1');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('6','C语⾔-2','130','2');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('7','C语⾔-3','120','3');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('8','C语⾔-4','80','4');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('9','JAVA程序设计-1','70','1');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('10','JAVA程序设计-2','100','2');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('11','JAVA程序设计-3','111','3');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('12','JAVA程序设计-4','111','4');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('13','数据结构-1','111','4');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('14','数据结构-2','111','2');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('15','数据结构-3','111','1');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('16','数据结构-4','11','2');
insert into`subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values('17','C#','111','1');
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'考试结果'
)ENGINE=INNODB DEFAULT CHARSET=utf8
insert into`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)values('1000','2','2020-11-08 21:24:49','89');
insert into`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)values('1000','1','2020-11-08 21:25:23','78');
insert into`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)values('1001','3','2020-11-08 21:
25:39','90');
insert into`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)values('1003','4','2020-11-08 21:25:47','100');
insert into`result`(`studentno`,`subjectno`,`examdate`,`studentresult`)values('1002','2','2020-11-08 21:26:12','119');
grade
-- 年级表
CREATE TABLE`grade`(
`gradeid`INT(11)NOT NULL COMMENT'年级编号',
`gradename`VARCHAR(50)NOT NULL COMMENT'年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
insert into`grade`(`gradeid`,`gradename`)values('1','⼤⼀');
insert into`grade`(`gradeid`,`gradename`)values('2','⼤⼆');
insert into`grade`(`gradeid`,`gradename`)values('3','⼤三');
insert into`grade`(`gradeid`,`gradename`)values('4','⼤四');
3.1 指定查询字段:
-- 查询所有学⽣
SELECT*FROM student;
-- 查询指定列(学号，姓名)
SELECT studentno,studentname FROM student;
AS 字句作为别名:
-- 给表中字段起别名
SELECT studentno AS`学号`,studentname AS`姓名`FROM student;
-- 给表起别名
SELECT studentno AS`学号`,studentname AS`姓名`FROM student AS s;
-- 使⽤as，为查询结果取⼀个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname)AS新姓名FROM student;
[外链图⽚转存失败，源站可能有防盗链机制，建议将图⽚保存下来直接上传(img-RlLDwBqn-1604913385510) (C:\Users\Acer\Desktop\jquery图⽚\image-20201108223914194.png)]
3.2 模糊查询:
===================模糊查询(⽐较操作符)=============== -- 模糊查询 between and \ like \ in \ null
/*
模糊查询 between and \ like \ in \ null
1、like结合使⽤的通配符 : % (代表0到任意个字符)  _ (⼀个字符)
2、查询姓名中含有特殊字符的需要使⽤转义符号 '\'
3、IN(1001,1002,1003);
4、NULL
*/
SELECT*FROM student;
-- 查询姓潘的同学的学号及姓名
SELECT studentno,studentname AS`姓名`FROM student WHERE studentname LIKE'潘%';
-- 查询姓潘的同学，后⾯只有⼀个字的
SELECT studentno,studentname AS`姓名`FROM student WHERE studentname LIKE'潘_';
-
- 查询姓潘的同学，后⾯只有两个字的
SELECT studentno,studentname AS`姓名`FROM student WHERE studentname LIKE'潘__';
-- 查询姓名中含有⼩字的
SELECT studentno,studentname AS`姓名`FROM student WHERE studentname LIKE'%⼩%';
-- in 关键字
-- 查询学号为1000,1001,1002的学⽣姓名
SELECT studentname AS`姓名`FROM student
WHERE studentno IN(1001,1002,1003);
-- 查询地址在北京，南京，河南洛阳的学⽣
SELECT studentname AS`姓名`FROM student
WHERE address IN('北京','南京','河南洛阳');
-
- NULL 关键字
-- 查询出⽣⽇期没有填写的同学
-- 注意: 不能直接写  =NULL，需要写错 is NULL
SELECT studentno,studentname FROM student
WHERE borndate IS NULL;
-- 查询出⽣⽇期填写的同学
SELECT studentno,studentname FROM student
WHERE borndate IS NOT NULL;
-- 查询没有写家庭住址的同学
SELECT studentno,studentname FROM student
WHERE address =''OR address =NULL;
3.3 连接查询(重要):
===================连接查询==================
/*
/*
连接查询，需要多张数据表进⾏查询时就需要进⾏联表
1、内连接 inner join
2、外连接:
左外连接 left join
右外连接 right join
3、⾃连接
4、等值连接和⾮等值连接
*/
-- 查询参加了考试的同学信息(学号、学⽣姓名、科⽬编号、分数)
SELECT*FROM student;
SELECT*FROM result;
/*
通过分析，可以了解到, 我们所要查询的信息在两个表中，那么如何
将这个两个表连接到⼀起呢?
内连接。
*/
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno;
-- 右连接查询
-- (以右表作为基准，左边表来⼀⼀匹配，匹配不上的，返回右表的记录，左表以NULL填充) 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;
-- 左连接查询(查询了所有同学，不考试的也会查出来)
-- (以左表作为基准，右边表来⼀⼀匹配，匹配不上的，返回左表的记录，右表以NULL填充) 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
INNER JOIN`subject` p
ON s.studentno = r.studentno AND r.subjectno = p.subjectno;
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