MySQL database tutorial eight easy words

As long as you master the following method, you can basically get the mysql database.
First, connect MYSQL format:
mysql-h host address
-u username
-p user password
1, Example 1: MySQL connected to this machine:
First open the DOS window, and then enter the directory mysqlbin, and then type the command mysql-uroot-p, press Enter and prompt you to lose the password, if you just installed MYSQL, super user root is no password, so enter directly to enter MYSQL, MYSQL prompt is: mysql>.
2, Example 2: MYSQL to connect to the remote host. Suppose the IP address of the remote host is: 110.110.110.110, user name is root, and password is abcd123. Type the following command: mysql -h110.110.110.110 -uroot -pabcd123.
3, exit the 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 root ab12. First enter the directory mysqlbin under DOS, then type the following command:
mysqladmin -uroot -password ab12
Note: Because root does not have a password at the beginning, the -p old password can be omitted.
2, Example 2: Then change the root password djg345:
mysqladmin -uroot -pab12 password djg345
Third, increase new users
(Note: different from the above, because the following is a MYSQL environment command, so the back with a semicolon as the command terminator)
format:
grant select on database. *
to username @ login host identified by "password"
Example 1, add a user test1 password abc, so that he can log in on any host, and all database query, insert, modify, delete permissions. First connect to MYSQL as the root user and type the following command:
grant select, insert, update,
delete on *. * to test1 @ "%" Identified by "abc";
However, the increased user of Example 1 is very dangerous, if you want someone know the password of test1, then he can log in your mysql database on any computer on the internet and you can do whatever you want, the solution See Example 2.
Example 2, add a user test2 password for abc, so that he can only log in on localhost, and the database mydb query, insert, modify, delete operation (localhost refers to the local host, that MYSQL database where the host) , So that even if users know the test2 password, he can not directly access the database from the internet, only through the MYSQL host web page to visit.
grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "abc";
If you do not want test2 password, you can call a command to eliminate the password.
grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "";
Note: You must first log in to MYSQL, the following operations are performed at the MYSQL prompt, and each command ends with a semicolon.
Fourth, operating skills
1, if you hit the command, enter and forget to add a semicolon, you do not have to repeat the command again, just hit a semicolon carriage on it. In other words you can put a complete command into a few lines to play, after the end of the semicolon mark for OK.
2, you can use the cursor up and down keys to recall the previous command. But before I used a MYSQL older version does not support. I am using is: mysql-3.23.27-beta-win.
Fifth, show the order
1, show the database list: show databases;
Just started two databases: mysql and test. Mysql library is very important It has MYSQL Based system information, we change the password and add users, in fact, is to operate with this library.
2, display the data table in the library:
use mysql;
// Open the library, learned FOXBASE will not be strange it
show tables
3, display the structure of the data table: describe table name;
4, building database: create database library name;
5, built table:
use
create table table name (field setting list);
6, delete the library and delete the table:
drop database
drop table
7, the records in the table emptied: delete from the table name;
8, show the record in the table: select * from table name;
Six, a database and build a table and insert data instance drop database if exists school; / / If SCHOOL is deleted
create database school
// Create library SCHOOL
use school
// open the library SCHOOL
create table teacher
// create table TEACHER
(
id int (3) auto_increment not null primary key,
name char (10) not null,
address varchar (50) default Shenzhen,
year date
); / / Build the end of the table
// The following is the insert field
insert into teacher valuess
(, glchengang, Shenzhen I, 1976-10-10);
insert into teacher valuess
(, jack, Shenzhen I, 1975-12-23);

Note: In the build table (1) set the ID to a number field of length 3: int (3) and have it increment automatically for each record: auto_increment and not null: not null and let him be the primary field primary key (2) Set NAME as a character field of length 10 (3) Set ADDRESS to a character field of length 50, and the default is Shenzhen. What is the difference between char and varchar, only to wait for later articles to say. (4) Set YEAR as the date field.
If you type the above command mysql prompt can, but not convenient debugging. You can write the above command as it is in a text file assuming school.sql, and then copied to c: and in the DOS state into the directory mysqlbin, and then type the following command:
mysql-uroot-p password If successful, a line without any show; if there is an error, there will be prompted. (The above command has been debugged, you only need to // remove the comment can be used).
Seven, the text data to the database
1, the text data should be in accordance with the format: tab separated by field data, null value instead of using n.
example:
3 rose Shenzhen two in 1976-10-10