Home >Database >Mysql Tutorial >mysql流程函数存储引擎InnoDB简单特性_MySQL
建表及插入数据语句: 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 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)