mariadb基本操作之索引

最新推荐文章于 2024-07-04 17:32:22 发布

搬砖小胖子

最新推荐文章于 2024-07-04 17:32:22 发布

阅读量1.2k

点赞数

分类专栏： mariadb 文章标签：索引

本文链接：https://blog.csdn.net/qq_39567748/article/details/100020735

版权

mariadb 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在这里插入图片描述

答案：
步骤（2）答案

create table user(userid int(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,username VARCHAR(20) NOT NULL,passwd VARCHAR(20) NOT NULL,info TEXT,UNIQUE index index_uid(userid DESC),index index_user(username,passwd),FULLTEXT index index_info(info))engine=MyISAM;

MariaDB [mybook]> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| userid   | int(10)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   | MUL | NULL    |                |
| passwd   | varchar(20) | NO   |     | NULL    |                |
| info     | text        | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [mybook]> show create table user \G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(20) NOT NULL,
  `info` text,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `userid` (`userid`),
  UNIQUE KEY `index_uid` (`userid`),
  KEY `index_user` (`username`,`passwd`),
  FULLTEXT KEY `index_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

步骤(3)(4)答案

create table information(id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,name VARCHAR(20) NOT NULL,sex VARCHAR(4) NOT NULL,birthday DATE,address VARCHAR(50),tel VARCHAR(20),pic BLOB,index index_name(name(10)),index index_bir(birthday,address));

MariaDB [mybook]> desc information;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | NO   | MUL | NULL    |                |
| sex      | varchar(4)  | NO   |     | NULL    |                |
| birthday | date        | YES  | MUL | NULL    |                |
| address  | varchar(50) | YES  |     | NULL    |                |
| tel      | varchar(20) | YES  |     | NULL    |                |
| pic      | blob        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

MariaDB [mybook]> show create table information \G;
*************************** 1. row ***************************
       Table: information
Create Table: CREATE TABLE `information` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) NOT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `pic` blob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_bir` (`birthday`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

步骤(6)答案

alter table information add UNIQUE index index_id(id ASC);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table information \G;
*************************** 1. row ***************************
       Table: information
Create Table: CREATE TABLE `information` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) NOT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `pic` blob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_bir` (`birthday`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

步骤(7)答案

MariaDB [mybook]> drop index index_user on user;
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table user \G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(20) NOT NULL,
  `info` text,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `userid` (`userid`),
  UNIQUE KEY `index_uid` (`userid`),
  FULLTEXT KEY `index_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

步骤8答案

MariaDB [mybook]> drop index index_name on information;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table information \G;
*************************** 1. row ***************************
       Table: information
Create Table: CREATE TABLE `information` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) NOT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `pic` blob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_bir` (`birthday`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

——————————————————————————————————————————————
在这里插入图片描述
建立表：

MariaDB [mybook]> create table workInfo(id INT(10) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,name VARCHAR(20) NOT NULL,type VARCHAR(20),address VARCHAR(50),wages INT,contents TINYTEXT,extra TEXT,UNIQUE index index_id(id ASC));
Query OK, 0 rows affected (0.04 sec)

MariaDB [mybook]> show create table wordInfo \G;
ERROR 1146 (42S02): Table 'mybook.wordInfo' doesn't exist
ERROR: No query specified

MariaDB [mybook]> show create table workInfo \G;
*************************** 1. row ***************************
       Table: workInfo
Create Table: CREATE TABLE `workInfo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `wages` int(11) DEFAULT NULL,
  `contents` tinytext,
  `extra` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [mybook]> desc workInfo;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | NO   |     | NULL    |                |
| type     | varchar(20) | YES  |     | NULL    |                |
| address  | varchar(50) | YES  |     | NULL    |                |
| wages    | int(11)     | YES  |     | NULL    |                |
| contents | tinytext    | YES  |     | NULL    |                |
| extra    | text        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

步骤(2)答案：

MariaDB [mybook]> alter table workInfo add index index_name(name(10));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table workInfo \G;
*************************** 1. row ***************************
       Table: workInfo
Create Table: CREATE TABLE `workInfo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `wages` int(11) DEFAULT NULL,
  `contents` tinytext,
  `extra` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_name` (`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

步骤3答案

MariaDB [mybook]> alter table workInfo add index index_t(type,address);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table workInfo \G;
*************************** 1. row ***************************
       Table: workInfo
Create Table: CREATE TABLE `workInfo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `wages` int(11) DEFAULT NULL,
  `contents` tinytext,
  `extra` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_t` (`type`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

步骤4 5答案

MariaDB [mybook]> alter table workInfo engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> alter table workInfo add FULLTEXT index index_ext(extra);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table workInfo \G;
*************************** 1. row ***************************
       Table: workInfo
Create Table: CREATE TABLE `workInfo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `wages` int(11) DEFAULT NULL,
  `contents` tinytext,
  `extra` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_t` (`type`,`address`),
  FULLTEXT KEY `index_ext` (`extra`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

步骤6答案

MariaDB [mybook]> drop index index_id on workInfo;
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mybook]> show create table workInfo \G;
*************************** 1. row ***************************
       Table: workInfo
Create Table: CREATE TABLE `workInfo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `wages` int(11) DEFAULT NULL,
  `contents` tinytext,
  `extra` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_t` (`type`,`address`),
  FULLTEXT KEY `index_ext` (`extra`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1