1MySQL基本概念
MySQL是一個開源的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS）境钟，使用結(jié)構(gòu)化查詢語言（SQL）作為查詢和操作數(shù)據(jù)的接口。關(guān)系型數(shù)據(jù)庫將數(shù)據(jù)組織成表（table）吱韭，每個表包含多個字段（column）和記錄（row）。
2SQL語言：
數(shù)據(jù)定義語言（DDL）痘煤、數(shù)據(jù)操作語言（DML）猿规、數(shù)據(jù)查詢語言（DQL）和數(shù)據(jù)控制語言（DCL）
DDL（Data Definition Language）：CREATE, ALTER, DROP
DML（Data Manipulation Language）：INSERT, UPDATE, DELETE
DQL（Data Query Language）：SELECT
DCL（Data Control Language）：GRANT, REVOKE
3數(shù)據(jù)庫設(shè)計
數(shù)據(jù)庫設(shè)計包括創(chuàng)建表、視圖蘸拔、索引等對象环葵。規(guī)范化理論有助于設(shè)計高效、可維護(hù)的數(shù)據(jù)庫結(jié)構(gòu)
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT
);
4數(shù)據(jù)類型
MySQL支持多種數(shù)據(jù)類型张遭，如整數(shù)（INT, SMALLINT, BIGINT等）、浮點(diǎn)數(shù)（FLOAT, DOUBLE等）缔恳、字符串（VARCHAR, CHAR, TEXT等）和日期時間（DATE, TIME, DATETIME等）
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
price DECIMAL(10, 2),
release_date DATE
);
5數(shù)據(jù)查詢
使用SELECT語句查詢數(shù)據(jù)洁闰，可以進(jìn)行篩選、排序扑眉、分組和連接等操作雅镊。
篩選（WHERE）
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees ORDER BY last_name;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT e.first_name, e.last_name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;
6數(shù)據(jù)操作
使用INSERT、UPDATE和DELETE語句操作數(shù)據(jù)耸弄。
INSERT INTO employees (first_name, last_name, department_id) VALUES ('John', 'Doe', 1);
UPDATE employees SET department_id = 2 WHERE id = 1;
DELETE FROM employees WHERE id = 1;
7函數(shù)與表達(dá)式
MySQL提供多種函數(shù)，如字符串砰诵、數(shù)值捌显、日期和聚合函數(shù)
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;
SELECT ROUND(price, 0) as rounded_price FROM products;
SELECT DATEDIFF(NOW(), release_date) as days_since_release FROM products;
SELECT department_id, AVG(salary) as average_salary FROM employees GROUP BY department_id;
8索引與優(yōu)化
創(chuàng)建索引可以提高查詢性能，但也會增加數(shù)據(jù)插入和更新的開銷扶歪。要權(quán)衡利弊，根據(jù)實際情況選擇合適的索引妹萨。
CREATE INDEX idx_employees_department_id ON employees (department_id);
9事務(wù)處理
事務(wù)是一組操作序列炫欺，保證數(shù)據(jù)的完整性和一致性。使用START TRANSACTION品洛、COMMIT和ROLLBACK控制事務(wù)。
START TRANSACTION;
INSERT INTO employees (first_name, last_name, department_id) VALUES ('Jane', 'Doe', 1);
UPDATE departments SET employee_count = employee_count + 1 WHERE id = 1;
COMMIT; -- 或者 ROLLBACK; 回滾事務(wù)
10數(shù)據(jù)導(dǎo)入導(dǎo)出
使用mysqldump帽揪、LOAD DATA INFILE等工具導(dǎo)入導(dǎo)出數(shù)據(jù)
mysqldump -u username -p dbname > backup.sql
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
