MySQL Getting Started note 2

Fuzzy query like

Replace any character with%

Where goods_name like ' Nokia% ';

Clear a few characters on the underline
Underline wildcard single character,% wildcard any

Exercises

Turn a bunch of 10, 20, and 30 numbers into 10,20,30.
Which is to remove single digits

Query out Select out

The MySQL function is used.

Floor returns the largest integer less than x

Let a bunch of numbers in addition to 10, then floor is a number, also took the 10 digits, then multiply 10

It becomes 10,20,30.

That means you can update data like this.

num of set = the value that participates in the calculation

2

Change the string Nokia to HTC

SUBSTRING

Also go to the MySQL document to check the corresponding function

Lesson 12th Strange Null

Create a table

Mysql> CREATE TABLE tmp (
ID int,
-Name varchar (20)
) CharSet UTF8 engine MyISAM;

Write it in.

mysql> INSERT INTO TMP
Values
(1, ' Lili '),
(2,null);

A null is defined
After
Go to query, where Name= NULL
The result is not,,, the query is not equal to NULL, and there is no

Mysql> SELECT * FROM TMP;
+--+--+
| ID | name |
+--+--+
| 1 | Lili |
| 2 | NULL |
+--+--+
2 rows in Set (0.00 sec)

Mysql> SELECT * from TMP where name=null;
Empty Set (0.00 sec)

Mysql> SELECT * from TMP where name!=null;
Empty Set (0.00 sec)

Null is no meaning, can not be compared, that is, the back of the Where comparison statement, no

There are special null-checking

Where name is NULL

Class 13th, group grouping and statistical functions

Max () max
Count () to find the number of rows
AVG ()
Min ()
SUM () Find sum

These are statistical functions

which
AVG is the average

Mysql> Select AVG (shop_price) from goods;
+ ————— –+
| AVG (Shop_price) |
+ ————— –+
| 1232.526774 |
+ ————— –+
1 row in Set (0.00 sec)

The number of rows to be taken is how many

Mysql> Select COUNT (shop_price) from goods
+ ——————-+
| Count (Shop_price) |
+ ——————-+
| 31 |
+ ——————-+
1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from goods;
+ ———-+
| COUNT (*) |
+ ———-+
| 31 |
+ ———-+
1 row in Set (0.00 sec)

Mysql> Select COUNT (1) from goods;
+ ———-+
| COUNT (1) |
+ ———-+
| 31 |
+ ———-+
1 row in Set (0.00 sec)

The above is a simple statistic

Here is the grouping statistics

Since it is a group of statistics, then there is a statistic, for example, according to the small title, then you have to give this small title first to shoot a preface, and then in the calculation, so with group or a bit of resource-intensive,
It is also possible to use the index

Command: What group to press from user group by

Remember the GROUP by

First press as cat_id

Mysql> Select cat_id, avg (shop_price) from goods group by CAT_ID;
+--–+ ————— –+
| cat_id | AVG (Shop_price) |
+--–+ ————— –+
| 2 | 823.330000 |
| 3 | 1746.066667 |
| 4 | 2297.000000 |
| 5 | 3700.000000 |
| 8 | 75.333333 |
| 11 | 31.000000 |
| 13 | 33.500000 |
| 14 | 54.000000 |
| 15 | 70.000000 |
+--–+ ————— –+
9 Rows in Set (0.00 sec)

Count of inventory remaining in each column
Mysql> Select cat_id, COUNT (*) from goods group by CAT_ID;
+--–+ ———-+
| cat_id | COUNT (*) |
+--–+ ———-+
| 2 | 1 |
| 3 | 15 |
| 4 | 3 |
| 5 | 1 |
| 8 | 3 |
| 11 | 2 |
| 13 | 2 |
| 14 | 2 |
| 15 | 2 |
+--–+ ———-+
9 Rows in Set (0.00 sec)

That's the group!!!!!!!!!!!!!.

14 Lesson Having screening

That is, if the calculation of the column out, the following conditions are the same, it is necessary to calculate two times, so the language can be assigned to the variable, the calculation of the assignment to another variable ~ ~ ~

It is

Selecct Goods_name, (market_price-shop_price) as Sheng from goods have sheng >200;

The normal query is to look at the following conditions first, the data on the disk on the condition to be taken out of the temporary storage.

That is to say, there are those operations in the proposed data, but there is no real data on the disk.

And then I would like to make the data filter on the basis of this proposed temporary table.

Where is for disk, having a temporary table for fetching

That means first
Selecct Goods_name, (market_price-shop_price) as Sheng from goods where 1

Take all of this and take it out and use it for the temporary data sheet you've taken out.

Having Sheng >200;

The result is the same as the one above directly with having.

Cout is the number of rows, score<60, no matter what, there will be a result, 0 or 1, then count will go regardless of 0 or 1 to go to a number of lines. should use sum!!!!. Ask for that and, then group by name,
Use where to take out, in with having the sum that is greater than 2

Mysql> Select Name,subject,sum (score<60) as GK, AVG (score) from result group by
name;
+--+ ——— +--+ ———— +
| name | Subject | GK | AVG (Score) |
+--+ ——— +--+ ———— +
| Zhang San | Math | 2 | 60.0000 |
| John Doe | language | 2 | 50.0000 |
| Harry | Politics | 1 | 30.0000 |
+--+ ——— +--+ ———— +
3 Rows in Set (0.00 sec)

Mysql> Select Name,subject,sum (score<60) as GK, AVG (score) from result group by
name;
+--+ ——— +--+ ———— +
| name | Subject | GK | AVG (Score) |
+--+ ——— +--+ ———— +
| Zhang San | Math | 2 | 60.0000 |
| John Doe | language | 2 | 50.0000 |
| Harry | Politics | 1 | 30.0000 |
+--+ ——— +--+ ———— +
3 Rows in Set (0.00 sec)

Mysql> Select Name,subject,sum (score<60) as GK, AVG (score) from result group by
Name
Have gk>=2;
+--+ ——— +--+ ———— +
| name | Subject | GK | AVG (Score) |
+--+ ——— +--+ ———— +
| Zhang San | Math | 2 | 60.0000 |
| John Doe | language | 2 | 50.0000 |
+--+ ——— +--+ ———— +
2 rows in Set (0.00 sec)

Mysql>

15. ORDER BY order

is the order by followed by the order of the conditions, what sort!
Very simple, uh.
Such as
Select goods_id Goods_name from goods order by Shop_price;

is sorted by commodity price, if there is no sort on disk, then in memory sort

But it has a positive sequence, flashbacks, etc.

The default is ascending ASC

Directly behind and Desc becomes descending.

Select Goods_id,goods_name,shop_price from goods order by Shop_price;

But can have, is to sort by cat_id first, and then found that the price of goods is disorderly, then let the price is
Sort of.

The first field is not finished, and the field that is followed by a comma plus a comparison is added directly.

And can be the first comparison is ascending, the second one is descending also can.

Mysql> Select Goods_id,goods_name,shop_price from goods ORDER BY Shop_price DESC
, Shop_price desc;
+ ———-+ —————————— + ———— +
| goods_id | Goods_name | Shop_price |
+ ———-+ —————————— + ———— +
| 22 | Multi-reach Touch HD | 5999.00 |
| 23 | Nokia N96 | 3700.00 |
| 32 | Nokia N85 | 3010.00 |
| 18 | Amoi T5 | 2878.00 |

You can also continue to add

That is, sort by column.

17 Class limit Check out entry

For example, take out the price before three high,

Selectively take out a few, first sort, then you can skip a few can also directly take

Yes, two parameters, 1 offsets is skipping a few

2 Take a few lines.

Mysql> Select Goods_id,goods_name,shop_price from Goods order by Shop_price limit 0, 3;
+ ———-+ ——————— –+ ———— +
| goods_id | Goods_name | Shop_price |
+ ———-+ ——————— –+ ———— +
| 30 | Mobile $20 Prepaid Card | 18.00 |
| 26 | PHS/fixed $20 Prepaid Card | 19.00 |
| 5 | Sony Ericsson original m2 card reader | 20.00 |
+ ———-+ ——————— –+ ———— +
3 Rows in Set (0.00 sec)

Mysql> Select Goods_id,goods_name,shop_price from Goods ORDER by shop_price ASC limit 0, 3;
+ ———-+ ——————— –+ ———— +
| goods_id | Goods_name | Shop_price |
+ ———-+ ——————— –+ ———— +
| 30 | Mobile $20 Prepaid Card | 18.00 |
| 26 | PHS/fixed $20 Prepaid Card | 19.00 |
| 5 | Sony Ericsson original m2 card reader | 20.00 |
+ ———-+ ——————— –+ ———— +
3 Rows in Set (0.00 sec)

Mysql> Select Goods_id,goods_name,shop_price from goods order BY Shop_price desc limit 0, 3;
+ ———-+ —————-+ ———— +
| goods_id | Goods_name | Shop_price |
+ ———-+ —————-+ ———— +
| 22 | Multi-reach Touch HD | 5999.00 |
| 23 | Nokia N96 | 3700.00 |
| 32 | Nokia N85 | 3010.00 |
+ ———-+ —————-+ ———— +
3 Rows in Set (0.00 sec)

Remember two parameters, one offset, one out of the number of entries

Remove the latest

is to press goods_id flashbacks first, and then 0,1 is to take out the latest bug directly E

In Oracle, there is no
Play a role in paging

18. Query TRAPS for clauses

Find the largest and newest products under each column. GOODS_ID as the largest up-to-date

Comprehensive application

The newest one under each column is the one with the largest ID.

These 5 seed sentences are in order and we are now learning the order.

Sub-query on the ~ ~ ~

19 Lesson where sub-query

Inner query result as condition of outside query

Is the above question, without sorting and limist

If you only take the most direct, not according to cat_id points,

Can select goods_id, goods_name from goods where = 32;

But I don't know the biggest is 32, so
OK
Check first select Max (goods_id) from goods;
Return the largest, and then on on it can be.

So let's give the maximum value of the return to the 32 position.

Sub-query:

Select goods_id, goods_name from goods where goods_id =

(select Max (goods_d) from goods);

Mysql> Select goods_id goods_name from goods where goods_id = (select Max (goods_id) from goods);
+ ———— +
| Goods_name |
+ ———— +
| 32 |
+ ———— +
1 row in Set (0.03 sec)

Solve the above problem, is to find out each title under the largest

Select Max (goods_id) from goods group by CAT_ID;
Returns the largest goods_id under each heading;
Then we'll just have to look at their information on this basis, but they're not regular.
Then use the in statement to check

It is

Select goods_id, goods_name where goods_id in
(select Max (goods_id) from goods Group by cat_id);

Instead of group by, the entire largest goods_id is grouped by group by.

The largest goods_id under each heading ~ ~ ~

Mysql> Select Goods_id,goods_name from goods where goods_id in
(select Max (goods_id) from goods Group by cat_id)
;
+ ———-+ —————————— +
| goods_id | Goods_name |
+ ———-+ —————————— +
| 6 | Sheng Chuang Kingmax Memory Card |
| 7 | Nokia N85 Original stereo Headset hs-82 |
| 16 | Henderson Albert G101 |
| 18 | Amoi T5 |
| 23 | Nokia N96 |
| 26 | PHS/fixed $20 Prepaid Card |
| 28 | China Unicom $50 Prepaid Card |
| 30 | Mobile $20 Prepaid Card |
| 32 | Nokia N85 |
+ ———-+ —————————— +
9 Rows in Set (0.00 sec)

If you don't use the subquery to return the ordinal and the name will be messy

20 Lesson from type subquery

After a normal query, a list is returned, which is a table that can then be
Using from to query the table

Is the result of the first query is a list as TMP as a temporary table, and then

From this temporary table, the following can also be normal conditions, that is, the simple query on the temporary table

As is named

Select goods_id,goods_name from goods where goods_id<25;

Select goods_id, Goods_name from (Select goods_id,goods_name to goods where goods_id<25) as TMP order by goods_id;

21 Lesson exists sub-query

Find out all the items that have a product,

Mysql> SELECT * from category where exists (select * from goods where goods.cat_i
d = category.cat_id);
+--–+ ——————-+ ——— –+
| cat_id | Cat_name | parent_id |
+--–+ ——————-+ ——— –+
| 2 | CDMA Mobile Phones | 1 |
| 3 | GSM Mobile Phone | 1 |
| 4 | 3G Mobile Phone | 1 |
| 5 | Dual-mode Mobile phones | 1 |
| 8 | Headphones | 6 |
| 11 | Card readers and Memory cards | 6 |
| 13 | PHS/fixed-line Prepaid Card | 12 |
| 14 | Mobile Phone Recharge Card | 12 |
| 15 | Unicom Mobile Prepaid Card | 12 |
+--–+ ——————-+ ——— –+
9 Rows in Set (0.00 sec)

The queries between the two tables, corresponding to the cat_id numbers that show them all present, are displayed.

Esists the meaning of existence.

22 Lessons, Novice 1+n mode query

Search for goods and column names with prices greater than $2000.

A goods table words, can't find out the column name, but there are column ID,

First check out the product price is greater than 2000, including the column ID, and then find out the n data, by the N data
You can go to the other table again to query. Code implementation.

This is very troublesome, is a novice, and then you can elicit connection query

23 in-Class connection query

Delete the table,,, the name of the drop table, it can be deleted

Truncate and delete only delete data without deleting the structure of the table (definition)

Speed, in general: drop> truncate > Delete

Query when two table joins are inner join after on is conditional join rule

To delete part of a data row with delete, note the WHERE clause. The rollback segment should be large enough.
Want to delete the table, of course with drop
To keep the table and delete all the data, if it is not related to the transaction, use truncate to

Http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

SQL truncate, delete and drop differences
Same point:
1.truncate and delete without a WHERE clause, and drop deletes the data in the table.

2.drop, truncate are DDL statements (data definition language) that are automatically committed after execution.

Different points:
1. Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index, and the stored procedure/function that depends on the table will remain, but become invalid state.

1. The DELETE statement is a database manipulation language (DML), which is put into the rollback segement, the transaction is committed, and if there is a corresponding trigger, the execution is triggered.
   Truncate, drop is the database definition language (DDL), the operation takes effect immediately, the original data is not placed in the rollback segment, cannot be rolled back, the operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
The drop statement frees all the space occupied by the table.
The TRUNCATE statement defaults to the space release to minextents extent unless using reuse storage;truncate resets the high watermark (back to the beginning).

4. Speed, in general: drop> truncate > Delete

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
For use, to delete some data rows with delete, note the WHERE clause. The rollback segment should be large enough.
Want to delete the table, of course with drop
To keep the table and delete all the data, if it is not related to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

6.delete is a DML statement and is not automatically committed. Drop/truncate are DDL statements that are automatically committed after they are executed.

7. TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

8. TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

9. For a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.

10. TRUNCATE table cannot be used for tables that participate in an indexed view.
.........................................................................................................................................................................................................................................................................................................................

| HID | bname |
+--+--–+
| A | Cock Wire |
| B | Yang over |
| C | Edison Chen |
+--+--–+
3 Rows in Set (0.00 sec)

Mysql> select * from girl;
+--+--–+
| HID | Gname |
+--+--–+
| B | Dragon Girl |
| C | Cecilia Cheung |
| D | Dead House Girl |
+--+--–+
3 Rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql> Select Boy.hid, Boy.bname, Girl.hid, Girl.gname
-From
Boy inner JOIN Girl
-On boy.hid = Girl.hid
;
+--+--–+--+--–+
| HID | bname | HID | Gname |
+--+--–+--+--–+
| B | Yang over | B | Dragon Girl |
| C | Edison Chen | C | Cecilia Cheung |
+--+--–+--+--–+
2 rows in Set (0.04 sec)

This is the Select table. What is the other table?

From table 1 inner JOIN table 2
On connection conditions

About 24 Lessons connected query

Before that, the boy on the left didn't take it, because there was no correspondence,
So now let him correspond to null

As left-hand, it is dominated by the left.

Mysql> Select Boy.hid, Boy.bname, Girl.hid, Girl.gname
-From
Boy left Join Girl
-On boy.hid = Girl.hid;
+--+--–+--+--–+
| HID | bname | HID | Gname |
+--+--–+--+--–+
| A | Cock Wire | NULL | NULL |
| B | Yang over | B | Dragon Girl |
| C | Edison Chen | C | Cecilia Cheung |
+--+--–+--+--–+
3 Rows in Set (0.00 sec)

is to find out all the tables on the left, and then take the data
Write null not found

Right connection

is to change the left to right.

Mysql> Select Boy.hid, Boy.bname, Girl.hid, Girl.gname
-From
Boy right Join Girl
-On boy.hid = Girl.hid;
+--+--–+--+--–+
| HID | bname | HID | Gname |
+--+--–+--+--–+
| B | Yang over | B | Dragon Girl |
| C | Edison Chen | C | Cecilia Cheung |
| NULL | NULL | D | Dead House Girl |
+--+--–+--+--–+
3 Rows in Set (0.00 sec)

Inner is the intersection of the two of them.
Left is the right side of the intersection.

MySQL does not support outer joins, which are all the aggregates.

For example

Mysql> Select Goods_id,goods_name,shop_price,cat_name
-From
-Goods left JOIN category
-On goods.cat_id = category.cat_id;

Write the table. is because different tables have the same fields, in order to differentiate

Mysql> Select Goods_id,cat_name,shop_price
-From
-Goods left JOIN category
-On goods.cat_id = category.cat_id
--Where goods.cat_id = 4;
+ ———-+ ———-+ ———— +
| goods_id | Cat_name | Shop_price |
+ ———-+ ———-+ ———— +
| 1 | 3G Mobile Phone | 1388.00 |
| 14 | 3G Mobile Phone | 2625.00 |
| 18 | 3G Mobile Phone | 2878.00 |
+ ———-+ ———-+ ———— +
3 Rows in Set (0.00 sec)

Select M.*,t1.tname as htame,t2.tame as Gteam
From
M INNER JOIN t as T1 on m.hid = T1.tid inner join t as t2 on m.gid = T2.tid

There are input \c after the previous statement is useless, sometimes input error, enter, input \c can end
The writing of this statement

Mysql> Select M.*,t1.tname as Htname, t2.tname as Gtname
-From
-M INNER join t as T1 on m.hid = T1.tid inner join t as t2 on m.gid = t
Id
+--+--+--+--+ ———— + ———-+ ———-+
| Mid | HID | GID | MREs | Matime | Htname | Gtname |
+--+--+--+--+ ———— + ———-+ ———-+
| 1 | 1 | 2 | 2:0 | 2006-05-21 | Guoan | Shenhua |
| 2 | 2 | 3 | 1:2 | 2006-06-21 | Shenhua | Boolean Wing |
| 3 | 3 | 1 | 2:5 | 2006-06-25 | Boolean Wing | Guoan |
| 4 | 2 | 1 | 3:2 | 2006-07-21 | Shenhua | Guoan |
+--+--+--+--+ ———— + ———-+ ———-+
4 rows in Set (0.00 sec)

This T-Watch has been connected two times, even two times.

MySQL Getting Started note 2