MatrixOne从入门到实战04——MatrixOne的连接和建表
宦烈
MatrixOne从入门到实战——MatrixOne的连接和建表
本章主要讲述如何使用不同方式连接MatrixOne以及进行表的创建。
连接MatrixOne
使用MySQL Client连接
使用MySQL Client连接mo服务时，我们需要在能够和MO服务通信的机器上准备一个mysql client。
安装MySQL Client
-
卸载mariadb
# 查询有无相关依赖 rpm -qa |grep mariadb # 卸载相关依赖 rpm -e xxx -
安装mysql-client
# 下载以下rpm包 https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.35-1.el7.x86_64.rpm https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.35-1.el7.x86_64.rpm https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.35-1.el7.x86_64.rpm # 安装rpm rpm -ivh mysql-community-common-5.7.35-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-5.7.35-1.el7.x86_64.rpm rpm -ivh mysql-community-client-5.7.35-1.el7.x86_64.rpm
使用mysql-client
mysql -h 192.168.110.170 -P6001 -uroot -p
-
连接成功后
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1001 Server version: 0.5.0 MatrixOne Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
使用JDBC驱动
Java代码
-
下载JDBC 连接器
-
下载安装JDK
-
具备一款代码编辑工具（IntelliJ IDEA,Eclipse）
-
准备测试数据
-
MatrixOne 建表
CREATE DATABASE test; USE test; CREATE TABLE `user` (`id` int(11) ,`user_name` varchar(255) ,`sex` varchar(255)); insert into user(id,user_name,sex) values('1', 'weder', 'man'), ('2', 'tom', 'man'), ('3', 'wederTom', 'man'); select * from user; +------+-----------+------+ | id | user_name | sex | +------+-----------+------+ | 1 | weder | man | | 2 | tom | man | | 3 | wederTom | man | +------+-----------+------+ -
在IDEA中创建工程
-
启动IDEA之后，选择左上角的File > New > Project创建一个新的项目工程
-
在弹出的对话框中选择Maven > Next > 在Name中输入项目名称 >点击Finish
-
项目创建完成后，编辑项目中的pom.xml文件，增加以下内容：
<dependencies> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> </dependencies> -
然后点击页面右上角的maven的刷新按钮，加载依赖
-
此时选择
src > main > java目录，鼠标右键点击选择New > Java Class > 选择Class 并输入类名为：MoDemo
-
-
-
编辑代码
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MoDemo { //test为数据库名称 // MySQL 8.0 以下版本选择 // static final String JdbcDriver = "com.mysql.jdbc.Driver"; // static final String Url = "jdbc:mysql://192.168.110.173:6001/test"; // MySQL 8.0 以上版本选择 static final String JdbcDriver = "com.mysql.jdbc.Driver"; static final String Url = "jdbc:mysql://192.168.110.173:6001/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; //输入连接数据库的用户名与密码 static final String User = "root";//输入你的数据库用户名 static final String PassWord = "111";//输入你的数据库连接密码 public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // 注册 JDBC 驱动 Class.forName(JdbcDriver); // 打开链接 System.out.println("连接数据库..."); conn = (Connection) DriverManager.getConnection(Url, User, PassWord); // 执行查询 System.out.println("输入sql语句后并执行..."); stmt = conn.createStatement(); String sql; sql = "select * from user";// 这里填写需要的sql语句 //执行sql语句 ResultSet rs = stmt.executeQuery(sql); // 展开结果集数据库 while (rs.next()) { // 通过字段检索 int id = rs.getInt("id");//获取id值 String name = rs.getString("user_name");//获取user_name值 String sex = rs.getString("sex");//获取sex值 // 输出数据 System.out.println("id: " + id); System.out.println("名字: " + name); System.out.println("性别: " + sex); } // 完成后关闭 rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // 处理 JDBC 错误 se.printStackTrace(); } catch (Exception e) { // 处理 Class.forName 错误 e.printStackTrace(); } finally { // 关闭资源 try { if (stmt != null) { stmt.close(); } } catch (SQLException se2) { } try { if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } System.out.println("\n执行成功！"); } } -
执行结果
连接数据库... 输入sql语句后并执行... id: 1 名字: weder 性别: man id: 2 名字: tom 性别: man id: 3 名字: wederTom 性别: man 执行成功！
python代码
-
环境要求
安装PIP
python3 -m pip install PyMySQL -
准备测试数据
CREATE DATABASE test; USE test; CREATE TABLE `user` (`id` int(11) ,`user_name` varchar(255) ,`sex` varchar(255)); insert into user(id,user_name,sex) values('1', 'weder', 'man'), ('2', 'tom', 'man'), ('3', 'wederTom', 'man'); select * from user; +------+-----------+------+ | id | user_name | sex | +------+-----------+------+ | 1 | weder | man | | 2 | tom | man | | 3 | wederTom | man | +------+-----------+------+ -
具备一款代码编辑工具 (pycharm) 或者直接在Linux 上编辑python文件
-
编辑代码
import pymysql.cursors # Connect to the database connection = pymysql.connect(host='127.0.0.1', user='dump', password='111', database='test', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # Create a new record sql = "INSERT INTO user (id,user_name,sex) VALUES (%s, %s, %s)" cursor.execute(sql, ('4', 'Jerry', 'man')) # connection is not autocommit by default. So you must commit to save # your changes. connection.commit() with connection.cursor() as cursor: # Read a single record sql = "SELECT id,user_name,sex FROM user WHERE id=%s" cursor.execute(sql, ('4',)) result = cursor.fetchone() print(result) -
执行结果
{'id': 4, 'user_name': 'Jerry', 'sex': 'man'}
建表
目前MatrixOne没有特殊的建表语法，建表时，只需要按照下列语法进行即可
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
name1 type1,
name2 type2,
...
)
-
示例
创建普通表
CREATE TABLE test(a int, b varchar(10));清空普通表
目前还不支持truncate语法删除普通表
drop table test;创建带有主键的表**(注意：MatrixOne 表名和列名不区分大小写，大写的表名和列名都会转为小写)**
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );查看表：
MySQL [ssb]> desc persons; +-----------+---------+------+------+---------+---------+ | Field | Type | Null | Key | Default | Comment | +-----------+---------+------+------+---------+---------+ | id | INT | NO | PRI | NULL | | | lastname | VARCHAR | NO | | NULL | | | firstname | VARCHAR | YES | | NULL | | | age | INT | YES | | NULL | | +-----------+---------+------+------+---------+---------+ 4 rows in set (0.00 sec)主键表目前支持多个字段作为主键，如下面的建表语句：
MySQL [ssb]> CREATE TABLE Students ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID,LastName) ); Query OK, 0 rows affected (0.01 sec)查看表：
+-----------------------------+---------+------+------+---------+---------+ | Field | Type | Null | Key | Default | Comment | +-----------------------------+---------+------+------+---------+---------+ | id | INT | NO | | NULL | | | lastname | VARCHAR | NO | | NULL | | | firstname | VARCHAR | YES | | NULL | | | age | INT | YES | | NULL | | | __mo_cpkey_002id008lastname | VARCHAR | NO | PRI | NULL | | +-----------------------------+---------+------+------+---------+---------+ 5 rows in set (0.03 sec)这里会发现有一个隐藏字段是id和lastname结合的一个varchar 类型的字段，用来当做主键。
类似资料：