Basic operation of MySQL database, data table and data

1. Database management

1.1 CREATE Database

Create Database Firstdb;

1.2 Show view all databases

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| Information_schema |
| Firstdb |
| MySQL |
| Performance_schema |
+--------------------+
4 rows in Set (0.00 sec)

1.3 Alter modifies the database

The ALTER command modifies the database encoding:

The default database created by default does not support Chinese characters, and if we need it to support Chinese characters, set its encoding to UTF8 format:

mysql> ALTER DATABASE TestDB CHARACTER SET UTF8;
Query OK, 1 row Affected (0.00 sec)

1.4 Use Database

mysql> use Firstdb;
Database changed

1.5 Viewing the currently used database

Mysql> Select Database ();
+------------+
| Database () |
+------------+
| Firstdb |
+------------+
1 row in Set (0.00 sec)

1.6 Drop Delete Database

mysql> drop Database Firstdb;
Query OK, 0 rows Affected (0.00 sec)

2, Data table (table) management

We'll start by creating a database that we'll use in the future:

mysql> CREATE DATABASE TestDB;
Query OK, 1 row Affected (0.00 sec)

After creating, remember to use the use command to enter (using) the database, or the subsequent operations will not be successful.

2.1 CREATE TABLE

Mysql> CREATE TABLE people (
-ID int auto_increment PRIMARY KEY,
-NAME varchar (a) NOT NULL,
-age int is not NULL,

Query OK, 0 rows affected (0.01 sec)

2.2 Show display table

Displays all data tables for the current database

Mysql> Show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| People |
+------------------+
1 row in Set (0.00 sec)

2.3 Desc View Table Structure

 mysql> desc People 
    , 
 +----------+-------------+------+-----+---------+----------------+ 
 | Field    | Type        | Null | Key | Default | Extra          | 
 +----------+-------------+------+-----+---------+----------------+ 
 | ID       | Int (one)     | NO   | PRI | NULL    | auto_increment | 
 | NAME     | varchar (20) | NO   |     | NULL    |                | 
 | Age      | Int (one)     | NO   |     | NULL    |                | 
 | BIRTHDAY | DateTime    | YES  |     | NULL    |                | 
 +----------+-------------+------+-----+---------+----------------+ 
 4 rows in Set (0.01 sec) 

2.4 Alter MODIFY table structure (add, delete, change)

The table created by default does not support Chinese characters, so you need to set the table encoding to UTF8:

mysql> ALTER TABLE KEYCHAIN CONVERT to CHARACTER SET UTF8;
Query OK, 1 row affected (0.02 sec)
Records:1 duplicates:0 warnings:0

2.4.1 Insert to add columns (fields) to a table

Mysql> ALTER TABLE people add star BOOL;
Query OK, 0 rows affected (0.02 sec)
records:0 duplicates:0 warnings:0

Tip: In MySQL, the Boolean type is automatically converted to the tinyint (1) type.

We might as well use DESC to look at the people table structure:

mysql> desc People;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| NAME | varchar (20) |     NO | |                NULL | |
| Age | Int (11) |     NO | |                NULL | |
| BIRTHDAY | datetime |     YES | |                NULL | |
| Star | tinyint (1) |     YES | |                NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

Now, you should believe me, right?

2.4.2 alter Modify table (column) field

Mysql> ALTER TABLE people MODIFY star int;
Query OK, 0 rows affected (0.01 sec)
records:0 duplicates:0 warnings:0

We use DESC again to view the people table structure:

mysql> desc People;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| NAME | varchar (20) |     NO | |                NULL | |
| Age | Int (11) |     NO | |                NULL | |
| BIRTHDAY | datetime |     YES | |                NULL | |
| Star | Int (11) |     YES | |                NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

2.4.3 Delete drop table (column) field

Mysql> ALTER TABLE People DROP column star;
Query OK, 0 rows affected (0.02 sec)
records:0 duplicates:0 warnings:0

Once deleted, review the People table structure again:

mysql> desc People;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| NAME | varchar (20) |     NO | |                NULL | |
| Age | Int (11) |     NO | |                NULL | |
| BIRTHDAY | datetime |     YES | |                NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in Set (0.00 sec)

Delete field is successful, now we can't see the star field anymore.

2.4.4 Rename renaming table names

Mysql> RENAME TABLE people to new_people;
Query OK, 0 rows Affected (0.00 sec)

2.5 Create new table with existing data

Mysql> CREATE TABLE newtable SELECT * from people;
Query OK, 0 rows affected (0.01 sec)
records:0 duplicates:0 warnings:0

Let's look at the table that exists in the current database:

Mysql> Show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| People |
| newtable |
+------------------+
2 rows in Set (0.00 sec)

3. Operation and management of data

The basic operation of data table includes adding, deleting, changing and checking data.

The following commands operate on the people table.

3.1 Add data (Increase)

People table currently has no data, it is an empty data table, we will now add some data.

INSERT INTO command to add data:

Mysql> INSERT INTO people VALUES (null, ' Anny ', 22, ' 1992-05-22 ');
Query OK, 1 row Affected (0.00 sec)

Using the Select command to view the table (which is described later), we now look at the data in the People data table:

Mysql> SELECT * from people;
+----+------+-----+---------------------+
| ID | NAME | Age | BIRTHDAY |
+----+------+-----+---------------------+
| 1 |  Anny | 22 | 1992-05-22 00:00:00 |
+----+------+-----+---------------------+
1 row in Set (0.00 sec)

The data is represented by a single piece of data.

We add a few more data, such as:

Mysql> SELECT * from people;
+----+--------+-----+---------------------+
| ID | NAME | Age | BIRTHDAY |
+----+--------+-----+---------------------+
| 1 |  Anny | 22 | 1992-05-22 00:00:00 |
| 2 |  Garvey | 23 | 1991-05-22 00:00:00 |
| 3 |  Lisa | 25 | 1989-05-22 00:00:00 |
| 4 |  Nick | 24 | 1990-05-22 00:00:00 |
| 5 |  Rick | 24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
5 rows in Set (0.00 sec)

3.2 Deleting data (delete)

The delete command deletes the data:

Mysql> Delete from people where name = ' Lisa ';
Query OK, 1 row affected (0.01 sec)

Query the People table again:

Mysql> SELECT * from people;
+----+--------+-----+---------------------+
| ID | NAME | Age | BIRTHDAY |
+----+--------+-----+---------------------+
| 1 |  Anny | 22 | 1992-05-22 00:00:00 |
| 2 |  Garvey | 23 | 1991-05-22 00:00:00 |
| 4 |  Nick | 24 | 1990-05-22 00:00:00 |
| 5 |  Rick | 24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in Set (0.00 sec)

The data named "Lisa" has not been seen.

3.3 Modifying data (change)

The update command modifies the data:

mysql> Update people set name= ' Calvin ' WHERE name = ' Garvey ';
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Query people table content:

Mysql> SELECT * from people;
+----+--------+-----+---------------------+
| ID | NAME | Age | BIRTHDAY |
+----+--------+-----+---------------------+
| 1 |  Anny | 22 | 1992-05-22 00:00:00 |
| 2 |  Calvin | 23 | 1991-05-22 00:00:00 |
| 4 |  Nick | 24 | 1990-05-22 00:00:00 |
| 5 |  Rick | 24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in Set (0.00 sec)

The record named "Garvey" has been modified to "Calvin".

3.4 Query data (check)

The SELECT command queries the data, and the simplest is to query all the data of the table, which is the command we used initially:

Mysql> SELECT * from people;
+----+--------+-----+---------------------+
| ID | NAME | Age | BIRTHDAY |
+----+--------+-----+---------------------+
| 1 |  Anny | 22 | 1992-05-22 00:00:00 |
| 2 |  Calvin | 23 | 1991-05-22 00:00:00 |
| 4 |  Nick | 24 | 1990-05-22 00:00:00 |
| 5 |  Rick | 24 | 1991-05-22 00:00:00 |
+----+--------+-----+---------------------+
4 rows in Set (0.00 sec)

Format: SELECT * FROM < table name >,* represents all fields.

You can also specify the displayed (column) fields when querying data:

Mysql> Select NAME, age, BIRTHDAY from people;
+--------+-----+---------------------+
| NAME | Age | BIRTHDAY |
+--------+-----+---------------------+
|  Anny | 22 | 1992-05-22 00:00:00 |
|  Calvin | 23 | 1991-05-22 00:00:00 |
|  Nick | 24 | 1990-05-22 00:00:00 |
|  Rick | 24 | 1991-05-22 00:00:00 |
+--------+-----+---------------------+
4 rows in Set (0.00 sec)

Format: Select < field name, field name,... > from < table name >.

Basic operation of MySQL database, data table and data