MySQL Library Operations

• A system database
• Two database operations
• Three-Name specification
• Four user rights
• Five. Change the password
• Six forgot Password
• Seven Chinese garbled problem

I. 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

Two. Database operations

1. Create a database

#语法: CREATE DATABASE db_name charset UTF8;

db_name database and specifies the encoding set for the current library as UTF8 CREATE DATABASE db_name CharSet UTF8;

Example

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

SELECT You cannot use the maximum number of 128 digits alone

Four. User rights

1. User Management

Create user ' username '    @ ' IP address ' identified by ' password ', delete user ' username '    @ ' IP address ', modify user    rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';

2. Authorization Management

Show grants for ' user ' @ ' IP address '                  --View permissions grant  permissions on database. Table to   ' user ' @ ' IP address '      --Authorize revoke permissions on database. Table from ' user ' @ ' IP ground Address '      --Cancel permissions

　

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

create user and authorization examples

             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

Mysqladmin-u Username-p password Password new password

method Two : Set the user password directly

Set password for ' username ' @ ' IP ' = password (' new password '); flush privileges;

Method Three: modify the user table under MySQL Library

5.7 Version Change password mode: Update mysql.user set Authentication_string=password (' New password ') where user= ' user name ' flush privileges; --Refresh Permissions 5.6 version update mysql.user Set password = password (' new password ') where user= ' user name ' flush privileges; --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 password update mysql.user set authentication _string=password (' 123456 ') where user= ' root '; #5. Flush permissions flush Privileges;

Seven. Chinese garbled problem

1. Query character encoding

SHOW VARIABLES 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