Mysql SQL Statement Review 2 -- Regular Expressions and mysql Regular Expressions

Mysql SQL Statement Review 2 -- Regular Expressions and mysql Regular Expressions

Here, we will briefly review the usage of Regular Expressions in mysql when filtering data. This mainly refers to an example for each usage or a brief description.

The REGEXP keyword is used to filter data using regular expressions.

Basic character match:

SELECT prod_name FROM products WHERE prod_name REGEXP '201312 ';

This query statement indicates that the prod_name field contains 1000 of data, as long as any part of the value contains 1000

SELECT prod_name FROM products WHERE prod_name REGEXP '. 000 ';

". "Represents a single-character wildcard, which is equivalent to any single character, similar to the wildcard" _ "in LIKE. This statement means that the prod_name field in the query contains, for example, or a single character is followed by 000 data records.

OR match:

SELECT prod_name FROM products WHERE prod_name REGEXP '2017 | 100 ';

Here, "|" indicates or, that is, "or". This statement will query data that contains 1000 or 2000 or both in the prod_name field, or matching can give more than two matching items, such as 1000 | 2000 | 3000

Match one of the following characters:

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton ';

[] The enclosed part indicates matching one of them, such as 1 Ton and 2 Ton.

Matching range:

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-9] Ton ';

If the characters in the [] section in the previous example are long and both are arrays, and the numbers are continuous, you can change them to range match, so that the SQL statement will be shorter, of course, you can replace [1-9] with [123456789]. If it is a letter, you can use [a-z ].

Match special characters:

SELECT prod_name FROM products WHERE prod_name REGEXP '\\.';

To match special characters in mysql, you must add // escape characters, because special characters have special meanings in SQL statements.

Match multiple instances:

First, we need to introduce several matching characters.

* 0 or multiple matches

+ One or more matches (equal to {1 ,})

? 0 or one matching (equal to {0, 1 })

{N} specified number of matches

{N ,}no less than a specified number of matches

The range of {n, m} matches (m cannot exceed 255)

SELECT prod_name FROM products WHERE prod_name REGEXP '\ ([0-9] sticks? \\)';

In this statement? Matches zero or one occurrence of any character before it

OPERATOR:

^ Match the beginning of the text

$ Match the end of the text

[[: <:] Start of a word

[[: >:]] End of a word

SELECT prod_name FROM products WHERE prod_name REGEXP '^ [0-9 \.]';

The matched string indicates that only data starting with. And any number is queried.

How do I use regular expressions in SQL statements?

Regular expression syntax
A regular expression is a text mode consisting of common characters (such as characters a to z) and special characters (such as metacharacters. This mode describes one or more strings to be matched when searching the text subject. A regular expression is used as a template to match a character pattern with the searched string.
Here are some examples of regular expressions that may be encountered:
JScriptVBScript match
/^ \ [\ T] * $/"^ \ [\ t] * $" matches a blank row.
/\ D {2}-\ d {5}/"\ d {2}-\ d {5}" verify that an ID number consists of two digits, A hyphen and a five-digit combination.
/<(. *)>. * <\/\ 1>/"<(. *)>. * <\/\ 1>" matches an HTML Tag.
The following table shows a complete list of metacharacters and their behaviors in the context of a regular expression:
Character Description
\ Mark the next character as a special character, a literal character, a back reference, or an octal escape character. For example, 'n' matches the character "n ". '\ N' matches a line break. The sequence '\' matches "\" and "\ (" matches "(".
^ Matches the start position of the input string. If the Multiline attribute of the RegExp object is set, ^ matches the position after '\ n' or' \ R.
$ Matches the end position of the input string. If the Multiline attribute of the RegExp object is set, $ also matches the position before '\ n' or' \ R.
* Matches the previous subexpression zero or multiple times. For example, zo * can match "z" and "zoo ". * Is equivalent to {0 ,}.
+ Match the previous subexpression once or multiple times. For example, 'Zo + 'can match "zo" and "zoo", but cannot match "z ". + Is equivalent to {1 ,}.
? Match the previous subexpression zero or once. For example, "do (es )? "Can match" do "in" do "or" does ".? It is equivalent to {0, 1 }.
{N} n is a non-negative integer. Match n times. For example, 'O {2} 'cannot match 'O' in "Bob", but can match two o in "food.
{N,} n is a non-negative integer. Match at least n times. For example, 'O {2,} 'cannot match 'O' in "Bob", but can match all o in "foooood. 'O {1,} 'is equivalent to 'o + '. 'O {0,} 'is equivalent to 'o *'.
{N, m} m and n are non-negative integers ...... the remaining full text>

Common symbols of SQL Regular Expressions

SQL classification:
DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-data manipulation language (SELECT, DELETE, UPDATE, INSERT)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

First, we will briefly introduce the basic statements:
1. Description: Create a database
Create database database-name
2. Description: delete a database.
Drop database dbname
3. Description: Back up SQL server
--- Create a device for the backup data
USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'
--- Start backup
Backup database pubs TO testBack
4. Description: Create a new table.
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only
5. Description: Delete the new table drop table tabname
6. Description: Add a column.
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)
8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: create view viewname as select statement
Delete view: drop view viewname
10. Description: several simple basic SQL statements
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: update table1 set field1 = value1 where range
Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!
Sort: select * from table1 order by field1, field2 [desc]
Total: select count (*) as totalcount from table1
Sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
Maximum ...... remaining full text>