Data table operation -- mysql as an example (2)

Create tables in the database
CREATE TABLE table name(

    Field name data type [constraint]
)
MariaDB [cc]>  CREATE TABLE test (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(32)
    -> );
Query OK, 0 rows affected (0.15 sec)

Constraints:

  • Primary key (primary key: cannot be empty and unique)
  • Auto increment
  • DEFAULT (set the DEFAULT value, if not set, the DEFAULT value is NULL)

    See

    • View table
show tables´╝Ť
MariaDB [cc]> show tables;
+--------------+
| Tables_in_cc |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)
  • View table information
desc table name;
MariaDB [cc]> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.12 sec)
  • View information when a table is created
show create table table name;
MariaDB [cc]> show create table test;
+-------+-----------------+
| Table | Create Table      
+-------+------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  PRIMARY KEY (`id`)
+-------+-------------------+
1 row in set (0.00 sec)

modify

  • Modify table structure (about field modification)
    Add fields
alter table table name add(
    Field name data type constraint
);
MariaDB [cc]> alter table test add(
    -> age int(8) default 18
    -> );
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [cc]> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| age   | int(8)      | YES  |     | 18      |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Delete field

alter table table name DROP;
MariaDB [cc]> alter table test drop age;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Modify field content

alter table table name modify(
    Field name data type constraint [first|after field] / * change position in table*/
);
MariaDB [cc]> alter table test modify name int(8) not null default 18 ;
Query OK, 0 rows affected (0.11 sec)               
Records: 0  Duplicates: 0  Warnings: 0

Modify field name

alter table name change old field name new field name data type;
MariaDB [cc]> alter table test change name age int(12);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [cc]> desc test;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| age   | int(12) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Change table name

rename table old table name to new table name;
MariaDB [cc]> rename table test to ntest;
Query OK, 0 rows affected (0.08 sec)

Delete table

drop table table name;

END !

Tags: MariaDB Database

Posted on Sat, 04 Apr 2020 03:28:59 -0700 by pb4life55