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 替代。