S01-24 JavaSE-数据库-MySQL

[TOC]

核心问题

淘宝网、京东等系统退出后再次访问，信息仍存在的原因：数据库（持久化存储数据）。

解决之道

数据库是更高效的数据管理工具，类似“数据的图书馆”。
推荐MySQL版本：5.7（稳定），8.0+（更高版本）。

MySQL安装与配置

下载地址

MySQL 5.7：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

命令行连接MySQL

启动/停止服务（DOS命令）：
- 启动：net start mysql服务名
- 停止：net stop mysql服务名
连接指令：mysql -h主机名 -P端口 -u用户名 -p密码
- 细节：-p后无空格，默认主机为localhost，默认端口3306。

图形化工具

Navicat

功能：管理和开发MySQL/MariaDB的理想工具。
下载地址：http://www.navicat.com.cn/products/

SQLyog

功能：轻量型MySQL图形化管理工具。
下载地址：https://sqlyog.en.softonic.com/

数据库三层结构

DBMS（数据库管理系统）：安装在主机的管理程序，可管理多个数据库。
数据库（DB）：一个DBMS可包含多个数据库，用于分类存储数据。
表（Table）：一个数据库可包含多个表，是数据存储的基本单位（本质是文件）。

数据存储方式

表的一行称为一条记录，对应Java中的一个对象。
表的一列称为一个字段，对应Java对象的一个属性。

SQL语句分类

分类	功能	关键字
DDL	数据定义（创建/删除库、表）	CREATE、DROP、ALTER
DML	数据操作（增删改数据）	INSERT、UPDATE、DELETE
DQL	数据查询	SELECT
DCL	数据控制（用户权限）	GRANT、REVOKE

创建数据库

语法

sql

CREATE DATABASE [IF NOT EXISTS] db_name 
[DEFAULT CHARACTER SET charset_name] 
[DEFAULT COLLATE collation_name];

CHARACTER SET：指定字符集（默认utf8）。
COLLATE：指定校对规则（utf8_bin区分大小写，utf8_general_ci不区分，默认后者）。

练习案例

sql

-- 1. 创建数据库hsp_db01
CREATE DATABASE hsp_db01;

-- 2. 创建utf8字符集的数据库hsp_db02
CREATE DATABASE hsp_db02 CHARACTER SET utf8;

-- 3. 创建utf8字符集+区分大小写的数据库hsp_db03
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin;

查看与删除数据库

查看数据库

sql

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

-- 查看数据库创建语句
SHOW CREATE DATABASE db_name;

删除数据库

sql

-- 慎用！删除数据库（存在则删除）
DROP DATABASE [IF EXISTS] db_name;

备份与恢复数据库

备份（DOS命令行执行）

sql

-- 备份多个数据库
mysqldump -u用户名 -p -B 数据库1 数据库2 > 备份文件.sql

恢复（MySQL命令行执行）

sql

-- 恢复数据库
SOURCE 备份文件路径;

练习案例

sql

-- 备份hsp_db02和hsp_db03
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql

-- 恢复备份
SOURCE d:\\bak.sql;

创建表

语法

sql

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    ...
) 
[CHARACTER SET 字符集] 
[COLLATE 校对规则] 
[ENGINE 存储引擎];

练习案例（创建用户表）

sql

CREATE TABLE `user` (
    id INT,
    `name` VARCHAR(255),
    `password` VARCHAR(255),
    `birthday` DATE
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

MySQL常用数据类型

数值类型

类型	字节	范围（带符号）	范围（无符号）	说明
TINYINT	1	-128~127	0~255	小整数
SMALLINT	2	-32768~32767	0~65535	中整数
INT	4	-2147483648~2147483647	0~4294967295	常用整数
BIGINT	8	-9e18~9e18	0~1.8e19	大整数
FLOAT	4	-3.4e38~3.4e38	0~3.4e38	单精度浮点数
DOUBLE	8	-1.8e308~1.8e308	0~1.8e308	双精度浮点数
DECIMAL(M,D)	-	自定义	自定义	定点数（M总长度，D小数位数）

字符串类型

类型	长度限制	说明
CHAR(size)	最大255字符	固定长度字符串
VARCHAR(size)	最大65535字节	可变长度字符串（utf8下最大21844字符）
TEXT	0~2^16字节	文本数据
LONGTEXT	0~2^32字节	大文本数据

日期时间类型

类型	格式	说明
DATE	YYYY-MM-DD	日期（仅年月日）
DATETIME	YYYY-MM-DD HH:MM:SS	日期时间（年月日时分秒）
TIMESTAMP	YYYY-MM-DD HH:MM:SS	时间戳（自动记录insert/update时间）

修改表

核心语法

操作	语法
添加列	ALTER TABLE 表名 ADD 列名类型 [DEFAULT 值] [AFTER 某列];
修改列	ALTER TABLE 表名 MODIFY 列名类型 [DEFAULT 值];
删除列	ALTER TABLE 表名 DROP 列名;
修改表名	RENAME TABLE 旧表名 TO 新表名;
修改字符集	ALTER TABLE 表名 CHARACTER SET 字符集;
修改列名	ALTER TABLE 表名 CHANGE 旧列名新列名类型;

练习案例

sql

-- 1. 给emp表添加image列（VARCHAR(32)，在resume后）
ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume;

-- 2. 修改job列长度为60
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT '';

-- 3. 删除sex列
ALTER TABLE emp DROP sex;

-- 4. 表名改为employee
RENAME TABLE emp TO employee;

-- 5. 修改表字符集为utf8
ALTER TABLE employee CHARACTER SET utf8;

-- 6. 列名name改为user_name
ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT '';

DML语句（增删改）

INSERT（添加数据）

语法

sql

INSERT INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...);

练习案例

sql

-- 创建商品表
CREATE TABLE `goods` (
    id INT,
    goods_name VARCHAR(10),
    price DOUBLE NOT NULL DEFAULT 100
);

-- 添加单条数据
INSERT INTO `goods` (id, goods_name, price) VALUES(10, '华为手机', 2000);

-- 添加多条数据
INSERT INTO `goods` (id, goods_name, price) 
VALUES(20, '苹果手机', 3000), (30, '小米手机', 1999);

-- 给所有字段添加数据（省略列名）
INSERT INTO `goods` VALUES(40, 'OPPO手机', 2500);

-- 利用默认值添加数据
INSERT INTO `goods` (id, goods_name) VALUES(50, '格力手机'); -- price取默认值100

关键细节

数据类型需与字段匹配，长度不超过字段限制。
字符/日期类型需用单引号包裹。
允许插入NULL（字段需支持）。

UPDATE（修改数据）

语法

sql

UPDATE table_name
SET column1=value1, column2=value2, ...
[WHERE condition];

练习案例

sql

-- 1. 将所有员工薪水改为5000（慎用！无WHERE则修改所有记录）
UPDATE employee SET salary = 5000;

-- 2. 将姓名为"小妖怪"的员工薪水改为3000
UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪';

-- 3. 给"老妖怪"的薪水增加1000
UPDATE employee SET salary = salary + 1000 WHERE user_name = '老妖怪';

-- 4. 同时修改多个字段
UPDATE employee SET salary = 6000, job = '出主意的' WHERE user_name = '老妖怪';

DELETE（删除数据）

语法

sql

DELETE FROM table_name [WHERE condition];

练习案例

sql

-- 1. 删除姓名为"老妖怪"的记录
DELETE FROM employee WHERE user_name = '老妖怪';

-- 2. 删除表中所有记录（慎用！）
DELETE FROM employee;

-- 3. 不能直接删除某一列的值，需用UPDATE设为NULL或空字符串
UPDATE employee SET job = '' WHERE user_name = '老妖怪';

关键细节

DELETE仅删除记录，不删除表结构；删除表用DROP TABLE 表名。

DQL语句（查询）

基本语法

sql

SELECT [DISTINCT] *|column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column ASC|DESC]
[LIMIT start, rows];

基础查询练习

sql

-- 创建学生表
CREATE TABLE student(
    id INT NOT NULL DEFAULT 1,
    NAME VARCHAR(20) NOT NULL DEFAULT '',
    chinese FLOAT NOT NULL DEFAULT 0.0,
    english FLOAT NOT NULL DEFAULT 0.0,
    math FLOAT NOT NULL DEFAULT 0.0
);

-- 插入测试数据
INSERT INTO student(id, NAME, chinese, english, math) 
VALUES(1,'韩顺平',89,78,90), (2,'张飞',67,98,56), (3,'宋江',87,78,77),
(4,'关羽',88,98,90), (5,'赵云',82,84,67), (6,'欧阳锋',55,85,45),
(7,'黄蓉',75,65,30), (8,'韩信',45,65,99);

-- 1. 查询所有学生信息
SELECT * FROM student;

-- 2. 查询姓名和英语成绩
SELECT `name`, english FROM student;

-- 3. 过滤英语成绩重复数据
SELECT DISTINCT english FROM student;

-- 4. 统计每个学生总分（用别名）
SELECT `name`, (chinese+english+math) AS total_score FROM student;

-- 5. 所有学生总分加10分
SELECT `name`, (chinese+english+math+10) AS total_score FROM student;

WHERE条件查询

常用运算符

类型	运算符	功能
比较运算符	>, <, =, !=, <>	大于、小于、等于、不等于
范围运算符	BETWEEN...AND...	在指定区间内（闭区间）
集合运算符	IN(set)	在集合中
模糊运算符	LIKE, NOT LIKE	模糊查询（%匹配0+字符，_匹配1个字符）
空值判断	IS NULL, IS NOT NULL	判断是否为空
逻辑运算符	AND, OR, NOT	并且、或者、非

练习案例

sql

-- 1. 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE `name` = '赵云';

-- 2. 查询英语成绩大于90的同学
SELECT * FROM student WHERE english > 90;

-- 3. 查询总分大于200的同学
SELECT * FROM student WHERE (chinese+english+math) > 200;

-- 4. 查询数学成绩在80-90之间的同学
SELECT * FROM student WHERE math BETWEEN 80 AND 90;

-- 5. 查询数学成绩为89、90、91的同学
SELECT * FROM student WHERE math IN (89,90,91);

-- 6. 查询姓赵的学生（模糊查询）
SELECT * FROM student WHERE `name` LIKE '赵%';

-- 7. 查询第三个字符为'O'的学生
SELECT * FROM student WHERE `name` LIKE '__O%';

-- 8. 查询英语成绩大于语文成绩且总分大于200的同学
SELECT * FROM student WHERE english > chinese AND (chinese+english+math) > 200;

排序查询（ORDER BY）

sql

-- 1. 数学成绩升序排列（默认ASC）
SELECT * FROM student ORDER BY math;

-- 2. 总分降序排列
SELECT `name`, (chinese+english+math) AS total_score FROM student ORDER BY total_score DESC;

-- 3. 部门号升序，工资降序排列（多字段排序）
SELECT * FROM emp ORDER BY deptno ASC, sal DESC;

分页查询（LIMIT）

语法

sql

-- start：起始索引（从0开始），rows：查询条数
SELECT * FROM table_name ORDER BY 列名 LIMIT start, rows;

-- 分页公式：第n页 = LIMIT (n-1)*每页条数, 每页条数

练习案例

sql

-- 按id升序，每页显示3条记录
-- 第1页
SELECT * FROM emp ORDER BY empno LIMIT 0, 3;
-- 第2页
SELECT * FROM emp ORDER BY empno LIMIT 3, 3;
-- 第3页
SELECT * FROM emp ORDER BY empno LIMIT 6, 3;

合计/统计函数

常用函数

函数	功能
COUNT(*)	统计满足条件的记录数（包含NULL）
COUNT(列名)	统计满足条件的列值非NULL的记录数
SUM(列名)	计算数值列的总和
AVG(列名)	计算数值列的平均值
MAX(列名)	计算数值列的最大值
MIN(列名)	计算数值列的最小值

练习案例

sql

-- 1. 统计学生总数
SELECT COUNT(*) FROM student;

-- 2. 统计数学成绩大于90的学生数
SELECT COUNT(*) FROM student WHERE math > 90;

-- 3. 统计数学总成绩
SELECT SUM(math) FROM student;

-- 4. 统计语文、英语、数学三科总成绩
SELECT SUM(chinese) AS chinese_total, SUM(english) AS english_total, SUM(math) AS math_total FROM student;

-- 5. 计算数学平均分
SELECT AVG(math) FROM student;

-- 6. 计算总分最高分和最低分
SELECT MAX(chinese+english+math) AS max_total, MIN(chinese+english+math) AS min_total FROM student;

---- 7. COUNT(*)与COUNT(列名)的区别
CREATE TABLE t15 (`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom'), ('jack'), ('mary'), (NULL);
SELECT COUNT(*) FROM t15; -- 4（包含NULL）
SELECT COUNT(`name`) FROM t15; -- 3（排除NULL）

分组查询（GROUP BY + HAVING）

语法

sql

SELECT 列名, 统计函数 FROM table_name
[WHERE condition]
GROUP BY 列名
HAVING 分组后条件
ORDER BY 列名
LIMIT start, rows;

练习案例

sql

-- 1. 按岗位分组，统计每个岗位的员工数和平均工资
SELECT job, COUNT(*) AS emp_count, AVG(sal) AS avg_sal FROM emp GROUP BY job;

-- 2. 按部门和岗位分组，统计平均工资和最低工资
SELECT deptno, job, AVG(sal) AS avg_sal, MIN(sal) AS min_sal FROM emp GROUP BY deptno, job;

-- 3. 统计平均工资大于2000的部门（分组后过滤）
SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 2000;

-- 4. 统计平均工资大于1000的部门，按平均工资降序，取前2条
SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1000 ORDER BY avg_sal DESC LIMIT 0,2;

常用函数

字符串函数

函数	功能
CHARSET(str)	返回字符串字符集
CONCAT(s1, s2, ...)	拼接字符串
INSTR(str, substr)	返回substr在str中首次出现的位置（无则0）
UCASE(str) / UPPER(str)	转换为大写
LCASE(str) / LOWER(str)	转换为小写
LEFT(str, length)	截取字符串左侧length个字符
LENGTH(str)	返回字符串字节长度
REPLACE(str, old, new)	替换字符串
SUBSTRING(str, pos, length)	从pos位置（1开始）截取length个字符
TRIM(str)	去除字符串首尾空格

练习案例

sql

-- 1. 拼接员工姓名和岗位
SELECT CONCAT(ename, '的工作是', job) FROM emp;

-- 2. 将员工姓名转为小写
SELECT LCASE(ename) FROM emp;

-- 3. 截取员工姓名前2个字符
SELECT LEFT(ename, 2) FROM emp;

-- 4. 替换岗位：MANAGER→经理
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp;

-- 5. 首字母小写显示员工姓名（两种方法）
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name FROM emp;

数学函数

函数	功能
ABS(num)	绝对值
BIN(num)	十进制转二进制
CEILING(num)	向上取整
FLOOR(num)	向下取整
FORMAT(num, n)	保留n位小数（四舍五入）
MOD(num1, num2)	取余
RAND()	生成0~1随机数

练习案例

sql

-- 1. 绝对值
SELECT ABS(-10) FROM DUAL;

-- 2. 向上取整
SELECT CEILING(-1.1) FROM DUAL;

-- 3. 保留2位小数
SELECT FORMAT(78.125458, 2) FROM DUAL;

-- 4. 取余
SELECT MOD(10, 3) FROM DUAL;

-- 5. 随机数
SELECT RAND() FROM DUAL;

日期时间函数

函数	功能
CURRENT_DATE()	获取当前日期（YYYY-MM-DD）
CURRENT_TIME()	获取当前时间（HH:MM:SS）
CURRENT_TIMESTAMP() / NOW()	获取当前日期时间
DATE_ADD(date, INTERVAL val type)	日期加指定时间（type：YEAR/DAY/MINUTE等）
DATE_SUB(date, INTERVAL val type)	日期减指定时间
DATEDIFF(date1, date2)	计算日期差（天数，date1-date2）
TIMEDIFF(time1, time2)	计算时间差
YEAR(date) / MONTH(date) / DAY(date)	提取年/月/日

练习案例

sql

-- 1. 获取当前日期时间
SELECT CURRENT_DATE(), CURRENT_TIME(), NOW() FROM DUAL;

-- 2. 计算2011-11-11与1990-01-01的天数差
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;

-- 3. 计算10分钟内发布的新闻
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);

-- 4. 计算活了多少天（假设1986-11-11出生）
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;

-- 5. 计算还能活多少天（假设活80岁）
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW()) FROM DUAL;

加密与系统函数

函数	功能
USER()	查询当前登录用户（用户@IP）
DATABASE()	查询当前使用的数据库
MD5(str)	对字符串进行MD5加密（32位）
PASSWORD(str)	MySQL用户密码加密（仅用于数据库用户）

练习案例

sql

-- 1. 查询当前用户
SELECT USER() FROM DUAL;

-- 2. 查询当前数据库
SELECT DATABASE() FROM DUAL;

-- 3. MD5加密（用户密码存储）
CREATE TABLE hsp_user (
    id INT,
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    pwd CHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO hsp_user VALUES(100, '韩顺平', MD5('hsp'));

流程控制函数

函数	功能
IF(expr1, expr2, expr3)	expr1为真返回expr2，否则返回expr3
IFNULL(expr1, expr2)	expr1非NULL返回expr1，否则返回expr2
CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END	多重分支判断

练习案例

sql

-- 1. IF函数：查询员工佣金，NULL显示0.0
SELECT ename, IF(comm IS NULL, 0.0, comm) AS comm FROM emp;
-- 或用IFNULL
SELECT ename, IFNULL(comm, 0.0) AS comm FROM emp;

-- 2. CASE函数：转换岗位名称
SELECT ename, 
CASE 
WHEN job = 'CLERK' THEN '职员' 
WHEN job = 'MANAGER' THEN '经理' 
WHEN job = 'SALESMAN' THEN '销售人员' 
ELSE job 
END AS job_name 
FROM emp;

多表查询

基本概念

多表查询需通过关联条件（表的个数-1个）避免笛卡尔集。
关联条件：通常是两张表的外键关联（如emp.deptno = dept.deptno）。

练习案例

sql

-- 准备表：emp（员工表）、dept（部门表）、salgrade（工资级别表）
-- 1. 显示雇员名、工资及所在部门名
SELECT e.ename, e.sal, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno; -- 关联条件

-- 2. 显示部门号为10的部门名、员工名和工资
SELECT d.dname, e.ename, e.sal 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.deptno = 10;

-- 3. 显示员工姓名、工资及工资级别
SELECT e.ename, e.sal, s.grade 
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal;

-- 4. 按部门降序排列员工信息
SELECT e.ename, e.sal, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno 
ORDER BY d.deptno DESC;

自连接（同表连接）

sql

-- 显示员工姓名和其上级姓名（emp表的mgr列关联empno列）
SELECT 
worker.ename AS '职员名', 
boss.ename AS '上级名' 
FROM emp worker, emp boss 
WHERE worker.mgr = boss.empno;

子查询（嵌套查询）

单行子查询（返回1行数据）

sql

-- 显示与SMITH同一部门的所有员工
SELECT * FROM emp 
WHERE deptno = (
    SELECT deptno FROM emp WHERE ename = 'SMITH'
);

多行子查询（返回多行数据，用IN）

sql

-- 查询和部门10工作相同的雇员（不含部门10）
SELECT ename, job, sal, deptno FROM emp 
WHERE job IN (
    SELECT DISTINCT job FROM emp WHERE deptno = 10
) AND deptno <> 10;

子查询作为临时表

sql

-- 查询各个类别中价格最高的商品
SELECT g.goods_id, g.cat_id, g.goods_name, g.shop_price 
FROM (
    SELECT cat_id, MAX(shop_price) AS max_price FROM ecs_goods GROUP BY cat_id
) temp, ecs_goods g 
WHERE temp.cat_id = g.cat_id AND temp.max_price = g.shop_price;

ALL/ANY操作符

sql

-- 1. 显示工资比部门30所有员工都高的员工
SELECT ename, sal, deptno FROM emp 
WHERE sal > ALL(
    SELECT sal FROM emp WHERE deptno = 30
);
-- 等价于
SELECT ename, sal, deptno FROM emp 
WHERE sal > (
    SELECT MAX(sal) FROM emp WHERE deptno = 30
);

-- 2. 显示工资比部门30任意一个员工高的员工
SELECT ename, sal, deptno FROM emp 
WHERE sal > ANY(
    SELECT sal FROM emp WHERE deptno = 30
);
-- 等价于
SELECT ename, sal, deptno FROM emp 
WHERE sal > (
    SELECT MIN(sal) FROM emp WHERE deptno = 30
);

子查询

多列子查询（manycolumn.sql）

概念：查询返回多个列数据的子查询语句。

案例1：查询与ALLEN的部门和岗位完全相同的所有雇员（不含ALLEN本人）

sql

-- 分析: 1. 得到ALLEN的部门和岗位
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN';

-- 分析: 2 把上面的查询当做子查询来使用，使用多列子查询语法匹配
SELECT *
FROM emp
WHERE (deptno, job) = (
    SELECT deptno, job
    FROM emp
    WHERE ename = 'ALLEN'
) AND ename != 'ALLEN';

案例2：查询和宋江数学、英语、语文成绩完全相同的学生

sql

SELECT *
FROM student
WHERE (math, english, chinese) = (
    SELECT math, english, chinese
    FROM student
    WHERE `name` = '宋江'
);

在from子句中使用子查询（subquery03.sql）

技巧：将子查询当作临时表使用。

案例1：查找每个部门工资高于本部门平均工资的人的资料

sql

-- 1. 先得到每个部门的部门号和对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno;

-- 2. 子查询作为临时表，与emp进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptno 
FROM emp, (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal;

案例2：查找每个部门工资最高的人的详细资料

sql

SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
    SELECT deptno, MAX(sal) AS max_sal
    FROM emp
    GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal;

课堂小练习：查询每个部门的信息和人员数量

sql

-- 1. 构建部门人员数量临时表
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno;

-- 2. 关联部门表和临时表
SELECT dname, dept.deptno, loc, tmp.per_num AS '人数'
FROM dept, (
    SELECT COUNT(*) AS per_num, deptno
    FROM emp
    GROUP BY deptno
) tmp 
WHERE tmp.deptno = dept.deptno;

-- 简化写法（表.* 显示所有列）
SELECT tmp.*, dname, loc
FROM dept, (
    SELECT COUNT(*) AS per_num, deptno FROM emp
    GROUP BY deptno
) tmp 
WHERE tmp.deptno = dept.deptno;

表复制

自我复制数据（蠕虫复制）

用途：为SQL效率测试创建海量数据。

步骤1：创建表并复制emp数据

sql

-- 创建表
CREATE TABLE my_tab01 (
    id INT,
    `name` VARCHAR(32),
    sal DOUBLE,
    job VARCHAR(32),
    deptno INT
);

-- 复制emp表数据到my_tab01
INSERT INTO my_tab01 (id, `name`, sal, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;

-- 自我复制（数据翻倍）
INSERT INTO my_tab01 SELECT * FROM my_tab01;

-- 统计数据量
SELECT COUNT(*) FROM my_tab01;

步骤2：删除表中重复记录

sql

-- 1. 创建临时表（复制原表结构）
CREATE TABLE my_tmp LIKE my_tab02;

-- 2. 去重复制原表数据到临时表
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;

-- 3. 清空原表
DELETE FROM my_tab02;

-- 4. 从临时表复制数据到原表
INSERT INTO my_tab02 SELECT * FROM my_tmp;

-- 5. 删除临时表
DROP TABLE my_tmp;

合并查询

作用：合并多个SELECT语句的结果，使用union或union all。

操作符	特点
union all	合并结果，不删除重复行
union	合并结果，自动去重

示例

sql

-- union all（保留重复）
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

-- union（去重）
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

MySQL表外连接

问题提出

普通多表查询只显示匹配的记录，需显示“无匹配”的记录时使用外连接。

外连接分类

左外连接：左侧表完全显示，右侧表匹配不到显示NULL
右外连接：右侧表完全显示，左侧表匹配不到显示NULL

准备测试数据

sql

-- 创建stu表
CREATE TABLE stu (
    id INT,
    `name` VARCHAR(32)
);
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');

-- 创建exam表
CREATE TABLE exam(
    id INT,
    grade INT
);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);

左外连接（显示所有人的成绩，无成绩显示NULL）

sql

SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;

右外连接（显示所有成绩，无匹配姓名显示NULL）

sql

SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;

练习：列出部门名称和员工信息（含无员工的部门）

sql

-- 左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno;

-- 右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno;

MySQL约束

作用：确保数据库数据满足商业规则，包括not null、unique、primary key、foreign key、check五种。

primary key（主键）

特点：唯一标识表行数据，不能重复且不能为NULL。

基本使用

sql

-- 方式1：字段后直接指定
CREATE TABLE t17 (
    id INT PRIMARY KEY, -- 主键列
    `name` VARCHAR(32),
    email VARCHAR(32)
);

-- 方式2：表定义最后指定
CREATE TABLE t20 (
    id INT,
    `name` VARCHAR(32),
    email VARCHAR(32),
    PRIMARY KEY(`name`)
);

-- 复合主键（id + name）
CREATE TABLE t18 (
    id INT,
    `name` VARCHAR(32),
    email VARCHAR(32),
    PRIMARY KEY (id, `name`) -- 复合主键
);

细节说明

一张表最多一个主键（可复合）
主键列值不可重复、不可为NULL
使用DESC 表名查看主键信息

not null（非空）

特点：插入数据时必须为该列提供值。

sql

CREATE TABLE t21 (
    id INT,
    `name` VARCHAR(32) NOT NULL -- 非空约束
);

unique（唯一）

特点：列值不能重复，可多个NULL。

sql

-- 单字段唯一
CREATE TABLE t21 (
    id INT UNIQUE, -- 唯一约束
    `name` VARCHAR(32)
);

-- 多字段唯一
CREATE TABLE t22 (
    id INT UNIQUE,
    `name` VARCHAR(32) UNIQUE -- 多个唯一约束
);

foreign key（外键）

作用：定义主表和从表的关系，外键列值必须在主表主键列存在或为NULL。

语法

sql

FOREIGN KEY(本表字段名) REFERENCES 主表名(主键名或unique字段名)

示例（学生-班级主从表）

sql

-- 主表（班级表）
CREATE TABLE my_class (
    id INT PRIMARY KEY, -- 班级编号
    `name` VARCHAR(32) NOT NULL DEFAULT ''
);

-- 从表（学生表）
CREATE TABLE my_stu (
    id INT PRIMARY KEY, -- 学生编号
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    class_id INT, -- 外键列
    FOREIGN KEY (class_id) REFERENCES my_class(id) -- 外键约束
);

-- 测试数据
INSERT INTO my_class VALUES(100, 'java'), (200, 'web'), (300, 'php');
INSERT INTO my_stu VALUES(1, 'tom', 100); -- 成功
INSERT INTO my_stu VALUES(4, 'mary', 400); -- 失败（400班级不存在）
INSERT INTO my_stu VALUES(5, 'king', NULL); -- 成功（外键允许NULL）

细节说明

主表字段必须是primary key或unique
表类型需为InnoDB（支持外键）
外键字段类型与主表主键类型一致
主表数据不能随意删除（受外键约束）

check

作用：强制行数据满足条件（MySQL5.7不生效，仅语法校验）。

sql

CREATE TABLE t23 (
    id INT PRIMARY KEY,
    `name` VARCHAR(32),
    sex VARCHAR(6) CHECK (sex IN('man','woman')), -- 性别约束
    sal DOUBLE CHECK (sal > 1000 AND sal < 2000) -- 工资约束
);

商店售货系统表设计案例

sql

CREATE DATABASE shop_db;

-- 商品表（goods）
CREATE TABLE goods (
    goods_id INT PRIMARY KEY,
    goods_name VARCHAR(64) NOT NULL DEFAULT '',
    unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
    category INT NOT NULL DEFAULT 0,
    provider VARCHAR(64) NOT NULL DEFAULT ''
);

-- 客户表（customer）
CREATE TABLE customer (
    customer_id CHAR(8) PRIMARY KEY,
    `name` VARCHAR(64) NOT NULL DEFAULT '', -- 非空
    address VARCHAR(64) NOT NULL DEFAULT '',
    email VARCHAR(64) UNIQUE NOT NULL, -- 唯一
    sex ENUM('男','女') NOT NULL, -- 枚举约束
    card_Id CHAR(18)
);

-- 购买表（purchase）
CREATE TABLE purchase (
    order_id INT UNSIGNED PRIMARY KEY,
    customer_id CHAR(8) NOT NULL DEFAULT '',
    goods_id INT NOT NULL DEFAULT 0,
    nums INT NOT NULL DEFAULT 0,
    -- 外键约束
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

自增长

作用：整数型字段自动从1开始递增，通常与primary key配合使用。

基本使用

sql

-- 创建自增长表
CREATE TABLE t24 (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 自增长列
    email VARCHAR(32) NOT NULL DEFAULT '',
    `name` VARCHAR(32) NOT NULL DEFAULT ''
);

-- 插入数据（自增长列可传NULL或省略）
INSERT INTO t24 VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t24 (email, `name`) VALUES('hsp@sohu.com', 'hsp');

-- 修改自增长起始值
ALTER TABLE t25 AUTO_INCREMENT = 100;
CREATE TABLE t25 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(32) NOT NULL DEFAULT '',
    `name` VARCHAR(32) NOT NULL DEFAULT ''
);

细节说明

自增长字段需为整数型
可配合unique使用（不推荐）
默认从1开始，可通过ALTER TABLE修改
插入时指定值则优先使用指定值

MySQL索引

索引快速入门

作用：提高查询速度（无需全表扫描），代价是占用磁盘空间、影响DML效率。

测试案例（海量数据800万条）

sql

-- 创建测试库和表
CREATE DATABASE tmp;
CREATE TABLE emp (
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    ename VARCHAR(20) NOT NULL DEFAULT "",
    job VARCHAR(9) NOT NULL DEFAULT "",
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2) NOT NULL,
    comm DECIMAL(7,2) NOT NULL,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);

-- 创建随机字符串函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$

-- 创建随机部门号函数
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(10+RAND()*500);
    RETURN i;
END $$

-- 创建存储过程插入800万条数据
CREATE PROCEDURE insert_emp(IN START INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; -- 关闭自动提交
    REPEAT
        SET i = i + 1;
        INSERT INTO emp VALUES ((START+i), rand_string(6), 'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
    UNTIL i = max_num END REPEAT;
    COMMIT; -- 批量提交
END $$

-- 执行存储过程
CALL insert_emp(100001, 8000000)$$
DELIMITER ;

-- 无索引查询（慢）
SELECT * FROM emp WHERE empno = 1234567;

-- 创建索引后查询（快）
CREATE INDEX empno_index ON emp (empno);
SELECT * FROM emp WHERE empno = 1234578; -- 0.003s vs 4.5s

索引类型

类型	说明
主键索引	主键自动成为索引（Primary key）
唯一索引	基于`unique`约束的索引
普通索引	普通字段创建的索引（INDEX）
全文索引	适用于MyISAM，开发中常用Solr/ES替代

索引使用语法

sql

-- 1. 创建普通索引
CREATE INDEX index_name ON tbl_name (col_name);
ALTER TABLE tbl_name ADD INDEX index_name (col_name);

-- 2. 创建唯一索引
CREATE UNIQUE INDEX index_name ON tbl_name (col_name);

-- 3. 创建主键索引
ALTER TABLE tbl_name ADD PRIMARY KEY (col_name);

-- 4. 删除索引
DROP INDEX index_name ON tbl_name;

-- 5. 删除主键索引
ALTER TABLE tbl_name DROP PRIMARY KEY;

-- 6. 查询索引
SHOW INDEX FROM tbl_name;
SHOW INDEXES FROM tbl_name;
SHOW KEYS FROM tbl_name;
DESC tbl_name;

适合创建索引的列

频繁作为查询条件的列
唯一性较好的列（不建议给性别等唯一性差的列建索引）
不建议给更新频繁的列建索引
不在WHERE子句中的列不建索引

MySQL事务

事务概念

作用：保证一组DML操作要么全成功，要么全失败（如转账）。

事务操作语法

sql

-- 1. 开始事务
START TRANSACTION;
-- 或
SET autocommit = OFF;

-- 2. 设置保存点
SAVEPOINT a;

-- 3. 执行DML操作
INSERT INTO t27 VALUES(100, 'tom');
SAVEPOINT b;
INSERT INTO t27 VALUES(200, 'jack');

-- 4. 回退到保存点
ROLLBACK TO b;
ROLLBACK TO a;

-- 5. 回退到事务开始
ROLLBACK;

-- 6. 提交事务（生效，不可回退）
COMMIT;

事务细节

未开始事务时，DML自动提交，不可回退
支持多个保存点，可回退到指定点
仅InnoDB支持事务，MyISAM不支持
事务期间表会加锁，防止并发修改

事务隔离级别

隔离级别问题

问题	说明
脏读	读取到其他事务未提交的修改
不可重复读	同一事务中多次查询，结果因其他事务修改而不同
幻读	同一事务中多次查询，结果因其他事务插入而不同

MySQL隔离级别

隔离级别	脏读	不可重复读	幻读	加锁
读未提交（Read uncommitted）	√	√	√	否
读已提交（Read committed）	×	√	√	否
可重复读（Repeatable read）	×	×	×	否
可串行化（Serializable）	×	×	×	是

隔离级别操作语法

sql

-- 查看当前会话隔离级别
SELECT @@tx_isolation;

-- 查看系统隔离级别
SELECT @@global.tx_isolation;

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;

-- 设置系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL repeatable read;

-- 全局配置（my.ini）
[mysqld]
transaction-isolation = REPEATABLE-READ

事务ACID特性

原子性（Atomicity）：事务不可分割，要么全执行要么全不执行
一致性（Consistency）：事务前后数据库状态一致
隔离性（Isolation）：并发事务互不干扰
持久性（Durability）：事务提交后，修改永久生效

MySQL表类型和存储引擎

主要存储引擎对比

特点	MyISAM	InnoDB	Memory
事务安全	×	√	×
外键支持	×	√	×
锁机制	表锁	行锁	表锁
存储限制	无	64TB	有（内存大小）
数据持久化	是	是	否（服务重启丢失）
访问速度	快	中	极快

存储引擎使用示例

sql

-- 创建MyISAM表
CREATE TABLE t28 (id INT, `name` VARCHAR(32)) ENGINE MYISAM;

-- 创建InnoDB表（默认）
CREATE TABLE t29 (id INT, `name` VARCHAR(32)) ENGINE INNODB;

-- 创建Memory表
CREATE TABLE t30 (id INT, `name` VARCHAR(32)) ENGINE MEMORY;

-- 修改存储引擎
ALTER TABLE t30 ENGINE = INNODB;

存储引擎选择建议

无需事务、追求速度：MyISAM
需要事务、外键：InnoDB
临时数据（如在线状态）：Memory

视图（view）

视图概念

作用：虚拟表，基于基表查询结果创建，用于隐藏敏感字段、简化查询。

视图基本使用

sql

-- 1. 创建视图
CREATE VIEW emp_view01 AS
SELECT empno, ename, job, deptno FROM emp;

-- 2. 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;

-- 3. 查看视图创建语句
SHOW CREATE VIEW emp_view01;

-- 4. 修改视图
ALTER VIEW emp_view01 AS
SELECT empno, ename FROM emp;

-- 5. 删除视图
DROP VIEW emp_view01;

-- 6. 视图嵌套
CREATE VIEW emp_view02 AS
SELECT empno, ename FROM emp_view01;

视图细节

视图仅存储结构（.frm文件），数据来自基表
视图数据修改会同步到基表，反之亦然
支持INSERT/UPDATE/DELETE（需满足基表约束）

视图最佳实践

安全：隐藏敏感字段（如sal、comm）
性能：避免复杂JOIN，直接查询视图
灵活：兼容旧系统表结构（映射到新表）

课堂练习：三表联合视图

sql

-- 基于emp、dept、salgrade创建视图
CREATE VIEW emp_view03 AS
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno
AND emp.sal BETWEEN losal AND hisal;

-- 查看视图
SELECT * FROM emp_view03;

MySQL管理

MySQL用户

用户存储在mysql.user表中，核心字段：

host：允许登录的IP（localhost=本地，%=所有IP）
user：用户名
authentication_string：加密后的密码

用户操作语法

sql

-- 1. 创建用户
CREATE USER '用户名'@'登录位置' IDENTIFIED BY '密码';
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'; -- 本地登录
CREATE USER 'smith'@'192.168.1.%'; -- 网段登录
CREATE USER 'jack'; -- 默认%（所有IP）

-- 2. 删除用户
DROP USER '用户名'@'登录位置';
DROP USER 'shunping'@'localhost';
DROP USER 'jack'; -- 等价于DROP USER 'jack'@'%'

-- 3. 修改密码
SET PASSWORD FOR '用户名'@'登录位置' = PASSWORD('新密码');
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');

-- 4. 授权
GRANT 权限列表 ON 库.对象 TO '用户名'@'登录位置';
GRANT SELECT, INSERT ON testdb.news TO 'shunping'@'localhost'; -- 表级权限
GRANT ALL ON *.* TO 'admin'@'%'; -- 所有库表权限

-- 5. 回收权限
REVOKE 权限列表 ON 库.对象 FROM '用户名'@'登录位置';
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost';

-- 6. 权限生效
FLUSH PRIVILEGES;

MySQL常用权限

权限	说明
ALL	所有权限（除GRANT OPTION）
SELECT/INSERT/UPDATE/DELETE	增删改查权限
CREATE/DROP	创建/删除库表权限
ALTER	修改表结构权限
GRANT OPTION	授权权限

本章作业

选择题

以下哪条语句是错误的？[D] A. SELECT empno,ename name,sal salary FROM emp; B. SELECT empno,ename name,sal AS salary FROM emp; C. SELECT ename, sal*12 AS "Annual Salary" FROM emp; D. SELECT ename,sal*12 Annual Salary FROM emp;
显示补助非空的所有雇员信息？[B] A. SELECT ename,sal,comm FROM emp WHERE comm<>null; B. SELECT ename,sal,comm FROM emp WHERE comm IS NOT null; C. SELECT ename,sal,comm FROM emp WHERE comm<>0;
以下哪条语句是错误的？[C] A. SELECT ename, sal salary FROM emp ORDER BY sal; B. SELECT ename, sal salary FROM emp ORDER BY salary; C. SELECT ename,sal salary FROM emp ORDER BY 3;

基础查询作业

sql

-- 1. 查看DEPT表和EMP表结构
DESC dept;
DESC emp;

-- 2. 显示所有部门名称
SELECT dname FROM dept;

-- 3. 显示所有雇员名及其全年收入（13月工资+补助）
SELECT ename, (sal + IFNULL(comm, 0))*13 AS "年收入" FROM emp;

-- 4. 显示工资超过2850的雇员姓名和工资
SELECT ename, sal FROM emp WHERE sal > 2850;

-- 5. 显示工资不在1500到2850之间的雇员名及工资
SELECT ename, sal FROM emp WHERE sal NOT BETWEEN 1500 AND 2850;

-- 6. 显示编号为7566的雇员姓名及所在部门编号
SELECT ename, deptno FROM emp WHERE empno = 7566;

-- 7. 显示部门10和30中工资超过1500的雇员名及工资
SELECT ename, sal FROM emp WHERE deptno IN (10,30) AND sal > 1500;

-- 8. 显示无管理者的雇员名及岗位
SELECT ename, job FROM emp WHERE mgr IS NULL;

S01-24 JavaSE-数据库-MySQL ​

核心问题 ​

解决之道 ​

MySQL安装与配置 ​

下载地址 ​

命令行连接MySQL ​

图形化工具 ​

Navicat ​

SQLyog ​

数据库三层结构 ​

数据存储方式 ​

SQL语句分类 ​

创建数据库 ​

语法 ​

练习案例 ​

查看与删除数据库 ​

查看数据库 ​

删除数据库 ​

备份与恢复数据库 ​

备份（DOS命令行执行） ​

恢复（MySQL命令行执行） ​

练习案例 ​

创建表 ​

语法 ​

练习案例（创建用户表） ​

MySQL常用数据类型 ​

数值类型 ​

字符串类型 ​

日期时间类型 ​

修改表 ​

核心语法 ​

练习案例 ​

DML语句（增删改） ​

INSERT（添加数据） ​

语法 ​

练习案例 ​

关键细节 ​

UPDATE（修改数据） ​

语法 ​

练习案例 ​

DELETE（删除数据） ​

语法 ​

练习案例 ​

关键细节 ​

DQL语句（查询） ​

基本语法 ​

基础查询练习 ​

WHERE条件查询 ​

常用运算符 ​

练习案例 ​

排序查询（ORDER BY） ​

分页查询（LIMIT） ​

语法 ​

练习案例 ​

合计/统计函数 ​

常用函数 ​

练习案例 ​

分组查询（GROUP BY + HAVING） ​

语法 ​

练习案例 ​

常用函数 ​

字符串函数 ​

练习案例 ​

数学函数 ​

练习案例 ​

日期时间函数 ​

练习案例 ​

加密与系统函数 ​

练习案例 ​

流程控制函数 ​

练习案例 ​

多表查询 ​

基本概念 ​

练习案例 ​

自连接（同表连接） ​

子查询（嵌套查询） ​

单行子查询（返回1行数据） ​

多行子查询（返回多行数据，用IN） ​

子查询作为临时表 ​

ALL/ANY操作符 ​

S01-24 JavaSE-数据库-MySQL

核心问题

解决之道

MySQL安装与配置

下载地址

命令行连接MySQL

图形化工具

Navicat

SQLyog

数据库三层结构

数据存储方式

SQL语句分类

创建数据库

语法

练习案例

查看与删除数据库

查看数据库

删除数据库

备份与恢复数据库

备份（DOS命令行执行）

恢复（MySQL命令行执行）

练习案例

创建表

语法

练习案例（创建用户表）

MySQL常用数据类型

数值类型

字符串类型

日期时间类型

修改表

核心语法

练习案例

DML语句（增删改）

INSERT（添加数据）

语法

练习案例

关键细节

UPDATE（修改数据）

语法

练习案例

DELETE（删除数据）

语法

练习案例

关键细节

DQL语句（查询）

基本语法

基础查询练习

WHERE条件查询

常用运算符

练习案例

排序查询（ORDER BY）

分页查询（LIMIT）

语法

练习案例

合计/统计函数

常用函数

练习案例

分组查询（GROUP BY + HAVING）

语法

练习案例

常用函数

字符串函数

练习案例

数学函数

练习案例

日期时间函数

练习案例

加密与系统函数

练习案例

流程控制函数

练习案例

多表查询

基本概念

练习案例

自连接（同表连接）

子查询（嵌套查询）

单行子查询（返回1行数据）

多行子查询（返回多行数据，用IN）

子查询作为临时表

ALL/ANY操作符