数据库基本知识

数据库

数据库基本概念

数据库与数据库管理系统

数据库 DataBase

保存一组数据的地方就称为一个数据库 . 比如我们创建一个目录 , 在目录中创建若干的文件 , 每个文件都是一组数据 , 那么

这个目录就可以称为一个数据库 .

只是针对这些文件上的数据维护要么手动 , 要么我们需要编写 java 代码进行维护 , 此时维护的通用性差 , 效率差 , 维护的复

杂度高 .

数据库管理系统 DBMS

它是一个独立的软件 , 用户维护磁盘上的数据 .

操作它便捷 , 且通用性高 .

常见的 DBMS:

Mysql

Oracle

DB2

SQLServer

在 java 项目中与数据库管理系统的关系

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

库与表的概念 表 : 表的结构由行和列构成 , 用于保存一组数据 .

表中的行称为记录 , 列称为字段

列 : 用于表示一条记录中有多少个属性

行 : 保存着一组属性的一条记录

库

可以叫数据库 , 每个数据库中可以包含若干张表 .

实际应用中每一个项目在数据库管理系统中都可以创建一个数据库 , 然后在这个数据库中创建若干张表来保存不同的

数据 . 如何操作数据库

所有的数据库 DBMS 都支持使用 SQL 语句进行操作 .

所有 DBMS 都支持 SQL92 标准 .

该标准中不是所有的数据库操作都有语法级定义

很多操作不同数据库就会各自定义对应的语法

SQL92 标准相当于是 " 普通话 ", 而各自定义的语法相当于 " 方言 "

这也是数据库学习的主要目的 : 学会 SQL 语言

SQL 的全称 :Structured Query Language 翻译 : 结构化查询语言

连接数据库的方式

命令行形式

图形化界面 ( 第一方或第三方提供的独立软件 )

IDEA 中操作

JDBC(java 数据库连接 ), 直接用 java 程序进行数据库连接与交互

SQL 分类

DDL 数据定义语言

DML 数据操作语言

DQL 数据查询语言

DCL 数据控制语言

TCL 事务控制语言 DDL 语言

数据定义语言，是针对数据库对象进行操作的语言

关键字 :

CREATE,DROP,ALTER

常见的数据库对象 : 数据库，表，视图，索引，序列等

数据库操作

新建一个数据库

语法 :

注 :SQL 语句中是不区分大小写的，但是行业规范中建议 : 关键字全大写，其他全小写。

例 :

创建数据库时指定字符集

查看数据库创建信息

例如 :

删除数据库

SHOW DATABASES

查看当前 DBMS 中已经创建的数据库

CREATE DATABASE 数据库名 [CHARSET= 字符集名称 ]

创建一个名为 mydb

CREATE DATABASE mydb

CREATE DATABASE mydb1 CHARSET=UTF8;

CREATE DATABASE mydb2 CHARSET=GBK;

SHOW CREATE DATABASE 数据库名

SHOW CREATE DATABSE mydb; 例如 :

切换数据库

将来可以针对某个数据库进行相关的操作，因此就会存在切换数据库操作

例如 :

练习 :

表的操作

创建表

语法 :

DROP DATABASE 数据库名

DROP DATABASE mydb;

USE 数据库名

USE mydb1;

USE mydb2;

1 . 创建 db1 和 db2 数据库 字符集分别为 utf8 和 gbk

CREATE DATABASE db1 CHARSET=UTF8;

CREATE DATABASE db2 CHARSET=GBK;

2 . 查询所有数据库检查是否创建成功

SHOW DATABASES;

3 . 检查两个数据库的字符集是否正确 ( 查看创建时的 SQL)

SHOW CREATE DATABASE db1;

SHOW CREATE DATABASE db2;

4 . 先使用 db2 再使用 db1

USE db2;

USE db1;

5 . 删除两个数据库

DROP DATABASE db1;

DROP DATABASE db2; 例如

查看表结构

语法 :

例如

查看创建表的详细信息

语法

例如

查看当前数据库中已创建的表

语法 :

CREATE TABLE 表名 (

字段 1 名 类型 [ 默认值，约束条件 ],

字段 2 名 类型 [ 默认值，约束条件 ],

....

)

CREATE DATABASE mydb; 准备工作，先创建一个数据库 mydb

USE mydb; 切换到 mydb 数据库

下面创建表时，都是创建到 mydb 这个库中了

CREATE TABLE user(

id INT , 在 MySQL 中整数类型为 int

username VARCHAR ( 30 ), VARCHAR 为变长字符串，指定的长度为字节 , 30 表示该字符

password VARCHAR ( 30 ), 串转换为字节后最多存 30 字节 (UTF8 编码中文 10 个字 )

age INT ( 3 ) int 若指定长度为整数的位数

)

DESC 表名

DESC user;

SHOW CREATE TABLE 表名

SHOW CREATE TABLE user; 修改表名

语法 :

例如

删除表

语法

例如 :

练习 :

修改表

准备一张表

SHOW TABLES;

RENAME TABLE 原表名 TO 新表名

将 user 表改名为 userinfo

REANAME TABLE user TO userinfo;

DROP TABLE 表名

DROP TABLE userinfo;

1 . 创建数据库 mydb3 字符集 gbk 并使用

2 . 创建 t_hero 英雄表 , 有名字和年龄字段

3 . 修改表名为 hero

4 . 查看表 hero 的信息

5 . 查询表 hero 结构

6 . 删除表 hero

7 . 删除数据库 mydb3

CREATE TABLE hero(

name VARCHAR ( 30 ),

age INT ( 3 )

) 添加字段

在表的末尾追加新的字段

例如 :

在表的最开始插入新字段

例如

ALTER TABLE 表名 ADD 字段名 类型

向表中追加性别字段

ALTER TABLE hero ADD gender VARCHAR ( 10 )

ALTER TABLE 表名 ADD 字段名 类型 FIRST

ALTER TABLE hero ADD id INT FIRST 在表中插入字段

例如 :

删除字段

例如 :

将指定的字段放在表中某字段的后面

ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中某字段

在名字后面追加密码字段 pwd

ALTER TABLE hero ADD pwd VARCHAR ( 30 ) AFTER name

ALTER TABLE 表名 DROP 字段名 修改字段

例如

删除 hero 表中的 pwd 字段

ALTER TABLE hero DROP pwd

ALTER TABLE 表名 CHANGE 原字段名 新字段名 类型

将 hero 表中年龄字段的长度改为 5

ALTER TABLE hero CHANGE age age INT ( 5 )

将 age 改为 VARCHAR( 10 )

ALTER TABLE hero CHANGE age age VARCHAR ( 10 )

将 hero 表中 gender 字段改名为 pwd 并且类型为 VARCHAR( 30 )

ALTER TABLE hero CHANGE gender pwd VARCHAR ( 30 ) 注意事项

修改字段时，如果表中已经包含数据

当修改字段类型的长度时，应当只增不减，由于原字段某记录的值长度不允许缩小了会导致修改失败

当修改字段类型时，尽量不进行。例如将 name 字段改为 int 值。 " 张三 " 怎么改成 int ？？？

练习 :

DML 数据操作语言

对表中记录进行操作的语言

关键字 :

INSERT,UPDATE,DELETE

准备一张表

插入数据 (INSERT 语句 )

1 . 创建数据库 mydb4 字符集 utf8 并使用

2 . 创建 teacher 表 有名字 (name) 字段

3 . 添加表字段 : 最后添加 age 最前面添加 id( int 型 ) , age 前面添加 salary 工资 ( int 型 )

4 . 删除 age 字段

5 . 修改表名为 t

6 . 删除表 t

7 . 删除数据库 mydb4

CREATE TABLE person(

name VARCHAR ( 30 ),

age INT ( 3 )

) 语法 :

例如 :

注：

数据库当中，字符串的字面量使用单引号括上，并且字符串的内容是区分大小写的

插入数据时指定的字段的顺序可以与表不同，但是 VALUES 后面指定的值必须与指定的字段顺序，类型，个数一

致

查询表中数据 :

插入默认值

当插入数据时，某个字段不指定时，则会插入默认值，当表中该字段没有明确指定默认值时，字段默认值为 NULL

例如 :

默认值的指定

注：这属于 DDL 语句的范畴，使用 DEFAULT 指定默认值

INSERT INTO 表名 [( 字段名 1, 字段名 2,...)] VALUES ( 字段值 1, 字段值 2,...)

INSERT INTO pesron (name,age) VALUES ( ' 张三 ' , 18 )

INSERT INTO pesron (age,name) VALUES ( 22 , ' 李四 ' )

SELECT * FROM 表名

SELECT * FROM person

INSERT INTO person (name) VALUES ( ' 王五 ' )

没有指定 age, 则该条记录插入后 age 字段的值为 null 全列插入

当不指定字段名时，则为全列插入，此时 VALUES 后面指定的值的顺序，类型，个数必须与表结构一致

创建表时

CREATE TABLE person(

name VARCHAR ( 30 ) DEFAULT ' 无名氏 ' ,

age INT ( 3 ) DEFAULT 18

)

修改该时

ALTER TABLE person CHANGE age age INT ( 3 ) DEFAULT 18 ;

修改后， age 字段的默认值为 18

INSERT INTO person (name) VALUES ( ' 赵六 ' )

插入数据后，赵六的年龄为 18( 默认值 ) 修改数据 (UPDATE 语句 )

语法 :

例 :

WHERE 子句在 UPDATE 中的使用

通常修改表中数据时，要在 UPDATE 语句中添加 WHERE 子句， WHERE 子句的作用是添加过滤条件用于筛选要修改的

记录

子句 : 在 SQL 中一个关键字后面会搭配一些内容，这个就是一个子句

例如 :

INSERT INTO person VALUES ( ' 钱七 ' , 26 )

年龄插入默认值 : 使用 DEFAULT 关键字代替值

INSERT INTO person VALUES ( ' 孙八 ' ,DEFAULT)

显示的插入 NULL 值 : 使用 NULL 关键字代替值

INSERT INTO person VALUES ( ' 周九 ' , NULL )

下列是错误的 :

INSERT INTO person VALUES ( ' 老十 ' ) 列对应值的个数不匹配

INSERT INTO person VALUES ( 22 , ' 老十 ' ) 列对应的值类型不匹配 ( 第一列应是名字 VARCHAR 类型 )

UPDATE 表名 SET 字段 1 名 = 字段 1 值 [, 字段 2 名 = 字段 2 值 ,.... ][ WHERE 过滤条件 ]

UPDATE person SET age= 40

上述 SQL 执行后 person 表中每条记录的 age 字段值都会被改为 40!

将张三的年龄改为 20 岁

UPDATE person 子句

SET age= 20 子句

WHERE name= ' 张三 ' 子句

在上述 SQL 中， WHERE 条件的作用是筛选出修改的记录

数据库在修改记录时是逐条进行修改的，记录是否被修改取决于这条记录是否满足 WHERE 指定的条件。

上述 SQL 中只有张三的记录符合 WHERE 条件，因此只有该条记录的年龄会被修改为 20 ，而其他记录则不会被修改。

将 person 表中年龄为 40 岁的改为 36 岁

UPDATE person WHERE 中还可以使用如 :>,>=,<,<=,<>( 不等于使用 "<>", 而 "!=" 不是所有数据库都支持 )

使用表达式进行修改

修改多个字段

删除数据 (DELETE 语句 )

语法 :

注：在 DELETE 语句中不添加 WHERE 子句就是清空表操作

例 :

清空表操作

SET age= 36

WHERE age= 40 person 表中凡是年龄为 40 的记录都会被改为 36

将 person 表中年龄大于 30 岁的人改为年龄 29 岁

UPDATE person

SET age= 29

WHERE age> 30

为 person 表中每个人的年龄涨一岁

UPDATE person

SET age=age+ 1

将 person 表中的 " 李四 " 改名为 " 李老四 " ，年龄改为 55 岁

UPDATE person

SET name= ' 李老四 ' ,age= 55

WHERE name= ' 李四 '

DELETE FROM 表名 [WHERE 过滤条件 ]

删除名字为 " 李老四 " 的记录

DELETE FROM person

WHERE name= ' 李老四 ' 删除表中满足 WHERE 条件的记录

删除年龄小于 25 岁的人

DELETE FROM person

WHERE age< 25 练习

总结

DML ：数据操作语言，它是对表中数据进行操作的语言，涵盖的操作 : 增 ( INSERT ) ，删 ( DELETE ) ，改 ( UPDATE )

INSERT 语句用于向表中插入数据

插入数据时，指定的字段名的顺序可以与表不相同，但是 VALUES 子句中指定的值要与指定的字段顺序，个

数，类型都相同

插入数据时可以忽略某些字段，此时插入的记录中没有指定的字段会插入该字段的默认值

可以使用 DEFAULT 关键字显示的为某个字段插入默认值

可以使用 NULL 显示的为某个字段插入 NULL 值

插入数据时可以不指定任何字段，此时为全列插入， VALUES 子句要求指定的值的顺序，个数，类型必须与

表一致

DELETE FROM person

1 . 创建数据库 day1db 字符集 utf8 并使用

CREATE DATABASE day1db CHARSET=UTF8

USE day1db

2 . 创建 t_hero 表 , 有 name 字段 , 字符集 utf8

CREATE TABLE t_hero(

name VARCHAR ( 30 )

)CHARSET=UTF8

3 . 修改表名为 hero

RENAME TABLE t_hero TO hero

4 . 最后面添加价格字段 money( 整数类型，长度 6), 最前面添加 id 字段 ( 整数类型 ), name 后面添加 age 字段 ( 整数类

型，长度 3)

ALTER TABLE hero ADD money INT ( 6 )

ALTER TABLE hero ADD id INT FIRST

ALTER TABLE hero ADD age INT ( 3 ) AFTER name

5 . 表中添加以下数据 : 1 , 李白 , 50 , 6888 2 , 赵云 , 30 , 13888 3 , 刘备 , 25 , 6888

INSERT INTO hero (id,name,age,money) VALUES ( 1 , ' 李白 ' , 50 , 6888 )

INSERT INTO hero (id,name,age,money) VALUES ( 2 , ' 赵云 ' , 30 , 13888 )

INSERT INTO hero (id,name,age,money) VALUES ( 3 , ' 刘备 ' , 25 , 6888 )

6 . 修改刘备年龄为 52 岁

UPDATE hero

SET age= 52

WHERE name= ' 刘备 '

7 . 修改年龄小于等于 50 岁的价格为 5000

UPDATE hero

SET money= 5000

WHERE age<= 50

8 . 删除价格为 5000 的信息

DELETE FROM hero

WHERE money= 5000

9 . 删除表 , 删除数据库

DROP TABLE hero

DROP DATABASE day1db UPDATE 语句用于修改表中数据

修改数据时通常会指定 WHERE 子句，否则为全表所有记录进行修改

DELETE 语句用于删除表中的数据

删除数据时通常也要指定 WHERE 子句，否则是清空表操作

数据类型

不同的数据库，数据类型不完全一致

数字类型

整数类型 :

INT(m)

BIGINT(m)

m 表示整数的位数。

例如

INT(5): 整数最大位数为 5. 如果插入一条记录该字段值为整数 18 ，实际保存时 :00018

浮点类型 DOUBLE(m,n)

m: 数字的位数

n: 为其中小数的位数

m 包含 n

DOUBLE(5,2): 数字总共 5 位，其中包含 2 位小数，可保存的最大值 :999.99

如果保存数字时小数的精度超过允许范围时，会进行四舍五入

例如

如果插入最大值还需要四舍五入时会报错

Person 表中有一个字段为 salary 是一个 DOUBLE 类型 (7,4)

INSERT INTO person (salary) VALUES(555.125687)

插入数据后，该记录中 salary 字段最终保存的值 :555.1257

INSERT INTO person (salary) VALUES(999.999987) 字符类型

定长字符 :CHAR(n)

n 表示长度，单位是字符

name CHAR(10):name 可以保存 10 个字符

最大值为 255 ，最多可以保存 255 个字符

定长字符串，无论表中该字段实际保存的字符个数 , 在磁盘空间中开辟的一定是最大字符所占用的空间。当达不

到指定长度时会在后面补充若干个空格。

优点 : 由于每条记录占用的磁盘空间是固定的，因此查询速度更快。

缺点 : 犹豫每条记录该字段占用的磁盘空间是固定的，那么这会导致磁盘空间的浪费

变长字符 :VARCHAR(n)

n 表示长度，但是为字节

最大值为 65535

实际占用磁盘空间为用多少占多少

优点 : 磁盘空间没有浪费

缺点 : 由于每条记录的占用空间不固定，因此查询速度相对较慢

变长字符 :TEXT(n)

n 表示长度，单位是字符

最大值为 65535

日期类型

DATE 类型 : 保存年月日

TIME 类型 : 保存时分秒

DATETIME: 保存年月日时分秒

TIMESTAMP: 时间戳类型，记录 UTC 时间。自 1970 年元旦到该时间之间经过的毫秒

例

准备一张表 CREATE TABLE userinfo(

id INT ,

username VARCHAR ( 30 ),

gender CHAR ( 1 ),

birth DATETIME ,

salary DOUBLE ( 7 , 2 )

)

插入日期类型是，如果日期类型为 DATETIME 型，可以用字符串各式表示 :"yyyy-MM-dd hh:mm:ss"

MM 表示 2 位数字的月， mm 表示 2 位数字的分

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES ( 1 , ' 张三 ' , ' 男 ' , '1998-12-20 10:05:22' , 5000.50 )

插入日期时，如果类型为 DATETIME ，是可以忽略时分秒的

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES ( 2 , ' 李四 ' , ' 男 ' , '2002-05-16' , 8000.96 )

插入日期时，如果类型为 DATETIME ， 不可以忽略年月日

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES ( 3 , ' 王五 ' , ' 女 ' , '19:23:56' , 9500.15 )

插入 DOUBLE 类型值是，小数部分超过精度会四舍五入，整数部分超过会直接报错

INSERT INTO userinfo(id,username,gender,birth,salary)

VALUES ( 3 , ' 王五 ' , ' 女 ' , '2002-05-16 19:23:56' , 100000 ) 约束条件

可以对表添加约束条件，这样一来只有满足约束的操作才会被允许，否则会被拒绝

主键约束 (Primary Key)

主键约束在一张表中只能被施加在一个字段上

主键约束的特点 : 该字段的值在整张表中要求每条记录都必须是 非空且唯一的

通常情况下一张表中第一个字段为主键字段，名字一般使用 "id"

例

主键字段不能插入重复的值

不能将 NULL 值插入主键字段

修改表中现有记录时，也不能修改重复的值或 NULL 给主键字段

CREATE TABLE user1(

id INT PRIMARY KEY,

name VARCHAR ( 30 ),

age INT ( 3 )

)

INSERT INTO user1(id,name,age) VALUES ( 1 , ' 张三 ' , 16 )

INSERT INTO user1(id,name,age) VALUES ( 2 , ' 李四 ' , 22 )

INSERT INTO user1(id,name,age) VALUES ( 1 , ' 王五 ' , 36 )

INSERT INTO user1(id,name,age) VALUES ( NULL , ' 王五 ' , 36 )

INSERT INTO user1(name,age) VALUES ( ' 王五 ' , 36 ) 具体主键约束的字段通常可以搭配自增使用 AUTO_INCREMENT

创建表时，可以在添加主键约束的同时添加自增

修改表时，也可以为主键字段添加自增

当主键字段具有自增时，插入数据可以忽略主键字段

UPDATE user1

SET id= 1

WHERE name= ' 李四 '

CREATE TABLE user2(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR ( 30 ),

age INT ( 3 )

)

ALTER TABLE user1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT

如果 id 字段已经具有了主键约束，那么可以单独修改添加自增

ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT

INSERT INTO user2(name,age) VALUES ( ' 张三 ' , 22 );

INSERT INTO user2(name,age) VALUES ( ' 李四 ' , 16 ); 当主键字段具有自增时，可以显示的为主键插入 NULL 值，此时主键仍然自增 ( 不推荐 )

非空约束 (NOT NULL)

被非空约束修饰的字段，在任何时候该字段的值都不允许为空

例

创建表时可以为某字段指定非空约束

修改表时为某字段施加非空约束

不能将 NULL 值插入具有非空约束的字段上

也不能将具有非空约束的字段修改为 NULL 值

INSERT INTO user2(id,name,age) VALUES ( NULL , ' 王五 ' , 45 );

CREATE TABLE user3(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR ( 30 ) NOT NULL , name 字段被施加非空约束

age INT ( 3 )

)

ALTER TABLE user2 CHANGE name name VARCHAR ( 30 ) NOT NULL

INSERT INTO user3(name,age) VALUES ( NULL , 16 ) 插入数据时，不能忽略具有非空约束的字段

唯一性约束 (UNIQUE)

具有唯一性约束的字段，该字段的值整张表中不允许有重复的

例

创建一张表时可以为某个字段添加唯一性约束

可以在修改表时为某个字段添加唯一性约束

INSERT INTO user3(name,age) VALUES ( ' 张三 ' , 22 );

数据插入后进行修改操作 :

UPDATE user3

SET name= NULL

WHERE name= ' 张三 '

INSERT INTO user3(age) VALUES ( 22 )

CREATE TABLE user4(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR ( 30 ) UNIQUE,

age INT ( 3 )

) 插入数据时，不能将重复的值插入具有唯一性约束的字段

具有唯一性约束的字段，一张表中可以存在 NULL 值且可以重复

修改记录时，不能将重复的值修改到具有唯一性约束的字段， NULL 除外。

外键约束

外键约束在实际开发中几乎不会被采用，因为它的约束要求不能满足很多业务场景对表中数据关联关系的要求。

ALTER TABLE user3 CHANGE name name VARCHAR ( 30 ) UNIQUE

注意 : 如果 user3 中 name 字段有非空约束时，上述 SQL 添加唯一性约束后会取消非空约束

ALTER TABLE user3 CHANGE name name VARCHAR ( 30 ) NOT NULL UNIQUE

INSERT INTO user4(name,age) VALUES ( ' 张三 ' , 22 ) 成功

INSERT INTO user4(name,age) VALUES ( ' 张三 ' , 55 ) 失败

INSERT INTO user4(name,age) VALUES ( NULL , 36 ) 成功

INSERT INTO user4(name,age) VALUES ( NULL , 47 ) 成功

UPDATE user4

SET name= ' 张三 '

WHERE age= 36