MySQL green version configuration and use of detailed

Original: MySQL green version configuration and use of detailed

I recently used MySQL database in project development, and after reading some articles about MySQL, I quickly started using it. There are still some problems in the use of the process, because the use of the green version of the installation of MySQL so there are some problems in the configuration, the article is mainly for the MySQL green version of the configuration and its use for discussion.

First, MySQL overview
MySQL database is developed by the Swedish MySQL AB company and is now owned by Oracle.       Like SQL Server, it is also a database management system based on relational databases, and MySQL is one of the best RDBMS in Web applications because it belongs to a lightweight RDBMS. Now the latest version of MySQL is 5.6.17, Latest: http://dev.mysql.com/downloads/mysql/, download completed the next installation deployment, the content of the installation of the deployment on the Web to view the next tutorial.

Second, MySQL configuration

Since MySQL is based on SQL, then he contains basic DML, DDL, DAL, these basic database language is easy to use, and MySQL also encapsulates a lot of database operations commands, these commands are running in the DOS system, which is he and SQL Server is different, the MySQL environment is the resume on the DOS system, to use the DOS command. It's a bit like Java, which can be said to be built on a virtual machine and can be used once and for all. To easily use the MySQL command also requires a few prerequisites to set, set the method is similar to the Java environment variables, the following method to avoid the installation version of MySQL as an example to demonstrate its configuration method.

1. mysql Environment configuration

You can use MySQL commands anywhere by configuring the decompression path of MySQL into a system variable. Note: This is a configured system variable, and any third-party commands that use console commands can be added to system variables, which are a link to the system variables when using commands.

2, MySQL server configuration after configuring the system environment variable can use all the services provided under the MySQL bin, and then need to install MySQL in the system.

2.1 Install the MySQL server open the Unzip file directory, locate the file with the suffix. ini, copy the name to My.ini, and replace the original content with the following.

[Mysqld]basedir=d:/program Files (x86)/mysql # Setting up the installation directory for MYSQL Datadir=d:/program Files (x86)/mysql/data # Set the data storage directory for the MySQL database, must be either data, or//xxx/data************************* split line *******************port = 3306socket =/tmp/ mysql.sockdefault-character-set=gbk# set the character set of the MySQL server Skip-lockingkey_buffer = 16kmax_allowed_packet = 1Mtable_cache = 4sort_buffer_size = 64kread_buffer_size = 256kread_rnd_buffer_size = 256knet_buffer_length = 2Kthread_stack = 64K[client ] #password = Your_passwordport = 3306socket =/TMP/MYSQL.SOCKDEFAULT-CHARACTER-SET=GBK ************************* Split Line * * *****************

Note:[mysqld] The following basedir and DataDir need to be set to the path after the file decompression, where I put the file in D:\Program files (x86) \mysql. In addition, the contents of the above split line are optional and can be reset when the database is created, and it is recommended not to be added at the time of creation because there are many uncertainties. After the My.ini file is configured, you can install the Mysqld service in CMD and run the command in CMD: mysqld--install MySQL--defaults-file= "D:\Program Files (x86) \mysql\my.ini", where MySQL is the name of the installation server, and you can specify any name. After the installation is complete, the following information is prompted: Service successfully installed, that is, the successful installation, the successful installation will be in the system's service Group Policy to add the service, in use only need to open.

Note : When running the installation command, be sure to be aware of the path problem within the CMD, which must be located in the path of the MySQL bin, such as my MySQL extracted to the D:\Program Files (x86) \mysql folder, Then the cmd current path must be D:\Program Files (x86) \mysql\bin, or an error message will occur when the service is started after installation: System error 2. The system cannot find the file specified.

2.2 Start the server to start the MySQL server, run the command in cmd: net start MySQL.

2.3 Stopping the server After use is complete, you can stop the server from running by command by running the command in cmd: net stop MySQL, 2.4 View design server name and password Server just installed its default name is root, there is no password at this time, you can use the cmd command to set the name and password. The corresponding command is: Mysql-u root. Alternatively, you can modify the root password by using the UPDATE statement in CMD, as shown in the following code: 1. Add a password to root ab12 first enter directory Mysql\bin under DOS, and then type the following command: mysqladmin-u root-p password ab12  。

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

2, then change the root password to djg345:mysqladmin-u root-p ab12 password djg345

2.5 Delete Service: mysqld--remove MySQLUse the Remove command, followed by the name of the database service you want to delete.
third, MySQL common commands 3.1 Connection service Here are two ways to connect to local and remote connections, respectively. 3.1.1 Local Area Connection Enter and run the command in cmd: Mysql-u root-p, then enter the appropriate password. Note that there can be no space between the user name-U and the user name, i.e. the-uroot is equally correct, but there must be a space between the password and-P. If it is just installed MySQL, the default root username is no password, the direct input mysql-u root can enter MySQL, MySQL prompt is:mysql>.
3.1.2 Remote ConnectionAssuming the IP address of the remote host is: 219.243.79.8, the user name is root, the password is 123, run the following command in CMD: mysql-h219.243.79.8-uroot-p 123.

3.1.3 Exit MySQL command: Exit

3.2 Adding new users

3.2.1 Super usersAdd a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First connect to MySQL with the root user, and then type the following command: Grant Select,insert,update,delete on * * to [[email protected] "%][email protected]"%[/email] "identified by" ABC "; But the added 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 into your MySQL database and your data can do whatever you like, solution see 2.3.2.2 Native UserAdd a user test2 password to ABC, so that he can only login on localhost, and the database mydb can be queried, inserted, modified, deleted operations (localhost refers to the local host, that is, the MySQL database is located on the 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]][email protected][/email] 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]][email protected][/email] identified by "";

3.3 Show Command

The show command is meant to be viewed and can be used to view some of the list information in MySQL, such as: show databases displays the names of all the databases; Show tables displays all the table names in a database.

3.4 Operational Databases

Before the operation to enter the relevant database, you can use command, such as: using TestDB into the database named TestDB, after entering the database can be both in the database operation of the object, the corresponding operation command using SQL statements, DDL, DML, DAL.3.4.1 Viewing the contents of a database1. View field information for a table in the database: Desc table name;

2, view the database table creation statement: Show create table table name; Of course, the same method can be used to view other SQL statements that create content, such as viewing the database creation statement, show create database name.


3.4.2 Modifying column types and names in a table (1) Modify column type onlyALTER TABLE database name. Table name Modify column name data type, For example, the sex column of the T_animal table is a Boolean type:

ALTER TABLE T_animal modify sex Boolean NOT null

       (2) Modify both the column name and the column data type
ALTER TABLE table name change column old column name the new row name data type, For example: Rename the Sex column of the T_animal table to Ani_sex and the data type to Boolean type:

ALTER TABLE t_animal change column sex Ani_sex boolean NOT null

Conclusion
This article has made a preliminary summary of the configuration and use of MySQL, MySQL has a lot of content in the use of slowly accumulated, and the article will also add new content, mainly for the development process of the situation and update. The article commands the author have carried on the test, there is what wrong place also please point out to learn from each other.

MySQL green version configuration and use of detailed