Java mysql delete (java mysql 删除数据)
语法
delete from 表名
delete from 表名 where {条件}
注:
- delete from 表名 表示删除所有表中数据(我们一般叫'删库跑路', 对于生产环境这么做,如果不是领导允许的,是违法的哦)
- delete from 表名 where {条件} 表示根据条件删除
例子
package com.pangugle.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestJDBC {
public static Connection getConnect() throws ClassNotFoundException, SQLException
{
// 通过的反射的方式,导入数据库连接驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://192.168.3.201:3306/testpgdb??useUnicode=true&characterEncoding=utf8&jdbcCompliantTruncation=false&useSSL=false&tinyInt1isBit=false";
Connection conn = DriverManager.getConnection(url, "root", "root");
System.out.println("connect mysql is success ? " + !conn.isClosed());
return conn;
}
public static void testInsert(String username, String password) throws ClassNotFoundException, SQLException
{
Connection conn = getConnect();
PreparedStatement ps = null;
try {
String sql = "insert into pangugle_user(user_name, user_password) values(?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, password);
int rs = ps.executeUpdate();
System.out.println("insert 结果为 = " + rs);
} catch (Exception e) {
e.printStackTrace();
} finally
{
// 一定要关闭资源(当然我们还有一种技术,叫做连接池技术,就不在需要我们来关闭了)
ps.close();
conn.close();
}
}
public static void testDelete(String username) throws ClassNotFoundException, SQLException
{
Connection conn = getConnect();
PreparedStatement ps = null;
try {
String sql = "delete from pangugle_user where user_name = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, username);
int rs = ps.executeUpdate();
System.out.println("delete 结果为 = " + rs);
} catch (Exception e) {
e.printStackTrace();
} finally
{
// 一定要关闭资源(当然我们还有一种技术,叫做连接池技术,就不在需要我们来关闭了)
ps.close();
conn.close();
}
}
public static void testDeleteAll() throws ClassNotFoundException, SQLException
{
Connection conn = getConnect();
PreparedStatement ps = null;
try {
String sql = "delete from pangugle_user";
ps = conn.prepareStatement(sql);
int rs = ps.executeUpdate();
System.out.println("delete 结果为 = " + rs);
} catch (Exception e) {
e.printStackTrace();
} finally
{
// 一定要关闭资源(当然我们还有一种技术,叫做连接池技术,就不在需要我们来关闭了)
ps.close();
conn.close();
}
}
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
// getConnect();
//testInsert("u1", "p1");
//testInsert("u2", "p2");
//testInsert("u3", "p3");
testDelete("u1");
// testDeleteAll();
}
}
运算结果为
connect mysql is success ? true
delete 结果为 = 1
然后我们再查看数据库记录,是不是 u1 这条记录的没有了, 说明被删除成功了;
我们还可以通过主键这个字段来删除,因为它也是唯一的
delete from pangugle_user where user_id = {主键id};
实际工作中,如果有参数一定要用 '?' 来代替参数,这样可以防止被sql注入, 也就是代码漏洞!
还有一个删除所有的,你们运行 testDeleteAll(), 记录把注释打开;