MySQL 学习笔记

MySQL概述

• 数据库就是一个文件系统,通过标准的SQL语句获取数据
• MySQL是一种关系型数据库,关系型数据库存放的是实体之间的关系
• 常见的关系型数据库:
  
  
  • MySQL
  • Oracle
  • SQLServer
  • DB2
  • SyBase
    
    
    

SQL

• 结构化查询语言
  
  

SQL分类

• DDL:数据定义语言
  
  
  • create,drop,alter...
    
    
• DCL:数据控制语言
  
  
  • grant,if...
    
    
• DML:数据操纵语言
  
  
  • insert,update,delete...
    
    
• DQL:数据查询语言
  
  
  • select
    
    
  
  

对数据库操作

• 创建数据库
  
  
  • create database 数据库名称 [character set 字符集 collate 校对规则];
    
    
• 查看数据库
  
  
  • 查看所有数据库:show databases;
  • 查看某个数据库的定义信息:show create database 数据库名称;
    
    
• 修改数据库
  
  
  • alter database 数据库名称 character set 字符集 collate 校对规则;
  • MySQL中没有直接修改数据库名的语句
    
    
• 删除数据库
  
  
  • drop database 数据库名称;
    
    
• 其他操作
  
  
  • 切换数据库:use 数据库名称;
  • 查看正在使用的数据库:select database();
    
    
  
  

对表进行操作

• 数据类型对比
  
  
  • 注:
    
    
    • char和varchar的区别：char 代表是固定长度的字符或字符串。varchar代表的是可变长度的字符串。
    • datetime和timestamp区别:datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中;timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。
      
      

Java中的类型	SQL中的类型
byte/short/int/long	tinyint/smallint/int/bigint
float	float
double	double
boolean	boolean
char/String	char/varchar
Date	date/time/datetime/timestamp
File	BLOB/TEXT

• 单表约束
  
  
  • 主键约束:primary key,默认是唯一且非空的
  • 唯一约束:unique
  • 非空约束:not null
    
    
• 创建表
  
  
  • create table 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束…);
    
    
• 查看表
  
  
  • 查看数据库下的所有表:show tables;
  • 查看某个表的结构信息:desc 表名;
    
    
• 删除表
  
  
  • drop table 表名;
    
    
• 修改表
  
  
  • 添加列:alter table 表名 add 列名 类型(长度) 约束;
  • 修改列类型,长度和约束:alter table 表名 modify 列名 类型(长度) 约束;
  • 删除列:alter table 表名 drop 列名;
  • 修改列名称:alter table 表名 change 旧列名 新列名 类型(长度) 约束;
  • 修改表名:rename table 表名 to 新的表名;
  • 修改表的字符集:alter table 表名 character set 字符集;
    
    

对记录进行操作

• 添加表的记录
  
  
  • 向表中插入某些列:insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...)
  • 向表中插入所有列:insert into 表名 values(值1,值2,值3...)
  • 注意:
    
    
    • 值的类型与数据库中表列的类型一致
    • 值的顺序与数据库中表列的顺序一致
    • 值的最大长度不能超过列设置最大长度
    • 值的类型是字符串或者是日期类型，使用单引号引起来
      
      
• 修改表的记录
  
  
  • update 表名 set 列名=值,列名=值 [where 条件];
  • 注意:
    
    
    • 值的类型与列的类型一致。
    • 值的最大长度不能超过列设置的最大长度。
    • 字符串类型和日期类型添加单引号。
      
      
• 删除表的记录
  
  
  • delete from 表名 [where 条件];
  • 注意:
    
    
    • 删除表的记录，指的是删除表中的一行记录。
    • 删除如果没有条件，默认是删除表中的所有记录。
      
      
  • 删除表中的记录有2种做法:
    
    
    • delete from 表名;
      
      
      • 删除所有记录,属于DML语句
        
        
    • truncate table 表名;
      
      
      • 删除所有记录,属于DDL语句
        
        
• 查看表的记录
  
  
  • select [distinct] *|列名 from 表名 [条件];
    
    
    • 别名查询:select [distinct] *|列名 [as] 别名 from 表名 [条件];
    • 条件查询:使用where子句
      
      
      • > , < , >= , <= , <> ,=
      • like:模糊查询:使用_或者%作为占位符。_只能代表一个字符，而%可以代表任意个字符。
      • in:范围查询
      • 条件关联:and , or ,not
        
        
    • 排序查询:使用order by 字段名称 asc/desc;
    • 分组统计查询
      
      
      • 聚合函数:

        求和	最大值	最小值	计数	平均值
        sum()	max()	min()	count()	avg()

      • 分组查询:  使用group by 字段名称;
      • 注: where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤（分组后条件过滤）需要使用一个关键字having
        
        
• 总结:
  
  
  • S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);
    
    

多表外键约束

• 用来保证多表间的数据完整性
• 添加外键约束:alter table 原表 add foreign key (表1字段) references 被引用表(表2字段);
• 创建外键约束后,原表在添加记录时,外键的位置可以设置为null,但不能设置为被引用表该字段中不存在的值
• 被引用表可以添加任意的记录,但不能删除被引用的记录
  
  

多表之间的关系

• 一对多
  
  
  • 在多的一方创建外键指向一的一方
    
    
• 多对多
  
  
  • 需要创建中间表,中间表中至少两个字段，分别作为外键指向多对多双方的主键
    
    
• 一对一
  
  
  • 唯一外键对应:假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique
  • (不推荐使用)主键对应:将两个表的主键建立对应关系即可
    
    

多表查询

• 连接查询
  
  
  • 交叉连接 cross join(任意两表之间均可查询)
    
    
    • select * from 表1 cross join 表2;
    • select * from 表1,表2;
      
      
  • 内连接 inner join(inner可省略)(存在外键约束的两表之间可以查询)
    
    
    • 根据关联条件去匹配两个表中的记录,如果匹配成功则返回记录,否则不返回
    • 显式内连接:select * from表1 inner join 表2 on 关联条件;
    • 隐式内连接(较常用):select * from 表1,表2 where 关联条件;
      
      
  • 外连接 outer join(outer 可省略)(存在外键约束的两表之间可以查询)
    
    
    • 左外连接: select * from 表1 left outer join 表2 on 关联条件;
      
      
      • 以左表为基准,根据关联条件去右表查询,如果匹配成功则返回记录,否则返回null
        
        
    • 右外连接:select * from 表1 right outer join 表2 on 关联条件;
      
      
      • 以右表为基准,根据关联条件去左表查询,如果匹配成功则返回记录,否则返回null
        
        
  • 内连接与外连接的区别
    
    
    • 内连接查询出的是两个表的交集
    • 外连接查询的是左表或右表的全部加上交集
      
      
• 子查询
  
  
  • 带in的子查询
  • 带exists的子查询
  • 带any的子查询
  • 带all的子查询
    
    

事务概述

• 指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败
• 开启事务:start transaction;
• 提交事务:commit;
• 回滚事务:rollback;
  
  

事务的特性(ACID特性)

• 原子性：事务的不可分割，组成事务的各个逻辑单元不可分割
• 一致性：事务执行的前后，数据完整性保持一致
• 隔离性：事务执行不应该受到其他事务的干扰
• 持久性：事务一旦结束，数据就持久化到数据库中
  
  

事务的隔离

• 隔离性：一个事务的执行,不应该受到其他事务的干扰;如果不考虑隔离性（一个事务执行受到其他的事务的干扰）,引发一些安全问题,主要体现在读取数据上：
  
  
  • 脏读:一个事务读到了另一个事务未提交的数据，导致查询结果不一致
  • 不可重复读:一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
  • 虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致
    
    
• 事务的隔离级别
  
  
  • read uncommitted:脏读,不可重复读,虚读都有可能发生
  • read conmmitted:避免脏读,但是不可重复读和虚读是有可能发生
  • repeatable read:避免脏读和不可重复读,但是虚读有可能发生
  • serializable:避免脏读,不可重复读,虚读
  • 查看隔离级别:select @@_tx isolation
  • 设置隔离级别:SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
    
    

MySQL 学习笔记

MySQL概述

• 数据库就是一个文件系统,通过标准的SQL语句获取数据
• MySQL是一种关系型数据库,关系型数据库存放的是实体之间的关系
• 常见的关系型数据库:
  
  
  • MySQL
  • Oracle
  • SQLServer
  • DB2
  • SyBase
    
    
    

SQL

• 结构化查询语言
  
  

SQL分类

• DDL:数据定义语言
  
  
  • create,drop,alter...
    
    
• DCL:数据控制语言
  
  
  • grant,if...
    
    
• DML:数据操纵语言
  
  
  • insert,update,delete...
    
    
• DQL:数据查询语言
  
  
  • select
    
    
  
  

对数据库操作

• 创建数据库
  
  
  • create database 数据库名称 [character set 字符集 collate 校对规则];
    
    
• 查看数据库
  
  
  • 查看所有数据库:show databases;
  • 查看某个数据库的定义信息:show create database 数据库名称;
    
    
• 修改数据库
  
  
  • alter database 数据库名称 character set 字符集 collate 校对规则;
  • MySQL中没有直接修改数据库名的语句
    
    
• 删除数据库
  
  
  • drop database 数据库名称;
    
    
• 其他操作
  
  
  • 切换数据库:use 数据库名称;
  • 查看正在使用的数据库:select database();
    
    
  
  

对表进行操作

• 数据类型对比
  
  
  • 注:
    
    
    • char和varchar的区别：char 代表是固定长度的字符或字符串。varchar代表的是可变长度的字符串。
    • datetime和timestamp区别:datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中;timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。
      
      

Java中的类型	SQL中的类型
byte/short/int/long	tinyint/smallint/int/bigint
float	float
double	double
boolean	boolean
char/String	char/varchar
Date	date/time/datetime/timestamp
File	BLOB/TEXT

• 单表约束
  
  
  • 主键约束:primary key,默认是唯一且非空的
  • 唯一约束:unique
  • 非空约束:not null
    
    
• 创建表
  
  
  • create table 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束…);
    
    
• 查看表
  
  
  • 查看数据库下的所有表:show tables;
  • 查看某个表的结构信息:desc 表名;
    
    
• 删除表
  
  
  • drop table 表名;
    
    
• 修改表
  
  
  • 添加列:alter table 表名 add 列名 类型(长度) 约束;
  • 修改列类型,长度和约束:alter table 表名 modify 列名 类型(长度) 约束;
  • 删除列:alter table 表名 drop 列名;
  • 修改列名称:alter table 表名 change 旧列名 新列名 类型(长度) 约束;
  • 修改表名:rename table 表名 to 新的表名;
  • 修改表的字符集:alter table 表名 character set 字符集;
    
    

对记录进行操作

• 添加表的记录
  
  
  • 向表中插入某些列:insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...)
  • 向表中插入所有列:insert into 表名 values(值1,值2,值3...)
  • 注意:
    
    
    • 值的类型与数据库中表列的类型一致
    • 值的顺序与数据库中表列的顺序一致
    • 值的最大长度不能超过列设置最大长度
    • 值的类型是字符串或者是日期类型，使用单引号引起来
      
      
• 修改表的记录
  
  
  • update 表名 set 列名=值,列名=值 [where 条件];
  • 注意:
    
    
    • 值的类型与列的类型一致。
    • 值的最大长度不能超过列设置的最大长度。
    • 字符串类型和日期类型添加单引号。
      
      
• 删除表的记录
  
  
  • delete from 表名 [where 条件];
  • 注意:
    
    
    • 删除表的记录，指的是删除表中的一行记录。
    • 删除如果没有条件，默认是删除表中的所有记录。
      
      
  • 删除表中的记录有2种做法:
    
    
    • delete from 表名;
      
      
      • 删除所有记录,属于DML语句
        
        
    • truncate table 表名;
      
      
      • 删除所有记录,属于DDL语句
        
        
• 查看表的记录
  
  
  • select [distinct] *|列名 from 表名 [条件];
    
    
    • 别名查询:select [distinct] *|列名 [as] 别名 from 表名 [条件];
    • 条件查询:使用where子句
      
      
      • > , < , >= , <= , <> ,=
      • like:模糊查询:使用_或者%作为占位符。_只能代表一个字符，而%可以代表任意个字符。
      • in:范围查询
      • 条件关联:and , or ,not
        
        
    • 排序查询:使用order by 字段名称 asc/desc;
    • 分组统计查询
      
      
      • 聚合函数:

        求和	最大值	最小值	计数	平均值
        sum()	max()	min()	count()	avg()

      • 分组查询:  使用group by 字段名称;
      • 注: where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤（分组后条件过滤）需要使用一个关键字having
        
        
• 总结:
  
  
  • S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);
    
    

多表外键约束

• 用来保证多表间的数据完整性
• 添加外键约束:alter table 原表 add foreign key (表1字段) references 被引用表(表2字段);
• 创建外键约束后,原表在添加记录时,外键的位置可以设置为null,但不能设置为被引用表该字段中不存在的值
• 被引用表可以添加任意的记录,但不能删除被引用的记录
  
  

多表之间的关系

• 一对多
  
  
  • 在多的一方创建外键指向一的一方
    
    
• 多对多
  
  
  • 需要创建中间表,中间表中至少两个字段，分别作为外键指向多对多双方的主键
    
    
• 一对一
  
  
  • 唯一外键对应:假设是一对多,在多的一方创建外键指向一的一方的主键,将外键设置为unique
  • (不推荐使用)主键对应:将两个表的主键建立对应关系即可
    
    

多表查询

• 连接查询
  
  
  • 交叉连接 cross join(任意两表之间均可查询)
    
    
    • select * from 表1 cross join 表2;
    • select * from 表1,表2;
      
      
  • 内连接 inner join(inner可省略)(存在外键约束的两表之间可以查询)
    
    
    • 根据关联条件去匹配两个表中的记录,如果匹配成功则返回记录,否则不返回
    • 显式内连接:select * from表1 inner join 表2 on 关联条件;
    • 隐式内连接(较常用):select * from 表1,表2 where 关联条件;
      
      
  • 外连接 outer join(outer 可省略)(存在外键约束的两表之间可以查询)
    
    
    • 左外连接: select * from 表1 left outer join 表2 on 关联条件;
      
      
      • 以左表为基准,根据关联条件去右表查询,如果匹配成功则返回记录,否则返回null
        
        
    • 右外连接:select * from 表1 right outer join 表2 on 关联条件;
      
      
      • 以右表为基准,根据关联条件去左表查询,如果匹配成功则返回记录,否则返回null
        
        
  • 内连接与外连接的区别
    
    
    • 内连接查询出的是两个表的交集
    • 外连接查询的是左表或右表的全部加上交集
      
      
• 子查询
  
  
  • 带in的子查询
  • 带exists的子查询
  • 带any的子查询
  • 带all的子查询
    
    

事务概述

• 指的是逻辑上的一组操作,组成这组操作的各个逻辑单元,要么全都成功,要么全都失败
• 开启事务:start transaction;
• 提交事务:commit;
• 回滚事务:rollback;
  
  

事务的特性(ACID特性)

• 原子性：事务的不可分割，组成事务的各个逻辑单元不可分割
• 一致性：事务执行的前后，数据完整性保持一致
• 隔离性：事务执行不应该受到其他事务的干扰
• 持久性：事务一旦结束，数据就持久化到数据库中
  
  

事务的隔离

• 隔离性：一个事务的执行,不应该受到其他事务的干扰;如果不考虑隔离性（一个事务执行受到其他的事务的干扰）,引发一些安全问题,主要体现在读取数据上：
  
  
  • 脏读:一个事务读到了另一个事务未提交的数据，导致查询结果不一致
  • 不可重复读:一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
  • 虚读/幻读:一个事务读到了另一个事务已经提交的insert的数据,导致多次查询结果不一致
    
    
• 事务的隔离级别
  
  
  • read uncommitted:脏读,不可重复读,虚读都有可能发生
  • read conmmitted:避免脏读,但是不可重复读和虚读是有可能发生
  • repeatable read:避免脏读和不可重复读,但是虚读有可能发生
  • serializable:避免脏读,不可重复读,虚读
  • 查看隔离级别:select @@_tx isolation
  • 设置隔离级别:SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;