mysql學習之表的基本操作的代碼分享

本文給大家分享的是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
喜歡就支持一下吧
點贊10 分享