MySQL Common commands

Ext.: http://www.cnblogs.com/TsengYuen/archive/2012/01/11/2319034.html

MySQL Common command line

MySQL often uses the command line Daquan, the start of MySQL work and stop
net stop MySQL
net start MySQL

This machine: mysql-u root-p Enter------> enter to login

Second trick, landing MySQL
The syntax is as follows: Mysql-u user name-p user Password
Type the command mysql-uroot-p, enter after entering the password, enter 12345, after the carriage return can enter into MySQL, MySQL prompt is:
Mysql>
Note that if you connect to another stiff, you need to meddle with a parametric-h IP

Mysql>mysql–u root–p–h 192.168.0.1
Third recruit, add new user
Format: Grant permissions on database. * To User name @ login host identified by "password"
For example, add a user user1 password for password1, so that it can log on to the computer, and all the databases have query, insert, modify, delete the permission. Begin with the root user to connect to MySQL, then type in the following command:
Grant Select,insert,update,delete on * * to [e-mail protected] identified by "Password1";
If you wish the user could have landed MySQL on any of the rigid, then change localhost to "%".

Grant Select,insert,update,delete on * * to [email protected] '% ' identified by ' password1 ';
If you don't want the User1 to have a password, you can take another call and remove the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
Four strokes: Manipulating the database
Log in to MySQL, and then run the command listed below at the MySQL prompt, with each command ending with a semicolon.
1. Display the list of databases.
show databases;
The default is two databases: MySQL and test. MySQL library is stored in the MySQL system and user rights information, we change the password and add users, the reality is to operate this library.
2. Display the data table in the library:
Use MySQL (own table name);
Show tables;
3, display the structure of the data table:
describe table name;
4. Build the library and delete the library:
Create database name;
drop database name;
5, build the table:
Use library name;
CREATE TABLE table name (field list);
drop table name;
Six, clear the table records:
Delete from table name;
Vii. display the records in the table:
SELECT * from table name;
Recruit, export, and import data
1. Export Data:
Mysqldump--opt Test > Mysql.test
Export the database test database to the Mysql.test file, which is a text file
such as: Mysqldump-u root-p123456--databases dbname > Mysql.dbname
is to export the database dbname to the file mysql.dbname.
2. Import data:
Mysqlimport-u root-p123456 < Mysql.dbname.
You don't have to explain.
3. Import text data into the database:
The field data of the text data is separated by the TAB key.
Use test;
Load data local infile "file name" into table name;
1: Apply the show statement to find out what database currently exists on the manager:
Mysql> SHOW dbtbbbses;
2:2. Create a database Mysqldbtb
mysql> drebte dbtbbbse MYSQLDBTB;
3: Select the database you created
mysql> use MYSQLDBTB; (press ENTER when the database changed declaration operation Music!)
4: See what tables exist in the database at this moment
Mysql> SHOW Tbbles;
5: Create a database table
Mysql> drebte tbble mytbble (name vbrdhbr), sex DHBR (1));
6: Show the structure of the table:
Mysql> Desdribe mytbble; 7: Meddling in the table record
mysql> INSERT INTO mytbble values ("HyQ", "M");
8: Loading data into a database table in text mode (for example, D:/mysql.txt)
mysql> lobd dbtb lodbl INFILE "D:/mysql.txt" into tbble mytbble;
9: Import. sql file command (e.g. D:/mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10: Delete the table
Mysql>drop tbble mytbble;
11: Clear the table
Mysql>delete from Mytbble;
12: Update data in table
Mysql>update mytbble set sex= "F" where name= ' HyQ ';
Posted on 2006-01-10 16:21 Happytian Read (6) Comments (0) compilation and preservation to 365Key
13: Backing Up the database
Mysqldump-u Root Library name >xxx.data
14: Example 2: Connecting to MySQL on a remote host
Assume that the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Then type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter)
User Rights control
For the 3.22.34 version of MySQL, there are 14 "Y", with the corresponding permissions (arranged in the order of the fields):
The Permission table column name explains the application scope accordingly
Select Select_priv requires a SELECT permission table only if it is actually retrieved from a table
Insert Insert_priv allows you to insert a new row into a table that exists in the table
Update Update_priv allows you to refresh the list of rows in an existing table with new values
Delete Delete_priv allows you to delete the Line table for satisfying conditions
Create Create_priv allows you to make new database and table databases, tables, or indexes
Drop Drop_priv Discard (delete) the existing database and table database or table
Reload Reload_priv allows you to tell the manager to re-read into the Power delegation table management
Shutdown Shutdown_priv may be abused (by terminating the service to deny service to other users) Manager
Process Process_priv allows you to view the normal text of the currently executing query, including setting or changing the password query Manager
File File_priv permissions can be abused to read any readable file on the service to the database table on the registry files access
Grant Grant_priv allows you to delegate those permissions you own to other user databases or tables
References References_priv allows you to open and close a record file database or table
Index INDEX_PRIV allows you to create or discard (delete) indexed tables
Alter ALTER_PRIV allows you to change the table, which can be used to push the permissions system table by renaming the table

Introduction to five frequently used MySQL graphical management tools

1 , phpMyAdmin(http://www.phpmyadmin.net/)

phpMyAdmin is the most commonly used MySQL maintenance tool, is a PHP development Web-based architecture on the site host MySQL management tools, support Chinese, management database is very convenient. The disadvantage is that it is inconvenient to backup and restore the large database.

2 , mysqldumper(http://www.mysqldumper.de/en/)

Mysqldumper use PHP developed MySQL database backup recovery program, solve the use of PHP for large database backup and recovery problems, the hundreds of trillion database can be easily backup recovery, do not worry about the slow speed caused by the middle of the problem, very easy to use. The software was developed by the Germans and there is no Chinese language pack.

3 , Navicat(http://www.navicat.com/)

Navicat is a desktop version of MySQL database management and development tools. Like Microsoft SQL Server Manager, it's easy to learn and use. Navicat uses a graphical user interface that makes it easier for users to use and manage. Support in Chinese, free version available.

4 , MySQL GUI Tools(http://dev.mysql.com/downloads/gui-tools/)

MySQL GUI Tools is the official MySQL provided by the graphical management tools, the function is very powerful, it is worth recommending, unfortunately there is no Chinese interface.

5 , MySQL ODBC Connector(http://dev.mysql.com/downloads/connector/odbc/)

MySQL is the official ODBC interface program, after the system installed this program, you can access MySQL through ODBC, so that you can achieve data conversion between SQL Server, Access and MySQL, but also support the ASP to access the MySQL database.

10 visual development MySQL management tools

Using a variety of well-designed tools to manage MySQL databases is much easier than simply using traditional methods. Developers should constantly look for tools that shorten development time. This is why we have compiled these 10 MySQL tools that simplify the development process.

First, MySQL Workbench

MySQL Workbench is a cross-platform, Visual Database tool developed by MySQL. It has been an attractive alternative to DBDESIGNER4 engineering. MySQL Workbench can be used as the original GUI tool on Windows, Linux and OS X systems, it has a variety of different versions, you can view the link below to get its version information.

Second, PhpMyAdmin

phpMyAdmin is a free, PHP-written tool for managing MySQL on the World Wide Web, which supports most of MySQL's features. This user-interface software supports some of the most common operations (such as managing databases, tables, fields, contacts, indexes, users, licenses, and so on), and you can execute any SQL statement directly.

It has the characteristics of:

> Intuitive Web Interface

> Supports most MySQL features:

> Browse and discard databases, tables, views, fields, and indexes

> Create, copy, delete, rename, and change databases, tables, fields, and indexes

> Maintain servers, databases, and tables to make recommendations for server configuration

> Execute, edit, and annotate any SQL statements or even bulk queries

> Manage MySQL users and user rights

> Managing stored procedures (stored procedures) and triggers (triggers)

> Import data from CSV and SQL file formats

> Ability to export data in multiple formats: CSV, SQL, XML, PDF, ISO/IEC 26300, etc.

> Managing multiple servers

> Create a PDF chart for your database layout

> Using Query-by-example (QBE) to create complex queries

> Global search in a database, or search in a subset of databases

> Convert stored data to any format with predefined functions

> There are other more features ...

Third, Aqua Data Studio

Aqua Data Studio is a complete integrated development environment (IDE) for database administrators, software developers, and business analysts. It is mainly equipped with four functions: 1) database query and management tools, 2) a set of database, source control and file system comparison tools; 3) design a complete integrated source control client for Subversion (SVN) and CVS ; 4) provides a database modeling tool (Modeler) that is as powerful as the best standalone database charting tool.

Four, SQLyog

SQLyog is a comprehensive MySQL database management tool (/' GUI '/' Frontend '). Its community edition (Community Edition) is a free open source software licensed under the GPL. This tool contains most of the features that developers need to use MySQL: query result sets, query analyzers, server messages, tabular data, tabular information, and query history, all in the form of labels that are displayed on the interface, with the developer simply clicking the mouse. In addition, it makes it easy to create views and stored procedures that I've been using repeatedly in recent weeks.

Five, MYSQL Front

The GUI of this MySQL database is a "real" application that provides a more precise user interface than a system built with PHP and HTML. Because the delay is not caused by overloading the HTML page, its response is instantaneous. If the vendor allows, you can have mysql-front work directly with the database. If not, you just have to install a small script on the publishing site. The login information is stored on your hard drive, so you no longer have to log on to a different web interface.

Six, Mytop

The Mytop is a console-based tool (not a GUI) that monitors the overall performance of threads and MySQL 3.22.x, 3.23.x, and 4.x servers. It can be run on most Unix systems that have Perl, DBI, and Term::readkey installed, including Mac system OS X. If you install Term::ansicolor, you can get a color view. If you install the Time::hires, you can also get a good "query per second" real-time statistics. Version mytop0.7 can even be run on Windows.

Mytop is inspired by the system monitoring Tool "top". I often use top on Linux, FreeBSD, and Solaris, and you'll probably notice some of the features from these operating systems in Mytop. After the mytop is connected to the MySQL server, you can periodically run the show processlist and show status commands and summarize the information obtained from these commands in a useful format.

Seven, Sequel Pro

Sequel Pro is an application that manages the Mac OSX database, which gives you direct access to the MySQL database on your local and remote servers, and enables you to import and export data from popular file formats, including SQL, CSV, and XML files. Initially, Sequel Pro was just a branch of the open source CocoaMySQL project. Some features are as follows:

> You can easily build a connection to your Mac computer's local MySQL server

> It has all of the table management features, including indexes.

> Support MySQL View

> It uses the Multi-window feature to enable immediate support for multiple databases or tables

Eight, SQL Buddy

SQL Buddy is a powerful lightweight AJAX database management tool. It's very easy to install, you just need to extract the folder to the server on the line, this is simple! You can also do most of the usual operations.

Nine, MySQL Sidu

MySQL Sidu is a free MySQL client, it runs through a Web browser, very easy to get started! Sidu These letters indicate select (select), insert (insert), delete (delete), and update (updates). Sidu actually has more features, and it looks more like the GUI of the MySQL front-end software than the Web page.

>sidu supports SQL selection, insertion, deletion, and update functionality.

>sidu supports working on browsers such as Firefox, IE, Opera, Safari, Chrome, and more.

>sidu looks like the GUI of the MySQL front-end software rather than the Web page.

>sidu can work with MySQL, Postgres, and SQLite dbs.

10. Navicat Lite MySQL Admin Tool

Navicat is a fast and reliable database management tool that is popular with everyone. Designed to simplify database management and reduce administrative costs, NAVICAT is intended to meet the needs of database administrators, database developers, and the vast majority of small and medium-sized enterprises, and it has a very intuitive GUI that allows you to create, organize, access and share information securely and conveniently.

For MySQL, the Navicat tool is a powerful database management and development tool. It works with any version of MySQL database server (version 3.21 or later) and supports most of the latest MySQL features, including Trigger, Stored Procedure, function, Event, view, and Manage User and so on. Navicat Lite can be downloaded for free, but only for non-commercial activities.

sqltoolbox ( database management tools ) V1.8.3 Green Edition

Sqltoolbox is a pure green, free database client software based on Java Swing, designed to provide developers, system engineers, and database administrators with a common, easy and fast database manipulation tool that will help them get rid of the frustration that they need to learn to master the use of multiple database clients. and reduce their daily operations database and write SQL statements of the task volume, help them to focus on solving more meaningful problems.

Sqltoolbox Existing Features
1. Capable of connecting to mysql,oracle and MS SQL Server three databases.
2. Once connected to the database, a tree view of the database schema and table is provided for the user to browse and locate, and a filter is provided to help the user narrow the search.
3. Users can automatically quickly get the creation of a single table, query, update, DELETE, build table statements, full table data INSERT statements, a single table corresponding to the Pojo class and a single table of hibernate mapping files and other common text, and can be used to construct more complex SQL statements.
4. Can execute the SQL statement and display the result of execution, if the query statement will display the results in tabular format, but also provide CSV form of data download, if the non-query statement or the wrong query statement will be text to inform the user.
5. Provide SQL syntax highlighting during user input of SQL statements to aid in the identification of SQL statements.
6. Provide SQL formatting capabilities to facilitate the identification and collation of SQL statements.
7. Provide redo/undo,shift overall fallback Gergenger, case conversion, SQL statements with StringBuilder containment, and the SQL statement in the keyword uppercase representation of common text editing functions. These can help programmers write SQL statements in the program.
8. Can save and memory database information, so that the next time to open.

MySQL Common commands