MySQL Learning note 009

calculate traffic by day

Build a table, insert some data each data represents the date a user visited a website (e.g. 2000-01-01)

mysql> CREATE TABLE T1 (
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 (2000,1,1), (2000,1,20), (2000,1,30), (2000,2,2),
(2000,2,23), (2000,2,23);
Query OK, 6 rows affected (0.08 sec)
Records:6 duplicates:0 warnings:0

Mysql>

Chestnuts: Query the number of visits per month (not counted)

The operation 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 ().

There is also a transmission, read the books are the same as me.

Look at 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 see in the transmission they also feel not very understanding of the students, then listen to my vernacular it.

See 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 doubts in mind, Bit_or () and Bit_count () What is the dry? Why do you still have this operation?

First explain the Bit_count () function:

Bit_count (n) returns the number of 1 in n (binary).

I write a chestnut and you get it:

SELECT Bit_count (100); What's the answer to that?

Think about it. 100 binary is: 1100100

mysql> SELECT bin (100);
+----------+
| Bin (100) |
+----------+
| 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>

You are a good eye, the answer is 3.

Now you know what the Bit_count () function is doing.

And then we'll go on to the second question. What is Bit_or ()?

Although they look like each other, they have different properties of the function.

Bit_or (expr) is the accuracy of the execution of all fields in this column of expr, or 64 bits, where many blogs have not enumerated how to use the example may be they think he is simple, or they may not use (they certainly will not use, hey Hey Hei)

Before that a shop table as the 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 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 rows in Set (0.06 sec)

Let's take article this column for Bit_or () and try it. What should be the results 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 a binary 0111 yes, and I don't care how many 1 consecutive or results are 1.

1 or 1 or 0 or 1 = 1

The logic that translates into this question is this: no matter how many times I visit today, I have visited this site (in logical notation is 1)

Let's go back and look at the operation again:

SELECT year, month, Bit_count (Bit_or (1<<day)) as day from T1 GROUPBY year, month;

Now it looks much better to explain. 1 The number of days left shifted, the corresponding number of digits represents the day of the one months (anyway 64, enough). Like today, 2017-06-02.

1<<2 is 100 means I visited this website today. I have the day this field or after the end of a 64 binary (the meaning of this number, I do not need to explain) and then use the Bit_count () function to get the results we want (can put me tired (?????), ink so long do not know how to speak clearly).

Use auto_increment

Listen to the name to know the automatic increase, directly on the operation:

mysql> CREATE TABLE Animals (
-ID mediumint not NULL auto_increment,
, name CHAR (+) 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>

First, a Animals table field has an ID and a name. The ID is set to the self-increment primary key, and the Insert data writes only name. The ID is then automatically populated.

There is also a command to modify the starting value of the auto_increment.

The operation is as follows:

ALTER TABLE tbl_name auto_increment = 2333;

To be Continued ...

MySQL Learning note 009