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 mConnection 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,否则返回falseStudent 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,如果最多返回一条记录，用ifstu.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书籍”即可获得！
