1. SQL sentence

SQL classification ：

Data definition language ： abbreviation DDL(Data Definition Language), Used to define database objects ： database , surface , Column, etc. . keyword ：create,alter,drop etc.

Data operation language ： abbreviation DML(Data Manipulation Language), Used to update the records of the tables in the database . keyword ：insert,delete,update etc.

Data control language ： abbreviation DCL(Data Control Language), It is used to define the access rights and security level of the database , And create users .

Data query language ： abbreviation DQL(Data Query Language), Used to query the records of tables in the database . keyword ：select,from,where etc. .

2. data type

• char It's a fixed length ,varchar It's getting longer ,char When the storage , If the number of characters does not reach the defined number of digits , It will be filled with spaces to the specified length , and varchar If the defined number of digits is not reached, it will not be filled , Store in actual length .
•  char Fixed length ,char Access speed is still faster than varchar It's much faster , Convenient program storage and search ; however char It's also the price of space , Because its length is fixed , So it takes up extra space , It can be said that space in exchange for time efficiency .varchar On the contrary , Time for space .

3. Database operation

view the database

view the database MySQL All the databases in the server :

show databases;

View information about the definition of a database :

show create database Database name

show create database user;

Delete database

drop database Database name :

drop database user;

See the table

Look at all the tables in the database

show tables;

View table structure ：

There are two ways

Method 1 ： desc Table name ;

Method 2 ： SHOW COLUMNS FROM Table name ;

Modify the table structure format （ Most commonly used in actual development ）

alter table Table name add Name type ( length ) constraint ;

ALTER TABLE user ADD udesc VARCHAR(20);

Add multiple fields :

/* Add multiple columns method 1 */
ALTER TABLE user
ADD unameVARCHAR(32) NOT NULL,
ADD utel CHAR(64) NOT NULL;
/*add Statements are separated by commas , End with a semicolon */
 
/* Add multiple columns method 2 */
ALTER TABLE user
ADD (unameVARCHAR(32) NOT NULL,utel CHAR(64) NOT NULL);

It is worth noting that ：

If the table needs to add multiple columns , And there is a column of fields uname It has been added before , The results are displayed Duplicate column name 'uname', Then the multi column fields you added this time are invalid , That is, adding all failed

What is slow query log ？ How to configure

When the query does not return results for a certain period of time , Will be recorded in the slow query log .
• Slow query logs can help DBA Find out what is slow to implement SQ sentence , Provide help for database optimization .
• Slow query log is not enabled by default , It is recommended to start slow query log .
• When sampling analysis is required, start it manually .
【 Slow query log on 】
About slow query log , It mainly involves the following parameters ：
slow_query_log ： Whether to enable the slow query log function （ Required ）
slow_query_log_file： Slow query log file and location
long_query_time ： Over the set value , Will be treated as slow queries , And record it in the slow query log file （ Required ）
in other words , Only if the above three conditions are met ,“ Slow query function ” Can it be opened correctly .

The parameter file is modified as follows ：
Modify the configuration file my.cnf, stay [mysqld] Let's add the bottom of the bottom
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
restart MySQL service service mysqld restart that will do

4. Database design principles

The principles of index design

1. The columns that are suitable for indexing appear in where Column in clause , Or the column specified in the join clause
2. A class with a smaller cardinality , Poor indexing , There is no need to index this column
3. Use short index , If you index long string Columns , You should specify a prefix length , This can save a lot of index space
4. Don't over index . Index requires extra disk space , And reduce the performance of write operations . When modifying the contents of the table , The index will be updated or even refactored , More index columns , The longer it will be . So just keeping the index you need is good for querying .