Mysql和JDBC

Linux操作mysql：
服务器端安装： sudo apt-get install mysql-server
启动服务：sudo service mysql start
查看进程中是否存在mysql服务：
ps -ajx|grep mysql
ps查看当前系统进程 -a显示所有用户进程 - j任务格式显示进程 -x显示无控制终端进程
停止服务：sudo service mysql stop
重启：sudo sevice mysql restart
MySQL配置文件
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
主要配置项如下：
bind-address表示服务器绑定的ip，默认为127.0.0.1
port表示端口，默认为3306
datadir表示数据库目录，默认/var/lib/mysql
general_los_file表示普通日志 默认/var/log/mysql/mysql.log
log_error表示错误日志，默认/var/log/mysql/error.log
客户端的安装：
客户端为开发人员与数据库管理员(dba)使用，
图形化界面客户端navicat,在navicat官网下载，下载linux系统的navicat，打开下载到的位置，使用命令chmod +x nevicat15-mysql-cs.AppImage 运行命令
黑马安装流程：
进入解压的目录，运行命令：
./start_navicat
试用期到期：删除用户目录下的.navicat64目录即可解决

Windows下安装Mysql数据库：
1、官网：http://dev.mysql.com/downloads/mysql
2、
3、
4、双击安装包进行安装
5、
6、
7、
8、

其实安装完windows版本的mysql服务器默认会有自带一个命令行的客户端。Linux可以使用：sudo apt-get install mysql-client,安装好：查看帮助文档：mysql --help
最基本的连接命令：mysql -u 用户名 -p密码
退出：quit 或者exit 或者ctrl+d

数据库备份与恢复（导入和导出）
备份
mysqldump -u用户名 -p　数据库名　> 目录/文件名.sql
mysqldump -uroot -p java_db1 > java_db1.sql
恢复（还原）：先创建一个空的数据库 java_db，然后再还原
mysql -u用户名 -p 新数据库名　< 数据库文件
mysql -uroot -p java_db < java-db1.sql;

Windows操作：由于MySQL默认使用UTF-8字符串，因此Windows可以通过”MySQL8.0 Command Line Client -Unicode”菜单启动命令行工具，该工具会使用UTF-8字符集，启动步骤：
开始菜单–>MySQL–>MySQL Server8.0–>”MySQL8.0 Command Line Client -Unicode”启动

操作MySQL数据库的图形界面软件：SQLyog 、navicat、Datagrip

数据库管理系统：Database Management System，简称DBMS，负责管理数据的存储、安全、一致性、并发、恢复和访问等操作，DMBS有一个数据字典（系统表），存储它拥有的每个事务的相关信息，例如名字、结构等，这种数据称为元数据。
数据库管理系统包含三大部分构成：
1、数据库文件集合：主要是一系列的数据文件，作用是存储数据
2、数据库服务器端：主要负责对数据文件以及文件中的数据进行管理
3、数据库客户端，主要负责和服务器通信，向服务端传输数据或者从服务端获取数据。
通常所说的数据库就是数据库 + 数据库管理系统。
数据库发展：网状型数据库—>层次型数据库---->关系型数据库---->面向对象数据库
也可分为关系型（以二维表存储）和非关系型数据库NoSQL（以key-value对存储）
关系型数据库：最基本的数据存储单元是数据表，数据表由行和列组成，每一行称为一条记录，存放一条完整的信息，每一列称为一个字段，每个表都应该制定一个特殊列唯一标识每行记录，这就是主键列。MySQL默认以分号结束每条命令，MySQL不区分大小写
Windows启动和登录mysql数据库：mysql -u用户名 -p
MySQL数据库的一个实例（Server Instance）可以同时包含多个数据库
常见数据库对象：
对象名称 对应关键字 描述
表 Table 表是存储数据的逻辑单元，以行和列的形式存在，列是字段，行是记录
数据字典 系统表， 存放数据库相关信息的表，系统表里的数据通常由数据库系统维护。
约束 Constraint 执行数据校验的规则，保证数据完整性的规则
视图 View 一个或多个数据表里的数据的逻辑显示，视图并不存储数据
函数 Function 完成一次特定的计算，具有一个返回值
存储过程 Procedure 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给引用环境
触发器 Trigger 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应处理。
查看当前实例包含的数据库：Show databases;
创建数据库：create database [IF NOT EXISTS] 数据库名;
使用数据库：use 数据库名
查询该数据库包含的表：show tables;
查询数据表的表结构：desc 表名;
MySQL通常支持如下两种存储机制：
MyISAM：早期MySQL5.0之前默认的存储机制，对事物支持不够好
InnoDB：提供事务安全带存储机制，InnoDB通过建立行级锁来保证事务完整性，并以Oracle风格的共享锁来处理Select语句。系统默认启动InnoDB存储机制，若不想使用InnoDB表，则可以使用skip-innodb选项。MySQL5.0版本后默认使用InnoDB存储机制。
MyISAM和InnoDB区别：
主外键：MyISAM不支持，InnoDB支持。
事务：只有InnoDB支持
行表锁：MyISAM：支持表锁 InnoDB支持行锁
缓存：MyISAM只缓存索引，不缓存数据。 InnoDB不仅缓存索引，还要缓存真实数据，对内存要求比较高，而且内存大小对性能有决定性的影响。
表空间：MyISAM和InnoDB都很小
关注点：MyIsam关注性能，功能少些，性能较好 InnoDB关注事务，功能多，性能差些。
默认安装：MyISAM和InnoDB都默认安装
文件结构：MyISAM：b.frm：描述表结构文件，字段长度等
b.MYD(MYData)：数据信息文件，存储数据信息（如果采用独立表存储模式）
b.MYI(MYindex)：索引信息文件。
InnoDB：b.frm：描述表结构信息，字段长度等
b.idb：存储数据信息和索引信息

创建表默认使用InnoDB，也可以加如下语句强制选择：
ENGINE=MyISAM：强制使用MyISAM存储机制
ENGINE=InnoDB：强制使用InnoDB存储机制
SQL全称Structured Query Language，结构化查询语言，标准SQL，用于操作任何数据库。
SQL语句分为如下几种类型：
DQL：数据查询语言，主要由select关键字完成，功能最复杂、最丰富的语句
DML：数据操作语言，由insert、update和delete三个关键字完成
DDL：数据定义语言主要由create、alter、drop、和truncate四个关键字完成
DCL：数据控制语言，主要由grant和revoke两个关键字完成，用于为数据库用户授权或者回收用户的权限。
事务控制语句：由commit、rollback和savepoint三个关键字完成。
MySQL支持的列类型：
MYSQL定义数据字段类型大致分为三类：数值、日期、时间和字符串（字符）类型
数值：
整型：如果设置为无符号，就可以使用0~XXX的取值范围。

浮点型：

Float:单精度型（保证6位内有效数字的准确性）
double:双精度型（保证16位内有效数字的准确性）
定义方式：
Float(M,D) 、double(M,D)
M代表数字的总位数，最大值是255，D代表其中小数的位数。
定点数：保以保存精确的小数
定义方式：decimal(M,D),M代表总的数字的位数，最大为65，D代表其中的小数位，如：decimal(5,2)代表共5位数，其中2位是小数。主用在需要精确的小数，如价格

字符串：

Varchar最多能保存65535个字节（UTF8编码时，一个字符占3个字节，GBK编码时，一个字符占2个字节）
Utf8时——>大约能保存65535/3=2万多个字符
Char和varchar的区别；
Char:定长字符串：创建表时，char字段占用硬盘空间的大小是已经固定了。比如：
定义name char(10)，代表name字符占10个字符的硬盘空间，具体是多少个字节，要看字段是什么编码，如果是UTF8编码，一个字符占3个字节，所以char(10)将占30个字节，无论内容够不够10个字节都会占30个字节，比如：输入a一个字符也会占用30个字节。
Varchar:变长字符串。在创建表时，varchar字段占用硬盘空间的大小是不固定的，比如：name varchar(10)，代表name最多保存10个字符，如果是UTF8，则最多占用30个字节，如果输入内容不够30个字节，比如：abc，那么只占三个字符9字节再+1，共占10个字节。因为字符后自带一个\n的结束符，所以占用的字节要+1.
Text类型：与char和varchar不同的是，text不可以有默认值，其最大长度是2的16次方-1

超过255个字符的只能用varchar或者text
能用varchar的地方不用text和char
枚举类型：enum,在定义字段时就预告规定好固定的几个值，然后插入记录时值只能用这几个固定好的值中选择一个。
语法定义：enum(‘男’，‘女’，‘妖’）
一个enum最多可以设置65535个值，这个字段最终只占1到2个字节（要看设置值是多少）比较节省空间。
时间类型：

Datetime和timestamp的区别：
范围不同：
datetime保存时间的范围:”1000-01-01 00：00：00”到”9999-12-31 23：59：58”
Timestamp保存时间的范围：”1970-01-01 00：00：00”到”2038-01-19 03:14:07”
存储空间不同

登录和退出数据库过程：

连接数据库2、输入用户名和密码3、完成对数据库的操作4、完成对表结构和表数据的操作5、退出数据库

数据库完整性和约束：
大部分数据库支持5大约束：
NOT NULL：非空约束，指定列不能为空
UNIQUE：唯一约束，指定某列或者几列组合不能重复
PRIMARY KEY：主键，指定该列的值可以唯一地表示该条记录
FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，用于保证参照完整性
CHECK：检查，指定一个布尔表达式，指定对应列必须满足该表达式。
MySQL不支持CHECK约束，即使使用也不会有任何作用。
约束的作用用于保证数据表的完整性，约束也是数据库对象，存储在系统表中。
MySQL使用information_schema数据库中的TABLE_CONSTRAINTS表保存约束信息。

NOT NULL非空约束： 指定列不允许为空，只能作为列级约束使用。
UNIQUE约束：指定某列或者几列组合不允许出现重复值，但可以出现多个null值，因为在
SQL中的null不等于null，也不等于空字符串，Mysql所有数据类型的值都可以是null。
同一个表中可以建立多个唯一约束，建立唯一约束后，MySQL会为该列相应的创建唯一索引，若不指定唯一约束名，则默认与列名同名，
语法一：列级约束语法：在创建表时，在指定列后添加唯一约束即可。只能指定一列
语法二：表级约束语法：在建表语句最后一行加：unique(列1,列2) 可以指定1列或多列
表级约束语法还可以指定唯一约束的名 语法： constraint 约束名 unique(列1,列2) 如：
Create table 表名
(
Column1 数据类型 约束，
…
ColumnN 数据类型 约束,
constraint 约束名 unique(列1,列2)
)
创表后使用add添加：alter table 表名 add unique(列1,列2)
创表后使用modify修改类型增加：alter table 表名 modify name varchar(20) unique
对于大部分数据库，删除约束：drop constraint 约束名
MySQL使用：alter table 表名 drop index 约束名;

PRIMARY KEY主键约束（索引）：相当于非空和唯一约束，不重复，不可以为空
如果对多列组合建立了主键约束，则多列都不能为空，但只要求多列组合不重复。
用于标识表中一条记录，一个表最多有一个，可以使用列级（一列）和表级语法（多列）。
MySQL将所有主键约束命名为PRIMARY，用户指定也无效（允许用户指定只是为了和标准SQL保持兼容性）
创建主键时，MYSQL在主键约束所在列或列组合上建立对应的唯一索引。
删除主键约束：alter table 表名 drop primary key;
为指定表增加主键：alter table 表名 add primary key(列1，列2)
若只为单独列增加，也可以使用modify：alter table 表名 modify列名 primary key;

FOREIGN KEY：外键约束 ，保证一个或两个数据表之间的参照完整性，主要用于构建一个表中的两个字段或两个表的两个字段之间的参照关系。从表外键列的值必须在主表被参照列的值范围之内，或者为空（也可以使用非空约束使其不能为空）
数据库的主键和外键类型一定要一致，两个表必须是innoDB类型。
从表外键参照的只能是主表主键列或唯一键列，这样才能保证从表记录可以准确定位到被参照的主表记录，同一个表内可以拥有多个外键，建立外键时，MySQL也会为该列建立索引。
一对多时，通常在多的列增加外键，多对多时，需要额外增加一个连接表添加外键来记录它们的关联关系。
因为外键的值必须是参照列已有的值，因此，从表插入记录前必须先向主表插入记录，否则从表记录的外键只能为null，外键约束只保证被参照的记录必须存在，并不保证必须要被参照记录，所以外键可以为null,若希望保证每条从表记录必须存在对应的主表记录，则应使用非空、外键两个约束。
外键同样可以采用列级别（只为一列建立）和表级别（一列或多列）语法。但MySQL的列级别语法不会生效。提供只是为了和标准SQL保持兼容性。
语法：
Create table 表名
(
Column1 数据类型 约束，
…
ColumnN 数据类型 约束,
constraint 约束名 unique(列1,列2),
Primary key((列1,列2)),
Constraint 外键名 foreign key(列1，列2…) references 主表(主表列1，主表列2…)
)
若指定外键名，则加constraint 外键名，若不指定，则可以省略，MySQL默认给外键约束命名为：table_name_ibfk_n 即表名_ibfk_n n为第几个外键，从1开始。
删除外键：drop foreign key 外键约束名
外键不仅可以参照其他表，也可以参照自身的列，即自关联，语法：
Foreign key(列1…) references 自身表名(列n…)
如果希望当删除主表记录时，从表记录也随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null
若添加on delete cascade，删除主表记录时，参照该主表的从表记录全部级联删除
若添加on delete set null，删除主表记录时，参照该主表的从表记录的外键设为null

MySQL不支持的CHECK约束：要求指定列满足表达式。
在定义表的所有列定义后使用：check(表达式) 如check(列1 > 10)即要求列1值大于10

数据库基本操作：
查看（show）、使用（use）、创建（create）、删除（drop）数据库的命令格式。
Ctrl + a回到开头 ctrl + e回到结尾 ctrl + l 清屏
退出ctrl +d
查看所有数据库：show databases;
查看版本：select version();
查看时间:select now();
查看当前使用的数据库：select database();

DDL语句：
创建指定数据库字符集：create database 数据库名 charset=utf8;
若数据库不存在才创建：create database if not exists 数据库名;
创建默认数据库：create database 数据库名
查看创建数据库信息的语句：show create database 数据库名
使用（切换）数据库：use 数据库名
删除数据库：drop database 数据库名
表结构的创建：查看（show）、创建(create)、显示表字段(desc)
创建表：create table 表名(
字段名称 数据类型 可选的约束条件，
Column1 datatype contrai,
Column2 datatype,
…
columnN datatype,
PRIMARY KEY(one or more columns)
);

auto_increment表示自动增长

利用子查询创建表：create table 表名 as 子表 新表和子表拥有同样的字段和数据类型
查看数据中有什么表：show tables
查看数据表的创建过程：show table 表名
查看表有什么字段：desc 表名
修改表结构的语法：
表中添加列（add）、修改字段名（change）、修改字段类型（modify）、删除（drop）
添加字段：
alter table 表名 add 列名 类型； 添加多个列可以使用()，各列逗号隔开
如：添加生日字段 datetime类型，id int类型
alter table students add (birthday datetime,id int);
注意：如果数据表中已有数据记录，除非给新增的列指定了默认值，否则新增数据列不可指定非空约束。
重命名字段：alter table 表名 change 字段原名 新名 类型及约束； 如；
alter table students change birthday birth datetime not null;
修改字段类型：已有字段，只修改类型：alter table 表名 modify 列名 新的类型及约束；如：Alter table students modify birth date not null;
Mysql的modify每次只能修改一个列定义,其他数据库如Oracle等可以修改多个列定义，语法和add相似，多个列定义使用括号，各列用逗号隔开，若希望mysql也支持一次修改多个列定义，在alter table后使用多个modify，多个modify用逗号隔开如：
alter table students modify test3 text,modify test4 double(3,2);
删除列/字段：alter table 表名 drop 列名;如：alter table students drop birth；
删除列定义时，会删除整列数据，释放该列在数据库中占用的内存。
修改表名：alter table 旧表名 rename to 新表名;
修改列名同时修改数据类型:alter table 表名 change 旧列名 新列名 数据类型 约束
Change用于修改列名，若只修改数据类型，用modify即可。
删除表：drop table 表名
删除数据表的效果：表结构被删除，表对象不再存在，表里的所有数据、相关索引、约束也被删除
truncate 表名：删除表的全部数据，保留表结构，实际上删除了表重新创建结构相同的新表
delete from 表名[约束条件]:可以一次删除一条，也可以删除多条或全部，
非InnoDB,truncate效率高。

DML：
select、insert、update、delete能够增删改查数据库中的数据
增删改查（curd）解释：
创建（create），更新（update）,读取（retrieve/Read）,删除（delete）

Insert into 插入
全行插入：insert into 表名values(…)
只插入指定字段： insert into 表名(列1，列2…) value(值1，值2…);
主键字段若为自动增长，可以使用0 null default来占位,系统会自动为该列分配至
多行插入：插入的多条记录使用逗号,隔开
insert into 表名 values(字段1…),(字段1…);
通过子查询插入数据到新的分类表中,如下方将查询出来的cate_name插入到表goods_cates的name列中。
insert into goods_cates(name) (select cate_name from goods group by cate_name);

update修改：
修改整个表一整列的值
update 表名 set 列1=值
修改指定行的列，按条件修改：
update 表名 set　列1=值　where 条件列名=值

使用as为列或者表指定别名：select 字段 as 别名,字段 as　别名 from 数据表;
也可以省略as，如：select 字段 别名,字段 别名 from 数据表;

查询时，字段的顺序会根据你首先输入的字段名优先显示，如
select age,name from students;
会显示age为第一列，name 为第二列。

delete from删除，删除整行
物理删除：delete from 表名 where 条件判断 物理删除是无法恢复的。
如：delete from students where id=4; 删除一行
删除整个表的数据 delete from 表名

逻辑删除：用一个字段来表示，这条信息是否已经不能再使用了，给students表添加一个is_delete字段bit类型。bit类型只能保存0和１，
is_delete = 1表示逻辑删除 is_delete = 0　表示没有删除
alter table students add is_delete bit default 0;
select * fron students where is_delete = 0;
只能查看到is_delete = 0的数据，逻辑上被删除，实际数据还在数据库中。

as、distinct的作用。
查询强化：
给表名起别名
select a.id,a.name from students as a
消除重复行：distinct字段，消除它后跟的字段的重复行
select distinct gender from students;

Where条件之比较运算
Where语句的作用：使用where句子对表中的数据筛选，结果为True的行才会进行操作
Where支持多种运算符，进行条件处理：
比较运算符、逻辑运算符、模糊查询、范围查询、空判断。
在mysql判断两个东西相等只需要一个等号。
比较运算符：=、>、 <、 >=、 !=、<>
<> 18 表示取大于18和小于18的数据，相当于不等于。
比较运算符不仅可以跟数字连接，还可以跟表达式或变量连接，如：select news_id + 5…
逻辑运算符：
and表示多个条件必须同时成立（值为True）
Or:表示多个条件满足任意一个时成立
Not：求否
运算符 优先级（优先级小的优先执行）
所有比较运算符 1
Not 2
And 3
Or 4

字符串连接：concat()函数
如：select concat(news_title,’xxx’)… 选择出news_id和’xxx’连接后的结果
但如果xxx改为null，则最后返回的都是null。
Not:表示取反操作。

模糊查询：使用like
like:配合字符使用
%表示任意多个任意字符 _表示一个任意字符
若希望使用下划线和%这个字符本身，加\转义，标准SQL没有提供\转义，使用escape
查询姓名中以小开头的名字：select * from students where name like ‘小%’;
查询姓名中有小的名字：select * from students where name like ‘%小%’;
查询有两个字的名字：select * from students where name like '’;
查询至少两个字的名字：Select * from students where name like ‘%’;

范围查询：
in表示在一个非连续的范围内,相当于多个值的or（或）关系
val1 In(val2,val3):要求val1等于后面括号里的任意一个表达式的值

查询编号是1或3或8的学生：select * from students where id in(1,3,8);
查询不是18和34岁的学生：Select * from students where age not in(18,34)
in还可以跟变量，如：select * from student where 2 in(student_id,teacher_id);
查询出student_id为2，teacher_id为2的记录。

beetween and：连续范围查询
Val1 between val2 and val3表示在一个连续的范围内，要求val1 >=val2且val1<=val3
查询编号3至8的学生：select * from students where id between 3 and 8;
还可以跟变量，如：select * from student where 2 between teacher_id and student_id;
查询出teacher_id小于等于2,student_id大于等于2的所有记录。

Where条件之空值判断：空判断：is null
查询没有填写身高的学生:select * from students where height is null;
null和“”不一样，null是什么都没有。
非空判断：is not null
查询填写了身高的学生：select * from students where height is not null;

Order by排序
排序查询语法：select * from 表名 order by 列1 asc/desc ，列2 asc/desc；
语法说明：
将数据按照列1进行排序，如果某些行的列1值相同时,则按照列2排序,以此类推。
asc从小到大排列，即升序，默认排序方式，可以不写
Desc从大到小排列，即降序
查询编号是1或3或8的学生查询年龄在18-34岁之间的男性，按照年龄从小到大的排序
select * from students where age between 18 and 34 and gender = ‘男’ order by age asc;
查询年龄在18-34的女性，身高从高到低排序
select * from students where age between 18 and 34 and gender = ‘女’ order by height desc;
order by 多个字段
查询年龄在18-34岁的女性，身高从高到低排序，如果身高一致，则按照年龄倒序排序
select * from students where age between 18 and 34 and gender = ‘女’ order by height desc,age desc;
查询年龄在18-34岁之间的男性，身高从高到低排序，如果身高相同按照年龄倒叙排序，如果年龄相同，则按照id的从大到小排序。
select * from students where age between 18 and 34 and gender = ‘男’ order by height desc,age desc,id asc;

Sql常用函数：
字符串函数：
Concat(s1,s2)将传的字符连接成一个字符串，任何字符串与null连接结果都是null，如：
Select concat(‘aaa’,’bbb’); 查询结果为aaabbb
Insert(str,x,y,instr)将字符串从x位置开始，y个字符长的子串替换为指定的字符。
Lower(str)和upper(str)：将字符串变成小写和大写
Left(str,x)和right(str,x)分别返回字符串最左边的x个字符和最右边的x个字符，如果第二个参数为null,那么都不返回任何字符。
Lpad(str,n,pad)和rpad(str,n,pad)用字符串pad对str最左边或最右边进行填充，直到长度为n个字符长度，即str最多不能超过长度n.
Ltrim(str)和rtrim(str)：去掉字符串当中做左侧和右侧的空格
Trim(str)去掉字符串(左右)两边的空格
Repeat(str,x) 返回str重复x次的结果
Replace(str,a,b)用字符串b替换字符串str中所有出现a字符的地方。
Substring(str,x,y)返回（截取）字符串str中第x位置起y个字符长度的字符

数值函数：
Abs(x):绝对值
Ceil(x) 向上取整
Floor(x)向下取整
Mod(x,y)：返回x/y的摸，即返回x对y取余（余数） 如：9/2余1 ,则mod（9，2）返回1
Rand()返回0-1内容的随机值

日期和时间函数：
Curdate()返回当前日期，只包含年月日
Curtime()返回当前时间，只包含时分秒
Now()返回当前日期和时间，年月日时分秒
Unix_timestamo返回当前时间戳
From_unixtime(unixtime)将一个时间戳转换成日期
Week(date)返回当前是一年中的第几周
Year()返回所给日期是哪一年
Hour(time)返回当前时间的小时
Minute(time)返回当前时间的分钟
Date_format(date,fmt)按字符串格式化日期date值
Date add(date,interval exprtype)计算日期间隔
dateDiff(date1,date2)计算两个日期相差的天数

流程函数：
If(value,t,f)如果value为真返回t，否则返回f.
Ifnull(value1,value2)如果value1不为空，返回value1，for则返回value2
Case when then end用法：select case 2 >3 then ‘对’ else ‘错’ end;
其他常用函数：
Select Database()：返回当前数据库
Select version() 返回当前数据库版本
User()返回当前登录用户名
Password(str) 对str进行加密
Md5()返回str的MD5值

聚合函数：aggregation function又称为组函数。
聚合函数的作用：聚合函数会把当前所在表当做一个组进行统计。
聚合函数特点：每个组函数接收一个参数（字段名或者表达式）
统计结果中默认忽略字段为null的记录，要想列值为Null的行也参与组函数的计算，必须使用IF NULL函数对NULL值做转换
不允许出现嵌套，比如：sum(max(xx)) #错误
Mysql常见的聚合函数：
命令 作用 示例
Count() 计算总行数 Select count() from
表名;
Max(列) 求此列的最大值 Select max(列名) from 表名;
Min(列) 求此列的最小值 Select min(列名) from 表名;
Sum(列) 求此列的和 Select sum(列名) from
表名;
Avg(列) 求此列的平均值 Select avg(列名) from
表名;
coutt()所有求总，也可以在括号内写字段：count(字段)，表示求的是该字段的总数，只对非空值进行累计。
查询男性有多少人，显示的字段是count(),可以指定别名。
select count() ‘女性总人数’ from students where gender = ‘男’;
最大值：max()
学生的最高身高
select max(age) from students;
女性的最高身高
select max(height) from studens where gender = ‘女’;
最小值：min
男性的最小年龄:select min(age) from students where gender =‘男’;
求和：sum
计算所有人的年龄总和:select sum(age) from students;
平均值:avg
计算平均年龄:
方法一：select avg(age) from students;
方法二：select sum(age)/count() from students;
四舍五入 round(数值，保留小数位数),如:round(123.23,1)保留一位小数123.2
计算所有人的平均年龄，保留两位小数：
select round(avg(age),2) from students;
计算男生的平均身高，保留两位小数。
select round(avg(height),2) from students where gender = ‘男’;
聚合函数是不能嵌套的，但是round不是聚合函数，因此可以嵌套。

调用存储过程：
Call 名称();
查看所有存储过程：show procedure status;
查看指定数据库中的存储过程：show procedure status where db = ‘数据库名’;
查看指定存储过程源代码：show create procedure 存储过程名
删除存储过程：drop procedure 名称

存储过程变量：使用DECLARE语句声明
单个变量声明语法：declare 变量名 数据类型（varchar()等） 默认值
如：declare avgRes int default 0;
同时声明两个或多个变量 declare 变量名1，变量名2 数据类型（varchar()等） 默认值
如：declare x,y int default 0;
分配变量值：
方式一：为变量分配一个值，使用set语句:set x = 3; set y = 4;
方式二：使用select into语句将查询的结果分配给一个变量：select avg(salary) into avgRes from info; 该语句将查询结果赋值给了avgRes;

变量的范围：只能在gegin和end之间进行使用。

存储过程参数：
三种类型：
In参数：表示调用者向过程传入值（传入值可以是字面值或变量）
Delimiter $$CreateproceduregetName(innamevarchar(50))BeginSelect\ast frominfowhereename=name;End$$
Delimiter ;
Call getName(‘李白’);
上面存储过程表示调用存储过程时，根据调用者传入的参数去查询信息。

out参数：表示存储过程向调用者传出值
Delimiter $$CreateproceduregetSalary(innamevarchar(50),outresint)BeginSelectsalaryintoresfrominfowhereename=name;End$$
Delimiter ;
Call getSalary(‘李白’,@res);
Select @res
上面存储过程表示调用者调用存储过程时，向存储过程传入参数’李白’，存储过程根据传入参数，返回一个值，调用者使用@res进行接收。

Inout：inout参数是in 和out参数的组合
delimiter $$
create procedure test(inout num int,in inc int)
BEGIN
set num = num + inc;

END$$
delimiter ;

set @num1 = 20;
call test(@num1,10);
select @num1;
上面的存储过程表示在存储过程外定义了一个可以传入的参数 @num1=20，可以传入的参数前缀用@修饰，调用时，存储过程根据调用者传入的@num，在存储过程进行操作，把最后得到的@num1值返回给调用者，即最后的@num1值为30

存储过程语句：
If 语句：if
自定义函数：
delimiter $$
create function rand_str(n int) returns varchar(255)
BEGIN
DECLARE str varchar(100) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
declare i int default 0;
declare res_str varchar(255) default ‘’;
while i < n do

set res_str = concat(res_str,SUBSTR(str,floor(1 + RAND() * 52),1));
set i = i + 1;
end while;
return res_str;
END$$
delimiter ;

– select rand_str(4);

如果创建自定义函数时出现错误：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1

索引：用于快速找出某个列中有一特定值的行，不使用索引，mysql必须从第一条记录开始读完整的表，直到找出相关的行，如果表中有索引mysql能够快速到达一个位置去搜索数据文件，而不必查看所有数据，提高查询效率。
优势：提高数据检索效率，降低数据库的IO成本，通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：索引也是一种数据表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占空间，虽然索引提高了查询速度，同时会降低更新表的速度，如对表进行insert、update、deleter

Group by:分组：对大量数据进行分类统计或者分类计算。
分组用法：分组就是将一个‘数据集’划分成若干个‘小区域’，然后针对若干个小区域进行数据处理。
Group by 分组：
特点：1、group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组。
group by 可用于单个字段分组，也可以用于多个字段分组。
分组的字段必须要出现在select后面。

在mysql默认情况下，select后的字段只能出现以下两种情况：
1、在group by后出现过
2、在聚合函数中出现。
分组group by
按照性别分组，查询所有性别
select gender from students group by gender;
Group by +聚合函数：
计算每种性别中的人数
select gender,count() from students group by gender;
查看不同年龄段有多少人
select age,count() from students group by age;
查询男性、女性的年龄最大值
select gender,max(age) from students where gender = ‘男’ or gender = ‘女’ group by gender;

Group by + group_concat()
group_concat（字段名）表示根据分组结果，使用group_concat()来放置每一个分组中某字段的集合
查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;
查询每组性别的平均年龄
select gender,avg(age) from students group by gender;
。

group by + having
Having条件表达式：用来过滤分组结果。
Having作用和where 类似，但having只能用于过滤group by 而where是用来过滤表数据。
查询平均年龄超过18岁的性别，
select gender,avg(age) from students group by gender having avg(age) > 18;
查询每组性别的平均年龄和名字
select gender,avg(age),group_concat(name) from students group by gender;
查询每种性别中的人数多余2个的性别和姓名
select gender,count() from students group by gender having count() > 2;
如果是取了别名，后面也要用别名，如：
select gender,count(*) as a from students group by gender having a > 2;

with rollup汇总的作用
在语法的最后加上with rollup会对所有数据进行统计。
select gender,count(*) from students group by gender with rollup;

Limit 限制记录，从多行数据中取出指定数量的数据。
Limit的使用：可以使用limit限制取出记录的数量，但limit要写在sql语句的最后。
语法：limit 起始记录，记录数
说明：1、起始记录时只从第几条记录开始取，第一条记录的下标是0
记录数是指从起始记录开始向后依次取的记录数。
例如：limit 1 2 既是从第一个位置开始取，取两条。
如果不写第一个参数，默认从0开始，如：limit 5 表示取出前五条数据。
标准的SQL书写格式：[]表示可以省略
Select 字段1，字段2 from 表名
[where 条件]
[group by 字段名]
[having 条件]
[order by 字段名 排序规则]
[limit 起始位置，数量];

分页查询：目标：能够使用公式计算limit查询的起始位置。
分页查询语法：select * from 表名 limit start=0,count
分页公式：
limit (第x页-1)*每页数量，每页数量

分页查询：
每页显示2个，第3个页面：
select * from students limit 4,2;
每页显示2个，第10个页面：
select * from students limit (10-1)*2,2;

连接查询：
连接概念：当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示。
Mysql支持多种类型的链接查询，分别为：
内连接:inner join
内连接查询：查询的结果为两个表匹配到的数据（交集）。

外连接：分为左外连接和右外连接。
右（外）连接查询：right join查询的结果为两个表匹配到的数据和右表（主表）特有的数据，对于左表中不匹配的数据使用null填充。

左（外）连接查询：left join查询的结果为两个表匹配到的数据和左表（主表）特有的数据，对于右表中匹配的数据使用null填充

连接查询语法：
左连接：主表 left join 从表on 连接条件;

内连接将两个表格连接后，连接后的字段是两个表的字段之和，连接后的行数，是两个表的行数相乘，因为表A的数据要跟表B的每一行都要进行连接，这就是笛卡尔积运算。
进行左连接时，是一行行进行匹配的，先找到从表的第一行数据，再从主表寻找匹配数据，然后继续找从表第二行，因此左连接的id排列顺序是不固定的，如：

连接查询：
内连接：inner join … on
两个表之间进行连接：select * from 表A inner join 表B;
select * from students inner join classes;
查询出每个学生对应的班级信息：
select * from students inner join classes where students.cls_id = classes.id;
按照要求显示姓名，班名
select students.name,classes.name from students inner join classelses where students.cls_id = classes.id;
给数据表取别名
select s.name,c.name from students s inner join classes c where s.cls_id = c.id;
内连接的另外一种写法,去掉inner join换成,连接两个表，后面的on换成where，如：
select s.name,c.name from students s,classes c where s.cls_id = c.id;

Inner join on上的on表示在某某条件下，作用和where一样，如：
查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，students.，只显示班级名称classes.name
select students.,classes.name from students inner join classes on students.cls_id = classes.id;
在以上的查询中，将班级姓名显示在第1列。
select classes.name, students.*from students inner join classes on students.cls_id = classes.id;
查询有能够对应班级的学生以及班级信息，按照班级编号进行排序。
select * from students inner join classes on students.cls_id = classes.id order by classes.id asc;
当是同一个班级时，按照学生的id 进行从小到大排序：
select * from students inner join classes on students.cls_id = classes.id order by classes.id asc，students.id asc;

外连接：
总字段依然等于两个表的字段之和
左（外）连接：主表 left join 从表 on 连接条件；
语法：select * from 主表 left join 从表 on 连接条件;

从左边主表去右边从表找满足连接条件的数据，如果找到，保留主表和从表对应的数据，如果没找到，左边主表数据依然保留，右边补null。

查询每一位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
查询没有班级对应的学生
select students.* from students left join classes on students.cls_id = classes.id where classes.id = null;
注意：不建议使用select * from students left join classes on students.cls_id =classes.id having classes.id is null;

右（外）连接：从表 right join 主表 on 连接条件;

语法：select * from 从表 right join 主表 on 连接条件;

从右边主表去左边从表找满足连接条件的数据，如果找到，保留主表和从表对应的数据，如果没找到右边主表数据依然保留，左边补null。
select * from students right join classes on students.cls_id = classes.id;
上语句可以查询出哪个班没有学生。

自连接查询：特殊的内连接，将自己的表和自己的表进行连接关联（自己连接自己）。
目标：能够说出自连接的作用以及自连接和普通连接上使用的区别。
自连接概述：

如需要查询一个省，比如广东省对应的所有的市信息，可以使用两个表连接查询。
自连接查询用法：

查询一共有多少个省
Select count(*) from areas where pid is null;
查询省的名称为‘山西省’的所有城市。
Select * from areas as city inner join areas as province on city.pid = province.aid where province.atitle=’山西省’;
如果要将脚本的数据导入表中吗可以使用：source 文件名.sql

子查询：
子查询概念：在一个select语句中，嵌入另外一个select语句，那么嵌入的select语句称之为子查询语句，外部select语句则成为主查询。
子查询和主查询的关系：子查询是嵌入到主查询中。子查询是辅助主查询的，要么充当条件，要么充当数据源。子查询是可以独立存在的语句，是一条完整的select语句。
嵌套时，子查询需要用括号括住。
子查询分类：
标量子查询：子查询返回的结果是一个数据（一行一列），如:
列子查询：返回的结果是一列（一列多行）
行子查询：返回的结果是一行（一行多列）
#表子查询：返回结果是一个表格（多行多列）
行子查询
查出高于平均身高的所有人的信息
Select * from students where height > (Select avg(height) from students);

列子查询：
查询学生的班级号能够对应的学生名字
select * from students where id in(select id from classes);

创建京东数据库
create database jing_dong charset=utf8;

use jing_dong;
创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment,
name varchar(40) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit default 1,
is_saleoff bit not null default 0
);

插入数据：
insert into goods values(0,‘r510vc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘3399’,default,default),(0,‘y400n 14.0英寸笔记本’,‘笔记本’,‘联想’,‘4999’,default,default),(0,‘g150th 15.6英寸游戏本’,‘游戏本’,‘雷神’,‘8499’,default,default),(0,‘x550cc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘2799’,default,default),(0,‘x240 超级本’,‘超级本’,‘联想’,‘4880’,default,default),(0,‘u330p 超级本’,‘超级本’,‘联想’,‘4299’,default,default),(0,‘svp13226scb 触控超级本’,‘超级本’,‘索尼’,‘7999’,default,default),(0,‘ipad mini 7.9英寸平板电脑’,‘平板电脑’,‘苹果’,‘1998’,default,default),(0,‘ideacentre c340 20英寸一体电脑’,‘台式机’,‘戴尔’,‘2899’,default,default),(0,‘imac me086ch/a 21.5英寸一体机’,‘台式机’,‘苹果’,‘9188’,default,default),(0,‘at7-7414lp 台式电脑linux’,‘台式机’,‘鸿基’,‘36999’,default,default),(0,‘z220sff f4f06pa工作站’,‘服务器/工作站’,‘惠普’,‘4288’,default,default),(0,‘poweredge ii服务器’,‘服务器/工作站’,‘戴尔’,‘5388’,default,default),(0,‘mac pro 专业级台式电脑’,‘服务器/工作站’,‘苹果’,‘2888’,default,default);

查询类型cate_name 为’超极本’的商品名称name、价格price
select name.price from goods where cate_name = ‘超级本’;
显示商品的种类
方法一：分组的方式
select cate_name from goods group by cate_name
方法二:去重的方式
select distinct cate_name from goods;
求所有电脑产品的平均价格，保留两位小数
select round(avg(price),2) from goods;
显示每种类型cate_name的平均价格
select cate_name,avg(price) from goods group by cate_name;
查询每种类型的商品中，最贵，最便宜，平均价格，数量。
select cate_name,max(price)/min(price)/avg(price)/count(price) from goods group by cate_name;
查询所有价格大于平均价格的商品，并且按照价格降序排序
select * from goods where price > (select avg(price) from goods) order by price desc;
查询每种类型中最贵的电脑的所有信息
select goods.* from goods inner join (select cate_name,max(price) as max_price from goods group by cate_name) as c on goods.cate_name = c.cate_name and goods.price = c.max_price;

SQL操作实战：优化商品表
上方创建的goods表格中，类型名称和品牌名称有多个数据是一样的，如果需要修改类型名称或者品牌名称，需要用update set语句进行修改
update 表名 set　列１　＝　值　where 条件列名　＝　值
update goods set cate_name =新型台式机 where cate_name = 台式机;
这么一修改，则将全部为台式机的类型全部都改为了新型台式机，达不到只修改一条数据的目的，如需修改一条，还要增加更多条件，代码复杂。

创建商品分类表
第一步：创建表（商品种类表goods_cates）
#if not exists 表示如果表不存在才创建。
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
第二步　同步　商品分类表　数据，将商品的所有（种类信息）写入到（商品种类表）中
按照分组的方式查询goods　表中的所有种类（cate_name）
首先写子查询的类型分组查询。
select cate_name from goods group by cate_name;
通过子查询插入数据到新的分类表中
insert into goods_cates(name) (select cate_name from goods group by cate_name);

第三步　　同步商品表数据，通过goods_cates数据表来更新goods表
update goods inner join goods_cates on goods.cate_name = goods_cates.name set goods.cate_name = goods_cates.id;

修改表结构字段名字不同change把cate_name改成cate_id
alter table goods change cate_name cate_id int unsigned not null;

创建品牌分类表：
1、查询品牌的名称
Select brand_name from goods group by brand_name;
子查询插将数据插入表goods_brands。
insert into goods_brands(name) (select brand_name from goods group by brand_name);

第二步：同步数据
更新goods
update goods inner join goods_brands on goods.brand_name = goods_brands.name set goods.brand_name = goods_brands.id;

第三步修改表结构
alter table goods change brand_name brand_id int unsigned not null;

外键使用：
假如往表中插入数据：
insert into goods(name,cate_id,brand_id,price) values(‘LaserJet pro P16０6dn’,12,4,‘1849’);
插入数据虽然显示成功，但是产品分类表中并没有12这个产品类型，所以类型无知。
这时候就需要添加外键约束，表和表之间的约束。
外键：一个表A的主键(primar key)，在另一个表B中出现，我们说A是B的一个外键
外键约束：对外键字段的值，在更新和植入时进行和引用的表中字段数据进行对标
foreign key,只有innodb数据库引擎支持外键约束。
语法：alter table 需要添加约束的表名 add foreign key(需要添加外键约束的列名) references 表名(列名)
对已经存在的字段添加外键约束：
给brand_id 添加外键约束和goods_brands的id建立外键关联。
alter table goods add foreign key(brand_id) references goods_brands(id);
给cate_id 添加外键约束和goods_cates的id建立关联
alter table goods add foreign key(cate_id) references goods_cates(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (jing_dong.#sql-1648_5, CONSTRAINT #sql-1648_5_ibfk_2 FOREIGN KEY (cate_id) REFERENCES goods_cates (id))
表示添加外键失败，表中已经有关键表不存在的数据。需要删除表中这条数据
delete from goods where cate_id = 12;

注意：添加外键时，两字段的数据类型必须完全一致，且必须有一个是主键字段。
对于不存在的表，可以在创建表的时候直接添加外键约束，而外键关联的表必须事先已经存在，如：创建goods_test表，字段为(id,name,cates_id,brands_id)，其中cate_id外键关联goods_cates的id 字段，brand_id外键关联goods_brands的is字段
create table goods_test(
id int unsigned primary key auto_increment, name varchar(40) not null, cate_id int unsigned not null, brand_id int unsigned not null, foreign key(cate_id) references goods_cates(id), foreign key(brand_id) references goods_brands(id) );

删除外键约束，需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称：
show create table goods;
获取名称之后就可以直接根据名称来删除外键约束
Alter table goods drop foreign key 外键名称;
使用到外键约束会极大地降低表更新的效率，所以在追求读写效率优先的场景下一般很少使用外键

视图：
是一张虚拟表，视图是对若干张基本表的引用，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）； 视图的本质其实就是一条被命名的SQL查询语句。
视图的好处：限制用户对数据的访问，使负责的查询变得简单，提供数据的独立性，对相同数据的不同显示。

定义视图：建议以V_开头
Create view 视图名称 as select语句;
若担心视图已存在，可以使用语句：create or replace view 视图名 as select语句;
和基本表的结构一致，不能有重复字段。
查看所有表或视图：show tables;
当原表数据发生改变，视图的数据也会发生改变。
注意：视图只用于查询，不要修改视图数据，因为视图本身没有存储数据。
若强制不允许改变视图的数据，可以在创建视图时使用with check option
语法：create or replace view 视图名 as SQL查询语句 with check option;
使用视图：查询时和使用普通表效果一致。
删除视图：drop view 视图名称; 如：drop view v_goods_info;
创建视图使用完整参数：
Create [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] as select语句 [WITH[CASCADED|LOCAL]CHECK OPTION];
ALGORITHM参数：
Merge:处理方式为替换式，可以进行更新真实表中的数据，为默认模式。
如果使用默认的替换式，则如果修改视图的数据，会把基表的数据也修改掉。
TEMPTABLE：具化式，由于数据存储在临时表中，所以不可以进行更新操作。
UNDEFINED：相当于没有定义ALGORITEM参数，mysql更倾向于选择替换方式，更有效。
WITH CHECK OPTION:更新数据时不能插入或更新不符合视图限制条件的记录。即不能不满足创建视图时select后面的where条件。
LOCAL和CASCADED：为可选参数，决定了检查测试的范围。默认值为CASCADED。
视图机制：
替换式：操作视图时，视图名直接被视图定义给替换掉，如操作视图info_salary_view:
语句：Select * from info_salary_view; 系统底层实际是将视图info_salary_view替换成创建视图的语句：select * from (select * from info where salary > 2000);
具化式：mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中，外面的select语句就调用了这些中间结果（临时表）如：
Select * from info_salary_view;系统底层实际是将创建视图的select语句先执行查询出来在内存保存为一个临时的表，再操作视图时替换视图,底层为：(select * from info where salary >2000) as temptable; select * from temptable；

替换式和具化式的区别：替换方式将视图公式替换后，当成一个整体sql进行处理，所以修改的数据会修改到真实的基表。具化方式则先处理视图结果，后处理外面的查询需求，由于数据存储在临时表中，所以不可以进行更新操作。
视图不可更新部分：聚合函数、DISTINCT关键字、group by字句、having字句、UNION运算符、from字句包含多个表等（即所有不在基表中的数据，就不能进行修改，所有来源于基表的数据都可以修改）

事务概念及特征：作为一个基本工作单位执行的一系列SQL语句操作，要么完全执行，要么完全不执行。
事务四大特征ACID
原子性(Atomicity)、一致性(consistency)、隔离性(isolation)、持久性(Durability)。
原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
一致性：事务执行的结果，必须使数据从一个一致性的状态转换到另一个一致性的状态。（双方的结果要一致，一边增加了1000，另一边减1000）。
隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务是隔离的。
持久性：一旦事务提交，则其所做的修改会永久保存到数据库。（操作完成结果持久不变）

事务的使用：
查看表引擎（数据表的存储方式）：数据库存储引擎是数据库底层软件组织，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能等，不同的存储引擎，还可以获得特定的功能。
查看数据库服务器支持的表引擎：show engines;
常见的有：InnoDB（默认格式，事务的表）、MyISAM（常见的，在5.0版本前默认的模式）、MEMORY（内存表，存在内存里，读取速度快,用于存储大量的临时数据）
查看表的创建语句show create table goods;，可以看到engine=innodb
如果表引擎不是innodb类型，需要做修改。
开启事务：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。
临时开启事务：begin 或者 start transaction
然后对数据进行增删改等操作。
提交事务：将缓存汇总的数据变更维护到物理表中
commit;
commit之后事务就提交完成，此时再rollback已经没用，如果再进行事务操作，需要重新开启事务。
回滚事务：放弃缓存中变更的数据，表示事务执行失败，应该回到开始事务前的状态。
rollback;
rollback后事务也会退出，如果再进行事务操作，需要再重新开启事务。
事务只能有一条DDL语句或一条DCL语句，因为执行DDL和DCL语句都会导致事务立即提交。
也可以使用：set autocommit = {0|1} 0为关闭自动提交，打开事务。
关闭自动提交后，所有DML语句都不会立即生效，上一个事务结束后，第一条DML语句将开启一个新的事务，后续执行的所有SQL语句都在事务中，除非显式使用commit提交或正常退出或使用rollback回滚或指定DDL、DCL语句导致事务隐式提交，、
一个MySQL命令行窗口代表一次连接Session，在该窗口设置set autocommit = 0相当于关闭了该连接Session的自藕丁提交，对其他客户端连接不会有任何影响，也就是对其他Mysql命令行窗口不会有任何影响。
此外，还可以使用语法：savepoint 中间点名 设置一个中间点，这样就可以使用：
rollback to 中间点名 回滚到指定的中间点，而不是回滚全部事务。回滚到中间点并不会结束事务，依然处于事务之中。

脏读：能看到别人未提交事务的操作，甲向乙转账1万，操作失误转了1.1万，乙刚好查看银行卡看到1.1万，但此时甲并为提交事务，发现错误后回滚，重新修改转账金额1万，此时乙实际收到1万， 但看到的是1.1万，这就是脏读，读提交能解决脏读问题
不可重复读：一个事务范围内两个相同的查询却返回了不同数据。如：在查询
幻读：甲查询乙的银行卡消费记录，打开事务后，发现消费2000元，刚好乙又消费了一万（即insert了一条记录），并提交，当甲打印消费记录时，发现是1.2万，这就是幻读
事务的隔离级别：
read uncommitted（读取未提交）：最低的隔离级别，允许读取尚未提交大数据变更，可能产生脏读、不可重复读或幻读

read committed（读取已提交）：允许读取并发事务已经提交的数据，没有提交不可以读取，可以防止脏读，但仍然可能产生不可重复度或幻读

repeatable read（可重复度）：开启事务读取某个数据时，不再允许修改操作，对同一个字段的多次读取结果都是一致的，除非数据是被本身的事务修改，可以阻止脏读和不可重复度，但仍然可能发生幻读

Serializable（可串行化）：最高隔离级别，完全服从ACID隔离级别，所有事务依次逐个执行，这样事务之间就完全不可能产生干扰，性能较差，该级别可以防止脏读、不可重复读以及幻读。设置隔离级别：
全局：set global transaction isolation level read committed;

当前会话：

权限操作：
权限：限制一个用户能做什么事情，在mysql中可以设置全局权限，指定数据库权限，指定表权限，指定字段权限。
权限种类：
Create:创建数据库、表或者索引权限
Drop:删除数据库或表权限
Alter：更改表比如添加字段、索引等权限
Delete:删除数据权限 Index:索引权限 Insert插入权限 Select查询权限 Update更新权限
Create view创建视图权限 execute执行存储过程权限。
创建用户：create user ‘用户名’@’localhost’ identified by ‘密码’;
删除用户：drop user’用户名’@’localhost’;
分配权限：
Grant 权限（columns） on 数据库对象（表、视图等） to 用户 identified by ‘密码’ with grant option;

数据库三范式：目标：知道一个表结构设计是否满足范式的要求。
数据库设计三范式：设计数据库提出了一些规范，被称为范式，目前有迹可循的共有8种范式，一般需要遵守3范式即可。
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。例如：保存一个联系人信息，一个字段直接保存电话和姓名等信息，不符合第一范式，第一范式要拆分成多个字段进行保存。
第二范式（2NF）：满足1NF。另外包含两部分内容。一是表必须有一个主键，二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖，既不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

1NF强调字段是最小单元，不可再分。
2NF强调在1NF基础上必须要有主键且非主键字段必须完全依赖于主键，也就是说，不能部分依赖。
3NF强调在2NF基础上，非主键字段必须直接依赖于主键，也就是说不能传递依赖（间接依赖）

数据库设计
SQL演练中所有的表：
1、创建商品分类表。
2、创建商品品牌表。
3、创建商品表。
前三均已经创建完毕
4、创建顾客表：
create table customer(
id int unsigned primary key auto_increment,
name varchar(30) not null,
addr varchar(100),
tel varchar(11) not null
);

5、创建订单表：
create table orders(
id int unsigned primary key auto_increment,
order_data_time datetime not null,
customer_id int unsigned,
foreign key(customer_id) references customer(id)
);
6、创建订单详情表：
create table order_detail(
id int unsigned primary key auto_increment,
order_id int unsigned not null,
goods_id int unsigned not null,
quantity tinyint unsigned not null,
foreign key(oeder_id) references orders(id),
foreign key(goods_id) references goods(id)
);

R模型及表间关系
目标：了解E-R模型的组成部分，能够举例说出生活1对1 1对多，多对多关系的例子。
R模型即E-R图，即实体-联系图，是指提供了表示实体型，属性和联系的方法，用来描述现实世界的概念模型。由美籍华裔陈品山发明
E=R模型的使用场景：
关系型数据库：关系模型的基础上，需要根须产品经理的设计策划，抽取出模型与关系，指定出表结构，这是项目开始的第一步。
在设计阶段一般使用E-R模型进行建模，有很多设计数据库的软件，常用的如：power designer,db designer等，可以直观的看到实体及实体间的关系。
设计数据库，可能是由专门的数据库设计人员完成，也可能是开发组成员完成，一般是项目经理带领组员来完成。
待设计完成E-R模型会将其转化为关系模型。
关系模型：用二维表的形式表示实体和实体间联系的数据模型。

R模型组成元素：

E-R图用实体、联系和属性这三个概念来描述现实问题。有以下三种元素：

实体型：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体，在E-R图中用矩形表示，矩形框内写实体名：比如 电商购物系统中的用户、购物车、订单等都是实体
属性：实体所具有的某一特性，一个实体由若干个属性来刻画，在E-R途中用椭圆形表示，并将无向边将其与相应的实体连接起来，比如用户的ID、用户名、密码、昵称、身份证号等
联系：实体彼此之间互相连接的方式称为联系，也称为关系，联系分为以下三种：
一对一：一个表的一条数据，能够和另外一个表的唯一一条数据对应（人和常住地址）
一对多：一个表的一条数据，能够和另外一个表的多条数据对应（一个班对应多个学生）
多对多：一个表的一条数据，能够和另外一个表的多条数据对应，相反也成立（学生和课程之间的关系）

关系也是一种数据，需要通过一个字段存储在表中
实体

索引：从属于数据表，存放在模式（schema）中的一个数据库对象。在数据字典中独立存放，不能独立存在，必须属于某个表。MySQL使用information_schema数据库的STATISTICS表来保存该数据库实例中的 所有索引信息。
索引是也是一种的文件（innodb数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的位置信息。类似于书的目录

作用：提升查询效率，使用快速路径访问方法来快速定位数据。
索引的分类：
单值索引：即一个索引只包含单个列，一个表可以有多个单值索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列：index multildx(id,name,age)
全文索引：只有在myisam引擎上才能使用
空间索引：只有在myisam引擎上才能使用，空间索引是对空间数据类型的字段建立的索引

索引的使用:查看表中已有索引：show index from 表名;
创建索引：
自动创建：定义主键约束、唯一约束和外键约束时，系统会为该列自动创建对应的索引。
主动创建：Create index 索引名称on 表名(字段1名称(长度)，字段2名称(长度)…)
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致，字段类型如果不是字符串，可以不填写长度部分
删除索引：数据表被删除时，表上的索引自动被删除，主动删除语法：
drop index 索引名称 on 表名;

Explain：用来检查索引是否正在被使用，并且输出其使用的索引的信息。
索引结构：先会对数据进行排序
btree索引：b + 树索引，b + 树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。
Hash索引：哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可李可定位到响应的位置，速度非常快。
Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次到位不像B-tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以hash索引的查询效率远高于Btree索引，但是hash索引只支持myisam引擎，使用较少。
哪些情况需要创建索引：
1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录，还会更新索引
5、Where条件里用不到的字段不创建索引
6、查询中排序的字段，排序的字段只通过索引去访问将大大提高排序速度
7、查询中统计或者分组字段
不需要添加索引的情况：
1、表记录太少
2、经常增删改的表
3、如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。
验证索引是否能够提升查询性能：
创建测试数据库：
Create database python_index_db charset=’utf8’;
创建测试表：
Create table test_index(title varchar(10));
使用python程序（ipython也可以）通过pymsql模块香表中加入十万条数据。
查询：
MySQL的Query Profiler 是一个实用非常方便的Query诊断分析工具，通过工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU、IO、IPC、SWAP等，以及发生的PAGE FAULTS，context SWITCHE等等，同时还能得到该Query执行过程中MYSQL所调用的各个函数在源文件的位置
通过执行set profiling命令可以开启Query Profile功能
开启运行时间监测：
set profiling=1;
查找10万条数据ha-9999
select * from test_index where title=‘ha-99999’
查看执行的时间：获取当前系统中保存的多个Query的profile的概要信息
show profiles
为表test_index的title列创建索引 title_index
create index title_index on test_index(title(10));
执行查询语句
select * from test_index where title= ‘ha-99999’;
再次查看执行的时间：
show profiles;
索引虽好，不过不能随意创建，建立太多的索引会影响更新和插入的速度，因为插入数据后，数据需要改变，所以值也要更新改变。对于已经经常需要更新和插入的表格，没有必要为一个很少使用的where字句创建索引。

加入一个表数据长期不变化，可以创建索引，经常变化的表格不创建。
用户管理：目标：能够使用grant创建一个用户并且授权。
MySQL账户管理；
MySQL账户体系：根据账户所具有权限的不同，MySQL的账户分为：
服务实例级账户：启动了一个mysqld，即为一个数据库实例，如果某用户如root,拥有服务实例级分配权限，那么该账户就可以删除所有的数据库以及表。
数据库级别账号：对特定数据库执行增删改查的所有操作
数据表级账户：对特定表执行增删改查等所有操作
字段级的权限：对某些表的特定字段进行操作
存储程序级别的账号：对存储程序进行增删改查的操作。
进行账户操作需要使用root账户登录。实例级账户登录后。
主要操作：查看所有用户、修改密码、删除用户
查看所有用户：（用户表在系统数据库mysql中的user表格上）
Use mysql;
Desc user;
主要字段说明：authentication_string表示密码，为加密后的值
查看所有用户：select host,user,authentication_string from user;
创建账户、授权：切换到实例级账户操作。
常用权限包括：create、alter、drop、insert、updata、delete、select
如果分配所有权限，可以使用all privileges 更新权限:flish privileges;
创建用户：create user ‘用户名’@’主机’ identified by ’密码’;
授权：grant 权限 on 数据库.表名 to “用户名”@主机;
创建一个laowang的账号，密码123456只能通过本地访问，并且只能对jing_dong数据库中的所有表进行读操作
mysql -uroot -p 回车后输入密码回车
创建用户：create user ‘laowang’@‘localhost’ identified by ‘123456’;
分配权限：
grant select on jing_dong.* to ‘laowang’@‘localhost’;
更新权限：
flush privileges;
创建一个laoli的账户，密码：123456 可以任意电脑进行连接访问，并且对jing_dong数据库中的所有表拥有所有权限。
创建用户：create user ‘laoli’@’%’ identified by ‘123456’;
分配权限:grant all privileges on jing_dong.* to ‘laoli’@’%’;
更新权限：flush privileges;
用root查看用户所有权限：show grants for ‘用户名’@主机;

账户管理：
目标：能够修改用户的密码和权限。
修改权限：
grant 权限名称 on 数据库 to 用户@主机 with grant option;
修改老王：grant select,update on jing_dong.* to ‘laowang’@’localhost’ with grant option;
修改密码：
使用root修改mysql数据库的user表。
使用password()函数进行密码加密：alter user ‘用户名’@”主机” identified by ‘密码’;
修改老王密码：alter user ‘laowang’@’localhost’ identified by ‘123’;

当忘记mysql密码：
解决方案：密码置空 + 修改密码
第一步：停止mysql服务
sudo service mysql stop
第二步：编辑MySQL配置文件：
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
sudo vim /etc/mysql/mysql.conf.d/mysqld.conf
在[mysqld]段下加入一行"skip-grant-tables"。让mysql跳过正常验证（非常危险，尽快操作），保存文件，退出编辑模式
第三部：启动mysql服务器
sudo service mysql start;
第四步控制台直接进输入mysql即可登陆到Mysql控制台。
第五步，修改密码，先设置root密码为空
use mysql
update user set authentication_string= ‘’ where user = ‘root’;
第六步，退出控制台，并修改mysql启动方式为普通模式，再次编辑mysql配置文件，注释或删除行skip-grant-tables保存退出。
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
第七步，重启mysql服务器，此时密码为空
sudo service mysql restart
第八步；使用root用户，密码空登陆
mysql -uroot -p
第九步：修改密码为mysql
alter user ‘root’@’%’ identified by ‘mysql’;
flush privileges;
第十步：验证是否能用新密码登陆。

远程登录（危险-慎用）
如果向一个乌班图中使用MYSQL命令远程连接另外一台mysql服务器的话，通过一下方式即可完成。此方法最好不要在实际生产环境中使用
修改root可以远程登录：
第一步：查看当前root的host值
Use mysql
Select host,user,authentication_string,plugin from user;
第二步：授权
Grant all on . to ‘root’@’%’;
第三步：刷新权限：flush privileges;
测试远程连接服务器：mysql -h192.168.31.124 -uroot -p
如果连接不上，可能原因：
1网络不通：通过ping ip地址可以发现网络是否正常。
2查看数据库是否配置了bind_address参数
本地登录数据库查看my.cnf文件和数据库当前参数show variables like ‘bind_address’如果设置了bind_address=127.0.0.1那么只能本机登录。
3查看数据库是否设置了skip_networking参数
如果设置了该参数，那么只能本地登录Mysql数据库
4端口是否正确

删除账户：
语法1，使用root登录删除
drop user “用户名”@”主机”;
语法2：使用root登录，删除MySQL数据库的user表中数据。
Delete from user where user = ‘用户名’
操作之后仍需更新权限：flush privileges;
推荐使用语法1。

清空整个数据表：语法：truncate table movie_link

主从配置：
主从配置：主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时一个服务器充当主服务器，其余的服务器充当从服务器，因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续链接主服务器，通过配置文件，可以指定复制所有数据，某个数据库，甚至是整个数据库上的某个数。
使用主从服务的好处：
提供服务可用性
通过增加从服务器提供数据库的性能，在主服务器执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
提高数据安全因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器响应数据。
主从同步的机制：
Mysql服务器之间的主从同步是基于二进制日志机制。
配置主从同步的步骤：
在主服务器上，必须开启二进制日志机制和配置一个独立的ID

Sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在主服器mysql服务器的配置文件[mysqld]添加三行：
log-bin=mysql-bin
Binlog_format=mixed
Server-id=101
重启服务器：sudo service mysql restart
登入主服务器乌班图的mysql，创建用于从服务器同步数据使用的账号
在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号。
在开始复制进程前，在主服务器上记录二进制文件的位置信息。
如果在开始复制之前，数据库中已经有数据，就必须创建一个数据快照（可以使用mysqldum导出数据，或者直接复制数据库文件。）
配置从服务器要连接的主服务器的IP地址和登录授权，二进制日志文件名和位置。
主和从的身份可以自己制定，将乌班图的MYSQL作为主，windows的mysql作为从。
配置主服务器原有数据到从服务器。
在主服务器乌班图上进行备份；
Mysqldump -uroot -pmysql --all-databases

JDBC
JDBC：java Database connectivity,即Java的数据库连接，是一种可以执行SQL语句的Java API,程序可以通过JDBC API连接到关系数据库，并使用结构化查询语句（SQL 数据库标准的查询语句）来完成对数据库的查询、更新。JDBC编写的程序不仅可以实现跨数据库（如果全部使用标准的SQL），还可以跨平台，具有非常优秀的可移植性。

Java提供了提供了一套标准的JDBC API，它们只是接口——实现类由各数据库厂商提供实现，这些实现类就是驱动程序，因此，使用JDBC只要面向标准的JDBC API编程即可。

JDBC主要完成以下三个基本工作：
1、建立与数据库的连接
2、执行SQL语句
3、获得SQL语句的执行结果
数据库驱动程序时JDBC程序与数据库之间的转换层，数据库驱动程序负责将JDBC调用映射成特定的数据库调用
JDBC驱动分为四种：
1、第一种：JDBC-ODBC桥，最早实现的JDBC驱动程序，访问Access这种伪数据库，不适 合在并发访问数据库的情况使用，在Java8已经删除
2、第二种：将JDBC API映射成数据库特定的客户端API，这种驱动包含特定数据库的本地 代码，用于访问特定数据的客户端。
3、第三种：支持三层结构的JDBC访问方式，主要用于Applet阶段，通过Applet访问数据 库
4、第四种：纯Java的，直接与数据库实例交互，这种驱动是智能的，知道数据库使用的底 层协议。
ODBC：open database connectibity，开放数据库连接，JDBC模仿了ODBC，ODBC也允许应用程序通过一组通用的API访问不同的数据库，也需要个数据库厂商提供驱动程序，而ODBC负责管理这些驱动程序。
JDBC相比ODBC的优势：
1、ODBC部分命令需要配置很多复杂的选项，JDBC采用更简单更直观的方式管理数据库。
2、JDBC比ODBC安全性更高，更易部署。
JDBC只是执行SQL语句的工具，允许对不同平台、不同的数据库采用相同的编程接口来执行SQL语句。

JDBC常用接口和类：
DriverManager：类，用于管理JDBC驱动的服务类，主要功能是获取Connection对象，方法：
Public static synchronized Connection getConnection(String url,String user,String pass) 该方法获得url对应数据库的连接。
Connection:代表数据库连接对象，每个Connection代表一个物理连接会话，该接口的方法：
Statement createStatement():返回一个Statement对象。
PreparedStatement prepareStatement(String sql)：返回预编译的Statement对象。即将SQL语句提交到数据库进行预编译。
CallableStatement prepareCall(String sql)：返回CallableStatement对象，用于调用存储过程。
Savepoint setSavepoint ()：创建一个保存点
Savepoint setSavepoint (String name)：以指定名字创建保存点
Void setTransactionIsolation(int level)：设置事务隔离级别
Void rollback()：回滚事务
Void rollback(Savepoint savepoint )：将事务回滚到指定保存点
Void setAutoCommit(boolean autoCommit)：关闭自动提交，打开事务。
Void commit():提交事务
还有setSchema(String schema)和getSchema()用于控制Connection访问数据库的schema
setNetworkTimeout(Executor executor,int milliseconds)和getNetworkTimeout()控制数据库连接的超时行为。

Statement：用于执行SQL语句的工具接口。方法：
ResultSet executeQuery(String sql)：执行查询语句，返回对应ResultSet对象。
Int executeUpdate(String sql)：执行DML语句，返回受影响行数
Boolean execute(String sql)：可以执行任何SQL语句，若执行后第一个结果为ResultSet对象，则返回true,若返回受影响行数或无结果则返回false;
closeOnCompletion() 如果Statement执行了该方法，则当所有依赖于该Statement的ResultSet关闭时，该Statement会自动关闭，isCloseOnCompletion()方法判断Statement是否打开了closeOnCompletion

PreparedStatement：预编译的Statement对象。是Statement的子接口，允许数据库预编译SQL语句，以后每次只改变SQL命令的参数，避免数据库每次都需要编译SQL语句，性能更好。使用PreparedStatement执行SQL语句时，无需再传入SQL语句只要为预编译的SQL语句传入参数即可。
Void setXxx(int parameterIndex,Xxx value)：根据传入参数值的类型不同，需要使用不同的方法，传入的值根据索引传给SQL语句中指定位置的参数。
执行executeUpdate()和executeQuery()方法无需在传入SQL命令，因为PreparedStatement对象已经预编译了SQL命令，只要为这些命令传入参数即可。

ResultSet：结果集对象，可以通过列索引或列名获得列数据。通过如下方法移动记录指针。
Void close()：释放ResultSet对象
Boolean absolute(int row)：将结果集的记录指针移动到第row行，如果row是负数，则移动到倒数第row行，如果移动后的记录指针指向一条有效记录，则方法返回true.
Void beforeFirst():将结果集的记录指针移动到首行之前，这是ResultSet结果集最初始的状态。——即记录指针的起始位置位于第一行之前
Boolean first()：将结果集的记录指针移动到首行，如果移动后的记录指针指向一条有效记录，则方法返回true.
Boolean previous()：将结果集的记录指针移动到上一行，如果移动后的记录指针指向一条有效记录，则方法返回true.
Bolean next()：将结果集的记录指针移动到下一行，如果移动后的记录指针指向一条有效记录，则方法返回true.
Bolean last()：将结果集的记录指针移动到最后一行，如果移动后的记录指针指向一条有效记录，则方法返回true.
Void beforeFirst():将结果集的记录指针移动到最后一行之后

ResultSet可以通过getXxx(int columnIndex)或getXxx(String columnLabel)方法获取当前行、指定列的值，前者根据列索引获取值，后者根据列名获取值。
getObject(int columnIndex,Class type)或getObject(String columnLabel,Class type)可以获取任意类型的值。
JDBC编程步骤：
1、加载数据库驱动
语法：Class.forName(DriverClass);//可以省略，新版JDBC驱动可以通过SPI自动注册。
Class.forName(“Com.mysql.cj.jdbc.Driver”);
加载驱动时，并不是真正使用数据库的驱动类，只是使用数据库驱动类名字符串而已。
2、获取数据库连接
DriverManager.getConnection(String url,String user,String pass);
Url写法：jdbc:mysql://主机名：port//databasename
3、通过Connection对象创建Statement对象。
createStatement():创建一个Statement对象。
prepareStatement(String sql)：根据传入的SQL语句创建预编译的Statement对象。即将SQL语句提交到数据库进行预编译。
prepareCall(String sql)：根据传入的SQL语句创建CallableStatement对象，用于调用存储过程。
4、使用Statement对象执行SQL语句
ResultSet executeQuery(String sql)：执行查询语句，返回对应ResultSet对象。
Int executeUpdate(String sql)：执行DML语句，返回受影响行数
Boolean execute(String sql)：可以执行任何SQL语句，若执行后第一个结果为ResultSet对象，则返回true,若返回受影响行数或无结果则返回false;

创建模块规范：公司名称.项目名称.当前模块名称
创建连接：
0、查看驱动相关API
1、加载驱动：把com.mysql.jdbc.Driver这份字节码加载进JVM，当一份字节码被加载到JVM时，就会执行该字节码中的静态代码块。
Class.forName(“com.mysql.jdbc.Driver”);
2、获取连接对象
String url = “jdbc:mysql://localhost:3306/mydb”;
String user = “root”;
String password = “mysql”;
Connection conn = DriverManager.getConnection(url,user,password);

连接数据库时出现异常：
Exception in thread “main” java.sql.SQLException: The server time zone value ‘?й???’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specifc time zone value

原因分析：
数据库安装时默认为英语,0:00时区,而中国是东八区,(英国0:00的时候,中国是早上8:00)比默认时区早8个小时
解决方案：
方法1: 配置JDBC连接参数,在url连接字符串后面加上?serverTimezone=UTC
方法2. 修改MySQL数据库配置，需要DBA的root权限

使用root用户登录mysql
–查看时区值
show variables like ‘%time_zone%’;
–设置为东八区（北京时间）
set global time_zone=’+8:00’;
所以mysql返回的时间会比实际时间要早8小时。

操作mysql创建表：
1、加载驱动
2、连接数据库
3、创建要执行的sql语句
String sql = “create table stu(id int,name varchar(50),age int)”;
Statement st = conn.createStatement();
Statement接口，用来执行静态SQL语句对象，把SQL语句发送到指定数据库库中去执行，并返回执行的结果(DQL返回查询结果集，DML返回受影响的行数，DDL返回0)，executeUpdate(String)：执行DML和DDL
4、执行sql
int row = st.executeUpdate(sql);
如果是DQL即查询语言，则使用int row = st.executeQuery(sql);

5、释放数据资源
st.close();
conn.close();

Connection链接就相当于java到mysql之间建立管道，链接只链接到数据，statement就相当于从数据库又接了一个管道链接mysql的执行程序。

查询操作DQL：
步骤和dml、DDL基本一致，不同的是：
1、DDL执行sql时返回的是一个结果集。
2、DDL执行sql时使用的是st.executeQuery();
结果集：ResultSet 表示数据库查询的结果的集合，在执行查询语句时就会得到一个这样的结果。
常用方法：
Boolean next():判断是否有下一行数据，若有，则向下移动一航指针
GetXxx(int colunmindex)：获取当前行中，第几列（从1开始）,不推荐
GetXxx(String columnName)：获取当前行中的指定列名的列值，可以写列名或者别名。
若列的类型是VARCHAR/CHAR/TEXT都用getString来获取，若列的类型是int/integer都是用getInt来获取列的值。

DAO设计：Data Access Object(数据访问对象)，位于业务逻辑和持久化数据之间。实现对持久化数据的访问。

ORM：对象关系映射，将关系数据库中表中的的记录映射成对象，以对象的形式展现，因此ORM的目的就是为了方便开发人员以面向对象的思想来实现对数据库的操作。

Domain:就是一个类，符合javaBean规范：一个类当中有字段和该字段的getter与setter方法
作用：是用户与数据交互的核心中转站。
DAO方法设计：

DAO设置规范：
编写DAO组件：1、定义DAO接口 2、编写对应DAO实现类
面向接口：接口就是指给出函数声明，但是没有函数体类，函数体在实现类中给出。
面向接口编程：根据客户提出的需求，定义接口，业务具体实现通过实现类来完成，当客户提出新的需求，只需要编写该业务逻辑的实现类。

好处：1、业务逻辑更加清晰2、增强代码的扩展性，可维护性3、接口和实现相分离，适合团队协作开发4、降低耦合度，便于以后升级扩展。

包名规范：整体规范：公司域名倒写.模块名称（项目名等）.组件名称
DAO包名规范：
package com.it666.jdbc.dormain 存储所有domain
Package com.it666.jdbc.dao 存储所有dao接口
Package com.it666.jdbc.dao.impl存储所有dao接口实现类
Package com.it666.test存储dao组件的测试类

类名规范：
Domain类命名，存储在domain包中，用于描述一个对象，是一个javaBean写时要见名知意。

dao接口命名：存储在dao包中，用于表示某一个对象的CRUD声明，起名规范：
接口-domain的名称-dao 如IDomainDao
如：domain是一个Students类，则dao接口的命名为：IStudentDao

Dao实现类命名：存储到dao.impl包中，用于表示dao接口的实现类，要实现dao接口
：domain的名称-dao-Impl
如：domain是一个Students类，则dao实现类命名为：StudentDaoImpl

测试类命名：domain的名称DaoTest
把相应类的包导入：ctrl shift + o
开发步骤：
1、创建表
2、建立domain包和domain类
3、建立dao包和dao接口
4、建立dao.impl包和dao实现类
5、根据dao接口创建dao测试类
6、编写实现类当中dao声明的方法体
7、每编写一个dao方法，进行测试功能是否正确
8、创建包

DAO代码重构：
1、每一个DAO方法当中都会写驱动名称：url、用户名、密码
2、每个DAO当中都会相同的4行代码

3、每个ADO方法每次操作只需要connection对象，至于怎么样创建的不关心
4、每次调用getConn就会创建一个Connection对象，但不需要每次都注册驱动
5、每个dao方法都要关闭资源
6、Dao方法中，拼接SQL太麻烦
需要使用预编译语句，

上面的内部优化mySQL不支持，ORACLE支持。
预编译语句：当没有预编语句时，所有的字符串都是进行拼接。
预编译语句：PreparedStatement用于预编译模板SQL语句，在性能和代码灵活上有显著地提高，PreparedStatement对象使用？作为占位符，即参数标记；
使用setXXX(index,value)方法将值绑定到参数中，每个参数标记是其顺序位置引用，index从1开始。
PreparedStatement对象执行SQL语句：
ExecuteQuery()

executeUpdate()
statement接口：用于进行java程序和数据库之间的数据传输。
三个实现类：
1、Statement：用于对数据进行通用访问，使用的是静态sql
2、PreparedStatement：用于预编译模板SQL语句，在运行时接受sql输入参数,当没有预编译语句时，要进行sql语句的拼接就使用PreparedStatement
3、CallebleStatement:

预编译语句：PreparedStatement用于预编译模板SQL语句，在性能和代码灵活上有显著提高，PreparedStatement对象用？作为占位符，即参数标记，使用setXXX(index,value)方法将值绑定到参数中，每个参数标记是其顺序位置引用，注意index从1开始
如果程序不清楚预编译SQL语句中个参数的类型，则可以使用setObject()方法来传入参数，由preparedStatement来负责类型转换。
内部优化：

如上方图，每条新的语句都会先到预编译池找，如果预编译池有该语句，就直接执行该语句，如果没有，会对新语句进行编译执行，多出编译的步骤，如果使用预编译语句，select * from emp where age = ? 该语句带问号，放入编译池后，如果重复使用会直接执行，不需要再次编译，只需要重新传入不同的参数，大大提高执行效率，该语句是先编译再设置参数的。
oracle支持预编译池，mysql不支持
7、DAO方法当中每次都创建一个connection对象，用完就关闭了，创建connection成本很大
8、JdbcUtil当中的用户名，密码这写信息都写到文件当中，不便于维护。

Sql注入：通过把sql命令插入到web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

PrepareState能够防止防止注入：因为把单引号转义了，变成 \’ 这样一来，就无法判断SQL语句，进而无法拼接SQL语句，基本没有办法注入了。如下：
select * from user where name =’’ OR 1=1 OR’’ and pwd = ‘12’
登录失败

Statement接口的第三个实现类：
主要用于调用数据库的存储过程
CallableStatement:
//1、链接数据库
Connection conn = JDBCUtil.getConn();
//2、调用存储过程
CallableStatement cs = conn.prepareCall("{call getStu(?)}");
//3、设置参数
cs.setString(1,“李白”);
//4、执行存储过程
ResultSet rs = cs.executeQuery();

调用带返回值的存储过程：

//1.连接数据库
Connection conn = JDBCUtil.getConn();
CallableStatement cs = conn.prepareCall(“call getName(?,?)”);
cs.setInt(1, 5);
cs.registerOutParameter(2, Types.VARCHAR);//存储过程返回的参数，只需要指明该参数是什么类型即可，因此用Types.VARCHAR
cs.execute();//不需要加query或者update等参数
//执行完有返回值的存储过程之后，就可以通过getXxx的方法去获取返回值。
String name = cs.getString(2);
System.out.println(name);

Jdbc处理事务：
1、关闭自动提交：在事务开始前输conn.setAutoCommit(false);
2、没有问题时，提交事务：整体事务结束后输conn.commit()
3、出线异常时，进行回滚操作：conn.rollback();
回滚之后，事务结束，释放资源。
如果出现异常，系统非正常退出，事务也会自动回滚，如果捕获了该异常，则需要在异常处理快显式地回滚事务。
4、只有增、删、改才需要事务，查询不需要
5、如果发现代码是正确的，测试也成功，但数据库当中的数据不变：
6、InnoDB才支持外键和事务，MyISAM不支持外键和事务。
设置事务的隔离级：
conn.setTransactionIsolation(Connection.TRANSACTION_REPATABLE_READ)；
Connection也提供设置中间点的方法：setSavepoint(),设置中间点后，使用rollback()回滚事务后，只会回滚到中间点，而不会直接回滚整个事务，回滚到中间点后，依然处于事务中。
JDBC批处理：一次性执行多条sql语句，运行多条语句一次性提交给数据库批量处理，执行效率高。
批量更新：多条SQL语句被作为一批操作同时收集，并同时提交。
批量更新必须得到底层数据库的支持，可以调用DatabaseMetaData的supportBatchUpdates()方法来查看底层数据库是否支持批量更新。
批量更新方法：addBatch(String sql):添加需要批量处理的sql语句
ExecuteBatch();执行批处理
若在批处理添加select查询语句，程序将出现错误。
支持情况：mysql默认情况不支持批处理，从5.1.13开始，需要在数据库名后面添加一个rewriteBatchedStatements参数：设置?rewriteBatchedStatements=true
Public static String url = “jdbc:mysql://localhost:3306/jdbc_db?rewriteBatchedStatements=true”;

Connection conn = JDBCUtil.getConn();
String sql = “insert into student(name,age) values(?,?)”;
PreparedStatement ps = conn.prepareStatement(sql);
long begin = System.currentTimeMillis();
for(int i = 0;i<1000;i++) {
ps.setString(1, “zs”);
ps.setInt(2, 10);
//添加到批处理
ps.addBatch();
}
//执行批处理
ps.executeBatch();
long end = System.currentTimeMillis();
long time = end - begin;
System.out.println(time);

	JDBCUtil.close(conn, ps, null);

存储图片：
数据库当中的BLOB：存储图片、音频、视频等多媒体信息，以二进制流的形式
真正开发中：不会把二进制的文件存放在数据库当中，把文件存储的路径保存在数据库当中，以后再取出路径，到该路径下读取信息。
BLOB类型：
TINYBLOB:255个字节
BLOB:65535字节
MEDIUMBLOB:16M
LONGBLOB:4G
操作：
1、在表中添加一个字段，类型选择blob类型
2、通过代码存入一张图片到数据库当中：
ps.setBlob(1,new FileInputStream(“d:/Pictures/1.png”));
3、从数据库当中把存储的图片取出

从数据库中取出图片：
Connection conn = JDBCUtil.getConn();
String sql = “select * from student where id = ?”;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
ResultSet res = ps.executeQuery();
if(res.next()) {
Blob blob = res.getBlob(“img”);
InputStream in = blob.getBinaryStream();

		//把程序当中的文件写到磁盘
		Files.copy(in,Paths.get("C:\\Users\\Administrator\\Desktop\\1.jpg"));	
	}
	JDBCUtil.close(conn, ps, res);

获取自动生成的主键：
在设置表时，会设置自动增长的主键，有时会插入数据时，想要知道生成的主键是多少。
需求场景：
用户注册时，添加用户名密码后：插入数据库当中
跳转到完善个人信息页面：完善信息更新到刚插入的那个记录当中，所有要获取刚插入的那个主键是多少，不能使用用户名做条件，用户名有重名的情况。

获取方法：
1、Statement语句：创建语句时，设置可以获取主键：
st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
通过语句对象的getGeneratedKeys获取主键

2、Preparement语句：在创建语句时传入参数：
PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
通过语句对象的getGeneratedKeys获取主键

连接池：
没有连接池时：
每次crud操作都要使用数据库的时候，都要创建一个数据库链接对象，普通的jdbc数据库链接使用DriverManager来获取，每次向数据库建立链接的时候都要将Connection加载到内存中，然后再验证用户名花费0.05s~1s左右，每次crud操作就向数据库要一个链接，执行完成后再断开链接，这样的方式将会消耗大量的资源和时间。
数据库的链接资源并没有得到很好的重复利用，多人频繁进行数据库链接操作时，将占用很多的系统资源，有可能会使系统崩溃。

数据库连接池：
池：保存对象的容器
连接池：保存数据库连接对象的容器
作用：初始化时创建一定数量的对象，需要时直接从池中取出一个空闲对象。
用完后并不直接释放掉对象，而是放到对象池中以方便下一次对象请求可以直接复用
池技术的优势是，可以消除对象创建所带来的延迟，从而提高系统的性能。
数据库连接池：就是为数据库连接创建一个“缓存池”，预先在缓存池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓存池”中取出一个，使用完毕之后再放回去。可以通过设定连接池最大连接数来放置系统无尽的与数据库连接，可以通过连接池的惯例几只监视数据库的链接的数量、使用情况，为系统开发、测试及性能调整提供依据。

连接池本质是一个集合，队列形式的list集合，两头都开。
连接对象是以链表形式存放，已释放的连接放到最后。从最前面获取连接对象。（即有客户端获取连接端详时，从连接池上方拿连接，用完后释放资源，连接对象排到连接池后面）

连接池中的属性：合理的设置连接池的属性，会提高链接池的性能：
1、链接数据库时需要的四个要素：驱动名称、数据库地址、用户名、密码
2、初始化连接数：初始化时，连接池当中创建多少个Connection对象
3、最大连接数：连接池当中最多存储多少个Connection对象
4、最小连接数：连接池当中最少存多少个Connection对象
5、最大的空闲时间：如果一个程序获取了链接对象，在指定时间内没有任何动作，就会自动释放链接（即收回连接池）。
6、最大等待时间：在指定时间内，尝试获取连接，如果超出了指定时间，就会提示获取失败。

连接池使用：
连接时是使用javax.sql.DataSource接口来表示连接池
DataSource和jdbc一样，也是只提供一个接口，由三方组织来提供
常用连接池：
DBCP:Spring推荐，Tomcat的数据源使用的就是DBCP
C3P0：C3P0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布，从2007年就没有更新了，性能比较差（不使用）。
Druid:阿里巴巴提供的连接池-德鲁伊-号称最好的连接池，有更多的功能，国产。
DataSource数据源和连接池Connection Pool是同一个东西，只是叫法不同。
使用连接池和不使用连接池的区别：
1、获取方式不用：传统：Connection conn = DriverManager.getConnection(url,userName,pwd);
连接池：Connection conn = DataSource对象.getConnection();

2、释放资源不同：传统：和数据库断开conn.close();
连接池：把数据库链接对象还给连接池，还可以供下一个人来使用。

连接池操作：主要是学习如何创建DataSource对象，再从DataSource对象中获取Connection对象，这写都是第三方提供好的，直接使用就行，获取连接对象之后，其余的操作和以前是一样的 不同的数据库连接池，就是在创建DataSource上不一样。

创建DataSource:
使用DBCP:
1、导入相关的jar包：a、commons-dbcp-1.4.jar b、commons-pool-1.5.6.jar
两个jar包的下载地址：
http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
http://commons.apache.org/proper/commons-pool/download_pool.cgi
下载bin.zip包，解压就能得到jar包，带src的包是带有源代码的包

使用连接池运行时如果报错：
NoClassDefFoundError: logging LogFactory
这时可以下载一个commons-logging.jar包添加到项目中，然后就可以执行了

2、在项目中使用连接池来获取连接：
String url = “jdbc:mysql://localhost:3306/jdbc_db?rewriteBatchedStatements=true”;
String user = “root”;
String password = “mysql”;
String driverName = “com.mysql.jdbc.Driver”;

BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(driverName);
ds.setUsername(user);
ds.setPassword(password);
ds.setUrl(url);

配置文件：资源文件，以.properties作为扩展名的文件，在上面使用数据库过程当中，把库的链接地址、用户名、密码都写在了代码当中，不便于维护：项目部署到别人的服务器当中，别人电脑中的数据库用户名和密码不是这一个root和mysql，此时就要去到项目中找对应的代码去修改账号和密码的地方。
但是部署项目时实施人员做的，他们不懂java代码，为了安全操作，不能让他直接修改java代码，此时，需要专门为数据库提供一个配置文件，里面就专门存放连接数据库的相关信息。
配置文件的书写：db.properties 内部是以key-value的形式存放：userName=root password=1234.
在web项目单独创建一个文件存放这个配置文件，右键项目——>new——>resource
必须用resource文件存储，只有src和resource folder两种文件才会编译到字节码文件。
配置文件：文件名必须写全

读取配置文件：

通过配置文件方式获取数据源：

当eclipse运行时显示找不到该类时，且项目出现红色感叹号，这时是因为工程中classpath中指向的包路径错误，此时需要右键web工程，build path—>Configure Build Path…找到libraries，把打叉的一行(JRE System Library [jdk11.08])remove,然后重新add进来，

点击应用即可，

使用Druid:开源地址：https://github.com/alibaba/druid
下载地址：http://druid.apache.org/downloads.html
Druid是一个JDBC组件库，包括数据库连接池、SQL Parser等组件。DruidDataSource是最好的数据库链接池。

最终代码重构：在dao当中执行的保存方法、更新、删除这些DML操作有太多重复代码。

重构代码问题：
同一个类中：有多个方法当中有太多相同的代码，不同的地方通过 参数传递进去，把他们抽取到另一个方法当中
不同类中：不同类中有共同的代码，给抽到一个心累当中，大家共享该类中的内容。

抽取DML方法：1、设计一个方法，要求传入参数，一个SQL语句的String参数，一个可变类型的参数。
返回值为int，即受影响的行数。
//可变参数的本质是一个数组，
public int executeUpdate(String sql,Object…params) {
Connection conn = null;
PreparedStatement ps = null;
// TODO Auto-generated method stub
try {
conn = JDBCUtil.getConn();
ps = conn.prepareStatement(sql);
//遍历参数
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
//4.执行sql
return ps.executeUpdate();
}catch(Exception e) {
e.printStackTrace();

	}finally {
		JDBCUtil.close(conn, ps,null);		
	}
	return 0;
}

@Override
public void save(Student stu) {
String sql = "insert into student(name,age) values(?,?)";	
	this.executeUpdate(sql,stu.getName(), stu.getAge());		
}

DQL抽取：

遗留问题：查询的时候只能查询学生，需要处理结果集：
处理结果集：把处理结果集的行为交给每个DAO，为了规范每个DAO的处理集，大家都叫同样的名字，需要定义一个处理结果集的接口：

结果集处理器添加泛型：

内省：
Class类型：即字节码类型，所有的字节码把他们当做一个群体类型。
通过字节码创建对象：Student stu = Student.class.newInstance();

内省：用于查看和操作JavaBean中的属性（即getter和setter方法）。
Introspector类：
Introspector.getBeanInfo(A.class,B.class) :获取指定类当中的字节码属性信息
获取A类及A类父类当中的 信息，但是不包括指定的类(B类），返回值BeanInfo
BeanInfo：getPropertyDescriptors() 获取所有的属性描述器，返回getPropertyDescriptor[]
PropertyDescriptor[] pds = beaninfo.getPropertyDescriptors();
获取get方法
for(PropertyDescriptor pd:pds) {
//获取所有属性的名称
//System.out.println(pd.getName());
//获取所有get方法。
// System.out.println(pd.getReadMethod());
//获取set方法
System.out.println(pd.getWriteMethod());
//获取到set方法后.invoke，后面的u表示传入u对象去执行该方法，即将u对象的属性值设置为111.
pd.getWriteMethod().invoke(u, “111”);
}

DBUtils：DBUtils是Apache公司编写的数据库操作使用的工具，小巧，简单，使用封装了对JDBC的操作，简化了JDBC操作。
下载：http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

QueryRunner:QueryRunner(DataSource ds):提供数据库连接池，会自动帮你创建连接。
Update(String sql,Object…obj):执行更新数据
Query(String sql,ResultSetHandler rsh,Object…params):执行查询
ResultHandler:
query(sql,new BeanHandler(Student.class),params):把查询结果封装成一个指定对象
query(sql,new BeanHandler(Student.class)):把查询结果封装成一个指定对象集合
qr.query(sql,new ScalarHandle()):查询单个值，返回一个long类型。
qr.query(sql,new MapListHandle()):把查询结果封装成一个Map集合
query(sql,new ColumnListHandler(“列名”))：查询指定的列