数据库和 SQL 和索引事务和 Java数据库编程（JDBC）

一、初识数据库

什么是数据库？和数据结构有什么关系？

数据库是“一类软件”，能够针对数据进行管理。数据结构，也是针对数据进行管理。所以，数据库其实就是一个“基于数据结构”实现出来的软件。

有哪些常用数据库？

数据库分为关系型数据库和非关系型数据库。

关系型数据库：对于存储的数据，格式上有严格的要求。

MySQL（免费）

Oracle（最好的数据库）

SQL Server（学校经常使用）

SQLite（非常轻，嵌入在安卓系统内部，只有一个可执行文件，大小是500多kb）

非关系型数据库：存储方式比较灵活，相比关系型数据库功能更少，但性能更快，也更好的适应分布式环境。

Redis、MongoDB、HBase

选取MySQL进行学习：MySQL是客户端服务器结构的软件。

MySQL/Oracle/SQL Server是软件，SQL是运行在软件上的编程语言（结构化的查询语言）。

选取了MySQL进行学习。

MySQL是客户端服务器结构的软件。下图左边是客户端，右边是服务器

客户端（client）：主动发送数据的一方。
服务器（server）：被动接收数据的一方。
客户端给服务器发送的数据，叫做请求（request）。
服务器给客户端返回的数据，叫做响应（response）。

正常情况下，一个服务器可以同时给多个客户端提供服务。多个客户端可以同时给一个服务器发送请求，服务器进行对应的响应。特殊情况下，一个服务器只给特定的一个客户端提供服务，这种情况一般出现在分布式系统，各个节点之间的通信。

客户端和服务器可以安装在不同主机上，也可以安装在同一台主机上。无论是不是在同一个主机上，客户端和服务器之间都是通过网络进行通信的。当客户端和服务器安装在同一台主机上，通过电脑上的环回网卡，可以自己发送自己接收，所以不联网也可以进行通信。

服务器是存储数据的本体，数据存储在主机的硬盘中

在客户端上输入SQL语句，服务器返回执行结果。

客户端是和用户交互的部分，服务器是存储数据的本体。数据存储在主机的硬盘中，数据库是在操作硬盘。硬盘属于外存。所以数据库存储的数据，存储空间大，且能持久化保存。

mysql默认的用户名是root，localhost是主机名，表示你自己当前的电脑

内存和外存的区别：

内存读写数据的速度快，外存读写速度慢（能差三四个数量级，几千~几万倍）
内存存储空间小，外存存储空间大
内存比外存贵
内存的数据“易失”，断电后数据就会丢失；外存的数据是“持久的”，断电之后，数据还在。

SQL是通过数据库的 SQL解析执行引擎来执行的，MySQL的存储引擎是 InnoDB

MySQL中有多个引擎，如：SQL解析执行引擎，存储引擎

SQL是通过 数据库的 SQL解析执行引擎来执行的，这里涉及到一些优化。执行引擎会自动评估，哪种方案成本最低，速度最快。具体这次查询，走不走索引，怎么走的，都不好预期。

存储引擎，实现了数据库如何在硬盘上组织数据，MySQL的存储引擎是 InnoDB

二、SQL

接下来我们来学习SQL这个编程语言。

一个数据库服务器上，可以有多个数据库（这里的数据库指逻辑上用来存储数据的集合，不是软件），一个数据库里，可以有多个数据表。表中，每一行是一条数据，称为记录（record），每一列是一个字段（field）。对于关系型数据库，要求表中每一列的数目和列中的类型要保持一致。

字符集：一般创建数据库的时候要指定字符集。对于GBK和unicode编码，汉字占2个字节（0~65535）。对于utf-8/utf8/UTF-8/UTF8，汉字占3个字节

1、关于数据库的操作

create database if not exists 数据库名 charset utf8;
show databases;
use 数据库名;
drop database 数据库名;

创建数据库

（1）create database xxx charset utf8;

（2）create database if not exists xxx charset utf8;

下面进行详细介绍：

create database xxx charset utf8;

这是创建一个数据库。

如：创建一个数据库test，字符编码是utf8

create database create charset utf-8;

数据库名不能是sql中的关键字，会报错。

create database `create` charset utf-8;

如果无可避免的要使用到数据库中的关键词，可以将关键词放在``符号中间，但最好不要使用数据库关键词。

create database if not exists xxx charset utf8;

加了 if not exists 表示：如果存在这个数据库，就不创建，不存在才创建。

当这个数据库已经存在时，会报一个警告（warning），不是错误（error），不会影响下面程序的继续执行，当一次性运行很多SQL语句时很有用。

如：又创建一个数据库test，该数据库已经存在，就不会在创建。

我们可以通过 show warnings; 来查看警告详情。

show warnings;

如果不加 if not exists，当此数据库已经存在时，就会报错（error），后续SQL语句直接无法执行

查看所有数据库

show databases;

show databases;

选中指定的数据库

use xxx;

use xxx;

由于一个mysql服务器上，数据库有多个，要对数据库进行操作，首先要确定是针对哪个数据库进行的，所以要先选定数据库。后续操作都是针对这个数据库进行的。

如：选中数据库test

删除数据库

drop database xxx;

drop database xxx;

如：删除数据库test

2、关于数据表的操作

create table if not exists 表名(列名 类型,列名 类型, 列名 类型......);
show tables;
desc 表名;
drop table 表名;

数据库中的表，每一列的数据都是有类型的。

下面介绍一下 mysql 的数据类型，标红为常用数据类型

数值类型

数据类型	大小	说明	对应java类型
bit(M)	M指定位数，默认值是1	二进制数，M的范围是1~64，存储数据的范围是0~2^M-1，M为1时，存储数据为0和1，一共2个	M为1时，对应Boolean
tinyint	1字节		Byte
smallint	2字节		Short
int	4字节		Integer
bigint	8字节		Long
float(M,D)	4字节	单精度浮点数，M表示有效数字的位数 (第一个非0数字开始，到最后一个数字的位数，就是有效数字的位数，如：12.34、0.001234 都有4位有效数字，3.00、3.0000分别有3位和5位有效数字)。 D表示小数点后保留几位，不适合保存精确的数据是 IEEE 754 标准	Float
double(M,D)	8字节	双精度浮点数，M表示有效数字的位数(第一个非0数字开始，到最后一个数字的位数，就是有效数字的位数，如：12.34、0.001234 都有4位有效数字，3.00、3.0000分别有3位和5位有效数字)。 D表示小数点后保留几位，不适合保存精确的数据	Double
decimal(M,D)	M/D最大值+2	精确表示浮点数，牺牲了存储空间运算速度，换来更精确的表示方式	BigDecimal
numeric(M,D)	M/D最大值+2	和decimal一样	BigDecimal

字符串类型

数据类型	大小	说明	对应java类型
varchar(size)	0~65535字节	最常用的表示字符串的类型，带有一个参数，约定了存储的最大空间。如：varchar(128)表示这个列最多存128个字符(不是字节)。当然，并不是你写了128就会真分配这么多，会动态适应，避免空间的浪费，且内存最大不会超过你分配的128个字符所以，我们最好根据实际需求，设置个适合的长度	String
text	0~65535字节 (约64KB) 64K=2^6*2^10 =2^16=65536	适合于更长的字符串（很少见）假如一篇文章1w字，utf8编码的话，就是3w字节 1B=1/1024KB，3wB约等于30KB，64KB足够用了	String
mediumtext	0~16777215字节	适合于更长的字符串（很少见）	String
blob	0~65535字节	主要存二进制数据，像word .docx excel .xlsx 是二进制存储的用记事本打开发现根本看不懂，是二进制	byte[]

日期类型

数据类型

大小

说明

对应java类型

datetime

8字节

范围从1000到9999年，

不会进行时区的检索及转换

java.util.Date、java.sql.Timestamp

timestamp

4字节

(42亿9千万)

范围从1970到2038年，

自动检索当前时区并进行转换

java.util.Date、java.sql.Timestamp

时间戳：以 1970年1月1日0时0分0秒，为基准时间，计算当前时间和基准时间的秒数/毫秒数/微秒数之差，这个很大的整数就是时间戳。

创建表

（1）create table 表名(列名类型,列名类型, 列名类型......);

（2）create table if not exists 表名(列名类型,列名类型, 列名类型......);

下面进行详细介绍：

如：在数据库test中创建一个学生表

use test;
create table student(id int,name varchar(20));
create table if not exists student(id int,name varchar(20));

创建表前，要先选中数据库。

同一个数据库中，不能有两个表，名字相同。即使表的列名或列的数量不同也不可以，表名相同就不可以存在。（所以，再次在数据库text中创建student表时才会报警告）。

另外，varchar(20)这个类型，此处的单位是字符，不是字节，表示这个列最多可存20个字符，字节数根据不同的编码而不同。

查看指定数据库下的所有表

show tables;

show tables;

查看指定表的结构

desc 表名;

desc student;

Field：字段，student表中有id和name两个字段，就是2个列名

Type：类型，列名对应的类型。int(11)，11表示显示的宽度，不影响存储；varchar(20)，20表示最大的长度是20个字符。

Null：是否允许为空，YES表示这个字段允许为空

Key：和列的约束有关，主键外键等啥的

Default：默认值，这两列默认值是NULL

Extra：额外的描述

删除表

drop table 表名;

drop table student;

3、增（insert into 表名）

insert into 表名 values(列值，列值，列值......);
insert into 表名 (列名,列名) values(列值,列值);
insert into 表名 values(列值，列值......),(列值，列值......),(列值，列值......)......;

新增一行完整的数据

insert into 表名 values(列值，列值，列值......);

values后面()中的内容，个数和类型都要和表的结构匹配。

比如，在学生表中新增2个学生

insert into student values(1,"zhangsan");
insert into student values(2,'lisi');

在SQL中，单引号' '、双引号" "都可以表示字符串，因为SQL没有字符类型，只有字符串类型。

指定列新增一行数据

insert into 表名 (列名,列名) values(列值,列值);

此时，未被指定的列，就是默认值。如果指定多个列，就使用逗号,进行分割。

比如，在学生表中指定 id 这一列新增一行数据

insert into student (id) values(3);

一次新增多行数据

insert into 表名 values(列值，列值......),(列值，列值......),(列值，列值......)......;

一次新增多行数据，比分多次，每次新增一行记录，速度要快。

原因：

1、数据库是客户端服务器结构的软件，客户端和服务器之间通过网络进行通信，网络请求和响应都有时间开销。一次肯定比多次时间开销低。

2、数据是存储在硬盘上的，而硬盘读写速度很慢。读写一次肯定比读写多次时间开销低。

3、mysql是关系型数据库，每次进行一个sql操作，内部都会开启一个事务，开启事务也会花费时间。只开启一个事务肯定比开启多个事务时间开销低。

比如，在学生表中一次新增多行数据

insert into student values(4,'张三'),(5,'李四'),(6,'王五');

4、查（select...from 表名）

select * from 表名;
select 列名,列名... from 表名;
select 列名,表达式...from 表名;
select 列名 as 别名,表达式 as 别名...from 表名;
select distinct 列名,列名......from 表名;
select * from 表名 order by 列名 asc/desc;
select 列名,表达式 as 别名 from 表名 order by 表达式/别名 asc/desc;
select * from 表名 order by 列名 asc/desc,列名 asc/desc;
select 列名 from 表名 where 条件;
select * from 表名 limit n;
select * from 表名 limit n offset m;
select * from 表名 limit m,n;

排序 order by，条件 where，限制 limit 不仅可以搭配 select 还可以搭配 update 和 delete

全列查询，查询表里的所有列

select * from 表名;

比如，查询学生表中所有列

select * from student;

指定列查询

select 列名,列名... from 表名;

student_score中，chinese的类型是decimal(3,1)。但是放90.23也不会报错，这是因为SQL是弱类型语言，对类型本身检查并不严格，会尽可能的进行“隐式类型转换”，此处进行了四舍五入。90.23变成90.2，符合decimal(3,1)类型了。

比如，查询学生成绩表中的name和chinese列

select name,chinese from student_name;

查询列为“表达式”

select 列名,表达式...from 表名;

“表达式”指对列进行的一些运算。

比如，查询学生成绩表中的name列和所有同学的语文成绩都加10的结果（chinese+10）

select name,chinese+10 from student_name;

我们发现，108.0和100.2不满足类型decimal(3,1)，那为什么不报错？

因为进行表达式查询的时候，查询的结果是一个“临时表”，临时表的类型和原来的表不完全一样，会尽可能的把数据表示进去。所以，不会报错。

而且临时表不会写入硬盘，只是临时的，表达式查询并不会改变硬盘中原来表中的内容。

给查询的列指定别名

select 列名 as 别名,表达式 as 别名...from 表名;

这里as可以省略，但不建议

比如，给学生成绩表中的name起个别名student_name，或者给chinese+maths起个别名total

select name as student_name from student_score;
select name,chinese+maths as total from student_score;

查询的时候，针对列去重

select distinct 列名,列名......from 表名;

distinct后面可以跟一个列名，也可以跟多个列名。

跟多个列名时，要跟的列的数据都相同，才算“重复”，才能去重。

比如，去重查询学生成绩表中的maths，或去重查询学生成绩表中的chinese和maths

select distinct maths from student_score;
select distinct chinese,maths from student_score;

针对查询结果进行排序

（1）select * from 表名 order by 列名 asc/desc;

（2）select 列名,表达式 as 别名 from 表名 order by 表达式/别名 asc/desc;

（3）select * from 表名 order by 列名 asc/desc,列名 asc/desc;

asc：升序，desc：降序，不加默认是升序

1、如果排序的列中，有值是NULL，那么NULL就是最小值

2、没有order by 排序，查询结果的先后顺序是随意的。通过 order by 排序查询，排序的列值相同，那么列值相同的这几行记录先后顺序也是随机的。

3、排序除了可以针对列名，也可以针对表达式或别名来进行排序。

4、指定多个列来进行排序，先以第一个列进行排序，若列值相同，再以第二个列进行排序。每个列名后面都可以加 asc或desc，指定是升序还是降序。

如：按chinese进行排序并全列查询

或按chinese+maths进行排序并指定列查询

或指定多个列进行排序并查询

select * from student_score order by chinese;
select * from student_score order by chinese desc;

select name,chinese+maths as score from student_score order by score;

select * from student_score order by maths,chinese desc;

条件查询，针对查询结果，按照一定条件进行筛选

select 列名 from 表名 where 条件;

where条件不仅可是搭配 select，还可以搭配 update 和 delete

条件可以是：

列名和数字/列名比较，表达式和数字/列名比较，等等。

但不能是：

别名和数字/列名比较，别名不能出现在where条件里。

比较运算符和逻辑运算符：

= ：比较相等，NULL不安全，NULL=NULL的结果是NULL，NULL会被当成false。NULL和任何值进行运算，结果都是NULL，都是false

<=>：比较相等，NULL安全，NULL<=>NULL的结果是true

between a and b：区间 [a,b]

in(option,......)：如果是option中的任意一个返回，不在里面就不返回

is null：是null，只能比较一个列

is not null：不是null，只能比较一个列

like：模糊匹配，%表示任意0个或多个字符，_表示任意1个字符

and，or，not：逻辑运算符

如：查询学生成绩表中chinese>90或maths>70 的记录

查询学生成绩表中 chinese>maths 的记录

查询学生成绩表中 chinese+maths>140 的记录

查询学生成绩表中 chinese 在[80,90] 之间的记录

查询学生成绩表中 chinese 是 58或59或98或99 的记录

查询学生成绩表中模糊匹配 % 或 _ 的记录

select * from student_score where chinese>90 or maths>70;

select * from student_score where chinese>maths;

select name,chinese+maths as score from student_score where score>140;
select name,chinese+maths as score from student_score where chinese+maths>140;

为什么别名不能出现在where条件中呢？

因为MySQL进行条件查询时，是先针对每一行记录，计算条件，按照条件进行筛选（此时并不认识别名）。满足条件的记录才会被保留，并取出对应的列，这时才会计算表达式生成别名。

select * from student_score where chinese between 80 and 90;

select * from student_score where chinese in(58,59,98,99);

select * from student_score where name like 'y_';
select * from student_score where name like '%y%';
select * from student_score where name like '_y_';

通过limit限制查询结果的数量

（1）select * from 表名 limit n;

（2）select * from 表名 limit n offset m;

（3）select * from 表名 limit m,n;

n是这次查询结果的最大数量

limit 搭配 offset 就可以指定从第几条开始筛选了（imit 是从1开始，offset的值是从0开始的）

limit n：查到的是前 n 条的记录

limit n offset m：查到的是从 m条开始的 n条记录

limit m,n：查到的是从 m条开始的 n条记录

如：查询学生成绩表中的记录，最多3条

查询从第0条数据开始的 3条记录

查询从第3条数据开始的 3条记录

select * from student_score limit 3;

select * from student_score limit 3 offset 0;
select * from student_score limit 3 offset 3;
select * from student_score limit 0,3;
select * from student_score limit 3,3;

5、改（update 表名）

update 表名 set 列名 = 列值 where 条件;
update 表名 set 列名 = 表达式;
update 表名 set 列名 = 列值,列名 = 列值......where 条件;
update 表名 set 列名 = 列值  order by 列名/表达式 limit n;

排序 order by，条件 where，限制 limit 不仅可以搭配 update 还可以搭配 select 和 delete

update后面是表名，然后是设置哪些列（修改列值），然后通过条件确定哪些行。也就是：update 要修改某个表里的 某些行中的某些列的值

update修改的是硬盘中的数据，修改之后会持久生效。

修改列值

update 表名 set 列名 = 列值 where 条件;

如：将学生成绩表中，maths = 69的记录的 chinese成绩设置成 80

update student_score set chinese = 80 where maths = 69;

通过表达式修改列值

update 表名 set 列名 = 表达式;

如：将学生成绩表中，chinese成绩都+5

update student_score set chinese = chinese+5;

同时修改多个列

update 表名 set 列名 = 列值,列名 = 列值......where 条件;

多个列之间使用逗号(,)隔开

update student_score set chinese = 92,maths = 64 where name like "y_";

搭配 order by/limit 等子句

update 表名 set 列名 = 列值 order by 列名/表达式 limit n;

如：将学生成绩表中，总成绩倒数5名的同学，语文成绩设置成 10

首先算出 chinese+maths的总和，然后通过 order by 根据总成绩进行升序，通过 limit 限制修改数量为5，最后把 chinese 设置成10

update student_score set chinese = 10 order by chinese+maths limit 5;

（1）向学生成绩表中增加数据

（2）查询学生成绩表中总成绩倒数5名的记录

（3）将总成绩倒数5名的同学的chinese设置成10

6、删（delete from 表名）

delete from 表名;
delete from 表名 where 条件;
delete from 表名 order by 列名/表达式 limit n;

排序 order by，条件 where，限制 limit 不仅可以搭配 delete 还可以搭配 update 和 select

delete 修改的是硬盘中的数据，修改之后会持久生效。

删除表中所有的数据

delete from 表名;

没写条件，删除表中所有的数据

搭配where

delete from 表名 where 条件;

如：删除学生成绩表中 name="ran" 的记录

delete from student_score where name = "ran";

搭配 order by/limit 等子句

delete from 表名 order by 列名/表达式 limit n;

如：删除学生成绩表中总成绩最小的2名学生的记录

delete from student_score order by chinese+maths limit 2;

7、数据库约束

约束：让数据库帮助程序员更好的检查数据是否正确（校验数据）！

（1）not null：不允许为空，必须有数据

（2）unique：唯一，不能重复

（3）primary key：主键，not null + unique的结合，不为空且不能重复

（4）default：指定默认值，当没给此列指定值时，列值就是默认值

（5）foreign key：外键，多个表之间的相互约束，把多张表给关联起来。

not null

加了 not null 的列，不能为空。

如：

create table student (id int not null,name varchar(15));
insert into student values(null,'wqy');//报错了
insert into student values(1,null);//可以

unique

加了 unique 的列，数据不能重复。

如：

create table student (id int unique,name varchar(15));
insert into student values(1,'wqy');
insert into student values(1,null);//报错

primary key

主键，not null + unique。加了 primary key 的列，既不能为空，又不能重复

主键有先查询的过程，mysql会默认给主键的列自动添加索引，来提高查询速度。

一个表里，只能有一个主键，不能有多个。

虽然主键只能有一个，但是mysql允许把多个列放到一起共同作为一个主键（联合主键）

可以使用mysql自带的 “自增主键” (auto_increment)作为主键的值。

如：

create table student(id int primary key,name varchar(15));
insert into student values(null,null);//报错
insert into student values(1,null);
insert into student values(1,'wqy');//报错

自增主键：auto_increment

每次增加的时候，会自动找到上一条记录的 id，在这个基础上，进行自增。

这里的null 是交给mysql自增的意思，不是说数据是null

如：

create table student(id int primary key anto_increment,name varchar(15));
insert into student values(null,'wqy');
insert into student values(null,'dudu');
insert into student values(null,'zhou');
insert into student values(100,'ran');
insert into student values(null,'yuyu');

default

指定默认值，当没给此列指定值时，列值就是默认值。

如：

create table student(id int,name varchar(15) default '无名氏');
insert into student values(1,'wqy');
insert into student values(2,null);
insert into student(id) values(3);

foreign key

外键，多个表之间的相互约束，把多张表给关联起来。

想在子表中使用外键，要先有父表，才能创建子表，使用外键。

要想创建外键，要求父表的对应的列，得有 primary key 或 unique 约束(索引+唯一)

外键约束，对 insert into ， update ，delete from 都有作用。

相互约束：

1、被外键约束的列，子表中此列的值必须在父表列值中存在

2、被外键约束的列，当父表中此列的某值被子表引用，那么父表中含有此列值的该行记录不能被删除

如：有一个学生表(student) 和一个班级表(class) ，要求学生表中学生的班级必须在班级表中存在

分析可知：

学生表和班级表中的数据有了相互约束，1、学生表中的班级必须在班级表中存在，2、班级表中被学生表使用了的班级不能被删除

create table class (id int primary key,name varchar(15));
create table student (id int primary key,name varchar(15),classId int,
foreign key (classId) references class(id));

外键约束对 insert into 有作用：

学生表中无法新增班级在班级表中不存在的记录。

insert into student values (1,'wqy',1);
insert into student values (2,'dudu',1);
insert into student values (3,'haha',4);

外键约束对 update 有作用：

学生表中无法将班级修改成班级表中不存在的班级。

班级表中无法将已在学生表中引用的班级修改成别的。

外键约束对 delete from 有作用：

班级表中无法删除已在学生表中引用的班级。

若班级表中的某些班级已被学生表引用，那么也不能删除班级表或班级表中的所有数据。

要想成功删除班级表：1、先删除学生表（子表），再删除班级表（父表）2、删库

8、更复杂的查询

把查询的结果替代values 新增到另一个表中

要求：查询结果的 临时表的 列数和列的类型，都要和另一个表匹配，才能新增成功

如：把student表中全列查询的结果，新增到student2表中

insert into student2 select * from student;

聚合查询

本质上是针对行与行之间进行运算

进行聚合查询，需要搭配聚合函数【count()，sum()，avg()，max()，min()】

以下函数都是针对某个列的所有行进行运算的：

count()：求查询到的结果的数量
sum()：求这个列所有值的 总和，这个列必须得是数字
avg()：求这个列所有值的 平均值，这个列必须得是数字
max()：求这个列所有值的 最大值，这个列必须得是数字
min()：求这个列所有值的 最小值，这个列必须得是数字

count()

count()：括号里可以是 *，列名，表达式

是 * 时，相当于先进行 select *，然后对查询的结果进行count运算，计算出行数。此处的计算，会把null行也算进去

是列名时，也是计算行数，但当此列为null时，不会算进去

是 表达式 时，带有列名的表达式，不会把null算进去；常量表达式，相当于 *，会把null算进去

如：

select count(*) from score;

select count(name) from score;
select count(chinese) from score;

select count(chinese+math) from score;
select count(name+1) from score;
select count(chinese+1) from score;
select count(1) from score;
select count(2) from score;

sum() ，avg()，max()，min()

要求这个列必须得是数字

括号里可以是列名，表达式

是列名时，当此列为null时，不会算进去

是 表达式 时，带有列名的表达式，不会把null算进去；常量表达式，会把null算进去

sum(常量)相当于常量×count(*)，
avg(常量)相当于常量×count(*)÷count(*)，结果就是该常量
max(常量)，min(常量)，结果就是该常量

select sum(chinese) from score;

select sum(chinese+math) from score;
select sum(chinese+1) from score;

select sum(1) from score;
select sum(2) from score;

select avg(chinese) from score;

select avg(chinese+math) from score;
select avg(chinese+1) from score;

select avg(1) from score;
select avg(2) from score;

select max(chinese),min(chinese) from score;

select max(chinese+math),min(chinese+math) from score;
select max(chinese+1),min(chinese+1) from score;

select max(1),min(1) from score;
select max(2),min(2) from score;

使用 group by 进行分组查询

分组查询一般搭配聚合函数使用

分组查询也可以指定排序，条件，和限制。

指定条件，有两种情况：

1、分组之前，指定条件，即先筛选，再分组，where

2、分组之后，指定条件，即先分组，在筛选，having

当然我们也可以分组之前和之后都指定条件。

执行过程：from，where，group by，having，select，order by，limit

如：

1、统计每个岗位的平均薪资

2、统计每个岗位的平均薪资并倒序

3、去掉马云的数据后，统计每个岗位的平均薪资

4、统计每个岗位的平均薪资，但是不要平均薪资在10w之上的

5、去掉马云的数据后，统计每个岗位的平均薪资，但是不要平均薪资在10w之上的

6、去掉马云的数据后，统计每个岗位的平均薪资，但是不要平均薪资在10w之上的，并倒序

7、去掉马云的数据后，统计每个岗位的平均薪资，但是不要平均薪资在10w之上的，并倒序，并限制查询结果的最大数量是1

联合查询（多表查询）

多表查询的基本执行过程：笛卡尔积

笛卡尔积：就是把这两个表放到一起进行计算 —— 分别取出第一张表的每一行，和第二张表的每一行，进行配对，得到一个新的记录

笛卡尔积会得到一张更大的表，这个表中，列数是两张表列数之和，行数是两张表行数之积

直接笛卡尔积会得到一些废数据，用来筛选有效数据的条件，就叫做联合查询的连接条件

内连接

内连接：两个表中都有的数据

where......and......

inner join......on......

如：有3张表，学生表，课程表，和成绩表。3表查询并显示学生和他选的课程的成绩

select student.name as 学生姓名,course.name as 课程名称,score as 成绩 
from student,score,course
where student.id = score.student_id and course.id = score.course_id; 

select student.name as 学生姓名,course.name as 课程名称,score as 成绩 
from student 
inner join score on student.id = score.student_id
inner join course on course.id = score.course_id;

外连接

左外连接：会把左表的结果全部列出来，哪怕在右表中没有对应的记录，会使用null填充

右外连接：会把右表的结果全部列出来，哪怕在左表中没有对应的记录，会使用null填充

select name,score from student inner join score on student.id = score.student_id;
select name,score from student left join score on student.id = score.student_id;
select name,score from student right join score on student.id = score.student_id;

自连接

自己和自己笛卡尔积

因为sql中无法针对行和行之间进行比较，于是使用自连接把行转成列

如：查询成绩表中这个学生的操作系统成绩比计算机网络成绩高的

操作系统id是3，计算机网络id是4

select * from score as s1,score as s2
where s1.student_id = s2.student_id
and s1.course_id = 3 and s2.course_id = 4
and s1.score > s2.score;

子查询

子查询：把一个查询作为另一个查询的一部分

慎用，子查询会构造出非常复杂且不易理解的sql，对代码的可读性有严重影响。

单行子查询：返回一行记录的子查询 =

多行子查询：返回多行记录的子查询 in

如：

查询学生 zhou 的同班同学

查询数据结构以及操作系统这两门课程的所有成绩

select name from student 
where class_id = (select class_id from student where name = 'zhou') 
and name != 'zhou';

select * from score 
where course_id in(select id from course where name = '数据结构' or name = '操作系统');

合并查询

本质上就是把两个查询的结果集，合并成一个

合并的前提条件：这俩结果集的列要匹配，即列的类型+列的个数+列的名字

合并查询的关键字：union，union all

union：会去重，把重复的行只保留一行
union all：不去重，可以保留多分

union 和 or 的区别：

or 只能把查询条件合并，查询结果只能来自同一个表
union 能把查询语句合并，查询结果可以来自不同的表

如：

查询 id<3 或者名称是计算机网络的课程

查询 id<3的课程或者 id<3 的学生

select * from course where id < 3 
or
name = '计算机网络';

select * from course where id < 3 
union
select * from course where name = '计算机网络';

select name from course where id < 3
union
select name from student where id < 3;

三、索引和事务

1、索引

show index from 表名;
create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

1、定义：

索引是一种数据结构，相当于“书的目录”，能够快速的查找数据。

2、索引的优缺点：

优点：加快了查找的速度

缺点：

提高了增删改的开销。因为进行增删改时，需要调整已经创建好的索引。
也提高了空间的开销。因为构建索引，需要额外的磁盘(就是硬盘)空间来保存

3、关于索引的创建

某列如果被 primary key （主键）或 unique （唯一）或 foreign key （外键）约束，那么这一列会自动创建索引
如果该表中没有索引，而经常按照某一列来查找，就可以针对这一列创建索引。
创建索引最好是创建表时就创建好，因为一旦表中已经有大量数据了，这时候再进行创建索引，就会占用大量的硬盘IO，花很长时间，会影响数据库的正常使用。删除索引也有可能会占用大量的磁盘IO，也是危险操作。
并不是所有情况都需要加上索引，如果这一列的重复数据非常多，虽然加了索引，但并不能提高查询速度。比如，给性别列加上索引，性别只有固定的几个，重复数据非常多，加上索引并不能提高查询速度

MySQL的存储引擎InnoDB 的索引的数据结构是啥

索引的主要目的是为了加快数据的查找速度，哪些数据结构可以加快查找速度呢？

同学们可能会说，哈希表，因为使用哈希表查找元素，时间复杂度为O(1)。

但是，哈希表并不适合做数据库的索引，因为哈希表只能比较相等，无法进行大于小于这样的范围查询，而数据库中经常会用到范围查询。不是哈希表

同学们可能又会说，二叉搜索树，因为使用二叉搜索树查找元素，时间复杂度为O(n)。

但是，数据库使用的也不是二叉搜索树，因为虽然二叉搜索树可以查起点，查终点，而且树里的元素是有序的，也就是说可以范围查询，但是二叉意味着当元素个数多的时候，树的高度就会比较高，由于数据库的数据存储在主机的硬盘中，查询元素需要读取硬盘，读取硬盘会很慢，树的高度决定了查询时，硬盘读写的次数，树的高度越高，读写硬盘的次数就越多，查的就越慢。不是二叉搜索树

那让树的高度低一点不就可以了吗，同学们可能会想到 N叉搜索树。

N叉搜索树，每个节点上有多个值，同时又有多个分叉。树的高度表示最多的 I/O次数。分叉多了，意味着保存相同元素的时候，树的高度就降低了。查找元素时速度就快了。

N叉搜索树有很多种不同的实现方式，其中的一种典型实现，叫做 B树。

对于B树来说，每个节点上的值可以视为一次磁盘I/O，树的高度表示最多的 I/O次数。虽然比较的次数没怎么减少（一个节点上可能有多个值，一个节点上可能需要比较多次），但是读写硬盘的次数减少了（树的高度降低了）。树的高度决定了查询时，硬盘读写的次数，在相同数量的总元素个数下，每个节点的元素个数越多，分叉越多，那么树的高度就越低，查询所需的磁盘I/O次数就越少。

B树已经比二叉搜索树更适合做数据库的索引了，但是还不够，于是引入了 B+树

B+树是对 B树进一步的改进，B+树 是给索引量身定做的数据结构。B+树也是 N叉搜索树，但又有一些新的特点。

所以， MySQL的存储引擎InnoDB的索引的数据结构是 B+树

如下图，就是一个B+树：

那么， B+树有什么特点呢？

1、B+树是一个 N叉搜索树，每个节点上可能包含N个key，N个key划分出N个区间，最后一个值相当于最大值
2、父元素的key会在子元素中以最大值的方式重复出现，于是叶子节点就包含了所有数据的全集。也就是说，非叶子节点中的所有值最终都会在叶子节点中。
3、会把叶子节点，用类似链表的方式，首尾相连

使用 B+树作为MySQL的索引，有什么好处？

作为一个 N叉搜索树，高度降低了，比较时 磁盘IO的次数就少了（和B树一样），查的速度就快了。
更适合进行范围查询，效率非常高。比如我想查 id>3 and id<10的元素，找到3，找到10，因为叶子节点之间进行了相互连接，很容易就找到了3和10之间的元素。
所有的查询，都是要落在叶子节点上的。无论查询哪个元素，中间比较的次数都差不多，查询操作比较均衡。由于都是查叶子节点，所以磁盘I/O的次数是一样的，大家的查询的速度都是一样的。对比B树，对于B树来说，在根节点或者不深的位置时，磁盘I/O的次数就少，查的就快；位置深时，磁盘I/O的次数就多，查的就慢。
由于所有的key都会在叶子节点中出现，所以，非叶子结点，不需要存表的真实记录（不需要存数据行），只需要存索引列的值（比如存个id），把真实的一行记录放到叶子节点上。
由于非叶子节点只需要存索引列的值，不需要存一整行，这大大降低了非叶子节点占用的空间。那么，非叶子节点就有可能被放进内存进行缓存，这些非叶子节点不在硬盘中，不需要磁盘I/O，进一步降低了硬盘IO的次数，提高了查询速度。

B+树的叶子节点中存的是啥？

有的表，不只有主键索引，别的非主键列，也有索引。那么这个表就会有2个B+树。

主键索引的B+树，非叶子节点存的是索引列的值，叶子节点存的是数据行；

非主键索引的B+树，非叶子节点存的是索引列的值，叶子节点存的是主键的值。

使用主键索引查询，叶子节点里存的就是我们要查的整行数据，因此只需要查一次B+树；

使用非主键索引查询，叶子节点里存的是主键的值，需要先查一遍索引列的B+树，查到主键的值，再查一遍主键列的B+树，查到需要的数据。即：使用主键索引查询只会查一次，而使用非主键索引查询，还需要回表查询。

索引的总结

索引，

索引是为了提高查询速度，提高查询速度，本质上就是在减少磁盘I/O的次数。

树的高度决定了查询时，磁盘I/O的次数。树的高度越低，磁盘I/O的次数越少。

B+树，

非叶子节点只存索引列的值，叶子节点存数据行/主键的值。
叶子节点之间用类似链表的方式相互连接。
所有的查询，都是落在叶子节点上的。
主键索引，查询只会查一次；非主键索引，查询还需要回表查询。

查看索引

show index from 表名;

如：查看学生表的索引

show index from student;

key_name：索引的名称

column_name：加索引的列的名称

主键加在了列名 id 这一列，外键加在了列名 class_id 这一列，自动创建2个索引

创建索引

create index 索引名 on 表名(列名);

如：给学生表 name 这一列添加索引

create index idx_student_name on student(name);

删除索引

drop index 索引名 on 表名;

如：删除学生表中索引名为 idx_student_name 的索引

drop index idx_student_name on student;

2、事务

1、什么是事务？

把多个SQL打包到一起，变成一个整体，要么全都执行完，要么一个都不执行。

2、如何实现要么全部执行完，要么一个不执行呢？

这里的一个都不执行，并不是真的不执行，而是会自动恢复到执行之前的样子，看起来就好像一个都没执行一样。数据库会把执行的每个操作记录下来，如果事务中的某个操作出错，就会把事务中前面的操作进行回滚（回滚：把执行过的操作逆向恢复回去，类似于 ctrl+z）根据之前的操作进行逆操作（如：前面是插入，现在就删除；前面是删除，现在就插入；前面是修改，现在就修改回去）

3、那么有了回滚，是不是就不需要担心删库删表了？

并不是，删库删表还是危险操作。因为记录是需要占据空间的。我们不可能花费比数据占得空间还大的空间来记录数据库中的所有数据是怎么来的。这就本末倒置，得不偿失了。于是，实际上，最多只会把正在执行的几个事务的每个操作保存下来，其他操作并不会记录。所以如果删库或者删表，是不能回滚成功的。
4、事务有哪四大特性？

（1）原子性：原子性是事务最核心的特性，是事务的初心。打包成整体这个操作，就称为“原子性”，原子性是事务最核心的特性
（2）一致性：事务执行前/执行后，都得是数据合法的状态。（比如，转账，不能出现转账过程中出错，钱转丢的情况，转账前后数据都得合法）通过原子性，保证了一致性。
（3）持久性：事务产生的修改，都是会写入硬盘的。修改具有持久性。
（4）隔离性：一个数据库服务器，同时执行多个事务的时候，事务之间的“相互影响程度”。
一个数据库服务器，同时可以给多个客户端提供服务，这多个客户端彼此之间，是“并发执行”的关系，多个客户端可能会同时发起事务。尤其是多个事务在操作同一个数据库的同一个表时，有可能会出现问题。
也就是说，
隔离性越高，意味着事务之间的并发程度越低，执行效率就越慢，但是数据的准确性越高；
隔离性越低，意味着事务之间的并发程度越高，执行效率就越快，但是数据的准确性越低；
MySQL给我们提供了不同的档位，也就是隔离级别，使我们可以控制隔离性的高低。

MySQL提供了哪四个隔离级别，有什么区别呢？

read uncommitted：不做任何限制，并发程度最高，隔离性最低，会产生脏读+不可重复读+幻读问题，但执行速度是最快的
read committed：对写操作加锁，并发程度降低了，隔离性提高了，解决了脏读问题，会产生不可重复读+幻读问题
repeatable read：对写操作和读操作都进行加锁，并发程度又降低了，隔离性又提高了，解决了脏读+不可重复读问题，会产生幻读问题
serializable：严格串行化，并发程度最低（串行执行），隔离性最高，解决了脏读+不可重复读+幻读问题，但执行速度是最慢的

举个例子来说明，

事务A想读一份文件数据，与此同时，事务B想修改同一份文件数据。如果不做任何限制，事务A和事务B就是完全并发执行的。
可能会出现以下问题：
问题1：事务B修改的过程中，事务A去读数据。事务A读完数据走了，事务B把那个数据改了。事务A就读了个有问题的数据，称为“脏读问题”。
如何解决问题1？
对修改操作进行加锁，事务B修改数据时，事务A别去读。事务B修改完，事务A才能去读。
问题2：已经对修改操作进行加锁，事务B修改时，不允许事务A去读。但是，事务A读的过程中，事务B又跑去修改了。导致事务A读数据读到一半，数据突然变了。称为“不可重复读”问题。
如何解决问题2？
对读操作也进行加锁，事务A在读的过程中，不允许事务B再去修改。
问题3：现在已经对读操作和修改操作都进行加锁了，事务B修改数据时，不允许事务A去读；事务A读数据时，也不允许事务B去修改。（同一份文件数据）但是，事务A读的过程中，事务B可以去修改其他文件数据呀（比如新增或删除一个其他文件）。事务A在读的过程中，虽然读的数据是正确的，但是它发现文件的数量变了，一会多一个，一会少一个。两次读到的结果集不同，称为“幻读”问题。
如何解决问题3？
舍弃并发，完全串行化，只要事务A在读数据，事务B就啥都不能干，就等着。

事务的使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）提交:commit;
开启事务之后，中间的这些SQL并不会立即执行，而是得commit之后一起执行，这是为了保证原子性
（4）rollback; 主动进行回滚

四、Java数据库编程

实际开发中，SQL很少是在数据库客户端里手动输入的，而是通过代码，自动执行。那么就需要让其他编程语言来操作数据库服务器。

其他编程语言如何操作数据库服务器呢？

数据库会提供各个语言版本的API（一组类或方法），通过这组API，其他编程语言就可以操作数据库，完成各种增删查改的操作。

像 Oracle，MySQL，SQL Server，SQLite等数据库，都有API，而且不同的数据库，提供的API都是不同的。想要操作不同的数据库服务器，就得学好几套不同的API，学习成本非常大。

能不能把这些不同数据库提供的API统一成一套，只需要学习一套API呢？

Java这个编程语言做到了。

Java如何操作数据库服务器？

通过JDBC（Java Database Connection，Java数据库连接，是一套用来执行SQL语句的Java API），使用同一套API 规范所有数据库的编程操作（JDBC只提供了接口，由各个数据库厂商来进行实现），各个数据库厂商都提供了适应JDBC相关的“驱动包”（相当于API的具体实现）。此时，只要掌握了JDBC这套API，无论操作哪个数据库，操作的代码都是不变的。

Java如何操作数据库服务器的过程如下：

由于JDBC这套API已经成为Java标准库的一部分了（在java.sql或javax.sql包底下），所以Java想要操作数据库服务器，只要安装对应数据库的驱动包就可以了。然后把驱动包（Java中，是jar包）导入到项目中，作为库使用。

Java数据库编程——通过代码，自动执行SQL

1、URL：统一资源地址符，描述了互联网上唯一的一个资源的位置，也就是我们平时所说的“网址”。如：
jdbc:mysql://127.0.0.1:3306/java?characterEncoding=utf8&useSSL=false
jdbc:mysql 代表这个URL是给jdbc中的 mysql 使用的

127.0.0.1 代表 IP地址，这是一个“环回IP”，使用IP地址来确定是哪台主机

3306 代表端口号，使用端口号来确定是主机上的哪个程序

java 代表访问的数据库名

characterEncoding=utf8 描述了请求的字符编码方式

useSSL=false 代表关闭加密功能

2、执行 SQL语句，针对增，删，改，都是使用 executeUpdate()来执行，针对查是使用

executeQuery() 来执行。

代码：

增加（insert）:

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCInsert {
    public static void main(String[] args) throws SQLException {
        //1、创建DataSource数据源，描述 MySQL服务器 在哪
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("123456");
        //2、和数据库建立连接
        Connection connection = dataSource.getConnection();
        //3、构造 SQL 语句
        Scanner scanner = new Scanner(System.in);
        int studentId = scanner.nextInt();
        String studentName = scanner.next();

        String sql = "insert into student values(?,?)";
        //PreparedStatement 背后会做很多事，比如会对 sql语句 进行一些预处理（对语法进行解析），减轻服务器的负担
        PreparedStatement statement = connection.prepareStatement(sql);
        //把studentId里的值替换到 第一个 占位符(?)里
        //把studentName里的值替换到 第二个 占位符里
        //这里占位符是从1开始的，第一个参数是几就代表替换的是第几个占位符
        statement.setInt(1,studentId);
        statement.setString(2,studentName);
        System.out.println("sql:"+statement);
        //4、执行 SQL 语句
        int ret = statement.executeUpdate();
        //5、断开连接，释放资源
        statement.close();
        connection.close();
    }
}

查找（select）：

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCSelect {
    public static void main(String[] args) throws SQLException {
        //1、创建数据源，描述数据库在哪儿
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java?characterEnding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("123456");
        //2、与数据库建立连接
        Connection connection = dataSource.getConnection();
        //3、构造sql语句，对sql语句进行预处理
        String sql = "select * from student";
        PreparedStatement statement = connection.prepareStatement(sql);
        //4、执行sql语句
        ResultSet resultSet = statement.executeQuery();
        //resultSet.next()：移动一下光标，让光标指向下一行，并判断此时是否还有元素，没元素了就返回false
        //初始情况下，光标指在第一行的前面
        //使用 getXX方法获取到每一项，参数就是数据表的列名
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println(id+":"+name);
        }
        //5、断开连接，释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}