简单数据库学习-2
用access创建表


对于主键,是无重复的(禁止新数据的录入)
SQL语句
SELECT
基本操作
SELECT * FROM StuClas

SELECT cLassid FROM StuCLas
这里有两个问题,第一个,classid大小写错误,但是仍能识别,且查询列名变为搜索名,表明大小写也错误,但是仍会查询,但是如果两者是不存在的名称(多输少输输错了都不行)
对列、行进行操作
SELECT StuName, 2020-Age AS birth FROM Students
对Age进行了操作且命名(不然会是Expr1001)

SELECT DISTINCT StuID FROM StuClas
由此得知不加DISTINCT
相当于加了ALL

添加限定条件
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

SELECT StuId, Grade FROM StuClas ORDER BY Grade DESC

SELECT * FROM Students ORDER BY Dept, Age DESC
系名升序,年龄降序
集函数

分组统计(GROUP BY 和集函数)
SELECT ClassID, COUNT(*) FROM StuClas GROUP BY ClassID

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

为了查询选了三门课以上的学生,我们需要使用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

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

自身连接

INNER JOIN


就生成了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

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

含谓词的子查询
利用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'