Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to manage MySQL database by terminal on Mac

2025-01-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to realize the terminal management of MySQL database on Mac, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Open the 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:

If you don't change your password at this time, hit enter directly. Otherwise, enter your password.

So you can access your database server.

1. Operation and management of

The base of the data table

Database (database) management

1.1 create 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 code:

The database created by default does not support Chinese characters by default. 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 uses the database

Mysql > use firstDB;Database changed

1.5 View the database currently in use

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, datasheet (table) management

Let's first create a database for our future use:

Mysql > create database testDB;Query OK, 1 row affected (0.00 sec)

Remember to use the use command to enter (use) the database after creation, otherwise the subsequent operations will not be successful.

2.1 create create Table

Mysql > create table PEOPLE (- > ID int AUTO_INCREMENT PRIMARY KEY,-> NAME varchar (20) not null,-> AGE int not null,-> BIRTHDAY datetime); Query OK, 0 rows affected (0.01 sec)

2.2 show display table

Show all the data tables in 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 (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.01 sec) |

2.4 alter modify table structure (add, delete, change)

The default created table 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 adds columns (fields) to the 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, Boolean types are automatically converted to tinyint (1) types.

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, is it time for you to believe me?

2.4.2 alter modifies table (column) fields

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).

Once again, we 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 | int (11) | YES | | NULL | | +-+-+ 5 rows in set (0.00 sec) |

2.4.3 delete delete table (column) fields

Mysql > alter table PEOPLE DROP column star;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

After deletion, view the structure of the PEOPLE table 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) |

The field was deleted successfully, and now we can no longer see the field of star.

2.4.4 rename rename table name

Mysql > RENAME TABLE PEOPLE TO NEW_PEOPLE;Query OK, 0 rows affected (0.00 sec)

2.4.5 null or not null

Modify the table field to be empty or not:

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", that is, this field can not be entered when the record is inserted. Otherwise, on the contrary.

Its format is: 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 take a look at the tables that currently exist in the database:

Mysql > show tables;+-+ | Tables_in_testdb | +-+ | PEOPLE | | newTable | +-+ 2 rows in set (0.00 sec) 3. Data operation and management

This operation includes adding, deleting, changing and checking data.

The following commands operate on the PEOPLE table.

3.1 increase data (increase)

The PEOPLE table currently has no data, it is an empty data table, let's add some data first.

The insert into command adds data:

Mysql > insert into PEOPLE VALUES (null, 'Anny', 22,' 1992-05-22'); Query OK, 1 row affected (0.00 sec)

Use the select command to view the table (described later), and now let's look at the data of 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 shows that there is a piece of data.

We add a few more pieces of data, such as:

Mysql > select * from PEOPLE +-+ | ID | NAME | AGE | BIRTHDAY | +-+ | 1 | Anny | 22 | 1992-05-22 00:00:00 | | 2 | Garvey | | 23 | 0-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 (1991 sec) |

3.2 Delete data (delete)

The delete command deletes data:

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

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 sec)

The data named "Lisa" can no longer be seen.

3.3 modify data (change)

The update command modifies 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 the contents of PEOPLE table:

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 sec)

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

3.4 query data (check)

The select command queries data, and the simplest thing is to query all the data in the table, which is the command we used in the first place:

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 sec)

Format: select * from, * represents all fields.

You can also specify the (column) fields to display 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 sec (1991 sec) |

Format: select from.

Select query commands also have many advanced uses, such as finding data that is not duplicated (distinct), making data order by, displaying data by query condition (where), and so on. These will be highlighted in the next article, please continue to pay attention to my blog, thank you.

4. Manage the view

4.1 create a view

A view is a virtual table that exports one or more tables from the database, which is used to facilitate users to manipulate the data.

Mysql > CREATE VIEW PEOPLE_VIEW (- > NAME, AGE)-> AS SELECT NAME, AGE FROM PEOPLE

View the view after the creation is successful.

PEOPLE PEOPLE.AGE PEOPLE.BIRTHDAY PEOPLE.ID PEOPLE.NAME mysql > SELECT * FROM PEOPLE_VIEW->; +-+-+ | NAME | AGE | +-+-+ | Anny | 22 | Calvin | 23 | Nick | 24 | Rick | 24 | +-+-+ 4 rows in set (0.00 sec)

We can also use the DESC command to view 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.01sec)

4.2 replace the view

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 the view after creating or replacing.

Mysql > SELECT * FROM PEOPLE_VIEW +-+ | PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE | +-+ | 1 | Anny | 22 | | 2 | Calvin | 23 | | 4 | Nick | 24 | | 5 | Rick | 24 | +-+ 4 rows in set (0.00 sec)

4.3 Operation View

When the view data changes (add, delete, change), the real table data will also change. In other words, the operation on the view is the data on the table, so we can think of the view as a table.

Example: insert a piece of data into the view.

Mysql > INSERT INTO PEOPLE_VIEW VALUES (NULL, 'Kerry',' 33'); Query OK, 1 row affected (0.00 sec)

View the view after successfully inserting the data.

Mysql > SELECT * FROM PEOPLE_VIEW +-+ | PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE | +-+ | 1 | Anny | 22 | | 2 | Calvin | 23 | | 4 | Nick | 24 | | 5 | Rick | 24 | | 6 | Kerry | 33 | +-+ 5 rows in set (0.00 sec)

You can see the data we just inserted in the view, and now let's verify that the real table will also change.

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 (1991 sec) |

It can be seen that the real table data has also changed, the data just inserted into the view exists in the real table, the truth is: the operation on the view is the data on the table.

4.4 deleting a view

Mysql > DROP VIEW PEOPLE_VIEW;Query OK, 0 rows affected (0.00 sec) Thank you for reading this article carefully. I hope it is helpful for everyone to share how to manage MySQL database on Mac. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report