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

1	#语法: CREATEDATABASEdb_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;

2. View the database

#查询当前用户下所有数据库show databases; #查看创建数据库的信息show CREATE database db_name; #查询当前操作所在的数据库名称select database ();

3. Select a database

1	USE db_name;

4. Deleting a database

1	DROPDATABASEdb_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

123456

创建用户    createuser‘用户名‘@‘IP地址‘identified by‘密码‘;删除用户    dropuser‘用户名‘@‘IP地址‘;修改用户    rename user‘用户名‘@‘IP地址‘; to‘新用户名‘@‘IP地址‘;

2. Authorization Management

123	show grants for‘用户‘@‘IP地址‘-- 查看权限grant权限 on数据库.表 to‘用户‘@‘IP地址‘-- 授权revoke权限 on数据库.表 from‘用户‘@‘IP地址‘-- 取消权限

#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 ... u Sage NO access ALTER use ALTER TABLE ALTER routine using ALTER p Rocedure and drop procedure create use the CREATE table create routine with CREATE proc Edure Create temporary tables use create temporary tables create user using create user, Drop U                  Ser, 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 use the Insert lock tables with the index insert to use the lock tabl    E process             Use show full Processlist Select to use Show databases with the Select Show                  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 List

Five. Change the password

Way One: mysqladmin command

1	mysqladmin -u用户名 -p密码 password新密码

method Two : Set the user password directly

123	setpassword for ‘用户名‘@‘IP‘ = password(‘新密码‘);            flush privileges;

Method Three: modify the user table under MySQL Library

1234567

5.7版本修改密码方式:updatemysql.usersetauthentication_string=password(‘新密码‘) whereuser= ‘用户名‘flush privileges; -- 刷新权限            5.6 版本updatemysql.usersetpassword= password(‘新密码‘) whereuser= ‘用户名‘flush privileges; -- 刷新权限

Six. Forgot password

12345678910111213141516

在忘记root密码的时候，可以这样:#1.首先打开cmd 窗口,关闭mysql服务    net stop mysql#2.然后跳过权限检查,启动mysql,输入命令    mysqld --skip-grant-tables #3.重新打开一个新的cmd窗口,启动客户端(已跳过权限检查,可以直接登录)    mysql#4.直接进来,修改密码updatemysql.usersetauthentication_string=password(‘123456‘) whereuser=‘root‘;#5. 刷新权限flush privileges;

Seven. Chinese garbled problem

1. Query character encoding

1	SHOW VARIABLES LIKE‘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'

solve the problem

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