Asian Cup with database creation-wish the Chinese team and the Chinese team for database creation

Asian Cup with database creation-wish the Chinese team and the Chinese team for database creation

Original Works are from the blog of "Deep Blue blog". You are welcome to reprint them. Please note the following source when reprinting them. Otherwise, you will be held legally liable for copyright.

Deep Blue blog: http://blog.csdn.net/huangyanlong/article/details/42869063

 

The journey of building a database in Heilongjiang has given me a period of breathing and soothing flat. I'm more pleased that I was not paying attention to the opening of the Asian Cup after my intense work.

Today, with A mentality of looking forward to the Chinese team's next opponent in the Asian Cup, I watched group A's South Korea's focus on Australia. What I did not think of was that South Korea took the initiative in the first place, even though Cahill, the old veteran, was put on the stage after the Australian team fell short of the ball, and then began a flood of counterattack, but ultimately did not change the results. As Bahrain's referee blew the whistle for the end of the game, South Korea beat Australia with a 1-0 score. The Australian team who played the game lost to our "Asian" local team. Although it was a bit comforting, it also meant that Australia would be our next competitor in the knockout round. In the face of this team, which is regarded as the best team and the best team at home, we will have a hard fight.

During the complicated work, I had a slight flash in my mind. I think that the Chinese team's competition and oracle technology have more or less a little connection and a corresponding relationship.

 

Didi Yi:

Basic operations for creating a database: tablespaces, users, and tables

Football Match elements: tactics, players, and cooperation

1-1 tablespace

Create tablespace TBS_HYL_PERSON

LOGGING

DATAFILE '+ OCR_DATA/HYL/TBS_HYL_PERSON' SIZE 1000 M AUTOEXTEND

On next 100 M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

Segment space management auto;

-- Common SQL statements for creating tablespaces

Map out the playing system of the Chinese team and use the dual waist to protect the backend defense. In the previous two games, it was fully reflected that there was no point in planning the tablespace layout, as shown below:

Whether there are similar points in the tablespace planning mapped to the database, as shown below:

1-2 users

Create user hyl identified by hyl

Default tablespace TBS_HYL_PERSON

Temporary tablespace TBS_HYL_TEMP;

-- Common SQL statements for creating users. The default tablespace is TBS_HYL_PERSON and the temporary tablespace is TBS_HYL_TEMP.

Mapped to the Chinese team, Coach peran and our young men, like the following Chinese team lineup:

Around the Chinese Young Men around head coach peran, unity and cooperation are the guarantee of victory. recall that there are similar scenarios in which multiple users access and manage each other in a large application system, like the following:

1-3 create a table

Create table TB_HYL_USER

(Id varchar2 (32 ),

Name varchar2 (32 ),

Id_no number,

Loadtime date default sysdate

)

Tablespace TBS_HYL_PERSON

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64 K

Next 8 K

Minextents 1

Maxextents unlimited

);

For Table creation, we will design different tablespaces for different businesses in the business system. Similarly, in the Chinese team's competition, the young guys will have different ways of scoring, for example, any ball, corner ball, penalty kick, sticky ball, biaoyi, single-knife ball, and world wave ball.

Bit by bit 2:

Data Processing for database creation: common type conversion, junk data processing, and repeated Data Processing

Practical considerations: Hold the tactical system to slow braking, reduce mistakes, find the right time to grasp the pace of the game to brake

 

2-1 The ORA-01861 text does not match the Format String

Symptom: Oracle insert time error: ORA-01861: text does not match Format String

This error often occurs when data is inserted at the time. Because the source data is in the format of "2008/07/28", such errors are often reported when data is inserted to the DATE type.

The solution is simple. Use to_date to convert it. The syntax is as follows: to_date ('field name', 'yyyy-mm-dd hh24: mi: ss ').

Ing: In football matches, think about the format conversion relationship. When the opponent's pace is getting faster, our Chinese team uses the method of taking the initiative to press the pace through ball control. This way, after a transformation by a Chinese young man, the ball successfully pulled back to our appropriate pace. This is a solid tactical system, with slow braking.

 

Junk value in the 2-2 time field

This is actually very simple. If you are careful enough to view the data, you will find junk data such as 2098. Therefore, we need to process it. If the time is too long, it is better to solve it. For example, use the following command to find it:

SQL> select dengjishijian from table1 where dengjishijian> to_date ('20170101', 'yyyy-mm-dd ');

-- Assume that the current time is January 1, January 1, 2015, so it is easy to check that the time exceeds the current time point's garbage time value.

In addition to this simple situation, we sometimes encounter data in formats such as "20130241". Obviously, there will be no 41 days in February, which is also junk data, or sometimes there are Chinese characters such as "July November 1, 2103". During data extraction, it is sometimes necessary to process the data before it can be successfully inserted to the DATE field. We can write a simple stored procedure to find these junk fields. For example:

Write the stored procedure:

Create or replace function isdate (p_in varchar2, f_in varchar2)

Return varchar is

Scrub_dt date;

Begin

Scrub_dt: = to_date (p_in, f_in );

Return 'y ';

Exception when others then

Return 'n ';

End;

/

SQL> select start_date from FLUSH_TEST_DATE where isdate (start_date, 'yyyymmddhh24miss ') = 'n ';

-- Execute this statement to check the data that does not conform to the digital date format. Here, N refers to calling the stored procedure and throwing out fields that do not conform to the date format.

Ing: Mistakes are inevitable in football matches, but we should try to reduce them as much as possible, and make up for the fatal consequences of a player mistake through other members of the team after a mistake. Similarly, just like junk data in databases, the generation of junk data is inevitable, but this requires flexible and correct processing.

 

2-3 completely duplicate data

If duplicate data is found in the database, you must confirm it with the business R & D personnel before processing.

Sometimes, if you leave the service, you may mistakenly understand the meaning of the duplicate data, because the generated duplicate data may be meaningful.

When you confirm that you want to delete duplicate data, you can use the following method for query, for example:

SQL> select * from table_test where count_id in (select count_id from table_test group by count_id having count (count_id)> 1 );

-- The subquery in this SQL statement uses having as the condition to query the count_id with a field repeat greater than 1, and then uses this count_id to query the data that meets the same count_id field.

 

Ing: Sometimes there will be completely duplicated data in the database. This may be caused by repeated data extraction from another database, which requires us to make a judgment, the duplicate data can be cleared as soon as possible, and the brakes should be triggered. In the Chinese team's competition, this is also the case. We cannot always use slow braking. Sometimes we need to use dynamic braking to combat the competition in Uzbekistan. When Uzbekistan takes one lead, and when we want to control the pace of the game, the Chinese team's young men used the speed to rush out of a threatening attack and finally turned around. This is not just a slow brake, we can also use dynamic braking. O (∩) O Haha ~

 

Bit by bit 3:

Online Business optimization: Create a joined field Index

China's long-term goal: to train troops for the Russian World Cup qualifying tournament in 2018

 

Create an index

Create index idx_TB_HYL_USER_id on TB_HYL_USER (id );

Create index idx_TB_HYL_USER_DM4 on TB_HYL_USER (SUBSTR (HYL_USER_DM, 0, 4 ));

-- For long numeric code, such as regional code, we can intercept fields to create indexes.

In business applications, there are often a large number of SQL statements associated with queries. In this case, we need to make judgments based on the business system as soon as possible. If conditions are met, we should first perform a test, then, relevant indexes are created on the production database in a timely manner to relieve the pressure on the business. This is a necessary preparation.

 

Ing: Database optimization has always been a major event and can help the business system to survive. Our young Chinese font team also needs to make optimizations and establish a molding style of play system to help the players form a tacit understanding, this requires the tactical planning of coaches and the efforts of young men. We are looking forward to it...

The Chinese team's performance in the Asian Cup was truly impressive. These young boys made the fans regain their expectations for the National Team during the cup. This expectation has always existed, but the performance of the Chinese team has always disappointed the fans. This time, we hope our Chinese boys will put down their burdens, do not be influenced by the pressure of public opinion. Go forward and hope to see them in the Australian Division Finals.

 

We wish the Chinese team a better chance to go further in the Asian Cup. We hope that the Chinese team will be expected in 2018, and we will look forward to seeing them together in Russia ~~~~

 

Deep Blue is recorded in January 17, 2015

 

Statement:

This is not a simple technical document. Since I have learned a few tricks about oracle, I cannot afford to sell it to anyone. Just after a meal, I joined several friends in the library. It is just a bit humorous and it can also be used to make fun of others.

At the same time, I have forgotten a lot of oracle knowledge points in recent months. In order to reproduce the knowledge points, I am inspired to publish them in a free form, the examples or stories listed in this article are bound to be far-fetched in terms of technical connection. It's just a note from cainiao. Don't use it as a technical article! I have read and laughed.

Welcome to shoot bricks. This will be the biggest motivation for my growth.

 

Original Works are from the blog of "Deep Blue blog". You are welcome to reprint them. Please note the following source when reprinting them. Otherwise, you will be held legally liable for copyright.

Deep Blue blog: http://blog.csdn.net/huangyanlong/article/details/42869063