Mysql 命令行列表

MySQL使用说明

登陆

mysql -h locolhost(IP) -u root -p

---

---

DataBase使用

1.create database bd_name

2.use bd_name

1.show tables

2.desc table_name

---

---

Table使用

Create

>create table table_name(   
id int auto_increment,             //自动增长
std_id int unique,                 //不可相等
id_2 int primary key,              //主键
eng_name varchar(50) default'zero', //初始值
Chi_name varchar(20) not null      //不可为空
Sex varchar(10),      
constraint c_fk(外键简化名) foreign key(Sex) reference table_name02(sex)    //外键（构建子表）
)

---

Change

1.alter table old_name [to] new_name

2.alter table table_name modify attribute_name data_Tyoe

3.alter table table_name change attribute_name_old attribute_name_new new_Type

4.alter table table_name modify attribute_name First(Position)/after attribute_name02

5.alter table table_name add attribute_name01 data_Tyoe First/after attribute_name02

6.alter table table_name engine=Mylsam

---

Show

1.show create table table_name

---

Delete

1.(1)(Usual) drop table table_name

1.(2)(Connect)
-1 show create table table_name
-2 alter table table_name drop foreign key c_fk
-3 drop table table_name

2.alter table table_neme drop attribute_name

3.alter table table_name drop foreign key c_fk

---

---

Data使用

Create

1.(Just one)

(1)insert into table_name values(v1,v2)

(2)insert into table_name(n1,n2) values (v1,v2)

2.insert into table_name [n1,n2,n3] values(trade_offs_m_n),(values)

3.insert into table_name01(attribute_names) select (attribute_name02) from table_name02 where if_set(条件表达式)

---

UpDate

1.update table_name set attribute_name01=v1 ,attribute_name02=v2 where if_set

---

Delete

1.delete from table_name where if_set

2.delete from table_name ; //all !!

---

---

About The Query

普通查询数据库

1.select attribute_name from table_name [where if(条件表达式1)] [group by attribute_name[having 条件表达式2]][order by attribute_name[ASC(升序)/DESC(降序i)]]

–

2.**single queue:**select attribute_name from table_name where if

–

3.**with in:**select attribute_name / * table_name where attribute_name in(v1,v2……)

–

4.**with between and:**select attribute_name / * from table_name where attribute_name between v1 and v2 (include v1 and v2)

–

5.with like:允许省略表示：%（任意长度字符串） _（单个字符）
select attribute_name / * from table_name where attribute_name [not]like if; not表示不匹配时

–

6.**for null:**select attribute_name / * from `table_name“attribute_name
is [not]null

–

7.**with and or:**select attribute_name / * from table_name where if1 and/or if2

–

8.**unique:**select distinst attribute_name from table_name

–

9.(分组查询)–（1）**不明觉利的一组，单独用group by分组，结果只会显示一个分组的一条记录:**select attriobute_name / * from table_name group by attribute_name
–(2)**没个字段的所有字段都可以显示：**select attribute_name ,group_coucat(attribute_name) from table_name group by attribute_name
–(3)**group by 和集合函数的使用：**select attribute_name,count (attribute_name) from table_name group by attribute_name having count(attribute_name) if ]
–(4)*多字段分组：**select from table_name group by attribute_name01 , attribute_name02……
–(5)**group by 与with rollup一起用:**select attribute_name , count(attribute_name)from table_name group by attribute_name with rollup

–

10.(限制查询)*with limit:**select from table_name limit a/a,b (from first to a,or a to b)

使用集合函数查询数据

1.count()：select count(attribute_name) from table_name

2.sum()：select attribute_name ,sum(attribute_name) from table_name where if

3.avg()：select avg(attribute_name) from table_name group by attribute_name

4.max与min:select max/min(attribute_name) from table_name

多表连接查询

内链接查询：两个以上表中存在意义相同的字段时，可以用该字段来连接表进行查询。
select attribute_name01 , attribute_name02 from table_name01 ,table_name02 where table_name01.attribute_name01=table_name02.attribute_name02

外连接表查询：select attribute_list from table_name01 left/right jion table_name02 on table_name01.attribute_name01=table_name02.attribute_name02

使用正则表达式查询

select * from table_name where attribute_name regexp `……`

---

---

MySQL备份

mysqldump -u username -p bd_name table01,table02 >BackupName.sql(可加绝对路径)

mysqldump -u username -p -databases bd_name1 bd_name2 >BackName.sql

mysqldump -u root -p -all -databases >BackName.sql

Mysql还原

mysql -u root -p <backup.sql