Spring笔记1 jdbc常用操作

https://www.bilibili.com/video/av47952931?p=8

之前学数据库的时候mysql踩了好多坑，主要是版本问题
不同版本语法有些不兼容
记录一下Apply生成的语句备用

---

版本
mysql-8.0.19-macos10.15-x86_64
MySQL Workbench 8.0.16

---

---

MySQL Workbench操作

创建schema

CREATE SCHEMA `groot` ;

---

创建table

这是b站教程给的sql

create table account(
	id int primary key auto_increment,
	name varchar(40),
	money float
)character set utf8 collate utf8_general_ci;

这是Workbench里生成的sql

CREATE TABLE `groot`.`account` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `money` FLOAT NULL,
  PRIMARY KEY (`id`));

---

插入记录

b站教程给的sql

insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);

Workbench里生成的sql

INSERT INTO `groot`.`account` (`name`, `money`) VALUES ('aaa', '1000');
INSERT INTO `groot`.`account` (`name`, `money`) VALUES ('bbb', '1000');
INSERT INTO `groot`.`account` (`name`, `money`) VALUES ('ccc', '1000');

---

查看记录

SELECT * FROM groot.account;

---

Idea中连接数据库

//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");

//2.获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/groot?useUnicode=true&characterEncoding=utf8","root","iamgroot");

//3.获取操作数据库的预处理对象
PreparedStatement pstm = conn.prepareStatement("select * from account");

//4.执行SQL，得到结果集
ResultSet rs = pstm.executeQuery();

//5.遍历结果集
while(rs.next()){
    System.out.println(rs.getString("name"));
}

//6.释放资源
rs.close();
pstm.close();
conn.close();