在诗词飞花令游戏数据库poemGameDB中,用MySQL完成下列查询操作: (1)查询poem表中所有唐代诗人所创作的诗词,列出诗词标题,作者,朝代以及诗词内容。 (2)查询poemGameDB数据库中记录的包含地名令的所有诗
时间: 2024-05-04 19:19:01 浏览: 71
(1)查询语句如下:
```
SELECT title, author, dynasty, content FROM poem WHERE dynasty='唐';
```
(2)查询语句如下:
```
SELECT title, author, content FROM poem WHERE content LIKE '%地名%';
```
其中,`%`表示通配符,代表任意字符。所以上述查询语句表示查询包含“地名”这个词的所有诗。
相关问题
1、创建名诗词数据表,字段主要包括:id、题名、作者姓名、全文。 2、实现添加、查询、修改、删除诗词信息的功能。
创建名诗词数据表的SQL语句如下:
CREATE TABLE poem (
id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(50) NOT NULL,
content TEXT NOT NULL
);
其中,id为自增主键,title为题名,author为作者姓名,content为诗词全文。
添加诗词信息的SQL语句如下:
INSERT INTO poem (title, author, content) VALUES ('静夜思', '李白', '床前明月光,疑是地上霜。举头望明月,低头思故乡。');
查询诗词信息的SQL语句如下:
SELECT * FROM poem WHERE author = '李白';
修改诗词信息的SQL语句如下:
UPDATE poem SET title = '夜泊牛渚怀古', content = '牛渚西江夜,青山独归迟。浊酒一杯相送,古今多少事。' WHERE id = 1;
删除诗词信息的SQL语句如下:
DELETE FROM poem WHERE id = 1;
使用sharepreferences 1、创建名诗词数据表,字段主要包括:id、题名、作者姓名、全文。 2、实现添加、查询、修改、删除诗词信息的功能。
以下是使用SharedPreferences实现诗词数据表的示例代码:
1. 创建名诗词数据表,字段主要包括:id、题名、作者姓名、全文。
```java
public class Poem {
private int id;
private String title;
private String author;
private String content;
public Poem(int id, String title, String author, String content) {
this.id = id;
this.title = title;
this.author = author;
this.content = content;
}
public int getId() {
return id;
}
public String getTitle() {
return title;
}
public String getAuthor() {
return author;
}
public String getContent() {
return content;
}
}
```
2. 实现添加、查询、修改、删除诗词信息的功能。
```java
public class PoemManager {
private static final String POEM_PREFS_NAME = "poem_prefs";
private static final String POEM_KEY_PREFIX = "poem_";
private Context mContext;
private SharedPreferences mPrefs;
public PoemManager(Context context) {
mContext = context;
mPrefs = context.getSharedPreferences(POEM_PREFS_NAME, Context.MODE_PRIVATE);
}
public void addPoem(Poem poem) {
SharedPreferences.Editor editor = mPrefs.edit();
editor.putString(POEM_KEY_PREFIX + poem.getId(), poemToString(poem));
editor.apply();
}
public Poem getPoem(int id) {
String poemString = mPrefs.getString(POEM_KEY_PREFIX + id, null);
if (poemString != null) {
return stringToPoem(poemString);
}
return null;
}
public void updatePoem(Poem poem) {
SharedPreferences.Editor editor = mPrefs.edit();
editor.putString(POEM_KEY_PREFIX + poem.getId(), poemToString(poem));
editor.apply();
}
public void deletePoem(int id) {
SharedPreferences.Editor editor = mPrefs.edit();
editor.remove(POEM_KEY_PREFIX + id);
editor.apply();
}
public List<Poem> getAllPoems() {
List<Poem> poemList = new ArrayList<>();
Map<String, ?> allEntries = mPrefs.getAll();
for (Map.Entry<String, ?> entry : allEntries.entrySet()) {
if (entry.getKey().startsWith(POEM_KEY_PREFIX)) {
Poem poem = stringToPoem((String) entry.getValue());
if (poem != null) {
poemList.add(poem);
}
}
}
return poemList;
}
private String poemToString(Poem poem) {
return poem.getId() + "," + poem.getTitle() + "," + poem.getAuthor() + "," + poem.getContent();
}
private Poem stringToPoem(String poemString) {
String[] parts = poemString.split(",");
if (parts.length == 4) {
int id = Integer.parseInt(parts[0]);
String title = parts[1];
String author = parts[2];
String content = parts[3];
return new Poem(id, title, author, content);
}
return null;
}
}
```
使用示例:
```java
// 创建诗词管理器
PoemManager poemManager = new PoemManager(context);
// 添加诗词
Poem poem = new Poem(1, "静夜思", "李白", "床前明月光,疑是地上霜。举头望明月,低头思故乡。");
poemManager.addPoem(poem);
// 查询诗词
Poem queriedPoem = poemManager.getPoem(1);
// 修改诗词
if (queriedPoem != null) {
queriedPoem.setTitle("静夜思(改)");
poemManager.updatePoem(queriedPoem);
}
// 删除诗词
poemManager.deletePoem(1);
// 获取所有诗词
List<Poem> poemList = poemManager.getAllPoems();
```