SQL基本知识以及一些发现

导语

在sql的学习过程中经常会遇到一些奇奇怪怪的东西，有些没有实际操作也不知道情况究竟是怎么样的，于是特地拿出来实操一下。

基本的创建删除操作

1.创建一个数据库：create database 库名;

2.查看一共有哪些库： show databases;

3.选择需要用的库: use 库名;

4.创建一个表: create table 表名 (字段1 类型，字段2 类型，字段3 类型 …);

5.往表中插入内容：
insert into 表名 values(内容1，内容2，内容3)；

insert into 表名 (字段1，字段3) values(内容1，内容3);

注意：内容如果是varchar()则必须加上引号！！！

6.查看表的内容： select * from 表名;

7.给表添加字段： alert table 表名 add 字段名类型;

7.删除某字段中的某个值： update 表名 set 字段名=NULL where 字段名=值;

8.删除一整行：delect from 表名 where 字段名=值；

9.删除某一个字段：alert table 表名 drop 字段名；

10删除一个表 : drop table 表名；

11删除一个数据库： drop database 库名；

12.查看警告: show warnings;

13.查看库里面的表名: show tables from 库名

where 的技巧

先贴上原始的表

由于实验需要换一个表

原始表

理解这个对于后面讲其他复杂的东西的理解是有帮助的。

where是select的过滤器，where用来过滤select出来的内容。比如说：
select from table where id=1; 意思是把table里面的所有内容，经过过滤器过滤，过滤出id=1的内容，显示出来。再比如,select from table where id=3 or id=2 or a=’asd’;就是把id=3或id=2或a=’asd’的内容过滤出来。那么我们就对b的内容过滤呢，也是一样，把*改成b就行了。

where过滤

深入一点

如果我们 or后面跟的不是字段=值的形式会怎么样？我们就跟一个1=1试一下。

1=1

全部返回，证实了我们的推论，在过滤的过程中后面恒为真，所以每条数据都满足条件。

那我们要是跟一个字段呢？

跟字段

咦？很奇怪吧，按理说a包含了全部的内容，遍历a应该返回全部内容的。其实这里面涉及到 mysql 对字符串和数字的处理机制。

数字和字符串

字符串是假

mysql 认为以数字开头的字符串就是数字本身，并且在用or的时候认为数字是真，字符串是假。

where 语句的小利用

常常在where 后面跟上一些正确的语句，添加上猜测的字段，来判断字段是否存在。
or 判断

or判断
in 判断

in判断

between判断

like 判断

like判断
……

小结：猜测列的时候可以用一下

concat()和group_concat()

union select 1,2,concat(列) from 表，可以把两个标的内容拼成一个表，其中1,2对应的是union前面表的第一第二个字段。
union select 1,2,group_concat(列) from 表;可以把列的所有内容组成一行输出，这对于有些时候想在一行输出的时候很有用（原本是与group y 组合使用，意思是按照group by 的那一列分组，输出相同group by 值得，现在没有group by 只好全部输出）

NULL FALSE ‘’ 0

这几个的比较我们来试一试

mysql> select * from pass where 0=false;
+------+------------------+-------------+
| id   | a                | b           |
+------+------------------+-------------+
|    1 | asd              | qsdf        |
|    2 | dfg              | 1op         |
|    3 | hoioi            | 656lkahlkf  |
|    4 | adshfasd         | aosijfiadfn |
|    5 | 2354asfaijdkvsjf | oioo        |
|    6 | 99iad            | 9okomuh     |
+------+------------------+-------------+
6 rows in set (0.00 sec)

mysql> select * from pass where ''=false;
+------+------------------+-------------+
| id   | a                | b           |
+------+------------------+-------------+
|    1 | asd              | qsdf        |
|    2 | dfg              | 1op         |
|    3 | hoioi            | 656lkahlkf  |
|    4 | adshfasd         | aosijfiadfn |
|    5 | 2354asfaijdkvsjf | oioo        |
|    6 | 99iad            | 9okomuh     |
+------+------------------+-------------+
6 rows in set (0.00 sec)

发现 ‘’ 和0 在mysql 看来都是false

mysql> select * from pass where 0=null;
Empty set (0.00 sec)

mysql> select * from pass where ''=null;
Empty set (0.00 sec)

mysql> select * from pass where null=null;
Empty set (0.00 sec)

但是NULL比较奇葩，什么和他比较都是假，包括他自己，但是有一种情况下为真

mysql> select * from pass where NULL<=>NULL;
+------+------------------+-------------+
| id   | a                | b           |
+------+------------------+-------------+
|    1 | asd              | qsdf        |
|    2 | dfg              | 1op         |
|    3 | hoioi            | 656lkahlkf  |
|    4 | adshfasd         | aosijfiadfn |
|    5 | 2354asfaijdkvsjf | oioo        |
|    6 | 99iad            | 9okomuh     |
+------+------------------+-------------+
6 rows in set (0.01 sec)

隐式类型转换

概念和例子

本质上就是mysql 对于字符和数字的比较等方面的处理，
不只是这个方面。

官方文档的翻译如下：
1.两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换

2.两个参数都是字符串，会按照字符串来比较，不做类型转换

3.两个参数都是整数，按照整数来比较，不做类型转换

4.十六进制的值和非数字做比较时，会被当做二进制串

5.有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp

6.有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较

7.所有其他情况下，两个参数都会被转换为浮点数再进行比较

重点在第七个，这也是我们刚刚讨论where or时候遇到的问题，现在有了自己的名字，叫隐式类型转换。
一个例子：

mysql> select 'a' + 'b';
+-----------+
| 'a' + 'b' |
+-----------+
|  0 |
+-----------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message    |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

是不是有点郁闷呢？

之所以出现这种情况，是因为+为算术操作符arithmetic operator 这样就可以解释为什么a和b都转换为double了。因为转换之后其实就是：0+0=0了。

推荐一链接：

安全问题

假如应用前端没有WAF防护，那么下面的sql很容易注入：

1
2
3

mysql> select * from t_account where fname='A' ;
fname传入 A' OR 1='1 
mysql> select * from t_account where fname='A' OR 1='1';

攻击者更聪明一点： fname传入 A’+’B ，fpassword传入 ccc’+0 ：

mysql> select * from t_account where fname='A'+'B' and fpassword='ccc'+0;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+
2 rows in set, 7 warnings (0.00 sec)