MySQL Database parsing

Brief introduction

MySQL is an open source small relational database management system, the developer of the Swedish MySQL AB company. MySQL is now widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database.

System Features　

1. Use C and C + + written, and use a variety of compilers to test, to ensure the portability of the source code;

2. Support for AIX, FreeBSD, HP-UX, Linux, Mac OS, Novellnetware, OpenBSD, Os/2 Wrap, Solaris, Windows and many other operating systems;

3. Provides APIs for a variety of programming languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.

4. Support multi-threading, make full use of CPU resources;

5. Optimized SQL query algorithm to improve query speed effectively;

6. Can be used as a separate application in the Client server network environment, but also as a library and embedded in other software;

7. Multi-language support, common encoding such as Chinese GB 2312, BIG5, Japanese shift_jis, etc. can be used as data table name and data column name;

8. Provide TCP/IP, ODBC, JDBC and many other database connection paths;

9. Provide management tools for managing, inspecting, and optimizing database operations;

10. Support for large databases. A large database with thousands records can be processed;

11. Supports multiple storage engines.

Basic commands

--Create a database

Mysql> CREATE DATABASE name

--Create a table

Mysql> CREATE TABLE Table name (

Column name (ID) type (int (4)) primary key (define primary key) Auto_increment (description self-increment),

......，

）；

--View all databases

mysql> show databases;

--Using a database

mysql> use database name;

--View all tables under the database you are using

Mysql> Show tables;

--Display the property structure of the table

mysql> desc table name;

--Select the display of the data in the table

--* represents the selection of all columns,

Mysql> select * FROM table name where id=? [and Name=? ] [or name=?];

Mysql> Select Id,name from table name order by a column name desc (descending, ASC Ascending)

--delete data from a table

mysql> Delete from table where id=? [or Name=? (and name=?)];

--Delete Table

mysql> drop table;

--Delete Database

mysql> drop database;

Beginner's Guide

One, connect MySQL folding

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

1. Example 1: Connect to MySQL on this computer.

First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is:mysql>

2. Example 2: Connect to MySQL on a remote host. 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-uroot-pabcd123

(Note: You and root can be used without spaces, others are the same)

3. Exit MySQL command: Exit (enter)

Note: To successfully connect to a remote host, you need to open MySQL remote access permission on the remote host

Here's how:

Enter as an administrator in the remote host

Enter the following command

Mysql>grant all privileges on *. * to ' Agui ' @% ' identifiedby ' 123 ' with GRANT OPTION;

FLUSH privileges;

Give any host access to data

Mysql>flush Privileges

Changes take effect

Agui user name used for us

Password is 123

That is, on the remote host to make good settings, we can connect to the remote host through the mysql-h110.110.110.110-uagui-p123

Second, change the password

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

1. Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and 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 to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

Third, add new users

(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)

Format: Grant Select on database. * To User name @ login host identified by "password"

Example 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, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

But example 1 increases 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 your MySQL database and to your data can do whatever, workaround see Example 2.

Example 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 [e-mail protected] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

Here's a look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.

Four, the Operation skill

1. If you hit the command, you find that you forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.

2. You can use the cursor up and down keys to bring up previous commands. But previously I used an old version of MySQL that was not supported. I'm using Mysql-3.23.27-beta-win now.

V. Display of commands

1. Displays the list of databases.

show databases;

Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.

2. To display the data tables in the library:

use MySQL;//Open the library, learn foxbase must not be unfamiliar with it

Show tables;

3. Show the structure of the data table:

describe table name;

4. Build Library:

Create database name;

5. Build table:

Use library name;

CREATE TABLE table name (field settings list);

6. Delete the library and delete the table:

drop database name;

drop table name;

7. Empty the records in the table:

Delete from table name;

8. Show the records in the table:

SELECT * from table name;

9. Displays errors, warnings, and notifications from the last statement executed:

Show warnings;

10. Displays only the errors resulting from the last execution statement:

Show errors;

Vi. examples

Drop database if exists school; Delete if school is present

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,

Ame 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 ', ' xx company ', ' 1976-10-10 ');

Insert into teacher values (' ', ' Jack ', ' xx company ', ' 1975-12-23 ');

Note: In the construction 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 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. The address is set to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for a later article to say.

4. Set year as the Date field.

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume that it is school.sql, then copy it to C: \ and enter directory \mysql\bin in DOS, and then type the following command.

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

If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

Vii. Text to Database

1. The format in which the text data should conform: The field data is separated by the TAB key, and the null value is replaced by \ n.

Cases:

3. Rose Shenzhen II 1976-10-10

4. Mike Shenzhen one 1975-12-23

2. Data incoming command load data local infile "file name" into table name;

Note: You may want to copy the files to the \mysql\bin directory, and you must first open the database in which the table resides with the use command.

Viii. backing up the database

(commands are executed in the DOS \mysql\bin directory)

Mysqldump--opt SCHOOL>SCHOOL.BBB

Note: Back up the database school to the school.bbb file, school.bbb is a text file, the filename is taken, open to see what you will find.

Nine, start the trace file

Mysqld--debug

X. Shut down MySQL server

Mysqladmin-u Root shutdown

Xi. start the MySQL service

Mysqld--console

MySQL Database parsing