数据库相关概念
名称 | 全称 | 简称 |
数据库 | 存储数据的仓库,数据是有组织的进行存储 | DataBase (DB) |
数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System (DBMS) |
SQL | 操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准 | Structured Query Language (SQL) |
· 主流的关系型数据库管理系统
Oracle、MySQL、SQLServer等
MySQL数据库
· 关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张互相连接的二维表组成的数据库
特点:
1.使用表存储数据,格式统一,便于维护
2.使用SQL语言操作,标准统一,使用方便
· 数据模型
概念:一个系统可以有多个数据库,一个数据库中可以有多张表
SQL
· SQL通用语法
1.SQL语句可以单行或多行书写,以分号结尾
2.SQL语句可以使用空格/缩进来增强语句的可读性
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
4.注释:
· 单行注释:-- 注释内容或#注释内容(MySQL特有)
· 多行注释:/*注释内容*/
·SQL分类
分类 | 全称 | 说明 |
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Mainpulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户,控制数据库的访问权限 |
cmd调用MySQL
mysql -u root -p;
· DDL - 数据库操作
> 查询
查询所有数据库: SHOW DATABASES;
查询当前数据库: SELECT DATABASE();
> 创建
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集]
[ COLLATE 排序规则];
> 删除
DROP DATABASE [ IF EXISTS ] 数据库名;
> 使用
USF 数据库名;
· DDL - 表操作 - 查询
> 查询当前数据库所有表
SHOW TABLES;
> 查询表结构
DESC 表名;
> 查询指定表的建表语句
SHOW CREATE TABLE 表名;
· DDL - 表操作 - 创建
CREATE TABLE 表名(
字段1 字段1类型[ COMMENT 字段1注释 ],
字段2 字段2类型[ COMMENT 字段2注释 ],
字段3 字段3类型[ COMMENT 字段3注释 ],
......
字段n 字段n类型[ COMMENT 字段n注释 ]
) [ COMMENT 表注释 ];
注意:[ ... ]为可选参数,最后一个字段后面没有逗号
· DDL - 表操作 - 数据类型
MySQL中的数据类型有很多,主要分为三类: 数值类型、字符串类型、日期时间类型。
· DDL - 表操作 - 修改
> 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
> 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
> 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
> 删除字段
ALTER TABLE 表名 DROP 字段名;
> 修改表名
ALTER TALBE 表名 RENAME TO 新表名;
> 删除表
DROP TALBE [IF EXISTS] 表名;
> 删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
注意:在删除表时,表中的全部数据也会被删除。
· DML - 添加数据(INSERT)
> 给指定字段添加数据
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...);
> 给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...);
> 批量添加数据
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...), (值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES (值1,值2,...), (值1,值2,...);
注意:
· 插入数据时,指定的字段顺序需要与值得顺序是一一对应的。
· 字符串和日期型数据应该包含在引号中。
· 插入的数据大小,应该在字段的规定范围内。
· DML - 修改数据(UPDATA)
UPDATA 表名 SET 字段名1 = 值1, 字段名2 = 值2, ..... [WHERE 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
· DML - 删除数据 (DELETE)
DELETE FROM 表名 [WHERE 条件];
注意:
· DELETE语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
· DELETE语句不能删除某一个字段的值(可以使用UPDATA)。
· DQL - 编写顺序
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
· DQL - 基本查询
> 查询多个字段
SELECT 字段1,字段2,字段3,... FROM 表名;
SELECT * FROM 表名;
> 设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2],... FROM 表名;
> 取出重复记录
SELECT DISTINCT 字段列表 FROM 表名;
· DQL - 条件查询 (WHERE)
> 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
> 条件
· DQL - 聚合函数(count、max、min、avg、sum)
> 介绍
将一列数据作为一个整体,进行纵向计算
> 常见聚合函数
> 语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:null值不参与所有聚合函数运算。
· DQL - 分组查询(GROUP BY)
> 语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
> where和having区别
· 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是 分组之后对结果进行过滤。
· 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意:
· 执行顺序:where > 聚合函数 > having。
· 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
· DQL - 排序查询(ORDER BY)
> 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
> 排序方式
· ASC: 升序(默认值)
· DESC: 降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
· DQL - 分页查询(LIMIT)
> 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意:
· 起始索引从0开始,起始索引 = (查询页码 -1) * 每页显示记录数。
· 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
· 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
· DQL - 执行顺序
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
· DCL - 管理用户
> 查询用户
USE mysql;
SELECT * FROM user;
> 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
> 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
> 删除用户
DROP UESR '用户名'@'主机名';
注意:
· 主机名可以使用%通配。
· 这类SQL开发人员操作的比较少,主要是DBA(Database Administrator 数据库管理员)使用。
· DCL - 权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
> 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
> 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
> 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意:
· 多个权限之间,使用逗号分隔。
· 授权时,数据库名和表名可以使用*进行通配,代表所有。
函数
· 字符串函数
MySQL种内置了很多字符串函数,常用的几个如下:
SELECT 函数(参数);
· 数值函数
常见的数值函数如下:
· 日期函数
常见的日期函数如下:
· 流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率
约束
· 概述
> 概念:约束就是作用于表中字段上的规则,用于限制存储在表中的数据。
> 目的:保证数据库中数据的正确、有效性和完整性。
> 分类:
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
· 外键约束
> 概念
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
> 语法
· 添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键名称) REFERENCES 主表(主表列名);
· 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
> 删除/更新行为
ALTER TALBE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
· 多表关系
> 一对多(多对一)
实现:在多的一方建立外键,指向一的一方的主键
> 多对多
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
> 一对一
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
· 多表查询分类
> 连接查询
内连接:相当于查询A、B交集部分数据
> 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件....;
> 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件....;
外连接:
> 左外连接:查询左表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 连接条件....;
> 右外连接:查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 连接条件....;
自链接:当前表与自身的连接查询,自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 连接条件....;
子查询(嵌套查询)
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
· 根据子查询结果不同,分为:
> 标量子查询(子查询结果为单个值)
常用的操作符:=、<> 、>、 >=、<、<=
> 列子查询(子查询结果为一列)
常用的操作符:IN、NOT IN、ANY、SOME、ALL
> 行子查询(子查询结果为一行)
常用的操作符:=、<>、IN、NOT IN
> 表子查询(子查询结果为多行多列)
常用的操作符:IN
· 根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。
事务
· 事务简介
事务是一组操作的集合,事务会把所有操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
· 事务操作
> 查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0;
> 开启事务
START TRANSACTION 或 BEGIN;
> 提交事务
COMMIT;
> 回滚事务
ROLLBACK;
· 四大特性 ACID
· 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
· 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
· 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立 环境下运行。
· 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
· 并发事务问题
· 事务隔离级别
--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
--设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}