SQL学习（四）删、改、查

删：

删除数据库：

DROP DATABASE 数据库名称

删除表：

SQLServer

MYSQL

SQL语句

use chatgpt;
DELETE FROM student;

删除单行数据：

DELETE FROM 表名称 WHERE 列名称 = 值

改：

SQLServer

MYSQL

SQL语句

修改单列：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

 修改多列：

        用逗号隔开即可

update student set ssex = '男', sdept = '文学院'
where sno = '0007'

查：

基本的SQL查询语句：

SELECT <table fields list>
FROM <table names list>
WHERE <row constraints specification>
GROUP BY <grouping specification>
HAVING <grouping selection specification>
ORDER BY <order rules specification>

操作符 BETWEEN ... AND

  会选取介于两个值之间的数据范围

select *
from FactCallCenter
where DateKey between '20101105' and '20101107'

 Avg、Min、Max、Sum

  Avg平均值，Min最小值，Max最大值，Sum求和

select
Avg(LevelOneOperators) as AverageLevelOneOperators
,Min(LevelOneOperators) as MinimumLevelOneOperators
,Max(LevelOneOperators) as MaximumLevelOneOperators
,Sum(LevelOneOperators) as SummaryLevelOneOperators
from FactCallCenter

group by & having 的作用

  group by Shift表示对不同的Shift值，分别求LevelOneOperators的最大值

  having Max(LevelOneOperators) > 2 进一步对结果进行筛选

select Shift, Max(LevelOneOperators) as LevelOneOperators最大值
from FactCallCenter
where Shift is not null
group by Shift
having Max(LevelOneOperators) > 2