mysql-Database Overview 2

First, Version introduction

Community Edition: Free, personal use, can not be commercially used, out of the problem of their own solution, no technical support

Enterprise Edition: Charges, commercial use, emphasis on basic services, the number of CPUs between 1-4, the cost of 40,000/year; more than 4, 70,000/year

Cluster Edition: Charge, commercial, focus on cluster setting, CPU number between 1-4, cost 70,000/year; more than 4, 140,000/year

Second, yum installation

MySQL: mainly provides management tools

Mysql-server: Provides basic modules and functions

Third, start the database

Loaded content and files under the database

Description: Ibdata1, IB_LOGFILE0, Ib_logfile1 are database index files, used to quickly locate the data, Mysql.sock socket file, bind the database IP, port, etc., want the user to connect access, it must have this file

Database file Explanation:

. frm File: Data table structure for storing data

. MyD files: Specific data for storing data tables

. Myi File: Index file for the current data table

Iv. Login Database

Mysql-u Users

-p password

-H log on to which server

-P Specify port

A. mysqladmin: Tools to add passwords

Format: mysqladmin-uroot password ' 123456 '

B. Change root password: after root login

Set Password=password ("654321")

C. Modify the normal user password, after root login

Set password for ' user1 ' @ ' localhost ' =password ("654321")

D. After forgetting the root user password, reset the password step:

1. Stop the database service mysqld stop

2. Modify the configuration file my.cnf, add Skip-grant-tables

3. Start the database service mysqld Stat, without password direct root login

4. Reset Password update mysql.user set Password=password (' 123 ') where user= ' root '

5. Modify the configuration file, delete the Skip-grant-tables

6. Restart the database service mysqld restart

7. New Password Login

V. Create a database user

Format: Create user [email protected] ' 192.168.1.0/24 ' by identified ' 123456 '

VI. database Operations (Backup and restore of multi-machine clusters)

Increase

1. Create databases DB1

2. Create Tables Tb1 (field 1 type 1, field 2 Type 2 ...)

Creating databases and tables, using the Create command, type- field constraints

3. insert into tb1(id,name,info) values (1, ' Zhangshan ', ' student ')

String values are enclosed in single quotes, and red can be used without writing, inserting all field values, noting the corresponding order, and using the insert into command

4. Insert into TB1 (name) VALUES (' Lisi ')

Insert part of field data

5. Insert into TB1 values (1, ' Zhangshan ', ' student '),(2, ' Wangwu ', ' student ')...

Inserting multiple rows of data at the same time

6. Insert into TB1 (ID) Select ID from TB2

copy the ID value in TB2 to the ID field of the TB1, two command merges, the ID type must be the same

by deleting

1. Drop Database db1

2. DROP table Tb1

deleting libraries and tables, using the drop command

3. Delete from tb1 where id=2

Delete the id=2 row of data, it is best to use the primary key to identify the row, avoid deleting the other rows, use the Delete command

Modified

Description: Update change data, alter change table

1. Update tb1 set info= ' student ' where id=5

Change a piece of data and use the Where to locate

2. Update tb1 set info= ' student ' where ID between 4 and

Change multiple data with range matching between and

3. ALTER TABLE TB1 rename tb10

Change the table name to use the rename command

4. ALTER TABLE TB1 modify name char (+) NOT null default '

Change the field type, such as the char length of name, and other properties, such as default, with the Modify command

5. ALTER TABLE TB1 Change info Information char (+) NULL

Change the name of the field, use the Alter command, and modify the other properties as well

6. ALTER TABLE TB1 drop Name

Delete a field and use the Drop command

7. ALTER TABLE TB1 add time Date first

Add a field, use the Add command, date to represent the type, first means to put in line rendering, no second option, no default last row;

8. ALTER TABLE TB1 add nian year after time

The after command indicates that the insert is behind a row

Check

1. Show databases

2. Show tables

3. Show Engines\g

4. Desc TB1

5. Select field Name/* FROM TB1

Field: Fields Properties

Null: null to allow numeric value

Key: A primary KEY (constraint), a field name with a unique numeric value that can be quickly positioned to a row, such as an ID

Default: When no value is entered, a null

Extra: Additional parameters

6. Show global variables like '%log% '

View the variables associated with the log

Bin start: Binary log information, master and slave configuration MySQL, logs from the server will also be generated in the main error log

General start: Generic query log information

InnoDB Start: Transaction log information

Relay Start: Relay log information

Permissions

1. Grant all on db1.* to ' user1 ' @ ' localhost ' identified by ' 123456 '

Authorize USE1R to log on locally and have all permissions for the DB1 library

2. Show grants for ' user1 ' @ ' localhost '

View User1 log on locally and what permissions you have

3. Revoke Select on *. * to ' user1 ' @ ' localhost '

Remove User1 query permissions after local login

Six, MySQL Database client graphical interface management tool

1. MyDB Studio

2. PhpMyAdmin

3. SQLyog

4. Navicat for MySQL

mysql-Database Overview 2