MySQL Library operations

First, the system database

• INFORMATION_SCHEMA: Virtual library, do not occupy disk space, storage is the database startup parameters, such as user table information, column information, permission information, character information, etc.
• MySQL: The core database, which contains users, permissions, keywords and other information. Can not delete
• Performance_schema:mysql new library added after 5.5, mainly collects system performance parameters, records various events, locks and other phenomena that occur when processing query requests.
• sys:mysql5.7 version of the new library, through this library can quickly understand the system's metadata information, can facilitate the DBA to find a lot of information about the database, to solve the performance bottleneck is a great help

II. database Operations 1. Create a database

CREATE DATABASE db_name CharSet UTF8;

Description:'db_name' is the database name, and the encoding set for the current library is specified as UTF8

2. View the database

Create Database db_name ; #查询当前操作所在的数据库名称 Select Database ();

3. Select a database

 Use db_name;   #一般在终端上使用

4. Deleting a database

DROP DATABASE db_name;

Three. Naming conventions

CREATE SELECT You cannot use the maximum number of 128 digits alone

Four. User Rights 1. User Management

Create userCreate User 'User name'@'IP Address'Identified by 'Password'; Delete a userDrop User 'User name'@'IP Address'; Modify User RenameUser 'User name'@'IP Address'; to 'New user name'@'IP Address';

2. Authorization Management

Show grants for 'User'@'IP Address'                  --View PermissionsGrantPermissions onDatabase. Table to   'User'@'IP Address'      --AuthorizedRevokePermissions onDatabase. Table from 'User'@'IP Address'      --Cancel Permissions

#创建新用户Create User 'Guo'@'127.0.0.1'Identified'123456'; #授权方式一: The query for all tables under the DB1 database for Guo authorization. Update. Modify PermissionsGrant Select,Update,Delete  onDB1.*   to 'Guo'@'127.0.0.1'; #授权方式二: All Permissions (except grant permissions) granted to all libraries for GuoGrant  All Privileges  on *.*   to 'Guo'@'127.0.0.1'; #刷新用户权限flushPrivileges;

create user and authorization examples

The IP address of this machine can be written as  localhost

Description

 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(turn MySQL off) Super uses change master,Kill, logs, purge, master, and set global. Also allows mysqladmin debug loginReplicationaccess to client server locationsReplicationSlave used by replication slaves

Permissions ListFive. Change the password Way One:Mysqladmin command

- -p password Password new password

Mode two: Set User password directly

Set  for ' User name '@'IP'= password (' new password ');              privileges;

Way Three:Modify the user table under MySQL Library

5. 7 Version Change password mode:UpdateMysql.User SetAuthentication_string=Password'New Password')where User= 'User name'FlushPrivileges;--Refresh Permissions             5.6versionUpdateMysql.User SetPassword=Password'New Password')where User= 'User name'FlushPrivileges;--Refresh Permissions

Six. Forgot password

when you forget the root password, you can: #1. First open the CMD window, close the MySQL service net stop MySQL #2. Then skip permission check, start MySQL, enter command mysqld--Skip-grant-tables #3. Reopen a new CMD window, start the client (skipped permission check, can log in directly) MySQL #4. Directly in, change the passwordUpdateMysql.User SetAuthentication_string=Password'123456')where User='Root'; #5. Flush Permissions FlushPrivileges;

Seven. Chinese garbled problem 1. Query character encoding

 like ' char% ';

2. Uniform garbled

#修改方法: #1. Create a My.ini file and place it under the MySQL root path #2Add the following to the file: #3After you add this file, you need to restart the service to ensure that the file takes effect------------------------------------------------------------[Client]default-character-Set=UTF8[MySQL]#设置mysql客户端默认字符集default-character-Set=UTF8[mysqld]#设置3306端口port= 3306#允许最大连接数max_connections= $#服务端使用的字符集默认为8比特编码的latin1字符集character-Set-Server=utf8# default storage engine to use when creating a new tabledefault-Storage-Engine=innodb# resolves MySQL 1055 error after executing SQL statement, Sql_mode=only_full_group_by Incompatible Sql_mode='no_engine_substitution'

garbled is dead

Note: If you are using the mysql5.7 version, you will need to create a My.ini file, 5.7 version of the previous database with My,ini files, directly change the code.

Currently the most stable and commonly used database version is (version 5.6 and 5.5)

MySQL Library operations