-- 测试其它常用函数

SELECT VERSION(),CONNECTION_ID();

SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();

-- 得到上一步插入操作产生AUTO_INCREMENT的值

SELECT LAST_INSERT_ID();

SELECT MD5('king');

-- PASSWORD():密码加密算法

SELECT PASSWORD('root');

CREATE TABLE IF NOT EXISTS class(

id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '班级ID',

name VARCHAR(20)  NOT NULL UNIQUE COMMENT '名称',

descrip VARCHAR(20) NOT NULL COMMENT '备注'

)ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT class VALUES(001,'一年级一班','重点班');

INSERT class VALUES(002,'一年级二班','重点班');

INSERT class VALUES(003,'二年级一班','重点班');

INSERT class VALUES(004,'二年级二班','普通班');

CREATE TABLE IF NOT EXISTS student(

s_id INT(4) UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID',

name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',

gender ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',

class TINYINT UNSIGNED COMMENT '班级'

)ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT student VALUES(1001,'赵小明','男',001),

(NULL,'王晓红','男',001),

(NULL,'张潇潇','女',001),

(NULL,'孙琪琪','女',003),

(NULL,'李咪咪','女',004),

(NULL,'赵晓刚','男',003),

(NULL,'张大宝','男',002),

(NULL,'张兰','女',004),

(NULL,'孙浩','男',001);

CREATE TABLE IF NOT EXISTS score(

sc_id TINYINT(3) UNSIGNED AUTO_INCREMENT  KEY COMMENT '成绩ID',

s_id INT UNSIGNED COMMENT '学生ID',

course VARCHAR(20) NOT NULL COMMENT '课程',

mark INT UNSIGNED COMMENT '分数'

)ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT score (s_id,course,mark)VALUES(1001,'数学',98),

(1001,'语文',90),(1001,'英语',97),(1002,'数学',96),

(1002,'语文',88),(1003,'语文',88),(1002,'英语',91),

(1003,'数学',96),(1003,'英语',86),(1004,'数学',89),

(1004,'语文',82),(1004,'英语',83),(1005,'数学',75),

(1005,'语文',86),(1005,'英语',77),(1006,'数学',81),

(1006,'语文',77),(1006,'英语',60),(1007,'数学',89),

(1007,'语文',56),(1007,'英语',70),(1008,'数学',87),

(1008,'语文',55),(1008,'英语',66),(1009,'数学',78),

(1009,'语文',60),(1009,'英语',52);

SELECT cl.c_id ,cl.name,sc.course,avg(sc.mark) from class AS cl

JOIN student AS stu

on cl.id=stu.class

JOIN score AS sc

on stu.s_id=sc.s_id

GROUP BY cl.name,sc.course

ORDER BY cl.id DESC;