数据库(中)

MySQL基本语法（补）

排序

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

可以设定多个字段来排序

这语法怎么感觉奇奇怪怪的？是需要table1和table2有具有相同属性的列才可以如此书写吧？

实例：

MariaDB [RUNOOB]> SELECT * FROM runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
+-----------+--------------+---------------+-----------------+
2 rows in set (0.001 sec)

MariaDB [RUNOOB]> INSERT INTO runoob_tbl
    -> VALUES
    -> (5, "java", "runoob", "2020-10-10");
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> SELECT * FROM runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
|         5 | java         | runoob        | 2020-10-10      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.000 sec)

MariaDB [RUNOOB]> SELECT * from runoob_tbl ORDER BY submission_date ASC;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         5 | java         | runoob        | 2020-10-10      |
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.001 sec)

MariaDB [RUNOOB]> SELECT * from runoob_tbl ORDER BY submission_date DESC;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | python       | RUNOOB        | 2023-03-26      |
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         5 | java         | runoob        | 2020-10-10      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.001 sec)

上面三种为默认情况下select以及升序，降序排列后的结果

数据导入

source命令导入

基本流程：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

下面是一份.sql文件

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

导入数据

MariaDB [RUNOOB]> source /home/frechen026/桌面/writing/data1.sql
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected (0.009 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 6 rows affected (0.001 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [RUNOOB]> show tables;
+------------------+
| Tables_in_RUNOOB |
+------------------+
| Websites         |
| apps             |
| employee_tbl     |
| runoob_tbl       |
+------------------+
4 rows in set (0.001 sec)

MariaDB [RUNOOB]> SELECT * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.001 sec)

导出数据

导出 SQL 格式的数据

frechen026@debian:~/桌面$ mysqldump -u root -p RUNOOB runoob_tbl > /home/frechen026/桌面/writing/1.txt
Enter password:

写入后的文件内容(删去了一堆奇奇怪怪的注释，诸如/*!40101 xxx)：

-- MariaDB dump 10.19  Distrib 10.5.18-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: RUNOOB
-- ------------------------------------------------------
-- Server version	10.5.18-MariaDB-0+deb11u1

--
-- Table structure for table `runoob_tbl`
--

DROP TABLE IF EXISTS `runoob_tbl`;

CREATE TABLE `runoob_tbl` (
  `runoob_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `runoob_tbl`
--

LOCK TABLES `runoob_tbl` WRITE;

INSERT INTO `runoob_tbl` VALUES (1,'python','菜鸟教程','2023-03-25'),(2,'python','RUNOOB','2023-03-26'),(5,'java','runoob','2020-10-10');

UNLOCK TABLES;

-- Dump completed on 2023-04-20 11:31:31

使用 SELECT … INTO OUTFILE 语句导出数据

会报无读写文件权限，挺奇怪的，执行mysql时不都已经sudo了吗

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

写到tmp文件夹可行，看来还得好好了解了解linux，写入后数据如下

1	python	菜鸟教程	2023-03-25
2	python	RUNOOB	2023-03-26
5	java	runoob	2020-10-10

分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

通用语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

MariaDB [RUNOOB]> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽   |        1 |
| 小明   |        3 |
| 小王   |        2 |
+--------+----------+
3 rows in set (0.001 sec)

划分的标准仅仅是名字是否相同吗？

连接

使用 MySQL 的 JOIN 在两个或多个表中查询数据

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

创建两张表加以验证

MariaDB [RUNOOB]> CREATE TABLE `tcount_tbl`(
    -> `runoob_author` VARCHAR(30),
    -> `runoob_count` INT
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.012 sec)

MariaDB [RUNOOB]> INSERT INTO tcount_tbl
    -> VALUES
    -> ("菜鸟教程", 10),
    -> ("RUNOOB.COM", 20),
    -> ("Google", 22);
Query OK, 3 rows affected (0.004 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> CREATE TABLE `runoob_tbl`(
    -> `runoob_id` INT,
    -> `runoob_title` VARCHAR(30),
    -> `runoob_author` VARCHAR(30),
    -> `submission_date` DATE
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.010 sec)

MariaDB [RUNOOB]> INSERT INTO runoob_tbl
    -> VALUES
    -> (1, "学习 PHP", "菜鸟教程", '2017-04-12'),
    -> (2, "学习 MySQL", "菜鸟教程", '2017-04-12'),
    -> (3, "学习 Java", "RUNOOB.COM", '2015-05-01'),
    -> (4, "学习 Python", "RUNOOB.COM", '2016-03-06'),
    -> (5, "学习 C", "FK", '2017-04-05');
Query OK, 5 rows affected (0.003 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.001 sec)

MariaDB [RUNOOB]> SELECT * FROM runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C        | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.001 sec)

注意：日期写法要加单引号，创建表格时表格名以及列名均用反引号括起来

INNER JOIN

table1和table2的交集

用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值

MariaDB [RUNOOB]> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.001 sec)

LEFT JOIN

table1

MySQL LEFT JOIN 会读取左边数据表的全部数据，即使右边表无对应数据。

MariaDB [RUNOOB]> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+
5 rows in set (0.001 sec)

RIGHT JOIN

table2

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即使左边边表无对应数据。

MariaDB [RUNOOB]> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|      NULL | NULL          |           22 |
+-----------+---------------+--------------+
5 rows in set (0.001 sec)

NULL处理

MariaDB [RUNOOB]> select * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.001 sec)

MariaDB [RUNOOB]> INSERT INTO tcount_tbl
    -> values
    -> ("frechen026", NULL);
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> select * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
| frechen026    |         NULL |
+---------------+--------------+
4 rows in set (0.001 sec)

实例：选出count取值非空的数据

MariaDB [RUNOOB]> select * from tcount_tbl where runoob_count != NULL;
Empty set (0.001 sec)

发现使用 != 无效

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：

MariaDB [RUNOOB]> select * from tcount_tbl where runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.001 sec)

正则表达式

在前面的章节我们已经了解到MySQL可以通过 LIKE …% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

要用的时候对照表格即可，下面是以2017开头的数据通过正则表达式给出

MariaDB [RUNOOB]> SELECT * FROM runoob_tbl WHERE submission_date  REGEXP '^2017'
;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2017-04-12      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.001 sec)

ALTER命令

需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

可以使用SHOW COLUMNS FROM table查看数据表中各列的属性

先创建进行实验的数据表

MariaDB [RUNOOB]> create table testalter_tbl
    -> (i INT, c CHAR(1));
Query OK, 0 rows affected (0.011 sec)

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.002 sec)

删除表字段

通用语法：

ALTER TABLE table_name DROP column_name;

实例：

删除i字段

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl DROP i;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.002 sec)

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl DROP c;
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead

增加表字段

ALTER TABLE table_name ADD column_name type [NOT NULL];

实例：
重新添加i表字段且将其置为NOT NULL，将自动添加到数据表字段的末尾

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl ADD i INT NOT NULL;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.002 sec)

指定添加的位置

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）

实例：添加A字段到首位，将B字段添加到其后

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl ADD A INT FIRST;
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl ADD B INT AFTER A;
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

实例：把字段 c 的类型从 CHAR(1) 改为 CHAR(10)

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
Query OK, 0 rows affected (0.025 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| A     | int(11)  | YES  |     | NULL    |       |
| B     | int(11)  | YES  |     | NULL    |       |
| c     | char(10) | YES  |     | NULL    |       |
| i     | int(11)  | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.002 sec)

似乎按照MODIFY的语法只能改变字段的类型？

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

实例：将c的类型改回chat(1)

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl CHANGE c c CHAR(1);
Query OK, 0 rows affected (0.024 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

MariaDB [RUNOOB]> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
Query OK, 0 rows affected (0.006 sec)

MariaDB [RUNOOB]> show tables;
+------------------+
| Tables_in_RUNOOB |
+------------------+
| Websites         |
| alter_tbl        |
| apps             |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
5 rows in set (0.001 sec)

索引

普通索引

基本做法

添加索引

通用语法：

CREATE INDEX indexName ON table_name (column_name)

实例：为alter_tbl表中的c添加索引

MariaDB [RUNOOB]> CREATE INDEX index_test ON alter_tbl (c);
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM alter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  | MUL | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

以下错误

MariaDB [RUNOOB]> CREATE INDEX index ON alter_tbl (c);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'index ON alter_tbl (c)' at line 1

mysql对于语法是不在意大小写的，所以添加名为index的索引，与关键字冲突，错误！

删除索引

通用语法：

DROP INDEX indexName ON table;

实例：删除刚刚创建的索引

MariaDB [RUNOOB]> drop index index_test on alter_tbl;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER语句

ALTER table tableName ADD INDEX indexName(columnName)

实例：为alter_tbl表中的c添加索引

MariaDB [RUNOOB]> ALTER table alter_tbl ADD INDEX index_test(c);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM alter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  | MUL | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

创建表格时指定

MariaDB [RUNOOB]> CREATE TABLE mytable(
    -> ID INT NOT NULL,
    -> username VARCHAR(16) NOT NULL,
    -> INDEX (username)
    -> );
Query OK, 0 rows affected (0.015 sec)

MariaDB [RUNOOB]> SHOW COLUMNS FROM mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

基本做法

CREATE UNIQUE INDEX indexName ON table_name (column_name)

MariaDB [RUNOOB]> CREATE UNIQUE INDEX index_test ON alter_tbl (c);
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM alter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  | UNI | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

ALTER语句

MariaDB [RUNOOB]> SHOW COLUMNS FROM alter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  | UNI | NULL    |       |
| i     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

MariaDB [RUNOOB]> ALTER table alter_tbl ADD UNIQUE INDEX index_test(i);
Query OK, 0 rows affected (0.028 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SHOW COLUMNS FROM alter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| A     | int(11) | YES  |     | NULL    |       |
| B     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.002 sec)

c直接被取代了吗？

创建表格时指定

MariaDB [RUNOOB]> DROP table mytable;
Query OK, 0 rows affected (0.006 sec)

MariaDB [RUNOOB]> CREATE TABLE mytable(
    -> ID INT NOT NULL,
    -> username VARCHAR(16) NOT NULL,
    -> UNIQUE INDEX (username)
    -> );
Query OK, 0 rows affected (0.010 sec)

MariaDB [RUNOOB]> SHOW COLUMNS FROM mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.002 sec)

key的PRI莫非是主键的意思？看了下创建表时指定主键的表的列属性，确实如此

奇怪了，上面还是UNI呢，这里怎么直接转成PRI了，留个坑

哦，好像似乎NOT NULL的就是PRI,NULL的就是UNI,看来还挺符合主键的要求的

使用ALTER 命令添加和删除索引

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

也可以使用 ALTER 命令删除主键：

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

MariaDB [RUNOOB]> ALTER TABLE employee_tbl DROP PRIMARY KEY;
Query OK, 6 rows affected (0.026 sec)              
Records: 6  Duplicates: 0  Warnings: 0

显示索引信息

SHOW INDEX FROM table_name\G

MariaDB [RUNOOB]> SHOW INDEX FROM alter_tbl;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| alter_tbl |          0 | index_test |            1 | i           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.001 sec)

MariaDB [RUNOOB]> SHOW INDEX FROM alter_tbl\G;
*************************** 1. row ***************************
        Table: alter_tbl
   Non_unique: 0
     Key_name: index_test
 Seq_in_index: 1
  Column_name: i
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.001 sec)

窗口不够的话上面那种看着确实不舒服。