mySQL 练习

增删改查的基本练习一

/*查询所有学生信息，返回所有字段*/
SELECT * FROM students;
/*查询所有学生，返回学生的姓名、年龄、所在地*/
SELECT name,age,city FROM students;
/*查询所有学生，返回学生的姓名、年龄在年龄的后边+岁)、所在地、国家（附加列，固定值为中国）*/
SELECT name,CONCAT(age,'岁') AS age,city,'中国' AS country FROM students;
/*查询年龄大于30的男生信息，返回学生的姓名、性别、年龄、所在地*/
SELECT name,sex,age,city FROM students WHERE age>30 AND sex='男';
/*查询前两位的学生信息，返回学生姓名、年龄、所在地*/
SELECT name,age,city FROM students LIMIT 0,2;
/*查询从第3位同学后边的2位同学信息*/
SELECT * FROM students LIMIT 3,2;
/*按年龄从大到小显示所有学员信息*/
SELECT * FROM students ORDER BY age DESC;
/*查询所有1班学员信息，并按照年龄的升序、性别的降序显示*/
SELECT *FROM students WHERE classid=1  ORDER BY age ASC,sex DESC;
/*查询3班中年龄最小的2位学员信息，返回学员的姓名、年龄、所在地，班级编号*/
SELECT name,age,city,classid FROM students WHERE classid=3 GROUP BY age ASC LIMIT 0,2;
/*统计所有学员年龄的总和*/
SELECT SUM(age) FROM students;
/*统计所有学员的平均年龄*/
SELECT AVG(age) FROM students;
/*统计所有学员的最大年龄*/
SELECT MAX(age) FROM students;
/*查询最近的开班时间*/
SELECT MAX(begin) FROM classes;
/*统计2班有多少个学生*/
SELECT COUNT(*) FROM students WHERE classid=2;
/*查询所有姓”张”的学员信息*/
SELECT * FROM students WHERE name LIKE '张%';
/*查询学生姓名中包含”乔”的学员信息*/
SELECT * FROM students WHERE name LIKE '%乔%';
/*查询2015年开班的班级信息(between and)*/ 
SELECT * FROM classes WHERE begin BETWEEN '2015-1-1' AND '2015-12-31';
/*查询所在地在“成都”，“许都”的学员信息*/
SELECT * FROM students WHERE city in ('成都','许都');
/*查询每个班的最大年龄，返回班级名称和最大年龄*/
SELECT classes.name,MAX(students.age) FROM classes,students WHERE students.classid=classes.id GROUP BY students.classid;
/*查询每个班级中最小年龄低于30岁的信息，返回班级名称与最小年龄，并按年龄降充显示*/
SELECT classes.name,MIN(students.age) FROM classes,students WHERE students.classid=classes.id GROUP BY students.classid ORDER BY MIN(students.age) DESC ;
/*查询班主任为”龙女”的学员信息，返回学员姓名、年龄、所在地及所在班级名称*/
SELECT students.name,students.age,students.city,classes.name FROM students,classes WHERE students.classid=classes.id AND classes.manager='龙女';
/*查询2015年开班的学员信息，返回学员姓名、年龄、所在班级名称*/
SELECT students.name,students.age,classes.name FROM classes,students WHERE students.classid=classes.id AND classes.begin BETWEEN '2015-1-1' AND '2015-12-31';
/*查询大于平均年龄的学员信息，返回学员的姓名、年龄、所在班级名称，班主任*/
SELECT students.name,students.age,classes.name,classes.manager FROM classes,students  WHERE students.classid=classes.id AND students.age>(SELECT AVG(students.age) FROM students);
/*查询最大年龄学员所在的班级信息，返回班级名称、班主任*/
SELECT classes.name ,classes.manager FROM classes,students WHERE students.classid=classes.id ORDER BY students.age DESC LIMIT 0,1;

/*查询可爱度在90以上的宠物信息，返回宠物名称、可爱度、健康值、宠物类型*/
SELECT * FROM pets;
SELECT name,lovely,health,typeid FROM pets;
/*查询2012年下半年收养的宠物信息，返回宠物名称、可爱度、健康值，、宠物类型，收养时间*/
SELECT name,lovely,health,typeid,adoptdate FROM pets WHERE adoptdate BETWEEN '2012-6-1' AND '2013-12-31';
/*查询可爱度和健康值都在90以上的老虎，返回宠物名字、健康值、可爱度、类型名称*/
SELECT name,health,lovely,typeid FROM pets WHERE lovely>90 AND health>90 AND typeid=3;
/*查询宠物最早收养时间，返回收养时间。*/
SELECT MIN(adoptdate) FROM pets;
/*查询最低的健康值，返回健康值。*/
SELECT MIN(health) FROM pets;
/*查询收养宠物的总数量*/
SELECT COUNT(*) FROM pets;
/*查询猫的平均健康值*/
SELECT AVG(health) FROM pets WHERE typeid=2;
/*查询可爱度最高的前3只宠物*/
SELECT * FROM pets GROUP BY lovely DESC LIMIT 0,3;
/*查询健康值小于60的宠物数量*/
SELECT count(*) FROM pets WHERE health<60;
/*完成主人登录功能：根据给定的用户名和密码，检查主人信息是否存在（可以使用count函数）*/
SELECT COUNT(*) FROM masters WHERE loginname='admin' AND password='123456';
/*查询登录名中包含admin的主人信息*/
SELECT * FROM masters WHERE loginname LIKE '%admin%';
/*查询可爱度介于80-90之间的宠物信息*/
SELECT * FROM pets WHERE lovely BETWEEN 80 AND 90;
/*查询收养时间不在2012年的宠物信息*/
SELECT *FROM pets WHERE adoptdate NOT BETWEEN '2012-1-1' AND '2012-12-31';
/*查询宠物类型为1或2的宠物信息*/
SELECT *FROM pets WHERE typeid in(1,2);
-- SELECT *FROM pets,pettypes WHERE pets.typeid=pettypes.id;
/*查询各种类型的宠物数量，返回宠物类型名称、数量*/
SELECT pettypes.name,count(*) FROM pets,pettypes WHERE pets.typeid=pettypes.id GROUP BY pets.typeid;
/*查询各类宠物健康平均值超过80的宠物，返回宠物类型名称、健康平均值，按健康值的降序显示*/
-- SELECT typeid,AVG(health) FROM pets GROUP BY typeid ORDER BY health DESC;
SELECT pets.name,pettypes.name,pets.health FROM pets,pettypes WHERE pets.typeid=pettypes.id AND pets.health>80 ORDER BY health DESC;
/*查询所有宠物信息，返回宠物名称、健康值、可爱度、宠物类型名称、主人名字*/
SELECT pets.name,pets.health,pets.lovely,pets.typeid,masters.loginname FROM pets ,masters WHERE pets.masterid=masters.id;
/*查询主人为admin收养的宠物信息，返回宠物名称、健康值、可爱度、宠物类型名称、主人名字*/
SELECT pets.name,pets.health,pets.lovely,pettypes.name,masters.loginname FROM masters,pets,pettypes WHERE pets.typeid=pettypes.id AND pets.masterid=masters.id AND masters.loginname='admin';
/*查询饲养老虎的相关信息，返回宠物名称、宠物类型、主人名字*/
SELECT pets.name,pettypes.name,masters.loginname FROM pets,pettypes,masters WHERE pettypes.name='虎' AND pets.typeid=pettypes.id AND pets.masterid=masters.id;
/*查询可爱度最高的宠物信息，返回宠物名称、健康值、可爱度、宠物类型名称、主人名称，并按照可爱度的降序显示*/
SELECT pets.name,pets.health,pets.lovely,pettypes.name,masters.loginname FROM pets,pettypes,masters WHERE pets.typeid=pettypes.id AND pets.masterid=masters.id ORDER BY pets.lovely DESC LIMIT 0,1;