- select所查询的结果是放在一个结果集里面的,相当于又建了一张表,把结果放进结果表里面。
- SQL是不区分大小写的。
- 一个主键可能由一个表的一列或多列构成。
- 两个连续的单引号表示字符串里面的一个单引号字符。例如,输入’don’’t’表示don’t。
- 保持字符列尽可能短要胜过为他们留下“增长空间”。短列的排序和分组要比长列快一些。
- SQL的select语法:select column from tables
[join joins]
[where search_condition]
[group by group_columns]
[having search_condition]
[order by sort_columns];
- 使用SELECT *经常是有风险的,因为表中列的个数或顺序一旦更改就有可能引起程序执行失败。同样地,SELECT*将无法被不熟悉表中列的人所理解。与在SELECT字句中给出特定列名的查询相比,SELECT*被跨越网络传递不需要的数据拖住,占用大量资源。
所以使用SELECT*的缺点如下:
①SELECT * 语句取出表中的所有字段,不论该字段的数据对调用的应用程序是否有用,这会对服务器资源造成浪费,甚至会对服务器的性能产生一定的影响。
②如果表的结构在以后发生了改变,那么SELECT * 语句可能会取到不正确的数据甚至是出错。
③执行SELECT * 语句时,SQL Server首先要查找出表中有哪些列,然后才能开始执行SELECT * 语句,这在某些情况会产生性能问题。
④使用SELECT * 语句将不会使用到覆盖索引,不利于查询的性能优化。
⑤在文档角度来看,SELECT * 语句没有列明将要取出哪些字段进行操作,所以也是不推荐的。
- 创建列的别名:
Select column1 [AS] alias1,
Column2 [AS] alias2,
......
columnN [AS] aliasN
From table;
- 如果表有被正确定义的主键,那么因为所有的行都是唯一的,所以SELECT DISTINCT * FROM TABLE;和SELECT * FROM TABLE;将返回相同的结果。
- SQL允许在Order by中指定列的相对位置编号而不是列名。位置编号引用的是结果中的列,而不是原始表中的列。
Select columns from table order by
sort_num1[ASC|DESC],
sort_num2[ASC|DESC],
sort_num3[ASC|DESC],
.....
sort_numN[ASC|DESC];
列如:select name,age from authors order 4 ASC,2 DESC;
假设结果集中有 phone,city,name,age这4列,那么结果就是对age升序,city降序排列。
- 最影响排序的速度,按照重要程度排序如下:
①选择的行的数量
②order by子句中列的数量
③order by子句中列的长度(指的是列的数据类型长度)
- 可以使用别名进行排序。
- 在SQL中<>表示不等于,类似于!=。
- 最好的习惯是只将简单的列引用放在=的左边,而将更加复杂的表达式放在=的右边。
- 一般而言,最快的比较是相等(=),然后是不相等(<,<=,>,>=),最慢的是不相等(<>)。
- 如果在select子句中给列定义了别名,那么不能在where子句中引用它。
- Not表示否定(取反)一个条件,select ..... where Not(age<20);表示查询年龄不小于20的信息。
- And比or有更高的优先级。
- 如果查询条件只包含and操作符,首先放置为真可能性最小的条件,查询将会变快。如果,col1 = ‘A’的可能性比col2 = ‘B’比小,那么 where col1 = ‘A’and col2 = ‘B’比where col2 = ‘B’ and col1 = ‘A’快,因为如果以一个为假,则DBMS()就不去计算第二个表达式了。对于只包含or操作符的查询条件,则按相反的方式操作。
- 分配律:
条件 | 等价于 |
NOT(NOT p) | p |
NOT(p AND q) | (NOT p) or (NOT q) |
NOT(p OR q) | (NOT p) AND (NOT q) |
P AND (q OR r) | (P AND q) OR (P AND r) |
P OR (q AND r) | (p OR q) AND(p OR r) |
- LIKE只对字符串起作用,对日期和数字不起作用。
可以看出通配符是用“!”表示的,并且要放在转义的符号前面。
- 通配符查找是很耗时间的------特别是如果使用以%开头的模式。
- Between和and是包含两端的值的。
- SQL提供了IS NULL来确定给定的值是否为空值。IS NULL可以应用于任意数据类型的列。
- 空值和空串的区别:空值是没有输入的,空串是有输入的。其中空值插入的时候,只能insert tablename(column) values(NULL);
- 算数操作符(+、-、*等)的优先级高于比较操作符(<,=,>等),比较操作符的优先级高于逻辑操作符(NOT、AND、OR)。
- 对于复杂的表达式添加圆括号(即便他们是不必要的)以确保期望的计算顺序,是代码更具可移植性和易懂性,这是一种良好的编程风格。
- 使用||连接串:string1||string2。
可以在SELECT、where和ORDER BY子句中或者任何允许使用表达式的位置使用||。
在mysql中是用CONCAT(str1,str2,...)来连接。
- CAST:把一种类型转换成另外一种类型。CAST(EXPR AS TYPE)。将INT(a)转换成CHAR:CAST(a AS CHAR(10))。
- SUBSTRING函数SUBSTRING(str FROM pos FOR len);str是从中提取字串的源串(包含字符串的列、串字面量或者是返回串的运算或函数的结果),pos 是截取开始位置(从1开始),len是要截取这个字符串的长度,如果省略for length,那么就表示从开始位置截取到末尾。
- TRIM()去空格:TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)。Str是串表达式,LEADING 删除前面的空格,TRAILING删除后面的空格,BOTH同时删除前面和后面的空格。如果不指定,默认为BOTH。
- 使用character_length得到字符串长度:CHARACTER_LENGTH(STR)。
- 使用POSITION查找字串:POSITION(substring in string)。Substring是要查找的串,string是被查找的串。
- 函数EXTRACT()将日期或时间间隔隔离为单一的字段,并以数字形式返回。
输入:EXTRACT(field FROM datetime_or_interval)。Field是Year、Month、Day、HOUR、MINUTE、SECOND、TIMEZONE_HOUR或TIMEZONE_MINUTE。
- CURRENT_DATE返回DATE,CUREENT_TIME返回TIME,CURRENT_TIMESTAMP返回TIMESTAMP.
- CASE用法:CASE comparison_value
When value1 THEN result1
When value2 THEN result2
...
When valueN THEN resultN
[else default_result]
END;
Value1,value2...valueN是表达式。Result1,result2...resultN是在相应的值与表达式comparison_value相匹配时返回的表达式。如果省略else default_result,默认 else null。
- 输入CASE
WHEN condition1 then result1
When condition2 then result2
...
When conditionN then resultN
【else default_result】
END;
Condition1,condition2...conditionN是搜索表达式(搜索条件是一个或者多个表达式,而它又是以and或or连接的复合表达式组成的)。Result1,result2...resultN是当相应的条件值为真时返回的表达式。所有的表达式必须是一个类型或者隐式的转换为一个类型。
- COALESCE检查空值:coalesce(expr1,expr2,expr3);类似于:
Case when expr1 is not null then expr1,
When expr2 is not null then expr2,
Else expr3;
- 函数NULLIF比较两个表达式,如果他们相等则返回空值,如果不相等则返回第一个表达式。NULLIF(expr1,expr2)等价于
Case when expr1 = expr2 THEN NULL
Else expr END;
SUM()和AVG()只对数字数据类型起作用。MIN()和MAX()对字符,数字、日期和时间数据类型起作用。COUNT(EXPR)和COUNT(*)对所有数据类型起作用。
注意:
①聚合函数不能出现在where表达式里面。
②select字句必须全部包含非聚合表达式,或者全部包含聚合表达式。(对于有分组的情况除外)。
③可以在SELECT子句中使用多个聚合表达式。
④不可以嵌套聚合函数。(ORACLE可以在GROUP BY查询中嵌套聚合函数)
⑤可以在子查询中使用聚合表达式。
⑥不可以在聚合表达式中使用子查询。
- 在count(expr)中不统计空值的情况。
- 可以用GROUP BY子句将一个表分隔成逻辑组(类别),并且对每一组进行聚合统计量。
GROUP BY子句主要特点如下:
①GROUP BY子句唯一where之后和ORDER by子句之前。
②分组可以是列名或者派生列。
③输入表中的列无法出现在聚合查询的SELECT子句中,除非他们也被包含在GROUP BY中。也就是说聚合函数和单独的列不能同时出现在SELECT句子中,除非有GROUP BY 包含单独的列。列如:select pub_id(单独的列),title_id,COUNT(*)(聚合函数)from titles group by pub_id,title_id(必须出现group by否则出错,并且group by要把这两列全部列进来);
④如果SELECT子句包含复杂的非聚合表达式,那么GROUP BY表达式必须准确的匹配SELECT表达式。
⑤可以在GROUP BY子句中指定多个分组列来嵌套分组。
⑥如果分组列包含一个空值,在结果中这行会变为一个分组。如果分组列包含多个空值,空值将被放进一个分组中。
⑦可以在包含GROUP BY子句的查询中使用WHERE子句,在分组前消除行。
⑧尽管表的别名被允许作为标识符,但不能在GROUP BY子句中是用列的别名。
- HAVING子句的主要特点如下:
①HAVING子句位于GROUP BY子句之后,ORDER BY子句之前。
②HAVING限制了GROUP BY的行数。
③WHERE查询条件在分组产生以前就被应用,而HAVING是在分组之后。
④除了HAVing可以包含聚合函数之外,HAVING语法与where类似。
⑤HAVING子句可以引用显示在select列表中任意一项。
总结:其实HAVING的作用同where一样,增加了限制条件。
- 别名隐藏了表名。如果为表命名了别名,则必须在所有的限定引用中使用它的别名。
- JOIN和WHERE可以等价查询。
- 在三表联接中,只有一个表可以被用作另一个表和第三个表的桥梁。
- 交叉联结(cross join)结果显示两个表所有可能的组合。
- USING可以替代ON,比如:select * from A inner join B on a.id = b.id可以改写成select * from A inner join B using(id);
- 默认情况下,join等价于inner join。
- 内联结其实就相当于把多个表根据条件联结起来,条件越多,结果的行数越少。所以内联结一直在做减的操作。但是外联结至少返回其中一个表的所有行。
- 总而言之,左外联结引用左表检验所有的行,右外联结引用右表检验所有的行,全外联结检索两个表的所有行。在所有的这些情况中,没有匹配的行用空值填充。
- 外连接:left outer join、right outer join和full outer join主要是用来联结几张表查看这几张表中所有行的数据。
- 自联结:就是自己与自己联结,虽然是两张表,但是都是同一张表。用途:在同一张表里面查找与一条数据相关的信息。比如在信息表里面查找与张三在一个城市的人,这样的查询就是自连接。