二超的MySQL笔记（三）超简洁

个人用户说明 {在MySQL下创建一个库名为data，在库的下面建表user；}

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| dbtest             |
| mysql              |
| performance_schema |
| sqltestdb          |
| test               |
+--------------------+
7 rows in set (0.01 sec)
mysql> use data
Database changed
mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| info           |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | uname  | upass |
+----+--------+-------+
|  1 | 李世龙 | 学生  |
|  2 | 孙久超 | 老师  |
|  3 | 赵欣源 | 可乐  |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+---------------+------------+------+
| id | uname         | upass      | type |
+----+---------------+------------+------+
|  1 | admin         | admin      |    1 |
|  2 | 李思影        | lsy        |    2 |
|  3 | zhanghuaichao | 5201314    |    3 |
|  4 | wangpeng      | 1314520    |    4 |
|  5 | huawei        | qiling9000 |    5 |
+----+---------------+------------+------+
5 rows in set (0.00 sec)

一.case用法

案例：case是通过用户给定的条件来查询相应的结果，并且可以生成单独的队列。
用法：

mysql> select uname,
    -> case
    -> when type!="3" then "超级用户" else "普通用户"
    -> end as 用户类别
    -> from user;
+---------------+----------+
| uname         | 用户类别 |
+---------------+----------+
| admin         | 超级用户 |
| 李思影        | 超级用户 |
| zhanghuaichao | 普通用户 |
| wangpeng      | 超级用户 |
| huawei        | 超级用户 |
+---------------+----------+
5 rows in set (0.00 sec)

二.在原有字段增减值

案例：对表中的某一类型的值增加100
用法：select id,uname,upass,type+100 from user;
		  select id,uname,upass,type-100 from user;
例图：

mysql> select id,uname,upass,type-100 from user;
+----+---------------+------------+----------+
| id | uname         | upass      | type-100 |
+----+---------------+------------+----------+
|  1 | admin         | admin      |      -99 |
|  2 | 李思影        | lsy        |      -98 |
|  3 | zhanghuaichao | 5201314    |      -97 |
|  4 | wangpeng      | 1314520    |      -96 |
|  5 | huawei        | qiling9000 |      -95 |
+----+---------------+------------+----------+
5 rows in set (0.00 sec)

mysql> select id,uname,upass,type+100 from user;
+----+---------------+------------+----------+
| id | uname         | upass      | type+100 |
+----+---------------+------------+----------+
|  1 | admin         | admin      |      101 |
|  2 | 李思影        | lsy        |      102 |
|  3 | zhanghuaichao | 5201314    |      103 |
|  4 | wangpeng      | 1314520    |      104 |
|  5 | huawei        | qiling9000 |      105 |
+----+---------------+------------+----------+
5 rows in set (0.00 sec)

三.聚合函数

（1）.count函数的使用
	案例：统计表中有几个id用户（*其统计时不会把值为null的计入*）
	用法：select count(id) from user
（2）.max/min/sum/avg求最大值,最小值,总和,平均值
	案例：求表中type的最大值，最小值，总和，平均值
	用法：@1.select max(id) from user;
	          @2.select min(id) from user;
	          @3.select sum(id) from user;
	          @4.select avg(id) from user;
	例图：`		

mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(type) from user;
+-----------+
| max(type) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(type) from user;
+-----------+
| min(type) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(id) from user;
+---------+
| sum(id) |
+---------+
|      15 |
+---------+
1 row in set (0.00 sec)

mysql> select avg(id) from user;
+---------+
| avg(id) |
+---------+
|  3.0000 |
+---------+
1 row in set (0.00 sec)`

四.where子句

（1）.关系表达式：
	>(大于),>=(大于等于),<(小于),<=(小于等于),!=(不等于),<>()
（2）.多条件连接符：
	and（和），or（或），[not]（非），between A and B（A到B之间）
（3）.集合操作
	>all(set)比所有的大,<all(set)比所有的小,>any(set):比任何一个大,<any(set)：比任何一个小
（4）.模糊查询：like
	_   : 占位符，表示匹配任意一个字符
	%  :表示匹配任意N个字符，大于等于0

实例图

mysql> select * from user where id="1" or type="4";
+----+----------+---------+------+
| id | uname    | upass   | type |
+----+----------+---------+------+
|  1 | admin    | admin   |    1 |
|  4 | wangpeng | 1314520 |    4 |
+----+----------+---------+------+
2 rows in set (0.00 sec)

mysql> select * from user where id in("1","4");
+----+----------+---------+------+
| id | uname    | upass   | type |
+----+----------+---------+------+
|  1 | admin    | admin   |    1 |
|  4 | wangpeng | 1314520 |    4 |
+----+----------+---------+------+
2 rows in set (0.00 sec)

mysql> select * from user where id not in("1","4");
+----+---------------+------------+------+
| id | uname         | upass      | type |
+----+---------------+------------+------+
|  2 | 李思影        | lsy        |    2 |
|  3 | zhanghuaichao | 5201314    |    3 |
|  5 | huawei        | qiling9000 |    5 |
+----+---------------+------------+------+
3 rows in set (0.00 sec)

mysql> select * from user where id>=2 and id<=4;
+----+---------------+---------+------+
| id | uname         | upass   | type |
+----+---------------+---------+------+
|  2 | 李思影        | lsy     |    2 |
|  3 | zhanghuaichao | 5201314 |    3 |
|  4 | wangpeng      | 1314520 |    4 |
+----+---------------+---------+------+
3 rows in set (0.00 sec)

mysql> select * from user where between 2 and 4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between 2 and 4' at line 1
mysql> select * from user where id between 2 and 4;
+----+---------------+---------+------+
| id | uname         | upass   | type |
+----+---------------+---------+------+
|  2 | 李思影        | lsy     |    2 |
|  3 | zhanghuaichao | 5201314 |    3 |
|  4 | wangpeng      | 1314520 |    4 |
+----+---------------+---------+------+
3 rows in set (0.00 sec)

mysql> select * from user where upass like"___";
+----+--------+-------+------+
| id | uname  | upass | type |
+----+--------+-------+------+
|  2 | 李思影 | lsy   |    2 |
+----+--------+-------+------+
1 row in set (0.00 sec)

mysql> select * from user where upass like"____";
Empty set (0.00 sec)

mysql> select * from user where upass like"z%";
Empty set (0.00 sec)

mysql> select * from user where uname like"z%";
+----+---------------+---------+------+
| id | uname         | upass   | type |
+----+---------------+---------+------+
|  3 | zhanghuaichao | 5201314 |    3 |
+----+---------------+---------+------+
1 row in set (0.00 sec)

mysql> select * from user where uname like"%g%";
+----+---------------+---------+------+
| id | uname         | upass   | type |
+----+---------------+---------+------+
|  3 | zhanghuaichao | 5201314 |    3 |
|  4 | wangpeng      | 1314520 |    4 |
+----+---------------+---------+------+
2 rows in set (0.00 sec)