Linux学习86 MariaDB基础操作实战
一、续上集
1、数据类型
d、种类
（1）、字符型：
1）、定长字符型：指的是当你指定要存一行以后如果字段定义成20个字符，无论你用还是不用20个字节都分配给你了，填一个也是20个，填20个也是20个，填多了就给你截断
CHAR（#）：不区分字符大小写
BINARY（#）：区分字符大小写
2）、变长字符型：比如我定义成长30个，你存一个就给你一个空间，存两个就给你两个空间，存30个就给30个空间，长于30个就截断了。
VARCHAR（#）：多占一个或两个字符空间
VARBINARY（#）：
3）、对象存储：
TEXT：文本格式，不区分字符大小写。
TINYTEXT：一个字串所能代表的字符长度空间是256个字符。即2^8
SMALLTEXT：可以存储两个字节所表示的字符长度，65535个，即2^16
MEDIUMTEXT：一千六百多万个字符。即2^24
TEXT：2^32
LONGTEXT：2^64
BLOB（Binary Large OBject）：二进制格式，区分大小写
TINYBLOB 2^8个字符
SMALLBLOB 2^16个字符
MEDIUMBLOB 2^24个字符
BLOB 2^32个字符
LONGBLOB 2^64个字符
4）、内置类型
SET
ENUM：枚举。即我定义好某字段填入的数据只能是哪几种。比如性别字段，只能选男或女。
（2）、数值型
1）、精确数值型
INT（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT）
TINYINT： 2^8个字符，即负128到正127
SMALLINT：2^16个字符
MEDIUMINT：2^24个字符
INT：2^32个字符
BIGINT：2^64个字符
上面都是从负到正的数，如果我们要用无符号的话我们需要加一个UNSIGNED，加了过后就表示最低的那位就不用表示符号。比如TINYINT就从 -128 --127变为了0-255，下面的也是同理
DECIMAL
2）、近似数值型：
FLOAT
DOBULE
（3）、日期时间型
1）、日期型：DATE
2）、时间型：TIME
3）、日期时间型：DATETIME
4）、时间戳：TIMESTAMP
5）、年份：YEAR（2），YEAR（4）
e、字段数据修饰符：
（1）、NOT NULL：非空
（2）、NULL：允许为空
（3）、DEFAULT value：默认值，即数据插入时没有给值的时候给其默认值。
（4）、PRIMARY KEY：主键
（5）、UNIQUE KEY：唯一键
（6）、AUTO_INCREMENT：自动增长 ，只要有AUTO_INCREMENT就必须要定义成主键或唯一键。
f、在mysql中字符串一定要加引号，而数值一定不能加引号
二、服务器端命令
1、DDL：数据定义语言，主要用于管理数据库组件，例如数据库，表，索引，试图，用户，存储过程
a、CREATE、ALTER、DROP
2、DML：数据操纵语言，主要用于管理表中的数据，实现数据的增删改查
b、INSERT，DELETE，UPDATE，SELECT
3、获取命令帮助：help KEYWORD
4、数据库管理（DDL）：
a、创建（HELP CREATE DATABASE）：
（1）、CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name #定义字符集
[DEFAULT] CONLLATE [=] collation_name #定义排序规则
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS hidb; Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [(none)]> SHOW WARNINGS; #这个warnings只能是上一条命令的warning信息 +-------+------+-----------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------+ | Note | 1007 | Can't create database 'hidb'; database exists | +-------+------+-----------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
b、修改
（1）、ALTER {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
MariaDB [(none)]> ALTER DATABASE hidb CHARACTER SET 'utf8'; Query OK, 1 row affected (0.00 sec)
c、删除
（1）、DROP {DATABASE | SCHEMA} {IF EXISTS} db_name；
MariaDB [(none)]> DROP DATABASE testdb; Query OK, 0 rows affected (0.00 sec)
d、查看：
（1）、SHOW DATABASES LIKE ''；假如我们查看以db结尾的数据库
MariaDB [(none)]> show databases like '%db'; +----------------+ | Database (%db) | +----------------+ | hidb | | testdb | +----------------+ 2 rows in set (0.00 sec)
5、表管理
a、创建
（1）、CREATE TABLE [IF NOT EXISTS] [db_name].tbl_name (create_defination) (table_options)
MariaDB [hidb]> CREATE TABLE tbl2(id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY,name VARCHAR(60) NOT NULL); Query OK, 0 rows affected (0.01 sec)
1）、上述意思是创建表tbl2，其中id字段的数据类型为SMALLINT，没有负号，不允许为空，自动增长，为唯一键；第二个字段为name，数据类型为VARCHAR，最长为60个字符，不允许为空。
2）、查看表字段（DESCRIBE或DESC都可以），这儿因为我们id字段中加了NOT NUL又设置为了唯一键，因此这儿显示的为PRIMARY KEY。因为唯一键是可以为空的，但是这儿加了不为空因此就变成了PRIMARY KEY了。
MariaDB [hidb]> desc tbl2; +-------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | | NULL | | +-------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
（2）、create_defination：
1）、字段：col_name data_type
2）、键
PRIMARY KEY（col1，col2，...）
UNIQUE KEY（col1，col2，...）
FOREIGN KEY（column）
3）、索引
KEY|INDEX [index_name] （col1，col2，...）
（3）、table_options：
1）、ENGINE [=] engine_name
2）、CHARACTER SET [=] charset_name：定义表的字符集，可以不同于所在的数据库
3）、COLLATE [=] collation_name：定义排序规则
（4）、查看数据库支持的所有存储引擎类型：
SHOW ENGINES；
（5）、查看某表的存储引擎类型
SHOW TABLES STATUS [LIKE 'tbl_name'] [WHERE clause]
1）、SHOW TABLES STATUS [LIKE 'tb1_name']
MariaDB [hidb]> SHOW TABLE STATUS; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+ -------------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+ -------------------+----------+----------------+---------+| tbl1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | NULL | 2020-05-23 15:27:42 | NULL | NULL | latin1_swedish_ci | NULL | | || tbl2 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | 1 | 2020-05-23 23:29:32 | NULL | NULL | utf8_general_ci | NULL | | || tbl3 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | 1 | 2020-05-23 23:43:02 | NULL | NULL | utf8_general_ci | NULL | | |+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+ -------------------+----------+----------------+---------+3 rows in set (0.01 sec)
上述也可以使用SHOW TABLE STATUS \G来横向查看
2）、查看单独的某张表
MariaDB [hidb]> SHOW TABLE STATUS LIKE 'tbl3' \G *************************** 1. row *************************** Name: tbl3 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 10485760 Auto_increment: 1 Create_time: 2020-05-23 23:43:02 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
3）、也可以使用where匹配
MariaDB [hidb]> SHOW TABLE STATUS WHERE Name LIKE 'tbl%'\G
4）、我们也可以加上我们的引擎
MariaDB [hidb]> CREATE TABLE tbl4 (classid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,classname VARCHAR(100) NOT NULL); Query OK, 0 rows affected (0.01 sec) MariaDB [hidb]> CREATE TABLE tbl5 (classid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,classname VARCHAR(100) NOT NULL) ENGINE 'MyISAM'; Query OK, 0 rows affected (0.02 sec) MariaDB [hidb]> SHOW TABLE STATUS LIKE 'tbl5' \G *************************** 1. row *************************** Name: tbl5 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: 1 Create_time: 2020-05-24 00:15:16 Update_time: 2020-05-24 00:15:16 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
5）、我们也可以查看引擎为MyISAM的表
MariaDB [hidb]> SHOW TABLE STATUS WHERE ENGINE='MyISAM' \G *************************** 1. row *************************** Name: tbl5 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: 1 Create_time: 2020-05-24 00:15:16 Update_time: 2020-05-24 00:15:16 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
（6）、示例
1）、如何上面的示例一样，我们可以把UNIQUE KEY写到后面，并且可以设置多个字段为UNIQUE KEY
MariaDB [hidb]> CREATE TABLE tbl3(id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,name VARCHAR(60) NOT NULL,UNIQUE KEY(id,name)); Query OK, 0 rows affected (0.00 sec)
MariaDB [hidb]> desc tbl3; +-------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | PRI | NULL | | +-------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
b、修改：
（1）、ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tb1_name [alter_specification] [,alter_specification]...]
（2）、alter_specification：
1）、字段：
添加：ADD [COLUMN] col_name data_type [FIRST | AFTER col_name]
MariaDB [hidb]> DESC tbl3; +-------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | PRI | NULL | | +-------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) MariaDB [hidb]> ALTER TABLE tbl3 ADD gender ENUM('F','M'); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | PRI | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
删除：DROP [COLUMN] col_name
MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [hidb]> ALTER TABLE tbl3 DROP gender; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> DESC tbl3; +-------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+----------------+ | stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | | NULL | | +-------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
修改：
修改字段的名称和定义：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | PRI | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [hidb]> ALTER TABLE tbl3 CHANGE id stuid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
修改字段的定义：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]，比如我们要修改字段的数据类型等就可以使用此关键词
2）、键：
添加：ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1，col2，...)
删除：
主键：DROP PRIMARY KEY
外键：DROP FOREIGN KEY fk_symbol
3）、索引
添加：ADD {INDEX|KEY} [index_name] (col1，col2)
我们现在来给我们tbl3的name字段添加一个索引
MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [hidb]> ALTER TABLE tbl3 ADD INDEX(name); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> DESC tbl3; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(60) | NO | MUL | NULL | | | gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
我们来查看索引
MariaDB [hidb]> SHOW INDEXES FROM tbl3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl3 | 0 | PRIMARY | 1 | stuid | A | 0 | NULL | NULL | | BTREE | | | | tbl3 | 0 | id | 1 | stuid | A | 0 | NULL | NULL | | BTREE | | | | tbl3 | 0 | id | 2 | name | A | 0 | NULL | NULL | | BTREE | | | | tbl3 | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.01 sec)
我们可以看到此前这个表因为id是唯一键，我们后来又创建了一个主键，唯一键没删，所以这个唯一键就重复了，所以我们来把id这个索引删除掉，然后就只剩name了
MariaDB [hidb]> ALTER TABLE tbl3 DROP INDEX id; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> SHOW INDEXES FROM tbl3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl3 | 0 | PRIMARY | 1 | stuid | A | 0 | NULL | NULL | | BTREE | | | | tbl3 | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
删除：DROP {INDEX|KEY} index_name
4）、表选项
ENGINE [=] engine_name
（3）、查看表上的索引的信息
SHOW INDEXES FROM tbl_name
（4）、示例
c、删除：
DROP TABLE [IF EXISTS] tbl_name [,tbl_name] ...
d、表的引用方式：
tbl_name
db_name.tbl_name
e、第二种创建方式：
（1）、复制表结构。
CREATE TABLE tbl_name LIKE other_table_name
1）、我们来创建一张和mysql库下user表结构一样的表
MariaDB [hidb]> CREATE TABLE tbl7 LIKE mysql.user; Query OK, 0 rows affected (0.00 sec)
f、第三种创建方式：
CREATE TABLE tbl_name() SELECT clause;
（1）、复制表数据。（这种的话最好再加上字段定义，因为复制的时候是不会加字段定义的）
1）、我们来创建一个表，他拥有mysql库下user表中前三个字段的表结构和相应的数据
MariaDB [hidb]> CREATE TABLE tbl8 SELECT Host,User,Password FROM mysql.user; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [hidb]> DESC tbl8; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Host | char(60) | NO | | | | | User | char(16) | NO | | | | | Password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [hidb]> SELECT * FROM tbl8; +--------------------+------+----------+ | Host | User | Password | +--------------------+------+----------+ | localhost | root | | | www.wohaoshuai.com | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | www.wohaoshuai.com | | | +--------------------+------+----------+ 6 rows in set (0.00 sec)
6、索引管理
a、索引是特殊的数据结构
b、索引：要有索引名称
c、创建：
（1）、CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name(col1，col2,...)
（2）示例
1）、创建索引
MariaDB [hidb]> desc tbl8; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Host | char(60) | NO | | | | | User | char(16) | NO | | | | | Password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [hidb]> CREATE INDEX user ON tbl8(User); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> desc tbl8; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Host | char(60) | NO | | | | | User | char(16) | NO | MUL | | | | Password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [hidb]> SHOW INDEXES FROM tbl8; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl8 | 1 | user | 1 | User | A | 6 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
2）、我们创建Host，User为索引
MariaDB [hidb]> CREATE UNIQUE INDEX host ON tbl8(Host,User); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [hidb]> SHOW INDEXES FROM tbl8; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl8 | 0 | host | 1 | Host | A | 6 | NULL | NULL | | BTREE | | | | tbl8 | 0 | host | 2 | User | A | 6 | NULL | NULL | | BTREE | | | | tbl8 | 1 | user | 1 | User | A | 6 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
d、删除：
（1）、DROP INDEX index_name ON tbl_name
（2）、示例
1）、我们来删host这个index。我们可以看到上面我们创建的HOST这个索引被删除掉了
MariaDB [hidb]> SHOW INDEXES FROM tbl8; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl8 | 0 | host | 1 | Host | A | 6 | NULL | NULL | | BTREE | | | | tbl8 | 0 | host | 2 | User | A | 6 | NULL | NULL | | BTREE | | | | tbl8 | 1 | user | 1 | User | A | 6 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) MariaDB [hidb]> DROP INDEX host ON tbl8; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [hidb]> SHOW INDEXES FROM tbl8; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl8 | 1 | user | 1 | User | A | 6 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
二、作业
1、创建数据库hidb，默认字符集为'utf8'；
2、创建表students，拥有以下字段：
stuid，整数，自动增长
name
gender
birthdate
class
3、将students的class字段修改为classid
4、为name字段添加索引
