SQL允许基于表中的数据计算值。可以使用算术公式,也可以使用SQL内置函数。
内置函数有,COUNT SUM MIN MAX AVG
SELECT MIN(MaxHours) AS MinmumMaxHours //最小值
MAX(MaxHours) ASMaxmumMaxHours // 最大值
SUM(MaxHours) ASTotalHours //总和
COUNT(Department) AS NumberOfDepartment // 数量
SELECT ProjectID,ProjectName,MaxHours,
(18.50*MaxHours)AS MaxProjectCost
列名不能和内置函数一起使用。
内置函数一般不能用于WHERE子句。
在SQL中,GROUP BY子句可以按公共值给行分组。
DELECT Department,count(*) AS NumberOfEmployee
FROM EMPLOYEE
GROUP BY Department;
按照Department统计总数
DELECT Department,count(*) AS NumberOfEmployee
FROM EMPLOYEE
GROUP BY Department;
HAVING COUNT(*)>1按照Department统计总数,只显示总数大于1的行
使用GROUP BY还可以添加WHERE子句,但容易产生模棱两可的情况。SQL规定,同时出现WHERE和GROUPBY时,首先运行WHERE条件。
使用子查询处理多个表
SELECT FirstName,LastName
FROM EMPLOYEE
WHERE EmployeeNumberIN //结果3:从EMPLOYEE中查找出EmployeeNumber在结果2中的行
(
SELECT DISTINCT EmployeeNumber
FROM ASSIGNMENT
WHERE HoursWorked >40 //结果2:从ASSIGNMENT中查出HoursWorked >40并且ProjectID在结果1中
AND ProjectID IN
(
SELECT ProjectID
FROM PROJECT
WHERE Department =‘Accounting’ // 结果 1:从PROJECT表中查出Department为Accounting的行
)
)
使用连接查询多个表
连接的基本思想就是连接两个或者多个表的内容,建立一个新表。
SELECT ProjectName,FirstName,LastName,HoursWorked
FROM EMPLOYEEAS E,PROJECTAS P,ASSIGNMENT AS A
WHERE E.EmployeeNumber = A.EmployeeNumber
AND P.ProjectID = A,ProjectID
ORDER BY P.ProjectID ,A.EmployeeNumber ;
查询结果以ProjectName,FirstName,LastName,HoursWorked展示,查找出EMPLOYEE中EmployeeNumber 和ASSIGNMENT中EmployeeNumber 相同的行,查找出EMPLOYEE中ProjectID 和ASSIGNMENT 中ProjectID 相同的行,并且先按照ProjectID 排序,相同的ProjectID 的行按照EmployeeNumber 排序
SQL JOIN...ON
SELECT ProjectName,FirstName,LastName,HoursWorked//查询结果显示这三项值
FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A
ON E.EmployeeNumber = A.EmployeeNumber //EMPLOYEE 和ASSIGNMENT 联合查询EmployeeNumber 相等
JOIN PROJECT AS P
ON A.ProjectID = P.ProjectID //联合PROJECT查询ProjectID 相同的行
ORDER BY P.ProjectID,A.EmployeeNumber; //查询结果先按照ProjectID 排序,相同的ProjectID 的行按照EmployeeNumber 排序
外连接
LEFT和RIGHT
SELECT ProjectName,FirstName,LastName,HoursWorked
FROM PROJECTLEFT JOINASSIGNMENT
ON PROJECT.ProjectID = ASSIGNMENT.ProjectID
这个链接的目的是把PROJECT表中的行追加到ASSIGNMENT表的相应行中,如果FROM子句左边的表(PROJECT)中的某行在ASSIGNMENT中没有匹配的行,它会添加到结果集里。
修改和删除数据
UPDATE...SET修改已有数据的值
UPDATE EMPLOYEE
SET Department = ‘Finance’,Phone='36986886655',
WHERE EmployeeNumber = 5
修改EmployeeNumber 为5的行Department 修改为Finance,Phone为36986886655。
DELETE
FROM EMPLOYEE
WHERE EmployeeNumber = 5
如果这行被别的表依赖,删除的时候会失败,必须先重新分配或者删除其他表中关联的行。
DROP TBALE
删除表结构以及所有数据。如果表包含或可以包含需要实施参照完整性约束的值,则此语句失败。
ALTER TABLE
用于添加、修改、删除列和约束
ALTER TABLE ASSIGNMENT
DROP CONSTRAINT ASSIGN_EMP_PK
删除ASSIGNMENT表或ASSIGN_EMP_PK外键结束。
ALTER TABLE PROJECT
ADD COUSRAINT PROJECT_Check_Dates
CHECK(StartDate < EndDate);
修改PROJECT表,添加所需的约束。
ALTER TABLE PROJECT
ADD CurrentTotalHours Numeric(8,2) NULL;
向表PROJECT中添加一列CurrentTotalHours
UPDATE PROJECT SET CurrentTotalHours =0;
ALTER TABLE PROJECT
ALTER COLUMN CurrentTotalHours Numeric(8,2) NOT NULL;
在已有行的CurrentTotalHours 中放入数据后将其改成NOT NULL
ALTER TABLE PROJECT
ALTER COLUMN CurrentTotalHours;
从PROJECT表中删除CurrentTotalHours列