Table of Contents
在SQL结构化查询语言中,LIKE语句有着至关重要的作用。 
Home Database Mysql Tutorial sql语句中like匹配的用法详解_MySQL

sql语句中like匹配的用法详解_MySQL

Jun 01, 2016 pm 01:26 PM
sql statement where

bitsCN.com

在SQL结构化查询语言中,LIKE语句有着至关重要的作用。 

LIKE语句的语法格式是:select * from 表名 where 字段名 like 对应值(子串),它主要是针对字符型字段的,它的作用是在一个字符型字段列中检索包含对应子串的。 
假设有一个数据库中有个表table1,在table1中有两个字段,分别是name和sex二者全是字符型数据。现在我们要在姓名字段中查询以“张”字开头的记录,语句如下: 
Java代码 
  1. select * from table1 where name like "张*"  
如果要查询以“张”结尾的记录,则语句如下: 
Java代码 
  1. select * from table1 where name like "*张"  
这里用到了通配符“*”,可以说,like语句是和通配符分不开的。下面我们就详细介绍一下通配符。 
匹配类型   
模式 
举例 及 代表值 
说明 

多个字符 

c*c代表cc,cBc,cbc,cabdfec等 
它同于DOS命令中的通配符,代表多个字符。 

多个字符 

%c%代表agdcagd等 
这种方法在很多程序中要用到,主要是查询包含子串的。 

特殊字符 
  • a
  • a代表a*a
  • 代替* 

    单字符 

    b?b代表brb,bFb等 
    同于DOS命令中的?通配符,代表单个字符 

    单数字 

    k#k代表k1k,k8k,k0k 
    大致同上,不同的是代只能代表单个数字。 

    字符范围 
    - [a-z]代表a到z的26个字母中任意一个 指定一个范围中任意一个 
    续上 
    排除 [!字符] [!a-z]代表9,0,%,*等 它只代表单个字符 
    数字排除 [!数字] [!0-9]代表A,b,C,d等 同上 
    组合类型 字符[范围类型]字符 cc[!a-d]#代表ccF#等 可以和其它几种方式组合使用 
    假设表table1中有以下记录: 
        name                          sex 
                    张小明              男 
        李明天       男 
        李a天       女 
        王5五       男 
        王清五           男 

    下面我们来举例说明一下: 
    例1,查询name字段中包含有“明”字的。 
    select * from table1 where name like '%明%'  

    例2,查询name字段中以“李”字开头。 
    select * from table1 where name like '李*'  

    例3,查询name字段中含有数字的。 
     select * from table1 where name like '%[0-9]%'  

    例4,查询name字段中含有小写字母的。 
    select * from table1 where name like '%[a-z]%'  

    例5,查询name字段中不含有数字的。 
    select * from table1 where name like '%[!0-9]%'  

    以上例子能列出什么值来显而易见。但在这里,我们着重要说明的是通配符“*”与“%”的区别。 
    很多朋友会问,为什么我在以上查询时有个别的表示所有字符的时候用"%"而不用“*”? 
    例子结果: 
    1. select * from table1 where name like *明*  
    2.   select * from table1 where name like %明%  

    大家会看到,前一条语句列出来的是所有的记录,而后一条记录列出来的是name字段中含有“明”的记录,所以说,当我们作字符型字段包含一个子串的查询时最好采用“%”而不用“*”,用“*”的时候只在开头或者只在结尾时,而不能两端全由“*”代替任意字符的情况下。 
    更多有关mysql数据库的内容,请参考:http://www.jbxue.com/db/mysql。bitsCN.com
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    Video Face Swap

    Video Face Swap

    Swap faces in any video effortlessly with our completely free AI face swap tool!

    Hot Tools

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    Can I install mysql on Windows 7 Can I install mysql on Windows 7 Apr 08, 2025 pm 03:21 PM

    Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

    How to create tables with sql server using sql statement How to create tables with sql server using sql statement Apr 09, 2025 pm 03:48 PM

    How to create tables using SQL statements in SQL Server: Open SQL Server Management Studio and connect to the database server. Select the database to create the table. Enter the CREATE TABLE statement to specify the table name, column name, data type, and constraints. Click the Execute button to create the table.

    How to judge SQL injection How to judge SQL injection Apr 09, 2025 pm 04:18 PM

    Methods to judge SQL injection include: detecting suspicious input, viewing original SQL statements, using detection tools, viewing database logs, and performing penetration testing. After the injection is detected, take measures to patch vulnerabilities, verify patches, monitor regularly, and improve developer awareness.

    How to check SQL statements How to check SQL statements Apr 09, 2025 pm 04:36 PM

    The methods to check SQL statements are: Syntax checking: Use the SQL editor or IDE. Logical check: Verify table name, column name, condition, and data type. Performance Check: Use EXPLAIN or ANALYZE to check indexes and optimize queries. Other checks: Check variables, permissions, and test queries.

    Does mysql optimize lock tables Does mysql optimize lock tables Apr 08, 2025 pm 01:51 PM

    MySQL uses shared locks and exclusive locks to manage concurrency, providing three lock types: table locks, row locks and page locks. Row locks can improve concurrency, and use the FOR UPDATE statement to add exclusive locks to rows. Pessimistic locks assume conflicts, and optimistic locks judge the data through the version number. Common lock table problems manifest as slow querying, use the SHOW PROCESSLIST command to view the queries held by the lock. Optimization measures include selecting appropriate indexes, reducing transaction scope, batch operations, and optimizing SQL statements.

    Do mysql need to pay Do mysql need to pay Apr 08, 2025 pm 05:36 PM

    MySQL has a free community version and a paid enterprise version. The community version can be used and modified for free, but the support is limited and is suitable for applications with low stability requirements and strong technical capabilities. The Enterprise Edition provides comprehensive commercial support for applications that require a stable, reliable, high-performance database and willing to pay for support. Factors considered when choosing a version include application criticality, budgeting, and technical skills. There is no perfect option, only the most suitable option, and you need to choose carefully according to the specific situation.

    How to write a tutorial on how to connect three tables in SQL statements How to write a tutorial on how to connect three tables in SQL statements Apr 09, 2025 pm 02:03 PM

    This article introduces a detailed tutorial on joining three tables using SQL statements to guide readers step by step how to effectively correlate data in different tables. With examples and detailed syntax explanations, this article will help you master the joining techniques of tables in SQL, so that you can efficiently retrieve associated information from the database.

    How to recover data after SQL deletes rows How to recover data after SQL deletes rows Apr 09, 2025 pm 12:21 PM

    Recovering deleted rows directly from the database is usually impossible unless there is a backup or transaction rollback mechanism. Key point: Transaction rollback: Execute ROLLBACK before the transaction is committed to recover data. Backup: Regular backup of the database can be used to quickly restore data. Database snapshot: You can create a read-only copy of the database and restore the data after the data is deleted accidentally. Use DELETE statement with caution: Check the conditions carefully to avoid accidentally deleting data. Use the WHERE clause: explicitly specify the data to be deleted. Use the test environment: Test before performing a DELETE operation.

    See all articles