MySQL Basic operation

MySQL database is a C/s (client/server) architecture application, to access the MySQL database to use specialized client software

• Login to MySQL server

[email protected]/]# MySQL-u rootwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 5Server Version:5.5. A-Log Source distributioncopyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle isA registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'To clear the current input statement.mysql>

• Show MASTER LOGS statement to view log file information for the current database service

Mysql> show master logs; +------------------+-----------+| Log_name         | File_size |+------------------+-----------+| Mysql-bin. 000001 |     27284 | | Mysql-bin. 000002 |   1031892 | | Mysql-bin. 000003 |       107 |+------------------+-----------+3inset (0.01 sec)

• Exiting the mysql> operating environment

Execute "quit" "Exit" to exit the MySQL command tool and return to the original shell environment

mysql> Quitbye

• See which libraries are in the current server

Mysql> show databases; +--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               |+---------- ----------+4inset (0.00 sec)

• See which tables are currently in use in the library

Mysql>Use MySQL; #切换到所使用的库Database Changedmysql>Show tables; #查看当前所在的库中包含的表+---------------------------+| Tables_in_mysql |+---------------------------+| Columns_priv | | db | |Event|| Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Host | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slow_log | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | | User |+---------------------------+ -Rowsinch Set(0.00sec) The MySQL database files are stored in/usr/lcoal/mysql/data directory, each database corresponds to a subdirectory that is used to store the data table file. Each data table corresponds to three files, and the suffix names are". frm"、". MyD"And". Myi"。

• View the structure of a table

Describe statement: Used to display the structure of a table, which is the information that makes up a table's fields (columns).

Mysql>Use mysql;database changedmysql>describe user;+------------------------+-----------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+---------+-------+| Host |Char( -)                          | NO |         PRI |       | || User |Char( -)                          | NO |         PRI |       | || Password |Char( A)                          |     NO |         |       | || Select_priv |enum('N','Y')                     |     NO | |       N | || Insert_priv |enum('N','Y')                     |     NO | |       N | || Update_priv |enum('N','Y')                     |     NO | |       N | || Delete_priv |enum('N','Y')                     |     NO | |       N | || Create_priv |enum('N','Y')                     |     NO | |       N | || Drop_priv |enum('N','Y')                     |     NO | |       N | || Reload_priv |enum('N','Y')                     |     NO | |       N | || Shutdown_priv |enum('N','Y')                     |     NO | |       N | || Process_priv |enum('N','Y')                     |     NO | |       N | || File_priv |enum('N','Y')                     |     NO | |       N | || Grant_priv |enum('N','Y')                     |     NO | |       N | || References_priv |enum('N','Y')                     |     NO | |       N | || Index_priv |enum('N','Y')                     |     NO | |       N | || Alter_priv |enum('N','Y')                     |     NO | |       N | || Show_db_priv |enum('N','Y')                     |     NO | |       N | || Super_priv |enum('N','Y')                     |     NO | |       N | || Create_tmp_table_priv |enum('N','Y')                     |     NO | |       N | || Lock_tables_priv |enum('N','Y')                     |     NO | |       N | || Execute_priv |enum('N','Y')                     |     NO | |       N | || Repl_slave_priv |enum('N','Y')                     |     NO | |       N | || Repl_client_priv |enum('N','Y')                     |     NO | |       N | || Create_view_priv |enum('N','Y')                     |     NO | |       N | || Show_view_priv |enum('N','Y')                     |     NO | |       N | || Create_routine_priv |enum('N','Y')                     |     NO | |       N | || Alter_routine_priv |enum('N','Y')                     |     NO | |       N | || Create_user_priv |enum('N','Y')                     |     NO | |       N | || Event_priv |enum('N','Y')                     |     NO | |       N | || Trigger_priv |enum('N','Y')                     |     NO | |       N | || Create_tablespace_priv |enum('N','Y')                     |     NO | |       N | || Ssl_type |enum("',' any','X509','SPECIFIED') |     NO |         |       | || Ssl_cipher | Blob |     NO | |       NULL | || X509_issuer | Blob |     NO | |       NULL | || X509_subject | Blob |     NO | |       NULL | || max_questions |int( One) unsigned |     NO | |0| || Max_updates |int( One) unsigned |     NO | |0| || max_connections |int( One) unsigned |     NO | |0| || max_user_connections |int( One) unsigned |     NO | |0| || Plugin |Char( -)                          |     YES |         |       | || authentication_string | Text |     YES | |       NULL | |+------------------------+-----------------------------------+------+-----+---------+-------+ theRowsinch Set(0.00Sec

• Create a new library

CREATE DATABASE statement: For creating a new library, you need to specify the name of the DB as the parameter.

mysql>1 row affected (0.00 sec)
The database you just created is empty and contains no tables, and an empty folder with the new library name is automatically generated under the/usr/lcoal/mysql/data directory

• Create a new table

CREATE TABLE statement: Used to wear a new table in the current library, specify the database table name as the parameter, and define the fields used by the table

mysql> use auth;database changedmysqlchar(+ nullchar default "'  0 rows affected (0.11 sec)

• DROP TABLE statement: Used to delete a table in a library, you need to specify "library name. Table name" as the parameter, if you specify only the parameters, you need to first switch to the target library by executing a "use" statement.

mysql>0 rows affected (0.00 sec)

• Delete a database

Drop DATABASE statement: To delete the specified library, you need to specify the library name as the parameter.

mysql>0 rows affected (0.00 sec)

MySQL Basic operation