数据库&SQL

数据库基本概念

数据库DataBase

定义

保存一组数据的仓库就称为数据库
例如
编写一个用户管理系统,我们将用户输入的信息以User对象形式表示,并序列化后保存到某个.obj的文件中,用一个目录来保存所有的用户文件,这个目录也可以称为是一个"数据库".
只是对于文件的维护工作(新建文件,修改现有文件数据,删除文件数据)通过编码来实现.

数据库管理系统(DBMS)

定义

一套可独立运行的软件,用于维护磁盘上的数据.维护性高,性能好,可扩展性强.

常见的数据库
MySQL
MariaDB
ORACLE
DB2
SQLServer

在JAVA项目中与数据库的结合

数据库管理系统中常见的概念

库
是表的集合,将来不同的项目都可以创建一个库,用来保存该项目中所有的表
表
是数据的集合,一组具有相同数据的数据保存在一张表上
行:一条记录,具有表中所有字段的一条实际数据
列:字段,一条记录应当具有的相应属性

库与表的关系

不同的数据要保存在不同的表中,一个项目中所有的表应当存在一个数据库中.而DBMS可以同时管理多个库
DBMS安装后是以服务端的角色运行的,因此我们向操作数据库是以客户端的身份与数据库进行连接,并进行相关操作

SQL

Structured Query Language
SQL语言的特点:
关键字不缺分大小写

好的书写习惯:
关键字全大写,非关键字全小写
字符串内容是区分大小写的

数据类型

在数据库中设计一张表时,要为表中每个字段(列)单独指定数据类型以确保可以保存正确的数据

数字类型

整数类型

• INT类型
  int类型占用4个字节,保存数字的范围±21
  INT(m):m表示整数的长度(位数),类型后面通常要指定长度,不指定时默认为11位.
• BIGINT类型
  bigint类型占用8个字节

浮点类型

• DOUBLE类型
  DOUBLE(M,N):定义DOUBLE类型时,要指明数字的长度和精度
  M:表述数字的长度(位数)
  N:表示小数点后的精度
  M包含N.
  DOUBLE(7,2):可以保存一个7位数字,其中2位数小数.最大值可以保存:99999.99

字符类型

定长字符串

• CHAR类型
  CHAR类型是定长字符串类型
  CHAR(m): m是一个数字，表示长度，单位是字符。m最大值255
  CHAR类型在磁盘中开辟的空间是固定的，根据指定的长度来进行开辟。
  例如:使用UTF-8编码，长度为3，最多可以保存3个字符，此时无论该字段是否保存够了三个字符都要都开3个字符占用的字节空间。
  优点: 在磁盘上开辟的空间是确定的，固定长度带来了更好的检索性能
  缺点: 浪费磁盘空间

变长字符串

• VARCHAR类型
  VARCHAR类型是边长字符串，实际占用磁盘空间的大小由数据决定
  VARCHAR(m): m是一个数字，表示长度，单位是字节，最大值为65535.
  实际占用的磁盘空间由数据决定
  例如:name VARCHAR(50)
  name字段保存的字符最多占用50个字节
  如果name保存字符串’张三’，'张三’在UTF-8编码中占6个字节，该字段值实际在磁盘上就占用6字节
  优点: 磁盘空间没有浪费
  缺点: 每条记录该字段长度不一致，会导致查询性能差一些

TEXT类型

• TEXT(m)
  m是一个数字，表示长度，单位是字符,最大值65535

日期类型

• DATE
  可以保存年，月，日
• TIME
  可以保存时，分，秒
• DATETIME
  可以保存年,月,日,时,分,秒
  格式必须为"yyyy-MM-dd hh:mm:ss"(M表示月，m表示分)
  插入数据时可以忽略时分秒，不指定时默认为0
  插入数据时不可以忽略年月日
• TIMESTAMP
  时间戳，保存UTC时间，最多可以精确到毫秒

SQL语言的分类

DDL:数据定义语言

用于进行数据库对象的操作.数据库对象指:库,表,视图,索引,序列等
对数据库对象进行操作的语言:CREATE,ALTER,DROP

• CREATE DATABASE 数据库名 [CHARSET=字符集]
  新建一个数据库
  指定字符集常用:UTF8,GBK

• SHOW DATABASES
  查看已经创建的数据库

• SHOW CREATE DATABASE 数据库名
  查看某个数据库创建时的信息

• DROP DATABASE 数据库名
  删除数据库

• USE 数据库名
  切换数据库
  注意:
  在DBMS下会为不同的项目创建不同的数据库，只有切换到某个数据库上，进行的相关操作才是针对该库的操作

• CREATE TABLE 表名(
  字段1名字 类型[(长度)] [DEFAULT 默认值] [约束],
  字段2名字 类型[(长度)] [DEFAULT 默认值] [约束],
  …
  )
  创建表

• DESC 表名
  查看表结构

• SHOW CREATE TABLE 表名
  查看表时创建的信息

• SHOW TABLES
  查看当前数据库所有的表

• RENAME TABLE 原表名 TO 新表名
  修改表名

• DROP TABLE 表名
  删除表

• ALTER TABLE 表名 ADD 字段名 类型[(长度)] [DEFAULT默认值] [约束]
  在一张表的末尾追加一个新的字段

• ALTER TABLE 表名 ADD 字段名 类型 FIRST
  在表最开始处插入字段

• ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中某字段
  将新字段添加到表中指定字段的后面

• ALTER TABLE 表名 DROP 字段名
  删除表中字段

• ALTER TABLE 表名 CHANGE 原字段名 新字段名 类型[长度 默认值 约束]
  修改表中字段

修改表结构的注意事项

修改表结构最好是在表中没有数据的前提下进行

如果表中已经存在数据

• 尽量不要修改字段类型，否则可能导致表中该字段原有的数据不符合新修改的类型而导致修改失败
• 字段的长度尽量不要缩短，否则可能由于表中该字段的值已经超过了修改后的长度导致修改失败
• 为字段添加新的约束时，要保证表中该字段原有的数据不违背该约束要求。
  

DML:数据操作语言

对表中数据进行操作的语言.包含:增(INSERT)删(DELETE)改(UPDATE)

• INSERT INTO 表名 (字段1,字段2,字段3,…) VALUES(值1,值2,值3,…)
  指定的值要与指定的字段一一对应，用于将值插入到表中作为一条记录
  INSERT语句中没有被指明的字段都是插入默认值。如果该字段没有明确指定默认值时，默认值为NULL
  注意事项:
  数据库中字符串的字面量使用单引号
  INSERT语句中VALUE子句中指定的值的顺序，个数，类型必须与前面指定的字段完全一致
  INSERT语句指定的字段的顺序，个数可以与该表不完全一样
  全列插入
  INSERT语句中不指定任何字段时，则为全列插入，此时要求VALUES子句中指定的值的顺序，个数，类型必须与表结构定义完全一致

• INSERT INTO 表名 VALUES(值1,值2,…)
  全列插入

• INSERT INTO 表名[(字段1,字段2,字段3…)] VALUES(值1,值2,值3),(第二组值),(第三组值),…
  批量插入

• SELECT * FROM 表名
  查看指定表中的所有记录

• UPDATE 表名 SET 字段1=新值1,字段2=新值2，… [WHERE 过滤条件]
  修改表数据

• WHERE子句的基础条件
  WHERE子句中可以使用的基础条件判断:>, >=, <, <=, =, <>(判断不等于使用"<>“,有些数据库支持”!=")

• DELETE FROM 表名 [WHERE 过滤条件]
  删除表中数据

• DELETE FROM 表名
  清空表

DQL:数据查询语言

用于检索表中数据的语言

SELECT

• SELECT 字段1,字段2,… FROM 表1,表2…
  SELECT * FROM 表1,表2… [WHERE 条件] [AND 条件] [OR 或/并列条件]

• 连接多个条件
  AND：“与”，都为真时才为真
  OR： “或”，都为假时才为假, 在SQL中有并列含义不是并且
  AND的优先级高于OR
  为了提高OR的优先级，可以使用"()"括起来

IN

• SELECT * FROM 表 WHERE 条件 OR 并列条件
  等价于
  SELECT * FROM 表 WHERE 条件 IN( ‘字符’,‘字符’ )

NOT IN

• SELECT * FROM 表 WHERE <>条件 AND <>条件
  等价于
  SELECT * FROM 表 WHERE 条件 NOT IN( ‘字符’,‘字符’ )

BETWEEN n AND m

• SELECT * FROM 表 WHERE 条件 BETWEEN n AND m
  在一个区间范围内。即:值要>=n并且<=m

DISTINCT

• SELECT DISTINCT 字段 FROM 表
  去重，将结果集中指定字段值重复的记录去除

LIKE

• _
  下划线表示一个字符(1个字符)

• %
  百分号表示任意个字符(0-任意次)

• SELECT 字段 FROM 表 WHERE 字段 LIKE ‘_%’

  常见格式

• LIKE ‘%X%’
  表示字符串中含有X(X之前或之后可以有任意个字符)

• LIKE ‘_X%’
  表示字符串第二个字符是X

• LIKE ‘X%’
  表示字符串是以X开头的

• LIKE ‘%X’
  表示字符串是以X结尾的

• LIKE ‘%X_Y’
  表示字符串倒数第三个字符是X并且最后一个字符是Y

NULL

• SELECT 字段 FROM 表 WHERE 字段 IS NULL
• SELECT 字段 FROM 表 WHERE 字段 IS NOT NULL
  IS NULL: 判断一个字段的值是否为空
  IS NOT NULL: 判断一个字段的值是否不为空
  不能使用"=“或者”<>"判断 NULL 和 非NULL

ORDER BY [DESC]

• SELECT 字段 FROM 表 [WHERE条件] ORDER BY 字段 [DESC]

DCL:数据控制语言

是对数据库进行管理的,通常比如创建用户,分配权限等.(DBA重点学习)

TCL:事务控制语言

包含:COMMIT(提交),ROLLBACK(回滚)

约束

我们可以对表施加约束条件，这样一来只有满足约束要求的操作才可以进行，否则会被数据库拒绝

主键约束

主键是用来唯一表示表中每一条记录的一个值

可以作为主键的值要同时满足的要求:

• 非空:
  每条记录都必须有该值,
  主键字段不可以插入NULL值
• 唯一:
  每条记录该字段的值都不可以重复

PRIMARY KEY

当表中某个字段添加了主键约束后,数据库会检查向该字段插入数据时是否非空且唯一,
一张表中只有能一个字段,添加主键约束作为主键的字段通常字段名命名为"id",一般也是表中的第一个字段

• 主键字段不可以插入重复的值
• 主键字段不可以插入NULL值
• 插入主键字段时，主键字段不可以忽略(忽略字段则插入默认值).
  如果主键字段有默认值的生成机制可以忽略
• 修改记录时，不可以修改重复的值到主键字段

AUTO_INCREMENT

• 创建表时为主键字段添加自增
• 修改表时，可以为字段添加自增
• 当主键字段具有自增后，可以插入NULL值，且NULL并不会被插入

非空约束

NOT NULL

非空约束可以要求表中字段的值不允许为NULL。插入数据时必须指定该字段的值，修改数据时也不可以将NULL值修改到该字段上

• 为字段指定非空约束
• 插入和修改数据时，不可以将NULL插入或修改到具有非空约束的字段上
• 修改表时为字段取消非空约束 指定 [NULL] 即允许插入NULL值

唯一性约束

UNIQUE

唯一性约束要求对应字段的值在整张表中不能有重复的值，NULL除外

• 可以为字段添加唯一性约束
• 插入数据时，不可以将重复值插入到具有唯一性约束的字段上
• NULL值可以插入到具有唯一性约束的字段中，且可以重复多次

检查约束

CHECK

检查约束允许我们为指定字段设定约束条件，只有满足条件的操作才被允许

• 为字段添加CHECK约束
• 插入和更新数据时,不能违反检查约束所自定义的规则

外键约束

小编得知实际开发中几乎不会使用