MatrixOne從入門到實戰04——MatrixOne的連接和建表( 二 )

2026-05-10 生活百科

執行結果

連接數據庫...輸入sql語句后并執行...id: 1名字: weder性別: manid: 2名字: tom性別: manid: 3名字: wederTom性別: man執行成功！

python代碼

環境要求
- Python – one of the following:
  - python.org/" rel="external nofollow noreferrer">CPython : 3.6 and newer
  - PyPy : Latest 3.x version
安裝PIP
python3 -m pip install PyMySQL
準備測試數據
CREATE DATABASE test;USEtest;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 recordsql = "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 recordsql = "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 類型的字段，用來當做主鍵。

推薦閱讀

上一篇：MatrixOne從入門到實踐08——SSB性能測試

下一篇：沒有了