mysql常用命令及注意事项
-
mysql内常用命令:
use database_name;#使用数据库 CREATE DATABASE database_name;#创建数据库 drop database <database_name>;#删除数据库 show tables;#查看数据库内全部数据表
-
创建数据表常用格式和数据库中常见的数据类型
CREATE TABLE IF NOT EXISTS `table_name`( `自增整形数据` INT UNSIGNED AUTO_INCREMENT, `浮点型数据` FLOAT(100) , `双精度浮点型数据` double(40), `字符串型数据` varchar(100) NOT NULL,#不能为空 PRIMARY KEY ( `自增整形数据` )#主键约束 )ENGINE=InnoDB DEFAULT CHARSET=utf8;#这里具体不需要了解,照抄就行 #补充说明:MySQL 主键约束是一个列或者列的组合,其值能唯一地标识表中的每一行;
-
增删查改:
-
获取表内全部数据:
select * from table_name;
-
添加数据:
insert into table_name (A,B,C) values(a,b,c);
-
根据给定信息 a 删除数据:
delete from table_name where a;
-
根据给定信息 a 查询数据:
select * from table_name where A like a;
-
修改指定数据:
update table_name set A=a,B=b,C=c where D=d;
-
JDBC的连接与操作
-
下载数据库连接驱动:https://static.runoob.com/download/mysql-connector-java-8.0.16.jar
-
将驱动导入项目
-
alt+enter–打开Properties界面
-
- 成功导入后包会如图显示
3.
设定参数:url,user,pwd和数据库连接**conn**
图书管理系统的JDBC功能实现
-
JDBCConnection
-
实现对数据库的连接:
package com.pickles.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCConnection { static final String driver = "com.mysql.jdbc.Driver"; static final String url = "jdbc:mysql://localhost:3306/pickles?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8"; static final String user = "root"; static final String password = "123456"; static Connection conn = null; Statement stmt = null; @SuppressWarnings("unused") public JDBCConnection() { // TODO Auto-generated constructor stub try { // 注册 JDBC 驱动 Class.forName(driver); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(url, user, password); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql; sql = "SELECT * FROM test"; ResultSet rs = stmt.executeQuery(sql); // 展开结果集数据库 while (rs.next()) { // 通过字段检索 int id = rs.getInt("Pnum"); String name = rs.getString("username"); String detail = rs.getString("detail"); System.out.println(detail); } // 完成后关闭 rs.close(); stmt.close(); conn.close(); } catch (Exception se) { // 处理 JDBC 错误 se.printStackTrace(); } finally { // 关闭资源 try { if (stmt != null) stmt.close(); } catch (Exception se2) { } // 什么都不做 try { if (conn != null) conn.close(); } catch (Exception se) { se.printStackTrace(); } } } public static void main(String[] args) { new JDBCConnection(); } public static Connection getCon() { try { conn = DriverManager.getConnection(url, user, password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } public void close() { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
-
-
Book
-
创建实体类Book,将书籍抽象成类
package com.pickles.pojo; public class Book { private int id; private String name; private String author; private int amount; private String press; public Book() { // TODO Auto-generated constructor stub } public Book(int id, String name, String author, int amount, String press) { this.id = id; this.name = name; this.author = author; this.amount = amount; this.press = press; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public int getAmount() { return amount; } public void setAmount(int amount) { this.amount = amount; } public String getPress() { return press; } public void setPress(String press) { this.press = press; } }
-
-
BookManageDAO
-
面向对象的数据库接口
package com.pickles.dao; import java.util.List; import com.pickles.pojo.Book; public interface BookManageDAO { public List<Book> getAllBook(); public boolean addBook(Book book); public boolean deleteBookByID(int id); public List<Book> searchBookByName(String name); public List<Book> updataBookByID(int id,Book book); }
-
-
BookManageImpl
-
数据库接口的实现类
package com.pickles.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.pickles.pojo.Book; public class BookManageImpl implements BookManageDAO { private List<Book> books; private boolean flag = false; Connection conn = JDBCConnection.getCon(); Statement stmt = null; @Override public List<Book> getAllBook() { // TODO Auto-generated method stub books = new ArrayList<Book>(); String str = "select * from booktable"; try { Statement st=conn.createStatement(); ResultSet rs = st.executeQuery(str); while (rs.next()) { Book book = new Book(rs.getInt("id"), rs.getString("name"), rs.getString("author"), rs.getInt("amount"), rs.getString("press")); books.add(book); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return books; } @Override public boolean addBook(Book book) { // TODO Auto-generated method stub books = new ArrayList<Book>(); String str = "insert into booktable values(?,?,?,?,?);"; try { PreparedStatement ps = conn.prepareStatement(str); ps.setInt(1, book.getId()); ps.setString(2, book.getName()); ps.setString(3, book.getAuthor()); ps.setInt(4, book.getAmount()); ps.setString(5, book.getPress()); books.add(book); flag = ps.executeUpdate() != -1; System.out.println(flag); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return flag; } @Override public boolean deleteBookByID(int id) { // TODO Auto-generated method stub String str="delete from booktable where id=?"; try { PreparedStatement ps = conn.prepareStatement(str); ps.setInt(1, id); ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return flag; } @Override public List<Book> searchBookByName(String name) { // TODO Auto-generated method stub books = new ArrayList<Book>(); String str="select * from booktable where name like ?"; try { PreparedStatement ps = conn.prepareStatement(str); ps.setString(1, name); ResultSet rs=ps.executeQuery(); while(rs.next()) { Book book = new Book(rs.getInt("id"), rs.getString("name"), rs.getString("author"), rs.getInt("amount"), rs.getString("press")); books.add(book); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return books; } @Override public List<Book> updataBookByID(int id, Book book) { // TODO Auto-generated method stub books = new ArrayList<Book>(); String str="update booktable set name=?,author=?,amount=?,press=? where id=?;"; try { PreparedStatement ps = conn.prepareStatement(str); ps.setString(1,book.getName()); ps.setString(2, book.getAuthor()); ps.setInt(3, book.getAmount()); ps.setString(4, book.getPress()); ps.setInt(5, id); flag=ps.executeUpdate()!=-1; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return books; } }
-