Installation configuration and use of MySQL Green edition under Windows 8.1

Original: Windows 8.1 mysql Green Edition installation configuration and use

Mysql-5.6.17-winx64 Operation steps:

First, install MySQL database

1, download.

: Http://downloads.mysql.com/archives/get/file/mysql-5.6.17-winx64.zip.

2. Unzip the MySQL archive package

Unzip to the specified directory, D:\Dev\mysql-5.6.17-winx64.

3. Add Environment variables

Add D:\Dev\mysql-5.6.17-winx64\bin in Path

4. Modify the configuration file

Change the My-default.ini name to: My.ini, the parameter configuration in the file:
[Mysqld]
# set up the MySQL installation directory
Basedir=d:\dev\mysql-5.6.17-winx64
# Set the data storage directory for MySQL database, must be
Datadir=d:\dev\mysql-5.6.17-winx64\data
Note: The path is a backslash, can also be changed to two forward slash, you can also add a double quotation mark
# MySQL Port
port=3306
# Character Set
Character_set_server=utf8 (i downloaded this version did not find this item)
Sql_mode=no_engine_substitution,strict_trans_tables

　　

5. Install MySQL Service

Automatic: mysqld--install [service name]
Manual: Mysqld--install-manual [service name]
Mysqld-install MySQL--defaults-file= "D:\Dev\mysql-5.6.17-winx64\my.ini"
Note: Be sure to change "-defaults" to "--defaults", mysqld directive does not have the-D option, while MySQL in parsing parameters, as long as the encounter xxxx-dxxxx will be the D as an option to deal with.
MySQL defaults to find C:\my.ini and C:\windows\my.ini, My.ini or my.cnf in the installation directory.

6. mysql Service start and stop

Method One, go to the MySQL Bin directory (D:\Dev\mysql-5.6.17-winx64\bin) under the Doc command,
Enter "net start MySQL" to start MySQL,
Enter "net stop MySQL" to stop the MySQL service.

Method Two, open the Administration Tools service and locate the MySQL service.

Start the service by right-clicking to start or by tapping on the left-hand boot directly.

Second, uninstall the MySQL service

Under the doc command, go to d:\dev\mysql-5.6.17-winx64\bin> input "Mysqld-remove" or "sc delete MySQL" to perform the uninstall service.

Third, the root password modification and login

1. Login

Log in to the MySQL database locally in the Doc Command window, enter the command: Mysql-u root-p
Enter the password when prompted.
Note: MySQL decompression version of the initial installation of the administrator root password is empty, directly and then return to log in the MySQL database.

2. Change the root password　　　　

The default password for the root account is empty when the installation is complete, and you can modify the password to the specified password. Example: 123456

Method One:

C:>mysql-u root-p

Mysql>show databases;

Mysql>use MySQL;

Mysql>update user SET Password=password ("123456") WHERE user= ' root ';

Mysql>flush privileges; "Attention must not forget this sentence, otherwise the password change will not take effect, refresh the MySQL system permissions related tables, the second method is to restart the MySQL server"

Mysql>quit

Method Two:

Use third-party management tools for password modification. such as Navicat for MySQL

Four, MySQL common commands

Create database name; Create a database

Use DatabaseName; Select Database

Drop database name deletes databases directly and does not alert

Show tables; Show Table

Describe TableName; Detailed description of the table

Add distinct in Select to remove duplicate fields

Mysqladmin drop DatabaseName You are prompted before you delete the database.

Show current MySQL version and current date

Select version (), current_date;

Read from File

Mysql-h myhost-u root-p Database < sql.txt

V. SQL statements in MySQL

　　 1. Database creation: create databases db_name;
Database deletion: Drop DB db_name; The deletion can be judged by the existence, written as: drop database if exits db_name

　　 2. Build table: syntax for creating data tables: CREATE TABLE table_name (field 1 data type, field 2 data type);
Example: CREATE TABLE mytable (ID int, username char (20));
Delete tables: DROP TABLE table_name; Example: Drop table mytable;

　　 3. Add data: Insert into table name [(Field 1, Field 2, ...)] VALUES (value 1, value 2, ...);
If you insert a value into each field in the table, the field name in the preceding [] parentheses is not writable
Example: INSERT INTO MyTable (id,username) VALUES (1, ' Zhangsan ');

　　 4. Query: Query all data: SELECT * FROM table_name;
Query data for the specified field: Select Field 1, field 2 from table_name;
Example: Select Id,username from MyTable where id=1 ORDER by DESC, multi-table query statement------------reference to 17th instance

　　 5. Update specified data to update data for a field (note that the name of the field is not updated)
Update table_name SET field name = ' new value ' [, Field 2 = ' new value ', ...] [Where Id=id_num] [Order BY Field]
Example: Update mytable set username= ' Lisi ' where id=1;
The order statement is a sequence of queries, such as ORDER by ID DESC (or ASC), in order of two kinds: desc reverse (100-1, which is queried from the latest data), ASC (from 1-100), where and order statements can also be used to query select and Delete del Ete

　　 6. To delete information from a table:
Delete information from the entire table: delete from table_name;
Delete the statement for the specified condition in the table: DELETE from table_name where condition statement; Conditional statements such as: id=3;

　　 7. Create a database user
You can create multiple database users at a time such as:
CREATE USER username1 identified by ' password ', username2 identified by ' password ' ....

　　 8. Permissions control for users: Grant
Library, table-level permission control: assigning control of a table in a library to a user
Grant all on Db_name.table_name to user_name [indentified by ' Password '];

　　 9. Table structure modification  
(1) Add a field format: &NBSP;
ALTER TABLE table_name add column (field name fields type);----This method with parentheses  
(2 Specifies the location where the field is inserted: &NBSP;
ALTER TABLE table_name ADD column Name field type after a field;  
Deletes a field: &NBSP;
ALTER TABLE tab Le_name drop field name;  
(3) Modify field name/Type &NBSP;
ALTER TABLE table_name change old field name new field name type of new field;  
(4) The name of the list &NB Sp
ALTER TABLE table_name Rename to new_table_name; 
(5) Clears all data from the table at once  
TRUNCATE TABLE table_name; this party The method also causes the &NBSP (ID) in the table to start at 1,
 
10. Add primary key, foreign key, constraint, index .... (see 17 examples)  
① constraint (primary key primary key, uniqueness unique, non-null NOT NULL)  
② Auto-add auto_increment 
③ foreign key foreign key -----With reference table_name (col_name column name), use   when the table is built,
④ Delete the associated data from multiple tables----settings foreign key is set NULL---specific settings reference Help document nbsp
 
11. View database current engine  
SHOW CREATE TABLE table_name; 
Modify Database engine  
Alter TAB LE table_name Engine=myisam | innodb; 

MySQL Green installation configuration and use under Windows 8.1