JDBC连接数据库&SQL基本操作

Java访问数据库的基本步骤

1. 装载数据库的JDBC驱动程序

    Class.forName(" com.mysql.jdbc.Driver").newInstance();

2. 建立与数据库的连接

   String url = "jdbc:mysql://主机:端口号/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false";  //例如：String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&useSSL=false";String user = "root";String password = "211";Connection conn= DriverManager.getConnection( url, user, password );

3. 建立Statement 对象，准备执行SQL语句

   String sql = "";  //“”内为所要执行的sql语句PreparedStatement pstmt = conn.prepareStatement(sql);

4. 执行SQL语句
   常用3种执行SQL语句的方法：

   • executeQuery()：用于Select语句,返回结果集(ResultSet)

         String sql = “select * from 表名”;    ResultSet rs = pstmt.executeQuery(sql);

   • executeUpdate()：用于创建和更新表(如Update/Insert/ Delete/Create)，返回受影响行数(int)

     String sql = “Delete from 表名 where … ”;int rsCount = pstmt.executeUpdate(sql); 

   • execute()：用于执行任何SQL语句，返回布尔值(执行是否成功)

5. 处理ResultSet结果集

   while( rs.next() ) {      String s1=rs.getString("name");  //根据列名取值    String s2=rs.getString(1);  //根据列号取值(从1开始)    int col3=rs.getInt(3);  //非字符串数据示例}

6. 依次关闭ResultSet、Statement和Connection对象

   rs.close();    stmt.close();    conn.close();

   示例：

   Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;try {    Class.forName("com.mysql.jdbc.Driver").newInstance();    String url = "jdbc:mysql://localhost:3306/users?useUnicode=true&characterEncoding=utf-8&useSSL=false";    String user = "root";    String password = "211";    conn = DriverManager.getConnection(url, user, password);    String sql = "select * from users";    pstmt = conn.prepareStatement(sql);    rs = pstmt.executeQuery();    while (rs.next()) {        System.out.println(rs.getInt(1));        System.out.println(rs.getString(2));        System.out.println(rs.getString(3));    }} catch (Exception e) {    e.printStackTrace();} finally {    try {        if (rs != null) {            rs.close();            rs = null;        }        if (pstmt != null) {            pstmt.close();            pstmt = null;        }        if (conn != null) {            conn.close();            conn = null;        }    } catch (Exception e) {        System.out.println("数据库关闭错误");        e.printStackTrace();    }}

SQL基本操作

• 显示数据库： show databases;
• 使用数据库： use 库名; （如： use mysql; ）
• 显示数据库表： show tables;
• 显示表结构： describe 表名;
• 删除表： drop table 表名;
• 删除数据库： drop database 库名;

• 在数据库中创建表： create table 表名( 字段定义);
  例如：

  use testdb; create table users(    id int auto_increment not null,    username varchar(20) not null,    password varchar(20),    primary key(id));

• 添加：

  insert into users values(1, 'wustzz','123456');insert into users values(null, 'zz','123456');

• 查询：

  select * from users where id=1;select distinct username from users where username like '%zz';select username,password from users where id in (1,3,5);select count(*) as total from users;select max(id) from users;

• 删除记录：

  delete from users where id>100;delete from users;

• 更新记录：

  update users set password='888888' where id between 1 and 10 ;update users set username="wustzz",password="888888";注意：一般不允许修改主键

MySQL中文处理

先运行：

Set  Names utf-8;create table teacher(  id int(3) not null primary key,name char(10),address varchar(50),year date)default character set utf-8;