# MySQL数据库学习笔记

单表操作

-- 列出所有的数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE study DEFAULT CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE study;

-- ----------------------------------
-- 数据库表的操作
-- 切换数据库
USE study;

-- 创建表
CREATE TABLE student(
    id INT,
    `name` CHAR(10),
    age INT,
    gender CHAR(1)
);

-- 查看所有表
SHOW TABLES;
-- 查看表的结构
DESC student; -- description
-- 删除表
DROP TABLE student;

-- 更改表的结构
-- 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
-- 删除字段
ALTER TABLE student DROP COLUMN address;
-- 修改字段类型/名称
ALTER TABLE student CHANGE address addr CHAR(20);
-- 修改表的名字
ALTER TABLE student RENAME TO stu;

-- 带主键、自增的标准建表语句
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10),
    age INT,
    gender CHAR(1)
);

基础CRUD

-- 查询 * 代表查询所有的列
SELECT * FROM student;

-- 插入数据
-- 主键冲突：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO student(id,`name`,age,gender) VALUES(1,'wangwu',23,'男');
INSERT INTO student VALUES(4,'赵六22',33,'男');
-- 插入部分字段
INSERT INTO student(`name`,age,gender) VALUES('小张11',23,'男');
-- 一次性插入多条
INSERT INTO student(`name`,age,gender) VALUES('小张77',23,'男'),('小王',22,'男');

-- 修改数据
UPDATE student SET age=age+1;
UPDATE student SET age=age+1,name='zhangsan' WHERE id=7;

-- 删除数据
DELETE FROM student; -- 删除表全部数据，不会重置自增主键，可回滚(事务)
DELETE FROM student WHERE age=24;
DELETE FROM student WHERE id IN(1,2);

-- TRUNCATE清空全表，重置自增计数器，无法事务回滚
TRUNCATE TABLE student;

查询详解

-- 指定列查询（开发禁止使用select *）
SELECT id,`name`,age,gender FROM student;

-- 别名、常量列、字段运算
SELECT id,`name`,age AS '年龄','java2403' AS '班级' FROM student;
SELECT id,`name`,(php+java) AS '总成绩' FROM student;

-- 去重 DISTINCT
SELECT DISTINCT address FROM student;

-- 条件查询 WHERE
SELECT * FROM student WHERE `name`='小王';

-- 逻辑运算符 AND OR
SELECT * FROM student WHERE `name`='小王' AND address='青岛';
SELECT * FROM student WHERE `name`='小王' OR address='北京';

-- 比较运算 > < >= <= != <>
SELECT * FROM student WHERE java BETWEEN 70 AND 80; -- 闭区间 >= && <=

-- NULL判断 不能使用 =null
SELECT * FROM student WHERE address IS NULL;
SELECT * FROM student WHERE address IS NOT NULL;

-- 模糊查询 LIKE
-- % 任意0~多个字符；_ 单个任意字符
SELECT * FROM student WHERE `name` LIKE '张%';
SELECT * FROM student WHERE `name` LIKE '张_';
SELECT * FROM student WHERE `name` LIKE '%张%';

聚合函数、排序、分组

-- 聚合函数 sum avg max min count
SELECT SUM(php) AS 'php总成绩' FROM student;
SELECT AVG(php) AS 'php平均值' FROM student;
SELECT MAX(php) AS 'php最大值' FROM student;
SELECT MIN(php) AS 'php最小值' FROM student;
SELECT COUNT(*) AS '总人数' FROM student;
-- COUNT(字段)会忽略NULL值，COUNT(*)不会忽略

-- 排序 ORDER BY 放在SQL末尾
SELECT * FROM student ORDER BY php DESC, java ASC;

-- 分组 GROUP BY
SELECT gender AS '性别',COUNT(*) AS '人数' FROM student GROUP BY gender;
-- 分组后条件使用 HAVING（WHERE作用于原始数据，HAVING作用于分组结果）
SELECT gender AS '性别',COUNT(*) AS '人数'
FROM student GROUP BY gender HAVING COUNT(*)>1;

基础概念说明

1. PRIMARY KEY 主键 等价 UNIQUE + NOT NULL

• 唯一、非空；唯一标识一条记录
• 主键值不可重复、不可为NULL、不建议修改、删除后主键不能复用

1. AUTO_INCREMENT 主键自增，一般搭配INT主键；TRUNCATE会重置自增，DELETE不会重置。
2. CHAR(N) vs VARCHAR(N)

• char(10)：定长，固定占用10字符空间，不足自动补空格；查询速度快，适合固定长度（手机号、性别）
• varchar(10)：变长，占用实际字符长度；节省空间，适合姓名、地址

1. Java与数据库映射关系 类 → 表 属性 → 字段（列） 对象 → 记录（行）

---

多表设计

关系分类

1. 一对多（最常用）：班级 → 学生 多方（学生表）添加外键保存一方主键
2. 多对多：班级 <-> 课程 需要中间关联表，两个外键分别指向两张主表，通常设置联合主键
3. 一对一：用户-用户信息，可合并表，也可分表（大字段拆分）

-- 班级表（一方）
CREATE TABLE banji(
     id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');

-- 学生表（多方，添加外键banji_id）
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    age INT,
    gender CHAR(1),
    banji_id INT,
    FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);

-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);

-- 中间表：班级-课程（多对多）
CREATE TABLE banji_course(
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id,course_id), -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id),
    FOREIGN KEY(course_id) REFERENCES course(id)
);
INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);

外键报错：Cannot add or update a child row 原因：外键的值，在主表主键中不存在

子查询

-- 单行子查询 =
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='Java1812');
-- 多行子查询 IN
SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812');

多表连接查询

笛卡尔积

没有连接条件直接查表，行数=表1行数 × 表2行数，大量无效数据，禁止直接使用

SELECT * FROM student,banji;

等值连接（隐式内连接）

SELECT banji.id,banji.name, COUNT(*)
FROM student,banji
WHERE student.banji_id=banji.id
GROUP BY banji.id;

显式连接语法（推荐）

连接类型	作用
INNER JOIN ... ON 内连接	只查询两张表匹配成功的数据
LEFT JOIN ... ON 左外连接	左表所有数据全部展示，右表无匹配显示NULL
RIGHT JOIN ... ON 右外连接	右表所有数据全部展示，左表无匹配显示NULL

-- 内连接示例
SELECT *
FROM student s
INNER JOIN banji b ON s.banji_id = b.id;

-- 左连接：查询所有学生，对应班级信息，没有班级的学生依然展示
SELECT * FROM student s LEFT JOIN banji b ON s.banji_id = b.id;

重点区分 WHERE 和 ON： 外连接条件写在ON；过滤结果集写在WHERE

实现查询班级人数的方式

 
 -- 子查询实现
SELECT banji_id AS '班级ID',(SELECT `name` FROM banji WHERE banji.id = banji_id) AS '班级名',count(*) AS '总数' FROM student GROUP BY banji_id;

-- 等值查询实现
SELECT banji.id,banji.`name`,COUNT(*) FROM student,banji WHERE student.banji_id = banji.id GROUP BY banji_id;

-- 内链接实现
SELECT b.id,b.`name`,COUNT(*) FROM student AS S INNER JOIN banji AS b ON S.banji_id = B.id GROUP BY banji_id

拓展知识点

分页查询 LIMIT

-- 语法 LIMIT 起始下标,条数  下标从0开始
SELECT * FROM student LIMIT 0,5; -- 第1页，5条
SELECT * FROM student LIMIT 5,5; -- 第2页，5条

约束汇总

• PRIMARY KEY 主键约束
• FOREIGN KEY 外键约束
• UNIQUE 唯一约束（允许一个NULL）
• NOT NULL 非空约束
• DEFAULT 默认值约束

事务

MySQL InnoDB引擎支持事务；MyISAM不支持 四大特性ACID：原子性、一致性、隔离性、持久性

START TRANSACTION;
-- 执行sql
COMMIT;  -- 提交
ROLLBACK; -- 回滚

事务原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存回滚段中，只有当事务中所有SQL语句均正常结束（COMMIT），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（ROLLBACK）。

事务特性

原子性（Atomicity）：事务中所有操作作为一个整体，是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

事务处理并发读的问题**

脏读

A修改数据 最后回滚了，B读到了修改后的数据

读取到另一个事务未提交的数据现象就是脏读

不可重复读

A开启事务 修改数据并提交 B两次读取分别读到了修改前和提交后的数据

前后两次读取的数据不一致的现象就是不可重复读

幻读

针对插入的操作

A查询某条数据没有查到 B插入了这条数据，A在插入显示不成功

四大隔离级别

隔离级别

读未提交

读已提交

可重复读(默认)

串行化

索引

主键默认自带索引；索引提升查询速度，降低增删改速度。

1. 不要使用 SELECT *，按需查询字段
2. WHERE条件字段尽量建立索引，避免模糊查询%xxx开头导致索引失效
3. 禁止不带WHERE的DELETE、UPDATE
4. 尽量少使用多表关联，表越多性能越低
5. NULL判断必须使用 IS NULL / IS NOT NULL

-- 1:主键为32的商品
SELECT * FROM goods WHERE goods_id=32;
-- 2:不属第3栏目的所有商品（category中id为3）
SELECT * FROM goods WHERE cat_id!=3;
-- 3:本店价格高于3000元的商品
SELECT * FROM goods WHERE market_price>3000;
-- 4:本店价格低于或等于100元的商品
SELECT * FROM goods WHERE market_price<=100;
-- 5:取出第4栏目或第11栏目的商品
SELECT * FROM goods WHERE cat_id=4 OR cat_id=11;
SELECT * FROM goods WHERE cat_id IN(4,11);
-- 6:取出100<=价格<=500的商品
SELECT * FROM goods WHERE market_price>=100 AND market_price<=500;
-- BETWEEN AND是能取到开始和结束的值，等价于>= and <=
SELECT * FROM goods WHERE market_price BETWEEN 100 AND 500;
-- 7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
SELECT * FROM goods WHERE cat_id!=3 AND cat_id!=11;
SELECT * FROM goods WHERE cat_id NOT IN(3,11);
-- IS NULL、IS NOT NULL     LIKE、NOT LIKE     IN、NOT IN
-- 8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
SELECT * FROM goods WHERE (market_price>100 AND market_price<300) OR (market_price>4000 AND market_price<5000);
-- 要适当的加括号(括号的优先级比AND和OR优先级高)，不加括号数据也正确，只是巧合，因为AND优先级要高于OR优先级，写出有歧义的语句并不能显出你多厉害
-- 任何时候使用AND和OR操作符时候，都应该加括号明确的分组操作符，不要过分依赖默认求值顺序，及时它确实如你希望的那样。使用括号没有什么坏处，它能消除歧义。
-- select * from goods where () OR ();

-- 9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
SELECT * FROM goods WHERE cat_id=3 AND (market_price<1000 OR market_price>3000) AND click_count>5;

-- 10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
SELECT * FROM goods WHERE cat_id IN(SELECT cat_id FROM category WHERE parent_id=1);
-- 11:取出名字以"诺基亚"开头的商品
-- like 模糊匹配
-- % 通配任意字符
-- _ 通配单一字符
SELECT * FROM goods WHERE goods_name LIKE '诺基亚%';

-- 12:取出名字为"诺基亚nxx"的手机
SELECT * FROM goods WHERE goods_name LIKE '诺基亚n__';
-- 13:取出名字不以"诺基亚"开头的商品
SELECT * FROM goods WHERE goods_name NOT LIKE '诺基亚%';
-- 14:取出第3个栏目下面价格在<1000或者>3000,并且点击量>5 "诺基亚"开头的系列商品
SELECT * FROM goods WHERE cat_id=3 AND (market_price<1000 OR market_price>3000) AND click_count>5
AND goods_name LIKE '诺基亚%';

-- 15:把goods表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',
-- 提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
-- substr(),concat(),trim(),ltrim(),rtrim()
SELECT goods_id,goods_name FROM goods WHERE goods_name LIKE '诺基亚%';
SELECT goods_id,CONCAT('HTC',SUBSTR(goods_name,4)) FROM goods WHERE goods_name LIKE '诺基亚%';




public static void main(String[] args) {
    String goodsName = "诺基亚n85原装充电器";
    String name = "HTC" + goodsName.substring(3);
    System.out.println(name);
}

-- 15:计算指定分类(cat_id=3)下面商品的平均价格，
SELECT AVG(market_price) FROM goods WHERE cat_id=3;

-- 16:组合聚集函数，SELECT可以根据需要包含多个聚集函数
-- goods_count  price_min  price_max price_avg
SELECT COUNT(*) AS goods_count,MIN(market_price) AS price_min,MAX(market_price) AS price_max,
AVG(market_price) AS price_avg FROM goods;

-- order by 与 limit：
-- 1、按照栏目由低到高排序，栏目相同按照价格由高到低排序
SELECT * FROM goods ORDER BY cat_id ASC,market_price DESC;


-- 2、取出价格最高的前三名商品
-- LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
-- LIMIT 接受一个或两个数字参数，参数必须是一个整数常量。
SELECT * FROM goods ORDER BY market_price DESC LIMIT 0,3;
SELECT * FROM goods ORDER BY market_price DESC LIMIT 3;

-- 初始记录行的偏移量是 0(而不是 1)
-- limit offset,rowcount  
-- limit 偏移到那个位置offset，往下数多少个rowcount

-- 3、取出点击量第三名到第五名的商品
SELECT * FROM goods ORDER BY click_count DESC LIMIT 2,3;