mysql补充.ppt

1、,1.搜索一个“NULL”值,SELECT* FROMa WHEREa.column=NULL,在SQL中，NULL什么也不等于，而且NULL也不等于NULL。这个查询不会返回任何结果的 。 当搜索NULL值的时候，应该使用这样的查询： SELECT* FROMa WHEREa.columnISNULL,2 LEFT JOIN,user表: id name - 1 libk 2 zyfon 3 daodao,user_action表: user_id action - 1 jump 1 kick 1 jump 2 run 4 swim,select id, name, action from

2、user as u left join user_action a on u.id = a.user_id 思考： 这条语句返回什么样的结果？,result:,id name action - 1 libk jump 1 libk kick 1 libk jump 2 zyfon run 3 daodao null ,注意到user_action中还有一个user_id=4, action=swim的纪录，但是没有在结果中出现，而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录，但是却出现在了结果集中。因为现在是MySQL left join，所有

3、的工作以left为准.结果1，2，3，4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录 结论： 我们可以想象MySQL left join 是这样工作的，从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，如：结果1和结果3)，如果右边没有与on条件匹配的表，那连接的字段都是null,我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录， 注意用来判断的那列必须声明为not null的。 如： sql: select id, name, action from user as u left join user_act

4、ion a on u.id = a.user_id where a.user_id is NULL,result:,id name action - 3 daodao NULL,使用附加条件的LEFT JOIN,SELECT* FROMa LEFTJOIN b ONb.id=a.id WHEREb.column=something,在LEFT JOIN之后才会检查WHERE条件，所以，上面这个查询在连接之后才会检查column。就像我们刚才了解到的那样，非NULL值才可以满足相等条件，所以，在a的记录中，那些在b中没有对应的条目的记录不可避免地要被过滤掉。,为了真正地匹配满足b.column

5、= something条件的记录（要返回a中的全部记录，也不过滤掉那些在b中没有对应的条目的记录），这个条件应该放在ON子句中： SELECT * FROM a LEFT JOIN b ON b.a = a.id AND b.column = something,3.小于一个值，但是不为NULL,SELECT * FROM b WHERE b.column something AND b.column IS NOT NULL,实际上，这并不是一个错误：这个查询是有效的，是故意这样做的。但是，这里的IS NOT NULL是冗余的。 如果b.column是NULL，那么无法满足b.column s

6、omething）一起使用。 这是因为，在MySQL中，在ORDER BY的时候，NULL会排在前面，因此，一些人错误地认为NULL比任何其他的值都要小,这个查询可以被简化： SELECT* FROMb WHEREb.columnsomething 在b.column中，不可能返回NULL,4,按照NULL来进行连接,SELECT* FROMa JOINb ONa.column=b.column 在两个表中，当column是null的时候，这个查询不会返回两个字段都是NULL的记录，原因如上所述：两个NULL并不相等。,这个查询应该这样来写： SELECT* FROMa JOINb ONa.c

7、olumn=b.column OR(a.columnISNULLANDb.columnISNULL) MySQL的优化器会把这个查询当成一个“等值连 接”，然后提供一个特殊的连接条件：ref_or_null,5. NOT IN和NULL值,SELECTa.* FROMa WHEREa.columnNOTIN ( SELECTcolumn FROMb ),如果在b.column中有一个NULL值，那么这个查询是不会返回任何结果的。和其他谓词一样，IN 和 NOT IN 遇到NULL也会被判定为NULL。 你应该使用NOT EXISTS重写这个查询：,SELECTa.* FROMa WHERENO

8、TEXISTS ( SELECTNULL FROMb WHEREb.column=a.column ) 不像IN，EXISTS总是被判定为true或false的。,6.对随机的样本进行排序,SELECT* FROMa ORDERBY RAND(),column LIMIT10,这个查询试图选出10个随机的记录，按照column来排序。 ORDER BY会按照自然顺序来对输出结果进行排序：这就是说，当第一个表达式的值相等的时候，这些记录才会按照第二个表达式来排序。 但是，RAND()的结果是随机的。要让RAND()的值相等是行不通的，所以，按照RAND()排序以后，再按照column来排序也是没

9、有意义的。 要对随机的样本记录进行排序，可以使用这个查询：,SELECT* FROM( SELECT* FROMmytable ORDERBY RAND() LIMIT10 )q ORDERBY column,7. IN和,值的分隔列表,SELECT* FROMa WHEREcolumnIN(1,2,3) 这个查询试图让column的值匹配用,分隔的字符串中的任意一个值：,这不会正常发挥作用的，因为在IN列表中，那个字符串并不会被展开。 如果列column是一个VARCHAR，那么它（作为一个字符串）会和整个列表（也作为一个字符串）进行比较，当然，这不可能匹配。如果 column是某个数值类型

10、，那么这个列表会被强制转换为那种数值类型（在最好的情况下，只有第一项会匹配） 处理这个查询的正确方法应该是使用合适的IN列表来重写它：,SELECT* FROMa WHEREcolumnIN(1,2,3),8 控制台无法输入中文,解决办法:在进入mysql控制台时,加上参数.示例如下:mysql.exe -user=root -password=root -default-character-set=gbk,9. 无法插入中文,ERROR 1366 (HY000): Incorrect string value: xB9xD8xD3xF0 for column status; alter ta

11、ble users character set GBK; show create table users; alter table users modify username char(20) character set gbk; truncate table users; alter table users modify username char(20) character set gbk;,10 调整字符集时经常用的命令,show variables like char% SETNAMESx相当于SETcharacter_set_client=x;SETcharacter_set_results=x;SETcharacter_set_connection=x;,11 无法查找中文,在mysql中，进行中文排序和查找的时候，对汉字的排序和查找结果是错误的。这种情况在mysql的非常多版本中都存在。如果这个问题不解决，那么mysql将无法实际处理中文。 出现这个问题的原因是：mysql在查询字符串时是大小写不敏感的，在编绎mysql时一般以iso-8859字符集作为默认的字符集