本文主要是介绍mysql 学习---- 查看引擎、myisam引擎、自增长、主外键关联、memory引擎、merge引擎,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
- 1.查看引擎
- mysql> show variables like 'table_type';
- mysql> show engines \G
- *************************** 1. row ***************************
- Engine: InnoDB
- Support: DEFAULT
- Comment: Supports transactions, row-level locking, and foreign keys
- Transactions: YES
- XA: YES
- Savepoints: YES
- *************************** 2. row ***************************
- Engine: PERFORMANCE_SCHEMA
- Support: YES
- Comment: Performance Schema
- Transactions: NO
- XA: NO
- Savepoints: NO
- *************************** 3. row ***************************
- Engine: MRG_MYISAM
- Support: YES
- Comment: Collection of identical MyISAM tables
- Transactions: NO
- XA: NO
- Savepoints: NO
- *************************** 4. row ***************************
- Engine: CSV
- Support: YES
- Comment: CSV storage engine
- Transactions: NO
- XA: NO
- Savepoints: NO
- *************************** 5. row ***************************
- Engine: MyISAM
- Support: YES
- Comment: MyISAM storage engine
- Transactions: NO
- XA: NO
- Savepoints: NO
- *************************** 6. row ***************************
- Engine: MEMORY
- Support: YES
- Comment: Hash based, stored in memory, useful for temporary tables
- Transactions: NO
- XA: NO
- Savepoints: NO
-
- mysql> show variables like 'have%';
- +----------------------+-------+
- | Variable_name | Value |
- +----------------------+-------+
- | have_compress | YES |
- | have_crypt | YES |
- | have_csv | YES |
- | have_dynamic_loading | YES |
- | have_geometry | YES |
- | have_innodb | YES |
- | have_ndbcluster | NO |
- | have_openssl | NO |
- | have_partitioning | YES |
- | have_profiling | YES |
- | have_query_cache | YES |
- | have_rtree_keys | YES |
- | have_ssl | NO |
- | have_symlink | YES |
- +----------------------+-------+
- 2.myisam引擎相关
- mysql> use test1;
- Database changed
- mysql> create table ai(
- -> i bigint(20) not null auto_increment,
- -> primary key(i)
- -> ) engine=myisam default charset=gbk;
- mysql> create table country(
- -> country_id smallint unsigned not null auto_increment,
- -> country varchar(50) not null,
- -> last_update timestamp not null default current_timestamp on update current_timestamp, primary key(country_id)
- -> ) engine = innodb default charset=gbk;
- mysql> alter table ai engine = innodb;
- 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
- ERROR:
- No query specified
- mysql> create table myisam_char(name char(10)) engine=myisam;
- Query OK, 0 rows affected (0.01 sec)
- mysql> insert into myisam_char values('abcde'),('abcde '),(' abcde'),(' abcde ');
-
- mysql> select name,length(name) from myisam_char;
- +------------+--------------+
- | name | length(name) |
- +------------+--------------+
- | abcde | 5 |
- | abcde | 5 |
- | abc | 10 |
- | abc | 10 |
- +------------+--------------+
- mysql> truncate myisam_char;
- mysql> select * from myisam_char;
- mysql> insert into myisam_char values
- -> ('abcde'),
- -> ('abcde '),
- -> (' abcde'),
- -> (' abcde ');
- mysql> select * from myisam_char;
- +---------+
- | name |
- +---------+
- | abcde |
- | abcde |
- | abcde |
- | abcde |
- +---------+
- mysql> select name,length(name) from myisam_char;
- +---------+--------------+
- | name | length(name) |
- +---------+--------------+
- | abcde | 5 |
- | abcde | 5 |
- | abcde | 7 |
- | abcde | 7 |
- +---------+--------------+
- 3.自增长
- mysql> use test1;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> create table autoincre_demo
- -> ( i smallint not null auto_increment,
- -> name varchar(10),primary key(i)
- -> ) engine = innodb;
- mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
-
- mysql> select * from autoincre_demo;
- +---+------+
- | i | name |
- +---+------+
- | 1 | 1 |
- | 2 | 2 |
- | 3 | 3 |
- +---+------+
- mysql> insert into autoincre_demo values(4,'4');
- mysql> select last_insert_id();
- +------------------+
- | last_insert_id() |
- +------------------+
- | 2 |
- +------------------+
- mysql> insert into autoincre_demo(name) values('5'),('6'),('7');
- mysql> select last_insert_id();
- +------------------+
- | last_insert_id() |
- +------------------+
- | 5 |
- +------------------+
- mysql> alter table autoincre_demo rename autoincre_demo_old;
- mysql> create table autoincre_demo (d1 smallint not null auto_increment, d2 smallint not null, name varchar(10), index(d2,d1) ) engine = myisam;
-
- mysql> insert into autoincre_demo (d2,name) values (2,'2'), (3,'3'), (4,'4'), (2,'2'), (3,'3'), (4,'4');
- 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 |
- +----+----+------+
- 4.主外键关联
- mysql> alter table country rename country_old;
- mysql> create table country( country_id smallint unsigned not null auto_increment, country varchar(50) not null, last_update timestamp not null default current_timestamp on update current_timestamp, primary key( country_id) ) engine = innodb default charset=utf8;
- mysql> create table city(
- -> city_id smallint unsigned not null auto_increment,
- -> city varchar(50) not null, country_id smallint unsigned not null,
- -> last_update timestamp not null default current_timestamp on update current_timestamp,
- -> primary key(city_id), key idx_fk_country_id(country_id),
- -> constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade
- -> ) engine = innodb default charset=utf8;
- mysql> insert into country(country_id,country) values (1,'tom');
- mysql> select * from country where country_id =1;
- +------------+---------+---------------------+
- | country_id | country | last_update |
- +------------+---------+---------------------+
- | 1 | tom | 2015-10-02 20:48:15 |
- +------------+---------+---------------------+
- mysql> insert into city(city_id,city,country_id) values ('251','bill',1);
- mysql> select * from city where country_id = 1;
- +---------+------+------------+---------------------+
- | city_id | city | country_id | last_update |
- +---------+------+------------+---------------------+
- | 251 | bill | 1 | 2015-10-02 20:48:51 |
- +---------+------+------------+---------------------+
- mysql> update country set country_id = 10000 where country_id = 1;
- mysql> select * from country where country='tom';
- +------------+---------+---------------------+
- | country_id | country | last_update |
- +------------+---------+---------------------+
- | 10000 | tom | 2015-10-02 20:49:29 |
- +------------+---------+---------------------+
- mysql> select * from city where city_id = 251;
- +---------+------+------------+---------------------+
- | city_id | city | country_id | last_update |
- +---------+------+------------+---------------------+
- | 251 | bill | 10000 | 2015-10-02 20:48:51 |
- +---------+------+------------+---------------------+
- mysql> show table status like 'city' \G
- *************************** 1. row ***************************
- Name: city
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 1
- Avg_row_length: 16384
- Data_length: 16384
- Max_data_length: 0
- Index_length: 16384
- Data_free: 0
- Auto_increment: 252
- Create_time: 2015-10-02 20:47:27
- Update_time: NULL
- Check_time: NULL
- Collation: utf8_general_ci
- Checksum: NULL
- Create_options:
- Comment:
- mysql> show table status like 'country' \G
- *************************** 1. row ***************************
- Name: country
- Engine: InnoDB
- Version: 10
- Row_format: Compact
- Rows: 1
- Avg_row_length: 16384
- Data_length: 16384
- Max_data_length: 0
- Index_length: 0
- Data_free: 0
- Auto_increment: 2
- Create_time: 2015-10-02 20:42:25
- Update_time: NULL
- Check_time: NULL
- Collation: utf8_general_ci
- Checksum: NULL
- Create_options:
- Comment:
- mysql> desc country;
- +-------------+----------------------+------+-----+-------------------+-----------------------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------------+----------------------+------+-----+-------------------+-----------------------------+
- | country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
- | country | varchar(50) | NO | | NULL | |
- | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
- +-------------+----------------------+------+-----+-------------------+-----------------------------+
- mysql> select * from city;
- +---------+------+------------+---------------------+
- | city_id | city | country_id | last_update |
- +---------+------+------------+---------------------+
- | 251 | bill | 10000 | 2015-10-02 20:48:51 |
- +---------+------+------------+---------------------+
- 5.memory引擎
- mysql> create table tab_memory engine = memory select city_id,city,country_id from city group by city_id;
- mysql> select count(*) from tab_memory;
- +----------+
- | count(*) |
- +----------+
- | 1 |
- +----------+
- mysql> show table status like 'tab_memory' \G
- *************************** 1. row ***************************
- Name: tab_memory
- Engine: MEMORY
- Version: 10
- Row_format: Fixed
- Rows: 1
- Avg_row_length: 155
- Data_length: 127040
- Max_data_length: 32505825
- Index_length: 0
- Data_free: 0
- Auto_increment: NULL
- Create_time: 2015-10-02 20:53:16
- Update_time: NULL
- Check_time: NULL
- Collation: utf8_general_ci
- Checksum: NULL
- Create_options:
- Comment:
- mysql> create index mem_hash using hash on tab_memory(city_id);
- mysql> show index from tab_memory \G;
- *************************** 1. row ***************************
- Table: tab_memory
- Non_unique: 1
- Key_name: mem_hash
- Seq_in_index: 1
- Column_name: city_id
- Collation: NULL
- Cardinality: 0
- Sub_part: NULL
- Packed: NULL
- Null:
- Index_type: HASH
- Comment:
- Index_comment:
- ERROR:
- No query specified
- mysql> drop index mem_hash on tab_memory;
- mysql> create index mem_hash using btree on tab_memory(city_id);
- mysql> show index from tab_memory \G
- *************************** 1. row ***************************
- Table: tab_memory
- Non_unique: 1
- Key_name: mem_hash
- Seq_in_index: 1
- Column_name: city_id
- Collation: A
- Cardinality: NULL
- Sub_part: NULL
- Packed: NULL
- Null:
- Index_type: BTREE
- Comment:
- Index_comment:
- 6.merge引擎
- mysql> use test1;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> create table payment_2006( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;
- mysql> create table payment_2007( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;
- mysql> create table payment_all(
- -> country_id smallint,
- -> payment_date datetime,
- -> amount decimal(15,2),
- -> index(country_id)
- -> )engine=merge union=(payment_2006,payment_2007) insert_method=last;
- mysql> insert into payment_2006
- -> values(1,'2006-05-01',100000),
- -> (2,'2006-08-15',150000);
- mysql> insert into payment_2007
- -> values(1,'2007-02-20',35000),
- -> (2,'2007-07-15',220000);
- mysql> select * from payment_2006;
- +------------+---------------------+-----------+
- | country_id | payment_date | amount |
- +------------+---------------------+-----------+
- | 1 | 2006-05-01 00:00:00 | 100000.00 |
- | 2 | 2006-08-15 00:00:00 | 150000.00 |
- +------------+---------------------+-----------+
- mysql> select * from payment_2007;
- +------------+---------------------+-----------+
- | country_id | payment_date | amount |
- +------------+---------------------+-----------+
- | 1 | 2007-02-20 00:00:00 | 35000.00 |
- | 2 | 2007-07-15 00:00:00 | 220000.00 |
- +------------+---------------------+-----------+
- mysql> select * from payment_all;
- +------------+---------------------+-----------+
- | country_id | payment_date | amount |
- +------------+---------------------+-----------+
- | 1 | 2006-05-01 00:00:00 | 100000.00 |
- | 2 | 2006-08-15 00:00:00 | 150000.00 |
- | 1 | 2007-02-20 00:00:00 | 35000.00 |
- | 2 | 2007-07-15 00:00:00 | 220000.00 |
- +------------+---------------------+-----------+
- mysql> insert into payment_all
- -> values(3,'2006-03-31',112200);
- mysql> select * from payment_all;
- +------------+---------------------+-----------+
- | country_id | payment_date | amount |
- +------------+---------------------+-----------+
- | 1 | 2006-05-01 00:00:00 | 100000.00 |
- | 2 | 2006-08-15 00:00:00 | 150000.00 |
- | 1 | 2007-02-20 00:00:00 | 35000.00 |
- | 2 | 2007-07-15 00:00:00 | 220000.00 |
- | 3 | 2006-03-31 00:00:00 | 112200.00 |
- +------------+---------------------+-----------+
- mysql> select * from payment_2007;
- +------------+---------------------+-----------+
- | country_id | payment_date | amount |
- +------------+---------------------+-----------+
- | 1 | 2007-02-20 00:00:00 | 35000.00 |
- | 2 | 2007-07-15 00:00:00 | 220000.00 |
- | 3 | 2006-03-31 00:00:00 | 112200.00 |
- +------------+---------------------+-----------+
-
这篇关于mysql 学习---- 查看引擎、myisam引擎、自增长、主外键关联、memory引擎、merge引擎的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!