数据库基础知识点

1.数据库中运算符

等于：=
大于：>
小于：<
大于或等于：>=
小于或等于：<=
不等于：<>

2.SQL语句

1.插入：INSERT

插入一行eg：

INSERT INTO Students(SName,SAddress,SSex)
VALUES('张晴',default,'女');

插入多行eg：

INSERT Students(SName,SGrade)
SELECT  '张可',7  UNION
SELECT  '李洋',4  UNION
SELECT  '杨晓',2;

2.更新：UPDATE

eg：

UPDATE Score    //(表名)
SET Scores=Scores+5
WHERE Scores<60;

3.删除DELETE、DROP COLUMN

删除数据行eg：

DELETE FROM Students
WHERE Scode='13121187';

删除数据列eg：

ALTER Students DROP COLUMN SSex     //删除性别属性列

4.查询(一)

1.eg：

SELECT * FROM Students
WHERE SSex='男'
ORDER by Scode;     //若想要倒序则ORDER by Scode DESC;

2.数据查询（根据列名）

1).使用As来重命名列

eg：

SELECT Scode As 学号，SName As 姓名
FROM Students
WHERE SAddress <> '奥运村';

2).使用=来重命名列

eg：

SELECT '姓名'=FirstName+'.'+LastName
FROM Employee
...;

3.查询空行

eg:

SELECT SName FROM Students WHERE SEmail IS NULL;//不能改为=NULL

4.使用常量列

eg：

SELECT 姓名=SName,地址=SAddress,'奥运村' As 学校//附加一列学校，其值全为奥运村

5.限制固定行数

eg：

SELECT TOP 3 SName,SAddress
FROM Students
WHERE SSex = '男'
ORDER BY Scode DESC;

5.查询（二）

1.模糊查找

1).IS NULL

eg：

SELECT SName,SAddress From Students
WHERE SAddress IS NULL;

2).BETWEEN

eg:

SELECT Scode,Java FROM Score
WHERE Java BETWEEN 60 AND 80;

3).IN

eg：

SELECT SName,SAddress FROM Students
WHERE SAddress IN ('北京','广州','上海');

2.聚合函数

1).SUM—总和

eg:

SELECT SUM(Java) FROM Scores
WHERE Java BETWEEN 70 AND 90;

2).AVG—平均数

eg:

SELECT AVG(Java) As Java平均成绩 FROM Scores
WHERE Java BETWEEN 70 AND 90;

3).MAX/MIN—最大、最小值

eg:

SELECT MAX(C) As C最高分,MIN(C) As C最低分
FROM Scores
WHERE C>=60;

4).COUNT—计数

eg:

SELECT COUNT(*) As C及格人数
FROM Scores
WHERE C >=60;

3.分组查询—GROUP BY

eg:
原始表：

SELECT 类别, SUM(数量) As 数量之和
FROM A
GROUP BY 类别

执行结果：

4.WHERE 和 HAVING

WHERE:去除不符合其搜索条件的数据
HAVING:去除不符合其组搜索条件的各组数据行
eg:

SELECT  部门编号,   COUNT(*)
FROM    员工信息表
WHERE   工资>=2000
GROUP BY 部门编号
HAVING   COUNT(*)>1;

5.多表联结—内联结

两个表eg：

SELECT S.SName,C.Java
FROM Scodes As C        //可与第三行调换顺序
INNER JOIN Students As S
ON C.StuID=S.Scode

上述代码等价于：

SELECT S.SName,C.Java
FROM Scodes As C        
INNER JOIN Students As S
WHERE C.StuID=S.Scode

三表联结：

......INNER JOIN A ON(......)
      INNER JOIN B ON(......)

5左外联结

eg：

SELECT S.SName,C.Java   //S中的SName全罗列出来，即使没有成绩
FROM Students As S      
LEFT JOIN Scodes As C
ON C.StuID=S.Scode

等价于：

SELECT S.SName,C.Java   
FROM Scodes As C        
RIGHT JOIN Students As S
ON C.StuID=S.Scode