11-29大型数据库实验

实验内容与完成情况：

MySQL操作

1. Shell命令操作

sql

复制代码

-- 创建Student表

CREATE TABLE Student (

    Name VARCHAR(50),

    English INT,

    Math INT,

    Computer INT

);

 

-- 插入数据

INSERT INTO Student VALUES ('zhangsan', 69, 86, 77), ('lisi', 55, 100, 88);

 

-- 查询所有记录

SELECT * FROM Student;

 

-- 查询zhangsan的Computer成绩

SELECT Computer FROM Student WHERE Name = 'zhangsan';

 

-- 修改lisi的Math成绩

UPDATE Student SET Math = 95 WHERE Name = 'lisi';

2. Java编程

java

复制代码

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "user", "password");

Statement stmt = conn.createStatement();

 

// 插入数据

String insertSQL = "INSERT INTO Student VALUES ('scofield', 45, 89, 100)";

stmt.executeUpdate(insertSQL);

 

// 查询数据

String querySQL = "SELECT English FROM Student WHERE Name = 'scofield'";

ResultSet rs = stmt.executeQuery(querySQL);

while (rs.next()) {

    System.out.println("English score: " + rs.getInt("English"));

}

---

HBase操作

1. Shell命令操作

shell

复制代码

# 创建表

create 'Student', 'English', 'Math', 'Computer'

 

# 插入数据

put 'Student', 'zhangsan', 'English:score', '69'

put 'Student', 'zhangsan', 'Math:score', '86'

put 'Student', 'zhangsan', 'Computer:score', '77'

put 'Student', 'lisi', 'English:score', '55'

put 'Student', 'lisi', 'Math:score', '100'

put 'Student', 'lisi', 'Computer:score', '88'

 

# 查询表

scan 'Student'

 

# 查询zhangsan的Computer成绩

get 'Student', 'zhangsan', 'Computer:score'

 

# 修改lisi的Math成绩

put 'Student', 'lisi', 'Math:score', '95'

 

2. Java编程

java

复制代码

Table table = connection.getTable(TableName.valueOf("Student"));

 

// 插入数据

Put put = new Put(Bytes.toBytes("scofield"));

put.addColumn(Bytes.toBytes("English"), Bytes.toBytes("score"), Bytes.toBytes("45"));

put.addColumn(Bytes.toBytes("Math"), Bytes.toBytes("score"), Bytes.toBytes("89"));

put.addColumn(Bytes.toBytes("Computer"), Bytes.toBytes("score"), Bytes.toBytes("100"));

table.put(put);

 

// 获取数据

Get get = new Get(Bytes.toBytes("scofield"));

Result result = table.get(get);

byte[] value = result.getValue(Bytes.toBytes("English"), Bytes.toBytes("score"));

System.out.println("English score: " + Bytes.toString(value));

---

Redis操作

1. Shell命令操作

shell

复制代码

# 插入数据

HSET student:zhangsan English 69 Math 86 Computer 77

HSET student:lisi English 55 Math 100 Computer 88

 

# 查询所有成绩

HGETALL student:zhangsan

 

# 查询zhangsan的Computer成绩

HGET student:zhangsan Computer

 

# 修改lisi的Math成绩

HSET student:lisi Math 95

 

2. Java编程

java

复制代码

Jedis jedis = new Jedis("localhost");

 

// 插入数据

Map<String, String> scofieldData = new HashMap<>();

scofieldData.put("English", "45");

scofieldData.put("Math", "89");

scofieldData.put("Computer", "100");

jedis.hmset("student:scofield", scofieldData);

 

// 获取数据

String englishScore = jedis.hget("student:scofield", "English");

System.out.println("English score: " + englishScore);

---

MongoDB

Shell:

// 插入文档

db.Student.insertMany([

    { name: "zhangsan", score: { English: 69, Math: 86, Computer: 77 } },

    { name: "lisi", score: { English: 55, Math: 100, Computer: 88 } }

]);

 

// 查询所有文档

db.Student.find();

 

// 查询zhangsan的所有成绩

db.Student.find({ name: "zhangsan" }, { score: 1, _id: 0 });

 

// 修改lisi的Math成绩

db.Student.updateOne(

    { name: "lisi" },

    { $set: { "score.Math": 95 } }

);

 

Java:

MongoClient mongoClient = new MongoClient("localhost", 27017);

MongoDatabase database = mongoClient.getDatabase("test");

MongoCollection<Document> collection = database.getCollection("Student");

 

// 插入数据

Document scofield = new Document("name", "scofield")

        .append("score", new Document("English", 45)

                .append("Math", 89)

                .append("Computer", 100));

collection.insertOne(scofield);

 

// 获取数据

Document query = collection.find(eq("name", "scofield")).projection(fields(include("score"), excludeId())).first();

System.out.println(query.toJson());