MySQL data definition language (DDL)

Posted by zaneosak on Mon, 23 Mar 2020 16:38:48 +0100

SQL consists of the following four parts:
    1 data definition language (DDL): DROP, CREATE, ALTER and other statements.
    2 data operation language (DML): INSERT, UPDATE and DELETE statements.
    3 data query language (DQL): SELECT statement.
    4 data control language (DCL): GRANT, REVOKE, COMMIT, ROLLBACK and other statements.

DDL(Data Definition Language) of MySQL: Data Definition Language: CREATE, ALERT, DROP, etc

DDL: operation database, table (CRUD)

1. Operation database

(1)C(Create): create database

CREATE DATABASE [IF NOT EXISTS] database name
 [[DEFAULT] CHARACTER SET character set name]
[[DEFAULT] COLLATE proofing rule name];

eg.

mysql> CREATE DATABASE IF NOT EXISTS test
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.01 sec)

 

(2)R(Retrieve): query

SHOW DATABASES [LIKE 'Database name'];

eg. query all

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |         |
| mqcms              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| sys                |
| test               |
| test_aa            |
| ultrax             |
+--------------------+
9 rows in set (0.00 sec)

eg. query a certain database (I have two libraries: Test and test_aa)

mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)
mysql> show databases like '%test%';
+-------------------+
| Database (%test%) |
+-------------------+
| dedetest          |
| test              |
| test_aa           |
+-------------------+
3 rows in set (0.00 sec)

like here is the same as fuzzy query% aa%;%aa;aa%;

eg. query the creation language of a database

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

 

(3)U(Update): modifying

 

 

To be continued

Topics: MySQL Database SQL phpMyAdmin