[大数据]MySQL葵花宝典

一.数据库基础

1.数据库的操作

1.1显示当前的数据库

show databases；

1.2创建数据库

create database [if not exists] db_name;

1.3使用数据库

use db_name;

1.4删除数据库

drop database [if exists] db_name;

数据库删除后，内部看不到对应的数据库，里面的表和数据全被删除

2.常用数据类型

2.1

数值类型可以指定为无符号（unsigned），表示不取负数。(尽量不适用unsigned)
1字节（bytes）=8bit

2.2字符串类型

2.3日期类型

3.表的操作

需要操作数据库中的表时，先要使用该数据库

use db_name;

3.1查看表

desc tb_name; //查看表名
show create table tb_name; //查看表结构+表编码
show tables；//查看当前数据库一共有多少表

3.2创建表

create table tb_name (
   field1 datatype,
   field2 datatype,
   ..........
);

可以使用comment增加字段说明

示例：

create table stu_test (
   id int,
   name varchar(20) comment '姓名',
   password varchar(50) comment '密码',
   age int,
   sex varchar(1),
   birthday timestamp,
   amout decimal(13,2),
   resume text
);

3.3删除表

drop table [if exists] tb_name;

3.4修改表内容

alter table tb_name add+新列名称+类型;//新增一列
alter table tb_name change+原列名+新列名+新列类型;//修改某一列
alter table tb_name 旧表名 rename 新表名;//修改表名称
alter table tb_name convert to character set 新编码; //修改表编码

二.MySQL表的CRUD

CURD即增加、查询、更新、删除

1、新增（create）

insert [into] tb_name (属性名) value (属性值);

案例：

-- 创建一张学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT,
   sn INT comment '学号',
   name VARCHAR(20) comment '姓名',
   qq_mail VARCHAR(20) comment 'QQ邮箱'
);

1.1单行数据插入+全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

1.2 多行数据+指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id, sn, name) VALUES
 (102, 20001, '曹孟德'),
 (103, 20002, '孙仲谋');

2.查询（retrieve）

语法：

SELECT
 [DISTINCT] {* | {column [, column] ...} 
 [FROM table_name]
 [WHERE ...]
 [ORDER BY column [ASC | DESC], ...]
 LIMIT ...

其中distinct为去重，asc为升序，desc为降序

案例：

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
 id INT,
 name VARCHAR(20),
 chinese DECIMAL(3,1),
 math DECIMAL(3,1),
 english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
 (1,'唐三藏', 67, 98, 56),
 (2,'孙悟空', 87.5, 78, 77),
 (3,'猪悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'刘玄德', 55.5, 85, 45),
 (6,'孙权', 70, 73, 78.5),
 (7,'宋公明', 75, 65, 30);

（1）全行全列查询

select * from tb_name;

（2）查找指定列

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english from exam_result;

（3）查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

（4）别名

SELECT column [AS] alias_name [...] FROM table_name;
-- 结果集中，表头的列名=别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;

as 可以省略，列名与别名中间用空格省略

（5）去重查询：DISTINCT

98分重复了

（6）排序：order by

select 列名 from tb_name order by 列名 [asc/desc];//默认升序

若结果中包含null值，这个值null认为是最小值，比负数还小

针对多个列进行排序：
查看所有学生的姓名，语文成绩，数学成绩，其中按照语文成绩升序，数学成绩降序排序
优先按照语文成绩升序排序，只有两个语文成绩相同时才按照数学成绩降序排序

（7）分页查询 limit

select 属性名称 from tb_name limit n; //查询结果按照前n行输出
select 属性名称 from tb_name limit n offset s;//查询结果按照前n行输出，从第s行输出开始

（8）条件查询：where （不支持别名）

3.修改表数据 update

update tb_name set 列名 = （表达式）;//根据表达式确定修改的行

（1）修改一行一列

update exam_result set math = math + 30 where name = '张三';//把name属性为‘张三’的数学成绩+30

（2）修改一行多列

update exam_result set math = math + 30,english = english + 20 where name = '张三';

（3）修改多行数据（where过滤出来的结果是多行）

//成绩表中所有名中带三的加30分
update exam_result set math = math + 30 where name like '%三%';

（4）全列修改

update 不带where条件;//(重置)

4.删除（delete）

delete from 表名 where 条件;

删除全表数据

delete from exam_result;//行行delete，慢，可加条件，可恢复
truncate table exam_result;//相当于在os中直接把数据变成0，快，不可恢复，不能加条件

三.MySQL进阶

1.数据库约束

1.1约束类型

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

示例：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY, //id属性不为空（设置为主键时可省略not null）
sn INT UNIQUE,//指定sn属性为唯一、不可重复的
name VARCHAR(20) DEFAULT 'unkown',//插入name列为空时，默认值为‘unknown’
qq_mail VARCHAR(20)
);

对于整数类型的主键，常常搭配自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1

1.2外键用于关联其他表的主键或唯一键

foreign key (字段名) references 主表（列）

2.表的设计

三大范式

2.1 一对一

2.2一对多

2.3多对多

3.新增

插入查询结果

insert into tb_name（字段1，字段2，...) select ....

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的
学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

4.查询

4.1聚合查询

（1）聚合函数

1. sum()、avg()、max()、min()数据类型不是数字则没有意义
2. count只会统计非null的个数
3. count(*) 效率低，全表扫描，统计行数
   count（任意值）效果相同，相当于在临时表中创建了一列属性，值都为设置的任意值，统计当前有多少行，速度快

（2）GROUP BY 子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数
中。

select column1, sum(column2), .. from table group by column1,column3;

(3)HAVING
GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

4.2联合查询

//初始化测试数据：
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20),
`desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20), name
varchar(20), qq_mail varchar(20) ,classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

classes表

course表

score表

student表

(1)内连接

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

（2）外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

案例：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

-- “老外学中文”同学 没有考试成绩，也显示出来了
select * from student stu left join score sco on stu.id=sco.student_id;
-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
stu.id,
stu.sn,
stu.NAME,
stu.qq_mail,
sco.score,
sco.course_id,
cou.NAME
FROM
student stu
LEFT JOIN score sco ON stu.id = sco.student_id
LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
stu.id;

（3）自连接
自连接是指在同一张表连接自身进行查询。
案例：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
s1.*
FROM
score s1,
score s2
WHERE
s1.student_id = s2.student_id
AND s1.score < s2.score
AND s1.course_id = 1
AND s2.course_id = 3;
-- 也可以使用join on 语句来进行自连接查询
SELECT
s1.*
FROM
score s1
JOIN score s2 ON s1.student_id = s2.student_id
AND s1.score < s2.score
AND s1.course_id = 1
AND s2.course_id = 3;

以上查询只显示了成绩信息，并且是分布执行的。要显示学生及成绩信息，并在一条语句显示：

SELECT
stu.*,
s1.score Java,
s2.score 计算机原理
FROM
score s1
JOIN score s2 ON s1.student_id = s2.student_id
JOIN student stu ON s1.student_id = stu.id
JOIN course c1 ON s1.course_id = c1.id
JOIN course c2 ON s2.course_id = c2.id
AND s1.score < s2.score
AND c1.NAME = 'Java'
AND c2.NAME = '计算机原理';

（4）子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 单行子查询：返回一行记录的子查询
  查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

• 多行子查询：返回多行记录的子查询
  案例：查询“语文”或“英文”课程的成绩信息
  1. [NOT] IN关键字：

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

可以使用多列包含：

-- 插入重复的分数：score, student_id, course_id列重复
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
-- 查询重复的分数
SELECT
*
FROM
score
WHERE
( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING count(
0 ) > 1 );

2. [NOT] EXISTS关键字：

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

• 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个
  子查询当做一个临时表使用。
  查询所有比“中文系2019级3班”平均分高的成绩信息：

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班';

查询成绩表中，比以上临时表平均分高的成绩

SELECT
*
FROM
score sco,
(
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班'
) tmp
WHERE
sco.score > tmp.score;

（5）合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致。

• union
  该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
  案例：查询id小于3，或者名字为“英文”的课程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

• union all
  该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
  案例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';

四.MySQL索引和事物

1.索引

1.1概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。（具体细节在后续的数据库原理课程讲解）

1.2作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助。

1.3使用场景

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.4使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

show index from 表名;

• 创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

• 删除索引

drop index 索引名 on 表名;

2.事务

2.1为什么要使用事务

准备测试表：

drop table if exists accout;
create table accout(
 id int primary key auto_increment,
 name varchar(20) comment '账户名称',
 money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是四十大盗的账户上就没有了增加的金额。
解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

2.2 事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.3事务的特性

1. 原子性：每个事务被看作一个不可分割的单元。只有事务中所有的任务语句同时成功才能被认为整个事务是成功的。如果事务失败，系统会返回到该事务执行前的状态（回滚）。——核心
2. 一致性：事务执行完成后，都将数据库从一个状态转变成另一个状态，事务不能违背定义在数据库中的任何完整性检测。（事务执行前后都要确保数据的合理性）
3. 隔离性：每个事务在其自己的会话空间发生，和其事务之间相互隔离，互不干扰。
4. 持久性：一个事务执行完成后，这个事务对数据库的所有修改都永久的保存在磁盘上不会丢失。

2.4 使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

说明：rollback即是全部失败，commit即是全部成功。

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

五.JDBC

1.获取数据源

MysqlDataSource dataSource = new MysqlDataSoure();

2.配置数据源属性

dataSource.setURL("jdbc:mysql://127.0.0.1:3306/库名？characterEncoding = utf8&useSSL=false");
dataSource.setUser("root");
dataSource.setPassword("数据库的密码");

3.获取数据库的连接

需要导入java.sql.Connection

Connection connection = dataSource.getConnection();

4.写sql语句

String sql = "insert into user(username,password) values (?,?)";

5.获取sql对象

获取执行sql的Statement对象，mysql中每一个sql的执行就对应着一个Statement对象

PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1,"张三");
statement.setString(2,"111");

statement:用于执行不带参数的简单sql语句
PrepareStatement: 1.用于执行带或不带参数的sql语句 2.sql语句会预编译在数据库系统 3.执行速度快于statement

6.执行这个sql,增删改查调用executeUpdate，返回受影响的行数

int rows = statement.executeUpdate();
System.out.println("插入成功，插入" + rows + "行");

7.关闭资源

connection.close();

8.具体CURD的实现