数据库查询语言

3.Mysql：使用DQL查询数据

3.1DQL语言

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

• 查询数据库数据 , 如SELECT语句
• 简单的单表查询或多表的复杂查询和嵌套查询
• 是数据库语言中最核心,最重要的语句
• 使用频率最高的语句

-- 创建学生信息表
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number`
VARCHAR(20) DEFAULT NULL COMMENT '学生编号',
`name`
VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`birthday` VARCHAR(20) DEFAULT NULL COMMENT '出生年月日',
`sex`
INT(1) DEFAULT NULL COMMENT '性别(0 男 1 女 2 未知)',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='学生信息表';

INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190901','小王
','1995-01-01',0);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190902','小张
','1994-03-06',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190903','小李
','1995-02-07',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190904','小红
','1997-08-22',0);

-- 创建课程信息表
DROP TABLE IF EXISTS `course_table`;
CREATE TABLE `course_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '课程编号',
`name` VARCHAR(20) DEFAULT NULL COMMENT '课程名称',
`teacherNumber` VARCHAR(20) DEFAULT NULL COMMENT '教师编号',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='课程信息表';

INSERT INTO course_table(number,NAME,teacherNumber) VALUES('100','英语','T001');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('101','高数','T002');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('102','思修','T003');

-- 创建成绩信息表
DROP TABLE IF EXISTS `score_table`;
CREATE TABLE `score_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`st_number` VARCHAR(20) DEFAULT NULL COMMENT '学生编号',
`co_number` VARCHAR(20) DEFAULT NULL COMMENT '课程编号',
`score`
INT(20) DEFAULT NULL COMMENT '课程分数',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='成绩信息表';
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','100',80);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','101',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','100',95);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','101',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','102',70);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','100',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','101',79);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','102',85);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','100',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','101',70);

-- 创建教师信息表
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '教师编号',
`name` VARCHAR(20) DEFAULT NULL COMMENT '教师姓名',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='教师信息表';

INSERT INTO teacher_table(number,NAME) VALUES('T001','张三');
INSERT INTO teacher_table(number,NAME) VALUES('T002','张四');
INSERT INTO teacher_table(number,NAME) VALUES('T003','王五');

指定查询字段

查询表结果时 , 可指定查询结果的数据列

• 查询表中所有的数据列结果 , 采用 **" * "**符号;
  • 如 : SELECT * FROM student;
  • 但是效率低 , 不推荐 。

-- 指定查询字段
-- 查询全部的学生 
SELECT * FROM `student_table`

-- 查询指定字段
SELECT `number`,`name`,`birthday` FROM `student_table`

AS子句作为别名

AS子句作用 :

• 可给数据列取一个新别名
• 可给表去一个新别名
• 可把经计算或总结的结果用另一个新名称来代替

-- 给结果起别名 AS 可以给字段起别名，也可以给表起别名
SELECT `number` AS '学生学号' ,`name` AS 学生姓名,`birthday`AS 出生日期 FROM `student_table`AS 学生信息表

经我验证，发现 AS 别名可以加单引号，也可以不加.

-- 函数 Concat(a,b)
-- 单个拼接
SELECT CONCAT('姓名: ',`name`)AS 学生姓名 FROM `student_table`
-- 多个拼接
SELECT CONCAT('姓名: ',`name`)AS 学生姓名,CONCAT('学号: ',`number`) AS 学生信息 FROM `student_table`

SQL练习：

• 查询出每门课都大于等于 80 分的学生学号和姓名？

-- 查询出每门课都大于等于 80 分的学生学号和姓名
SELECT a.number,a.name
FROM student_table a
LEFT JOIN score_table AS b ON b.st_number=a.number
GROUP BY b.st_number HAVING MIN(b.score)>=80;

• 查询课程英语的成绩比课程高数的成绩高的所有学生的学号？

-- 查询课程英语的成绩比课程高数的成绩高的所有学生的学号
SELECT a.st_number FROM(SELECT st_number,score FROM score_table WHERE co_number='100') a,
(SELECT st_number,score FROM score_table WHERE co_number='101') b
WHERE a.score>b.score AND a.st_number=b.st_number;

• 查询平均成绩大于 60 分的同学的学号和平均成绩？

-- 查询平均成绩大于 60 分的同学的学号和平均成绩
SELECT a.st_number AS '学号',AVG(a.score) AS '平均成绩'
FROM (SELECT st_number,score FROM score_table) a
GROUP BY a.st_number HAVING AVG(a.score)>60;

• 查询所有同学的学号、姓名、选课数、总成绩 ？

-- 查询所有同学的学号、姓名、选课数、总成绩
SELECT a.st_number AS 学号, b.name AS 姓名,
COUNT(a.co_number) AS 选课数, SUM(a.score) AS 总成绩
FROM score_table a,student_table b
WHERE a.st_number = b.number
GROUP BY a.st_number, b.name

• 查询姓“张”的老师的个数？

-- 查询姓“张”的老师的个数
SELECT COUNT(*) FROM teacher_table WHERE NAME LIKE '张%';

• 查询同时学过英语课程和思修课程的同学的学号、姓名？

-- 查询同时学过英语课程和思修课程的同学的学号、姓名
SELECT number, NAME FROM student_table
WHERE number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =100)
AND number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =102);

• 查询学过”王五“老师所教课程的所有同学的学号、姓名？

-- 查询学过”王五“老师所教课程的所有同学的学号、姓名
SELECT a.number,a.name FROM student_table a,score_table b
WHERE a.number = b.st_number AND b.co_number
IN(SELECT c.number FROM course_table c,teacher_table d WHERE c.teacherNumber = d.number AND
d.name ='王五');

• 查询各科成绩最高分和最低分？

-- 查询各科成绩最高分和最低分
SELECT ct.name AS 课程, MAX(st.score) AS 最高分,MIN(st.score) 最低分
FROM score_table st
LEFT JOIN course_table ct ON ct.number=st.co_number
GROUP BY st.co_number;

去重distinct

--distinct:去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询全部的学生 
SELECT * FROM `student_table`
-- 查询全部的成绩
SELECT *FROM `score_table`
-- 查询有哪些同学参加了考试
SELECT `st_number` FROM `score_table`
-- 去掉重复项
SELECT DISTINCT `st_number` FROM `score_table`

数据库中的列（表达式）

-- 数据库的列（表达式）
SELECT VERSION() -- 查询系统版本（函数） -- 5.7.19
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式） -- 299
SELECT @@auto_increment_increment -- 查询自增的步长（变量）-- 1
-- 学生考试成绩+1分查看
SELECT `st_number`,`score` +1 AS '学生提分后成绩' FROM `score_table`
-- 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...
-- select 表达式 from 表