MySQL simple face question

The difference between Drop,delete and truncate:
Priority: Drop > Truncate > Delete
TRUNCATE by row Delete does not log the delete operation record into the journal Save (unrecoverable)
Delete all delete the data in the table and can record the deletion in the log, you can add where words, can be applied to table and view
Drop deletes the entire table (structure and data) and then inserts the self-growth ID again starting at 1

Replication Fundamentals Process
Main: Binlog thread--record all the statements that changed the database data and put it into the binlog on master;
From: IO thread-After using start slave, it is responsible to pull binlog content from master and put it into its relay log;
From: SQL execution thread-executes the statement in relay log;

The difference between MyISAM and InnoDB
1.InnoDB supports things, and MyISAM doesn't support things
2.InnoDB supports row-level locks, while myisam supports table-level locks
3.InnoDB supports MVCC, while MyISAM does not support
4.InnoDB supports foreign keys, while MyISAM does not support
5.InnoDB does not support full-text indexing, while MyISAM supports

MyISAM with InnoDB in select COUNT (*) which is faster, why?
MyISAM is faster because there is a counter inside the MyISAM that can be directly adjusted

The difference between varchar and char:
Char is fixed in length, and the length of varchar can vary
For example, store the string "abc":
char (10) means that the stored characters will take up 10 bytes (including 7 null characters), which will be truncated if they are greater than 10 bytes and varchar is no different.
varchar (10) indicates a length of only 3 bytes, stored in actual length when the stored characters are less than or less than 10 o'clock

InnoDB has redo and undo two kinds of logs ...

MySQL binlog Several kinds of log differences
Statement: Every SQL that modifies data is recorded in Binlog
Row: Do not log the SQL statement context-sensitive information, only save which record is modified
Mixedlevel: It is a mix of the above two levels
Statement is more space-efficient when one SQL operates on multiple rows of data, and row is more space-intensive. But row mode is more reliable

When the CPU soared to 500%, first use the top command to see if the mysqld occupancy caused, if not, find the high-occupancy process
If it is the mysqld caused by the session, locate whether there is a resource consuming SQL running to find the high-consumption SQL and kill and then limit the number of connections or make other adjustments

The difference between mysqldump and xtranbackup:
In general, the 100G library, you can consider the use of mysqldump. Lightweight and flexible backup files are smaller and smaller after compression
More than 100G of library, you can consider using Xtranbackup. Faster than mysqldump.

Table:
Constraints
Self-growth: auto_increment
Non-empty: NOT NULL
Default value: ' XX '
Unique: Unique
Specify character Set: CharSet//Example: CharSet Utf-8
Primary key: Primary key

Increase:
CREATE TABLE Students (
ID int (auto_increment),
Name varchar (ten) is not NULL,
Sex varchar (3) Default ',
Age char (2),
Address varchar (50),
Phone char (one) is not null unique,
Primary key (ID));
mysql> desc students;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| ID | Int (15) | NO | PRI | NULL | auto_increment |
| name | varchar (10) | NO | | NULL | |
| sex | varchar (3) | YES | | | |
| Age | char (2) | YES | | NULL | |
| Address | varchar (50) | YES | | NULL | |
| Phone | Int (11) | NO | UNI | NULL | |
+---------+-------------+------+-----+---------+----------------+
Mysql> INSERT into students (Name,sex,age,address,phone) VALUES (' Zhangsan ', ' Male ', 26, ' Guangdong province Zhuhai Xiangzhou District ', 13356789124);
Mysql> INSERT into students (Name,sex,age,address,phone) VALUES (' Jinzhengna ', ' female ', 54, ' Guangdong province Zhuhai Gongbei District ', 15844551634);
Mysql> INSERT into students values (' ', ' golden Three fat ', ' male ', 58, ' North Korea ', 14444444444);
Mysql> INSERT into students values (' ', ' Junichiro Koizumi ', ' Male ', 78, ' Japan ', 14445554444);
Mysql> INSERT into students values (' ', ' Park Geun ', ' female ', 60, ' Korea ', 14445554774);
Mysql> SELECT * from students;
+----+------------+------+------+-----------------------------+-------------+
| ID | name | sex | Age | Address | Phone |
+----+------------+------+------+-----------------------------+-------------+
| 1 | Zhangsan | Male | 26 | Guangdong province Zhuhai Xiangzhou District | 13356789124 |
| 2 | Jinzhengna | Women | 54 | Guangdong province Zhuhai Gongbei District | 15844551634 |
| 3 | Golden Three Fat | Male | 58 | North Korea | 14444444444 |
| 4 | Kang? Male | 78 | Japan | 14445554444 |
| 5 | Park Geun-hye | Women | 60 | South Korea | 14445554774 |
+----+------------+------+------+-----------------------------+-------------+

INSERT into student (Name,money,sex,phone) VALUES (' HK ', 10000, ' Male ', 188);
INSERT into student values (' ', ' xiaoming ', 100, ', 120 ');

By deleting:
drop TABLE TableName; #删除整张表, the operation will be logged
TRUNCATE TableName; #删除整表数据, self-growth ID from the beginning, fast, deleted directly from disk, unrecoverable
Delete from student #删除整个表的数据, since growth continues

Check:
SELECT * from students limit 5;　　　　#查询前5条
SELECT id,name,sex,money,phone from students; 　　　　#指定查询的字段
SELECT * from students where sex= ' man ' and money>100; 　　　　#多个条件, the
SELECT * from students where sex= ' man ' or sex= ' unknown ' must be met at the same time; 　　　　#多个条件, there is one that satisfies the
SELECT * from students where sex! = ' male ';　　　　# <> is also not equal to
SELECT * from students where addr like '% Tokyo% ';　　　　#模糊匹配,% represents a wildcard character and must be used like
SELECT * from students a where stu_name like ' yao _ ';　　　　#_通配符表示任意一个单字符, Yao can only follow a word after the word
SELECT * from students ORDER by money Desc; 　　　　 #order by xxx desc, according to which field sort, default is ascending, descending is desc, ascending ASC
SELECT * FROM table ORDER by ID desc,name desc; #多个字段排序

Change:
ALTER TABLE oldtable rename newtable; #改表名
ALTER TABLE tablename Modify name varchar (20); #改表结构
ALTER TABLE tablename change name newname varchar (20); #改表结构
ALTER TABLE TableName add age float after name; #在name后面新增age字段
Update student set money=100; #不指定条件, modify All
Update student set money=110 where name= ' HK '; #只改hk

Backup library:
Mysqldump-u root-p mysql > ~/mysql.sql
To restore a library:
Mysql-u Root-p Web < ~/web.sql
Backup table:
Mysqldump-u root-p database Name Table name > Xxx.sql
Recovery table:
Mysqldump-uroot-p Database name < Xxx.sql

MySQL simple face question