人人狠狠综合久久亚洲88,亚洲午夜精品久久久久久app,无码国内精品久久人妻

本文給大家分享的是mysql學習筆記系列文章的入門篇，主要講述mysql表的基本操作命令，非常詳細，有需要的小伙伴可以來查看下

創建表

create table 表名

create table if not exists 表名

mysql&gt;?create?database?company;  Query?OK,?1?row?affected?(0.00?sec)  mysql&gt;?use?company;  Database?changed  mysql&gt;?create?table?if?not?exists?t_dept(  ??-&gt;?deptno?int,  ??-&gt;?dname?varchar(20),  ??-&gt;?loc?varchar(40));  Query?OK,?0?rows?affected?(0.20?sec)  mysql&gt;?show?tables;  +-------------------+  |?Tables_in_company?|  +-------------------+  |?t_dept??????|  +-------------------+  1?row?in?set?(0.00?sec)  mysql&gt;

顯示當前庫下的所有表

show tables;

mysql&gt;?show?tables;  +-------------------+  |?Tables_in_company?|  +-------------------+  |?t_dept??????|  +-------------------+  1?row?in?set?(0.00?sec)

查看表的結構

describe 表名

簡寫

desc 表名

mysql&gt;?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&gt;?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&gt;?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&gt;?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&gt;?drop?table?if?exists?t_dept;  Query?OK,?0?rows?affected?(0.12?sec)  mysql&gt;?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&gt;?alter?table?t_dept?rename?tab_dept;  Query?OK,?0?rows?affected?(0.09?sec)  mysql&gt;?show?tables;  +-------------------+  |?Tables_in_company?|  +-------------------+  |?tab_dept?????|  +-------------------+  1?row?in?set?(0.00?sec)  mysql&gt;?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&gt;?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&gt;?alter?table?tab_dept?add?descri?varchar(20);  Query?OK,?0?rows?affected?(0.33?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?add?id?int?first;  Query?OK,?0?rows?affected?(0.38?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?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&gt;?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&gt;?alter?table?tab_dept?drop?comm;  Query?OK,?0?rows?affected?(0.32?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?modify?descri?int;  Query?OK,?0?rows?affected?(0.45?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?change?id?deptid?int;  Query?OK,?0?rows?affected?(0.07?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?change?deptid?id?varchar(32);  Query?OK,?0?rows?affected?(0.49?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?modify?deptno?int?first;  Query?OK,?0?rows?affected?(0.33?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?modify?deptno?int?after?descri;  Query?OK,?0?rows?affected?(0.29?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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&gt;?alter?table?tab_dept?modify?deptno?int?first;  Query?OK,?0?rows?affected?(0.34?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?alter?table?tab_dept?modify?id?int?after?descri;  Query?OK,?0?rows?affected?(0.47?sec)  Records:?0?Duplicates:?0?Warnings:?0  mysql&gt;?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

數據庫
# 數據類型

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