索引介绍及索引分类
1.主键索引(primary key)
-- 唯一的标识，主键不可重复，只能有一个列作为主键
2.唯一索引(unique key)
-- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
3.常规索引(key/index)
4.全文索引(fulltext)
创造1000000条数据测试索引
create database app_user character set utf8 collate utf8_general_ci
use app_user
create table `app_user`(
`name` varchar(20),
`idcard` int(19)
)engine=innodb default charset=utf8
set global log_bin_trust_function_creators=1
delimiter $$ -- 写函数之前必须写
create function mock()
returns int
begin
declare num int default 10000;
declare i int default 0;
while i<num do
insert into `app_user`(`idcard`,`name`) values (rand()*10000000+100000000,concat('用户',i));
set i=i+1;
end while;
return i;
end;
select mock()
select * from app_user where `name`='用户9999'
explain select * from app_user where `name`='用户9999'
create index id_app_user_name on app_user(`name`)在写函数时需要以delimiter $$开始，以end;结尾
在未添加索引时查询用户9999需要1s时间，在添加索引后无论查询哪个用户都只需0.001s，由此可见索引能够大大加快我们查询的速度，但是仅限数据量较大的场合，数据量少就基本没有差异
其中还有explain(分析),加在语句前方可以分析语句执行状况
索引原则
一、索引不是越多越好
二、不要对经常变动数据加索引
三、小数据量的表不需要加索引
四、索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree ：innodb的默认数据结构
数据库用户管理
-- 创建用户 create user 用户名 indentified by '密码'
create user xiaowu Iidentified by '123456'
-- 修改密码（修改当前用户密码）
set password = password('密码')
-- 修改密码（修改指定用户密码）
set password for xiaowu = password('新密码')
-- 重命名 rename user 原名 to 新名
rename user xiaowu to xiaowu2
-- 用户授权 all privileges 全部权限 on 库.表 to 哪个用户
-- all privileges 除了给别人授权什么都可以做
grant all privileges on *.* to xiaowu2
-- 查询权限
show grants for xiaowu2 -- 查看指定用户的权限
show grants for root@localhost -- root用户的权限
-- 撤销权限 revoke 哪些权限 在哪个库取消 给谁取消
revoke all privileges on *.* from xiaowu2
-- 删除用户
drop user xiaowu2MySQL备份
为何要备份？
1、保证重要数据不丢失
2、数据转移
MySQL数据库转移的方式
1、直接拷贝物理文件
2、在各个可视化工具中在想要导出的表中或库中右键即可看到导出选项
3、在命令行用mysqldump
如何设计一个项目的数据库
三大范式
为什么需要数据规范化？
1、信息重复
2、更新异常
3、插入异常
-- 无法正常显示信息
4、删除异常
-- 丢失有效信息
三大范式
第一范式（1NF）：要求数据库的每一列都是不可分割的原子数据项
即列中信息不能再次分割，如家庭信息写（3口人，江西），人口和户籍可以分开，就不满足第一范式
第二范式（2NF）：确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
每张表只描述一件事情
前提：满足第一范式
例如
在这张表中订单号和产品号为联合主键，产品数量、产品折扣和产品价格有关
而订单金额和订单时间只与订单号有关
所以不满足第二范式，需要分为两张表
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF的基础上消除传递依赖）
第三范式需要确保数据表中每一列数据都和主键直接相关，而不能间接相关
前提：满足第一范式与第二范式
如上表，班主任姓名确实与主键有关，但是班主任性别与年龄与主键只是间接相关，故不满足第三范式需要分为两张表
规范性和性能的问题
关联查询的表不得超过三张表
1、考虑商业化的需求和目标,（成本，用户体验）数据库的性能更加重要
2、在提升数据库性能的时候要适当考虑一下规范性
3、故意给表增加一些冗余字段（从多表查询变为单表查询）
4、故意增加一些计算列（从大数据量减为小数据量的查询：索引）
JDBC
数据库驱动和JDBC
编程软件不能直接操作数据库，于是在两者之间添加一层让两者联系起来，这就是JDBC
java连接数据库
import java.sql.*;
public class lesson01 {
public static void main(String[] args) throws Exception{
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法，加载驱动
//2用户信息和url
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "数据库密码";
//3.连接成功，接收返回对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象Statement 执行sql的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象 去 执行SQL，可能存在结果，看返回结果
String sql = "SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果集中封装了我们全部查询出来的结果
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("class=" + resultSet.getObject("class"));
System.out.println("==============================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
第一步是固定写法要加载驱动
-- 在新版中语句为Class.forName("com.mysql.cj.jdbc.Driver");
第二步就需要我们填写sql用户信息
-- 一般要新建3个变量分别输入url、用户名、sql密码
第三步是连接数据库
-- 使用Connection类来接收数据库
第四步创建执行sql语句类
-- 使用Statement类执行sql语句
第五步接收sql语句结果
-- 使用Result类接收结果
第六步关闭资源
JDBC中对象解释
URL
DriverManager
Class.forName("com.mysql.jdbc.Driver"); 固定写法，加载驱动
connection代表数据库，其中有几个函数
-- rollback 事务回滚
-- commit 事务提交
-- setAutoCommit 数据库设置自动提交
Statement
Statement 执行sql的对象 PrepareStatement执行sql的对象
statement.executeQuery() 执行sql查询操作，返回一个结果集
statement.execute() 执行任何sql语句
statement.excuteUpdate() 更新、插入、删除都是使用该方法，该方法会返回一个受影响的行数
ResultSet
结果集，用于保存结果
resultset.getObject(); --在不知道类型的情况下使用
--以下是知道已知类型情况下使用--
resultset.getString()
resultset.getInt()
resultset.getDate()............
指针遍历
释放
resultset.close()
statement.close()
connection.close()
Statement对象详解
工具类：
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
username = properties.getProperty("username");
password = properties.getProperty("password");
url = properties.getProperty("url");
//1.驱动只要加载一次
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, username, password);
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet res){
if (res!=null){
try {
res.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static void main(String[] args) {
System.out.println(url);
}
}配置信息文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true
username =root使用statement进行插入Insert
import Jdbcsty.$03Statement对象详解.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet res = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获取sql的执行对象
String sql = "INSERT INTO student(id,`name`,`class`) VALUES ('7','郑积健','高三12班')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,res);
}
}
}配置类文件引用
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
再使用Properties内置方法getProperties("属性名")来获取内容
SQL注入问题
主要因为sql存在拼接情况当输入参数为'or'1=1时，会被拼接在sql语句中，使判断正确
import Jdbcsty.$03Statement对象详解.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class lesson04 {
public static void main(String[] args){
login(" 'or '1=1","'or'1=1");
}
public static void login(String name,String password){
Connection conn = null;
Statement st = null;
ResultSet res = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获取sql的执行对象
String sql = "select * from student where name='"+name+"'and password = '"+password+"'";
res = st.executeQuery(sql);
while (res.next()){
System.out.println(res.getObject("name"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,res);
}
}
}
虽然我在主方法中没有输入任何信息，但是确能够获取到数据库所有信息
PreparedStatement对象
为解决SQL注入问题，推出了preparedStatement来执行sql
PreparedStatement防止sql注入的本质是把传递进来的参数当做字符，即在外面包裹了一层''
如果其中存在转义字符，如 ' 会被直接转义失去效果
Preparedstatement执行：
1、Preparedstatememt是先写好sql语句(预编译)，一些变量使用 ? 占位符来代替
2、在预编译结束后使用setInt(index,String....)来给每一个占位符赋值
3、随后二者步骤一致
package Jdbcsty.$5PreparedStatement对象;
import Jdbcsty.$03Statement对象详解.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class lesson05 {
public static void main(String[] args){
login(" 'or '1=1","'or'1=1");
}
public static void login(String name,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet res = null;
try {
String sql = "select * from student where name= ? and password = ? ";
conn = JdbcUtils.getConnection();
st = conn.prepareStatement(sql);
st.setString(1,name);
st.setString(2,password);
res = st.executeQuery();
while (res.next()){
System.out.println(res.getObject("name"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,res);
}
}
}
使用IDEA连接数据库过程
1.点击右上角数据库
2.进入以后点击＋号添加数据库
3.选择数据源
4.填写信息
5.选择需要导入的数据库
写sql代码
JDBC操作事务
import java.sql.*;
public class lesson06 {
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = null;
PreparedStatement st1=null,st2=null;
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "wyh2251291";
try {
conn = DriverManager.getConnection(url,username,password);
conn.setAutoCommit(false); //关闭自动提交，开启事务
String sql1 = "update bank.account set money = money - 100 where `name` = 'A'";
st1 = conn.prepareStatement(sql1);
st1.execute();
//int i = 1/0;
String sql2 = "update bank.account set money = money + 100 where `name` = 'B'";
st2 = conn.prepareStatement(sql2);
st2.execute();
conn.commit();
System.out.println("成功");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (st1!=null) {
try {
st1.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st2!=null) {
try {
st2.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
}
开启事务之前需要关闭数据库的自动提交
connection.setAutoCommit(false)
在try/catch语句中，有隐式的回滚语句，也可以直接写出来
DBCP-C3P0连接池
即将数据库开放连接，连接者都可操作数据库数据，类似于线程池
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
