Mysql数据库学习（三）数据导入导出、数据查询

0基础数据库系统学习（三）
数据导入导出 、 管理表记录 、 匹配条件 、 MySQL管理工具
管理工具提取码和连接：链接：https://pan.baidu.com/s/1bowgl2q_ozm-wyJqqxSbew
提取码：tty2

1 数据导入导出（批量处理数据）
1.1 检索目录：存放导入导出数据时文件存放的目录下
查看默认的检索目录
mysql> show variables like “secure_file_priv”;
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
修改检索目录
】# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
:wq

[root@localhost ~]# mkdir /myload
[root@localhost ~]# chown mysql /myload/
[root@localhost ~]# ls -ld /myload
drwxr-xr-x. 2 mysql root 6 1月 18 09:10 /myload
[root@localhost ~]#
[root@localhost ~]# setenforce 0
[root@localhost ~]#
[root@localhost ~]# systemctl restart mysqld

mysql> show variables like “secure_file_priv”;
±-----------------±---------+
| Variable_name | Value |
±-----------------±---------+
| secure_file_priv | /myload/ |
±-----------------±---------+
1 row in set (0.00 sec)

1.2 数据导入 把系统文件里内容存储数据库服务器的表里
数据导入命令格式 load data
MySQL> load data infile “/目录名/文件名” into table 库名.表名
fields terminated by “分隔符” lines terminated by “\n”;

导入数据的步骤：
1 建库
2 建表（根据要导入的数据创建表头）
3 把系统文件拷贝的检索目录下
4 执行导入数据的命令
5 查看数据
导入数据的注意事项？
例子：把/etc/passwd文件的内容存储到db3库下的user表里
mysql> create database db3;
mysql> use db3;
mysql> create table user(
-> name char(50), password char(1), uid int , gid int ,
-> comment varchar(150) , homedir char(100) , shell char(60) );

mysql> desc user;
±---------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±-------------±-----±----±--------±------+
| name | char(50) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(100) | YES | | NULL | |
| shell | char(60) | YES | | NULL | |
±---------±-------------±-----±----±--------±------+

mysql> system cp /etc/passwd /myload/
mysql> system ls /myload
mysql> load data infile “/myload/passwd” into table db3.user
fields terminated by “:” lines terminated by “\n” ;

mysql> select * from db3.user;

给表中的行添加行号 字段 ， 便于对表的管理
alter table db3.user add id int primary key auto_increment first;

select * from db3.user;

select * from db3.user where id <= 3;

select * from db3.user where id = 1;

1.3 数据导出 把储数据库服务器的表里保存到系统文件里
命令格式1
select 查询命令 into outfile “/目录名/文件名”;
mysql> select * from db3.user where id = 1 into outfile “/myload/a.txt”;
命令格式2
select 查询命令 into outfile “/目录名/文件名” fields terminated by “符号”;
mysql> select * from db3.user where id = 2 into outfile “/myload/b.txt” fields terminated by “:” ;
命令格式3
select 查询命令 into outfile “/目录名/文件名” fields terminated by “符号” lines terminated by “符号” ；
mysql> select name,uid,shell from db3.user where id <= 3 into outfile “/myload/d.txt” fields terminated by “:” lines terminated by “###”;

2 管理表记录 ( 添加新行 查询数据 更新数据 删除行)
2.1 添加表记录（向表里添加新行）分为以下几种情况
添加1行给所有列赋值
mysql> insert into db3.user values(30,“bob”,“x”,2009,2009,“test user”,"/home/bob","/bin/bash");
添加多行给所有列赋值
mysql> insert into db3.user values (42,“bob”,“x”,2009,2009,“test user”,"/home/bob","/bin/bash"),(32,“bob”,“x”,2009,2009,“test user”,"/home/bob","/bin/bash");
添加1行给某几列列赋值
mysql> insert into db3.user(name,uid,homedir) values(“jim”,3000,"/home/jim");
添加多行给某几列列赋值
mysql> insert into db3.user(name,uid,homedir) values(“tom”,3001,"/home/tomm"),(“lucy”,3002,"/home/lucy");

2.2 更新表记录 (修改表行里的列)
update 库.表 set 列名=值 , 列名=值; #不加条件的修改,批量修改

update 库.表 set 列名=值 , 列名=值 where 条件表达式； #加的条件的修改，只修改与条件匹配行里的列

mysql> update db3.user set password=“a” ;
mysql> update db3.user set password=“x” where name=“root”;

2.3 查找表记录 （查看表里的行）
命令格式
select 字段名列表 from 库.表 where 匹配条件；
select * from db3.user; #查看所有行所有列

select * from db3.user where uid <=5; #执行查看与条件匹配行的所有列
select name,uid,gid from db3.user where uid <=5; #只查看与条件匹配的行中 指定列的值

2.4 删除表记录 (删除表里的行)
命令格式
delete from 库.表 where 匹配条件；
delete from db1.stuinfo ; #不加条件是删除表里的所有行
delete from db3.user id > 20 ; #加条件 只删除与条件匹配的行

3 匹配条件（查询select 更新update 删除delete ）
3.1 基本条件
1 数值比较 符号 = != > >= < <=
select name from db3.user where uid=15;
select name , uid from db3.user where id <= 3;
select name, uid , gid from db3.user where uid = gid ;
select name , uid ,gid from db3.user where uid != gid ;

2 字符比较 符号 = !=
mysql> select name,shell from db3.user where shell = “/bin/bash”;
mysql> select name,shell from db3.user where shell != “/bin/bash”;
mysql> select name from db3.user where name=“httpd”;
Empty set (0.00 sec)

mysql> select name from db3.user where name=“mysql”;

3 匹配空 is null （没有数据）
4 非空 is not null （有数据）

mysql> select id,name from db3.user where name is null ;
Empty set (0.00 sec)
mysql> select id,name from db3.user where name is not null ;

5 逻辑判断（有多个判断条件）
逻辑与 and 有多个判断条件必须都成立
逻辑或 or 有多个判断条件某1个条件成立即可
逻辑非 not ! 取反

学历 本科 and 性别 女 and 英语水平 四级 and 颜值 高
学历 本科 or 性别 女 or 英语水平 四级 or 颜值 高

mysql> select name ,uid ,gid from db3.user where uid>=10 and uid<=20 and gid>100;
mysql> select name ,uid ,gid from db3.user where uid>=10 and uid<=20 ;
mysql> select name from db3.user where name=“root” or name=“mysql” or name=“bin”;
mysql> select name,uid from db3.user where name=“root” and uid=0;

6 ( ) 提高执行顺序

select name , uid from db3.user where name=“root” and uid=0 or uid=1 ;
select name , uid from db3.user where uid=1 or name=“root” and uid=0 ;
mysql> select name , uid from db3.user where name=“root” and (uid=0 or uid=1 );
select name,uid,gid from user where uid >= 10 and uid <=20 and gid > 10 ;
7 范围匹配
in
not in
between … and …

select name from db3.user where name in (“root”,“bin”,“sync”,“apche”);
select name,uid from db3.user where uid in (3,6,9,11,7);
select shell from db3.user where shell not in ("/bin/bash","/sbin/nologin");
mysql> select name,uid from db3.user where uid between 0 and 5 ;

3.2 高级匹配条件
模糊匹配 like

where 字段名 like ‘通配符’

_ ：表示1个字符

%：表示0~n个字符

select name from db3.user where name like ‘%a%’;（匹配name字段里字符中有a的数据）
select name from db3.user where name like ‘____’;（匹配name字段里四个字符的数据）
select name from db3.user where name like ‘a’;（匹配三个字符中间是a的数据）
select name from db3.user where name like ‘%’;（匹配至少两个字符的数据）

正则匹配 regexp
where 字段名 regexp ‘正则表达式’

元字符：^ $ . | [ ] *
1 select name from db3.user where name regexp ‘^a’;
（以a开头的数据）
2 select name from db3.user where name regexp ‘t${}^{\prime };（以t结尾的数据）3selectnamefromdb3.userwherenameregex{p}^{\prime a}t$’;
（字符为at的数据）
4 select name from db3.user where name regexp ‘^a|t${}^{\prime };（字符是以a开头或者以t结尾的数据都进行输出）5selectnamefromdb3.userwherenameregex{p}^{\prime a}.\ast t$’;
（匹配以a开头以t结尾中间任意字符可以是0个的数据）
6 select name from db3.user where name regexp ‘[0-9]’;
（匹配字符中有数字的数据）
7 select name from db3.user where name regexp ‘¹’;
（匹配以数字开头的数据）
8 select name,uid from db3.user where uid regexp ‘…’;
（匹配至少任意三个连续字符的数据）
9 select name,uid from db3.user where uid regexp ‘^…$’;
（匹配任意三个连续字符开头的数据）

	四则运算(做数学计算) + - * / %    

1 update db3.user set uid=uid+1 where id <= 3;

2 update db3.user set uid=uid-1 where id <= 3;

3 select name,uid from db3.user where uid % 2 = 0;

4 select name,uid from db3.user where uid % 2 != 0;

5 select name,uid,gid , (uid+gid)/2 pjf from db3.user where uid>=5 and uid <=10;

6 select name,uid,gid , (uid+gid)*2 pjf from db3.user where uid>=5 and uid <=10;

7 alter table db3.user add age int default 21 after name ;

8 select name, age , 2021-age csnf from db3.user where name=“root”;

3.3 操作查询结果(把select 命令查询出来的数据，再使用命令处理一下)
1 排序（排队）order by 字段名 排序的方式
mysql> select name,uid from db3.user where id >= 10 and id<=20 ;

mysql> select name,uid from db3.user where id >= 10 and id<=20 order by uid;
（uid从小到大排序）
mysql> select name,uid from db3.user where id >= 10 and id<=20 order by uid desc;
（uid从大到小排序）

2 分组(把类似的数据放一块)group by 字段名；
mysql> select shell from db3.user；
mysql> select shell from db3.user group by shell;

3 过滤查询结果(在查找出来的数据里 再找一下想查找的数据)
having 条件；

mysql> select name,uid from db3.user where id <=15 having name=“zhangsan”;
Empty set (0.00 sec)

mysql> select name,uid from db3.user where id <=15 having name in (“root”,“bin”);

select name,uid from db3.user where id <=15 and name=“zhangsan”;
select name,uid from db3.user where id <=15 and name in (“root”,“bin”);

4 聚集函数的使用(mysql服务提供的对数据在统计的命令)

mysql> select uid from db3.user;
mysql> select avg(uid) from db3.user;（平均）
mysql> select sum(uid) from db3.user;（求和）
mysql> select min(uid) from db3.user;（最小）
mysql> select max(uid) from db3.user;（最大）
mysql> select min(uid) from db3.user where id <= 10 ;

mysql> select count(*) from db3.user;（求表中一共有多少行count求总数）

mysql> select name from db3.user where shell = “/sbin/nologin”;
mysql> select count(name) from db3.user where shell = “/sbin/nologin”;

mysql> select shell from db3.user group by shell;
mysql> select count(shell) from db3.user group by shell;

5 限制查询结果显示的行数（不限制的话会输出所有的查询结果）

limit 数字； 只显示查询结果的前几行
mysql> select * from db3.user；
mysql> select * from db3.user limit 1;
mysql> select * from db3.user limit 2;（显示前两行）
mysql> select name,shell from db3.user where uid <= 10 ;

mysql> select name,shell from db3.user where uid <= 10 limit 5;

               limit  数字1，数字2；显示指定范围的行

	  数字1 从查询结果的第数字1+1行开始显示
                              数字2 一共显示几行

select name,comment,shell from db3.user ;

select name,comment,shell from db3.user limit 0,5;（从第一行开始往下显示5行）

select name,comment,shell from db3.user limit 1,3;（从第二行开始往下显示三行）

4 安装图形管理工具 （在数据库服务器安装图形软件，访问图形软件对数据做管理）
4.1 连接MySQL服务的方式：命令行 安装图形软件 脚本
4.2 图形软件都有哪些？

4.3 在数据库服务器50主机 安装图形软件 phpmyadmin
1 部署LAMP 或LNMP 环境
93 rpm -q httpd
94 rpm -q php
95 rpm -q php-mysql
96 yum -y install httpd php php-mysql
97 systemctl start httpd
98 vim /var/www/html/a.php

<?php $x=99; echo $x; ?>

:wq
[root@localhost ~]# curl http://localhost/a.php
99

2 安装软件 phpMyAdmin-2.11.11-all-languages.tar.gz
108 tar -zxvf /var/ftp/pub/phpMyAdmin-2.11.11-all-languages.tar.gz
109 ls
110 mv phpMyAdmin-2.11.11-all-languages/ /var/www/html/admin
111 ls /var/www/html/
112 ls /var/www/html/admin/
113 ls /var/www/html/admin/index.php

3 修改软件的主配置文件 (告诉软件管理本机的数据库服务)
[root@localhost ~]# cd /var/www/html/admin/

[root@localhost admin]# ls config.sample.inc.php
config.sample.inc.php
[root@localhost admin]# cp config.sample.inc.php config.inc.php

[root@localhost admin]#
[root@localhost admin]# sed -n ‘31p’ config.inc.php
$cfg{[}^{\prime }Server{s}^{\prime }][$i][‘host’] = ‘localhost’;

[root@localhost admin]# vim +17 config.inc.php
$cfg[‘blowfish_secret’] = ‘plj123’;
:wq

      4 客户端访问软件 对数据库做管理
	客户端打开本机的浏览器，
            在地址栏输入访问的地址 http://192.168.4.50/admin/index.php

  5 输入连接的 用户名root  和 密码123456

---

1. 0-9 ↩︎