数据库基本概念及SQL语句

来源：拉勾教育Java就业集训营1.数据库的基本概念1.1 什么是数据库

1. 数据库 就是存储和管理数据的仓库
2. 其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上

1.2 为什么使用数据库​ 数据存储方式的比较

存储方式	优点	缺点
内存	速度快	不能够永久保存,数据是临时状态的
文件	数据是可以永久保存的	使用IO流操作文件, 不方便
数据库	1.数据可以永久保存 2.方便存储和管理数据 3.使用统一的方式操作数据库	占用资源,有些数据库需要付费

通过上面的比较,我们可以看出,使用数据库存储数据, 用户可以非常方便对数据库中的数据进行增加, 删 除, 修改及查询操作。1.3 常见的数据库

数据库名	介绍
MySql数据 库	开源免费的数据库 因为免费开源、运作简单的特点，常作为中小型的项目的数据库首选。 MySQL1996年开始运作，目前已经被Oracle公司收购了. MySQL6.x开始收费
Oracle数据 库	收费的大型数据库，Oracle公司的核心产品。 安全性高
DB2	IBM公司的数据库产品,收费的超大型数据库。 常在银行系统中使用
SQL Server	MicroSoft 微软公司收费的中型的数据库。 C#、.net等语言常使用。 但该数据库只能运行在windows机器上，扩展性、稳定性、安全性、性能都表现 平平。

为什么选择MySQL 1. 功能强大,足以应付web应用开发 2. 开源, 免费2.SQL2.1 SQL的概念1） 什么是SQL ？ ​结构化查询语言简称SQL，是一种特殊目的的编程语言，是一种数据库 查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。 2） SQL 的作用

• 是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL
• 所有的关系型数据库都可以使用SQL
• 不同数据库之间的SQL 有一些区别 方言

2.2 SQL通用语法1） SQL语句可以单行 或者 多行书写，以分号 结尾 ; （Sqlyog中可以不用写分号） 2） 可以使用空格和缩进来增加语句的可读性。 3） MySql中使用SQL不区分大小写，一般关键字大写，数据库名 表名列名 小写。

4） 注释方式

注释语法	说明
-- 空格	单行注释
	多行注释
#	MySql特有的单行注释

# show databases; 单行注释-- show databases; 单行注释2.3 SQL的分类

分类	说明
数据定义语 言	简称DDL，用来定义数据库对象：数据库，表，列 等。
数据操作语 言	简称DML，用来对数据库中表的记录进行更新。
数据查询语 言	简称DQL，用来查询数据库中表的记录。
数据控制语 言	简称DCL，用来定义数据库的访问权限和安全级别， 及创建用户。

注意：MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，区别在于：

• char类型是固定长度的： 根据定义的字符串长度分配足够的空间。
• varchar类型是可变长度的： 只使用字符串长度所需的空间

比如：保存字符串 "abc"x char 占用10个字节y varchar 占用3个字节适用场景：

• char类型适合存储 固定长度的字符串，比如 密码 ，性别一类
• varchar类型适合存储 在一定范围内，有长度变化的字符串

2.5.2 创建表语法格式：

CREATE

TABLE

表名

；

#快速创建一个表结构相同的表（复制表结构）

-- 创建一个表结构与 test1 相同的 test2表

CREATE

TABLE

test2

LIKE

test1

;

2.5.3 查看表

命令	说明
show tables;	查看当前数据库中的所有表名
desc 表名；	查看数据表的结构
SHOW CREATE TABLE 表名；	查看创建表的SQL语句

2.5.4 删除表

命令	说明
drop table 表名；	删除表（从数据库中永久删除某一张表）
drop table if exists 表名；	判断表是否存在， 存在的话就删除,不存在就不执行删除

2.5.5 修改表1）修改表名rename table 旧表名 to 新表名2) 修改表的字符集alter table 表名 character set 字符集3）向表中添加列， 关键字 ADDalert table 表名 add 字段名称 字段类型4）修改表中列的 数据类型或长度 ， 关键字 MODIFYalter table 表名 modify 字段名称 字段类型5）修改列名称 , 关键字 CHANGEalter table 表名 change 旧列名 新列名 类型;6）删除列 ，关键字 DROPalter table 表名 drop 列名;2.6 DML 操作表中数据2.6.1 插入数据

insert

into

表名

（字段名

1

，字段名

2

...

）

values

;

#方式1： 插入全部字段， 将所有字段名都写出来

INSERT

INTO

student

VALUES

;

#方式2： 插入全部字段，不写字段名

INSERT

INTO

student

VALUES

;

#方式3：插入指定字段的值

INSERT

INTO

category

VALUES

;

注意： ​1) 值与字段必须要对应，个数相同&数据类型相同 ​2）值的数据大小，必须在字段指定的长度范围内 ​3）varchar char date类型的值必须使用单引号，或者双引号 包裹。

​4）如果要插入空值，可以忽略不写，或者插入null ​5) 如果插入指定字段的值，必须要上写列名2.6.2 更改数据

#不带条件的修改

update

表名

set

列名

=

值

#带条件的修改

update

表名

set

列名

=

值

[

where

条件表达式：字段名

=

值

]

#一次修改多个列， 将sid为 2 的学员，年龄改为 20，地址改为 北京

UPDATE

student

SET

age

=

20

,

address

=

'北京'

WHERE

sid

=

2

;

2.6.3 删除数据

#删除所有数据

delete

from

表名

#指定条件 删除数据

delete

from

表名

[

where

字段名

=

值

]

#如果要删除表中的所有数据,有两种做法

#1. delete from 表名; 不推荐. 有多少条记录 就执行多少次删除操作. 效率低

#2. truncate table 表名: 推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高

truncate

table

student

;

2.7 DQL 查询表中数据2.7.1 简单查询

查询不会对数据库中的数据进行修改.只是一种显示数据的方式 SELECT

#语法格式

select

列名

from

表名

#查询emp中的 所有数据

SELECT

*

FROM

emp

;

-- 使用 * 表示所有列

#查询emp表中的所有记录，仅显示id和name字段

SELECT

eid

,

ename

FROM

emp

;

#将所有的员工信息查询出来，并将列名改为中文

#别名查询，使用关键字 as

SELECT

eid

AS

'编号'

,

hire_date

'入职时间'

,

-- AS 可以省略

FROM

emp

;

#查询一共有几个部门

#使用去重关键字 distinct

-- 使用distinct 关键字,去掉重复部门信息

SELECT

DISTINCT

dept_name

FROM

emp

;

#将所有员工的工资 +1000 元进行显示

#运算查询

SELECT

ename

,

salary

+

1000

FROM

emp

;

2.7.2 条件查询

如果查询语句中没有设置条件,就会查询所有的行信息,在实际应用中,一定要指定查询条件,对记录进行过 滤

select

列名

from

表名

where

条件表达式

*

先取出表中的每条数据

,

满足条件的数据就返回

,

不满足的就过滤掉

运算符1) 比较运算符

运算符	说明
> < <= >= = <> !=	大于、小于、大于等于、不等于
BETWEEN ...AND...	显示在某一区间的值 例如: 2000-10000之间： Between 2000 and 10000
IN	集合表示多个值,使用逗号分隔,例如: name in in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE '%张%'	模糊查询
IS NULL	查询某一列为NULL的值, 注: 不能写 = NULL

2) 逻辑运算符

运算符	说明
And &&	多个条件同时成立
Or ||	多个条件任一成立
Not	不成立，取反。

模糊查询 通配符

通配符	说明
%	表示匹配任意多个字符串,
_	表示匹配 一个字符

2.7.3排序

通过 ORDER BY 子句,可以将查询出的结果进行排序

#DESC 表示降序排序

排序方式1) 单列排序 :只按照某一个字段进行排序, 就是单列排序

-- 默认升序排序 ASC

SELECT

*

FROM

emp

ORDER

BY

salary

;

-- 降序排序

SELECT

*

FROM

emp

ORDER

BY

salary

DESC

;

2) 组合排序:同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推-- 组合排序SELECT * FROM emp ORDER BY salary DESC, eid DESC;2.7.4聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵 向查询，它是对某一列的值进行计算，然后返回一个单一的值；

SELECT

聚合函数

FROM

表名

;

聚合函数	作用
count	统计指定列不为NULL的记录行数
sum	计算指定列的数值和
max	计算指定列的最大值
min	计算指定列的最小值
avg	计算指定列的平均值

2.7.5分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

SELECT

分组字段

/

聚合函数

FROM

表名

GROUP

BY

分组字段

[

HAVING

条件

];

注意：分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作. * 查询其他字段没有意义

• 需求:查询平均薪资大于6000的部门.
• 分析： 需要在分组后,对数据进行过滤,使用 关键字 hiving
• 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

# 查询平均薪资大于6000的部门

-- 需要在分组后再次进行过滤,使用 having

SELECT

dept_name

,

AVG

FROM

emp

WHERE

dept_name

IS

NOT

NULL

GROUP

BY

dept_name

HAVING

AVG

>

6000

;

where 与 having的区别

过滤方式	特点
where	where 进行分组前的过滤 where 后面不能写 聚合函数
having	having 是分组后的过滤 having 后面可以写 聚合函数

2.7.6 limit关键字limit 关键字的作用

• limit是限制的意思,用于 限制返回的查询结果的行数
• limit 语法是 MySql的方言,用来完成分页

SELECT

字段

1

,

字段

2

...

FROM

表名

LIMIT

offset

,

length

;

#limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数

#offset 起始行数, 从0开始记数, 如果省略 则默认为 0

#length 返回的行数

-- 分页操作 每页显示3条数据

SELECT

*

FROM

emp

LIMIT

0

,

3

;

-- 第1页

SELECT

*

FROM

emp

LIMIT

3

,

3

;

-- 第2页 2-1=1 1*3=3

SELECT

*

FROM

emp

LIMIT

6

,

3

;

-- 第三页

-- 分页公式 起始索引 = * 每页条数

-- limit是MySql中的方言

3.SQL约束1) 约束的作用:对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中2) 常见的约束

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

3.1 主键约束

特点	不可重复 唯一 非空
作用	用来表示数据库中的每一条记录

3.1.1 添加主键约束

字段名

字段类型

primary

key

# 方式1 创建一个带主键的表

CREATE

TABLE

emp2

,

sex

CHAR

);

-- 删除表

DROP

TABLE

emp2

;

-- 方式2 创建一个带主键的表

CREATE

TABLE

emp2

,

sex

CHAR

,

-- 指定主键为 eid字段

PRIMARY

KEY

);

-- 方式3 创建一个带主键的表

CREATE

TABLE

emp2

,

sex

CHAR

)

-- 创建的时候不指定主键,然后通过 DDL语句进行设置

ALTER

TABLE

emp2

ADD

PRIMARY

KEY

;

哪些字段可以作为主键

• 通常针对业务去设计主键,每张表都设计一个主键id
• 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主键.

3.1.2 删除主键约束

-- 使用DDL语句 删除表中的主键

ALTER

TABLE

emp2

DROP

PRIMARY

KEY

;

DESC

emp2

;

3.1.3 主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段 的值.

关键字:AUTO_INCREMENT 表示自动增长3.1.4 修改主键自增的起始值默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

-- 创建主键自增的表,自定义自增其实值

CREATE

TABLE

emp2

,

sex

CHAR

)

AUTO_INCREMENT

=

100

;

-- 插入数据,观察主键的起始值

INSERT

INTO

emp2

VALUES

;

INSERT

INTO

emp2

VALUES

3.1.5 DELETE和TRUNCATE对自增长的影响

删除表中所有数据有两种方式

清空表数据的方式	特点
DELETE	只是删除表中所有数据,对自增没有影响
TRUNCATE	truncate 是将整个表删除掉,然后创建一个新的表 自增的主键,重新从 1开始

3.2 非空约束非空约束的特点: 某一列不予许为空

字段名

字段类型

not

null

3.3 唯一约束唯一约束的特点: 表中的某一列的值不能重复

字段名

字段值

unique

主键约束与唯一约束的区别:

• 主键约束 唯一且不能够为空
• 唯一约束,唯一 但是可以为空
• 一个表中只能有一个主键 , 但是可以有多个唯一约束

3.4 外键约束3.4.1 什么是外键

• 外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
• 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

3.4.2 创建外键约束

-- 新建表时添加外键

[

CONSTRAINT

]

[

外键约束名称

]

FOREIGN

KEY

REFERENCES

主表名

-- 已有表添加外键

ALTER

TABLE

从表

ADD

[

CONSTRAINT

]

[

外键约束名称

]

FOREIGN

KEY

REFERENCES

主表

;

-- 可以省略外键名称, 系统会自动生成一个

添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性,插入一条外键不存在的数据时会报错，外键可以为NULL

3.4.3 删除外键约束

alter

table

从表

drop

foreign

key

外键约束名称

3.4.4 外键约束的注意事项

1. 从表外键类型必须与主表主键类型一致 否则创建失败.
2. 添加数据时, 应该先添加主表中的数据.
3. 删除数据时,应该先删除从表中的数据.

3.4.5 级联删除操作

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

级联删除

ON

DELETE

CASCADE

-- 重新创建添加级联操作

CREATE

TABLE

employee

,

age

INT

,

dept_id

INT

,

CONSTRAINT

emp_dept_fk

FOREIGN

KEY

REFERENCES

department

-- 添加级联删除

ON

DELETE

CASCADE

);

3.5 默认值默认值约束 用来指定某列的默认值

字段名

字段类型

DEFAULT

默认值

4. 多表关系设计​实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表、 商品表、订单表等多张表。

且这些表的数据之间存在一定的关系，接下来我们一起 学习一下多表关系设计方面的知识

表与表之间的三种关系
一对多关系: 最常见的关系, 学生对班级,员工对部门
多对多关系: 学生与课程, 用户与角色
一对一关系: 使用较少,因为一对一关系可以合成为一张表

4.1 一对多关系

• 一对多关系（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品
• 一对多建表原则 在从表创建一个字段,字段作为外键指向主表的主键

4.3 一对一关系

• 一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。
• 一对一建表原则 外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

• 内连接: inner join , 只获取两张表中 交集部分的数据.
• 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
• 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

6.子查询 6.1 什么是子查询

• 子查询概念
• 一条select 查询语句的结果, 作为另一条 select 语句的一部分
• 子查询的特点
• 子查询必须放在小括号中 子查询一般作为父查询的查询条件使用
• 子查询常见分类
• where型 子查询: 将子查询的结果, 作为父查询的比较条件
• from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
• exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查 询的结果

6.2 子查询的结果作为查询条件

SELECT

查询字段

FROM

表

WHERE

字段

=

（子查询）

;

#1. 通过子查询的方式, 查询价格最高的商品信息

# 通过子查询的方式, 查询价格最高的商品信息

-- 1.先查询出最高价格

SELECT

MAX

FROM

products

;

-- 2.将最高价格作为条件,获取商品信息

SELECT

*

FROM

products

WHERE

price

=

FROM

products

);

6.3 子查询的结果作为一张表

SELECT

查询字段

FROM

（子查询）表别名

WHERE

条件

;

#1. 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称

-- 1. 先查询分类表的数据

SELECT

*

FROM

category

;

-- 2.将上面的查询语句 作为一张表使用

SELECT

p

.

`

pname

`

,

p

.

`

price

`

,

c

.

cname

FROM

products

p

-- 子查询作为一张表使用时 要起别名 才能访问表中字段

INNER

JOIN

c

ON

p

.

`

category_id

`

=

c

.

cid

WHERE

p

.

`

price

`

>

500

;

注意： 当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。