Implement terminal management of MySQL database on Mac

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 will appear:

Enter password:

If you have not changed your password at this time, just hit Enter. Otherwise, enter your password.
This way you can access your database server.

---

1. Operation and management

Basic of data table

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 Modify the database

The alter command modifies the database encoding:

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 use database

mysql> use firstDB;
Database changed

1.5 View the currently used database

mysql> select database();
+------------+| database() |
+------------+| firstdb    |
+------------+1 row in set (0.00 sec)

1.6 drop Delete the database

mysql> drop database firstDB;
Query OK, 0 rows affected (0.00 sec)

2. Data table (table) Management

We first create a database to provide us with future use:

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

After creation, remember to use the use command to enter (use) the database, otherwise subsequent operations will be unsuccessful.

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

Display all 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, modify)
The table created by default does not support Chinese characters , so the table encoding needs to be set 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 Add 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

Tips: In MySQL, Boolean types are automatically converted to tinyint(1) types.

We might as well use desc to check 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) 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) .

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 delete table (column) field

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

After deletion, check 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)

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

2.4.4 rename rename the 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 fields that are allowed to be empty or not allowed to be empty:

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 to be empty", that is, this field does not need to be entered when inserting records. Otherwise it's the opposite.
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 check 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, Including adding, deleting, modifying and checking data.
The following commands all operate on the PEOPLE table.

3.1 Add data (increase)
The PEOPLE table currently has no data. It is an empty data table. Let’s add some data now.
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)

Use the select command to view the table (will be introduced later). Now we view 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 table now has a piece of data .

Let’s 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 | 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 Delete data (delete)

delete command to delete 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" can no longer be seen.

3.3 Modify data (change)

update command to modify 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 the 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 rows in set (0.00 sec)

named The record for "Garvey" has been changed to "Calvin".

3.4 Query data (check)

select command to query data. The simplest is to query all the data in 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

, * 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 from

.

The select query command also has many advanced uses, such as finding unique data (distinct), sorting data according to conditions (order by), displaying data according to query conditions (where), etc. These will be highlighted in the next article. Please continue to pay attention to my blog. Thank you.

4. Management view

4.1 Create a view

A view is a virtual table that exports one or more tables from the database for convenience. User operations on data.

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

View the 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   |  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.01 sec)

4.2 Replace 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 view after creation or replacement.

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 Operating views

When the view data changes (add, delete, modify), the real table data will also change. In other words, the operation on the view is the data of the table, so we can treat 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 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)

可以在视图上看到我们刚刚插入的数据，现在我们就来验证一下真实的表是否也会作出变化。

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)

可见，真实的表数据也已经有所改变，刚刚往视图里插入的那一条数据存在于真实表中，真理便是：对视图的操作就是对表的数据。

4.4 删除视图

mysql> DROP VIEW PEOPLE_VIEW;Query OK, 0 rows affected (0.00 sec)

相关推荐：

mac使用终端运行mysql,mysql终端,mysql mac,mysql目录,mysql路径，macmysql

mac使用终端运行mysql,mysql终端,mysql mac,mysql目录,mysql路径

The above is the detailed content of Implement terminal management of MySQL database on Mac. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

mac mysql

source：php.cn

Previous article：MySQL example explanation: adding accounts, granting permissions, and deleting users Next article：[MySQL Database] Interpretation of Chapter 1: MySQL Architecture and History

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Latest Articles by Author

• WeChat development tutorial: automatic reply to messages and customer service messages, text with link jump
  2018-08-10 17:53:11
• Detailed explanation of the usage of call(), apply() and bind() in js
  2018-08-10 17:50:08
• Comparison of the three major rounding functions of js (round(), ceil(), floor())
  2018-08-10 17:44:54
• Latest collection of common JavaScript event types
  2018-08-10 17:21:11
• C# .NET/JS implementation of Paypal payment function
  2018-08-10 17:11:46
• js visual area loading: getBoundingClientRect method
  2018-08-10 16:56:16
• What is modular programming? Summary of js modular programming
  2018-08-10 16:51:55
• Detailed explanation of application code of js closure and prototype
  2018-08-10 16:35:09
• Practical exercise--js to transfer data between web pages
  2018-08-10 16:16:37
• Summary and compilation of Java interview questions from major companies (all)
  2018-08-10 15:43:58

Latest Issues

How to display only the lowest value from a result set (MYSQL) I have the following statement: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'...

From 2024-04-06 21:44:19

0

1

603

Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...

From 2024-04-06 19:39:29

0

1

441

MySQL: "Select text from... as <variable here or subquery>" I have the following table with the following data: id text language 1 german text german ...

From 2024-04-06 19:25:54

0

2

529

How to group and count in MySQL? I'm trying to write a query that extracts the total number of undeleted messages sent to f...

From 2024-04-06 18:30:17

0

1

353

MySQL gets data from multiple tables I have a eg_design table which contains the following columns: and eg_domains table which ...

From 2024-04-06 18:42:44

0

2

479

Related Topics

More>

• mysql modify data table name
• MySQL creates stored procedure
• The difference between mongodb and mysql
• How to check if mysql password is forgotten
• mysql create database
• mysql default transaction isolation level
• The difference between sqlserver and mysql
• mysqlforgot password

Popular Recommendations

• How to uninstall mysql cleanly?
• What does sql mean?
• What is the sql add, delete, modify, query statement?
• How to check the version of mysql
• How to completely uninstall the database sql server?

Popular Tutorials

More>

Related Tutorials

Popular Recommendations

Latest courses

• 
  The latest ThinkPHP 5.1 world premiere video tutorial (60 days to become a PHP expert online training course)

  1416875
• 
  PHP introductory tutorial one: Learn PHP in one week

  4257473
• 
  JAVA Beginner's Video Tutorial

  2475936
• 
  Little Turtle's zero-based introduction to learning Python video tutorial

  503693
• 
  PHP zero-based introductory tutorial

  844150

• 
  The latest ThinkPHP 5.1 world premiere video tutorial (60 days to become a PHP expert online training course)

  1416875 times of learning
• 
  JAVA Beginner's Video Tutorial

  2475936 times of learning
• 
  Little Turtle's zero-based introduction to learning Python video tutorial

  503693 times of learning
• 
  Quick introduction to web front-end development

  215280 times of learning
• 
  Master PS video tutorials from scratch

  876939 times of learning

• 
  [Web front-end] Node.js quick start

  6368 times of learning
• 
  Complete collection of foreign web development full-stack courses

  5026 times of learning
• 
  Go language practical GraphQL

  4201 times of learning
• 
  550W fan master learns JavaScript from scratch step by step

  633 times of learning
• 
  Python master Mosh, a beginner with zero basic knowledge can get started in 6 hours

  21335 times of learning

Latest Downloads

More>

Web Effects

Website Source Code

Website Materials

Front End Template

• [form button] jQuery enterprise message form contact code
• [Player special effects] HTML5 MP3 music box playback effects
• [Menu navigation] HTML5 cool particle animation navigation menu special effects
• [form button] jQuery visual form drag and drop editing code
• [Player special effects] VUE.JS imitation Kugou music player code
• [html5 special effects] Classic html5 pushing box game
• [Picture special effects] jQuery scrolling to add or reduce image effects
• [Photo album effects] CSS3 personal album cover hover zoom effect

• [Front-end template] Home Decor Cleaning and Repair Service Company Website Template
• [Front-end template] Fresh color personal resume guide page template
• [Front-end template] Designer Creative Job Resume Web Template
• [Front-end template] Modern engineering construction company website template
• [Front-end template] Responsive HTML5 template for educational service institutions
• [Front-end template] Online e-book store mall website template
• [Front-end template] IT technology solves Internet company website template
• [Front-end template] Purple style foreign exchange trading service website template

• [PNG material] Cute summer elements vector material (EPS PNG)
• [PNG material] Four red 2023 graduation badges vector material (AI EPS PNG)
• [banner picture] Singing bird and cart filled with flowers design spring banner vector material (AI EPS)
• [PNG material] Golden graduation cap vector material (EPS PNG)
• [PNG material] Black and white style mountain icon vector material (EPS PNG)
• [PNG material] Superhero silhouette vector material (EPS PNG) with different color cloaks and different poses
• [banner picture] Flat style Arbor Day banner vector material (AI+EPS)
• [PNG material] Nine comic-style exploding chat bubbles vector material (EPS+PNG)

• [Front-end template] Home Decor Cleaning and Repair Service Company Website Template
• [Front-end template] Fresh color personal resume guide page template
• [Front-end template] Designer Creative Job Resume Web Template
• [Front-end template] Modern engineering construction company website template
• [Front-end template] Responsive HTML5 template for educational service institutions
• [Front-end template] Online e-book store mall website template
• [Front-end template] IT technology solves Internet company website template
• [Front-end template] Purple style foreign exchange trading service website template

About us Disclaimer Sitemap

php.cn：Public welfare online PHP training，Help PHP learners grow quickly！