Getting started with MYSQL

Getting started with MYSQL

1. Database Introduction

What is a database?

Data warehouse. For example, in an atm instance, we create a database directory called a database.

What are MySQL, Oracle, SQLite, Access, and ms SQL Server?

They are database software and have two main functions: 1. Save data to memory or files. 2. Accept specific commands to operate data

What is SQL?

As mentioned above, the database needs to accept specific commands. This command is written in SQL. It is short for Structured Query Language (Structured Query Language) and a Language dedicated to interacting with the database.

Ii. Database Installation

Windows

1. Download

MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/

2. Decompress

If you want MySQL to be installed in a specified directory, move the decompressed folder to the specified directory, such as: C: \ mysql-5.7.16-winx64

3. Initialization

The decompressed bin directory of MySQL contains a large number of executable files. Run the following command to initialize the data:

cd c:\mysql-5.7.16-winx64\bin mysqld --initialize-insecure

4. Start the MySQL Service

Run the command to start the MySQL Service

# Enter the executable file directory cd c: \ mysql-5.7.16-winx64 \ bin # Start MySQL service mysqld

5. Start the MySQL client and connect to the MySQL Service

Because the [mysqld -- initialize-insecure] command is used during initialization, it does not set a password for the root account by default.

# Enter the executable file directory cd c: \ mysql-5.7.16-winx64 \ bin # connect to MySQL Server mysql-u root-p # prompt please enter the password, press ENTER

Enter the press Enter:

,

So far, the MySQL server has been installed successfully and the client can be connected. To operate MySQL later, you only need to repeat steps 4 and 5 above. However, repeated steps 4 and 5 are cumbersome to enter the executable file directory. To facilitate future operations, you can perform the following operations.

Add Environment Variables

Add the MySQL executable file to the environment variable to execute the command.

Right-click the computer and choose Properties> advanced system Settings> advanced environment variables> Path in the second content box. one row, double-click to append the bin directory path of MySQL to the variable value, and separate it with;. For example, C: \ Program Files (x86) \ Parallels Tools \ Applications; % SystemRoot % \ system32; % SystemRoot % \ System32 \ Wbem; % SYSTEMROOT % \ System32 \ WindowsPowerShell \ v1.0 \; C: \ Python27; C: \ Python35; c: \ mysql-5.7.16-winx64 \ bin

In this way, you only need:

# Start the MySQL service, enter mysqld on the terminal # connect to the MySQL service, and enter mysql-u root-p on the terminal

. Create the MySQL service as a windows Service

Some problems have been solved in the previous step, but they are not thorough enough, because the current shard will be hang when the MySQL server is started in mysqd, so you can solve this problem by setting it:

# Create MySQL Windows Service, execute this command on the terminal: "c: \ mysql-5.7.16-winx64 \ bin \ mysqld" -- install # Remove MySQL Windows Service, execute this command on the terminal: "c: \ mysql-5.7.16-winx64 \ bin \ mysqld" -- remove

After registering as a service, you only need to execute the following command to start and close the MySQL service later:

 

# Starting MySQL Service net start mysql # disabling MySQL service net stop mysql

Linux version

Install

yum install mysql-server　　

Start

mysql.server start

Link

Connection: mysql-h host-u user-p common ERROR: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql. sock '(2), it means that the MySQL server daemon (Unix) or service (Windows) is not running. exit: QUIT or Control + D

Iii. Database Operations

1. display the database

SHOW DATABASES;

Default database:
Mysql-user permission-related data
Test-used for user test data
Information_schema-MySQL architecture data

2. Create a database

# Utf-8CREATE DATABASE name default charset utf8 COLLATE utf8_general_ci; # gbkCREATE DATABASE name default character set gbk COLLATE gbk_chinese_ci;

3. Use a database

USE db_name;

4. User Management

Create user 'username' @ 'IP address' identified by 'Password'; delete user drop user 'username' @ 'IP address '; modify the user rename user 'username' @ 'IP address'; to 'new username' @ 'IP address ';; change password set Password for 'username' @ 'IP address' = password ('new password') PS: the user permission-related data is stored in the mysql database user table, therefore, you can directly operate on it (not recommended)

5. authorization management

Show grants for 'user' @ 'IP address' -- view the permission grant permission on the database. table to 'user' @ 'IP address' -- authorize revoke permission on database. table from 'user' @ 'IP address' -- cancel permission

1 all privileges all permissions except grant 2 select only query permissions 3 select, insert query and insert permissions 4... 5 usage no access permission 6 alter use alter table 7 alter routine use alter procedure and drop procedure 8 create use create table 9 create routine use create procedure10 create temporary tables use create temporary tables11 create user use create user, drop user, rename user, and revoke all privileges12 create view use create view13 delete use delete14 drop use drop table15 execute use call and stored procedure 16 file use select into outfile and load data infile17 grant option use grant and revoke18 index use index19 insert use insert20 lock tables use lock table21 process use show full processlist22 select use select23 show databases use show databases24 show view use show view25 update use update26 reload flush27 shutdown using mysqladmin shutdown (disable MySQL) 28 super