MySQL 一些小知识

1. 关于多表查询

我的理解:由于MySQL多表查询时表之间的连接是笛卡尔积的方式,所以尽量少使用多表查询,如果使用则使用嵌套语句

例:说明: `tb_notice_message` 表数量百万级别以上,别名是wm表1000条左右

1 SELECT COUNT(1) AS TotalNum
2 FROM `tb_notice_message` nm, (SELECT u.`userid` FROM `xiaoxin`.`tb_ios_whitemobile` m INNER JOIN `user_center`.`tb_user` u ON m.`userid` = u.`userid` WHERE u.`identity` = 1) AS wm
3 WHERE nm.`deleted` = 0 AND nm.`state` = 1 AND nm.`sender` <> wm.`userid` AND nm.`sid` = 4001 AND nm.sendtime >= 2014-10-22 AND nm.sendtime < 2014-11-1 AND nm.`noticetype` = 1
View Code

结果:半个小时内没有查询结果(用过inner join,也不行),不知道<>的查询效率如何,还请各位大神指教

不使用多表查询,使用嵌套

1 SELECT COUNT(1) AS TotalNum
2 FROM `tb_notice_message` nm
3 WHERE nm.`deleted` = 0 AND nm.`state` = 1 AND nm.`sender` NOT IN(SELECT u.`userid` FROM `xiaoxin`.`tb_ios_whitemobile` m INNER JOIN `user_center`.`tb_user` u ON m.`userid` = u.`userid` WHERE u.`identity` = 1)
View Code

结果:一般10秒以内出现查询结果

2. 一次插入多条数据的方式(这里很多人都知道就不多说了),和SQL Server,及Oracle比较

1 MySQL:INSERT INTO table VALUES (col1,col2...),(col1,col2...)......
2 SQL Server:INSERT INTO table VALUES SELECT ‘‘,‘‘,‘‘ UNION ALL SELECT ‘‘,‘‘,‘‘.......
3 Oracle:INSERT INTO table SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL

3. update MySQL中不允许使用update select 语句使用其他语句替代

 1 update a inner join (select yy from b) c on a.id =b.id set a.xx = c.yy 

例:

1 UPDATE `tb_user` u INNER JOIN
2  (
3 SELECT se.`studentid`, gg.`guardian` 
4 FROM `tb_user` uu INNER JOIN `tb_guardian` gg ON uu.`userid` = gg.`guardian` JOIN `tb_student_ext` se ON se.`userid` = gg.`child` WHERE uu.`account` = ‘‘ AND uu.`identity` = 3
5 ) AS b
6  ON u.`userid` = b.`guardian` SET u.`account` = b.`studentid`
7 WHERE
8  u.`account` = ‘‘ AND u.`identity` = 3 

4. MySQL去除tab和空格

1 update et_product set name =trim(name);
2 update et_product set name =trim(replace(name,  ,  ));

例:

1 SELECT se.`studentid` AS 学号, REPLACE(REPLACE(u.`name`, ", ‘‘),     , ‘‘) AS 姓名, REPLACE(REPLACE(s.`name`, n, ‘‘),     , ‘‘) AS 学校, a.`name` AS 地区
2 FROM
3  `tb_student_ext` se INNER JOIN `tb_user` u ON u.`userid` = se.`userid`
4  INNER JOIN `tb_school` s ON se.`sid` = s.`sid`
5  INNER JOIN `tb_area` a ON a.`aid` = s.`aid`
6 WHERE
7  u.`deleted` = 0 AND s.`deleted` = 0 AND
8  u.`state` = 0 AND    -- 未激活
9  a.aid = 33

5. GROUP_CONCAT关键字(查询某个字段所有值,使用逗号进行分割)

 1 SELECT GROUP_CONCAT(`ClassId`) cids FROM `tb_xa_class` 

查询结果:

 2804,2904,3004,3104,3204,3206........... 

6.  insert或者update使用单引号(如果字段是字符串类型)

例:

1 StringBuilder sb = new  StringBuilder();
2 sb.Append(@"INSERT INTO tb_school (name ,aid ,stid ,state ,xaschoolid ) VALUES ");
3                 foreach (var t in sqList)
4                 {
5                     sb.AppendFormat("({0},{1},{2},{3},{4}),",
6                         t.name, t.aid, t.stid, t.state, t.xaschoolid);
7                 }
8                 string sql = sb.ToString().TrimEnd(,);

7. 存储过程变量赋值方式

1 DECLARE cnt INT DEFAULT 0;
2 方式一:
3 select count(*) into cnt from test_tbl;
4 方式二:
5 set @cnt = (select count(*) from test_tbl);
6 方式三:
7 select count(*) into @cnt1 from test_tbl;
8 方式四:
9 SELECT @Num:= COUNT(*) FROM `tb_class_student_relation`;
View Code

8. 拼接字符串的函数CONCATCONCAT_WS

CONCAT:
  CONCAT(str1,str2,…)

  返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col)。

CONCAT_WS:

  CONCAT_WS(separator,str1,str2,…)

  CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

注意:CONCAT_WS()不会忽略任何空字符串。(然而会忽略所有的 NULL)

9. MySQL定时备份

资料:http://www.pc6.com/infoview/Article_53398.html

10. MySQL API

下载

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。