mysql 流程函數 儲存引擎 InnoDB簡單特性

標籤：mysql 流程函數 innodb特性

建表及插入資料語句：

mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,
null);
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

1.if(value,t,f)函數:這裡認為高於2000就是‘high‘,其他都是low

mysql> select if(salary>2000,‘high‘,‘low‘) from salary;
+------------------------------+
| if(salary>2000,‘high‘,‘low‘) |
+------------------------------+
| low                          |
| low                          |
| high                         |
| high                         |
| high                         |
| low                          |
+------------------------------+
6 rows in set (0.00 sec)

2.ifnull(value1,value2)函數:這個函數可以用來將NULL值換成0

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|             0.00 |
+------------------+
6 rows in set (0.00 sec)

3.case when [value1] then [result]...else [default] end函數:

mysql> select case when salary<=2000 then ‘low‘ else ‘high‘ end from salary;
+---------------------------------------------------+
| case when salary<=2000 then ‘low‘ else ‘high‘ end |
+---------------------------------------------------+
| low                                               |
| low                                               |
| high                                              |
| high                                              |
| high                                              |
| high                                              |
+---------------------------------------------------+
6 rows in set (0.00 sec)

4.case [expr] when [value1] then [result] ... else [default] end函數:

mysql> select case salary when 1000 then ‘low‘ when 2000 then ‘mid‘ else ‘high‘e
nd from salary;
+----------------------------------------------------------------------+
| case salary when 1000 then ‘low‘ when 2000 then ‘mid‘ else ‘high‘end |
+----------------------------------------------------------------------+
| low                                                                  |
| mid                                                                  |
| high                                                                 |
| high                                                                 |
| high                                                                 |
| high                                                                 |
+----------------------------------------------------------------------+
6 rows in set (0.02 sec)

5.關於mysql儲存引擎的一些東西:

儲存引擎是mysql不同於其他資料庫的一個重要特性，使用者可以根據實際需要利用這個特性定製自己的儲存引擎.

mysql的引擎有:

mysql> show engines \G;
*************************** 1. row ***************************
 Engine: MyISAM
Support: YES
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
 Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
 Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappears)
*************************** 6. row ***************************
 Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
 Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
 Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
 Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
 Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
 Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)

或者使用這個cmd:

mysql> show variables like ‘have%‘;
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| have_archive          | YES      |
| have_bdb              | NO       |
| have_blackhole_engine | NO       |
| have_compress         | YES      |
| have_crypt            | NO       |
| have_csv              | NO       |
| have_example_engine   | NO       |
| have_federated_engine | NO       |
| have_geometry         | YES      |
| have_innodb           | YES      |
| have_isam             | NO       |
| have_ndbcluster       | NO       |
| have_openssl               | DISABLED |
| have_query_cache      | YES      |
| have_raid             | NO       |
| have_rtree_keys       | YES      |
| have_symlink          | YES      |
+-----------------------+----------+
17 rows in set (0.00 sec)

disabled說明mysql支援該engine，但是啟動的時候被禁用.

建立表的時候，可以使用engine關鍵字指定該表使用哪個engine:

mysql> create table ai(i bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY(I)) ENGI
NE=MyISAM DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.03 sec)

也可以修改表的引擎:

mysql> alter table ai engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table ai \G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

常見的儲存引擎有：

MyISAM,InnoDB,MEMORY,MERGE,NDB

上述引擎中只有InnoDB支援外鍵。

mysql的預設儲存引擎是MyISAM

每個MyISAM在磁碟上儲存成3個檔案:

.frm(儲存表定義)

.MYD(MYData,儲存資料)

.MYI(MYIndex,儲存索引)

關於InnoDB的一些特性:

a.自動成長欄位:

mysql> create table autoincre(i smallint not null auto_increment,name varchar(20
),primary key(i))engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into autoincre values(1,‘1‘),(2,‘2‘),(null,‘3‘);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from autoincre;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 3 | 3    |
+---+------+
3 rows in set (0.00 sec)

向自增長欄位插入記錄後，不影響該欄位自己增加值.

對於InnoDB表，自動成長欄位必須是索引，如果是複合式索引也必須是複合式索引的第一個列.

但是對於MyISAM表，自增長欄位可以不是複合式索引的第一個列，可以作為第二個列出現:

mysql> create table autoincre_demo(d1 smallint not null auto_increment,d2 smalli
nt not null,name varchar(10),index(d2,d1))engine=myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into autoincre_demo(d2,name)values(2,‘2‘),(3,‘3‘),(4,‘4‘),(2,‘2‘),
(3,‘3‘),(4,‘4‘);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

自增長欄位d1作為複合式索引在第二列中出現，自增長欄位的記錄按照複合式索引d2進行排序後遞增.

b.mysql的儲存引擎中只有InnoDB支援fk:

建表語句:

mysql> create table country(country_id smallint unsigned not null auto_increment
,country varchar(50) not null,primary key(country_id))
    -> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.14 sec)

mysql> create table city(
    ->  city_id smallint unsigned not null auto_increment,
    ->  country_id smallint unsigned not null,
    ->  primary key(city_id),
    ->  foreign key(country_id) references country(country_id)
    ->  on delete restrict on update cascade
    -> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)

插入記錄:

mysql> insert into country(country) values(‘china‘);
Query OK, 1 row affected (0.03 sec)
mysql> select * from country;
+------------+---------+
| country_id | country |
+------------+---------+
|          1 | china   |
+------------+---------+
1 row in set (0.00 sec)
mysql> insert into city(country_id) values(1);
Query OK, 1 row affected (0.11 sec)

mysql> select * from city;
+---------+------------+
| city_id | country_id |
+---------+------------+
|       1 |          1 |
+---------+------------+
1 row in set (0.00 sec)

city表依賴country表的country_id欄位，刪除會出錯:

mysql> delete from country where country_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`tom1/city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFER
ENCES `country` (`country_id`) ON UPDATE CASCADE)

現在更新country表中的country_id欄位，city表的country_id欄位也會被同步更新，這是因為在建立city表的最後加了:on update cascade，即:更新時做級聯操作

mysql> update country set country_id=1000 where country_id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from city;
+---------+------------+
| city_id | country_id |
+---------+------------+
|       1 |       1000 |
+---------+------------+
1 row in set (0.00 sec)

mysql 流程函數 儲存引擎 InnoDB簡單特性