Doris MySQL Commands

Run the CREATE DATABASE command to create a database. You must specify the database name when creating a database.

mysql> CREATE DATABASE demo;
Query OK, 0 rows affected (0.00 sec)

Assume that this is a table that records users' behavior of accessing a product web page. The procedure of implementing service operations on the MySQL client is as follows:

CREATE TABLE IF NOT EXISTS demo.example_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "User ID",
    `date` DATE NOT NULL COMMENT "Data import date and time",
    `city` VARCHAR(20) COMMENT "City where the user locates",
    `age` SMALLINT COMMENT "User age",
    `sex` TINYINT COMMENT "User gender",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time",
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 3"
);

Run the INSERT INTO command to insert data into a specified table. You need to specify the table name, column, and value.

INSERT INTO demo.example_tbl (user_id,date,city,age,sex,last_visit_date,cost,max_dwell_time,min_dwell_time) VALUES('10000','2017-10-01','A','20','0','2017-10-01 07:00:00','35','10','2'),('10001','2017-10-01','A','30','1','2017-10-01 17:05:45','2','22','22'),('10002','2017-10-02','B','20','1','2017-10-02 12:59:12','200','5','5'),('10003','2017-10-02','C','32','0','2017-10-02 11:20:12','30','11','11'),('10004','2017-10-01','D','35','0','2017-10-01 10:00:15','100','3','3'),('10004','2017-10-03','D','35','0','2017-10-03 10:20:22','11','6','6');

• demo.example_tbl indicates the table name.
• user_id, date, city, age, sex, last_visit_date, cost, max_dwell_time, and min_dwell_time are columns.
• VALUES indicates the inserted value.

• Use Doris to perform the quick data query.

  mysql> SELECT * FROM demo.example_tbl;                                                                                                                                                                      +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
  +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  | 10000   | 2017-10-01 | A    |   20 |    0 | 2017-10-01 07:00:00 |   35 |             10 |              2 |
  | 10001   | 2017-10-01 | A    |   30 |    1 | 2017-10-01 17:05:45 |    2 |             22 |             22 |
  | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
  | 10003   | 2017-10-02 | C    |   32 |    0 | 2017-10-02 11:20:12 |   30 |             11 |             11 |
  | 10004   | 2017-10-01 | D    |   35 |    0 | 2017-10-01 10:00:15 |  100 |              3 |              3 |
  | 10004   | 2017-10-03 | D    |   35 |    0 | 2017-10-03 10:20:22 |   11 |              6 |              6 |
  +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  6 rows in set (0.02 sec)
  
  

• Query information about city B in the demo.example_tbl table.

  mysql> SELECT * FROM demo.example_tbl where city='B';
  +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
  +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
  +---------+------------+------+------+------+---------------------+------+----------------+----------------+
  1 row in set (0.10 sec)
  
  

1. Deletes a specified row of data.

   mysql> DELETE FROM demo.example_tbl WHERE user_id = 10003;
   Query OK, 0 rows affected (0.04 sec)
   {'label':'delete_77ed273a-a052-4d64-bac0-23916b698003', 'status':'VISIBLE', 'txnId':'39'}
   
   

2. Delete a table.

   mysql> DROP TABLE demo.example_tbl;
   Query OK, 0 rows affected (0.01 sec)