一篇文章教会你如何使用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());

}

}

}