DQL练习资料

SQL查询练习资料

一.使用工具

SQLyog:

SQLyog 是一个快速而简洁的图形化管理MYSQL数据库的工具
mysql端口号3306
基字符集 utf8
数据库排序规则utf8_general_ci

二.查询练习所需资料

CREATE DATABASE IF NOT EXISTS `school`;

USE school;

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;

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;

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 = 19 DEFAULT CHARSET = utf8;

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 `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES(1000,'111111','周丹',1,1,'13800000001','地址1','1986-12-11','text111@qq.com','123456198001011201'),
(1001,'123456','周颖',1,2,'13800000002','地址2','1981-12-31','text111@qq.com','123456198001011202'),
(1002,'111111','杨文瑞',1,1,'13800000003','地址3','1986-11-30','text111@qq.com','123456198001011203'),
(1003,'123456','韩萌',1,3,'13800000004','地址4','1986-12-31','text111@qq.com','123456198001011204'),
(1004,'123456','刘丽霞',1,4,'13800000005','地址5','1989-12-31','text111@qq.com','123456198001011205'),
(1005,'123456','蒋佳航',2,1,'13800000006','地址6','1986-12-31','text111@qq.com','123456198001011206'),
(1006,'123456','刘洋',2,4,'13800000007','地址7','1986-12-31','text111@qq.com','123456198001011207'),
(1007,'111111','刘二狗',1,1,'13800000008','地址8','1986-12-31','text111@qq.com','123456198001011208'),
(1008,'111111','赵大狗',1,1,'13800000009','地址9','1986-12-31','text111@qq.com','123456198001011209'),
(1009,'123456','周杰伦',1,2,'13800000010','地址10','1986-12-31','text111@qq.com','123456198001011210'),
(1010,'111111','许嵩',1,1,'13800000011','地址11','1986-12-31','text111@qq.com','123456198001011211'),
(1011,'111111','汪苏泷',1,1,'13800000012','地址12','1984-12-31','text111@qq.com','123456198001011212'),
(1012,'123456','刘德华',2,3,'13800000013','地址13','1986-12-31','text111@qq.com','123456198001011213'),
(1013,'123456','张卫健',2,1,'13800000014','地址14','1986-12-31','text111@qq.com','123456198001011214'),
(1014,'123456','刘备',2,4,'13800000015','地址15','1986-12-31','text111@qq.com','123456198001011215'),
(1015,'123456','曹操',1,4,'13800000016','地址16','1976-12-31','text111@qq.com','123456198001011216'),
(1016,'111111','关羽',1,1,'13800000017','地址17','1986-12-31','text111@qq.com','123456198001011217'),
(1017,'123456','张飞',2,3,'13800000018','地址18','1981-09-10','text111@qq.com','123456198001011218');

INSERT INTO `grade` (`gradeid`,`gradename`) 
VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

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程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

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',85),
(1001,2,'2013-11-12 16:00:00',70),
(1001,3,'2013-11-11 09:00:00',68),
(1001,4,'2013-11-13 16:00:00',98),
(1001,6,'2013-11-14 16:00:00',58),
(1002,1,'2013-11-11 16:00:00',85),
(1002,2,'2013-11-12 16:00:00',70),
(1002,3,'2013-11-11 09:00:00',68),
(1002,4,'2013-11-13 16:00:00',98),
(1002,7,'2013-11-14 16:00:00',58),
(1003,1,'2013-11-11 16:00:00',85),
(1003,2,'2013-11-12 16:00:00',70),
(1003,3,'2013-11-11 09:00:00',68),
(1003,4,'2013-11-13 16:00:00',98),
(1003,8,'2013-11-14 16:00:00',58),
(1004,1,'2013-11-11 16:00:00',85),
(1004,2,'2013-11-12 16:00:00',70),
(1004,3,'2013-11-11 09:00:00',68),
(1004,4,'2013-11-13 16:00:00',98),
(1004,9,'2013-11-14 16:00:00',58),
(1005,1,'2013-11-11 16:00:00',85),
(1005,2,'2013-11-12 16:00:00',70),
(1005,3,'2013-11-11 09:00:00',68),
(1005,4,'2013-11-13 16:00:00',98),
(1005,10,'2013-11-14 16:00:00',58),
(1006,1,'2013-11-11 16:00:00',85),
(1006,2,'2013-11-12 16:00:00',70),
(1006,3,'2013-11-11 09:00:00',68),
(1006,4,'2013-11-13 16:00:00',98),
(1006,11,'2013-11-14 16:00:00',58),
(1007,1,'2013-11-11 16:00:00',85),
(1007,2,'2013-11-12 16:00:00',70),
(1007,3,'2013-11-11 09:00:00',68),
(1007,4,'2013-11-13 16:00:00',98),
(1007,12,'2013-11-14 16:00:00',58),
(1008,1,'2013-11-11 16:00:00',85),
(1008,2,'2013-11-12 16:00:00',70),
(1008,3,'2013-11-11 09:00:00',68),
(1008,4,'2013-11-13 16:00:00',98),
(1008,13,'2013-11-14 16:00:00',58),
(1009,1,'2013-11-11 16:00:00',85),
(1009,2,'2013-11-12 16:00:00',70),
(1009,3,'2013-11-11 09:00:00',68),
(1009,4,'2013-11-13 16:00:00',98),
(1009,14,'2013-11-14 16:00:00',58),
(1010,1,'2013-11-11 16:00:00',85),
(1010,2,'2013-11-12 16:00:00',70),
(1010,3,'2013-11-11 09:00:00',68),
(1010,4,'2013-11-13 16:00:00',98),
(1010,15,'2013-11-14 16:00:00',58),
(1011,1,'2013-11-11 16:00:00',85),
(1011,2,'2013-11-12 16:00:00',70),
(1011,3,'2013-11-11 09:00:00',68),
(1011,4,'2013-11-13 16:00:00',98),
(1011,16,'2013-11-14 16:00:00',58),
(1012,1,'2013-11-11 16:00:00',85),
(1012,2,'2013-11-12 16:00:00',70),
(1012,3,'2013-11-11 09:00:00',68),
(1012,4,'2013-11-13 16:00:00',98),
(1012,17,'2013-11-14 16:00:00',58),
(1013,1,'2013-11-11 16:00:00',85),
(1013,2,'2013-11-12 16:00:00',70),
(1013,3,'2013-11-11 09:00:00',68),
(1013,4,'2013-11-13 16:00:00',98),
(1013,6,'2013-11-14 16:00:00',58),
(1014,1,'2013-11-11 16:00:00',85),
(1014,2,'2013-11-12 16:00:00',70),
(1014,3,'2013-11-11 09:00:00',68),
(1014,4,'2013-11-13 16:00:00',98),
(1014,7,'2013-11-14 16:00:00',58),
(1015,1,'2013-11-11 16:00:00',85),
(1015,2,'2013-11-12 16:00:00',70),
(1015,3,'2013-11-11 09:00:00',68),
(1015,4,'2013-11-13 16:00:00',98),
(1015,8,'2013-11-14 16:00:00',58),
(1016,1,'2013-11-11 16:00:00',85),
(1016,2,'2013-11-12 16:00:00',70),
(1016,3,'2013-11-11 09:00:00',68),
(1016,4,'2013-11-13 16:00:00',98),
(1016,9,'2013-11-14 16:00:00',58),
(1017,1,'2013-11-11 16:00:00',85),
(1017,2,'2013-11-12 16:00:00',70),
(1017,3,'2013-11-11 09:00:00',68),
(1017,4,'2013-11-13 16:00:00',98),
(1017,10,'2013-11-14 16:00:00',58);

将上面语句逐个执行即可，生成所需要的的库和表！

三.查询练习

格式说明：

select 去重(distinct) 要查询的字段 from 表(注意：表和字段可以取别名)
left/right/inner join 另张表 on 等值判断
where (具体的值或子查询语句)
group by (通过哪个字段分组)
having (过滤分组后的信息，条件格式和where一样)
order by (升序asc/降序desc)
limit (start,size);

这里我只声明了一下具体的先后顺序。

四.具体的SQL查询题

借鉴B站up主“遇见狂神说”之《MySQL最新教程通俗易懂》（p16开始跟随视频练习DQL)，通过看视频辅助学习，效果更好哦！~~~