四、DQL查询数据1

1. 数据准备

• 准备student学生表并插入数据

-- student表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME COMMENT '出身日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入student表数据
INSERT INTO `student`(`name`, `sex`, `birthday`, `address`, `email`)
VALUES ('张三', '男', '2001-01-01', '上海', 'zhangs@qq.com'),
('李四', '女', '2012-02-02', '北京', 'lis@163.com'),
('王二', '男', '2009-04-01', '重庆', 'wange@sina.com')

• 准备score成绩表并插入数据

-- score表
CREATE TABLE IF NOT EXISTS `score`(
	`studentId` INT(4) NOT NULL COMMENT '学号',
	`subject` varchar(10) NOT NULL COMMENT '科目',
	`goal` FLOAT(2) NOT NULL COMMENT '分数'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入socre表数据
INSERT INTO `score`(`studentId`, `subject`, `goal`)
VALUES (1, '语文', 70),
(2, '语文', 92),
(3, '语文', 88),
(1, '数学', 99),
(2, '数学', 30),
(3, '数学', 63),
(1, '英语', 71),
(2, '英语', 55),
(3, '英语', 89)

2. 基本查询语句和别名

语法：select 字段名1, 字段名2 from 表名 where [条件]

SELECT `name`, `sex` FROM student WHERE `id`=1;

我们查询出来的结果是这样的：

这样显示显示的列表名为英文，不利于阅读，如果想要改变这里的列名的显示，可以使用别名功能，使用AS关键字即可。

SELECT `name` AS 姓名, `sex` AS 性别 FROM student WHERE `id`=1;

3. 去重、where和逻辑运算符

3.1 去重

例：查询socre表中包含的科目信息

如果我们直接使用下面的语句查询，会发现结果中有重复的科目，因为每个人都有语数外科目

SELECT `subject` FROM score;

因此我们需要对查询出的结果去重，使用DISTINCT关键字

SELECT DISTINCT `subject` FROM score;

3.2 where条件和逻辑运算符

where字句的作用就是查询符合where中条件的结果；包含的逻辑运算符有and(&&), or(||), not(!).

-- 查询语文成绩大于80分的信息
SELECT * FROM `score` WHERE `goal`>=80 AND `subject`='语文';
-- 查询除语文之外所有科目的成绩
SELECT * FROM `score` WHERE `subject` != '语文';
-- 查询2005年后出生的学生或者地址在重庆的学生
SELECT `name` FROM `student` WHERE `birthday`>'2005-01-01' OR `address`='重庆';

3.3 模糊查询

在查询条件不明确的时候，我们需要用到模糊查询，例如，查询所有姓王的学生。

模糊查询使用关键字LIKE，配合%和_使用；%表示的是匹配所有自负，_表示的是匹配一个字符。

'王%'：表示的是姓王的所有人，可以是王五、王二麻子...王后面的字符数量不限制；

'王_'：表示的是姓王并且王后面只有一个字的人，比如王五，王峰。

SELECT * FROM `student` WHERE `name` LIKE '张%';
SELECT * FROM `student` WHERE `name` LIKE '张_';

4. 联表查询

联表查询指的是查询的信息在一张表中无法获得，需要根据两张表的关联字段，联合查询对应的信息。

例：通过student表和score表，查询学生的姓名和科目成绩。

在这个问题中，student表中没有成绩，但是有姓名；score表中没有姓名，但是有成绩；因此我们需要将两张表关联起来，关联的关键就是学生的id.

SELECT `name`,`subject`,`goal`
FROM student AS s1 JOIN score AS s2
ON s1.id=s2.studentId;

语法：select 字段1,字段2... from 表1 join 表2 on 关联的字段

INNER JOIN

INNER JOIN内连接，指的是联表查询的结果是联张表的交集部分，我们再插入两条数据

-- 往student表中插入一个id为4的学生信息
INSERT INTO `student`(`name`,`sex`,`birthday`,`address`,`email`)
VALUES ('王五','男','1999-09-23','西安','wangw@qq.com');

-- 往score表中插入一个id为5的成绩信息
INSERT INTO `score`(`studentId`,`subject`,`goal`)
VALUES(5,'语文',78);

这次我们使用INNER JOIN查询，查看结果

SELECT `name`,`subject`,`goal`
FROM student AS s1 JOIN score AS s2
ON s1.id=s2.studentId;

可以看到查询出来的结果中都是两张表学生id交集的信息，没有id为4和5的信息。

LEFT JOIN和RIGHT JOIN

LEFT JOIN和RIGHT JOIN与上面的INNER JOIN不同，left join的意思就是以左边的表为主表，如果有变的表没有相关的信息，则没有信息的字段显示为空。

我们把上面的sql换成left join试下：

SELECT `id`,`name`,`subject`,`goal`
FROM student AS s1 LEFT JOIN score AS s2
ON s1.id=s2.studentId;

可以看到王五没有成绩信息，但是也被查询了出来。right join同理，在这里不再写例子展示。