SQL Rookie Study notes (i)

Just started to learn SQL, starting with the most basic statements, using a LOL database to do the experiment. Currently using the tool is MySQL Workbench, feel more comfortable, the interface does not take much time to read almost, so the current use of this tool to do SQL learning.

(1) Multi-criteria query, then modify the value. Because I'm here to edit a row that was originally a value of 0, a = 0 for a conditional search is used in a multi-conditional query.

The 0-value line just mentioned is like this, when the first copy line encountered a very wonderful error-some rows must have a default value to the table and the data between the copy and paste, so the default value is placed a 0 value.

(2) Carefully pondering the previous bug, it is just beginning to deal with too complex, directly to the NOT NULL option canceled, will not report this error, the default value is null.

Then there is a more wonderful thing, directly clear 0 value, and can not set the value to NULL, but instead of the value into a ", that is not counted as a space, there is no length of a value, so there is no null identity on the table.

The problem with this is that NULL is the search condition, and the rows of these values are not found.

I am whimsical, "since there is no length, then I will use the length function to search, the result will be what?" Sure enough, the rows that include this value are searched because the odd value is 0 and less than 1.

Because to enter this part of all the data, only according to the traditional method, identify.

After a look at the background, the relationship between the primary key and the modified SQL was not understood before, and it was originally established by where the primary key was associated with a value in the modified tuple, which was changed.

The background of this peculiar value is here, and this ' value is really memorable.

This null value is still given, click on a value, Set Field to NULL, so that the upper left corner of the null can be identified.

(3) April 26 wrote the first statement to use an inline (inner join) to bulk copy a column. The inline is a A, a, a, two table establishes a connection if a column has a commonality (at least one value matches). This value is usually the primary key, because the data entry for a table usually starts with a primary key.

After learning a lot of methods of copying columns, and forget the classic method, really should not. This also shows that the learning of SQL really have to learn to backtrack past statements, otherwise take the time to learn the new method is too inefficient, but also add unnecessary learning load.

—————— Divider Line ——————

(4 ) Number 26th also wrote the first multi-table join query that returns a multicolumn value. The syntax is not too difficult, SELECT a from B or the basic framework, where a is a representation of multiple tables, précis-writers is a. Element1,b.element2,c.element3 ... Since this place uses the a,b,c ... To do précis-writers, the back must also be supplemented by a note, a table refers to which table, B table refers to which table .... (PS: This code is not elegant, looks very messy, the left of a few ' X ' also let me see the frightened, but fortunately run successfully, also counted not white write small half a day)

Advanced Multi-table connection query, this time the overall look is very beautiful. Without learning SQL, I have always doubted the significance of database existence, and now it is understood that data classification to store, build associations, take out and read it is really much easier and more convenient than traditional Excel. Because of the presence of associations, data modifications are also easier to synchronize quickly.

(5) The drag of the column in the table, the reflection in SQL is after. For example C after b;b after a; then the order of the last table column (left to right) is a B C. After occurs at the end of the statement, before the semicolon, and the position of the comment is similar.

(6) Previously are single-valued or multi-form value query, multi-valued query is also separate write, efficiency feeling is not high, because the same statement to use OR and and connect a few times, very time-consuming. Later know in this keyword, much easier.

Incidentally get the use method of distinct. SELECT DISTINCT (a) is to remove the duplicate a value, which, of course, is created in the same way that the tuple is identical. since Studentnum and Lessonnum are federated primary keys, it is not possible to filter with distinct because each primary key (in this case, the Federated primary key) is different, i.e. the actual primary key is (A, b) (A,c) (b,c) (b,d) In this form, they are different, and naturally you cannot use distinct to filter the duplicate values contained in one of the values.

(7) Violent replication of table data, INSERT into statement is very useful, and for the first time know that the SELECT from statement can be so nested.

—————— Divider Line ——————

The language of SQL is really more interesting, but also very practical, after all, after all, big data will be more widely used, so the database will be the necessary technology.

The first rookie study notes also recorded ~ Road resistance and long, to their own refueling!

By the way, the last mentioned multi-table connection query statement.

Use lck2017springsplit;SelectA.racenum, A.racename, B.teama_protop_g1, C.teama_legendtop_g1, D.TEAMA_TOPDMG_G1, B.TEAMA_PROTOP_G2, C . Teama_legendtop_g2, D.teama_topdmg_g2, B.teama_protop_g3, C.teama_legendtop_g3, D.teama_topdmg_g3, B.TEAMB_PR       OTOP_G1, C.teamb_legendtop_g1, D.teamb_topdmg_g1, B.teamb_protop_g2, C.teamb_legendtop_g2, D.TeamB_TopDmg_G2, B.teamb_protop_g3, C.TEAMB_LEGENDTOP_G3, D.teamb_topdmg_g3 fromRegracename_num A join Regracenum_pros_top b join Regracenum_legend_top c join Regracenum_createdamage_top D on A.racenum= B.racenum and B.racenum = c.racenum and C.racenum = D.racenumwhereA.teama='KT'and B.TEAMA_PROTOP_G1 ='Smeb'or A.teama='KT'and b.teama_protop_g2 ='Smeb'or A.teama='KT'and b.teama_protop_g3 ='Smeb'or A.teamb='KT'and B.TEAMB_PROTOP_G1 ='Smeb'or A.teamb='KT'and b.teamb_protop_g2 ='Smeb'or A.teamb='KT'and b.teamb_protop_g3 ='Smeb';

Use lck2017springsplit;SelectA.racenum, A.racename, B.teama_protop_g1, C.teama_legendtop_g1, D.TEAMA_TOPDMG_G1, B.TEAMA_PROTOP_G2, C . Teama_legendtop_g2, D.teama_topdmg_g2, B.teama_protop_g3, C.teama_legendtop_g3, D.teama_topdmg_g3, B.TEAMB_PR       OTOP_G1, C.teamb_legendtop_g1, D.teamb_topdmg_g1, B.teamb_protop_g2, C.teamb_legendtop_g2, D.TeamB_TopDmg_G2, B.teamb_protop_g3, C.TEAMB_LEGENDTOP_G3, D.teamb_topdmg_g3 fromRegracename_num A join Regracenum_pros_top b join Regracenum_legend_top c join Regracenum_createdamage_top D on A.racenum= B.racenum and B.racenum = c.racenum and C.racenum = D.racenumwhereA.teama='KT'and B.TEAMA_PROTOP_G1 ='Smeb'and C.TEAMA_LEGENDTOP_G1 ='Rambo'or A.teama='KT'and b.teama_protop_g2 ='Smeb'and c.teama_legendtop_g2 ='Rambo'or A.teama='KT'and b.teama_protop_g3 ='Smeb'and c.teama_legendtop_g3 ='Rambo'or A.teamb='KT'and B.TEAMB_PROTOP_G1 ='Smeb'and C.TEAMB_LEGENDTOP_G1 ='Rambo'or A.teamb='KT'and b.teamb_protop_g2 ='Smeb'and c.teamb_legendtop_g2 ='Rambo'or A.teamb='KT'and b.teamb_protop_g3 ='Smeb'and c.teamb_legendtop_g3 ='Rambo';

SQL Rookie Study notes (i)