alter table命令可以用来改变一个数据表的许多设计细节,如添加或删除一些数据列,改变数据列的属性,定义和删除各种索引等。
1.增加一个数据列
alter table tblname add newcolname coltype coloptions
2.修改一个数据列
alter table tblname change oldcolname newcolname coltype coloptions
如:alter table table1 change id id auto_increment,说明列没有改名,也也可实现改名
3.删除一个数据列
alter table tblname drop colname
4.增加一个索引
alter
table
tblname
add
primary
key
(indexcols
)
alter
table
tblname
add
index
[
indexname
]
(indexcols
)
alter
table
tblname
add
unique
[
indexname
]
(indexcols
)
5.添加一个外键约束条件
alter
table
tblname
add
foreign
key
[
indexname
]
(column1)
references
table2 (column2)
6.删除一个索引
alter
table
tblname
drop
primary
key
alter
table
tblname
drop
index
indexname
alter
table
tblname
drop
foreign
key
indexname
update命令用来修改数据库里现有的数据记录
1.where限定的update语句
update
tablename
set
column1
=
value1,column2
=
value2![](https://www.cnblogs.com/Images/dot.gif)
where
columnN
=
value
2.不带where限定的update对整个数据表做修改
insert命令可以向表中插入数据
1.一条命令插入多条数据记录
1.增加一个数据列
alter table tblname add newcolname coltype coloptions
2.修改一个数据列
alter table tblname change oldcolname newcolname coltype coloptions
如:alter table table1 change id id auto_increment,说明列没有改名,也也可实现改名
3.删除一个数据列
alter table tblname drop colname
4.增加一个索引
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
update命令用来修改数据库里现有的数据记录
1.where限定的update语句
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
update
titles
set
year
=
2005
update titles set price = price * 1.05
3.编辑排列清单里的数据记录
update titles set price = price * 1.05
update
tablename
set
mydata
=
0
order
by
name limit
10
4.更新关联数据表里的数据记录
update
table1,table2
set table1.columnA = table2.columnB
where table1.table1ID = table2.table1ID
set table1.columnA = table2.columnB
where table1.table1ID = table2.table1ID
insert命令可以向表中插入数据
1.一条命令插入多条数据记录
insert
into
table
(columnA columnB columnC)
values ( ' a ' , 1 , 2 ),( ' b ' , 12 , 13 ),( ' c ' , 22 , 33 ),
values ( ' a ' , 1 , 2 ),( ' b ' , 12 , 13 ),( ' c ' , 22 , 33 ),
![](https://www.cnblogs.com/Images/dot.gif)
delete命令用于删除表中记录
delete from titles where titleID=8//因为删除肯定是删除一行记录,所以delete后不需要加*
1.删除关联记录
delete
t1,t2
from
t1,t2,t3
where
condition1
and
condition2
2.输出排序清单里的数据记录
delete
from
authors
order
by
ts
desc
limit
1
show命令用于查看原数据
show databases
show tables from dbname
show [ full ] columns from tablename // 返回全部数据列的详细信息
show index from tablename
show tables from dbname
show [ full ] columns from tablename // 返回全部数据列的详细信息
show index from tablename