需求:
实现代码:
create database mybank
create table bank(
customerName varchar(20) comment'用户姓名',
currentMoney int(8) comment'余额'
)charset='utf8'
insert into bank(customerName,currentMoney)
values('张三',1000),('李四',1)
begin;
insert into bank(customerName,currentMoney)
values('王五',5000),('马六',3000),('老七',500);
rollback;
BEGIN;
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='王五';
ROLLBACK;
实现效果:
事务提交后会变化;
回滚事务后,数据不会发生改变;
需求:
实现代码:
USE mybank
CREATE VIEW v1 AS SELECT*FROM bank
SELECT * FROM v1 WHERE customerName='李四';
实现效果:
需求:
实现代码:
CREATE INDEX index_name ON student(studentName,result);
CREATE UNIQUE INDEX index_name1 ON student(identityCard);
CREATE INDEX index_name ON result(studentResult);
实现效果:
需求:
实现代码:
mysqldump -u root -proot myschool student >d:\school.sql
mysqldump -u root -proot myschool result >d:\school1.sql
实现效果:
需求:
实现代码:
SELECT * FROM SUBJECT INTO OUTFILE 'd:/subject.txt';
LOAD DATA INFILE 'd:/subject.txt' INTO TABLE schoolDB
CREATE TABLE schoolDB(
studentNo INT(4),
subjectName VARCHAR(50),
classHour INT(4),
gradeID INT(4)
)CHARSET='utf8'
实现效果:
需求:
实现代码:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Jdbc02 {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/myschool?useUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "root";
//加载数据库访问驱动
try {
Class.forName("com.mysql.jdbc.Driver");
conn.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("数据库链接失败....");
}
}
}
实现效果:
需求:
实现代码:
package jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestJdbc1{
public static void main(String[] args) throws Exception {
String url="jdbc:mysql://127.0.0.1:3306/myschool?useUnicode=true&characterEncoding=UTF-8";
String username="root";
String password="root";
//加载数据库的访问驱动
//Class.forName("com.mysql.cj.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");
//创建数据库链接
Connection conn= DriverManager.getConnection(url,username,password);
System.out.println("数据库连接成功~~~");
//发送Sql语句
String sql="select * from student";
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery(sql);
//处理结果集
while(rs.next()){
String studentName = rs.getString("studentName");
String loginPwd=rs.getString("loginPwd");
System.out.println(studentName);
System.out.println(loginPwd);
}
//关闭资源
rs.close();
st.close();
conn.close();
}
}
实现效果:
以上均为个人所写,如有错误,欢迎指正,谢谢!