一、MySQL 部分:
需要下载一个MySQL服务器端,地址:http://dev.mysql.com/downloads/mysql/ 下载ZIP免安装的那个版本。
下载完毕后解压,目录可以随意。例如:G:\Job\mySql\tools\mysql
以下是学习MySQL做的笔记:
1.启动MySql服务端
cmd -> G:\Job\mySql\tools\mysql\bin ->输入mysqld --console
2.关闭MySql服务端:ctrl + c
3.启动客户端:cmd -> G:\Job\mySql\tools\mysql\bin -> mysql -u root
4.查看信息 指令status
5.SELECT host,user,password FROM mysql.user;查询用户
6.SET PASSWORD for'root'@'localhost'=password('password');修改密码
7.DROP USER ''@'localhost';//删除匿名用户
8.CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'xxxx';创建用户密码为xxxx
9.GRANT ALL ON *.* TO 'myuser'@'localhost';授予"myuser"用户权限 (GARNT SELECT,INSETR,UPDATE ON studentdb.*)
10.";"以为着命令结束,\c取消当前SQL命令 status显示数据库状态,如:端口号,\G 纵向显示记录
11.查询所有数据库:SHOW DATABASES;
12.CREATE TABLE class101(id INT, name varchar(50), gpa FLOAT);创建表
13.查看studentdb中所有表 SHOW TABLES;
14.DESC class101;查看表的定义/结构
15.编写脚本:source G:\Job\mySql\Courses\MySQL\tools\mycommands.sql;
16.创建数据库:CREATE DATABASE IF NOT EXISTS databaseName;
17.创建表:CREATE TABLE IF NOT EXISTS products( id int , category char(3), name varchar(50), price float );
18.插入一条数据: INSERT INTO products VALUES( 1001, 'PEN', 'PEN RED', 1.45 );
19.更新数据:UPDATE products SET price = 2.0 WHERE id = 1001;
20.删除数据:DELETE FROM products WHERE id = 1001;
二、JDBC部分:
这个是JAVA 调用 MySql的一个桥梁。
可以直接点击mysql-connector-java-5.1.28-bin.jar下载。
也可以去MySQL官网下载JDBC驱动。地址:http://dev.mysql.com/downloads/connector/j/
下载时需要注意选择Platform Independent,然后选择.ZIP版本,
下载完毕后你将看到有一个mysql-connector-java-5.1.28-bin.jar,这个会在你项目中用到。
三、JAVA调用JDBC部分:
在运行以下代码时,首先需要开启mysql服务器。可以根据自己实际创建的数据库更改下面的代码。
将mysql-connector-java-5.1.28-bin.jar导入到你的工程。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest {
static Connection conn;
static Statement st;
public static void main( String args[] ){
// insert();
// delete();
update();
query();
}
public static Connection getConnection(){
Connection con = null;
try {
Class.forName( "com.mysql.jdbc.Driver" );// 加载MySql数据驱动
con = DriverManager.getConnection( "jdbc:mysql://localhost:8888/studentdb", "root", "password" );
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void insert(){
conn = getConnection();
try {
String sql = "INSERT INTO class101(id, name, gpa)"+"VALUES(1, 'lijiang', 5.0)";
st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println( "插入失败");
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println( "插入数据失败: "+e.getMessage() );
}
}
public static void delete(){
conn = getConnection();
try {
String sql = "DELETE FROM class101 WHERE name = 'lijiang'";
st = conn.createStatement();
int count = st.executeUpdate(sql);//返回删除的条数
System.out.println( "count: "+count );
System.out.println( "删除成功" );
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println( "删除失败: "+e.getMessage() );
}
}
public static void update(){
conn = getConnection();
try {
String sql = "UPDATE class101 set gpa = 4.9 WHERE name = 'Jack'";
st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println( "更新成功" );
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println( "更新失败" );
}
}
//查询数据库,输出符合要求的记录情况
public static void query(){
conn = getConnection();
try {
String sql = "SELECT * FROM class101";
st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
System.out.println( "最后查询的结果: " );
while( rs.next() ){
int id = rs.getInt( "id" );
String name = rs.getString("name");
float gpa = rs.getFloat( "gpa" );
System.out.println( "id: "+id +",name: "+name+",gpa: "+gpa );
System.out.println( "----------------------------------------" );
}
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
参考资料:http://www.fenby.com/course/list/1018
http://blog.csdn.net/cxwen78/article/details/6863696