数据库基础 day-03 DDL，触发器，函数，存储过程，索引,JDBC，PreparedStatement预编译

已于 2022-04-01 19:31:45 修改
阅读量867 收藏
点赞数
分类专栏： 数据库基础 文章标签： mysql sql eclipse
于 2022-03-31 16:58:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43734992/article/details/123862670
版权

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包
  • 导入需要的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语句过来，就直接拿编译后的执行计划加上不同的参数直接执行，可以大大提升性能。
铅笔芯片
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
JDBC中Statement与PreparedStatement的区别
wangyiwujava的专栏
10-25 478
1. statement每次执行sql语句，相关数据库都要执行sql语句的编译；preparedstatement是预编译的, 采用Cache机制（预编译语句，放在Cache中，下次执行相同SQL语句时，则可以直接从Cache中取出来，有利于sql生成查询计划。），对于批量处理可以大大提高效率. 也叫JDBC存储过程。 例如，如果要执行两条sql语句 select colume from
DDL创建删除表 PreparedStatement
jibihai1985的博客
04-13 672
package sqExe; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import cn.java.myLib.CommDb; public class ExeDDLP { private static Prepared...
JDBC使用statement写DDL语句
二丑的博客
04-09 925
package Demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class Demo1 {      public static void main(String[] args) throws Exception {      Connection con...
关于weblogic中使用prepared statement cache后操作DDL的问题
color123456的博客
09-13 415
前几天有客户问我这么个问题，他们在weblogic中配置了prepared statement cache, 而他们应用中有操作DDL的地方，比如alter table什么的，这时候如果使用cached prepared sta...
使用statement对象执行DDL语句创建表
weixin_57219176的博客
07-27 569
使用statement对象执行DDL语句创建表
数据库应用技术：第11章-存储过程与触发器.docx
最新发布
12-17
存储过程是数据库管理中一种重要的程序设计组件，它由一系列预先编译好的Transact-SQL语句组成，保存在数据库服务器中，用于执行特定的任务。调用存储过程时，只需要通过EXECUTE命令，而无需再次发送整个过程的代码...
SQLServer2008数据库基础入门教程含思考题练习题 第11章 存储过程 触发器和函数 共62页.ppt
02-01
**存储过程**是数据库中的重要组成部分，它是一个预编译的SQL代码模块，可以高效地执行特定任务，并且是可重用的。存储过程分为三种类型：用户定义存储过程、扩展存储过程和系统存储过程。用户定义的存储过程由用户...
SQL基础-----DDL
02-24
SQL语句主要可以划分为以下3个类别.DDL(DataDefinitionLanguages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。.DML...
SQLServer EVENTDATA()函数来获取DDL 触发器信息
12-15
DDL触发器允许我们监视并响应对数据库结构的更改，如创建、修改或删除表、视图、索引等对象。`EVENTDATA()`函数在此场景下扮演着关键角色，它能够捕获并返回触发触发器的DDL语句的相关信息。 首先，让我们深入理解`...
DDL语句造成Postgres PreparStatement报错
supermancoke的博客
03-02 818
参考文档 https://jdbc.postgresql.org/documentation/head/server-prepare.html https://jdbc.postgresql.org/documentation/head/connect.html#autosave 连接相关设置 prepareThreshold(int):指定在一个会话中需要经过多少次PreparedStat...
数据预处理索引
buaaeezz的专栏
03-22 514
数据预处理常用方法1.filling null data fill with average value. using logistic regression or naive bayes algorithm to predict the null data according to other data nearby. 2.data smoothness 3.clean outli
JDBC基础篇（MYSQL）——PreparedStatement执行DML、DQL等
五彩世界的博客
05-10 407
注意：其中的JdbcUtil是我自定义的连接工具类：代码例子链接：package day03.prepare; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import util.JdbcUtil; ...
statement对象执行DDL语句
cledwyn
07-10 1356
statement对象执行DDL语句 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; /** * 使用Statement对象执行静态的SQL语句 */ publ...
JDBC学习笔记（四）：执行SQL语句（Statement与PreparedStatement）
页面无法显示_(:з」∠)__
03-24 867
执行方法：使用Statement对象。 Statement简介： Statement是Connection对象返回的，提供了基本查询的接口的类，一般用来执行简单的、无参数的查询语句。 执行稍微复杂的sql语句，一般选择PreparedStatement，可以有效防止SQLInjection。 Statement对象的获取： Statement stm = Connection.crea
jdbc增删改查_JDBC入门（二）—— 增删改查
weixin_39603132的博客
12-03 137
一、执行DDL语句 SQL的DDL语句也就是数据定义语言（Data Definition Language），在JDBC中需要获取Statement类对象，然后使用Statement类对象发起 现在尝试通过JDBC在数据库中创建表：// 参数： 这里使用了Statement类的executeUpdate()方法来执行DDL语句，这个方法对于 SQL 数据操作语言 (DML) 语...
jdbc、PreparedStatement预处理对象、连接池详解
m0_48811221的博客
03-01 929
jdbc 概念 操作数据库有三种方式 cmd　操作 工具操作(sqlYog ,navicate ) 通过java的方式操作 数据库 jdbc :java中用来操作数据库的 一种技术 Java DataBase Connectivity (java的数据库连接) JDBC由来 jdbc的含义 :接口概念 1.jdbc的接口中api是什么 2.导入驱动jar包 jdbc快速入门 1.数据库 CREATE DATABASE day06; USE day06; CREATE TABLE `user
JDBC编程之预编译SQL与防注入式攻击以及PreparedStatement的使用教程
a631278993的博客
09-16 314
转载请注明原文地址： http://www.cnblogs.com/ygj0930/p/5876951.html 在JDBC编程中，常用Statement、PreparedStatement和CallableStatement三种方式来执行查询语句，其中Statement用于通用查询，PreparedStatement用于执行参数化查询，而Callabl...
jdbc中Statement与Preparestament的区别（从jdbc与数据库的方向来理解）
woshishuiaaaD的博客
08-15 5387
1.先了解下sql语句在数据库中的执行过程： 一、 基本阶段         当用户执行SQL语句（这里主要值数据操纵语言DML）时，通过连接，先将该语句发送到oracle服务器，再由服务器进程处理该语句。服务器进程处理SQL语句的基本阶段是：解析、执行、返回结果。     1、解析（perse）         解析指检查ＳＱＬ语句的语法和语义，生成SQL语句的执行计
Java学习日志Day31_ResultSet_jdbc查询员工表_Statement执行DDL,DML语句的弊端_PreparedStatement_数据库连接池(初窥)
igfff的博客
06-10 303
一、ResultSet :结果集的数据表 举例1： import com.qf.utils.JdbcUtils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JdbcTest { public static void main(String[] args) { Con
SQLServer2008数据库编程：存储过程、触发器与函数解析
"SQLServer2008数据库基础入门教程，涵盖了存储过程、触发器和函数的详细讲解，包括它们的特点、创建与使用方法。本教程旨在提高Transact-SQL语句的执行效率和增强数据完整性。" 在SQL Server 2008中，存储过程是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值