AnalyticDB:SQL WorkBench/J

Test environment

Test scope

• Test connectivity: Use SQL Workbench/J to connect to a MySQL database. For Driver, select MySQL (com.mysql.jdbc.Driver). For URL, enter jdbc:mysql://localhost:3303/test4dmp. Enter your username and password, and then click Test. A dialog box confirms a successful connection.
• List databases

  show databases;
  -- Result:
  -- Database
  -- MYSQL
  -- INFORMATION_SCHEMA
  -- test4dmp

• Create a table

  CREATE TABLE `school` (
  `id` bigint NOT NULL,
  `name` varchar,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  key status_idx(`name`),
  primary key (id)
  ) DISTRIBUTE BY HASH(`id`);

• List all tables: In SQL Workbench/J, connect to the test4dmp database as the user kepler and run show tables;. The query returns 32 tables, including binary_test, course, dimension_test_date, dimension_test_date_1, dimension_test_id, dimension_test_id_1, dimension_test_int, dimension_test_int_1, dimension_test_timestamp, dimension_test_timestamp_1, and elective.
• View the table schema

  show create table school;
  -- Query result
  -- Table: school
  -- Create Table:
  CREATE TABLE `school` (
    `id` bigint NOT NULL,
    `name` varchar,
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ...
  )

• Insert data into a table

  insert into school (id, name) value (1,'a');

• View table data: In SQL Workbench/J, connect to the test4dmp database as the user kepler and run select * from school limit 10;. The query returns one row of data (id=1, name='a', create_time=2019-11-19 14:22:54, update_time=2019-11-19 14:22:54).
• Create a view: Run

  CREATE VIEW `school_view` as select * from school;

  , which returns View school_view created on success.
• View schema

  show create view school_view;
  +-------------+---------------------------------------------------------------+----------------------+----------------------+
  | View        | Create View                                                   | character_set_client | collation_connection |
  +-------------+---------------------------------------------------------------+----------------------+----------------------+
  | school_view | CREATE VIEW `test4dmp`.`school_view` AS SELECT * FROM  school | utf8                 | utf8_general_ci      |
  +-------------+---------------------------------------------------------------+----------------------+----------------------+

• Query a view: In SQL Workbench/J, run select * from school_view limit 10;. The query returns one record where id is 1, name is 'a', and both create_time and update_time are 2019-11-19 14:22:54.