常用数据库操作命令

drop database if exists school; //如果存在school则删除
create database school;  //建立库school
use school;   //打开库school
create table student  //建立表student
(
id int(3) auto_increment not null primary key,
name char(10) no null,
age int(2),
sex char(2)
);

//插入数据
insert into student(name,age,sex) values('jack','22','男');
//修改数据
update student set name='Sean' where name='jack';

//修改表名
alter table student rename to Student;
alter table student rename as Student;
//添加列
alter table student add birthday date;
//删除列
alter table student drop birthday;
//加索引
create index 索引名 on 表名(字段名)
//修改字段名
alter table student change age Age int(8);
//修改字段类型
alter table student change name varchar(20);
alter table student modify name varchar(20);

//备份表结构
mysqldump -u root -p -d school >school.sql;

//备份数据+表结构

mysqldump -u root -p school  student >student.bak;

mysqldump -u root -p --database school  --table student >scool.student.sql;

//导出数据
select name age from student into outfile "/home/mysql/temp.txt";

//导入数据
load data infile "/home/mysql/temp.txt" into table 表名;

mysql -uroot -p school

//导出存储过程和表结构

mysqldump -u root -p -R -d school >school.sql;