数据库MySQL知识总结
MySQL数据类型
数值类型
字段类型 | 长度(字节) | 取值范围(无符号) |
---|---|---|
tinyint | 1字节 | 0 ~ 255 |
smallint | 2字节 | 0 ~ 65 535 |
mediumint | 3字节 | 0 ~ 16 777 215 |
int | 4字节 | 很大的11位整数 |
bigint | 8字节 | 很大很大的20位整数 |
float | 4字节 | |
double | 8字节 | |
decimal | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 |
注意无符号数和有符号数的范围差别:col_name tinyint(4) unsigned;
表示tinyint的显示长度为4,无符号数 (范围在0-255);
若不显示声明,则默认为有符号,即范围在 -128~127;
字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
字段类型 | 长度(字节) | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
enum的使用
当列值为确定的几个有限值时,可以使用enum,能减少存储空间;
enum实际是使用整数值来存储,且要注意其可选值的顺序,分别是从1到n,index=0是' '值,null值在第一(为null),
eg: alter student add column sex enum("male","female");
查看enum索引个数
select sex+0 from student;
日期类型
字段类型 | 长度(字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
tips:针对值为NULL的情况, 若列值允许为null,用is null(/is not null)进行判断;
null值不能进行运算,可以使用NULL函数将其作为0处理,
eg: SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products
索引
索引类型
- 唯一索引
- 聚集索引
- 普通索引
存储引擎
InnoDB(默认)
支持事务,外键,行级锁。适合写密集,需要事务控制并发的场景;
MyISAM
alter table student engine=myisam;
表级锁,不支持事务外键;
适合读密集操作的表,select count(*)可以直接返回结果,因为有单独存储它的数据结构
InnoDB VS MyISAM1
- InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
- InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;
- InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。
- 而MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
- InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
- Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;
数据库常用函数
1.ifnull
select name,sum(ifnull(score,0)) as cou from student left join score on (student.id=score.stu_id) group by stu_id order by cou desc;
常用MySQL命令行操作:
-
切换至mysql安装目录,mysql -v 查看版本信息 若报错ERROR 1045 (28000): Access denied
for user ‘ODBC’@‘localhost’ (using password: NO)
可在my.ini中,找到里面的[mysqld] 然后在下面加上这句:skip_grant_tables (启动MySQL服务的时候跳过权限表认证) -
create database 创建数据库,
-
use database 选择数据库,
-
show databases 显示所有数据库,
-
select database() 显示当前所在数据库,
-
describe tablename 显示表结构信息
describe student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | smallint(6) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| hobby | tinyint(4) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
- show create table student; 查看建表语句细节(包括存储引擎等)
student | CREATE TABLE `student` (
`id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1889 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show engines; 查看支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
alter table student add column class tinyint after name; 指定新增字段位置
修改字段类型 modfiy 或 change
数据库SQL优化:
一般来说,应该在这些列上创建索引:
- 在经常需要搜索的列上,可以加快搜索的速度;
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
- 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
- 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
union的使用举例
场景:将三张表的数据一次查询出来,每个表分别查询一个字段(不存在的字段,可以用 [’’ newcol]替代 ),sql如下:
select size,'' color,'' resolution from tb_size
union (select '' size,color,'' resolution from tb_color)
union (select '' size,'' color,resolution from tb_resolution) order by size,color,resolution desc
注意:如果上面查询的每张表,都使用的索引,那这么查询 效率还是可以的;不然数据库会全表扫描3次。
视图的作用,和表的区别?
视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。
游标
是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。
存储过程
是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。
事务在spring中的使用
Spring的事务传播行为类型说明
- PROPAGATION_REQUIRED 如果当前没有事务,就新建一个事务。如果已经存在一个事务中,加入到这个事务中。这是最常见的选择。
- PROPAGATION_REQUIRES_NEW 如果当前没有事务,就新建一个事务。如果当前存在事务,就把当前事务挂起,另建一个事务。
- PROPAGATION_NESTED 如果当前存在事务,则在嵌套事务内执行。如果当前没有事务,则执行与 PROPAGATION_REQUIRED 类似的操作。外套的事务异常可使内嵌事务回滚,反之不会。(底层的数据源必须基于 JDBC 3.0 ,并且实现者需要支持保存点事务机制)
- PROPAGATION_SUPPORTS 支持当前事务,如果当前没有事务,就以非事务方式执行。
- PROPAGATION_NOT_SUPPORTED 以非事务方式执行操作,如果当前存在事务,就把当前事务挂起。
- PROPAGATION_NEVER以 非事务方式执行,如果当前存在事务,则抛出异常
- PROPAGATION_MANDATORY 使用当前的事务,如果当前没有事务,就抛出异常。
Spring @Transactional注解
- 首先调用的是AOP代理对象而不是目标对象,首先执行事务切面,事务切面内部通过Transaction Interceptor环绕增强进行事务的增强,即进入目标方法之前开启事务,退出目标方法时提交/回滚事务。
- Spring在处理@Tranasctional注解时,会“proxy”掉当前的类。
- 如果A和B两个类都有@Transactional时,实际上运行的是A的代理类A‘, A,B的代理类B’, B四个类的instance。一个外部服务调用A,实际上是 外部–>A’–>A–>B’–>B这样执行的。而抛出异常的代码实际上是在B‘做的。
- 但是如果是同一个类内部方法直接调用的话,那么就是简单的方法直接调用,即 外部–>A’–>A方法1–>A方法2。 A方法1不会找到A’去调用。于是,“传播”的规则不会生效。
Spring并不直接管理事务,而是提供了多种事务管理器,他们将事务管理的职责委托给Hibernate或者JTA等持久化机制所提供的相关平台框架的事务来实现。 Spring事务管理器的接口是org.springframework.transaction.PlatformTransactionManager,通过这个接口,Spring为各个平台如JDBC、Hibernate等都提供了对应的事务管理器,但是具体的实现就是各个平台自己的事情了。此接口的内容如下:
Public interface PlatformTransactionManager()…{
// 由TransactionDefinition得到TransactionStatus对象
TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;
// 提交
Void commit(TransactionStatus status) throws TransactionException;
// 回滚
Void rollback(TransactionStatus status) throws TransactionException;
}
from 知乎:https://www.zhihu.com/question/20596402/answer/211492971 ↩︎