一、修改表结构
修改表结构的指令格式:
mysql> alter table 库名.表名 执行动作
add
modeify
change
drop
rename
-----------------------------------------------------------------------------------
1.1 添加新字段add
#添加homeadd字段,默认值为bj,不指定位置时默认在最后
mysql> alter table myself add homeaddr char(50) default "bj";
#添加身份证号,位置放在首行
mysql> alter table myself add id char(20) first;
#添加QQ信息,位置放在name之后
mysql> alter table myself add qq char(11) after name;
mysql> desc myself;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | char(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| qq | char(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
| homeaddr | char(50) | YES | | bj | |
+----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
1.2 modify
(1) 修改字段类型
当要修改字段中有数据,且该数据的约束条件与要修改的约束条件矛盾时是不能做修改的.
#把name的字符类型修改为varchar(25)
mysql> alter table myself modify name varchar(25);
(2) 修改字段的位置
#把sex放到name的后面
mysql> alter table myself modify sex enum('boy','girl') after name;
mysql> desc myself;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | char(20) | YES | | NULL | |
| name | varchar(25) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
| qq | char(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| homeaddr | char(50) | YES | | bj | |
+----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
1.3 change 修改字段的名字
mysql> alter table myself change id num char(20);
mysql> alter table myself change name user char(20);
mysql> desc myself;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| num | char(20) | YES | | NULL | |
| user | char(20) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
| qq | char(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| homeaddr | char(50) | YES | | bj | |
+----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
1.4 删除表中的字段
#删除myself表中的user字段
mysql> alter table myself drop user;
mysql> select * from myself; #表中所有记录的这个字段都被删除
+------+------+------+------+----------+
| num | sex | qq | age | homeaddr |
+------+------+------+------+----------+
| NULL | boy | NULL | 20 | bj |
| NULL | NULL | NULL | 21 | bj |
+------+------+------+------+----------+
2 rows in set (0.00 sec)
mysql>
1.5 修改表名
mysql> alter table myself rename student;
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| student |
| t7 |
+---------------+
2 rows in set (0.00 sec)
mysql>
二 数据的导入/导出
2.1 数据导入导出环境准备
只有把要导入的文件放入到索引目录下,才可以执行数据的导入和导出
索引目录：存放导入、导出文件的目录
#执行导入/导出之前要先查看索引目录的位置，索引文件实际
#位置是以变量形式存放。查看该变量时必须以数据库管事员身份登陆
mysql> show variables;
mysql> show variables like "%file%"; #模糊查询找到secure_file_priv
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
#在mysql下面执行shell指令
mysql> system ls /var/lib/
#默认配置时,需要把文件放在/var/lib/mysql-files
mysql> system ls -ld /var/lib/mysql-files
drwxr-x--- 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files
#修改索引目录，自己定义数据导入和导出时文
[root@mysql50 ~]# mkdir /myload #件存放的目录。注意这个目录不要放在/root/下面
[root@mysql50 ~]# chown mysql /myload #因为mysql对/root/没有执行权限
[root@mysql50 ~]# vim /etc/my.cnf
#在[mysqld]下添加
secure_file_priv="/myload"
#重启服务
[root@mysql50 ~]# systemctl restart mysqld
----------------------------------------------------------------------------
2.2 数据的导入:
把系统文件的内容存储到表里,系统文件的内容要有一定的格式默认只有root才可以执行导入
建表要求:
字段分隔符要与文件一致
表字段类型和字段个数要与文件匹配
导入数据时要指定文件的绝对路径
命令格式:
mysql> load date infile "目录名/文件名"
into table 库名.表名
fields terminated by "分隔符" #字段分隔符
lines terminated by "
" #记录的分隔符
#把/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 char(150),
-> homedir char(100),
-> shell char(100)
-> );
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 | char(150) | YES | | NULL | |
| homedir | char(100) | YES | | NULL | |
| shell | char(100) | YES | | NULL | |
+----------+-----------+------+-----+---------+-------+
mysql>
mysql> system cp /etc/passwd /myload/
#执行导入数据之前先把要导入的文件放在/myload下
#注意用system关键字可以不用退出mysql执行shell命令
mysql> system ls /myload #查看是否拷贝成功,
passwd
mysql> load data infile "/myload/passwd" into table db3.user
-> fields terminated by ":" lines terminated by "
";
mysql> select * from user;
#为了便于查找方便,可以在表中添加一个id字段同时设置为
#自增长
mysql> alter table db3.user add id int primary key auto_increment first;
mysql> select * from user where id=1;
mysql> select * from user where id<=5;
----------------------------------------------------------------
2.3 数据的导出
表里的记录保存到系统文件里
导出数据行数由SQL查询决定
导出的是表记录,不包括字段名
自动创建存储数据的文件
存储数据文件,具有唯一性
命令格式:
mysql> select into outfile "目录名/文件名"
fields terminated by "分隔符"
lines terminated by "
";
mysql> select * from user where id<=5 into outfile "/myload/user.txt";
mysql> system cat /myload/user.txt
1 root x 0 0 root /root /bin/bash
2 bin x 1 1 bin /bin /sbin/nologin
3 daemon x 2 2 daemon /sbin /sbin/nologin
4 adm x 3 4 adm /var/adm /sbin/nologin
5 lp x 4 7 lp /var/spool/lpd /sbin/nologin
mysql>
#默认的分隔符是Tab ,行分隔符是
#导出到文件时指定分隔符为###
mysql> select * from user where id<=5 into outfile "/myload/user2.txt"
fields terminated by "###" ;
mysql> system cat /myload/usr2.txt
#导出到文件时指定分隔符为#,行分隔符为?
mysql> select name,shell,uid from user where id<=5 into outfile
"/myload/user3.txt" fields terminated by "#" lines terminated by "?";
mysql>