MySQL common commands (for reference)
The most common display commands are:
1. Display the list of databases.
2. Display the data table in the library:
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * FROM table name
Connect to MySQL
Format: mysql-h host address-u user name-P user Password
Example 1: Connect to MySQL on this computer.
Connect to MYSQL on the remote host.
2. Connect to MySQL on the remote host. Assume the remote host IP is: 184.108.40.206, the user name is root, the password is abcd123. Type the following command:
Mysql-h220.127.116.11-u Root-p 123; (Note: You can not add a space between the root and the other)
3. Exit MySQL command: Exit (enter)
Modify New Password
In Terminal input: Mysql-u user name-p password, enter MySQL.
Update user set Password=password (' New password ') where user= ' username ';
Flush privileges; #更新权限
Second, change the password.
Format: Mysqladmin-u username-P Old password password new password
1, add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2, then change the root password to djg345.
Mysqladmin-u root-p ab12 Password djg345
(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)
3, the command line to modify the root password:
mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;
4. Display the current User:
Mysql> SELECT USER ();
Third, add new users.
Format: Grant Select on database. * To User name @ login host identified by "password"
1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, connect with the root user
MYSQL, and then type the following command:
Grant Select,insert,update,delete on test1 "%" identified by "ABC";
But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2.
2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host),
This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [email protected] identifiedby "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
Mysql-u User name-p password
Mysql>delete from user where user= ' user name ' and host= ' localhost ';
Delete a user's database
Mysql>drop database dbname;
Show all databases
mysql> show databases; (note: There is a last s)
Create a database
mysql> CREATE DATABASE test;
Connecting to a database
mysql> use test;
View the database currently in use
Mysql> Select Database ();
Table information contained in the current database
Mysql> Show tables; (Note: There is a last s)
Deleting a database
mysql> drop database test;
Note: You should connect to a database by using use < database name > before the operation.
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Mysql> CREATE TABLE MyClass (
ID int (4) NOT null primary key auto_increment,
Name Char (a) is 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
Mysql> describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;
Delete a table
Command: DROP table < table name >
Example: Deleting a table with a table named MyClass
mysql> drop table MyClass;
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);
Querying data in a table
Querying all Rows
Mysql> select * from MyClass;
Querying the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Mysql> select * from MyClass limit 0, 2;
Delete data from a table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table MyClass
Mysql> Delete from MyClass where id=1;
modifying data in a table
Command: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;
Add fields to a table
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '
Change table name
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass
Mysql> Rename table MyClass to Youclass;
Update Field Contents
Command: Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
For example: Add 4 spaces in front of the article
Update article Set Content=concat (", content);
Database Import and Export
To export a database file from a database
Using the "mysqldump" command
First, go to the DOS interface and do the following.
1) Export all databases
Format: mysqldump-u [database user name]-p-a>[save path to backup file]
2) Export data and structure
Format: mysqldump-u [Database user name]-p [database name to be backed up]>[backup file save path]
Example 1: Export the database mydb to a e:mysqlmydb.sql file.
Open start, run, input "cmd" and enter command line mode.
C:> mysqldump-h localhost-u root-p mydb >e:mysqlmydb.sql
Then enter the password, wait for an export to succeed, you can check the target file for success.
Example 2: Export mytable from Database mydb to the E:mysqlmytable.sql file.
C:> mysqldump-h localhost-u root-p mydb mytable>e:mysqlmytable.sql
Example 3: Export the structure of the database mydb to a e:mysqlmydb_stru.sql file.
C:> mysqldump-h localhost-u root-p mydb–add-drop-table >e:mysqlmydb_stru.sql
Note:-h localhost can be omitted, it is generally used on the virtual host.
3) Export data structure only
mysqldump-u [Database user name]-p-t [the name of the database to be backed up]>[the save path to the backup file]
4) Export the events in the database
Format: mysqldump-u [database user name]-p-e [Database user name]>[save path to backup file]
5) exporting stored procedures and functions in the database
Format: mysqldump-u [database user name]-p-r [Database user name]>[save path to backup file]
Import the database from an external file
1) Use the "source" command
First go to the MySQL command console, then create the database, and then use the database. Finally, perform the following actions.
Mysql>source [Save path of backup file]
2) Use the "<" symbol
First go to the "MySQL" command console, then create the database, then exit MySQL and enter the DOS interface. Finally, perform the following actions.
Mysql-u Root–p < [save path of backup file]
Vi. backing up the database:
Note that the mysqldump command executes under the DOS Mysqlbin 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 Mysqlbin 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 substituted 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 Mysqlbin 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.
11. Create a stored procedure
CREATE PROCEDURE procedurename (in Paramentname type, in Paramentname type,......)
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 fromnamemysql.proc WHERE db = ' your_db_name ' andtype= ' 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 $$
mysql> usedb_name$$//Select Database
mysql> Drop PROCEDURE If EXISTSaddMoney$$//If there is a stored procedure with the same name, delete the
mysql> CREATE definer=root@localhostPROCEDUREaddMoney(in XID Int (5), in Xmoney Int (6))
Mysql> UPDATE USER u SET u.money = U.money + Xmoney WHERE u.id = XID; Semicolon ";" Does not result in statement execution because the current delimiter is defined as $$
Mysql> DELIMITER; Change the delimiter back to the semicolon ";"
Mysql> call Addmoney (5,1000); Executing stored procedures
———-Example Two ——— –
Mysql> CREATE PROCEDURE Proc_name (in Parameter integer)
mysql> if Parameter=0 Then
Mysql> SELECT * from the user order by ID ASC;
Mysql> SELECT * from the user order by id desc;
Mysql> End If;
Mysql>////Here "//" for Terminator
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 ',
); 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 it is in a text file, assuming school.sql, then copy it to C:, and enter directory Mysqlbin 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 is a relational database (relational databases Management System), this so-called "relational" can be understood as the concept of "table", a relational database consists of one or more tables, a table:
Header (header): The name of each column;
Row (COL): Each line is used to describe the specific information of a person/thing;
Value: The specific information for the row, each value must be the same as the data type of the column;
Key: A method used in a table to identify a particular person, and the value of the key is unique in the current column.
Basic composition of MySQL scripts
Like the regular scripting language, MySQL also has a set of rules for the use of characters, words, and special symbols, and MySQL executes SQL scripts to perform operations on the database, which consists of one or more MySQL statements (SQL statement + extension statements), and the script file suffix is typically. s when saved. Ql. Under the console, the MySQL client can also execute a single sentence without saving the. sql file.
Identifiers are used to name objects, such as databases, tables, columns, variables, and so on, to be referenced elsewhere in the script. MySQL identifier naming rules are a little cumbersome, and here we use universal Naming conventions: Identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or an underscore.
The case sensitivity of identifiers depends on the current operating system and is not sensitive under Windows, but for most Linuxunix systems These identifiers are case sensitive.
Data types in MySQL
MySQL has three categories of data types, number, date time, string, and more detailed sub-types in the three categories:
integers: tinyint, smallint, mediumint, int, bigint
Floating-point numbers: float, double, real, decimal
Dates and times: date, Time, DateTime, timestamp, year
String: char, varchar
Text: Tinytext, text, Mediumtext, Longtext
Binary (used to store pictures, music, etc.): Tinyblob, Blob, Mediumblob, Longblob
For more information, see: MySQL Data type: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html
Using the MySQL Database
Log in to MySQL
When the MySQL service is already running, we can log in to the MySQL database via MySQL's own client tool, first open a command prompt, enter the following format naming:
MYSQL-H host name-u user name-P
-H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;
-U: The name of the user to log in;
-P: Tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.
To log in as an example of a MySQL database that has just been installed on this computer, enter Mysql-u root-p at the command line to confirm that if the installation is correct and MySQL is running, you will get the following response:
If the password exists, enter the password login, does not exist then directly press ENTER to log in, according to the installation method in this article, the default root account is no password. Once you're signed in, you'll see Welecome to the MySQL monitor ... The prompt.
Then the command prompt will always wait for the command input with mysql> plus a blinking cursor, enter exit or quit to sign out.
Create a database
Use the CREATE DATABASE statement to complete the creation of databases, with the following format for creating commands:
Create database name [other options];
For example, we need to create a database named samp_db and execute the following command at the command line:
Create DATABASE samp_db character Set GBK;
To facilitate the display of Chinese at the command prompt, the database character encoding is specified as GBK at creation time by character set GBK. The response to Query OK, 1 row affected (0.02 sec) is obtained when the creation is successful.
Note: The MySQL statement is a semicolon (;) as the end of the statement, if you do not add a semicolon at the end of the statement, the command prompt prompts you to continue typing (there are individual exceptions, but the semicolon is definitely not wrong);
Hint: You can use show databases; command to see which databases have been created.
Select the database you want to manipulate
To operate on a database, you must first select the database, or you will be prompted with an error:
ERROR 1046 (3d000): No Database selected
Two options for using the database:
One: Specified when logging in to the database, command: mysql-d selected database name-h hostname-u user name-P
For example, select the database you just created when you log in: mysql-d samp_db-u root-p
Second: After logging in using the USE statement to specify, command: used database name;
The USE statement can be executed without a semicolon, and a samp_db is used to select the database you just created, and you will be prompted after successful selection: Database changed
Create a database table
Use the CREATE TABLE statement to complete the creation of a table, the common form of CREATE TABLE:
CREATE TABLE table name (column declaration);
To create the students table, for example, the table will hold the number (ID), name, Gender (sex), Age, contact phone (tel) content:
CREATE TABLE Students
ID int unsigned NOT NULL Auto_increment primary key,
Name Char (8) is not NULL,
Sex char (4) NOT NULL,
Age tinyint unsigned is not NULL,
Tel char (+) NULL default "-"
For some longer statements at the command prompt it may be easy to get wrong, so we can write the statement through any text editor and save it as a createtable.sql file, executing the script through the file redirection at the command prompt.
Open a command prompt, enter: mysql-d samp_db-u root-p < Createtable.sql
(Hint: 1. If connecting to a remote host, add the-H command; 2. The Createtable.sql file must specify the full path of the file if it is not in the current working directory. )
CREATE TABLE tablename (columns) is the command that creates a database table, the name of the column and the data type of the column will be completed in parentheses;
In parentheses, 5 columns are declared, ID, name, sex, age, and Tel are the names of each column followed by the data type description, separated by commas (,) between the columns and column descriptions;
The "id int unsigned NOT NULL auto_increment primary key" line is described:
"id" is the name of the column;
"int" Specifies that the column is of type int (with a value range of 8388608 to 8388607), which is then decorated with "unsigned" to indicate that the type is unsigned, at which time the column has a value ranging from 0 to 16777215;
"Not NULL" indicates that the value of the column cannot be empty and must be filled, and the default can be null if the property is not specified;
The "auto_increment" needs to be used in an integer sequence, and the effect is that if the column is NULL when inserting the data, MySQL will automatically produce a unique identifier value that is larger than the existing values. Only one such value can be in each table and the column must be an indexed column.
"PRIMARY key" means that the column is the primary key of the table, the value of this column must be unique, and MySQL will automatically index the column.
The following char (8) indicates that the stored character length is 8, the tinyint value range is 127 to +, and the default property specifies a value when the column value is empty.
For more data types, see MySQL data type: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html
Tip: 1. Use show tables; command to view the name of the table you have created; 2. Use the describe table name; command to view the details of a table that has been created.
Working with MySQL Database
Inserting data into a table
The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:
Insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...);
where  The content is optional, for example, to insert a record into the students table in the SAMP_DB database, execute the statement:
INSERT into students values (NULL, "Wang Gang", "male", 20, "13811371377");
Press ENTER to confirm that if you prompt Query Ok, 1 row affected (0.05 sec) indicates that the data was inserted successfully. If the insertion fails, check that the database you want to manipulate is selected.
Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:
INSERT into students (name, sex, age) VALUES ("Sun Lihua", "female", 21);
Querying data in a table
Select statements are commonly used to from a database based on certain query rules, with the following basic usage:
Select Column name from table name [query condition];
For example, to query the names and ages of all students in the students table, enter the statement select name, age from students; The results of the implementation are as follows:
Mysql> select name, age from students;
| name | Age |
| Wang Gang | 20 |
| Sun Lihua | 21 |
| Engineering | 23 |
| Zheng June | 19 |
| Chen Fang | 22 |
| Zhang Weibong | 21 |
6 rows in Set (0.00 sec)
You can also use the wildcard character * to query all the contents of the table, statements: SELECT * from students;
Query by specific criteria:
The WHERE keyword is used to specify the query condition, in the form of the Select Column name from the table name where condition;
To query all gender-based information as an example, enter a query statement: SELECT * from students where sex= "female";
The WHERE clause does not only support the "where Column name = value" Query form, which is named equal to the value, and is supported for the operators of general comparison operations such as =, >, <, >=, <,! =, and some extension operators are [not] null, in, and like Wait a minute. You can also combine queries with OR and and for query criteria, and later learn more advanced conditional query methods, which are no longer introduced.
Find information for everyone over the age of 21: SELECT * from students where ages > 21;
Query everyone with the word "King" in the name: SELECT * from students where name is like "% king";
Information for anyone with a query ID of less than 5 and older than 20: SELECT * from students where id<5 and age>20;
Updating data in a table
The UPDATE statement can be used to modify the data in the table, using the following basic form:
Update table name set column name = new value where update condition;
Examples of Use:
Change the phone number with ID 5 to the default "-": Update students set tel=default where id=5;
Increase the age of all 1:update students set age=age+1;
Change the name of the phone number 13288097888 to "Zhang Weipeng" and change the age to 19:update students set name= "Zhang Weipeng" age=19 where tel= "13288097888";
Delete data from a table
The DELETE statement is used to delete data from a table, with the following basic usage:
Delete from table name where delete condition;
Examples of Use:
Delete the row with ID 2: Delete from students where id=2;
Delete all data older than 21 years: Delete from students where age<20;
Delete all data from the table: delete from students;
Modifications to the table after creation
The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:
Basic form: ALTER TABLE name add column list data type [after insertion position];
The last appended column of the table Address:alter table students add address char (60);
Inserts a column after the column named Age birthday:alter table students add birthday date after age;
Basic form: ALTER TABLE name change column Name column new name new data type;
Rename Table Tel column to Telphone:alter table students Change Tel telphone char (+) Default "-";
Change the data type of the Name column to char: ALTER TABLE students changes name name char (+) not null;
Basic form: ALTER TABLE name drop column name;
Delete birthday column: ALTER TABLE students drop birthday;
Renaming a table
Basic: ALTER TABLE name rename new table name;
Rename students table for workmates:alter table students rename workmates;
Delete entire table
Basic form: drop table name;
Example: Deleting a workmates table: drop-table workmates;
Delete entire database
Basic form: drop database name;
Example: Delete a samp_db database: Drop the DB samp_db;
To modify the root user password
According to the installation of this article, the root user does not have a password by default, there are more ways to reset the root password, this is only a more common way.
Using the Mysqladmin method:
Open command Prompt interface, execute command: mysqladmin-u root-p password New password
After executing prompts to enter the old password to complete the password modification, when the old password is empty directly press ENTER to confirm.
Visual Management Tools MySQL Workbench
Although we can execute the MySQL statement at the command prompt through a line of input or through a redirected file, this is inefficient, because there is no automatic check of the syntax before execution, and the likelihood of some errors caused by input errors is greatly increased, so try some visual MySQL database management tools , MySQL Workbench is a visual management tool that MySQL officially provides to MySQL, where you can directly manage the contents of the database in a visual way, and the SQL Script Editor of MySQL Workbench supports syntax highlighting and input language The law examines, of course, its powerful, not limited to these two points.
MySQL Workbench Official Description: http://www.mysql.com/products/workbench/
MySQL Workbench download page: http://dev.mysql.com/downloads/tools/workbench/
The following are common commands under Linux:
MySQL installation directory
/usr/share/mysql (mysql.server command and configuration file)
/usr/bin (Mysqladmin mysqldump and other commands)
/etc/init.d/mysql (startup script file for MySQL directory)
MySQL common commands under Linux
MySQL Common instructions, java,php programmers, database engineers must. Programmers Xiaoice Common Data collation