mysql 5.6.17 green version (installation-free) installation and configuration tutorial_MySQL

I recently used the MySql database when doing project development. After reading some articles about MySql, I quickly started using it. Some problems still occurred during use. Because the green installation-free version of MySql was used, some problems occurred during configuration. This article mainly discusses the configuration and use of the green version of MySql.

1. Overview of MySql
         MySql database was developed by the Swedish MySql AB company, which is now owned by Oracle and acquired by Oracle. Similar to SQL Server, it is also a database management system based on a relational database. MySQL is one of the best RDBMS for Web applications because it is a lightweight RDBMS. The latest version of MySql is now 5.6.17. The latest download address is: http://dev.mysql.com/downloads/mysql/. After the download is completed, the next step is to install and deploy. For information about installation and deployment, check it out online. Tutorials are fine.

2. MySql configuration

Since MySql is based on SQL, it includes basic DML, DDL, and DAL. These basic database languages ​​are easy to use. In addition, MySql also encapsulates many database operation commands, which are implemented in the DOS system. This is the difference between it and SQL Server. The environment of MySql is based on the dos system, so you need to use the dos command. It is somewhat similar to Java. It can be said that it is also built on a virtual machine and can be created once and used everywhere. If you want to use MySql commands conveniently, you need to set some prerequisites. The setting method is similar to Java's environment variables. The following method uses the non-installed 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 the system variable. ​​​​

Note: This is a configured system variable. Any third-party commands that use console commands can be added to the system variables. System variables are a link. When using commands, system variables will be searched first.                                                                                     

2. MySql server configuration

After configuring the system environment variables, you can use all the services provided under the MySql bin. Next, you need to install MySQL in the system.

2.1 Install MySql server

Open the unzipped file directory, find the file with the suffix .ini, make a copy and rename it my.ini, and replace the original content with the following content.

[mysqld]
basedir=D:/Program Files (x86)/MySql # 设置mysql的安装目录
datadir=D:/Program Files (x86)/MySql/data # 设置mysql数据库的数据的存放目录，必须是data，或者是//xxx/data

*************************分割线*******************
port = 3306
socket = /tmp/mysql.sock
default-character-set=gbk # 设置mysql服务器的字符集
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

[client] 
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set=gbk 

************************ dividing line****************** *******

Note: [mysqld] The following basedir and datadir need to be set to the path after the file is decompressed. Here the author puts the file under D:Program Files (x86)MySql. In addition, the content within the dividing line above is optional and can be reset when creating the database. It is recommended not to add it when creating because there will be many uncertain factors.

After the my.ini file is configured, you can install the mysqld service in cmd. Run the command in cmd:

mysqld --install MySQL --defaults-file="D:Program Files(x86)MySqlmy.ini", Where MySQL is the name of the installation server, any name can be specified. After the installation is completed, the following message will be prompted: Service successfully installed, which means the installation is successful. After the installation is successful, the service will be added to the system's service group policy. You only need to turn it on when using it.                                                       

Note: Be sure to pay attention to the path problem in cmd when running the installation command. The path must be in the path where mysql's bin is located. For example, my mysql is decompressed to the D:Program Files (x86)MySql folder. , then the current path of cmd must be D:Program Files(x86)MySqlbin, otherwise an error message will appear when starting the service after the installation is completed: System Error 2. The system can not find the file specified.

2.2 Start the server

Start the MySQL server and run the command in cmd: net start MySQL.                                                       

2.3 Stop server

After use, you can stop the server by running the command in cmd:

net stop MySQL.

2.4 View the design server name and password

The default name of the newly installed server is root. There is no password at this time. You can set the name and password through the cmd command. The corresponding command is: mysql -u root. In addition, you can modify the root password by using the update statement in cmd. The specific setting method is as shown in the following code:​​​

1), add a password ab12 to root

First enter the directory mysqlbin 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 item can be omitted.
2), then change the root password to djg345:

mysqladmin -u root -p ab12 password djg345

2.5 Delete service: mysqld --remove MySQL  ​ ​​​ Use the remove command followed by the name of the database service you want to remove.

3. Common MySql commands

3.1 Connection Service The two connection methods introduced here are local connection and remote connection.

3.1.1 Local connection

Enter and run the command in cmd: mysql -u root -p, and then enter the corresponding password. It should be noted that there can be no space between the username -u and the username, that is, -uroot is also correct, but there must be a space between the password and -p. If MYSQL is just installed, the default root user name does not have a password. You can directly enter mysql -u root to enter MYSQL. The MYSQL prompt is: mysql>.                                         

3.1.2 Remote connection

Assume that the IP address of the remote host is: 219.243.79.8, the user name is root, and the password is 123. Then run the following command in cmd: mysql -h219.243.79.8 -uroot -p 123.

3.1.3 Exit MYSQL command: exit

3.2 Add new users
3.2.1 Super User

Add a user test1 with the password abc, so that he can log in on any host and have query, insert, modify, and delete permissions on all databases. First connect to MYSQL as the root user, and then type the following command:​​​​​​​​

grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;

但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。   

3.2.2 本机用户       增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。              

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;     

如果你不想test2有密码，可以再打一个命令将密码消掉。            

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”; 

3.3 show命令

show命令是查看的意思，可以用来查看MySql中的一些列表信息，如：show databases显示所有数据库的名称；show tables显示一个数据库中的所有表名称。 

3.4 操作数据库

操作前要进入相关的数据库，可以使用use命令，如：use testdb进入名为testdb的数据库，进入数据库后既可以对数据库中的对象操作，相应的操作命令使用的是SQL语句，DDL、DML、DAL。   

3.4.1 查看数据库内容       

1)、查看数据库某个表的字段信息：desc 表名；                                   

        

2)、查看数据库表的创建语句：show create table 表名；当然使用同样的方法也可以查看其它创建内容的SQL语句，如查看数据库的创建语句，show create database 数据库名。                           

       

3.4.2 修改表中列类型及名称       

（1）只修改列类型         

alter table 数据库名.表名  modify column 列名  数据类型，例如：将t_animal表的sex列该为boolean类型：

alter table t_animal modify sex boolean not null

（2）同时修改列名和列数据类型
alter table 表名 change column 旧列名 新列名 数据类型，例如：将t_animal表的sex列更名为ani_sex，数据类型修改为boolean类型：

alter table t_animal change column sex ani_sex boolean not null

结语

本文对MySql的配置及使用方法做了初步的总结，MySql还有很多内容在使用中慢慢积累，并且该文章也会不定时的添加新内容，主要是针对开发过程中的情况而更新。

文章的命令笔者都进行了测试，有哪些不对的地方还请指出互相学习。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本