Installation, Configuration and Use of MySQL Green Edition under Windows

One, install MySQL database

1. Download

Download link: http://downloads.mysql.com/archives/get/file/mysql-5.7.11-winx64.zip.

2. Unzip the MySQL compressed package

Unzip it to the specified directory, D:\Dev\mysql-5.7.11.

3. Add environment variables

Add D:\Dev\mysql-5.7.11\bin to Path

4. Modify the configuration file

Change the my-default.ininame to:, my.inithe parameter configuration in the file:

 [mysqld]
# 设置mysql的安装目录
basedir=D:\Dev\mysql-5.7.11
# 设置mysql数据库的数据的存放目录,必须是data
datadir=D:\Dev\mysql-5.7.11\data
注意:路径是反斜线,也可以改为两个正斜线,还可以加上双引号
# mysql端口
port=3306
# 字符集
character_set_server=utf8 (我下载这个版本没有找到此项)
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

The configuration file is shown in the figure:

Insert picture description here

5. Install MySQL service

cmd enters the bin directory of mysql (D:\Dev\mysql-5.7.11\bin)
If there is a "Data" directory in the installation directory, be sure to delete the data directory first (or move it to another place).

Execute mysqld --initialize.

  • Automatic: mysqld --install [service name]
  • Manual: mysqld --install -manual [service name]

Then execute:

mysqld -install MySQL --defaults-file="D:\Dev\mysql-5.7.11\my.ini"

When it displays Service successfully installed, it means that the mysql service is successfully installed.

Note: Be sure to change "-defaults" to "–defaults". The mysqld command does not have the -d option, and MYSQL will treat -D as an option whenever it encounters xxxx-dxxxx when parsing parameters.

6. Configuration file call

Mysql will find configuration files in the following paths by default:

c:\my.ini
c:\windows\my.ini
安装目录下的my.ini或my.cnf

7, Mysql service start and stop

Method one, enter the mysql bin directory (D:\Dev\mysql-5.7.11\bin) under the doc command,

Enter "net start mysql" to start mysql, and
enter "net stop mysql" to stop the mysql service.

Method two, open the management tool service and find the MySQL service.

Start the service by right-clicking and selecting Start or directly clicking Start on the left.

Two, uninstall MySQL service

Enter D:\Dev\mysql-5.7.11\bin> under the doc command

mysqld -remove
或者
sc delete mysql

Perform uninstall service.

Three, root password modification and login

1. Log in

Log in to the mysql database locally and enter the command in the doc command window:

mysql -u root -p

Prompt to enter the password after pressing Enter.
Note: The password of the administrator root for the first installation of the mysql decompression version is empty, and press Enter to log in to the mysql database.

If ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: YES) error occurs, edit the mysql configuration file my.ini, add skip-grant-tables under the entry [mysqld], and save Restart mysql after exiting.

2. Modify the root password

When the installation is completed, the default password of the root account is empty. At this time, the password can be changed to the specified password. Such as: 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; 
mysql>QUIT

After making the changes, modify the my.ini file again, delete the line "skip-grant-tables" just added, save and exit, and then restart mysql.

Method 2:
Use a third-party management tool to modify the password. Such as Navicat for MySQL

Four, mysql commonly used commands

create database name; create database
use databasename; select database
drop database name to directly delete the database without reminding
show tables; show table
describe tablename; a detailed description of the table
add distinct to remove duplicate fields in select
mysqladmin drop databasename Before deleting the database, there is a prompt.

Display the current mysql version and current date
select version(), current_date;

Read
mysql -h myhost -u root -p database <sql.txt from the file

Five, SQL statements in mysql

1. Database creation: Create database db_name;
  Database deletion: Drop database db_name; When deleting, you can first determine whether it exists, written as: drop database if exits db_name
  
2. Create a table: Create a data table syntax: create table table_name (field 1 data type , Field 2 data type);
   Example: create table mytable (id int, username char(20));
   Delete table: 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, then the field name in the front [] brackets can be written or not.
   Example: insert into mytable (id,username) values ​​(1,'zhangsan');

4. Query: Query all data

 select * from table_name; 

Query the data of 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 ------------Refer to Article 17 for example

5. Update the specified data, update the data of a certain field

(Note that it is not the name of the updated field)

Update table_name set field name ='new value' [, field 2 ='new value', ……][where id=id_num] [order by field order]

example :

update mytable set username=’lisi’ where id=1; 

Order statement is the order of query, such as: order by id desc (or asc), there are two kinds of order: desc reverse order (100-1, that is, query from the latest data), asc (from 1-100), Where and order Statement can also be used to query select and delete delete

6. Delete the information in the table:

Delete the information in the entire table: delete from table_name;
delete the statement specifying the condition in the table: delete from table_name where conditional statement; conditional statement such as: id=3;

7. Create a database user

You can create multiple database users at one time, such as:

CREATE USER username1 identified BY ‘password’ , username2 IDENTIFIED BY ‘password’…. 

8. User's permission control: grant

Library, table-level permission control: Give a user the control of a table in a library

Grant all ON db_name.table_name TO user_name [ indentified by ‘password’ ]; 

9. Modification of the table structure

(1) Add a field format:

alter table table_name add column (字段名 字段类型);   ----此方法带括号 

(2) Specify where the field is inserted:

alter table table_name add column 字段名 字段类型 after 某字段; 

Delete a field:

alter table table_name drop字段名; 

(3) Modify the field name/type

alter table table_name change 旧字段名 新字段名 新字段的类型; 

(4) Change the name of the table

alter table table_name rename to new_table_name; 

(5) Clear all data in the table at one time

truncate table table_name; 此方法也会使表中的取号器(ID)从1开始 

10. Add primary key, foreign key, constraint, index

① Constraints (Primary key, Uniqueness, Not Null)
  ② Automatically increase auto_increment ③Foreign
  key Foreign key-used in conjunction with reference table_name (col_name column name), used alone when building a table
  ④ Delete multiple There are associated data in each table-set the foreign key to set null-refer to the help document for specific settings

11. View the current engine of the database

SHOW CREATE TABLE table_name; 

Modify the database engine

ALTER TABLE table_name ENGINE=MyISAM | InnoDB;