There are many friends who have installed mySQL but I don't know how to use it. In this article, we will connect MySQL,
Change your password, add users, etc. to learn some of the common commands of MySQL.
First, connect mysql.
Format: mysql -h host address -u username -P user password
1, Example 1: Connect to MySQL on the unit.
First open the DOS window, then enter the directory mysqlin, then type the command mysql -uroot -p, back
After the car prompts you to lose your password, if you just install MySQL, the superuser root is no password, so it is directly
The car can enter mysql, MySQL prompts are: mysql>
2, Example 2: Connect to MySQL on the remote host. Suppose IPs of remote hosts: 110.110.110.110, users
Named root, the password is ABCD123. Type the following command:
mysql -h110.110.110.110 -uroot -Pabcd123
(Note: u and root do not have to bind the body, others are the same)
3, exit mysql command: exit (Enter)
Second, modify the password.
Format: mysqladmin -u username -P old password Password new password
1, Example 1: Add a password to the root AB12. First enter the directory mysqlbin under DOS, then type the following command
MySQLADMIN -UROOT Password AB12
Note: Because the root does not have a password, the -p old password can be omitted.
2, Example 2: Change the root password to DJG345.
mysqladmin -uroot -Pab12 Password DJG345
Another method:
Shell> mysql -u root -p
MySQL> Set Password for root = password ("root");
Third, add new users. (Note: Different from the above, because it is a command in the mysql environment,
A semicolon as the end of the command)
Format: Grant SELECT ON Database. * To Username @ Login Host Identified by "Password"
Example 1, add a user Test1 password to ABC, allowing him to log in on any host and have all databases
Query, insert, modify, delete permissions. First use the root user into MySQL, then type the following life
make:
Grant SELECT, INSERT, UPDATE, DELETE ON *. * to Test1 @ "%" Identified
BY "ABC";
However, the addition of the example 1 is very dangerous. You want someone to know the password of Test1, then he can
Log in to your MySQL database on any computer on the Internet and you can do whatever you want.
Solution Example 2.
Example 2, add a user TEST2 password to ABC, let it log in to localhost and you can
MYDB is inquiry, inserted, modified, and deleted (localhost refers to the local host, "MYSQL data
The host where the library is located, so that users can use the password knowing the test2, they cannot be straight from the Internet.
The access database can only be accessed through the web page on the MySQL host.
Grant SELECT, INSERT, UPDATE, DELETE ON MYDB. * to Test2 @ localhostiDentified by "abc";
If you don't want Test2 with a password, you can make a command to remove the password.
Grant SELECT, INSERT, UPDATE, DELETE ON MYDB. * to Test2 @ localhost
Identified by ""
About the database operations. Note: You must first log in to MySQL first, the following operations are in MySQL
The rule is carried out, and each command ends at a semicolon.
First, operation skills
1. If you play a command, then I found forget to add a semicolon after I entered the bus, and you don't need to re-play a command, as long as you have a semicolon.
The car is OK. That is to say, you can divide a complete command into a few lines, follow the semicolon ending
The logo is OK.
2, you can use the cursor upside button to call up the previous command. But I used a MySQL old version I have not supported.
I am using mysql-3.23.27-beta-win.
Second, display command
1. Display the database list.
Show databases;
Two databases are just started: mysql and test. MySQL library is very important to have mysql system information,
We change your password and add users, actually use this library to operate.
2, display the data table in the library:
Use mysql; // Open the library, I will not be unfamiliar with FoxBase.
Show table;
3, display the structure of the data sheet:
DESCRIBE table;
4, build the library:
CREATE DATABASE library name;
5, build the table:
USE library name;
CREATE TABLE Name (Field Settings List);
6, delete libraries and deleted forms:
DROP DATABASE library name;
DROP TABLE table name;
7. Record the table in the table:
DELETE FROM table name;
8, the record in the display:
SELECT * FROM table name;
Third, an example of a construction and construction form and insert data
DROP DATABASE IF EXISTS SCHOOL; / / If there is School, delete
Create Database School; // Establishment Library School
Use school; // Open the library SCHOOL
CREATE TABLE TEACHER / / Establishment TEACHER
(
ID INT (3) Auto_Increment Not Null Primary Key,
Name char (10) Not null,
Address Varchar (50) Default 'Shenzhen',
Year Date
); // End of the table
// The following is inserted in the field
INSERT INTO TEACHER VALUES ('', 'Glcheng', 'Shenzhen No.1 Middle ",' 1976-10-10 ');
INSERT INTO TEACHER VALUES ('', 'Jack', 'Shenzhen No.1 ",' 1975-12-23 ');
Note: in the form of the table
(1) Set the ID to a digital field with a length of 3: INT (3), and let it automatically add one: auto_increment,
It can't be empty: NOT NULL, and let it become the primary field Primary Key
(2) Set Name to a character field of length 10
(3) Set the Address to the character field of length 50, and the default is Shenzhen. What is the difference between VARCHAR and CHAR?
, Only the next article will be said. (4) Set the Year of Year to the date field.
If you type the above command on the MySQL prompt, it is not convenient to debug. You can order the above commands
Write into a text file to assume School.SQL, then copy to C: Under, and enter the directory in the DOS state
MySQLIN, then type the following command:
MySQL -UROOT -P password If successful, there is no display in a row; if there is a mistake, there will be prompts. (The above command has been debugged, you As long as the // comment is removed, you can use it). Fourth, transfer the text data into the database 1. The text data should match the format: The field data is separated with the Tab key, and the null value is used instead. example: 3 Rose Shenzhen 2nd 1976-10-10 4 MIKE Shenzhen No.1975-12-23 2. Data Introduction Command Load Data Local Infile "file name" INTO TABLE table name; Note: You'd better copy files to the mysqlin directory and you must first select the library where you want to select the table. V. Export and import data: (commands in the mysqlin directory of DOS) Export table MySQLDUMP --Opt School> School.sql Note: Remove all the tables in the database SCHOOL to the School.sql file, and School.sql is a text file, The file name is taken, open to see if you will have new discoveries. MySQLDUMP --Opt School Teacher Student> School.Teacher.student.sql Note: Bringing the Teacher Table and Student table in the database School to School.Teacher.Student.sql text Part, school.teacher.student.sql is a text file, file name, open and see if you have new discovery. Introduction table mysql Mysql> Create Database School; Mysql> Use school; mysql> Source School.sql; (Or change School.SQL to School.Teacher.SQL / SCHOOL.TEacher.student.sql) Export database MySQLDUMP - Databases DB1 DB2> DB1.DB2.SQL Note: Back up database DBL and DB2 to db1.db2.sql file, db1.db2.sql is a text file, file name Cut, open and see if you have new discovery. (for example: MySQLDUMP -H HOST -U User -p Pass --Databases DBNAME> file.dump That is to import the name User, password PASS on Host into the file file.dump. ) Import database MySQL Copy database MySQLDUMP - AlL-Databases> All-databases.sql Note: Bet all databases to all-databases.sql files, all-databases.sql is a text file, The file name is all. Import database mysql mysql> DROP DATABASE A; mysql> DROP DATABASE B; mysql> DROP DATABASE C; ... MySQL> Source All-Databases.SQL; (or EXIT Exit MySQL after mysql In fact, MySQL's operations in the database are similar to other SQL databases. You'd better find this book of SQL. Look. I only introduce some basic in here. In fact, I only know these, huh, huh. The best mysql tutorial is also a "mysql Chinese reference manual" translated by "子". Not only free, every related website has Download, and it is the most authoritative. Unfortunately, it is not like "PHP4 Chinese Manual", which is the format of CHM, in the lookup function command It is not convenient. 3. Open the database: Use dbname; Show all databases: show data; Display database mysql all tables: first use mysql; then show tables; Listing information of the table: Describe user; (Display the information of the User table in the table mysql database); 4. Create a complete super user that can connect to the server anywhere, but must use a password Something to do this Grant all privileges on *. * To monty @ localhost iDentified by 'Something' with grant option; Grant all privileges on *. * To monty @ "%" Identified by 'Something' with grant Option; 5. Delete Authorization: Revoke all privileges on *. * From root @ "%"; Use mysql; Delete from user where user = "root" and host = "%"; Flush privileges; 6. Create a user Custom Login in a specific client weiqiong.com, access a specific database BankAccount Mysql> Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON BankAccount. * TO Custom@weiqiong.com Identified by 'stupid' 7. Rename the table: ALTER TABLE T1 RENAME T2; In order to change the column A, change from Integer to Tinyint Not Null (name), And change column B, change from char (10) to char (20), and rename it, from b to C: Alter Table T2 Modify A Tinyint Not Null, Change B C Char (20); Add a new TimeStAMP column, named D: ALTER TABLE T2 Add D TimeStamp; Add an index to column D and make the column A as the primary key: ALTER TABLE T2 Add Index (D), Add Primary Key (A); Delete column C: ALTER TABLE T2 DROP COLUMN C; Add a new auto_increment integer column, name C: ALTER TABLE T2 Add C Int Unsigned Not Null Auto_Increment, Add Index (C); Note that we indevert C because the auto_increment column must be indexed, and we declare that C is NOT NULL, because the indexed column cannot be NULL. 8. Delete record: Delete from t1 where c> 10; 6. Change a few lines: Update T1 Set User = weiqiong, password = weiqiong; 7. Create an index using the head 10 characters of the Name column: CREATE INDEX Part_OF_NAME ON Customer (Name (10)); Full text
