基本查询 习题1-11

数据资料

CREATE TABLE student(    id INT(10) NOT NULL PRIMARY KEY  UNIQUE COMMENT '编号' AUTO_INCREMENT,    name VARCHAR(2)  NOT NULL COMMENT'名字',    sex VARCHAR(4)   COMMENT '性别' ,    birth year COMMENT '年月日' ,    department VARCHAR(20),    address VARCHAR(50))AUTO_INCREMENT=901;CREATE TABLE score(    id INT(10)  COMMENT '编号' ,    stu_id int(10) ,    c_name VARCHAR(20),    grade INT(10));DROP TABLE student;DELETE FROM student;DROP TABLE score;DELETE FROM score;INSERT INTO student(name,sex,birth,department,address) VALUES('张老大','男',1985,'计算机系','北京市海淀区'),('张老二','男',1986,'中文系','北京市昌平淀区'),('张三','女',1990,'中文系','湖南省永州市区'),('李四','男',1990,'英语系','辽宁省胡新市'),('王五','女',1991,'英语系','福建省厦门市'),('王六','男',1988,'计算机系','湖南省衡阳市');INSERT INTO score(stu_id,c_name,grade) VALUES(901,'计算机',98),(901,'英语',80),(902,'计算机',65),(902,'中文',88),(903,'中文',95),(904,'计算机',70),(904,'英语',92),(905,'英语',94),(906,'计算机',90),(906,'英语',85);

1查询student表的所有记录

SELECT *        FROM student;

– 2查询student表的第2条到4条记录

SELECT *        FROM student        where id BETWEEN 902 and 903;

– 3从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department        from student

– 4从student表中查询计算机系和英语系的学生的信息

SELECT *        FROM student         where department='计算机系' or department='英语系';

– 5从student表中查询年龄18~22岁的学生信息

SELECT *        FROM student         where birth BETWEEN 1995 and 1999;

– 6从student表中查询每个院系有多少人

select department,COUNT(department)     from student     GROUP BY department;   -- 默认有一个升序排序

– 7从score表中查询每个科目的最高分

select  c_name,MAX(grade)        from score        GROUP BY c_name;

– 8查询李四的考试科目（c_name）和考试成绩（grade）

select  c_name,grade        from score        WHERE stu_id in(SELECT id FROM student WHERE id = 904) ;

– 9用连接的方式查询所有学生的信息和考试信息???

SELECT student.id,name,sex,birth,department,address,c_name,grade    FROM student,score    WHERE student.id=score.stu_id

– 10计算每个学生的总成绩

SELECT student.id,name,sex,birth,department,address,GROUP_CONCAT(c_name),avg(grade)    FROM student,score    WHERE student.id=score.stu_id    GROUP BY stu_id;

– 11查询student表的所有记录 改变group的值 即可查找所有元素

SELECT student.id,name,sex,birth,department,address,c_name,sum(grade)        FROM student,score        WHERE student.id=score.stu_id        GROUP BY sex;