SQL经验谈

素颜马尾好姑娘i 2022-06-17 00:36 317阅读 0赞

、执行SQL  
  我们可以通过三种方式执行SQL：??在工具中执行。各个DBMS 几乎都提供了工具用于执行SQL 语句，比如Microso SQLServer的Management Studio、DB2 的命令中心、Oracle的SqlPlus或者MySQL的QueryBrowser。在这些工具中我们只要输入要执行的SQL然后点击【执行】按钮就可以得到执行结果。  
  
二、索引  
  虽然索引可以提高数据查询的速度，但索引占据一定的磁盘空间，会减慢数据的插入和删除，因为每次插入和删除都需要更新索引。  
  
三、主键  
   主键是在同一张表中必须是唯一的，如果在进行数据更新的时候指定的主键与表中已有的数据重复的话则会导致违反主键约束的异常。  
  
四、外键  
  外键是指向另一个表中已有数据的约束，因此外键值必须是在目标表中存在的。如果插入的数据在目标表中不存在的话则会导致违反外键约束异常。  
  由于T\_Debt表中FPerson字段是指向表T\_Person的FName字段的外键，所以必须首先删除T\_Debt表中的数据后才能删除T\_Person中的数据。  
  
五、集合匹配  
  集合匹配只在MSSQLServer 上提供支持，在MYSQL、Oracle、DB2 等数据库中不支持，必须采用变通的手段来实现。  
SELECT \* FROM T\_Employee WHERE NOT(FName LIKE 'S%') AND NOT(FName LIKE  
'J%')进行集合匹配的通配符为“\[\]”，方括号中包含一个字符集，它匹配与字符集中任意一个字符相匹配的字符。比如通配符表达式“\[bt\]%”匹配第一个字符为b或者t、长度不限的字符串，“bed”、“token”、“t”都能匹配这个表达式，而“at”、“lab”、“lot”等则不能匹配这个表达式。还可以使用否定符“^”来对集合取反，它匹配不与字符集中任意一个字符相匹配的字符。比如通配符表达式“\[^bt\]%”匹配第一个字符不为b或者t、长度不限的字符串，“at”、“lab”、“lot”都能匹配这个表达式，而 “bed”、“token”、“t”等则不能匹配这个表达式。  
  通配符过滤一个非常强大的功能，不过在使用通配符过滤进行检索的时候， **数据库系统会对全表进行扫描**，所以执行速度非常慢。因此不要过分使用通配符过滤，在使用其他方式可以实现的效果的时候就应该避免使用通配符过滤。  
  
六、4.2.2 空值检测  
SELECT \* FROM T\_Employee  
WHERE FNAME=null这个语句是可以执行的，不过执行以后我们看不到任何的执行结果，那个Fnumber为“IT002”的数据行中Fname字段为空，但是没有被查询出来。这是因为在SQL语句中对空值的处理有些特别，不能使用普通的等于运算符进行判断，而要使用IS NULL关键字，使用方法为“待检测字段名IS NULL”，比如要查询所有姓名未知的员工信息，则运行下面的SQL语句：SELECT \* FROM T\_Employee WHERE FNAME IS NULL 。  
  
七、4.2.3反义运算符  
  “=”、“<”、“>”等运算符都是用来进行数值判断的，有的时候则会想使用这些运算符的反义，比如“不等于”、“不小于”或者“不大于”，MSSQLServer、DB2提供了“!”运算符来对运算符求反义，也就是“!=”表示“不等于”、“!<”表示“不小于”，而“!>”表示“不大于”。“!”运算符能够把“不等于”、“不大于”、“不小于”这样的语义直接翻译成SQL运算符，不过这个运算符只在MSSQLServer和DB2两种数据库系统上提供支持，如果在其他数据库系统上则可以用其他的变通的方式实现，最常用的变通实现方式有两种：使用同义运算符、使用NOT运算符。否定的语义都有对应的同义运算符，比如“不大于”的同义词是“小于等于”、而“不小于”的同义词是“大于等于”，同时SQL提供了通用的表示“不等于”的运算符“<>”。  
  
八、between and  
  数据库系统对“BETTWEEN AND”进行了查询优化，使用它进行范围值检测将会得到比其他方式更好的性能，因此在进行范围值检测的时候应该优先使用“BETTWEEN AND”。需要注意的就是“BETTWEEN AND”在进行检测的时候是包括了范围的边界值的（也就是闭区间），如果需要进行开区间或者半开半闭区间的范围值检测的话就必须使用其他的解决方案了——and 去除边界。  
  
九、4.2.6 低效的“WHERE 1=1”  
  因为使用添加了“1=1”的过滤条件以后数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（也就是全表扫描）以比较此行是否满足过滤条件，当表中数据量比较大的时候查询速度会非常慢。因此如果数据检索对性能有比较高的要求就不要使用这种“简便”的方式。  
  
十、4.3.1 数据分组  
  需要分组的所有列都必须位于GROUP BY子句的列名列表中，也就是没有出现在GROUP BY子句中的列（聚合函数除外）是不能放到SELECT语句后的列名列表中的。比如下面的SQL语句是错误的：SELECT FAge,FSalary FROM  
T\_Employee GROUP BY  
FAge道理非常简单，因为采用分组以后的查询结果集是以分组形式提供的，由于每组中人员的员工工资都不一样，所以就不存在能够统一代表本组工资水平的FSalary字段了，所以上面的SQL语句是错误的。不过每组中员工的平均工资却是能够代表本组统一工资水平的，所以可以对FSalary使用聚合函数，下面的SQL语句是正确的：SELECT FAge,AVG(FSalary) FROM T\_Employee GROUP BY FAge  
  
十一、4.3.2数据分组与聚合函数  
  这是因为聚合函数不能在WHERE语句中使用，必须使用HAVING子句来代替SELECT FAge,COUNT(\*) AS CountOfThisAge  
FROM T\_Employee GROUP BY FAge HAVING COUNT(\*) IN (1,3)  
  
十二、4.4 限制结果集行数  
MYSQL  
  SELECT \* FROM T\_Employee ORDER BY  
FSalary DESC LIMIT 2,5; 行号从0开始。从行号为2开始返回5条记录。MSSQLServer2000  
  比如要实现检索按照工资从高到低排序检索从第六名开始一共三个人的信息，那么就可以首先将前五名的主键取出来，在检索的时候检索排除了这五名员工的前三个人，SQL如下：SELECT top 3 \* FROM T\_EmployeeWHERE FNumber NOT IN(SELECT TOP 5 FNumber FROM T\_Employee  
ORDER BY FSalary DESC)ORDER BY FSalary DESC  
MSSQLServer2005  
   也就是说ROW\_NUMBER()不能用在WHERE语句中。我们可以用子查询来解决这个问题，下面的SQL语句用来返回第3行到第5行的数据：SELECT \*  
FROM(SELECT ROW\_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,FNumber,FName,FSalary,FAge FROM T\_Employee) AS a  WHERE  
a.rownum>=3 AND a.rownum<=5；  
4.4.4Oracle  
  Oracle中定义表别名的时候不能使用AS关键字，所以这里也去掉了ASOracle支持标准的函数ROW\_NUMBER()，不过Oracle中提供了更方便的特性用来计算行号，也就在Oracle中可以无需自行计算行号，Oracle为每个结果集都增加了一个默认的表示行号的列，这个列的名称为rownum。按工资从高到底排序的前6名员工的信息：SELECT \* FROM T\_EmployeeWHERE rownum<=6ORDER BY FSalary Desc  
但：SELECT  
rownum,FNumber,FName,FSalary,FAge FROM T\_EmployeeWHERE rownum BETWEEN 3 AND 5 ORDER BY FSalary  
DESC当进行检索的时候，对于第一条数据，其rownum为1，因为不符合“WHERE rownum BETWEEN 3 AND 5”，所以没有被放到了检索结果中；当检索到第二条数据的时候，因为第一条数据没有放到结果集中，所以第二条数据的rownum仍然为1，而不是我们想像的2，所以因为不符合“WHERE rownum<=6”，没有被放到了检索结果中；当检索到第三条数据的时候，因为第一、二条数据没有放到结果集中，所以第三条数据的rownum仍然  
为1，而不是我们想像的3，所以因为不符合“WHERE rownum<=6”，没有被放到了检索结果中……依此类推，这样所有的数据行都没有被放到结果集中。因此如果要使用rownum来实现“按照工资从高到低的顺序取出第三个到第五个员工信息”的功能，就必须借助于窗口函数ROW\_NUMBER()。  
4.4.5DB2  
  DB2中支持窗口函数ROW\_NUMBER()，DB2还提供了FETCH关键字用来提取结果集的前N行SELECT \* FROM T\_EmployeeORDER BY FSalary  
DescFETCH FIRST 6 ROWS ONLY需要注意的是FETCH子句要放到ORDER BY语句的后面.可以首先将前五名的主键取出来，在检索的时候检索排除了这五名员工的前三个人，SQL如下：SELECT \* FROM  
T\_EmployeeWHERE FNumber NOT IN(SELECT FNumber FROM T\_EmployeeORDER BY FSalary DESCFETCH FIRST 5 ROWS ONLY)ORDER BY FSalary  
DESCFETCH FIRST 3 ROWS ONLY  
  
十三、distinct  
  DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每一个列。  
  
十四、4.6.1 常量字段  
这里的'CowNew集团'和918000000并不是一个实际的存在的列，但是在查询出来的数据中它们看起来是一个实际存在的字段，这样的字段被称  
为“常量字段”（也称为“常量值”）。  
  
十五、Substring  
  主流数据库系统都提供了计算字符串长度的函数，在MYSQL、Oracle、DB2中这个函数名称为LENGTH，而在MSSQLServer中这个函数的名称则为LEN。这个函数接受一个字符串类型的字段值做为参数，返回值为这个字符串的长度，在MYSQL、MSSQLServer中这个函数名称为SUBSTRING，而在Oracle、DB2这个函数名称为SUBSTR。  
  
十六、并接字符串  
MYSQL会尝试将加号两端的字段值尝试转换为数字类型，如果转换失败则认为字段值为0，比如我们执行下面的SQL语句：SELECT  
'abc'+'123',FAge+'a' FROM T\_Employee'abc'+'123' FAge+'a'123 25在MYSQL中进行字符串的拼接要使用CONCAT函数,CONCAT函数会将所有参数按照参数的顺序拼接成一个字符串做为返回值CONCAT支持只有一个参数的用法，这时的CONCAT可以看作是一个将这个参数值尝试转化为字符串类型值的函数。MYSQL中还提供了另外一个进行字符串拼接的函数CONCAT\_WS，CONCAT\_WS可以在待拼接的字符串之间加入指定的分隔符，它的第一个参数值为采用的分隔符，而剩下的参数则为待拼接的字符串值，比如执行下面的SQL：SELECT  
CONCAT\_WS(',',FNumber,FAge,FDepartment,FSalary) FROM  
T\_Employee执行完毕我们就能在输出结果中看到下面的执行结果：CONCAT\_WS(',',FNumber,FAge,FDepartment,FSalary)4.6.4.2 MSSQLServer  
与MYSQL不同，MSSQLServer中可以直接使用加号“+”来拼接字符串。4.6.4.3 Oracle  
Oracle中使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。比如执行下面的SQL语句：SELECT  
'工号为'||FNumber||'的员工姓名为'||FName FROM T\_EmployeeWHERE FName IS NOT NULL  
除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接，比如执行下面的SQL语句：SELECT CONCAT('工号:',FNumber) FROM  
T\_Employee与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数，不支持两个以上字符串的拼接。  
4.6.4.4 DB2  
DB2中使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样与oracle不同，如果CONCAT中连接的值不是字符串，则DB2不会尝试进行类型转换而是报出错误信息，比如执行下面的SQL语句是错误的：SELECT CONCAT('年龄:',FAge) FROM  
T\_Employee运行以后DB2会报出下面的错误信息：未找到类型为 "FUNCTION" 命名为 "CONCAT"  
且具有兼容自变量的已授权例程与MYSQL的CONCAT()函数不同，DB2的CONCAT()函数只支持两个参数。  
  
十七、4.7 不从实体表中取的数据  
MYSQL和MSSQLServer允许使用不带FROM子句的SELECT语句来查询这些不属于任何实体表的数据;还可以在不带FROM子句的SELECT语句中使用函数；  
SELECT 1  
SELECT LENGTH('abc')  
SELECT 1,2,3,'a','b','c'  
  在Oracle和DB2是不允许使用这种不带FROM子句的SELECT语句，不过我们可以使用Oracle的系统表来作为FROM子句中的表名。  
  
十八、4.8 联合结果集  
  有的时候我们需要组合两个完全不同的查询结果集，而这两个查询结果之间没有必然的联系，只是我们需要将他们显示在一个结果集中而已。  
  在SQL中可以使用UNION运算符来将两个或者多个查询结果集联合为一个结果集中。联合结果集不必受被联合的多个结果集之间的关系限制，不过使用UNION仍然有两个基本的原则需要遵守：一是每个结果集必须有相同的列数；二是每个结果集的列必须类型相容。可以看到MYSQL将FAge转换为了文本类型，以便于与FName字段值匹配。不过这句SQL语句在MSSQLServer、Oracle、DB2中执行则会报出类似如下的错误信息：表达式必须具有与对应表达式相同的数据类型。这时因为默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在UNION运算符后使用ALL操作符，比如下面的SQL语句：SELECT  
FName,FAge FROM T\_EmployeeUNION ALLSELECT FName,FAge FROM T\_TempEmployee  
例子：正式员工工资报表  
SELECT FNumber,FSalary FROM  
T\_Employee UNION SELECT '工资合计',SUM(FSalary) FROM T\_Employee