Java基础知识总结梳理(八)
mysql基础知识梳理
参考菜鸟教程:https://www.runoob.com/mysql/mysql-tutorial.html
数据库
- 创建
- create database 表名
- 查看、删除数据库
- show databases
- drop database 表名
- 备份恢复数据库
- DOS执行:mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
- Mysql命令行执行:Source 文件名.sql
表
- 创建 : CREATE TABLE table_name()
- 删除
- 修改
CRUD
- Insert
- Update
- Delete
- Select
- 单表
- SELECT [DISTUNCT] *|{column1,column2,column3,…} FROM tablename;
select 指定查询哪些列的数据
column指定列名
*号代表查询所有列
From 指定查询哪张表
DISTINCT可选,指显示结果时,是否去掉重复数据 - 使用表达式对查询的列进行运算
SELECT * | {column1 | expression, column2 | expression, …} FROM tablename; - 在select语句中可使用as语句
SELECT column_name as 别名 from 表名; - 在where子句中经常使用的运算符
比较运算符:
大于、小于、大于(小于)等于、不等于
BETWEEN …AND… 显示在某一区间的值
IN(set) 显示在in列表中的值
LIKE “张pattern” 模糊查询
IS NULL 判断是否为空
逻辑运算符:
and 多个条件同时成立
or 多个条件任一成立
not 不成立 - 使用order by子句排序查询结果
SELECT column1, colum2, column3,… FROM table; order by column asc|desc, …
- SELECT [DISTUNCT] *|{column1,column2,column3,…} FROM tablename;
- 查询增强
- 分页查询
select … limit start, rows
- 分页查询
- 多表
- 自连接
自连接是指在同一张表的连接查询【将同一张表看作两张表】 - 多行子查询
子查询是指在其他sql语句中的select语句,也叫嵌套查询
all
any - 子查询作为临时表
- 多列子查询
多列子查询是指查询返回多个列数据的子查询语句 - 合并查询
union
union all
- 自连接
函数
- 统计函数
- Count 返回行的总数
- Sum函数返回满足where条件的行的和–一般使用在数值列
- AVG函数返回满足where条件的一列的平均值
- MAX/min函数返回满足where条件的一列的最大/最小值
- group by子句对列进行分组
- having子句对分组后的结果进行过滤
- 时间日期
- CURRENT_DATE() 当前日期
- CURRENT_TIME() 当前时间
- CURRENT_TIMESTAMP() 当前时间戳
- DATE(datetime) 返回datetime的日期部分
- DATE_ADD(date2,INTERVAL d_value d_type) 在date2中加上日期或时间
- DATE_SUB(date2,INTERVAL d_value d_type) 在date2上减去一个时间
- DATEDIFF(date1,date2)两个日期差(结果是天)
- TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)
- NOW() 当前时间
- YEAR|Month|DATE(datetime) FROM_UNIXTIME() 年 月 日
- 字符串函数
- CHARSET(str) 返回字符串字符集
- CONCAT(string2 [,…]) 连接字符
- UCASE(string2) 转换成大写
- LCASE(string2) 转换成小写
- LEFT(string2,length)从string2中的左起取length个字符
- LENGTH(string) string长度【按照字节】
- REPLACE(str,search_str,replace_str)在str中用replace_str替换search_str
- STRCMP(string1,string2)逐字符比较两字符串大小
- SUBSTRING(str,position [.length])从str的position开始【从1开始计算】取length个字符
- LTRIM(string2) RTRIM(string2) trim 去除前端空格或后端空格
- 数学函数
- ABS(num) 绝对值
- BIN(decimal_number) 十进制转二进制
- CONV(number2,from_base,to_base) 进制转换
- FLOOR(number2)向下取整,得到比num2小的最大整数
- FORMAT(number,decimal_palces)保留小数位数
- HEX(DecimalNumber) 转十六进制
- LEAST(number,number2,[…]) 求最小值
- MOD(numerator,denominator) 求余
- RAND([seed]) 其范围为0<=v<=1.0
- 加密函数和系统函数
- USER() 查询用户
- DATEBASE() 数据库名称
- MD5(str) 为字符串算出一个MD5 32的字符串,(用户密码)加密
- 流程控制
- IF(expr1,expr2,expr3)如果expr1为True,则返回expr2否则返回expr3
- IFNULL(expr1,expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2
- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;【类似于多重分支】如果expr1为TRUE,则返回expr2,如果expr2为t,返回expr4,否则返回expr5
内连接
外连接
- 左外连接:select … from 表1 left join 表2 on 条件
- 右外连接:select … from 表1 right join 表2 on 条件
约束
- not null
- 字段名 字段类型 not null
- primary key
- 方式一:字段名 字段类型 primary key
用于唯一的标识表行的数据,当定义主键约束后,改列不能重复,不能为空 - 方式二:在表定义最后写 primary key(列名);
- unique
- 字段名 字段类型 unique
- 当定义了唯一约束后,该列值是不能重复的
- foreign key
- 用于定义主表和从表之间的关系:外键约束要定义在从表上,主表必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的外键列存在或是null
- 外键指向的表的字段,要求时primary key或者是unique
- 表的类型是innodb,这样的表才支持外键
- 外键字段的类型要和主键字段类型一致(长度可以不同)
- 外键字段的值,必须在主键字段中出现过,或者为null【前提是外键字段允许为null】
- 一旦建立主外键的关系,数据就不能随意删除了
- 自增长
- 字段名 整型 primary key auto_increment
- 一般来说自增长是和primary key 配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型的
- 自增常默认从1开始,可以通过命令修改:alter table 表名 auto_increment = 新的开始值;
- 如果添加数据值,给自增长字段(列)指定的有值,则以指定的值为准,如果指定了自增长,一般来说,就按照自增长的规则来添加数据
索引
- 索引原理
参考博客:索引原理详解 - 主键索引
- 唯一索引
- 普通索引
- 全文索引
事务
- 什么是事务
- 事务用于保证数据的一致性,它由一组相关的dml语句(数据操作语言)组成,该组的dml语句要么全部成功,要么全部失败
- 事务操作
- 开始一个事务 start transaction
- 设置保存点 savepoint 保存点名
- 回退事务 rollback to 保存点名
- 回退全部事务 rollback
- 提交事务,所有操作生效,不能回退 commit
- 事务注意事项
- 如果是不开始事务,默认情况下,dml操作是自动提交的,不能回滚
- 如果开始了一个事务,没有创建保存点,可以执行rollback,默认回退到事务开始的状态
- 可以在这个事务中(还没有提交时),创建多个保存点,比如savepint aaa;执行dml,savepoint bbb
- 可以在事务没有提交前,选择回退到哪个保存点
- mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使
- 开始一个事务start transaction,set autocommit = off
- 事务隔离级别
不考虑隔离性,可能引发的问题
脏读:当一个事务读取到另一个事务尚未提交的修改时,产生脏读
不可重复读:同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复读
幻读:同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读
- 读未提交Read uncommitted 可能发生脏读、不可重复读、幻读
- 读已提交Read committed 可能发生不可重复读、幻读
- 可重复读 Repeatable read
- 可串行化 Serializable
- ACID特性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另一个一致性状态 - 隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离 - 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
mysql表类型和存储引擎
- 存储引擎
- MyISAM
MyISAM不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求 - innoDB
innoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全,但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引 - Memory
MEMORY存储引擎实用存在内存中的内容来创建表,每个MEMORY表只实际对应一个磁盘文件,MEMORY类型的表访问非常的快,因为它的数据是放在内存中,并且默认使用HASH索引,但是一个Mysql服务关闭,表中的数据就会丢失掉,表的结构还在
- 表类型
- CSV
- Memory
- ARCHIVE
- MRG_MYISAM
- MYISAM
- innoDB
视图
- 视图是一个虚拟表,其内容由查询定义,同真实的表一样,视图包含列,其数据来自对应的真实表(基表)
参考内容:
【1】https://www.bilibili.com/video/BV1fh411y7R8?spm_id_from=333.337.search-card.all.click&vd_source=dd9bdf6f42e38406b07ef92b986e8171