Mysql数据导入导出、管理表记录

Posted on 2021-03-31 11:29 -=- 阅读(193) 评论(0) 收藏举报

一、数据导入导出

案例：

修改检索目录为/myload
将/etc/passwd文件导入db3库的user表里，并添加行号字段。
将db3库user表
所有记录导出, 存到/myload/user.txt 文件里。

步骤一：修改检索目录为/myload

1）修改配置文件，重启服务

 1 ]# mkdir  /myload  
 2 ]# chown  mysql  /myload 
 3 ]# vim  /etc/my.cnf
 4         [mysqld]
 5         secure_file_priv="/myload”
 6 :wq
 7 ]# systemctl  restart mysqld
 8 mysql> show  variables  like  “secure_file_priv”;  //查看
 9  +------------------+-----------------------+
10 | Variable_name    | Value                          |
11 +------------------+-----------------------+
12 | secure_file_priv   | /myload/   |
13 +------------------+-----------------------+     
14 Mysql>

2）新建db3库、user表

 1 [root@dbsvr1 ~]# mysql -u root –p123456
 2 mysql> CREATE DATABASE db3;
 3 create table db3.user(
 4         name char(50),
 5         password  char(1),      
 6         uid int,
 7         gid int,
 8         comment  char(150),     
 9         homedir char(50),       
10         shell   char(50)        
11 );
12 Query OK, 0 rows affected (0.70 sec)

步骤二：将/etc/passwd文件导入db3库的user表里，并添加行号字段。

1）拷贝文件到检索目录下

1 [root@dbsvr1 ~]# cp  /etc/passwd   /myload/

2）导入数据

1 [root@dbsvr1 ~]# mysql –uroot –ptarena
2 mysql> load data infile "/myload/passwd" into table db3.user
3        fields terminated by ":" lines terminated by "\n" ; //导入数据
4 mysql> select  * from  db3.user;  //查看表记录
5 mysql> alter table  db3.user
6     -> add
7     -> id  int primary key auto_increment first;  //添加行号id 字段
8 mysql> select  * from  db3.user; //查看表记录

步骤三：将db3库user表所有记录导出, 存到/myload/user.txt 文件里。

1）查询要导出的数据

1 mysql> select  * from  db3.user ；

导出数据

1 mysql> select  * from  db3.user  into outfile "/myload/user1.txt";

3）查看文件内容

1 ]# cat  /myload/user1.txt

二、管理表记录

步骤一：插入表记录

1）插入记录时，指定记录的每一个字段的值

这种情况下，不需要明确指出字段，但每条记录的值的顺序、类型都必须与表格结构向一致，否则可能无法正确插入记录。

比如，以下操作将向stu_info表插入3条表记录：

1 mysql> INSERT stu_info VALUES
2     -> ('Jim','girl',24),
3     -> ('Tom','boy',21),
4     -> ('Lily','girl',20);
5 Query OK, 3 rows affected (0.15 sec)
6 Records: 3  Duplicates: 0  Warnings: 0

完成插入后确认表记录：

1 mysql> SELECT * FROM stu_info;
2 +------+--------+-----+
3 | name | gender | age |
4 +------+--------+-----+
5 | Jim  | girl   |  24 |
6 | Tom  | boy    |  21 |
7 | Lily | girl   |  20 |
8 +------+--------+-----+
9 3 rows in set (0.00 sec)

2）插入记录时，只指定记录的部分字段的值

这种情况下，必须指出各项值所对应的字段；而且，未赋值的字段应设置有默认值或者有自增填充属性或者允许为空，否则插入操作将会失败。

比如，向stu_info表插入Jerry的年龄信息，性别为默认的“boy”，自动编号，相关操作如下：

1 mysql> INSERT INTO stu_info(name,age)
2     -> VALUES('Jerry',27);
3 Query OK, 1 row affected (0.04 sec)

类似的，再插入用户Mike的年龄信息：

1 mysql> INSERT INTO stu_info(name,age)
2     -> VALUES('Mike',21);
3 Query OK, 1 row affected (0.05 sec)

确认目前stu_info表的所有记录：

 1 mysql> SELECT * FROM stu_info;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Jim   | girl   |  24 |
 6 | Tom   | boy    |  21 |
 7 | Lily  | girl   |  20 |
 8 | Jerry | boy    |  27 |
 9 | Mike  | boy    |  21 |
10 +-------+--------+-----+
11 5 rows in set (0.00 sec

3）更新表记录时，若未限制条件，则适用于所有记录

将stu_info表中所有记录的age设置为10：

1 mysql> UPDATE stu_info SET age=10;
2 Query OK, 5 rows affected (0.04 sec)
3 Rows matched: 5  Changed: 5  Warnings: 0

确认更新结果：

 1 mysql> SELECT * FROM stu_info;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Jim   | girl   |  10 |
 6 | Tom   | boy    |  10 |
 7 | Lily  | girl   |  10 |
 8 | Jerry | boy    |  10 |
 9 | Mike  | boy    |  10 |
10 +-------+--------+-----+
11 5 rows in set (0.00 sec)

4）更新表记录时，可以限制条件，只对符合条件的记录有效

将stu_info表中所有性别为“boy”的记录的age设置为20：

1 mysql> UPDATE stu_info SET age=20
2     -> WHERE gender='boy';
3 Query OK, 3 rows affected (0.04 sec)
4 Rows matched: 3  Changed: 3  Warnings: 0

确认更新结果：

 1 mysql> SELECT * FROM stu_info;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Jim   | girl   |  10 |
 6 | Tom   | boy    |  20 |
 7 | Lily  | girl   |  10 |
 8 | Jerry | boy    |  20 |
 9 | Mike  | boy    |  20 |
10 +-------+--------+-----+
11 5 rows in set (0.00 sec)

5）删除表记录时，可以限制条件，只删除符合条件的记录

删除stu_info表中年龄小于18的记录：

1 mysql> DELETE FROM stu_info WHERE age < 18;
2 Query OK, 2 rows affected (0.03 sec)

确认删除结果：

1 mysql> SELECT * FROM stu_info;
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Tom   | boy    |  20 |
6 | Jerry | boy    |  20 |
7 | Mike  | boy    |  20 |
8 +-------+--------+-----+
9 3 rows in set (0.00 sec)

6）删除表记录时，如果未限制条件，则会删除所有的表记录

删除stu_info表的所有记录：

1 mysql> DELETE FROM stu_info;
2 Query OK, 3 rows affected (0.00 sec)

确认删除结果：

1 mysql> SELECT * FROM stu_info;
2 Empty set (0.00 sec)

三、匹配条件

步骤一：匹配条件

1）常用的表记录统计函数

查询stu_info表一共有多少条记录

1 mysql> SELECT count(*) FROM stu_info;
2 +----------+
3 | count(*) |
4 +----------+
5 |        5 |
6 +----------+
7 1 row in set (0.00 sec)

计算stu_info表中各学员的平均年龄、最大年龄、最小年龄：

1 mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
2 +----------+----------+----------+
3 | avg(age) | max(age) | min(age) |
4 +----------+----------+----------+
5 |  22.6000 |       27 |       20 |
6 +----------+----------+----------+
7 1 row in set (0.00 sec)

计算stu_info表中男学员的个数：

1 mysql> SELECT count(gender) FROM stu_info WHERE gender='boy';
2 +---------------+
3 | count(gender) |
4 +---------------+
5 |             3 |
6 +---------------+
7 1 row in set (0.00 sec)

2）字段值的数值比较

列出stu_info表中年龄为21岁的学员记录：

1 mysql> SELECT * FROM stu_info WHERE age=21;
2 +------+--------+-----+
3 | name | gender | age |
4 +------+--------+-----+
5 | Tom  | boy    |  21 |
6 | Mike | boy    |  21 |
7 +------+--------+-----+
8 2 rows in set (0.00 sec)

列出stu_info表中年龄超过21岁的学员记录：

1 mysql> SELECT * FROM stu_info WHERE age>21;
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jim   | girl   |  24 |
6 | Jerry | boy    |  27 |
7 +-------+--------+-----+
8 2 rows in set (0.00 sec)

列出stu_info表中年龄大于或等于21岁的学员记录：

 1 mysql> SELECT * FROM stu_info WHERE age>=21;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Jim   | girl   |  24 |
 6 | Tom   | boy    |  21 |
 7 | Jerry | boy    |  27 |
 8 | Mike  | boy    |  21 |
 9 +-------+--------+-----+
10 4 rows in set (0.00 sec)

列出stu_info表中年龄在20岁和24岁之间的学员记录：

 1 mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;
 2 +------+--------+-----+
 3 | name | gender | age |
 4 +------+--------+-----+
 5 | Jim  | girl   |  24 |
 6 | Tom  | boy    |  21 |
 7 | Lily | girl   |  20 |
 8 | Mike | boy    |  21 |
 9 +------+--------+-----+
10 4 rows in set (0.00 sec)

3）多个条件的组合

列出stu_info表中年龄小于23岁的女学员记录：

1 mysql> SELECT * FROM stu_info WHERE age < 23 AND gender='girl';
2 +------+--------+-----+
3 | name | gender | age |
4 +------+--------+-----+
5 | Lily | girl   |  20 |
6 +------+--------+-----+
7 1 row in set (0.00 sec)

列出stu_info表中年龄小于23岁的学员，或者女学员的记录：

 1 mysql> SELECT * FROM stu_info WHERE age < 23 OR gender='girl';
 2 +------+--------+-----+
 3 | name | gender | age |
 4 +------+--------+-----+
 5 | Jim  | girl   |  24 |
 6 | Tom  | boy    |  21 |
 7 | Lily | girl   |  20 |
 8 | Mike | boy    |  21 |
 9 +------+--------+-----+
10 4 rows in set (0.00 sec)

如果某个记录的姓名属于指定范围内的一个，则将其列出：

1 mysql> SELECT * FROM stu_info WHERE name IN
2     -> ('Jim','Tom','Mickey','Minnie');
3 +------+--------+-----+
4 | name | gender | age |
5 +------+--------+-----+
6 | Jim  | girl   |  24 |
7 | Tom  | boy    |  21 |
8 +------+--------+-----+
9 2 rows in set (0.00 sec)

4）使用SELECT做数学计算

计算1234与5678的和：

1 mysql> SELECT 1234+5678;
2 +-----------+
3 | 1234+5678 |
4 +-----------+
5 |      6912 |
6 +-----------+
7 1 row in set (0.00 sec)

计算1234与5678的乘积：

1 mysql> SELECT 1234*5678;
2 +-----------+
3 | 1234*5678 |
4 +-----------+
5 |   7006652 |
6 +-----------+
7 1 row in set (0.00 sec)

计算1.23456789除以3的结果：

1 mysql> SELECT 1.23456789/3;
2 +----------------+
3 | 1.23456789/3   |
4 +----------------+
5 | 0.411522630000 |
6 +----------------+
7 1 row in set (0.00 sec)

输出stu_info表各学员的姓名、15年后的年龄：

 1 mysql> SELECT name,age+15 FROM stu_info;
 2 +-------+--------+
 3 | name  | age+15 |
 4 +-------+--------+
 5 | Jim   |     39 |
 6 | Tom   |     36 |
 7 | Lily  |     35 |
 8 | Jerry |     42 |
 9 | Mike  |     36 |
10 +-------+--------+
11 5 rows in set (0.00 sec)

5）使用模糊查询，LIKE

以下划线 _ 匹配单个字符，% 可匹配任意多个字符。

列出stu_info表中姓名以“J”开头的学员记录：

1 mysql> SELECT * FROM stu_info WHERE name LIKE 'J%';
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jim   | girl   |  24 |
6 | Jerry | boy    |  27 |
7 +-------+--------+-----+
8 2 rows in set (0.00 sec)

列出stu_info表中姓名以“J”开头且只有3个字母的学员记录：

1 mysql> SELECT * FROM stu_info WHERE name LIKE 'J__';
2 +------+--------+-----+
3 | name | gender | age |
4 +------+--------+-----+
5 | Jim  | girl   |  24 |
6 +------+--------+-----+
7 1 row in set (0.00 sec)

6）使用正则表达式，REGEXP

列出stu_info表中姓名以“J”开头且以“y”结尾的学员记录：

1 mysql> SELECT * FROM stu_info WHERE name REGEXP '^J.*y$';
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jerry | boy    |  27 |
6 +-------+--------+-----+
7 1 row in set (0.00 sec)

效果等同于：

1 mysql> SELECT * FROM stu_info WHERE name Like 'J%y';
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jerry | boy    |  27 |
6 +-------+--------+-----+
7 1 row in set (0.00 sec)

列出stu_info表中姓名以“J”开头或者以“y”结尾的学员记录：

1 mysql> SELECT * FROM stu_info WHERE name REGEXP '^J|y$';
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jim   | girl   |  24 |
6 | Lily  | girl   |  20 |
7 | Jerry | boy    |  27 |
8 +-------+--------+-----+
9 3 rows in set (0.00 sec)

效果等同于：

1 mysql> SELECT * FROM stu_info WHERE name Like 'J%' OR name Like '%y';
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jim   | girl   |  24 |
6 | Lily  | girl   |  20 |
7 | Jerry | boy    |  27 |
8 +-------+--------+-----+
9 3 rows in set (0.00 sec)

7）按指定的字段排序，ORDER BY

列出stu_info表的所有记录，按年龄排序：

 1 mysql> SELECT * FROM stu_info ORDER BY age;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Lily  | girl   |  20 |
 6 | Tom   | boy    |  21 |
 7 | Jim   | girl   |  24 |
 8 | Jerry | boy    |  27 |
 9 +-------+--------+-----+
10 4 rows in set (0.00 sec)

因默认为升序（Ascend）排列，所以上述操作等效于：

 1 mysql> SELECT * FROM stu_info ORDER BY age ASC;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Lily  | girl   |  20 |
 6 | Tom   | boy    |  21 |
 7 | Jim   | girl   |  24 |
 8 | Jerry | boy    |  27 |
 9 +-------+--------+-----+
10 4 rows in set (0.00 sec)

若要按降序（Descend）排列，则将ASC改为DESC即可：

 1 mysql> SELECT * FROM stu_info ORDER BY age DESC;
 2 +-------+--------+-----+
 3 | name  | gender | age |
 4 +-------+--------+-----+
 5 | Jerry | boy    |  27 |
 6 | Jim   | girl   |  24 |
 7 | Tom   | boy    |  21 |
 8 | Lily  | girl   |  20 |
 9 +-------+--------+-----+
10 4 rows in set (0.00 sec)

8）限制查询结果的输出条数，LIMIT

查询stu_info表的所有记录，只列出前3条：

1 mysql> SELECT * FROM stu_info LIMIT 3;
2 +------+--------+-----+
3 | name | gender | age |
4 +------+--------+-----+
5 | Jim  | girl   |  24 |
6 | Tom  | boy    |  21 |
7 | Lily | girl   |  20 |
8 +------+--------+-----+
9 3 rows in set (0.00 sec)

列出stu_info表中年龄最大的3条学员记录：

1 mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;
2 +-------+--------+-----+
3 | name  | gender | age |
4 +-------+--------+-----+
5 | Jerry | boy    |  27 |
6 | Jim   | girl   |  24 |
7 | Tom   | boy    |  21 |
8 +-------+--------+-----+
9 3 rows in set (0.00 sec)

9）分组查询结果，GROUP BY

针对stu_info表，按性别分组，分别统计出男、女学员的人数：

1 mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender;
2 +--------+---------------+
3 | gender | count(gender) |
4 +--------+---------------+
5 | boy    |             3 |
6 | girl   |             2 |
7 +--------+---------------+
8 2 rows in set (0.00 sec)

列出查询字段时，可以通过AS关键字来指定显示别名，比如上述操作可改为：

1 mysql> SELECT gender AS '性别',count(gender) AS '人数'
2     -> FROM stu_info GROUP BY gender;
3 +--------+--------+
4 | 性别   | 人数   |
5 +--------+--------+
6 | boy    |      3 |
7 | girl   |      2 |
8 +--------+--------+
9 2 rows in set (0.00 sec)

四、MySQL管理工具

步骤一：准备软件的运行环境 lamp

1 [root@mysql6~]# rpm -q httpd  php  php-mysql    //检测是否安装软件包
2 未安装软件包 httpd 
3 未安装软件包 php 
4 未安装软件包 php-mysql
5 [root@mysql6~]# yum  -y   install   httpd     php    php-mysql  //装包
6 [root@mysql6~]# systemctl  start  httpd       //启动服务
7 [root@mysql6~]# systemctl  enable httpd      //设置开机自启
8 Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.

步骤二：测试运行环境

1 [root@mysql6~]# vim  /var/www/html/test.php     //编辑页面测试文件
2 [root@mysql6~]# cat /var/www/html/test.php      //查看页面测试文件
3 <?php
4 $x=mysql_connect("localhost","root","123456");
5 if($x){   echo "ok";    }else{    echo "no";    };
6 ?>
7 [root@mysql6~]# yum  -y   install  elinks     //安装测试网页工具
8 [root@mysql6~]#  elinks   --dump   http://localhost/test.php
9    Ok                                                //验证测试页面成功

步骤三：安装软件包

1）物理机传输解压包给虚拟机192.168.4.6

1 [root@room9pc桌面]# scp phpMyAdmin-2.11.11-all-languages.tar.gz 192.168.4.6:/root/
2 root@192.168.4.6's password: 
3 phpMyAdmin-2.11.11-a 100% 4218KB 122.5MB/s   00:00

2）虚拟机192.168.4.6解压phpMyAdmin-2.11.11-all-languages.tar.gz压缩包

1 [root@mysql6~]# tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/   //-C 表示改变至目录
2 [root@mysql6~]# cd /var/www/html/
3 [root@mysql6~]# mv phpMyAdmin-2.11.11-all-languages  phpmyadmin //改变目录名
4 [root@mysql6~]#  chown  -R  apache:apache  phpmyadmin/ //改变phpmyadmin目录权限

步骤四：修改软件的配置文件定义管理的数据库服务器

切换到部署后的phpmyadmin程序目录，拷贝配置文件，并修改配置以正确指定MySQL服务器的地址

1 [root@mysql6html]# cd  phpmyadmin
2 [root@mysql6 phpmyadmin]# cp   config.sample.inc.php   config.inc.php  
3 //备份主配置文件
4 [root@mysql6 phpmyadmin]# vim   config.inc.php  //编辑主配置文件
5 17 $cfg['blowfish_secret'] = 'plj123';     //给cookie做认证的值，可以随便填写
6 31 $cfg['Servers'][$i]['host'] = 'localhost';  //指定主机名，定义连接哪台服务器
7 :wq

步骤五：在客户端访问软件管理数据库服务器

1）在客户端访问软件,打开浏览器输入http://192.168.4.6/phpmyadmin(数据库服务器地址) 访问软件，如图所示，用户名是root，密码是123456

2）登入成功后，即可在授权范围内对MySQL数据库进行管理。

刷新页面返回顶部

-=-