Database knowledge Collation < two >

Continue to write blog, I hope I can persist in blogging every day. Sharing your own bits and pieces is helpful to your own growth. This afternoon high-intensity played three hours of basketball, calf cramps. It was cool, and the lost mood seemed to have become enlightened. Think of a sentence: "Like SB-type insist always have a good harvest", because determined the goal, not to reach the goal. The rest is nothing. Well, not much of that. Continue with the database knowledge summary.

two. Managing Databases and tables:

2.1 Managing Databases:

• To create and use a database:

1. SQL statement for creating database: Create database name;
2. It is important to note that the database name must be unique on the server and follow the rules of the valid identifiers:
3. The first character must be A-Z and a-Z, an underscore (_), an at sign (@), and a number sign (#).
4. The following characters can be: letters, decimal digits, @, $, #, _. Cannot be a reserved word for an RDBMS, spaces, or other special characters are not allowed.

• To link to a database:

1. Use that database as the SQL statement: using the database name;

• To delete a database:

1. SQL statement to delete database: drop database name;

2.2 Data type:

• Integer data type:

1. TINYINT: Storage range 0-255, this type of data consumes 1 bytes of storage space.
2. A smallint:2 byte.
3. A int:4 byte.
4. A bigint:8 byte.

• Floating-point data type:

1. Real type
2. Float type
3. Decimal type
4. Numeric type: Exactly the same as the person above.

• String type: Char, Vchar, text
• Binary data type: binary, VARBINARY. (must be preceded with ox identification to be a binary data type)
• Logical data type: BIT-0 or 1. If you enter a value other than 0 or 1, unification will be treated as 1. and cannot be empty empty is meaningless.
• Graphic data type: image
• Date Time data type: DATETIME, smalldatetime.
• Currency data type: money, smallmoney. The currency data type must precede the data with a currency symbol before the system can identify the currency of the country. If you don't think ￥.
• Special data types: TIMESTAMP, uniqueidentifier.

2.3 Management tables:

• Create a table's SQL statement: Create table< table name > (< column name >< column data type >[< column constraint;]);
• Delete a table's SQL statement: Drop table< table name >; (Note: When deleting a table, be aware of the foreign Key Association, if there is a foreign key associated with the first delete the Foreign Key association table in the Delete this table, but also to see if there is permission to delete this table)
• Create temporary tables: the so-called temporary tables are tables that exist for a limited period of time, and the way to create temporary tables is simply to precede the table names with # and # #. Add # to this surface and add # # to the global table.
• Copy table: The only way to create a new table in a database is to copy a table that already exists. SQL statements such as: SELECT * to My_friends from Friends; the conventions for replicating tables at the same time cannot be duplicated, so it is recommended to create tables and data using the Creat method. When copying a table, you can manually change the constraint of the table by alter. If we just want to copy the structure of the table instead of the data, we can use the SQL statement like this: SELECT * into My_friends from Friends where 1=0; let the following condition always be false.
• Modify tables, ALTER TABLE statements together with other options can change the data structure of the tables:

1. Add a field that is not in a table Address:alter table Friend add Address varchar (50);
2. If you want to add more than one column: Alter TABLE Friend add Address varchar, Email varchar (30);
3. If you want to change the column definition, you can use SQL statements, such as adding a default value constraint to the phone column, default to ' Don't know phone number ': ALTER TABLE Friend Modify phone default (' Don't know phone number ');
4. To delete a Phoneno column in a table: ALTER TABLE Friend drop column Phoneno;

2.4 Managing indexes (very important, often asked by the interviewer):

Index meaning: an index is a database object designed to provide an overall database operation speed, which is achieved by creating an internal index table for fast search purposes. Indexes are built on tables, and creating an index on a table can increase the speed of executing a SELECT statement on that table, because the index is used to quickly find the desired record instead of performing a full table scan, But it reduces the delete, update, The execution speed of the INSERT statement is because the internal index structure needs to be updated each time these operations are performed, and the benefits in most cases outweigh the disadvantages.

Note: The index can be unique and non-unique. A unique index does not allow duplicate values to appear on an indexed column. A unique index is typically created on a column that has a primary key or a unique constraint. Indexes that are automatically created by setting a primary key or UNIQUE constraint are automatically deleted after the constraint is deleted and cannot be deleted individually.

1. CREATE INDEX: SQL Statement-create index < index name > on < table name > (< column name >,[< column two;],... ）;
2. Delete index: SQL statement-drop index. For example: Drop index friend.phoneindexno;

　　

Summarize:

1. The indexing of database tables is very important and will be favored by most interviewers.
2. Remember the last interview, the interviewer asked me a query about the knowledge, he said: We query 10 data quickly added only 0.03 seconds, according to this speed into tens of millions of data we can not find se years, ask me how to improve the speed of query?? In fact, this question really TM asked the straightforward, do not blame the interviewer deliberately difficult for me. This is not the function of the index naked told me, the poor did not give me explicit. So I deserved to be brushed down. Kill me, and I won't forget it.-Index. With it, my mother would never worry about me checking a lot of data. Then I have to feel the charm of it again.

Database knowledge Collation < two >