MacBook access MySQL specific step-up

First, MySQL database installation:

Download the database installation file for your operating system category and version from its official website and follow the instructions to install it. After you open a. dmg file, there are three required installation files, including: MySQL-...-osx-64.pkg, mysqlstartupitem.pkg and Mysql.prepane.

At the end of this installation process, we can pass: "Launchpad---system Preferences---show All", MySQL appears in the interface.

Second, enter the terminal of the Mac:

With the "Finder---to the---utility---terminal", we entered the terminal operator interface. Using MySQL command: mysql–u root, will prompt for a password, this command means: Enter MySQL as root user. If this does not specify the root user, you may not have permission to create operations such as databases. After the installation of the default password is empty, if not set, we directly enter the MySQL operating environment, the interface prompts mysql>

Iii. Common Commands

Start and stop of MySQL service

1. net stop MySQL
3. net start MySQL

Log in to MySQL

1. Mysql-u User name-p user Password
3. Mysql-uroot-p, enter the password prompt you, enter 12345, and then enter into the MySQL, MySQL prompt is:
5. MySQL> Note that if you are connected to another machine, you need to add a parameter-H machine IP

Add new users

Grant permissions on the database. * to User name @ login host identified by "password"

Add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First use the root user to connect to MySQL, then type the following command: Grant Select,insert,update,delete on * * to [email protected] identified by "Password1"; If you want the user to be able to log on to MySQL on any machine, change localhost to "%".

If you do not want to User1 have a password, you can make another command to remove the password. Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

The operations database logs in to MySQL, and then runs the following command at the prompt of MySQL, with each command ending with a semicolon.

Displays the list of databases.

show databases; The default is two databases: MySQL and test. MySQL inventory in the MySQL system and user rights information, we change the password and the new user, is actually to operate the library.

To display the data tables in the library:

1. Use MySQL;
3. Show tables;

Display the structure of the data table: describe table name;

Build Library and Delete library: Create database name; drop database name;

Build table: Use library name; CREATE TABLE table name (field list); drop table name;

Clear record in table: delete from table name;

Show Records in table: SELECT * from table name;

Exporting and importing data

Export data: mysqldump--opt Test > Mysql.test Export the Database test database to a mysql.test file, followed by a text file such as: Mysqldump-u root-p123456--databases dbname > Mysql.dbname is to export the database dbname to the file mysql.dbname.

Import data: Mysqlimport-u root-p123456 < Mysql.dbname. You don't have to explain.

Import text data into the database: The field data of the text data is separated by the TAB key. Use test; Load data local infile "file name" into table name;

How to use SQL Common commands:

(1) Data record filter:

2. Sql="SELECT * from data table where field name = field value order BY field name [desc]"
6. Sql="SELECT * from data table where field name like '% field value% ' Order by field name [desc]"
10. Sql="SELECT Top ten * from data table where field name order by field name [desc]"
14. Sql="SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ')"
18. Sql="SELECT * from data table where field name between value 1 and value 2"

(2) Update data record:

1. sql="Update data table set field name = field value where Condition expression"
3. sql="Update data Table set field 1= value 1, field 2= value 2 ... field n= value n where condition expression "

(3) Delete data record:

1. sql="Delete from data table where conditional expression"
3. sql="Delete from data table" (delete all records of the datasheet)

(4) Add data record:

1. sql="INSERT into Data table (field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...) "
3. sql="INSERT INTO Target data table select * From source data table" (Add records from source data table to target datasheet)

(5) Data record statistic function:

AVG (field name) results in a table bar average count (*| field name) statistics on the number of rows of data or the number of rows with values for a column statistics max (field name) gets the maximum value of a table column min (field name) gets the value of a table bar sum (field name) adds the values of the data bar

Methods that refer to the above functions:

1. sql="Select sum (field name) as Alias from data table where conditional expression" set rs=conn.excute (SQL)

Use RS ("Alias") to obtain the value of the system, the other functions used ibid.

(6) Creation and deletion of data sheets:

CREATE table Data table name (field 1 type 1 (length), Field 2 type 2 (length) ...)

Example: CREATE TABLE tab01 (name varchar), datetime default Now ())

drop table data table name (permanently delete a data table)

MySQL Batch processing command

Batching is a non-interactive way to run a MySQL program, and you will still use these commands, just like the commands you use in MySQL.

In order to implement batching, you redirect a file into the MySQL program, first we need a text file that contains the same text as the command we entered in MySQL. For example, we want to insert some data, use a file containing the following text (the file name is New_data.sql, of course we can also be named New_data.txt and any other legal name, do not necessarily end with a suffix SQL):

1. Use Meet_a_geek;
3. INSERT into Customers (customer_id, last_name) VALUES (NULL, "Block");
5. INSERT into Customers (customer_id, last_name) VALUES (NULL, "Newton");
7. INSERT into Customers (customer_id, last_name) VALUES (NULL, "Simmons");

Note that the syntax of the above sentences must be correct, and each sentence ends with a semicolon. The use command above selects the database, and the INSERT command inserts the data.

Below we want to import the above file into the database, before the import to confirm that the database is already running, that is, the mysqld process (or service, Windows NT is called "service", Unix under "process") is already running. Then run the following command:

1. Bin/mysql–p

Then follow the prompts to enter the password, if the above file is not an error in the statement, then this data is imported into the database.

The command line uses load data INFILE to import from the file to the database: Now you might ask yourself, "Why do I have to enter all these SQL statements into a file and then run them through the program?" "It looks like it needs a lot of work. Well, it's probably right that you think so. But what if you have log records from all of these commands? Now that's great, well, most databases will automatically generate log of event logs in the database. Most of the logs contain the original SQL commands that were useful. So, if you can't export data from your current database to a new MySQL database, you can use the log and MySQL batch features to quickly and easily import your data. Of course, this eliminates the hassle of typing.

LOAD Data INFILE This is the last method we will introduce to import data into the MySQL database. This command is very similar to Mysqlimport, but this method can be used on the MySQL command line. This means that you can use this command in all programs that use the API. With this approach, you can import the data you want to import in your application.

Before using this command, the MYSQLD process (service) must already be running. Start MySQL command line: bin/mysql–p

Enter the password as prompted and enter the following command after successfully entering the MySQL command line:

1. Use Meet_a_geek;
3. LOAD DATA INFILE "/home/mark/data.sql" into TABLE Orders;

Simply put, this will import the contents of the file Data.sql into the table orders, like the Mysqlimport tool, which also has some parameters to choose from. For example, if you need to import data from your computer to a remote database server, you can use the following command:

1. LOAD DATA LOCAL INFILE "C:\MyDocs\SQL.txt" into TABLE Orders;

The local parameter above indicates that the file is a native file and that the server is the server you are logged on to. This eliminates the use of FTP to upload files to the server, MySQL for you to complete. You can also set the priority of the INSERT statement, and if you want to mark it as low priority (low_priority), then MySQL will wait until no one else reads the table before inserting the data. You can use the following command:

1. LOAD DATA low_priority INFILE "/home/mark/data.sql" into TABLE Orders;

You can also specify whether to replace or omit duplicate key values in the file and data table when inserting data. Syntax to override duplicate key values:

1. LOAD DATA low_priority INFILE "/home/mark/data.sql" REPLACE into TABLE Orders;

The sentence above looks a bit clumsy, but it puts the keyword in a place that your profiler can understand.

The following pair of options describe the file format, which is also available in the Mysqlimport tool. They look a little different here. First, to use the fields keyword, if you use this keyword, the MySQL parser wants to see at least one of the following options:

1. TERMINATED by character
3. Enclosed by character
5. Escaped by character

These keywords are the same as their arguments, as in Mysqlimport. The TERMINATED by describes the delimiter for the field, by default the tab character (\ t) enclosed by describes the enclosed character of the field. For example, enclose each field in quotation marks. Escaped by describes the escape character. The default is the backslash (backslash:\). The following example of the previous Mysqlimport command is still used to import the same file into the database using the load DATA infile statement:

1. LOAD DATA INFILE "/home/mark/orders.txt" REPLACE into TABLE Orders fields TERMINATED by ', ' enclosed by ' ";

There is no feature in the LOAD DATA infile statement in a Mysqlimport tool.

The LOAD DATA INFILE can import files into the database by the specified columns.

This feature is important when we want to import part of the data. For example, when we are upgrading from an Access database to a MySQL database, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. At this point, the data in our Access database is still available, but because the columns (field) of the data are no longer matched in MySQL, the Mysqlimport tool cannot be used anymore. However, we can still use the load data INFILE, which shows how to import data into a specified field:

1. LOAD DATA INFILE "/home/order.txt" into TABLE Orders (Order_number, order_date, customer_id);

As you can see, we may specify the required fields. These specified fields are still enclosed in parentheses, separated by commas, and if you omit any of them, MySQL will alert you.

The MySQL command line method under Ubuntu. To download the Blue.sql, run:

1. (sudo) MySQL
3. Create Database XXXX;
5. Use XXXX;
7. SOURCE Blue.sql

Initial login remote MySQL database mysql-hip-u user name-p password.

The knowledge about the command line of MySQL database is introduced here, if you want to learn more about MySQL database, you can look at the article here: http://database.51cto.com/mysql/, I believe will bring you harvest!

Turn from:

Http://database.51cto.com/art/201107/279428.htm

http://andrewli.blog.51cto.com/7625043/1536397

MacBook access MySQL specific step-up