[hd@master ~]$ mysql -u hive -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.4.18-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hive |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.003 sec)
MariaDB [(none)]> use test
Database changed
MariaDB [test]> show tables;
Empty set (0.001 sec)
###设计一个通用的表，用来装不用统计的数据
MariaDB [test]> CREATE TABLE `order_stat` (`id` int NOT NULL AUTO_INCREMENT,`rowkey` varchar(20) DEFAULT NULL, `province` varchar(25) DEFAULT NULL, `val` double DEFAULT NULL, KEY `id` (`id`)) ;
Query OK, 0 rows affected (0.004 sec)
MariaDB [test]> select * from order_stat;
Empty set (0.001 sec)
MariaDB [test]> insert into order_stat(rowkey,province,val) values('stat01','GD',32003.98);
Query OK, 1 row affected (0.001 sec)
MariaDB [test]>
MariaDB [test]>
MariaDB [test]> CREATE TABLE `order_stat2` (
-> `province` VARCHAR(25) DEFAULT NULL,
-> `val` DOUBLE DEFAULT NULL
-> )
-> ;
Query OK, 0 rows affected (0.003 sec)
MariaDB [test]>
MariaDB [test]> select * from order_stat2;
Empty set (0.000 sec)
MariaDB [test]>
MariaDB [(none)]> select * from test.order_stat2;
+--------------------------+--------------------+
| province | val |
+--------------------------+--------------------+
| 西藏自治区 | 489.72 |
| 辽宁省 | 107355.93000000007 |
| 浙江省 | 203126.96 |
| 广西壮族自治区 | 35140.09999999999 |
| 海南省 | 16828.18 |
| 河北省 | 106561.56000000004 |
| 福建省 | 37075.529999999984 |
| 湖南省 | 102929.22000000007 |
| 宁夏回族自治区 | 4804.92 |
| 天津 | 124564.24000000003 |
| 陕西省 | 59450.93 |
| 山西省 | 46568.799999999996 |
| 内蒙古自治区 | 36827 |
| 贵州省 | 32274.16 |
| 甘肃省 | 14294.76 |
| 四川省 | 188948.12000000005 |
| 湖北省 | 8581.7 |
| 广东省 | 227855.27999999968 |
| 黑龙江省 | 35058.28999999999 |
| 重庆 | 108975.65000000008 |
| 新疆维吾尔自治区 | 10112.9 |
| 山东省 | 175046.1300000001 |
| 河南省 | 90619.72000000003 |
| 吉林省 | 42040.92 |
| 青海省 | 2396.2 |
| 上海 | 544907.6299999994 |
| 江西省 | 36791.649999999994 |
| 安徽省 | 61378.67 |
| 北京 | 231055.48999999993 |
| 江苏省 | 227930.92999999985 |
| 云南省 | 75769.32000000002 |
+--------------------------+--------------------+
31 rows in set (0.000 sec)
3. MySQL中文乱码
使用MySQL的root用户对数据库进行修改以下设置
##修改整库的字符集
ALTER DATABASE <database_name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
##修改表的字符集
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MariaDB [(none)]> ALTER DATABASE test CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]>
MariaDB [(none)]> ALTER TABLE test.order_stat2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.010 sec)
Records: 0 Duplicates: 0 Warnings: 0
