MySQL基础回顾(六)：数据查询语言DQL---select查询语句总结(篇一)

最新推荐文章于 2024-02-19 12:11:22 发布

A_K_L

最新推荐文章于 2024-02-19 12:11:22 发布

阅读量344

点赞数 2

分类专栏：笔记文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/A_K_L/article/details/104933860

版权

笔记专栏收录该内容

29 篇文章 0 订阅

订阅专栏

先建一个数据库和若干张表

-- 创建一个school数据库
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;

-- 使用这个数据库
USE `school`;


-- 删除grade这个表，如果它存在的话
DROP TABLE IF EXISTS `grade`;
-- 创建一个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;
-- 向grade表里插入一些数据
INSERT  INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');


-- 删除result这张表，如果它存在的话
DROP TABLE IF EXISTS `result`;
-- 创建一个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',94),(1000,2,'2012-11-10 10:00:00',75),(1000,3,'2011-12-19 10:00:00',76),(1000,4,'2010-11-18 11:00:00',93),(1000,5,'2013-11-11 14:00:00',97),(1000,6,'2012-09-13 15:00:00',87),(1000,7,'2011-10-16 16:00:00',79),(1000,8,'2010-11-11 16:00:00',74),(1000,9,'2013-11-21 10:00:00',69),(1000,10,'2012-11-11 12:00:00',78),(1000,11,'2011-11-11 14:00:00',66),(1000,12,'2010-11-11 15:00:00',82),(1000,13,'2013-11-11 14:00:00',94),(1000,14,'2012-11-11 15:00:00',98),(1000,15,'2011-12-11 10:00:00',70),(1000,16,'2010-09-11 10:00:00',74),(1001,1,'2013-11-11 16:00:00',76),(1001,2,'2012-11-10 10:00:00',93),(1001,3,'2011-12-19 10:00:00',65),(1001,4,'2010-11-18 11:00:00',71),(1001,5,'2013-11-11 14:00:00',98),(1001,6,'2012-09-13 15:00:00',74),(1001,7,'2011-10-16 16:00:00',85),(1001,8,'2010-11-11 16:00:00',69),(1001,9,'2013-11-21 10:00:00',63),(1001,10,'2012-11-11 12:00:00',70),(1001,11,'2011-11-11 14:00:00',62),(1001,12,'2010-11-11 15:00:00',90),(1001,13,'2013-11-11 14:00:00',97),(1001,14,'2012-11-11 15:00:00',89),(1001,15,'2011-12-11 10:00:00',72),(1001,16,'2010-09-11 10:00:00',90),(1002,1,'2013-11-11 16:00:00',61),(1002,2,'2012-11-10 10:00:00',80),(1002,3,'2011-12-19 10:00:00',89),(1002,4,'2010-11-18 11:00:00',88),(1002,5,'2013-11-11 14:00:00',82),(1002,6,'2012-09-13 15:00:00',91),(1002,7,'2011-10-16 16:00:00',63),(1002,8,'2010-11-11 16:00:00',84),(1002,9,'2013-11-21 10:00:00',60),(1002,10,'2012-11-11 12:00:00',71),(1002,11,'2011-11-11 14:00:00',93),(1002,12,'2010-11-11 15:00:00',96),(1002,13,'2013-11-11 14:00:00',83),(1002,14,'2012-11-11 15:00:00',69),(1002,15,'2011-12-11 10:00:00',89),(1002,16,'2010-09-11 10:00:00',83),(1003,1,'2013-11-11 16:00:00',91),(1003,2,'2012-11-10 10:00:00',75),(1003,3,'2011-12-19 10:00:00',65),(1003,4,'2010-11-18 11:00:00',63),(1003,5,'2013-11-11 14:00:00',90),(1003,6,'2012-09-13 15:00:00',96),(1003,7,'2011-10-16 16:00:00',97),(1003,8,'2010-11-11 16:00:00',77),(1003,9,'2013-11-21 10:00:00',62),(1003,10,'2012-11-11 12:00:00',81),(1003,11,'2011-11-11 14:00:00',76),(1003,12,'2010-11-11 15:00:00',61),(1003,13,'2013-11-11 14:00:00',93),(1003,14,'2012-11-11 15:00:00',79),(1003,15,'2011-12-11 10:00:00',78),(1003,16,'2010-09-11 10:00:00',96),(1004,1,'2013-11-11 16:00:00',84),(1004,2,'2012-11-10 10:00:00',79),(1004,3,'2011-12-19 10:00:00',76),(1004,4,'2010-11-18 11:00:00',78),(1004,5,'2013-11-11 14:00:00',81),(1004,6,'2012-09-13 15:00:00',90),(1004,7,'2011-10-16 16:00:00',63),(1004,8,'2010-11-11 16:00:00',89),(1004,9,'2013-11-21 10:00:00',67),(1004,10,'2012-11-11 12:00:00',100),(1004,11,'2011-11-11 14:00:00',94),(1004,12,'2010-11-11 15:00:00',65),(1004,13,'2013-11-11 14:00:00',86),(1004,14,'2012-11-11 15:00:00',77),(1004,15,'2011-12-11 10:00:00',82),(1004,16,'2010-09-11 10:00:00',87),(1005,1,'2013-11-11 16:00:00',82),(1005,2,'2012-11-10 10:00:00',92),(1005,3,'2011-12-19 10:00:00',80),(1005,4,'2010-11-18 11:00:00',92),(1005,5,'2013-11-11 14:00:00',97),(1005,6,'2012-09-13 15:00:00',72),(1005,7,'2011-10-16 16:00:00',84),(1005,8,'2010-11-11 16:00:00',79),(1005,9,'2013-11-21 10:00:00',76),(1005,10,'2012-11-11 12:00:00',87),(1005,11,'2011-11-11 14:00:00',65),(1005,12,'2010-11-11 15:00:00',67),(1005,13,'2013-11-11 14:00:00',63),(1005,14,'2012-11-11 15:00:00',64),(1005,15,'2011-12-11 10:00:00',99),(1005,16,'2010-09-11 10:00:00',97),(1006,1,'2013-11-11 16:00:00',82),(1006,2,'2012-11-10 10:00:00',73),(1006,3,'2011-12-19 10:00:00',79),(1006,4,'2010-11-18 11:00:00',63),(1006,5,'2013-11-11 14:00:00',97),(1006,6,'2012-09-13 15:00:00',83),(1006,7,'2011-10-16 16:00:00',78),(1006,8,'2010-11-11 16:00:00',88),(1006,9,'2013-11-21 10:00:00',89),(1006,10,'2012-11-11 12:00:00',82),(1006,11,'2011-11-11 14:00:00',70),(1006,12,'2010-11-11 15:00:00',69),(1006,13,'2013-11-11 14:00:00',64),(1006,14,'2012-11-11 15:00:00',80),(1006,15,'2011-12-11 10:00:00',90),(1006,16,'2010-09-11 10:00:00',85),(1007,1,'2013-11-11 16:00:00',87),(1007,2,'2012-11-10 10:00:00',63),(1007,3,'2011-12-19 10:00:00',70),(1007,4,'2010-11-18 11:00:00',74),(1007,5,'2013-11-11 14:00:00',79),(1007,6,'2012-09-13 15:00:00',83),(1007,7,'2011-10-16 16:00:00',86),(1007,8,'2010-11-11 16:00:00',76),(1007,9,'2013-11-21 10:00:00',65),(1007,10,'2012-11-11 12:00:00',87),(1007,11,'2011-11-11 14:00:00',69),(1007,12,'2010-11-11 15:00:00',69),(1007,13,'2013-11-11 14:00:00',90),(1007,14,'2012-11-11 15:00:00',84),(1007,15,'2011-12-11 10:00:00',95),(1007,16,'2010-09-11 10:00:00',92),(1008,1,'2013-11-11 16:00:00',96),(1008,2,'2012-11-10 10:00:00',62),(1008,3,'2011-12-19 10:00:00',97),(1008,4,'2010-11-18 11:00:00',84),(1008,5,'2013-11-11 14:00:00',86),(1008,6,'2012-09-13 15:00:00',72),(1008,7,'2011-10-16 16:00:00',67),(1008,8,'2010-11-11 16:00:00',83),(1008,9,'2013-11-21 10:00:00',86),(1008,10,'2012-11-11 12:00:00',60),(1008,11,'2011-11-11 14:00:00',61),(1008,12,'2010-11-11 15:00:00',68),(1008,13,'2013-11-11 14:00:00',99),(1008,14,'2012-11-11 15:00:00',77),(1008,15,'2011-12-11 10:00:00',73),(1008,16,'2010-09-11 10:00:00',78),(1009,1,'2013-11-11 16:00:00',67),(1009,2,'2012-11-10 10:00:00',70),(1009,3,'2011-12-19 10:00:00',75),(1009,4,'2010-11-18 11:00:00',92),(1009,5,'2013-11-11 14:00:00',76),(1009,6,'2012-09-13 15:00:00',90),(1009,7,'2011-10-16 16:00:00',62),(1009,8,'2010-11-11 16:00:00',68),(1009,9,'2013-11-21 10:00:00',70),(1009,10,'2012-11-11 12:00:00',83),(1009,11,'2011-11-11 14:00:00',88),(1009,12,'2010-11-11 15:00:00',65),(1009,13,'2013-11-11 14:00:00',91),(1009,14,'2012-11-11 15:00:00',99),(1009,15,'2011-12-11 10:00:00',65),(1009,16,'2010-09-11 10:00:00',83),(1010,1,'2013-11-11 16:00:00',83),(1010,2,'2012-11-10 10:00:00',87),(1010,3,'2011-12-19 10:00:00',89),(1010,4,'2010-11-18 11:00:00',99),(1010,5,'2013-11-11 14:00:00',91),(1010,6,'2012-09-13 15:00:00',96),(1010,7,'2011-10-16 16:00:00',72),(1010,8,'2010-11-11 16:00:00',72),(1010,9,'2013-11-21 10:00:00',98),(1010,10,'2012-11-11 12:00:00',73),(1010,11,'2011-11-11 14:00:00',68),(1010,12,'2010-11-11 15:00:00',62),(1010,13,'2013-11-11 14:00:00',67),(1010,14,'2012-11-11 15:00:00',69),(1010,15,'2011-12-11 10:00:00',71),(1010,16,'2010-09-11 10:00:00',66),(1011,1,'2013-11-11 16:00:00',62),(1011,2,'2012-11-10 10:00:00',72),(1011,3,'2011-12-19 10:00:00',96),(1011,4,'2010-11-18 11:00:00',64),(1011,5,'2013-11-11 14:00:00',89),(1011,6,'2012-09-13 15:00:00',91),(1011,7,'2011-10-16 16:00:00',95),(1011,8,'2010-11-11 16:00:00',96),(1011,9,'2013-11-21 10:00:00',89),(1011,10,'2012-11-11 12:00:00',73),(1011,11,'2011-11-11 14:00:00',82),(1011,12,'2010-11-11 15:00:00',98),(1011,13,'2013-11-11 14:00:00',66),(1011,14,'2012-11-11 15:00:00',69),(1011,15,'2011-12-11 10:00:00',91),(1011,16,'2010-09-11 10:00:00',69),(1012,1,'2013-11-11 16:00:00',86),(1012,2,'2012-11-10 10:00:00',66),(1012,3,'2011-12-19 10:00:00',97),(1012,4,'2010-11-18 11:00:00',69),(1012,5,'2013-11-11 14:00:00',70),(1012,6,'2012-09-13 15:00:00',74),(1012,7,'2011-10-16 16:00:00',91),(1012,8,'2010-11-11 16:00:00',97),(1012,9,'2013-11-21 10:00:00',84),(1012,10,'2012-11-11 12:00:00',82),(1012,11,'2011-11-11 14:00:00',90),(1012,12,'2010-11-11 15:00:00',91),(1012,13,'2013-11-11 14:00:00',91),(1012,14,'2012-11-11 15:00:00',97),(1012,15,'2011-12-11 10:00:00',85),(1012,16,'2010-09-11 10:00:00',90),(1013,1,'2013-11-11 16:00:00',73),(1013,2,'2012-11-10 10:00:00',69),(1013,3,'2011-12-19 10:00:00',91),(1013,4,'2010-11-18 11:00:00',72),(1013,5,'2013-11-11 14:00:00',76),(1013,6,'2012-09-13 15:00:00',87),(1013,7,'2011-10-16 16:00:00',61),(1013,8,'2010-11-11 16:00:00',77),(1013,9,'2013-11-21 10:00:00',83),(1013,10,'2012-11-11 12:00:00',99),(1013,11,'2011-11-11 14:00:00',91),(1013,12,'2010-11-11 15:00:00',84),(1013,13,'2013-11-11 14:00:00',98),(1013,14,'2012-11-11 15:00:00',74),(1013,15,'2011-12-11 10:00:00',92),(1013,16,'2010-09-11 10:00:00',90),(1014,1,'2013-11-11 16:00:00',64),(1014,2,'2012-11-10 10:00:00',81),(1014,3,'2011-12-19 10:00:00',79),(1014,4,'2010-11-18 11:00:00',74),(1014,5,'2013-11-11 14:00:00',65),(1014,6,'2012-09-13 15:00:00',88),(1014,7,'2011-10-16 16:00:00',86),(1014,8,'2010-11-11 16:00:00',77),(1014,9,'2013-11-21 10:00:00',86),(1014,10,'2012-11-11 12:00:00',85),(1014,11,'2011-11-11 14:00:00',86),(1014,12,'2010-11-11 15:00:00',75),(1014,13,'2013-11-11 14:00:00',89),(1014,14,'2012-11-11 15:00:00',79),(1014,15,'2011-12-11 10:00:00',73),(1014,16,'2010-09-11 10:00:00',68),(1015,1,'2013-11-11 16:00:00',99),(1015,2,'2012-11-10 10:00:00',60),(1015,3,'2011-12-19 10:00:00',60),(1015,4,'2010-11-18 11:00:00',75),(1015,5,'2013-11-11 14:00:00',78),(1015,6,'2012-09-13 15:00:00',78),(1015,7,'2011-10-16 16:00:00',84),(1015,8,'2010-11-11 16:00:00',95),(1015,9,'2013-11-21 10:00:00',93),(1015,10,'2012-11-11 12:00:00',79),(1015,11,'2011-11-11 14:00:00',74),(1015,12,'2010-11-11 15:00:00',65),(1015,13,'2013-11-11 14:00:00',63),(1015,14,'2012-11-11 15:00:00',74),(1015,15,'2011-12-11 10:00:00',67),(1015,16,'2010-09-11 10:00:00',65),(1016,1,'2013-11-11 16:00:00',97),(1016,2,'2012-11-10 10:00:00',90),(1016,3,'2011-12-19 10:00:00',77),(1016,4,'2010-11-18 11:00:00',75),(1016,5,'2013-11-11 14:00:00',75),(1016,6,'2012-09-13 15:00:00',97),(1016,7,'2011-10-16 16:00:00',96),(1016,8,'2010-11-11 16:00:00',92),(1016,9,'2013-11-21 10:00:00',62),(1016,10,'2012-11-11 12:00:00',83),(1016,11,'2011-11-11 14:00:00',98),(1016,12,'2010-11-11 15:00:00',94),(1016,13,'2013-11-11 14:00:00',62),(1016,14,'2012-11-11 15:00:00',97),(1016,15,'2011-12-11 10:00:00',76),(1016,16,'2010-09-11 10:00:00',82),(1017,1,'2013-11-11 16:00:00',100),(1017,2,'2012-11-10 10:00:00',88),(1017,3,'2011-12-19 10:00:00',86),(1017,4,'2010-11-18 11:00:00',73),(1017,5,'2013-11-11 14:00:00',96),(1017,6,'2012-09-13 15:00:00',64),(1017,7,'2011-10-16 16:00:00',81),(1017,8,'2010-11-11 16:00:00',66),(1017,9,'2013-11-21 10:00:00',76),(1017,10,'2012-11-11 12:00:00',95),(1017,11,'2011-11-11 14:00:00',73),(1017,12,'2010-11-11 15:00:00',82),(1017,13,'2013-11-11 14:00:00',85),(1017,14,'2012-11-11 15:00:00',68),(1017,15,'2011-12-11 10:00:00',99),(1017,16,'2010-09-11 10:00:00',76);


-- 删除student这张表，如果它存在的话
DROP TABLE IF EXISTS `student`;
-- 创建一个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;
-- 向student学生表里插入一些数据
INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) VALUES (1000,'111111','郭靖',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');


-- 删除subject这张表，如果它存在的话
DROP TABLE IF EXISTS `subject`;
-- 创建一个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=18 DEFAULT CHARSET=utf8;

完整语法

SELECT [ALL | DISTINCT]   -- 不加distinct去重的话，就默认是all是查全部
{* | 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 去重选项字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];

1.基础查询

语法1：
select 字段 from 表  -- 查询一张表的某个字段
语法2：
select * from 表   --使用*代表查询所有字段

示例1：查询学生表所有字段

mysql> select * from student;
+-----------+----------+--------------+------+---------+-------------+------------------------------------+---------------------+---------------+--------------------+
| StudentNo | LoginPwd | StudentName  | Sex  | GradeId | Phone       | Address                            | BornDate            | Email         | IdentityCard       |
+-----------+----------+--------------+------+---------+-------------+------------------------------------+---------------------+---------------+--------------------+
|      1000 | 111111   | 郭靖         |    1 |       1 | 13500000001 | 北京海淀区中关村大街1号            | 1986-12-11 00:00:00 | test1@bdqn.cn | 450323198612111234 |
|      1001 | 123456   | 李文才       |    1 |       2 | 13500000002 | 河南洛阳                           | 1981-12-31 00:00:00 | test1@bdqn.cn | 450323198112311234 |
|      1002 | 111111   | 李斯文       |    1 |       1 | 13500000003 | 天津市和平区                       | 1986-11-30 00:00:00 | test1@bdqn.cn | 450323198611301234 |
|      1003 | 123456   | 武松         |    1 |       3 | 13500000004 | 上海卢湾区                         | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612314234 |
|      1004 | 123456   | 张三         |    1 |       4 | 13500000005 | 北京市通州                         | 1989-12-31 00:00:00 | test1@bdqn.cn | 450323198612311244 |
|      1005 | 123456   | 张秋丽       |    2 |       1 | 13500000006 | 广西桂林市灵川                     | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311214 |
|      1006 | 123456   | 肖梅         |    2 |       4 | 13500000007 | 地址不详                           | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311134 |
|      1007 | 111111   | 欧阳峻峰     |    1 |       1 | 13500000008 |                                    | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311133 |
|      1008 | 111111   | 梅超风       |    1 |       1 | 13500000009 | 河南洛阳                           | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311221 |
|      1009 | 123456   | 刘毅         |    1 |       2 | 13500000011 | 安徽                               | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311231 |
|      1010 | 111111   | 大凡         |    1 |       1 | 13500000012 | 河南洛阳                           | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311044 |
|      1011 | 111111   | 奥丹斯       |    1 |       1 | 13500000013 | 北京海淀区中关村大街*号            | 1984-12-31 00:00:00 | test1@bdqn.cn | 450323198412311234 |
|      1012 | 123456   | 多伦         |    2 |       3 | 13500000014 | 广西南宁中央大街                   | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311334 |
|      1013 | 123456   | 李梅         |    2 |       1 | 13500000015 | 上海卢湾区                         | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311534 |
|      1014 | 123456   | 张得         |    2 |       4 | 13500000016 | 北京海淀区中关村大街*号            | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311264 |
|      1015 | 123456   | 李东方       |    1 |       4 | 13500000017 | 广西桂林市灵川                     | 1976-12-31 00:00:00 | test1@bdqn.cn | 450323197612311234 |
|      1016 | 111111   | 刘奋斗       |    1 |       1 | 13500000018 | 上海卢湾区                         | 1986-12-31 00:00:00 | test1@bdqn.cn | 450323198612311251 |
|      1017 | 123456   | 可可         |    2 |       3 | 13500000019 | 北京长安街1号                      | 1981-09-10 00:00:00 | test1@bdqn.cn | 450323198109108311 |
|     10066 |          | Tom          |    1 |       1 | 13500000000 |                                    | 0000-00-00 00:00:00 | email@22.com  | 33123123123123123  |
+-----------+----------+--------------+------+---------+-------------+------------------------------------+---------------------+---------------+--------------------+
19 rows in set (0.03 sec)

示例2：只查询学生表的学号字段。

mysql> select `studentNo` from student;
+-----------+
| studentNo |
+-----------+
|      1000 |
|      1001 |
|      1002 |
|      1003 |
|      1004 |
|      1005 |
|      1006 |
|      1007 |
|      1008 |
|      1009 |
|      1010 |
|      1011 |
|      1012 |
|      1013 |
|      1014 |
|      1015 |
|      1016 |
|      1017 |
|     10066 |
+-----------+
19 rows in set (0.00 sec)

2.字段别名

正常情况下数据库的表头我们在看查询结果时时希望以自己更通俗易懂的名字查看，怎么办呢？
这里就可以使用使用AS关键字，给字段起别名，让它看起来更加见名知意。

语法：
select 旧字段名 AS 新字段名 from 表

示例：

mysql> SELECT `StudentNo` AS 学号 ,`StudentName` AS 学生姓名 FROM `student`;
+--------+--------------+
| 学号   | 学生姓名     |
+--------+--------------+
|   1000 | 郭靖         |
|   1001 | 李文才       |
|   1002 | 李斯文       |
|   1003 | 武松         |
|   1004 | 张三         |
|   1005 | 张秋丽       |
|   1006 | 肖梅         |
|   1007 | 欧阳峻峰     |
|   1008 | 梅超风       |
|   1009 | 刘毅         |
|   1010 | 大凡         |
|   1011 | 奥丹斯       |
|   1012 | 多伦         |
|   1013 | 李梅         |
|   1014 | 张得         |
|   1015 | 李东方       |
|   1016 | 刘奋斗       |
|   1017 | 可可         |
|  10066 | Tom          |
+--------+--------------+
19 rows in set (0.00 sec)

如果不加别名的话，“学号”字样将会以StudentNo显示，“学生姓名”字样将会以StudentName显示。

3.去重

去重就是对结果中完全相同的记录（所有字段数据都相同）只保留一条。

all ：不去重
distinct：去重

值得注意的是：去重针对的是查询出来的记录，而不是存储在表中的记录。如果说仅仅查询的是某些字段，那么去重针对的是便是这些字段。

先看一段代码：
不去重的情况下，从成绩表中查询所有同学的成绩。

mysql> SELECT * FROM result;
+-----------+-----------+---------------------+---------------+
| StudentNo | SubjectNo | ExamDate            | StudentResult |
+-----------+-----------+---------------------+---------------+
|      1000 |         1 | 2013-11-11 16:00:00 |            94 |
|      1000 |         2 | 2012-11-10 10:00:00 |            75 |
|      1000 |         3 | 2011-12-19 10:00:00 |            76 |
|      1000 |         4 | 2010-11-18 11:00:00 |            93 |
|      1000 |         5 | 2013-11-11 14:00:00 |            97 |
|      1000 |         6 | 2012-09-13 15:00:00 |            87 |
|      1000 |         7 | 2011-10-16 16:00:00 |            79 |
|      1000 |         8 | 2010-11-11 16:00:00 |            74 |
|      1000 |         9 | 2013-11-21 10:00:00 |            69 |
|      1000 |        10 | 2012-11-11 12:00:00 |            78 |
|      1000 |        11 | 2011-11-11 14:00:00 |            66 |
|      1000 |        12 | 2010-11-11 15:00:00 |            82 |
|      1000 |        13 | 2013-11-11 14:00:00 |            94 |
|      1000 |        14 | 2012-11-11 15:00:00 |            98 |
|      1000 |        15 | 2011-12-11 10:00:00 |            70 |
|      1000 |        16 | 2010-09-11 10:00:00 |            74 |
|      1001 |         1 | 2013-11-11 16:00:00 |            76 |
|      1001 |         2 | 2012-11-10 10:00:00 |            93 |
|      1001 |         3 | 2011-12-19 10:00:00 |            65 |
|      1001 |         4 | 2010-11-18 11:00:00 |            71 |
|      1001 |         5 | 2013-11-11 14:00:00 |            98 |
|      1001 |         6 | 2012-09-13 15:00:00 |            74 |
|      1001 |         7 | 2011-10-16 16:00:00 |            85 |
|      1001 |         8 | 2010-11-11 16:00:00 |            69 |
|      1001 |         9 | 2013-11-21 10:00:00 |            63 |
|      1001 |        10 | 2012-11-11 12:00:00 |            70 |
|      1001 |        11 | 2011-11-11 14:00:00 |            62 |
|      1001 |        12 | 2010-11-11 15:00:00 |            90 |
|      1001 |        13 | 2013-11-11 14:00:00 |            97 |
|      1001 |        14 | 2012-11-11 15:00:00 |            89 |
|      1001 |        15 | 2011-12-11 10:00:00 |            72 |
|      1001 |        16 | 2010-09-11 10:00:00 |            90 |

上面只截取了一部分查询结果，可以看到每个人都参加了16门考试。数据很多也不好看，现在我只想知道哪些同学参加了考试，该怎么办呢？

这时候就要使用关键字distinct进行去重处理了。

mysql> SELECT DISTINCT `StudentNo` FROM result ;
+-----------+
| StudentNo |
+-----------+
|      1000 |
|      1001 |
|      1002 |
|      1003 |
|      1004 |
|      1005 |
|      1006 |
|      1007 |
|      1008 |
|      1009 |
|      1010 |
|      1011 |
|      1012 |
|      1013 |
|      1014 |
|      1015 |
|      1016 |
|      1017 |
+-----------+
18 rows in set (0.01 sec)

这样查询结果中每个同学就只出现了一次，很好的满足了我们的需求。而且可以看到使用了distinct关键字进行去重处理后，查询结果变的非常简洁。

4.where子句查询

where子句是用于筛选符合条件的结果的，它主要分为以下三种条件：

1.逻辑条件

逻辑运算符     语法                     结果描述
and或者&&      a and b或者a && b        逻辑与
or或者||       a or b或者 a || b        逻辑或
not或者！      not a 或者 !a            逻辑非

小提醒：为了可读性更高，应尽量使用and or not这一类英文字母，而不是&&，||，！这些符号。

2.基于值的条件

= : where 字段 = 值;   与它用法相同的的还有>,>=,<,<=。

3.基于值的范围条件

符号                   用法                       解释                                       示例
in                     where 字段 in 范围;        查找出对应字段的值在所指定范围的记录。       例如：where  StudentResult in (60,80,100)
not in                 where 字段 not in 范围;    查找出对应字段的值不在所指定范围的记录。     例如：where  StudentResult not in (60,80,100)
between x and y        where 字段 between x and y;       查找出对应字段的值在闭区间[x,y]范围的记录。   例如：where  StudentResult between 80 and 100。

示例1：
查询考试成绩在95-100分之间的学生学号，和具体分数。

mysql> SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult>=95 AND StudentResult<=100;
+-----------+---------------+
| StudentNo | StudentResult |
+-----------+---------------+
|      1000 |            97 |
|      1000 |            98 |
|      1001 |            98 |
|      1001 |            97 |
|      1002 |            96 |
|      1003 |            96 |
|      1003 |            97 |
|      1003 |            96 |
|      1004 |           100 |
|      1005 |            97 |
|      1005 |            99 |
|      1005 |            97 |
|      1006 |            97 |
|      1007 |            95 |
|      1008 |            96 |
|      1008 |            97 |
|      1008 |            99 |
|      1009 |            99 |
|      1010 |            99 |
|      1010 |            96 |
|      1010 |            98 |
|      1011 |            96 |
|      1011 |            95 |
|      1011 |            96 |
|      1011 |            98 |
|      1012 |            97 |
|      1012 |            97 |
|      1012 |            97 |
|      1013 |            99 |
|      1013 |            98 |
|      1015 |            99 |
|      1015 |            95 |
|      1016 |            97 |
|      1016 |            97 |
|      1016 |            96 |
|      1016 |            98 |
|      1016 |            97 |
|      1017 |           100 |
|      1017 |            96 |
|      1017 |            95 |
|      1017 |            99 |
+-----------+---------------+
41 rows in set (0.01 sec)

示例2：
上面例子是用逻辑运算符AND完成查找的，其实使用between也可以。
语句如下，效果是一样的。

mysql> SELECT `StudentNo`,`StudentResult` FROM result  WHERE `StudentResult` BETWEEN 95 AND 100;
+-----------+---------------+
| StudentNo | StudentResult |
+-----------+---------------+
|      1000 |            97 |
|      1000 |            98 |
|      1001 |            98 |
|      1001 |            97 |
|      1002 |            96 |
|      1003 |            96 |
|      1003 |            97 |
|      1003 |            96 |
|      1004 |           100 |
|      1005 |            97 |
|      1005 |            99 |
|      1005 |            97 |
|      1006 |            97 |
|      1007 |            95 |
|      1008 |            96 |
|      1008 |            97 |
|      1008 |            99 |
|      1009 |            99 |
|      1010 |            99 |
|      1010 |            96 |
|      1010 |            98 |
|      1011 |            96 |
|      1011 |            95 |
|      1011 |            96 |
|      1011 |            98 |
|      1012 |            97 |
|      1012 |            97 |
|      1012 |            97 |
|      1013 |            99 |
|      1013 |            98 |
|      1015 |            99 |
|      1015 |            95 |
|      1016 |            97 |
|      1016 |            97 |
|      1016 |            96 |
|      1016 |            98 |
|      1016 |            97 |
|      1017 |           100 |
|      1017 |            96 |
|      1017 |            95 |
|      1017 |            99 |
+-----------+---------------+
41 rows in set (0.01 sec)

示例3：
使用in来查询指定分数对应的学生学号。

mysql> SELECT `StudentNo`,`StudentResult` FROM result  WHERE `StudentResult`in (80,85,90);
+-----------+---------------+
| StudentNo | StudentResult |
+-----------+---------------+
|      1001 |            85 |
|      1001 |            90 |
|      1001 |            90 |
|      1002 |            80 |
|      1003 |            90 |
|      1004 |            90 |
|      1005 |            80 |
|      1006 |            80 |
|      1006 |            90 |
|      1006 |            85 |
|      1007 |            90 |
|      1009 |            90 |
|      1012 |            90 |
|      1012 |            85 |
|      1012 |            90 |
|      1013 |            90 |
|      1014 |            85 |
|      1016 |            90 |
|      1017 |            85 |
+-----------+---------------+
19 rows in set (0.00 sec)

关于where需要特别注意的是：where是从磁盘中获取数据的时候就进行筛选的。所以某些在内存是才有的东西where无法使用。 比如字段别名什么的是本来不是“磁盘中的数据”（是在内存这中运行时才定义的），所以where无法使用，此时一般都依靠having来筛选。

示例

mysql> select StudentNo as sn,studentName from student where studentNo = 1000;
+------+-------------+
| sn   | studentName |
+------+-------------+
| 1000 | 郭靖        |
+------+-------------+
1 row in set (0.02 sec)

--在使用在用别名当作where条件时会直接抛出错误
mysql> select StudentNo as sn,studentName from student where sn = 1000;
ERROR 1054 (42S22): Unknown error 1054


-- 使用having搭配别名就可以正常使用了
mysql> select StudentNo as sn,studentName from student having sn = 1000;
+------+-------------+
| sn   | studentName |
+------+-------------+
| 1000 | 郭靖        |
+------+-------------+
1 row in set (0.00 sec)

5.模糊查询

模糊查询：本质上也是是比较运算符。
上一部分演示的between and还有in，就是经典的模糊查询操作符。
这里模糊查询主要介绍like这个关键字。

通过上面的演示，我们可以知道下面这个查询可以查询学生表的所有学生的学号和姓名。

SELECT `StudentNo`,`StudentName` FROM student

如果只想查询姓刘的同学，怎么办呢？
这里就可以使用like关键字。

示例1：

-- 这样所有姓刘的同学都被查询出来了。
mysql> SELECT `StudentNo`,`StudentName` FROM student WHERE `StudentName` LIKE '刘%';
+-----------+-------------+
| StudentNo | StudentName |
+-----------+-------------+
|      1009 | 刘毅        |
|      1016 | 刘奋斗      |
+-----------+-------------+
2 rows in set (0.00 sec)

上面查询语句中’刘%'表示第一个字符为刘就符合条件了，至于刘后面有几个字符我们就不关心了。
如果我们是只想查姓刘，名字只有一个字的学生，怎么办呢？
这里我们就可以用 _ 这个符号了。

示例2：

-- 这样就查询出了，姓刘，但是名字只有一个字的同学。
mysql> SELECT `StudentNo`,`StudentName` FROM student WHERE `StudentName` LIKE '刘_';
+-----------+-------------+
| StudentNo | StudentName |
+-----------+-------------+
|      1009 | 刘毅        |
+-----------+-------------+
1 row in set (0.00 sec)

一个 _ 表示后面只匹配一个字，很容易联想到两个杠，就是往后面匹配两位。

示例3

-- 这样就查询出了姓刘，但是名字只有两个字的同学
mysql> SELECT `StudentNo`,`StudentName` FROM student WHERE `StudentName` LIKE '刘__';
+-----------+-------------+
| StudentNo | StudentName |
+-----------+-------------+
|      1016 | 刘奋斗      |
+-----------+-------------+
1 row in set (0.00 sec)

如果只想查询名字中带有某个字，至于这个字在哪我们不关心，怎么查呢？
这时候就可以用两个 %% 来查询。

示例4：

-- 查询所有名字中带有斯的同学，带斯就行，在哪都行。
mysql> SELECT `StudentNO`,`StudentName` FROM student WHERE `StudentName` LIKE '%斯%' ;
+-----------+-------------+
| StudentNO | StudentName |
+-----------+-------------+
|      1002 | 李斯文      |
|      1011 | 奥丹斯      |
+-----------+-------------+
2 rows in set (0.00 sec)

6.联表查询

本来想一篇写完的，好像有点多，就放到下一篇吧。
链接：MySQL基础回顾(七)：数据查询语言DQL—select查询语句总结(篇二)

7.分页和排序

8.子查询和嵌套查询

9.分组查询和having语句

10.select与数据库表达式

A_K_L

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础回顾(六)：数据查询语言DQL---select查询语句总结(篇一)

目录先建一个数据库和若干张表完整语法1.基础查询2.字段别名3.去重4.where子句查询5.模糊查询6.联表查询7.分页和排序8.子查询和嵌套查询9.分组查询和having语句10.select妙用查询语言算是数据库中最重要的语言了，因为往往开发时查的操作比写的操作要频繁的多。所有的查询操作都可以用Select，简单的查询，复杂的查询它都能做。接下来就让我们来详细的了解一下select语句。...
复制链接

扫一扫