mysql复合查询结果集顺序的一个问题

刚到家,@冰冻的狐狸在qq上给我留言,问这两句SQL为什么查询结果的排序不同

SELECT * FROM `pp_user_history` order by id desc;
SELECT * from (SELECT * FROM `pp_user_history` order by id desc) as hh GROUP BY hh.item_id;

我在本地用两条sql测试了下

SELECT * FROM words ORDER BY id ASC;
SELECT * FROM (SELECT * FROM `words` ORDER BY id ASC) AS hh GROUP BY hh.pinyin_s;

果然是不同的

仔细结果集发现
YJ1_21]E_LOZ6ET7`L`VRI7
通过红框圈出来的那个字段得到如下结论:

结果集排序默认按照主查询的group by字段升序排

BTW.明天年会,尼玛还有一个项目要赶着上线,哥要是不是请了将近三天假陪老娘在医院哥也搞完了啊,催个毛线!今天组里有个同事提离职了,我也和老大说了自己的想法和看法。人是需要成长的,路,也该是不断探索的,我想如果我不按照寻常路走下去,或许我是会遭受许多的挫折和磨难,但是我想也会得到更多。

在此新年之际,谢过老大一年来对我的悉心教导。谢谢!

详解mysql数据库sql_mode模式

mysql可以运行在不同sql mode模式下面,sql mode模式定义了mysql应该支持的sql语法,数据校验等!

查看默认的sql mode模式:
select @@sql_mode;
我的数据库是:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
在此模式下面,如果插入的数据的长度大于定义的长度,那么就会报错!

set session sql_mode=’REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI’;
在这种模式下面:插入的数据的长度大于定义的时候,就会截取,并警告,但是可以插入进去
session表示只在本次中有效
global:表示在本次连接中不生效,而对于新的连接就生效

启用NO_BACKSLASH_ESCAPES模式,使反斜线成为普通字符,在导入数据时候,如果数据中有反斜线,启用这个模式是个不错的选择

启用PIPES_AS_CNCAT模式,将||看成是普通字符串

常用的sql mode:
sql mode值 说明
ANSI: ‘REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE和ANSI组合’,这种模式使语法和行为更符合标准的sql
STRICT_TRANS_TABLES : 使用与事务和非事务表,严格模式
TRADITIONAL :也是严格模式,对于插入不正确的值给出错误而不是警告。用在事务时,只要发生错误就立即回滚

在Mysql5.0以下,默认的sql mode(sql mode参数)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在这些模式下可以插入超过字段定义长度的数据,或是在字段中没有定义的元素数据(如,enum)。不过在插入后会有一个warning(可以用 show warnings来查看)。
在Mysql5.0以上版本中,有三种sql mode模式(ANSI、TRADITIONAL和STRICT_TRANS_TABLES(严格模式))可以用来解决以下问题:
(1). 通过设置不同的sql mode,可以在不同严格程序进行数据校验,有效地保证了数据准确性.
(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务修改太多.
通过设置sql mode为STRICT_TRANS_TABLES(严格模式)来实现数据的严格校检,使错误数据不能插入,从而保证数据准确性。TRADITIONAL 模式也属于严格模式,同样可以实现严格校检,使错误数据不能插入,从而保证数据准确性。不过在这种模式MAX(X,0)返回的结果是NULL,所以在包含有MAX的运算中根据实际情况设定好sql mode.
ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。在某些情况下,ENUM值也可以为空字符串(”)或NULL:如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值。该字符串与“普通” 空字符串不同,该字符串有数值值0。
如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素。
(1)sql mode为ANSI

mysql> create table test(id bigint(20) auto_increment primary key, browsertype enum('ie','firefox','other')); 
mysql> insert into test(browsertype) values('ie') ; 
Query OK, 1 row affected (0.07 sec) 
mysql> insert into test(browsertype) values('maxthon') ; 
Query OK, 1 row affected (0.03 sec) 
mysql> show warnings;(数据虽然成功insert,但有warning) 
+---------+------+--------------------------------------------------+ 
| Level | Code | Message | 
+---------+------+--------------------------------------------------+ 
| Warning | 1265 | Data truncated for column 'browsertype' at row 1 | 
+---------+------+--------------------------------------------------+ 
1 row in set (0.01 sec) 
mysql> select * from test; 
+----+-------------+ 
| id | browsertype | 
+----+-------------+ 
| 1 | ie | 
| 2 | | 
+----+-------------+ 
2 rows in set (0.01 sec)

(2)使用严格模式(STRICT_TRANS_TABLES)

mysql> set session sql_mode='STRICT_TRANS_TABLES'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> select @@sql_mode; 
+---------------------+ 
| @@sql_mode | 
+---------------------+ 
| STRICT_TRANS_TABLES | 
+---------------------+ 
1 row in set (0.00 sec) 
mysql> insert into test(browsertype) values('maxthon') ; 
ERROR 1265: Data truncated for column 'browsertype' at row 1 

mysql> insert into test(browsertype) values('firefox') ; 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test; 
+----+-------------+ 
| id | browsertype | 
+----+-------------+ 
| 1 | ie | 
| 2 | | 
| 3 | firefox | 
+----+-------------+ 
3 rows in set (0.00 sec)

(3) TRADITIONAL模式

mysql> create table t11 (i int); 
Query OK, 0 rows affected (0.02 sec) 
mysql> set sql_mode='ANSI'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into t11 values(9%0); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from t11; 
+------+ 
| i | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.00 sec) 
mysql> set sql_mode='TRADITIONAL'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into t11 values(9%0); 
ERROR 1365: Division by 0 
mysql> show warnings; 
+-------+------+---------------+ 
| Level | Code | Message | 
+-------+------+---------------+ 
| Error | 1365 | Division by 0 | 
+-------+------+---------------+ 
1 row in set (0.02 sec)

 

mysql中order by field的使用

SELECT * FROM fruit 
ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+
SELECT * FROM fruit
ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+
SELECT * FROM fruit
ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
+----------+--------+---------------------+

mysql中的编码相关变量

character_set_client

来自客户端的语句的字符集。

·         character_set_connection

用于没有字符集导入符的文字和数字-字符串转换。

·         character_set_database

默认数据库使用的字符集。当默认数据库更改时,服务器则设置该变量。如果没有默认数据库,变量的值同character_set_server。

·         character_set_results

用于向客户端返回查询结果的字符集。

·         character_set_ server

服务器的默认字符集。

·         character_set_system

服务器用来保存识别符的字符集。该值一定是utf8。

·         character_sets_dir

字符集安装目录。

mysql随机数

若要在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如, 若要在7 到 12 的范围(包括7和12)内得到一个随机整数, 可使用以下语句:

SELECT FLOOR(7 + (RAND() * 6));

MySQL利用存储过程批量插入数据

实验环境:mysql Query Browser 1.2.7       MySQL5.1.58

存储过程:

DELIMITER $$

DROP PROCEDURE IF EXISTS `aa` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `aa`()
BEGIN
DECLARE i INT DEFAULT 200;
WHILE i < 200000 DO
INSERT INTO test (`num`) VALUES (i);
SET i = i + 1;
END WHILE;
END $$

DELIMITER ;

然后直接在query里面调用:

CALL aa();

SQL语句中空值问题

sql查询语句中有一个查询条件为空值,我写成了=NULL始终查不出来结果,易大哥告诉我写成IS NULL即可,果断可以了,纠结了好久问题在这,记录下……

另摘录一段where查询条件示例:

使用WHERE子句设置查询条件
WHERE子句设置查询条件,过滤掉不需要的数据行。
例如下面语句查询年龄大于20的数据:
SELECT *
FROM usertable
WHERE age>20

WHERE子句可包括各种条件运算符:
比较运算符(大小比较):>、>=、=、<、<=、<>、!>、!<
范围运算符(表达式值是否在指定的范围):BETWEEN…AND… NOT BETWEEN…AND…
列表运算符(判断表达式是否为列表中的指定项):IN (项1,项2……) NOT IN (项1,项2……)
模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOT LIKE
空值判断符(判断表达式是否为空):IS NULL、IS NOT NULL

逻辑运算符(用于多条件的逻辑连接):NOT、AND、OR

1、范围运算符例:age BETWEEN 10 AND 30相当于age>=10 AND age<=30
2、列表运算符例:country IN (’Germany’,’China’)
3、模式匹配符例:常用于模糊查找,它判断列值是否与指定的字符串格式相匹配。
可用于char、 varchar、text、ntext、datetime和smalldatetime等类型查询。
可使用以下通配字符: 百分号%:可匹配任意类型和长度的字符,如果是中文,请使用两个百分号即%%。 下划线_:匹配单个任意字符,它常用来限制表达式的字符长度。 方括号[]:指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。 [^]:其取值也[] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
例如: 限制以Publishing结尾,使用LIKE ‘%Publishing’ 限制以A开头:LIKE ‘[A]%’ 限制以A开头外:LIKE ‘[^A]%’ 4、空值判断符例WHERE age IS NULL 5、逻辑运算符:优先级为NOT、AND、OR