MYSQL command summary

2011-01-24  来源:本站原创  分类:Database  人气:114 

First, connect MYSQL.
Format: mysql-h host address-u username-p password
1, connected to the machine on MYSQL.
First, open a DOS window, then enter the directory mysql \ bin, then type the command mysql-u root-p, enter the password after the prompt you lose Note before the user name can have spaces and can be no space, but there is no space before the password Otherwise, you re-enter the password.
If you have just installed MYSQL, there is no super-user root password, so you can directly enter into in the MYSQL, MYSQL prompt is: mysql>
2, connected to a remote host on MYSQL. Assuming the remote host's IP is: 110.110.110.110, user name is root, password is abcd123. Type the following command:
mysql-h110.110.110.110-u root-p 123; (Note: u and can not have spaces between the root, the other is the same)
3, exit MYSQL command: exit (Enter)
Second, change your password.
Format: mysqladmin-u username-p password old password new password
1, to add a root password ab12. First, enter the directory in DOS mysql \ bin, and then type the following command
mysqladmin-u root-password ab12
Note: Since the beginning of root without a password, so-p old password one can be omitted.
2, then the root password to djg345.
mysqladmin-u root-p ab12 password djg345
Third, the addition of new user.
(Note: the above is different because it is MYSQL following command in the environment, so the back with a semicolon as a command terminator)
Format: grant select on database .* to username @ log host identified by "password"
1, add a password for user test1 abc, so that he can log on any host, and all databases have query, insert, update, delete permissions. First of all connected with the root user MYSQL, and then type the following command:
grant select, insert, update, delete on *.* to [email = test1 @ "%] test1"%[/ email]" Identified by "abc";
However, the increase in users is very dangerous, you want to know if someone test1 password, then he can on the internet on any computer to access your mysql database and your data can do whatever they want, and the solution, see 2 .
2, add a user test2 password to abc, so that he can only log on localhost, and can query the database mydb, insert, update, delete operations (localhost means the local host, that is where the MYSQL database that host),
This user test2 password that is used to know, he can not access the database directly from the internet, only through the MYSQL host web pages visited.
grant select, insert, update, delete on mydb .* to [email = test2 @ localhost] test2 @ localhost [/ email] identified by "abc";
If you do not want test2 password, you can resort to a command to eliminate the password.
grant select, insert, update, delete on mydb .* to [email = test2 @ localhost] test2 @ localhost [/ email] identified by "";
I was next in the MYSQL database in operation. Note: You must first log into MYSQL, the following operations are carried out in the MYSQL prompt, and each command with a semicolon.
First, the operating skills
1, if you hit command, enter a semicolon after the discovery forget, you need not re-order to play again, as long as a semicolon enter it.
That means you can put into a complete command lines to play, finished with a semicolon marks the end of it OK.
2, you can use the cursor up and down keys to recall the previous command.
Second, the display command
1, shows the current database server database list:
mysql> SHOW DATABASES;
Note: mysql MYSQL database system information there, we change the password and add users actually use this library to operate.
2, display data in the database table:
mysql> USE database name;
mysql> SHOW TABLES;
3 shows the structure of the data sheet:
mysql> DESCRIBE table;
4, establish a database:
mysql> CREATE DATABASE database name;
5, a data table:
mysql> USE database name;
mysql> CREATE TABLE table name (field name VARCHAR (20), field name CHAR (1));
6, delete the database:
mysql> DROP DATABASE database name;
7, delete the data sheet:
mysql> DROP TABLE table;
8, the empty record in the table:
mysql> DELETE FROM table;
9, show records in the table:
mysql> SELECT * FROM table;
10, to insert a record into the table:
mysql> INSERT INTO table VALUES ("hyq", "M");
11, update data in the table:
mysql-> UPDATE table SET field name 1 = 'a', field name 2 = 'b' WHERE field name 3 = 'c';
12, with the text data into the data sheet:
mysql> LOAD DATA LOCAL INFILE "D: / mysql.txt" INTO TABLE table;
13 import. Sql files command:
mysql> USE database;
mysql> SOURCE d: / mysql.sql;
14, the command line to modify the root password:
mysql> UPDATE mysql.user SET password = PASSWORD ('new password') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
15, showing use of the database name:
mysql> SELECT DATABASE ();
16, displays the current user:
mysql> SELECT USER ();
Third, building a database and build a table and insert an instance of the data
drop database if exists school; / / delete if SCHOOL
create database school; / / build libraries SCHOOL
use school; / / open the library SCHOOL
create table teacher / / build table TEACHER
(
id int (3) auto_increment not null primary key,
name char (10) not null,
address varchar (50) default 'Shenzhen',
year date
); / / Build the table end
/ / The following is the Insert Field
insert into teacher values ​​(", 'allen', 'Dalian one', '1976 -10-10 ');
insert into teacher values ​​(", 'jack', 'Dalian II', '1975 -12-23 ');
If you are in the mysql prompt, type the above command can be, but is not convenient debugging.
(1) the above command you can write a text file, as is assumed to school.sql, then copy it to c: \ \, and within the state to enter the DOS directory \ \ mysql \ \ bin, and then type the following command:
mysql-uroot-p password <c: \ \ school.sql
If successful, empty out the line without any display; any error, will be prompt. (The above command has to debug, as long as you / / uncomment to use).
(2), or enter the command line using mysql> source c: \ \ school.sql; can also school.sql file into the database.
Fourth, the text data to the database
1, textual data should be consistent with the format: use the tab key between fields separated data, null values ​​with \ \ n instead. Example:
3 rose Dalian II 1976-10-10
4 mike Dalian one 1975-12-23
Suppose you put two sets of data saved as school.txt file in the c root directory.
2, data incoming command load data local infile "c: \ \ school.txt" into table table name;
Note: You'd better copy the file to \ \ mysql \ \ bin directory, and hit the table first with the use command where the library.
Fifth, the backup database: (command in DOS \ \ mysql \ \ bin directory, execute)
1 Export to export the entire database file is there by default mysql \ bin directory
mysqldump-u username-p database name> Export file name
mysqldump-u user_name-p123456 database_name> outfile_name.sql
2 Export a table
mysqldump-u username-p database name watches> Export file name
mysqldump-u user_name-p database_name table_name> outfile_name.sql
3. Exporting a database structure
mysqldump-u user_name-p-d-add-drop-table database_name> outfile_name.sql
-D there is no data-add-drop-table before each create statement to add a drop table
4 with parameters derived language
mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name.sql

Data import and export

Command line to export the database:
1, enter the MySQL directory to the bin folder: cd MySQL bin folder in the directory as I entered the command line: cd C: \ Program Files \ MySQL \ MySQL Server 4.1 \ bin
2, export the database: mysqldump-u username-p database name> Export file name as I enter the command line: mysqldump-u root-p news> news.sql (entered into the MySQL will let you enter the password)
(If you export a single table, then enter the database name and table name to the back)
3, will see the automatically generated file news.sql next to the bin file

Command line to import the database:
1, will be imported. Sql files to bin files, such a path is more convenient
2, with the export of Step 1 above
3, into MySQL: mysql-u username-p
As I entered the command line: mysql-u root-p (input also will let you enter ySQL after the password)
4, MySQL-Front you want to build the new database, then an empty database, called the news as a new target database (mysql> create database news;)
5, enter: mysql> use the target database name as I enter the command line: mysql> use news;
6, import the file: mysql> source into the file name;
As I entered the command line: mysql> source news.sql;

This article comes from CSDN blog, reproduced, please indicate the source: http://blog.csdn.net/cbynietg/archive/2009/06/06/4246779.aspx

相关文章
  • MYSQL command summary 2011-01-24

    First, connect MYSQL. Format: mysql-h host address-u username-p password 1, connected to the machine on MYSQL. First, open a DOS window, then enter the directory mysql \ bin, then type the command mysql-u root-p, enter the password after the prompt y

  • MySql common command summary 2010-06-18

    MySQL basic operation of common operations, the following are MySQL5.0 tested by first description, remember the end of each command with; (semicolon) 1. Export the entire database mysqldump-u username-p - default-character-set = latin1 database name

  • MySQL command line frequently used commands 2010-12-21

    Mysql commonly used commands show databases; display database create database name; create a database use databasename; Select database drop database name directly delete the database, do not remind the show tables; indicator describe tablename; show

  • Linux (Ubuntu, CentOs) Common Command Summary 2011-02-15

    Linux (Ubuntu, CentOs) Common Command Summary To help other novice like me can get started quickly, not "command" the fear, order the following special commands used for inspection: (The following experience mainly from Ubuntu, Red Hat and CentO

  • Mysql command set used 2011-01-22

    Mysql commonly used commands show databases; display database create database name; create a database use databasename; select the database drop database name directly delete the database, do not remind show tables; display table describe tablename;

  • mount Command Summary 2010-03-29

    mount Command Summary After reading this article, I assume you have Linux systems on the hard disk, CD-ROM device commands understand the rules, such as the relationship between sda and sda1, and hda, sda, fd, cdrom and other equipment. === I plug in

  • How to change mysql command prompt information under 2010-04-06

    We often encounter this problem you also for your MySQL command mode, the previous information or tips: MySQL>, then how are we going to change under the mysql command prompt information? So what the current selected database, what the user is curren

  • How to change the mysql command prompt information under 2010-04-06

    We often encounter this problem you also for your MySQL command mode, the previous information or tips: MySQL>, then how are we going to change under the mysql command prompt information? So what the current selected database, what the user is curren

  • MySQL command Collection (change) 2010-04-13

    First, connect MYSQL. Format: mysql-h host address-u username-p password 1, connected to the machine on MYSQL. First, open the DOS window, then enter the directory mysql \ bin, then type the command mysql-u root-p, a carriage return after the prompt

  • mysql command Collection 2010-05-07

    Test environment: mysql 5.0.45 [Note: in mysql through mysql> SELECT VERSION (); to see the database version] Order: leo First, connect MYSQL. Format: mysql-h host address-u username-p password 1, connected to the machine on MYSQL. First, open the DO

  • mysql command under linux 2010-05-24

    mysql command under linux (2010-04-14 18:09:36) reproduced Tags: topics for one, to summarize: 1.linux start the mysql command: mysqladmin start / Ect / init.d / mysql start (in front of the installation path for the mysql) 2.linux next restart mysql

  • MYSQL command Daquan 2010-05-24

    Common MySQL command Daquan First, connect MySQL Format: mysql-h host address-u username-p password 1, Example 1: Connect to the machine on MYSQL. First, open the DOS window, then enter the directory mysqlbin, then type the command mysql-uroot-p, a c

  • DB2 file import and export common command summary 2010-05-27

    DB2 file import and export common command summary DB2 data migration, the most commonly used is the import and export functions, and import and export command seems very simple, but in fact contains mystery, ever-changing, too easy, then full of mist

  • Journey to the mysql command used MySql 2010-05-30

    First, connect MYSQL. Format: mysql-h host address-u username-p password 1, Example 1: Connect to the machine on MYSQL. First, open the DOS window, then enter the directory mysqlbin, then type the command mysql-uroot-p, a carriage return after the pr

  • mysql command line frequently used commands (rpm) 2010-07-30

    The first one, mysql service start and stop net stop mysql net start mysql The second measure, landing mysql Syntax is as follows: mysql-u username-p password type the command mysql-uroot-p, Enter your password after the prompt, enter 12345, then pre

  • MySQL command line to import. Exported. Sql files 2010-08-17

    MySQL import and export. Sql file, follow these steps: 1. MySQL command-line mode settings: Desktop -> My Computer -> Properties -> Environment Variables -> New -> PATH = "; path \ mysql \ bin;" one path for the MySQL installation

  • mysql command (turn) 2010-08-30

    1. Installation and configuration of MYSQL 2. Common mysql command-line command 1. Mysql start and stop start the MYSQL service net start mysql Stop service net stop mysql MYSQL 2. Netstat-na | findstr 3306 view was listening port, findstr to find th

  • MySQL command to export import data 2010-09-18

    Keywords: mysql command to export import data 1. Export the entire database mysqldump-u username-p database name> exported file name mysqldump-u wcnc-p smgp_apps_wcnc> wcnc.sql 2. Exporting a table mysqldump-u username-p database watches were> ex

  • MySQL command Daquan Classic Edition (switch) 2010-09-23

    The following article introduces the MySQL command Daquan, including the MySQL database of commonly used commands, modify the MySQL database root password and to grant the actual practical application of the introduction, the following is the descrip

  • linux server and configured to start the MYSQL command from the Start 2010-09-28

    linux server and configured to start the MYSQL command from the Start Keywords: self-starting configuration mysql / Usr / local / mysql / bin / mysqld_safe - user = mysql & Note MYSQL installation directory Configuration from the start By vi / etc /