Mysql authoritative guide Study Notes

Mysql authoritative guide Study Notes 1. mysql can contain up to 64 characters www.2cto.com 2, drop table table1, table2, and table3. When deleting multiple tables, separate them by commas, to avoid unnecessary errors, we try to use drop table if ixists table1; 3. For varchar, although it is of a mutable type, its maximum length is 255 characters, it is tested that it can contain a maximum of 255 characters and 510 Chinese characters, but if you set the varchar length, it cannot exceed this length. At this time, I defined a char type data, the length of his data will change accordingly. What I don't understand is whether the varchar type is used to save space? After MySQL version 5, are all variable data types?
The following data table solves my question: store the storage space required by the corresponding byte data type storage 144 byte string storage 30 character string maximum String Length char (150) 150 150 255 varchar (150) 145 31 255 tinytext (150) 145 31 255 text (150) 146 32 65535 mediumtext (150) 147 33 16777215 longtext (150) 148 34 4294967295
4. create table test_index (id int not null primary key auto_increment, name char (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; as long as the data before and after matching can be done, 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: u Pdate user set id = last_insert_id (id + 1) where id = 19; 7, auto_increment = 9; you can directly add the corresponding value after auto_increment for the primary key increment problem, the value is incremented by 8. MySQL's operators include +-*/= <>! = <> <=> = 9. The performance of between and in is 10 better than that of only using basic operators. The regular expression of MySQL is represented by regexp, regexp can be used to replace the like statement pattern. match any single character [] match any character in parentheses * match the zero or multiple ^ characters before the symbol. The content following the symbol must start with the value $ the content before the symbol must be the end of the value 11, select to_days (now ()-to_days ('2017-12-19 ') for calculating the specific days from the day to the day; 12. MySQL provides the date format function date_format () function 13: some functions of MySQL: soundex can check the pronunciation of the string, trim can remove unnecessary spaces, so that you can control the 'space' when logging on, enter 14, (optimized) to minimize database connection and disconnection, we recommend that you use the connection pool www.2cto.com 15 (optimized) index, usually in, betwe Using indexes in the like statement, without using indexes in the query using the formula, and using % in the like statement is not used before. The index is used only when the like statement is used separately, if the index is used too much, the maintenance overhead will be increased. However, if multiple fields in the where area are used as the index, the query efficiency will be greatly improved; the number of queries for nested queries is the product of the number of queries for two substatements, which greatly increases the query efficiency. Therefore, appropriately increasing the index greatly improves the efficiency. 16, (optimization) 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, you do not need to open or close the file key_buffer_size to control 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 www.2cto.com 17 (optimized) static MySQL versions are 18 faster than dynamic versions. (secure) Common Database roles are used to create category role environment privileges. Description developer: Development of delete, insert, select, update developer to write application code. Their privileges should only involve the development of the application called alter, create, delete, drop, index, insert, select and update impact ect design the database structure for specific applications. Their privileges should only involve the QA testing of the involved applications, such as delete, insert, select, and update QA, to test whether the application works normally, they need the same privileges as applications for use to support immigrator testing, trial run, product alter, create, delete, drop, index, insert, select, updateimmigrator in the product environment to upgrade the database structure, to reflect the changes required during the development process. Emigrator development, testing, and trial run select emigrator extracts the database mode from one environment, which can be transplanted to another environment where all DBA DBAs manage MySQL installation and run 19 (secure) grant (privileged, object, user) and revoke grant privileged commands and revoke privileged privileges: alter, create, delete, drop, file (access server files), index (create, modify, delete indexes), insert, process (grant the user the privilege to access the MySQL process thread and provide the show processlist and kill SQL statements), reload, select, shutdown, update, and usage (establish a connection ), privileges (all privileges, usually replaced by all) object: Database Name. the table name, or the table name under the current database, or * indicates all tables under the current database, or *. * It refers to all table users of all databases: Add @ after the specific user and write the specific access path. If it is random, it means "% ", if not, write the specific address www.2cto.com for the specific instance: grant select on yb. staff to primos; revoke: revoke select on yb. staff from primos; 20, (secure) When performing login verification, try not to take out the password to avoid leakage, but directly compare it as follows, you can also use password () method select id from user where name = "user" and password = password ("password"); this prevents the password from being copied and transferred in different buffer zones. (Design) entity (1NF In the first paradigm, 2NF In the second paradigm, 3NF in the third paradigm), data model, we can express the relationship between databases and tables through the specific cd. The example is derived from this book (chapter 7)
22, (Application) transaction isolation layer: Name Description read uncommitted transactions allow dirty, non-repetitive, and fantasy read committed transactions do not allow dirty reads, however, a non-repeated phantom read repeatable read transaction allows repeated and phantom READ committed. A non-repeated READ is an unsupported SERIALIZABLE transaction that only allows repeated READ committed, phantom read is not allowed 23, UDF function, mainly depends on C for programming 24, if you want to be compatible with ASNI, you should always use single quotes cited string 25, when type conversion specifies that the varchar column is smaller than four characters, it is converted to char as long as at least one column in the table is longer, all char columns with a length greater than three characters are converted to varchar.