简介

关系数据库：使用表将数据组织起来，代表：Oracle、DB2、MySQL、SQL Server

关系型数据库缺点：数据模型、性能、拓展伸缩性的缺点。

热门领域：非关系型数据库

出色的 NoSQL 数据库：

键值存储数据库：Oracle BDB，Redis，BeansDB
列式储数数据库：HBase，Cassandra，Riak
文档型数据库：MongoDB，CouchDB
图形数据库：Neo4J，InfoGrid，Infinite Graph

MySQL：开源，体积小，速度快，适用于中小型企业领域

SQL：Structured Query Language。

SQL 包含6个部分：

数据查询语言DQL select, where, order by, having,
数据操作语言DML insert update delete
事务处理语言TCL 确保DML语句能及时更新，Begin transaction commit rollback
数据控制语言DCL
数据定义语言DDL create drop
指针控制语言CCL

为了提高可读性，一般关键字大写，其他小写。

安装 mysql

MySQL的默认端口是:3306

数据库默认字符集就是utf8

安装完 mysql 要配置环境

设置初始密码：

1	alter user user() identified by "123456";

/usr/local/mysql/lib 将 mysql 环境变量添加到 .bash_profile 中以保证全局可用

连接数据库

安装 navicat

方式2：在命令行中：找到安装目录下的bin录制中有个mysql的命令

格式:mysql -u账户 -p密码 -h数据库服务器安装的主机 -P数据库端口

mysql -uroot -padmin -hlocalhost -P 3306

若连接的数据库服务器在本机上，并且端口是3306。

则可以简写： mysql -u root -p admin

操作数据库流程：
启动服务器 — 客户端身份验证 — 选择数据库 — 选择表 — 操作表

mysql 数据库

系统数据库(系统自带的数据库)：不能修改

infomation_schema 存储数据库对象信息，如：用户表信息，列信息，权限，字符，分区等信息。

performance_schema 存储数据库服务器性能参数信息

mysql 存储用户权限信息

test 测试数据库

MySQL 安装文件夹结构

bin MySQL 相关二进制文件存储目录，存放 MySQL 提供的系统工具：mysql;mysqld;mysqldump;
data: 默认的数据存放目录
include: 包含一些头文件，提供其他程序连接 mysql 的 API 接口
lib: MySQL 依赖的库
share: 存放几个初始表文件和每种语言的 errmsg.sys 错误日志
sql-bench: 对数据库进行压力测试工具
my.ini: MySQL 的基础配置文件，

my.ini

MySQL 服务的属性

port 启动端口
basedir MySQL的主目录
datadir MySQL数据文件存放的位置，默认是在MySQL主目录的data文件夹下
character-set-server 默认的字符编码
default-storage-engine 默认的存储引擎
max_connections 最大允许连接数

MySQL数据文件简介(存储数据的文件):

数据文件存放格式，注意MyISAM和InnoDB的文件不同格式；
常见的文件类型：
- .frm：与表相关的元数据（meta）信息，比如表结构定义；所有表都需要这个文件；
- .MYD(MyISAM Data)：MyISAM 存储引擎专用，存放MyISAM 表的数据；
- .MYI(MyISAM Index)：MyISAM 存储引擎专用，主要存放MyISAM 表的索引相关信息；
- .ibd和ibdata：存放InnoDB数据；
- ibdata1：共享存储空间，相当于所有InnoDB的数据都存放在公共的这个ibdata1文件中；
- .ibd：独享存储空间，相当于每一个表的数据还是保存在自己独立的文件中，可以通过innodb_file_per_table=1参数来设置，但是独立表空间和共享表空间不能同时存在；
- db.opt：该数据库的默认配置；

数据库常见操作

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

use mysql;

1	Database changed

show tables;
create database 数据库名称;
drop database 数据库名称;

MySQL 中的数据用各种不同的技术存储在文件或者内存中，每种技术都使用了不同的存储机制、索引技巧、锁定水平且最终提供不同的功能和能力

技术表如下：

MyISAM 和 InnoDB 各自的优缺点
MyISAM 有较高的插入，查询速度，但是不支持事务，不支持外键
InnoDB 支持事务和外键，支持行级锁定，性能较低。

InnoDB 存储引擎提供了具有提交、回滚和炳奎恢复能力和事务安全，但对比 MyISAM 来说处理效率差，会占用更多的磁盘空间保留数据和索引。

不能没有事务，一般选择 InnDB

MySql 列的常见类型

整数类型

一般就是 INT

小数类型

Decimal，高精度类型，金额货币有限选择。

字符类型

字符型一定要指定位宽；varchar(20)

比较 char(5) 和 varchar(5) 的区别

日期类型

二进制类型

存放图形、声音和影响，二进制对象，0-4GB

BIT 我们一般存储0或1，其他的大二进制类型，开发中一般不用

三范式的约束

第一范式：关系模式的基本要求，不满足第一范式就不是关系数据库，数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值
第二范式：要求数据库表中的每个实例或行必须可以被唯一的区分，有主键
第三范式：要求数据库表中不包含其它表中的非主键信息(设计表的时候，为了性能，有时会打破第三范式)

操作表

建表

create table `student`(
`id` bigint(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL) engine=InnoDB DEFAULT charset=utf8;

方式二：

create table student(
id bigint,
name varchar(20),
age int);
Query OK, 0 rows affected

表名的问题：使用标识符时不要用 SQL 的关键字，如果一定要用，
使用反引号()把自定义的标识符引起来:table`
或者使用表名作为前缀，t_table

查看表信息

desc student

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

show create table 表名

mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表的约束

非空约束： not null (NK)
设置默认值：default
唯一：unique(uk)
主键：primary key(pk)，非空且唯一，auto_increment，从1开始步长为1，自增长
外键约束：foreign key(fk), A表中的外键列的值必须参照B表中国的某一列

主键设计：单列主键，且不参与业务。

1	create table t_student(id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(20), age int DEFAULT 18);

DML

INSERT

1	INSERT INTO `t_student` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '10');

一一对应。时间和字符串一定要用单引号。

可以一次插入多组数据

1	INSERT INTO `t_student` (id, name, age) VALUES(1, 'zhangsan', 10), (1, 'zhangsan', 10), (1, 'zhangsan', 10)...;

增加数据，指数级

1	INSERT INTO user(name) (SELECT name FROM user)

UPDATE

1	UPDATE `t_student` SET `name`=`123`, `age`=`20` WHERE (`id`=`5`);

如果没有 WHERE 全表都会被改。

DELETE

1	DELETE FROM `t_student` WHERE (`id`=`3`);

如果没有 WHERE 全表被删

案例：删除产品表中’有线鼠标’的数据

1	DELETE FROM product WHERE dir_id = (SELECT id FROM productdir WHERE dirName = '有线鼠标');

drop 删除整张表

截断表

truncate table users;

截断表后，truncate 之后，如果有自动主键的话，会恢复成1，所有数据清空，但是字段和表结构都还在。

DQL

1	SELECT {*, column[alias],...} FROM table_name [WHERE...];

查询语句中可运算，DATE 型数据可以使用操作符创建表达式(+ -)

mysql> SELECT id,productName,(salePrice * cutoff) FROM t_product; 
+----+-----------------------+----------------------+
| id | productName           | (salePrice * cutoff) |
+----+-----------------------+----------------------+
|  1 | 罗技M90             |                45.00 |
|  2 | 罗技M100            |                44.10 |
|  3 | 罗技M115            |                59.40 |
|  4 | 罗技M125            |                72.00 |
|  5 | 罗技木星轨迹球 |                64.00 |
|  6 | 罗技火星轨迹球 |               303.63 |
+----+-----------------------+----------------------+
6 rows in set (0.00 sec)

mysql> SELECT id,productName,(salePrice * cutoff) AS tradePrice FROM t_product; 
+----+-----------------------+------------+
| id | productName           | tradePrice |
+----+-----------------------+------------+
|  1 | 罗技M90             |      45.00 |
|  2 | 罗技M100            |      44.10 |
|  3 | 罗技M115            |      59.40 |
|  4 | 罗技M125            |      72.00 |
|  5 | 罗技木星轨迹球 |      64.00 |
|  6 | 罗技火星轨迹球 |     303.63 |
+----+-----------------------+------------+
6 rows in set (0.00 sec)

AS(AS可以省略)

改变表名
表示计算结果的含义
作为列的别名

concat

把多个值以字符串拼接的形式拼在一起，可以使用函数 concat(…)

mysql> SELECT CONCAT(productName,'的售价是',salePrice) poster FROM t_product;
+--------------------------------------+
| poster                               |
+--------------------------------------+
| 罗技M90的售价是90              |
| 罗技M100的售价是49             |
| 罗技M115的售价是99             |
| 罗技M125的售价是80             |
| 罗技木星轨迹球的售价是80  |
| 罗技火星轨迹球的售价是349 |
+--------------------------------------+
6 rows in set (0.00 sec)

日期查询

1 2	-- 查询只查年份 SELECT * FROM emp WHERE YEAR(HIREDATE) = '1981'

日期转换字符串

1	SELECT NOW() FROM DUAL

当前系统时间 NOW()，DUAL 是一个虚表，没有列。

函数名	含义
DAY(date)	获取日期中的天数范围 1-31
HOUR(time)	返回 time 对应的小时数
MINUTE(time)	返回 time 对应的分钟
YEAR(date)	返回日期的月份
LAST_DAY(date)	获取日期的月份最后一天对应的值

日期格式字符串	format对应的格式
%Y	年份，类似yyyy，4位数
%m	月份，数字形式(00…12)
%d	该月日期，数字形式(00…31)
%H	小时(00…23)
%i	分钟，数字形式(00…59)
%s	秒(00…59)

1	SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') FROM dual

过滤查询

比较运算符

1	SELECT * FROM t_product WHERE productName = '罗技M90';

如果需要区分大小写，语法如下：

1 2	# BINARY 表示要区分大小写 SELECT * FROM t_product WHERE BINARY productName = '罗技M90';

有别名时，过滤语句不能使用前面的别名，sql 代码运行顺序如下：

逻辑运算符

AND(&&)

OR(||)

NOT(!)

# 需求，选择id，货物名称，零售价在 300-400
mysql> SELECT id, productName FROM t_product WHERE salePrice > 300 && salePrice < 400;
+----+-----------------------+
| id | productName           |
+----+-----------------------+
|  6 | 罗技火星轨迹球 |
+----+-----------------------+
1 row in set (0.00 sec)

优先级：比较运算符 > NOT > AND > OR

括号跨越所有优先级

范围查询

BETWEEN 运算符显示某一值域范围的记录，最常见的使用在数字类型数据的范围上，对于字符类型数据和日期类型数去一样可以用。

1	SELECT <columnList> FROM table_name WHERE 列名 BETWEEN minValue AND maxValue

BETWEEN 左右两边均为 闭区间

# 需求：选择id，货品名称，零售价在300-400之间的货品
mysql> SELECT id, productName FROM t_product WHERE salePrice BETWEEN 300 AND 400;
+----+-----------------------+
| id | productName           |
+----+-----------------------+
|  6 | 罗技火星轨迹球 |
+----+-----------------------+
1 row in set (0.00 sec)

集合查询

使用 IN 运算符，判断列的值是否在指定集合中

WHERE 列名 IN (值1，值2…);

# 需求：选择id，货物名称，分类编号2或4的所有货品
mysql> SELECT id, productName, dir_id FROM t_product WHERE dir_id IN (2, 4);
+----+-------------+--------+
| id | productName | dir_id |
+----+-------------+--------+
|  2 | 罗技M100  |      2 |
|  4 | 罗技M125  |      4 |
+----+-------------+--------+
2 rows in set (0.00 sec)

空值查询

所有带有空值的任何算术表达式，结果都为空

IS NULL：判断列的值是否为空，空值是指没有值的列

WHERE 列名 IS NULL

mysql> SELECT id, productName FROM t_product WHERE supplier is NOT NULL;
+----+-----------------------+
| id | productName           |
+----+-----------------------+
|  1 | 罗技M90             |
|  2 | 罗技M100            |
|  3 | 罗技M115            |
|  4 | 罗技M125            |
|  5 | 罗技木星轨迹球 |
+----+-----------------------+
5 rows in set (0.00 sec)

IFNULL(arg0, arg1) 如果arg0 不为空，则是arg0，如果 arg0为空就是arg1


SELECT e.ename, (sal + IFNULL(comm, 0)) * 12 yearSalary FROM emp e  

ename	yearSalary
SMITH	9600.00
ALLEN	22800.00
WARD	21000.00
JONES	35700.00
MARTIN	31800.00
BLAKE	34200.00
CLARK	29400.00
SCOTT	36000.00
KING	60000.00
TURNER	18000.00
ADAMS	13200.00
JAMES	11400.00
FORD	36000.00
MILLER	15600.00

空值不等于0，或空格，也不表示空字符串。

!= == 用于字符串

空值判断用 is not null 或者 ifnull(arg0, arg1)

模糊查询

使用 LIKE 运算符执行通配查询，查询条件可以包含字符或数字：

% 通配符：可以表示任意个数的任意字符。
_ 通配符：可以表示任意一个字符

通配符：用来匹配部分值的特殊字符。

# 需求：查询货物名称带有'M'的所有信息
mysql> SELECT id, productName FROM t_product WHERE productName LIKE '%M%';
+----+------------+
| id | p          |
+----+------------+
|  1 | 罗技M90  |
|  2 | 罗技M100 |
|  3 | 罗技M115 |
|  4 | 罗技M125 |
+----+------------+
4 rows in set (0.01 sec)

# 需求：查询匹配货物名称'罗技M9??'的所有信息
mysql> SELECT id, productName FROM t_product WHERE productName LIKE '罗技M__';
+----+-------------+
| id | productName |
+----+-------------+
|  1 | 罗技M90   |
+----+-------------+
1 row in set (0.00 sec)

结果集排序

使用 ORDER BY 将记录排序(先有结果，再排序)

ASC：升序，缺省。 DESC：降序

ORDER BY 子句出现在 SELECT 语句的最后

格式：

SELECT <selecteList> FROM table_name WHERE 条件 ORDER BY 列名1[ASC/DESC], 列名2[ASC/DESC]...;

# 需求：选择id, 货品名称，分类编号，零售价，先按分类编号排序，编号相同再按零售价排序：

mysql> SELECT id, productName, dir_id, salePrice FROM t_product ORDER BY dir_id DESC;
+----+-----------------------+--------+-----------+
| id | productName           | dir_id | salePrice |
+----+-----------------------+--------+-----------+
|  4 | 罗技M125            |      4 |        80 |
|  1 | 罗技M90             |      3 |        90 |
|  3 | 罗技M115            |      3 |        99 |
|  5 | 罗技木星轨迹球 |      3 |        80 |
|  6 | 罗技火星轨迹球 |      3 |       349 |
|  2 | 罗技M100            |      2 |        49 |
+----+-----------------------+--------+-----------+
6 rows in set (0.00 sec)
 
mysql> SELECT id, productName, dir_id, salePrice FROM t_product ORDER BY dir_id DESC, salePrice ASC;
+----+-----------------------+--------+-----------+
| id | productName           | dir_id | salePrice |
+----+-----------------------+--------+-----------+
|  4 | 罗技M125            |      4 |        80 |
|  5 | 罗技木星轨迹球 |      3 |        80 |
|  1 | 罗技M90             |      3 |        90 |
|  3 | 罗技M115            |      3 |        99 |
|  6 | 罗技火星轨迹球 |      3 |       349 |
|  2 | 罗技M100            |      2 |        49 |
+----+-----------------------+--------+-----------+
6 rows in set (0.00 sec)

SELECT 语句执行顺序：

先执行 FROM -> 接着执行 WHERE -> 再执行 SELECT -> 最后执行 ORDER BY

分页查询

1	SELECT * FROM product LIMIT 0, 1000;

语法 LIMIT?, ?
参数一：起始索引，从0开始
参数二：每次获取多少条数据

想要分页，需要知道两个条件：当前也 currentPage，每一页多少条数据 pageSize

起始数据 = (currentPage - 1) * pageSize;

select * from table_name limit(currentPage - 1) * pageSize, pageSize

# 需求：每页显示6条记录，查询第三页
mysql> SELECT * FROM t_product LIMIT 12, 6;
+----+-----------------------+--------+-----------+----------+--------+-----------+
| id | productName           | dir_id | salePrice | supplier | cutoff | costPrice |
+----+-----------------------+--------+-----------+----------+--------+-----------+
| 13 | 罗技M90             |      3 |        90 | 罗技   |   0.50 | 35.00     |
| 14 | 罗技M100            |      2 |        49 | 罗技   |   0.90 | 33.00     |
| 15 | 罗技M115            |      3 |        99 | 罗技   |   0.60 | 38.00     |
| 16 | 罗技M125            |      4 |        80 | 罗技   |   0.90 | 39.00     |
| 17 | 罗技木星轨迹球 |      3 |        80 | 罗技   |   0.80 | 80.00     |
| 18 | 罗技火星轨迹球 |      3 |       349 |          |   0.87 | 290.00    |
+----+-----------------------+--------+-----------+----------+--------+-----------+
6 rows in set (0.00 sec)

工作中一般不用 limit，性能本不好，且是 mysql 特有的。

聚合函数

COUNT：统计结果记录数(COUNT(*))
MAX：统计计算最大值
MIN：统计最小值
SUM：统计计算求和
AVG：统计计算平均值

语法格式：组函数名(列名)

# 需求：查询所有商品的平均零售价
mysql> SELECT AVG(salePrice) FROM t_product;
+----------------+
| AVG(salePrice) |
+----------------+
| 124.5000       |
+----------------+
1 row in set (0.00 sec)

# 需求：查询所有商品总记录数
mysql> SELECT COUNT(*) FROM t_product;
+----------+
| COUNT(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

# 需求：查询分类为2的商品总数
mysql> SELECT COUNT(*) FROM t_product WHERE dir_id = 2;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

避免重复数据-DISTINCT

查询数据库中的字段的值又多少不同的值

mysql> SELECT distinct dir_id FROM t_product;
+--------+
| dir_id |
+--------+
|      3 |
|      2 |
|      4 |
+--------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT dir_id) FROM t_product;
+------------------------+
| COUNT(distinct dir_id) |
+------------------------+
|                      3 |
+------------------------+
1 row in set (0.00 sec)

DISTINCT 表示不同的，独特的，有区别的

多表联查(隐式连接)

多表查询：多表联合然后查询数据

最简单的格式：SELECT FROM 表A, 表B,…

没有连接条件的表返回结果为笛卡尔积：
集合 A={a,b}，B={1,2,3} 如果按简单查询表的话，他们的笛卡尔积为{a(1),a(2),a(3),b(1),b(2),b(3)}
实际开发要避免
解决方案：在WHERE中加入有效的连接条件->等值连接

需求：查询所有货物的名称，零售价，货物分类名称

SELECT productName, salePrice, directorName FROM t_product p, t_director d WHERE p.dir_id = d.dir_id;

+-----------------------+-----------+--------------+
| productName           | salePrice | directorName |
+-----------------------+-----------+--------------+
| 罗技M100            |        49 | 航空母舰 |
| 罗技M100            |        49 | 航空母舰 |
| 罗技M100            |        49 | 航空母舰 |
| 罗技M100            |        49 | 航空母舰 |
| 罗技M100            |        49 | 航空母舰 |
| 罗技M100            |        49 | 航空母舰 |
| 罗技M90             |        90 | 银河战舰 |
| 罗技M115            |        99 | 银河战舰 |
+-----------------------+-----------+--------------+

连接 n 个表至少需要 n-1 个连接条件。
要给表取别名，要区分开表中的列名相同的列
表取了别名后就不能使用真名。

多表联查(显示连接)

union/ union all

mysql> select ename, deptno a from emp where job='salesman' or job='analyst' union all select ename, deptno from emp where job='analyst';
+--------+----+
| ename  | a  |
+--------+----+
| ALLEN  | 30 |
| WARD   | 30 |
| MARTIN | 30 |
| SCOTT  | 20 |
| TURNER | 30 |
| FORD   | 20 |
| SCOTT  | 20 |
| FORD   | 20 |
+--------+----+
8 rows in set (0.01 sec)

注意事项

UNION 内部的 select 语句必须拥有相同的列

列也必须拥有兼容的数据类型

每条 select 语句中的列的顺序必须相同

UNION 结果集中的列名总是等于 UNION 中第一个 select 语句中的列名

UNION 操作符选取不重复值，若允许重复的值使用 UNION ALL

自连接

自连接查询,将一张表看成是两张表使用别名区分.

需求:查询员工名称和其对应经理的名称
查询员工和其经理，如果没有经理显示总经理

课堂练习:自连接查询productdir这张表, 查询出所有子目录,对应的父目录名称

单行函数

分组查询

分组函数单独使用：将整个表分成一组，select count(empno) from emp;

案例：

按照职位分组，求出每个职位的最高和最低工资

1	SELECT job, max(sal), min(sal) FROM emp GROUP BY job;

查询平均工资高于2000的部门和其平均工资

1	SELECT deptnp, avg(sal) FROM emp GROUP BY deptno HAVING avg(sal) > 2000;

查询各个管理人员运功的平均工资，其中平均工资不能低于1300，不计算老板

1	SELECT mgr, avg(sal) aa from emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING aa >= 1300;

查询至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资

1 2	SELECT d.deptno, d.dname, count(e.empno) aa, avg(sal) FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno GROUP BY d.deptno, d.dname;

外键约束

外键约束：A表依赖 B表，则 A表的字段添加外键关联 B表

外键列允许为空

注意：MySQL 中， InnoDB 支持事务和外键 ALTER TABLE 表明 ENGINE=’InnoDB’

主表和从表：主表可以单独存在，从表依赖主表。

删除表时，先删除从表，再删除主表。

格式：alter table 从表 add constraint 外键 forrign key(从表外键字段) references 主表(主表字段)

开发中为了提高性能，往往不要外键约束。需要我们使用 java 代码去主动维护外键的概念。

子查询

查询大于公司平均工资的员工名：

1	SELECT ename,sal FROM emp WHERE sal > (SELECT AVG(SAL) FROM emp)

查询工资大于 martin 的人的信息

1	SELECT * FROM emp WHERE sal > (SELECT SAL FROM emp WHERE ENAME='martin')

单行单列子查询可以看做一个值
多行单列，可以看做一个集合
- IN 与集合内任意值相等即可
- ANY 与子查询返回的任意一个值比较
  - = ANY 此时与 IN 相同
  - > ANY 表示大于集合中的最小值
  - < ANY 小于集合中最大值
- ALL 与子查询返回的每一个值比较
  - > ALL 大于集合中的最大值
  - < ALL 小于集合中的最小值
多列子查询，临时表

案例：

案例：查询每个部门编号、名称、部门人数，平均工资

1
2
3

SELECT d.DEPTNO, d.DNAME, IFNULL(a.count,0), a.avg FROM dept d LEFT JOIN 
(SELECT DEPTNO, AVG(sal) avg, COUNT(EMPNO) count FROM emp GROUP BY DEPTNO) a 
on d.DEPTNO = a.DEPTNO

先查询 emp 表中的平均工资、人数、部门编号，并且按 deptNo 分组，

然后将 dept 表和上面查出的表通过字段 deptNo 结合

案例：查询员工信息与部门名称

1	SELECT e.ename, (SELECT DNAME FROM dept where deptno = e.deptno) dname, deptno from emp e;

每查一条 emp 表数据，就会执行以下 () 中的子查询查到部门名称。

SQL 执行顺序

select - from - where - group by - having - order by - limit ?,?

-> from – where –group by – having – select – order by – limit ?,?

规范约定

表名，t_表名
主键：id
外键：A表依赖B表，则使用B表的表名(缩写)_主键名：t_director -> dir_id

数据备份和恢复

修改之前，需要先做数据备份

MySQL 自身数据库维护：默认禁用。需要修改my.ini 配置后重启服务器，注释掉 [client]no-beep

导出
通过命令行，mysqldump -u账户 -p 数据库名称 > 脚本文件地址
nysqldump -u root -p admin test>f:/test_bak.sql

导入
mysql -u 账户 -p 数据库名称<脚本文件存储地址
mysql -u root -padmin test<f:/test_bak.sql

navicat 工具的导入和导出

ORM(Object Relational Mapping)

ORM思想：为了解决面向对象与关系数据库存在的互不匹配的现象的技术。ORM是通过使用描述对象和数据库之间映射的元数据，将java程序中的对象自动持久化到关系数据库中。

事务并发

多个事务并发去访问/修改同一个资源

事务并发可能会导致的问题：

第一类丢失更新
- 撤销一个事务时，把其他事务已提交的更新数据回滚了。
第二类丢失更新
- 提交一个事务时，把其他事务已提交的更新的数据覆盖了
脏读：一个事务读到另一个事务未提交的更新数据。
幻读：一个事务执行两次查询，但第二次查询比第一次查询多处了一些数据行
不可重复读：一个事务两次读同一行数据，得到的数据不一样

隔离级别

第一级别：Read Uncommitted(读取未提交内容)
1. 所有事务都可以看到其他未提交事务的执行结果
2. 本隔离级别很少用于实际应用，因为它的性能也不好
3. 该级别引发的问题：脏读，读取到了未提交的数据
第二级别：Read Committed(读取提交内容)
1. 这是大多数数据库系统的默认隔离级别(不是MySQL默认的)
2. 一个事务只能看见自己已经提交事务所做的改变
3. 这种隔离出现的问题是：不可重复读，不可重复读意味着我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果
4. 导致这种情况的原因可能有：
  - 有一个较差的事务有新的 commit，导致了数据的改变
  - 一个数据库被多个实例操作，同一事务的其他实例在该实例处理期间可能会有新的 commit
第三级别：Repeatable Read(可重读)
1. 这个是MySQL的默认事务隔离级别
2. 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
3. 此级别可能出现的问题—幻读，当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的行
4. InnoDB和Falcon存储引擎通过多版本并发控制(MBCC, Multiversion Concurrency Control)机制解决了该问题
第四级别：Serializable(可串行化)
1. 这是最高的隔离级别
2. 它通过强制事务排序，使之不可能互相冲突，从而解决幻读问题，它是在每个读的数据行上加上共享锁。
3. 这个级别，可能导致大量的超时现象和锁竞争。

查看是否隔离级别 SELECT @@tx_isolation; MySQL默认是可重复读

悲观锁和乐观锁

悲观锁

写锁

两个事务开启，当其中一个更新某一行数据后，另一个再要访问同一行数据，会阻塞。当访问的那个事务提交后，另一个事务才会正常运转

排他锁

乐观锁

在表中额外增加一个列，用来表示修改的版本(int)，修改一次就把版本加1

Mybatis 会通过返回结果的受影响行数进行判断操作是否成功。

操作步骤：

在表中新增一列，用来表示修改的版本号，类型使用整数类型，初始值为0
在修改数据前，先发送 SELECT 语句查询当前被修改数据的信息
发送 update 语句更新数据，版本号修改递增1/判断条件中的版本号必须是刚刚查询出来的版本号
- UPDATE person set name='java', version=version + 1 where id=10 and version= 刚刚查询出的版本号
判断 update 语句执行后的受影响行数 rows，若 rows>0 则提交事务，否则回滚事务。

意识：如果需要处理乐观锁，应该写好 DAO 之后，立马编写 service 的 update 方法。

MySQL 数据库中的权限

权限相关命令： GRANT

完整语法： GRANT 权限(columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码" WITH GRANT OPTION

三种具体语法：

给一个存在用户赋予权限
- GRANT 权限(columns) ON 数据库对象 TO 用户
- 赋予 will 账户:SELECT和INSERT权限
1
GRANT select, insert ON *.* TO will@localhost;
- .左边的*是数据库，.右边是数据表 *.*表示全局权限
创建用户，设置密码，赋予权限
- GRANT 权限(columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码"
- 创建lucy用户：
- GRANT ALL ON *.* TO lucy@localhost IDENTIFIED BY '1234'
创建用户，设置密码，赋予权限，并且该用户可以继续授权给其他用户：
- GRANT 权限(columns) ON 数据库对象 TO 用户 IDENTIFIED BY '密码' WITH GRANT OPTION
- GRANT ALL privileges on *.* TO will@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
- 创建一个超级管理员，用户名为 will，密码为1234，只能在 localhost 登录，可以给别人授权。

其中

ALL PRIVILEGES 代表所有权限
*.*：所有数据库对象。普通格式为 db.table，比如test.*
will@’localhost’ : will用户，只能在本机访问。
- 用户账户包括 user@host
- host 为
  - %从任意网址访问
  - 10.250.7.% 只能从指定网段访问
  - 192.168.1.2只能从某个 IP 访问
WITH GRANT OPTION 是否允许用户继续授权(注意，这个用户能够授权只能是该用户的权限子集)
注意，授权操作之后，使用 flush privileges 命令刷新权限。

删除用户

DROP USER 用户名称， user必须包括 user@host，如：DROP USER will@'%';
查看当前用户的权限：show grants ，show grants for root@localhost 查看特定用户权限

注意点

一种 sql 写法，

<resultMap id="BaseResultMap" type="cn.wolfcode.crm.domain.CustomerTraceHistory">
	<association property="customer" javaType="Customer" columnPrefix="c_">
		<id column="id" property="id"/>
		<result column="name" property="name"/>
	</association>
</resultMap>

<select id="queryForList" resultMap="BaseResultMap">
	SELECT
		his.id
		his.trace_time
		...
		c.id c_id
		c.name c_name
		type.id type_id
		type.name type_name
		FROM customertracehistory his
		LEFT JOIN customer c ON c.id = his.customer_id
		...
</select>