HeadFirstSQL: Study Notes 2/2

Chapter 7 multi-Table Design

1. A foreign key is a column in a table. It refers to a unique key in another table, generally a primary key. The foreign key is used to confirm that the rows in one table correspond to the rows in the other table.

2. The primary key referenced by the foreign key is called the parent key, and the table where the parent key is located is called the parent table.

3. The foreign key is not unique and can be null. If it is null, no corresponding primary key exists in the parent table.

4. Integrity of reference: the value of the inserted foreign key column must be in the source column of the parent table. The foreign key can ensure the integrity of the reference, which is an important function of the foreign key.

5. constraints define certain rules in the table to prevent the table structure from being damaged.

6. Define the foreign key:

Create table nickname (

Id int not null auto_increment primary key,

Nname varchar (30) not null,

Player_id int );

Mysql> alter table nickname add constraint players_id_fk foreign key (player_id)

References players (id );

Note: The type of the foreign key must be the same as that of the primary key; otherwise, the error errno150 may occur.

7. Relationship between tables

One-to-one: rarely used

One-to-multiple: connect through a foreign key

Many-to-many: complex. You can connect to the junction table to store the primary keys of the two tables.

Key combination: multiple keys constitute the primary key

8. paradigm:

1NF ). Rule 1: Data columns only contain Atomic values (one column stores multiple information fields at the same time ). Rule 2: No repeated data groups (multiple columns store fields of the same type, such as columns color1 and color2 ).

2NF ). Rule 1: 1NF is first met. Rule 2: There is no function dependency (column 1 and column 2 are composite primary keys. For example, column 3 is generated based on column 1 data but is irrelevant to column 2, which is called column 3 partial dependency ).

U function dependency: The values of one column change according to the values of the other column.

U function dependency: non-primary key columns depend on a portion of the primary key.

U transfer function dependency: any non-key column is associated with another non-key column.

If 1NF is met, and the primary key column has only one column, 2NF must be met.

3NF ). Rule 1: 2NF is first met. Rule 2: no function dependency is passed.

Understanding of the three paradigms (the following content is referenced in:

Http://blog.csdn.net/famousdt/article/details/6921622 ):

◆ 1NF: It emphasizes the atomicity of the column, that is, the column cannot be further divided into several other columns.

Consider this table: [contact] (name, gender, phone number)

In actual scenarios, if a contact has a home phone number and a company phone number, the table structure is not 1NF. To comply with 1NF, we only need to split the column (phone number), that is, [contact person] (name, gender, home phone number, company phone number ). 1NF is well identified, but 2NF and 3NF are easy to confuse.

◆ 2NF: 1NF is the first and contains two parts. One is that the table must have a primary key, and the other is that columns not included in the primary key must be completely dependent on the primary key, instead of relying only on a portion of the primary key.

Consider an order list: [OrderDetail] (OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName ).

Because we know that multiple products can be ordered in an order, an OrderID alone is not enough to become a primary key. The primary key should be (OrderID, ProductID ). Obviously, Discount (Discount) and Quantity (Quantity) depend entirely on the primary key (OderID, ProductID), while UnitPrice and ProductName depend only on ProductID. Therefore, the OrderDetail table does not conform to 2NF. Non-conforming 2NF design is prone to redundant data.

You can split the [OrderDetail] table into [OrderDetail] (OrderID, ProductID, Discount, Quantity) and [Product] (ProductID, UnitPrice, ProductName) to eliminate UnitPrice in the original order table, productName is repeated multiple times.

◆ 3NF: 2NF is the first, and non-primary key columns must depend on primary keys directly. That is, it cannot exist: non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key.

Consider that the primary key of an Order table [Order] (OrderID, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity) is (OrderID ).

Among them, non-primary key columns such as OrderDate, CustomerID, CustomerName, CustomerAddr, and CustomerCity depend entirely on the primary key (OrderID), so they comply with 2NF. However, the problem is that CustomerName, CustomerAddr, and CustomerCity depend directly on CustomerID (non-primary key column) instead of directly relying on the primary key. It depends on the primary key through transmission, so it does not conform to 3NF.

You can split [Order] into [Order] (OrderID, OrderDate, CustomerID) and [Customer] (CustomerID, CustomerName, CustomerAddr, CustomerCity) to 3NF.

The concepts of 2NF and 3NF are confusing. The key to distinguishing them is: 2NF: whether non-primary key columns fully depend on the primary key, it depends on a portion of the primary key. 3NF: whether a non-primary key column depends directly on the primary key or a non-primary key column.

Chapter 8 join and operations on multiple tables

1. Use AS to insert query results into a new table AS data. Create table new (id int not null auto_increment primary key, partition sion varchar) as select partition Sion from mytable group by partition sion order by partition Sion;

2. Use AS to add an alias. Select transformation sion AS my_profes from my_contacts; AS can be omitted.

3. Cross join:

Select t. toy, B. boy from toys as t cross join boys as B; cross join returns the result of multiplying each row of the two tables (Cartesian multiplication.

4. inner join combines two tables using the comparison operators in the conditional formula;

Select boys. boy, toys. toy from boys inner join toys on boys. toy_id = toys. toy_id; equal join

Select boys. boy, toys. toy from boys inner join toys on boys. toy_id <> toys. toy_id; unequal join

Select boys. boy, toys. toy from boys nature join toys; natural join (two tables contain equal internal join with the same column name)

Chapter 9 subquery

1. subquery: A Query surrounded by another query can also be called an inner-layer query.

Select interest from interest as inte where inte. playerID in (select play

ErID from interest );

2. subqueries are usually used with as and connections to improve query efficiency.

3. Comparison between In/notin and exists/not exists:

From: http://blog.csdn.net/ldl22847/article/details/7800572

In is a hash connection between the external table and the internal table, while exists is a loop on the External table. Each loop then queries the internal table. The argument that exists is more efficient than in is always inaccurate.

If the two tables to be queried are of the same size, there is little difference between in and exists.

If one of the two tables is small and the other is a large table, exists is used for the large subquery table and in is used for the small subquery table:

Example: Table A (small table) and Table B (large table)

A:

Select * from A where cc in (select cc from B) is inefficient and uses the index of the cc column in table;

Select * from A where exists (select cc from B where cc = A. cc) is highly efficient and uses the index of the cc column in table B.

Opposite

B:

Select * from B where cc in (select cc from A) is highly efficient and uses the index of the cc column in table B;

Select * from B where exists (select cc from A where cc = B. cc) is inefficient and uses the index of the cc column in table.

Not in and not exists if the query statement uses not in, the internal and external tables are scanned for the whole table, and no index is used. However, the not extsts subquery can still use the table index. Therefore, whether the table is large, not exists is faster than not in.

Difference between in and =

Select name from student where name in ('zhang ', 'wang', 'lil', 'zhao ');

And

Select name from student where name = 'zhang' or name = 'lil' or name = 'wang' or name = 'zhao'

The results are the same.

Chapter 10 outer join, inner join and join

1. left Outer Join: match each row in the left table and the rows that meet the requirements in the right table.

Select g. girl, B. boy from girls as g left out join toys as t on g. toy_id = t. toy_id;

2. The difference between the outer join and the inner join is that the outer join will certainly return the data row of the result set (the corresponding NULL is not found), and the number of result rows is equal to the number of rows in the right table.

3. right outer join: right out join. The number of result rows equals to the left table function.

4. self-referenced foreign key: self-referencing foreign key, which references the primary key of the same table for other purposes.

5. Self-join: Applicable to tables with sub-referenced Foreign keys. A single table is regarded as two tables with identical information for query. Use inner join to complete the query. The boss_id self-referenced foreign key id of table cc.

Select c1.name, c2.name as boss from cc c1 inner join cc c2 on c1.boss _ id = c2.id;

6. union: union, combined query result set;

Select distinct Sion from A union select distinct Sion from B; the same given sion only appears once

Select distinct Sion from A union all select distinct Sion from B; the same sequence sion appears multiple times

7. Use union to create a new table: Any select statement can create a new table.

Create table test as select distinct Sion from A union select sex from B;

8. intersect: intersection

9. distinct T: difference set

Chapter 4 Constraints, views, and transactions

1. constraints: such as check (limiting the values allowed to insert A column, coin char (1) check in ('A', 'B', 'C'), not null, primary key, foreign key, and unique.

Add constraints: alter table mytable add constraint check gender in ('M', 'F ');

2. View: virtual data table

Create view: create view webdesign AS select name, sex from table1 nature join table2 where table1.id = table2.id;

View view: select * from webdesign;

Delete view: drip view webdesign;

3. transaction: the SQL statement used to complete a group of tasks. All steps must be completed. Otherwise, no task is completed.

Transaction Process: start transaction-> Execute SQL statement-> commit/rollback

Show data table creation code: show create table players;

The storage engine that supports transactions must be used: InnoDB and BDB.

Alter table yourtable TYPE = InnoDB;

Chapter 4 Security

1. set the User password: set password for 'root' @ 'localhost' = password ('aaa ');

2. Add a new user: create user conan identified by 'conancup ';

3. Authorization: grant select on table1 to conan

4. revoke permissions: revoke select on table1 from conan

5. create a role: create role data_entry;

6. Authorization: grant select, update, insert on table1 to data_entry;

7. Role: grant data_entry to conan;

8. delete a role: drop role data_entry;