需求：
1：安装mysql 5.6数据库
2：建studb库，并建stuinfo学生表，要求建字段stu_id name age sex class dizhi，建表时添加普通，唯一索引
3：在表中添加新字段名id为主键索引，并让id值自动增长，修改dizhi为home
4：新建stuchengji表，建表时设置外键关联stuinfo
5：为stuinfo表插入数据
6：导入文件passwd内容到新表passwd，导出 stuinfo表数据到/bak
实现步骤：
1：安装mysql程序，启动服务，设置为开机启动，备份主配置文件
[root@localhost ~]# rpm -ivh MySQL-server-5.6.15-1.el6.x86_64.rpm \
>MySQL-client-5.6.15-1.el6.x86_64.rpm
>MySQL-devel-5.6.15-1.el6.x86_64.rpm
[root@localhost ~]#service mysql start
[root@localhost ~]#chkconfig mysql on
[root@localhost ~]#cp /etc/{my.cnf,my.cnf.bak}
2：本机登录数据库，并改密码
[root@localhost ~]# cat .mysql_secret
# The random password set for the root user (local time): w3GQUyIV //安装时随机生成的密码
[root@localhost ~]#mysql -hlocalhost -uroot -pw3GQUyIV //第一次登录时用root的随机密码登录
mysql>SET PASSWORD FOR ‘root’@’localhost’=PASSWORD(’123456’); //第一次必须改密码否则没操作权
3：建立studb库
mysql> create database studb;
4：进入studb库建立stuinfo表
mysql> use studb; //进入studb数据库操作
mysql> create table stuinfo( //建立stuinfo表
-> stu_id int(13),
-> name varchar(15) not null,
-> age tinyint(2) not null default 22,
-> sex enum("boy","girl") not null default “boy”,
-> class varchar(8) default "NSD1404",
-> dizhi varchar(30),
-> index(name), //给字段name建普通索引
-> unique(stu_id)); //给stu_id建唯一索引
mysql> desc stuinfo; //查看表结构
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| stu_id | int(13) | YES | UNI | NULL | |
| name | varchar(15) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 22 | |
| sex | enum(‘boy‘,‘girl‘) | NO | | boy | |
| class | varchar(8) | YES | | NSD1404 | |
| dizhi | varchar(30) | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
5：添加新字段id为主键索引，并让id值自动增长，并修改字段dizhi为home
mysql> alter table stuinfo add id int primary key auto_increment first; // 加新字段id为主键，并让id值自动增长 mysql> alter table stuinfo change dizhi home varchar(30); //修改字段名
6：新建stuchengji表，建表时设置外键关联stuinfo
mysql> create table stuchengji( -> stu_id int(13), chengji int(3), -> foreign key(stu_id) references stuinfo(stu_id) on update cascade on delete cascade -> ); //stu_id外键了stuinfo表
7：为stuinfo插入数据
mysql>insert into stuinfo values(null,12014002,"sw",30,"boy","NSD1404"," "); //id字段插入null占位 mysql> select * from stuinfo; //查看表 +----+----------+------+-----+-----+---------+------+ | id | stu_id | name | age | sex | class | home | +----+----------+------+-----+-----+---------+------+ | 1 | 12014001 | sw | 30 | boy | NSD1404 | | +----+----------+------+-----+-----+---------+------+
8：导入passwd ，然后导出数据到/bak
mysql> create table passwd( //为要导入的数据建立模板表 ->name varchar(15) primary key, ->mina varchar(1), ->uid int(5) not null, -> gid int(5) not null, ->miaoshu varchar(35), ->home varchar(50), ->shell varchar(50) not null, ->unique(uid)); mysql> desc passwd; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(15) | NO | PRI | NULL | | | mina | varchar(1) | YES | | NULL | | | uid | int(5) | NO | UNI | NULL | | | gid | int(5) | NO | | NULL | | | miaoshu | varchar(35) | YES | | NULL | | | home | varchar(50) | YES | | NULL | | | shell | varchar(50) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+ mysql> load data infile"/etc/passwd" into table passwd //导入passwd到表passwd并用：分隔 -> fields terminated by ":";
[root@localhost ~]# mkdir /bak
[root@localhost ~]# setfacl -m u:mysql:rwx /bak //给mydql写权限
mysql> select * from stuinfo into outfile "/bak/stuinfo"; //导出
[root@localhost ~]# cat /bak/stuinfo
112014001sw30boyNSD1404
本文出自 “sleepcat” 博客，请务必保留此出处http://sw5720.blog.51cto.com/8812314/1433269