MatrixOne从入门到实战——MatrixOne的连接和建表

前几篇文章，为大家介绍了MatrixOne这个产品，以及编译、部署MatrixOne的服务。

直通车：

MatrixOne从入门到实践——初识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中创建工程

编辑代码

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&amp;allowPublicKeyRetrieval=true&amp;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代码

环境要求
- Python – one of the following:
  - CPython : 3.6 and newer
  - PyPy : Latest 3.x version
安装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)</code></pre></li>
执行结果
{'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 类型的字段，用来当做主键。

手机扫一扫
移动阅读更方便

你可能感兴趣的文章