一、创建数据库
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;
标签:精通,01,name,MySQL,WHERE,orders,SELECT,入门 From: https://blog.csdn.net/qq_58997690/article/details/141789392