where疑难字句

mysql> select * from one where password='123'and age='22';,  //逻辑与
+----+----------+----------+-----+
| id | username | password | age |      //这句话的含义是查询年龄为22的并且密码为123的用户   
+----+----------+----------+-----+      
|  2 | zhang2   | 123      |  22 |
+----+----------+----------+-----+
1 row in set (0.00 sec) 




mysql> select * from one where password=123 or age=22;
+----+----------+----------+-----+
| id | username | password | age |
+----+----------+----------+-----+
|  2 | zhang2   | 123      |  22 | 
+----+----------+----------+-----+
6 rows in set (0.00 sec)                     坨

mysql> select * from one where age!=21;
+----+----------+----------+-----+           //查询出年龄不为20的所有用户
| id | username | password | age |  
+----+----------+----------+-----+   
|  2 | zhang2   | 123      |  22 |
|  3 | zhang3   | 123      |  23 |
|  4 | zhang4   | 123      |  24 |
|  5 | zhang5   | 123      |  25 |
|  6 | zhang6   | 123      |  26 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

mysql> select * from one where age is null;   //年龄中查询有没有空值 
Empty set (0.00 sec)   
 
mysql> select * from one where age is not null;  
+----+----------+----------+-----+           //查询年龄不为空的用户 
| id | username | password | age |
+----+----------+----------+-----+
|  1 | zhang1   | 123      |  21 |
|  2 | zhang2   | 123      |  22 |
|  3 | zhang3   | 123      |  23 |
|  4 | zhang4   | 123      |  24 |
|  5 | zhang5   | 123      |  25 |
|  6 | zhang6   | 123      |  26 |
|  7 | zhang7   | 456      |   0 |
+----+----------+----------+-----+
7 rows in set (0.00 sec)

mysql> select * from one where age>=22 and age<=26;
+----+----------+----------+-----+     //查询年龄大于等于22的并且小于等于26的用户 
| id | username | password | age |
+----+----------+----------+-----+
|  2 | zhang2   | 123      |  22 |
|  3 | zhang3   | 123      |  23 |
|  4 | zhang4   | 123      |  24 |
|  5 | zhang5   | 123      |  25 |
|  6 | zhang6   | 123      |  26 |
+----+----------+----------+-----+
5 rows in set (0.00 sec) 

mysql> select * from one where age between 22 and 26;
+----+----------+----------+-----+   //查询年龄大于等于22的并且小于等于26的用户 同上 
| id | username | password | age |
+----+----------+----------+-----+
|  2 | zhang2   | 123      |  22 |
|  3 | zhang3   | 123      |  23 |
|  4 | zhang4   | 123      |  24 |
|  5 | zhang5   | 123      |  25 |
|  6 | zhang6   | 123      |  26 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

mysql> select * from one where age not between 22 and 26;
+----+----------+----------+-----+  //查询年龄不在22到26之间的用户 
| id | username | password | age |
+----+----------+----------+-----+
|  1 | zhang1   | 123      |  21 |
|  7 | zhang7   | 456      |   0 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
   

                                        //like 是模糊查询 
mysql> select * from one where username like 'zhang%';
+----+----------+----------+-----+      //查询出所有姓张的用户 
| id | username | password | age |
+----+----------+----------+-----+      //% 代表通配符 代表的意思是任意个任意字符 
|  1 | zhang1   | 123      |  21 |      //_ 代表一个任意字符  
|  2 | zhang2   | 123      |  22 |      //like 会大大降低查询效率  前边% 不建议使用 
|  3 | zhang3   | 123      |  23 |      
|  4 | zhang4   | 123      |  24 |
|  5 | zhang5   | 123      |  25 |
|  6 | zhang6   | 123      |  26 |
|  7 | zhang7   | 456      |   0 |
+----+----------+----------+-----+
7 rows in set (0.00 sec)

mysql> select * from one where username like '%3%';
+----+----------+----------+-----+     //查询出用户名带3的用户 
| id | username | password | age |
+----+----------+----------+-----+     //执行效率比较慢 不建议使用 
|  3 | zhang3   | 123      |  23 |
+----+----------+----------+-----+
1 row in set (0.00 sec)

mysql> select * from one where id=1 or id=3 or id=5 or id=7;
+----+----------+----------+-----+
| id | username | password | age |    //查询id=1 ，3， 5， 7 的用户 
+----+----------+----------+-----+
|  1 | zhang1   | 123      |  21 |
|  3 | zhang3   | 123      |  23 |
|  5 | zhang5   | 123      |  25 |
|  7 | zhang7   | 456      |   0 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> select * from one where id in(1,3,5,7);
+----+----------+----------+-----+   //同上 这个简介  推荐使用 
| id | username | password | age |
+----+----------+----------+-----+
|  1 | zhang1   | 123      |  21 |
|  3 | zhang3   | 123      |  23 |
|  5 | zhang5   | 123      |  25 |
|  7 | zhang7   | 456      |   0 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)