DDL:数据定义语言
- 非重点,了解即可。以后对数据库的操作使用图形界面
- 对数据库,表,视图等各种对象进行创建,删除,修改等操作
-- 创建数据库:employee
CREATE DATABASE employee
-- 移除一个数据库
DROP DATABASE employee
-- 创建一个表:t_employee
CREATE TABLE t_employee(
-- 自增长 nusighed?
id INT auto_increment ,
-- 不为空
`name` VARCHAR(20) NOT NULL,
birthday DATE,
-- 主键
PRIMARY KEY(id)
);
-- 修改表-添加字段
ALTER TABLE t_employee ADD email VARCHAR(30);
-- 修改表字段
ALTER TABLE t_employee CHANGE email contact TEXT;
-- 删除表
DROP TABLE t_employee;
-- 创建一个视图:v_employee
CREATE VIEW v_employee AS SELECT * FROM t_employee
触发器
- 非重点,了解即可。移植困难,不通用
- 指在对表进行插入,修改,删除数据的时候,触发事件,对操作进行校验或调整。
- 触发时机:before和after,在执行插入,更新,删除操作之前或者之后触发。
- 如果需要在一个字段上设置多个触发器,可以继续添加触发器。
- 在定义中写流程控制语块,用来定义触发时的具体动作。
- 示例:为学生性别添加触发器,默认为男
- 当添加字段不是男或者女时,会触发异常。
函数
- 非重点,了解即可。移植困难,不通用
- 常用的函数可以参考MySQL 函数
- 可以自定义函数:
-- 自定义函数
CREATE FUNCTION my_sum(m INT,n INT) RETURNS INT
BEGIN
-- 定义一个变量,值为0
DECLARE s INT DEFAULT 0;
DECLARE i INT DEFAULT m;
WHILE i <= n DO
SET s = s + i;
SET i = i + 1;
END WHILE;
RETURN s;
END
-- 调用自定义函数
SELECT my_sum(100,200) from t_student
-- 定义函数,计算同姓平均身高
CREATE FUNCTION f_get_avg_score_of_same_lastname(lastname VARCHAR(20)) RETURNS FLOAT
BEGIN
DECLARE avg FLOAT;
SELECT avg(height) INTO avg FROM t_student t1 where t1.last_name = lastname;
RETURN avg;
END
-- 调用函数
SELECT id,`name`,sex, last_name, height,f_get_avg_score_of_same_lastname(last_name)
FROM t_student WHERE id >400 AND id < 410
存储过程
- 对java开发来说,非重点,了解即可。移植困难,不通用
- 批量对数据库进行增删改查操作。
- 参数类型
- in:传入参数
索引
以下内容参考自裸奔的小鸵鸟
- 什么是索引?为什么要建立索引?
- 索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
- 例如:有一张person表,其中有2W条记录,记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码,现在想要查询出电话号码为xxxx的人的信息。
- 如果没有索引,那么将从表中第一条记录一条条往下遍历,直到找到该条信息为止。
- 如果有了索引,那么会将该Phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种:BTREE、HASH。 也就是用树或者Hash值来存储该字段,要知道其中详细是如何查找的,就需要会算法的知识了。我们现在只需要知道索引的作用,功能是什么就行。
- MySQL中索引的优点和缺点和使用原则
- 优点:
- 所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段设置索引
- 大大加快数据的查询速度
- 缺点:
- 创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加
- 索引也需要占空间,我们知道数据表中的数据也会有最大上线设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值
- 当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
- 使用原则
- 对经常更新的表就避免对其进行过多的索引,对经常用于查询的字段应该创建索引
- 数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
- 在一同值少的列上(字段上)不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可以建立索引。
*索引的分类
- 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。(使用最多)
- 其他略
JDBC(重点!):java数据库连接技术
- 下载需要的jar包
- mvnrepository网站 :https://mvnrepository.com/
- 搜索MySQL Connect
- 点击此处下载即可
- 导入需要的jar,同导入dom4j方法相同
- 在project上右键,新建folder,命名为lib,将jar包复制到lib中,右键build path,设置其路径即可使用。
- eclipse连接数据库
示例- mysql中表为:
package project1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Test1 {
public static void main(String[] args) {
Connection conn = null;
// 写一段jdbc连接数据库的程序
try {
// 1、测试数据库驱动是否存在,驱动名:com.mysql.cj.jdbc.Driver
// 描述类的类
Class.forName("com.mysql.cj.jdbc.Driver");
// 2、获取数据库连接,参数1,数据库连接协议字符串,参数2,用户名,参数3,用户名密码
// localhost标识本机域名,127.0.0.1标识本机的IP地址,关闭安全连接useSSL = false,自动重连autoReconnect =
// true,时间设置serverTimeZone = Asia/Shanghai
// String url =
// "jdbc:mysql://localhost:3306/test1?charaterEncoding:UTF-8&&useSSL =
// false&&autoReconnect = true&serverTimeZone = Asia/Shanghai";
String url = "jdbc:mysql://localhost:3306/test1";
String username = "root";
String password = "123456";
conn = DriverManager.getConnection(url, username, password);
// 3、创建sql指令的载体
Statement stmt = conn.createStatement();
// 4、向数据库服务器发送sql指令
String sql = "select id,name,sex,birthday,email from t_student limit 20";
// 5、执行查询操作,返回查询后的结果集
ResultSet rs = stmt.executeQuery(sql);
// 6、对结果集进行操作,读写操作
while (rs.next()) {
// 取出数据
// 使用interger不用int的原因是:数据库中该值可能为空,用int无法表示空的情况。
Integer idInteger = rs.getInt("id");// 使用名字作为索引
String nameString = rs.getString("name");
Date birthdayDate = rs.getDate("birthday");
String emailString = rs.getString("email");
SimpleDateFormat sdfDateFormat = new SimpleDateFormat("yyyy-MM-dd");
System.out.println(idInteger + " : " + emailString + " : " + nameString + " : "
+ sdfDateFormat.format(birthdayDate));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (conn != null) {
try {
// 数据库连接关闭
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
- 显示结果为:
增加
// 增加,向test2的student表中,添加姓名王五,学号3
String urlString = "insert into student (name,id) values ('王五',3)";
/*
* 允许查询语句,更新语句,DDL语句,返回为true时,标识执行的是查询语句,可以通过getResult()方法获取结果,
* 返回值为false时,执行的是更新语句。插入语句或者DDl语句 DDL:数据定义语言
*/
boolean success = statement.execute(urlString);
System.out.println(success);//输出false
删除
// 删除test1的t_student表中id在100~105之间的学生信息,注意语句中不包含*
String urlString = "delete from t_student where id>100 and id<105";
int row = statement.executeUpdate(urlString);
System.out.println(row);// 删除的行数,4
修改
// 更新test1的t_student表中学号在105~120的身高信息,重置为180
String urlString = "update t_student set height = 180 where id >105 and id <120";
int rows = statement.executeUpdate(urlString);
System.out.println(rows);// 返回更新的行数
以下内容参考自PreparedStatement用法详解
private static void preparedStatement() {
// 总结一下JDBC的最基本的使用过程
// 1、加载驱动类:Class.forName()
// 2、获取数据库连接:DriverManager.getConnection()
// 3、创建SQL语句执行句柄:Connection.createStatement()
// 4、执行SQL语句:Statement.executeUpdate()
// 5、释放数据库连接资源:finally,Connection.close()
// 定义数据库连接对象
// 引用JDBC相关的所有接口或者是抽象类的时候,必须是引用java.sql包下的
// java.sql包下的,才代表了java提供的JDBC接口,只是一套规范
// 至于具体的实现,则由数据库驱动来提供,切记不要引用诸如com.mysql.jdbc包的类
Connection conn = null;
// 定义SQL语句执行句柄:PrepareStatement对象
// PreparedStatement对象,其实就是底层会基于Connection数据库连接
// 可以让我们方便的针对数据库中的表,执行增删改查的SQL语句
// 比如和insert update delete和select语句
PreparedStatement ps = null;
try {
// 第一步,加载数据库的驱动,我们都是面向java.sql包下的接口在编程,所以
// 要想让JDBC代码能够真正操作数据库,那么就必须第一步先加载进来你要操作的数据库的驱动类
// 使用Class.forName()方式来加载数据库的驱动类
// Class.forName()是Java提供的一种基于反射的方式,直接根据类的全限定名(包+类)
// 从类所在的磁盘文件(.class文件)中加载类对应的内容,并创建对应的Class对象
Class.forName("com.mysql.jdbc.Driver");
// 获取数据库的连接
// 使用DriverManager.getConnection()方法获取针对数据库的连接
// 需要给方法传入三个参数,包括url、user、password
// 其中url就是有特定格式的数据库连接串,包括“主协议:子协议://主机名:端口号//数据库”
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/spark_project?characterEncoding=utf8",
"root", "root");
// 基于数据库连接Connection对象,创建SQL语句执行句柄,Statement对象
// prepareStatement对象,就是用来基于底层的Connection代表的数据库连接
// 允许我们通过java程序,通过prepareStatement对象,向MySQL数据库发送SQL语句
// 从而实现通过发送的SQL语句来执行增删改查等逻辑
// 第一个,SQL语句中,值所在的地方,都用问好代表
String sql = "insert into user(name,age) values(?,?)";
ps = conn.prepareStatement(sql);
// 第二个,必须调用PreparedStatement的setX()系列方法,对指定的占位符设置实际的值
ps.setString(1, "李四");
ps.setInt(2, 26);
// Statement.executeUpdate()方法,就可以用来执行insert、update、delete语句
// 返回类型是个int值,也就是SQL语句影响的行数
// 第三个,执行SQL语句时,直接使用executeUpdate()即可,不用传入任何参数
int rtn = ps.executeUpdate();
System.out.println("SQL语句影响了【" + rtn + "】行。");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 最后一定要记得在finally代码块中,尽快在执行完SQL语句之后,就释放数据库连接
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
PreparedStatement预编译
- PrepareStatement 测试插入数据库
- 如果使用Statement,那么就必须在SQL语句中,实际地去嵌入值,比如之前的insert语句
- 但是这种方式有一个弊端,第一,是容易发生SQL注入,SQL注入,简单来说,就是你的网页的用户在使用,比如论坛的留言板,电商网站的评论页面,提交内容的时候,可以使用’1 or 1’,诸如此类的
非法的字符,然后你的后台,如果在插入评论数据到表中的时候,如果使用Statement,就会原封不动的将用户填写的内容拼接在SQL中,此时可能会发生对数据库的意外的损坏,甚至数据泄露,这种情况就叫做SQL注入- 第二种弊端,就是性能的低下,比如insert into test_user(name,age) values(‘张三’,25) insert into test_user(name,age) values(‘李四’,26) 其实两条SQL语句的结构大同小异,但是如果使用这种方式,在MySQL中执行SQL语句的时候,却需要对每一条SQL语句都实现编译,编译的耗时在整个SQL语句的执行耗时中占据了大部分的比例。所以,Statement会导致执行大量类似SQL语句的时候的,性能低下,
- 如果使用PreparedStatement,那么就可以解决上述的两个问题
- SQL注入,使用PreparedStatement时,是可以在SQL语句中,对值所在的位置使用?这种占位符的。
- 使用占位符之后,实际的值,可以通过另外一份放在数组中的参数来代表。此时PreparedStatement会对值做特殊的处理,往往特殊处理后,就会导致不法分子的恶意注入的SQL代码失效
- 提升性能,使用PreparedStatement之后,其实结构类似的SQL语句,都变成一样的了,因为值的地方都会变成?,那么一条SQL语句,在MySQL中只会编译一次,后面的SQL语句过来,就直接拿编译后的执行计划加上不同的参数直接执行,可以大大提升性能。