本文共 3025 字，大约阅读时间需要 10 分钟。

#创建用户

mysql> create user 'SQL'@'%' identified by '1234';Query OK, 0 rows affected (0.00 sec)

#给用户授权

mysql> GRANT ALL PRIVILEGES ON *.* TO 'SQL'@'%';Query OK, 0 rows affected (0.00 sec)

#修改用户密码

mysql> set password for 'SQL'@'%' =password("mysql");Query OK, 0 rows affected, 1 warning (0.00 sec)

#创建test 数据库

mysql> create database test;  Query OK, 1 row affected (0.01 sec)

#选择进入数据库

mysql> use test; Database changed #已进入表

#查看数据库中存在的表

mysql> show tables; Empty set (0.01 sec)

#创建数据库表stu

mysql> create table stu (name VARCHAR(20), sex CHAR(1), birth DATE, class VARCHAR(20));Query OK, 0 rows affected (0.01 sec)

#查看数据库表

mysql> show tables;+----------------+| Tables_in_test |+----------------+| stu            |+----------------+

#显示表的结构

mysql> describe stu;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(20) | YES  |     | NULL    |       || sex   | char(1)     | YES  |     | NULL    |       || birth | date        | YES  |     | NULL    |       || class | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+

#在 /data/ 中创建文本文件 “stu.txt” 写入内容，用定位符（tab）把值分开

[root@localhost ~]# vim /data/stu.txt lisi    f       2000-03-14      01zs      f       2000-04-18      01lili    m       2000-02-23      02linwu   m       2000-05-04      02#

#使用命令将文本文件 “stu.txt” 装载到stu表中

mysql> use test;mysql> load data local infile "/data/stu.txt" into table stu;mysql> select * from stu; #查询stu表+-------+------+------------+-------+| name  | sex  | birth      | class |+-------+------+------------+-------+| lisi  | f    | 2000-03-14 | 01    || zs    | f    | 2000-04-18 | 01    || lili  | m    | 2000-02-23 | 02    || linwu | m    | 2000-05-04 | 02    |+-------+------+------------+-------+

如出现以下情况，则说明 stu.txt 中存在空格，每个值之间都要用定位值（tab）分开

mysql> select * from stu;+----------------------+------+-------+-------+| name                 | sex  | birth | class |+----------------------+------+-------+-------+| lisi    f       2000 | NULL | NULL  | NULL  || zs      f       2000 | NULL | NULL  | NULL  || lili    m       2000 | NULL | NULL  | NULL  || linwu   m       2000 | NULL | NULL  | NULL  |+----------------------+------+-------+-------+

#删除表：drop table 表名;

mysql> drop table stu;

#出现装载错误

mysql>load data local infile "/data/stu.txt" into table stuERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

#检查一个全局系统变量 ‘local_infile’ 的状态:

mysql> show global variables like 'local_infile';+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile  | OFF   |+---------------+-------+

#若Value=OFF,说明不可用。使用以下方法启动

mysql> set global local_infile=1;

#然后退出quit 退出mysql。再次进入时，便会出现 Value=ON

mysql> show global variables like 'local_infile';+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile  | ON    |+---------------+-------+

转载地址：https://blog.csdn.net/weixin_43389777/article/details/112040864 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！