服务mysql-创建数据库与表并导入导出数据

1、登陆后创建用户自己的数据库：

>create database test;

Database changed
MariaDB [test]> show tables; 
+----------------+
| Tables_in_test |
+----------------+
| admin          |
+----------------+
1 row in set (0.00 sec)

2、在自己的数据库中创建一个数据库表：

MariaDB [test]> drop table admin; #删除原来的测试表；
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables; 
Empty set (0.00 sec)

MariaDB [test]> create table users(user text, password text, host text)character set=utf8; #创建新的测试表users
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables; 
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

3、通过命令导入1个数据库文件和表格到数据库表中：

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> source /root/users.mdb; #导入1个数据库文件

MariaDB [test]> load data local infile '/root/mysql_xls/user2.txt' into table users character set utf8 fields terminated by ',' lines terminated by '\r\n';  #xls格式的文件需要先转换成txt或csv格式，如果第一行是列表可以添加 ignore 1 lines;            
Query OK, 2 rows affected (0.00 sec)                 
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> select * from users; 
+------------------+----------+-----------+
| user             | password | host      |
+------------------+----------+-----------+
| l1              | 123      | localhost |
| l2               | 321      | localhost |
+------------------+----------+-----------+
3 rows in set (0.00 sec)

4、将查询的数据导出到文件：

#mysql -uroot -p****** --default-character-set=utf8 -e"use mysql;select user,password,host from user" > /root/mysql_xls/user.xls