用access创建表

image-20200315144420364 image-20200315144549680

对于主键,是无重复的(禁止新数据的录入)

SQL语句

SELECT

基本操作

SELECT * FROM StuClas

image-20200315174938285

SELECT cLassid FROM StuCLas

这里有两个问题,第一个,classid大小写错误,但是仍能识别,且查询列名变为搜索名,表明大小写也错误,但是仍会查询,但是如果两者是不存在的名称(多输少输输错了都不行)

image-20200315175058195

对列、行进行操作

SELECT StuName, 2020-Age AS birth FROM Students

对Age进行了操作且命名(不然会是Expr1001)

image-20200315175648942

SELECT DISTINCT StuID FROM StuClas

由此得知不加DISTINCT相当于加了ALL

image-20200315180154171

添加限定条件

SELECT DISTINCT StuName FROM Students WHERE StuID='1001'

精确查找,不等于用<>

SELECT StuId, Grade FROM StuClas WHERE Grade IS NULL

空数据查询

SELECT DISTINCT StuID FROM StuClas WHERE Grade<60

查找挂科的学生

SELECT StuName FROM Students WHERE age BETWEEN 16 AND 18

SELECT DISTINCT StuID FROM StuClas WHERE Grade NOT BETWEEN 70 AND 80

范围选择

SELECT StuName, Sex FROM Students WHERE Dept IN ('生物', ''计算机')

SELECT StuName, Sex FROM Students WHERE Dept NOT IN ('生物', ''计算机')

范围筛选

SELECT StuID, StuName FROM Students WHERE StuName LIKE '刘*'

SELECT StuID, StuName FROM Students WHERE StuName LIKE '刘??'

正则

多条件

AND or

排序

SELECT StuId, Grade FROM StuClas ORDER BY Grade

默认升序ASC

image-20200315182548649

SELECT StuId, Grade FROM StuClas ORDER BY Grade DESC

image-20200315182635482

SELECT * FROM Students ORDER BY Dept, Age DESC

系名升序,年龄降序

集函数

image-20200315183615574

分组统计(GROUP BY 和集函数)

SELECT ClassID, COUNT(*) FROM StuClas GROUP BY ClassID

image-20200315185427170

说明一下COUNT函数这里的作用,不使用GROUP的时候,COUNT(*)返回总元组个数(且只能有COUNT函数作为列),有的时候返回分组(按照重复的名称)的个数。

SELECT ClassID, AVG(Grade) FROM StuClas GROUP BY ClassID

image-20200315185847378

为了查询选了三门课以上的学生,我们需要使用HAVING语句(常与GROUP BY搭配

SELECT StuID, COUNT(*) FROM StuClas GROUP BY StuID HAVING COUNT(*) >= 3

连接查询(笛卡尔积加筛选)

SELECT Students.StuID, Students.StuName, Students.Sex, Grade FROM StuClas, Students WHERE StuClas.StuID = Students.StuID

image-20200315195319910

SELECT Students.StuID, StuName, ClassName, Credit, Grade FROM Students, StuClas, Classes WHERE Students.StuID = StuClas.StuID AND StuClas.ClassID = Classes.ClassID

image-20200315200155809

自身连接

image-20200315200607425

INNER JOIN

image-20200316011056581 image-20200316011133938

就生成了SQL语句

SELECT * FROM Students INNER JOIN StuClas ON Students.StuId = StuClas.StuID

LEFT JOIN / RIGHT JOIN

对于INNER JOIN要求两表的主键取交集,而LEFT JOIN指取左边表的全部,右边表的主键如果为子集则补集留空,RIGHT JOIN同理

SELECT * FROM Students LEFT JOIN StuClas ON Students.StuId = StuClas.StuID

image-20200316011648295

嵌套查询

``SELECT StuID, StuName FROM Students WHERE StuID IN (SELECT StuID FROM StuClas WHERE ClassID=‘C002’)`

image-20200316013549046

含谓词的子查询

利用ANY ALL EXIST关键字进行查询

SELECT StuName, Age From Students WHERE Dept <> '生物' AND Age < ANY (SELECT Age FROM Students WHERE Dept = '生物')

集合查询

UNION INTERSECT EXCEPT交并差

SELECT StuID FROM StuClas WHERE ClassID = 'C001' UNION SELECT StuID FROM StuClas WHERE ClassID = 'C002'

其他语句

INSERT

INSERT INTO Stus VALUES ('3001', '陈东', '男', 18, '生物')

UPDATE

UPDATE Stus SET Age = 20 WHERE StuID = '3001'

DELETE

DELETE FROM Stus WHERE StuID='3001'