MySql基础语法速通_数据库速通-优快云博客

本文链接：https://blog.youkuaiyun.com/bobplease/article/details/140034355

创建表：

CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE mytable (
    id INT PRIMARY KEY AUTO_INCREMENT,//创建id并设为主键，自动递增
    name VARCHAR(100) NOT NULL,//创建name列并设置非空，类型为可变长字符
    age INT NOT NULL
);

ps:主键（Primary Key）是数据库表中的一种约束，用于唯一标识表中的每一行数据

MySql里的数据类型：

数值类型

整数类型（Integer Types）
- TINYINT: 存储非常小的整数，范围为 -128 到 127（有符号）或 0 到 255（无符号）。常用于存储非常小的数值，例如状态标志。
- SMALLINT: 存储小整数，范围为 -32,768 到 32,767（有符号）或 0 到 65,535（无符号）。
- MEDIUMINT: 存储中等大小的整数，范围为 -8,388,608 到 8,388,607（有符号）或 0 到 16,777,215（无符号）。
- INT (INTEGER): 存储标准整数，范围为 -2,147,483,648 到 2,147,483,647（有符号）或 0 到 4,294,967,295（无符号）。常用于存储大多数整数值，如用户ID。
- BIGINT: 存储大整数，范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号）或 0 到 18,446,744,073,709,551,615（无符号）。用于存储非常大的数值，例如全局唯一标识符（GUID）。
浮点数类型（Floating-Point Types）
- FLOAT: 存储单精度浮点数。常用于存储小数位数不多的数值。
- DOUBLE: 存储双精度浮点数。常用于存储高精度的数值。
- DECIMAL: 存储定点数。用于存储精确的小数，如货币金额。

字符串类型

CHAR: 定长字符串，最多可存储 255 个字符。适用于存储长度固定的数据，如性别（M/F）。
VARCHAR: 变长字符串，最多可存储 65,535 个字符。常用于存储长度可变的字符串，如名称、地址。
TEXT: 存储长文本，最多可存储 65,535 个字符。用于存储大段文本，如文章内容。
- TINYTEXT: 最多可存储 255 个字符。
- MEDIUMTEXT: 最多可存储 16,777,215 个字符。
- LONGTEXT: 最多可存储 4,294,967,295 个字符。

二进制类型

BINARY: 定长二进制字符串，最多可存储 255 字节。用于存储固定长度的二进制数据。
VARBINARY: 变长二进制字符串，最多可存储 65,535 字节。用于存储可变长度的二进制数据。
BLOB: 存储二进制大对象。用于存储大型二进制数据，如图像、音频。
- TINYBLOB: 最多可存储 255 字节。
- MEDIUMBLOB: 最多可存储 16,777,215 字节。
- LONGBLOB: 最多可存储 4,294,967,295 字节。

日期和时间类型

DATE: 存储日期，格式为 YYYY-MM-DD。用于存储日期，如出生日期。
TIME: 存储时间，格式为 HH:MM:SS。用于存储时间，如活动开始时间。
DATETIME: 存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。用于存储特定时刻，如订单创建时间。
TIMESTAMP: 存储时间戳，自动更新为当前时间。常用于记录行的创建或修改时间。
YEAR: 存储年份，格式为 YYYY。用于存储年份，如毕业年份。

枚举和集合类型

ENUM: 枚举类型，定义一组预定义的字符串值，字段的值必须是这些值中的一个。用于存储有限的选项，如性别（'M', 'F'）。
SET: 集合类型，定义一组预定义的字符串值，字段的值可以是这些值中的一个或多个。用于存储多个选项的组合，如兴趣爱好。

JSON 类型

JSON: 存储 JSON 格式的数据。用于存储结构化数据，适用于需要灵活数据结构的场景。

ps:不用全部记下来，用到了再查

查询：

简单查询：

SELECT * FROM employees;//全表查询
SELECT first_name, last_name FROM employees;//查询特定列
SELECT * FROM employees WHERE hire_date = '2022-01-01';//使用where查询特定条件下的行
SELECT * FROM employees WHERE hire_date = '2022-01-01' OR hire_date = '2022-01-02';//查询满足任一条件的行，如果是and就代表查询两个条件都满足的行
SELECT * FROM employees WHERE first_name LIKE 'J%';//查询J字开头的字符串所在行
SELECT * FROM employees ORDER BY last_name DESC;//以降序输出查询结果，默认是升序
SELECT * FROM employees WHERE hire_date between '2022-01-01' and '2022-01-04'//查询在2022年1月1日到2022年1月4日就职的员工
SELECT * FROM employees WHERE hire_date is not null//查询就职时间非空的职员
SELECT * FROM employees LIMIT 5;//限制每次返回的行不超过5行
SELECT * FROM employees LIMIT 5 OFFSET 20;//限制每次返回的行不超过5行,从第21条记录开始获取数据

多表查询：

//内连接

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;//以员工表和部门表的部门id为值取两表的交集

//外连接

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
//以员工表为基准，从员工表中出发找到部门表中对应部门，这是左连接，右连接与之相反不过很少用到

//跨数据库连接

SELECT a.*, b.*
FROM db1.table1 a
JOIN db2.table2 b ON a.id = b.id;
//连接数据库db1和db2里的a和b表

//复合连接(根据多个字段进行连接)
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id

//隐式连接（也就是不显式开启join,on而是根据where条件建立一个交集）
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

//Using语句(指定连接字段)
SELECT 
  o.order_id,
  c.first_name,
  sh.name AS shipper
FROM orders o
JOIN customers c USING(customer_id)
LEFT JOIN shippers sh USING(shipper_id)
//自然连接，自动匹配连接字段
SELECT 
  o.order_id,
  c.first_name
FROM orders o NATURAL JOIN customers c

//交叉连接，第一个表的每一行对应第二个表的每一行，比如第一个表里只有一个颜色red，第二个表对应三个大小，那么两表连接后的结果就是red对应三个尺寸
SELECT 
  sh.name AS shipper, 
  p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name

联合查询(联合了多段查询结果)

(SELECT first_name, last_name FROM Employees)
UNION
(SELECT first_name, last_name FROM Contractors);SQL

增删改

//插入
INSERT INTO Orders (order_id, customer_id, order_date, total_amount)
VALUES (1001, 2001, '2023-08-15', 150.50);
//插入多行
INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    (value1, value2, value3, ...);
//删除
DELETE FROM Orders
WHERE order_id = 1001;
//更新
UPDATE Orders
SET total_amount = 175.00
WHERE order_id = 1001;

汇总分组数据

//汇总invoices表中invoice_total值最大最小


SELECT 
  MAX(invoice_total) AS highest,
  MIN(invoice_total) AS lowest,
  AVG(invoice_total) AS average,
  SUM(invoice_total * 1.1) AS total,
  COUNT(DISTINCT client_id) AS total_records
FROM invoices

//按照客户id进行分组，并汇总该客户总的消费量
SELECT 
  client_id, 
  SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id

//分组后过滤，这里使用having子句来进行分组后过滤
SELECT column1, aggregate_function(column2), ...
FROM table_name
GROUP BY column1
HAVING condition;

子查询

-- 1. 嵌套在 SELECT 子句中的子查询
SELECT (SELECT COUNT(*) FROM Orders WHERE product_id = p.product_id) AS order_count,
       p.product_name
FROM Products p
WHERE (SELECT COUNT(*) FROM Orders WHERE product_id = p.product_id) > 10;

-- 2. 嵌套在 FROM 子句中的子查询
SELECT c.customer_name, o.order_id
FROM (SELECT * FROM Customers WHERE country = 'USA') c
JOIN Orders o ON c.customer_id = o.customer_id;

-- 3. 嵌套在 WHERE 子句中的子查询
SELECT customer_id, order_date
FROM Orders
WHERE customer_id IN (SELECT customer_id FROM Customers WHERE country = 'USA');

-- 4. 子查询与比较运算符结合
SELECT customer_id, order_date
FROM Orders
WHERE total_amount > (SELECT AVG(total_amount) FROM Orders);

-- 5. 子查询与 ANY/SOME 运算符结合
SELECT customer_id, order_date
FROM Orders
WHERE total_amount > ANY (SELECT MAX(total_amount) FROM Orders GROUP BY customer_id);

-- 6. 子查询与 ALL 运算符结合
SELECT customer_id, order_date
FROM Orders
WHERE total_amount < ALL (SELECT MIN(total_amount) FROM Orders GROUP BY customer_id);

-- 7. 子查询与 EXISTS 运算符结合
SELECT customer_id
FROM Customers
WHERE EXISTS (SELECT 1 FROM Orders WHERE Customers.customer_id = Orders.customer_id);

-- 8. 子查询与 NOT EXISTS 运算符结合
SELECT customer_id
FROM Customers
WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Customers.customer_id = Orders.customer_id);

函数

字符串函数

UPPER(column_name): 将字符串转换为大写。
LOWER(column_name): 将字符串转换为小写。
CONCAT(column_name1, column_name2): 连接两个或多个字符串。
TRIM(LEADING|TRAILING|BOTH 'char' FROM column_name): 删除字符串开头、结尾或两边的指定字符。
SUBSTRING(column_name, start, length): 返回字符串的一部分。
REPLACE(column_name, search_string, replace_with): 替换字符串中的某个部分。
LENGTH(column_name): 返回字符串的长度。
LEFT(column_name, num_chars): 返回字符串左边的指定数量的字符。
RIGHT(column_name, num_chars): 返回字符串右边的指定数量的字符。
LOCATE(substring, column_name): 返回子字符串的位置。
INSTR(column_name, substring): 同 LOCATE，返回子字符串的位置。

数学函数

ABS(column_name): 返回绝对值。
CEIL(column_name): 向上取整。
FLOOR(column_name): 向下取整。
ROUND(column_name[, decimal_places]): 四舍五入。
TRUNCATE(column_name, decimal_places): 截断小数位。
MOD(column_name1, column_name2): 求模（余数）。
POWER(column_name1, column_name2): 幂运算。
SQRT(column_name): 开平方根。
RAND(): 生成随机数。
PI(): 返回圆周率 π。
LOG(column_name): 自然对数。
LOG10(column_name): 以10为底的对数。

日期时间函数

CURDATE(): 获取当前日期。
CURTIME(): 获取当前时间。
NOW(): 获取当前日期和时间。
DATE_ADD(date, INTERVAL expr unit): 添加时间间隔。
DATE_SUB(date, INTERVAL expr unit): 减去时间间隔。
YEAR(date): 提取年份。
MONTH(date): 提取月份。
DAY(date): 提取天数。
HOUR(time): 提取小时。
MINUTE(time): 提取分钟。
SECOND(time): 提取秒数。
EXTRACT(unit FROM date_time): 提取日期时间的部分。
TIMESTAMPDIFF(unit, date1, date2): 计算两个日期时间之间的差值。

聚合函数

COUNT(column_name): 计数。
SUM(column_name): 求和。
AVG(column_name): 求平均值。
MAX(column_name): 最大值。
MIN(column_name): 最小值。

事务

事务概念：

事务就是一序列数据库操作集合，这些操作要么全部成功，要么全部失败，而且具有ACID的特性，即：

1.原子性 (Atomicity)

事务中的所有操作都必须成功完成，否则整个事务都将被回滚，撤销该事务的所有更改。这意味着如果事务中的任何操作失败，那么之前的操作也不会被提交到数据库。

2. 一致性 (Consistency)

事务执行的结果必须使数据库从一个一致性状态转变到另一个一致性状态。这意味着事务完成后，数据库的状态必须符合所有的预定义规则和约束。(一致性状态是指该状态的数据是正确，有效，且符合预期的)

3. 隔离性 (Isolation)

当多个事务并发执行时，每个事务都应该与其他事务隔离，好像它们是单独执行的一样。MySQL 支持不同级别的事务隔离，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

4. 持久性 (Durability)

一旦事务被提交，它对数据库所做的更改就是永久的，并且不会因为系统故障而丢失。

事务隔离级别

MySQL中提供了四种事务隔离级别：

1. 读未提交 (Read Uncommitted)

描述: 在这种隔离级别下，事务可以读取到其他事务还未提交的数据。
特点: 可能会出现脏读（Dirty Reads），即读取到未提交的数据。
优点: 性能最高，因为几乎没有锁竞争。
缺点: 数据一致性最差。

2. 读已提交 (Read Committed)

描述: 在这种隔离级别下，事务只能读取到其他事务已经提交的数据。
特点: 不会出现脏读，但可能出现不可重复读（Non-Repeatable Reads）和幻读（Phantom Reads）。
优点: 数据一致性比读未提交要好。
缺点: 仍然存在一些数据不一致的情况。

3. 可重复读 (Repeatable Read)

描述: 在这种隔离级别下，事务在整个过程中可以多次读取同一行数据并得到相同的结果，即使其他事务在此期间对该行进行了修改或删除。
特点: 通过使用行级锁来避免不可重复读和幻读。
优点: 数据一致性较好。
缺点: 可能出现更多的锁竞争，影响并发性能。
默认设置: MySQL 默认的隔离级别就是可重复读。

4. 串行化 (Serializable)

描述: 在这种隔离级别下，所有事务都被串行化执行，即一个接一个地执行，从而避免了任何并发问题。
特点: 数据一致性最好。
优点: 完全避免了并发问题。
缺点: 性能最低，因为所有事务都必须等待其他事务完成才能开始执行。

主键与外键

主键 (Primary Key)

主键是一种特殊的唯一标识符，用于唯一标识表中的每一行记录。每个表最多只能有一个主键，并且主键的值不能为 NULL。

主键的特点

唯一性：主键的值必须是唯一的。
非空性：主键的值不能为 NULL。
唯一索引：主键自动创建了一个唯一索引，这有助于提高查询性能。
不可更改性：一旦定义为主键，其值就不能被更改（除非重新定义主键）。

创建主键

在 MySQL 中，可以通过以下方式创建主键：

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (customer_id)
);

--在现有表中添加

ALTER TABLE Customers ADD PRIMARY KEY (customer_id);

外键 (Foreign Key)

外键是一种引用其他表中主键的约束，用于建立表之间的关系。外键确保了数据的一致性和参照完整性。

外键的特点

参照完整性：外键确保了外键字段的值必须存在于被引用表的主键字段中，或者为 NULL。
限制删除和更新：外键约束可以防止删除或更新被引用表中的行，如果这样做会导致违反参照完整性。

创建外键

CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    product_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);

外键约束选项

当定义外键时，可以指定以下约束选项：

ON DELETE CASCADE：当被引用表中的行被删除时，自动删除引用表中的相关行。
ON DELETE SET NULL：当被引用表中的行被删除时，将引用表中的外键字段设为 NULL。
ON UPDATE CASCADE：当被引用表中的主键值被更新时，自动更新引用表中的外键字段。
ON UPDATE SET NULL：当被引用表中的主键值被更新时，将引用表中的外键字段设为 NULL。s

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

三大范式

三大范式是数据库设计中用来减少数据冗余和提高数据完整性的指导原则。遵循这些范式可以帮助设计更加规范化和高效的数据库结构。

第一范式（1NF）：确保每列不可分

第一范式就像是要求我们在填写表格时，每一格只能填一个东西，不能填多个。这意味着表中的每一列都应该只包含单一的值，不能包含列表或多个值。

第二范式（2NF）：消除部分依赖

第二范式是在第一范式的基础上，要求我们的表格中的每一列都直接依赖于整个主键，而不是主键的一部分。简单来说，就是确保表中的每一列都与主键紧密相关，而不是与主键的某一部分相关。

第三范式（3NF）：消除传递依赖

第三范式是在第二范式的基础上，要求表中的每一列都不应该依赖于其他非主键列。换句话说，每一列都应该直接依赖于主键，而不是间接依赖于其他非主键列。