[MySQL learning] MySQL collection

Author: gnuhpc
Source: http://www.cnblogs.com/gnuhpc/

I just read some books and forget them. We recommend that you take notes from professional websites.

1. The primary key value must be unique and cannot be blank. This increases the speed at which MySQL retrieves data from multiple tables or retrieves rows corresponding to the specified key value. MySQL achieves this through a special data structure called Index. index is a fast way to find a record, just like the card directory of the library.

2. Run the describe command to view the column definitions of a table.

3. The default integer column size is 11.

4. Use quotation marks during insert: Do not use numeric values, functions, or null. Enclose string values, times, and dates in quotation marks.

5. When you use alter to modify the table definition, even if the column data type is not changed, the column definition is still indispensable.

6. When using select statements, you should develop the table. Column Method to reference columns, so as to avoid confusion.

7. Select XX from YY order by ZZ for sorting results;

8. Using Natural join to natural join can avoid entering a long command: MySQL can connect the same name fields in two tables. You can use the left join method to connect tables.

9. Join on is also a join, but you must specify the column to be connected. Select * From books join authors on (books. title_id = authors. title_id );

10. by using aliases, you can replace a long table name with a short one. In addition, you can reference the same table twice in the same query to identify whether you are the first or second referenced table.

Select * From books as B, authors as a where B. title_id = A. title_id;

11. SQL statement features: What to do + where to start, for example, select from * Where title_id = 1;

12. Search: Select * from authors where author like "% B %"; the percent sign is a wildcard, indicating one or more. The underscore _ represents a character.

12. The connection between tables formed by containing the primary key of another table is called a foreign key relationship.

13. There are three types of database relationships: one-to-one, one-to-many, and multiple-to-many. Take an online bookstore as an example. The relationship between users and their mailing addresses (assuming only one address can be set) is one-to-one, while the relationship between the types of books and specific books is one-to-many, user-to-book relationship is multi-to-many relationship. Only when many-to-many relationships are converted into two one-to-many relationship transformation tables can they be reflected in the database.

14. Standardization:

The process of clarifying the relationship between data and finding the most effective way to organize data is called normalization. To standardize a database, we must start with the most basic rules and divide it into three stages: the first paradigm, the second paradigm, and the third paradigm. The latter must be completed on the basis of the former.

First paradigm: To solve data redundancy in the same row, all tables must not repeatedly contain the same data column, and all columns must contain only one value, you must have a primary key that can specify a unique row of data. The primary key can be one or multiple columns, depending on how many columns are required to specify a unique row of data.

Second paradigm: To solve data redundancy in columns, it is required that columns without repeated values should be referenced using the primary key of the original table in their separate tables.

Third paradigm: after the first two paradigms are met, the third paradigm is optional. You need to find the data in the table that is not fully dependent on the primary key but dependent on other domains, put the information in a separate table. However, this result produces too many tables, so normalization also requires a degree of control.

15. Back up the database: mysqldump-u root-PStore> My_backup_of_store. SQL

Back up one of the tables: mysqldump-u root-PStore authors> Authors. SQL

Back up a database completely: mysqldump-u root-p -- all-databases> my_backup. SQL

Create an empty copy: mysqldump-u root-p -- no-DataStore> Structure. SQL

Back up only the database data without backing up the database structure: mysqldump-u root-p -- no-create-InfoStore> Data. SQL

Note: regular backup is best practice

16. Recovery:

Mysql-u root-P <my_backup. SQL

For selective backup, use-D, MySQL-u root-p-dStore<My_backup. SQL

17. Import:

Mysqlimport-u root-p -- fields-terminated-by = ', 'store books.txt

18. Index:

The index data is sorted, and the data organization method optimizes the query. We need to determine which data domains are indexed. Each index is stored as a separate data file. The index must be defined before use. The benefits of using indexes are summarized as follows: queries matching index columns can be very fast, and the uniqueness of index values is very fast, the disadvantage is that the insert and delete operations on the indexed table are slow and extra storage space is required.

Create IndexAuthindOnAuthors (author); You can create indexes for multiple columns. The columns suitable for indexing are those that may be used in the WHERE clause. If you know that the combination of certain columns will be used, you can create a multi-column index for these columns.

19. Group by specifies the columns or columns used for grouping.

20. Concat can connect multiple domains in the result. Select Concat (title, 'has', pages, 'pages. ') from books;

+ ---------------------------------------- +
| Concat (title, 'has', pages, 'pages. ') |
+ ---------------------------------------- +
| Linux in a nuttshell has 476 pages. |
| Classic shell scripting has 256 pages. |
+ ---------------------------------------- +

21. Use a predefined separator to connect: Select concat_ws (',', author_id, title_id, author) from authors;

+ ------------------------------------------ +
| Concat_ws (',', author_id, title_id, author) |
+ ------------------------------------------ +
| 1, 1, Ellen siever |
| 2,1, Aaron Weber |
| 3, 2, Arnold Robbins |
| 4,2, Nelson Beebe |
+ ------------------------------------------ +

22. String Length:

Select Concat (title, 'has', length (title), 'characters. ') from books;

+ ----------------------------------------------------- +
| Concat (title, 'has', length (title), 'characters. ') |
+ ----------------------------------------------------- +
| Linux in a nutshell has 19 characters. |
| Classic shell scripting has 23 characters. |
+ ----------------------------------------------------- +

23. String Filling

Select lpad (title, 30, '.') from books;

+ -------------------------------- +
| Lpad (title, 30, '.') |
+ -------------------------------- +
|... Linux in a nutshell |
| ...... Classic shell scripting |
+ -------------------------------- +

24. Transactions

The database is forced to treat several changes as a unit of work, either all successful or all failed. If you use the storage engine to support transactions, such as InnoDB or bdb, you can run the "Start transaction" command to start a transaction. When a transaction ends, the change is committed through commit or rollback to cancel the change.

Author: gnuhpc
Source: http://www.cnblogs.com/gnuhpc/