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

时间：2024-05-04 07:49:25

一、初识数据库

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

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

有哪些常用数据库？

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

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

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是主机名，表示你自己当前的电脑

内存和外存的区别：

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

 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 表名;

相关文章