mysql基础操作记录
安装mysql
Mac使用homebrew安装mysql，命令行执行以下命令：brew install mysql
启动mysql服务
安装完成后执行start 命令。
➜ ~ mysql.server start
Starting MySQL
. SUCCESS!
连接数据库
命令行方式
默认用户是root，密码为空。
➜ ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Homebrew
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
JDBC方式
导入JDBC jar包
通过maven方式引入
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
下载后，add进项目
可以直接在mysql官网下载jar包，复制在项目里面，然后添加进依赖即可。
连接mysql
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBhelper{
public static void main (String[] args){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
conn.close();
} catch(SQLException e){
e.printStackTrace();
}
}
}
数据库操作
数据库操作指令
- 创建：
create database test_db; - 查询数据库：
show databases; - 删除数据库：
drop test_db;
增删改查操作指令
命令行方式
插入数据
insert into 表名 (列名....) values (数据....);
删除数据,不添加where条件会清楚整个表的数据
delete from 表名 where 条件;
更新数据,修改id为9的内容name为空
update 表名 set name='' where id=9;
查询数据
select * from table;
select 列名 from table;
select * from table where id = 9;
select * from table order by id;
连表查询
select t.id, tt.name from table1 as t inner join table2 as tt on t.id = tt.u_id;
JDBC操作增删改查
更新数据
插入，更新和删除都是对数据的更新操作，用相同的方法，只是传入的sql语句不同。
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TransactionTest {
public static String url = "jdbc:mysql://localhost:3306/test";
public static String user = "root";
public static String password = "";
public static String driverName = "com.mysql.jdbc.Driver";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void update(String sql) {
Connection conn = getConnection();
try {
Statement st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("更新了 " + count + " 条数据");
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
查询数据
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBHelper {
public static final String url = "jdbc:mysql://127.0.0.1/test";
public static final String name = "com.mysql.jdbc.Driver";
public static final String user = "root";
public static final String password = "";
Connection conn;
PreparedStatement pst;
ResultSet ret;
private void ConnectionDB() {
try {
Class.forName(name);
conn = DriverManager.getConnection(url,user,password);
pst = conn.prepareStatement("select * from myclass;");
ret = pst.executeQuery();
int col = ret.getMetaData().getColumnCount();
while (ret.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(ret.getString(i) + "\t");
if ((i == 2) && (ret.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
ret.close();
pst.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
DBHelper dbHelper = new DBHelper();
dbHelper.ConnectionDB();
}
}
JDBC处理事务
事务是为了保证数据的一致性，要么事务中的数据操作语句都成功，失败的话立即回滚。
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TransactionTest {
public static String url = "jdbc:mysql://localhost:3306/test";
public static String user = "root";
public static String password = "";
public static String driverName = "com.mysql.jdbc.Driver";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void update(String sql) {
Connection conn = getConnection();
try {
Statement st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("更新了 " + count + " 条数据");
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollbackTest() {
Connection conn = getConnection();
try {
conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.executeUpdate("insert into Websites values (8,'tutu','url',99,'CN')");
st.executeUpdate("insert into access_log values ()");
conn.commit();
st.close();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
rollbackTest();
}
}
