SQL练习

---

一、练习一

sql:

CREATE TABLE `Student` (
	`SRUNO` INT ( 8 ) NOT NULL,
	`SNAME` VARCHAR ( 12 ) NOT NULL,
	`SEX` TINYINT ( 2 ) NULL,
	`BIRTHDAY` VARCHAR ( 255 ) NULL,
	`EMAIL` VARCHAR ( 255 ) NULL,
	`SCORE` TINYINT ( 255 ) NULL,
	`CLASSNO` TINYINT ( 0 ) NOT NULL DEFAULT Student AUTO_INCREMENT,
	PRIMARY KEY ( `SRUNO` ) 
);

# 给表中一班的成绩加2
UPDATE student SET score = score + 2 
	
# 给姓张的按成绩降序排序
SELECT * FROM student WHERE SNAME LIKE '张%' ORDER BY score DESC;

SELECT * FROM student WHERE SNAME LIKE '张%' ORDER BY score ASC;

# 查询所有学生的生日
select BIETHDAY from student;

# 按班级编号统计各班的人数
SELECT count(*) from student where classno = 1;
SELECT count(*) from student where classno = 2;
SELECT count(*) from student where classno = 3;

# 最高分
SELECT MAX(score) from student where classno = 1 ;
#最低分
SELECT MIN(score) from student where classno = 1 ;
#平均分
SELECT AVG(score) from student where classno = 1 ;

# (5)
SELECT classno,COUNT(classno),MAX(score),MIN(SCORE),AVG(SCORE) FROM student GROUP BY classno ORDER BY AVG(SCORE) DESC;