-- 创建学生表
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据
INSERT INTO student (name, age) VALUES ('张三', 20), ('李四', 21);
-- 查询学生
SELECT * FROM student WHERE age > 20 ORDER BY age DESC;
@SpringBootApplication
@RestController
public class DemoApplication {
@GetMapping("/hello")
public String hello() {
return "Hello, Spring Boot!";
}
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
MyBatis
数据库访问层框架，比JDBC爽多了：
xml
<!-- mapper接口 -->
public interface UserMapper {
@Select("SELECT * FROM user WHERE id = #{id}")
User findById(Long id);
@Insert("INSERT INTO user(name) VALUES(#{name})")
void insert(User user);
}
Linux命令行（Command Line Interface，CLI）是与Linux系统交互的主要方式之一。与图形界面不同，命令行通过文本指令来控制系统，虽然学习曲线较陡，但具有以下优势：
高效快捷：熟练后可实现秒级操作
远程连接：通过SSH轻松管理远程服务器
可自动化：配合脚本实现批量处理
资源节省：不消耗图形界面资源
精确控制：可实现复杂精细的操作
打开终端
bash
# Ubuntu/Debian：Ctrl + Alt + T
# macOS：Command + Space 搜索"Terminal"
# Windows：WSL或安装Git Bash
# 远程连接Linux服务器
ssh username@server_ip
ssh -p 2222 username@server_ip # 指定端口
ssh -i ~/.ssh/private_key username@server_ip # 密钥登录
基础命令
bash
# 查看当前用户
whoami
# 查看主机名
hostname
# 查看当前目录
pwd
# 查看当前日期时间
date
# 查看日历
cal
cal 2026 # 查看全年日历
# 显示帮助信息
man ls # 查看命令手册
ls --help # 快速帮助
help cd # Shell内置命令帮助
二、文件和目录管理
目录操作
bash
# 切换目录
cd /home # 切换到绝对路径
cd ./folder # 切换到相对路径
cd .. # 返回上级目录
cd ~ # 返回用户主目录
cd - # 返回上一次目录
# 查看目录内容
ls # 简单列表
ls -l # 详细列表
ls -a # 显示隐藏文件
ls -lh # 人类可读大小
ls -lt # 按修改时间排序
ls -lS # 按文件大小排序
ls -R # 递归显示子目录
# 创建目录
mkdir folder # 创建单个目录
mkdir -p folder/sub # 递归创建
mkdir -p folder1 folder2 # 同时创建多个
# 删除目录
rmdir folder # 删除空目录
rm -rf folder # 删除非空目录（慎用）
# 临时设置IP（重启后失效）
ip addr add 192.168.1.100/24 dev eth0
ip addr del 192.168.1.100/24 dev eth0
# 启用/禁用网卡
ip link set eth0 up
ip link set eth0 down
# 设置网关
ip route add default via 192.168.1.1
# 永久配置（Ubuntu Netplan）
# /etc/netplan/*.yaml
-- 查询所有记录
SELECT * FROM users;
-- 查询指定列
SELECT username, email FROM users;
-- 别名查询
SELECT username AS '用户名', email AS '邮箱' FROM users;
-- 去重查询
SELECT DISTINCT status FROM users;
-- 条件查询
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE status = 1 AND age >= 18;
SELECT * FROM users WHERE username LIKE '张%';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- 排序查询
SELECT * FROM users ORDER BY created_at DESC; -- 降序
SELECT * FROM users ORDER BY age ASC; -- 升序
SELECT * FROM users ORDER BY status, created_at DESC;
-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10, 20; -- 从第10条开始，取20条
SELECT * FROM users LIMIT 20; -- 前20条
聚合函数
sql
-- COUNT 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 1;
-- SUM 求和
SELECT SUM(views) FROM articles;
-- AVG 平均值
SELECT AVG(age) FROM users;
-- MAX 最大值
SELECT MAX(views) FROM articles;
-- MIN 最小值
SELECT MIN(age) FROM users;
-- 分组统计
SELECT status, COUNT(*) as total FROM users GROUP BY status;
SELECT user_id, COUNT(*) as article_count
FROM articles
GROUP BY user_id
HAVING article_count > 5;
-- 统计函数组合
SELECT
COUNT(*) as total_users,
AVG(age) as avg_age,
MAX(age) as max_age,
MIN(age) as min_age
FROM users;
多表查询
sql
-- 内连接
SELECT u.username, a.title, a.created_at
FROM users u
INNER JOIN articles a ON u.id = a.user_id;
-- 左连接
SELECT u.username, a.title
FROM users u
LEFT JOIN articles a ON u.id = a.user_id;
-- 右连接
SELECT u.username, a.title
FROM users u
RIGHT JOIN articles a ON u.id = a.user_id;
-- 多表连接
SELECT
u.username,
a.title,
c.content as comment
FROM users u
INNER JOIN articles a ON u.id = a.user_id
INNER JOIN comments c ON a.id = c.article_id
WHERE u.status = 1;
-- 子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM articles);
SELECT * FROM articles
WHERE views > (SELECT AVG(views) FROM articles);
-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM articles a WHERE a.user_id = u.id
);
高级查询技巧
sql
-- UNION合并查询
SELECT username FROM users WHERE status = 1
UNION
SELECT username FROM admin_users;
-- UNION ALL（保留重复）
SELECT username FROM users
UNION ALL
SELECT username FROM admin_users;
-- IF条件判断
SELECT
username,
IF(age >= 18, '成年', '未成年') as age_group
FROM users;
-- CASE语句
SELECT
username,
CASE
WHEN age < 18 THEN '未成年'
WHEN age BETWEEN 18 AND 30 THEN '青年'
WHEN age BETWEEN 31 AND 50 THEN '中年'
ELSE '老年'
END as age_category
FROM users;
-- COALESCE处理NULL
SELECT
username,
COALESCE(email, phone, '无联系方式') as contact
FROM users;
-- CONCAT拼接
SELECT CONCAT(username, ' - ', email) as user_info FROM users;
四、数据操作（CRUD）
插入数据
sql
-- 插入单条记录
INSERT INTO users (username, email, password, age)
VALUES ('张三', 'zhangsan@example.com', 'hashed_pass', 25);
-- 插入多条记录
INSERT INTO users (username, email, password, age) VALUES
('李四', 'lisi@example.com', 'pass1', 30),
('王五', 'wangwu@example.com', 'pass2', 28),
('赵六', 'zhaoliu@example.com', 'pass3', 35);
-- 插入查询结果
INSERT INTO articles (user_id, title, content)
SELECT id, CONCAT('欢迎 ', username), '欢迎加入我们！'
FROM users WHERE status = 1;
-- REPLACE（存在则替换）
REPLACE INTO users (id, username, email)
VALUES (1, '新张三', 'new@example.com');
-- ON DUPLICATE KEY UPDATE
INSERT INTO article_stats (article_id, views)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE views = views + 1;
更新数据
sql
-- 更新单条记录
UPDATE users SET age = 26 WHERE id = 1;
-- 更新多条记录
UPDATE users SET status = 0, updated_at = NOW() WHERE age < 18;
-- 使用表达式更新
UPDATE articles SET views = views + 1 WHERE id = 1;
-- 条件更新
UPDATE users
SET email = CONCAT(username, '@example.com')
WHERE email IS NULL;
-- 更新前备份
UPDATE articles
SET content = OLD_CONTENT
WHERE id = 1;
删除数据
sql
-- 删除单条记录
DELETE FROM users WHERE id = 1;
-- 批量删除
DELETE FROM users WHERE status = 0 AND created_at < '2025-01-01';
-- 清空表（慎用）
DELETE FROM articles; -- 可回滚，有日志
TRUNCATE TABLE articles; -- 快速清空，不可回滚
-- 级联删除（配合外键）
DELETE FROM users CASCADE; -- 外键设置后自动级联
五、索引与性能优化
索引类型
sql
-- 主键索引
ALTER TABLE users ADD PRIMARY KEY (id);
-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
-- 普通索引
ALTER TABLE users ADD INDEX idx_username (username);
-- 复合索引
ALTER TABLE articles ADD INDEX idx_user_date (user_id, created_at);
-- 全文索引（InnoDB/MyISAM）
ALTER TABLE articles ADD FULLTEXT INDEX idx_fulltext (title, content);
-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 优化' IN NATURAL LANGUAGE MODE);
-- 查看表的所有索引
SHOW INDEX FROM users;
-- 删除索引
ALTER TABLE users DROP INDEX idx_username;
DROP INDEX idx_username ON users;
SQL性能优化
sql
-- 避免SELECT *
SELECT id, username, email FROM users WHERE id = 1;
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
-- 优化示例
-- 优化前
SELECT * FROM articles a, users u
WHERE a.user_id = u.id AND u.status = 1;
-- 优化后（确保有索引）
SELECT a.id, a.title, u.username
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE u.status = 1;
-- 使用LIMIT限制结果
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10;
-- 分页优化（大数据量）
-- 低效
SELECT * FROM articles LIMIT 1000000, 10;
-- 高效
SELECT a.*
FROM articles a
INNER JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 1000000, 10) b
ON a.id = b.id;
慢查询日志
sql
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log
六、事务处理
事务基础
sql
-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;
-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务隔离级别
sql
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 示例：读已提交（Read Committed）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 示例：可重复读（Repeatable Read）- MySQL默认
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Serializable（串行化）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
保存点
sql
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
SAVEPOINT after_item;
-- 回滚到保存点
ROLLBACK TO after_item;
-- 提交（只提交保存点之前的内容）
COMMIT;
七、视图和存储过程
视图
sql
-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 1;
-- 创建复杂视图
CREATE VIEW user_articles_stats AS
SELECT
u.id,
u.username,
COUNT(a.id) as article_count,
SUM(COALESCE(a.views, 0)) as total_views
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
WHERE u.status = 1
GROUP BY u.id, u.username;
-- 使用视图
SELECT * FROM active_users;
SELECT * FROM user_articles_stats WHERE article_count > 5;
-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;
-- 删除视图
DROP VIEW IF EXISTS active_users;
存储过程
sql
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_stats(IN user_id INT, OUT total_articles INT)
BEGIN
SELECT COUNT(*) INTO total_articles
FROM articles
WHERE user_id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL get_user_stats(1, @total);
SELECT @total;
-- 创建带事务的存储过程
DELIMITER //
CREATE PROCEDURE transfer_money(
IN from_user INT,
IN to_user INT,
IN amount DECIMAL(10, 2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Transaction failed' as message;
END;
START TRANSACTION;
UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
COMMIT;
SELECT 'Transfer successful' as message;
END //
DELIMITER ;
-- 调用
CALL transfer_money(1, 2, 100.00);
-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_stats;
八、用户权限管理
用户操作
sql
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
-- 重命名用户
RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';
-- 删除用户
DROP USER 'newuser'@'localhost';
-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'newuser'@'localhost' = 'new_password';
权限管理
sql
-- 授予权限
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'newuser'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost';
GRANT EXECUTE ON PROCEDURE myapp.transfer_money TO 'newuser'@'localhost';
-- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';
SHOW GRANTS;
-- 撤销权限
REVOKE INSERT, UPDATE ON myapp.* FROM 'newuser'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
-- 解决：允许远程连接
GRANT ALL PRIVILEGES ON myapp.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- 检查用户主机限制
SELECT user, host FROM mysql.user WHERE user = 'root';
性能问题
sql
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
-- 查看查询缓存
SHOW VARIABLES LIKE 'query_cache%';
-- 优化表
OPTIMIZE TABLE users;
-- 分析表统计信息
ANALYZE TABLE users;
-- 检查表
CHECK TABLE users;
字符集问题
sql
-- 查看字符集设置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
-- 批量修改字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
#!/bin/bash
str1="hello"
str2="world"
str3="hello"
# 字符串比较
if [ "$str1" = "$str3" ]; then
echo "字符串相等"
fi
if [ -z "$empty_str" ]; then
echo "字符串为空"
fi
if [ -n "$str1" ]; then
echo "字符串非空"
fi
# 常见字符串操作符
# =: 相等
# !=: 不等
# -z: 字符串长度为0
# -n: 字符串长度不为0
文件测试
bash
#!/bin/bash
file="/path/to/file.txt"
if [ -e "$file" ]; then
echo "文件存在"
fi
if [ -f "$file" ]; then
echo "是普通文件"
fi
if [ -d "$file" ]; then
echo "是目录"
fi
if [ -r "$file" ]; then
echo "可读"
fi
if [ -w "$file" ]; then
echo "可写"
fi
if [ -x "$file" ]; then
echo "可执行"
fi
#!/bin/bash
# 遍历列表
for fruit in apple banana orange; do
echo "我喜欢吃: $fruit"
done
# 使用C风格语法
for ((i=0; i<5; i++)); do
echo "计数器: $i"
done
# 遍历文件
for file in *.txt; do
echo "处理文件: $file"
done
# 使用seq命令
for num in $(seq 1 5); do
echo "数字: $num"
done
# 简化写法
for i in {1..10}; do
echo $i
done
while循环
bash
#!/bin/bash
# 基本while循环
count=1
while [ $count -le 5 ]; do
echo "计数: $count"
((count++))
done
# 读取文件行
while read line; do
echo "读取到: $line"
done < file.txt
# 无限循环（配合break使用）
while true; do
read -p "输入q退出: " input
if [ "$input" = "q" ]; then
break
fi
echo "你输入了: $input"
done
# until循环（条件为假时继续）
num=1
until [ $num -gt 5 ]; do
echo "until循环: $num"
((num++))
done
循环控制
bash
#!/bin/bash
# break和continue
for i in {1..10}; do
if [ $i -eq 3 ]; then
continue # 跳过当前迭代
fi
if [ $i -eq 8 ]; then
break # 退出循环
fi
echo "数字: $i"
done
使用日志记录：便于问题排查 bashLOG_FILE="/var/log/script.log" function log() { echo "[$(date)] $1" | tee -a "$LOG_FILE" }
性能优化
bash
#!/bin/bash
# 避免子shell，使用内联操作
# 慢：
for item in $(cat file.txt); do
process $item
done
# 快：
while read item; do
process $item
done < file.txt
# 使用数组而非字符串拼接
array=()
for i in {1..1000}; do
array+=("item_$i")
done
