MySQL Common commands

1, connected to MySQL format: mysql-h host address-u user name-p user password 1, connected to the local MySQL. First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have a space or no space, but before the password must have no space, or let you re-enter the password. If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>2, connected to the remote host on the MySQL. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command: Mysql-h110.110.110.110-u root-p 123; (Note: You can not add a space between the root, the other is the same) 3, quit MySQL command: Exit (enter) 2, change the password format: mysqladmin-u Username-p Old password password new password 1, add a password to root ab12. First enter directory Mysql\bin 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 djg3453, adding new users Note: Unlike the above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as the command Terminator format: The 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 to MySQL with the root user, and then type the following command: Grant Select,insert,update,delete on *. * To[email protected]"%" identified by "ABC"; but the increase in the 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 on to 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]Identified by "ABC"; If you do not want to test2 have a password, you can use another command to erase the password. Grant Select,insert,update,delete on mydb.* to[email protected] Identified by ""; 4.1 Creating a Database Note: Before you create a database, connect to the MySQL server command: CREATE DATABASE < database name > Example 1: Create a db named Xhkdb   mysql> Create databases XHKDB; Example 2: Create a database and assign user ①create database name; ②grant select,insert,update,delete,create,drop,alter on database name. * To Database name @localhost identified by ' password '; ③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password '); Execute 3 commands to complete database creation. Note: The Chinese "password" and "database" are the user's own needs to set. 4.2 Show database commands: Show databases (note: Finally there is a s) mysql> show databases; note: To modify the default encoding of the database in order to not display the garbled characters. The following is an example of the GBK encoding page: 1, modify the MySQL configuration file: My.ini inside Modify DEFAULT-CHARACTER-SET=GBK2, Code Runtime Modification:  ①java code: jdbc:mysql:// LOCALHOST:3306/TEST?USEUNICODE=TRUE&AMP;CHARACTERENCODING=GBK  ②php Code: Header ("content-type:text/html; charset=gb2312 ");  ③c language code: int mysql_set_character_set (mysql * mysql, char * csname); This function is used to set the default character set for the current connection. The string csname specifies 1 valid character set names. Connection proofing becomes the default proofing for character sets. This function works similarly to the set names statement, but it also sets the value of mysql-> CharSet, which affects the character sets set by the Mysql_real_escape_string (). 4.3 Delete database command: Drop databases < database name > For example: Delete a database named Xhkdb mysql> drop DB xhkdb;Sub 1: Delete a database that has been determined to exist   mysql> drop DB drop_database;   Query OK, 0 rows Affected (0.00 sec) Example 2: Delete an indeterminate database drop_database mysql> drop   ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist      //error occurred, cannot delete ' drop_database ' databases, the database does not exist.   mysql> drop database if exists drop_database;   Query OK, 0 rows affected, 1 Warning (0.00 sec)//generates a warning stating that this database does not exist   mysql> CREATE database drop_database;   Query OK, 1 row Affected (0.00 sec)   mysql> drop database if exists drop_database;//if exists determine if the databases exist, do not save Do not generate error   Query OK, 0 rows Affected (0.00 sec) 4.4 Connection Database command: Use < database name > For example: If the XHKDB database exists, try to access it:   mysql> US e xhkdb; ScreenTip: The database Changeduse statement can advertise that MySQL uses the Db_name database as the default (current) database for subsequent statements. The database remains as the default database until the end of the segment, or until a different use statement is published:   mysql> use DB1;   mysql> SELECT COUNT (*) from mytable;   # selects from db1.mytable   mysql> use DB2;   MYSQL&GT; SELECT COUNT (*) from mytable;   # selects from db2.mytable using the USE statement to mark a specific current database does not prevent you from accessing tables in other databases. The following example accesses the author table from the DB1 database and accesses the edit table from the DB2 database:   mysql> use DB1;   mysql> SELECT author_name,editor_name from author,db2.editor       →       &NB Sp WHERE author.editor_id = db2.editor.editor_id; The USE statement is set up to be compatible with Sybase. Some netizens asked, how to quit after the connection. In fact, you do not have to quit, use the database, using show databases can query all the database, if you want to jump to another database, with   use other database name. 4.5 The currently selected database command:mysql> select Database (); The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL? 1. Show MySQL version mysql> select version (); +-----------------------+ | Version ()             | +-----------------------+ | 6.0.4-alpha-community | +-----------------------+ 1 row in Set (0.02 sec) 2. Show Current Time mysql> select Now (); +---------------------+ | Now ()               | +---------------------+ | 2009-09-15 22:35:32 | +---------------------+ 1 row in Set (0.04 sec) 3. Display Month Day Select DayOfMonth (current_date); +--------------------------+ | DayOfMonth (current_date) | +--------------------------+ |                       15 | +--------------------------+ 1 row in Set (0.01 sec)  select MONTH (current_date); +---------------------+ | MONTH (current_date) | +---------------------+ |                   9 | +---------------------+ 1 row in Set (0.00 sec)  select Year (current_date); +--------------------+ | Year (current_date) | +--------------------+ |               2009 | +--------------------+ 1 row in Set (0.00 sec) 4. Display string mysql> Select "Welecome to my blog!"; +----------------------+ | Welecome to my blog! | +----------------------+ | Welecome to my blog! | +----------------------+ 1 row in Set (0.00 sec) 5. When calculator with select ((4 * 4)/10) + 25; +----------------------+ | ((4 * 4)/10) +25 | +----------------------+ |                26.60 | +----------------------+ 1 row in Set (0.00 sec) 6. Threaded string Select CONCAT (F_name, "", L_name) as name from Employee_data where title = ' Marketing Executive '; +---------------+ | Name          | +---------------+ | Monica Sehgal | | Hal Simlai    | | Joseph Irvine | +---------------+ 3 rows in Set (0.00 sec) Note: the concat () function is used here to string strings together. In addition, we used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym. 5.1 Creating a data Table command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]); For example, to establish a table named MyClass, field name numeric type data width is empty whether the primary key automatically increases the default value ID int 4 No primary key auto_increment &nbsp ; Name Char 20 no       sex int 4 no     0 degree double 16 is       mysql> Create t Able MyClass (> ID int (4) NOT null primary key auto_increment,> name char (NO) null,> sex int (4) NOT NULL Defau Lt ' 0 ',> degree double (16,2)); 5.3 Delete data Table command:DROP table < table name > For example: Delete table named MyClass   mysql> drop table Myclass;drop table is used to cancel one or more tables. You must have drop permissions for each table. All table data and table definitions are canceled, so use this statement with caution! Note: For a partitioned table, the DROP table permanently cancels the tables definition, cancels the partitions, and cancels all the data stored in those partitions. DROP table also cancels the partition definition (. par) file that is associated with the canceled table. For tables that do not exist, use the if exists to prevent errors from occurring. When using the if exists, a note is generated for each table that does not exist. Restrict and cascade can make partitioning easier. Currently, restrict and cascade do not work. 5.4 Table Insert Data command: INSERT into < table name > [(< Field name 1>[,.. < field name n>])] VALUES (value 1) [, (value N)] For example: Two records are inserted into table MyClass, two records indicate: 1 is named Tom with a score of 96.45, 2 is named Joan, the score is 82.99, and the name is 3. Wang's score was 96.5.   mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59); NOTE: insert Into can only insert one record into the table at a time. 5.5 Query table Data 1), query All Rows command: Select < Field 1, field 2,...> from < table name > where < expression > Example: View all data in table MyClass   MYSQL&G T SELECT * from myclass;2), query the first few rows of data such as: View Table MyClass in the first 2 rows of data mysql> SELECT * FROM MyClass ORDER by ID limit 0,2;select general mate where Use to query for more accurate and complex data. 5.6 Delete data in table command: Delete from table name where expression for example: Delete records in table MyClass 1 mysql> Deletefrom MyClass where id=1; Here is a comparison of the table before and after the deleted data. Firstnamelastnameagepeter Griffin Glenn Quagmire 33 The following example removes all the Persons ' Lastname= ' records from the "Griffin" table in PHP code:

After this deletion, the table is like this: Firstnamelastnameageglenn Quagmire 33 5.7 Modify data syntax in table: Update table name SET field = new value,... where condition   mysql> update M Yclass set name= ' Mary ' where id=1; Example 1: Single table MySQL UPDATE statement:   Update [low_priority] [IGNORE] Tbl_name set col_name1=expr 1 [, COL_NAME2=EXPR2 ...] [WHERE Where_definition] [ORDER by ...] [LIMIT Row_count] Example 2: Multiple table UPDATE statement: Update [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2= Expr2 ...] [WHERE Where_definition] The update syntax can update the columns in the original table row with the new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used to limit the number of rows that can be updated, given a limit value. 5.8 Add field command: ALTER TABLE name add field type other; For example: Added a field passtest in table MyClass, type int (4), default value 0   mysql> ALTER TABLE MyClass AD d passtest Int (4) default ' 0 ' plus index   mysql> ALTER TABLE name add index name (field name 1[, field Name 2 ...]); Example: mysql> ALTER TABLE employee ADD index emp_name (name); Add primary keyword Indexes  mysql> ALTER TABLE name add primary key (field name) ; Example: mysql> ALTER TABLE employee ADD primary key (ID); plus uniqueIndex of Constraints   mysql> ALTER TABLE name add unique index name (field name); Example: mysql> ALTER TABLE employee add unique emp_name2 (Cardn umber) Delete an index   mysql> ALTER TABLE name DROP index name; example: Mysql>alter table employee DROP index emp_name; add field: my Sql> ALTER TABLE table_name ADD field_name Field_type; Modify the original field name and type:mysql> ALTER TABLE table_name change Old_field_nam e new_field_name field_type; Delete field: MySQL ALTER TABLE table_name drop field_name;5.9 Modify Table name command: Rename table name to new name; for example: in table Mycl The name changed to Youclass   mysql> rename table MyClass to Youclass; When you execute rename, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table. If MySQL encounters any errors in the multi-table renaming, it reverses all renamed tables, returning everything to its original state. RENAME TABLE was added to MySQL 3.23.23. 6, BACKUP DATABASE command in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory execution 1. Export the entire database export file by default is present in the Mysql\bin directory     Mysqldump-u user name-p database name > exported file name    mysqldump-u user_name-p123456 database_name > OUTFILE_NAME.SQL2. Export a Table   &NBSP;MYSQLDUMP-U user name-P database Name table name &GT Exported file names   &NBSP;MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL3. Exporting a database structure   &NBSP;MYSQ Ldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql    -d No data –add-drop-table in each creat Add a drop table4 before the E statement. With language parameter export    mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql For example, back up the AAA library to a file back_aaa: [[email protected] root]# cd/home/data/mysql [[Email  protected] mysql]# mysqldump-u root-p--opt AAA > back_aaa7.1 An example of building a library and building a table 1drop database if exists school; Delete Create Database school if school is present; Establish library Schooluse School; Open Library Schoolcreate table teacher//Build tables Teacher (   id int (3) auto_increment NOT NULL primary key,    nam e Char (TEN) not NULL,    address varchar () Default ' Shenzhen ',    year date); Build table End//Following insert field inserts into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ '); insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′); If you type the above command at the MySQL prompt, you can, but it is not easy to debug. 1, you can write the above command as is written in a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url], Then type the following command:    mysql-uroot-p Password < c:\\school.sql If successful, empty a row without any display, if there is an error, you will be prompted. (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. 7.2 An instance of building and building a table 2drop database if exists school; Delete Create Database school if school is present; Establish library Schooluse School; Open Library Schoolcreate table teacher//Build tables Teacher (   id int (3) auto_increment NOT NULL primary key,    nam e Char (TEN) not NULL,    address varchar (() Default ' Shenzhen ',    year date); Build table End//following inserts into teacher values (' ' ', ' ' Glchengang ', ' ' Shenzhen One ', ' 1976-10-10 '); insert into teacher values (' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 '); note: In the table 1, set the ID to a number field of length 3: Int (3); and let it automatically add one to each record: auto_increment; NOT null , and let him become the main field primary key. 2. Set name to Character field 3 with length 10, set address as character field of length 50, and the default value is Shenzhen. 4. Set year as the Date field.

MySQL Common commands