mysql學(xué)習(xí)之表的基本設(shè)置的代碼分享
發(fā)表時間：2023-09-04 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]本文給大家分享的是MySQL學(xué)習(xí)筆記系列文章的入門篇，主要講述MySQL表的基本操作命令，非常詳細，有需要的小伙伴可以來查看下創(chuàng)建表create table 表名create table if not exists 表名mysql> create database company;Query...
本文給大家分享的是MySQL學(xué)習(xí)筆記系列文章的入門篇，主要講述MySQL表的基本操作命令，非常詳細，有需要的小伙伴可以來查看下
創(chuàng)建表
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>
顯示當(dāng)前庫下的所有表
show tables;
mysql> show tables;
+-------------------+
Tables_in_company
+-------------------+
t_dept
+-------------------+
1 row in set (0.00 sec)
查看表的結(jié)構(gòu)
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)
字段修改-修改字段數(shù)據(jù)類型
ALTER TABLE table_name MODIFY 屬性名 數(shù)據(jù)類型
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 舊屬性名 新屬性名 舊數(shù)據(jù)類型
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)
字段修改-同時修改字段名稱與數(shù)據(jù)類型
ALTER TABLE table_name CHANGE 舊屬性名 新屬性名 新數(shù)據(jù)類型
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 數(shù)據(jù)類型 FIRST AFTER 屬性名2
2個屬性必須存在
將deptno調(diào)到第一個位置
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)
以上就是mysql學(xué)習(xí)之表的基本操作的代碼分享的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。
