学生成绩表sql示例

小灰灰 2022-06-17 07:20 187阅读 0赞

/* 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