MySQL数据库基础与核心操作指南
数据库基本概念
数据库是按照特定数据结构组织、存储和管理数据的系统，它是在计算机中长期存储的、有组织的、可共享的数据集合。
数据库类型分类
- 网状数据库：如通用汽车公司的IDS系统，采用节点方式存储
- 层次数据库：如IBM的IMS系统，使用树状结构存储
- 关系型数据库：如Oracle、DB2、MySQL、SQL Server，以表格形式存储，通过关联关系实现数据访问
- 非关系型数据库：如MongoDB、Redis，多采用键值对存储方式
MySQL概述
MySQL是由瑞典MySQL AB公司开发的关系型数据库管理系统，现隶属于Oracle公司，是市场上最流行的开源关系型数据库之一。
环境配置
Windows系统配置：
- 创建系统变量MYSQL_HOME：C:\Program Files\MySQL\MySQL Server 5.7
- 在PATH变量中添加：%MYSQL_HOME%\bin
登录MySQL：
mysql -uroot -p密码
-- 或者
mysql -uroot -p
-- 然后输入密码
核心目录结构
- bin目录：可执行命令文件
- lib目录：库文件
- include目录：头文件
- share目录：字符集和语言信息
配置文件说明
配置文件路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
- default-character-set：客户端字符集
- character-set-server：服务端字符集
- port：端口号
- default-storage-engine=INNODB：默认存储引擎
SQL语言基础
SQL（结构化查询语言）是用于管理关系数据库的标准语言，主要包含增删改查操作（CRUD）。
数据库基本操作
# 查看所有数据库
SHOW DATABASES;
# 创建数据库
CREATE DATABASE mydb1;
CREATE DATABASE mydb2 CHARACTER SET gbk;
CREATE DATABASE IF NOT EXISTS mydb2;
# 查看数据库信息
SHOW CREATE DATABASE mydb2;
# 修改数据库编码
ALTER DATABASE mydb2 CHARACTER SET utf8;
# 删除数据库
DROP DATABASE mydb1;
# 查看当前数据库
SELECT DATABASE();
# 使用数据库
USE mydb2;
数据查询操作
基础查询语法
# 查询指定列
SELECT employee_id, first_name, email FROM t_employees;
# 查询所有列
SELECT * FROM t_employees;
# 列运算
SELECT employee_id, first_name, salary*12 FROM t_employees;
# 列别名
SELECT employee_id AS '编号', first_name AS '姓名', salary*12 AS '年薪' FROM t_employees;
# 去重查询
SELECT DISTINCT manager_id FROM t_employees;
排序查询
# 单列排序
SELECT employee_id, first_name, salary FROM t_employees ORDER BY CONVERT(salary, DECIMAL) ASC;
# 多列排序
SELECT employee_id, first_name, salary FROM t_employees ORDER BY salary DESC, employee_id DESC;
条件查询
# 等值查询
SELECT employee_id, first_name, salary FROM t_employees WHERE salary = 11000;
# 逻辑运算
SELECT employee_id, first_name, salary FROM t_employees WHERE salary = 11000 AND commission_pct = 0.30;
# 范围查询
SELECT employee_id, first_name, salary FROM t_employees WHERE salary BETWEEN 6000 AND 10000;
# NULL值查询
SELECT employee_id, first_name, salary, commission_pct FROM t_employees WHERE commission_pct IS NULL;
# 枚举查询
SELECT employee_id, first_name, salary, department_id FROM t_employees WHERE department_id IN (70, 80, 90);
# 模糊查询
SELECT employee_id, first_name, salary, department_id FROM t_employees WHERE first_name LIKE 'L%';
分支查询
SELECT employee_id, first_name, salary, department_id,
CASE
WHEN salary >= 10000 THEN 'A'
WHEN salary >= 8000 AND salary < 10000 THEN 'B'
WHEN salary >= 6000 AND salary < 8000 THEN 'C'
WHEN salary >= 4000 AND salary < 6000 THEN 'D'
ELSE 'E'
END AS level
FROM t_employees;
时间函数
SELECT SYSDATE(); -- 当前系统时间
SELECT CURDATE(); -- 当前日期
SELECT CURTIME(); -- 当前时间
SELECT WEEK(SYSDATE()); -- 当前周数
SELECT YEAR('2021-3-16'); -- 指定年份
SELECT DATEDIFF('2021-2-2', '2020-2-2'); -- 间隔天数
字符串函数
SELECT CONCAT('My', 'S', 'QL'); -- 字符串连接
SELECT INSERT('这是数据库', 3, 2, 'MySQL'); -- 字符串替换
SELECT LOWER('MYSQL'); -- 转小写
SELECT UPPER('mysql'); -- 转大写
SELECT SUBSTRING('JavaMySQLOracle', 5, 5); -- 字符串截取
聚合函数
SELECT SUM(salary) FROM t_employees; -- 求和
SELECT AVG(salary) FROM t_employees; -- 平均值
SELECT MAX(salary) FROM t_employees; -- 最大值
SELECT MIN(salary) FROM t_employees; -- 最小值
SELECT COUNT(employee_id) FROM t_employees; -- 计数
分组查询
SELECT department_id, COUNT(employee_id) FROM t_employees GROUP BY department_id;
SELECT department_id, AVG(salary) FROM t_employees GROUP BY department_id;
限定查询
SELECT * FROM t_employees LIMIT 0, 5; -- 前5条记录
SELECT * FROM t_employees LIMIT 3, 10; -- 从第4条开始的10条记录
子查询
SELECT * FROM t_employees WHERE salary > (SELECT salary FROM t_employees WHERE first_name = 'Bruce');
SELECT * FROM t_employees WHERE department_id IN (SELECT department_id FROM t_employees WHERE last_name = 'King');
连接查询
SELECT * FROM t_employees e INNER JOIN t_departments d ON e.department_id = d.department_id;
SELECT * FROM t_employees e LEFT JOIN t_departments d ON e.department_id = d.department_id;
DML数据操作
插入数据
INSERT INTO t_jobs (job_id, job_title, min_salary, max_salary)
VALUES ('java_le', 'java_lecturer', 2500, 9000);
更新数据
UPDATE t_employees SET salary = 25000 WHERE employee_id = '100';
UPDATE t_employees SET job_id = 'ST_MAN', salary = 3500 WHERE employee_id = '135';
删除数据
DELETE FROM t_employees WHERE employee_id = '135';
DELETE FROM t_employees WHERE first_name = 'Peter' AND last_name = 'Hall';
清空表
TRUNCATE TABLE t_countries;
数据表操作
创建表
CREATE TABLE subject (
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
) CHARSET=utf8;
修改表结构
ALTER TABLE subject ADD gradeId INT; -- 添加列
ALTER TABLE subject MODIFY subjectName VARCHAR(10); -- 修改列
ALTER TABLE subject DROP gradeId; -- 删除列
ALTER TABLE subject CHANGE subjectHours classHours INT; -- 修改列名
ALTER TABLE subject RENAME sub; -- 修改表名
删除表
DROP TABLE sub;
约束机制
主键约束
CREATE TABLE subject (
subjectId INT PRIMARY KEY,
subjectName VARCHAR(20),
subjectHours INT
) CHARSET=utf8;
唯一约束
CREATE TABLE subject (
subjectId INT PRIMARY KEY,
subjectName VARCHAR(20) UNIQUE,
subjectHours INT
) CHARSET=utf8;
自动增长
CREATE TABLE subject (
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE,
subjectHours INT
) CHARSET=utf8;
外键约束
CREATE TABLE speciality (
id INT PRIMARY KEY AUTO_INCREMENT,
specialName VARCHAR(20) UNIQUE NOT NULL
) CHARSET=utf8;
CREATE TABLE subject (
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20,
specialId INT NOT NULL,
CONSTRAINT fk_subject_specialId FOREIGN KEY(specialId) REFERENCES speciality(id)
) CHARSET=utf8;
事务处理
START TRANSACTION;
UPDATE account SET money = money - 1000 WHERE id = 1;
UPDATE account SET money = money + 1000 WHERE id = 2;
COMMIT;
-- 或者出错时
ROLLBACK;
权限管理
CREATE USER 'zhangsan' IDENTIFIED BY '123';
GRANT ALL ON companydb.* TO 'zhangsan';
REVOKE ALL ON companydb.* FROM 'zhangsan';
DROP USER 'zhangsan';
视图操作
CREATE VIEW t_empInfo AS
SELECT employee_id, first_name, last_name, email, salary FROM t_employees;
SELECT * FROM t_empInfo WHERE employee_id = '101';
DROP VIEW t_empInfo;
SQL语言分类
- DQL：数据查询语言（SELECT）
- DDL：数据定义语言（CREATE、ALTER、DROP）
- DML：数据操作语言（INSERT、UPDATE、DELETE）
- TPL：事务处理语言（COMMIT、ROLLBACK）
- DCL：数据控制语言（GRANT、REVOKE）
