mysql8学习笔记13--常用函数（一）
操作符优先级
• 下图展示了所有操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况下则从左到右执行
• 如果想改变优先级执行顺序，则可以使用括号
mysql> select 1+2*3; +-------+ | 1+2*3 | +-------+ | 7 | +-------+ 1 row in set (0.00 sec) mysql> select (1+1)*3; +---------+ | (1+1)*3 | +---------+ | 6 | +---------+ 1 row in set (0.00 sec) mysql>
对比操作符
• 对比操作符的执行结果为true,false,null三种
• Between A and B 代表检查值是否在A和B之间
• Coalesce()代表返回第一个非Null的值
• =代表相等操作符
• >代表大于操作符
• >=代表大于等于操作符
• Greatest()代表返回最大的值
• In()代表检查值是否在一系列的值之中
• Interval()代表返回比第一个参数小的参数的位置
• is/is not代表检查值是否与布尔值相同/不同
• Is not null代表检查值是否是非NULL
• Is null代表检查值是否是NULL
•isnull(exper) 判断exper是否为NULL，是则返回1，否则返回0
• ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替
• nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1
• Least()代表返回最小的参数
• <代表小于操作符
• <=代表小于等于操作符
• Like代表字符匹配
• Not between A and B代表检查值是否不在A和B的范围之内
• !=/<>代表不等于操作符
• Not in()代表检查值是否不在一系列值的当中
• Not like代表检查值是否不匹配
• Strcmp()对比两个字符串
mysql> select * from customers3; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from customers3 where cust_id between 10001 and 10003;#Between A and B 代表检查值是否在A和B之间 +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | +---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+ 3 rows in set (0.00 sec)
mysql> select coalesce(null,2,3,1);#Coalesce()代表返回第一个非Null的值 +----------------------+ | coalesce(null,2,3,1) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec) mysql> alter table customers3 add column cust_start varchar(20) default null; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from customers3 where cust_id between 10001 and 10004; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | cust_start | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | NULL | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | NULL | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ 4 rows in set (0.00 sec) mysql> select coalesce(cust_start,1) as cust_start from customers3;;#Coalesce()代表返回第一个非Null的值,这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数 +------------+ | cust_start | +------------+ | 1 | | 1 | | 1 | | 1 | | 1 | +------------+ 5 rows in set (0.00 sec) ERROR: No query specified mysql>
mysql> select * from customers3; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | cust_start | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | NULL | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | NULL | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | NULL | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select greatest(cust_id,cust_zip,cust_start) from customers3;#Greatest()代表返回最大的值,当字段有值是null时，返回null +---------------------------------------+ | greatest(cust_id,cust_zip,cust_start) | +---------------------------------------+ | NULL | | NULL | | NULL | | NULL | | NULL | +---------------------------------------+ 5 rows in set (0.00 sec) mysql> select greatest(cust_id,cust_zip) from customers3; +----------------------------+ | greatest(cust_id,cust_zip) | +----------------------------+ | 44444 | | 43333 | | 42222 | | 88888 | | 54545 | +----------------------------+ 5 rows in set (0.00 sec) mysql>
mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | tb_college | | tb_course | | tb_score | | tb_student | | tb_teacher | +------------------+ 5 rows in set (0.01 sec) mysql> select * from tb_student; +-------+--------------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+--------------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 2035 | 东方不败 | | 1988-06-30 | NULL | 2 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | | 3755 | 项少龙 | | 1993-01-25 | NULL | 3 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+--------------+--------+------------+--------------+--------+ 10 rows in set (0.01 sec) mysql> select * from tb_student where stuid between 1002 and 1378; +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | +-------+-----------+--------+------------+--------------+--------+ 3 rows in set (0.00 sec) mysql> select coalesce(null,2,3,1); +----------------------+ | coalesce(null,2,3,1) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec) mysql> select coalesce(stuaddr,2),stuid,stuaddr from tb_student where stuid=2035; #Coalesce()代表返回第一个非Null的值,这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数 +---------------------+-------+---------+ | coalesce(stuaddr,2) | stuid | stuaddr | +---------------------+-------+---------+ | 2 | 2035 | NULL | +---------------------+-------+---------+ 1 row in set (0.00 sec) mysql> mysql> select * from tb_student where stuid=1002; +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | +-------+-----------+--------+------------+--------------+--------+ 1 row in set (0.00 sec) mysql> select * from tb_student where stuid>=1002 and stuid<=1378; +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | +-------+-----------+--------+------------+--------------+--------+ 3 rows in set (0.00 sec) mysql> mysql> select * from tb_course; +-------+-----------------------+-----------+-------+ | couid | couname | coucredit | teaid | +-------+-----------------------+-----------+-------+ | 1111 | Python程序设计 | 3 | 1122 | | 2222 | Web前端开发 | 2 | 1122 | | 3333 | 操作系统 | 4 | 1122 | | 4444 | 计算机网络 | 2 | 1133 | | 5555 | 编译原理 | 4 | 1144 | | 6666 | 算法和数据结构 | 3 | 1144 | | 7777 | 经贸法语 | 3 | 2255 | | 8888 | 成本会计 | 2 | 3366 | | 9999 | 审计学 | 3 | 3366 | +-------+-----------------------+-----------+-------+ 9 rows in set (0.00 sec) mysql> desc tb_course; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | couid | int(11) | NO | PRI | NULL | | | couname | varchar(50) | NO | | NULL | | | coucredit | int(11) | NO | | NULL | | | teaid | int(11) | NO | MUL | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select greatest(couid,coucredit,teaid) from tb_course; #greatest是横向的，取每条记录的最大值。 +---------------------------------+ | greatest(couid,coucredit,teaid) | +---------------------------------+ | 1122 | | 2222 | | 3333 | | 4444 | | 5555 | | 6666 | | 7777 | | 8888 | | 9999 | +---------------------------------+ 9 rows in set (0.01 sec) mysql> mysql> select max(couid) from tb_course; #max是纵向的 +------------+ | max(couid) | +------------+ | 9999 | +------------+ 1 row in set (0.00 sec) mysql> mysql> select * from tb_student where stuid in (1001,3923); #In()代表检查值是否在一系列的值之中 +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+-----------+--------+------------+--------------+--------+ 2 rows in set (0.00 sec) mysql> select * from tb_student where stuid in (1001); +-------+---------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+---------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | +-------+---------+--------+------------+--------------+--------+ 1 row in set (0.00 sec) mysql> select * from tb_student where stuid in ();# ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 mysql> mysql> select interval(5,1,2,3,4,5,6,7);#INTERVAL()函数进行比较列表(N，N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。 +---------------------------+ | interval(5,1,2,3,4,5,6,7) | +---------------------------+ | 5 | +---------------------------+ 1 row in set (0.00 sec) mysql> select interval(null,1,2,3,4,5,6,7); +------------------------------+ | interval(null,1,2,3,4,5,6,7) | +------------------------------+ | -1 | +------------------------------+ 1 row in set (0.00 sec) mysql> select interval(1,1,2,3,4,5,6,7); +---------------------------+ | interval(1,1,2,3,4,5,6,7) | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) mysql> select interval(0,1,2,3,4,5,6,7); +---------------------------+ | interval(0,1,2,3,4,5,6,7) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> select * from tb_student where stuaddr is true;#代表检查值是否与布尔值相同/不同 Empty set, 8 warnings (0.00 sec) mysql> select * from tb_student where stuaddr is not true; +-------+--------------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+--------------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 2035 | 东方不败 | | 1988-06-30 | NULL | 2 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | | 3755 | 项少龙 | | 1993-01-25 | NULL | 3 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+--------------+--------+------------+--------------+--------+ 10 rows in set, 8 warnings (0.00 sec) mysql> select * from tb_student where stuaddr = NULL; Empty set (0.00 sec) mysql> select * from tb_student where stuaddr is NULL;#Is null代表检查值是否是NULL +-------+--------------+--------+------------+---------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+--------------+--------+------------+---------+--------+ | 2035 | 东方不败 | | 1988-06-30 | NULL | 2 | | 3755 | 项少龙 | | 1993-01-25 | NULL | 3 | +-------+--------------+--------+------------+---------+--------+ 2 rows in set (0.00 sec) mysql> mysql> select * from tb_student where stuaddr is not NULL; #Is not null代表检查值是否是非NULL +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+-----------+--------+------------+--------------+--------+ 8 rows in set (0.00 sec) mysql> mysql> select * from tb_course; +-------+-----------------------+-----------+-------+ | couid | couname | coucredit | teaid | +-------+-----------------------+-----------+-------+ | 1111 | Python程序设计 | 3 | 1122 | | 2222 | Web前端开发 | 2 | 1122 | | 3333 | 操作系统 | 4 | 1122 | | 4444 | 计算机网络 | 2 | 1133 | | 5555 | 编译原理 | 4 | 1144 | | 6666 | 算法和数据结构 | 3 | 1144 | | 7777 | 经贸法语 | 3 | 2255 | | 8888 | 成本会计 | 2 | 3366 | | 9999 | 审计学 | 3 | 3366 | +-------+-----------------------+-----------+-------+ 9 rows in set (0.00 sec) #isnull() mysql> select concat('该专业存在，可以选择') as resp_msg,isnull(t.couname) as result,t.couid from tb_course t where t.couname='Python程序设计';#这个例子举的有点不实用，生产会有用到的场景。 +--------------------------------+--------+-------+ | resp_msg | result | couid | +--------------------------------+--------+-------+ | 该专业存在，可以选择 | 0 | 1111 | +--------------------------------+--------+-------+ 1 row in set (0.00 sec) mysql> select isnull(1);#isnull(exper) 判断exper是否为NULL，是则返回1，否则返回0 +-----------+ | isnull(1) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select isnull(null); +--------------+ | isnull(null) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> select ifnull(null,3);# ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替 +----------------+ | ifnull(null,3) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) mysql> select ifnull(2,3); +-------------+ | ifnull(2,3) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> select nullif(2,2);# nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1 +-------------+ | nullif(2,2) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select nullif(2,5); +-------------+ | nullif(2,5) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> select least(5,2,3,4);#Least()代表返回最小的参数 +----------------+ | least(5,2,3,4) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec) mysql> select least(stuid,couid,scmark) from tb_score where scid=10; +---------------------------+ | least(stuid,couid,scmark) | +---------------------------+ | 82.5 | +---------------------------+ 1 row in set (0.00 sec) mysql> select stuid,couid,scmark from tb_score where scid=10; +-------+-------+--------+ | stuid | couid | scmark | +-------+-------+--------+ | 1033 | 5555 | 82.5 | +-------+-------+--------+ 1 row in set (0.00 sec) mysql> mysql> select t.* from tb_score t where t.scmark not between 95.0 and 78.0; #Not between A and B代表检查值是否不在A和B的范围之内 区间不要写反了 +------+-------+-------+---------------------+--------+ | scid | stuid | couid | scdate | scmark | +------+-------+-------+---------------------+--------+ | 1 | 1001 | 1111 | 2017-09-01 00:00:00 | 95.0 | | 2 | 1001 | 2222 | 2017-09-01 00:00:00 | 87.5 | | 3 | 1001 | 3333 | 2017-09-01 00:00:00 | 100.0 | | 5 | 1001 | 6666 | 2017-09-02 00:00:00 | 100.0 | | 6 | 1002 | 1111 | 2017-09-03 00:00:00 | 65.0 | | 7 | 1002 | 5555 | 2017-09-01 00:00:00 | 42.0 | | 8 | 1033 | 1111 | 2017-09-03 00:00:00 | 92.5 | | 9 | 1033 | 4444 | 2017-09-01 00:00:00 | 78.0 | | 10 | 1033 | 5555 | 2017-09-01 00:00:00 | 82.5 | | 11 | 1572 | 1111 | 2017-09-02 00:00:00 | 78.0 | | 12 | 1378 | 1111 | 2017-09-05 00:00:00 | 82.0 | | 13 | 1378 | 7777 | 2017-09-02 00:00:00 | 65.5 | | 14 | 2035 | 7777 | 2018-09-03 00:00:00 | 88.0 | | 18 | 3755 | 9999 | 2017-09-01 00:00:00 | 92.0 | +------+-------+-------+---------------------+--------+ 14 rows in set (0.00 sec) mysql> select t.* from tb_score t where t.scmark not between 78.0 and 95.0; +------+-------+-------+---------------------+--------+ | scid | stuid | couid | scdate | scmark | +------+-------+-------+---------------------+--------+ | 3 | 1001 | 3333 | 2017-09-01 00:00:00 | 100.0 | | 5 | 1001 | 6666 | 2017-09-02 00:00:00 | 100.0 | | 6 | 1002 | 1111 | 2017-09-03 00:00:00 | 65.0 | | 7 | 1002 | 5555 | 2017-09-01 00:00:00 | 42.0 | | 13 | 1378 | 7777 | 2017-09-02 00:00:00 | 65.5 | +------+-------+-------+---------------------+--------+ 5 rows in set (0.00 sec) mysql> select t.* from tb_score t ; +------+-------+-------+---------------------+--------+ | scid | stuid | couid | scdate | scmark | +------+-------+-------+---------------------+--------+ | 1 | 1001 | 1111 | 2017-09-01 00:00:00 | 95.0 | | 2 | 1001 | 2222 | 2017-09-01 00:00:00 | 87.5 | | 3 | 1001 | 3333 | 2017-09-01 00:00:00 | 100.0 | | 4 | 1001 | 4444 | 2018-09-03 00:00:00 | NULL | | 5 | 1001 | 6666 | 2017-09-02 00:00:00 | 100.0 | | 6 | 1002 | 1111 | 2017-09-03 00:00:00 | 65.0 | | 7 | 1002 | 5555 | 2017-09-01 00:00:00 | 42.0 | | 8 | 1033 | 1111 | 2017-09-03 00:00:00 | 92.5 | | 9 | 1033 | 4444 | 2017-09-01 00:00:00 | 78.0 | | 10 | 1033 | 5555 | 2017-09-01 00:00:00 | 82.5 | | 11 | 1572 | 1111 | 2017-09-02 00:00:00 | 78.0 | | 12 | 1378 | 1111 | 2017-09-05 00:00:00 | 82.0 | | 13 | 1378 | 7777 | 2017-09-02 00:00:00 | 65.5 | | 14 | 2035 | 7777 | 2018-09-03 00:00:00 | 88.0 | | 15 | 2035 | 9999 | 2020-08-01 00:00:00 | NULL | | 16 | 3755 | 1111 | 2020-08-01 00:00:00 | NULL | | 17 | 3755 | 8888 | 2020-08-01 00:00:00 | NULL | | 18 | 3755 | 9999 | 2017-09-01 00:00:00 | 92.0 | +------+-------+-------+---------------------+--------+ 18 rows in set (0.00 sec) mysql> mysql> select * from tb_student where stuaddr like '四川%';#Like代表字符匹配 +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+-----------+--------+------------+--------------+--------+ 4 rows in set (0.00 sec) mysql> mysql> select * from tb_student where stuaddr <> '四川成都'; +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | +-------+-----------+--------+------------+--------------+--------+ 5 rows in set (0.00 sec) mysql> select * from tb_student where stuaddr != '四川成都'; +-------+-----------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+-----------+--------+------------+--------------+--------+ | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | +-------+-----------+--------+------------+--------------+--------+ 5 rows in set (0.00 sec)
• =号对比操作符
• mysql> SELECT 1 = 0;
• -> 0
• mysql> SELECT '0' = 0;
• -> 1
• mysql> SELECT '0.0' = 0;
• -> 1
• mysql> SELECT '0.01' = 0;
• -> 0
• mysql> SELECT '.01' = 0.01;
• -> 1
• <>/!=号对比操作符
• mysql> SELECT '.01' <> '0.01';
• -> 1
• mysql> SELECT .01 <> '0.01';
• -> 0
• mysql> SELECT 'zapp' <> 'zappp';
• -> 1
• <=小于等于对比操作符
• mysql> SELECT 0.1 <= 2;
• -> 1
• <小于对比操作符
• mysql> SELECT 2 < 2;
• -> 0
• >=大于等于对比操作符
• mysql> SELECT 2 >= 2;
• -> 1
• >大于对比操作符
• mysql> SELECT 2 > 2;
• -> 0
• is操作符
• mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
• -> 1, 1, 1
• Is not操作符
• mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS
NOT UNKNOWN;
• -> 1, 1, 0
• Is null对比操作符
• mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
• -> 0, 0, 1
• Is not null对比操作符
• mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
• -> 1, 1, 0
• Expr between min and max对比操作符
• 相当于min <= expr AND expr <= max
• mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
• -> 1, 0
• mysql> SELECT 1 BETWEEN 2 AND 3;
• -> 0
• mysql> SELECT 'b' BETWEEN 'a' AND 'c';
• -> 1
• mysql> SELECT 2 BETWEEN 2 AND '3';
• -> 1
• mysql> SELECT 2 BETWEEN 2 AND 'x-3';
• -> 0
• expr IN (value,...)对比操作符
• 当expr值能在values中找到，则返回1，否则返回0
• mysql> SELECT 2 IN (0,3,5,7);
• -> 0
• mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
• -> 1
• mysql> SELECT (3,4) IN ((1,2), (3,4));
• -> 1
• mysql> SELECT (3,4) IN ((1,2), (3,5));
• -> 0
• expr NOT IN (value,...)对比操作符
• Isnull(expr)操作符
• 如果expr是null，则返回1，否则返回0
• mysql> SELECT ISNULL(1+1);
• -> 0
• mysql> SELECT ISNULL(1/0);
• -> 1
• LEAST(value1,value2,...)
• 返回最小值，如果其中有值为null，则返回null
• mysql> SELECT LEAST(2,0);
• -> 0
• mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
• -> 3.0
• mysql> SELECT LEAST('B','A','C');
• -> 'A'
逻辑操作符
• 逻辑操作符返回1 (TRUE), 0 (FALSE), 或者NULL
• NOT, !逻辑操作符代表非操作
• And,&&逻辑操作符
mysql> select not 10,! 10,not 0; +--------+------+-------+ | not 10 | ! 10 | not 0 | +--------+------+-------+ | 0 | 0 | 1 | +--------+------+-------+ 1 row in set (0.00 sec) mysql> select 1 and 0,1 and 1,null and 0,null and 1; +---------+---------+------------+------------+ | 1 and 0 | 1 and 1 | null and 0 | null and 1 | +---------+---------+------------+------------+ | 0 | 1 | 0 | NULL | +---------+---------+------------+------------+ 1 row in set (0.00 sec) mysql>
• Or, ||逻辑操作符
mysql> select 1 or 0,1 or 1,0 or 0,0 or null,1 or null; +--------+--------+--------+-----------+-----------+ | 1 or 0 | 1 or 1 | 0 or 0 | 0 or null | 1 or null | +--------+--------+--------+-----------+-----------+ | 1 | 1 | 0 | NULL | 1 | +--------+--------+--------+-----------+-----------+ 1 row in set (0.00 sec) mysql>
• Xor逻辑操作符 “XOR”表示逻辑异或,当任意一个操作数为NULL时,返回值为NULL，对于非NULL的操作数,如果两个的逻辑真假值相异，则返回结果为1，否则为0,就是两个不能同时成立,也不能同时不成立,只成立其中一个条件
mysql> select 1 xor 0,1 xor 1,0 xor 0,null xor 0; +---------+---------+---------+------------+ | 1 xor 0 | 1 xor 1 | 0 xor 0 | null xor 0 | +---------+---------+---------+------------+ | 1 | 0 | 0 | NULL | +---------+---------+---------+------------+ 1 row in set (0.00 sec) mysql>
• 分配操作符是指赋值操作
• mysql> SELECT @var1, @var2;
• -> NULL, NULL
• mysql> SELECT @var1 := 1, @var2;
• -> 1, NULL
• mysql> SELECT @var1, @var2;
• -> 1, NULL
• mysql> SELECT @var1, @var2 := @var1;
• -> 1, 1
• mysql> SELECT @var1, @var2;
• -> 1, 1
• mysql> SELECT @var1:=COUNT(*) FROM t1;
• -> 4
• mysql> SELECT @var1;
• -> 4
mysql> select @a1,@a2; +------+------+ | @a1 | @a2 | +------+------+ | NULL | NULL | +------+------+ 1 row in set (0.00 sec) mysql> select @a1:='ABCabc',@a2; +---------------+------+ | @a1:='ABCabc' | @a2 | +---------------+------+ | ABCabc | NULL | +---------------+------+ 1 row in set, 1 warning (0.00 sec) mysql> select @a1,@a2; +--------+------+ | @a1 | @a2 | +--------+------+ | ABCabc | NULL | +--------+------+ 1 row in set (0.00 sec) mysql> set @a2='DEF'; Query OK, 0 rows affected (0.00 sec) mysql> select @a1,@a2; +--------+------+ | @a1 | @a2 | +--------+------+ | ABCabc | DEF | +--------+------+ 1 row in set (0.00 sec) mysql
• :=操作符也可以用在update等语句
• mysql> SELECT @var1;
• -> 4
• mysql> SELECT * FROM t1;
• -> 1, 3, 5, 7
• mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
• Query OK, 1 row affected (0.00 sec)
• Rows matched: 1 Changed: 1 Warnings: 0
• mysql> SELECT @var1;
• -> 1
• mysql> SELECT * FROM t1;
• -> 2, 3, 5, 7
mysql> select @a1; +--------+ | @a1 | +--------+ | ABCabc | +--------+ 1 row in set (0.00 sec) mysql> select * from tb_student; +-------+--------------+--------+------------+--------------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+--------------+--------+------------+--------------+--------+ | 1001 | 杨逍 | | 1990-03-04 | 四川成都 | 1 | | 1002 | 任我行 | | 1992-02-02 | 湖南长沙 | 1 | | 1033 | 王语嫣 | | 1989-12-03 | 四川成都 | 1 | | 1378 | 纪嫣然 | | 1995-08-12 | 四川绵阳 | 1 | | 1572 | 岳不群 | | 1993-07-19 | 陕西咸阳 | 1 | | 1954 | 林平之 | | 1994-09-20 | 福建莆田 | 1 | | 2035 | 东方不败 | | 1988-06-30 | NULL | 2 | | 3011 | 林震南 | | 1985-12-12 | 福建莆田 | 3 | | 3755 | 项少龙 | | 1993-01-25 | NULL | 3 | | 3923 | 杨不悔 | | 1985-04-17 | 四川成都 | 3 | +-------+--------------+--------+------------+--------------+--------+ 10 rows in set (0.00 sec) mysql> update tb_student set stuaddr=@a1 where stuid=1001; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_student where stuid=1001; +-------+---------+--------+------------+---------+--------+ | stuid | stuname | stusex | stubirth | stuaddr | collid | +-------+---------+--------+------------+---------+--------+ | 1001 | 杨逍 | | 1990-03-04 | ABCabc | 1 | +-------+---------+--------+------------+---------+--------+ 1 row in set (0.00 sec) mysql>
• =操作符在两种情况下会被认为是赋值操作，而其他情况下会认
为是对比操作符
• 在set语句中，=操作符会被认为是赋值操作
• mysql> set @a=1;
• mysql> select @a;
• +------+
• | @a |
• +------+
• | 1 |
• 在update语句中的set子句中，=操作符会被认为是赋值操作
