/*创建:
create database 数据库名 [ character set 字符集 ] */
create database student character set utf8
/*创建表:*/
create table student(
id char(12),
name varchar(20),
mobile char(11),
address varchar(30)
)
/*删除:
drop database 数据库名称*/
drop database student
/*修改:
alter database 数据库名 character set 编码*/
alter database student character set utf8
/*查询:
select from student [where ='']*/
delete from student where id='001'
/*查看正在使用的数据库 select database()*/
select database()
/*查看MySQL数据库管理系统中所有数据库:show databases*/
show database
/*查看某个数据库的定义信息:show create database 数据库名*/
show create database
/*删除数据*/
delete from student where id='001'
/*修改数据*/
update student set name='Jim',mobile='13528375637' where id='002'
/*添加数据*/
insert into student (id,name,mobile,address) value('001','Jack','13526509597','henan')/*依次对应添加*/
insert into student value('002','Make','18703628548','zhengzhou')/*默认全部依次添加*/
rollback;
commit;
事务四大特性(ACID):
完成一个事务:
(在转账时,转出和转入必须同时成功才能修改余额数据,若有任意一个不成功,则不对数据库内容进行修改)
通过Navicat Premium操作:
#创建一个用户信息表
create table account(
id char(36) primary key,
card_id varchar(20) unique,
name varchar(8) not null,
money float(10,2) default 0
);
insert into account values('6ab71673-9502-44ba-8db0-7f625f17a67d','1234567890','张三',1000);
insert into account (id,card_id,name) values('9883a53d-9127-4a9a-bdcb-96cf87afe831','0987654321','张三');
select * from account
update account set money=money-100 where card_id='1234567890';
update account set money=money+100 where card_id='0987654321';
select * from account
update account set money=money-100 where card_id='1234567890';
update account set money=money+100 where card_d='0987654321';
select * from account
#默认:每一条sql语句都是一个独立的事务,执行完单条sql语句后直接持久化到磁盘
set autocommit=0;#设置执行完一条sql语句后不再自动提交
update account set money=money-100 where card_id='1234567890';
update account set money=money+100 where card_d='0987654321';
commit;#手动提交
rollback;#手动回滚
select * from account
通过Java操作:
package com.jd.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public boolean batch(String...sqls) {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");//1、加载驱动
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url ,"root","root");//2、获取连接
connection.setAutoCommit(false);//3、关闭自动提交
Statement statement = connection.createStatement();//4、创建sql语句
for (String sql : sqls) {
statement.addBatch(sql);
}
statement.executeBatch();//5、执行sql语句
connection.commit();//6、手动提交
return true;
} catch (Exception e) {
try {
if (connection!=null) {
connection.rollback();//7、若出现问题,则进行回滚
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
return false;
}
public static void main(String[] args) {
if(new Test().batch("update account set money=money-100 where card_id='1234567890'","update account set money=money+100 where card_id='0987654321'")) {
System.out.println("Yes");
return;
}
System.out.println("No");
}
}
本文地址:https://blog.csdn.net/weixin_45949075/article/details/107141247
如对本文有疑问, 点击进行留言回复!!
Error: Avoided redundant navigation to current location: “/XXX“.的问题
Avoided redundant navigation to current location:
荐 四十一、Vue项目上手 | 用户管理系统 实现用户修改和删除功能(完成篇)
网友评论