Study database notes 3. Database notes

Study database notes 3. Database notes

I

Select query model?

Very important! Determines whether you can write powerful query statements!

Model [columns are variables (variables can be calculated, so columns can be computed)

After where is an expression, the value is true or false. When the value is true, the value of this row of variables (columns) is taken out.] (where id = 3 and id is 3 for comparison, the result is true, take the row with id 3)

** Therefore, the SQL statement is executed in two steps. 1. judge the value of the expression after where. 2. If it is equal to true, the value of the column (variable) is taken out; otherwise, the column (variable) is not taken out.

Select * from user where 1; this statement is well understood according to the model, and the expression is true under any circumstances, that is, the rows of the entire table are true, retrieve * all columns. The result is the data of the entire table.

Select * from user where 0; this statement is false in any case, so there is no row that meets the condition, so the content of a row is not found

Now, 'variable (column) can be computed '?

First, let's look at the age of next year.

Select uid, name,Age + 1From user; directly add 1 to age

If your boss asks you to query how much the store's products are cheaper than the market price

Select goods_id, goods_name, shop_price, market_price, market_price-shop_price from goods; directly take the market price minus the price of our shop can be

The term for querying only a few columns is"Projection operation", Which means to project the values of these two columns. You need to take the values of these two columns as a projection.

Think of a column as a variable and take two columns for calculation. This is called"Guangyi projection".

II

SQL statement query exercise

1. query the products with the product ID 32.

Select goods_id, goods_name from goods where goods_id = 32;

2. query all products that do not belong to column 3rd (column name cat_id)

Select cat_id, goods_id, goods_name from goods wher cat_id <> 3; in the SQL statement <> and! = Is not equal

3. query the products with prices greater than 3000 in this store.

Select shop_price, goods_id, goods_name from goods where shop_price> 3000;

4. query the products whose prices are less than or equal to 100 yuan.

Select shop_price, goods_id, goods_name from goods where shop_price <= 100;

5. query all products with column 4 or column 11. Do not use or

Do not use or. |, or in

| Select cat_id, goods_id, goods_name from goods where cat_id = 4 | cat_id = 11;

It means to test whether you will use in, so it is better to write it as follows:

Use in select cat_id, goods_id, goods_name from goods whereCat_id in (4, 11);

6. Check the products with prices greater than 100 and less than 500. Do not use or

Select shop_price, goods_id, goods_name from goods where shop_price between 100, and 500;

This method can also be used.

Select shop_price, goods_id, goods_name from goods where (shop_price> = 100) and (shop_price <= 500 );

7. query the products with column id not equal to 3 and column id not equal to 11, and use and not in to implement

Select cat_id, goods_id, goods_name from goods where (cat_id <> 3) and (cat_id <> 11 );

Select cat_id, goods_id, goods_name from goods whereCat_id not in (3,11);

8. query the products whose prices are greater than 100 and less than 500 or greater than 4000 and less than 5000

Select shop_price, goods_id, goods_name from goods where (shop_price between 100 and 500) or (shop_price between 4000 and 5000 );

9. Take out the products with prices less than 3rd or greater than 1000 and clicks greater than 5 under section 3000.

In this case, the conditions are listed one by one and connected with or.

Select cat_id, shop_price, click_count, goods_name from goods where (cat_id = 3) and (shop_price <1000 or shop_price> 3000) and (click_count> 5 );

10. take out the product under the first column (the first column is usually the parent node, generally it does not have a direct product below, but put the sub-column, so without the student query, first, let's look at the topic table, which is column 1 and take the items under these columns out)

Select cat_id, goods_id, goods_name from goods where cat_id in (2, 3, 4, 5 );

11. Retrieve products starting with Nokia

Select goods_id, goods_name from goods where goods_name like 'nokia % '; the uncertain part is replaced by %, which represents any character.

12. Retrieve the product named 'nokia nxx'

Select goods_id, goods_name from goods where goods_name like 'nokia N _ '; _ represents a character

13. Retrieve products whose names do not start with Nokia

Select goods_id, goods_name from goods where goods_name not like 'nokia % ';

14. Take out the 3rd topics, the price is greater than 1000 and less than 3000, the clicks are greater than 5, and the product name starts with 'nokia *

Select cat_id, shop_price, goods_name from goods where (cat_id = 3) and (shop_price between 1000 and 3000) and (click_count> 5) and (goods_name like 'nokia % ');

Like Fuzzy match

% Match any character

_ Match a single character

3.

Two interview questions

1. Change the number of num values between [20, 29] to 20.

Change the number of num values between [30, 39] to 30.

A: update mian set num = floor (num/10) * 10 where num between 20 and 39;

First, the where scope is 20 ~ 39, this is to calculate the number of the 20 and 30 ranges, and then calculate the 20 ~ Num = floor (num/10) * 10 for the number of 39, divided by 10 first, and then rounded down. Now more than 20 are all 3 for the number, multiply by 10 to get the expected result.

2. Change the product name 'nokia XXX' in the goods table to 'htcxxx'

This question is complex and functions are used.

You can complete this step by step.

A) select goods_name from goods where goods_name like 'nokia ___ '; first, check the data of 'nokia XXX'.

+ ------------ +
| Nokia e66 |
| Nokia n96 |
| Nokia n85 |
+ ------------ +

B) select substring (goods_name, 4) from goods where goods_name like 'nokia ___ '; Use the substring function to extract the first three words from Nokia.

+ ------------------------- +

| Substring (goods_name, 4) |
+ ------------------------- +
| E66 |
| N96 |
| N85 |
+ ------------------------- +

C) select concat ('htc ', substring (goods_name, 4) from goods where goods_name like 'nokia ___'; concatenate three characters in front of HTC using the concat function, the replacement is successful.