Mysql Study Notes and

Mysql Study Notes and
Daily traffic Calculation

 

 

Create a table and insert some data. Each data entry indicates the date on which the user accesses a website (for example, 2000-01-01)

Mysql> create table t1 (
-> Year YEAR (4 ),
-> Month INT (2) unsigned zerofill,
-> Day INT (2) UNSIGNED ZEROFILL
-> );
Query OK, 0 rows affected (0.53 sec)

Mysql> insert into t1 VALUES (, 1), (, 20), (, 30), (, 2 ),
-> (2000,2, 23), (2000,2, 23 );
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0

Mysql>

 

 

Chestnut: query the number of access days per month (repeated is not counted)

The procedure is as follows:

SELECT year, month, BIT_COUNT (BIT_OR (1 <day) AS day FROM t1 GROUPBY year, month;

Mysql> SELECT year, month,
-> BIT_COUNT (BIT_OR (1 <day) AS day FROM t1
-> Group by year, month;
+ ------ + ------- + ----- +
| Year | month | day |
+ ------ + ------- + ----- +
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+ ------ + ------- + ----- +
2 rows in set (0.00 sec)

Mysql>

The portal shares a blog explaining BIT_COUNT () and BIT_OR.

Another transmitter reads the same books as me.

 

 

Check the data in the table:

Mysql> SELECT * FROM t1;
+ ------ + ------- + ------ +
| Year | month | day |
+ ------ + ------- + ------ +
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+ ------ + ------- + ------ +
6 rows in set (0.00 sec)

Mysql>

 

 

If you think you are not very familiar with the transfer, let me talk about it.

After reading this operation:

SELECT year, month, BIT_COUNT (BIT_OR (1 <day) AS day FROM t1 GROUPBY year, month;

I believe that most people will have two questions in their minds. What are BIT_OR () and BIT_COUNT? Why is there such an operation?

The BIT_COUNT () function is described as follows:

BIT_COUNT (N) returns the number of 1 in N (Binary.

When I write a chestnut, you will understand:

SELECT BIT_COUNT (100); what is the answer?

Think about it. The binary value of 100 is: 1100100.

Mysql & gt; SELECT bin (100 );
+ ---------- +
| BIND (100) |
+ ---------- +
| 1, 1100100 |
+ ---------- +
1 row in set (0.00 sec)

Mysql>

So the answer should be 3?

Mysql> SELECT BIT_COUNT (100 );
+ ---------------- +
| BIT_COUNT (100) |
+ ---------------- +
| 3 |
+ ---------------- +
1 row in set (0.00 sec)

Mysql>

Shao Xia: You have excellent eyesight. The answer is 3.

Now we know what the BIT_COUNT () function is.

 

 

Next we will discuss the second question: what is BIT_OR?

Although the two of them look very similar, they have different functions of different nature.

BIT_OR (expr) is to calculate the accuracy of all fields in the expr column OR to 64-bit, many blogs have not listed how to use example. They may think it is simple, or they may not use it (they certainly won't use it,)

The previous shop table is used as an operation:

SELECT * FROM shop;

Mysql> SELECT * FROM shop;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 1, 0003 | C | 1.69 |
| 1, 0003 | D | 1.25 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
7 rows in set (0.06 sec)

We will try BIT_OR () for the article column. What is the result calculated before the experiment?

0001

0001

0010

0011

0011

0011

OR 0100

--------

0111

Should the result be like this?

Mysql> SELECT BIT_OR (article) FROM shop;
+ ----------------- +
| BIT_OR (article) |
+ ----------------- +
| 7 |
+ ----------------- +
1 row in set (0.00 sec)

Mysql>

7 is the binary 0111, right? And no matter how many 1 consecutive OR results are 1.

1 or 1 or 0 or 1 = 1

The logic in this problem is: no matter how many times I visited today, I accessed this website (logically expressed as 1)

 

 

Let's look back at this operation again:

SELECT year, month, BIT_COUNT (BIT_OR (1 <day) AS day FROM t1 GROUPBY year, month;

Now it seems that there are more explanations. 1 shifts the number of days to the left, and the corresponding number of digits indicates the day of the month (64 bits, enough ). For example, today

1 <2 means 100 indicates that I visited this website today. After I complete the day field OR, I get a 64-bit binary (meaning of this number, I don't want to explain it) and use BIT_COUNT () the number of functions is the result we want (I am exhausted). I don't know how to explain it for so long ).

 

 

Use AUTO_INCREMENT

 

 

The listener name is automatically added, and you can directly perform the following operations:

Mysql> create table animals (
-> Id mediumint not null AUTO_INCREMENT,
-> Name CHAR (30) not null,
-> Primary key (id)
-> );
Query OK, 0 rows affected (0.85 sec)

Mysql> insert into animals (name) VALUES
-> ('Dog'), ('cat'), ('penguin '),
-> ('Lax '), ('whale'), ('ostrich ');
Query OK, 6 rows affected (0.13 sec)
Records: 6 Duplicates: 0 Warnings: 0

Mysql> SELECT * FROM animals;
+ ---- + --------- +
| Id | name |
+ ---- + --------- +
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+ ---- + --------- +
6 rows in set (0.00 sec)

Mysql>

An animals table is created with the id and name fields. Id is set to the auto-increment primary key, and only the name is used to insert data. Then the id is automatically filled.

 

 

Another command can be used to modify the starting value of AUTO_INCREMENT.

The procedure is as follows:

ALTER TABLETbl_nameAUTO_INCREMENT = 2333;

 

 

To be continued...