SQL-12. index + 13. Join

Index

 

• Full table Scan: The worst efficiency of data retrieval (select) is full table scan, which is to find data in one row.
• If there is no directory, You need to flip the Chinese Dictionary page by page. If you have a directory, you only need to query the directory. To improve the search speed, you can add columns that are frequently searched.Index, equivalent to creating a directory.
• To create an index, right-click the Table Designer and choose "index/Key"> "add"> "select the columns included in the index in the column.
• Using indexes can improve query efficiency,Indexes also occupy spaceAnd the index needs to be updated synchronously when adding, updating, and deleting data, which will reduce the insert, update, and delete speeds. You can only create an index on a field that is frequently searched (where.
• (*) Even if an index is created, it is still possible to scan the entire table, such as like, function, and type conversion.

 

Index: Index queries for select fields frequently increase the speed (especially when the data volume is large), but will reduce the insert, update, and delete speeds. Even if an index is created, it is still possible to scan the entire table, such as like, function, and type conversion.

 

Join

There are two tables: the customer table (t_customers) and the order table (t_orders). The customer table fields are ID, name, and age, and the order table fields are ID, billno, and customerid, the Order table uses mermerid to associate the customer table. For test data, see the remarks.

Create two tables: t_mers MERs and t_orders.

 

 Create Table  [T_customers]  (  [ID] [int]  Not null,  [Name] [nvarchar]  ( 50 )  Collate  Chinese_prc_ci_as Null,  [Age] [int]  Null );  Insert  [T_customers]  (  [ID]  ,  [Name]  ,  [Age]  )  Values  ( 1 ,  N 'Tom'  , 10 ); Insert  [T_customers]  (  [ID]  ,  [Name]  ,  [Age]  )  Values  ( 2 ,  N 'Jerry'  , 15 );  Insert  [T_customers]  ( [ID]  ,  [Name]  ,  [Age]  )  Values  ( 3 ,  N 'john'  , 22 );  Insert  [T_customers]  (  [ID]  ,  [Name] ,  [Age]  )  Values  ( 4 ,  N 'lily'  , 18 );  Insert  [T_customers]  (  [ID]  ,  [Name]  ,  [Age]  ) Values  ( 5 ,  N 'Lucy'  , 18 );  Create Table  [T_orders]  (  [ID] [int]  Not null,  [Billno] [nvarchar]  ( 50 )  Collate  Chinese_prc_ci_as  Null, [Customerid] [int]  Null );  Insert  [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values  ( 1 ,  N '001'  , 1 );  Insert [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values  ( 2 ,  N' 002'  , 1 );  Insert  [T_orders]  (  [ID] ,  [Billno]  ,  [Customerid]  )  Values  ( 3 ,  N '003'  , 3 );  Insert  [T_orders]  (  [ID]  ,  [Billno]  , [Customerid]  )  Values  ( 4 ,  N '004'  , 2 );  Insert  [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values ( 5 ,  N'005'  , 2 );  Insert  [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values  ( 6 ,  N '006' , 5 );  Insert  [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values  ( 7 ,  N '007'  , 4 );  Insert [T_orders]  (  [ID]  ,  [Billno]  ,  [Customerid]  )  Values  ( 8 ,  N' 008'  , 5 ); 

 

Select O. billno, C. Name, C. Age from t_orders as O
JoinT_customers as COnO. customerid = C. ID

Question: The order number, customer name, and customer age of all customers older than 15 years old must be displayed.

Select  O  .  Billno  ,  C  .  Name  ,  C  .  Age  From  T_orders  As  O  Join  T_customers  As C  On  O  .  Customerid  =  C  .  ID  Where  C  .  Age  > 15

 

Orders purchased by customers older than the average age must be displayed

 

 Select  O  . Billno  ,  C  .  Name  ,  C  .  Age  From  T_orders  As  O  Join  T_customers  As  C  On  O .  Customerid  =  C  .  ID  Where  C  .  Age  > (  SelectAVG(Age)FromT_customers  )