1 在使用多列作为主键时, 上述条件必须应用到所有列,所有列值的组合必须是唯一的(但单个列的值可以不唯一) 。
2 多条SQL语句必须以分号(; ) 分隔。
3 在选择多个列时, 一定要在列名之间加上逗号
4 DISTINCT关键字作用于所有的列, 不仅仅是跟在其后的那一列
5 返回特定数量的行:各种数据库中的这一SQL实现并不相同,sqlserver是top
6 通常, ORDER BY子句中使用的列将是为显示而选择的列。 但是, 实际上并不一定要这样, 用非检索的列排序数据是完全合法的
7 如果想在多个列上进行降序排序, 必须对每一列指定DESC关键字。
8 select name from tbl where price between 5 and 10;
9 判断字段是否没值:SELECT "UID", "CID" FROM "BASEINFO"."MP_SYS" where NOT "GID" is NULL;10 or :在第一个条件满足时, 不管第二个条件是否满足, 相应的行都将被检索出来 11 在where语句里可以使用圆括号: where (id=1 or id=3) and price=10012 IN取一组由逗号分隔、 括在圆括号中的合法值 13 否定条件用:NOT14 模糊查询用 :like ,子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行 15 %表示任何字符出现任意次数。 下划线的用途与%一样, 但它只匹配单个字符。方括号([]) 匹配指定位置(通配符的位置) 的一个字符。 16 别名 :SELECT "GID" ,"WFID" as "haha" FROM "BASEINFO"."MP_SYS" where NOT "WFID" is NULL;17 算术运算: + - * / ,例如 SELECT "V01"+"V02" AS "RET" FROM "CNYB"18 聚合函数:avg sum max min count 使用COUNT(*)对表中行的数目进行计数, 不管表列中包含的是空值(NULL) 还是非空值。 使用COUNT(column)对特定列中具有值的行进行计数, 忽略NULL值。 19 算术和聚合混用:SELECT min("P0000"+"P0015") FROM "CNYB"20 分组 : group by SELECT SUM("P1100"+"P1130"),"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID"; 除聚集计算语句外, SELECT语句中的每一列都必须在GROUP BY子句中给出
SELECT "P1100" ,"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE AND "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID","P1100"; -执行成功
SELECT "P1100" ,"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE AND "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID"; -执行失败
21 WHERE过滤行, 而HAVING过滤分组。 WHERE在数据分组前进行过滤, HAVING在数据分组后进行过滤
22 排序: order by
23 select 子句顺序: select from where group by having order by
24 作为子查询的SELECT语句只能查询单个列。 企图检索多个列将返回错误。
25 作为嵌套字段使用子查询:
SELECT cust_name,cust_state,
(SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders
FROM Customers ORDER BY cust_name;
26 在引用的列可能出现歧义时, 必须使用完全限定列名(用一个句点分隔表名和列名)
27 等值联结就是内联结
等值联结:SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
内联结: SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
28 左联结:left outer join 全外联结:full outer join
29 组合查询:利用union将多个select 语句的查询结果合并成一个 【UNION中的每个查询必须包含相同的列、 表达式或聚集函数 】
在用UNION组合查询时, 只能使用一条ORDER BY子句, 它必须位于最后一条SELECT语句之后
虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分, 但实际上DBMS将用它来排序所有SELECT语句返回的所有结果。
30 假如想把另一表中的顾客列合并到Customers表中,可以使用insert select
SELECT中列出的每一列对应于Customers表名后所跟的每一列 ,这个例子在INSERT和SELECT语句中使用了相同的列名。 但是, 不一定要求列名匹配。 事实上, DBMS一点儿也不关
心SELECT返回的列名。 它使用的是列的位置, 因此SELECT中的第一列(不管其列名) 将用来填充表列中指定的第一列, 第二列将用来填充表列中指定的第二列, 如此等等
31 从一个表复制到另外一个表:select into 【不管从多少个表中检索数据, 数据都只能插入到一个表中 】
32 删除:DELETE FROM Customers WHERE cust_id = '1000000006';
更新:UPDATE Customers SET cust_email = 'kim@thetoystore.com',name="sdf" WHERE cust_id = '1000000005';
33 DELETE删除整行而不是删除列。 要删除指定的列, 请使用UPDATE语句。
34 在UPDATE或DELETE语句使用WHERE子句前, 应该先用SELECT进行测试, 保证它过滤的是正确的记录, 以防编写的WHERE子句不正确。
35 只有不允许NULL值的列可作为主键
36 NULL值是没有值, 不是空字符串
37 大多数约束是在表定义中定义的
38 主键是唯一的, 而且永不改动,也并能为NULL,每个表只允许一个主键
39 外键是表中的一列, 其值必须列在另一表的主键中。 外键是保证引用完整性的极其重要部分
40 在定义外键后, DBMS不允许删除在另一个表中具有关联行的行。 例如, 不能删除关联订单的顾客。 删除该顾客的唯一方法是首先删除相关的订单
41 取值不多的数据(如州) 不如具有更多可能值的数据(如姓或名),能通过索引得到那么多的好处
=======索引=======
索引匹配的最左原则具体是说,假如索引列分别为A,B,C,顺序也是A,B,C:
那么查询的时候,如果查询【A】【A,B】 【A,B,C】,那么可以通过索引查询
如果查询的时候,采用【A,C】,那么C这个虽然是索引,但是由于中间缺失了B,因此C这个索引是用不到的,只能用到A索引
如果查询的时候,采用【B】 【B,C】 【C】,由于没有用到第一列索引,不是最左前缀,那么后面的索引也是用不到了
如果查询的时候,采用范围查询,并且是最左前缀,也就是第一列索引,那么可以用到索引,但是范围后面的列无法用到索引
因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好
请尽量在InnoDB上采用自增字段做主键。
建立索引的常用技巧
1、最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2、=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
4、索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5、尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可,当然要考虑原有数据和线上使用情况
我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描
使用explain 只需要在原有select 基础上加上explain关键字就可以了,如下:explain select * from servers;
explain各个字段的含义
id : 表示SQL执行的顺序的标识,SQL从大到小的执行
select_type:表示查询中每个select子句的类型
table:显示这一行的数据是关于哪张表的,有时不是真实的表名字
type:表示MySQL在表中找到所需行的方式,又称“访问类型”。常用的类型有:ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
Key:key列显示MySQL实际决定使用的键(索引),如果没有选择索引,键是NULL。
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows:表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,理论上行数越少,查询性能越好
Extra:该列包含MySQL解决查询的详细信息
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的,并非精确值
EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。
mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and msg_id>=6273803462253938690 and from_id != 113487 order by msg_id asc limit 30; +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16 | NULL | 349780 | Using where | +----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
mysql> explain select msg_id from circlemessage_idx_0 where to_id = 113487 and circle_id=10019063 and from_id != 113487 order by msg_id asc limit 30; +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16 | NULL | 30 | Using where | +----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
通过上面两个执行计划可以发现当没有msg_id >= xxx这
个查询条件的时候,检索的rows要少很多,并且两者查询的时候都用到了索引,而且用到的还只是主键索引。那说明索引应该是不合理的,没有发挥最大作用。
分析这个执行计划可以看到,当包含msg_id >= xxx
查询条件的时候,rows有34w多行,这种情况,说明检索太多,要么就是表里面确实有这么大,要么就是索引不合理没有用到索引,大都情况是没用合理用到索引。列中所用到的索引也是PRIMARY,那就可能是(msg_id,to_id)的其中一个,注意我们建立表的时候msg_id索引的顺序是在to_id前面的,因此MySQL查询一定会优先用msg_id索引,在使用了msg_id索引后,就已经检索出了34w行,并且由于msg_id的查询条件是大于等于,因此,再这个查询条件后,就不能再用到to_id的索引。
然后再看key_len长度为16,结合 key为PRIMARY,那么可以分析得知,只有一个主键索引被用到。
最后看看 type 值,是range,那么就说明这个查询要么是范围查询,要么就是多值匹配。
请注意,
from_id != xxx
这样的语句,是无法用到索引的。只有from_id = xxx
就可以用到所以,因此from id 的索引其实可以不用,建立索引的时候就要考虑清楚