catalogue
2. View creation, query, modification and Application
3, Application of regular expression in mysql
1, View
1. View definition
View is a table derived from one or more tables. It is a virtual table. This virtual table is a dynamic representation of real data
State mapping, including real data, is only mapped. This virtual table can dynamically save the result set because there is a gap between the view and the real table
It is a dynamic synchronization relationship, so when we modify the virtual table, the real data will also be affected. But what's the name of the view
None. It's a projection, so it doesn't take up space. We only need to set its permissions to ensure its security
Define the display conditions for the view and display different web content for different people.
The view is like a window through which you can see the data provided by the system. In this way, the user can not see it
The data in the whole database, and it cares about the data that is useful to itself. The database only stores the definition of the view, not the view
The data in the figure is stored in the original table. When using the view to query data, the database system will take it out of the original table
Corresponding data. The data in the view depends on the data in the original table. Once the data in the table changes, the data displayed in the view
It will also change. View can ensure data security and improve query efficiency.
Role of view
① To simplify the operation, you can define a view for frequently used queries, so that users do not have to specify conditions for the same query operation.
② . increase the security of data. Through the view, users can only query and modify the specified data.
③ Improve the logical independence of the table, and the view can shield the impact caused by the change of the original table structure.
2. View creation, query, modification and Application
2, Null and null values
NULL characters are often encountered during the use of SQL statements. NULL is usually used to indicate missing
Value, that is, the field has no value in the table. If you restrict some fields from being empty when creating a table, you can use NOT
NULL keyword. If it is not used, it can be empty by default. When inserting a record into a table or updating a record, if the field does not
NOT NULL and has no value. In this case, the field of the new record will be saved as NULL. Note that NULL values and numbers
Fields with word 0 or spaces are different, and fields with NULL value have no value. In SQL statements, use IS
NULL can be used to judge whether a field in the table is a NULL value. On the contrary, IS NOT NULL can be used to judge whether it is a NULL value.
Difference between NULL and NULL values:
The null value length is 0 and does not occupy space;
The length of null value is null, occupying space;
is null cannot determine null value;
Null values are processed with "=" or "< >";
During count() calculation, NULL will be ignored and NULL value will be added to the calculation.
mysql> select * from tt3; #Table used at this stage +----+----------+-------+------------+-------+------+ | id | name | score | address | hobby | addr | +----+----------+-------+------------+-------+------+ | 1 | liuyi | 80.00 | beijing | 2 | NULL | | 2 | wangwu | 90.00 | shengzheng | 2 | NULL | | 4 | tianqi | 99.00 | hangzhou | 5 | NULL | | 5 | jiaoshou | 98.00 | laowo | 3 | NULL | +----+----------+-------+------------+-------+------+ 4 rows in set (0.00 sec) mysql> update tt3 set addr='' where id=4; #Change the value of addr in id=4 to null Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update tt3 set addr='0' where id=1; #Change the value of addr in id=1 to 0 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tt3; #Modified tt3 table +----+----------+-------+------------+-------+------+ | id | name | score | address | hobby | addr | +----+----------+-------+------------+-------+------+ | 1 | liuyi | 80.00 | beijing | 2 | 0 | | 2 | wangwu | 90.00 | shengzheng | 2 | NULL | | 4 | tianqi | 99.00 | hangzhou | 5 | | | 5 | jiaoshou | 98.00 | laowo | 3 | NULL | +----+----------+-------+------------+-------+------+ 4 rows in set (0.00 sec) mysql> select count(addr) from tt3; #Count quantity: check whether null and empty values will be added to the statistics +-------------+ | count(addr) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> select * from tt3 where addr is null; #Query records with null values +----+----------+-------+------------+-------+------+ | id | name | score | address | hobby | addr | +----+----------+-------+------------+-------+------+ | 2 | wangwu | 90.00 | shengzheng | 2 | NULL | | 5 | jiaoshou | 98.00 | laowo | 3 | NULL | +----+----------+-------+------------+-------+------+ 2 rows in set (0.00 sec) mysql> select * from tt3 where addr is not null; #Query records with non empty values +----+--------+-------+----------+-------+------+ | id | name | score | address | hobby | addr | +----+--------+-------+----------+-------+------+ | 1 | liuyi | 80.00 | beijing | 2 | 0 | | 4 | tianqi | 99.00 | hangzhou | 5 | | +----+--------+-------+----------+-------+------+ 2 rows in set (0.00 sec) mysql>
3, Application of regular expression in mysql
MySQL regular expressions usually match the records according to the specified matching pattern when retrieving database records
Find the special string of. MySQL regular expressions use the keyword REGEXP to specify the matching pattern of regular expressions,
The matching patterns supported by the REGEXP operator are shown in the table.
^Match start character
p1|p2 matches p1 or P2
$match end character
[...] matches any character in the character set
. match any single character
[^...] matches any character that is not within brackets
*Match any preceding character
{n} Match the previous string n times
+ match the previous character at least once
{n,m} matches the previous string at least N times and at most m times
#Query student information starting with sh select id,name from info where name regexp '^sh'; #Query student information ending with n select id,name from info where name regexp 'n$'; #Query the student information contained in an's name select id,name from info where name regexp 'an'; #The query name starts with tangy and ends with n. I don't know what character is in the middle select id,name from info where name regexp 'tangy.n'; #Query the student information with an or zh in the name select id,name from info where name regexp 'an|zh'; #Query the student information whose name contains an and g at least once select id,name from info where name regexp 'ang+'; #Query student information whose name starts with s-x select id,name from info where name regexp '^[s-x]'; #Query student information whose name is not caicai select id,name from info where name regexp '[^caicai]'; #Query student information whose name does not begin with czx each letter select id,name from info where name regexp '^[^czx]';
4, Operator
MySQL operators are used to operate on field values in records. There are four types of MySQL operators: operator
Arithmetic operators, comparison operators, logical operators, and bitwise operators.
1. Arithmetic operator
The SELECT command is used to realize the most basic addition, subtraction, multiplication and division operations. MySQL supports the arithmetic operators used.
| Arithmetic operator | |
| operator | describe |
| + | addition |
| - | subtraction |
| * | multiplication |
| / | division |
| % | Surplus |
| Note: in division and remainder operations, the divisor cannot be 0. If the divisor is 0, the returned result is NULL. It should be noted that if there are multiple operators, they are calculated according to the priority of multiplication and division before addition and subtraction, and the operators with the same priority have no order. | |
mysql> select 2+3,4-1,3*4,6/2,5%3; #Arithmetic operator +-----+-----+-----+--------+------+ | 2+3 | 4-1 | 3*4 | 6/2 | 5%3 | +-----+-----+-----+--------+------+ | 5 | 3 | 12 | 3.0000 | 2 | +-----+-----+-----+--------+------+ 1 row in set (0.00 sec) mysql> select 2+4-3*4; #Operation by priority +---------+ | 2+4-3*4 | +---------+ | -6 | +---------+ 1 row in set (0.00 sec) mysql> create table mmt as select 1+2,2-1,3*2,4/2,5%2; #You can import the results of the operation into a new table Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from mmt; #View operation results +-----+-----+-----+--------+------+ | 1+2 | 2-1 | 3*2 | 4/2 | 5%2 | +-----+-----+-----+--------+------+ | 3 | 1 | 6 | 2.0000 | 1 | +-----+-----+-----+--------+------+ 1 row in set (0.00 sec) mysql> desc mmt; #View table structure +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | 1+2 | int(3) | NO | | 0 | | | 2-1 | int(3) | NO | | 0 | | | 3*2 | int(3) | NO | | 0 | | | 4/2 | decimal(5,4) | YES | | NULL | | | 5%2 | int(1) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>
2. Comparison operator
Comparison operator is a kind of operator often used when querying data records. By using the comparison operator, you can determine the
Which records are qualified? If the comparison result (returned by Boolean value) is true, it will return 1. If
If it is false, it returns 0. If the comparison result is uncertain, it returns NULL. When comparing strings, they are indistinguishable by default
Case sensitive. If you want to be case sensitive, you can use the binary keyword.
| Comparison operator | |
| operator | describe |
| = | be equal to |
| > | greater than |
| < | less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| != Or < > | Not equal to |
| is null | Determines whether a value is NULL |
| IS NOT NULL | Determines whether a value is not NULL |
| BETWEEN AND | Between the two |
| IN | In collection |
| LIKE | Wildcard matching |
| GREATEST | Returns the maximum value for two or more parameters |
| LEAST | Returns the minimum value for two or more parameters |
| REGEXP | regular expression |
① # The equal sign (=) is used to judge whether numbers, strings and expressions are equal. If they are equal, 1(true) is returned, such as
If it is not equal, it returns 0 (false). If one of the two values compared is NULL, the result of the comparison is NULL. Where
The comparison is based on the ASCII code. If the ASCII codes are equal, it means that the two characters are the same; if the ASCII codes are different
And so on, it means that the two characters are different.
◆ If both are integers, the comparison is based on integer values.
◆ if an integer is a string, the string will be automatically converted into a number and then compared. (in the program, it is generally not necessary.)
Yes, compare the two.
◆ if both are strings, compare them according to the string.
◆ if at least one of the two values is NULL, the comparison result is NULL.
② # The not equal (< >,! =) not equal sign can be written in two ways, namely < > or! =. It is used for numbers, strings and tables
A comparison of expressions that are not equal. If they are not equal, they return 1. If they are equal, they return 0. This is just the opposite of the return value of equal. You need to
Note that the not equal operator cannot be used to determine NULL.
③ # The greater than (>) operator is used to determine whether the operand on the left is greater than the operand on the right. If it is greater than 1, otherwise it returns 1
Return to 0, which cannot be used to judge NULL. The less than (<) operator is used to judge whether the operand on the left is less than the operand on the right,
If it is less than 1, otherwise it returns 0, which cannot be used to judge NULL. Greater than or equal (> =) determines whether the operand on the left is greater than
Is equal to the operand on the right. If it is greater than or equal to, it returns 1, otherwise it returns 0, which cannot be used to judge NULL. It is judged by less than or equal (< =)
Whether the operand on the left is less than or equal to the operand on the right. If it is less than or equal to, it returns 1, otherwise it returns 0 and cannot be used for judgment
NULL.
④ # Judge whether a value is / IS NOT NULL (is null, IS NOT NULL)
#IS NULL determines whether a value IS NULL. If it IS NULL, it returns 1, otherwise it returns 0.
#IS NOT NOLL determines whether a value is not NULL. If it is not NULL, it returns 1, otherwise it returns 0.
⑤ # The BETWEEN AND comparison operation is usually used to determine whether a value falls between two values. For example, to determine a number
Whether the word is between the other two numbers, you can also judge whether an English letter is between the other two letters, specific operation and condition symbol
Returns 1 when combined, otherwise returns 0.
Note: Between and covers > = and < = relationships.
⑥ # least and greatest (minimum and maximum)
LEAST: when there are two or more parameters, the minimum value is returned. If one of the values is NULL, the returned result is NULL
Is NULL.
GREATEST: when there are two or more parameters, the maximum value is returned. If one of the values is NULL, the maximum value is returned
The result is NULL.
To determine which is the smallest and which is the largest in a group of numbers or letters, you can use leap and GREATEST.
⑦ # IN determines whether a value is IN the corresponding list. If yes, it returns 1; otherwise, it returns 0.
NOT IN determines whether a value is NOT IN the corresponding list. If not, it returns 1, otherwise it returns 0.
⑧ # LIKE is used to match strings. If the match is successful, it returns 1, otherwise it returns 0
LIKE supports two wildcard characters: '%' is used to match any number of characters (* matches the previous character), while; '' can only match
With one character.
NOT LIKE is just the opposite of LIKE. If no match is successful, it returns 1, otherwise it returns 0.
3. Logical operator (Boolean)
Logical operators, also known as Boolean operators, are usually used to judge whether an expression is true or false. If true, it returns 1, otherwise it returns 1
0, TRUE and FALSE can also be represented by TRUE and FALSE. There are four logical operators supported in MySQL.
| Logical operator | |
| not or! | Logical non |
| and or&& | Logic and |
| or | Logical or |
| xor | Logical XOR |
① Logical non
The simplest logical operator is logical non, which is represented by NOT or! And logical non will follow it
If the operand after NOT is 0, the resulting value is 1; if
When the operand is non-0, the resulting value is 0; if the operand is NULL, the resulting value is NULL.
② , logic and
When all operands are non-zero and non null, the return value is 1, otherwise it is 0.
(null AND 0 are special) logical AND are represented by AND or & &.
It can be seen from the results that and and & & have the same effect,
1 and -1 have no 0 or null, so the return value is 1
There are 0 in 1 and 0, so the return value is 0
1 and null have nulls, so the return value is null
null and 0 return value is 0
③ , logical or (or)
Logical OR usually uses OR. Logical OR represents the contained operands. When either of them is a non-zero value and is not NULL, it returns
Returns 1, otherwise it returns 0. When one operand is null, if the other operand is a non-0 value, the return value is 1, otherwise it is 0
Null. If both operands are null, the return value is null.
④ Logical XOR
Operands with two non NULL values. If both are 0 or both are non-0, 0 is returned; if one is 0, the other is 0
If any value is non-0, the returned result is 1; if any value is NULL, the returned value is NULL.
4. Bitwise operator
Bit operators are actually operators that evaluate binary numbers. MySQL bitwise operations will first turn operands into binary numbers
Format (1010 1111), then perform bit operation, and finally change the calculation result from binary to decimal format for the convenience of users
View. MySQL supports 6 kinds of bitwise operators.
| 6 bit operators | |
| Bitwise Operators | describe |
| & | Bitwise AND |
| | | Bitwise OR |
| ~ | Bitwise inversion |
| ^ | Bitwise XOR |
| << | Shift left by bit |
| >> | Shift right by bit |
10 to binary is 1010; 15 to binary is 1111
◆ bitwise and operation (&) correspond to binary bits of 1. Their operation result is 1, otherwise it is 0, so 10 & 15
The result is 10.
◆ bitwise OR operation (|) means that if one or two corresponding binary bits are 1, the operation result is 1, otherwise it is 0, so 10|
The result of 15 is 15.
◆ bitwise XOR operation (^) means that the corresponding binary bits are different, and the operation result is 1, otherwise it is 0, so it is the result of 10 ^ 15
Is 5.
◆ bit by bit inversion (~) is the bit by bit inversion of the corresponding binary number, that is, 1 becomes 0 after inversion, and 0 becomes 1 after inversion. The second part of the number 1
The base number is 0001, which becomes 1110 after being reversed, and the binary number of digit 5 is 0101. Sum 1110 and 0101, and the result is
If it is binary 0100, the conversion to decimal is 4.
5. Operator priority
No matter which operator is used, there is a priority problem. The priority of operators determines the number of different operators
The sequence of calculation. Operators with higher levels will be calculated first. If the operators have the same level, MySQL will follow the sequence
The sequence is calculated from left to right, and the priority is shown in the table below.
| Operator priority | |
| priority | operator |
| 1 | ! |
| 2 | ~ |
| 3 | ^ |
| 4 | *,/,% |
| 5 | +,- |
| 6 | >>,<< |
| 7 | & |
| 8 | | |
| 9 | =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN |
| 10 | BETWEEN,CASE,WHEN,THEN,ELSE |
| 11 | NOT |
| 12 | &&,AND |
| 13 | ||,OR,XOR |
| 14 | := |
summary