MySQL authoritative guide Study Notes

《Mysql##(2 2 2014-088文 download www.linuxidc.comlinux2014-0398821.htm 1, mysql has the most identifiers

MySQL authoritative guide (original book version 2nd) Clear Chinese scan version PDF download http://www.linuxidc.com/Linux/2014-03/98821.htm 1, mysql most

MySQL authoritative guide (original book version 2nd) Clear Chinese scanned version PDF download

1. mysql can contain up to 64 characters

2. drop table table1, table2, table3; Use commas (,) to delete multiple tables. To avoid unnecessary errors, try to use

Drop table if ixists table1;

3. For varchar, although it is a variable type, it can contain a maximum of 255 characters. It is tested that it can contain a maximum of 255 characters and 510 Chinese characters.

However, if you set the length of a specific varchar, it cannot exceed this length. At this time, I defined a char type data and checked its data length.

It will change accordingly. What I don't understand is whether the varchar type saves space? After MySQL version 5, are all variable data types?

The following data table solves my question: storage space required for storing the corresponding bytes

4. Two ways to create an index

Mysql> create table test_index (id int not null primary key auto_increment, name c

Har (54) not null, index index1 (id, name), unique index index2 (name ));

Create index index3 on test_index (id );

5. You can directly query and insert data as follows:

Insert into user (name) select name from user limit 1;

You only need to match the data before and after.

You can also use various restrictions in the select statement.

Insert into user (name) select name from user order by id desc limit 1;

6. Use of the last_insert_id () function:

Update user set id = last_insert_id (id + 1) where id = 19;

7, auto_increment = 9; for the primary key increment problem, you can add the corresponding value directly after auto_increment to increment the value.

8. MySQL operators include +-*/= <>! = <> <=> =

9. between and in have better performance than only using basic operators.

10. The MySQL regular expression is represented by regexp. You can replace regexp with the like statement we know well.

Mode description

. Match any single character

[] Matching any character in parentheses

* Matches zero or multiple characters before the symbol.

^ The content following the symbol must start with a value.

$ The content before the symbol must end with a value

11. Calculate the specific number of days from the day to the day.

Select to_days (now ()-to_days ('2017-12-19 ');

12. MySQL provides the date format function date_format ().

13. Some functions of MySQL: soundex can check the character string pronunciation, trim can remove unnecessary spaces, so as to control the 'space' input during login

14. The connection pool is recommended to minimize database connection and disconnection.

15. Use of (optimized) indexes. indexes are usually used in, between, and so on. indexes are not used in queries using formulas, % is not used before the like statement. Therefore, indexes are used only when they are used separately. If you use too many indexes to increase the maintenance overhead, however, indexes can be used for multiple fields in the where clause, which greatly improves the query efficiency. The number of queries for nested queries is the product of the number of queries for two substatements, this greatly increases the query efficiency, so appropriately increasing the index greatly improves the efficiency;

16. (optimized) server optimization and configuration may affect other applications:

Table_cache controls the cache size of MySQL tables. Adding this parameter allows MySQL to have more tables that can be opened at the same time without the need to open or close files.

Key_buffer_size

Controls the buffer size used to save the index. By adding this parameter, you can create and modify the index so that more indexes can be saved in MySQL memory.

17. (optimized) the static MySQL version is faster than the dynamic version.

18. (Security) Common Database role creation categories

Immigrator testing, trial run, product

Alter, create, delete, drop, index, insert, select, update

Immigrator upgrades the database structure to reflect the changes required during development.

19. (Security) grant (privileged, object, user) and revoke grant privileged commands and revoke privileges

Privileges: alter, create, delete, drop, file, index, insert, and process, provides show processlist and kill SQL statements), reload, select, shutdown, update, usage (establish a connection), privileges (all privileges, usually replaced by all)

Object: database name. Table name, or table name under the current database, or * indicates all tables under the current database, or *. * indicates all tables of all databases.

User: Add @ to a specific user and write down the specific access path. If it is casual, it is "%". If not, write the specific address clearly.

Specific instance:

Grant: grant select on yb. staff to primos;

Revoke: revoke select on yb. staff from primos;

20. (secure) When performing login verification, try not to retrieve the password to avoid leakage, but directly compare it as follows. You can also use the password () method

Select id from user where name = "user" and password = password ("password ");

This prevents password duplication and transmission in different buffer zones.

21. (Design) entity (1NF In the first paradigm, 2NF In the second paradigm, 3NF in the third paradigm), data model,

We can use cd to represent the relationship between tables created in databases. The example is from this book (chapter 7)

22. (Application) transaction isolation layer:

Name Description

Read uncommitted transactions allow dirty, non-repetitive, and fantasy reads

COMMITTED transactions do not allow dirty reads, but allow non-repetitive Phantom reads

Repeatable read Transactions allow committed duplicate reads and Phantom reads. Non-duplicate reads are not allowed.

SERIALIZABLE transactions only allow repeated committed reads. Phantom reads are not allowed.

23. UDF mainly depends on C for programming.

24. If you want to be compatible with ASNI, you should always use single quotes to quote strings

25. type conversion

When the size of the specified varchar column is less than four characters, it is converted to char

As long as at least one column in the table is variable-length, all char columns with a length greater than three characters will be converted to varchar