MySQL表操作及欄位資料類型

標籤：MySQL   MySQL表操作   MySQL建立表   

1、查看當前資料庫所有表

mysql> show tables;

2、建立表，id欄位為整型無符號，不能有負數並且是主鍵（主鍵唯一），引擎為InnoDB，字元集是utf8，注釋資訊為"學生表"，int括弧中的3表示欄位的寬度為3，以100開始自增，zerofill意思是自動補零

mysql> create table student(

    id int(3) unsigned  zerofill not null auto_increment,

    name varchar(30) not null,

    primary key(id)

)engine=InnoDB auto_increment=100 charset=utf8 comment="學生表";

3、刪除表

mysql> drop table if exists student;

4、查看錶結構

mysql> desc student;

5、查看建表代碼

mysql> show create table student;

6、插入資料

mysql> insert into student(name) values ("jack"),("王五"),("趙六");

7、簡單查詢資料

mysql> select * from student;

8、顯示表的所有列，比desc要詳細

mysql> show full columns from student;

9、刪除多個表，表名用逗號分隔

mysql> drop table if exists stu,a,b;

欄位資料類型

1>整型：tinyint（1位元組，0-255）、smallint（2位元組，0-65535）、mediumint（3位元組，0-16777215）、int（4位元組，0-4294967295）、bigint（5位元組，0-18446744073709551615）

tinyint：最多能存到255，比如建立表的時候用tinyint的話，在存256的時候會報錯，這種類型存年齡、部門編號比較合適，例如：

mysql> create table stu(

    -> id int(3) unsigned auto_increment,

    -> name varchar(30),

    -> age tinyint(2) zerofill,

    -> primary key(id)

    -> )engine=InnoDB default charset=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into stu(name,age) values ("張三",256);           //如果插入資料超過了tinyint範圍就會報錯，存小數的話會四捨五入

ERROR 1264 (22003): Out of range value for column 'age' at row 1

2>小數類型：decimal(p,s)、numeric(p,s),兩個選一即可，例如

mysql> create table stu( 

    id int(3) unsigned auto_increment, 

    name varchar(30), 

    money decimal(4,2),     //money欄位用小數類型，(4,2)代表4位元字，其中2位整數和2位小數，也就是說最高存99.99       

    primary key(id) 

)engine=InnoDB default charset=utf8;

mysql> insert into stu (name,money) values ("李四",200);

ERROR 1264 (22003): Out of range value for column 'money' at row 1

mysql> show errors;     //查看錯誤

3>字串類型：char()、varchar()、text()、longtext()

char()為固定長度字串(如果存1個字元會補5個0)，最大為255，手機號、社會安全號碼可以使用char類型，例如

mysql> create table stu(

    -> id int unsigned not null auto_increment primary key,

    -> name char(6)                        //如果char範圍超過255會報錯，(6)代表6個字元，超出6會報錯

    -> )engine=InnoDB default charset=utf8;

varchar()為可變長度字串，最大為65535，例如：

mysql> create table stu( 

    id int unsigned not null auto_increment primary key, 

    name varchar(2)                      //採用varchar類型

)engine=InnoDB default charset=utf8;

mysql> insert into stu (name) values ("張三");       

Query OK, 1 row affected (0.00 sec)

mysql> insert into stu (name) values ("齊天大聖");

ERROR 1406 (22001): Data too long for column 'name' at row 1

注意：以上插入資料四個字也會報錯，可變長字串並不是說想變多長變多長，比如定義char(3)，存入一個字元a，系統會自動補兩個空位，結果是一個a和兩個空格補齊了三個字元；而定義varchar(3)，存入a後，不會自動補齊空位，就是實實在在存入了一個字元，但是存入的最大位元組數還是3，存入4個字元也會報錯。大家注意兩個概念。

text()也是可變長的，最大為65535；longtext()最大為4G，存文章可以使用。。。

簡單說以下enum參數，這個參數代表枚舉，比如我要存一個性別，除了男就是女，例如：

mysql> create table stu( 

    id int unsigned not null auto_increment primary key, 

    name varchar(5) not null,

    gender enum("男","女") 

)engine=InnoDB default charset=utf8;

mysql> insert into stu (name,gender) values ("張三","中");              //必須存enum()內定義的選項

ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> insert into stu (name,gender) values ("張三","男");

4>二進位類型：blob、longblob，比如我要存一個MP3格式的檔案，就需要用到此類型，一般沒有往資料庫直接存這些東西的，資料庫存的只是這些檔案的地址。

5>日期時間類型：date、datetime，date只能存年月日，而datetime能存年月日時分秒

mysql> create table stu( 

    id int unsigned not null auto_increment primary key, 

    name varchar(5) not null,gender enum("男","女"),

    birthday datetime                       //出生日期採用datetime類型 

)engine=InnoDB default charset=utf8;

mysql> insert into stu (name,gender,birthday) values ("張三","男","2018-02-26 00:00:00");

如果欄位類型有註冊時間，而且我們想要自動添加怎麼辦？如下：

mysql> create table stu( 

    id int unsigned not null auto_increment primary key, 

    name varchar(5) not null,

    gender enum("男","女"),

    registered datetime default now()

)engine=InnoDB default charset=utf8;

mysql> insert into stu (name,gender) values ("張三","男");       //再添加資料時就不需要再添加registered欄位資訊了

注意：datetime類型的資料在修改資料時，時間不會改變，而時間戳記類型會改變，例如：

mysql> create table stu( 

    id int unsigned not null auto_increment primary key, 

    name varchar(5) not null,

    gender enum("男","女"),

    registered timestamp                 //時間戳記類型，這時使用update語句修改資料時，時間戳記會改變，如果不想它改變，應該寫成registered timestamp default current_timestamp()

)engine=InnoDB default charset=utf8;

MySQL表操作及欄位資料類型