创建表
create table 表名
create table if not exists 表名
mysql> create database company; Query OK, 1 row affected (0.00 sec) mysql> use company; Database changed mysql> create table if not exists t_dept( -> deptno int, -> dname varchar(20), -> loc varchar(40)); Query OK, 0 rows affected (0.20 sec) mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | t_dept | +-------------------+ 1 row in set (0.00 sec) mysql>
显示当前库下的所有表
show tables;
mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | t_dept | +-------------------+ 1 row in set (0.00 sec)
查看表的结构
describe 表名
简写
desc 表名
mysql> describe t_dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | deptno | int(11) | YES | | NULL | | | dname | varchar(20) | YES | | NULL | | | loc | varchar(40) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc t_dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | deptno | int(11) | YES | | NULL | | | dname | varchar(20) | YES | | NULL | | | loc | varchar(40) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
查看表的详细
show create table 表名
mysql> show create table t_dept; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_dept | CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) show create table t_dept \G mysql> show create table t_dept \G *************************** 1. row *************************** Table: t_dept Create Table: CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
删除表
drop table 表名
drop table if exists 表名
mysql> drop table if exists t_dept; Query OK, 0 rows affected (0.12 sec) mysql> show tables; Empty set (0.00 sec)
修改表名
ALTER TABLE old_table_name RENAME [TO] new_table_name
old_table_name 原表名
new_table_name 新表名
将t_dept修改为tab_dept
mysql> alter table t_dept rename tab_dept; Query OK, 0 rows affected (0.09 sec) mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | tab_dept | +-------------------+ 1 row in set (0.00 sec) mysql> desc tab_dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | deptno | int(11) | YES | | NULL | | | dname | varchar(20) | YES | | NULL | | | loc | varchar(40) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
为表增加一个字段默认在最后
ALTER TABLE table_name ADD 属性名 属性类型
为tab_dept增加一个字段descri varchar(20)
mysql> desc tab_dept; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | deptno | int(11) | YES | | NULL | | | dname | varchar(20) | YES | | NULL | | | loc | varchar(40) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table tab_dept add descri varchar(20); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
在表的第一个位置增加一个字段
ALTER TABLE table_name ADD 属性名 属性类型 first
mysql> alter table tab_dept add id int first; Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
在表的指定字段之后增加字段
ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名
mysql> alter table tab_dept add comm varchar(20) after dname; Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
删除字段
ALTER TABLE table_name DROP 属性名
mysql> alter table tab_dept drop comm; Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
字段修改-修改字段数据类型
ALTER TABLE table_name MODIFY 属性名 数据类型
mysql> alter table tab_dept modify descri int; Query OK, 0 rows affected (0.45 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
字段修改-修改字段名称
ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型
mysql> alter table tab_dept change id deptid int; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
字段修改-同时修改字段名称与数据类型
ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型
mysql> alter table tab_dept change deptid id varchar(32); Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
修改顺序
ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2
2个属性必须存在
将deptno调到第一个位置
mysql> alter table tab_dept modify deptno int first; Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
将ID放在最后
mysql> alter table tab_dept modify deptno int after descri; Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tab_dept modify deptno int first; Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table tab_dept modify id int after descri; Query OK, 0 rows affected (0.47 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tab_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 | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 曾轶可.2011-一只猫的旅行【天娱传媒】【WAV+CUE】
- 黑神话悟空上品狼刺客精魄获取方法一览|上品狼刺客精魄收集攻略
- 视觉小说《神椿市建设中REGENERATE》将于2025年2月20日发售
- 黑神话悟空上品鼠弩手精魄获取方法一览|上品鼠弩手精魄收集攻略
- 中国音乐地图听见世界非物质文化遗产《非遗HQCD》wav
- 中国民乐合奏作品集《华夏之音HQ》头版限量编号[WAV+CUE]
- [电影原声带]黎允文《天将雄师》[FLAC+CUE]
- 曝NS继任机型已进入量产阶段:已花了30亿日元
- 不是Switch2?曝《马里奥惊奇》NS捆绑包即将公开
- 暖心任天堂!网友Switch维修返还时贴纸被完好保留
- 《国语老歌 经典对唱情歌 2CD》[WAV/分轨][1.1GB]
- 《刀郎 翻唱精选华语专辑 披着羊皮的狼 》[WAV+CUE][430MB]
- 《真的田震 中国乐坛红极一时的歌者 精品集》[WAV+CUE][300MB]
- 群星.1995-新滚石九大天王之情歌大全【滚石】【WAV+CUE】
- 群星.1993-滚石九大天王十二出好戏·纵夏欢唱【滚石】【WAV+CUE】