數(shù)據(jù)表的操作
- 查看當(dāng)前數(shù)據(jù)庫(kù)中所有表
mysql> show tables; -- 顯示指定數(shù)據(jù)庫(kù)的所有表，使用該命令前需要使用 use 命令來(lái)選擇要操作的數(shù)據(jù)庫(kù)。
+------------------+
| Tables_in_demo |
+------------------+
| bm_list |
| rhs_users |
+------------------+
2 rows in set (0.00 sec)
- 創(chuàng)建表
-- auto_increment表示自動(dòng)增長(zhǎng)
-- 創(chuàng)建一個(gè)學(xué)生的數(shù)據(jù)表(id、name、age、high、gender、cls_id)
-- create table 數(shù)據(jù)表名字 (字段 類(lèi)型 約束[, 字段 類(lèi)型 約束]); -- [] 表示可有可無(wú)
-- 多個(gè)約束 不分先后順序
-- enum 表示枚舉
-- 最后一個(gè)字段不要添加逗號(hào)
-- unsigned: 無(wú)符號(hào), 不能夠存儲(chǔ)負(fù)數(shù)
-- 創(chuàng)建students表
mysql> create table students(
-> id int unsigned primary key auto_increment not null,
-> name varchar(10) not null,
-> age tinyint unsigned default 0,
-> high decimal(5,2) default 0.0, -- 180.88
-> gender enum("男", "女", "中性", "保密") default "保密",
-> cls_id int unsigned not null
-> );
Query OK, 0 rows affected (0.02 sec)
- 查看表的創(chuàng)建語(yǔ)句
mysql> mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`age` tinyint(3) unsigned DEFAULT '0',
`high` decimal(5,2) DEFAULT '0.00',
`gender` enum('男','女','中性','保密') DEFAULT '保密',
`cls_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 查看表結(jié)構(gòu)
mysql> desc students; -- show columns form students;也可以 -- 使用頻率非常高
+--------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
+--------+-------------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
顯示數(shù)據(jù)表的屬性，屬性類(lèi)型，主鍵信息 ，是否為 NULL，默認(rèn)值等其他信息。
- 查看數(shù)據(jù)表的詳細(xì)索引信息
show index from students;
image
- 修改表結(jié)構(gòu)
-- add -添加字段
-- 用法: alter table 表名 add 列名 類(lèi)型/約束;
-- 給students表添加‘birthday‘字段,默認(rèn)值為"2011-11-11 11:11:11"
mysql> alter table students add birthday date default "2011-11-11";
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 結(jié)果
mysql> desc students;
+----------+-----------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
| birthday | date | YES | | 2011-11-11 | |
+----------+-----------------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
-- modify -修改表字段值 不重命名版
-- 用法: alter table 表名 modify 列名 類(lèi)型及約束;
alter table students modify birthday date default "2011-11-11";
-- change -修改表字段 重命名版
-- 用法: alter table 表名 change 原列名 新列名 類(lèi)型及約束;
mysql> alter table students change birthday birth date default "2011-11-11";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+------------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
| birth | date | YES | | 2011-11-11 | |
+--------+------------------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
-- 刪除字段
-- 用法: alter table 表名 drop 字段名
alter table students drop birth;
-- 刪除表
-- 用法: alter table 表名
drop table students;
