学生成绩表sql示例

/*
SQLyog v10.2 
MySQL - 5.1.62-community : Database - ab
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

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

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `a` */

CREATE TABLE `a` (
  `id` varchar(20) NOT NULL,
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(20) NOT NULL,
  `s_age` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `a` */

insert  into `a`(`id`,`s_id`,`s_name`,`s_age`) values ('1','1245','张三','16'),('2','002','李四','16'),('3','003','刘琦','15');

/*Table structure for table `b` */

CREATE TABLE `b` (
  `id` varchar(20) NOT NULL,
  `s_id` varchar(20) NOT NULL COMMENT '学号',
  `s_ubject` varchar(20) NOT NULL COMMENT '科目',
  `s_core` varchar(36) NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `b` */

insert  into `b`(`id`,`s_id`,`s_ubject`,`s_core`) values ('1','1245','数学','80'),('2','1245','语文','96'),('3','1245','英语','71'),('4','002','数学','80'),('5','002','语文','76'),('6','002','英语','70'),('7','003','数学','50'),('8','003','英语','56'),('9','003','语文','99');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

/**
a是学生表
b是成绩表
*/
查询学号为1245名字为张三的语文成绩

SELECT s_name,s_ubject,s_core FROM a,b WHERE a.s_id=b.s_id AND s_name=’张三’ AND s_ubject=’语文’

按学号统计每个学生的总成绩

SELECT s_id AS 学号, SUM(s_core) AS 总成绩
FROM b
GROUP BY s_id

查询a表中重名的学生名字
SELECT s_name,COUNT() FROM a GROUP BY s_name HAVING COUNT()>1