Detailed MySQL basic commands
Format: mysql -h host address -u user name -p User password
1, is 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, you lose the password prompt after the carriage return may or may not have a space before the space Note that the user name, but there must be no space before the password, or let re-enter your password.
If you have just installed MYSQL, super-user root no password, so you can directly enter into the MYSQL, MYSQL prompt is: MySQL>
2, to connect to remote host on MYSQL. Suppose the remote host 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: You can not add a space between u and the root, the other is the same)
3, exit MYSQL command : exit (Enter)
2. Change Password
Format: mysqladmin -u username -p password old password new password
1, to add a root password ab12.
First, enter the DOS directory mysql \ bin, then type the following command
mysqladmin -u root -password ab12
Note: Since the beginning of root without a password, so the -p an old password can be omitted.
2, then the root password to djg345.
mysqladmin -u root -p ab12 password djg345
3, add new users
Note: different above and below the MYSQL environment because it is a command, so the back with a semicolon as a command terminator
format:. Grant select on database * to username @ log on the host identified by “password”
1, adding a password for the user test1 abc, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, the root user connected to MYSQL, and then type the following command:
Grant SELECT, INSERT, Update, the Delete ON * * to [Email = test1 @ “%] test1 @”% [/ Email] “Identified by” ABC “;.
But increase in users is very dangerous, you want to know as someone test1 password, then he can log your mysql database and the data you can do whatever the solution, see 2 on the internet on any computer.
2, add a user test2 password is abc, so that he can only log on localhost, and can query the database mydb, insert, modify, delete operations (localhost means the local host, that is, MYSQL database where that host) , so that users know that the use test2 password, he can not directly access the database from the internet, can only be accessed through the web page MYSQL host.
Grant SELECT, INSERT, Update, the 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, the Delete mydb ON * to [@ Email = test2. localhost] test2 @ localhost [/ email ] identified by “”;
4.1 Creating a database
Note: Creating a first connection Mysql database server before
the command: create database <database name>
Example 1: Create a database called xhkdb the
mysql> create database xhkdb;
Example 2: Creating a database and assigned a user
?CREATE DATABASE database name;
?GRANT the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON * TO database name, database name @localhost IDENTIFIED BY ‘password’;.
?SET the FOR pASSWORD ‘database name’ @ ‘localhost’ = OLD_PASSWORD ( ‘ password’);
followed by the implementation 3 command to create a complete database. Note: Chinese “Password” and “database” is the need to set up their own households.
4.2 Displaying Database
Command: show databases (Note: There is a final S)
MySQL> Show Databases;
Note: For longer garbled when displayed, to modify the database default encoding. In the following pages GBK encoding as an example:
1, MYSQL modify configuration files: the my.ini modify inside-Character-SET = default GBK
2, run-time code modification:
?Java Code: jdbc: mysql: // localhost: 3306 / ? the Test useUnicode = to true & characterEncoding = GBK
?PHP Code: header ( “the Content-the Type: text / HTML; charset = GB2312”);
? C language code: int mysql_set_character_set (MYSQL * mysql, char * csname);
this function is used for the current connection set the default character set. Csname string specifies a valid character set name. Connection collation becomes the default collation character set. The function of the statement of work is similar to SET NAMES, but it can also set mysql-> charset value, thus affecting the set by mysql_real_escape_string () character set.
4.3 Delete Database
Command: drop database <database name>
For example: to delete a database named xhkdb
mysql> drop database xhkdb;
Example 1: Delete an already existing database to determine the
MySQL> the drop DROP DATABASE Database;
Query the OK, 0 rows affected (0.00 sec)
Example 2 : delete an uncertain existence of the database
MySQL> the drop database DROP dATABASE;
eRROR 1008 (HY000): the drop of can not database ‘drop_database’; database does not the exist
// error, can not be deleted ‘drop_database’ database, which does not exist .
MySQL> Database the drop DROP DATABASE IF EXISTS;
Query the OK, 0 rows affected, 1 warning (0.00 sec) // generate a warning Explanation This database does not exist
MySQL> the Create Database DROP DATABASE;
Query the OK, 1 Row affected (0.00 sec)
MySQL> the drop database if exists drop_database; // if exists is determined whether the database exists, there is no error
Query OK, 0 rows affected (0.00 sec)
4.4 database connection
Command: use <database name>
Example: If xhkdb database exists, try to access it:
MySQL> use xhkdb;
screen prompts: Database changed
use MySQL statements can notice the db_name database as the default (current) database for subsequent statements. The database remains the default database until the end of the discourse, or until a different release USE statement:
MySQL> USE DB1;
MySQL> the SELECT COUNT (*) the FROM MyTable; # Selects from db1.mytable
MySQL> USE DB2;
MySQL> SELECT COUNT (*) fROM mytable; # selects from db2.mytable
uSE statement for a particular database current mark, will not prevent you from accessing other tables in the database. The following examples from the authors table db1 database access, and access to edit the table from the db2 database:
MySQL> the USE db1;
MySQL> the SELECT AUTHOR_NAME, EDITOR_NAME the FROM author, db2.editor
-> the WHERE author.editor_id = db2.editor.editor_id;
the USE statement is provided for compatibility with Sybase.
Some friends asked, how to exit after the connection. In fact, do not withdraw, after use database, using show databases can query the database for all, if you want to jump to other databases with
use another database name
on it.
4.5 currently selected database
Command: MySQL> SELECT Database ();
the MySQL in the SELECT command is similar to other programming languages in print or write, you can use it to display a string, numeric result, math expressions, and so on. How to use the command in MySQL SELECT special features?
1. Display the version of MYSQL
MySQL> SELECT Version ();
+ ———————– +
| Version () |
+ —— + —————–
| 6.0.4-Alpha-Community Community |
+ ———————- – +
1 Row in SET (0.02 sec)
2. displays the current time
MySQL> SELECT now ();
+ ——————— +
| now () |
+ ——————— +
| 2009-09-15 22:35:32 |
+ ————– + ——-
1 Row in SET (0.04 sec)
3. To display the date
the SELECT DAYOFMONTH (CURRENT_DATE);
+ ——————— + —–
| DAYOFMONTH (CURRENT_DATE) |
+ ————————– +
| 15 |
+ —— + ——————–
1 Row in SET (0.01 sec)
the SELECT MONTH (CURRENT_DATE);
+ ————– + ——-
| MONTH (CURRENT_DATE) |
+ ——————— +
|. 9 |
+ ——— + ————
1 Row in SET (0.00 sec)
the SELECT YEAR (CURRENT_DATE);
+ ——————– +
| YEAR (CURRENT_DATE) |
+ ——————– +
| 2009 |
+ ——————- – +
1 Row in SET (0.00 sec)
4. The display string
MySQL> the SELECT “welecome to My Blog!”;
+ ———————- +
| welecome to My Blog |!
+ ———————- +
| welecome to My Blog |!
+ ———– + ———–
1 Row in SET (0.00 sec)
5. The calculator when using
SELECT ((4 * 4) / 10) + 25;
+ ———– + ———–
| ((4 * 4) / 10) + 25 |
+ ———————- +
| 26.60 |
+ ———————- +
1 Row in SET (0.00 sec)
6. The concatenated string
select CONCAT (f_name, “”, l_name)
the Name the AS
from employee_data
the WHERE title = ‘Marketing Executive’;
+ ————— +
| the Name |
+ ————— +
| Sehgal monica |
| Hal Simlai |
| Joseph Irvine |
+ ————— +
3 rows in SET (0.00 sec)
Note: this use CONCAT () function is used to character string pick up. In addition, we use the results to previously learned AS column ‘CONCAT (f_name, “”, l_name)’ played a pseudonym.
5.1 Create a data table
Command: create table <table name> (<field name 1> <Type 1> [, .. <field name n> <type n>]);
for example, to create a table named MyClass,
Field name Digital Type Data Width Is empty Whether the primary key Automatically increase Defaults
id int 4 no primary key auto_increment
name char 20 no
sex int 4 no 0
degree double 16 Yes
MySQL> the Create the Table MyClass (
> the above mentioned id int (4) Not null Primary Key AUTO_INCREMENT ,
> name char (20) Not null,
> Sex int (4) Not null default ‘0’,
> Double Degree (16,2));
5.3 Delete Data Sheet
Command: drop table <table name>
For example: to delete the table named MyClass table
MySQL> the drop the Table MyClass;
the DROP TABLE to cancel one or more tables. You must have the DROP privilege for each table. All table data and table definitions will be canceled, so use this statement to be careful!
Note: For a table with partitions, DROP TABLE permanently cancel the table definition, cancel each partition and cancel all the data stored in these partitions. DROP TABLE will be canceled and the cancellation of the table associated partition definition (.par) file.
For a non-existent table using IF EXISTS to prevent an error. When using IF EXISTS, for each table that does not exist, it generates a NOTE.
RESTRICT and CASCADE can make it easier for the partition. Currently, RESTRICT and CASCADE not work.
Table 5.4 insert data
Command: insert into <table name> [(<1 field name> [, .. <field name n>])] values (value 1) [, (value n)]
Example: To insert a table MyClass two records, which two record indicates: number of scores named Tom 1 is 96.45, No. 2 named Joan score was 82.99, No. 3 named Wang score was 96.5.
mysql> insert into MyClass values (1 , ‘Tom’, 96.45), (2, ‘Joan’, 82.99), (2, ‘Wang’, 96.59);
Note: insert into each table can only be inserted into a record .
5.5 data Query table
1), to query all rows
command: select <Field 1, Field 2, …> from <table name> where <expression>
For example: view all the data in the table MyClass
MySQL> SELECT * from MyClass;
2), before the inquiry a few lines of data
, for example: see the table in the first two rows of data MyClass
MySQL> SELECT * from the order by the above mentioned id MyClass limit 0,2;
SELECT with where ships used to find out more accurate and complex data.
5.6 to delete data in the table
Command: delete from table name where expressions
such as: MyClass delete table records numbered 1
mysql> delete from MyClass where id = 1;
The following is a comparison of before and after the deleted data table.
FirstName LastName Age
Peter Griffin 35
Glenn Quagmire 33
PHP code as an example below to delete “Persons” table all LastName = ‘Griffin’ records:
<? Php
$ Con = mysql_connect ( “localhost”, “peter”, “abc123”);
if (! $ con)
{
die ( ‘Could not connect:’ mysql_error ().);
}
mysql_select_db ( “my_db”, $ con);
mysql_query ( “DELETE FROM Persons WHERE LastName = ‘Griffin'”); mysql_close ($ con);
?>
After this deletion, the table is this:
FirstName LastName Age
Glenn Quagmire 33
5.7 modify the data in the table
Syntax: update table set field = new value, … where conditions
mysql> update MyClass set name = ‘ Mary’ where id = 1;
example 1: MySQL UPDATE statement is a single table:
the UPDATE [the LOW_PRIORITY] [IGNORE] tbl_name the SET col_name1 = expr1 [, col_name2 = expr2 …] [WHERE where_definition] [ORDER BY …] [LIMIT row_count]
examples of 2: UPDATE statement multi-table:
the UPDATE [the LOW_PRIORITY] [IGNORE] table_references the SET col_name1 = expr1 [, col_name2 = … expr2] [the WHERE where_definition]
the uPDATE syntax can be updated with the new values in the original table rows columns. SET clause indicates which columns to modify and what value should be given. WHERE clause specifies which rows should be updated. If no WHERE clause, all rows are updated. If you specify an ORDER BY clause, in accordance with the designated order of rows to be updated. LIMIT clause to a given limit, limit the number of rows that can be updated.
5.8 increase the field
Command: the ALTER the Table table add field types other;
for example: In Table MyClass added a field passtest, of type int (4), the default value is 0
MySQL> the ALTER the Table MyClass add passtest int ( 4 ) default ‘0’
plus index
mysql> alter table table add index index name (field name 1 [, field name 2 …]);
example: mysql> alter table employee add index emp_name (name);
plus the primary key index
mysql> alter table table name add primary key (field names);
example: mysql> alter table employee add primary key (id);
plus a unique constraint index
mysql> alter table table name add unique index name (field name);
example: mysql> alter table employee add unique emp_name2 (cardnumber);
delete an index
mysql> alter table table name drop index index name;
example: mysql> alter table employee drop index emp_name;
increase the field:
MySQL> the ALTER tABLE table_name the ADD FIELD_NAME field_type;
modify the original field names and type:
MySQL> the ALTER TABLE table_name the CHANGE old_field_name new_field_name field_type;
delete fields:
the MySQL the ALTER TABLE table_name the DROP FIELD_NAME;
5.9 modify the table name
Command: rename table the original table to the new table name;
for example: In Table MyClass name changed to YouClass
MySQL> MyClass to the rename the Table YouClass;
When you execute RENAME, you can not have any locked tables or transaction activity. You also need to have the original table ALTER, and DROP privileges, and the CREATE and INSERT privileges on the new table.
If the multi-table rename, MySQL encounters any errors, it will all be renamed renamed the table back, everything will return to its original state.
RENAME TABLE are added in MySQL 3.23.23.
6, the backup database
DOS command in the [url = file: // \\ mysql \\ bin] \\ mysql \\ bin [/ url] to the next directory
1. Export the entire database
export files exist by default mysql \ bin directory
mysqldump -u user name -p database name> exported 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. To export a database structure
the mysqldump -u USER_NAME -p -d -add-drop-table database_name> outfile_name.sql
-d no data -add-drop-table before each create statement increases the Table a the drop
4. with language parameter derivation
mysqldump -uroot -p -default-character-set = latin1 -set-charset = gbk -skip-opt database_name> outfile_name.sql
example, aaa database backup to a file back_aaa in:
[root @ test1 root] # CD / Home / Data / MySQL
[root @ test1 MySQL] # the mysqldump -u root -p –opt AAA> back_aaa
7.1 Examples of building a database and build a table of 1
drop database if exists school; // if there is deleted SCHOOL
create database school; // build libraries SCHOOL
use School; // open the library SCHOOL
the Create the Table Teacher // build table TEACHER
(
the above mentioned id int (3) Not null Primary Key AUTO_INCREMENT,
char name (10) Not null,
address VARCHAR (50) default ‘rmohan’
year a Date
); // build table end
// Here is the insert field
insert into teacher values ( “, ‘ allen’, ‘ one india rajesh’ ‘1976-10-10’);
INSERT INTO Teacher values ( ” ‘Jack’, ‘rajesh II’, ‘1975-12-23’);
If you type the above command at mysql prompt can be, but not convenient debugging.
1, you can write the above command as a text file is assumed to be school.sql, and then copied to the c: \\ and state into the DOS directory [url = file: // \\ mysql \\ bin] \\ mysql \\ bin [/ url] , and then type the following command:
MySQL-uroot–p password <c: \\ school.sql
If successful, vacated his party without any display; any error, there will be prompt. (The above command has been debugging, as long as you // uncomment to use).
2, or go to the command line using the mysql> source c: \\ school.sql; school.sql files can also be imported in the database.
7.2 Examples of building a database and build a table of 2
drop database if exists school; // if there is deleted SCHOOL
create database school; // build libraries SCHOOL
use School; // open the library SCHOOL
the Create the Table Teacher // build table TEACHER
(
the above mentioned id int (3) Not null Primary Key AUTO_INCREMENT,
char name (10) Not null,
address VARCHAR (50) default ” rmohan ”,
year a Date
); // build table end
// Here is the insert field
insert into teacher values ( ” ‘ ‘, ” john ” , ” in rmohan, a ”, ” 1976-10-10 ”);
INSERT INTO Teacher values ( ” ”, ” Jack ”, ” in rmohan, a ”, ” 1975-12- 23 ”);
Note: under construction table
1, the ID number field is set to a length of 3: int (3); and it is automatically incremented for each record: auto_increment; and can not be empty: not null; and let him become the main field primary key.
2. Set the NAME field length of 10 characters
3, the ADDRESS field length to 50 characters, and the default value of rmohan.
4, YEAR set the date field.
Recent Comments