Mysql command line collection

View the character set mysqlshowcharacterset supported by the current mysql database; view the character set of the current database: mysqlshowvariableslike % char %; 1. Connect to MYSQL: mysql-h host address-u user name-p user password or: mysql-u username-p press enter and enter the password. The password is invisible. 1

View the character set mysql show character set supported by the current mysql database; view the character set of the current database: mysqlshow variables like '% char %'; 1. Connect to MYSQL: mysql-h host address-u user name-p user password or: mysql-u user name-p // press enter to enter the password, the password is not visible 1

View the character sets supported by the current mysql database
Mysql> show character set;

View the character set of the current database:

Mysql> show variables like '% char % ';

1. Connect to MYSQL

Format: mysql-h host address-u user name-p User Password

Or: mysql-u username-p // press enter and enter the password. The password is invisible.

1. Connect to MYSQL on the local machine.
First, open the DOS window, enter the mysql \ bin directory, type the mysql-u root-p command, and press enter to prompt you to enter the password. note that there can be spaces or spaces before the user name, but if-p is followed by the user password, there must be no space between-p and the password; otherwise, you will be asked to re-enter the password. for example, the following are valid logon attempts: (account: root Password: 123)
Mysql-u root-p
Mysql-uroot-p
Mysql-uroot-p123

If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>

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 command:
Mysql-h110.110.110.110-u root-p123)

Mysql-u admin-p123-h 192.168.1.130

3. exit MYSQL command: exit (Press ENTER)

2. Change the password.

Format: mysqladmin-u username-p old password New password

1. Add a password ab12 to the root user. First, enter the mysql \ bin directory under DOS, and then type the following command
Mysqladmin-u root-password ab12
Note: because the root account does not have a password at the beginning, the old-p password can be omitted.

2. Change the root password to djg345.
Mysqladmin-u root-p ab12 password djg345

(Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)

3. Change the root password through the command line:
Mysql> UPDATE mysql. user SET password = PASSWORD ('new password') WHERE User = 'root ';
Mysql> flush privileges;

4. display the current user:
Mysql> select user ();

3. Add new users.

Format: grant select on database. * to username @ login host identified by "password"

1. Add a user named "test1" with the password "abc" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect

MYSQL, and then type the following command:
Grant select, insert, update, delete on *. * to test1 "%" Identified by "abc ";
However, the added users are very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. See solution 2.

2. Add a user named "test2" with the password "abc" so that the user can only log on to localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host of the MYSQL database ),
In this way, the user knows the password of test2 and cannot directly access the database from the internet. He can only access the database through the web page on the MYSQL host.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identifiedby "abc ";
If you do not want test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "";

Iv. Operation Skills

1. If you forget the extra points after you press Enter when making the command, you don't have to repeat the command. You just need to press a semicolon to press Enter.
That is to say, you can divide a complete command into several lines, and then use a semicolon as the end sign to complete the operation.

2. You can use the cursor to bring up or down the previous commands.

V. Database Operations

1. display the list of databases on the current database server:
Mysql> show databases;
Note: the mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations.

2. display data tables in the database:
Mysql> USE Database Name;
Mysql> show tables;

3. Display database Definitions
Show create database yourDB;

4. display the Database Name of use:
Mysql> select database ();

5. Create a database:
Mysql> create database name;

6. delete a database:
Mysql> drop database name;

7. Import the. SQL FILE command:
Mysql> USE Database Name;
Mysql> SOURCE d:/mysql. SQL;

You can also enter the following command in the DOS environment for import:
Mysql-uroot-proot databasename <databasename. SQL
Note: before importing data, ensure that the database databasename must exist in mysql;

8. creation attempt: create view name select * from ....

Delete view: drop view name

View the created view: show create view name;

6. Back up the database:

The default character set utf8 parameter must be added to both new databases and new data tables in MySQL. This avoids the trouble of using tables in the future.

Note: The mysqldump command is executed in the DOS mysql \ bin directory and cannot be executed in the mysql environment. Therefore, it cannot end with a semicolon. If you have logged on to mysql, run the exit command mysql> exit

1. Export the entire database
The exported files are stored in the mysql \ bin directory by default.
Mysqldump-u username-p Database Name> exported file name
Mysqldump-u root-p123456 database_name> outfile_name. SQL

Mysqldump-h host-u user-ppasword -- default-character-set = latin1 -- skip-opt-B database

-- Tables tablename> old. SQL

The -- skip-opt parameter cannot be missing. If it is missing, the insert statement in the generated script is too large. An error message may appear during import:

ERROR 1153 (08S01) at line xxx: Got a packet bigger than 'max _ allowed_packet 'bytes

With this parameter added, each record corresponds to only one insert statement. Although the exported file is larger, errors are prevented.

When the database table structure is backed up, the -- skip-opt option is specified, which is equivalent to disabling the following parameters:

--add-drop-table, --add-locks,--create-options, --quick, --extended-insert,--lock-tables, --set-charset, and --disable-keys

Option -- create-option looks inconspicuous:

  -a, --create-options       Include all MySQL specific create options.

In fact, if we disable it, the table structure backed up will be missing:

AUTO_INCREMENT -- The AUTO_INCREMENT attribute of the PK field and the AUTO_INCREMENT attribute of the data table all lose ENGINE = InnoDB default charset = utf8 -- the ENGINE and character set attribute of the data table

Reference: http://doc.chinaunix.net/mysql/200912/128997_2.shtml

2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u user_name-p database_name table_name> outfile_name. SQL

3. Export a database structure
Mysqldump-u user_name-p-d-add-drop-table database_name> outfile_name. SQL
-D no data-add-drop-table add a drop table before each create statement

Mysqldump-u root-p123-h192.168.1.130-d> c:/msyql. SQL (-d indicates that data is not included)

4. Export with language Parameters
Mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name. SQL

7. Transfer text data to the database

1. Text data should conform to the format: field data is separated by the tab key, and the null value is replaced by \ n. For example:
3 rose Dalian No. 2 Middle School 1976-10-10
4 mike Dalian No. 1 1975-12-23
Assume that you save these two sets of data as a school.txt file and put it under the c-drive root directory.

2. Data Import command
Mysql> load data local infile "c: \ school.txt" into table name;
Note: You 'd better copy the file to the mysql \ bin directory and use the use command to create the database where the table is located.

8. Table operations

1. display the data table structure:
Mysql> DESCRIBE table name; (DESC table name)

2. Create a data table:
Mysql> USE Database Name; // enter the database
Mysql> create table Name (field name VARCHAR (20), field name CHAR (1 ));

3. delete a data table:
Mysql> drop table name;

4. rename a data table
Alter table t1 rename t2;

5. display the records in the table:
Mysql> SELECT * FROM table name;

6. insert records into the table:
Mysql> insert into table name VALUES ("hyq", "M ");

7. Update table data:
Mysql-> UPDATE table name: SET field name: 1 = 'a'; field name: 2 = 'B' WHERE field name: 3 = 'C ';

Connection field processing:

Update category set attribute_group = CONCAT (attribute_group, ', 6') where category_id = 149; // Add 6 after the foundation of the original attribute_group

8. Clear records in the table:
Mysql> delete from table name;

9. load data into a data table in text mode:
Mysql> load data local infile "D:/mysql.txt" into table name;

Mysql> Load Data InFile "D:/mysql.txt" into table name;

10. display the table definition. You can also see the table constraints, such as foreign keys.

Mysql> show create table yourtablename;
You can also use mysqldump to dump the complete table definition to a file, including the foreign key definition.

You can also use the following command to list the foreign key constraints of table T:
Mysql> show table status from yourdatabasename LIKE 'T'
The foreign key constraint is listed in the Table comment.

Stored Procedure

11. Create a stored procedure

Create procedure procedureName (in paramentName type, in paramentName type ,......)
BEGIN
SQL sentences;
END

12. Call the Stored Procedure
Mysql> CALL procedureName (paramentList );

E. g.: mysql> CALL addMoney (12,500 );

13. view the stored procedure of a specific database
Method 1: mysql> SELECT 'name' FROM mysql. proc WHERE db = 'your _ db_name 'AND 'type' = 'Procedure ';
Method 2: mysql> show procedure status;

14. delete a stored procedure
Mysql> drop procedure procedure_name;
Mysql> drop procedure if exists procedure_name;

15. view the definition of a specified Stored Procedure
Mysql> show create procedure proc_name;
Mysql> show create function func_name;

---------- Example 1 -----------
Mysql> DELIMITER $
Mysql> USE 'db _ name' $ // select a database
Mysql> drop procedure if exists 'addmoney' $ // delete a stored PROCEDURE with the same name
Mysql> create definer = 'root' @ 'localhost' PROCEDURE 'addmoney' (IN xid INT (5), IN xmoney INT (6 ))
Mysql> BEGIN
Mysql> update user u SET u. money = u. money + xmoney WHERE u. id = xid; // Semicolon ";" does not cause statement execution because the current Delimiter is defined as $
Mysql> END $ // termination
Mysql> DELIMITER; // change the DELIMITER back to the Semicolon ";"

Mysql> call addMoney (5,); // executes the Stored Procedure

---------- Example 2 -----------
Mysql> delimiter //
Mysql> create procedure proc_name (in parameter integer)
Mysql> begin
Mysql> if parameter = 0 then
Mysql> select * from user order by id asc;
Mysql> else
Mysql> select * from user order by id desc;
Mysql> end if;
Mysql> end;
Mysql> //// here "//" is the Terminator
Mysql> delimiter;
Mysql> show warnings;
Mysql> call proc_name (1 );
Mysql> call proc_name (0 );

9. modify database and table operations

1. To change column a from INTEGER to tinyint not null (same name ),
Change Column B from CHAR (10) To CHAR (20), rename it, and change from B to c:
Mysql> alter table t2 MODIFY a tinyint not null, CHANGE B c CHAR (20 );

2. Add a new TIMESTAMP column named d:
Mysql> alter table t2 ADD d TIMESTAMP;

Alter table comments add isShow TINYINT (1) default 1 not null comment '1: displayed, 2: deleted, 3: not displayed'

3. Add an index on column d and set column a as the primary key:
Mysql> alter table t2 add index (d), add primary key ();

// Add a field and set the primary key

Alter table tabelname ADD new_field_id int (5) unsigned default 0 not null auto_increment, ADD primary key (new_field_id );

Alter table MERs modify id int not null auto_increment primary key;