{"id":6322,"date":"2016-10-11T16:30:06","date_gmt":"2016-10-11T08:30:06","guid":{"rendered":"http:\/\/rmohan.com\/?p=6322"},"modified":"2016-10-11T16:30:06","modified_gmt":"2016-10-11T08:30:06","slug":"detailed-mysql-basic-commands","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6322","title":{"rendered":"Detailed MySQL basic commands"},"content":{"rendered":"

Detailed MySQL basic commands<\/strong><\/p>\n

Format: mysql -h host address -u user name -p User password<\/p>\n

1, is connected to the machine on MYSQL.
\nFirst, 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.<\/p>\n

If you have just installed MYSQL, super-user root no password, so you can directly enter into the MYSQL, MYSQL prompt is: MySQL><\/p>\n

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:
\nMySQL -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)<\/p>\n

3, exit MYSQL command : exit (Enter)<\/p>\n

 <\/p>\n

2. Change Password<\/strong><\/p>\n

Format: mysqladmin -u username -p password old password new password<\/p>\n

1, to add a root password ab12.
\nFirst, enter the DOS directory mysql \\ bin, then type the following command
\nmysqladmin -u root -password ab12
\nNote: Since the beginning of root without a password, so the -p an old password can be omitted.<\/p>\n

2, then the root password to djg345.
\nmysqladmin -u root -p ab12 password djg345
\n3, add new users<\/strong><\/p>\n

Note: different above and below the MYSQL environment because it is a command, so the back with a semicolon as a command terminator<\/p>\n

format:. Grant select on database * to username @ log on the host identified by “password”<\/p>\n

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:
\nGrant SELECT, INSERT, Update, the Delete ON * * to [Email = test1 @ “%] test1 @”% [\/ Email] “Identified by” ABC “;.<\/p>\n

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.<\/p>\n

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.
\nGrant SELECT, INSERT, Update, the Delete oN mydb. * to [Email = test2 @ localhost] test2 @ localhost [\/ email ] identified by “abc”;<\/p>\n

If you do not want test2 password, you can resort to a command to eliminate the password.
\nGrant SELECT, INSERT, Update, the Delete mydb ON * to [@ Email = test2. localhost] test2 @ localhost [\/ email ] identified by “”;<\/p>\n

4.1 Creating a database<\/strong><\/p>\n

Note: Creating a first connection Mysql database server before<\/p>\n

the command: create database <database name><\/p>\n

Example 1: Create a database called xhkdb the
\nmysql> create database xhkdb;<\/p>\n

Example 2: Creating a database and assigned a user<\/p>\n

?CREATE DATABASE database name;<\/p>\n

?GRANT the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON * TO database name, database name @localhost IDENTIFIED BY ‘password’;.<\/p>\n

?SET the FOR pASSWORD ‘database name’ @ ‘localhost’ = OLD_PASSWORD ( ‘ password’);<\/p>\n

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.
\n4.2 Displaying Database<\/p>\n

Command: show databases (Note: There is a final S)
\nMySQL> Show Databases;<\/p>\n

Note: For longer garbled when displayed, to modify the database default encoding. In the following pages GBK encoding as an example:<\/p>\n

1, MYSQL modify configuration files: the my.ini modify inside-Character-SET = default GBK
\n2, run-time code modification:
\n?Java Code: jdbc: mysql: \/\/ localhost: 3306 \/ ? the Test useUnicode = to true & characterEncoding = GBK
\n?PHP Code: header ( “the Content-the Type: text \/ HTML; charset = GB2312”);
\n? C language code: int mysql_set_character_set (MYSQL * mysql, char * csname);
\nthis 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.<\/p>\n

 
\n4.3 Delete Database<\/strong><\/p>\n

Command: drop database <database name>
\nFor example: to delete a database named xhkdb
\nmysql> drop database xhkdb;<\/p>\n

Example 1: Delete an already existing database to determine the
\nMySQL> the drop DROP DATABASE Database;
\nQuery the OK, 0 rows affected (0.00 sec)<\/p>\n

Example 2 : delete an uncertain existence of the database
\nMySQL> the drop database DROP dATABASE;
\neRROR 1008 (HY000): the drop of can not database ‘drop_database’; database does not the exist
\n\/\/ error, can not be deleted ‘drop_database’ database, which does not exist .
\nMySQL> Database the drop DROP DATABASE IF EXISTS;
\nQuery the OK, 0 rows affected, 1 warning (0.00 sec) \/\/ generate a warning Explanation This database does not exist
\nMySQL> the Create Database DROP DATABASE;
\nQuery the OK, 1 Row affected (0.00 sec)
\nMySQL> the drop database if exists drop_database; \/\/ if exists is determined whether the database exists, there is no error
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

 
\n4.4 database connection<\/strong><\/p>\n

Command: use <database name><\/p>\n

Example: If xhkdb database exists, try to access it:
\nMySQL> use xhkdb;
\nscreen prompts: Database changed<\/p>\n

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:
\nMySQL> USE DB1;
\nMySQL> the SELECT COUNT (*) the FROM MyTable; # Selects from db1.mytable
\nMySQL> USE DB2;
\nMySQL> SELECT COUNT (*) fROM mytable; # selects from db2.mytable<\/p>\n

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:
\nMySQL> the USE db1;
\nMySQL> the SELECT AUTHOR_NAME, EDITOR_NAME the FROM author, db2.editor
\n-> the WHERE author.editor_id = db2.editor.editor_id;<\/p>\n

the USE statement is provided for compatibility with Sybase.<\/p>\n

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
\nuse another database name
\non it.<\/p>\n

4.5 currently selected database<\/strong><\/p>\n

Command: MySQL> SELECT Database ();<\/p>\n

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?<\/p>\n

1. Display the version of MYSQL
\nMySQL> SELECT Version ();
\n+ ———————– +
\n| Version () |
\n+ —— + —————–
\n| 6.0.4-Alpha-Community Community |
\n+ ———————- – +
\n1 Row in SET (0.02 sec)<\/p>\n

2. displays the current time
\nMySQL> SELECT now ();
\n+ ——————— +
\n| now () |
\n+ ——————— +
\n| 2009-09-15 22:35:32 |
\n+ ————– + ——-
\n1 Row in SET (0.04 sec)<\/p>\n

3. To display the date
\nthe SELECT DAYOFMONTH (CURRENT_DATE);
\n+ ——————— + —–
\n| DAYOFMONTH (CURRENT_DATE) |
\n+ ————————– +
\n| 15 |
\n+ —— + ——————–
\n1 Row in SET (0.01 sec)<\/p>\n

the SELECT MONTH (CURRENT_DATE);
\n+ ————– + ——-
\n| MONTH (CURRENT_DATE) |
\n+ ——————— +
\n|. 9 |
\n+ ——— + ————
\n1 Row in SET (0.00 sec)<\/p>\n

the SELECT YEAR (CURRENT_DATE);
\n+ ——————– +
\n| YEAR (CURRENT_DATE) |
\n+ ——————– +
\n| 2009 |
\n+ ——————- – +
\n1 Row in SET (0.00 sec)<\/p>\n

4. The display string
\nMySQL> the SELECT “welecome to My Blog!”;
\n+ ———————- +
\n| welecome to My Blog |!
\n+ ———————- +
\n| welecome to My Blog |!
\n+ ———– + ———–
\n1 Row in SET (0.00 sec)<\/p>\n

5. The calculator when using
\nSELECT ((4 * 4) \/ 10) + 25;
\n+ ———– + ———–
\n| ((4 * 4) \/ 10) + 25 |
\n+ ———————- +
\n| 26.60 |
\n+ ———————- +
\n1 Row in SET (0.00 sec)<\/p>\n

6. The concatenated string
\nselect CONCAT (f_name, “”, l_name)
\nthe Name the AS
\nfrom employee_data
\nthe WHERE title = ‘Marketing Executive’;
\n+ ————— +
\n| the Name |
\n+ ————— +
\n| Sehgal monica |
\n| Hal Simlai |
\n| Joseph Irvine |
\n+ ————— +
\n3 rows in SET (0.00 sec)
\nNote: 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.<\/p>\n

 
\n5.1 Create a data table<\/strong><\/p>\n

Command: create table <table name> (<field name 1> <Type 1> [, .. <field name n> <type n>]);<\/p>\n

for example, to create a table named MyClass,
\nField name Digital Type Data Width Is empty Whether the primary key Automatically increase Defaults
\nid int 4 no primary key auto_increment
\nname char 20 no
\nsex int 4 no 0
\ndegree double 16 Yes
\nMySQL> the Create the Table MyClass (
\n> the above mentioned id int (4) Not null Primary Key AUTO_INCREMENT ,
\n> name char (20) Not null,
\n> Sex int (4) Not null default ‘0’,
\n> Double Degree (16,2));
\n5.3 Delete Data Sheet<\/strong><\/p>\n

Command: drop table <table name><\/p>\n

For example: to delete the table named MyClass table
\nMySQL> the drop the Table MyClass;<\/p>\n

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!<\/p>\n

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.<\/p>\n

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.<\/p>\n

RESTRICT and CASCADE can make it easier for the partition. Currently, RESTRICT and CASCADE not work.
\nTable 5.4 insert data<\/strong><\/p>\n

Command: insert into <table name> [(<1 field name> [, .. <field name n>])] values (value 1) [, (value n)]<\/p>\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.
\nmysql> insert into MyClass values (1 , ‘Tom’, 96.45), (2, ‘Joan’, 82.99), (2, ‘Wang’, 96.59);<\/p>\n

Note: insert into each table can only be inserted into a record .
\n5.5 data Query table<\/strong><\/p>\n

1), to query all rows
\ncommand: select <Field 1, Field 2, …> from <table name> where <expression>
\nFor example: view all the data in the table MyClass
\nMySQL> SELECT * from MyClass;<\/p>\n

2), before the inquiry a few lines of data
\n, for example: see the table in the first two rows of data MyClass
\nMySQL> SELECT * from the order by the above mentioned id MyClass limit 0,2;<\/p>\n

SELECT with where ships used to find out more accurate and complex data.
\n5.6 to delete data in the table<\/strong>
\nCommand: delete from table name where expressions<\/p>\n

such as: MyClass delete table records numbered 1
\nmysql> delete from MyClass where id = 1;<\/p>\n

The following is a comparison of before and after the deleted data table.
\nFirstName LastName Age
\nPeter Griffin 35
\nGlenn Quagmire 33
\nPHP code as an example below to delete “Persons” table all LastName = ‘Griffin’ records:
\n<? Php
\n$ Con = mysql_connect ( “localhost”, “peter”, “abc123”);
\nif (! $ con)
\n{
\ndie ( ‘Could not connect:’ mysql_error ().);
\n}
\nmysql_select_db ( “my_db”, $ con);
\nmysql_query ( “DELETE FROM Persons WHERE LastName = ‘Griffin'”); mysql_close ($ con);
\n?>
\nAfter this deletion, the table is this:
\nFirstName LastName Age
\nGlenn Quagmire 33<\/p>\n

5.7 modify the data in the table<\/strong><\/p>\n

Syntax: update table set field = new value, … where conditions
\nmysql> update MyClass set name = ‘ Mary’ where id = 1;<\/p>\n

example 1: MySQL UPDATE statement is a single table:
\nthe UPDATE [the LOW_PRIORITY] [IGNORE] tbl_name the SET col_name1 = expr1 [, col_name2 = expr2 …] [WHERE where_definition] [ORDER BY …] [LIMIT row_count]<\/p>\n

examples of 2: UPDATE statement multi-table:
\nthe UPDATE [the LOW_PRIORITY] [IGNORE] table_references the SET col_name1 = expr1 [, col_name2 = … expr2] [the WHERE where_definition]<\/p>\n

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.<\/p>\n

5.8 increase the field<\/strong><\/p>\n

Command: the ALTER the Table table add field types other;
\nfor example: In Table MyClass added a field passtest, of type int (4), the default value is 0
\nMySQL> the ALTER the Table MyClass add passtest int ( 4 ) default ‘0’<\/p>\n

plus index
\nmysql> alter table table add index index name (field name 1 [, field name 2 …]);
\nexample: mysql> alter table employee add index emp_name (name);<\/p>\n

plus the primary key index
\nmysql> alter table table name add primary key (field names);
\nexample: mysql> alter table employee add primary key (id);<\/p>\n

plus a unique constraint index
\nmysql> alter table table name add unique index name (field name);
\nexample: mysql> alter table employee add unique emp_name2 (cardnumber);<\/p>\n

delete an index
\nmysql> alter table table name drop index index name;
\nexample: mysql> alter table employee drop index emp_name;<\/p>\n

increase the field:
\nMySQL> the ALTER tABLE table_name the ADD FIELD_NAME field_type;<\/p>\n

modify the original field names and type:
\nMySQL> the ALTER TABLE table_name the CHANGE old_field_name new_field_name field_type;<\/p>\n

delete fields:
\nthe MySQL the ALTER TABLE table_name the DROP FIELD_NAME;
\n5.9 modify the table name<\/p>\n

Command: rename table the original table to the new table name;<\/p>\n

for example: In Table MyClass name changed to YouClass
\nMySQL> MyClass to the rename the Table YouClass;<\/p>\n

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.<\/p>\n

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.<\/p>\n

RENAME TABLE are added in MySQL 3.23.23.<\/p>\n

 
\n6, the backup database<\/strong><\/p>\n

DOS command in the [url = file: \/\/ \\\\ mysql \\\\ bin] \\\\ mysql \\\\ bin [\/ url] to the next directory<\/p>\n

1. Export the entire database
\nexport files exist by default mysql \\ bin directory
\nmysqldump -u user name -p database name> exported file name
\nmysqldump -u USER_NAME -p123456 database_name> outfile_name.sql<\/p>\n

2. export a table
\nmysqldump -u username -p database name watches> export file name
\nmysqldump -u user_name -p database_name table_name> outfile_name.sql<\/p>\n

3. To export a database structure
\nthe mysqldump -u USER_NAME -p -d -add-drop-table database_name> outfile_name.sql
\n-d no data -add-drop-table before each create statement increases the Table a the drop<\/p>\n

4. with language parameter derivation
\nmysqldump -uroot -p -default-character-set = latin1 -set-charset = gbk -skip-opt database_name> outfile_name.sql<\/p>\n

example, aaa database backup to a file back_aaa in:
\n[root @ test1 root] # CD \/ Home \/ Data \/ MySQL
\n[root @ test1 MySQL] # the mysqldump -u root -p –opt AAA> back_aaa
\n7.1 Examples of building a database and build a table of 1<\/p>\n

drop database if exists school; \/\/ if there is deleted SCHOOL
\ncreate database school; \/\/ build libraries SCHOOL
\nuse School; \/\/ open the library SCHOOL
\nthe Create the Table Teacher \/\/ build table TEACHER
\n(
\nthe above mentioned id int (3) Not null Primary Key AUTO_INCREMENT,
\nchar name (10) Not null,
\naddress VARCHAR (50) default ‘rmohan’
\nyear a Date
\n); \/\/ build table end<\/p>\n

\/\/ Here is the insert field
\ninsert into teacher values ( “, ‘ allen’, ‘ one india rajesh’ ‘1976-10-10’);
\nINSERT INTO Teacher values ( ” ‘Jack’, ‘rajesh II’, ‘1975-12-23’);<\/p>\n

If you type the above command at mysql prompt can be, but not convenient debugging.
\n1, 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:
\nMySQL-uroot–p password <c: \\\\ school.sql
\nIf 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).<\/p>\n

2, or go to the command line using the mysql> source c: \\\\ school.sql; school.sql files can also be imported in the database.<\/p>\n

7.2 Examples of building a database and build a table of 2<\/p>\n

drop database if exists school; \/\/ if there is deleted SCHOOL
\ncreate database school; \/\/ build libraries SCHOOL
\nuse School; \/\/ open the library SCHOOL
\nthe Create the Table Teacher \/\/ build table TEACHER
\n(
\nthe above mentioned id int (3) Not null Primary Key AUTO_INCREMENT,
\nchar name (10) Not null,
\naddress VARCHAR (50) default ” rmohan ”,
\nyear a Date
\n); \/\/ build table end<\/p>\n

\/\/ Here is the insert field
\ninsert into teacher values ( ” ‘ ‘, ” john ” , ” in rmohan, a ”, ” 1976-10-10 ”);
\nINSERT INTO Teacher values ( ” ”, ” Jack ”, ” in rmohan, a ”, ” 1975-12- 23 ”);<\/p>\n

Note: under construction table
\n1, 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.<\/p>\n

2. Set the NAME field length of 10 characters<\/p>\n

3, the ADDRESS field length to 50 characters, and the default value of rmohan.<\/p>\n

4, YEAR set the date field.<\/p>\n","protected":false},"excerpt":{"rendered":"

Detailed MySQL basic commands<\/p>\n

Format: mysql -h host address -u user name -p User password<\/p>\n

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72,16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6322"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6322"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6322\/revisions"}],"predecessor-version":[{"id":6323,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6322\/revisions\/6323"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}