数据库03

一、数据的导入导出

1.1数据导入导出默认路径

查看路径：

mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.26 sec)

在MySQL登录状态下执行系统命令：在命令前加system关键字

所有导入导出的文件必须放入/var/lib/mysql-files中，不修改状态下这是默认路径

1.2修改默认路径

　　1)创建新的文件夹：mkdir /myload因为数据库建立时就自动生成了一个mysql的用户，这里要把这个修改路径的所有者变为mysql

　　　　chown mysql /myload

　　2)去数据库的配置文件中修改：

　　　　vim /etc/my.cnf

[mysqld]
secure_file_priv="/myload"

　　3)systemctl restart mysqld 重启服务

这是再执行：show variables like "secure_file_priv"（在MySQL服务下查看），可观察到改变后的路径生效

mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+

1.2 数据导入：把系统文件内容存储到数据库服务器的表里(这里以把系统用户信息放入表中为例)

　　1） 要把将要导出的文件放入/myload中

　　　　cp /etc/passwd /myload

　　2）依据将要导入的文件格式在数据库中建库建表

mysql> create table db3.user(
    -> username char(50),
    -> password char(1),
    -> uid int,
    -> gid int,
    -> comment char(150),
    -> address char(50),
    -> shell char(50));

　　3）使用命令导入：

load data infile "目录名/文件名" into table 库名.表名 fields terminated  by "列分隔符"  lines terminated by " ";

在运行上述命令格式时，出现了以下报错：

ERROR 13 (HY000): Can't get stat of '/myload/passwd' (Errcode: 13 - Permission denied)

百度后使用以下命令：load  data默认读的是服务器上的文件，加上local参数后，可以实现

load data local infile "/myload/passwd " into table db3.user fields terminated by ":" lines terminated by " ";

之后 select *from 库名.表名 即可查看到导入的信息

注意事项：—字段分隔符要与文件一致

　　　　　—表字段类型和字段个数要与文件匹配

　　　　　—导入数据时使用指定文件的绝对路径

1.3数据导出：

命令格式：

　　格式1：select into outfile "目录名/文件名"；

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

　　查看：

mysql> system cat /myload/user1.txt
root    x    0    0    root    /root    /bin/bash
bin    x    1    1    bin    /bin    /sbin/nologin
daemon    x    2    2    daemon    /sbin    /sbin/nologin
adm    x    3    4    adm    /var/adm    /sbin/nologin
lp    x    4    7    lp    /var/spool/lpd    /sbin/nologin
sync    x    5    0    sync    /sbin    /bin/sync
shutdown    x    6    0    shutdown    /sbin    /sbin/shutdown
halt    x    7    0    halt    /sbin    /sbin/halt
mail    x    8    12    mail    /var/spool/mail    /sbin/nologin
operator    x    11    0    operator    /root    /sbin/nologin
games    x    12    100    games    /usr/games    /sbin/nologin
ftp    x    14    50    FTP User    /var/ftp    /sbin/nologin
nobody    x    99    99    Nobody    /    /sbin/nologin

　　格式2：select into outfile "目录名/文件名"   fields terminated by "分隔符"；

mysql> select *from db3.user into outfile "/myload/user2.txt" fields terminated by ":";    ##指明列与列之间增加：

查看：

mysql> system cat /myload/user2.txt
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin

　　格式3：select into outfile "目录名/文件名"  fields terminated by "分隔符"  lines terminated by " ";

mysql> select *from db3.user into outfile "/myload/user3.txt" fields terminated by ":" lines terminated by " ";  ###结果和格式2 没有区别

mysql> system cat /myload/user3.txt
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin

注意事项：

　　　　—导出的数据行数由sql查询决定

　　　　—导出的是表记录，不包括字段名（只是表中的数据）

　　　　—自动创建存储数据的文件（指定名字就好，不必实现创建）

　　　　—存储数据文件，具有唯一性

导入过程中一直报错Can't create/write to file '/myload/user1.txt' (Errcode: 13 - Permission denied)找了半天才发现是SElinux没有关（心累ing上面的local可能也是这个原因）

###########################################################################################################################

二、管理表记录

mysql> select *from  T3;
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| bob  |       |   19 | music |     67 |
| lucy | 3     |   19 | music |     78 |
| anmy | 2     |   19 | music |     91 |
| lili | 1     |   19 | music |     86 |
+------+-------+------+-------+--------+

　　2.1增加表记录

　　1）增加一条记录，给所有字段赋值

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

　　insert into T3 values("kangkang",1,16,eat,60);

　　2）给指定字段赋值

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

　　mysql> insert into T3(name,class,grades) values("lulu",2,78);

　 当然也可以插多条表记录，逗号隔开。

　　2.2更新记录表

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

　　mysql> update T3 set age=18;

mysql> select *from  T3;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   18 | music |     78 |
| anmy     | 2     |   18 | music |     91 |
| lili     | 1     |   18 | music |     86 |
| kangkang | 1     |   18 | eat   |     60 |
| lulu     | 2     |   18 | music |     78 |
+----------+-------+------+-------+--------+

　　2）可以限制条件：

　　mysql> update T3 set age=24 where name="lili";    ####where指定限制条件

mysql> select *from T3;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   18 | music |     78 |
| anmy     | 2     |   18 | music |     91 |
| lili     | 1     |   24 | music |     86 |
| kangkang | 1     |   18 | eat   |     60 |
| lulu     | 2     |   18 | music |     78 |
+----------+-------+------+-------+--------+

删除的时候也可以限定条件：mysql> delete from T3 where age=24;

mysql> select *from T3;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   18 | music |     78 |
| anmy     | 2     |   18 | music |     91 |
| kangkang | 1     |   18 | eat   |     60 |
| lulu     | 2     |   18 | music |     78 |
+----------+-------+------+-------+--------+

　　2.3查询记录表select

　　1）select *from 库名.表名;  ###查询表的所有数据内容

　　2）select 字段名，字段名 from 库名.表名 ；####查固定字段

mysql> select name ,likes from T3;
+----------+-------+
| name     | likes |
+----------+-------+
| bob      | music |
| lucy     | music |
| anmy     | music |
| kangkang | eat   |
| lulu     | music |
+----------+-------+

　　3）与where条件限制连用

  select */字段名，字段名 from 表名 where 限制条件

mysql> select name,class,age from T3 where grades=78;
+------+-------+------+
| name | class | age  |
+------+-------+------+
| lucy | 3     |   18 |
| lulu | 2     |   18 |
+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from T3 where grades=78;
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lucy | 3     |   18 | music |     78 |
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+
2 rows in set (0.00 sec)

　　— *代表所有字段

　　—查看当前库表时，库名可以省略

　　—指定的字段是列

　　—指定的条件限制是行

3、匹配条件　　

3.1聚集函数

count 字段名　　统计字段值的个数

mysql> select count(name)from T3;
+-------------+
| count(name) |
+-------------+
|           5 |
+-------------+

sum 字段名　　统计字段总和

max 字段名　　字段值最大

min 字段名　　字段值最小

avg 字段名　　字段值的平均值

mysql> select sum(age),avg(grades),max(grades),min(grades)from T3;
+----------+-------------+-------------+-------------+
| sum(age) | avg(grades) | max(grades) | min(grades) |
+----------+-------------+-------------+-------------+
|       90 |     74.8000 |          91 |          60 |
+----------+-------------+-------------+-------------+

可以与where连用

3.2普通匹配条件

　　1、数值比较（字段类型必须是数值类型）

　　> 　　<　　=　　>=　　<=　　！=

ysql> select * from T3 where age>18 and grades>70;
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lucy | 3     |   24 | music |     78 |
+------+-------+------+-------+--------+
1 row in set (0.00 sec)

　　2、字符比较/匹配空/匹配非空（字段类型必须为字符型）

　　=　　！=　　is null　　is not null

　　3、逻辑匹配（多个判断条件时使用）

　　and(逻辑与)   or（逻辑或）　　！　　not(逻辑非)　　

　　4、范围匹配/去重显示

　　（匹配范围内的任意一个值）

　　in（在……里）　　not in (不在……里)　　between……and (在……之间)　　distinct （去重显示）

mysql> select *from T3 where name in ('bob','lili','fangfang');
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| bob  |       |   18 | music |     67 |
+------+-------+------+-------+--------+

3.3高级匹配

　　1）模糊查询

　　where 字段 like '通配符'，not like 可以显示不以……的

　　通配符： %表示0~n个字符

　　　　　　_表示一个字符

　　　　　　可以把通配符放在不同的位置来作为匹配

mysql> select *from T3 where name like "l%";
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lucy | 3     |   24 | music |     78 |
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select *from T3 where name like "l_l_";
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+

　　2）正则表达式regexp

　　where 字段 regexp '正则表达式'

　　正则表达式符号：^ （开头）$（结尾） .（英文中.表示一个字节，中文...表示一个汉字） | *

　　###查询姓名以 l  开头或 y 结尾的

mysql> select * from T3 where name regexp '^l|y$';
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lucy | 3     |   24 | music |     78 |
| anmy | 2     |   18 | music |     91 |
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+

mysql> select * from T3 where name regexp '.u'; ####找姓名中的u前面一个是字符的
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lucy | 3     |   24 | music |     78 |
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+

　　找到 字母 l 和字母  l 之间还有一个字符的姓名

mysql> select *from T3;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   24 | music |     78 |
| anmy     | 2     |   18 | music |     91 |
| kangkang | 1     |   18 | eat   |     60 |
| lulu     | 2     |   18 | music |     78 |
| llu      | 2     |   17 | game  |     88 |
| luul     | 2     |   21 | game  |     98 |
+----------+-------+------+-------+--------+
7 rows in set (0.00 sec)

mysql> select * from T3 where name regexp 'l.l';
+------+-------+------+-------+--------+
| name | class | age  | likes | grades |
+------+-------+------+-------+--------+
| lulu | 2     |   18 | music |     78 |
+------+-------+------+-------+--------+

　　3）四则运算

　　+　　-　　*　　/　　%　　（）提高优先级

）使用SELECT做数学计算

计算1234与5678的和：

mysql> SELECT 1234+5678;
+-----------+
| 1234+5678 |
+-----------+
|      6912 |
+-----------+
1 row in set (0.00 sec)
计算1234与5678的乘积：

mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
|   7006652 |
+-----------+
1 row in set (0.00 sec)
计算1.23456789除以3的结果：

mysql> SELECT 1.23456789/3;
+----------------+
| 1.23456789/3   |
+----------------+
| 0.411522630000 |
+----------------+
1 row in set (0.00 sec)
输出stu_info表各学员的姓名、15年后的年龄：

mysql> SELECT name,age+15 FROM stu_info;
+-------+--------+
| name  | age+15 |
+-------+--------+
| Jim   |     39 |
| Tom   |     36 |
| Lily  |     35 |
| Jerry |     42 |
| Mike  |     36 |
+-------+--------+
5 rows in set (0.00 sec)

　　4）查询结果排序（通常都是数值类型的字段）

　　sql 查询 order by 字段名 [asc | desc ];

　　---asc 升序排列

　　—desc 降序排列

ysql> select * from T3 order by grades asc;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| kangkang | 1     |   18 | eat   |     60 |
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   24 | music |     78 |
| lulu     | 2     |   18 | music |     78 |
| llu      | 2     |   17 | game  |     88 |
| anmy     | 2     |   18 | music |     91 |
| luul     | 2     |   21 | game  |     98 |
+----------+-------+------+-------+--------+

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

mysql> select * from T3 order by grades asc limit 4;
+----------+-------+------+-------+--------+
| name     | class | age  | likes | grades |
+----------+-------+------+-------+--------+
| kangkang | 1     |   18 | eat   |     60 |
| bob      |       |   18 | music |     67 |
| lucy     | 3     |   24 | music |     78 |
| lulu     | 2     |   18 | music |     78 |
+----------+-------+------+-------+--------+
4 rows in set (0.00 sec)

　　6）分组查询结果，GROUP BY（通常都是字符类型）

mysql> select likes,count(likes) from T3 group by likes;
+-------+--------------+
| likes | count(likes) |
+-------+--------------+
| music |            4 |
| game  |            2 |
| eat   |            1 |
+-------+--------------+
3 rows in set (0.00 sec)

　　7）AS指定别名

mysql> select name as "姓名" from T3;
+----------+
| 姓名     |
+----------+
| anmy     |
| bob      |
| kangkang |
| llu      |
| lucy     |
| lulu     |
| luul     |
+----------+

总结：所有想要在屏幕上显示的内容都可以在select后指定

　　8）条件过滤： having

　　

mysql> select name from T3 where grades>78 having name="llu";
+------+
| name |
+------+
| llu  |
+------+

聚合函数不可以与where连用，但是可以与having连用，having常与group by 连用

如查找平均分超过80分的班级，并显示班级和平均分

mysql> select class,avg(grades)from T3 group by class having avg(grades>80);
+-------+-------------+
| class | avg(grades) |
+-------+-------------+
| 2     |     88.7500 |
+-------+-------------+
1 row in set (0.00 sec)

4.MySQL的管理方式

部署LAMP+phpMyAdmin平台

步骤一：准备软件的运行环境 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(数据库服务器地址) 访问软件，如图-1所示，用户名是root，密码是123456

图-1

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