java技术栈快速复习03_数据库（这里指mysql）

mysql

• 数据库相关概念

  • 什么叫数据库：数据库就是用来存储和管理数据的仓库，我们通常说的数据库就是RDBMS（relational database Management system，关系型数据库管理系统 ）。当然，除此之外还可以通过变量存储在内存上或者文件存储到硬盘上保存数据据
  • 数据库的分类
    • 关系型数据库：使用二维表格来存储数据；
      • Oracle：美国Oracle(甲骨文)公司，用于电信、金融行业，下载免费，服务收费
      • DB2：美国IBM，主要用于金融领域
      • Sql server：微软，只能用于微软平台
      • Mysql：瑞典NySQL AB开发，属于oracle旗下产品，分为社区版和收费版，使用广泛
    • 非关系型数据库：多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据。
      • ElasticSearch：分布式全文检索引擎
      • MongoDB
      • Redis（Remote Dictionary Server )，即远程字典服务,用于做缓存

• SQL相关概念

  • 什么叫sql？
    • Structured Query Language（结构化查询语言），其实就是定义了操作所有关系型数据库的规则,客户端使用SQL来操作数据库，可以应用到所有关系型数据库中。
    • SQL语言标准由ISO（国际标准化组织）发布，ISO定义了很多SQL标准（例如SQL99）对RDBMS进行统一的操作，相同的语句可以操作Oracle，MySQL。各个数据库厂商有自己的标准，类似于方言，MySQL中的limit。
  • SQL语法：
    • 可以单行或者多行书写，以分号结尾
    • MySQL不区分大小写，建议大写
    • 数据库的注释：
      • 单行注释-- 注释内容；
        • mysql独有#
      • 多行注释/* 注释内容 * /
  • SQL分类：
    • DDL（Data definition language，数据定义语言）：操作数据库和数据表结构，关键字：create、drop、alter
    • DML（Data Manipulation language，数据操作语言）：对数据表中的的内容进行增删改，关键字insert、delete、update
    • DQL（Data query language，数据查询语言）：对数据表中的数据就行查询，关键字：select, where 等
    • TPL（Transaction process language，事务处理语言）：对事务进行处理
    • DCL（Data control language，数据控制语言）：定义数据库访问权限相关（创建用户、用户授权）；关键字：GRANT， REVOKE 等

• DDL-操作数据库/表

  • 操作数据库

    • SHOW DATABASES; 
      CREATE DATABASE mydb1; #创建名字为mydb1数据库
      DROP DATABASE IF EXISTS mydb2;  #如果存在数据库mydb2就删除
      CREATE DATABASE IF NOT EXISTS mydb3;  #如果mydb3数据库不存在，则创建；如果存在，则不创建。
      USE 【数据库名】;
      select database();
      ALTER DATABASE mydb1 CHARACTER SET gbk; #修改数据库的字符集
      ...
      

  • 操作数据表结构

    • 约束

      • 分类	约束	说明
        实体完整性约束	PRIMARY KEY	主键约束
        	PRIMARY KEY AUTO_INCREMENT	主键自动增长
        	UNIQUE	唯一约束
        域完整性约束	NOT NULL	非空约束
        	DEFAULT 【默认值】	默认值
        引用完整性约束/外键约束	FOREIGN KEY(外键列名)	外键约束
        	FOREIGN KEY ON DELETE CASCADE	外键级联删除

    • 数据类型:大致分为三类，分别是数值，字符串（字符），日期时间，这里就说几个常用的

      • 数值：int (主键，整数)、double（浮点）、decamal（金额）

      • 字符串：char(定长字符串，比如身份证，手机号)、varchar(变长字符串，比如用户名，备注)、text（长文本）、blob（二进制）

      • 时间日期：timestamp存储的时间与时区有关，变换时区数据会受影响；timestamp存储时间范围小，1970-01-01 00:00:00到2038-01-19 03:14:07；datetime没有这个限制。所以工作中不要使用timestamp，而推荐datetime。

      • 创建数据表CREATE TABLE 表名

        • CREATE TABLE [IF NOT EXISTS] 表名（
              列名 数据类型 [约束],
              列名 数据类型 [约束],
              列名 数据类型 [约束]       //最后一列的末尾不加逗号
          ）[CHARSET=utf8];            //可根据需要指定表的字符编码集
          
          CREATE TABLE IF NOT EXISTS `user`(
              `uid` INT UNSIGNED AUTO_INCREMENT,
              `name` VARCHAR(100) NOT NULL,
              PRIMARY KEY ( `uid` )
          )ENGINE=InnoDB DEFAULT CHARSET=utf8;
          

      • 删除数据表DROP TABLE 表名;

      • 修改数据表ALTER 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, ...);
      

  • update

    • # WHERE条件是可选的, 如果没有条件, 就修改所有记录, 多数时候我们都加上WHERE条件
      UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ...[WHERE 条件]
      

    • 条件必须是一个boolean类型的值或表达式；

    • 运算符:=、!=、<>、>、<、>=、<=、between…and… 、in(…)、is null、not、or、and；

  • delete

    • # WHERE条件是可选的, 如果没有条件, 就删除所有记录, 多数时候我们都加上WHERE条件
      DELETE FROM 表名 [WHERE 条件];
      

• DQL-数据表数据的查询select

  • 查询语句执行顺序：select、distinct、from、where、group by、having、order by 、limit

  • 单表查询

    • 基本查询

      • 查询所有列SELECT * FROM 表名
      • 查询指定列SELECT 列1 [, 列2, ...列N] FROM 表名;
      • 去重DISTINCT
      • 列运算
        • 算数运算SELECT ename, sal*1.5 FROM emp;
        • 字符串连接运算SELECT ename, CONCAT(sal, '$') FROM emp; # MySQL使用CONCAT()进行连接运算, 不能用“+”连接字符串
        • 转换nullSELECT ename, sal+IFNULL(comm, 0) FROM emp;# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
      • 别名SELECT ename AS '姓名', sal+IFNULL(comm, 0) AS '总工资' FROM emp;# 除了可以给列起别名，也可以给表起别名，在多表查询中会使用到为表起别名

    • 条件查询WHERE

    • 模糊查询like；"_“匹配一个任意字符，只匹配一个字符而不是多个,”%"匹配0~N个任意字符

    • 分组查询group by 列1 [having 筛选分组]

    • 排序order by 列1 asc/desc

    • 分页查询limit 起始行，查询行数

  • 多表查询

    • 合并结果集UNION、UNION ALL

    • 内连接inner join 表名 on 连接条件

      • # 方式1(MySQL特有，不符合SQL标准)
        SELECT 列名 FROM 表1, 表2 WHERE 表1.列名 条件运算符 表2.列名 [AND 条件];
        # 方式2(符合SQL标准)
        SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 条件运算符 表2.列名 [WHERE 条件];
        

    • 外连接

      • 左外连接 LEFT JOIN 表名 on 连接条件

      • 右外连接RIGHT JOIN 表名 ON 连接条件

      • 全外连接:MySQL不支持这种语法，可以使用合并结果集进行模拟全外连接。

        • SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
          UNION
          SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
          

  • 子查询

    • where后

      • SELECT * FROM emp WHERE emp.deptno IN (SELECT DISTINCT deptno FROM emp WHERE emp.job='文员');
        

    • from后

      • # 列出在每个部门名称、员工数量、平均工资
        SELECT
            d.dname, e.cnt, e.avgsal
        FROM
            (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
        WHERE e.deptno=d.deptno;
        

• TPL-事务

  • 什么是事务？一个操作序列，这些操作要么都执行，要么都不执行

  • 使用步骤

    • # 开启事务
      START TRANSACTION;
      
      # 多个SQL操作
      
      # 提交事务
      COMMIT;
      # 回滚
      ROLLBACK;
      
      # 在执行SQL语句之前，先执行START TRANSACTION，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句;要结束事务，COMMIT表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中，或者ROLLBACK，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了；
      

  • 事务原理

    • 数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（COMMIT），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（ROLLBACK）。

  • 事务特性：

    • 原子性（Atomicity）：事务中所有操作作为一个整体，是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败，如果操作失败则不能对数据库有任何影响。。
    • 一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
    • 隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。
    • 持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

  • 事务并发读问题：脏读，不可重复读，幻读。

  • 四大隔离级别： 为了解决事务并发读问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交(READ UNCOMMITTED)、读已提交(READ COMMITTED)、可重复读(REPEATABLE READ)【默认】、串行化(SERIALIZABLE)

    • 隔离级别	脏读	不可重复读	幻读
      读未提交(READ UNCOMMITTED)	可能	可能	可能
      读已提交(READ COMMITTED)	不可能	可能	可能
      可重复读(REPEATABLE READ)(默认)	不可能	不可能	可能
      串行化(SERIALIZABLE)	不可能	不可能	不可能

• DCL-权限控制

  • 创建用户user1，密码设置为1234

    CREATE USER user1@localhost IDENTIFIED BY ‘1234’;

    。。。

• 常用函数

  • 分类	函数	功能
    聚合函数	COUNT	计算个数
    	MAX	最大值
    	MIN	最小值
    	AVG	平均值
    	SUM	和
    字符函数	length(‘字符串’)	获取字符串所占字节数，在utf-8编码下 英文字符占一个字节，中文占三个字节。
    	char_length(‘字符串’)	获取字符个数
    	concat(‘字符串1’,‘字符串2’,‘字符串3’,…)	拼接字符串
    	upper(‘字符串’)、lower(‘字符串’)	大小写转换
    	substr(‘字符串’, 起始位置, [长度])	字符串截取
    	instr(‘字符串’,‘子字符串’)	获取子串第一次出现的索引
    	trim([{BOTH / LEADING / TRAILING} [remstr] FROM] str)	去除字符串前后子字符串
    	ltrim(‘字符串’)	去掉左侧空格
    	rtrim(‘字符串’)	去掉右侧空格
    	lpad(‘字符串’,填充后的总长度,‘填充的字符串’)	左填充
    	rpad(‘字符串’,填充后的总长度,‘填充的字符串’)	右填充
    	replace(‘字符串’， ‘被替换的字符串’， ‘替换之后的字符串’)	字符串替换
    数学函数	round(数字, 小数位数)	四舍五入
    	ceil(数字)	向上取整
    	floor(数字)	向下取整
    	mod(被模数，模数)	取模
    	truncate(数字，截取后小数的位数)	截断小数
    	rand()	随机数
    日期函数	now()	获取当前日期和时间
    	str_to_date(‘日期格式字符串’,‘格式’)	日期格式的字符转换成日期 select str_to_date(‘1-2/2020’, ‘%c-%d/%Y’);
    	date_format(日期, ‘格式’)	日期转换为字符

• 视图

  • 视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

  • 推荐直接使用navicat创建

  • 视图的增删改

    • CREATE VIEW 视图名 AS 查询语句;
      DROP VIEW 视图名;
      ALTER VIEW 视图名 AS 查询语句;
      

• 变量/存储过程/函数：一般不使用，不用学