mysql学习笔记之表的基本操作

2024-02-29 08:32:02 164

创建表

createtable表名

createtableifnotexists表名

mysql>createdatabasecompany;
QueryOK,1rowaffected(0.00sec)
mysql>usecompany;
Databasechanged
mysql>createtableifnotexistst_dept(
->deptnoint,
->dnamevarchar(20),
->locvarchar(40));
QueryOK,0rowsaffected(0.20sec)
mysql>showtables;
+-------------------+
|Tables_in_company|
+-------------------+
|t_dept|
+-------------------+
1rowinset(0.00sec)
mysql>

显示当前库下的所有表

showtables;

mysql>showtables;
+-------------------+
|Tables_in_company|
+-------------------+
|t_dept|
+-------------------+
1rowinset(0.00sec)

查看表的结构

describe表名

简写

desc表名

mysql>describet_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
3rowsinset(0.00sec)
mysql>desct_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
3rowsinset(0.00sec)

查看表的详细

showcreatetable表名

mysql>showcreatetablet_dept;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|t_dept|CREATETABLE`t_dept`(
`deptno`int(11)DEFAULTNULL,
`dname`varchar(20)DEFAULTNULL,
`loc`varchar(40)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8|
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
showcreatetablet_dept\G

mysql>showcreatetablet_dept\G
***************************1.row***************************
Table:t_dept
CreateTable:CREATETABLE`t_dept`(
`deptno`int(11)DEFAULTNULL,
`dname`varchar(20)DEFAULTNULL,
`loc`varchar(40)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)

删除表

droptable表名
droptableifexists表名

mysql>droptableifexistst_dept;
QueryOK,0rowsaffected(0.12sec)
mysql>showtables;
Emptyset(0.00sec)

修改表名

ALTERTABLEold_table_nameRENAME[TO]new_table_name
old_table_name原表名
new_table_name新表名
将t_dept修改为tab_dept

mysql>altertablet_deptrenametab_dept;
QueryOK,0rowsaffected(0.09sec)
mysql>showtables;
+-------------------+
|Tables_in_company|
+-------------------+
|tab_dept|
+-------------------+
1rowinset(0.00sec)
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
3rowsinset(0.00sec)

为表增加一个字段默认在最后
ALTERTABLEtable_nameADD属性名属性类型

为tab_dept增加一个字段descrivarchar(20)

mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
3rowsinset(0.00sec)
mysql>altertabletab_deptadddescrivarchar(20);
QueryOK,0rowsaffected(0.33sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|varchar(20)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
4rowsinset(0.00sec)

在表的第一个位置增加一个字段

ALTERTABLEtable_nameADD属性名属性类型first

mysql>altertabletab_deptaddidintfirst;
QueryOK,0rowsaffected(0.38sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|varchar(20)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

在表的指定字段之后增加字段

ALTERTABLEtable_nameADD属性名属性类型AFTER属性名

mysql>altertabletab_deptaddcommvarchar(20)afterdname;
QueryOK,0rowsaffected(0.31sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|comm|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|varchar(20)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
6rowsinset(0.00sec)

删除字段

ALTERTABLEtable_nameDROP属性名

mysql>altertabletab_deptdropcomm;
QueryOK,0rowsaffected(0.32sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|varchar(20)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

字段修改-修改字段数据类型
ALTERTABLEtable_nameMODIFY属性名数据类型

mysql>altertabletab_deptmodifydescriint;
QueryOK,0rowsaffected(0.45sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

字段修改-修改字段名称

ALTERTABLEtable_nameCHANGE旧属性名新属性名旧数据类型

mysql>altertabletab_deptchangeiddeptidint;
QueryOK,0rowsaffected(0.07sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptid|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

字段修改-同时修改字段名称与数据类型

ALTERTABLEtable_nameCHANGE旧属性名新属性名新数据类型

mysql>altertabletab_deptchangedeptididvarchar(32);
QueryOK,0rowsaffected(0.49sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|varchar(32)|YES||NULL||
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

修改顺序

ALTERTABLEtable_nameMODIFY属性名1数据类型FIRST|AFTER属性名2

2个属性必须存在
将deptno调到第一个位置

mysql>altertabletab_deptmodifydeptnointfirst;
QueryOK,0rowsaffected(0.33sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|id|varchar(32)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

将ID放在最后

mysql>altertabletab_deptmodifydeptnointafterdescri;
QueryOK,0rowsaffected(0.29sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|varchar(32)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
|deptno|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)
mysql>altertabletab_deptmodifydeptnointfirst;
QueryOK,0rowsaffected(0.34sec)
Records:0Duplicates:0Warnings:0
mysql>altertabletab_deptmodifyidintafterdescri;
QueryOK,0rowsaffected(0.47sec)
Records:0Duplicates:0Warnings:0
mysql>desctab_dept;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|deptno|int(11)|YES||NULL||
|dname|varchar(20)|YES||NULL||
|loc|varchar(40)|YES||NULL||
|descri|int(11)|YES||NULL||
|id|int(11)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
5rowsinset(0.00sec)

mysql学习笔记之表的基本操作

热门推荐

随机推荐