Explore SQL with MatrixOne
MatrixOne is compatible with MySQL, you can use MySQL statements directly in most of the cases. For unsupported features, see MySQL Compatibility, and for more information on connecting to MatriOne, see Using client connect to the MatrixOne server.
What is SQL?
The Structured Query Language (SQL) is used to manage a relational database management system (RDBMS). The scope of SQL includes data insertion, query, update, and delete, database schema creation and modification, and data access control.
How many types SQL does MatrixOne have?
SQL is divided into the following 6 types according to their functions in MatrixOne:
-
DDL (Data Definition Language): It is used to define database objects, including databases, tables, and views. Such as
CREATE
,ALTER
, andDROP
. -
DML (Data Manipulation Language): It is used to manipulate application related records. Such as
SELECT
,DELETE
, andINSERT
. -
DQL (Data Query Language): It is used to query the records after conditional filtering. Such as
SELECT...FROM...[WHERE]
. -
TCL (Transaction Control Language): It is used to manage transactions. Such as
COMMIT
,ROLLBACK
, orSET TRANSACTION
. -
DCL (Data Control Language): It is used to define access privileges and security levels. Such as
CREATET ACCOUNT
,DROP ACCOUNT
, andGRANT
. -
Other: Other types of management languages in MatrixOne are not directly related to data and are a general term for the acquisition and modification of database parameters and resource allocation. Such as
SHOW
,SET variable
, andKILL
.
Basic SQL about SHOW, CREATE and DROP a database
A database in MatrixOne can be considered as a collection of objects such as tables.
- To show the list of databases, use the
SHOW DATABASES
statement:
SHOW DATABASES;
- To create the database named dbdemo, use the
CREATE DATABASE db_name [options];
statement, for example:
CREATE DATABASE dbdemo;
or:
CREATE DATABASE IF NOT EXISTS dbdemo;
Add IF NOT EXISTS
to prevent an error if the database exists.
- To use the database named dbdemo, use the following statement:
USE dbdemo;
- To show all the tables in a database, use the
SHOW TABLES
statement:
SHOW TABLES FROM dbdemo;
- To delete a database, use the
DROP DATABASE
statement:
DROP DATABASE dbdemo;
Basic SQL about CREATE, SHOW, and DROP a table
- To create a table, use the
CREATE TABLE
statement:
CREATE TABLE table_name column_name data_type constraint;
For example, to create a table named person which includes fields such as number, name, and birthday, use the following statement:
CREATE TABLE person (
id INT(11),
name VARCHAR(255),
birthday DATE
);
- To view the statement that creates the table (DDL), use the
SHOW CREATE
statement:
SHOW CREATE table person;
Expected result:
+--------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`id` INT DEFAULT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`birthday` DATE DEFAULT NULL
) |
+--------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
- To delete a table, use the
DROP TABLE
statement:
DROP TABLE person;
Basic SQL about INSERT, UPDATE, and DELETE data
Common DML features are adding, modifying, and deleting table records. The corresponding commands are INSERT
, UPDATE
, REPLACE
and DELETE
.
- To insert data into a table, use the
INSERT
statement:
INSERT INTO person VALUES(1,'tom','20170912');
- To insert a record containing data of some fields into a table, use the
INSERT
statement:
INSERT INTO person(id,name) VALUES('2','bob');
- To update some fields of a record in a table, use the
UPDATE
statement:
UPDATE person SET birthday='20180808' WHERE id=2;
- To replace some field data of table records in the table, use the
UPDATE
statement:
REPLACE INTO person SET birthday='20180809' WHERE id=2;
- To delete the data in a table, use the
DELETE
statement:
DELETE FROM person WHERE id=2;
Note
The UPDATE
and DELETE
statements without the WHERE
clause as a filter operate on the entire table.
Basic SQL about Query data
DQL is used to retrieve the desired data rows from a table or multiple tables.
- To view the data in a table, use the
SELECT
statement:
SELECT * FROM person;
Expected result:
+------+------+------------+
| id | name | birthday |
+------+------+------------+
| 1 | tom | 2017-09-12 |
+------+------+------------+
1 row in set (0.00 sec)
- To query a specific column, add the column name after the
SELECT
keyword:
SELECT name FROM person;
+------+
| name |
+------+
| tom |
+------+
1 rows in set (0.00 sec)
Use the WHERE
clause to filter all records that match the conditions and then return the result:
SELECT * FROM person where id<5;
Expected result:
+------+------+------------+
| id | name | birthday |
+------+------+------------+
| 1 | tom | 2017-09-12 |
+------+------+------------+
1 row in set (0.00 sec)
Basic SQL about CREATE, GRANT, and DROP a user
CREATE
, GRANT
, and DROP
are usually used to create or delete users, and manage user privileges.
- To create a user, use the
CREATE USER
statement. The following example creates a user named mouser with the password 111:
> CREATE USER mouser IDENTIFIED BY '111';
Query OK, 0 rows affected (0.10 sec)
- To create a role for the user:
> CREATE ROLE role_r1;
Query OK, 0 rows affected (0.05 sec)
- To grant mouser the role_r1:
> GRANT role_r1 to mouser;
Query OK, 0 rows affected (0.04 sec)
- To grant mouser the privilege to create table in the dbdemo database:
GRANT create table on database * to role_r1;
- To check the privileges of mouser:
> SHOW GRANTS for mouser@localhost;
+-------------------------------------------------------+
| Grants for mouser@localhost |
+-------------------------------------------------------+
| GRANT create table ON database * `mouser`@`localhost` |
| GRANT connect ON account `mouser`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.02 sec)
You have successfully granted the permission of create table
in the database to mouser.
- To delete mouser:
DROP USER mouser;
Privilege management is a huge but very useful function. For more information, see Privilege Management.