Simple MySQL database operations, mysql database operations

Source: Internet
Author: User
Tags table definition

Simple MySQL database operations, 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 runs on a machine other than the login server, you must specify the host name. Contact the Administrator to find the connection parameters (that is, the connected host, user name, and password ). After knowing the correct parameters, you can connect using the following methods: shell> mysql-h host-u user-pmysql> select version (), current_date; + --------------------------------------- + -------------- + | version () | current_date | + hour + -------------- + | 5.6.17-enterprise-Internal cial-advanced | 2014-04-28 | + --------------------------------------- + ------------ + 1 row in set (0.03 sec) -- You can directly enter my SQL to log on to mysql> # prompt to tell you That mysql is ready to enter commands 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 the mysql> prompt) exit mysql> QUITBye at any time -- in Unix, you can also press control-D to disconnect the server.


2. Release commands

Mysql command execution can be divided into non-interactive and interactive modes a) non-interactive mode, also called batch mode, that is, to put the command to be run in a file, then tell 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 cnfoselect * from tb_tmp; [root @ linux1 ~] # Mysql-u root-pmysql <query. sqlWarning: Using a password on the command line interface can be insecure. databaseinformation_schemacnfomysqlperformance_schematestname sex birthJack F 2014-04-28John M 2013-04-28 -- you can also use the following two methods to execute a batch of mysql> source/<dir>/filename mysql> \. /<dir>/finename -- The following Demonstration [root @ linux1 ~] # Mysql-u root-pmysqlmysql> 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 namesYou can turn off this feature to get a quicker startup with-ADatabase 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) in interactive mode, commands are directly published and executed at the mysql prompt. 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 mysql> select power (2, 3 ), (5-1) * 4; + ------------ + --------- + | power (2, 3) | (5-1) * 4 | + ------------ + --------- + | 8 | 16 | + ------------ + --------- + 1 row in set (0.00 sec) -- semicolon separates multiple rows of mysql> select version (); select current_date; + Limit + | version () | + --------------------------------------- + | 5.6.17-enterprise-defined cial-advanced | + Limit + 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 a blank line 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


3. Obtain mysql help information
Enter mysql -- help directly at the prompt to obtain help information about all mysql command parameters, which can be used with the pipeline operator more.
Shell> mysql -- help

 

4. Definitions of mysql common prompts
Prompt description
Mysql> prepare to accept new commands.
-> Wait for the next line of the multi-line command.
'> Wait for the next line, and wait for the end of the string starting with a single quotation mark.
"> Wait for the next line, and wait for the end of the string starting with double quotation marks.
'> Wait for the next line, and wait for the end of the identifier starting with the reverse oblique point.
/*> Wait for the next line and wait for the end of comments starting.

 

5. Routine operations

-- Create mysql> create database cnfo; Query OK, 1 row affected (0.00 sec) -- switch database mysql> use cnfoDatabase changed -- View Current database mysql> select database (); + ------------ + | database () | + ------------ + | cnfo | + ------------ + 1 row in set (0.00 sec) -- connect to the specified database when starting mysql [root @ linux1 ~] # Mysql-u root-p cnfoEnter password: mysql> select database (); + ------------ + | database () | + ------------ + | cnfo | + ------------ + 1 row in set (0.01 sec) -- create a table mysql> create table tb_tmp (name varchar (20) in the current database ), -> sex char (1), birth date); Query OK, 0 rows affected (0.09 sec) -- display all tables in the current database mysql> show tables; + ---------------- + | Tables_in_cnfo | + ------------------ + | tb_tmp | + ---------------- + 1 row in set (0.00 sec) -- View table definition information mysql> desc tb_tmp->; + ------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ------------- + ------ + ----- + --------- + ------- + | name | varchar (20) | YES | NULL | sex | char (1) | YES | NULL | birth | date | YES | NULL | + ------- + ------------- + ------ + ----- + --------- + ------- + 3 rows in set (0.02 sec) -- Author: Leshami -- Blog: Author> insert into tb_tmp values ('jack', 'F', '123'); Query OK, 1 row affected (20140428 sec) mysql> insert into tb_tmp values ('john', 'M', '000000'); Query OK, 1 row affected (20130428 sec) -- view the table record mysql> select * from tb_tmp; + ------- + ------ + ------------ + | name | sex | birth | + ------- + ------ + ------------ + | Jcack | F | 2014-04-28 | John | M | 2013-04-28 | + ------- + ------ + ------------ + 2 rows in set (0.00 sec) -- update the record mysql> update tb_tmp set name = 'jack' where name = 'jack'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- filter query mysql> select * from tb_tmp where sex = 'F '; + ------ + ------------ + | name | sex | birth | + ------ + ------------ + | Jack | F | 2014-04-28 | + ------ + ------------ + 1 row in set (0.00 sec)

In mysql, how can one user operate only one database?

Security, login name, double-click, user ing, This is the 2008 operation, other versions of SQL can refer to this.
 
How does the MySql database management software operate?

Management Software for MySql databases:
1. MySQL Workbench

2. Navicat for MySQL

Either of them is easy to manage and operate.

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.