SQL语句与多表查询

首先先了解一下一些基本的概念问题：

1.group by是分组查询一般和聚合函数一起使用。

例如：

Select   studentID,Count(*)   From   sc   Group   By   score Having   Count(*)   >   1

常用的聚合函数有sum,count,等。

学生表（student）（学号，姓名，性别，年龄，学院），课程表（course）（课程号，课程名，学分），学生选课表（SC）（学号，课程号，成绩）

/*
SQLyog Enterprise - MySQL GUI v7.11 
MySQL - 5.0.21-community-nt : Database - toutiao
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`toutiao` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `toutiao`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(11) NOT NULL auto_increment,
  `courseID` int(11) NOT NULL,
  `courseName` varchar(11) NOT NULL,
  `credit` int(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`courseID`,`courseName`,`credit`) values (1,0,'',0);

/*Table structure for table `sc` */

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (
  `id` int(11) NOT NULL auto_increment,
  `studentID` int(11) NOT NULL,
  `courseID` int(11) NOT NULL,
  `score` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `sc` */

insert  into `sc`(`id`,`studentID`,`courseID`,`score`) values (1,1101,1101,89),(2,1101,1102,67),(3,1101,1103,90),(4,1101,1104,89),(5,1101,1105,89),(6,1102,1101,69),(7,1102,1102,69);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL auto_increment,
  `sno` varchar(11) NOT NULL default '',
  `username` varchar(11) NOT NULL default '',
  `sex` char(2) NOT NULL,
  `age` int(4) NOT NULL,
  `collage` varchar(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`sno`,`username`,`sex`,`age`,`collage`) values (1,'11','11','',0,''),(2,'11','11','',0,''),(3,'33','jou','',0,''),(4,'11','fff','',0,'');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

1.学生中其他学院比计算机学院姓“刘”的学生年龄都小学生（结果列：学号，姓名，年龄，学院）

2.查询选修了2门以上课程的学生（结果列：学号，姓名）

3.查询没门课程都大于80分的学生（结果列：学号，姓名）

4，如果下表为生产加工每个计划对应的成品，退料，余料，废次材捆包的个数
计划号      捆包类型     个数
001         成品         5
001         退料         2
002         成品         4
002         余料         1
002         废次材       1
003         成品         5
003         废次材       1
用一句select语句实现以下方式统计各个计划对应各个类型捆包的个数：
计划号      成品     退料   余料   废次材
001         5        2       0      0
002         4        0       1      1
003         5        0       0      1

5.写出一句SQL语句：取出表A中第31DAO第40记录（已自动增长的ID作为主键。注意：ID可能不是连续的。）

6,如何删除一个表中某个字段记录重复的记录（重复的只要保留一条）

6.1查询表中多余的重复记录，重复的记录以字段(studentID)为根据。

select   *   from   sc where   studentID  in   
(select     studentID     from     sc     group     by     studentID     having     count(studentID)   >   1)

6.2查询表中重复记录，重复的记录以（studentID，score）为根据，即是多个字段。

select * from sc a where (a.studentID,a.score) in 
(select studentID ,score from sc group by studentID,score having count(*)>1);

6.3查询表中重复记录，重复的记录以（studentID，score）为根据，但是重复记录中不包含（id）最小的这条记录。

select * from sc a where (a.studentID,a.score)in     
(select   studentID,score from  sc   group  by  studentID,score   having   count(*)   >   1)
and   id   not   in   (select   min(id)   from   sc   group   by   studentID,score  having   count(*)> 1)

数据库的基础之二

分享到：