[MySQL] Use MySQL (connect, select database, display database and table information)

Keywords: MySQL Database Oracle Spring

Chapter 3 Using MySQL

Article Directory

Simple Records - MySQL will always - [English]Ben Forta

You'll learn how to connect to and log in to MySQL, how to execute MySQL statements, and how to get information about databases and tables.

Connect

To connect to MySQL, you need the following information:

  • Host name (computer name) - localhost if connected to a local MySQL server;

  • A valid user name; for example, root;

  • Port (if using a port other than the default port 3306);

  • User password (your username corresponds to the password set).

Once connected, you can access any database and tables the login can access

Login: mysql [-h hostname-P port number]-u username-p password

Exit: exit or ctrl+C

mysql [h ost name-p port number]-u username-p password
Example: mysql-h localhost-P3306-u root-p123456 The first three spaces are OK, -p must have no spaces
-P means port number, -h means host host host host, and localhost means local host.

If you are logged in locally and using the default port 3306, you can log in like this

mysql -u root -p123456

C:\Users\x1c>mysql -h localhost -P 3306 -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

C:\Users\x1c>mysql -h localhost -P 3306 -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

C:\Users\x1c>mysql  -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Select Database

After connecting to MySQL, select a database operation that uses the USE keyword.

Terminology: Keyword is a reserved word that is part of the MySQL language. You cannot name a table or column with a key word.

For example, in order to use the mysqlcrashcourse database, you should enter the following USE mysqlcrashcourse:

mysql> USE mysqlcrashcourse;
Database changed
mysql>

Analysis: USE mysqlcrashcourse selected mysqlcrashcourse database, database change message indicates that mysql command line program selected database mysqlcrashcourse successfully

Note: We must use USE to open the corresponding database before we can read the data in the database.

Understanding databases and tables

What if you don't know the name of the database you can use?

How can I display a list of available databases?

Information about databases, tables, columns, users, permissions, and so on, is stored in databases and tables (MySQL uses MySQL to store this information).However, internal tables are generally not directly accessed.You can use the SHOW command of MySQL to display this information (MySQL extracts this information from internal tables).Take the following example:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mmall_learning     |
| mysql              |
| mysqlcrashcourse   |
| performance_schema |
| spring             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

SHOW DATABASES; returns a list of available databases.Included in this list may be databases used internally by MySQL (such as MySQL and information_schema in the examples).Of course, there are also many databases that I created myself (such as mmall_learning, mysqlcrashcourse, spring, which I created earlier.)

To get a list of tables in a database, use the SHOW TABLES command; as follows:

mysql> SHOW TABLES;
+----------------------------+
| Tables_in_mysqlcrashcourse |
+----------------------------+
| customers                  |
| orderitems                 |
| orders                     |
| productnotes               |
| products                   |
| vendors                    |
+----------------------------+
6 rows in set (0.01 sec)

mysql>

SHOW TABLES; returns a list of available tables in the currently selected database.SHOW can also be used to display columns of a table, such as all columns of the customers table:

mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql>

Analysis:

SHOW COLUMNS requires a table name (FROM customers in this example), which returns a row for each field containing the field name, data type, whether NULL is allowed, key information, default values, and other information (such as auto_increment for field cust_id).

Description: What is automatic increment?Some table columns require unique values.For example, the customer ID (shown in the example above).When each row is added to the table, MySQL automatically assigns the next available number to each row, instead of manually assigning unique values when adding a row (which must remember the last value used).This function is called auto-increment.If you need it, you must create the table as part of its definition.

Tip: The DESCRIBE statement MySQL supports DESCRIBE as a shortcut to SHOW COLUMNS FROM.In other words, DESCRIBE customers; SHOW COLUMNSFROM customers; is a shortcut.

Other SHOW statements supported are:

  • SHOW STATUS, used to display extensive server status information;

  • SHOW CREATE DATABASE and SHOW CREATE TABLE, respectively, are used to display MySQL statements that create specific databases or tables;

  • SHOW GRANTS, which displays the security permissions granted to users (all users or specific users);

    mysql> show grants;
    +---------------------------------------------------------------------+
    | Grants for root@localhost                                           |
    +---------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
    +---------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
  • SHOW ERRORS and SHOW WARNINGS, used to display server error or warning messages.

Tips:

To learn more about SHOW, execute the command HELP SHOW in the mysql command line utility; display the allowed SHOW statements.

mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/show.html


mysql>

Summary

It briefly describes how to connect to and log in to MySQL, how to select a database with USE, and how to view MySQL database, tables, and internal information with SHOW.

mysql -h localhost -P 3306 -u root -p123456;
use mysqlcrashcourse;
show databases;
show tables;
show columns from customers;
help show;
show status;
show grants;
show grants;
show errors;
show warnings;
Published 85 original articles. Accepted 11. Visited 10,000+
Private letter follow

Posted by TravisJRyan on Sat, 22 Feb 2020 18:48:14 -0800