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 代码
-
环境要求
-
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) -
执行后果
{'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 类型的字段，用来当做主键。