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.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.35-1.el7.x86_64.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.35-1.el7.x86_64.rpm# 装置rpmrpm -ivh mysql-community-common-5.7.35-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.35-1.el7.x86_64.rpmrpm -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 1001Server version: 0.5.0 MatrixOneCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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性别: manid: 2名字: tom性别: manid: 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 databaseconnection = 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 类型的字段，用来当做主键。