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;

#创建一个名字为 db_name Database and specify the encoding set for the current library to utf8create 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 user ' alex ' @ ' localhost ' identified ' 123456 '; #授权方式一: A query for all tables under the DB1 database that Alex authorizes. Update. Modify permissions Grant Select, Update,delete on db1.* to ' Alex ' @ ' localhost '; #授权方式二: Give Alex all permissions (except grant permissions) for all libraries grant all privileges on *. * to ' Alex ' @ ' L Ocalhost '; #刷新用户权限flush privileges; 

 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 set global. Also allows mysqladmin to debug login replication client server location access replication slave used by replication slaves

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

#修改方法: #1. Create the My.ini file and place it under the MySQL root path. Add the following to the file: #3. After you add this file, you need to restart the service to ensure that the file is in effect------------------------------------------------------------[client] Default-character-set=utf8[mysql] #设置mysql客户端默认字符集default-character-set=utf8[mysqld] #设置3306端口port = 3306# Allow maximum number of connections max_connections=200# the character set used by the service side defaults to a 8-bit encoded latin1 character Set character-set-server=utf8# The default storage engine that will be used when creating a new table default-storage-engine=innodb# resolves a 1055 error after MySQL executes the SQL statement, Sql_mode = only_full_group_by incompatible sql_mode= ' No_engine_substitution '

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