在 MySQL 数据库中,定义语言(DDL,Data Definition Language)用于定义和修改数据库结构。主要的 DDL 语句包括:
1. 创建数据库和表
创建数据库
CREATE DATABASE database_name;
例如:
sql
CREATE DATABASE my_database;
创建表
sql
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
例如:
sql
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
hire_date DATE
);
2. 修改数据库和表
修改表结构
sql
ALTER TABLE table_name
ADD column_name datatype [constraint];
例如:
sql
ALTER TABLE employees
ADD email VARCHAR(100);
修改列的数据类型
sql
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
例如:
sql
ALTER TABLE employees
MODIFY COLUMN name TEXT;
重命名列
sql
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
例如:
sql
ALTER TABLE employees
RENAME COLUMN position TO job_title;
删除列
sql
ALTER TABLE table_name
DROP COLUMN column_name;
例如:
sql
ALTER TABLE employees
DROP COLUMN email;
重命名表
sql
RENAME TABLE old_table_name TO new_table_name;
例如:
sql
RENAME TABLE employees TO staff;
3. 删除数据库和表
删除表
sql
DROP TABLE table_name;
例如:
sql
DROP TABLE employees;
删除数据库
sql
DROP DATABASE database_name;
例如:
sql
DROP DATABASE my_database;
4. 创建和删除索引
创建索引
sql
CREATE INDEX index_name
ON table_name (column_name);
例如:
sql
CREATE INDEX idx_name
ON employees (name);
删除索引
sql
DROP INDEX index_name
ON table_name;
例如:
sql
DROP INDEX idx_name
ON employees;
5. 约束定义
添加约束
sql
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name) REFERENCES other_table(column_name);
例如:
sql
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id);
删除约束
sql
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;
例如:
sql
ALTER TABLE employees
DROP FOREIGN KEY fk_department;
总结
这些 DDL 语句用于创建、修改和删除数据库及其对象,帮助你定义数据存储结构及其关系。合理使用这些语句可以有效管理数据库的结构和性能
MySQL 数据库查询语言主要是 SQL(结构化查询语言)。SQL 用于从数据库中检索、插入、更新和删除数据。下面是一些常用的 MySQL 查询语句及其示例:
1. 查询数据
-
选择所有列
sqlCopy Code
SELECT * FROM table_name;
例如:
sqlCopy Code
SELECT * FROM employees;
-
选择特定列
sqlCopy Code
SELECT column1, column2 FROM table_name;
例如:
sqlCopy Code
SELECT name, position FROM employees;
-
使用条件
sqlCopy Code
SELECT * FROM table_name WHERE condition;
例如:
sqlCopy Code
SELECT * FROM employees WHERE position = 'Manager';
-
排序结果
sqlCopy Code
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
例如:
sqlCopy Code
SELECT * FROM employees ORDER BY hire_date DESC;
-
限制结果数量
sqlCopy Code
SELECT * FROM table_name LIMIT number;
例如:
sqlCopy Code
SELECT * FROM employees LIMIT 10;
2. 插入数据
-
插入单条记录
sqlCopy Code
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
例如:
sqlCopy Code
INSERT INTO employees (name, position, hire_date) VALUES ('Alice', 'Developer', '2024-08-09');
-
插入多条记录
sqlCopy Code
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...);
例如:
sqlCopy Code
INSERT INTO employees (name, position, hire_date) VALUES ('Bob', 'Analyst', '2024-08-10'), ('Carol', 'Manager', '2024-08-11');
3. 更新数据
-
更新记录
sqlCopy Code
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
例如:
sqlCopy Code
UPDATE employees SET position = 'Senior Developer' WHERE name = 'Alice';
4. 删除数据
-
删除记录
sqlCopy Code
DELETE FROM table_name WHERE condition;
例如:
sqlCopy Code
DELETE FROM employees WHERE name = 'Bob';
5. 表的联合操作
-
内连接
sqlCopy Code
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
例如:
sqlCopy Code
SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
-
左连接
sqlCopy Code
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
例如:
sqlCopy Code
SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
-
右连接
sqlCopy Code
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
例如:
sqlCopy Code
SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
-
全外连接(MySQL 从 8.0.19 开始支持)
sqlCopy Code
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
6. 聚合函数
-
计数
sqlCopy Code
SELECT COUNT(column_name) FROM table_name;
例如:
sqlCopy Code
SELECT COUNT(*) FROM employees;
-
求和
sqlCopy Code
SELECT SUM(column_name) FROM table_name;
例如:
sqlCopy Code
SELECT SUM(salary) FROM employees;
-
平均值
sqlCopy Code
SELECT AVG(column_name) FROM table_name;
例如:
sqlCopy Code
SELECT AVG(salary) FROM employees;
-
最大值
sqlCopy Code
SELECT MAX(column_name) FROM table_name;
例如:
sqlCopy Code
SELECT MAX(salary) FROM employees;
-
最小值
sqlCopy Code
SELECT MIN(column_name) FROM table_name;
例如:
sqlCopy Code
SELECT MIN(salary) FROM employees;
7. 子查询
-
在 SELECT 中使用子查询
sqlCopy Code
SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS alias FROM table1;
例如:
sqlCopy Code
SELECT name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department FROM employees;
-
在 WHERE 中使用子查询
sqlCopy Code
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM other_table WHERE condition);
例如:
sqlCopy Code
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE department_name = 'Sales');
这些 SQL 语句和操作是 MySQL 查询语言的基础,帮助你有效地检索和管理数据库中的数据。
MySQL 数据操作语言(DML)语句用于操作数据库中的数据。以下是主要的 DML 语句及其示例:
1. 插入数据
插入单条记录
sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
示例:
sql
INSERT INTO employees (name, position, hire_date)
VALUES ('Alice', 'Developer', '2024-08-09');
插入多条记录
sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...);
示例:
sql
INSERT INTO employees (name, position, hire_date)
VALUES ('Bob', 'Analyst', '2024-08-10'), ('Carol', 'Manager', '2024-08-11');
2. 更新数据
更新记录
sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
示例:
sql
UPDATE employees
SET position = 'Senior Developer'
WHERE name = 'Alice';
3. 删除数据
删除记录
sql
DELETE FROM table_name
WHERE condition;
示例:
sql
DELETE FROM employees
WHERE name = 'Bob';
4. 选择数据
虽然选择数据属于查询操作,但它也是常见的 DML 语句的一部分:
选择所有记录
sql
SELECT * FROM table_name;
选择特定列
sql
SELECT column1, column2 FROM table_name;
带条件的选择
sql
SELECT * FROM table_name WHERE condition;
这些语句用于添加、更新、删除和查询数据库中的数据,帮助进行日常的数据管理任务。