MySql MySql使用方法 Mysql例子

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