SQL Study Notes (17) ---------- MySQL Command, mysql import SQL FILE command

SQL Study Notes (17) ---------- MySQL Command, mysql import SQL FILE command

Mysql Common commands

 

Mysql installation directory
Database directory
/Var/lib/mysql/
Configuration File
/Usr/share/mysql (mysql. server command and configuration file)
Related commands
/Usr/bin (commands such as mysqladmin mysqldump)
Start script
/Etc/init. d/mysql (directory for starting the script file mysql)

System Management
Connect to MySQL
Format: mysql-h host address-u user name-p User Password
Example 1: connect to MySQL on the local machine.
Hadoop @ ubuntu :~ $ Mysql-uroot-pmysql;

Example 2: connect to MYSQL on the remote host.
Hadoop @ ubuntu :~ $ Mysql-h 127.0.0.1-uroot-pmysql;

Change New Password
Enter mysql-u username-p password on the terminal, and press enter to enter Mysql.
> Use mysql;
> Update user set password = PASSWORD ('new password') where user = 'username ';
> Flush privileges; # update Permissions
> Quit; # exit

Add new users
Format: grant select on database. * to username @ login host identified by 'Password'
Example:
Example 1: Add a user test1 whose password is abc so that he can log on to any host and
Query, insert, modify, and delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Mysql> grant select, insert, update, delete on *. * to root @ localhost identified by 'mysql ';
Or
Grant all privileges on *. * to root @ localhost identified by 'mysql ';
Then refresh the permission settings.
Flush privileges;

Example 2: If you do not want the root user to have a password to operate the data table in the Database "mydb", you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to root @ localhost identified '';

Delete a user
Hadoop @ ubuntu :~ $ Mysql-u username-p Password
Mysql> delete from user where user = 'username' and host = 'localhost ';
Mysql> flush privileges;
// Delete the user's database
Mysql> drop database dbname;

Database Operations
Show all databases
Mysql> show databases; (Note: There is a last s)

Create a database
Mysql> create database test;

Connect to database
Mysql> use test;

View the currently used database
Mysql> select database ();

Table information contained in the current database
Mysql> show tables; (Note: There is a last s)

Delete Database
Mysql> drop database test;

Table operations
Note: before the operation, use "use <Database Name>" to connect to a database.
Create a table
Command: create table <table Name> (<field name 1> <type 1> [,... <field name n> <type n>]);
Example:
Mysql> create table MyClass (
> Id int (4) not null primary key auto_increment,
> Name char (20) not null,
> Sex int (4) not null default '0 ',
> Degree double (16, 2 ));

Get table structure
Command: desc table name or show columns from Table Name
Example:
Mysql> describe MyClass
Mysql> desc MyClass;
Mysql> show columns from MyClass;

Delete table
Command: drop table <table Name>
For example, delete a table named MyClass.
Mysql> drop table MyClass;

Insert data
Command: insert into <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]
Example:
Mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );

Query table data
Query all rows
Mysql> select * from MyClass;

Query the first few rows of data
For example, view the first two rows of data in the MyClass table.
Mysql> select * from MyClass order by id limit 0, 2;
Or
Mysql> select * from MyClass limit 0, 2;

Delete table data
Command: delete from table name where expression
For example, delete the record numbered 1 in MyClass.
Mysql> delete from MyClass where id = 1;

Modify Table Data
Command: update table name set field = new value,... where Condition
Mysql> update MyClass set name = 'Mary 'where id = 1;

Add fields to the table
Command: alter table name, add, other field types;
For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.
Mysql> alter table MyClass add passtest int (4) default '0'

Change table name
Command: rename table original table name to new table name;
For example, the MyClass name in the table is changed to YouClass.
Mysql> rename table MyClass to YouClass;

Update field content
Command: update table name set field name = new content
Update table name set field name = replace (field name, 'old content', 'new content ');
For example, four spaces are added before the article.
Update article set content = concat ('', content );

Import and Export Databases
Export Database files from a database
Use the "mysqldump" command
First, go to the DOS interface and perform the following operations.
1) Export all databases
Format: mysqldump-u [Database User Name]-p-A> [backup file storage path]

2) export data and data structure
Format: mysqldump-u [Database User Name]-p [name of the database to be backed up]> [storage path of the backup file]
Example:
Example 1: export the database mydb to the e: \ MySQL \ mydb. SQL file.
Open start> RUN> Enter cmd to Enter command line mode.
C: \> mysqldump-h localhost-u root-p mydb> e: \ MySQL \ mydb. SQL
Enter the password and wait for a moment until the export is successful. You can check whether the export is successful in the target file.

Example 2: Export mytable in mydb to the e: \ MySQL \ mytable. SQL file.
C: \> mysqldump-h localhost-u root-p mydb mytable> e: \ MySQL \ mytable. SQL

Example 3: export the database mydb structure to the e: \ MySQL \ mydb_stru. SQL file.
C: \> mysqldump-h localhost-u root-p mydb -- add-drop-table> e: \ MySQL \ mydb_stru. SQL
Note:-h localhost can be omitted, which is generally used on a VM.

3) only export data without exporting the Data Structure
Format:
Mysqldump-u [Database User Name]-p-t [name of the database to be backed up]> [storage path of the backup file]

4) export the Events in the database
Format: mysqldump-u [Database User Name]-p-E [Database User Name]> [backup file storage path]

5) Export stored procedures and functions in the database
Format: mysqldump-u [Database User Name]-p-R [Database User Name]> [backup file storage path]

Importing data from external files to the database
1) use the "source" command
First, go to the "mysql" command console, create a database, and then use the database. Finally, perform the following operations.
Mysql> source [backup file storage path]

2) use the "<" symbol
First, go to the "mysql" command console, create a database, and then exit MySQL to enter the DOS interface. Finally, perform the following operations.
Mysql-u root-p <[backup file storage path]

 

 

 

 

 

 

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 name;
Drop 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 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.

 

FILE: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
SHUTDOWN: Shut down the MySQL service.
Database/data table/data column permissions:
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: CREATE a new database or data table.
DELETE: DELETE table records.
DROP: delete a data table or database.
INDEX: Create or delete an INDEX.
INSERT: Add Table records.
SELECT: displays/searches for table records.
UPDATE: Modify existing records in the table.
Special permissions:
ALL: allow anything (same as root ).
USAGE: Only Logon Allowed-nothing else is allowed.

 

 

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 program developed by PHP to solve the problem of using PHP for large database backup and recovery. hundreds of megabytes of databases can be easily backed up and restored, you don't have to worry about the problem that the network speed is too slow, resulting in intermediate interruption, which is very convenient and easy to use. 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 comparison tools for database, source code management and file systems; 3) Subversion (SVN) A complete integrated source code management client is designed with CVS. 4) A database modeling tool (modeler) is provided, which is as powerful as the best independent database Chart Tool.

Iv. SQLyog

SQLyog is a comprehensive MySQL database management tool (/'GU'/'frontend '). Its Community 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.

6. 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.

9. 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 Tool) V1.8.3 Green 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.

 

 

References:

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

Http://www.cnblogs.com/linjiqin/archive/2013/03/01/2939384.html