数据库概念

来源黑马

数据模型

关系型数据库

概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库

而所谓的二维表,是指由行和列组成的表。MySQL、Oracle、DB2、SQLServer都属于关系型数据库,都是基于二维表存储数据的。

特点:使用表存储数据,格式统一,便于维护;使用SQL语言操作,标准统一,使用方便

数据模型

  • 我们可以通过MySQL客户端连接数据库管理系统DBMS,然后通过DBMS操作数据库。
  • 可以使用SQL语句,通过数据库管理系统操作数据库,以及操作数据库中的表结构及数据。
  • 一个数据库服务器中可以创建多个数据库,一个数据库中也可以包含多张表,而一张表中又可以包含多行记录

SQL

全称 Structured Query Language,结构化查询语言。操作关系型数据库的编程语言,定义了

一套操作关系型数据库统一标准

SQL通用语言

  1. SQL语句可以单行或多行书写,以分号结尾
  2. SQL语句可以使用空格、缩进来增强语句的可读性
  3. MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
  4. 注释:
    1. 单行注释:– 注释内容 或 # 注释内容
    2. 多行注释:/* 注释内容 */

SQL分类

SQL语句,根据其功能,主要分为四类:DDL、DML、DQL、DCL。

DDL

Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段) 。

数据库操作

查询所有数据库

show databases ;

查询当前数据库

select database() ;

创建数据库

`create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序

规则 ] ; `

在同一个数据库中不能创建两个名称相同的数据库,否则会报错

可以通过IF NOT EXISTS来解决这个问题

CREATE DATEBASE IF NOT EXISTS itcast;

创建数据库并指定字符集

CREATE DATEBASE itheima DEFAULT CHARSET utf8mb4;

删除数据库

drop database [ if exists ] 数据库名 ;

切换数据库

use 数据库名 ;

表操作

查询创建

查询当前数据库所有表

show tables;

查询指定表结构

desc 表名

查询指定表的建表语句

show create table 表名 ;

创建表结构
1
2
3
4
5
6
7
CREATE TABLE 表名( 
字段1 字段1类型 [COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

数据类型

主要分为三类:数值类型、字符串类型、日期时间类型。

数值类型
字符串类型
日期类型

修改

添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

修改数据类型
修改字段名和字段类型
删除字段
删除表名

删除

删除表
删除指定表,并重新创建

DML

DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。

  • 添加字段INSERT
  • 修改字段UPDATE
  • 删除字段DELETE

添加数据

给指定字段添加数据

修改数据

删除数据

DQL

基本语法

基础查询

条件查询

聚合函数

分组查询

排序查询

分页查询

案例

执行顺序

DCL

管理用户

查询用户

select * from mysql.user

Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

注意事项:

  • 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
  • 主机名可以使用 % 通配。
  • 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用

权限控制

查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项:

  • 多个权限之间,使用逗号分隔
  • 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。

函数

字符串函数

数值函数

日期函数

流程函数

约束

概述

约束条件 说明
PRIMARY KEY 主键约束用于唯一标识对应的记录
FOREIGN KEY 外键约束
NOT NULL 非空约束
UNIQUE 唯一性约束
DEFAULT 默认值约束,用于设置字段的默认值

主键约束

主键约束(primary key)用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为null,主键的值是不可以重复的。

一张表最大只能有一个主键,但可以是复合主键

字段名 数据类型 primary key;

外键约束

介绍

外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。

外键约束即FOREIGN KEY常用于多张表之间的约束

用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是null

语法

1
2
3
4
-- 在创建数据表时语法如下:
[CONSTRAINT] [外键名] FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下:
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

删除、更新

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

非空约束

非空约束(NOT NULL)指的是字段的值不能为空

字段名 数据类型 NOT NULL

1
2
3
4
create table student02(
id int
name varchar(20) not null
);

默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值,即当在表中插入一条新记录时若未给该字段赋值,那么,数据库系统会自动为这个字段插人默认值

字段名 数据类型 DEFAULT 默认值;

1
2
3
4
5
create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性,即表中字段的值不能重复出现

1
2
3
4
create table student04(
id int,
name varchar(20) unique
);

使用细节:

  • 如果没有指定not null,则unique字段可以有多个null
  • 一张表可以有多个unique字段

Check约束

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间,如果不在该范围内就会提示出错

注:mysql5.7不支持check,只做语法校验,但不会生效;mysql8.0支持

1
2
3
4
5
6
7
8
9
CREATE TABLE t23 ( 
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);
-- 添加数据
INSERT INTO t23 VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;

多表查询

多表关系

多表查询概述

内连接

内连接查询的是两张表交集部分的数据。是不含null的

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显示内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

inner可以不写

外连接

外连接分为两种,分别是:左外连接 和 右外连接。

左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。

右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。

自连接

自连接查询

自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

联合查询

对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。

1
2
3
SELECT 字段列表 
FROM 表A ...
UNION [ ALL ] SELECT 字段列表 FROM 表B ....;
  • 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。

示例:

1
2
3
4
select * 
from emp
where salary < 5000
union all select * from emp where age > 50;
  • union all查询出来的结果,仅仅进行简单的合并,并未去重
  • union 联合查询,会对查询出来的结果进行去重处理

子查询

三表联查

案例

事务

事务简介

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。典型案例是银行转账

默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务。

事务操作

使用的表:

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE if EXISTS account;

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
`name` VARCHAR(10) COMMENT '姓名',
money DOUBLE(10,2) COMMENT '余额'
)COMMENT '账户表';

INSERT INTO account(`name`, money)
VALUES ('张三',2000), ('李四',2000);

SELECT * FROM account

未控制事务

控制事务一

查看事务提交方式

SELECT @@autocommit ;

设置事务提交方式:1为自动提交 0为手动提交

SET @@autocommit = 0 ;

提交事务

COMMIT

回滚事务

ROLLBACK

上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

控制事务二

开启事务

START TRANSACTION 或 BEGIN ;

提交事务

COMMIT

回滚事务

ROLLBACK

事务四大特性ACID

  • 原子性Atomicity:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
  • 一致性Consistency:事务完成时,必须使所有的数据都保持一致状态
  • 隔离性Isolation:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性Durability:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的

并发事务问题

脏读:一个事务读到另外一个事务还没有提交的数据

不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读

幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在, 好像出现了幻影

事务隔离级别

为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:

隔离级别 脏读 不可重复读 幻读
Read uncommitted
Read committed ×
Repeatable × ×
Serializable × × ×

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

1
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

事务隔离级别越高,数据越安全,但是性能越低