喵星之旅-成长的雏鹰-MySQL数据库应用技术-3-使用DQL命令

成长的雏鹰mysql

数据准备

Alt text

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
  
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@kittybunny.club','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@kittybunny.club','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@kittybunny.club','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@kittybunny.club','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@kittybunny.club','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@kittybunny.club','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@kittybunny.club','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@kittybunny.club','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@kittybunny.club','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@kittybunny.club','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);

DQL语言

DQL(Data Query Language，数据查询语言)
1.查询数据库数据，如SELECT语句
2.简单的单表查询或多表的复杂查询和嵌套查询
3.数据库语言中最核心、最重要的语句
4.使用频率最高的语句

SELECT语法

1
2
3
4
5
6
7
8
9
10
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …]#指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; 
#指定查询的记录从哪条至哪条

[] 括号代表可选的；
{} 括号代表必须的；
#MySQL语句中的注释符，也可以用 /该处为注释/

指定查询字段

查询表中所有的数据列结果，采用“*”符号

1
select * from student;

可指定查询的结果数据列

如只查询student表中的学号、姓名、电话

1
SELECT StudentNo, StudentName, Phone FROM student;

如区分连接查询时两个表有同名的字段

1
2
SELECT student.StudentNo , StudentName, StudentResult
FROM student , result ;

AS子句

AS子句作用
1.可给数据列取一个新别名
2.可给表取一个新别名
3.可把经计算或总结的结果用另外一个新名称来代替

AS子句用法 

1
2
3
SELECT StudentNo AS “学号” FROM student;
SELECT a.StudentNo FROM student AS a;
SELECT Phone+1 AS Tel FROM student;

AS 也可省略不写

DISTINCT关键字的使用

去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条。

1
SELECT DISTINCT 字段名1, 字段名2... FROM 表名
1
2
#查询成绩表中的所包含的课程ID
SELECT DISTINCT SubjectNo FROM result;

使用表达式的列

表达式一般由文本值、列值、NULL、函数和操作符等组成
应用场景:1.SELECT语句返回结果列中使用;2.SELECT语句的ORDER BY、HAVING等子句中使用;3.DML语句中的where条件语句中使用表达式。

1
2
3
4
SELECT version() , 100*3 #返回MySQL版本和计算结果
SELECT SubjectName “课程名称”, ClassHour+10 AS “新学时”
FROM subject;
#给返回结果中的课时都加10个课时

where条件语句

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

逻辑操作符
Alt text

比较操作符
Alt text

1、数值数据类型的记录之间才能进行算术运算
2、相同数据类型的数据之间才能进行比较 

1
2
3
4
5
6
7
/*
案例  查询在80-90分之间的所有成绩记录
*/ 

SELECT  *
FROM  result
WHERE  StudentResult >= 80  AND  StudentResult <= 90;

BETWEEN AND范围查询

根据一个范围值来检索,等同于 >= 和 <= 联合使用

1
2
3
SELECT 字段列1，字段2 ,…
FROM 表名
WHERE 字段x BETWEEN 值1 AND 值2
1
2
3
4
#查询课程表中课时在110和120之间的所有记录
SELECT * FROM subject WHERE ClassHour BETWEEN 110 AND 120;
等同于:
SELECT * FROM subject WHERE ClassHour >= 110 AND ClassHour <=120;

LIKE模糊查询

在WHERE子句中，使用LIKE关键字进行模糊查询
1.与“%”一起使用，表示匹配0或任意多个字符
2.与“_”一起使用，表示匹配单个字符 

1
2
3
4
5
#查询包含“数学”的所有课程
SELECT * FROM subject WHERE SubjectName LIKE "%数学%";
#查询所有姓名为“李**”三个字的学生信息
SELECT StudentNo,StudentName FROM student
WHERE StudentName LIKE “李__”;

使用IN进行范围查询

在WHERE子句中使用IN进行范围查询

  • 查询的字段x的值，至少与括号中的一个值相同
  • 多个值之间用英文逗号隔开
    1
    SELECT 字段列1，字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)
1
2
3
4
SELECT * FROM subject where ClassHour = 100 OR ClassHour
=110 OR ClassHour = 120; #普通处理方式
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
#使用IN进行查询方式，更为简洁，效率更高

NULL空值条件查询

  • NULL代表“无值”
  • 区别于零值0和空符串“”
  • 只能出现在定义允许为NULL的字段
  • 须使用 IS NULL 或 IS NOT NULL 比较操作符去比较
    1
    2
    3
    4
    5
    6
    7
    /*
    案例 查找地址不为空的学生信息
    */ 

    SELECT  *
    FROM  student
    WHERE  Address IS NOT NULL;

连接查询（多表查询）

连接查询
如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询。

分类包括

  • 内连接 ( inner join)
    等值和非等值的连接查询
    自身连接查询
  • 外连接 ( out join )
    左连接（LEFT JOIN）
    右连接 ( RIGHT JOIN)

内连接查询

INNER JOIN内连接,在表中至少一个匹配时，则返回记录

1
2
3
4
SELECT 字段1,字段2,… FROM table_1
INNER JOIN table_2 ON table_1.字段x = table_2.字段y;
# INNER JOIN 与 JOIN 是相同的；
# 如table_1中的行在table_2中没有匹配，则不返回；
1
2
3
#要求：从subject和grade数据表查询课程名称和所属年级名称
SELECT SubjectName,GradeName FROM subject INNER JOIN grade
ON subject.GradeID= grade.GradeID;

等值和非等值的连接查询

  • 与单表查询类似，都是SELECT语句
  • 把多个表放到FROM后，并用逗号隔开
  • 可使用AS关键字取别名，便于引用
  • 如无重名查询字段则可省略数据表的指定
    1
    2
    3
    4
    5
    6
    7
    8
    #要求：从subject和grade数据表查询课程名称和所属年级名称
    #非等值连接查询
    SELECT SubjectName, GradeName FROM subject, grade;
    #等值查询
    SELECT SubjectName, GradeName FROM subject, grade
    WHERE subject.GradeID = grade.GradeID;
    #返回记录数为两表记录数的乘积
    #等效于内连接

外连接

左外连接（LEFT JOIN）
从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行

1
2
SELECT 字段1,字段2,… FROM table_1
LEFT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

右外连接(RIGHT JOIN)
从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行

1
2
SELECT 字段1,字段2,… FROM table_1
RIGHT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;

自连接查询

数据表与自身进行连接 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table if not exists  category(
	categoryId int(10)  unsigned  not null  auto_increment,
	pid  int(10) not null,
	categoryName   varchar(32) not null,
	primary key(categoryId)
);

insert into  category values
(2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"Photoshop基础"),
(6,2,"色彩搭配学"),
(7,3,"PHP基础"),
(8,3,"一起学JAVA");

SELECT  c1.categoryName AS "父栏目名称",c2.categoryName AS  "子栏目名称" 
FROM category AS c1,category AS c2 
WHERE c1.categoryId = c2.pid;

排序查询结果

ORDER BY排序查询

  • 对SELECT语句查询得到的结果，按某些字段进行排序
  • 与DESC或ASC搭配使用，默认为ASC
    1
    2
    3
    4
    5
    6
    7
    # 查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列

    SELECT studentresult,subjectname 
    FROM result INNER JOIN SUBJECT
    ON result.SubjectNo = subject.SubjectNo
    WHERE subject.SubjectName='数据库结构-1'
    ORDER BY studentresult DESC;

LIMIT的使用

LIMIT [m,]n 或 LIMIT n OFFSET m

  • 限制SELECT返回结果的行数
  • m 制定第一个返回记录行的偏移量
  • n 制定返回记录行的最大数目
  • m不指定则偏移量为0，从第一条开始返回前n条记录
  • LIMIT 常用于分页显示
1
2
SELECT * FROM `result` LIMIT 5 #返回前5条记录
SELECT * FROM `result` LIMIT 5,10 #返回6-15条记录

MySQL子查询

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句。

嵌套查询可由多个子查询组成，求解的方式是由里及外子查询返回的结果一般都是集合，故而建议使用 IN 关键字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 #演示示例：子查询
 #查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
SELECT    
s.StudentNo,StudentName  
FROM  student AS s  
WHERE   
studentNo  
IN 
 (  
    SELECT  
	StudentNO 
    FROM  
        result 
    WHERE  
	SubjectNo =  (SELECT SubjectNo FROM subject WHERE SubjectName = "高等数学-2") 
	AND   
	StudentResult  >=  80  
 );

MySQL聚合函数与分组查询

  • COUNT( ) 返回满足SELECT条件的记录总和数;
  • SUM( ) 返回数字字段或表达式列作统计，返回一列的总和;
  • AVG( ) 通常为数值字段或表达列作统计，返回一列的平均值;
  • MAX( ) 可以为数值字段、字符字段或表达式列作统计，返回最大的值;
  • MIN( ) 可以为数值字段、字符字段或表达式列作统计，返回最小的值。

使用GROUP BY关键字对查询结果分组

  • 对所有的数据进行分组统计
  • 分组的依据字段可以有多个，并依次分组
  • 与HAVING结合使用，进行分组后的数据筛选
1
2
3
4
5
6
7
8
9
10
11
#按照不同的课程，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示

 
SELECT 
	s.SubjectName as "课程名",MAX(StudentResult) as "最高分" ,  MIN(StudentResult) as "最低分" ,  AVG(StudentResult) AS "平均分" 
FROM 
	result  as r 
LEFT JOIN 
	subject as s ON s.SubjectNo = r.SubjectNo
GROUP BY  r.SubjectNo 
HAVING   AVG(StudentResult) >= 60 ;
文章目录
  1. 数据准备
  2. DQL语言
  3. SELECT语法
    1. 指定查询字段
    2. AS子句
    3. DISTINCT关键字的使用
    4. 使用表达式的列
    5. where条件语句
    6. BETWEEN AND范围查询
    7. LIKE模糊查询
    8. 使用IN进行范围查询
    9. NULL空值条件查询
  4. 连接查询（多表查询）
    1. 内连接查询
    2. 外连接
    3. 自连接查询
  5. 排序查询结果
  6. LIMIT的使用
  7. MySQL子查询
  8. MySQL聚合函数与分组查询
|