mysql查询笔记_MySQL学习笔记（一）：查询

查询实例：

1.创建数据库并使用：

create database school;

use school;

2.创建表并插入内容：

create table student(

Sno char(9) primary key,

Sname char(20) unique,

Ssex char(2),

Sage int,

Sdept char(20));

create table Course(

Cno char(4) primary key,

Cname char(40),

Cpno char(4),

Ccredit int

);

create table SC(

Sno char(9),

Cno char(4),

Grade int,

primary key(Sno,Cno),

foreign key (sno) references Student(sno),

foreign key (Cno) references Course(Cno));

insert into student values ('95001','李勇','男',20,'CS');

insert into student values ('95002', '刘晨','女',19,'IS');

insert into student values('95003', '王敏', '女',18, 'MA');

insert into student values('95004', '张立', '男',19, 'IS');

insert into Course values(1,'数据库', 5,4);

insert into Course values(2, '数学', 3 ,2);

insert into Course values(3, '信息系统', 1, 4);

insert into Course values('4', '操作系统', '6', 3);

insert into Course values('5', ' 数据结构', '7', 4);

insert into Course values('6', '数据处理', '', 2);

insert into Course values('7', 'PASCAL语言', '6', 4);

insert into SC values('95001', '1', 92);

insert into SC values('95001', '2', 85);

insert into SC values('95001', '3', 88);

insert into SC values('95002', '2', 90);

insert into SC values('95002', '3', 80);

3.查询实例：

1. 投影查询

(1) 查询SC表的sno的不重复记录。

select distinct * from SC;

(2) 改变查询结果的标题名：sno为学号,sname为姓名,ssex 为性别,sdept 为系名。

select sno as "学号", sname as "姓名", ssex as "性别", sdept as "系名" from student;

(3) 查询STUDENT表的前3条记录。

select * from student limit 3;

2. 选择查询

(1) 查询成绩在60-80之间的姓名、系名和成绩。

select sname,sdept,grade from student, SC where grade > 60 and grade < 90;

(2) 查询信息系和计算机系的姓名和成绩。

select grade, sname from student join sc on student.sno = sc.sno join course on course.cno = sc.cno

where sdept = 'cs' or sdept = 'is';

(3) 查询计算机系或女同学的记录。

select * from student join sc on student.sno = sc.sno join course on course.cno = sc.cno where sdept = 'cs' or ssex = '女';

(4) 查询李姓同学的记录。

select * from student join sc on student.sno = sc.sno join course on course.cno = sc.cno where sname like '%李%';

(5) 查询计算机系女同学的记录。

select * from student join sc on student.sno = sc.sno join course on course.cno = sc.cno where sdept = 'cs' and ssex = '女';

3. 排序查询

(1)查询STUDENT表的所有字段和记录按年龄由大到小排序。

select * from student order by sage;

(2)查询STUDENT表的所有字段和记录按年龄由小到大排序。

select * from student order by sage desc;

4. 结合统计函数

(1) 查询SC表的最高分。

select max(grade) from sc;

(2) 统计SC表95001学生的平均分。

select avg(sc.grade) from student join sc on student.sno = sc.sno where student.sno='95001';

(3)统计SC表各课程的最低分。

select min(grade) from sc join course on sc.cno=course.cno where course.cname='数据库';

select min(grade) from sc join course on sc.cno=course.cno where course.cname='数学';

select min(grade) from sc join course on sc.cno=course.cno where course.cname='信息系统';

(4)查询超过平均分的学生姓名和系名。

select sname, sdept from student join sc on student.sno=sc.sno where sc.grade>87;

(5)查询低于平均分的学生姓名、系名和课程名。

select sname, sdept, cname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where sc.grade<87;

5.其他

(1)查询其他系中比信息技术系某一学生小的学生姓名和年龄。

select sname,sage from student where sno not in (select sno from student where sdept='is' or sage >=19);

**由于使用mysql，无except语句，于是用not in代替。

(2) 查询其他系中比计算机系所有学生年龄都小的学生姓名和年龄。

select sname,sage from student where sno not in (select sno from student where sdept='cs' or sage>=20);

(3) 查询每一门课的间接先修课(先修课的先修课)。

select cname from course,sc where course.cpno=sc.cno;

***SQL和mysql语法有些是不一样的，如查询前几条数据的 top 和 limit，mysql中也没有except的用法，用 not in 替代。