MySQL review article and a one-to-many and many-to-many summary (17.6.26)

First, the question

1.varchar differs from Char

A: Char can only store the specified character length, and the character length of the varchar store is variable

Example char (ten), varchar (10), 10 means that the character length is 10,char indicates that only 10 characters greater than 10 are not stored less than 10 also by 10 positions, varchar, how much to deposit as much as you want.

Char pros and cons: High efficiency, wasting memory compared to varchar.

varchar advantages and disadvantages, space saving, low efficiency.

2.select * from T1 the where Nid in (all in all) code meaning;

Answer: Only the specified data is displayed

　　

　　

3.mysql How to achieve interval query;

Answer: SELECT * from Preson where Nid betwenn 2 and 5;

　　

4. How to sort the contents of the query tip: DESC

     select *  from 表  order by 列  asc - 根据 “列” 从小到大排列      select *  from 表  order by 列  desc - 根据 “列” 从大到小排列      select *  from 表  order by 列1  desc ,列2  asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

5. Limit Code hints:

     select* from表 limit 5;          - 只显示前5行；

     select *  from 表 limit 4,5;         - 从第4行开始，显示后面的5行；      select *  from 表 limit 5 offset 4    - 和上面一样

6. Grouping (aggregation function) code hint: Select from T1 GROUP by partment

: SELECT COUNT (NID2) from T1 GROUP by partment

: Select count (nid) from T1 GROUP by Partment have Count (nid) >2;

#sum avg min Max

For:

　　　　selectnum from 表 group bynum  # num列中只显示不重合的

     select num,nid  from 表  group by nid,num #nid列中显示所有不重合的，num依附nid   　　　　      select num,nid  from 表   where nid > 10  group by num,nid  order nid  desc#显示 the NUM and NID columns in the table, condition one, nid value greater than 10, result processing: num grouping, and NID column large to small order.      select num,nid, count (*), sum (score), max (score), min (score)  from 表  group by num,nid# display columns, grouping;

　　

7. Use T2 to search T1 (cross-table operation) code hint: select name email from t1 where Nit in

: select name email from t1 where Nit in (select Nid from T2)

A: The name and email prerequisites are shown in T1: Nid is the NID in the T2 table

8. The query results renamed, code hints: select Name as n, email from T1;

A: Modify the resulting header to ' n '

9. Verify that the results of the Pymysql are returned after the INSERT, update and deletion has been performed;

A: The result is a num value, which means a few lines are deleted, a few lines are added, and several lines have been modified.

10.pymysql gets the ID of the inserted content;

A: new_id = Cursor.lastrowid can get the number of rows of new data;

Two or one-to-many and many-to-many

A. A multi-to-many

1, you know a pair of more;

A: The mechanism by which a table uses data from another table without re-creating duplicate data;

A one-to-many means that an item in the main table can have a relationship with an item in the secondary table (monogamy);

2.1, Navicat for MySQL implementation of a pair of more;

A: To achieve success, attention, it is best to create a new table when the foreign key is created, or the back is very troublesome

Table Design ==> foreign key ==> field (the column you want to draw) ==> reference table ==> Reference field ==>ctrl+s

2.2, code implementation;

CREATE TABLE Person1 (Nid INT (one) auto_increment,name varchar (+), email varchar (+),  Part_nid int,primary Key (NID),-- key fk_xxx (part_nit), CONSTRAINT fk_xxx FOREIGN KEY (part_nid) REFERENCES part1 (NID))

code implementation FOREIGN key

2.3, code deletion and add a pair of more;

Delete: ALTER TABLE person1 drop FOREIGN key fk_xxx

Added: ALTER TABLE Person1 add constraint fk_xxx foreign key (Part_nid) references Part1 (NID)

3. Realize one-to-one and one-to-many queries:

　　　　Requirements: With left JOIN, inner join flexible implementation;

General condition Query SELECT Person.name,part1.nidd from person,part1 where Person.nid = Part1.nidd

An associated query for a table:

SELECT * FROM Main Table left join secondary table on Main Table association column = Secondary Table association column where ...

Associated queries with multiple tables:

SELECT * FROM Main Table

Left join secondary table on Main Table association column = Secondary Table association column

Left join secondary table on Main Table association column = Secondary Table association column

where ...

The inner join is similar to the right join function.

B. Many-to-many

1, how do you understand the many-to-many;

Many-to-many means that one item in the main table can be related to multiple items in a secondary table (polygamy);

2, Navicat for MySQL implementation of many-to-many, hint: CREATE table One, table II, table III, the use of table three to establish the relationship between table I and Table II;

Answer: Complete

3. Realize flexible search tips using Join

SELECT * FROM Main Table

Left join secondary table 1 on Main Table association column = Secondary Table 1 association column

Left join secondary table 2 on Main Table association column = Secondary Table 2 Association column

where ...

　　

　　　　

　　　　

MySQL review and one-to-many and many-to-many summaries (17.6.26)