mysql jdbc 示例_详解MySQL的简单使用和JDBC示例代码分享

MySql简单操作//启动mysql

net start mysql

//登陆

mysql -u root -p

//创建建数据库

create database mydb;

create database test;

//删除数据库

drop database test;

//使用数据库

use mydb;

//创建表

create table mytable(name varchar(20)，sex char);

//显示数据库中所有表

show tables;

//增加一列

alter table mytable add age int;

//插入

insert mytable value("liu",'f',22);

insert mytable value("wang",'m',22);

insert mytable(name,sex) value("li",'f');

insert mytable value("zhao",'m',22);

//删除表中项

delete from mytable where name=”liu“;

delete from mytable where name=”li“;

//更新

update mytable set age = 23 where name = "wang";

//查询

select * from mytable;

JDBC

JDBC驱动程序分为4类

JDBC-ODBC桥 部分本地API，部分Java驱动程序 JDBC网络纯Java驱动程序 本地协议Java驱动程序

JDBC的示例import java.sql.*;

import com.mysql.jdbc.Driver;

public class JDBCMySql {

public void operateMySql() throws SQLException, ClassNotFoundException{

String driverName ="com.mysql.jdbc.Driver";

String URL = "jdbc:mysql://127.0.0.1:3306/mydb";

String sql = "SELECT * FROM mydb.mytable";

String username = "root";

String password = "";

Connection conn = null;

try{

//加载驱动

Driver.class.forName(driverName);

//建立连接

conn = DriverManager.getConnection(URL,username,password);

//创建statement来执行sql语句

Statement ps = conn.createStatement();

//结果集处理

ResultSet rs = ps.executeQuery(sql);

while(rs.next()) {

System.out.println(rs.getString("name")+","+rs.getString("sex")+","+rs.getInt("age"));

}

}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch(SQLException e){

e.printStackTrace();

}

//关闭连接

finally{

if(conn!=null){

try{

conn.close();

}catch(SQLException e){

e.printStackTrace();

}

}

}

}

}

建立一个test case来验证一下

import static org.junit.Assert.*;

import java.sql.SQLException;

import org.junit.Before;

import org.junit.Test;

public class JDBCTest {

@Test

public void test() throws ClassNotFoundException, SQLException {

JDBCMySql jm = new JDBCMySql();

jm.operateMySql();

}

}

执行结果