Data Query LANGUAGE:数据查询语言

    所有的查询操作都用它 Select
    简单的查询，复杂的查询它都能做
    数据库中最核心的语言
    使用频率最高的语句

    SELECT [ALL | DISTINCT]
    { | 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}];
    — 指定查询的记录从哪条至哪条
    注意 : [ ] 括号代表可选的 , { }括号代表必选得

    1. -- p16用到的数据表
    3. /*
    4. SQLyog Ultimate v13.1.1 (64 bit)
    5. MySQL - 5.7.33 : Database - school
    6. *********************************************************************
    7. */
    9. /*!40101 SET NAMES utf8 */;
    11. /*!40101 SET SQL_MODE=''*/;
    13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    17. CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;
    19. USE `school`;
    21. /*Table structure for table `grade` */
    23. DROP TABLE IF EXISTS `grade`;
    25. CREATE TABLE `grade` (
    26.   `GradeID` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
    27.   `GradeName` varchar(50) NOT NULL COMMENT '年级名称',
    28.   PRIMARY KEY (`GradeID`)
    29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    31. /*Data for the table `grade` */
    33. insert  into `grade`(`GradeID`,`GradeName`) values 
    34. (1,'大一'),
    35. (2,'大二'),
    36. (3,'大三'),
    37. (4,'大四'),
    38. (5,'预科班');
    40. /*Table structure for table `result` */
    42. DROP TABLE IF EXISTS `result`;
    44. CREATE TABLE `result` (
    45.   `StudentNo` int(4) NOT NULL COMMENT '学号',
    46.   `SubjectNo` int(4) NOT NULL COMMENT '课程编号',
    47.   `ExamDate` datetime NOT NULL COMMENT '考试日期',
    48.   `StudentResult` int(4) NOT NULL COMMENT '考试成绩',
    49.   KEY `SubjectNo` (`SubjectNo`)
    50. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    52. /*Data for the table `result` */
    54. insert  into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) values 
    55. (1000,1,'2013-11-11 16:00:00',85),
    56. (1000,2,'2013-11-12 16:00:00',70),
    57. (1000,3,'2013-11-11 09:00:00',68),
    58. (1000,4,'2013-11-13 16:00:00',98),
    59. (1000,5,'2013-11-14 16:00:00',58);
    61. /*Table structure for table `student` */
    63. DROP TABLE IF EXISTS `student`;
    65. CREATE TABLE `student` (
    66.   `StudentNo` int(4) NOT NULL COMMENT '学号',
    67.   `LoginPwd` varchar(20) DEFAULT NULL,
    68.   `StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
    69.   `Sex` tinyint(1) DEFAULT NULL COMMENT '性别，0或1',
    70.   `GradeId` int(11) DEFAULT NULL COMMENT '年级编号',
    71.   `Phone` varchar(50) NOT NULL COMMENT '联系电话，允许为空',
    72.   `Address` varchar(255) NOT NULL COMMENT '地址，允许为空',
    73.   `BornDate` datetime DEFAULT NULL COMMENT '出生时间',
    74.   `Email` varchar(50) NOT NULL COMMENT '邮箱账号允许为空',
    75.   `IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
    76.   PRIMARY KEY (`StudentNo`),
    77.   UNIQUE KEY `IdentityCard` (`IdentityCard`),
    78.   KEY `Email` (`Email`)
    79. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    81. /*Data for the table `student` */
    83. insert  into `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) values 
    84. (1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-01-01 00:00:00','text123@qq.com','123456198001011234'),
    85. (1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-01-01 00:00:00','text111@qq.com','123456199001011233');
    87. /*Table structure for table `subject` */
    89. DROP TABLE IF EXISTS `subject`;
    91. CREATE TABLE `subject` (
    92.   `SubjectNo` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    93.   `SubjectName` varchar(50) DEFAULT NULL COMMENT '课程名称',
    94.   `ClassHour` int(4) DEFAULT NULL COMMENT '学时',
    95.   `GradeID` int(4) DEFAULT NULL COMMENT '年级编号',
    96.   PRIMARY KEY (`SubjectNo`)
    97. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    99. /*Data for the table `subject` */
    101. insert  into `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) values 
    102. (1,'高等数学-1',110,1),
    103. (2,'高等数学-2',110,2),
    104. (3,'高等数学-3',100,3),
    105. (4,'高等数学-4',130,4),
    106. (5,'C语言-1',110,1),
    107. (6,'C语言-2',110,2),
    108. (7,'C语言-3',100,3),
    109. (8,'C语言-4',130,4),
    110. (9,'Java程序设计-1',110,1),
    111. (10,'Java程序设计-2',110,2),
    112. (11,'Java程序设计-3',100,3),
    113. (12,'Java程序设计-4',130,4),
    114. (13,'数据库结构-1',110,1),
    115. (14,'数据库结构-2',110,2),
    116. (15,'数据库结构-3',100,3),
    117. (16,'数据库结构-4',130,4),
    118. (17,'C#基础',130,1);
    120. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    121. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    122. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    123. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;