Access中的基础SQL操作

SQL是一种结构化查询语言，集数据定义、数据查询、数据操纵、数据控制功能于一体。

定义	create、drop、alter
查询	select
操纵	inster、update、delete
控制	crant、revote

【实例一：图书管理数据库】

假设我们有一个图书管理的数据库，其中的表关系如下：

一、创建表

create table 读者(借书证号 primary key,姓名 char(4) not null，部门.......) 

二、修改表字段

1、增加表字段

alter table 读者 add 照片 

2、删除表字段

alter table 读者 drop 备注

3、删除表

drop table 读者

三、查询操作

1、查询图书总价

SELECT sum(图书.单价) AS 总价
FROM 图书;

2、查询每个出版社图书的总数量，以降序排序

SELECT 出版社, sum(图书.库存数量)
FROM 图书
GROUP BY 出版社
ORDER BY sum(图书.库存数量) DESC;

3、查询单价最高的2本书的信息

SELECT TOP 2 *
FROM 图书
ORDER BY 单价 DESC;

4、查询以“计算机”开头的图书信息

SELECT 书名, 作者, 出版社
FROM 图书
WHERE 书名 like "单*";

5、查询高于平均单价的图书信息

SELECT 书名
FROM 图书
WHERE 单价>(select avg(单价)from 图书);

6、查询每本图书的借阅次数

SELECT 图书.书名, count(借书登记.书号) AS 人数
FROM 图书, 借书登记
WHERE 图书.书号=借书登记.书号
GROUP BY 图书.书名;

7、查询最受欢迎（借阅次数多）的2本书的书号

SELECT TOP 2 书号, count(书号) AS 次数
FROM 借书登记
GROUP BY 书号
ORDER BY count(书号) DESC;

8、查询最受欢迎的2本书的书名

SELECT TOP 2 图书.书名, count(借书登记.书号) AS 次数
FROM 借书登记, 图书
WHERE 借书登记.书号=图书.书号
GROUP BY 图书.书名
ORDER BY count(借书登记.书号) DESC;

9、查询每本未还图书的借阅信息

SELECT *
FROM 借书登记
WHERE 还书日期 is null;

10、查询计算机系读者的借阅信息

SELECT 借书登记.*
FROM 借书登记, 读者
WHERE 借书登记.借书证号=读者.借书证号
and 读者.部门="计算机";

11、如果借阅时间超过一个月还未还书，将记录中”是否超期”改为true

UPDATE 借书登记 SET 是否超期 = 1
WHERE 还书日期-借书日期>30;

12、查询所有借了书的读者的姓名和部门

SELECT 读者.姓名, 部门
FROM 读者, 借书登记
WHERE 读者.借书证号=借书登记.借书证号;

13、查询图书信息，按图书入库数量降序排列

SELECT *
FROM 图书
ORDER BY 入库数量 DESC;

14、查询图书库存数量最多的图书信息

SELECT TOP 1 *
FROM 图书
ORDER BY 库存数量 DESC;

15、查询没有被借阅的图书信息

SELECT *
FROM 图书 LEFT JOIN 借书登记 ON 图书.书号=借书登记.书号
WHERE 图书.书号 not in(select 书号 from 借书登记);

16、查询2015年1月1号至2015年3月31号期间借书的读者编号

SELECT 借书证号
FROM 借书登记
WHERE 借书日期 between #2015/1/01# and #2015/3/31#;

17、查询2015年1月1号至2015年3月31号期间借书的读者姓名

SELECT 读者.姓名
FROM 读者, 借书登记
WHERE 读者.借书证号=借书登记.借书证号
and 借书登记.借书日期 between #2015/1/01# and #2015/3/31#
GROUP BY 读者.姓名;

【实例二：教学管理数据库】

数据库关系图如下：

1、查询每名学生选课信息，并显示姓名、课程名、考试成绩等字段信息

SELECT 学生.姓名, 课程.课程名称, 选课成绩.考试成绩
FROM 学生 INNER JOIN (课程 INNER JOIN 选课成绩 ON 课程.[课程编号] = 选课成绩.[课程编号]) ON 学生.[学生编号] = 选课成绩.[学生编号];

2、查询每名学生的考试成绩的平均成绩、总成绩、最高成绩、最低成绩以及选课数

SELECT DISTINCTROW 学生.姓名, Sum(选课成绩.考试成绩) AS [考试成绩 之 合计], Avg(选课成绩.考试成绩) AS [考试成绩 之 平均值], Min(选课成绩.考试成绩) AS [考试成绩 之 最小值], Max(选课成绩.考试成绩) AS [考试成绩 之 最大值], Count(学生.姓名) AS 选课数
FROM 学生 INNER JOIN 选课成绩 ON 学生.[学生编号] = 选课成绩.[学生编号]
GROUP BY 学生.姓名;

3、查询学生表中同名的学生记录

SELECT First(学生.姓名) AS [姓名 字段], Count(学生.姓名) AS NumberOfDups
FROM 学生
GROUP BY 学生.姓名
HAVING (((Count(学生.姓名))>1));

4、查询选课表中相同成绩的选课信息

SELECT First(选课成绩.课程编号) AS [课程编号 字段], First(选课成绩.考试成绩) AS [考试成绩 字段], Count(选课成绩.课程编号) AS NumberOfDups
FROM 选课成绩
GROUP BY 选课成绩.课程编号, 选课成绩.考试成绩
HAVING (((Count(选课成绩.课程编号))>1) AND ((Count(选课成绩.考试成绩))>1));

5、查询没有学生选修的课程

SELECT 课程.*
FROM 课程 LEFT JOIN 选课成绩 ON 课程.[课程编号] = 选课成绩.[课程编号]
WHERE (((选课成绩.课程编号) Is Null));

6、查询没有选修课程的学生

SELECT 学生.姓名, 选课成绩.学生编号
FROM 学生 LEFT JOIN 选课成绩 ON 学生.[学生编号] = 选课成绩.[学生编号]
WHERE (((选课成绩.学生编号) Is Null));

7、查询学生表中所有学生的班级、姓名、年龄

SELECT 学生.姓名, 学生.班级, Year(Now())-Year([出生日期]) AS 年龄
FROM 学生;

8、查询选课的学生中没有参加考试的学生，显示“姓名”、“性别”

9、查询考试成绩等于0分的学生，显示“姓名”、“性别”、“考试成绩”

10、查询考试成绩不等于0分的学生，显示“姓名”、“性别”、“考试成绩”

SELECT 选课成绩.考试成绩, 学生.姓名, 学生.性别
FROM 学生 INNER JOIN 选课成绩 ON 学生.学生编号 = 选课成绩.学生编号
WHERE (((选课成绩.考试成绩)>0));

11、查询考试成绩小于60分和考试成绩大于等于90分的女生，显示“姓名”、“考试成绩”

SELECT 选课成绩.考试成绩, 学生.姓名
FROM 学生 INNER JOIN 选课成绩 ON 学生.学生编号 = 选课成绩.学生编号
WHERE (((选课成绩.考试成绩)<=60 Or (选课成绩.考试成绩)>=90) AND ((学生.性别)="女"));

13、查询学生表中不同性别的学生人数

SELECT Count(学生.学生编号) AS 人数统计, 学生.性别
FROM 学生
WHERE (((学生.性别)="女" Or (学生.性别)="男"))
GROUP BY 学生.性别;

14、查询学生表中不同性别的学生每门课程的平均成绩，以交叉表的形式显示

TRANSFORM Avg(选课成绩.总评成绩) AS 总评成绩之平均值
SELECT 学生.性别
FROM 课程 INNER JOIN (学生 INNER JOIN 选课成绩 ON 学生.学生编号 = 选课成绩.学生编号) ON 课程.课程编号 = 选课成绩.课程编号
GROUP BY 学生.性别
PIVOT 课程.课程名称;

15、查询学生表中每个学生的选课情况，要求以交叉表的形式显示每个学生选了哪门课及每个学生总的选课数

TRANSFORM Count(选课成绩.课程编号) AS 课程编号之计数
SELECT 学生.姓名, Count(课程.课程编号) AS 总选课数
FROM 学生 INNER JOIN (课程 INNER JOIN 选课成绩 ON 课程.课程编号 = 选课成绩.课程编号) ON 学生.学生编号 = 选课成绩.学生编号
GROUP BY 学生.姓名
PIVOT 课程.课程名称;

16、建立参数查询，要求根据输入的学生姓名，显示该学生的全部信息

SELECT 学生.姓名, 学生.性别, 课程.课程名称, 选课成绩.总评成绩, 学生.学生编号
FROM 课程 INNER JOIN (学生 INNER JOIN 选课成绩 ON 学生.学生编号 = 选课成绩.学生编号) ON 课程.课程编号 = 选课成绩.课程编号
WHERE (((学生.姓名)=[请输入学生姓名：]));

17、建立更新查询，将“女学生”表中的所有非团员都改为团员

UPDATE 学生 SET 学生.团员否 = "1"