SQL&JDBC注意要点

where 和 having 的区别？(记忆)

• where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来

• where 后不可以跟聚合函数，having可以进行聚合函数的判断。

什么是事务(记忆)

• 介绍

1. 概念：    多个增删改操作的时候，我们通常把这几步步骤方法事务中去执行；
	*  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2. 操作：
	1. 开启事务： start transaction;
	2. 回滚：rollback;   回到事务开启之前的数据状态；
	3. 提交：commit;     就是sql执行成功；

• MySQL数据库中事务默认自动提交

* 事务提交的两种方式：
		* 自动提交：
			* mysql就是自动提交的
			* 一条DML(增删改)语句会自动提交一次事务。
		* 手动提交：
			* Oracle 数据库默认是手动提交事务
			* 需要先开启事务，再提交
* 修改事务的默认提交方式：不建议修改
		* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
		* 修改默认提交方式： set @@autocommit = 0;

事务的四大特征(面试题)

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。   增删改  commit;   
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变      1000/1000 commit 500/1500 rollback 1000/1000

事务的隔离级别与问题(了解)

* 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
* 存在问题：
	1. 脏读：一个事务，读取到另一个事务中没有提交的数据(读取到数据可能会,被回滚)
	2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。(即读到其他事务进行提交的数据,然而进行单个事务过程中的某些需求下是不可取的)
	3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
* 隔离级别：
	1. read uncommitted：读未提交
		* 产生的问题：脏读、不可重复读、幻读
	2. read committed：读已提交 （Oracle）
		* 产生的问题：不可重复读、幻读
	3. repeatable read：可重复读 （MySQL默认）
		* 产生的问题：幻读
	4. serializable：串行化    java中的多线程的同步锁； 线程安全，效率低；
		* 可以解决所有的问题

	* 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
	* 数据库查询隔离级别：
		* select @@tx_isolation;
	* 数据库设置隔离级别：
		* set global transaction isolation level  级别字符串;

* 演示：
	set global transaction isolation level read uncommitted;
	start transaction;
	-- 转账操作
	update account set balance = balance - 500 where id = 1;
	update account set balance = balance + 500 where id = 2;

参考链接

SQL操作语句总结

• SQL分类

1) DDL(Data Definition Language)数据定义语言
		用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作语言
		用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询语言
		用来查询数据库中表的记录(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制语言(了解)
		用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

• 操作数据库、表(了解)

1. C(Create):创建
	 创建数据库：
		 create database 数据库名称;
	 创建数据库，判断不存在，再创建：有用
		 create database if not exists 数据库名称;
	 创建数据库，并指定字符集
		 create database 数据库名称 character set 字符集名;
2. R(Retrieve)：查询
	 查询所有数据库的名称:
		 show databases;
	 查询某个数据库的字符集:查询某个数据库的创建语句
		 show create database 数据库名称;
3. U(Update):修改
	 修改数据库的字符集
		 alter database 数据库名称 character set 字符集名称;
4. D(Delete):删除
	 删除数据库	
		 drop database 数据库名称;
	 判断数据库存在，存在再删除
		 drop database if exists 数据库名称;
5. 使用数据库
	 查询当前正在使用的数据库名称
		 select database();
	 使用数据库
		 use 数据库名称; 有用

• 操作表(了解)

1. C(Create):创建
	1. 语法：
		create table 表名(
			列名1 数据类型1 约束,
			列名2 数据类型2 约束,
			....
			列名n 数据类型n 约束
		);
		 注意：最后一列，不需要加逗号（,）
		 数据库类型：
			1. int：整数类型
			2. double:小数类型
				 score double(5,2)  5 是包括小数点两边的数字的个数
			3. date:日期，只包含年月日，yyyy-MM-dd
			4. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
			5. timestamp:时间错类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
				 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
			6. varchar：字符串
				 name varchar(20):姓名最大20个字符
				 zhangsan 8个字符  张三 2个字符
	 复制表：
		 create table 表名 like 被复制的表名;	  	
2. R(Retrieve)：查询
	 查询某个数据库中所有的表名称
		 show tables;
	 查询表结构
		 desc 表名;
3. U(Update):修改
	1. 修改表名
		alter table 表名 rename to 新的表名;
	2. 修改表的字符集
		alter table 表名 character set 字符集名称;
	3. 添加一列
		alter table 表名 add 列名 数据类型;
	4. 修改列名称 类型
		alter table 表名 change 列名 新列别 新数据类型;
		alter table 表名 modify 列名 新数据类型;
	5. 删除列
		alter table 表名 drop 列名;
4. D(Delete):删除
	 drop table 表名;
	 drop table  if exists 表名 ;

• 数据操作(精通)

1. 添加数据：
	 语法：
		 insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n),(值1,值2,...值n)...;
2. 删除数据：
	 语法：
		 delete from 表名 [where 条件]   -- 删除表数据，自增列不重新开始
		 TRUNCATE TABLE 表名;   -- 摧毁表，主键自增重新开始
3. 修改数据：
	 语法：
		 update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

• 数据查询(精通)

去除重复： distinct
1. 基础查询
	select 字段名 from 表名;
2. 条件查询
	select 字段名 from 表名 where 字段名 = 条件;
3. 分组查询
	select 字段名 from 表名 where 字段名 = 条件 order by 字段名 asc 升序 / desc 降序
4. 聚合函数  
	sum 求和  avg  求平均数   max 求最大值  min 求最小值  count 求个数
	sum内可以添加条件,如:sum(score>60)
	count内不能进行书写条件
	select sum(字段名) from 表名
5. 分组查询   
	group by 字段名 having 过滤  通常结合聚合函数一同使用
	select 分组字段名, sum(字段名) from 表名 group by 分组字段名 
6. 分页查询   limit 0,5   limit 5,5      limit 3 查询前3条数据
	SELECT  FROM student LIMIT 0,3; -- 第1页
	SELECT  FROM student LIMIT 3,3; -- 第2页
	SELECT  FROM student LIMIT 6,3; -- 第3页

• 表约束

分类：
	1. 主键约束：primary key   保证以该列字段查询，查出唯一数据
	2. 非空约束：not null      保证该列数据不能为null
	3. 唯一约束：unique        保证该列数据的唯一性
	4. 外键约束：foreign key   设计多表，从表的数据，依赖于主表；
	5. 默认约束：default 1     如果该列数据不存数据，那么有一个默认值；

1. 非空约束：not null，某一列的值不能为null   
	1. 创建表时添加约束
		CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name为非空
		);
	2. 创建表完后，添加非空约束
		ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;
	3. 删除name的非空约束
		ALTER TABLE 表名 MODIFY 列名 类型;

2. 唯一约束：unique，某一列的值不能重复 
	1. 在创建表时，添加唯一约束
		CREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- 手机号
		);
	2. 删除唯一约束
		ALTER TABLE 表名 DROP INDEX 列名;
	3. 在表创建完后，添加唯一约束
		ALTER TABLE 表名 MODIFY 列名 类型 UNIQUE;	
3. 主键约束：primary key  非空且唯一
	1. 在创建表时，添加主键约束
		create table stu(
			id int primary key auto_increment,-- 给id添加主键约束
			name varchar(20)
		);
	2. 删除主键
		ALTER TABLE 表名 DROP PRIMARY KEY;
	3. 创建完表后，添加主键
		ALTER TABLE 表名 MODIFY 列名 INT PRIMARY KEY;
	4. 自动增长：auto_increment
		create table stu(
			id int primary key auto_increment,-- 给id添加主键约束
			name varchar(20)
		);
	5. 删除自动增长
		ALTER TABLE 表名 MODIFY 列名 INT;
	6. 添加自动增长
		ALTER TABLE 表名 MODIFY 列名 INT AUTO_INCREMENT;


4 外键约束：foreign key   让表于表产生关系，从而保证数据的正确性。
	1. 在创建表时，可以添加外键 
			create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);
	2. 删除外键
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
	3. 创建表之后，添加外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
	4. 级联操作
		1. 添加级联操作
			语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
					FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
		2. 分类：
			1. 级联更新：ON UPDATE CASCADE 
			2. 级联删除：ON DELETE CASCADE 
	自动增长不是约束 auto_increment

• 管理用户,授权(了解)

管理用户

1. 添加用户：
	 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2. 删除用户：
	 语法：DROP USER '用户名'@'主机名';
3. 修改用户密码：
	UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
	UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
	
	SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

	 mysql中忘记了root用户的密码？
		1. cmd -- > net stop mysql 停止mysql服务
			 需要管理员运行该cmd

		2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
		3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
		4. use mysql;
		5. update user set password = password('你的新密码') where user = 'root';
		6. 关闭两个窗口
		7. 打开任务管理器，手动结束mysqld.exe 的进程
		8. 启动mysql服务
		9. 使用新密码登录。
4. 查询用户：
	-- 1. 切换到mysql数据库
	USE myql;
	-- 2. 查询user表
	SELECT  FROM USER;
	
	 通配符： % 表示可以在任意主机使用用户登录数据库

权限管理

1. 查询权限：
	-- 查询权限
	SHOW GRANTS FOR '用户名'@'主机名';
	SHOW GRANTS FOR 'lisi'@'%';

2. 授予权限：
	-- 授予权限
	grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
	-- 给张三用户授予所有权限，在任意数据库任意表上
	
	GRANT ALL ON . TO 'zhangsan'@'localhost';
3. 撤销权限：
	-- 撤销权限：
	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
	REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

JDBC(记忆)

• 概述:Java DataBase Connectivity Java 数据库连接， Java语言操作数据库
• 本质:其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。
• 快速使用

1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
	1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
	2.右键-->Add As Library
2. 注册驱动
3. 获取数据库连接对象 Connection
4. 定义sql
5. 获取执行sql语句的对象 Statement
6. 执行sql，接受返回结果
7. 处理结果
8. 释放资源

• 解析对象

1. DriverManager：驱动管理对象
	原理:注册驱动：告诉程序该使用哪一个数据库驱动jar
		 static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。 
	     写代码使用：  Class.forName("com.mysql.jdbc.Driver");
	     通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块
		 static {
				try {
					java.sql.DriverManager.registerDriver(new Driver());
				} catch (SQLException E) {
					throw new RuntimeException("Can't register driver!");
				}
			}
	
		注意：mysql5之后的驱动jar包可以省略注册驱动的步骤
2. Connection：数据库连接对象
 	方法: Statement createStatement()  获取Statement对象
	 	  PreparedStatement prepareStatement(String sql)  获取prepareStatement对象
	 	  开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
	      提交事务：commit() 
	      回滚事务：rollback()
3. Statement：执行sql的对象
 		  1. boolean execute(String sql) ：可以执行任意的sql 了解 
	      2. int executeUpdate(String sql) ：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句
		     返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功，反之，则失败。
	      3. ResultSet executeQuery(String sql)  ：执行DQL（select)语句
4. ResultSet：结果集对象,封装查询结果
		 boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
		 getXxx(参数):获取数据
 					 Xxx：代表数据类型   如： int getInt() ,	String getString()
 				    参数：
	                     1. int：代表列的编号,从1开始   如： getString(1)
	                     2. String：代表列名称。 如： getDouble("balance")

详细对象连接

获取绝对路径

ClassLoader classLoader = JDBCUtils.class.getClassLoader();
		URL res  = classLoader.getResource("jdbc.properties");
		String path = res.getPath();
		System.out.println(path)

• JDBCUtils:为当前类名
• jdbc.properties为获取的文件名
• paht为绝对路径
• ClassLoader为类加载器
• URL 为统一资源适配器

JDBC连接池

• C3PO
• Druid
• 详细使用
• Spring JDBC
• 注意参数可以添加数组,并依次读取值,赋值给占位符