数据库中表的基本操作都不会,还能干啥?

最新推荐文章于 2022-03-18 17:03:59 发布

CodeMartain

最新推荐文章于 2022-03-18 17:03:59 发布

阅读量85

点赞数

分类专栏： Database_All 文章标签： big data mysql 数据库

本文链接：https://blog.csdn.net/weixin_54061333/article/details/119989020

版权

Database_All 专栏收录该内容

62 篇文章 1 订阅

订阅专栏

数据表数据基本操作

加减乘除
比较运算
like与正则表达式
逻辑运算符
位运算符

加减乘除

mysql> create table test(
    -> a int,
    -> b double,
    -> c decimal(4,1));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test values(1,2,3);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |  3.0 |
+------+------+------+
1 row in set (0.00 sec)


mysql> select a ,a+1,a-1,a*2,a/0,a%2,a+1.6,a*5.9,a/3 from test;
+------+------+------+------+------+------+-------+-------+--------+
| a    | a+1  | a-1  | a*2  | a/0  | a%2  | a+1.6 | a*5.9 | a/3    |
+------+------+------+------+------+------+-------+-------+--------+
|    1 |    2 |    0 |    2 | NULL |    1 |   2.6 |   5.9 | 0.3333 |
+------+------+------+------+------+------+-------+-------+--------+
1 row in set (0.00 sec)

mysql> select b,b/2,b%2,b+5.6,b-0.4,b*2.3,b/3 from test;
+------+------+------+-------+-------+-------+--------------------+
| b    | b/2  | b%2  | b+5.6 | b-0.4 | b*2.3 | b/3                |
+------+------+------+-------+-------+-------+--------------------+
|    2 |    1 |    0 |   7.6 |   1.6 |   4.6 | 0.6666666666666666 |
+------+------+------+-------+-------+-------+--------------------+
1 row in set (0.00 sec)


mysql> select c ,c+1.8,c*2.9,c/0,c%0,c-1.623,c+5.123 from test;
+------+-------+-------+------+------+---------+---------+
| c    | c+1.8 | c*2.9 | c/0  | c%0  | c-1.623 | c+5.123 |
+------+-------+-------+------+------+---------+---------+
|  3.0 |   4.8 |  8.70 | NULL | NULL |   1.377 |   8.123 |
+------+-------+-------+------+------+---------+---------+
1 row in set (0.00 sec)

1,在数据库中一个数除以0 /模0,得到的值为null;
2,对于无法整除的数据,不同的数据类型/不同的精度要求保留的位数是不一样的,整型是保留小数点后4位,
3,浮点型在没有指定精度的情况下是保留小数点后16位;

比较运算

比较运算符如下表------
在这里插入图片描述


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

1,比较结果为true返回1,为false返回0
2,任何值跟NULL做等于比较,返回值为NULL,
3,NULL只有在跟NULL做安全等于的时候返回1;
4,等号不能用于判断空值 null.

比较符号----is null ,isnull 用于判断是否为空值,
比较符号-----is not null 用于判断是否为非空;

mysql> insert into test(a,c) values (6,8);
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |  3.0 |
|    6 | NULL |  8.0 |
+------+------+------+
2 rows in set (0.02 sec)

mysql> select * from test where b is null;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    6 | NULL |  8.0 |
+------+------+------+
1 row in set (0.00 sec)

mysql> select * from test where b=null;
Empty set (0.00 sec)

mysql> select * from test where b is null or c is not null;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |  3.0 |
|    6 | NULL |  8.0 |
+------+------+------+
2 rows in set (0.00 sec)

5,对于有字符串的参与的比较-------

当数字跟字符串比较时,会将字符串转换为数字进行比较;
当都为字符串时,按照字符串来进行比较;


mysql> select '12'=12,12>'8','9'='9','7'!='8';
+---------+--------+---------+----------+
| '12'=12 | 12>'8' | '9'='9' | '7'!='8' |
+---------+--------+---------+----------+
|       1 |      1 |       1 |        1 |
+---------+--------+---------+----------+
1 row in set (0.00 sec)

值1 between 值2 and 值 3
表达式的意思时----值1 是在值2和值3 之间吗?
即数学上的区间值1在[值2,值3]区间,true返回1,false返回0


mysql> select 1 between 2 and 3,6 between 5.1 and 7.2,8 between 7 and 8,7 between 7 and 8;
+-------------------+-----------------------+-------------------+-------------------+
| 1 between 2 and 3 | 6 between 5.1 and 7.2 | 8 between 7 and 8 | 7 between 7 and 8 |
+-------------------+-----------------------+-------------------+-------------------+
|                 0 |                     1 |                 1 |                 1 |
+-------------------+-----------------------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> select '1' between 2 and 3,6 between '5.1' and 7.2,'8' between '7' and '8','7' between '7' and 8;
+---------------------+-------------------------+-------------------------+-----------------------+
| '1' between 2 and 3 | 6 between '5.1' and 7.2 | '8' between '7' and '8' | '7' between '7' and 8 |
+---------------------+-------------------------+-------------------------+-----------------------+
|                   0 |                       1 |                       1 |                     1 |
+---------------------+-------------------------+-------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> select 'd' between 'f' and 'c', 'e' between 'a' and 'f','d' between 'c' and 'f';
+-------------------------+-------------------------+-------------------------+
| 'd' between 'f' and 'c' | 'e' between 'a' and 'f' | 'd' between 'c' and 'f' |
+-------------------------+-------------------------+-------------------------+
|                       0 |                       1 |                       1 |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

对于数字来说是按照数字大小进行比较的,对于字符串----字母来说也是按照字母顺序进行比较的,
两者都是从值2向后数的,
如果值3大于等于值2且区间范围内包含则返回1,否则返回0;
如果值3小于值2,则直接返回0;

mysql> select 3 between 3 and 2 ,3 between 2 and 3 ,'c' between 'a'and 'c','c' between 'c' and 'c','c' between 'c' and  'a' ,3 between 3 and 3;
+-------------------+-------------------+------------------------+-------------------------+--------------------------+-------------------+
| 3 between 3 and 2 | 3 between 2 and 3 | 'c' between 'a'and 'c' | 'c' between 'c' and 'c' | 'c' between 'c' and  'a' | 3 between 3 and 3 |
+-------------------+-------------------+------------------------+-------------------------+--------------------------+-------------------+
|                 0 |                 1 |                      1 |                       1 |                        0 |                 1 |
+-------------------+-------------------+------------------------+-------------------------+--------------------------+-------------------+
1 row in set (0.00 sec)

least 与greatest
least(值1,值2…值n) 返回当中最小值

greatest(值1,值2…值n) 返回当中最小值


mysql> select least(1,2,3,4,56,-12,0.2),greatest(1,2,3,4,65,-98,123.5),least(NULL,1,2,3,4,-23,89,65.2),greatest(NULL,1,2,3.4,67,-87.8);
+---------------------------+--------------------------------+---------------------------------+---------------------------------+
| least(1,2,3,4,56,-12,0.2) | greatest(1,2,3,4,65,-98,123.5) | least(NULL,1,2,3,4,-23,89,65.2) | greatest(NULL,1,2,3.4,67,-87.8) |
+---------------------------+--------------------------------+---------------------------------+---------------------------------+
|                     -12.0 |                          123.5 |                            NULL |                            NULL |
+---------------------------+--------------------------------+---------------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> select least('a','c','a','d'),greatest('a','c','a','d'),least('a','c','a','d',NULL),greatest('a','c','a','d',NULL);
+------------------------+---------------------------+-----------------------------+--------------------------------+
| least('a','c','a','d') | greatest('a','c','a','d') | least('a','c','a','d',NULL) | greatest('a','c','a','d',NULL) |
+------------------------+---------------------------+-----------------------------+--------------------------------+
| a                      | d                         | NULL                        | NULL                           |
+------------------------+---------------------------+-----------------------------+--------------------------------+
1 row in set (0.00 sec)

1,字符串是按照字母顺序进行比较的------底层ascii码
2,数字是按大小进行比较的
3,当值中有NULL时,返回NULL,而不是返回最大值或者最小值;
4,数字和字母比较是按照ASCII码表来进行大小比较的
5,对于数字和字母组合的比较没有实际意义不做讨论;

常见ASCII码的大小规则：0_9<AZ<a~z。

数字比字母要小。如 “7”<“F”；数字0比数字9要小，并按0到9顺序递增。如“3”<“8”。

字母A比字母Z要小，并按A到Z顺序递增。如“A”<“Z”；同个字母的大写字母比小写字母要小32。如“A”<“a”。

几个常见字母的ASCII码大小：“A”为65；“a”为97；“0”为 48。

IN 与NOT IN

IN运算符用来判断操作数是否为IN列表中的其中一个值：如果是，返回值为1；否则返回值为0。
NOT IN运算符用来判断表达式是否为IN列表中的其中一个值：如果不是，返回值为1；否则返回值为0


mysql> SELECT  's' in(1,2,3,4,'s'),NULL in('12','null',null,23,34,NULL),12 in(null,12,2,'a',NULL);
+---------------------+--------------------------------------+---------------------------+
| 's' in(1,2,3,4,'s') | NULL in('12','null',null,23,34,NULL) | 12 in(null,12,2,'a',NULL) |
+---------------------+--------------------------------------+---------------------------+
|                   1 |                                 NULL |                         1 |
+---------------------+--------------------------------------+---------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT  's' in(1,2,3,4,'s'),NULL in('12','null',null,23,34,NULL),12 in(null,2,'a',NULL);
+---------------------+--------------------------------------+------------------------+
| 's' in(1,2,3,4,'s') | NULL in('12','null',null,23,34,NULL) | 12 in(null,2,'a',NULL) |
+---------------------+--------------------------------------+------------------------+
|                   1 |                                 NULL |                   NULL |
+---------------------+--------------------------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

当左边为NULL 右边无论有什么值,返回结果为NULL
当左边值1不为NULL,右边的值(包含null)如果有值1,则返回1,否则返回null;

默认情况下mysql不区分大小写----

mysql> select 'a'='A',binary 'a'='A';
+---------+----------------+
| 'a'='A' | binary 'a'='A' |
+---------+----------------+
|       1 |              0 |
+---------+----------------+
1 row in set (0.00 sec)

like与正则表达式

LIKE

like的意思是像,就是长得像的东西来比较

其中会用到两个符号----
（1）‘%’，匹配任何数目的字符，甚至包括零字符。（2）‘_’，只能匹配一个字符。



mysql> select 'day' like 'day','day' like 'da_','today' like 't%' ,'today' like 't___','today' like 't____';
+------------------+------------------+-------------------+---------------------+----------------------+
| 'day' like 'day' | 'day' like 'da_' | 'today' like 't%' | 'today' like 't___' | 'today' like 't____' |
+------------------+------------------+-------------------+---------------------+----------------------+
|                1 |                1 |                 1 |                   0 |                    1 |
+------------------+------------------+-------------------+---------------------+----------------------+

正则 regexp
回想java中的正则-------

**REGEXP运算符在进行匹配时，常用的有下面几种通配符：
（1）‘^’匹配以该字符后面的字符开头的字符串。（2）‘$’匹配以该字符前面的字符结尾的字符串。


mysql> select 'helloword' regexp('^h'),'hello' regexp('o$');
+--------------------------+----------------------+
| 'helloword' regexp('^h') | 'hello' regexp('o$') |
+--------------------------+----------------------+
|                        1 |                    1 |
+--------------------------+----------------------+
1 row in set (0.00 sec)

（3）‘.’匹配任何一个单字符。
（4）“[…]”匹配在方括号内的任何字符。
例如，“[abc]”匹配“a”“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，
“[0-9]”匹配任何数量的数字，而“”匹配任何数量的任何字符。**

+--------------------------+--------------------------------+------------------------------------+
| 'hello' regexp ('hell.') | 'hello' regexp ('^[f-k]ello$') | '10086Hello' regexp('[0-9]*.ello') |
+--------------------------+--------------------------------+------------------------------------+
|                        1 |                              1 |                                  1 |
+--------------------------+--------------------------------+------------------------------------+
1 row in set (0.00 sec)

正则表达式是一个可以进行复杂查询的强大工具。相对于LIKE字符串匹配，它可以使用更多的通配符类型，查询结果更加灵活。

逻辑运算符

在这里插入图片描述
逻辑非 -----!或者NOT


mysql> select  not 0 ,not 1,not 10,not 'a', !0,!'a', !1,not null,!null;
+-------+-------+--------+---------+----+------+----+----------+-------+
| not 0 | not 1 | not 10 | not 'a' | !0 | !'a' | !1 | not null | !null |
+-------+-------+--------+---------+----+------+----+----------+-------+
|     1 |     0 |      0 |       1 |  1 |    1 |  0 |     NULL |  NULL |
+-------+-------+--------+---------+----+------+----+----------+-------+
1 row in set, 6 warnings (0.00 sec)

当为数字时,非0 返回1,0则返回1;
当为字符串时返回1;
当为null时返回null;


mysql> select !1+1,!(1+1);
+------+--------+
| !1+1 | !(1+1) |
+------+--------+
|    1 |      0 |
+------+--------+
1 row in set, 2 warnings (0.00 sec)

运算优先级— ! 大于算数运算符

逻辑与 -----and或者&&

mysql> select 1 and 0,'a' and 'b',null and 1,null and 0,0 and null, 1 and null, null and null,1 and 'a';
+---------+-------------+------------+------------+------------+------------+---------------+-----------+
| 1 and 0 | 'a' and 'b' | null and 1 | null and 0 | 0 and null | 1 and null | null and null | 1 and 'a' |
+---------+-------------+------------+------------+------------+------------+---------------+-----------+
|       0 |           0 |       NULL |          0 |          0 |       NULL |          NULL |         0 |
+---------+-------------+------------+------------+------------+------------+---------------+-----------+
1 row in set, 2 warnings (0.00 sec)

当为数时----
非零值并且不为NULL时，返回1；
当有0时(包含0 and null)，返回0；
其余情况返回值为NULL。
当有字符串时----
返回0;


mysql> select 0 and'a',null and 'a';
+----------+--------------+
| 0 and'a' | null and 'a' |
+----------+--------------+
|        0 |            0 |
+----------+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 0 and'a',null and 'a','a' and 'a';
+----------+--------------+-------------+
| 0 and'a' | null and 'a' | 'a' and 'a' |
+----------+--------------+-------------+
|        0 |            0 |           0 |
+----------+--------------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select '0' and'a',null and 'a','a' and 'a';
+------------+--------------+-------------+
| '0' and'a' | null and 'a' | 'a' and 'a' |
+------------+--------------+-------------+
|          0 |            0 |           0 |
+------------+--------------+-------------+
1 row in set, 2 warnings (0.00 sec)

逻辑或运算or


mysql> select 1 or 1,1  or 0 ,0 or 0,'a' or 'a',1 or 'a' ,0 or 'a' ,'1' or 0,null or null,1 or null,'0' or null,'1' or null;
+--------+---------+--------+------------+----------+----------+----------+--------------+-----------+-------------+-------------+
| 1 or 1 | 1  or 0 | 0 or 0 | 'a' or 'a' | 1 or 'a' | 0 or 'a' | '1' or 0 | null or null | 1 or null | '0' or null | '1' or null |
+--------+---------+--------+------------+----------+----------+----------+--------------+-----------+-------------+-------------+
|      1 |       1 |      0 |          0 |        1 |        0 |        1 |         NULL |         1 |        NULL |           1 |
+--------+---------+--------+------------+----------+----------+----------+--------------+-----------+-------------+-------------+
1 row in set, 3 warnings (0.00 sec)

有1 的时候返回1

逻辑异或运算符XOR

mysql> select 1 xor 1,1 xor 0,2 xor 2,0 xor 0, 'a' xor 'a','a' xor 'b', 1 xor null, 0 xor null, null xor null,1 xor 'a',0 xor 'a';
+---------+---------+---------+---------+-------------+-------------+------------+------------+---------------+-----------+-----------+
| 1 xor 1 | 1 xor 0 | 2 xor 2 | 0 xor 0 | 'a' xor 'a' | 'a' xor 'b' | 1 xor null | 0 xor null | null xor null | 1 xor 'a' | 0 xor 'a' |
+---------+---------+---------+---------+-------------+-------------+------------+------------+---------------+-----------+-----------+
|       0 |       1 |       0 |       0 |           0 |           0 |       NULL |       NULL |          NULL |         1 |         0 |
+---------+---------+---------+---------+-------------+-------------+------------+------------+---------------+-----------+-----------+
1 row in set, 6 warnings (0.00 sec)

1,如果有null则返回 null;
2,1 xor 0 返回1;
3,其他情况返回0

mysql> select  1 xor 2 ,!1 and 2,1 and !2;
+---------+----------+----------+
| 1 xor 2 | !1 and 2 | 1 and !2 |
+---------+----------+----------+
|       0 |        0 |        0 |
+---------+----------+----------+
1 row in set, 2 warnings (0.00 sec)

由于!的优先级时最高的,所以没加括号,不过最好是加括号以便于阅读;

a XOR b的计算等同于(a AND (NOT b))或者((NOT a)AND b)。

位运算符

在这里插入图片描述
位操作跟Java中是一样的,

mysql> select 1|2,2 & 3,4 ^ 5,6 << 7, 7 >> 8,~2;
+-----+-------+-------+--------+--------+----------------------+
| 1|2 | 2 & 3 | 4 ^ 5 | 6 << 7 | 7 >> 8 | ~2                   |
+-----+-------+-------+--------+--------+----------------------+
|   3 |     2 |     1 |    768 |      0 | 18446744073709551613 |
+-----+-------+-------+--------+--------+----------------------+
1 row in set (0.00 sec)

在这里插入图片描述

关于字符串的逻辑比较-----
虽然可以比较,但是当数字跟字符串比较时,会出现警告;


mysql> select 1 and 'a';
+-----------+
| 1 and 'a' |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

CodeMartain

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库中表的基本操作都不会,还能干啥?

数据表数据基本操作加减乘除比较运算like与正则表达式逻辑运算符位运算符加减乘除mysql> create table test( -> a int, -> b double, -> c decimal(4,1));Query OK, 0 rows affected (0.07 sec)mysql> insert into test values(1,2,3);Query OK, 1 row affected (0.03 sec)mysq
复制链接

扫一扫