本文給大家分享的是mysql學習筆記系列文章的入門篇，主要講述mysql表的基本操作命令，非常詳細，有需要的小伙伴可以來查看下
創建表
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)
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
