• MySQL笔记-06 基础SQL操作


    文章目录

    本章开始讲解一些MySQL数据库的基本操作,这里基本均是DML语句

    1 运算符

    1.1 算术运算符

    算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括加、减、乘、除、求余。

    符号作用
    +加法运算
    -减法运算
    *乘法运算
    /除法运算
    %求余运算
    DIV除法运算,返回商。同“/”
    MOD求余运算,返回余数。同“%”

    加(+)、减(-)和乘(*)可以同时运算多个操作数。除号(/)和求余运算符(%)也可以同时计算多个操作数,但是这两个符号计算多个操作数不太好。DIV和MOD这两个运算符只有两个参数。进行除法和求余的运算时,如果第2个参数是0时,计算结果将是空值(NULL)。

    1.2 比较运算符

    比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符,可以判断表中的哪些记录是符合条件的。

    运算符名称示例
    =等于id=5
    <=>安全的等于运算符id=5
    >大于id>5
    <小于id<5
    =>大于等于id>=5
    <=小于等于id<=5
    !=<>不等于id!=5
    IS NULL为NULLid is null
    IS NOT NULL不为BULLid IS NOT NULL
    BETWEEN AND存在与指定范围id BETWEEN 1 AND 5
    IN存在与指定集合id IN (3,4,5)
    NOT IN不存在与指定集合name NOT IN (shi,li)
    LIKE模式匹配name LIKE ('shi%')
    NOT LIKE模式匹配name NOT LIKE ('shi%')
    REGEXPRLIKE正则表达式匹配name REGEXP 正则表达式

    1.2.1 运算符=

    =用来判断数字、字符串和表达式等是否相等。如果相等,返回1,否则返回0。

    在运用运算符=判断两个字符是否相同时,数据库系统都是根据字符的ASCII码进行判断的。如果ASCII码相等,则表示这两个字符相同。如果ASCII码不相等,则表示两个字符不同。切记空值(NULL)不能使用=来判断

    mysql> select 1=0,1=1,NULL=NULL;
    +-----+-----+-----------+
    | 1=0 | 1=1 | NULL=NULL |
    +-----+-----+-----------+
    |   0 |   1 |      NULL |
    +-----+-----+-----------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.2 运算符<=>

    <=>用来判断数字、字符串和表达式等是否相等。如果相等,返回1,否则返回0。

    其他均与=类似,NULL能用于<=>比较

    mysql> select 1<=>1, 2<=>0,0<=>0,NULL<=>NULL;
    +-------+-------+-------+-------------+
    | 1<=>1 | 2<=>0 | 0<=>0 | NULL<=>NULL |
    +-------+-------+-------+-------------+
    |     1 |     0 |     1 |           1 |
    +-------+-------+-------+-------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.3 运算符<>!=

    <>!=用来判断数字、字符串、表达式等是否不相等。如果不相等,则返回1;否则,返回0。这两个符号也不能用来判断空值(NULL)

    mysql> select 1<>0,1<>1,NULL<>NULL;
    +------+------+------------+
    | 1<>0 | 1<>1 | NULL<>NULL |
    +------+------+------------+
    |    1 |    0 |       NULL |
    +------+------+------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.4 运算符>

    >用来判断左边的操作数是否大于右边的操作数。如果大于,返回1;否则,返回0。同样空值(NULL)不能使用>来判断

    运算符<、运算符<=和运算符>=都与运算符>如出一辙,其使用方法基本相同,这里不再赘述。

    mysql> select 'a'>'b','abc'>'a',1>0;
    +---------+-----------+-----+
    | 'a'>'b' | 'abc'>'a' | 1>0 |
    +---------+-----------+-----+
    |       0 |         1 |   1 |
    +---------+-----------+-----+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.5 运算符IS NULL

    IS NULL用来判断操作数是否为空值(NULL)。操作数为NULL时,结果返回1;否则,返回0。IS NOT NULL刚好与IS NULL相反。

    mysql> select 0 is null,null is null;
    +-----------+--------------+
    | 0 is null | null is null |
    +-----------+--------------+
    |         0 |            1 |
    +-----------+--------------+
    1 row in set (0.00 sec)
    
    mysql> select 0 is not null,null is not null;
    +---------------+------------------+
    | 0 is not null | null is not null |
    +---------------+------------------+
    |             1 |                0 |
    +---------------+------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    = <> != > >= < <=等运算符都不能用来判断空值(NULL)。一旦使用,结果将返回NULL。如果要判断一个值是否为空值,可以使用<=>、IS NULL和IS NOT NULL来判断。注意:NULL和’ NULL’是不同的,前者表示为空值,后者表示一个由4个字母组成的字符串。

    1.2.6 运算符BETWEEN AND

    BETWEEN AND用于判断数据是否在某个取值范围内,其表达式如下。

    x1 BETWEEN min AND max
    
    • 1

    如果x1大于等于min,且小于等于max,结果将返回1,否则将返回0。此表达式等价于x1>=min and x1,当操作数类型不同时,比较时会遵循类型转换原则进行转换后,再进行比较运算。

    mysql> select 10 between 10 and 20,9 between 10 and 20;
    +----------------------+---------------------+
    | 10 between 10 and 20 | 9 between 10 and 20 |
    +----------------------+---------------------+
    |                    1 |                   0 |
    +----------------------+---------------------+
    1 row in set (0.01 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.7 运算符IN

    IN用于判断数据是否存在于某个集合中,其表达式如下。

    x1 IN(值1,值2,…,值n)
    
    • 1

    如果x1等于值1到值n中的任何一个值,结果将返回1;如果不是,结果将返回0。

    mysql> select 1 in (1,2,3) ,'t' in ('t','a','b','1','e'),0 in (1,2),1 in ('1','2');
    +--------------+------------------------------+------------+----------------+
    | 1 in (1,2,3) | 't' in ('t','a','b','1','e') | 0 in (1,2) | 1 in ('1','2') |
    +--------------+------------------------------+------------+----------------+
    |            1 |                            1 |          0 |              1 |
    +--------------+------------------------------+------------+----------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.8 运算符LIKE

    LIKE用来匹配字符串,其表达式如下。

    x1 LIKE s1
    
    • 1

    如果x1与字符串s1匹配,结果将返回1;否则返回0。

    mysql> select 123456 like '123%',123456 like '%123%',123456 like '%321%';
    +--------------------+---------------------+---------------------+
    | 123456 like '123%' | 123456 like '%123%' | 123456 like '%321%' |
    +--------------------+---------------------+---------------------+
    |                  1 |                   1 |                   0 |
    +--------------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.2.9 运算符REGEXP

    REGEXP同样用于匹配字符串,但其使用的是正则表达式进行匹配,其表达式如下。

    x1 REGEXP '匹配方式'
    
    • 1

    如果x1满足匹配方式,结果将返回1;否则将返回0。

    使用运算符REGEXP匹配字符串,其使用方法非常简单。REGEXP运算符经常与^$.一起使用。^用来匹配字符串的开始部分;$用来匹配字符串的结尾部分;.用来代表字符串中的一个字符。

    mysql> select 'abcdef' regexp 'ab','abcdefg' regexp 'k';
    +----------------------+----------------------+
    | 'abcdef' regexp 'ab' | 'abcdefg' regexp 'k' |
    +----------------------+----------------------+
    |                    1 |                    0 |
    +----------------------+----------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.3 逻辑运算符

    逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回1;如果表达式是假,结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符,分别是与、或、非和异或。

    符号作用
    &&AND逻辑与
    `
    !NOT逻辑非
    XOR逻辑异或

    1.3.1 与运算

    &&或者AND是与运算的两种表达方式。如果所有数据不为0且不为空值(NULL)时,结果返回1;如果存在任何一个数据为0时,结果返回0;如果存在一个数据为NULL且没有数据为0时,结果返回NULL。与运算符支持多个数据同时进行运算。

    mysql> select (1 and 1),(0 and 1),(3 and 1),(1 and null),(0 and null),(null and null);
    +-----------+-----------+-----------+--------------+--------------+-----------------+
    | (1 and 1) | (0 and 1) | (3 and 1) | (1 and null) | (0 and null) | (null and null) |
    +-----------+-----------+-----------+--------------+--------------+-----------------+
    |         1 |         0 |         1 |         NULL |            0 |            NULL |
    +-----------+-----------+-----------+--------------+--------------+-----------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.3.2 或运算

    ||或者OR表示或运算。当两个操作数均为非NULL值时,如果有任意一个操作数非0值,则结果为1,否则为0;当有一个操作数为NULL时,如果另一个操作数为非0值,则结果为1,否则结果为NULL。假如两个操作数均为NULL,则所得结果NULL。或运算符支持多个数据同时进行运算。

    mysql> select (1 or 0),(0 or 0),(1 or null),(0 or null),(1 or 1),(null or null),(1 or 2 or 1);
    +----------+----------+-------------+-------------+----------+----------------+---------------+
    | (1 or 0) | (0 or 0) | (1 or null) | (0 or null) | (1 or 1) | (null or null) | (1 or 2 or 1) |
    +----------+----------+-------------+-------------+----------+----------------+---------------+
    |        1 |        0 |           1 |        NULL |        1 |           NULL |             1 |
    +----------+----------+-------------+-------------+----------+----------------+---------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.3.3 非运算

    !或者NOT表示非运算。通过非运算,将返回与操作数据相反的结果。如果操作数据是非0的数字,结果返回0;如果操作数据是0,结果返回1;如果操作数据是NULL,结果返回NULL

    mysql> select not 0,not 1,not null;
    +-------+-------+----------+
    | not 0 | not 1 | not null |
    +-------+-------+----------+
    |     1 |     0 |     NULL |
    +-------+-------+----------+
    1 row in set (0.01 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.3.4 异或运算

    XOR表示异或运算。只要其中任何一个操作数据为NULL时,结果返回NULL;如果两个操作数都是非0值,或者都是0,则返回结果为0;如果一个为0,另一个为非0值,返回结果是1。

    mysql> select 1 xor 1,1 xor 2,0 xor 0,1 xor 0,0 xor 1,0 xor null,null xor 1,null xor null;
    +---------+---------+---------+---------+---------+------------+------------+---------------+
    | 1 xor 1 | 1 xor 2 | 0 xor 0 | 1 xor 0 | 0 xor 1 | 0 xor null | null xor 1 | null xor null |
    +---------+---------+---------+---------+---------+------------+------------+---------------+
    |       0 |       0 |       0 |       1 |       1 |       NULL |       NULL |          NULL |
    +---------+---------+---------+---------+---------+------------+------------+---------------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.4 位运算符

    位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数再进行位运算,然后再将计算结果从二进制数变回十进制数。MySQL中支持6种位运算符:

    符号作用
    &按位与(位AND)。进行该运算时,数据库系统会先将十进制数转换为二进制数。然后对应操作数的每个二进制位上进行与运算。1和1相与得1,与0相与得0。运算完成后再将二进制数变回十进制。
    ``
    ~按位取反。将操作数转换为二进制数后,每位都进行取反运算。1取反后变成0,0取反后变成1
    ^按位异或(位XOR)。将操作数转换为二进制数后,每位都进行异或运算。相同的数异或的结果是0,不同的数异或的结果是1
    <<按位左移。m<表示m的二进制数向左移n位,右边补上n个0。
    >>按位右移。m>>n表示m的二进制数向右移n位,左边补上n个0。

    1.4.1 位与

    mysql> select 2&3;
    +-----+
    | 2&3 |
    +-----+
    |   2 |
    +-----+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2的二进制数是10,3是11,所以10&11的结果是10,十进制数字是2。

    mysql> select 2&3&4;
    +-------+
    | 2&3&4 |
    +-------+
    |     0 |
    +-------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    4的二进制是100,和上面的10做与 100&010,结果000。

    1.4.2 位或

    mysql> select 2|3;
    +-----+
    | 2|3 |
    +-----+
    |   3 |
    +-----+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    前面的例子,10|11,等11,结果3。

    1.4.3 位异或

    10^11 等于01,结果是1。

    mysql> select 2^3;
    +-----+
    | 2^3 |
    +-----+
    |   1 |
    +-----+
    1 row in set (0.01 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.4.4 位取反

    位取反对操作数的二进制位做NOT操作,这里的操作数只能是一位。

    mysql> select ~1,~18446744073709551614;
    +----------------------+-----------------------+
    | ~1                   | ~18446744073709551614 |
    +----------------------+-----------------------+
    | 18446744073709551614 |                     1 |
    +----------------------+-----------------------+
    1 row in set (0.02 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.4.5 位右移

    对左操作数向右移动指定的位数,并且左边补0。

    mysql> select bin(100),bin(100>>3),100>>3;
    +----------+-------------+--------+
    | bin(100) | bin(100>>3) | 100>>3 |
    +----------+-------------+--------+
    | 1100100  | 1100        |     12 |
    +----------+-------------+--------+
    1 row in set (0.00 sec)
    
    mysql> 
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    位右移类似,不做过多赘述。

    1.5 运算符的优先级

    由于在实际应用中可能需要同时使用多个运算符。这就必须考虑运算符的运算顺序。

    MySQL运算符使用的优先级,如下图所示。按照从高到低,从左到右的级别进行运算操作。如果优先级相同,则表达式左边的运算符先运算。

    image.png

    2 流程控制语句

    在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。其中包括IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句,它们可以进行流程控制。

    2.1 IF语句

    IF语句用来进行条件判断,根据不同的条件执行不同的操作。该语句在执行时首先判断IF后的条件是否为真,则执行THEN后的语句,如果为假则继续判断IF语句直到为真为止,当以上都不满足时则执行ELSE语句后的内容。IF语句表示形式如下:

    IF condition THEN
    …
    [ELSE condition THEN]
    …
    [ELSE]
    …
    ENDIF
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.2 CASE语句

    CASE语句为多分支语句结构,该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值,如果查找到则执行该分支的内容,否则执行ELSE后的内容。CASE语句表示形式如下:

    CASE value
    WHEN value THEN …
    [WHEN valueTHEN…]
    [ELSE…]
    END CASE
    
    • 1
    • 2
    • 3
    • 4
    • 5

    value参数表示条件判断的变量;WHEN……THEN中的value参数表示变量的取值。

    CASE语句另一种语法表示形式如下:

    CASE WHEN value THEN…
    [WHEN valueTHEN…]
    [ELSE…]
    END CASE
    
    • 1
    • 2
    • 3
    • 4

    2.3 WHILE循环语句

    WHILE循环语句执行时首先判断condition条件是否为真,如果是则执行循环体,否则退出循环。该语句表示形式如下:

    WHILE condition DO
    …
    END WHILE;
    
    • 1
    • 2
    • 3

    2.4 LOOP循环语句

    该循环没有内置的循环条件,但可以通过LEAVE语句退出循环。LOOP语句表示形式如下。

    LOOP
    …
    END LOOP
    
    • 1
    • 2
    • 3

    LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,中间省略的部分是需要重复执行的语句。在循环内的语句一直重复直至循环被退出,退出循环应用LEAVE语句。

    LEAVE语句经常和BEGIN……END或循环一起使用,其表示形式如下。

    LEAVE label
    
    • 1

    label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

    create procedure example_loop (out sum int)
    begin
    declare i int default 1;
    declare s int default 0;
    loop_label:loop
    set s=s+i;
    set i=i+1;
    if i>100 then
    leave loop_label;
    end if;
    end loop;
    set sum=s;
    end
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    2.5 REPEAT循环语句

    该语句先执行一次循环体,之后判断condition条件是否为真,为真则退出循环,否则继续执行循环。REPEAT语句表示形式如下:

    REPEAT
    …
    UNTIL condition
    END REPEAT
    
    • 1
    • 2
    • 3
    • 4

    首先定义变量i和s,分别用来控制循环的次数和保存前100项和,进入循环体后首先使s的值加i,之后使i的值加1,直到i大于100时退出循环并输出结果。

    create procedure example_repeat (out sum int)
    begin
    declare i int default 1;
    declare s int default 0;
    repeat
    set s=s+i;
    set i=i+1;
    until i>100
    end repeat;
    set sum=s;
    end
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    3 表数据的增、删、改操作

    3.1 插入数据

    在建立一个空的数据库和数据表时,首先需要考虑的是如何向数据表中添加数据,该操作可以使用INSERT语句来完成。使用INSERT语句可以向一个已有数据表中插一个新行,也就是插入一行新记录。在MySQL中,INSERT语句有3种语法格式,分别是INSERT……VALUES语句、INSERT……SET语句和INSERT……SELECT语句。下面将分别进行介绍。

    3.1.1 使用INSERT……VALUES语句插入数据

    使用INSERT……VALUES语句插入数据,是INSERT语句的最常用的语法格式。它的语法格式如下:

    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,……)]
    VALUES ({值 | DEFAULT},……),(……),……
    [ ON DUPLICATE KEY UPDATE 字段名=表达式,……]
    
    • 1
    • 2
    • 3
    • 4

    参数说明如下:

    • (1)[LOW_PRIORITY|DELAYED|HIGH_PRIORITY]:可选项,其中,LOW_PRIORITY是INSERT、UPDATE和DELETE语句都支持的一种可选修饰符,通常应用在多用户访问数据库的情况下,用于指示MySQL降低INSERT、DELETE或UPDATE操作执行的优先级;DELAYED是INSERT语句支持的一种可选修饰符,用于指定MySQL服务器把待插入的行数据放到一个缓冲器中,直到待插数据的表空闲时,才真正在表中插入数据行;HIGH_PRIORITY是INSERT和SELECT语句支持的一种可选修饰符,用于指定INSERT和SELECT操作优先执行的。
    • (2)[IGNORE]:可选项,表示在执行INSERT语句时,所出现的错误都会被当作警告处理。
    • (3)[INTO]数据表名:可选项,用于指定被操作的数据表。
    • (4)[(字段名,……)]:可选项,当不指定该选项时,表示要向表中所有列插入数据,否则表示向数据表的指定列插入数据。
    • (5)VALUES ({值| DEFAULT},……),(……),……:必选项,用于指定需要插入的数据清单,其顺序必须与字段的顺序相对应。其中的每一列的数据可以是一个常量、变量、表达式或者NULL,但是其数据类型要与对应的字段类型相匹配;也可以直接使用DEFAULT关键字,表示为该列插入默认值,但是使用的前提是已经明确指定了默认值,否则会出错。
    • (6)ON DUPLICATE KEY UPDATE子句:可选项,用于指定向表中插入行时,如果导致UNIQUE KEY或PRIMARY KEY出现重复值,系统会根据UPDATE后的语句修改表中原有行数据。

    INSERT……VALUES语句在使用时,通常有以下3种方式:

    • (1) 插入完整数据:将全部字段都写入值
    • (2) 插入数据记录的一部分:只插入表的一行中的某几个字段的值
    • (3) 插入多条记录:一次性插入多条数据记录。使用该方法批量插入数据,比使用多条单行的INSERT语句的效率要高。
    INSERT INTO tb_admin (user,password,createtime)
    VALUES('mrbccd','111', '2014-09-05 10:35:26')
    ,( 'mingri','111', '2014-09-05 10:45:27')
    ,( 'mingrisoft','111', '2014-09-05 10:55:28');
    
    • 1
    • 2
    • 3
    • 4

    3.1.2 使用INSERT……SET语句插入数据

    在MySQL中,除了可以使用INSERT……VALUES语句插入数据外,还可以使用INSERT……SET语句插入数据。这种语法格式用于通过直接给表中的某些字段指定对应的值来实现插入指定数据,对于未指定值的字段将采用默认值进行添加。

    INSERT……SET语句的语法格式如下:

    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名
    SET 字段名={值 | DEFAULT}, ……
    [ ON DUPLICATE KEY UPDATE 字段名=表达式,……]
    
    • 1
    • 2
    • 3
    • 4

    参数说明如下。

    • (1)[LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]:可选项,其作用与INSERT……VALUES语句相同,这里将不再赘述。
    • (2)[INTO]数据表名:用于指定被操作的数据表,其中,[INTO]为可选项,可以省略。
    • (3)SET字段名={值| DEFAULT}:用于给数据表中的某些字段设置要插入的值。
    • (4)ON DUPLICATE KEY UPDATE子句:可选项,其作用与INSERT……VALUES语句相同,这里将不再赘述。

    3.1.3 插入查询结果

    在MySQL中,支持将查询结果插入到指定的数据表中,这可以通过INSERT……SELECT语句来实现。

    INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,……)]
    SELECT ……
    [ ON DUPLICATE KEY UPDATE 字段名=表达式, …… ]
    
    • 1
    • 2
    • 3
    • 4

    参数说明如下:

    • (1)[LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]:可选项,其作用与INSERT……VALUES语句相同,这里不再赘述。
    • (2)[INTO]数据表名:用于指定被操作的数据表,其中,[INTO]为可选项,可以省略。
    • (3)[(字段名,……)]:可选项,当不指定该选项时,表示要向表中所有列插入数据,否则表示向数据表的指定列插入数据。
    • (4)SELECT子句:用于快速地从一个或者多个表中取出数据,并将这些数据作为行数据插入到目标数据表中。需要注意的是,SELECT子句返回的结果集中的字段数、字段类型必须与目标数据表完全一致。
    • (5)ON DUPLICATE KEY UPDATE子句:可选项,其作用与INSERT……VALUES语句相同,这里不再赘述。

    3.2 修改数据

    要执行修改的操作可以使用UPDATE语句,语法如下:

    UPDATE [LOW_PRIORITY] [IGNORE] 数据表名
    SET 字段1=值1 [, 字段2=值2……]
    [WHERE 条件表达式]
    [ORDER BY……]
    [LIMIT 行数]
    
    • 1
    • 2
    • 3
    • 4
    • 5

    参数说明如下:

    • (1)[LOW_PRIORITY]:可选项,表示在多用户访问数据库的情况下可用于延迟UPDATE操作,直到没有别的用户再从表中读取数据为止。这个过程仅适用于表级锁的存储引擎(如IyISAM、MEMORY和MERGE)。
    • (2)[IGNORE]:在MySQL中,通过UPDATE语句更新表中多行数据时,如果出现错误,那么整个UPDATE语句操作都会被取消,错误发生前更新的所有行将被恢复到它们原来的值。因此,为了在发生错误时也要继续进行更新,则可以在UPDATE语句中使用IGNORE关键字。
    • (3)SET子句:必选项,用于指定表中要修改的字段名及其字段值。其中的值可以是表达式,也可以是该字段所对应的默认值。如果指定默认值,那么使用关键字DEFAULT指定。
    • (4)WHERE子句:可选项,用于限定表中要修改的行,如果不指定该子句,那么UPDATE语句会更新表中的所有行。
    • (5)ORDER BY子句:可选项,用于限定表中的行被修改的次序。
    • (6)LIMIT子句:可选项,用于限定被修改的行数。

    3.3 删除数据

    在数据库中,有些数据已经失去意义或者错误时就需要将它们删除,在MySQL中,可以使用DELETE语句或者TRUNCATE TABLE语句删除表中的一行或多行数据。

    3.3.1 通过DELETE语句删除数据

    通过DELETE语句删除数据的基本语法格式如下:

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 数据表名
    [WHERE 条件表达式]
    [ORDER BY……]
    [LIMIT 行数]
    
    • 1
    • 2
    • 3
    • 4

    参数说明如下:

    • (1)[LOW_PRIORITY]:可选项,表示在多用户访问数据库的情况下可用于延迟DELETE操作,直到没有别的用户再从表中读取数据为止。这个过程仅适用于表级锁的存储引擎(如IyISAM、MEMORY和MERGE)。
    • (2)[QUICK]:可选项,用于加快部分种类的删除操作的速度。
    • (3)[IGNORE]:在MySQL中,通过DELETE语句删除表中多行数据时,如果出现错误,那么整个DELETE语句操作都会被取消,错误发生前更新的所有行将被恢复到它们原来的值。因此,为了在发生错误时也要继续进行删除,则可以在DELETE语句中使用IGNORE关键字。
    • (4)数据表名:用于指定要删除的数据表的表名。
    • (5)WHERE子句:可选项,用于限定表中要删除的行,如果不指定该子句,那么DELETE语句会删除表中的所有行。
    • (6)ORDER BY子句:可选项,用于限定表中的行被删除的次序。
    • (7)LIMIT子句:可选项,用于限定被删除的行数。

    3.3.2 通过TRUNCATE TABLE语句删除数据

    在删除数据时,如果要从表中删除所有的行,通过TRUNCATE TABLE语句删除数据的基本语法格式如下:

    TRUNCATE [TABLE] 数据表名
    
    • 1

    在上面的语法中,数据表名表示的就是删除的数据表的表名,也可以使用“数据库名.数据表名”来指定该数据表隶属于哪个数据库。
    注意
    由于TRUNCATE TABLE语句会删除数据表中的所有数据,并且无法恢复,因此使用TRUNCATE TABLE语句时一定要十分小心。

    4 数据查询

    4.1 基本查询语句

    SELECT语句是最常用的查询语句,它的使用方式有些复杂,但功能是相当强大的。SELECT语句的基本语法如下:

    select selection_list //要查询的内容,选择哪些列
    from 数据表名 //指定数据表
    where primary_constraint //查询时需要满足的条件,行必须满足的条件
    group by grouping_columns //如何对结果进行分组
    order by sorting_cloumns //如何对结果进行排序
    having secondary_constraint //查询时满足的第二条件
    limit count //限定输出的查询结果
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 使用SELECT语句查询一个数据表
      使用SELECT语句时,首先要确定所要查询的列。“*”代表所有的列。

    • 查询表中的一列或多列
      针对表中的多列进行查询,只要在select后面指定要查询的列名即可,多列之间用“,”分隔。

    • 从一个或多个表中获取数据
      使用SELECT语句进行查询,需要确定所要查询的数据在哪个表或哪些表中,在对多个表进行查询时,同样使用“,”对多个表进行分隔。

    在查询数据库中的数据时,如果数据中涉及中文字符串,有可能在输出时会出现乱码。那么最后在执行查询操作之前,通过set names语句设置其编码格式,然后再输出中文字符串时就不会出现乱码了。

    还可以在WHERE子句中使用连接运算来确定表之间的联系,然后根据这个条件返回查询结果。

    4.2 单表查询

    单表查询是指从一张表中查询所需要的数据,所有查询操作都比较简单。

    4.2.1 查询所有字段

    查询所有字段是指查询表中所有字段的数据。这种方式可以将表中所有字段的数据都查询出来。在MySQL中可以使用“*”代表所有的列,即可查出所有的字段,语法格式如下:

    SELECT * FROM 表名;
    
    • 1

    4.2.2 查询指定字段

    查询指定字段可以使用下面的语法格式:

    SELECT 字段名 FROM 表名;
    
    • 1

    如果是查询多个字段,可以使用“,”对字段进行分隔。

    4.2.3 查询指定数据

    如果要从很多记录中查询出指定的记录,那么就需要一个查询的条件。设定查询条件应用的是WHERE子句。通过它可以实现很多复杂的条件查询。在使用WHERE子句时,需要使用一些比较运算符来确定查询的条件。

    4.2.4 带关键字IN的查询

    关键字IN可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中,则满足查询条件,该记录将被查询出来;如果不在集合中,则不满足查询条件。其语法格式如下:

    SELECT * FROM 表名 WHERE 条件 [NOT] IN(元素1,元素2,……,元素n);
    
    • 1
    • (1)[NOT]:是可选项,加上NOT表示不在集合内满足条件;
    • (2)元素:表示集合中的元素,各元素之间用逗号隔开,字符型元素需要加上单引号。

    4.2.5 带关键字BETWEEN AND的范围查询

    关键字BETWEEN AND可以判断某个字段的值是否在指定的范围内。如果字段的值在指定范围内,则满足查询条件,该记录将被查询出来。如果不在指定范围内,则不满足查询条件。其语法如下:

    SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2;
    
    • 1
    • (1)[NOT]:可选项,表示不在指定范围内满足条件。
    • (2)取值1:表示范围的起始值。
    • (3)取值2:表示范围的终止值。

    4.2.6 带LIKE的字符匹配查询

    LIKE属于较常用的比较运算符,通过它可以实现模糊查询。它有两种通配符:“%”和下划线“_”。

    • (1)“%”可以匹配一个或多个字符,可以代表任意长度的字符串,长度可以为0。例如,“明%技”表示以“明”开头,以“技”结尾的任意长度的字符串。该字符串可以代表“明日科技”“明日编程科技”“明日图书科技”等字符串。
    • (2)_只匹配一个字符。例如,m_n表示以m开头,以n结尾的3个字符。中间的_可以代表任意一个字符。

    字符串“p”和“入”都算作一个字符,在这点上英文字母和中文是没有区别的。

    4.2.7 用关键字IS NULL查询空值

    关键字IS NULL可以用来判断字段的值是否为空值(NULL)。如果字段的值是空值,则满足查询条件,该记录将被查询出来。如果字段的值不是空值,则不满足查询条件。其语法格式如下:

    IS [NOT] NULL
    
    • 1

    其中,“NOT”是可选项,表示字段不是空值时满足条件。

    4.2.8 带关键字AND的多条件查询

    关键字AND可以用来联合多个条件进行查询。使用关键字AND时,只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个,这样的记录将被排除掉。关键字AND的语法格式如下:

    select * from 数据表名 where 条件1 and 条件2 [……AND 条件表达式n];
    
    • 1

    关键字AND连接两个条件表达式,可以同时使用多个关键字AND来连接多个条件表达式。

    4.2.9 带关键字OR的多条件查询

    关键字OR也可以用来联合多个条件进行查询,但是与关键字AND不同,关键字OR只要满足查询条件中的一个,那么此记录就会被查询出来;如果不满足这些查询条件中的任何一个,这样的记录将被排除掉。关键字OR的语法格式如下:

    select * from 数据表名 where 条件1 OR 条件2 [……OR 条件表达式n];
    
    • 1

    关键字OR可以用来连接两个条件表达式。而且,可以同时使用多个关键字OR连接多个条件表达式。

    4.2.10 用关键字DISTINCT去除结果中的重复行

    使用关键字DISTINCT可以去除查询结果中的重复记录,语法格式如下:

    select distinct 字段名 from 表名;
    
    • 1

    4.2.11 用关键字ORDER BY对查询结果排序

    使用关键字ORDER BY可以对查询的结果进行升序(ASC)和降序(DESC)排列,在默认情况下,ORDER BY按升序输出结果。如果要按降序排列可以使用DESC来实现。语法格式如下:

    ORDER BY 字段名 [ASC|DESC];
    
    • 1
    • (1)ASC表示按升序进行排序。
    • (2)DESC表示按降序进行排序。

    对含有NULL值的列进行排序时,如果是按升序排列,NULL值将出现在最前面,如果是按降序排列,NULL值将出现在最后。

    4.2.12 用关键字GROUP BY分组查询

    通过关键字GROUP BY可以将数据划分到不同的组中,实现对记录进行分组查询。在查询时,所查询的列必须包含在分组的列中,目的是使查询到的数据没有矛盾。

    • 使用关键字GROUP BY来分组
      单独使用关键字GROUP BY查询结果只显示每组的一条记录。
    select id,books,talk from tb_book GROUP BY talk;
    
    • 1
    • 关键字GROUP BY与GROUP_CONCAT()函数一起使用
      使用关键字GROUP BY和GROUP_CONCAT()函数查询,可以将每个组中的所有字段值都显示出来。
    select id,books,GROUP_CONCAT(talk) from tb_book GROUP BY talk;
    
    • 1
    • 按多个字段进行分组
      使用关键字GROUP BY也可以按多个字段进行分组。

    4.2.13 用关键字LIMIT限制查询结果的数量

    查询数据时,可能会查询出很多的记录,而用户需要的记录可能只是很少的一部分,这样就需要来限制查询结果的数量。LIMIT是MySQL中的一个特殊关键字。关键字LIMIT可以对查询结果的记录条数进行限定,控制它输出的行数。

    select * from tb_login order by id asc limit 3;
    
    • 1

    使用关键字LIMIT还可以从查询结果的中间部分取值。首先要定义两个参数,参数1是开始读取的第一条记录的编号(在查询结果中,第一个结果的记录编号是0,而不是1);参数2是要查询记录的个数。

    select * from tb_login where id order by id asc limit 1,2;
    
    • 1

    4.3 聚合函数查询

    聚合函数的最大特点是它们根据一组数据求出一个值。聚合函数的结果值只根据选定行中非NULL的值进行计算,NULL值被忽略

    4.3.1 COUNT()函数

    COUNT()函数,对于除*以外的任何参数,返回所选择集合中非NULL值的行的数目;对于参数*,返回选择集合中所有行的数目,包含NULL值的行。没有WHERE子句的COUNT(*)是经过内部优化的,能够快速返回表中所有的记录总数。

    4.3.2 SUM()函数

    SUM()函数可以求出表中某个字段取值的总和。

    4.3.3 AVG()函数

    AVG()函数可以求出表中某个字段取值的平均值。

    4.3.4 MAX()函数

    MAX()函数可以求出表中某个字段取值的最大值。

    4.3.5 MIN()函数

    MIN()函数可以求出表中某个字段取值的最小值。

    4.4 连接查询

    连接是把不同表的记录连到一起的最普遍的方法。一种错误的观念认为由于MySQL的简单性和源代码开放性,使它不擅长连接。MySQL从一开始就能够很好地支持连接,现在还以支持标准的SQL92连接语句而自豪,这种连接语句可以以多种高级方法来组合表记录。

    4.4.1 内连接查询

    内连接是最普遍的连接类型,而且是最匀称的,因为它们要求构成连接的每一部分的每个表的匹配,不匹配的行将被排除。

    内连接的最常见的例子是相等连接,也就是连接后的表中的某个字段与每个表中的都相同。这种情况下,最后的结果集只包含参加连接的表中与指定字段相符的行。

    select name,books from tb_login,tb_book where tb_login.user=tb_book.user;
    
    • 1

    4.4.2 外连接查询

    与内连接不同,外连接是指使用OUTER JOIN关键字将两个表连接起来。外连接生成的结果集不仅包含符合连接条件的行数据,而且还包括左表(左外连接时的表)、右表(右外连接时的表)或两边连接表(全外连接时的表)中所有的数据行。语法格式如下:

    SELECT 字段名称 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名1=表名2.属性名2;
    
    • 1

    外连接分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)和全外连接3种类型。

    • 左外连接
      左外连接(LEFT JOIN)是指将左表中的所有数据分别与右表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包括左表中不符合条件的数据,并在右表的相应列中添加NULL值。

    • 右外连接
      右外连接(RIGHT JOIN)是指将右表中的所有数据分别与左表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包括右表中不符合条件的数据,并在左表的相应列中添加NULL。

    4.4.3 复合条件连接查询

    在连接查询时,也可以增加其他的限制条件。通过多个条件的复合查询,可以使查询结果更加准确。

    4.5 子查询

    子查询就是SELECT查询是另一个查询的附属。MySQL 4.1可以嵌套多个查询,在外面一层的查询中使用里面一层查询产生的结果集。这样就不是执行两个(或者多个)独立的查询,而是执行包含一个(或者多个)子查询的单独查询。

    当遇到这样的多层查询时,MySQL从最内层的查询开始,然后从它开始向外向上移动到外层(主)查询,在这个过程中每个查询产生的结果集都被赋给包围它的父查询,接着这个父查询被执行,它的结果也被指定给父查询。

    除了结果集经常由包含一个或多个值的一列组成外,子查询和常规SELECT查询的执行方式一样。子查询可以用在任何可以使用表达式的地方,它必须由父查询包围,而且,如同常规的SELECT查询,它必须包含一个字段列表(这是一个单列列表)、一个具有一个或者多个表名字的FROM子句以及可选的WHERE、HAVING和GROUP BY子句。

    4.5.1 带关键字IN的子查询

    只有子查询返回的结果列包含一个值时,比较运算符才适用。假如一个子查询返回的结果集是值的列表,这时比较运算符就必须用关键字IN代替。

    IN运算符可以检测结果集中是否存在某个特定的值,如果检测成功就执行外部的查询。

    select * from tb_login where user in(select user from tb_book);
    
    • 1

    4.5.2 带比较运算符的子查询

    子查询可以使用比较运算符,包括=、!=、>、>=、<和<=等。比较运算符在子查询时使用非常广泛。

    select id,books,row from tb_book where row>=(select row from tb_row where id=1);
    
    • 1

    4.5.3 带关键字EXISTS的子查询

    使用关键字EXISTS时,内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值(true),否则将返回一个假值(false)。当返回的值为true时,外层查询语句将进行查询;当返回的值为false时,外层查询语句不进行查询或者查询不出任何记录。

    select * from tb_row where exists (select * from tb_book where id=27);
    
    • 1

    当关键字EXISTS与其他查询条件一起使用时,需要使用AND或者OR来连接表达式与EXISTS关键字。

    select id,books,row from tb_book where row>=90 and exists(select * from tb_row where name='优秀');
    
    • 1

    与关键字EXISTS刚好相反,使用关键字NOT EXISTS时,当返回的值是true时,外层查询语句不执行查询;当返回值是false时,外层查询语句将执行查询。

    4.5.4 带关键字ANY的子查询

    关键字ANY表示满足其中任意一个条件。使用关键字ANY时,只要满足内层查询语句返回的结果中的任意一个,就可以通过该条件来执行外层查询语句。

    any,all关键字必须与一个比较操作符一起使用。any关键词可以理解为“对于子查询返回的列中的任一数值,如果比较结果为true,则返回true”。

    查询tb_book表中row字段的值小于tb_row表中row字段最小值的记录,首先查询出tb_row表中row字段的值,然后使用关键字ANY(表示小于所有值)判断,查询语句如下:

    select books,row from tb_book where row
    • 1
    • 示例:
    select s1 from t1 where s1 > any (select s1 from t2);
    
    • 1

    假设any后面的s1返回了三个值,那其实就等价于

    select s1 from t1 where s1 > result1 or s1 > result2 or s2 > result3
    
    • 1

    4.5.5 带关键字ALL的子查询

    关键字ALL表示满足所有条件。使用关键字ALL时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。

    all的意思是“对于子查询返回的列中的所有值,如果比较结果为true,则返回true”。

    all的用法相当于把4.5.4示例语句的‘or’缓冲‘and’

    查询tb_book表中row字段的值大于tb_row表中row字段最大值的记录,首先使用子查询,查询出tb_row表中row字段的值,然后使用ALL关键字(“>=ALL”表示大于等于所有值)判断,查询语句如下:

    select books,row from tb_book where row>=ALL(select row from tb_row);
    
    • 1

    4.6 合并查询结果

    合并查询结果是将多个SELECT语句的查询结果合并到一起。因为某种情况下,需要将几个SELECT语句查询出来的结果合并起来显示。合并查询结果使用关键字UNION和UNION ALL。

    • 关键字UNION是将所有的查询结果合并到一起,然后去除相同记录;
    • 关键字UNION ALL则只是简单地将结果合并到一起。
    • UNION
    select user from tb_book
    UNION
    select user from tb_login;
    
    • 1
    • 2
    • 3
    • UNION ALL
    select user from tb_book
    UNION ALL
    select user from tb_login;
    
    • 1
    • 2
    • 3

    4.7 定义表和字段的别名

    查询时,可以为表和字段取一个别名,这个别名可以代替其指定的表和字段。为字段和表取别名,能够使查询更加方便,而且可以使查询结果以更加合理的方式显示。

    4.7.1 为表取别名

    当表的名称特别长时,在查询中直接使用表名很不方便,这时可以为表取一个贴切的别名。

    select * from tb_program p where p.talk='PHP';
    
    • 1

    4.7.2 为字段取别名

    当查询数据时,MySQL会显示每个输出列的名词。默认情况下,显示的列名是创建表时定义的列名。同样可以为这个列取一个别名。

    MySQL中为字段取别名的基本形式如下:

    字段名 [AS] 别名
    
    • 1

    示例:

    select section AS login_section,name AS login_name from tb_login;
    
    • 1

    4.8 使用正则表达式查询

    正则表达式是用某种模式去匹配一类字符串的一个方式。正则表达式的查询能力比通配字符的查询能力更强大,而且更加灵活。下面详细讲解如何使用正则表达式来查询。

    在MySQL中,使用关键字REGEXP来匹配查询正则表达式,其基本形式如下:

    字段名 REGEXP '匹配方式'
    
    • 1
    • (1)字段名:表示需要查询的字段名称。
    • (2)匹配方式:表示以哪种方式来进行匹配查询。

    支持的模式匹配字符如下:
    image.png

    这里的正则表达式与Java语言、PHP语言等编程语言中的正则表达式基本一致。

    4.8.1 匹配指定字符中的任意一个

    使用方括号([])可以将需要查询字符组成一个字符集。只要记录中包含方括号中的任意字符,该记录将会被查询出来。

    从info表name字段中查询包含c、e和o 3个字母中任意一个的记录

    SELECT * FROM info WHERE name REGEXP ‘[ceo]’;
    
    • 1

    4.8.2 使用“*”和“+”来匹配多个字符

    正则表达式中,“”和“+”都可以匹配多个该符号之前的字符。但是,“+”至少表示一个字符,而“”可以表示0个字符。

    从info表name字段中查询字母‘c’之前出现过‘a’的记录。

    SELECT * FROM info WHERE name REGEXP 'a*c';
    
    • 1

    4.8.3 匹配以指定的字符开头和结束的记录

    正则表达式中,^表示字符串的开始位置,$表示字符串的结束位置。下面将通过一个具体的实例演示如何匹配以指定的字符开头和结束的记录。

    在学生成绩信息表computer_stu中查找姓名(name)字段中以L开头、以y结束的,中间包含两个字符的学生的成绩信息

    SELECT * FROM computer_stu WHERE name REGEXP '^L..y$';
    
    • 1
  • 相关阅读:
    如何将安防视频监控系统/视频云存储EasyCVR平台推流到公网直播间?
    C#语言基础速成Day07
    一文了解io.ReadAtLeast函数
    async await使用同步方式写异步代码
    MySQL忘记登录密码解决
    Elasticsearch 8.X:这个复杂的检索需求如何实现?
    石油开采消防安全VR仿真培训加强学员的切身感受
    【收藏】如何用Python进行文本词云可视化
    C#语言编写的仅有8KB大小的简易贪吃蛇开源游戏
    嵌入式驱动学习第一周——内核的中断机制
  • 原文地址:https://blog.csdn.net/qui910/article/details/126247138