[mysql]数据库查询实例

name	age
yjh	23
zhong	21
bin	23
chao	24

0、建立表

create table student(
id int(10) auto_increment primary key,
name varchar(10) not null,
birth year not null,
department varchar(10) not null,
address varchar(10) not null
);

格式是  列名 类型（长度） ，  记得increment的拼写，不是increasement。 是varchar不是vchar

插入数据

insert into student values(null,'张老大',1985,'计算机系','北京市海淀区');

autoincrement的列给null就会自动从1开始递增

1、查找age最小的一个

普通SQL可使用top 1，但mysql没有top关键字
select * from stu order by num limit 0,1;
也可以使用子查询
select * from stu where num<=all(select * from stu);
也可以使用min函数
select * from stu where num=(select min(num) from stu);

2、查找年龄一样的人

首先用group by 选择重复的num的集合，然后用in选择合适的行
select * from stu where num in (select num from stu group by num having count(*)>1);
注意，使用了group by后，有关类聚的限定条件不再使用where，使用having

3、按成绩降序输出

注意降序的关键字，不是descend
select * from stu order by num desc;

student

+-----+--------+------+-------+------------+--------------+
| Id  | Name   | Sex  | Birth | Department | Address      |
+-----+--------+------+-------+------------+--------------+
| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |
| 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
| 905 | 王五   | 女   |  1991 | 英语系     | 福建省厦门市 |
| 906 | 王六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+

score

+----+--------+--------+-------+
| Id | Stu_id | C_name | Grade |
+----+--------+--------+-------+
|  1 |    901 | 计算机 |    98 |
|  2 |    901 | 英语   |    80 |
|  3 |    902 | 计算机 |    65 |
|  4 |    902 | 中文   |    88 |
|  5 |    903 | 中文   |    95 |
|  6 |    904 | 计算机 |    70 |
|  7 |    904 | 英语   |    92 |
|  8 |    905 | 英语   |    94 |
|  9 |    906 | 计算机 |    90 |
| 10 |    906 | 英语   |    85 |
+----+--------+--------+-------+

1、查找英语系和计算机系人的信息

蠢的写法
select * from student where Department='计算机系' or Department='英语系';
正常写法，使用集合
select * from student where Department in ('计算机系','英语系');

2、选择18~22的学生信息

使用between and表示范围，now()函数显示当前时间，date_format()格式化输出
select * from student where (select date_format(now(),'%Y'))-Birth between 18 and 22;

3、统计每个系的人数

select Department,count(*) from student group by Department;

4、选择每门课的最高分

使用group by，一般都会使用聚类函数
select C_name,max(Grade) from score group by C_name;

5、选择李四的考试科目以及成绩

使用子查询
select c_name,grade from score where Stu_id=(select Id from student where Name='李四');

6、链接两表查询所有学生信息和考试信息

select student.Id,Name,Sex,Address,Birth,Department,C_name,Grade from student,score where student.Id=Stu_id;

7、显示学生的总成绩

非聚类的条件还是用where，聚类的条件使用having
select Name,sum(Grade) from score,student where student.Id=Stu_id group by Stu_id;

8、显示各科平均分

使用AS重命名列名
select C_name,avg(Grade) as '平均分' from score group by C_name;

9、选择计算机低于95的学生信息

select * from student where Id in (select Stu_id from score where C_name='计算机' and Grade<95);

10、选择同时考英语和计算机学生的信息

查询同时XX时，不能用and，因为同一条记录不能同时是a又是b，只能是一条记录是a，另一条记录是b，而此两条记录都是属于同一个人。因此可以把是a的人的集合和是b的人的集合都选出来，然后再两个集合求交集
select * from student where Id in (select distinct Stu_id from score where Stu_id in (select Stu_id from score where C_name='计算机') and Stu_id in (select Stu_id from score where C_name='英语'));

也可以先选出符合一个条件的集合，再从此集合中选择符合第二个条件的
select * from student where Id in (select Stu_id from score where Stu_id in (select Stu_id from score where C_name='计算机') and C_name='英语');

或者使用两个相同的表，分别与学生表链接并规定范围
select a.* from student a,score b,score c where a.Id=b.Stu_id and a.Id=c.Stu_id and b.C_name='计算机' and c.C_name='英语';

11、查询计算机考试成绩并从高到低排序

select * from score where C_name='计算机' order by Grade desc;

12、从两个表查询学生学号，并合并

union all直接合并两个查询结果
SELECT id  FROM student union all SELECT stu_id  FROM score;
union合并后排序并去除重复记录，效率比union all低
SELECT id  FROM student union SELECT stu_id  FROM score;

mysql只有union一个并集操作，差集交集使用in和not in自行实现

交集
select * from score where stu_id in (select stu_id from score where c_name='计算机') and c_name='英语';
差集
select * from score where stu_id not in (select stu_id from score where c_name='计算机') and c_name='英语';

13、查找姓王和姓张的同学的姓名、院系、考试科目及成绩

普通like关键字，使用%代表不定长字符，_代表单个字符
select name,department,c_name,grade from student,score where stu_id=student.id and (name like '张%' or name like '王%');
rlike可使用正则表达式
select name,department,c_name,grade from student,score where stu_id=student.id and (name rlike '张*' or name rlike '王*');

14、查询所有来自湖南学生的个人信息及考试信息

select name,department,c_name,grade from student,score where stu_id=student.id and address rlike '湖南*';

15、使用exists

exists表明子查询的结果集大于1条
mysql> select name from student where exists (select * from student where birth=1988) and name='王六';

16、group by多个字段

先按dept分组，组内按job分组
select dept,job,max(salary) from factory group by dept,job;

17、order by 可用第n个字段的n代替字段名

两者是一样的
select Stu_id ,Grade from score order by 2;
select Stu_id ,Grade from score order by Grade asc; //asc是升序，默认就是升序，所以一般很少用。降序desc