一、创建数据库
CREATE DATABASE 数据库名称;
如果数据库存在，则会提示报错。
二、选择数据库
USE 数据库名称;
三、创建数据表
CREATE TABLE 数据表名称;
四、MySQL数据类型
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型
4.1 数值类型
数值类型
类型 大小 用途
INT 4Bytes 整数值
FLOAT 4Bytes 单精度
DOUBLE 8Bytes
双精度
4.2 字符串类型
字符串类型
类型 用途
CHAR 定长字符串
VARCHAR 变长字符串
4.3 日期和时间类型
五、MySQL插入数据
INSERT INTO table_name (column1,column2,column3)
VALUES (value1,value2,value3)
六、查询数据库
SHOW DATABASES;
七、查询数据表
SELECT * FROM users;
八、MySQL WHERE子句
语法：
SELECT column1,column2
FROM table_name
WHERE condition;
参数说明：
column1,column2是显示列的名称
table_name是你选择查询数据的表的名称
WHERE condition是用于指定过滤条件的子句
WHERE简单实例：
1、等于条件
SELECT * FROM users WHERE username = 'test';
2、不等于条件
SELECT * FROM users WHERE username != 'test';
3、大于条件
SELECT * FROM products WHERE price > 50.00;
4、小于条件
SELECT * FROM orders WHERE order_date < '2023-01-01';
5、大于等于条件
SELECT * FROM employees WHERE salary >= 50000;
6、小于等于条件
SELECT * FROM students WHERE age <= 21;
7、组合条件
SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;
SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;
8、模糊匹配条件
SELECT * FROM customers WHERE first_name LIKE 'J%';
9、IN条件
SELECT * FROM countries WHERE country_code IN ('US','CA','MX');
10、NOT条件
SELECT * FROM products WHERE NOT category = 'Clothing';
11、BETWEEN条件
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-32';
12、IS NULL条件
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
13、IS NOT NULL条件
SELECT * FROM customers WHERE email IS NOT NULL;
九、MySQL UPDATE更新
UPDATE table_name
SET column1 = value1,column2 = value2
WHERE condition
参数说明：
table_name是更细数据的表的名称
column1,column2是更新的列的名称
value1,value2是新的值，用于替换旧的值
WHERE condition是一个可选的子句
实例
1、更新单个列的值：
UPDATE employees
SET salary = 60000
WHERE employee_id = 101
2、更新多个列的值：
UPDATE orders
STE status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;
3、使用表达式更新值
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics'
4、更新使用子查询的值
UPDATE customers
SET total_purchases = (
SELECT SUM(amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';
十、MySQL DELETE语句
语法：
DELETE FROM table_name
WHERE condition;
参数说明：
table_name是删除数据的表的名称
WHERE condition是一个可选的子句。
实例
1、删除复合条件的行
DELETE FROM students
WHERE graduation_year = 2021;
2、删除所有行
DELETE FROM orders;
3、使用子查询删除复合条件的行
DELETE FROM customers
WHERE customer_id IN(
SELECT customer_id
FROM orders
WHERE order_date < '2023-01-01'
);
十一、MySQL LIKE子句
%通配符表示零个或多个字符；_通配符表示一个字符；不区分大小写的匹配
实例：
SELECT * FROM customers WHERE last_name LIKE 'S%';
SELECT * FROM products WHERE product_name LIKE '_a%';
十二、MySQL ORDER BY(排序）语句
语法：
SELECT column1,column2
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC]
1.单列排序：
SELECT * FROM products
ORDER BY products_name ASC;
2.多列排序：
SELECT * FROM employees
ORDER BY department_id ASC,hire_date DESC;
3.使用数字表列列的位置
ORDER BY 3 DESC, 1 ASC;
十三、MySQL GROUP BY语句
GROUP BY语句根据一个或多个列对结果集进行分组
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
十四、MySQL UNION操作符
描述
MySQL UNION操作符用于连接两个以上的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;
2、使用过滤条件的UNION
SELECT product_name FROM products WHERE category = 'Electronics'
UNION
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name
3、UNION操作中的列数和数据类型必须相同
SELECT first_name,last_name FROM employees
UNION
SELECT department_name,NULL FROM departments
ORDER BY first_name;
4、使用UNION ALL不去除重复行
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;
十五、MySQL连接的使用
INNER JOIN(内连接)：获取两个表中字段匹配关系的记录
LEFT JOIN(左连接)：获取左表所有记录
RIGHT JOIN(有连接)：获取右表所有记录
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、多表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;
3、使用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
1、简单的LEFT JOIN:
SELECT orders.order_id,customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;
2、多表LEFT JOIN:
SELECT orders.order_id,customers.customer_name,products.product_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.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;
3、使用WHERE子句进行过滤
SELECT orders.order_id,customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';
RIGHT JOIN和LEFT JOIN使用方法是一样的。
十六、MySQL ALTER命令
描述：当我们需要修改数据表名或者数据表字段时，就需要使用到MySQL ALTER命令
1、添加列
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
实例
ALTER TABLE employees
ADD COLUMN birth_date DATE;
2、修改列的数据类型
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype
实例
ALTER TABLE employees
MODIFY COLUM 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);
4、删除列
ALTER TABLE table_name
DROP COLUMN column_name;
实例
ALTER TABLE employees
DROP COLUMN birth_date;
5、添加PRIMARY KEY
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
实例
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)
实例
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;
实例ALTER TABLE employees
RENAME TO staff;
十七、MySQL处理重复数据
SELECT DISTINCT last_name,first_name
FROM person_tb1;
十八、MySQL删除数据库
drop命令删除数据库
DROP DATABE
十九、MySQL删除数据表
DROP TABLE table_name;
文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树SQL高级技巧CTE和递归查询92732 人正在系统学习中
原文链接：https://blog.csdn.net/qq_58997690/article/details/141789392
