MySQL (a)

I. Overview

1. What is a database?
Answer: The warehouse of the data.

2. What is MySQL, Oracle, SQLite, Access, MS SQL Server, etc.?
A: They are all software and have two main functions:

• A. Saving data to a file or memory
• B. Receive a specific command, and then perform the appropriate action on the file

PS: If you have the above software, do not have to create files and folders yourself, but instead of directly passing commands to the above software, let them for file operations, they collectively referred to as the database management system (Dbms,database Management Systems)

3. What is SQL?
A: The above mentioned MySQL and other software can accept the command, and do the appropriate action, because the command can contain delete files, get the contents of the file, and so many operations, for the written command is the SQL statement. SQ, an abbreviation for structured language (structured Query Language), is a language specifically designed to communicate with a database.

Second, download the installation

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company. MySQL's most popular relational database management system, MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software in WEB applications.

To use MySQL to store and manipulate data, you need to do a few things:

1. Installing the MySQL server
2. Installing the MySQL Client
3. "Client" Connection "server Side"
4. The "client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate action (add-and-revise)

window version 1. Download

http://dev.mysql.com/downloads/mysql/

2. Decompression

If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-8.0.11-winx64

3. Initialization

MySQL after the extracted bin directory has a large number of executables, execute the following command to initialize the data: (First step into the bin directory, if the environment variable has been added, you can directly the following line.) )

CD c:\mysql-5.7.16---initialize-insecure

After initialization, the C:\mysql-8.0.11-winx64 directory will have a more data directory.

4. Start the MySQL service

Execute the command to start the MySQL service.

# go to executable directory CD c:\mysql-5.7.16-#  start MySQL service mysqld

It will hang at this point, that is, the cursor will stay on the following line flashing. If you want to start the client, you need to open another cmd command prompt. (There are workarounds later.) 5th Step method B)

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

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

# go to executable directory CD c:\mysql-5.7.16-#  connect MySQL server mysql-u root-# Prompt Please enter password, direct enter

To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.

A. Adding environment variables

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

"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"- "the MySQL Bin directory path appended to the variable value, with; such as: C:\Program Files (x86) \parallels\parallels tools\applications; %systemroot%\system32;%systemroot%;%systemroot%\system32\wbem;%systemroot%\system32\windowspowershell\v1.0\; C:\Python27; C:\Python35; C:\mysql-5.7.16-winx64\bin

This way, when you start the service and connect later, you only need to:

# start the MySQL service, enter in the terminal  #  connect MySQL service, enter in Terminal:mysql-u root-p

B. Making a MySQL service into a Windows service

The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:

# To make a Windows service for MySQL, execute this command at the terminal: " C:\mysql-5.7.16-winx64\bin\mysqld " --#  Remove the MySQL Windows service and execute this command at the terminal:"c:\mysql-5.7.16-winx64\bin\ Mysqld" --remove

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

# start the MySQL service  #  close MySQL service net stop MySQL

Third, database operation

1. Display Database

show databases;

Default database:
MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data

2. Create a database

Create database test;# UTF-8create database name DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # GBKCREATEDATABASESET gbk COLLATE gbk_chinese_ci;

3. Delete Database

Drop databases < database name >;

4. Using the database

Use database name;

The function is to switch to that database. Later, such as displaying all tables in the database currently in use: Show tables; The table in this database is displayed.

5. User Management

Creating user Create users'User name'@'IP Address'Identified by'Password'Delete users drop user'User name'@'IP Address'; Modify users rename user'User name'@'IP Address'; To'New user name'@'IP Address';; Modify password Set password for'User name'@'IP Address'= Password ('New Password'PS: User Rights related data is saved in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

If the local user, enter " localhost " at the IP address.

Show Users: (with root permission)

Select User, host, password from Mysql.user;

6. Authorization Management

' User '@'IP address '                  --   View permissions grant permissions on database. Tableto' user  ' @'IP address '      --' user ' @'IP address '      --Cancel permissions

             All Privilegesall permissions except GrantSelectCheck Permissions onlySelect,Insertcheck and Insert Permissions ... usage no access rightsAlterUse alterTable            AlterRoutine using ALTER procedure and dropprocedure            CreateUsing the CreateTable            CreateRoutine using Createprocedure            Create TemporaryTables using CreateTemporaryTablesCreate UserUsing the CreateUser、Drop User, rename user, and revoke All Privileges            Create ViewUsing the CreateView            Deleteusing the DeleteDropUse DropTable            Executeusing call and stored proceduresfileUsing Select intoOutFile andLoadData infileGrant optionUsing Grant andRevoke            IndexUse indexInsertUse the lock tables with the Insert lockTableprocess using show FullprocesslistSelectUse the select Show databases with show databases showViewUsing showView            UpdateUsing the flush with update reloadshutdownUsing Mysqladminshutdown(close MySQL) Super???? Use change master,Kill, logs, purge, master, and set global. also allows mysqladmin???????? Commissioning LoginReplicationaccess to client server locationsReplicationSlave used by replication slaves

Permissions

        for the target database and other internal:            database names. *            All database names in the database            . Table          Specifies a table database name in the database            . The stored procedure     specifies the stored procedure            in the database *.  *                all databases

            User name @IP address user          can only access            user name @192 under IP change .   168.1. %   Users can only be accessed under the IP segment (wildcard % means any)            user name @%             user may be accessed under any IP (default IP address is %)

            Grant  All Privileges  onDb1.tb1 to 'User name'@'IP'            Grant Select  onDB1.*  to 'User name'@'IP'            Grant Select,Insert  on *.*  to 'User name'@'IP'            Revoke Select  onDb1.tb1 from 'User name'@'IP'

MySQL (a)