Mysql数据库的学习(课时一)
#增加一条数据 INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '500元', 456, '2021/11/30', 'spingsping') #查询表单结构 DESC book #查询表的内容 SELECT * FROM book #查询一条记录 SELECT *FROM book WHERE name='Sping'and maker='中国邮政' #查询多个条件 #且 and #或 or INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '400元', 456, '2021/11/30', 'spingsping') SELECT *FROM book WHERE price='400元'or price='500元' #修改数据内容 UPDATE `studentdb`.`book` SET `maker` = 'kut' WHERE `id` = 1005 #修改大量内容 SELECT * FROM book #创建一个数据 INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('SpingBoot', '中国人名出版社', '300元', 356, '2021/12/30', 'spingbook') #修改大量的数据内容 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (NULL, 'jquery', 'sum', '123', 345, '2020/1/2', 'gh') UPDATE `studentdb`.`book` SET `name` = 'html', `maker` = 'sumdt', `price` = '678', `num` = 340, `time` = '2021/1/2', `autor` = 'ghj' WHERE `id` = 1008 #删除表的数据 DELETE FROM `studentdb`.book WHERE ` id` = 1007 DELETE FROM `studentdb`.`book` WHERE `id` = 1004 -- 练习题 #增一条语句 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1013, 'Html5', 'hellowhtml5', '200元', 678, '2020/1/2', '你哈') #改一条语句的多个条件 UPDATE `studentdb`.`book` SET `name` = 'php', `maker` = 'as', `price` = '344元', `num` = 2334, `time` = '2021/2/4', `autor` = 'nees' WHERE `id` = 10014 AND `name` = Cast('ps' AS Binary(2)) #查表的结构 DESC book DESC tb_student #查表的内容 SELECT * FROM book #删除语句 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1015, 'Html5', 'maysquery', '210元', 678, '2020/11/30', '增加的一条语句') #删除上面增加的语句 DELETE FROM `studentdb`.`book` WHERE `id` = 1015 AND `name` = Cast('Html5' AS Binary(5)) UPDATE `studentdb`.`book` SET `maker` = '', `price` = '' WHERE `id` = 10014 AND `name` = Cast('php' AS Binary(3))
方案一：利用java的方式连接数据之前要导入架包
@A-1 定义工具类 DBUtil
package jdbc; //方案一 //构建方法体 //建立链接数据库 关闭数据库 import java.sql.*; public class DBUtil { /** * 建立数据库连接 */ //fist public static Connection getConnection() { Connection conn=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); System.out.println("加载驱动类成功"); //建立数据库 String url ="jdbc:mysql://127.0.0.1:3306/students"; System.out.println("mysql连接成功"); String username="root"; String password="123456"; conn=DriverManager.getConnection(url,username,password); } catch (Exception e) { e.printStackTrace(); System.out.println("数据库连接成功，开始了"); } return conn; } //last //封装方法 public static void getClose(Connection conn) { try { if(conn!=null) { conn.close(); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } System.err.println("数据库的内容删除成功"); } }
2 开始对数据的增删改查
2-1 增 package jdbc; import java.sql.*; import java.sql.Connection; import java.sql.DriverManager; //增加信息 public class JDBCZeng { public static void main(String[] args) throws Exception{ Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql=" INSERT INTO `students`.`student`(`stuId`, `stuName`, `stuSex`, `stuAge`) VALUES (1008, 'hjy', '女', 35)"; //4 执行语句 int count= stmt.executeUpdate(sql); if(count>0) { System.out.println("学生插入成功"); }else { System.out.println("in the end"); } DBUtil.getClose(conn); } } 2-2删 package jdbc; import java.sql.*; //查询学生信息 //删除信息 public class JDBCDelect { public static void main(String[] args) throws Exception { // 建立链接 Connection conn = DBUtil.getConnection(); //创建statement对象 Statement stmt =conn.createStatement(); //执行mysql语句 String sql="DELETE FROM `students`.`student` WHERE `stuId` = 1006"; ResultSet rs =stmt.executeQuery(sql); DBUtil.getClose(conn); } } 2-3改 package jdbc; import java.sql.*; /** * 修改数据信息用用 * @author MZFAITHDREAM * */ public class JDBCXouGai { //修改信息 public static void main(String[] args) throws Exception { // 建立链接 Connection conn = DBUtil.getConnection(); // Statement stmt =conn.createStatement(); String sql="update student set stuSex='男' "; stmt.executeLargeUpdate(sql); DBUtil.getClose(conn); } } 2-4查 package jdbc; import java.sql.*; //查询学生信息 //查询数据库的信息 public class JDBCCha { public static void main(String[] args) throws Exception { // 建立链接 Connection conn = DBUtil.getConnection(); //创建statement对象 Statement stmt =conn.createStatement(); //执行mysql语句 String sql="select *from student"; ResultSet rs =stmt.executeQuery(sql); //遍历集合 循环遍历 while(rs.next()) { int stuId=rs.getInt("stuId"); String stuName=rs.getString("stuName"); String stuSex=rs.getString("stuSex"); int stuAge=rs.getInt("stuAge"); System.out.println(stuId+"---------"+stuName+"-----------"+stuSex+"---------"+stuAge); } //关闭数据库连接 DBUtil.getClose(conn); } }
2-5完成一个标准类的封装。
package jdbc; public class Student { private int stuId; private String stuName; private String stuSex; private int stuAge; public Student() { super(); // TODO Auto-generated constructor stub } public Student(int stuId, String stuName, String stuSex, int stuAge) { super(); this.stuId = stuId; this.stuName = stuName; this.stuSex = stuSex; this.stuAge = stuAge; } /** * @return the stuId */ public int getStuId() { return stuId; } /** * @param stuId the stuId to set */ public void setStuId(int stuId) { this.stuId = stuId; } /** * @return the stuName */ public String getStuName() { return stuName; } /** * @param stuName the stuName to set */ public void setStuName(String stuName) { this.stuName = stuName; } /** * @return the stuAge */ public int getStuAge() { return stuAge; } /** * @param stuAge the stuAge to set */ public void setStuAge(int stuAge) { this.stuAge = stuAge; } @Override public String toString() { return "Student [stuId=" + stuId + ", stuName=" + stuName + ", stuSex=" + stuSex + ", stuAge=" + stuAge + "]"; }
2-5Test用于代码测试
package test; import dao.StudentDao; import dao.StudentDao2; public class StudentTest { public static void main(String[] args) { StudentDao2 s = new StudentDao2(); try { //s.insert(12, "李四", "男", 23); s.insert(15, "小吴", "女", 23); //s.delete(12); s.update(1004); s.find(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
@A-2(针对A-1的优化)
1-1 优化 不便于修改
package dao; import java.sql.*; import entily.Student; import util.*; public class StudentDao { public void find2(){ try{ Connection conn=DBUtil.getConnection(); String sql="select * from stus"; PreparedStatement ps=conn.prepareStatement(sql); ResultSet rs=ps.executeQuery(sql); while(rs.next()){ Student stu=new Student(); stu.setStuId(rs.getInt("stuId")); stu.setStuName(rs.getString("stuName")); stu.setStuSex(rs.getString("stuSex")); stu.setStuAge(rs.getInt("stuAge")); } }catch(Exception e){ e.printStackTrace(); } } public void insert(int i, String string, String string2, int j) { try { Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql="INSERT INTO `students`.`stu`(`stuId`, `stuName`, `stuSex`, `stuAge`) VALUES (1003, '你们', '男', 45)"; //4 执行语句 int count= stmt.executeUpdate(sql); if(count>0) { System.out.println("学生插入成功"); } } catch (Exception e) { e.printStackTrace(); } } public void delete() { try { Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql="delete from `stu` where stuId=1006"; ; //4 执行语句 int count= stmt.executeUpdate(sql); if(count>0) { System.out.println("学生删除成功"); } } catch (Exception e) { e.printStackTrace(); } } public void update() { try { Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql="update stu set stuSex='男' where stuId=1006"; //4 执行语句 int count= stmt.executeUpdate(sql); if(count>0) { System.out.println("学生修改成功"); } } catch (Exception e) { e.printStackTrace(); } } public void find() { try { Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql="INSERT INTO `students`.`stu`(`stuId`, `stuName`, `stuSex`, `stuAge`) VALUES (1006, '你们', '女', 45)"; //4 执行语句 stmt.executeUpdate(sql); System.out.println("插入成功"); } catch (Exception e) { e.printStackTrace(); } } }
2 Test测试
package test; import dao.StudentDao; import dao.StudentDao2; public class StudentTest { public static void main(String[] args) { StudentDao dao = new StudentDao(); da dao.find(); dao.update(); dao.delete(); dao.insert(1002, "滤镜", "男", 67);*/ }
