MySQL common command line

MySQL common command line

MySQL often uses the number command line to get started and stop MySQL
Net stop MySQL
Net start MySQL
Second, login to MySQL
Syntax: mysql-u user name-P User Password
Enter mysql-uroot-P, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The MySQL prompt is:
Mysql>
Note: if it is connected to another server, you need to intervene in a parameter-H Server IP address.

Mysql> mysql-u root-p-h 192.168.0.1
Third, add new users
Format: grant permission on database. * To username @ login host identified by "password"
For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission to query, insert, modify, and delete all databases. Use the root user to connect to MySQL, and then enter the following command:
Grant select, insert, update, delete on *. * To user1 @ localhost identified by "password1 ";
Change localhost to "%" if you want the user to log on to MySQL on any server ".

Grant select, insert, update, delete on *. * To user1 @ '%' identified by "password1 ";
If you do not want user1.
Grant select, insert, update, delete on mydb. * To user1 @ localhost identified "";
Step 4: operate databases
Log on to MySQL, and then run the order listed below at the MySQL prompt. Each order ends with a semicolon.
1. display the Database List.
Show databases;
By default, two databases are available: MySQL and test. The MySQL database stores the MySQL system and user permission information. We change the password and add users. In reality, we operate on this database.
2. display the data tables in the database:
Use MySQL;
Show tables;
3. display the data table structure:
Describe table name;
4. Create and delete databases:
Create Database database name;
Drop database database name;
5. Create a table:
Use Database Name;
Create Table Name (Field List );
Drop table name;
6. Clear the table records:
Delete from table name;
7. display the records in the table:
Select * from table name;
Step 5: 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
For example, mysqldump-u root-p123456 -- databases dbname> MySQL. dbname
Export the database dbname to the mysql. dbname file.
2. import data:
Mysqlimport-u root-p123456 <MySQL. dbname.
No need to explain it.
3. Import text data to the database:
Field data of text data is separated by the tab key.
Use test;
Load data local infile "file name" into Table table name;
1: Apply the show statement to find out which sub-database exists on the server:
Mysql> show dbtbbbses;
. Create a database mysqldbtb
Mysql> drebte dbtbbbse mysqldbtb;
3: select the database you created
Mysql> Use mysqldbtb !)
4: Check what table exists in the database at the moment
Mysql> show tbbles;
5. Create a database table
Mysql> drebte tbble mytbble (name vbrdhbr (20), sex dhbr (1 ));
6: display the table structure:
Mysql> desdribe mytbble; 7: Add records to the table
Mysql> insert into mytbble values ("hyq", "M ");
8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> lobd dbtb lodbl infile "D:/mysql.txt" into tbble mytbble;
9: import the. SQL file number order (for example, D:/MySQL. SQL)
Mysql> use database;
Mysql> source D:/MySQL. SQL;
10: delete a table
Mysql> drop tbble mytbble;
11: Clear the table
Mysql> Delete from mytbble;
12: Update table data
Mysql> Update mytbble set sex = "F" where name = 'hyq ';
Posted on happytian read (6) Comments (0) prepared for preservation to 365key
13. Back up the database
Mysqldump-u root Database Name> XXX. Data
14: Example 2: connect to MySQL on the remote host
Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following order:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: you do not need to add spaces for u and root. The same applies to others)
3. log out of MySQL: exit (Press ENTER)
User permission Control
For MySQL 3.22.34, there are four "Y" in total. The corresponding permissions are as follows (listed in the field order ):
Permission table column name explanation Scope
Select select_priv the select permission table is required only when a table is actually retrieved.
Insert insert_priv allows you to Insert a new row into an existing table.
Update update_priv allows you to use the new value to update the columns in the row of an existing table.
Delete delete_priv allows you to delete rows and tables with full condition
Create create_priv allows you to create new databases and table databases, tables, or indexes.
Drop drop_priv discard (delete) existing database and table database or table
Reload reload_priv allows you to tell the server to read the delegated table server for management.
Shutdown shutdown_priv may be abused (by terminating the server and rejecting other users for work) Server Management
Process process_priv allows you to view the common text of the currently executed query, including setting or changing the password for query Server Management
File file_priv permission can be abused to read any readable files on the server and access the files on the database table server.
Grant grant_priv allows you to grant your own permissions to other user databases or tables.
References references_priv allows you to open and close the record file database or table
Index index_priv allows you to create or discard (delete) index tables.
Alter alter_priv allows you to change the table. It can be used to rename the table to overturn the permission system table.

Five frequently-used MySQL graphical management tools

1,PhpMyAdmin(Http://www.phpmyadmin.net /)

PhpMyAdmin is the most common MySQL maintenance tool. It is a MySQL management tool developed using PHP Based on the Web architecture on the website host. It supports Chinese characters and is very convenient to manage databases. It is inconvenient to back up and restore large databases.

2,MysqldumperHttp://www.mysqldumper.de/en)

Mysqldumper uses the MySQL database backup and recovery developed by PHPProgramIt solves the problem of using PHP to back up and recover large databases. hundreds of megabytes of databases can be easily backed up and restored without worrying about intermediate interruptions caused by slow network speeds. The software was developed by Germans and has no Chinese language packs.

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

Navicat is a desktop MySQL database management and development tool. Similar to Microsoft sqlserver manager, it is easy to learn and use. Navicat uses a graphical user interface to make it easier for users to use and manage. Chinese characters are supported, and free versions are available.

4,MySQL GUI toolsHttp://dev.mysql.com/downloads/gui-tools)

MySQL GUI tools is a graphical management tool officially provided by MySQL. It has powerful functions and is recommended. Unfortunately, there is no Chinese interface.

5,MySQL ODBC ConnectorHttp://dev.mysql.com/downloads/connector/odbc)

The ODBC interface program officially provided by MySQL. After the system installs this program, it can access MySQL through ODBC, so that data conversion between sqlserver, access and MySQL can be realized, you can also access the MySQL database through ASP.

 

10 visual 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 can shorten the development time. This is why we have compiled these 10 MySQL tools to simplify the development process.

I,MySQL workbench

MySQL workbench is a cross-platform and visualized database tool developed by MySQL. As an alternative application of the dbdesigner4 project, it is highly noticed. MySQL workbench can be used as the original GUI tool on Windows, Linux, and OS X systems. It has different versions. You can view the following link to obtain its version information.

II,PhpMyAdmin

PhpMyAdmin is a free PHP tool used to manage MySQL on the World Wide Web. It supports most of MySQL functions. This software with user interfaces can support some of the most common operations (such as database management, tables, fields, contacts, indexing, users, licenses, and so on ), you can also directly execute any SQL statement.

It has the following features:

> Intuitive Web Interface

> Supports most MySQL functions:

> 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, and provide suggestions on server configuration.

> Execute, edit, and annotate any SQL statements, or even Perform Batch queries.

> Manage mysql users and User Permissions

> Stored procedures and triggers)

> Import data from CSV and SQL file formats

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

> Manage multiple servers

> Create a PDF chart for the database layout

> Use query-by-example (QBE) to create complex queries

> Perform a global search in the database or search in the database subset.

> Use predefined functions to convert stored data into any format

> More features...

III,Aqua data Studio

For database administrators, software developers, and business analysts, Aqua data studio is a complete integrated development environment (IDE ). It has four main functions: 1) database query and management tools; 2) a set of databases,Source codeManagement and file system comparison tools; 3) designed a complete integration source for subversion (SVN) and CVSCodeManagement client; 4) provides a database modeling tool (modeler), which is as powerful as the best standalone database Chart Tool.

IV,Sqlyog

Sqlyog is a comprehensive MySQL database management tool (/'GU'/'frontend '). ItsCommunityCommunity edition is a free open-source software with a GPL license. This tool contains the vast majority of functions required by developers when using MySQL: query result set, query analyzer, Server Message, table data, table information, and query history, they are all displayed in the form of labels on the interface, developers just need to click the mouse. In addition, it allows you to easily create views and stored procedures. In recent weeks, I have been using this function repeatedly.

V,MySQL front

The graphical 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 it does not cause latency due to heavy loading of HTML webpages, its response is instant. If the supplier permits it, You can allow mysql-front to work directly with the database. If not, you only need to install a small script on the publishing website. The logon information is stored on your hard disk, so you do not have to log on to different network interfaces.

VI,Mytop

Mytop is a console-based tool (not GUI) used to monitor threads and the overall performance of MySQL 3.22.x, 3.23.x, and 4.x servers. It can run on most Unix systems with Perl, DBI, and term: readkey installed (including Mac OS X. If you have installed term: ansicolor, you can get a color view. If you have installed time: hires, you can get a good real-time statistics on the number of queries per second. Mytop0.7 can even run on Windows.

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

VII,Sequel pro

Sequel pro is an application for managing Mac OSX databases. It allows you to directly access MySQL Databases on local and remote servers, you can also import and export data from popular file formats, including SQL, CSV, XML, and other files. At first, sequel Pro was just a branch of the open-source cocoamysql project. Some features are as follows:

> You can easily establish a connection to the local MySQL server on the Mac computer.

> It has all table management functions, including indexes.

> MySQL view supported

> It uses the multi-window function to support multiple databases or tables immediately

8,SQL buddy

SQL buddy is a powerful lightweight Ajax database management tool. It is very easy to install. You only need to extract the folder to the server, which is no more simple! You can also perform most common operations.

IX,MySQL sidu

MySQL sidu is a free MYSQL client that runs through a Web browser and is very easy to use! The sidu letters indicate select, insert, delete, and update ). Sidu actually has more features. It looks more like the GUI of the MySQL front-end software than the web page.

> Sidu supports SQL selection, insertion, deletion, and update.

> Sidu supports working on browsers, such as Firefox, ie, opera, Safari, and chrome.

> Sidu looks like the GUI of the MySQL front-end software rather than the webpage.

> Sidu can work with MySQL, Postgres, and SQLite DBS.

10,Navicat lite MySQL Admin Tool

Navicat is a fast and reliable database management tool, which is very popular. Navicat is designed to simplify database management and reduce management costs. It is designed to meet the needs of database administrators, database developers, and SMEs. It has a very intuitive GUI, this allows you to create, organize, access, and share information securely and conveniently.

For MySQL, navicat 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 functions, including trigger, stored procedure, function, and event, view and manage user. Navicat lite can be downloaded for free, but only for non-commercial activities.

Sqltoolbox (Database Management Tools) V1.8.3Green Edition

Sqltoolbox is a pure green free database client software developed based on Java swing. It is designed to provide developers, System Engineers, and database administrators with a common and convenient database operation tool, it frees them from the need to learn how to use a variety of database clients, and reduces their daily operations on the database and the amount of tasks for writing SQL statements, helping them put their energy into solving more meaningful problems.

Existing functions of sqltoolbox
1. Connect to MySQL, Oracle, and ms SQL Server databases.
2. After connecting to the database, a tree view of the database schema and table is provided for users to browse and search. In addition, a filter is provided to help users narrow the search scope.
3. users can automatically and quickly obtain the statements for creating, querying, updating, deleting, and creating a single table, and inserting all data into the entire table, A single table corresponds to common texts such as pojo classes and hibernate ing files of a single table, and you can use this to construct more complex SQL statements.
4. the SQL statement can be executed and the execution result is displayed. If the query statement is used, the results are displayed in tables and CSV data is downloaded; if it is a non-query statement or an incorrect query statement, the user is notified in text format.
5. You can highlight the SQL syntax when entering SQL statements to help you identify SQL statements.
6. SQL formatting is provided to help identify and organize SQL statements.
7. provides common text editing functions such as redo/undo, shift overall unlattice into cells, Case conversion, SQL statement inclusion using stringbuilder, and capital representation of keywords in SQL statements. These can help programmers write SQL statements in programs.
8. Save and remember the database information so that it can be opened next time.