MySQL复习

根据秦疆老师课堂笔记自己整理

1.MySQL

MySQL中不区分大小写

操作数据库时，如果表明或者字段名是一个特殊字符，就要带''，例如USE 'school'

数据库的列类型

tiny int（1字节），smallint（2字节），mediumint（3字节），int（4字节），bigint（8字节），float（4字节），double（8字节），decimal（字符串形式浮点数）。char(0-255)，varchar(0-65535)，tinytext（2^8-1，微型文本），text（2^16-1，大文本），timestamp（时间戳，从1970.1.1到现在的ms数），不要使用NULL进行运算，结果一定为NULL！

数据引擎INNODB和MYISAM的区别

INNODB支持事务，数据行锁定，外键约束。但是不支持全文索引，并且表空间的大小约为MYISAM的两倍

在物理文件上，INNODB只有*.frm表结构定义文件和*.ibd，而MYISAM有*.frm、*.MYD（数据文件）和*.MYI（索引文件）

所有数据库文件都存在data目录下，数据的存储本质还是文件的存储！

 

所有的创建和删除表的操作尽量加上IF条件判断，否则会报错

CRUD DQL语言

增加：

INSERT INTO 'student' ('name', 'pwd', 'gender') VALUES ('晏楚', '123456', '1'), ('晏楚', '456789', '0')

修改：

UPDATE 'student' SET 'name'='晏楚2', 'pwd'=111111, 'gender'=1 WHERE id='1'

删除:

DELETE FROM 'student' WHERE 'id'='1'

查询：

SELECT ‘studentno’ as 'stno', 'studentname' FROM 'student' WHERE 'studentname' LIKE '晏%'

完整的SELECT SQL

 

delete和、truncate、drop的区别

delete和truncate只删除表数据不删除表结构

处理速度上来说 drop>truncate>delete

delete是dml(Data Manipulation Language)语言，可以有事务回滚，而truncate是ddl(Data Definition Language)语言，不能事务回滚

联表查询

使用JOIN ON判断交叉条件，使用WHERE判断表内条件

INNER JOIN	只要两个表有一个表有匹配的记录就返回行
LEFT JOIN	以左表为主，返回左表全部的记录，即使没有和右表匹配
RIGHT JOIN	以右表为主，返回右表的全部记录，即使没有和左表匹配

分页和排序

-- 降序排序
ORDER BY 'studentid' DESC 
-- 升序排序
ORDER BY 'studentid' ASC
​
-- 参数分别为当前开始位置，该页显示个数
LIMIT 0,5

子查询

快于连表查询，由内而外

使用聚合函数就不能只查询一条记录

事务

MySQL是默认开启事务的

手动提交事务

SET autocommit= 0 -> START TRANSACTION -> INSERT INTO 'student' ... VALUES, DELETE FROM 'student' WHERE ..., UPDATE 'student' SET ...=..., SELECT 'studentid' from 'student' -> COMMIT -> （如果失败） ROOLBACK -> SET autocommit = 1

ACID（Automic， Consistency，Isulation, Duration）

原子性：事务要么都成功要么都失败，不可分割

一致性：事务前后不改变数据的总状态

隔离性：事务之间的操作不相互影响

持久性：一旦事务被提交就被持久化到数据库中，不可逆

• 事务的隔离级别

事务高并发下会有以下问题：脏读（事务未提交而被另外一个线程使用了部分数据），不可重复读（一个线程读取另外一个线程修改前和修改后的数据，导致两次读取的数据结果不一致）、幻读（一个线程读取另外一个线程增加记录前后的数据，导致第二次多出来额外的数据）、丢失修改（两个线程同时对一行记录的int字段20执行-1操作，最后该字段是19而不是18）

基于上述事务并发出现的问题，事务有四种隔离级别分别是：

读取未提交（READ-UNCOMMITTED），读取提交（READ-COMMITTED），可重复读（REPETABLE-READ），可串行化（SERIALIZABLE）

各事务隔离级别能防止的问题

	脏读	可重复读	幻读
READ-UNCOMMITTED	×	×	×
READ-COMMITTED	√	×	×
REPETABLE-READ	√	√	×
SERIALIZABLE	√	√	√

一般的搜索引擎的默认事务隔离级别都是可重复度（REPETABLE-READ），而INNODB引擎虽然隔离级别是REPETABLE-READ，但是它却由于某些锁（Next-key Lock）的机制而能防止高并发下的幻读问题而不损失任何性能，这就是它牛逼的地方。

索引

index是帮助MySQL高效获取数据的数据结构

只有在特定的搜索引擎下才有全文索引（FullText Index），例如MYISAM搜索引擎

对不需要经常改动的字段上可以添加索引，格式为

CREATE INDEX id_studentname on student('studentname');

数据库三大范式（Normal Form）

1NF：一张表的每一列都有不可再分的原子性

2NF：在1NF的基础上，每张表只能描述一件事情，该表的每个字段都和主键相关

3NF： 在1NF和2NF的基础上，该表的每个字段都和主键唯一相关，而不能间接相关

关于三大范式的应用

• 一张表不得关联超过三张表

• 出于商业化的考虑，建立数据库中适当考虑三大范式，但是也要追求高性能，例如在一张表中故意增加和该表主键不相关的字段，有的情况下这样可以节省另外一张表的创建，又比如CREATE INDEX 'id_studentname' ON 'student'('studentname')，故意增加字段列也即是索引这种数据结构来提高查询的速度。

JDBC

应用程序->数据库驱动->数据库（驱动有显卡、声卡、数据库驱动等）

应用程序通过数据库驱动来和数据库打交道！

在应用程序和数据库驱动之间加了一层JDBC，数据库驱动厂商遵守JDBC规范，而我们只需要掌握JDBC的功能API即可！

 

JDBC默认数据源工具类

• 在db.properties中分别配置driver、url、username、password

• JDBCUtils.class.getClassLoader().getResourceAsStream(db.properties)返回InputStream对象in

• 加载驱动,直接使用Properties类的对象properties，properties.load(in)，properties.getProperty("driver")返回String对象driver，Class.forName(driver)此处是加载驱动，不用返回对象

• 驱动连接数据库，获取代表数据库的对象，properties.getProperty("url/username/password/driver")，DriverMannager.getConnection(url, username, password)返回Connection对象connection

• 获取数据库执行sql的Statement对象statement，connection.createStatement()返回Statement对象statement

• 正常写sql，例如String sql = "select 'studentno', 'studentname' from 'student' where 'studentname' = '晏楚'";

• 执行sql并，sql.executequery(sql)返回ResultSet对象resultset

• 对resultset进行遍历，并且把值都传递给POJO类中

  while(result.next())

  {

  System.out.println(resultset.getString("studentno"));

  System.out.println(resultset.getString("studentname"));

  }

• 关闭三个使用的对象，connection.close();、statement.close()、resultset().close()。