MySQL Database learning notes

Login MySQL:

Mysql-h localhost-u root-ptest Password must have no space before you re-enter the password

Mysql-h110.110.110.110-u Root-p 123; (Note: There is no space between you and root, and so are the others.)

MySQL Commands collection

One, the connection MySQL.

Format: mysql-h host address-u user name-P user Password

1, connected to the MySQL on the computer.

First open the DOS window, and then enter the directory Mysqlbin, and then type the command Mysql-u root-p, after the carriage return prompts you to lose

Password. Note that before the user name can have spaces or no spaces, but the password must have no space before, or let you lose again

into the password.

If you have just installed MySQL, superuser root is no password, so direct return can enter into MySQL, MySQL

The prompt is: mysql>

2, connect to the remote host MySQL. Assume 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: There is no space between you and root, and so are the others.)

）

3, exit MySQL command: Exit (enter)

Second, modify the password.

Format: Mysqladmin-u username-P Old password password new password

1, to root add a password ab12. First enter the directory Mysqlbin in DOS, and then type the following command

Mysqladmin-u Root-password AB12

Note: Since Root does not have a password at the beginning, the-p old password can be omitted.

2, and then the root of the password changed to djg345.

Mysqladmin-u root-p ab12 Password djg345

Third, add new users.

(Note: Unlike the above, the following are the commands in the MySQL environment, so a semicolon is followed by a command knot.)

Shing)

Format: 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, plug

Permissions to enter, modify, and delete. First connect to MySQL with root user, and then type the following command:

Grant Select,insert,update,delete on *.* to [email=test1@ "%]test1@"%[/email] "

Identified by "ABC";

But the added user is very dangerous and you want someone who knows Test1 's password so he can be on the Internet

Any computer that logs on to your MySQL database and can do whatever you want with your data, see resolution 2.

2, add a user test2 password for ABC, so that he can only log on localhost, and can be mydb to the database

Query, insert, modify, delete operation (localhost refers to the local host, that is, the host of the MySQL database),

This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through the MySQL master

The Web page on the machine is accessed.

Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]

Test2@localhost[/email] identified by "ABC";

If you do not want to test2 the password, you can make another command to eliminate the password.

Grant Select,insert,update,delete on mydb.* to [Email=test2@localhost]

Test2@localhost[/email] identified by "";

The next article I am MySQL on the database aspects of the operation. Note: You must first log in to MySQL, the following actions are

MySQL prompts, and each command ends with a semicolon.

First, the Operation skill

1, if you hit the command, when you find that you forgot to add a semicolon, you do not need to repeat the command, as long as a semicolon return can be

With a.

That is to say, you can divide a complete command into lines, and then use a semicolon as the closing sign to make it OK.

2, you can use the cursor up and down keys to pull up the previous command.

Second, display command

1. Display the list of databases in the current database server:

Mysql> show DATABASES;

Note: MySQL library has MySQL system information, we change the password and new users, is actually used this library for the exercise

For

2, display the data table in the database:

mysql> use library name;

Mysql> show TABLES;

3, display the structure of the data table:

mysql> DESCRIBE table name;

4, the establishment of a database:

mysql> CREATE database name;

5, the establishment of data tables:

mysql> use library name;

mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));

6, delete the database:

mysql> DROP Database library name;

7, delete the data table:

mysql> DROP table name;

8. Empty the record of the table:

Mysql> DELETE from table name;

9, display the records in the table:

Mysql> SELECT * from table name;

10. Insert records into the table:

mysql> INSERT into table name VALUES ("HyQ", "M");

11, update the data in the table:

mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';

12. Loading data into the data table by means of text:

mysql> LOAD DATA Local INFILE "d:/mysql.txt" to table name;

13, Import. sql File command:

mysql> use database name;

Mysql> SOURCE D:/mysql.sql;

14, the command line to modify the root password:

mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';

mysql> FLUSH privileges;

15, display the use of the database name:

Mysql> SELECT DATABASE ();

16, display the current user:

Mysql> SELECT USER ();

Example of 三、一个 and table-building and inserting data

Drop database if exists school; Delete if there is school

Create Database School; Build 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 (a) NOT NULL,

Address varchar default ' Shenzhen ',

Year Date

); Build Table End

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′");

If you type the above command at the MySQL prompt, it's not easy to debug.

(1) You can write the above command as is in a text file, suppose to be school.sql, and then copy to C:\,

and enter the directory [Url=file://\mysql\bin]\mysql\bin[/url] in DOS, and then type the following command:

Mysql-uroot-p Password < C:\school.sql

If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged, you only have to be//

Note removed for use).

(2) or after entering the command line, use mysql> source c:\school.sql; You can also import the School.sql file

Database.

Iv. transferring text data to a database

1, the text data should conform to the format: The field data is separated by the TAB key, null value with [url=file://\n]\n

[/URL] To replace the. Example:

3 Rose Dalian II, 1976-10-10

4 Mike Dalian One 1975-12-23

Suppose you save these two sets of data as school.txt files and place them in the C-packing directory.

2, Data incoming command load infile "c:\school.txt" into table name;

Note: You'd better copy the files to the [Url=file://\mysql\bin]\mysql\bin[/url] directory and

Use the using command to hit the library where the table is located.

V. BACKUP DATABASE: (command executes in DOS [Url=file://\mysql\bin]\mysql\bin[/url] directory)

）

1. Export the entire database

The export file defaults to the existence of the Mysqlbin directory

Mysqldump-u user name-p database name > exported file name

Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2. Export a table

Mysqldump-u user name-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. With language parameter export

Mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt

database_name > Outfile_name.sql

---------------------------------------------------------------------------------

---------

Data type tinyint smallint no varchar2, only char and varchar used to store strings

-------

Composite type

Enum multiple-Select an enumeration type

Set multiple selection

-------

<=> null-safe equals regexp or rlike regular expression matching

Aggregate functions (grouped functions Oracle)

Trim (str) Removes the space replace (str). SRCHSTR,RPLCSTR) replaces the string str with a string rplcstr

All occurrences of the character Srchstr.

There can only be one database instance in Oracle, and MySQL has multiple

MySQL default transaction autocommit set autocommit=0;

Create DATABASE Zhang;

Use Zhang;

Show tables;

CREATE TABLE test (ID int primary KEY auto_increment,name varchar (15));

Select Now () does not have to be not complete the present () current time

The maximum length is automatically intercepted when the data exceeds the maximum length.

Decima the default decimal (10,0) When the argument is not written

MySQL ignores case, insensitive binary binary matches exactly where binary name= ' Zhang '

INSERT into test values (), (), () insert multiple rows at the same time.

Time does not give the symbol to look forward from the back, to the timing of the given is stored.

NULL when datetime NULL

Timestamp to system time when null P85

-----

Operator

null+ any number of =null

Divisible by any value to 0 is null

Can be converted to the number of the conversion, can not be intercepted. Cut from the beginning, the words cannot be 0.

' A ' = ' a ' 1 binary ' a ' = ' a ' 0

Converts a string to a number as long as one side is a number.

Null is returned as long as there is null participation. Null=null NULL NULL is NULL to 1

The front space cannot be saved, the back can be. Null in (2,NULL) is a null null<=>null of 1 2<=>null

is 0

regexp ^ expression with what opening; ^ham

MD5 () Irreversible encryption algorithm

Group_concat () P121 combines additional information with the same attribute Abc,def,ghi

SQL scripts

SOURCE D:1.sql Execute SQL script

Paging select * FROM table name limit 0,10 starting from 0, take 10 records---count from zero.