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

View:

View is used to wrap sql query statements, sometimes a query statement may have to write dozens of lines, if every time to send such a long query statement to the server is not good, and every time to write such a long statement is also more troublesome and time-consuming, so view is used to solve this problem, view will query statements wrapped into a table. Therefore, views are also called pseudo-tables and virtual tables, because in fact, when using views, they are similar to using tables.

Create a view:

The keyword is VIEW.

Code example:

View has three engines MERGE, TEMPTABLE, UNDEFINED:

In general, most of them use MERGE engine, and UNDEFINED engine is an algorithm engine of mysql.

Code example:

Modify View:

Modify the view by using the modify keyword, just like modifying the table, except that the data in this table is an sql query statement.

Code example:

Delete View:

Delete views as you delete tables with DROOP.

Code example:

Form design:

When designing tables, we should consider many aspects, and the tables are generally not changed after they are designed. Therefore, when designing tables, we should think about how to design tables without changing the table structure in the future, and the tables designed should be flexible and able to adapt to some possible situations.

Examples of incorrect table designs:

The design errors in the above figure include: 1. Automatic numbering should not be used 2. Gender should not be used Boolean 3. Telephone numbers should not be used int 4. Hobbies should not be designed in this table

Question 1: What if the int type is automatically numbered? What if the amount of data accumulates to a certain extent and the int type is not enough? You don't know how long a system will run, but if it runs for many years, it may accumulate more data than int types can store, so you shouldn't use auto-numbering in company development, you can use varchar types.

Question 2: Boolean type has only two values true and false, although the gender seems to be only male and female, but what if you need a confidential option or need other options? So it's better to use varchar type, even if you use enumeration type is not too good, because enumeration type is also a dead option.

Question 3: Although domestic telephone numbers are generally 11 digits, what if you need to store foreign telephone numbers or telephone numbers and landline numbers of special administrative regions? So you can't use int, you should use varchar.

Question 4: A person can have many hobbies. If all hobbies are stored in one field, it is impossible to modify, delete or change a hobby individually. Hobbies should be placed in a separate table, and then form a corresponding relationship with the student table, so as to better manipulate the data.

Examples of the correct form design:

design idea

1. How to design tables

1. Must have primary key

2. Each row must be distinguishable

3. A column cannot contain multiple values.

4. multipurpose string type

5. Do not use big data types when designing tables

6. No duplicate values are allowed. One table cannot duplicate data already existing in other tables.

2. How do we put business into tables?

One-to-many one-to-many foreign keys

One-to-one primary key pair primary key

Many-to-many requires three tables to maintain

for example

One person can learn many subjects

A course can be taught to multiple students

A character can have many functions.

A function can be assigned to many roles.

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.