Mysql study notes

Calculate the number of visits based on days

First create a table and insert some data. Each piece of data represents the date the user visited a certain 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(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>

Chestnut: Query the number of visit days per month (duplicates are not counted)

The operation is as follows:

SELECT year, month, BIT_COUNT(BIT_OR(1<

mysql> SELECT year, month,
-> BIT_COUNT(BIT_OR(1<-> GROUP BY year, month ;
+------+-------+-----+
| year | month | day |
+------+--- ----+-----+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+ -----+
2 rows in set (0.00 sec)

mysql>

Portal Share a blog explaining BIT_COUNT() and BIT_OR().

There is another teleporter who 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 feel like you don’t understand it after reading it, then just listen to my vernacular.

Read this operation:

SELECT year, month, BIT_COUNT(BIT_OR(1<

I believe that most people will have two doubts in their minds. What do BIT_OR() and BIT_COUNT() do? Why is this still done?

First explain the BIT_COUNT() function:

BIT_COUNT(N) returns the number of 1's in N (binary).

I'll write a chestnut and you can Got it:

SELECT BIT_COUNT(100); What’s the answer to this?

Think about the binary representation of 100: 1100100

mysql> SELECT bin(100);
+----------+
| bin( 100) |
+----------+
| 1100100 |
+----------+
1 row in set (0.00 sec)

mysql>

Then the answer should be 3?

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

mysql>

Young man, you have really good eyesight, the answer is 3.

Now you know what the BIT_COUNT() function does.

Then let’s discuss the second question. What does BIT_OR() do?

Even though they look alike, they have different functions.

BIT_OR(expr) is to OR all the fields in the expr column. The accuracy of calculation execution is 64 bits. Many blogs here have not listed examples of how to use it. Maybe they think it is simple, or maybe Even they don’t know how to use it (they definitely don’t know how to use it, hehehe)

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 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 rows in set (0.06 sec)

Let’s try BIT_OR() on the article column. Before the experiment, guess what the result should be?

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 binary 0111, right, and no matter how many 1 there are, the result of continuous OR is always 1

1 or 1 or 0 or 1 = 1

The logic in this question is: No matter how many times I visited today, I have visited this website (the logical representation is 1)

Let’s go back and look at this operation again:

SELECT year, month, BIT_COUNT(BIT_OR(1<

Now it seems much easier to explain. 1 Shift the number of days to the left, and the corresponding number of digits represents the day of the month (64 bits are enough anyway). For example, today 2017-06-02

1<<2 is 100, which means I visited this website today. After ORing the day field, I got a 64-bit binary (I don’t think I need to explain the meaning of this number). Then I used the BIT_COUNT() function to count and got the result we wanted (it exhausted me to death) (๑•ᴗ•๑), I’ve been writing it down for so long, I don’t know if I understand it clearly).

UseAUTO_INCREMENT

Just listen to the name and you will know that it will be automatically added. Just go to the operation:

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>

First create an animals table with fields id and name. The id is set to an auto-incrementing primary key, and only the name is written when inserting data. Then the id will be filled in automatically.

There is also another command that can modify the starting value of AUTO_INCREMENT.

The operation is as follows:

ALTER TABLE tbl_name AUTO_INCREMENT = 2333;

to be continued. ..

The above is the detailed content of Mysql study notes. For more information, please follow other related articles on the PHP Chinese website!