MySQL管理与优化(13):常用SQL技巧和常见问题

原创
2014/07/04 08:19
阅读数 168

常用SQL技巧和常见问题

正则表达式的使用:

  • "^":匹配开始字符,匹配返回1,反之返回0:
mysql> SELECT 'abcdefg' REGEXP '^a';
+-----------------------+
| 'abcdefg' REGEXP '^a' |
+-----------------------+
|                     1 |
+-----------------------+
  • "$":匹配结尾字符,匹配返回1,反之返回0:
mysql> SELECT 'abcdefg' REGEXP 'g$';
+-----------------------+
| 'abcdefg' REGEXP 'g$' |
+-----------------------+
|                     1 |
+-----------------------+
  • 其他正则表达式使用可参考正则表达式的使用。

巧用RAND()提取随机行:

  • 比如在随机抽样统计中,可以这样使用:
mysql> SELECT * FROM users ORDER BY rand() LIMIT 2;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | XiaoMing | 123456   |
|  3 | XiaoLi   | 111111   |
+----+----------+----------+

利用GROUP BY的WITH ROLLUP子句做统计:

范例:

# 建表
mysql> CREATE TABLE sales(
    -> year INT NOT NULL,
    -> country VARCHAR(20) NOT NULL,
    -> product VARCHAR(32) NOT NULL,
    -> profit int);
Query OK, 0 rows affected (0.18 sec)

# 插入一些数据
mysql> SELECT * FROM sales;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2004 | china   | tnt1    |   2001 |
| 2004 | china   | tnt2    |   2002 |
| 2004 | china   | tnt3    |   2003 |
| 2005 | china   | tnt1    |   2005 |
| 2005 | china   | tnt2    |   2004 |
| 2005 | china   | tnt3    |   2006 |
| 2005 | china   | tnt2    |   2008 |
| 2005 | china   | tnt3    |   2009 |
| 2005 | china   | tnt1    |   2007 |
| 2006 | china   | tnt1    |   2010 |
| 2006 | china   | tnt2    |   2011 |
| 2006 | china   | tnt3    |   2012 |
+------+---------+---------+--------+
12 rows in set (0.00 sec)

--不带WITH ROLLUP进行GROUP
mysql> SELECT year, country, product, SUM(profit) FROM sales GROUP BY year,country,product;
+------+---------+---------+-------------+
| year | country | product | SUM(profit) |
+------+---------+---------+-------------+
| 2004 | china   | tnt1    |        2001 |
| 2004 | china   | tnt2    |        2002 |
| 2004 | china   | tnt3    |        2003 |
| 2005 | china   | tnt1    |        4012 |
| 2005 | china   | tnt2    |        4012 |
| 2005 | china   | tnt3    |        4015 |
| 2006 | china   | tnt1    |        2010 |
| 2006 | china   | tnt2    |        2011 |
| 2006 | china   | tnt3    |        2012 |
+------+---------+---------+-------------+
9 rows in set (0.04 sec)

--带WITH ROLLUP进行GROUP,会有更多统计信息,如2004年所有产品的利润6006
mysql> SELECT year, country, product, SUM(profit) FROM sales GROUP BY year,country,product WITH ROLLUP;
+------+---------+---------+-------------+
| year | country | product | SUM(profit) |
+------+---------+---------+-------------+
| 2004 | china   | tnt1    |        2001 |
| 2004 | china   | tnt2    |        2002 |
| 2004 | china   | tnt3    |        2003 |
| 2004 | china   | NULL    |        6006 |
| 2004 | NULL    | NULL    |        6006 |
| 2005 | china   | tnt1    |        4012 |
| 2005 | china   | tnt2    |        4012 |
| 2005 | china   | tnt3    |        4015 |
| 2005 | china   | NULL    |       12039 |
| 2005 | NULL    | NULL    |       12039 |
| 2006 | china   | tnt1    |        2010 |
| 2006 | china   | tnt2    |        2011 |
| 2006 | china   | tnt3    |        2012 |
| 2006 | china   | NULL    |        6033 |
| 2006 | NULL    | NULL    |        6033 |
| NULL | NULL    | NULL    |       24078 |
+------+---------+---------+-------------+
16 rows in set (0.03 sec)
  • ROLLUP与ORDER BY不能同时出现 ,并且LIMIT出现在ROLLUP之后

数据库名,表名大小写问题:

  • 列,索引,存储子程序和触发器名在任何平台上对大小写不敏感
  • 表别名在Unix系统上大小写敏感在Windows或Mac OSX上不敏感
  • 在MySQL中可以通过lower_case_tables_name系统变量指定如何在系统中保存使用表名和数据库名

       

  • UNIX系统中将lower_case_tables_name设置为1,重启动MySQL前需将旧的数据库名或表名改为小写。
  • 保持统一的大小写风格

外键使用的问题:

  • 外键在InnoDB的存储引擎的表中有实际效果,在其他类型的表中并无实际效果。

不吝指正。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
3 收藏
分享
打赏
0 评论
3 收藏
0
分享
返回顶部
顶部