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;
