Mysql命令行

Mysql命令行

连接数据库

mysql -u root -p

关闭

exit

列出所有可用的数据库：

SHOW DATABASES;

选择要使用的数据库：

USE your_database;

列出所选数据库中的所有表：

SHOW TABLES;

创建数据库

CREATE DATABASE 数据库名;
/* 如果数据库已经存在，执行 CREATE DATABASE 将导致错误。
为了避免这种情况，你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 
子句：*/
CREATE DATABASE IF NOT EXISTS mydatabase;

删除数据库 drop 命令格式：

/* 参数说明：
IF EXISTS 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误。
database_name 是你要删除的数据库的名称。
*/
DROP DATABASE <database_name>; -- 直接删除数据库，不检查是否存在
或
DROP DATABASE IF EXISTS <database_name>;

创建数据表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);
/* 实例解析：
id: 用户 id，整数类型，自增长，作为主键。
username: 用户名，变长字符串，不允许为空。
email: 用户邮箱，变长字符串，不允许为空。
birthdate: 用户的生日，日期类型。
is_active: 用户是否已经激活，布尔类型，默认值为 true。
以上只是一个简单的实例，用到了一些常见的数据类型包括 INT, VARCHAR, DATE, BOOLEAN，你可以根据实际需要选择不同的数据类型。AUTO_INCREMENT 关键字用于创建一个自增长的列，PRIMARY KEY 用于定义主键。 */

//如果你希望在创建表时指定数据引擎，字符集和排序规则等，可以使用 CHARACTER SET 和 COLLATE 子句：
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
/*以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。*/

//以下例子中我们将在 RUNOOB 数据库中创建数据表 runoob_tbl：
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：
如果你不想字段为空可以设置字段的属性为 NOT NULL，如上实例中的 runoob_title 与 runoob_author 字段， 在操作数据库时如果输入该字段的数据为空，就会报错。
AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号 , 分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表

DROP TABLE table_name ;    -- 直接删除表，不检查是否存在
或
DROP TABLE IF EXISTS table_name;、
参数说明：

table_name 是要删除的表的名称。
IF EXISTS 是一个可选的子句，表示如果表存在才执行删除操作，避免因为表不存在而引发错误。

插入数据（增）

//插入了一行数据到名为 users 的表中：
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);

//如果你要插入所有列的数据，可以省略列名：
//NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。
INSERT INTO users
VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);

//要插入多行数据，可以在 VALUES 子句中指定多组数值：
INSERT INTO users (username, email, birthdate, is_active)
VALUES
    ('test1', 'test1@runoob.com', '1985-07-10', true),
    ('test2', 'test2@runoob.com', '1988-11-25', false),
    ('test3', 'test3@runoob.com', '1993-05-03', true);

查询数据（查）

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 是一个可选的子句，用于限制返回的行数。
*/
-- 选择所有列的所有行
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 子句（查）

/* 如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。*/
SELECT column1, column2, ...
FROM table_name
WHERE condition;
/* 
参数说明：
column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
table_name 是你要从中查询数据的表的名称。
WHERE condition 是用于指定过滤条件的子句。

更多说明：
查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
*/

UPDATE 更新（改）

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
/*
参数说明：
table_name 是你要更新数据的表的名称。
column1, column2, ... 是你要更新的列的名称。
value1, value2, ... 是新的值，用于替换旧的值。
WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行。

更多说明：
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
*/
//示例
//1. 更新单个列的值：
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
//2. 更新多个列的值：
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;
//3. 使用表达式更新值：
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';
//4. 更新符合条件的所有行：
UPDATE students
SET status = 'Graduated';
//以上 SQL 语句将所有学生的状态更新为 'Graduated'。
//5. 更新使用子查询的值：
UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';
//以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额，并将该值更新到 total_purchases 列中。

DELETE 语句（删）

DELETE FROM table_name
WHERE condition;
/*
参数说明：
table_name 是你要删除数据的表的名称。
WHERE condition 是一个可选的子句，用于指定删除的行。如果省略 WHERE 子句，将删除表中的所有行。

更多说明：
如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
*/
//实例
//1. 删除符合条件的行：
DELETE FROM students
WHERE graduation_year = 2021;
//以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。

//2. 删除所有行：
DELETE FROM orders;
//以上 SQL 语句删除了 orders 表中的所有记录，但表结构保持不变。

//3. 使用子查询删除符合条件的行：
DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);
//以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。

ORDER BY(排序) 语句

//以下是 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 子句来设置条件。
*/
//1. 单列排序：
SELECT * FROM products
ORDER BY product_name ASC;
/*以上 SQL 语句将选择产品表 products 中的所有产品，并按产品名称升序 ASC 排序。*/
//2. 多列排序：
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
/*以上 SQL 语句将选择员工表 employees 中的所有员工，并先按部门 ID 升序 ASC 排序，然后在相同部门中按雇佣日期降序 DESC 排序。*/

GROUP BY 语句

//GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
/*column1：指定分组的列。
aggregate_function(column2)：对分组后的每个组执行的聚合函数。
table_name：要查询的表名。
condition：可选，用于筛选结果的条件。*/

连接的使用(多表查)

* 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;
/*以上 SQL 语句将选择 orders 表和 customers 表中满足连接条件的订单 ID 和客户名称。*/

//2. 使用表别名：
SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
/*以上 SQL 语句使用表别名 o 和 c 作为 orders 和 customers 表的别名。*/

//3. 多表 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;
/*以上 SQL 语句涉及了 orders、customers、order_items 和 products 四个表的连接。它选择了订单 ID、客户名称和产品名称，连接了这些表的关联列。*/

//4. 使用 WHERE 子句进行过滤：
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';
/*以上 SQL 语句在 INNER JOIN 后使用 WHERE 子句，过滤了订单日期在 '2023-01-01' 及以后的订单。*/

* LEFT JOIN（左连接）：LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值。

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;
/*以上 SQL 语句将选择客户表中的客户 ID 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 ID（如果有的话）。*/

//2. 使用表别名：
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
/*以上 SQL 语句使用表别名 c 和 o 分别代替 customers 和 orders 表的名称。*/

//3. 多表 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;
/*以上 SQL 语句连接了 customers、orders、order_items 和 products 四个表，并选择了客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息。*/

//4. 使用 WHERE 子句进行过滤：
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL;
/*以上 SQL 语句在 LEFT JOIN 后使用 WHERE 子句，过滤了订单日期在 '2023-01-01' 及以后的订单，以及没有匹配订单的客户。*/

* RIGHT JOIN（右连接）（不常用）： 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值。

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
//示例
SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
/*以上 SQL 语句将选择右表 orders 中的所有订单 ID，并包括左表 customers 中匹配的客户 ID。如果在 customers 表中没有匹配的客户 ID，相关列将显示为 NULL。*/

NULL 值处理

//三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

//！！！在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

//1. 检查是否为 NULL：
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

//2. 使用 COALESCE 函数处理 NULL：
//COALESCE 函数可以用于替换为 NULL 的值，它接受多个参数，返回参数列表中的第一个非 NULL 值：
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;
//以上 SQL 语句中，如果 stock_quantity 列为 NULL，则 COALESCE 将返回 0。

//3. 使用 IFNULL 函数处理 NULL：
//IFNULL 函数是 COALESCE 的 MySQL 特定版本，它接受两个参数，如果第一个参数为 NULL，则返回第二个参数。
SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

//4. NULL 排序：
//在使用 ORDER BY 子句进行排序时，NULL 值默认会被放在排序的最后。如果希望将 NULL 值放在最前面，可以使用 ORDER BY column_name ASC NULLS FIRST，反之使用 ORDER BY column_name DESC NULLS LAST。
SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

//5. 使用 <=> 操作符进行 NULL 比较：
//<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符，对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。
SELECT * FROM employees WHERE commission <=> NULL;

//6. 注意聚合函数对 NULL 的处理：
//在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

//这样即使 salary 为 NULL，聚合函数也会将其视为 0。

正则表达式

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';
/*参数说明：
column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
table_name 是你要从中查询数据的表的名称。
column_name 是你要进行正则表达式匹配的列的名称。
'pattern' 是一个正则表达式模式。*/

//查找 name 字段中以 'st' 为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

事务

/*
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
*/
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert、update、delete 语句

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT 来实现
   BEGIN 或 START TRANSACTION：开用于开始一个事务。
   ROLLBACK 事务回滚，取消之前的更改。
   COMMIT：事务确认，提交事务，使更改永久生效。
2、直接用 SET 来改变 MySQL 的自动提交模式:
   SET AUTOCOMMIT=0 禁止自动提交
   SET AUTOCOMMIT=1 开启自动提交
   
//BEGIN 或 START TRANSACTION -- 用于开始一个事务：
BEGIN; -- 或者使用 START TRANSACTION;
//COMMIT -- 用于提交事务，将所有的修改永久保存到数据库：
COMMIT;
//ROLLBACK -- 用于回滚事务，撤销自上次提交以来所做的所有更改：
ROLLBACK;
//SAVEPOINT -- 用于在事务中设置保存点，以便稍后能够回滚到该点：、
SAVEPOINT savepoint_name;
//ROLLBACK TO SAVEPOINT -- 用于回滚到之前设置的保存点：
ROLLBACK TO SAVEPOINT savepoint_name;

//实例：
-- 开始事务
START TRANSACTION;

-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 判断是否要提交还是回滚
IF (条件) THEN
    COMMIT; -- 提交事务
ELSE
    ROLLBACK; -- 回滚事务
END IF;

ALTER 命令

/*
当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。
MySQL 的 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);

7. 添加 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);

8. 修改表名
ALTER TABLE old_table_name
RENAME TO new_table_name;
//以下 SQL 语句将表名由 employees 修改为 staff：
ALTER TABLE employees
RENAME TO staff;

/*注意：
但在使用 ALTER 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。
在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作。*/

索引