您的位置:一分快三全天精准计划 > 一分快三全天精准计划-操作系统 > mysql 笔记1

mysql 笔记1

2020-01-05 00:01
#mysql 的语句笔记
----

#查询当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-02-14 19:28:01 |
+---------------------+
1 row in set (0.02 sec)



select * from hs_esl order by c

ctrl + u

#sql语句中使用变量 “@” 设置变量
mysql> select @name:= id from hs_ap where ip = 1;
+------------+
| @name:= id |
+------------+
| 1          |
| 10         |
| 11         |
| 12         |
| 13         |
| 14         |
| 15         |
| 16         |
| 17         |
| 19         |
| 2          |
| 20         |
| 21         |
| 22         |
| 23         |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
+------------+
22 rows in set (0.00 sec)

#使用变量做加法运算
mysql> set @sum=4+6;
Query OK, 0 rows affected (0.00 sec)

mysql> select @sum;
+------+
| @sum |
+------+
|   10 |
+------+
1 row in set (0.00 sec)


mysql> select *from hs_goods_esl_pr limit 3;
+-----+----------+-------------+-------+--------+---------------------+
| id  | goods_id | esl_id      | ap_id | status | create_time         |
+-----+----------+-------------+-------+--------+---------------------+
|   8 | 503607   | 5C-C5-4A-99 | 23    |   NULL | 2015-12-06 03:51:11 |
| 103 | 349561   | 5C-8B-17-99 | 3     |   NULL | 2015-11-26 23:02:52 |
| 104 | 535766   | 5C-8C-89-99 | 3     |   NULL | 2016-02-22 08:14:14 |
+-----+----------+-------------+-------+--------+---------------------+
3 rows in set (0.00 sec)


#设置动态变量,用查询的结果做变量
mysql> set @esl_i = (select id from hs_goods_esl_pr where esl_id='5C-C5-4A-99');
Query OK, 0 rows affected (0.00 sec)

mysql> select @esl_i;
+--------+
| @esl_i |
+--------+
|      8 |
+--------+
1 row in set (0.01 sec)


#设置变量并且使用循环语句
mysql> set @n = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @n:=@n+1 as rown ,id from hs_goods_esl_pr limit 3;
+------+-------+
| rown | id    |
+------+-------+
|    1 | 38977 |
|    2 | 41432 |
|    3 | 38976 |
+------+-------+
3 rows in set (0.00 sec)


#sql语句中 SORT 关键字的用法
mysql> select (12+23) /SQRT(64);
+-------------------+
| (12+23) /SQRT(64) |
+-------------------+
|             4.375 |
+-------------------+
1 row in set (0.00 sec)


#查询大小写是否敏感 1 == true 0 == false
mysql> select 'abc' = 'ABCD';
+----------------+
| 'abc' = 'ABCD' |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)


mysql> select 'abc' = 'ABC';
+---------------+
| 'abc' = 'ABC' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

#查询当前mysql的版本号,已经当前的数据库名字
mysql> select version(), database();
+-----------+------------+
| version() | database() |
+-----------+------------+
| 5.6.21    | wm         |
+-----------+------------+
1 row in set (0.01 sec)


#limit的用法
mysql> select id,last_time from hs_esl limit 3;
+-------------+---------------------+
| id          | last_time           |
+-------------+---------------------+
| 58-0A-D3-99 | 2016-03-18 00:09:48 |
| 58-0B-8A-99 | 2016-03-15 12:29:23 |
| 58-0D-23-99 | 2016-03-10 12:29:41 |
+-------------+---------------------+
3 rows in set (0.00 sec)


#
mysql> select area as a from hs_ap;
+--------+
| a      |
+--------+
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
|        |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| 酒区   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
+--------+
22 rows in set (0.00 sec)


#sql中加法运算
mysql> select '1+1+1' as jiafa , 1+1+1 as 'result';
+-------+--------+
| jiafa | result |
+-------+--------+
| 1+1+1 |      3 |
+-------+--------+
1 row in set (0.01 sec)


#查询结果中连接两个结果的查询语句(concat)的用法
mysql> select concat(last_time,'=+=',create_time) from hs_ap;
+-------------------------------------------+
| concat(last_time,'=+=',create_time)       |
+-------------------------------------------+
| 2016-03-18 04:03:45=+=2015-10-26 13:41:40 |
| 2016-03-18 04:03:37=+=2015-11-26 12:19:30 |
| 2016-03-18 04:03:17=+=2015-10-24 02:06:10 |
| 2016-03-18 04:03:59=+=2015-11-26 12:19:03 |
| 2016-03-18 04:03:33=+=2015-11-26 12:18:46 |
| 2016-03-18 04:03:01=+=2015-11-26 12:19:32 |
| 2016-03-18 04:03:58=+=2015-11-26 12:18:40 |
| 2016-03-18 04:04:02=+=2015-11-26 12:18:49 |
| 2016-03-18 04:03:44=+=2015-11-26 12:19:12 |
| 2016-03-18 04:03:58=+=2015-11-26 12:19:07 |
| 2016-03-18 04:03:39=+=2015-10-26 13:41:29 |
| 2016-03-18 04:03:44=+=2015-11-26 12:19:04 |
| 2016-03-18 04:03:20=+=2015-11-26 12:18:59 |
| 2016-03-18 04:03:35=+=2015-11-26 12:19:18 |
| 2016-03-18 04:03:39=+=2015-11-26 15:58:37 |
| 2016-03-18 04:03:48=+=2015-10-26 13:42:09 |
| 2016-03-18 04:03:29=+=2015-10-26 13:41:47 |
| 2016-03-18 04:03:58=+=2015-10-26 13:41:32 |
| 2016-03-18 04:03:31=+=2015-10-26 13:41:55 |
| 2016-03-18 04:03:04=+=2015-10-26 13:42:04 |
| 2016-03-18 04:03:57=+=2015-11-26 12:18:42 |
| 2016-03-18 04:03:18=+=2015-11-26 12:19:30 |
+-------------------------------------------+
22 rows in set (0.00 sec)


#查询语句中as 关键词的用法
mysql> select id as sct from hs_goods where id > 801126001;
+-----------+
| sct       |
+-----------+
| 801127001 |
| 801127002 |
| 801127003 |
| 801127004 |
| 801127005 |
| 801259001 |
| 801259002 |
| 801126002 |
| 801126003 |
| 801126004 |
| 801141001 |
| 801141002 |
| 801143001 |
| 801143002 |
+-----------+
14 rows in set (0.01 sec)

mysql> select id,id > 801126001 from hs_goods limit 3;
+--------+----------------+
| id     | id > 801126001 |
+--------+----------------+
| 507981 |              0 |
| 297538 |              0 |
| 297539 |              0 |
+--------+----------------+
3 rows in set (0.01 sec)


#查询语句中大于号的使用
mysql> select id,id > 500000 from hs_goods limit 3;
+--------+-------------+
| id     | id > 500000 |
+--------+-------------+
| 507981 |           1 |
| 297538 |           0 |
| 297539 |           0 |
+--------+-------------+
3 rows in set (0.00 sec)

1 == true 0 == false

#去掉冗余信息
mysql> select distinct area from hs_ap;
+--------+
| area   |
+--------+
| NULL   |
|        |
| 酒区   |
+--------+
3 rows in set (0.00 sec)

#查询结果中互不相同的结果
mysql> select count(distinct ap_id) from hs_goods_esl_pr ; //chaxun jieguozhong hubuxiangtong de jieguo
+-----------------------+
| count(distinct ap_id) |
+-----------------------+
|                    21 |
+-----------------------+
1 row in set (0.00 sec)


mysql> select * from hs_goods_esl_pr where status = NULL;
Empty set (0.01 sec)

mysql> select * from hs_goods_esl_pr where status != NULL;
Empty set (0.01 sec)


#查询数据中有NULL中的数据信息
mysql> select * from hs_goods_esl_pr where status is NULL limit 3;
+-----+----------+-------------+-------+--------+---------------------+
| id  | goods_id | esl_id      | ap_id | status | create_time         |
+-----+----------+-------------+-------+--------+---------------------+
|   8 | 503607   | 5C-C5-4A-99 | 23    |   NULL | 2015-12-06 03:51:11 |
| 103 | 349561   | 5C-8B-17-99 | 3     |   NULL | 2015-11-26 23:02:52 |
| 104 | 535766   | 5C-8C-89-99 | 3     |   NULL | 2016-02-22 08:14:14 |
+-----+----------+-------------+-------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from hs_ap;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL   | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL   | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL   | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL   | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL   | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL   | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 2  | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
| 20 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL   | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL   | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
| 4  | online | 1    | 1    | NULL   | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
| 5  | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-10-26 13:41:32 |
| 6  | online | 1    | 1    | NULL   | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
| 7  | online | 1    | 1    | NULL   | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 8  | online | 1    | 1    | NULL   | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 9  | online | 1    | 1    | NULL   | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+--------+---------------------+---------------------+
22 rows in set (0.00 sec)


#查询数据中不含有NULL值的数据
mysql> select * from hs_ap where area is not NULL;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
+----+--------+------+------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql>


mysql> select * from hs_ap;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL   | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL   | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL   | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL   | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL   | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL   | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 2  | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
| 20 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL   | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL   | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
| 4  | online | 1    | 1    | NULL   | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
| 5  | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-10-26 13:41:32 |
| 6  | online | 1    | 1    | NULL   | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
| 7  | online | 1    | 1    | NULL   | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 8  | online | 1    | 1    | NULL   | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 9  | online | 1    | 1    | NULL   | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+--------+---------------------+---------------------+
22 rows in set (0.01 sec)

#在sql语句中使用if判断语句
mysql> select id ,if(area is not  NULL,'unknown',area) as 'sct' from hs_ap;//shiyong if yuju
+----+---------+
| id | sct     |
+----+---------+
| 1  | NULL    |
| 10 | NULL    |
| 11 | NULL    |
| 12 | NULL    |
| 13 | NULL    |
| 14 | NULL    |
| 15 | NULL    |
| 16 | unknown |
| 17 | NULL    |
| 19 | NULL    |
| 2  | NULL    |
| 20 | NULL    |
| 21 | NULL    |
| 22 | NULL    |
| 23 | NULL    |
| 3  | unknown |
| 4  | NULL    |
| 5  | NULL    |
| 6  | NULL    |
| 7  | NULL    |
| 8  | NULL    |
| 9  | NULL    |
+----+---------+
22 rows in set (0.00 sec)



mysql> select * from hs_ap;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL   | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL   | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL   | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL   | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL   | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL   | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 2  | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
| 20 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL   | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL   | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
| 4  | online | 1    | 1    | NULL   | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
| 5  | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-10-26 13:41:32 |
| 6  | online | 1    | 1    | NULL   | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
| 7  | online | 1    | 1    | NULL   | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 8  | online | 1    | 1    | NULL   | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 9  | online | 1    | 1    | NULL   | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+--------+---------------------+---------------------+
22 rows in set (0.01 sec)


#在sql语句中使用if判断语句,比上个语句更精简
mysql> select id ,ifNULL(area,"fuck") as "sct" from hs_ap;
+----+--------+
| id | sct    |
+----+--------+
| 1  | fuck   |
| 10 | fuck   |
| 11 | fuck   |
| 12 | fuck   |
| 13 | fuck   |
| 14 | fuck   |
| 15 | fuck   |
| 16 |        |
| 17 | fuck   |
| 19 | fuck   |
| 2  | fuck   |
| 20 | fuck   |
| 21 | fuck   |
| 22 | fuck   |
| 23 | fuck   |
| 3  | 酒区   |
| 4  | fuck   |
| 5  | fuck   |
| 6  | fuck   |
| 7  | fuck   |
| 8  | fuck   |
| 9  | fuck   |
+----+--------+
22 rows in set (0.01 sec)


#查询ap表的信息
mysql> select * from hs_ap;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL   | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL   | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL   | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL   | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL   | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL   | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 2  | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
| 20 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL   | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL   | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
| 4  | online | 1    | 1    | NULL   | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
| 5  | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-10-26 13:41:32 |
| 6  | online | 1    | 1    | NULL   | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
| 7  | online | 1    | 1    | NULL   | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 8  | online | 1    | 1    | NULL   | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 9  | online | 1    | 1    | NULL   | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+--------+---------------------+---------------------+
22 rows in set (0.00 sec)


#查询id大于10的信息
mysql> select *from hs_ap where id > 10 order by id;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |      | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 20 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
+----+--------+------+------+------+---------------------+---------------------+
12 rows in set (0.00 sec)

#查询id大于10的信息按倒序排列
mysql> select *from hs_ap where id > 10 order by id desc;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 23 | online | 1    | 1    | NULL | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 22 | online | 1    | 1    | NULL | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 21 | online | 1    | 1    | NULL | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 20 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 19 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 17 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 16 | online | 1    | 1    |      | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 15 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 14 | online | 1    | 1    | NULL | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 13 | online | 1    | 1    | NULL | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
+----+--------+------+------+------+---------------------+---------------------+
12 rows in set (0.00 sec)

#查询id大于10 的信息
mysql> select id , area  from hs_ap where id > 10 order by id desc;
+----+------+
| id | area |
+----+------+
| 23 | NULL |
| 22 | NULL |
| 21 | NULL |
| 20 | NULL |
| 19 | NULL |
| 17 | NULL |
| 16 |      |
| 15 | NULL |
| 14 | NULL |
| 13 | NULL |
| 12 | NULL |
| 11 | NULL |
+----+------+
12 rows in set (0.00 sec)

#创建表结构,并插入信息
mysql> create table pro(
    -> pro_id int unsigned not null,
    -> service char(20) not null,
    -> con_name char(20) not null,
    -> index (pro_id)
    -> );

Query OK, 0 rows affected (0.41 sec)

insert into pro(pro_id,service,con_name) values(1,'AIM','user1-aim');
insert into pro(pro_id,service,con_name) values(1,'MSN','user1-msn');
insert into pro(pro_id,service,con_name) values(2,'AIM','user2-aim');
insert into pro(pro_id,service,con_name) values(2,'MSN','user2-msd');
insert into pro(pro_id,service,con_name) values(2,'yahoo','user2-yahoo');
insert into pro(pro_id,service,con_name) values(4,'yahoo','user4-yahoo');

#删除表
mysql> drop tables pro;
Query OK, 0 rows affected (0.10 sec)

mysql> select *from hs_ap order by id desc limit 4;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 9  | online | 1    | 1    | NULL | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
| 8  | online | 1    | 1    | NULL | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 7  | online | 1    | 1    | NULL | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 6  | online | 1    | 1    | NULL | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
+----+--------+------+------+------+---------------------+---------------------+
4 rows in set (0.01 sec)

#查询绑定表的中总数
mysql> select count(*) from hs_goods_esl_pr;
+----------+
| count(*) |
+----------+
|    22367 |
+----------+
1 row in set (0.06 sec)

#查询价格最高的商品名称
mysql> select name,price from hs_goods order by price desc ,name limit 4;

+--------------------------------------+--------+
| name                                 | price  |
+--------------------------------------+--------+
| BD6031澳星艾利奥套装锅二代           | 519.00 |
| 水井坊鸿运装52度500ml                | 518.00 |
| BD6031汾酒500ml 53度青花20年         | 499.00 |
| 雅路250*250富丽情缘四件套            | 459.00 |
+--------------------------------------+--------+
4 rows in set (0.06 sec)


#查询价格最高的3ge商品名称,但是会存在相同价格的有两个商品,womenxianquchudi4gede jiaqe,rahouzaibijiaodayudisigedejiage
mysql> select name,price from hs_goods order by price desc ,name limit 3,1;
+------------------------------------+--------+
| name                               | price  |
+------------------------------------+--------+
| 雅路250*250富丽情缘四件套          | 459.00 |
+------------------------------------+--------+
1 row in set (0.06 sec)

mysql> select name ,price from hs_goods where price >= 459.00 order by price desc ,name;
+--------------------------------------+--------+
| name                                 | price  |
+--------------------------------------+--------+
| BD6031澳星艾利奥套装锅二代           | 519.00 |
| 水井坊鸿运装52度500ml                | 518.00 |
| BD6031汾酒500ml 53度青花20年         | 499.00 |
| 雅路250*250富丽情缘四件套            | 459.00 |
+--------------------------------------+--------+
4 rows in set (0.05 sec)

#inner join on 的用法,如果两个表中有相同的字段名,需要加上表名.字段名
mysql> select goods_id,ap_id ,result,area from hs_goods_esl_pr inner join hs_ap on hs_ap.id =hs_goods_esl_pr.ap_id limit 3;
+----------+-------+--------+------+
| goods_id | ap_id | result | area |
+----------+-------+--------+------+
| 517149   | 1     | online | NULL |
| 536125   | 1     | online | NULL |
| 546407   | 1     | online | NULL |
+----------+-------+--------+------+
3 rows in set (0.00 sec)

#两表查询也可以使用多层select语句 。
mysql> select goods_id from hs_goods_esl_pr where ap_id = (select id from hs_ap where id = 1) limit 3; 
+----------+
| goods_id |
+----------+
| 517149   |
| 536125   |
| 546407   |
+----------+
3 rows in set (0.00 sec)s

#倒序查询
mysql> select *from hs_ap order by id desc limit 1;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 9  | online | 1    | 1    | NULL | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+------+---------------------+---------------------+
1 row in set (0.00 sec)


#中间使用limit语句
mysql> select * from hs_ap order by id limit 4;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
+----+--------+------+------+------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from hs_ap order by id limit 2,2;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
+----+--------+------+------+------+---------------------+---------------------+
2 rows in set (0.00 sec)


#取出时间最晚的3个id,先算出总数,然后取出最后4个 
mysql> select count(*) from hs_ap;
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.01 sec)

mysql> select id,last_time from hs_ap order by last_time limit  18,4;
+----+---------------------+
| id | last_time           |
+----+---------------------+
| 15 | 2016-03-18 04:03:58 |
| 5  | 2016-03-18 04:03:58 |
| 12 | 2016-03-18 04:03:59 |
| 16 | 2016-03-18 04:04:02 |
+----+---------------------+
4 rows in set (0.00 sec)

mysql> select * from (select id,last_time from hs_ap order by last_time desc limit 4 ) as t order by last_time;
+----+---------------------+
| id | last_time           |
+----+---------------------+
| 15 | 2016-03-18 04:03:58 |
| 19 | 2016-03-18 04:03:58 |
| 12 | 2016-03-18 04:03:59 |
| 16 | 2016-03-18 04:04:02 |
+----+---------------------+
4 rows in set (0.00 sec)

#创建新表,并且复制表结构
mysql> create table fuck like hs_ap;
Query OK, 0 rows affected (0.25 sec)

mysql> desc fuck;
+-------------+--------------+------+-----+---------------------+-----------------------------+
| Field       | Type         | Null | Key | Default             | Extra                       |
+-------------+--------------+------+-----+---------------------+-----------------------------+
| id          | varchar(50)  | NO   | PRI | NULL                |                             |
| result      | varchar(255) | YES  |     | NULL                |                             |
| ip          | varchar(20)  | YES  |     | NULL                |                             |
| port        | varchar(10)  | YES  |     | NULL                |                             |
| area        | varchar(255) | YES  |     | NULL                |                             |
| last_time   | timestamp    | YES  |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| create_time | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
+-------------+--------------+------+-----+---------------------+-----------------------------+
7 rows in set (0.00 sec)

mysql> desc hs_ap;
+-------------+--------------+------+-----+---------------------+-----------------------------+
| Field       | Type         | Null | Key | Default             | Extra                       |
+-------------+--------------+------+-----+---------------------+-----------------------------+
| id          | varchar(50)  | NO   | PRI | NULL                |                             |
| result      | varchar(255) | YES  |     | NULL                |                             |
| ip          | varchar(20)  | YES  |     | NULL                |                             |
| port        | varchar(10)  | YES  |     | NULL                |                             |
| area        | varchar(255) | YES  |     | NULL                |                             |
| last_time   | timestamp    | YES  |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| create_time | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
+-------------+--------------+------+-----+---------------------+-----------------------------+
7 rows in set (0.00 sec)

mysql> insert into  fuck select * from hs_ap;
Query OK, 22 rows affected (0.06 sec)
Records: 22  Duplicates: 0  Warnings: 0


mysql> select * from fuck;
+----+--------+------+------+--------+---------------------+---------------------+
| id | result | ip   | port | area   | last_time           | create_time         |
+----+--------+------+------+--------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL   | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL   | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL   | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL   | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL   | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL   | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |        | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 2  | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
| 20 | online | 1    | 1    | NULL   | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL   | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL   | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL   | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
| 3  | online | 1    | 1    | 酒区   | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
| 4  | online | 1    | 1    | NULL   | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
| 5  | online | 1    | 1    | NULL   | 2016-03-18 04:03:58 | 2015-10-26 13:41:32 |
| 6  | online | 1    | 1    | NULL   | 2016-03-18 04:03:31 | 2015-10-26 13:41:55 |
| 7  | online | 1    | 1    | NULL   | 2016-03-18 04:03:04 | 2015-10-26 13:42:04 |
| 8  | online | 1    | 1    | NULL   | 2016-03-18 04:03:57 | 2015-11-26 12:18:42 |
| 9  | online | 1    | 1    | NULL   | 2016-03-18 04:03:18 | 2015-11-26 12:19:30 |
+----+--------+------+------+--------+---------------------+---------------------+
22 rows in set (0.01 sec)

mysql> insert into fuck select * from hs_ap where id > 10 ;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from fuck;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
| 14 | online | 1    | 1    | NULL | 2016-03-18 04:03:01 | 2015-11-26 12:19:32 |
| 15 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:18:40 |
| 16 | online | 1    | 1    |      | 2016-03-18 04:04:02 | 2015-11-26 12:18:49 |
| 17 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:12 |
| 19 | online | 1    | 1    | NULL | 2016-03-18 04:03:58 | 2015-11-26 12:19:07 |
| 20 | online | 1    | 1    | NULL | 2016-03-18 04:03:44 | 2015-11-26 12:19:04 |
| 21 | online | 1    | 1    | NULL | 2016-03-18 04:03:20 | 2015-11-26 12:18:59 |
| 22 | online | 1    | 1    | NULL | 2016-03-18 04:03:35 | 2015-11-26 12:19:18 |
| 23 | online | 1    | 1    | NULL | 2016-03-18 04:03:39 | 2015-11-26 15:58:37 |
+----+--------+------+------+------+---------------------+---------------------+
12 rows in set (0.00 sec)

#自定义列内容
mysql> create table suck ( id int not null auto_increment,
    -> primary key (id)
    -> )
    -> select id,last_time,create_time from hs_ap;
Query OK, 22 rows affected (0.31 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> select *from suck limit 4;
+----+---------------------+---------------------+
| id | last_time           | create_time         |
+----+---------------------+---------------------+
|  1 | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
|  2 | 2016-03-18 04:03:39 | 2015-10-26 13:41:29 |
|  3 | 2016-03-18 04:03:48 | 2015-10-26 13:42:09 |
|  4 | 2016-03-18 04:03:29 | 2015-10-26 13:41:47 |
+----+---------------------+---------------------+
4 rows in set (0.00 sec)


mysql> select * from hs_ap limit 5;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
| 12 | online | 1    | 1    | NULL | 2016-03-18 04:03:59 | 2015-11-26 12:19:03 |
| 13 | online | 1    | 1    | NULL | 2016-03-18 04:03:33 | 2015-11-26 12:18:46 |
+----+--------+------+------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> create table fuck select  id ,sum(id+ip) as suck from hs_ap group by id;
Query OK, 22 rows affected (0.28 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> select * from fuck limit 4;
+----+------+
| id | suck |
+----+------+
| 1  |    2 |
| 10 |   11 |
| 11 |   12 |
| 12 |   13 |
+----+------+
4 rows in set (0.00 sec)

#给新表设置列属性,增加自增和设置主键功能 
mysql> create table fuck (primary key (id)) select * from hs_ap;
Query OK, 22 rows affected (0.30 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> alter table fuck modify id int unsigned not null auto_increment;
Query OK, 22 rows affected (0.52 sec)
Records: 22  Duplicates: 0  Warnings: 0


# 创建临时表,退出后消失  
mysql> create temporary table dick select *from hs_ap limit 3;
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from dick;
+----+--------+------+------+------+---------------------+---------------------+
| id | result | ip   | port | area | last_time           | create_time         |
+----+--------+------+------+------+---------------------+---------------------+
| 1  | online | 1    | 1    | NULL | 2016-03-18 04:03:45 | 2015-10-26 13:41:40 |
| 10 | online | 1    | 1    | NULL | 2016-03-18 04:03:37 | 2015-11-26 12:19:30 |
| 11 | online | 1    | 1    | NULL | 2016-03-18 04:03:17 | 2015-10-24 02:06:10 |
+----+--------+------+------+------+---------------------+---------------------+
3 rows in set (0.01 sec)


mysql> create  temporary table hs_ap select * from hs_ap limit 3;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select count(*) from hs_ap;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> delete from hs_ap;
Query OK, 3 rows affected (0.02 sec)

mysql> select count(*) from hs_ap;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> drop table hs_ap;
Query OK, 0 rows affected (0.03 sec)

mysql> select count(*) from hs_ap;
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)

#显示数据库用的引擎 
mysql> show table status from hs_ap;
ERROR 1049 (42000): Unknown database 'hs_ap'
mysql> show table status from wm;
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| Name               | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options     | Comment |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| authorities        | InnoDB |      10 | Compact    |       6 |           2730 |       16384 |               0 |        32768 |         0 |           1475 | 2017-02-13 20:44:41 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| biz_counters       | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 20:44:42 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| biz_regional       | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2017-02-13 20:44:42 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| biz_shelf          | InnoDB |      10 | Compact    |      90 |            182 |       16384 |               0 |            0 |         0 |          27750 | 2017-02-13 20:44:42 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| biz_shelfcompare   | InnoDB |      10 | Compact    |      36 |            455 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 20:44:46 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| biz_shelfunit      | InnoDB |      10 | Compact    |    2835 |            127 |      360448 |               0 |            0 |         0 |         335846 | 2017-02-13 20:44:47 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| hs_abutment_record | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2017-02-13 20:46:30 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| hs_ap              | InnoDB |      10 | Compact    |      22 |            744 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 20:46:30 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_black_list      | InnoDB |      10 | Compact    |      67 |            244 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 20:46:32 | NULL        | NULL       | utf8_general_ci |     NULL |                    |         |
| hs_esl             | InnoDB |      10 | Compact    |   22342 |            118 |     2637824 |               0 |       442368 |   4194304 |           NULL | 2017-02-13 20:46:34 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_esl_category    | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 21:00:56 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_esl_position    | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2017-02-13 21:00:56 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_esl_theme       | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2017-02-13 21:00:57 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_exception       | InnoDB |      10 | Compact    |   28908 |             91 |     2637824 |               0 |            0 |   4194304 |          30228 | 2017-02-13 21:00:57 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_goods           | InnoDB |      10 | Compact    |   79439 |            363 |    28901376 |               0 |      2637824 |   6291456 |           NULL | 2017-02-13 21:18:57 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_goods_category  | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-02-13 22:12:52 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_goods_esl_pr    | InnoDB |      10 | Compact    |   20609 |             77 |     1589248 |               0 |      3653632 |   4194304 |          55383 | 2017-02-13 22:12:52 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_goods_position  | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2017-02-13 22:26:18 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
| hs_goods_record    | InnoDB |      10 | Compact    | 1570142 |            140 |   220954624 |               0 |            0 |   4194304 |        1680889 | 2017-02-13 22:26:19 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPACT |         |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
19 rows in set (0.00 sec)

#显示系统支持的字符集
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.01 sec)

#unicode 用2个字节
mysql> set @s = convert('abc' using ucs2);
Query OK, 0 rows affected (0.00 sec)

mysql> select length(@s), char_length(@s);
+------------+-----------------+
| length(@s) | char_length(@s) |
+------------+-----------------+
|          6 |               3 |
+------------+-----------------+
1 row in set (0.00 sec)

#utf8 可能只占用一个字节
mysql> set @s = convert('abc' using utf8);
Query OK, 0 rows affected (0.00 sec)

mysql> select length(@s), char_length(@s);
+------------+-----------------+
| length(@s) | char_length(@s) |
+------------+-----------------+
|          3 |               3 |
+------------+-----------------+
1 row in set (0.00 sec)

本文由一分快三全天精准计划发布于一分快三全天精准计划-操作系统,转载请注明出处:mysql 笔记1

关键词: