MySQL安装+MYSQL基础到深入

 坚持是成功的不二法门！！！

MySQL简介

MySQL 是最流行的关系型数据库管理系统(RDBMS)，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

数据库简介

简而言之，就是存储数据(以表格的形式)的仓库.

MySQL前备知识，RDBMS术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL安装环境

1.下载MySQL，MySQL官网（MySQL :: MySQL Downloads）

2.安装MySQL

默认用户名为root，牢记输入的密码，下面会用到.

3.安装成功，开始使用

进入MySQL中的bin目录下输出cmd，或者使用“cd”命令或者phpstudy搭建使用

 mysql -h localhost -u root -p 登录，密码就是之前设置的密码.

即可使用

MySQL语法

MySQL语法特性：逗号连接语句，分号结束语句

MySQL注释

单行注释：

    使用双破折号 -- 后跟空格和注释内容：
-- 这是一个单行注释

--使用井号 # 后跟注释内容：
    # 这也是一个单行注释

多行注释：
    使用 /* 开始，以 */ 结束：
/* 这是一个

       多行注释示例 */

特殊用途的内联注释（也称为条件注释）：
   -- 用于兼容不同MySQL版本时指定不同的执行逻辑：
        /*!40101 SET character_set_client = utf8mb4 */;
      -- 这种类型的注释对于特定MySQL版本具有特殊意义，服务器会根据版本解析并可能执行其中的内容。
-- 另外，在创建或修改表结构时为字段添加注释的语法如下：
ALTER TABLE table_name MODIFY column_name column_type COMMENT '字段注释内容';

--或者在创建表的时候直接定义字段注释：

CREATE TABLE my_table (
    id INT AUTO_INCREMENT,
    name VARCHAR(50) COMMENT '用户姓名',
    -- 其他字段...
    PRIMARY KEY (id)
);
--同样，也可以为整个表添加注释：

ALTER TABLE table_name COMMENT '表注释内容';

MySQL数据库管理基本语句

SHOW DATABASE;         //显示所有数据库

USE 数据库名；  //选择数据库并使用，"use MySQL;"

SHOW TABLIES；   //显示当前数据库的所有表

SHOW COLUMNS FROM 数据表;        //显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

SHOW INDEX FROM 数据库;         //显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

SHOW TABLES STATUS [FROM db_name][LIKE 'pattern']\G;        //该命令将输出Mysql数据库管理系统的性能及统计信息。

MySQL创建数据库

基本语法

CREATE DATABASE [IF NOT EXISTS] database_name  
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

实例:

CREATE DATABASE mydatabase   //数据库名mydatabase
  CHARACTER SET utf8mb4       //设置数据库的默认字符集为 utf8mb4，这允许存储Unicode字符，包括表情符号和大多数国际语言字符。
  COLLATE utf8mb4_general_ci;    //设置排序规则为 utf8mb4_general_ci，这是一个大小写不敏感的排序规则，适合那些对大小写不敏感的搜索场景。它会以一种对大小写较为宽松的方式比较字符串，这样比如 'Apple' 和 'apple' 在排序时会被视为相同的值。

扩展：使用mysqladmin创建数据库

简介：mysqladmin 是 MySQL 提供的一个用于执行管理任务的命令行工具。

创建数据库语法   mysqladmin -u your_username -p create your_database

• -u 参数用于指定 MySQL 用户名。
• -p 参数表示需要输入密码。
• create 是执行的操作，表示创建数据库。
• your_database 是要创建的数据库的名称。

mysqladmin -u your_username -p create your_database \ 与上述实例效果一样，多了个default参数
  --default-character-set=utf8mb4 \
  --default-collation=utf8mb4_general_ci

MySQL删除数据库

慎用，不法恢复被删除的数据库

基础语法

DROP DATABASE <database_name>;   

//直接删除数据库，不检查是否存在，如：DROP DATABASE mydatabase；
或
DROP DATABASE IF EXISTS <database_name>;  

//IF EXISTS 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误，实例：DROP DATABASE IF EXISTS mydatabase;

MySQL选择数据库

上述管理数据库中的use方法。

MySQL数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期、时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

注：char(30)，意为可以存储30个字符

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

枚举与集合类型（Enumeration and Set Types）

• ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
• SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

空间数据类型（Space Data Types）

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

MySQL创建数据表

数据包包含：表名，表字符段，定义每个表字段的数据类型

基本语法

CREATE TABLE table_name (    //表名
    column1 datatype,      //第一列列及其数据类型
    column2 datatype,
    ...
);

实例一

CREATE TABLE users (         //表名user
    id INT AUTO_INCREMENT PRIMARY KEY,  //用户id int类型 自增长 作为主键
    username VARCHAR(50) NOT NULL,    //用户名username 变长字符串 不允许为空
    email VARCHAR(100) NOT NULL,    //用户邮箱email 变长字符串 不允许为空
    birthdate DATE,    //用户生日，日期类型
    is_active BOOLEAN DEFAULT TRUE  //用户是否被激活 布尔类型 默认值(default)为true
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;   //使用字符集utf8md4，utf8mb4_general_ci 排序

实例二：

CREATE TABLE Orders (

    id INT AUTO_INCREMENT,

    customer_id INT NOT NULL,

    order_date DATE NOT NULL,

    total_amount DECIMAL(10, 2) NOT NULL,

    status ENUM('pending', 'processed', 'shipped', 'delivered') DEFAULT 'pending',  
     //定义一个status的字段，数据类型为枚举，意为status只能为这四个值之一，若未注明，则默认为pending
    PRIMARY KEY (id)   //主键为id，由上述得知，INT AUTO_INCREMENT属性一般都附给主键

) ENGINE=InnoDB DEFAULT CHARSET=utf8; //ENGINE为存储引擎
//ENGINE=InnoDB：这是指定表的存储引擎为InnoDB。InnoDB是一个事务安全的存储引擎，支持行级锁定和外键约束，非常适合处理大量并发写入操作，并且能够确保数据的一致性和完整性。

MySQL删除数据表

慎用，无法恢复

基础语法

DROP TABLE table_name ;    //直接删除表，不检查是否存在
或
DROP TABLE IF EXISTS table_name;  //会检查是否存在

MySQL插入数据

基础语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

实例一：

INSERT INTO users (username, email, birthdate, is_active)   //表名(插入的列名)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);   //插入列名的值，与上述插入列名一一对应

实例二：插入所有列，可忽略列名

INSERT INTO users
VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);
//这里，NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。

实例三：多行插入

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);

MySQL查询数据

基础语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[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;

实例二：select语句进阶

-- 使用 AND 运算符和通配符，通配符(%)匹配0个及以上的字符，'j%'意为以j开头的，LIKE相当于'='
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 子句，IN可以理解为'='
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

MySQL where子句

where都是配合select查询语句使用的

实例：SELECT * FROM users WHERE username = 'test';

实例中的'='可以替换为如下运算符

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

where扩展及其实例：

组合条件（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';

ETWEEN 条件:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

IS NULL 条件：

SELECT * FROM employees WHERE department IS NULL;

MySQL UPDATE更新

基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

实例一：更新单列

UPDATE employees   -- 更新单列
SET salary = 60000    -- 更新薪水的数据
WHERE employee_id = 101;  -- where指定工人id

实例二：更新多列

UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'  -- 添加更新列名及数据即可
WHERE order_id = 1001;

实例三：表达式更新

UPDATE products
SET price = price * 1.1    -- 产品价格提高10%
WHERE category = 'Electronics';

实例四：更新符合条件的所有行

UPDATE students
SET status = 'Graduated';  -- 所有学生毕业

实例五：更新使用子查询的值

UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)      -- 更新内容由子查询语句返回
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

UPDTAE更新语句要仔细确定更新的位置及其条件！！！

MySQL DELETE语句

上实例即可

-- 删除符合条件的行
DELETE FROM students
WHERE graduation_year = 2021;

-- 删除所有行
DELETE FROM orders;

-- 使用子查询删除符合条件的行：
DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

MySQL LIKE语句

LIKE语句特性除了多一个通配符(%)的使用，与'='没有什么区别

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

实例：

-- % 通配符表示零个或多个字符。例如，'a%' 匹配以字母 'a' 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';

-- '_'通配符表示一个字符。例如，'_r%' 匹配第二个字母为 'r' 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';

-- 组合使用 % 和 _：
SELECT * FROM users WHERE username LIKE 'a%o_';

-- 不区分大小写的匹配：
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;

MySQL UNION操作符

基本描述

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。

UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

基本语法

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];

实例：

1.-- 基本的 UNION 操作：
SELECT city FROM customers    
UNION
SELECT city FROM suppliers
ORDER BY city;
-- 先union合并查询结果再order by 对结果排序

2.-- 使用过滤条件的 UNION：     -- union合并带条件查询，在order by
SELECT product_name FROM products WHERE category = 'Electronics'
UNION       
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name;
-- union合并带条件查询，在order by

3.-- UNION 操作中的列数和数据类型必须相同：
SELECT first_name, last_name FROM employees
UNION
SELECT department_name, NULL FROM departments
ORDER BY first_name;
-- union合并后first_name和department_name在同一列，用于null填充避免列数不同而报错

4.-- 使用 UNION ALL 不去除重复行：
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;
-- 使用 UNION ALL，将不去除重复行，也就是真正本质上完整的列

MySQL ORDER BY 排序语句

既然排序就分为升序和降序

基本语法

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

实例：

1.-- 单列排序：

SELECT * FROM products
ORDER BY product_name ASC;  
-- 以产品名升序呈现
----------------------------------------------------------------
2.-- 多列排序：

SELECT * FROM employees      
ORDER BY department_id ASC, hire_date DESC;
-- 先以部门升序排序，同一部门的以雇佣时间降序排序
-----------------------------------------------------------------
3.-- 使用数字表示列的位置：

SELECT first_name, last_name, salary  
FROM employees     
ORDER BY 3 DESC, 1 ASC;   
-- 先以3(salary)降排序，再以1(first_name)升排序，数字与上述select的内容一一对应
----------------------------------------------------------------
4.-- 使用表达式排序：

SELECT product_name, price * discount_rate AS discounted_price  
FROM products
ORDER BY discounted_price DESC;
-- 现场定义字段discounted_price，并以此排序，AS 作为
-------------------------------------------------------------------
5.-- 使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值：

SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;  
-- 以price降序排序，price为NULL的放置到最后

MySQL GROUP BY语句

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

基本语法

SELECT column1, aggregate_function(column2)   //此函数是对分组后的每个组执行的聚合函数。
FROM table_name
WHERE condition
GROUP BY column1;    //本质上是标志要使用聚合函数的列

常见aggregate(聚合)函数及其在此的特殊解释如下：

COUNT()      -- 返回group by 后面字段（上文的column1）出现的次数

SUM()       -- 返回group by 后面字段（上文的column1）的值的总和

AVG()         --返回group by 后面字段（上文的column1）的值的平均值

以此表(employee_tbl)为例   signin在那天的某时刻后登录的次数

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name; 

//查询name列中name具体值出现的次数，" * "号匹配的是所有行

此语句查询结果如下

GROUP BY 使用WITH ROLLUP

以上述表为例就是多了一个统计的过程

SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

结果如下

NULL为统计总登记数

你可以用coalesce()0对NULL进行命名

用法：coalesce(a,b,c);

//参数说明：如果 a==null，则选择 b；如果 b==null,则选择 c；如果 a!=null,则选择 a；如果 a b c 都为 null ，则返回为 null（没意义）。

SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

//如果name列中数据包被列举完了，就用"总数"来替代，查询结果如下

MySQL连接的使用

关键词JOIN

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

简而言之，join的功能就是联合两个表，方便一起查询或者操作

基本语法

INNER JOIN

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
 

INNER JOIN实例走起

1.-- 简单的 INNER JOIN
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_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;

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;

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';

LEFT JOIN

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN实例

 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;

 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;

 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;

 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;

RIGHT JOIN

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT的用法与LEFT大同小异，参照LEFT学习就行了

MySQL NULL值的处理

在 MySQL 中，NULL 用于表示缺失的或未知的数据，处理 NULL 值需要特别小心，因为在数据库中它可能会导致不同于预期的结果。

处理NULL主要又三大运算符

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

注：因为NULL值的特殊性，你不能使用= NULL 或 != NULL在列中查找值

处理NULL值的实例

处理NULL值的常见事项和技巧

1. 检查是否为 NULL：

要检查某列是否为 NULL，可以使用 IS NULL 或 IS NOT 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;

在此如果stock_quantity列中有NULL值，NULL就会被替换为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。（上文order by语句处提到过）

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。

MySQL正则表达式

我们已经了解到 MySQL 可以通过 LIKE ...% 来进行模糊匹配。

MySQL中使用REGEXP和RLIKE进行正则表达式匹配，且两者没有任何区别，只讲REGEXP.

在REGEXP中的操作符如下

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

正则表达式匹配的字符类

• .：匹配任意单个字符。
• ^：匹配字符串的开始。
• $：匹配字符串的结束。
• *：匹配零个或多个前面的元素。
• +：匹配一个或多个前面的元素。
• ?：匹配零个或一个前面的元素。
• [abc]：匹配字符集中的任意一个字符。
• [^abc]：匹配除了字符集中的任意一个字符以外的字符。
• [a-z]：匹配范围内的任意一个小写字母。
• \d：匹配一个数字字符。
• \w：匹配一个字母数字字符（包括下划线）。
• \s：匹配一个空白字符。

基本句法

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';

REGEXP匹配实例

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

2.-- 查找 name 字段中以 'ok' 为结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

3.-- 查找 name 字段中包含 'mar' 字符串的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';

4.-- 查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

5.-- 选择订单表中描述中包含 "item" 后跟一个或多个数字的记录。
SELECT * FROM orders WHERE order_description REGEXP 'item[0-9]+';

6.-- 使用 BINARY 关键字，使得匹配区分大小写：
SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';

7.-- 使用 OR 进行多个匹配条件，以下将选择姓氏为 "Smith" 或 "Johnson" 的员工记录：
SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';

MySQL ALTER命令

ALTER命令作用：修改数据库、表和索引等对象的结构。

ALTER 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。

1.添加列基础语法及实例

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;


ALTER TABLE employees
ADD COLUMN birth_date DATE;
-- 在employees表中添加birth_day列，其类型为日期类型

2.修改列的数据类型

ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;


ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10,2);
-- 将 employees 表中的 salary 列的数据类型修改为 DECIMAL(10,2)：

3.修改列表

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;


ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
-- 将 employees 表中的某个列的名字由 old_column_name 修改为 new_column_name，并且可以同时修改数据类型

4.删除列

ALTER TABLE table_name
DROP COLUMN column_name;     //如果数据表中只剩余一个字段则无法使用DROP来删除字段。


ALTER TABLE employees
DROP COLUMN birth_date;
-- 将 employees 表中的 birth_date 列删除

5.添加PREMARY KEY

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);


ALTER TABLE employees
ADD PRIMARY KEY (employee_id);
-- 在 employees 表中添加了一个主键

6.添加FOREIGN KEY

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);


ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
-- 在 orders 表中添加了一个外键，关联到 customers 表的 customer_id 列

7.修改表名

ALTER TABLE old_table_name
RENAME TO new_table_name;


ALTER TABLE employees
RENAME TO staff;
-- 将表名由 employees 修改为 staff

ALTER 扩展

1.删除、添加、修改表字段

ALTER TABLE testalter_tbl  DROP i;
-- 删除i字段

ALTER TABLE testalter_tbl ADD i INT;
-- 添加i字段并声明数据类型


FIRST  AFTER  关键字   --用于调整添加字段的位置
---------------------------------------------------
ALTER TABLE testalter_tbl DROP i;
-- 先删除字段i
ALTER TABLE testalter_tbl ADD i INT FIRST;
-- 把字段i添加到表的第一位
ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT AFTER c;
-- 把字段i添加到表中c字段的后一位

2.修改字段类型及名称

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
-- 把字段c从CHAR(1)改为CHAR(10)


CHANGE关键字

ALTER TABLE testalter_tbl CHANGE i j BIGINT;
-- 把i字段修改为j字段，且j字段的数据类型为BIGINT
ALTER TABLE testalter_tbl CHANGE j j INT;
-- 把j字段的BIGINT数据类型改为INT

3.ALTER TABLE 对 Null 值和默认值的影响

当你修改字段的时候，你可以指定是否包含或者是否设置默认值.

ALTER TABLE testalter_tbl 
MODIFY j BIGINT NOT NULL DEFAULT 100;
-- 指定j字段为NOT NULL且默认值为100.

4.修改字段默认值

实例：

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
-- 设定i字段默认值为1000，然后查看

SHOW COLUMNS FROM testalter_tbl;
-- 查看表的列
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
-- 删除字段默认值

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

5.修改表类型

ALTER TABLE testalter_tbl ENGINE = MYISAM;
-- 修改表的类型通常是指更改表的存储引擎（Engine）

SHOW TABLE STATUS LIKE 'testalter_tbl'\G
-- 查看表的类型，\G 参数是格式化输出的标识符，它会让结果以垂直方式展示（每行一个字段及其值）

6.修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;
-- 将数据表 testalter_tbl 重命名为 alter_tbl

 锲而不舍，金石可镂！！！