Implement terminal management MySQL database on Mac

Open terminal and enter the following command:

/usr/local/mysql/bin/mysql-u root-p

Where Root is the user name.

The following command appears:

Enter Password:

At this point, if you do not change the password, directly hit enter. Otherwise, enter your password.
This will allow you to access your database server.

1, the operation and management

The base of the data table

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 |+------- -------------+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) NOT NULL,    -> ; Age int. NOT NULL,    BIRTHDAY datetime);                                                     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     | 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     | NO   | PRI | NULL    | auto_increment | | NAME     | varchar (20) | NO   |     | NULL    |                | | Age      | int (one)     | 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

You can also specify the length of int (n), such as int (2).

We use DESC again to view the people table structure:

Mysql> desc people;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| ID       | int     | NO   | PRI | NULL    | auto_increment | | NAME     | varchar (20) | NO   |     | NULL    |                | | Age      | int (one)     | NO   |     | NULL    |                | | BIRTHDAY | DateTime    | YES  |     | NULL    |                | | star     | int |     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     | NO   | PRI | NULL    | auto_increment | | NAME     | varchar (20) | NO   |     | NULL    |                | | Age      | int (one)     | 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.4.5 null or NOT NULL
Modified table fields are allowed to be empty or not allowed:

Mysql> ALTER TABLE People MODIFY Age INT (3) NULL; Query OK, 0 rows affected (0.01 sec) records:0  duplicates:0  warnings:0

Set the age field of the people table to "allow null", which is the field that is not entered when the record is inserted. otherwise the opposite.
It is in the format: ALTER TABLE MODIFY

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

This operation 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 >.

The Select query command also has many advanced uses, such as finding data that is not duplicated (distinct), sorting the data by criteria (order by), displaying the data by query criteria (where), and so on. All these will be highlighted in the next article, please continue to pay attention to my blog, thank you.

4. Management view

4.1 Creating a View

A view is a virtual table that exports one or more tables from a database and is used to facilitate user manipulation of the data.

Mysql> CREATE VIEW People_view (-    name, age)-    as SELECT NAME, age from people;

View view after successful creation.

People          people. Age      people. BIRTHDAY people.id       people.name    mysql> SELECT * from People_view    , +--------+-----+| NAME   | Age |+--------+-----+| Anny   |  22 | | Calvin |  23 | | Nick   |  24 | | Rick   |  |+--------+-----+4 rows in Set (0.00 sec)

We can also use the DESC command to see the structure of the view.

Mysql> DESC people_view;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| ID    | int (11) | NO   |     | 0       |       | +-------+---------+------+-----+---------+-------+1 row in Set (0.01 sec)

4.2 Replacing views

Create or replace the original view.

mysql> CREATE OR REPLACE VIEW People_view (people_id,people_name,people_age) as SELECT id,name,age from people; Query OK, 0 rows Affected (0.00 sec)

View view after creating or replacing.

Mysql> SELECT * from people_view;+-----------+-------------+------------+| people_id | People_name | People_age |+-----------+-------------+------------+|         1 | Anny        |         | |         2 | Calvin      |         | |         4 | Nick        |         | |         5 | Rick        |         |+-----------+-------------+------------+4 rows in Set (0.00 sec)

4.3 Operation view

When the view data changes (increase, delete, change), the real table data will change as well. That is, the operation of the view is the data on the table, so we can treat it as a table.

Example: Inserting a piece of data into a view.

Mysql> INSERT into People_view VALUES (NULL, ' Kerry ', ' 33 '); Query OK, 1 row Affected (0.00 sec)

View view after inserting data successfully.

Mysql> SELECT * from People_view, +-----------+-------------+------------+| people_id | People_name | People_age |+-----------+-------------+------------+|         1 | Anny        |         | |         2 | Calvin      |         | |         4 | Nick        |         | |         5 | Rick        |         | |         6 | Kerry       |         |+-----------+-------------+------------+5 rows in Set (0.00 sec)

You can see the data we just inserted on the view, and now we're going to verify that the actual table will change as well.

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 | |  6 | Kerry  |  33 | NULL                |+----+--------+-----+---------------------+5 rows in Set (0.00 sec)

As you can see, the real table data has changed, and the data that has just been inserted into the view is present in the real table, and the truth is that the operation of the image is the data on the table.

4.4 Deleting a view

mysql> DROP VIEW People_view; Query OK, 0 rows Affected (0.00 sec)