cmd下mysql操作總結

標籤：

 

1.連結資料庫
C:\>mysql -hlocalhost -uroot -p //按enter後，會提示輸入密碼，若密碼為空白直接enter
C:\>mysql --help
C:\>mysqladmin -uroot -p password //按照提示修改密碼，密碼不填就表示為密碼為空白

2.資料庫操作(建立資料庫school_db，其下建立資料表students)
create database school_db;
show databases;
use database school_db;
select database();
show tables;
drop database;

3.資料表操作
create table students(
id int unsigned not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
);

insert into students values(NULL,‘Tom‘,‘man‘,22,‘13249076325‘);
insert into students (name,sex) values(‘Lucy‘,‘woman‘);

select * from students;
select * from students where name like "%L%";
select * from students where id<5 and age>20;
select * from students order by id limit 0,2;

update students set tel=default where id=5;
update students set name="Jack",age=age+1 where id=1;

delete from students;
delete from students where id=2

4.建立後表的修改：
alter table students add address char(60);
alter table students add birthday after age;

alter table students change tel telphone char(13) default "-";
alter table students change name name char(16) not null;

alter table students drop birthday;

alter table students rename workmates;
alter table students rename my_db.students; //把表移動到資料庫my_db下面，並命名為students

drop table workmates;

drop database school_db;

5.匯出匯入資料庫和資料表
C:\>mysqldump -uroot school_db > E:\school_db.sql;
C:\>mysqldump -uroot school_db students > E:\school_db_students.sql;

C:\>mysql -uroot -D school_db < E:/school_db.sql; // 用最下面的source
C:\>mysqldump -uroot school_db < E:/school_db.sql; // 將資料庫匯入到資料庫中
C:\>mysqldump -uroot school_db < E:/school_db_students.sql; // 將資料表匯入到資料庫中
mysql>use school_db;
myslq>source E:/school_db.sql;

 

cmd下mysql操作總結