Shulou(Shulou.com)06/01 Report--

1. View:

1. The definition of the view: the view is the mapping result of the physical table. If the physical table changes, the view must change.

Create view v_name as select column name (can also be the result of a function calculation, such as avg ()) from table

2. The function of the view:

2.1. Permission control

For example, some columns allow users to query, other columns are not allowed.

You can open one or more of the columns through the view to play the role of permission control.

Such as the purchase price in the commodity table, purchasers and other important information, the general user can not see.

2.2. Simplify complex query statements.

3. Can views be updated, deleted or added?

If each row of the view corresponds to the physical table one-to-one, then the view changes the physical table.

If the rows of view are calculated from multiple rows of the physical table, the view cannot be updated.

Second, the difference between delete and truncate:

1. Delete is DML and just deletes the data in the table.

2. Truncate is DDL, delete the table first, and then create the table.

For example: create a list of self-growing tables, and you can make it clear at a glance.

III. Index:

Indexing is a good and inexpensive way to improve database performance. Do not need to add memory, do not change the program, do not need to adjust sql, just need to build the index correctly, you can increase the query speed a hundredfold. However, there is no free lunch. Indexing improves the query speed, but slows down the speed of adding, deleting and changing.

1. An index is a directory of data, which can quickly locate the location of row data.

2. The index improves the query speed, but slows down the speed of adding, deleting and changing (the index table should also be changed when adding, deleting and changing), so it is not that the more indexes, the better.

3. The index is generally added on the columns with high query frequency, and the effect is better on the columns with low repetition.

4. Index classification:

(1), index | key general index: improve the query speed.

A. Specify create table test (id int,key (id)) when creating the table

B. Add: alter table table name add index (id) or create index index_name on tableName (column) after creating the table

(2), unique | unique key unique index: speed up the query, and the constraint data cannot be repeated.

A. Specify create table test (id int primary key,name varchar (30), email varchar (30) unique) when creating the table

Or create table test (id int primary key,name varchar (30), email varchar (30), unique (email))

B. Add: alter table table name add unique (column) after creating the table

(3) primary key primary key index: speed up the query, and the constraint data can not be repeated.

A. Specify when creating:

Create table test (id int primary key)

B. Add: alter table table name add primary key (column) after creating the table

(4) fulltext full-text index:

It is mainly for text retrieval, which is only valid for MyISAM engine and not valid on InnoDB. And it is almost invalid in the Chinese environment, which requires word segmentation and indexing. Third-party solutions such as compass are generally used.

(5), the difference between primary key index and unique index:

The primary key index cannot be empty and cannot be repeated; the unique index can be empty and cannot be repeated.

4. Commonly used sentences:

1. Show databases: show all databases

2. Show create database database name: displays the statement that creates the database.

3. Show create table table name: displays the statement that creates the table.

4. Show index from table name: displays the index of the table.

5. Show table status: the current library displays the status of all tables

6. Show table status like table name: the current database displays the status of a table

7. Select database (): displays the current library name.

8. Select user (): displays the current user.

9. Explain sql statement: displays the execution status of the sql statement.

10. Add / remove indexes:

Alter table table name add [index | unique] index name (column name)

Drop table index index name on table name

11. Add / remove primary key index:

Alter table table name add primary key (column name)

Drop table primary key on table name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.