SQL查询语句

SQL查询语句

select（最重要的）

1、先创建一个数据库供我们训练select语法

-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
USE `school`;

-- 创建年级表
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',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58),
(1001,1,'2013-11-11 16:00:00',66),
(1001,2,'2013-11-12 16:00:00',67),
(1001,3,'2013-11-11 09:00:00',78),
(1001,4,'2013-11-13 16:00:00',67),
(1001,5,'2013-11-14 16:00:00',88),
(1002,1,'2013-11-11 16:00:00',67),
(1002,2,'2013-11-12 16:00:00',59),
(1002,3,'2013-11-11 09:00:00',89),
(1002,4,'2013-11-13 16:00:00',79),
(1002,5,'2013-11-14 16:00:00',90),
(1003,1,'2013-11-11 16:00:00',79),
(1003,2,'2013-11-12 16:00:00',89),
(1003,3,'2013-11-11 09:00:00',98),
(1003,4,'2013-11-13 16:00:00',85),
(1003,5,'2013-11-14 16:00:00',97),
(1004,1,'2013-11-11 16:00:00',70),
(1004,2,'2013-11-12 16:00:00',59),
(1004,3,'2013-11-11 09:00:00',85),
(1004,4,'2013-11-13 16:00:00',83),
(1004,5,'2013-11-14 16:00:00',93),
(1005,1,'2013-11-11 16:00:00',83),
(1005,2,'2013-11-12 16:00:00',77),
(1005,3,'2013-11-11 09:00:00',99),
(1005,4,'2013-11-13 16:00:00',98),
(1005,5,'2013-11-14 16:00:00',57),
(1006,1,'2013-11-11 16:00:00',85),
(1006,2,'2013-11-12 16:00:00',87),
(1006,3,'2013-11-11 09:00:00',98),
(1006,4,'2013-11-13 16:00:00',98),
(1006,5,'2013-11-14 16:00:00',55),
(1007,1,'2013-11-11 16:00:00',85),
(1007,2,'2013-11-12 16:00:00',79)