作者在 2016-07-24 20:33:29 发布以下内容
#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)
