Java后台之路(02)-MySql

DQL数据准备工作

本章来讲DQL数据库查询语句，这几乎是应用最广的语句。在进行查询之前我们先进行一些数据的准备工作。

分别创建学生表student和雇员表emp，并向它们插入一定的数据。

// 创建学生表student
create table student(
    sid int,
    sname varchar(30),
    age int
);

// 向学生表插入数据
insert into student(sid,sname,age) values
(1,'小一', 21),
(2,'小二', 22),
(3,'小三', 23),
(4,'小四', 24),
(5,'小五', 25),
(6,'小六', 26),
(7,'小七', 27);

// 创建雇员表
CREATE TABLE emp(
    empno INT,
    ename VARCHAR(20),
    job VARCHAR(50),
    hiredate DATE,
    sal DECIMAL(7,2)
);

// 向雇员表插入数据
INSERT INTO emp(empno,ename,job,hiredate,sal) VALUES
(0, '大一', 'Android',  '1992-08-05', 100),
(1, '大二', 'java后台', '1992-08-06', 110),
(2, '大三', 'ui设计师', '1992-08-07', 120),
(3, '大四', 'ios',      '1992-08-08', 130);

通过sql工具SQLYog来查询表;

学生表student

雇员表emp

---

DQL数据查询语言

1. 基本查询

// 查询年龄在23到25岁的学生
select * from student where age>=23 and age<=25;
select * from student where age between 23 and 25;

// 查询年龄是22岁或者24岁的学生
select * from student where age=22 or age=24;

// 查询编号为1到5的学生
select * from student where sid in (1,2,3,4,5);

// 查询除小四/小五以外的学生
select * from student where sname not in ('小四', '小五');

2. 模糊查询

• 模糊查询常使用关键字LIKE；
• 通配符_表示任意一个字符；
• 通配符%表示任意0—N个字符；

// 查询工作为a开头的雇员
select * from emp where job like "A%";

3. 字段控制查询

// distinct来去除重复数据
select distinct * from student;

// 将null值转换为20 将无年龄数据学生的年龄显示为20
select ifnull(age,20) from student;

4. 排序查询

// 按照年龄降序
select * from student order by age desc;

// 按照年龄升序
select * from student order by age asc;

5. 聚合函数

聚合函数用于纵向（即列的运算），它具有自动滤空的，某一个为null就会被自动使其不参与运算。

• count()，统计指定列值不为null的记录行数；
• max()，计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算；
• min()，计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算；
• sum()，计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0；
• avg()，计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0；

例子：

// 雇员有几个人有薪水
select count(sal) from emp;

// 最高的薪水是多少
select max(sal) from emp;

// 最低的薪水是多少
select min(sal) from emp;

// 所有人薪水总和
select sum(sal) from emp;

// 薪水的平均值
select avg(sal) from emp;

6. 分组查询

为了讲解分组，我们将雇员表emp增加一个部门字段；

alter table emp add column deptno varchar(20);

数据是这样的：

来举分组的例子：

// 按照部分去计算各部分的平均工资
select deptno,avg(sal) from emp group by deptno;

7. HAVING子句

HAVING子句用于在GROUP BY分组之后对数据再次筛选。

例子：

// 部门工资高于220
select deptno,sum(sal) from emp group by deptno having sum(sal)>220;

8. LIMIT

LIMIT用来限定查询结果的起始行和总行数，常用于分页。

// 所有记录的2-5行
select * from student limit 2,5;

查询代码的书写顺序和执行顺序

查询语句的书写顺序：
select – from – where – group by – having – order by – limit

查询语句的执行顺序：
from – where – group by – having - select - order by – limit

---

事务Transaction

MySql事务用于处理操作量大，复杂度高的数据。我们举个例子，我们需要在表中删除一整个班级学生的信息，还要删除他们相关的所有数据比如成绩等等，这时候事务处理最合适不过了，在处理数据的时候如果有一个数据处理出错了，我们就放弃所有操作，这就是事务的原子性。

事务的4大特性：

• 原子性，我们把事务中所有操作当做一个整体，要么所有操作成功，要么失败；
• 稳定性，有非法数据时，事务撤回，不影响之前所有的数据；
• 隔离性，事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

在多个线程中各自开启事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。假如，事务不考虑隔离性可能会引发如下问题：

• 赃读，在一个事务中读取了另一个事务未提交的数据；
• 不可重复读，在一个事务内读取表中的某些数据，多次读取结果不同；即一个事务读取到了另一个事务提交后的数据。比如：开启事务后利用SQL语句查询数据，得到结果；立即再次查询得到的结果却与刚才不一样。通俗地说，不可重复读就是：自己的事务还没有提交，又读到了其它事务中执行了upate并提交的数据；
• 虚读，是指在一个事务内读取到了别的事务中执行insert并提交的数据，导致前后读取不一致。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显示地开启一个事务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• begin，显示地开启一个事务；
• commit，提交事务，对数据库所有的修改称为永久性的；
• rollback，回滚事务，结束当前事务，并撤销事务中对数据的所有操作；
• savepoint 节点名称，在事务中创建一个保存节点；
• rollback to 节点名称，把事务回滚到标记点；
• release savepoint 节点名称，删除一个节点；
• set transaction，用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

隔离级别：

• READ UNCOMMITTED，将事务级别设置为该级别后，赃读、不可重复读、虚读都有可能发生；
• READ COMMITTED，将事务级别设置为该级别后，可避免赃读；但是不可重复读、虚读都有可能发生；
• REPEATABLE READ，将事务级别设置为该级别后，避免赃读、不可重复读；但是虚读有可能发生；
• SERIALIZABLE ，将事务级别设置为该级别后，可避免赃读、不可重复读以及虚读。

关于数据库的事务级别，请注意：

• 事务级别越高那么数据操作越安全但是操作性能越低；
• 在MySQL中可利用SELECT @@TX_ISOLATION查看当前的事务隔离级别；
• 在MySQL中可利用SET TRANSACTION ISOLATION LEVEL XXXXX 更改事务隔离级别；
• 应在开启事务之前设置隔离级别。

JDBC中事务的操作：

• connection.setTransactionIsolation(int level)设置隔离级别；
• connection.setAutoCommit(false)开启事务；
• connection.commit( )提交事务；
• connection.rollback( )回滚事务。

---