|
1、启动MySql的服务 net start mysql 2、登录MySql的命令行界面，输入密码(123456) 3、显示当前有哪些数据库 show databases; MySql中分号表示一条sql语句的结束 4、切换到某个数据库中 use 数据库名称 5、查看当前数据库中有哪些数据表 show tables; 6、查看某一张表中的数据 select * from 表名; 7、创建数据库 create database mydata; 8、切换到mydata数据库 use mydata; 9、创建一张表 create table student
(
stuNo int auto_increment primary key,
stuName varchar(30) not null,
stuAge int not null,
stuBirthday datetime
);
10、怎么知道表创建成功没有？ show tables; 11、向学生表中添加数据 insert into student values(null, 'zhangsan', 20, '2008-11-11 11-11-11');
insert into student values(null, 'lis', 20, '2008-11-11 11-11-11');
insert into student values(null, 'wangwu', 20, '2008-11-11 11-11-11');
insert into student values(null, 'zhaoliu', 20, '2008-11-11 11-11-11');
insert into student values(null, 'sunqi', 20, '2008-11-11 11-11-11');
insert into student values(null, 'qianba', 20, '2008-11-11 11-11-11');
insert into student(stuName, stuAge, stuBirthday) values('world', 20, '2008-11-11 11-11-11');
12、MySql中提供了一个分页函数 limit 起始位置处的索引 查询记录条数 select * from student limit 1, 3; 13、删除一条数据 delete from student where stuNo = 2; 14、修改数据 update student set stuName = 'hello' where stuNo = 4;
15、创建项目 16、数据库连接工具类:DBUtil.java package com.westaccp.mysql;
import java.sql.*;
/**
* 链接数据库的工具类
* @author student
*
*/
public class DBUtil {
private static final String DRIVER = 'com.mysql.jdbc.Driver';
//mydata: 数据库名
//user: 登录数据库的用户名
//password: 登录数据库的密码
private static final String URL = 'jdbc:mysql://localhost/mydata?user=root&password=123456';
/**
* 获得数据库连接的方法
* @return
*/
public static Connection getCon() {
Connection con = null;
try {
Class.forName(DRIVER); //加载驱动 程序 并 注册
con = DriverManager.getConnection(URL); //通过驱动程序获得数据局库连接对象
} catch (Exception ex) {
ex.printStackTrace();
}
return con;
}
/**
* 关闭数据库的方法
* @param rs：记录集对象
* @param pstmt：预编译上下文对象
* @param con：连接对象
*/
public static void closeDB(ResultSet rs, PreparedStatement pstmt, Connection con) {
if(rs != null) {
try {
rs.close();
rs = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
if(pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
if(con != null) {
try {
con.close();
con = null;
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
}
17、实体类:Student.java package com.westaccp.mysql;
/**
* 实体类
*
* @author student
*
*/
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String stuBirthday;
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getStuBirthday() {
return stuBirthday;
}
public void setStuBirthday(String stuBirthday) {
this.stuBirthday = stuBirthday;
}
}
18、数据库操作类(StudentDAO.java) package com.westaccp.mysql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
* 数据库操作类
* @author student
*
*/
public class StudentDAO {
public List<Student> findAll() { //ctrl shift m
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Student> stuList = null;
try {
con = DBUtil.getCon(); //通过连接类获得连接对象
String sql = 'select * from student'; //sql语句
pstmt = con.prepareStatement(sql); //通过连接对象获得预编译上下文对象
rs = pstmt.executeQuery(); //执行查询，获得记录集
stuList = new ArrayList<Student>();
while(rs.next()) { //rs.next()：让游标向下移动一行
//如果游标指向某条记录，返回true,否则返回false
Student stu = new Student();
stu.setStuNo(rs.getInt('stuNo'));
stu.setStuName(rs.getString('stuName'));
stu.setStuAge(rs.getInt('stuAge'));
stu.setStuBirthday(rs.getString('stuBirthday'));
stuList.add(stu);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeDB(rs, pstmt, con);
}
return stuList;
}
public Student findByNo(int stuNo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Student stu = null;
try {
con = DBUtil.getCon(); //通过连接类获得连接对象
String sql = 'select * from student where stuNo = ?'; //sql语句
pstmt = con.prepareStatement(sql); //通过连接对象获得预编译上下文对象
pstmt.setInt(1, stuNo);
rs = pstmt.executeQuery(); //执行查询，获得记录集
if(rs.next()) { //如果可能返回多条记录，用while,如果最多返回一条记录，用if
stu.setStuNo(rs.getInt('stuNo'));
stu.setStuName(rs.getString('stuName'));
stu.setStuAge(rs.getInt('stuAge'));
stu.setStuBirthday(rs.getString('stuBirthday'));
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeDB(rs, pstmt, con);
}
return stu;
}
public int deleteByNo(int stuNo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int rowCount = 0;
try {
con = DBUtil.getCon(); //通过连接类获得连接对象
String sql = 'delete from student where stuNo = ?'; //sql语句
pstmt = con.prepareStatement(sql); //通过连接对象获得预编译上下文对象
pstmt.setInt(1, stuNo);
rowCount = pstmt.executeUpdate(); //执行更新
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeDB(rs, pstmt, con);
}
return rowCount;
}
}
19、测试类 TestJdbc.java
package com.westaccp.mysql;
import java.util.List;
public class TestJdbc {
public static void main(String[] args) {
StudentDAO stuDao = new StudentDAO();
List<Student> stuList = stuDao.findAll();
for(Student stu : stuList) {
System.out.println(stu.getStuNo() '--' stu.getStuName());
}
}
}
一篇文章告诉你如何使用MySQL，有Java代码及sql语句，需要的朋友，关注微信“大数据研习社”后，微信后台回复“mysql”即可获得！同时，赠送《Head First PHP&MySQL》电子书一本，关注微信后，回复“mysql书籍”即可获得！ |
|
|
