1, problem one: Use update mysql.user set password= ' root ' change password, cannot log in
WORKAROUND: The operating procedure is as follows.
(1) Close MySQL (Kill the mysqld process), then use the command:
Mysqld--skip-grant-tables
(2) Start the database, you can connect the normal
(3) then clear the Rootpassword:
Update Mysql.user Set Password=null
(4) Start the database using the command mysqld
Once you've done this, you'll be able to log in normally.
2. Problem two: MySQL Error 1130 Host ' localhost ' is not allowed to connect to this MySQL server
Workaround: authorize with authorization statement
GRANT all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT OPTION ;
Give root authorization for local access.
References:
1. Connect MySQL format: mysql-h host address-uusername-p user password
1. Connect to MySQL on this machine.
First open the Dos form. Then go to Folder Mysql\bin. Then type the command Mysql-u root-p, enter after the prompt you to lose password. Note that before username can have a space can also have no space, but before password must have no space, otherwise let you enter password again.
Assuming that MySQL has just been installed, Superuser root is not password. So you can enter the direct return to MySQL, MySQL prompt is: mysql>
2. Connect to MySQL on the remote host.
If the IP of the remote host is: 110.110.110.110. Username is Root,password for abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add spaces between the root and the other)
3. Exit MySQL command : Exit (Enter)
2, Change password format: mysqladmin-uusername-p old password Password new password
1, add a passwordab12 to root.
First enter the folder Mysql\bin under DOS. Then type the following command
Mysqladmin-u Root-password AB12
Note: Since root is not password at the beginning, the-p old password can be omitted.
2, then the root of the password changed to djg345.
Mysqladmin-u root-p ab12 Password djg345
3. Add a new userNote: Different from the above. The following are the commands in the MySQL environment, so they are followed by a semicolon as a command terminator
Format: Grant Select on database. * to [email protected] Login host identified by "password"
1, add a user Test1password for ABC, so that he can log on regardless of the host, and all the database has query, insert, change, 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] '%][email protected] '%[/email] ' identified by ' ABC ';
But the added user is very critical. If you want someone to know Test1 's password, then he'll be able to log into your MySQL database on any computer on the Internet and be able to do whatever you like with your data. The solution is described in 2.
2, add a user Test2password for ABC, let him only be able to login on localhost, and be able to query, insert, change, delete the database mydb (localhost refers to the local host, that is, the MySQL database host) , so that the user knows test2 password. He was also unable to access the database directly from the Internet. It is only possible to access the Web pages on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "ABC";
Suppose you don't want to test2 have password. Be able to make another command to eliminate password.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "";
4.1 Creating a DatabaseNote: Before you create a database, you must first connect MySQLServer
Command: Create databases < database name >
Example 1: Establishing a database named Xhkdb
mysql> CREATE DATABASE xhkdb;
Example 2: Creating a database and assigning users
①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 ');
Run 3 commands in sequence to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set.
4.2 Display Database command: Show databases (note: There is a last s)
mysql> show databases;
Note: To change 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, change the MySQL configuration file:My.ini inside Changes DEFAULT-CHARACTER-SET=GBK
2, code execution changes:
①java Code: JDBC:MYSQL://LOCALHOST:3306/TEST?USEUNICODE=TRUE&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. The function works in a similar way to the set names statement. But it can also set the value of mysql-> CharSet. This affects the character sets set by Mysql_real_escape_string ().
4.3 Delete database command: Drop database < DB name >
For example, delete a database named Xhkdb
mysql> drop Database xhkdb;
Example 1: Delete a database that has been determined to exist
mysql> drop Database drop_database;
Query OK, 0 rows Affected (0.00 sec)
Example 2: Deleting a database that is not sure of being present
mysql> drop Database drop_database;
ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist
Error occurred, cannot delete ' drop_database ' database, 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 infers whether the databases exist, does not exist, and does not produce errors
Query OK, 0 rows Affected (0.00 sec)
4.4 Connect Database command: Use < database name >
For example: Suppose the XHKDB database exists, try to access it:
mysql> use XHKDB;
Screen tip: Database changed
The
Use statement can advertise that MySQL uses the Db_name database as the default (current) database for a possible statement. The database remains 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> SELECT COUNT (*) from mytable; # selects from db2.mytable
Using the USE statement to mark a specific current database does not prevent you from visiting tables in other databases.
The following example is able to access the author table from the DB1 database and to interview the edit table from the DB2 database:
mysql> use DB1;
Mysql> SELECT Author_name,editor_name from Author,db2.editor
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 after using the database. Use show databases to query all databases, false assumptions to jump to other databases, with
Use other database names
will be able to.
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. Display the MySQL version number
Mysql> select version ();
+-----------------------+
| Version () |
+-----------------------+
| 6.0.4-alpha-community |
+-----------------------+
1 row in Set (0.02 sec)
2. Display the current time
Mysql> Select Now ();
+---------------------+
| Now () |
+---------------------+
| 2009-09-15 22:35:32 |
+---------------------+
1 row in Set (0.04 sec)
3. Display Date
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 the calculator uses
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 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>]);
Like what. Create a table named MyClass,
| Field name |
Number Type |
Data width |
is empty |
Whether the primary key |
Add your own initiative |
Default value |
| Id |
Int |
4 |
Whether |
Primary key |
Auto_increment |
|
| Name |
Char |
20 |
Whether |
|
|
|
| Sex |
Int |
4 |
Whether |
|
|
0 |
| Degree |
Double |
16 |
Is |
|
|
|
Mysql>
CREATE TABLEMyClass (
> ID int (4) NOT NULL
PRIMARY KEY Auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT NULL
default' 0 ',
> Degree double (16,2)); 5.3 Delete data Table command: DROP table < table name >
For example, delete a 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 will permanently cancel the tables definition and cancel the partitions. and cancel all the data stored in these 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. For each table that does not exist, a note is generated.
Restrict and cascade can make partitioning easier. For now, restrict and cascade don't work.
5.4 Table Insert Data command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
For example: Insert two records into the table MyClass, these two records indicate: 1 is named Tom's score is 96.45, 2 is named Joan, the result is 82.99, the number of 3 named Wang is 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 a single record into the table at a time.
5.5 Querying the data in a table
1), query all rows
Command: Select < Field 1, field 2,...> from < table name > where < expression >
For example: View all data in table MyClass
Mysql> select * from MyClass;
2), query the first few rows of data
For example: View the first 2 rows of data in table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Select is typically used with where to query for more accurate and complex data.
5.6 Delete Table Data command: Delete from table name where expression
For example, delete the record numbered 1 in table MyClass
Mysql> Delete from MyClass where id=1;
The following is a comparison of the table before and after deleting the data.
| FirstName |
LastName |
| Age
| Peter |
Griffin |
35 |
| Glenn |
Quagmire |
33 |
The following is an example of PHP code to delete all Lastname= ' Griffin ' records in the "Persons" table:
<?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 Alter DATA syntax in table: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;
Example 1: MySQL UPDATE statement for single table:
UPDATE [low_priority] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [ORDER by ...] [LIMIT Row_count]
Example 2: UPDATE statement for multiple tables:
UPDATE [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]
The update syntax allows you to update the columns in the original table row with the new values.
The SET clause indicates which columns to change and which values to give. The WHERE clause specifies which rows should be updated. Assuming there is no WHERE clause, all rows are updated.
Assuming an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used for a given limit value. Limit the number of rows that can be updated.
5.8 Add Field command:
ALTER TABLETable name
Addfield type Other;
For example, a field passtest is added to the table MyClass, and the type is int (4). The default value is 0
Mysql>
ALTER TABLEMyClass
AddPasstest
Int (4
) Default' 0 '
Gazzo Primer
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);
Example: mysql> ALTER TABLE employee ADD index emp_name (name);
index of the main keyword
Mysql> ALTER TABLE name add primary key (field name);
Example: mysql> ALTER TABLE employee ADD primary key (ID);
index with unique restriction criteria
Mysql> ALTER TABLE name add unique index name (field name);
Example: mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);
Delete an index
mysql> ALTER TABLE name DROP INDEX name;
Example: Mysql>alter table employee DROP index emp_name;
To
Add a field:
mysql> ALTER TABLE table_name ADD field_name Field_type;
Change the name and type of the original field:
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;
To
delete a field:
MySQL ALTER TABLE table_name DROP field_name;5.9 changelog 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;
When you run RENAME, you cannot have a locked table or an active transaction.
You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.
Assume that in a multi-table renaming. MySQL encounters no matter what the error. It reverses the name of all renamed tables, returning everything to its original state.
RENAME TABLE was added in MySQL 3.23.23.
6. Backup DATABASE command runs under DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Folder
1. Export the entire database
The export file defaults to the existence of the Mysql\bin folder
Mysqldump-u username-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 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 references
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 file Back_aaa:
[Email protected]1 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; If school is present, delete
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′ ');
Assume that you type the above command at the MySQL prompt as well. But it is not easy to debug.
1. You can write the above command as it is in a text file. If it is school.sql, then copy it to c:\\ and enter the folder [Url=file://\\mysql\\bin]\\mysql\\bin[/url] in the DOS state. Then type the following command:
Mysql-uroot-ppassword < C:\\school.sql
If successful, empty a row without any display, or if there is an error. There will be a hint.
(The above command has been debugged, you just have to put//the gaze is removed and can be used).
2, or enter the command line after using mysql> source C:\\school.sql; The School.sql file can also be imported into the database.
7.2 An instance of building and building a table 2drop database if exists school; If school is present, delete
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 (' ' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');
Note: In the table under construction
1. Set the ID to a numeric field of length 3: Int (3). And let it every record of its own initiative plus one: auto_increment. Cannot be empty: NOT NULL, and let him be the main field primary key.
2. Set name to a character field of length 10
3. Set address as the character field of length 50, and the default value is Shenzhen.
4. Set year as the Date field.
MySQL Login Issues
