扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
本文主要给大家带来什么是MySQL索引与事务、存储引擎及实例介绍,文章内容都是笔者用心摘选和编辑的,具有一定的针对性,对大家的参考意义还是比较大的,下面跟笔者一起了解下MySQL索引与事务、存储引擎及实例吧。
让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:域名申请、网络空间、营销软件、网站建设、城关网站维护、网站推广。
CREATE INDEX <索引的名字> ON tablename(列的列表);
CREATE INDEX salary index ON IT salary(薪资);
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
CREATE UNIQUE INDEX salary_unique_index ON IT_salary(姓名);
CREATE TABLE tablename ( [..], PRIMARY KEY (列的列表) );
ALTER TABLE tablename ADD PRIMARY KEY (列的列表); //添加表结构的方式创建主键索引
ALTER TABLE IT_ salary ADD PRIMARY KEY (员工ID);
SHOW INDEX FROM tablename;
SHOW KEYS FROM tablename;
SHOW INDEX FROM IT_salary;
SHOW KEYS FROM IT_salary;
mysql> create index index_age on info (age); ##创建普通索引 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##查看表中的索引 +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | index_age | 1 | age | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> drop index index_age on info; ##删除表中的索引 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##查看表中的索引 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec) mysql> create unique index unique_name on info (name); ##创建唯一性索引 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##查看表中索引 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | unique_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> drop index unique_name on info; ##删除表中的索引 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##查看表中的索引 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> alter table info add unique index index_name (name); ##使用alter插入表索引 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##查看表中的索引 +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
mysql> select * from info; ##查看表内容 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | +----+----------+----------+-----+ 2 rows in set (0.00 sec) mysql> show index from info; ##查看表的索引 +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> create fulltext index full_addr on info (address); ##以address创建全文索引 Query OK, 0 rows affected, 1 warning (0.07 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index from info; ##查看表索引 +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | full_addr | 1 | address | NULL | 2 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> select * from user; ##查看user表内容 +----+--------+-------+-------+ | id | name | score | hobby | +----+--------+-------+-------+ | 1 | test01 | 88 | 3 | | 2 | stu01 | 99 | 2 | | 3 | wangwu | 77 | 3 | +----+--------+-------+-------+ 3 rows in set (0.00 sec) mysql> create index index_name_score on user (name,score); ##创建name和score的组合索引 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from user; ##查看表索引 +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_score | 1 | score | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 2 | score | A | 3 | NULL | NULL | | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
mysql> create table user( ##创建user表 -> id int(4) not null primary key auto_increment, ##设置主键和自动增加 -> name varchar(10) not null, -> score decimal not null, -> hobby int(2) not null default '1', ##默认1 -> index index_score (score)); ##设置索引score Query OK, 0 rows affected (0.01 sec) mysql> desc user; ##查看表结构 +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(10,0) | NO | MUL | NULL | | | hobby | int(2) | NO | | 1 | | +-------+---------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3); ##向表中插入数据 Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from user; ##查看表内容 +----+--------+-------+-------+ | id | name | score | hobby | +----+--------+-------+-------+ | 1 | test01 | 88 | 1 | | 2 | stu01 | 99 | 2 | | 3 | wangwu | 77 | 3 | +----+--------+-------+-------+ 3 rows in set (0.00 sec) mysql> create table hob( ##创建hob表 -> id int(2) not null primary key, -> hob_name varchar(10) not null); Query OK, 0 rows affected (0.00 sec) mysql> desc hob; ##查看表结构 +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(2) | NO | PRI | NULL | | | hob_name | varchar(10) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into hob (id,hob_name) values (1,'看书'),(2,'运动'),(3,'听歌'); ##插入表数据 Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from hob; ##查看表内容 +----+----------+ | id | hob_name | +----+----------+ | 1 | 看书 | | 2 | 运动 | | 3 | 听歌 | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from user inner join hob on user.hobby=hob.id; ##关联user和hob两张表 +----+--------+-------+-------+----+----------+ | id | name | score | hobby | id | hob_name | +----+--------+-------+-------+----+----------+ | 1 | test01 | 88 | 1 | 1 | 看书 | | 2 | stu01 | 99 | 2 | 2 | 运动 | | 3 | wangwu | 77 | 3 | 3 | 听歌 | +----+--------+-------+-------+----+----------+ 3 rows in set (0.00 sec) mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id; ##去除其他内容显示name和hob_name内容 +--------+----------+ | name | hob_name | +--------+----------+ | test01 | 看书 | | stu01 | 运动 | | wangwu | 听歌 | +--------+----------+ 3 rows in set (0.00 sec) mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##设置简易名称 +--------+----------+ | name | hob_name | +--------+----------+ | test01 | 看书 | | stu01 | 运动 | | wangwu | 听歌 | +--------+----------+ 3 rows in set (0.00 sec) mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby ##创建视图 Query OK, 0 rows affected (0.00 sec) mysql> select * from view_user; ##查看视图 +--------+----------+ | name | hob_name | +--------+----------+ | test01 | 看书 | | stu01 | 运动 | | wangwu | 听歌 | +--------+----------+ 3 rows in set (0.00 sec) mysql> update user set hobby=3 where name='test01'; ##修改user表中内容 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from view_user; ##查看视图,即视图就是表的一个链接 +--------+----------+ | name | hob_name | +--------+----------+ | stu01 | 运动 | | test01 | 听歌 | | wangwu | 听歌 | +--------+----------+ 3 rows in set (0.00 sec)
mysql> select * from info; ##查看表内容数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | +----+----------+----------+-----+ 2 rows in set (0.00 sec) mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30);##插入数据 mysql> begin; ##开启事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31); ##插入数据 Query OK, 1 row affected (0.00 sec) mysql> savepoint a; ##设置保存节点a Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32); ##继续插入数据 Query OK, 1 row affected (0.00 sec) mysql> savepoint b; ##设置保存节点b Query OK, 0 rows affected (0.00 sec) mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##继续插入数据 Query OK, 1 row affected (0.00 sec) mysql> select * from info; ##查看表内容 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | | 5 | tianqi | hangzhou | 32 | | 6 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 6 rows in set (0.00 sec)
[root@master2 ~]# mysql -uroot -p ##进入数据库 Enter password: ##输入密码 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use school; ##使用数据库 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> select * from info; ##查看表内容,此时并没有提交 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | +----+----------+----------+-----+ 3 rows in set (0.00 sec)
mysql> rollback to b; ##利用回滚到保存节点b Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | | 5 | tianqi | hangzhou | 32 | +----+----------+----------+-----+ 5 rows in set (0.00 sec) mysql> rollback to a; ##回滚到保存节点a Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) mysql> rollback; ##回滚到初始,退出事务状态 Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | +----+----------+----------+-----+ 3 rows in set (0.00 sec)
mysql> begin; ##开启事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##插入数据 Query OK, 1 row affected (0.00 sec) mysql> commit; ##提交事务 Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec)
mysql> select * from info; ##查看表数据 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec)
mysql> set autocommit=0; ##设置不自动提交事务 Query OK, 0 rows affected (0.00 sec) mysql> update info set address='beijing' where name='heiba'; ##修改表数据 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from info; ##查看表信息 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | beijing | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) ##另一个终端查看 mysql> select * from info; ##查看表信息,并没有修改 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) mysql> set autocommit=1; ##开启自动提交事务 Query OK, 0 rows affected (0.00 sec) ##另一个终端查看 mysql> select * from info; ##查看表数据,此时就已经修改 +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | beijing | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec)
mysql> show engines; ##查看默认存储引擎innodb +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | mysql> show create table info; ##查看创建的表的存储引擎innodb | info | CREATE TABLE "info" ( "id" int(4) NOT NULL AUTO_INCREMENT, "name" varchar(10) NOT NULL, "address" varchar(50) DEFAULT 'nanjing', "age" int(3) NOT NULL, PRIMARY KEY ("id"), UNIQUE KEY "index_name" ("name"), FULLTEXT KEY "full_addr" ("address") ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
[root@localhost ~]# vim /etc/my.cnf ##修改配置文件 [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysql.pid socket = /usr/local/mysql/mysql.sock server-id = 1 default-storage-engine=Myisam ##添加默认存储引擎为Myisam [root@master2 ~]# systemctl restart mysqld.service ##重启MySQL服务
[root@master2 ~]# mysql -uroot -p ##进入数据库 Enter password: ##输入密码 mysql> use school; ##使用数据库 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 a ( id int ); ##创建一个a表 Query OK, 0 rows affected (0.00 sec) mysql> show create table a; ##查看表默认的存储引擎Myisam +-------+-------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------+ | a | CREATE TABLE "a" ( "id" int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table a engine=innodb; ##修改表的存储引擎为innodb Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table a; ##查看表的存储引擎innodb +-------+-------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------+ | a | CREATE TABLE "a" ( "id" int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------+1 row in set (0.00 sec)
看完以上关于什么是MySQL索引与事务、存储引擎及实例介绍,很多读者朋友肯定多少有一定的了解,如需获取更多的行业知识信息 ,可以持续关注我们的行业资讯栏目的。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流