mysql 学习笔记

Wesley13
• 阅读 666
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  
mysql 学习笔记

 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 的索引其实可以不用,建立索引的时候就要考虑清楚

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这