一、 常用的查询命令
常用的 MySQL 元数据查询:
查看所有数据库:
SHOW DATABASES;
选择数据库:
USE database_name;
查看数据库中的所有表:
SHOW TABLES;
查看表的结构:
DESC table_name;
查看表的索引:
SHOW INDEX FROM table_name;
查看表的创建语句:
SHOW CREATE TABLE table_name;
查看表的行数:
SELECT COUNT(*) FROM table_name;
查看列的信息:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';
以上SQL 语句中的 ‘your_database_name’ 和 ‘your_table_name’ 分别是你的数据库名和表名。
查看外键信息:
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;
请替换上述 SQL 语句中的 ‘your_database_name’ 和 ‘your_table_name’ 为实际的数据库名和表名。
查看表索引:
SHOW INDEX FROM table_name\G
- SHOW INDEX: 用于显示索引信息的关键字。
- FROM table_name: 指定要查看索引信息的表的名称。
- \G: 格式化输出信息。
information_schema 数据库
information_schema 是 MySQL 数据库中的一个系统数据库,它包含有关数据库服务器的元数据信息,这些信息以表的形式存储在 information_schema 数据库中。
SCHEMATA 表
存储有关数据库的信息,如数据库名、字符集、排序规则等。
SELECT * FROM information_schema.SCHEMATA;
TABLES 表
包含有关数据库中所有表的信息,如表名、数据库名、引擎、行数等。
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘your_database_name’;
COLUMNS 表
包含有关表中列的信息,如列名、数据类型、是否允许 NULL 等。
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;
STATISTICS 表
提供有关表索引的统计信息,如索引名、列名、唯一性等。
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;
KEY_COLUMN_USAGE 表
包含有关表中外键的信息,如外键名、列名、关联表等。
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;
REFERENTIAL_CONSTRAINTS 表
存储有关外键约束的信息,如约束名、关联表等。
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;
二、数据库操作
数据库
连接数据库
cmd连接:
:::tips
cmd连接本地数据库
cmd切换磁盘目录 : C:\Users\Lenovo>cd /d 需要切换的路径
连接:mysql -h主机地址 -u用户名 -p用户密码
Mysql -hlocalhost -u root -proot
断开:exit (回车)
:::
脚本连接(PHP):
<?php
$servername = "localhost"; //主机地址
$username = "root"; //用户名
$password = "root"; //密码
$databass = "test"; //需要连接的数据库名
//创建连接
$conn = mysqli_connect($servername, $username, $password, $databass);
//连接测试
if($conn->connect_error){
die("连接失败" . $conn->connect_error);
}
?>
创建数据库
create 命令创建数据库
如果数据库已经存在,执行 CREATE DATABASE 将导致错误,
为了避免这种情况,你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句:
CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;
删除数据库
drop 命令删除数据库
DROP DATABASE <database_name>; -- 直接删除数据库,不检查是否存在
-- 或
DROP DATABASE [IF EXISTS] <database_name>;
-- IF EXISTS 是一个可选的子句,表示如果数据库存在才执行删除操作,避免因为数据库不存在而引发错误。
-- database_name 是你要删除的数据库的名称。
选择数据库
命令:
:::tips
USE database_name;
:::
- database_name 是你要选择的数据库的名称。
数据表:
创建数据表
创建数据表三个关键信息:表名、表字段名、数据类型。
CREATE TABLE IF NOT EXISTS test (
id int(10) AUTO_INCREMENT PRIMARY KEY COMMENT 'Id',
username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';
-- DEFAULT 'default' 设置默认值default
-- COMMENT '用户名' 设置备注COMMENT(每个字段最后加,表最后也可以加备注)
- id: 用户 id,整数类型,自增长,作为主键。
- username: 用户名,变长字符串,不允许为空。设置默认值default,备注Id
- password: 密码,变长字符串,不允许为空。设置默认值default,备注用户名
- email: 用户邮箱,变长字符串,不允许为空。设置默认值default,备注密码
删除数据表
DROP TABLE table_name ; -- 直接删除表,不检查是否存在
或
DROP TABLE [IF EXISTS] table_name;
插入数据表
使用 INSERT INTO 语句来插入数据
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
- table_name 是你要插入数据的表的名称。
- column1, column2, column3, … 是表中的列名。
- value1, value2, value3, … 是要插入的具体数值。
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...);
查询数据表
select(查询)
使用 SELECT 语句来查询数据:
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
- column1, column2, … 是你想要选择的列的名称,如果使用 * 表示选择所有列。
- table_name 是你要从中查询数据的表的名称。
- WHERE condition 是一个可选的子句,用于指定过滤条件,只返回符合条件的行。
- ORDER BY column_name [ASC | DESC] 是一个可选的子句,用于指定结果集的排序顺序,默认是升序(ASC)。
- LIMIT number 是一个可选的子句,用于限制返回的行数。
:::tips
– 选择所有列的所有行
SELECT * FROM users;
– 选择特定列的所有行
SELECT username, email FROM users;
– 添加 WHERE 子句,选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;
– 添加 ORDER BY 子句,按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;
– 添加 ORDER BY 子句,按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;
– 添加 LIMIT 子句,限制返回的行数
SELECT * FROM users LIMIT 10;
:::
WHERE 子句中,你可以使用各种条件运算符(如 =, <, >, <=, >=, !=),逻辑运算符(如 AND, OR, NOT),以及通配符(如 %)等
:::tips
– 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE ‘j%’ AND is_active = TRUE;
– 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < ‘1990-01-01’;
– 使用 IN 子句
SELECT * FROM users WHERE birthdate IN (‘1990-01-01’, ‘1992-03-15’, ‘1993-05-03’);
:::
where(过滤结果)
WHERE 子句用于在 MySQL 中过滤查询结果,只返回满足特定条件的行。
SELECT column1, column2, ...
FROM table_name
WHERE condition;
-
column1, column2, … 是你要选择的列的名称,如果使用 * 表示选择所有列。
-
table_name 是你要从中查询数据的表的名称。
-
WHERE condition 是用于指定过滤条件的子句。
:::tips
更多说明: -
查询语句中你可以使用一个或者多个表,表之间使用逗号**,** 分割,并使用WHERE语句来设定查询条件。
-
你可以在 WHERE 子句中指定任何条件。
-
你可以使用 AND 或者 OR 指定一个或多个条件。
-
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
-
WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
:::
:::tips
演示:
- 等于条件:
SELECT * FROM users WHERE username = ‘test’; - 不等于条件:
SELECT * FROM users WHERE username != ‘runoob’; - 大于条件:
不等于条件:
SELECT * FROM users WHERE username != ‘runoob’; - 大于条件:
SELECT * FROM products WHERE price > 50.00; - 小于条件:
SELECT * FROM orders WHERE order_date < ‘2023-01-01’; - 大于等于条件:
SELECT * FROM employees WHERE salary >= 50000; - 小于等于条件:
SELECT * FROM students WHERE age <= 21; - 组合条件(AND、OR):
SELECT * FROM products WHERE category = ‘Electronics’ AND price > 100.00;
SELECT * FROM orders WHERE order_date >= ‘2023-01-01’ OR total_amount > 1000.00; - 模糊匹配条件(LIKE):
SELECT * FROM customers WHERE first_name LIKE ‘J%’; - IN 条件:
SELECT * FROM countries WHERE country_code IN (‘US’, ‘CA’, ‘MX’); - NOT 条件:
SELECT * FROM products WHERE NOT category = ‘Clothing’; - BETWEEN 条件:
SELECT * FROM orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’; - IS NULL 条件
SELECT * FROM employees WHERE department IS NULL; - IS NOT NULL 条件:
SELECT * FROM customers WHERE email IS NOT NULL;
:::
like(模糊匹配 )
SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
参数说明:
- column1, column2, … 是你要选择的列的名称,如果使用 * 表示选择所有列。
- table_name 是你要从中查询数据的表的名称。
- column_name 是你要应用 LIKE 子句的列的名称。
- pattern 是用于匹配的模式,可以包含通配符。
更多说明:
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在 WHERE 子句中使用LIKE子句。
- 你可以使用LIKE子句代替等号 =。
- LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
- 百分号通配符 %:
% 通配符表示零个或多个字符。例如,‘a%’ 匹配以字母 ‘a’ 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';
以上 SQL 语句将选择所有姓氏以 ‘S’ 开头的客户。
- 下划线通配符 :
**** 通配符表示一个字符。例如,‘_r%’ 匹配第二个字母为 ‘r’ 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';
以上 SQL 语句将选择产品名称的第二个字符为 ‘a’ 的所有产品。
- 组合使用 % 和 _:
SELECT * FROM users WHERE username LIKE 'a%o_';
以上 SQL 语句将匹配以字母 ‘a’ 开头,然后是零个或多个字符,接着是 ‘o’,最后是一个任意字符的字符串,如 ‘aaron’、‘apol’。
- 不区分大小写的匹配:
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;
以上 SQL 语句将选择姓氏以 ‘smi’ 开头的所有员工,不区分大小写。
UNION (合并)操作符
描述
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合,并去除重复的行。
UNION 操作符必须由两个或多个 SELECT 语句组成,每个 SELECT 语句的列数和对应位置的数据类型必须相同。
语法
MySQL UNION 操作符语法格式:
SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];
参数说明:
- column1, column2, … 是你要选择的列的名称,如果使用 * 表示选择所有列。
- table1, table2, … 是你要从中查询数据的表的名称。
- condition1, condition2, … 是每个 SELECT 语句的过滤条件,是可选的。
- ORDER BY 子句是一个可选的子句,用于指定合并后的结果集的排序顺序。
ORDER BY(排序) 语句
ORDER BY(排序) 语句可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序。
语法
以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
参数说明:
- column1, column2, … 是你要选择的列的名称,如果使用 * 表示选择所有列。
- table_name 是你要从中查询数据的表的名称。
- ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], … 是用于指定排序顺序的子句。ASC 表示升序(默认),DESC 表示降序。
更多说明:
- 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
- 你可以设定多个字段来排序。
- 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
- 你可以添加 WHERE…LIKE 子句来设置条件。
- 单列排序:
SELECT * FROM products
ORDER BY product_name ASC;
- 多列排序:
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
- 使用数字表示列的位置:
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
- 使用表达式排序:
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
- 使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
GROUP BY(分组) 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具,尤其在处理大量数据时,它能够提供有用的汇总信息。
GROUP BY 语法
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
- column1:指定分组的列。
- aggregate_function(column2):对分组后的每个组执行的聚合函数。
- table_name:要查询的表名。
- condition:可选,用于筛选结果的条件。
- GROUP BY 子句通常与聚合函数一起使用,因为分组后需要对每个组进行聚合操作。
- SELECT 子句中的列通常要么是分组列,要么是聚合函数的参数。
- 可以使用多个列进行分组,只需在 GROUP BY 子句中用逗号分隔列名即可。
JOIN 连接
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- **LEFT JOIN(左连接):**获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
INNER JOIN(内连接)
INNER JOIN 返回两个表中满足连接条件的匹配行,以下是 INNER JOIN 语句的基本语法:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
参数说明:
- column1, column2, … 是你要选择的列的名称,如果使用 * 表示选择所有列。
- table1, table2 是要连接的两个表的名称。
- table1.column_name = table2.column_name 是连接条件,指定了两个表中用于匹配的列。
简单的 INNER JOIN:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
多表 INNER JOIN
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;
LEFT JOIN
LEFT JOIN 返回左表的所有行,并包括右表中匹配的行,如果右表中没有匹配的行,将返回 NULL 值,以下是 LEFT JOIN 语句的基本语法:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
1简单的 LEFT JOIN:
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
多表 LEFT JOIN:
SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;
RIGHT JOIN
RIGHT JOIN 返回右表的所有行,并包括左表中匹配的行,如果左表中没有匹配的行,将返回 NULL 值,以下是 RIGHT JOIN 语句的基本语法::
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
简单的 RIGHT JOIN :
SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
更新数据表
UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
参数说明:
table_name 是你要更新数据的表的名称。
column1, column2, … 是你要更新的列的名称。
value1, value2, … 是新的值,用于替换旧的值。
WHERE condition 是一个可选的子句,用于指定更新的行。如果省略 WHERE 子句,将更新表中的所有行。
更多说明:
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
删除数据表记录
DELETE 语句从 MySQL 数据表中删除数据的通用语法:
DELETE FROM table_name
WHERE condition;
参数说明:
table_name 是你要删除数据的表的名称。
WHERE condition 是一个可选的子句,用于指定删除的行。如果省略 WHERE 子句,将删除表中的所有行。
更多说明:
如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
ALTER 命令(修改数据结构)
ALTER 命令用于修改数据库、表和索引等对象的结构。
ALTER 命令允许你添加、修改或删除数据库对象,并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。
1. 添加列
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
以下 SQL 语句在 employees 表中添加了一个名为 birth_date 的日期列:
ALTER TABLE employees
ADD COLUMN birth_date DATE;
2. 修改列的数据类型
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;
以下 SQL 语句将 employees 表中的 salary 列的数据类型修改为 DECIMAL(10,2):
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10,2);
3. 修改列名
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;
以下 SQL 语句将 employees 表中的某个列的名字由 old_column_name 修改为 new_column_name,并且可以同时修改数据类型:
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
4. 删除列
ALTER TABLE table_name
DROP COLUMN column_name;
以下 SQL 语句将 employees 表中的 birth_date 列删除:
ALTER TABLE employees
DROP COLUMN birth_date;
5. 添加 PRIMARY KEY
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
以下 SQL 语句在 employees 表中添加了一个主键:
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);
6. 添加 FOREIGN KEY
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);
以下 SQL 语句在 orders 表中添加了一个外键,关联到 customers 表的 customer_id 列:
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
7. 修改表名
ALTER TABLE old_table_name
RENAME TO new_table_name;
以下 SQL 语句将表名由 employees 修改为 staff:
ALTER TABLE employees
RENAME TO staff;
索引(index)
MySQL 索引是一种数据结构,用于加快数据库查询的速度和性能。
MySQL 索引的建立对于 MySQL 的高效运行是很重要的,索引可以大大提高 MySQL 的检索速度。
索引分单列索引和组合索引:
- 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引。
- 组合索引,即一个索引包含多个列。
普通索引
创建索引
使用 CREATE INDEX 语句可以创建普通索引。
普通索引是最常见的索引类型,用于加速对表中数据的查询。
CREATE INDEX 的语法:
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
- CREATE INDEX: 用于创建普通索引的关键字。
- index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
- table_name: 指定要在哪个表上创建索引。
- (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
修改表结构(添加索引)
我们可以使用 ALTER TABLE 命令可以在已有的表中创建索引。
ALTER TABLE 允许你修改表的结构,包括添加、修改或删除索引。
ALTER TABLE 创建索引的语法:
ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
- ALTER TABLE: 用于修改表结构的关键字。
- table_name: 指定要修改的表的名称。
- ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引。
- index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
- (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
下面是一个实例,我们将在已存在的名为 employees 的表上创建一个普通索引:
ALTER TABLE employees
ADD INDEX idx_age (age);
创建表的时候直接指定
我们可以在创建表的时候,你可以在 CREATE TABLE 语句中直接指定索引,以创建表和索引的组合。
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
...,
INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
- CREATE TABLE: 用于创建新表的关键字。
- table_name: 指定要创建的表的名称。
- (column1, column2, …): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- INDEX: 用于创建普通索引的关键字。
- index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
- (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
下面是一个实例,我们要创建一个名为 students 的表,并在 age 列上创建一个普通索引。
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
INDEX idx_age (age)
);
删除索引的语法
我们可以使用 DROP INDEX 语句来删除索引。
DROP INDEX 的语法:
DROP INDEX index_name ON table_name;
- DROP INDEX: 用于删除索引的关键字。
- index_name: 指定要删除的索引的名称。
- ON table_name: 指定要在哪个表上删除索引。
使用 ALTER TABLE 语句删除索引的语法如下:
ALTER TABLE table_name
DROP INDEX index_name;
- ALTER TABLE: 用于修改表结构的关键字。
- table_name: 指定要修改的表的名称。
- DROP INDEX: 用于删除索引的子句。
- index_name: 指定要删除的索引的名称。
以下实例假设我们有一个名为 employees 的表,并在 age 列上有一个名为 idx_age 的索引,现在我们要删除这个索引:
DROP INDEX idx_age ON employees;
或使用 ALTER TABLE 语句:
ALTER TABLE employees
DROP INDEX idx_age;
这两个命令都会从 employees 表中删除名为 idx_age 的索引。
如果该索引不存在,执行命令时会产生错误。因此,在删除索引之前最好确认该索引是否存在,或者使用错误处理机制来处理可能的错误情况。
唯一索引
创建索引
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
- CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
- index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
- table_name: 指定要在哪个表上创建唯一索引。
- (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- ASC和DESC(可选): 用于指定索引的排序顺序。默认情况下,索引以升序(ASC)排序。
以下是一个创建唯一索引的实例: 假设我们有一个名为 employees的 表,包含 id 和 email 列,现在我们想在email列上创建一个唯一索引,以确保每个员工的电子邮件地址都是唯一的。
CREATE UNIQUE INDEX idx_email ON employees (email);
以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。
修改表结构添加索引
我们可以使用 ALTER TABLE 命令来创建唯一索引。
ALTER TABLE命令允许你修改已经存在的表结构,包括添加新的索引。
ALTER table mytable
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
- ALTER TABLE: 用于修改表结构的关键字。
- table_name: 指定要修改的表的名称。
- ADD CONSTRAINT: 这是用于添加约束(包括唯一索引)的关键字。
- unique_constraint_name : 指定要创建的唯一索引的名称,约束名称在表中必须是唯一的。
- UNIQUE (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
以下是一个使用 ALTER TABLE 命令创建唯一索引的实例:假设我们有一个名为 employees 的表,包含 id 和 email 列,现在我们想在 email 列上创建一个唯一索引,以确保每个员工的电子邮件地址都是唯一的。
ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);
以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。
请注意,如果表中已经有重复的 email 值,那么添加唯一索引将会失败。在创建唯一索引之前,你可能需要确保表中的 email 列没有重复的值。
创建表的时候直接指定
我们也可以在创建表的同时,你可以在 CREATE TABLE 语句中使用 UNIQUE 关键字来创建唯一索引。
这将在表创建时同时定义唯一索引约束。
CREATE TABLE 语句中创建唯一索引的语法:
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
...,
CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
- CREATE TABLE: 用于创建新表的关键字。
- table_name: 指定要创建的表的名称。
- (column1, column2, …): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
- CONSTRAINT: 用于添加约束的关键字。
- index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
- UNIQUE (column1, column2, …): 指定要索引的表列名。
以下是一个在创建表时创建唯一索引的实例:假设我们要创建一个名为 employees 的表,其中包含 id、name 和 email 列,我们希望 email 列的值是唯一的,因此我们要在创建表时定义唯一索引。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
在这个例子中,email 列被定义为唯一索引,因为在它的后面加上了 UNIQUE 关键字。
请注意,使用 UNIQUE 关键字后,索引名称将自动生成,你也可以根据需要指定索引名称。
使用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 ,用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX ©;
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
使用 ALTER 命令添加和删除主键
主键作用于列上(可以一个列或多个列联合主键),添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。
可以通过添加 \G 来格式化输出信息。
SHOW INDEX 语句::
mysql> SHOW INDEX FROM table_name\G
........
- SHOW INDEX: 用于显示索引信息的关键字。
- FROM table_name: 指定要查看索引信息的表的名称。
- \G: 格式化输出信息。
执行上述命令后,将会显示指定表中所有索引的详细信息,包括索引名称(Key_name)、索引列(Column_name)、是否是唯一索引(Non_unique)、排序方式(Collation)、索引的基数(Cardinality)等。