mysql-Library Operations

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

# syntax: CREATE DATABASE db_name charset UTF8;

# Create a database named Db_name, and specify the encoding set for the current library as UTF8 CREATE DATABASE db_name charset UTF8;

Example

2. View the database

# querying all databases under the current user show databases; # View information about creating a database Show Create database db_name; # Query the name of the database in which the current operation is located select Databases ();

3. Select a database

Use db_name;

4. Deleting a database

DROP DATABASE db_name;

Three. Naming conventions

can be by letter, number, underscore, @, #, $ case-sensitive uniqueness cannot use keywords such as: CREATE Select cannot use the number maximum 128 bits alone

Four. User rights

1. User Management

1 Create user2Create user'User name'@'IP Address'Identified by'Password';3 Delete User4Drop user'User name'@'IP Address';5 Modify User6Rename User'User name'@'IP Address'; To'New user name'@'IP Address';

2. Authorization Management

1Show grants for 'User'@'IP Address'--View Permissions2Grant permissions on the database. Table to'User'@'IP Address'--Authorized3Revoke permissions on the database. Table from 'User'@'IP Address'--Cancel Permissions

#Create a new userCreate user'Alex'@'localhost'Identified'123456';#Authorization method One: For Alex authorized db1 the query for all tables under the database. Update. Modify PermissionsGrant Select,update,delete on db1.* to'Alex'@'localhost';#Authorization Method Two: All permissions for Alex to authorize all libraries (except grant permissions)Grant all privileges on * * to'Alex'@'localhost';#Refresh user PermissionsFlush privileges;

create user and authorization examples

 All privileges except grant all permissions select only Check permissions Select,insert check and insert Permissions ... Usage No access permission ALTER use ALTER TABLE ALTER ROUTINE using ALTER Procedure and drop procedure create using the CREATE table create routine using Create Pro Cedure Create temporary tables use create temporary tables create user using create user, drop                  User, rename user, and revoke all privileges CREATE view use the CREATE VIEW delete                    Use drop table execute with delete drop to use call and stored procedure file            Use SELECT INTO outfile and load data infile grant option using GRANT and REVOKE Index using the Insert lock tables with the index insert to use the Lock tab   Le process              Use the show full processlist Select to use the SHO with select Show databases                  W databases show view uses the update reload with show view update                   Use flush shutdown with mysqladmin shutdown (turn off MySQL) super  Use change master, kill, logs, purge, master, and setGlobal. Also allows mysqladmin to debug login replication client server location access replication slave used by replication slaves

Permissions ListFive. Change the password

Way One: mysqladmin command

1 mysqladmin-u user name-p password Password new password

method Two : Set the user password directly

1  for ' User name '@'IP' = password (' new password '); 2              3 flush privileges;

Method Three: modify the user table under MySQL Library

15. 7 Version Change password mode:2Update Mysql.user Set Authentication_string=password ('New Password') Where user='User name'          3Flush privileges; --Refresh Permissions4              55.6version6Update mysql.user Set password = password ('New Password') Where user='User name'7Flush privileges; --Refresh Permissions

Six. Forgot password

1 when you forget the root password, you can:2  3 #1. First open the CMD window and turn off the MySQL service4 net stop MySQL5  6 #2. Then skip permission check, start MySQL, enter command7Mysqld--skip-grant-Tables8  9 #3. Reopen a new CMD window to start the client (the permission check has been skipped, you can log in directly)Ten MySQL One   A #4. Directly in, change the password -Update Mysql.user Set Authentication_string=password ('123456') Where user='Root'; -   the #5. Refresh Permissions -Flush privileges;

Seven. Chinese garbled problem

1. Query character encoding

1 ' char% ';

2. Uniform garbled

#Modification Method:#1. Create the My.ini file and place it under the MySQL root path#2. Add the following content to the file:#3. After you add this file, you need to restart the service to ensure this file is valid------------------------------------------------------------[Client]default-character-set=Utf8[mysql]#set the MySQL client default character setdefault-character-set=Utf8[mysqld]#Set 3306 PortPort= 3306#Maximum number of connections allowedmax_connections=200#the character set used by the service side defaults to the 8-bit encoded latin1 character Setcharacter-set-server=UTF8#default storage engine that will be used when creating a new tabledefault-storage-engine=INNODB#resolve MySQL 1055 error after executing SQL statement, Sql_mode = only_full_group_by IncompatibleSql_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