一、DQL语言

DQL（Data Query Language 数据查询语言）

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

二、查询

下面这张图上后面的顺序是固定的。

image.png

代码素材：

  
DROP TABLE IF EXISTS `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;

 

INSERT  INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
 
 
 
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',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);

 

DROP TABLE IF EXISTS `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;
 
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');

DROP TABLE IF EXISTS `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;
  
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第一学年',110,1),(10,'JAVA第二学年',110,2),(11,'JAVA第三学年',100,3),(12,'JAVA第四学年',130,4),(13,'数据库结构-1',110,1),(14,'数据库结构-2',110,2),(15,'数据库结构-3',100,3),(16,'数据库结构-4',130,4),(17,'C#基础',130,1);

素材表结构：

IMG_20180703_091932.jpg

1、全部查询

查询表中所有的数据列结果时，采用“*”符号，如：SELECT * FROM student，但是如果用 * 的话，会效率低，不推荐

示例代码：

#查询所有学生信息（*代表查询所有列，效率低）
SELECT * FROM student;

运行结果（截图未截全）：

image.png

2、按字段查询

查询表结果时，可指定查询结果的数据列。
示例代码：

#查询指定列（学号和姓名）（不区分大小写）
SELECT StudentNo, studentName FROM student;

运行结果：

image.png

按字段查询的时候，也可以让查询的字段在结果中以我们想要的方式显示。即将数据库中的变量名以简单易懂的方式在界面上显示。代码如下：

#取别名（as 可以省略）
SELECT StudentNo AS 学号, studentName AS 姓名 FROM student;

SELECT StudentNo 学号, studentName  姓名 FROM student;

#使用as也可以为表取别名（as 也可以省略）
SELECT StudentNo 学号, studentName  姓名 FROM student AS s;

SELECT StudentNo 学号, studentName  姓名 FROM student s;

#使用as，为查询结果取一个新名字
SELECT CONCAT('姓名:', studentname) AS 新姓名 FROM student;

运行结果：

image.png

注：
1、给表取别名在单表查询的时候意义不大，但是MySQL的表与表之间是有关系的，在操作多个表的时候就可以给表取别名了。一是好操作，二是当不同表里面有相同名字的列的时候，可以通过别名点出来列进行区分。给表取别名当在使用多个表进行连接查询的时候就非常有用了。单标查询意义不大。
2、在给表里面的列取别名之后，表里面相应的列的名字不会改变。别名影响的只是查询出来的给用户呈现的结果，并不影响原表里面的数据。

3、去掉重复项

比如我有一张成绩表，但是我只想知道有哪些同学参加了考试（使用distinct关键字）。
示例代码：

#查看考试成绩
SELECT * FROM result;

#查看哪些同学参加了考试（学号）——去除重复项(distinct，默认all)
SELECT DISTINCT studentno FROM result;

SELECT ALL studentno FROM result;

4、select查询中可以使用表达式

比如我卷子上有个题出错了，我想给所有参加这个考试的同学提高一分。

示例代码：

#select查询中可以使用表达式
SELECT @@auto_increment_increment;
SELECT VERSION();
SELECT 100*3-1;

#学院考试成绩集体提高一分
SELECT studentno, studentresult+1 AS 提分后 FROM result;

运行结果：

image.png

注：使用select这种方式不改变表中的原数据，只是可以看到修改后表中数据是个什么样子。

5、where条件语句

用于检索数据表中符合条件的记录。
搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
搜索条件的组成：逻辑操作符、比较操作符

image.png

image.png

示例：查询在80~90分之间的所有成绩记录

示例代码：

#满足条件的查询（where）考试成绩在95到100之间的
SELECT studentno, studentresult 
FROM result
WHERE studentresult>=95 AND studentresult<=100;

#模糊查询
SELECT studentno, studentresult 
FROM result
WHERE studentresult BETWEEN 95 AND 100;

#精确查询
SELECT studentno, studentresult 
FROM result
WHERE studentno=1000;

#或者写成&&
SELECT studentno, studentresult 
FROM result
WHERE studentresult>=95 && studentresult<=100;

#除了1000号这个同学，我要其他同学的考试成绩
SELECT studentno, studentresult 
FROM result
WHERE studentno!=1000;

SELECT studentno, studentresult 
FROM result
WHERE NOT studentno=1000;

6、模糊查询 between and \ like \ in \ null

6.1 like

like 结合使用的通配符： %（代表0到任意个字符）和下划线_（一个字符），通配符可以使用多次。
要求：查询姓李的同学的学号和姓名
示例代码：

#模糊查询 between and \ like \ in \ null
#查询姓李的同学的学号和姓名
#like 结合使用的： %（代表0到任意个字符）和  下划线_（一个字符）
SELECT studentno, studentname 
FROM student
WHERE studentname LIKE '李%';

#查询姓李的，名字中只有一个字的
SELECT studentno, studentname 
FROM student
WHERE studentname LIKE '李_';

#查询姓李的，名字中有俩个字的
SELECT studentno, studentname 
FROM student
WHERE studentname LIKE '李__';

#查询姓名中含有“文”这个字的
SELECT studentno, studentname 
FROM student
WHERE studentname LIKE '%文%';

问题：
1、能否使用自己定义的字符作为通配符？可以，使用关键字 ESCAPE。示例代码如下：

#转义符 \ ，能不能使用我自己定义的转移符呢？ ，比如我想用冒号（:）
SELECT studentno, studentname 
FROM student 
WHERE studentname LIKE '%:_%' ESCAPE ':';

2、当学生名字中包含%或者_的时候怎么办？
使用转义符“\”，示例代码如下：

#插入两个新的学生：小%  和  小_
INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (6666,'111111','小%',1,1,'13500008888','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@163.cn','450323198612111666'),
(6667,'123456','小_',1,2,'13500000666','河南洛阳','1981-12-31 00:00:00','test1@126.cn','450323198112311888')

#查询学生姓名中有“%”这个字的同学学号姓名
SELECT studentno, studentname 
FROM student
WHERE studentname LIKE '%\%%';

6.2 in

代表在某个范围中，比如你给我几个值，我去查询，只要跟这几个值匹配，我就把它拿出来。

示例代码：

#模糊查询： in  
SELECT studentno, studentname 
FROM student
WHERE studentno=1000 OR studentno=1001 OR studentno=1002;

#模糊查询： in  
SELECT studentno, studentname 
FROM student
WHERE studentno IN (1000, 1001, 1002);

#查询所有地址是北京的同学，下面这段是错误的，具体怎么改还不知道
SELECT studentno, studentname 
FROM student
WHERE address IN('北京');

6.3 null

1、匹配NULL
=NULL这种写法是错误的，和NULL去比较必须写is null
示例代码：

#查询出生日期没有填写的同学  =NULL这种写法是错误的，和NULL去比较必须写is null
SELECT studentname 
FROM student 
WHERE borndate IS NULL;

#查询出生日期填写的同学
SELECT studentname 
FROM student 
WHERE borndate IS NOT NULL;

2、NULL与空值

image.png

上图中的空格和NULL值是不一样的，空格是原来有值然后删掉了，而NULL是空值。

示例代码：



#区别空字符串与NULL
SELECT * FROM student;

#查询家庭住址没有写的同学
SELECT studentname 
FROM student 
WHERE address IS NULL;

上面这段代码运行结果：

image.png

可以看到，并没有把TOM筛选出来，所以空格跟NULL是不一样的。

当代码这样写的时候：

#查询家庭住址没有写的同学
SELECT studentname 
FROM student 
WHERE address='';

运行结果：

image.png

可以看到把TOM筛选出来了。

所以要想将所有地址都没有写的同学都筛选出来的话，需要这样写才不会漏：

#查询家庭住址没有写的同学
SELECT studentname 
FROM student  
WHERE address='' OR address IS NULL;

三、MySQL查询语句

三、MySQL查询语句

一、DQL语言

二、查询

1、全部查询

2、按字段查询

3、去掉重复项

4、select查询中可以使用表达式

5、where条件语句

6、模糊查询 between and \ like \ in \ null

6.1 like

6.2 in

6.3 null

推荐阅读更多精彩内容