问题描述:
sql在access的用法
解析:
SQL是一种结构化查询语言即Structure Query Language
一般格式：
SELECT[DISTINCT]<目标列表达式>…
FROM<表名>，<查询名>……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]
[ORDER BY<列名2>[ASC│DESC]
SELECT SNAME,SDEPT FROM STUDENT
SELECT SNO,CNO FROM 查询1
SELECT * FROM STUDENT
SELECT DISTINCT SNO FROM SC
SELECT SNAME,SAGE FROM STUDENT WHERE SDEPT='CS'
WHERE SAGE NOT BETWEEN 10 AND 18
'',""
BETWEEN #1978-01-01# AND #1980-12-31#
*,?,#
'' ""
IN NOT IN
WHERE SDEPT IN('CS','IS')SDEPT='CS' OR SEPT='IS'
LIKE *,?,#
SELECT * FROM STUDENT WHERE SNAME LIKE '刘?'
SAGE LIKE '1#'
1按学生成绩降序排序，成绩相同按课程号排序
select * from sc order by grade desc,o
2安课程号排序，课程号相同按成绩将需排列
IS NULL,IS NOT NULL
SELECT SNO,CNO FROM SC WHERE GRADE IS NULL
count(*),count(CNO),sum(),avg(),min(),max()
select count(*) from sc
select o,count(*) from sc group by o
and or not
select sname ,2003-sage as 出生年份 from student
SELECT SC.SNO,SNAME,CNO FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO
SELECT C1.CNO,C2.CPNO FROM COURSE C1,COURSE C2 WHERE C1.CPNO=C2.CNO
LEFT JOIN RIGHT JOIN
SELECT course.*, sc.*
FROM course LEFT JOIN sc ON courseo = sco
SELECT SNAME+SDEPT AS SNSD FROM STUDENT
1查询信息系年龄在18到20之间的学生信息
SELECT *
FROM STUDENT
WHERE SDEPT='IS'AND SAGE BETWEEN 18 AND 20
2查询每门课程相应的选课人数
SELECT CNO,COUNT(SNO)
FROM SC
GROUP BY CNO
SELECT COUNT(*) FROM SC
3查询每个学生不及格课程门数
SELECT CNO,COUNT(CNO)
FROM SC
WHERE GRADE<60
GROUP BY SNO
4查询成绩在90到100份之间的每门课程的选修人数
SELECT CNO, COUNT( SNO)
FROM SC
WHERE GRADE BETWEEN 90 AND 100
GROUP BY CNO
5查询姓名第二个字是“力”的学生姓名和所在系
SELECT SNAME,SDEPT
FROM STUDENT
WHERE SNAME LIKE "?力*"
6查询没有成绩的学生学号和对应的课程号
SELECT SNO,CNO
FROM SC
WHERE GRADE IS NULL
7查询数学系和信息系所有男学生的情况
SELECT *
FROM STUDENT
WHERE SDEPT ="IS" OR SDEPT= "MA" AND SSEX="男"
8查询总成绩大于200分的学生学号
SELECT SNO
FROM SC
GROUP BY SNO HAVING SUM(GRADE)>200
SELECT * FROM SC ORDER BY SNO ,GRADE DESC
1.SELECT ENO,ENAME FROM EMP,WORKS WHERE
CMP.ENO=WORKS.ENO,SEX='男',AGE>50
2.SELECT ENO,ENAME FROM EMP ,WORKS WHERE SALARY>1000 AND EMP.ENO=WORKS.ENO
3.SELECT ENO,ENAME FROM EMP,COMP,WORKS WHERE CNAME="LHGS" AND WORKS.CNO=COMP.CNO AND EMP.ENO=WORKS.ENO
4.SELECT ENO,ENAME FROM EMP,COMP,WORKS WHERE WORKS.CNO=COMP.CNO AND EMP.ENO=WORKS.ENO AND SEX='M'AND CNAME='LHGS' AND SALARY>1000
left join right jion
select * from student left join sc on studnt.sno=sc.sno
#1987-10-01#
1、select distinct sno from sc where grade<60
2 select * from student where sdept in('cs','is')
select * from student where sdept='cs' or sdept='is'
3 select sno,sname,sage from student where sage beeen 10 and 19
>=10 and <=19
like '1#'
4 select sname ,sdept from student where sname like '?力*'
5 select sno from sc where grade is not null
子查询
概念：
分类：
引导谓词：select from where sage>(select sage from student where sdept='cs')
>，=，<
查询和刘晨同在一个系学习的学生
SELECT S1.* FROM STUDENT S1,STUDENT S2 WHERE S2.SNAME='刘晨' AND S1.SDEPT=S2.SDEPT
SELECT *
FROM STUDENT
WHERE SDEPT=
(SELECT SDEPT
FROM STUDENT
WHERE SNAME='刘晨')
select sname from student where sdept=(select sdept from student where sname='刘晨')
in
查询选修了数据库课程的学生的姓名和所在系
SELECT SNAME,SDEPT
FROM STUDENT
WHERE SNO IN
( SELECT SNO
FROM SC
WHERE CNO IN
(SELECT CNO
FROM COURSE
WHERE CNAME='数据库'))
select sname ,sdept
from student
where sno in
(select sno
from sc
where o=
(select o
from course
where ame='数据库'))
select sname ,sdept
from student,sc,course
where student.sno=sc.sno and sco=courseo and ame='数据库'
查询其他系比信息系任一学生年龄小的学生情况
any,all
>any大于子查询结果中的某个值(>min()大于最小值）
>all大于子查询结果中的所有值(>max()大于最大值）
<any小于子查询结果中的某个值(<max()小于最大值）
<all小于子查询结果中的所有值(<min()小于最小值）
SELECT *
FROM STUDENT
WHERE SAGE<(SELECT max(SAGE)
FROM STUDENT WHERE SDEPT='IS')
AND SDEPT<>'IS'
select sname
from student
where sage <any(select sage from student where sdept='is') and sdept <>'is'
*** 查询
并（UNION）、交、差
查询既选修了1号课程又选修了2号课程的学生学号。
SELECT SNO FROM SC WHERE CNO=1 AND SNO IN(SELECT SNO FROM SC WHERE CNO=2)
exists
网络班
查询选修了全部课程的学生姓名
select sname
from student
where sno in
(select sno
from sc
group by sno having count(*)=
(select count(*)
from course) )
使用EXISTS
SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
(SELECT *
FROM COURSE
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SNO=STUDENT.SNO
AND CNO=COURSE.CNO))
查询至少选修了01001选修的全部课程的学生号码
select sno from sc where o in(select o from sc where sno=01001) group by sno having count(*)=(select count(*) from sc where sno=01001)
SELECT SNAME FROM STUDENT
WHERE SNO IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM COURSE WHERE CNAME='数据库'))
更新查询
插入（INSERT INTO）
INSERT INTO STUDENT VALUES(95033,'','IS','男',19)
INSERT INTO C1(CPNO,CNAME)
SELECT CPNO,CNAME FROM COURSE WHERE CNO IN(1,2)
INSERT INTO SC1(SNO,CNO)
SELECT SNO,CNO FROM STUDENT,COURSE
修改(UPDATE)
UPDATE SC SET GRADE=0 WHERE 'IS'=(SELECT SDEPT FROM STUDENT WHERE SNO=SC.SNO )
删除(DELETE)
DELETE FROM C1
DELETE FROM SC WHERE 'IS' =(SELECT SDEPT FROM STUDENT WHERE SNO=SC.SNO)
UPDATE STUDENT SET SNO=95030 WHERE SNO=95004
一、SQL的作用：1、SQL主要用于数据库系列的软件的查询、汇总、写入、删改等方面的操控，具体应用于数据库编程或数据库数据的维护。2、也就是说SQL一般用于数据库编程，以及现有数据库错误排查。所以一般涉及数据库的编程人员或电脑信息化系统维护人员需要对SQL（也就是对数据库）知识进行学习。二、相关知识：1、SQL是结构化查询语言(Structured Query Language)的简称。2、在当前它最主要应用于微软的SQL Server和甲骨文公司的Oracle数据库服务器中对数据的操控。其它的小型数据库、单机数据库也往往使用它作为接口指令语句，比如MYSQL、ACCESS等。3、在大型数据库应用中，一般都会经由程序开发设计人员将特定功能对应的SQL语句嵌入到程序中，需要多条复杂SQL语句时，还会以储存过程等方式集写在数据库服务器中进行调用。用户往往不需要与专业化的SQL语句打交道。
