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