MySQL自学(基础语法)

@[TOC]MySQL学习记录

基础语法

创建数据库

CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;//如果不存在则创建
CREATE DATABASE IF NOT EXISTS 数据库名 [CHARACTER SET charset_name] [COLLATE collation_name];//指定字符集和排序规则

例：

CREATE DATABASE IF NOT EXISTS mydatabase 
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF NOT EXISTS 数据库名;

选择数据库

USE 数据库名;

创建表

CREATE TABLE table_name (
	列名 datatype,
	列名 datatype,
	...
	列名 datatype
);

删除表

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;//如果存在则删除
TRUNCATE TABLE table_name;//只清空表中数据，但保留表的结构

插入数据

INSERT INTO table_name(column1,column2,...)
VALUES (value1,value2,...);//column1...列名

查询数据

SELECT column1,column2,...
FROM table_name
[WHERE condition]
[ORDER BY column_name(ASC,DESC)]
[LIMIT number];
// condition 筛选条件
// ASC升序，DESC降序，默认升序排列
// LIMIT 限制返回的行数，最多返回number行

WHERE子句的条件

SELECT * FROM table_name WHERE column1 = 'target';
SELECT * FROM table_name WHERE column1 != 'target';
SELECT * FROM table_name WHERE column2 > 50;
SELECT * FROM table_name WHERE column2 <= 100;
SELECT * FROM table_name WHERE column2 > 50 AND column2 <= 100;//AND
SELECT * FROM table_name WHERE column2 > 50 OR column2 < 100;//OR
SELECT * FROM table_name WHERE column3 in ('US','CA','MX');//IN
SELECT * FROM table_name WHERE column4 BETWEEN '2024-09-01' AND '2024-10-1';//BETWEEN
SELECT * FROM table_name WHERE NOT column2 = 100;//NOT
SELECT * FROM table_name WHERE column2 IS NULL;//IS NULL
SELECT * FROM table_name WHERE column2 IS NOT NULL;//IS NOT NULL
SELECT * FROM table_name WHERE column1 LIKE 'S%';//模糊匹配条件

更新数据

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

UPDATE employees
SET salary = 6000;
WHERE employee_id = 101;

UPDATE products SET price = price * 1.1
WHERE id = 11;

UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

删除数据

DELETE FROM table_name
[WHERE condition];

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

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, ...];

排序

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

例：

SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
//选择员工表 employees 中的所有员工，并先按部门 ID 升序 ASC 排序，然后在相同部门中按雇佣日期降序 DESC 排序。

SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
//选择员工表 employees 中的名字和工资列，并按第三列（salary）降序 DESC 排序，然后按第一列（first_name）升序 ASC 排序。