MySQL Database Simple operation

For kids who want to work in or like MySQL, it's important to have some simple things to do with MySQL at the command line. This article describes how to log on to a MySQL database server, how to publish commands at the MySQL prompt, create a database, and perform some simple DML operations.

1. Connect to and exit MySQL

In order to connect to the MySQL database server, when you call MySQL, you usually need to provide a MySQL user name and most likely require a password. If the server is running on a machine other than the logon server, you also need to specify the host name. Contact the administrator to find out the parameters used to connect (that is, the host, user name, and password used) for the connection. Once you know the correct parameters, you can connect as follows:shell> mysql-h host-u user-pmysql> Select version (), current_date;+--------------------- ------------------+--------------+| Version ()                             | current_date |+---------------------------------------+--------------+| 5.6.17- enterprise-commercial-advanced | 2014-04-28   |+---------------------------------------+--------------+1 row in Set (0.03 sec)-- Login mysql> #提示符告诉你mysql准备为你输入命令 can be realized directly by entering MySQL directly at the shell prompt, allowing anonymous logons to the local server. Shell> mysql--Enter a semicolon to indicate the end command input and execute the command-after successful connection, you can enter quit (or \q, exit) at the mysql> prompt to exit mysql> quitbye--in Unix, You can also press the CONTROL-D key to disconnect the server.

2. Issuing commands

MySQL execution commands can be divided into non-interactive and interactive mode a) non-interactive mode non-interactive mode, also called batch mode, that is, the command you want to run in a file, and then tell MySQL to read its input from the file. Typically used to return a large amount of data, as well as batch management, to perform a special script run scenario. shell> MySQL <query.sql[[email protected] ~]# more query.sql show Databases;use cnfoselect * from Tb_tmp;  [[email protected] ~]# 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 20 13-04-28--can also use the following 2 ways to execute batch mysql > source/<dir>/filename mysql > \./<dir>/finename--as shown in the following demo [email  protected] ~]# 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 Co Lumn namesyou can turn off this feature to get aQuicker startup With-adatabase changed+------+------+------------+| name | sex | Birth |+------+------+------------+| Jack | F | 2014-04-28 | | John | M |  2013-04-28 |+------+------+------------+2 rows in Set (0.00 sec) can also execute SQL directly in Shell mode, as in the following method:-E or--execution=option Shell>mysql-e "SQL cmd1; SQL cmd2; ... " Shell>mysql--execute= "SQL cmd1; SQL cmd2; ... " b) Interactive mode interaction mode is to publish commands and execute commands directly at the MySQL prompt. The following operations, case-insensitive, enter a carriage return will be the result of command execution, that is, interactive mode. Mysql> Select Version (), current_date;mysql> Select version (), current_date;mysql> Select version (), Current_ date;--Simple calculation mysql> Select power (2,3), (5-1) *4;+------------+---------+| Power (2,3) |          (5-1) |+------------+---------+|      8 | |+------------+---------+1 row in Set (0.00 sec)-Semicolon splits multiple lines mysql> select version (); Select current_date;+------------ ---------------------------+| Version () |+---------------------------------------+| 5.6.17-enterprise-commercial-advanced |+---------------------------------------+1 Row in Set (0.01 sec) +--------------+| Current_date |+--------------+| 2014-04-28 |+--------------+1 row in Set (0.00 sec)-NewLine Input command-note, you can enter a blank line mysql> Select User (), current_date;+--- -------------+--------------+| User () | Current_date |+----------------+--------------+| [Email protected] | 2014-04-28 |+----------------+--------------+1 row in Set (0.00 sec)--Cancel execution of current command mysql> Select Current_date () \c

3. Get MySQL Help information
Entering MySQL--help directly at the prompt will get help with all the parameters of the MySQL command and can be used with the pipe character more
shell> MySQL--help

4, the meaning of common MySQL prompt
Prompt meaning
Mysql> ready to accept the new order.
Wait for the next line of the multiline command.
' > waits for the next line, waiting for the end of the string to begin with single quotation marks ("'").
> waits for the next line, waiting for the end of the string to begin with double quotation marks ("" ").
' > waits for the next line, waiting for the end of the qualifier to begin with a backslash (' ').
/*> waits for the next line, waiting for the end of the comment to start with/*.

5, daily operation

--Creating database mysql> create databases Cnfo; Query OK, 1 row Affected (0.00 sec)--switch database mysql> use cnfodatabase changed--view current database mysql> select databases (); +------- -----+| Database () |+------------+| Cnfo |+------------+1 row in Set (0.00 sec)-Connect to a specified database when you start MySQL [[email protected] ~]# mysql-u root-p cnfoenter Password:mysql> Select Database (); +------------+| Database () |+------------+| Cnfo |+------------+1 row in Set (0.01 sec)--creating table in current library mysql> create tables tb_tmp (name varchar), sex cha R (1), birth date); Query OK, 0 rows affected (0.09 sec)-Displays all tables in the current library 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:http://blog. csdn.net/leshami--Insert record for table mysql> insert into tb_tmp values (' Jcack ', ' F ', ' 20140428 '); Query OK, 1 row affected (0.08 sec) mysql> insert into tb_tmp values (' John ', ' M ', ' 20130428 '); Query OK, 1 row affected (0.02 sec)--View the records on the table 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 record on table mysql> update tb_tmp set name= ' Jack ' where NA Me= ' Jcack '; 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) 

MySQL Database Simple operation