CREATE DATABASE test;
use test;
CREATE TABLE students (
studentNo int(10) primary key auto_increment,
names varchar(10),
sex varchar(1),
hometown varchar(20),
age int(4),
class varchar(10),
card varchar(20)
);
INSERT INTO students (names, sex, hometown, age, class, card)
VALUES ('王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
( '张飞', '男', '南京', '24', '3班', '340322199003247654'),
( '白起', '男', '安徽', '22', '4班', '3403221 99005247654'),
( '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
( '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
( '小乔', '女', '河南', '15', '3班', NULL),
( '百里守约', '男', '湖南', '21', '1班', ''),
( '妲己', '女', '广东', '26', '2班', '340322199607247654'),
( '李白', '男', '北京', '30', '4班', '340322199005267754'),
( '孙膑', '男', '新疆', '26', '3班', '340322199000297655');

use test;
CREATE TABLE courses (
courseNo int(10) PRIMARY KEY AUTO_INCREMENT,
names varchar(10)
);
INSERT INTO courses
VALUES ('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');

use test;
CREATE TABLE scores (
id int(10) PRIMARY KEY AUTO_INCREMENT,
courseNo int(10),
studentNo int(10),
score int(4)
);
INSERT INTO scores
VALUES ('1', '1', 1, '90'),
('2', '1', 2, '75'),
('3', '2', 2, '98'),
('4', '3', 1, '86'),
('5', '3', 3, '80'),
('6', '4', 4, '79'),
('7', '5', 5, '96'),
('8', '6', 6, '80');


/*题目
1、查询学生"百里守约"的基本信*/
/*select * from students where names='百里守约';*/
/*2、查询学生"百里守约"或”百里玄策”的基本信息*/
/*select * from students where names='百里守约'or names = '百里玄策';*/
/*3、查询姓"张"学生的姓名，年龄，班级*/
/*select names,age,class from students where names like '张%';
/*4、查询姓名中含有"约"字的学生的基本信息*/
/*select * from students where names like '%约%';
/*5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号*/
/*select names,age,class,card from students where names like '孙__';
/*6、查询姓"百"或者姓”孙”的学生的基本信息*/
/*select * from students where names like '百%' or names like '孙%';
/*7、查询姓"百"并且家乡是"山西"的学生信息*/
/*select * from students where names like '百%' and hometown='山西';
/*8、查询家乡是"北京"、”新疆”、”山东”或者"上海"的学生的信息*/
/*select * from students where hometown='北京,新疆' or hometown='山东, 上海';
/*9、查询姓"孙"，但是家乡不是"河北"的学生信息*/
select * from students where names like '孙%' and hometown not in ('河北');
/*10、查询家乡不是"北京"、"新疆"、"山东"、"上海"的学生的信息*/
/*select * from students where hometown not in('北京','新疆','山东','上海');*/
/*11、查询全部学生信息，并按照“性别”排序*/
/*select * from students  order by sex;*/
/*12、查询现有学生都来自于哪些不同的省份*/
/*select hometown from students group by hometown;*/
/*13、查询所有男生，并按年龄升序排序*/
/*select * FROM students where sex='男' order by age;*/
/*14、统计共有多少个学生*/
select count(*) from students;
/*15、统计年龄大于20岁的学生有多少个*/
select count(*) from students where age>20;
/*16、统计男生的平均年龄*/
select avg(age) from students;
/*17、查询1班学生中的最大年龄是多少*/
select max(age) from students where class='1班'; 
/*18、统计2班男女生各有多少人*/
select sex, count(*) from students where  class='2班' group by sex;
/*19、统计每个班级中每种性别的学生人数，并按照班级升序排序*/
select class,sex,count(*) from students group by class,sex  order by class;