MySQL 数据库学习笔记

连接MySQL服务器

本地连接

mysql -u root -p

远程连接

mysql -h 主机IP -u 用户名 -p

SSH连接到远程MySQL

ssh 用户名@服务器IP
# 连接成功后再执行
mysql -u root -p

SSH配置（如需远程连接）

编辑SSH配置文件 /etc/ssh/sshd_config:

# 启用SSH端口
Port 22

# 允许root登录
PermitRootLogin yes

# 启用公钥认证
PubkeyAuthentication yes

重启SSH服务:

systemctl restart sshd

创建与维护数据库

1. 创建数据库

基本语法

CREATE DATABASE <数据库名>;

指定字符集

CREATE DATABASE <数据库名> CHARACTER SET <字符集名>;

完整语法

CREATE DATABASE <数据库名> 
[DEFAULT] CHARACTER SET <字符集名> 
[COLLATE <排序规则名>];

示例

-- 创建基本数据库
CREATE DATABASE navicat_db;

-- 创建指定字符集的数据库
CREATE DATABASE sql_db CHARACTER SET gb2312;

-- 创建UTF8MB4字符集的数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 查看数据库

查看所有数据库

SHOW DATABASES;

查看数据库创建信息

SHOW CREATE DATABASE <数据库名>;

示例

SHOW CREATE DATABASE sql_db;

3. 修改数据库

修改字符集

ALTER DATABASE <数据库名> CHARACTER SET <字符集名>;

示例

ALTER DATABASE sql_db CHARACTER SET utf8mb4;

4. 使用数据库

USE <数据库名>;

5. 删除数据库

DROP DATABASE <数据库名>;

示例

DROP DATABASE navicat_db;
DROP DATABASE sql_db;

创建与维护数据表

1. 创建数据表

基本语法

CREATE TABLE <表名> (
    字段名 数据类型 [约束条件],
    字段名 数据类型 [约束条件],
    ...
);

完整语法示例

CREATE TABLE course (
    cno CHAR(5) NOT NULL PRIMARY KEY,
    cname VARCHAR(20) NOT NULL,
    credit INT DEFAULT 0,
    description TEXT
);

常用数据类型

类型	说明	示例
INT	整数	age INT
VARCHAR	变长字符串	name VARCHAR(50)
CHAR	定长字符串	code CHAR(10)
TEXT	长文本	content TEXT
DATE	日期	birth DATE
DATETIME	日期时间	created_at DATETIME
DECIMAL	精确小数	price DECIMAL(10,2)
BOOLEAN	布尔值	is_active BOOLEAN

约束类型

约束	说明	示例
PRIMARY KEY	主键	id INT PRIMARY KEY
FOREIGN KEY	外键	user_id INT REFERENCES users(id)
NOT NULL	非空	name VARCHAR(50) NOT NULL
UNIQUE	唯一	email VARCHAR(100) UNIQUE
DEFAULT	默认值	status INT DEFAULT 0
CHECK	检查约束	age INT CHECK(age >= 18)

复杂表示例

CREATE TABLE student (
    sno CHAR(10) NOT NULL PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    ssex CHAR(2) DEFAULT '男',
    sage INT CHECK(sage BETWEEN 15 AND 50),
    sdept VARCHAR(30),
    speciality VARCHAR(50),
    enrollment_date DATE,
    FOREIGN KEY (sdept) REFERENCES department(dept_name)
);

CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sno CHAR(10) NOT NULL,
    cno CHAR(5) NOT NULL,
    score DECIMAL(5,2) CHECK(score BETWEEN 0 AND 100),
    exam_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sno) REFERENCES student(sno),
    FOREIGN KEY (cno) REFERENCES course(cno),
    UNIQUE KEY unique_student_course (sno, cno)
);

2. 插入数据

基本语法

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

批量插入

INSERT INTO course (cno, cname) VALUES
('C01', '数据库'),
('C02', '数学'),
('C03', '信息系统'),
('C04', '操作系统'),
('C05', '计算机网络');

插入完整记录

INSERT INTO student VALUES 
('2021001', '张三', '男', 20, '计算机', '软件工程', '2021-09-01'),
('2021002', '李四', '女', 19, '数学', '应用数学', '2021-09-01');

3. 修改表结构

添加字段

ALTER TABLE <表名> ADD <字段名> <数据类型> [约束];

示例

-- 添加入学时间字段
ALTER TABLE student ADD enrollment_date DATE;

-- 添加带默认值的字段
ALTER TABLE student ADD status INT DEFAULT 1;

修改字段类型

ALTER TABLE <表名> MODIFY <字段名> <新数据类型> [约束];

示例

-- 修改字段长度
ALTER TABLE student MODIFY sdept VARCHAR(50);

-- 修改字段类型和约束
ALTER TABLE student MODIFY sage INT NOT NULL;

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <数据类型> [约束];

示例

-- 重命名字段
ALTER TABLE student CHANGE sdept sdepartment VARCHAR(30);

-- 同时修改字段名和类型
ALTER TABLE student CHANGE sdepartment dept_name VARCHAR(50) NOT NULL;

删除字段

ALTER TABLE <表名> DROP <字段名>;

示例

-- 删除专业字段
ALTER TABLE student DROP speciality;

重命名表

ALTER TABLE <旧表名> RENAME <新表名>;

示例

-- 重命名表
ALTER TABLE teaching RENAME teach;

-- 或者使用RENAME TABLE语句
RENAME TABLE old_table TO new_table;

4. 删除表

DROP TABLE <表名>;

示例

DROP TABLE teach;

5. 复制表

复制表结构和数据

CREATE TABLE <新表名> AS SELECT * FROM <源表名>;

只复制表结构

CREATE TABLE <新表名> LIKE <源表名>;

示例

-- 完全复制
CREATE TABLE student_backup AS SELECT * FROM student;

-- 只复制结构
CREATE TABLE student_structure LIKE student;

常用操作命令总结

数据库操作

-- 创建数据库
CREATE DATABASE db_name CHARACTER SET utf8mb4;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE db_name;

-- 查看当前数据库
SELECT DATABASE();

-- 修改数据库字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE db_name;

表操作

-- 创建表
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC table_name;
-- 或者
DESCRIBE table_name;

-- 查看创建表的SQL
SHOW CREATE TABLE table_name;

-- 重命名表
RENAME TABLE old_name TO new_name;

-- 删除表
DROP TABLE table_name;

字段操作

-- 添加字段
ALTER TABLE table_name ADD column_name VARCHAR(100);

-- 修改字段类型
ALTER TABLE table_name MODIFY column_name INT;

-- 修改字段名
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(100);

-- 删除字段
ALTER TABLE table_name DROP column_name;

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE table_name ADD FOREIGN KEY (user_id) REFERENCES users(id);

数据操作

-- 插入数据
INSERT INTO table_name (col1, col2) VALUES (value1, value2);

-- 查询数据
SELECT * FROM table_name WHERE condition;

-- 更新数据
UPDATE table_name SET col1 = value1 WHERE condition;

-- 删除数据
DELETE FROM table_name WHERE condition;

实用技巧

1. 字符集设置

-- 创建数据库时指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表时指定字符集
CREATE TABLE mytable (
    id INT PRIMARY KEY
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 索引操作

-- 添加普通索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

-- 添加全文索引
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);

3. 查看表信息

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看表的列信息
SHOW COLUMNS FROM table_name;

4. 备份与恢复

# 备份数据库
mysqldump -u username -p database_name > backup.sql

# 恢复数据库
mysql -u username -p database_name < backup.sql

视图 (VIEW)

1. 什么是视图

视图是基于SQL语句的结果集的可视化表。视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库表中的字段。

2. 创建视图

基本语法

CREATE VIEW <视图名> AS
SELECT <列名> FROM <表名> [WHERE 条件];

创建复杂视图

CREATE VIEW student_course_view AS
SELECT 
    s.sno,
    s.sname,
    s.sdept,
    c.cno,
    c.cname,
    sc.score
FROM student s
JOIN score sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno;

创建带统计的视图

CREATE VIEW student_score_stats AS
SELECT 
    s.sno,
    s.sname,
    COUNT(sc.cno) AS course_count,
    AVG(sc.score) AS avg_score,
    MAX(sc.score) AS max_score,
    MIN(sc.score) AS min_score
FROM student s
LEFT JOIN score sc ON s.sno = sc.sno
GROUP BY s.sno, s.sname;

3. 查看视图

查看所有视图

SHOW FULL TABLES WHERE table_type = 'VIEW';

查看视图结构

DESC <视图名>;
-- 或者
DESCRIBE <视图名>;

查看视图创建语句

SHOW CREATE VIEW <视图名>;

4. 使用视图

查询视图数据

-- 基本查询
SELECT * FROM student_course_view;

-- 带条件查询
SELECT * FROM student_course_view WHERE sdept = '计算机';

-- 统计查询
SELECT sdept, AVG(score) as avg_score 
FROM student_course_view 
GROUP BY sdept;

5. 修改视图

替换视图

CREATE OR REPLACE VIEW <视图名> AS
SELECT <新查询语句>;

示例

CREATE OR REPLACE VIEW student_course_view AS
SELECT 
    s.sno,
    s.sname,
    s.ssex,
    s.sdept,
    c.cno,
    c.cname,
    c.credit,
    sc.score,
    CASE 
        WHEN sc.score >= 90 THEN '优秀'
        WHEN sc.score >= 80 THEN '良好'
        WHEN sc.score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade_level
FROM student s
JOIN score sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno;

6. 删除视图

DROP VIEW <视图名>;

7. 视图的优点

• 简化查询:将复杂的SQL查询封装为简单的视图
• 安全性:只显示用户需要的数据,隐藏敏感信息
• 逻辑独立性:表结构改变时,只需修改视图
• 数据一致性:确保数据的一致性