大数据——四种数据库(MySQL,HBase,MongoDB,Redis)操作实例
问题描述:
Student学生表
1. 根据上面给出的表格,利用MySQL5.7设计出student学生表格;
a) 设计完后,用select语句输出所有的相关信息,并给出截图;
b) 查询zhangsan的Computer成绩,并给出截图;
c)修改lisi的Math成绩,改为95.给出截图.
2. 根据上面已经设计出的student表,用MySQL的JAVA客户端编程;
a)添加数据:English:45 Math:89 Computer:100
b)获取scofield的English成绩信息
问题解决:
1. 根据上面给出的表格,利用MySQL5.7设计出student学生表格;
设计表代码:(复制粘贴代码时,要删除注释部分,下同)
$ hadoop@jiantao-VirtualBox:~$ mysql -u root -p
(密码是:******)
进入mysql>之后,分别执行:
mysql> Create DATABASE stu; //创建数据库stu
mysql> USE stu;
mysql> create table student( //创建表student
name varchar(30) not null,
English tinyint unsigned not null,
Math tinyint unsigned not null,
Computer tinyint unsigned not null
);
mysql> insert into student values("zhangsan",69,86,77); //向表中插入数据
mysql> insert into student values("lisi",55,100,88);
对应的Linux终端运行截图:
a) 设计完后,用select语句输出所有的相关信息,并给出截图;
mysql> select * from student;//显示表中的所有数据
对应的Linux终端运行截图:
b) 查询zhangsan的Computer成绩,并给出截图;
mysql> select name , Computer from student where name = "zhangsan"; //查询zhangsan的Computer成绩
对应的Linux终端运行截图:
c)修改lisi的Math成绩,改为95.给出截图.
mysql> update student set Math=95 where name="lisi"; //修改lisi的Math成绩,改为95.
对应的Linux终端运行截图:
2. 根据上面已经设计出的student表,用MySQL的JAVA客户端编程;
(1)按Ubuntu操作系统桌面左上角的搜索图标,搜索到Eclipse,点击打开
(2)新建一个Java Project,随便起一个工程名,点击完成
a)添加数据:English:45 Math:89 Computer:100
(1)右键点击工程名,新建一个class,起名mysql_test
(2)将如下代码粘贴至mysql_test.java
import java.sql.*;
public class mysql_test {
/**
* @param args
*/
//JDBC DRIVER and DB
static final String DRIVER="com.mysql.cj.jdbc.Driver";
static final String DB="jdbc:mysql://localhost/stu?useUnicode=true&characterEncoding=utf-8&useSSL=false";
//Database auth
static final String USER="root";
static final String PASSWD="123456";
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=null;
Statement stmt=null;
try {
//加载驱动程序
Class.forName(DRIVER);
System.out.println("Connecting to a selected database...");
//打开一个连接
conn=DriverManager.getConnection(DB, USER, PASSWD);
//执行一个查询
stmt=conn.createStatement();
String sql="insert into student values('scofield',45,89,100)";
stmt.executeUpdate(sql);
System.out.println("Inserting records into the table successfully!");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
(3)将JDBC驱动mysql-connector-java-6.0.5-bin.jar导入到工程中去,操作方法:工程名(右键)--buildpath--configure build path--add external jars(在Libraries栏目下),添加mysql-connector-java-6.0.5-bin.jar(如果没有jar包,可下载:https://download.csdn.net/download/weixin_43042683/12439546),点击确定。
(4)运行此mysql_test.java,在Linux终端通过select语句查询名为scofield的学生记录是否已经被添加?
java运行结果:
Nosql检验结果:scofield的学生记录已经被添加
b)获取scofield的English成绩信息
(1)为了获取scofield的English成绩信息,请将如下代码添加至新建的mysql_query.java文件中,调试运行,给出结果截图。
import java.sql.*;
public class mysql_query {
//JDBC DRIVER and DB
static final String DRIVER="com.mysql.cj.jdbc.Driver";
static final String DB="jdbc:mysql://localhost/stu?useUnicode=true&characterEncoding=utf-8&useSSL=false";
//Database auth
static final String USER="root";
static final String PASSWD="123456";
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
//加载驱动程序
Class.forName(DRIVER);
System.out.println("Connecting to a selected database...");
//打开一个连接
conn=DriverManager.getConnection(DB, USER, PASSWD);
//执行一个查询
stmt=conn.createStatement();
String sql="select name,English from student where name='scofield' ";
//获得结果集
rs=stmt.executeQuery(sql);
System.out.println("name"+"\t\t"+"English");
while(rs.next())
{
System.out.print(rs.getString(1)+"\t\t");
System.out.println(rs.getInt(2));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
if(rs!=null)
try {
rs.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Java运行结果如下: