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());
}
}
}