Simple MySQL database operations

Source: Internet
Author: User
Tags mysql commands

Simple MySQL database operations

For children who want to engage in or love mysql-related work, it is necessary to implement some simple operations on mysql under the command line. This article describes how to log on to the mysql database server, how to publish commands, create databases, and perform some simple DML operations at a mysql prompt.

1. connect to and exit mysql

To connect to the mysql database server, a mysql user name and password are usually required when MySQL is called. If the server
The host name must be specified for other machines running on the login server. Contact the Administrator to find the connection parameters (that is, the connected host
). After you know the correct parameters, you can connect them as follows:
Shell> mysql-h host-u user-p
Mysql> select version (), current_date;
+ --------------------------------------- + -------------- +
| Version () | current_date |
+ --------------------------------------- + -------------- +
| 5.6.17-enterprise-defined cial-advanced |
+ --------------------------------------- + -------------- +
1 row in set (0.03 sec)

-- If anonymous logon to the local server is allowed, you can directly enter mysql at the shell prompt to log on.
Mysql> # prompt to tell you That mysql is ready to enter a command for you.

Shell> mysql
-- Enter a semicolon to end the command and run the command
-- After successful connection, you can enter QUIT (or \ q, exit) at any time at the mysql> prompt to exit.
Mysql> QUIT
Bye
-- In Unix, you can also press control-D to disconnect the server.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

2. Release commands

Mysql commands can be executed in non-interactive and interactive modes.
A) non-interactive mode
Non-interactive mode, also called batch mode, stores the command to be run in a file and tells mysql to read its input from the file.
It is usually used when a large amount of data is returned, as well as batch management, and special scripts are executed.
Shell> mysql <query. SQL

[Root @ linux1 ~] # More query. SQL
Show databases;
Use cnfo
Select * from tb_tmp;
[Root @ linux1 ~] # Mysql-u root-pmysql <query. SQL
Warning: Using a password on the command line interface can be insecure.
Database
Information_schema
Cnfo
Mysql
Performance_schema
Test
Name sex birth
Jack F 2014-04-28
John M 2013-04-28

-- You can also use the following two methods to execute the batch
Mysql> source/<dir>/filename
Mysql> \./<dir>/finename

-- Demo below
[Root @ linux1 ~] # Mysql-u root-pmysql
Mysql> source query. SQL
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Cnfo |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
+ ------ + ------------ +
| Name | sex | birth |
+ ------ + ------------ +
| Jack | F | 2014-04-28 |
| John | M | 2013-04-28 |
+ ------ + ------------ +
2 rows in set (0.00 sec)

You can also execute SQL directly in shell mode, as shown in the following method:
-E or -- execution = option
Shell> mysql-e "SQL cmd1; SQL cmd2 ;.."
Shell> mysql -- execute = "SQL cmd1; SQL cmd2 ;.."

B) Interaction Mode
In the interactive mode, the command is directly published at the mysql prompt and executed.
The following operations are case-insensitive. After you press enter, the command execution result is displayed, that is, the interactive mode.
Mysql> select version (), CURRENT_DATE;
Mysql> select version (), current_date;
Mysql> SeLeCt vErSiOn (), current_DATE;

-- Simple computing
Mysql> select power (2, 3), (5-1) * 4;
+ ------------ + --------- +
| Power (2, 3) | (5-1) * 4 |
+ ------------ + --------- +
| 8 | 16 |
+ ------------ + --------- +
1 row in set (0.00 sec)

-- Split multiple rows with semicolons
Mysql> select version (); select current_date;
+ --------------------------------------- +
| Version () |
+ --------------------------------------- +
| 5.6.17-enterprise-defined cial-advanced |
+ --------------------------------------- +
1 row in set (0.01 sec)

+ -------------- +
| Current_date |
+ -------------- +
| 2014-04-28 |
+ -------------- +
1 row in set (0.00 sec)

-- Line feed input command
-- Note: You can enter empty rows.
Mysql> select user (),
-> Current_date;
+ ---------------- + -------------- +
| User () | current_date |
+ ---------------- + -------------- +
| Root @ localhost | 2014-04-28 |
+ ---------------- + -------------- +
1 row in set (0.00 sec)

-- Cancel executing the current command
Mysql> select current_date () \ c

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.