MySQL Common commands

1. Connect to MySQL
Mysql-u Username-p//Enter password, password is not visible
2. Exit the MySQL command
Exit (Enter)
3. Change the password
Mysqladmin-u username-p Old password password new password
4. View MySQL permissions and users
Select Host,user from user;

Operations on a database
1. Displays the list of databases in the current database server:
Mysql> SHOW DATABASES;

2. Display the data tables in the database:
mysql> use library name;
Mysql> SHOW TABLES;

3. Display the database name of use:
Mysql> SELECT DATABASE ();

4. Establish the database:
mysql> CREATE database name;

5. Delete the database:
mysql> DROP database name;

6. Import the. sql File command:
mysql> use database name;
Mysql> SOURCE D:/mysql.sql;

You can also import by typing the following command in a DOS environment:
Mysql-uroot-proot DatabaseName < Databasename.sql
Note: Before importing, please ensure that MySQL must have databasename this database;

Vi. backing up the database:

Note that the mysqldump command executes under the DOS Mysql\bin directory and cannot be executed in a MySQL environment, so it cannot be separated by a semicolon ";" End. If you have logged in to MySQL, run the exit command mysql> exit

1. Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
mysqldump-uroot-p123456 database_name > Outfile_name.sql

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

4. Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

Vii. transferring text data to the database

1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \ n. Example:
3 Rose Dalian II 1976-10-10
4 Mike Dalian One 1975-12-23
Suppose you save these two sets of data as school.txt files, placed in the C packing directory.

2. Data Incoming command
mysql> Load Data local infile "c:\school.txt" into table name;
Note: You might want to copy the file to the Mysql\bin directory, and use the using command to hit the library that contains the table.

Viii. operation of the table

1, display the structure of the data table:
mysql> DESCRIBE table name; (DESC table name)

2. Set up the data sheet:
mysql> use library name; Enter the database
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));

3. Delete Data sheet:
mysql> DROP table name;

4. Renaming the data table
ALTER TABLE t1 rename T2;

5. Display the records in the table:
Mysql> SELECT * from table name;

6. Insert a record into the table:
mysql> INSERT into table name VALUES ("HyQ", "M");

7. Update the data in the table:
mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';

8. Empty the records in the table:
Mysql> DELETE from table name;

9. Load data into the data table in text mode:
mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;

10, display the definition of the table, you can also see the constraints of the table, such as foreign keys

mysql> SHOW CREATE TABLE yourtablename;
You can also dump the full definition of a table into a file by mysqldump, including, of course, the foreign key definition.

You can also list foreign key constraints for table T by using the following directives:
Mysql> SHOW TABLE STATUS from yourdatabasename like ' T '
FOREIGN KEY constraints will be listed in the table comments.

Stored Procedures

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);

Example:mysql> call Addmoney (12, 500);

13. View stored procedures for a specific database
Method one:mysql> SELECT ' name ' from mysql.proc WHERE db = ' your_db_name ' and ' type ' = ' PROCEDURE ';
Method Two:mysql> show procedure status;

14. Delete stored Procedures
mysql> DROP PROCEDURE procedure_name;
mysql> DROP PROCEDURE IF EXISTS procedure_name;

15. View the specified stored procedure definition
mysql> SHOW CREATE PROCEDURE proc_name;
mysql> SHOW CREATE FUNCTION func_name;

----------Example One-----------
mysql> DELIMITER [Math processing Error]//Select database
mysql> DROP PROCEDURE IF EXISTS ' Addmoney ' [Math processing Error]
Mysql> end$$//termination
Mysql> DELIMITER; Change the delimiter back to the semicolon ";"

Mysql> call Addmoney (5,1000); Executing stored procedures

----------Example Two-----------
Mysql> delimiter//
Mysql> CREATE PROCEDURE Proc_name (in Parameter integer)
Mysql> begin
mysql> if Parameter=0 Then
Mysql> SELECT * from the user order by ID ASC;
Mysql> Else
Mysql> SELECT * from the user order by id desc;
Mysql> End If;
Mysql> end;
Mysql>////Here "//" for Terminator
Mysql> delimiter;
Mysql> Show warnings;
Mysql> call Proc_name (1);
Mysql> call Proc_name (0);

Ix. actions to modify column properties of a table

1. In order to change column A, change from integer to tinyint not NULL (same name),
and change column B, from char (10) to char (20), rename it at the same time, 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;

3. Add an index to column D and make column a the primary key:
mysql> ALTER TABLE T2 add INDEX (d), add PRIMARY KEY (a);

4. Delete Column C:
mysql> ALTER TABLE T2 DROP COLUMN C;

5. Add a new auto_increment integer column named C:
mysql> ALTER TABLE T2 ADD C INT UNSIGNED not NULL auto_increment,add INDEX (c);
Note that we indexed C because the auto_increment column must be indexed, and in addition we declare that C is not NULL,
Because the indexed column cannot be null

Ang setting build and build tables and instances of inserting data

Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table
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 ');
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.
(1) You can write the above command as-is to a text file, assume that it is school.sql, then copy to C: \, and enter directory \mysql\bin in DOS, and then type the following command.
Mysql-uroot-p Password < C:\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).
(2) or enter the command line after using mysql> source C:\school.sql; You can also import the School.sql file into the database.

MySQL Common commands