mysql操作符和常用函数

mysql操作符和常用函数

1.将所有的大写字母变换成小写字母

mysql> select lower ("aADsadAD");

+--------------------+

| lower ("aADsadAD") |

+--------------------+

| aadsadad           |

+--------------------+

1 row in set (0.05 sec)

2.将所有的小写字母变换成大写字母

select upper ("aADsadAD");

+--------------------+

| upper ("aADsadAD") |

+--------------------+

| AADSADAD           |

+--------------------+

1 row in set (0.00 sec)

3.操作符优先级

下图展示了所有操作符的执行优先级,从高到低,同一行中的操作符优先级相同,

相同优先级的情况下从左到右依次执行.

 

4.对比操作符 > < 和 = 都是一样的用法.

select * from students where sid > 3;

逐行判断当前的sid与3的大小.如如果小于3就忽略大于3就查出来.

​

区间between 操作符

select * from students where sid between 3 and 5;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   3 | Bob   | 1      |       1 | 1983-01-01 00:00:00 | NULL   | 66     |

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

3 rows in set (0.00 sec)

5.greatest ()某一个列表中返回一个最大的值

select greatest(1,12,3,44,55);

+------------------------+

| greatest(1,12,3,44,55) |

+------------------------+

|                     55 |

+------------------------+

1 row in set (0.00 sec)

6.in() 代表检查值是否是在一系列的数值中.如果在取值范围内就进行显示.不在取值范围内就不进行显示.

 select * from students where sid in (3,5,10);

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   3 | Bob   | 1      |       1 | 1983-01-01 00:00:00 | NULL   | 66     |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

2 rows in set (0.01 sec)

7.如果需要查表中的某个字段是否是null,可以是用 is 进行查看

select * from students where sname2 is null;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   3 | Bob   | 1      |       1 | 1983-01-01 00:00:00 | NULL   | 66     |

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

4 rows in set (0.00 sec)

8.同理 使用 is not 进行查看反向集合

select * from students where sname3 is not null;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   3 | Bob   | 1      |       1 | 1983-01-01 00:00:00 | NULL   | 66     |

+-----+-------+--------+---------+---------------------+--------+--------+

1 row in set (0.00 sec)

使用 is 过着 is not 一般都是用来进行字符串形式的比较查看.

9.用于int型数据的比较查看

select * from students where sid != 3;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

3 rows in set (0.00 sec)

其中 !=3 表示 sid不等于3的所有的数据集合;

mysql> select * from students where sid <> 3;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

3 rows in set (0.00 sec)

其中 sid<>3 表示 sid不等于3的所有的数据集合;

10.变量赋值,在进行变量赋值的时候不仅仅可以赋值数字.也可以赋值字符串.

使用select进行变量的复制

select @a:=1;

+-------+

| @a:=1 |

+-------+

|     1 |

+-------+

1 row in set, 1 warning (0.00 sec)

将@变量赋值成1;

mysql> select @a;

+------+

| @a   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

查看赋值变量

使用set 进行变量赋值

set @a = 2;mysql> set @a = 2;

Query OK, 0 rows affected (0.00 sec)

将变量@a赋值成数字2.

mysql> select @a;

+------+

| @a   |

+------+

|    2 |

+------+

1 row in set (0.00 sec)

查看变量结果