正则表达式
bitsCN.com怎么说呢,用markdown编辑好的文本,无法用在博客园中,不知道怎么处理。
一、排序
1、按多个列排序
使用逗号隔开,如果指定方向则紧挨着要排序的列名
对于多个列的排序,先按照前一个排序然后在前一个的基础上按照后面的排序。
如:
SELECT * FROM a2 ORDER BY a_id DESC,t_id desc
数据结果如下:
2、order by
与limit
SELECT * FROM a2 ORDER BY a_id DESC,t_id desc LIMIT 1
注意:order by
的位置,from
之后,limit
之前。
二、数据过滤操作符(and/or/in/not)
注意:优先级
当含有and
和or
时,and
的优先级高于or
,所以先执行and
,解决的办法就是使用()
括号的优先级高于and
,能够消除歧义。
如:
SELECT * FROM a2 WHERE (a_id = 2 or t_id>4) AND id>3
in
操作符的特点in
操作符完成了与or
相同的功能,如:
SELECT * FROM a2 WHERE t_id in(1,2,3)SELECT * FROM a2 WHERE t_id =1 OR t_id=2 OR t_id=3
上面两个功能相同。
既然如此,那么为什么还用in
呢,下面就说说in
的优点:
in
的优点:
1.在使用长的合法选项清单时,IN
操作符的更清楚直观
2.计算次序容易管理
3.比OR
执行快
4.IN
最大的优点可以包含其他select语句,能够动态的建立Where子句。
SELECT * FROM a2 WHERE t_id in( SELECT t_id FROM a2 WHERE id=5);
NOT 取反NOT
对IN
、BETWEEN
、exists
子句取反。
三、数据过滤通配符
%通配符
%:表示任何字符出现任意次数
LIKE '惠普%' :以'惠普'开头 LIKE '%惠普' :以'惠普'结尾 LIKE '%惠普%':包含'惠普' LIKE 's%e':以s开头,e结尾
注意:
1.除了一个或多个字符外,%还能匹配0个字符。
2.尾空格可能会干扰通配符匹配,如:保存great时,如果它后面有一个或多个空格,则%great
将不会匹配它们,因为后面有多余的字符(空格),解决办法就是使用双%,%great%
,一个更好的办法是使用函数去掉首尾空格。
3.%不能匹配NULL
.
_通配符
下划线通配符与%相同,但是它只匹配单个字符而非多个。
SELECT * FROM a WHERE name LIKE '_ood' #goodSELECT * FROM a WHERE name LIKE 'goo_' #goodSELECT * FROM a WHERE name LIKE 'go_d' #good
优化:
通配符在搜索处理上比其他的要慢些,所以要记住以下技巧:
1.不要过度使用通配符
2.不要把它们用在搜索模式的开始处,如若不然,搜索起来会很慢的。
3.仔细注意通配符的位置,切勿放错。
四、使用正则表达式
本小节学习如何在Where
子句中使用正则表达式来更好的控制数据过滤。
更多详细知识参考《正则表达式必知必会》
1.基本字符的匹配
SELECT * FROM a1 WHERE name regexp '1000' #匹配名称含有1000的所有行SELECT * FROM a1 WHERE name regexp '.000' #匹配以000结尾的所有行,(.正则中表示:匹配任意一个字符)
从中可以看到正则表达式能够模拟LIKE使用通配符,注意:在通配符能完成的时候就不用正则,因为正则可能慢点,当然正则也能简化通配符,完成更大的作用。所以要有所取舍。
LIKE
与REGEXP
的区别:
SELECT * FROM a1 WHERE name LIKE 'a' SELECT * FROM a1 WHERE name regexp 'a'
下面两条语句第一条不返回数据,第二条返回数据。
原因如下:
LIKE
匹配整个列值时,不会找到它,相应的行也不会被返回(除非使用通配符)
REGEXP
匹配时,会自动查找并返回结果。
那么REGEXP
也能匹配整个列值,使用^
和$
定位符即可!
匹配不区分大小写
Mysql正则大小写都会匹配,为区分大小写可使用binary
关键字,如:
SELECT * FROM a1 WHERE name LIKE binary '%J%' #使用LIKE+通配符匹配大写JSELECT * FROM a1 WHERE name regexp binary 'j' #使用正则匹配小写j
2.进行OR
匹配
|
为正则表达式的OR
操作符,表示匹配其中之一
SELECT * FROM a1 WHERE name regexp binary 'a|j|G'
3.匹配特定字符
使用[]
括起来的字符,将会匹配其中任意单个字符。
SELECT * FROM a1 WHERE name regexp '[12]st'
以上'[12]st'正则表达式,[12]定义一组字符,它的意思是匹配1或2,因此结果如下:
正如所见,
[]
是另一种OR
语句,[123]st可以是[1|2|3]st的缩写,也可以使用后者,注意:1|2|3 st
这样不推荐,因为mysql会假定你的意思是匹配'1'或'2'或'3st'除非你把字符|
括在一个集合中,如:[1|2|3]st
字符也可以否定,加^
则意味着除此之外,如[^123]st
意思是匹配除了1st、2st、3st之外的数据。
4.匹配范围
正则表达式可以使用
-
匹配一个范围,如[0-9]
匹配任意数字,无论是1还是11还是10111,[a-z]
匹配任意小写字母。
5.匹配特殊字符
如上,.
/-
/[]
等是正则表达式的特殊字符,如果要匹配含有这些字符的数据,就需要使用转义(escaping),//
。如//.
表示查找'.'。//
也用来引用元字符(具有特殊含义的字符),如://f
:表示换页//n
:表示换行//r
:表示回车//t
:表示制表//v
:表示纵向制表
Notes:
如果匹配反斜杠本身()则需要使用
///
为什么Mysql使用两个反斜杠(/),而很多语言使用一个反斜杠转义呢,因为mysql自己解释一个,正则表达式库解释一个。
6.匹配字符串

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Notepad++7.3.1
Easy-to-use and free code editor