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判断

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 只好全部输出)
group_concat

NULL FALSE ‘’ 0

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

1
2
3
4
5
6
7
8
9
10
11
12
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)

1
2
3
4
5
6
7
8
9
10
11
12
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

1
2
3
4
5
6
7
8
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比较奇葩,什么和他比较都是假,包括他自己,但是有一种情况下为真

1
2
3
4
5
6
7
8
9
10
11
12
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时候遇到的问题,现在有了自己的名字,叫隐式类型转换。
一个例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 :

1
2
3
4
5
6
7
8
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)

文章目录
  1. 1. 导语
  2. 2. 基本的创建删除操作
  3. 3. where 的技巧
    1. 3.1. 先贴上原始的表
    2. 3.2. 深入一点
    3. 3.3. where 语句的小利用
  4. 4. concat()和group_concat()
  5. 5. NULL FALSE ‘’ 0
  6. 6. 隐式类型转换
    1. 6.1. 概念和例子
    2. 6.2. 安全问题
|