mysql語句進階

標籤：

1.null

mysql> create table worker(id int not null,name varchar(8) not null,pass varchar(20) not null);

mysql> insert into worker values(1,‘HA‘,‘123456‘);

mysql> insert into worker values(1,‘LB‘,null);

ERROR 1048 (23000): Column ‘pass‘ cannot be null  不能為null

mysql> insert into worker values(2,‘HPC‘,‘‘);

mysql中的NULL是佔用空間的。是一個數值。 null 和‘‘是不同的。

2.default 設定欄位的預設值

mysql> create table test2(name varchar(8) not null,dept varchar(25) default ‘SOS‘);

mysql> insert into test2 (name) values (‘kko‘);

如果時間欄位，預設為目前時間，插入0時，預設為目前時間。

如果是enum 類型，預設為第一個元素。

3.auto_increment欄位約束

對於主鍵，這是非常有用的。可以為每條記錄建立一個惟一的標識符（最大值加1）

mysql> create table items ( id int not null auto_increment primary key , label varchar(20) not null);

mysql>  insert into items (label) values (‘aaba‘);

mysql>  insert into items values (9,‘aaba‘);

再插入一條id將為多少

mysql>  insert into items (label) values (‘abc‘);

delete 不能清除auto_increment

mysql> delete from items;

mysql> insert into items (label) values ("aaaa");

方法二：刪除表中所有記錄，清auto_increment 值。

mysql> truncate table items;

mysql> insert into items(label)values(‘abv‘);

4.索引

為了加快搜尋速度，減少查詢時間 。

1 索引是以檔案儲存體的。如果索引過多，占磁碟空間較大。而且他影響： insert ,update ,delete 執行時間。

2索引中資料必須與資料表資料同步：如果索引過多，當表中資料更新的時候後，索引也要同步更新，這就降低了效率。

索引的類型

1、普通索引

2、唯一性索引

3、主鍵索引（主索引）

4、複合索引

普通索引

最基本的索引，不具備唯一性，就是加快查詢速度

註：可以使用key，也可以使用index 。index 索引名稱（欄位）  ，索引名稱，可以加也可以不加，不加使用欄位名作為索引名。。

mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );

注意：index和 key 是相同的

mysql> create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );

mysql> create table demo2( id int(4), name varchar(20), pwd varchar(20), key index_pwd(pwd) );  #加上索引名稱

查看索引

註：如果Key是MUL, 那麼該列的值可以重複, 該列是一個非唯一索引的前置列(第一列)或者是一個唯一性索引的組成部分但是可以含有空值NULL。就是表示是一個普通索引。

我們先刪除索引

mysql> alter table demo drop key pwd; 注意此處的pwd指的是索引的名稱,而不是表中pwd的那個欄位

再用alter添加

mysql> alter table demo add key(pwd);

唯一索引

與普通索引基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一，用來約束內容，欄位值只能出現一次。應該加唯一索引。唯一性允許有NULL值<允許為空白>。

mysql> create table demo3(id int(4) auto_increment primary key, uName varchar(20), uPwd varchar(20), unique  index  (uName));

方法二：修改表時加唯一索引

alter table 表名 add unique 索引名 (欄位);

mysql> alter table demo3 drop key uName;

mysql> alter table demo3 add unique(uName);

主鍵索引

查詢資料庫，按主鍵查詢是最快的，每個表只能有一個主鍵列，可以有多個普通索引列。主鍵列要求列的所有內容必須唯一，而索引列不要求內容必須唯一，不允許為空白

mysql> create table demo4 (id int(4) not null auto_increment primary key,name varchar(4) not null );

方法二：建立表後添加<不推薦>

刪除遇到這種情況是auto_increment的原因

mysql> alter table demo4 change id id int(4) not null;

mysql> alter table demo4 drop primary key;

再添加

mysql> alter table demo4 change id id int(4) not null primary key auto_increment;

總結：主鍵索引，唯一性索引區別：主鍵索引不能有NULL，唯一性索引可以有空值

複合索引

索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作複合索引

例：建立一個表存放伺服器允許或拒絕的IP和port，要記錄中IP和port要唯一。

mysql> create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum(‘deny‘,‘allow‘) not null, primary key (host,port));

mysql> insert into firewall values(‘10.96.52.46‘,22,‘deny‘);

mysql> insert into firewall values(‘10.96.52.46‘,21,‘allow‘);

mysql> insert into firewall values(‘10.96.52.46‘,21,‘allow‘);

ERROR 1062 (23000): Duplicate entry ‘10.96.52.46-21‘ for key ‘PRIMARY‘

5.外鍵

外鍵：每次插入或更新時，都會檢查資料的完整性。

註：建立成功，必須滿足以下4個條件：

1、確保參照的表和欄位存在。

2、組成外鍵的欄位被索引。

3、必須使用type指定儲存引擎為：innodb.

4、外鍵欄位和關聯欄位，資料類型必須一致。

例子：我們建立一個資料庫，包含使用者資訊表和訂單表

mysql> create database market;

mysql> create table `user`(id int(11) not null auto_increment, name varchar(50) not null default ‘‘, sex int(1) not null default ‘0‘, primary key(id))ENGINE=innodb;

#建立時，如果表名是sql關鍵字，使用時，需要使用反引號``

mysql> create table `order`(o_id int(11) auto_increment, u_id int(11) default ‘0‘, username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;

註：

1:on delete cascade  on update cascade 添加串聯刪除和更新：

2:：確保參照的表user中id欄位存在。組成外鍵的欄位u_id被索引。必須使用type指定儲存引擎為：innodb。

外鍵欄位和關聯欄位，資料類型必須一致。

插入測試資料

mysql> insert into user(name,sex)values(‘HA‘,1),(‘LB‘,2),(‘HPC‘,1);

mysql> insert into `order` (u_id,username,money)values(1,‘HA‘,234),(2,‘LB‘,146),(3,‘HPC‘,256);

測試串聯刪除：

mysql> delete from user where id=1; 刪除user表中id為1的資料

測試串聯更新：

mysql> update user set id=6 where id=2;

 

測試資料完整性

外鍵約束，order表受user表的約束

在order裡面插入一條資料u_id為5使用者，在user表裡面根本沒有，所以插入不進去

mysql> insert into user values(5,‘Find‘,1);

mysql> insert into `order` (u_id,username,money)values(5,‘Find‘,346);

方法二：通過alter table 建立外鍵和串聯更新，串聯刪除

mysql> create table order1(o_id int(11) auto_increment, u_id int(11) default ‘0‘, username varchar(50), money int(11), primary key(o_id), index(u_id))type=innodb;

mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade,type=innodb;

mysql> alter table order1 add constraint `bk`foreign key(u_id) references user(id) on delete cascade on update cascade,type=innodb;  指定外鍵名稱

mysql> show create table order1;

 

刪除外鍵：

文法

alter table 資料表名稱 drop foreign key 約束（外鍵）名稱

mysql> alter table order1 drop foreign key order1_ibfk_1;

mysql> show create table order1;

6.視圖

建立視圖

文法：create view視圖名稱（即虛擬表名） as select 語句。

mysql>  create view bc as select b.bName ,b.price ,c.bTypeName from books as b left join category as c  on b.bTypeId=c.bTypeId ;

可以按照普通表去訪問。

另外視圖表中的資料和原資料表中資料是同步的。

mysql> show create view bc \G

更新或修改視圖

alter view視圖名稱（即虛擬表名） as select 語句。

update view視圖名稱（即虛擬表名）set 

mysql> alter view bc as select b.bName ,b.publishing ,c.bTypeId from books as b left join category as c  on b.bTypeId=c.bTypeId ;

mysql> update bc set bName=‘HA‘ where price=34;

刪除視圖

drop view 視圖名。

mysql> drop view bc;

 

 

 

 

 

 

 

 

mysql語句進階