索引
Msql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引 （unique key）
- 避免重复的列出现，可以重复，多个列都可以标示为唯一索引
- 常规索引（key/index）
- 默认的 index 或者key关键字来设置
- 全文索引（FullText）
- 在特定的数据库引擎下才有，myisam
- 快速定位数据
基础语法
-- 索引的使用--
- 在创建表的时候给字段增加索引--
- 创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student;
-- 新增一个索引 (索引名) 列名
ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);
ALTER TABLE `student` ADD FULLTEXT INDEX `FI_PHONE` (`phone`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138');-- 全文索引
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上
数据库用户管理
sql 命令操作
用户表：mysql.user
本质:读这张表进行增删改查
-- 创建用户
CREATE USER zyy IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zyy = PASSWORD('123456');
-- 重命名 RENAME 原名子 zyy TO 新名字;
RENAME USER zyy TO newzyy;
-- 用户授权 ALL PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权不行，其他都能干
GRANT ALL PRIVILEGES ON *.* TO newzyy;
-- 查询权限
SHOW GRANTS FOR newzyy;-- 查看指定用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root用户的权限
-- 撤销权限
REVOKE哪些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM newzyy;
-- 删除用户
DROP USER newzyy;
数据库三大范式
为什么需要数据规范化？
信息重复，更新异常，插入异常（无法正常显示信息），删除异常（丢失有效的信息）
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。 每张表只描述一件事情。
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据库降低为小数据量的查询：索引）
第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USER jdbcstudy;
CREATE TABLE users(
`id` INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`email` VARCHAR(60),
`birthday` DATE
);
INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'张三','123456','zs@sina.com','1980-12-04');
(3,'李四','123456','lisi@sina.com','1981-12-04');
(3,'王五','123456','wangwu@sina.com','1982-12-04');
- 创建一个普通项目
- 导入数据库驱动（jar包）
- 编写测试代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和URL
// useSSL=true可能会报错
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String userName = "root";
String passWord = "123456";
//3.连接成功，数据库对象 Connection代表数据库
Connection connection = DriverManager.getConnection(url, userName, passWord); //4.执行SQl的对象 Statement 执行的sql对象
Statement statement = connection.createStatement();
//5.执行SQL的对象 去 执行SQL ，可能存在结果，查看返回的结果
String sql = "SELECT * FROM users";
//返回的结果集 结果集中封装了我们全部的查询的结果
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("password="+resultSet.getObject("password")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); System.out.println("===============================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获取执行SQL的对象 Statement
- 获得返回的结果集
- 释放连接
JDBC中对象解释
DriverManager
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, userName,passWord);
// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
// mysql默认端口3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid
Stateent 执行sql对象 、 PreparedStatement 执行sql对象
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery();//执行查询 返回
ResultSetstatement.executeUpdate();//新增、删除、修改，都用这个，返回受影响的行数
statement.execute();//执行任何SQL
ResultSet 查询的结果集，封装了所有的查询结果
获得指定的数据类型
//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型，就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();//...
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();//消耗资源
statement对象详解
JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的SQL语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发送了变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) { System.out.println("插入成功~");}
CRUD操作-delete
Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) { System.out.println("删除成功~");}
CRUD操作-update
Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) { System.out.println("修改成功~");}
CRUD操作-read
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
//根据获取列的数据类型，分别调用resultSet的相应方法映射到java对象中}
PreparedStatement对象
PreparedStatement可以防止SQL注入，效率更好
1.新增
Connection con = null;
PreparedStatement st = null;
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
//预编译SQL，先写SQL，然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 5);
st.setString(2, "钱七");
st.setString(3, "123456");
st.setString(4, "qianqi@sina.com");
st.setDate(5, new java.sql.Date(new java.util.Date().getTime())); int num = st.executeUpdate();
if (num > 0) { System.out.println("插入成功！"); }
2.删除
Connection con = null;
PreparedStatement st = null;
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "DELETE FROM users WHERE `id`=?";
//预编译SQL，先写SQL，然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 5);
int num = st.executeUpdate();
if (num > 0) { System.out.println("删除成功！"); }
3.更新
Connection con = null;
PreparedStatement st = null;
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "UPDATE users SET birthday=? WHERE id=?";
//预编译SQL，先写SQL，然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setDate(1, new java.sql.Date(new java.util.Date().getTime())); st.setInt(2, 1);
int num = st.executeUpdate();
if (num > 0) { System.out.println("修改成功！"); }
4.查询
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
con = JDBCUtils.getConnection();
//使用?占位符代替参数
String sql = "SELECT * FROM users WHERE id=?";
//预编译SQL，先写SQL，然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 1);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id="+rs.getInt("id")); System.out.println("name="+rs.getString("name"));
}
防止sql注入
public class SQLQuestion {
public static void main(String[] args) {
//正常登录
// login("张三","123456");
//sql注入
login("' or '1=1", "123456"); }
/** * 登录业务 */
public static void login(String userName, String password) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
// PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
// 假设其中存在转义字符，比如说'会被直接转义
String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
st = con.prepareStatement(sql);
st.setString(1, userName);
st.setString(2, password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println("id=" + rs.getInt("id")); System.out.println("name=" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally { JDBCUtils.release(con, st, rs); }
}
}
1648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
