一、使用DDL語句建立表
DDL語言全面資料定義語言(Data Define Language)
主要的DDL動詞:
CREATE(建立)、DROP(删除)、ALTER(修改)
TRUNCATE(截斷)、RENAME(重命名)
注意:
1、使用DDL語言時,必須在動詞後跟上資料庫對象名詞(例如:TABLE、VIEW、INDEX、SCHEMA、TRIGGER等)。
2、MySQL的DDL語言建立資料庫,如CREATE DATABASE、DROP DATABASE(在Oracle中沒有)。
文法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [database_name.] ( [[not] null],…
)
TEMPORARY:指明建立臨時表
IF NOT EXISTS:如果要建立的表已經存在,強制不顯示錯誤消息
database_name:資料庫名
table_name:表名
column_name:列名
data_type:資料類型
mysql> help create table #檢視詳細的建表幫助資訊
示例:建立一個員工(employee)資訊表
mysql>create table emp_xinxi ->( -> id int unsigned not nullprimary key,-> name varchar(20) not null,-> sex char(1) not null,-> birth date not null,-> job varchar(30) null
->);
mysql>desc emp_xinxi;+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | char(1) | NO | | NULL | |
| birth | date | NO | | NULL | |
| job | varchar(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
MySQL反引号`的使用:為了區分保留字與普通字元而引入的符号
舉個栗子:
SELECT `select` FROM `test` WHERE select='字段值';
解析:
在test表中,有個select字段,如果不用反引号,MySQL将把select視為保留字而導緻出錯,是以,有mysql保留字作為字段的,必須加上反引号來區分。
①保留字不能用于表名,比如desc,此時需要加入反引号來差別;但是,使用表名時可忽略反引号
mysql>create table desc……報錯
mysql>create table `desc`……成功
mysql>create table `test`……成功
mysql> drop table test……成功
②保留字不能用于字段名,比如desc,此時也需要加入反引号;并且,insert等使用時也要加上反引号
mysql> create table `test`(`desc` varchar(255)); 成功
mysql> insert into test(desc) values('fxf'); 失敗
mysql> insert into test(`desc`) values('fxf'); 成功
是以:在容易因為保留字問題會出現問題的情況下,都使用反引号`進行區分,不容易出錯。
二、建表時列的資料類型
1、整型:(5種)
每個整型類型可以指定一個最小顯示寬度(注意:這個寬度并不表示存儲的值有多大)
基本格式:
INT[(M)] [UNSIGNED] [ZEROFILL]
①M:指定顯示寬度(預設是M=10),以實際的取值範圍為準,值超過顯示寬度,顯示寬度自動增加
②unsigned:無符号的,表示隻允許正數,是以取值範圍取絕對值且最大值*2
(例:int unsigned range is 0 to 4294967295)
③zerofill:填充0(如果聲明了zerofill,該列會自動設為unsigned)
影響數字的顯示方式:
如果一個數字的寬度小于所允許的最大寬度,這個值前面會用0填充;
如果寬度大于所允許的最大寬度但不超過取值範圍,以實際的取值範圍為準,不填0;超出取值範圍的報錯不存儲。
mysql> create table t1(id intzerofill); #預設顯示寬度10
mysql> insert into t1 values(-1);
ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t1 values(123);
mysql> insert into t1values(12300);
mysql> insert into t1values(1.123);
mysql> select * fromt1+------------+
| id |
+------------+
| 0000000123 |
| 0000012300 |
| 0123456789 |
| 0000000001 |
+------------+
解析:因為建表的設定是int整數,小數點後的數會四舍五入
2、小數類型
1)定點小數:decimal(dec)
同義詞:decimal=dec=numeric=fixed
基本格式:
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
①M:精度,(整數位數+小數位數)不包含小數點的數字總位數(1--30)。不指定預設為10
②D:小數位數,如果不指定,預設為0
若位數不夠,小數末尾補零;若位數超預算(整數位數=M-D),報錯。
mysql> create table t2id dec(5,3) unsigned);
##數字總位數5
##整數位數5-3=2(不夠沒關系;超出報錯)
##小數位數3(不夠補零;超出四舍五入)
mysql> insert into t2values(12.345);
mysql> insert into t2values(1.23);
mysql> insert into t2values(123);
ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t2values(12.3456);
mysql> select * fromt2;+--------+
| id |
+--------+
| 12.345 |
| 1.230 |
| 12.346 |
+--------+
2)浮點小數:float/double
基本格式:
float[(length) | (,)]
double[(precision) | (,)]
兩者差別在于值所保留的存儲空間數量不同
注意:
由于對一個浮點數字來說,可用的存儲空間有限,如果一個數字非常大或非常小,将存儲這個數字的近似值而不是實際值
①使用一個參數來指定浮點類型
length(precision):在一個浮點資料類型中可以指定長度,來确定具體的浮點類型
0~24:單精度float,從第7位有效位,進行四舍五入存儲
25~30:雙精度double,從第16位有效位,進行四舍五入存儲
mysql> create table t3(id float(8));
m=8,說明是單精度,存儲6位的近似值(第7位四舍五入,後面為0)
mysql> insert into t3 values(123);
mysql> insert into t3 values(1234567);
mysql> insert into t3 values(123456789);
mysql> insert into t3 values(123456789.123456789);
mysql> select * fromt3;+-----------+
| id |
+-----------+
| 123 |
| 1234570 |
| 123457000 |
| 123457000 |
+-----------+
②使用兩個參數來指定浮點類型
單精度float(m,d):m表示精度(0~24),d表示小數位數
雙精度double(m,d):m表示精度(25~30),d表示小數位數
示例:設一個字段定義為float(5,3)---單精度浮點,精度5,小數位3(和dec一樣)
mysql> create table t_3(id float(5,3) unsigned);
##數字總位數5
##整數位數5-3=2(不夠沒關系;超出報錯)
##小數位數3(不夠補零;超出四舍五入)
mysql> insert into t_3 values(123);
ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t_3 values(12.3);
mysql> insert into t_3 values(12.3456);
mysql> insert into t_3 values(1.23456);
mysql> select * fromt_3;+--------+
| id |
+--------+
| 12.300 |
| 12.346 |
| 1.235 |
+--------+
解析:float的小數位數不是固定的;超出存儲範圍,近似值進行存儲。
3、位類型
基本格式:
BIT [(LENGTH)]
存儲基于位的值,Length取值範圍1---64.預設為1
mysql> create table an_bit (id bit(8));
mysql> insert into an_bit values(b'11');
#插入資料,用b注釋插入的是位類型資料(預設是十進制資料),要添加的資料用引号引起
mysql> select * froman_bit;
#注意:位類型資料不能如此查詢,會亂碼
mysql> select id+0 froman_bit; #可以看到十進制的值+------+
| id+0 |
+------+
| 3 |
+------+mysql> select bin(id+0) froman_bit; #可以看到二進制的值+-----------+
| bin(id+0) |
+-----------+
| 11 |
+-----------+mysql> select hex(id+0) froman_bit; #可以看到十六進制的值+-----------+
| hex(id+0) |
+-----------+
| 3 |
+-----------+
位類型一般用在記錄資料隻有兩種選擇,比如:男1女0、省内1省外0……
4、字元串類型
①char[(length)]:固定長度字元串
值少于長度則在尾部自動填充空格
length的機關是字元,取值範圍0---255
②varchar[(length)]:可變長度字元串
length的機關是字元,取值範圍0---65535
對于char和varchar,如果長度為0,則隻能存儲null值或者空字元串’’值
③long varchar:可變長度字元串
最大長度2的24次方減1個字元
④longtext:可變長度字元串
最大長度4G個字元
text是varchar的同義詞,mediumtext是long varchar的同義詞
5、日期類型
date : 3位元組
datetime: 8位元組
time : 3位元組
timestamp : 4位元組
year[(2) | (4)] : 1位元組
6、二進制大對象類型
binary: 255個字元
varbinary :65535個字元
blob: 65535字元(圖檔、檔案)
long varbinary: 2的24次方減1個字元
long blob: 2的32次方-1個字元
圖檔存儲方式:
①在資料庫中定義資料類型為blob,進行圖檔存儲
②……varchar,“mysql.PNG“,在資料庫中存放圖檔的路徑,指向檔案系統該照片對象
三、添加資料類型選項
1、對于字元串類型,可以添加類型選項:character set和collate
character set:設定字元集
collate:設定校對規則
mysql> create table t1(id int,name varchar(20)) character set=gbk collate=gbk_bin;
#t1建表指定校對規則(區分大小寫)
2、對于bit之外的所有數字類型,可以添加一個或是幾個類型選項:
①unsigned:無符号的,表示隻允許正數
②zerofill:填充0,(自動設unsigned)若數字的寬度小于所允許的最大寬度,在值前面用0填充
③auto_increment:自動增長,隻适應于整數類型,一個表中隻有一個列可以是自動增長的。(一般會用在主鍵序列自增)
示例:建立一個city_names表,其主鍵是自動增長的
注意:
當在insert語句中為auto_increment列,指定了null值或者沒有指定值時,MySQL自動為其指派;
MySQL會記住給出的最後一個順序号碼是多少。當要配置設定一個新的順序号碼時,最後一個順序号碼被找到,并且列中的最大值被确定。新的順序号碼就是這兩個值中的最大者加1。
mysql> insert into city_names values(10,'武漢');
mysql> insert into city_names(name) values('贛州');
mysql> select * fromcity_names;+-------+--------+
| seqno | name |
+-------+--------+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 深圳 |
| 10 | 武漢 |
| 11 | 贛州 |
+-------+--------+mysql> delete fromcity_names; #删除表中的所有行
mysql> insert into city_names values(null,'天津');
mysql> insert into city_names values(null,'成都');
mysql> select * fromcity_names;+-------+--------+
| seqno | name |
+-------+--------+
| 12 | 天津 |
| 13 | 成都 |
+-------+--------+
即使删除了表中的所有行,插入新行時,編号還是從原來的位置繼續;要想再次從1開始,必須删除整個表并重新建立。
預設情況下,順序号碼從1開始并且每次加1;可以分别使用兩個系統變量來改變預設行為:
mysql> show variables like '%auto_increment%';+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+mysql> set @@auto_increment_offset=10; --起始值
mysql> set @@auto_increment_increment=10; --每次加幾
mysql> create table t5(id integer auto_increment not nullprimary key);
mysql> insert into t5 values(null),(null);
mysql> select * fromt5;+----+
| id |
+----+
| 10 |
| 20 |
+----+