MySQL 一些小知识

1. 关于多表查询

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

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

SELECT COUNT(1) AS TotalNum
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
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

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

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

SELECT COUNT(1) AS TotalNum
FROM `tb_notice_message` nm
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)

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

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

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

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

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

例：

UPDATE `tb_user` u INNER JOIN
 (
SELECT se.`studentid`, gg.`guardian` 
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
) AS b
 ON u.`userid` = b.`guardian` SET u.`account` = b.`studentid`
WHERE
 u.`account` = '' AND u.`identity` = 3 

4. MySQL去除tab和空格

update et_product set name =trim(name);
update et_product set name =trim(replace(name, ' ', ' '));

例：

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

5. GROUP_CONCAT关键字（查询某个字段所有值，使用逗号进行分割）

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

查询结果：

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

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

例：

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

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

DECLARE cnt INT DEFAULT 0;
方式一：
select count(*) into cnt from test_tbl;
方式二：
set @cnt = (select count(*) from test_tbl);
方式三：
select count(*) into @cnt1 from test_tbl;
方式四：
SELECT @Num:= COUNT(*) FROM `tb_class_student_relation`;

8. 拼接字符串的函数CONCAT和CONCAT_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

下载