06.sql语句-运算符+排序+模糊匹配+聚合函数+子查询+连接查询+limit限定+范围运算+字符串函数

逻辑运算符

and（且）

select name,pace from info where pace=20 and name='python';

查找info表中pace=20并且name=python的数据，并显示其name和pace信息。

 

MariaDB [books]> select name,pace from info where pace=20 and name='python';

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

| name   | pace |

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

| python |   20 |

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

1 row in set (0.00 sec)

 

or（或）

select name,pace from info where pace=30 or pace=40 or pace=50;

查找info表中pace=30或者40或者50的数据，并显示其name和pace。

 

MariaDB [books]> select name,pace from info where pace=30 or pace=40 or pace=50;

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

| name    | pace |

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

| linux   |   50 |

| html    |   30 |

| mysql   |   40 |

| css     |   30 |

| js      |   30 |

| mariadb |   40 |

| redis   |   40 |

| mongodb |   30 |

| shell   |   40 |

| zabbex  |   40 |

| shell   |   40 |

| zabbex  |   40 |

| shell   |   40 |

| zabbex  |   40 |

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

14 rows in set (0.00 sec)

 

not（非）

select name,pace from info where not pace=60 and not pace=50;

查找info表中pace不是60和pace不是50的数据，并显示其name和pace。

 

MariaDB [books]> select name,pace from info where not pace=60 and not pace=50;

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

| name    | pace |

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

| python  |   20 |

| html    |   30 |

| mysql   |   40 |

| css     |   30 |

| js      |   30 |

| mariadb |   40 |

| redis   |   40 |

| pycharm |   20 |

| mongodb |   30 |

| shell   |   40 |

| zabbex  |   40 |

| shell   |   40 |

| zabbex  |   40 |

| shell   |   40 |

| zabbex  |   40 |

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

15 rows in set (0.00 sec)

 

算数运算符

=（等于）

select * from info where pace=20;

查找info表中pace=20的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace=20;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

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

2 rows in set (0.00 sec)

 

<>或!=（不等于）

select * from info where pace<>60;

查看info表中pace不等于60的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace<>60;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| linux   |   50 | linux ???  |

| html    |   30 | html???    |

| mysql   |   40 | mysql???   |

| css     |   30 | css???     |

| js      |   30 | js???      |

| mariadb |   40 | mariadb??? |

| redis   |   40 | redis???   |

| pycharm |   20 | pycharm??? |

| mongodb |   30 | mongodb??? |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

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

16 rows in set (0.00 sec)

 

select * from info where pace!=60;

查看info表中pace不等于60的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace!=60;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| linux   |   50 | linux ???  |

| html    |   30 | html???    |

| mysql   |   40 | mysql???   |

| css     |   30 | css???     |

| js      |   30 | js???      |

| mariadb |   40 | mariadb??? |

| redis   |   40 | redis???   |

| pycharm |   20 | pycharm??? |

| mongodb |   30 | mongodb??? |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

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

16 rows in set (0.00 sec)

 

>（大于）

select * from info where pace>50;

查看info表中pace>50的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace>50;

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

| name  | pace | address   |

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

| RHCSE |   60 | redhat??? |

| RHCE  |   60 | redhat??? |

| RHCA  |   60 | redhat??? |

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

3 rows in set (0.00 sec)

 

<（小于）

select * from info where pace<30;

查看info表中pace<30的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace<30;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

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

2 rows in set (0.00 sec)

 

>=（大于等于）

select * from info where pace>=50;

查看info表中pace>=50的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace>=50;

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

| name  | pace | address   |

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

| linux |   50 | linux ??? |

| RHCSE |   60 | redhat??? |

| RHCE  |   60 | redhat??? |

| RHCA  |   60 | redhat??? |

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

4 rows in set (0.00 sec)

 

<=（小于等于）

select * from info where pace<=30;

查看info表中pace<=30的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace<=30;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| html    |   30 | html???    |

| css     |   30 | css???     |

| js      |   30 | js???      |

| pycharm |   20 | pycharm??? |

| mongodb |   30 | mongodb??? |

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

6 rows in set (0.00 sec)

 

in

select * from info where pace in(20,30);

查看info表中pace在20到30之间的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace in(20,30);

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| html    |   30 | html???    |

| css     |   30 | css???     |

| js      |   30 | js???      |

| pycharm |   20 | pycharm??? |

| mongodb |   30 | mongodb??? |

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

6 rows in set (0.00 sec)

 

not in

select * from info where pace not in(30,40,50);

查看info表中pace不是30、40、50的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace not in(30,40,50);

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| RHCSE   |   60 | redhat???  |

| RHCE    |   60 | redhat???  |

| RHCA    |   60 | redhat???  |

| pycharm |   20 | pycharm??? |

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

5 rows in set (0.00 sec)

 

排序

升序

select * from info order by pace asc;

语法：select 字段名1,字段名2,…… order by 排序字段 asc;

 

MariaDB [books]> select * from info order by pace asc;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

| html    |   30 | html???    |

| css     |   30 | css???     |

| mongodb |   30 | mongodb??? |

| js      |   30 | js???      |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| redis   |   40 | redis???   |

| mariadb |   40 | mariadb??? |

| mysql   |   40 | mysql???   |

| linux   |   50 | linux ???  |

| RHCA    |   60 | redhat???  |

| RHCSE   |   60 | redhat???  |

| RHCE    |   60 | redhat???  |

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

19 rows in set (0.00 sec)

 

降序

select * from info order by pace desc;

语法：select 字段名1,字段名2,…… order by 排序字段 desc;

 

MariaDB [books]> select * from info order by pace desc;

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

| name    | pace | address    |

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

| RHCE    |   60 | redhat???  |

| RHCA    |   60 | redhat???  |

| RHCSE   |   60 | redhat???  |

| linux   |   50 | linux ???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| redis   |   40 | redis???   |

| mariadb |   40 | mariadb??? |

| mysql   |   40 | mysql???   |

| mongodb |   30 | mongodb??? |

| js      |   30 | js???      |

| css     |   30 | css???     |

| html    |   30 | html???    |

| pycharm |   20 | pycharm??? |

| python  |   20 | python???  |

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

19 rows in set (0.00 sec)

 

多字段排序

select * from info order by pace desc,name desc;

语法：select 字段1,字段2,…… from 表名 order by 排序字段1 desc,排序字段2 desc;

 

MariaDB [books]> select * from info order by pace desc,name desc;

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

| name    | pace | address    |

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

| RHCSE   |   60 | redhat???  |

| RHCE    |   60 | redhat???  |

| RHCA    |   60 | redhat???  |

| linux   |   50 | linux ???  |

| zabbex  |   40 | zabbex???  |

| zabbex  |   40 | zabbex???  |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| shell   |   40 | shell???   |

| shell   |   40 | shell???   |

| redis   |   40 | redis???   |

| mysql   |   40 | mysql???   |

| mariadb |   40 | mariadb??? |

| mongodb |   30 | mongodb??? |

| js      |   30 | js???      |

| html    |   30 | html???    |

| css     |   30 | css???     |

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

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

19 rows in set (0.00 sec)

 

select * from info order by pace asc,name asc;

语法：select 字段1,字段2,…… from 表名 order by 排序字段1 asc,排序字段2 asc;

 

MariaDB [books]> select * from info order by pace asc,name asc;  

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

| name    | pace | address    |

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

| pycharm |   20 | pycharm??? |

| python  |   20 | python???  |

| css     |   30 | css???     |

| html    |   30 | html???    |

| js      |   30 | js???      |

| mongodb |   30 | mongodb??? |

| mariadb |   40 | mariadb??? |

| mysql   |   40 | mysql???   |

| redis   |   40 | redis???   |

| shell   |   40 | shell???   |

| shell   |   40 | shell???   |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| zabbex  |   40 | zabbex???  |

| zabbex  |   40 | zabbex???  |

| linux   |   50 | linux ???  |

| RHCA    |   60 | redhat???  |

| RHCE    |   60 | redhat???  |

| RHCSE   |   60 | redhat???  |

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

19 rows in set (0.00 sec)

 

范围运算

between and

select * from info where pace between 20 and 30;

语法：select 字段名1,字段名2,…… from 表名 where 定位字段 between 值1 and 值2;

查看info表中pace在20到30之间的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace between 20 and 30;

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| html    |   30 | html???    |

| css     |   30 | css???     |

| js      |   30 | js???      |

| pycharm |   20 | pycharm??? |

| mongodb |   30 | mongodb??? |

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

6 rows in set (0.00 sec)

 

not between and

select * from info where pace not between 20 and 30

语法：select 字段名1,字段名2,…… from 表名 where 定位字段 not between 值1 and 值2;

查看info表中pace在20到30之间的数据，并显示其所有信息。

 

MariaDB [books]> select * from info where pace not between 20 and 30;

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

| name    | pace | address    |

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

| linux   |   50 | linux ???  |

| mysql   |   40 | mysql???   |

| mariadb |   40 | mariadb??? |

| redis   |   40 | redis???   |

| RHCSE   |   60 | redhat???  |

| RHCE    |   60 | redhat???  |

| RHCA    |   60 | redhat???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

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

13 rows in set (0.00 sec)

 

模糊匹配查询

%（匹配任意多个字符）含有（like）

select * from info where name like 'p%';

 

MariaDB [books]> select * from info where name like 'p%';

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

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

2 rows in set (0.00 sec)

 

select * from info where name like '%i%';

MariaDB [books]> select * from info where name like '%i%';

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

| name    | pace | address    |

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

| linux   |   50 | linux ???  |

| mariadb |   40 | mariadb??? |

| redis   |   40 | redis???   |

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

3 rows in set (0.00 sec)

 

%（匹配任意多个字符）不含有（not like）

select * from info where name not like '%R%';

 

MariaDB [books]> select * from info where name not like '%R%';

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| linux   |   50 | linux ???  |

| html    |   30 | html???    |

| mysql   |   40 | mysql???   |

| css     |   30 | css???     |

| js      |   30 | js???      |

| mongodb |   30 | mongodb??? |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

| shell   |   40 | shell???   |

| zabbex  |   40 | zabbex???  |

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

13 rows in set (0.00 sec)

 

MySQL子查询

什么是子查询，即在select的where条件中又出现了select查询语句，此称为子查询。（查询中嵌套这查询）

 

子查询语句查询的结果只能由一个，将这个值赋值给父查询，进行查询！！！

 

书写子查询时，建议先写子查询，然后将其语句添加到父查询中。

 

select * from info where pace=(select pace from info where name='python');

语法：select 字段名1,字段名2,…… from 表名 where 定位字段=(select 字段名 from 表名 where 定位字段名=定位字段值);

 

MariaDB [books]> select * from info where pace=(select pace from info where name='python');

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

| name    | pace | address    |

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

| python  |   20 | python???  |

| pycharm |   20 | pycharm??? |

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

2 rows in set (0.00 sec)

 

limit限定显示条目

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

 

 

 

 

连接查询

以一个共同的字段，求两张表当中符合条件的并集。通过共同字段把两张表连接起来。

 

内连接

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

交集！！！

 

 

语法：select 字段名1,字段名2,…… from 表名1 inner join 表名2 on 表名1.字段=表名2.字段;

 

select iname,uname from info inner join user on info.id=user.uid;

 

MariaDB [books]> select iname,uname from info inner join user on info.id=user.uid;

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

| iname   | uname        |

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

| python  | wangzhibin   |

| linux   | liyi         |

| html    | liuzhiwei    |

| mysql   | wangting     |

| css     | weifangchao  |

| js      | qiyongkang   |

| mariadb | azhen        |

| redis   | caomingming  |

| RHCSE   | guoshiying   |

| RHCE    | liuhongqian  |

| RHCA    | liuguohui    |

| pycharm | lifu         |

| mongodb | fanquanshuai |

| zabbex  | wangbin      |

| zabbex  | wangbin      |

| zabbex  | wangbin      |

| shell   | changmengka  |

| shell   | changmengka  |

| shell   | changmengka  |

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

19 rows in set (0.00 sec)

 

select iname,uname,age from info inner join user on info.id=user.uid where user.age>26;

 

MariaDB [books]> select iname,uname,age from info inner join user on info.id=user.uid where user.age>26;

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

| iname   | uname        | age  |

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

| linux   | liyi         |   27 |

| css     | weifangchao  |   27 |

| js      | qiyongkang   |   27 |

| mariadb | azhen        |   27 |

| RHCE    | liuhongqian  |   27 |

| RHCA    | liuguohui    |   28 |

| pycharm | lifu         |   27 |

| mongodb | fanquanshuai |   29 |

| zabbex  | wangbin      |   27 |

| zabbex  | wangbin      |   27 |

| zabbex  | wangbin      |   27 |

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

11 rows in set (0.00 sec)

 

左外连接

左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。

 

 

语法：select 字段名1,字段名2,…… from 表名1 left join 表名2 on 表名1.字段=表名2.字段;

 

表名1：是主表，都显示。

表名2：是从表

 

select iname,uname from info left join user on info.id=user.uid;

 

MariaDB [books]> select iname,uname from info left join user on info.id=user.uid;

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

| iname   | uname        |

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

| python  | wangzhibin   |

| linux   | liyi         |

| html    | liuzhiwei    |

| mysql   | wangting     |

| css     | weifangchao  |

| js      | qiyongkang   |

| mariadb | azhen        |

| redis   | caomingming  |

| RHCSE   | guoshiying   |

| RHCE    | liuhongqian  |

| RHCA    | liuguohui    |

| pycharm | lifu         |

| mongodb | fanquanshuai |

| zabbex  | wangbin      |

| zabbex  | wangbin      |

| zabbex  | wangbin      |

| shell   | changmengka  |

| shell   | changmengka  |

| shell   | changmengka  |

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

19 rows in set (0.00 sec)

 

select iname as '技能',uname as ‘姓名’,age from info left join user on info.id=user.uid where iname='shell';

 

MariaDB [books]> select iname as '技能',uname as ‘姓名’,age from info left join user on info.id=user.uid where iname='shell';    

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

| 技能   | ‘姓名’       | age  |

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

| shell  | changmengka  |   26 |

| shell  | changmengka  |   26 |

| shell  | changmengka  |   26 |

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

3 rows in set (0.00 sec)

 

右外连接

左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。

 

 

语法：select 字段名1,字段名2,…… from 表名1 left join 表名2 on 表名1.字段=表名2.字段;

 

表名1：是从表

表名2：是主表，都显示。

 

select iname as '技能',uname as ‘姓名’,age from info right join user on info.id=user.uid;

 

MariaDB [books]> select iname as '技能',uname as ‘姓名’,age from info right join user on info.id=user.uid;                   

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

| 技能    | ‘姓名’       | age  |

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

| python  | wangzhibin   |   26 |

| linux   | liyi         |   27 |

| html    | liuzhiwei    |   26 |

| mysql   | wangting     |   25 |

| css     | weifangchao  |   27 |

| js      | qiyongkang   |   27 |

| mariadb | azhen        |   27 |

| redis   | caomingming  |   24 |

| RHCSE   | guoshiying   |   25 |

| RHCE    | liuhongqian  |   27 |

| RHCA    | liuguohui    |   28 |

| pycharm | lifu         |   27 |

| mongodb | fanquanshuai |   29 |

| shell   | changmengka  |   26 |

| zabbex  | wangbin      |   27 |

| shell   | changmengka  |   26 |

| zabbex  | wangbin      |   27 |

| shell   | changmengka  |   26 |

| zabbex  | wangbin      |   27 |

| NULL    | guochangyu   |   27 |

| NULL    | masao        |   29 |

| NULL    | zhuyajie     |   27 |

| NULL    | wangbaolong  |   28 |

| NULL    | zhaoxinxin   |   28 |

| NULL    | guorang      |   27 |

| NULL    | guobinghui   |   24 |

| NULL    | weiyapeng    |   29 |

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

27 rows in set (0.00 sec)

 

select iname as '技能',uname as ‘姓名’,age from info right join user on info.id=user.uid where age >27;

 

MariaDB [books]> select iname as '技能',uname as ‘姓名’,age from info right join user on info.id=user.uid where age >27;

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

| 技能    | ‘姓名’       | age  |

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

| RHCA    | liuguohui    |   28 |

| mongodb | fanquanshuai |   29 |

| NULL    | masao        |   29 |

| NULL    | wangbaolong  |   28 |

| NULL    | zhaoxinxin   |   28 |

| NULL    | weiyapeng    |   29 |

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

6 rows in set (0.00 sec)

 

聚合函数

执行特定功能的代码块。

 

sum()（求和）

显示所有年龄的总和。

 

语法：select sum(字段名) from 表名;

select sum(age) from user;

 

MariaDB [books]> select sum(age) from user;

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

| sum(age) |

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

|      617 |

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

1 row in set (0.00 sec)

 

avg()（求平均值）

显示所有年龄的总和的平均值。

 

语法：select avg(字段名) from 表名;

 

select avg(age) from user;

 

MariaDB [books]> select avg(age) from user;  

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

| avg(age) |

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

|  26.8261 |

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

1 row in set (0.00 sec)

 

max()（最大值）

显示所有年龄的最大值。

 

语法：select max(字段名) from 表名;

 

select max(age) from user;

 

MariaDB [books]> select max(age) from user;  

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

| max(age) |

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

|       29 |

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

1 row in set (0.00 sec)

 

min()（最小值）

 

显示所有年龄的最小值。

 

语法：select min(字段名) from 表名;

 

select min(age) from user;

 

MariaDB [books]> select min(age) from user;        

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

| min(age) |

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

|       24 |

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

1 row in set (0.01 sec)

 

select count(age) from user;

 

统计列表中所有年龄的个数。

 

语法：select count(字段名) from 表名;

 

MariaDB [books]> select count(age) from user;  

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

| count(age) |

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

|         23 |

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

1 row in set (0.00 sec)

 

算数运算符

+（加）

update info set pace=pace+5 where pace<30;

 

MariaDB [books]> update info set pace=pace+5 where pace<30;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

-（减）

update info set pace=pace-10 where pace<30;

 

MariaDB [books]> update info set pace=pace-10 where pace<30;    

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

*（乘）

update info set pace=pace*10 where pace<30;

 

MariaDB [books]> update info set pace=pace*10 where pace<30;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

/（除）

update info set pace=pace/2 where pace>100;

 

MariaDB [books]> update info set pace=pace/2 where pace>100;

Query OK, 2 rows affected (0.01 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

字符串函数

substr()（截取字段）

语法：select substr(字段名,n,m) from 表名 where 定位字段名=定位字段值;

n：表示开始位置，从1开始的。

m：表示截取的长度。

select substr(iname,1,3) from info where id=1;

 

MariaDB [books]> select substr(iname,1,3) from info where id=1;

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

| substr(iname,1,3) |

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

| pyt               |

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

1 row in set (0.00 sec)

concat()（拼接字段）

语法：select concat(字段名,’字符串1’,’字符串2’,……) from 表名 where 定位字段名=定位字段值;

select concat(iname,'--','ni hao') from info;

 

MariaDB [books]> select concat(iname,'--','ni hao') from info;   

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

| concat(iname,'--','ni hao') |

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

| python--ni hao              |

| linux--ni hao               |

| html--ni hao                |

| mysql--ni hao               |

| css--ni hao                 |

| js--ni hao                  |

| mariadb--ni hao             |

| redis--ni hao               |

| RHCSE--ni hao               |

| RHCE--ni hao                |

| RHCA--ni hao                |

| pycharm--ni hao             |

| mongodb--ni hao             |

| shell--ni hao               |

| zabbex--ni hao              |

| shell--ni hao               |

| zabbex--ni hao              |

| shell--ni hao               |

| zabbex--ni hao              |

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

19 rows in set (0.01 sec)

 

select concat(iname,'--','ni hao') from info where pace<=30;

 

MariaDB [books]> select concat(iname,'--','ni hao') from info where pace<=30;

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

| concat(iname,'--','ni hao') |

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

| html--ni hao                |

| css--ni hao                 |

| js--ni hao                  |

| mongodb--ni hao             |

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

4 rows in set (0.00 sec)

 

upper()（转大写）

语法：select concat(字段名1,字段名2,……) from 表名 where 定位字段名=定位字段值;

 

select upper(iname) from info;

 

MariaDB [books]> select upper(iname) from info;

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

| upper(iname) |

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

| PYTHON       |

| LINUX        |

| HTML         |

| MYSQL        |

| CSS          |

| JS           |

| MARIADB      |

| REDIS        |

| RHCSE        |

| RHCE         |

| RHCA         |

| PYCHARM      |

| MONGODB      |

| SHELL        |

| ZABBEX       |

| SHELL        |

| ZABBEX       |

| SHELL        |

| ZABBEX       |

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

19 rows in set (0.00 sec)

 

select upper(iname) from info where pace<=30;

 

MariaDB [books]> select upper(iname) from info where pace<=30;

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

| upper(iname) |

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

| HTML         |

| CSS          |

| JS           |

| MONGODB      |

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

4 rows in set (0.00 sec)

 

lower()（转小写）

语法：select concat(字段名1,字段名2,……) from 表名 where 定位字段名=定位字段值;

 

select lower(iname) from info;

MariaDB [books]> select lower(iname) from info;             

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

| lower(iname) |

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

| python       |

| linux        |

| html         |

| mysql        |

| css          |

| js           |

| mariadb      |

| redis        |

| rhcse        |

| rhce         |

| rhca         |

| pycharm      |

| mongodb      |

| shell        |

| zabbex       |

| shell        |

| zabbex       |

| shell        |

| zabbex       |

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

19 rows in set (0.00 sec)

 

select lower(iname) from info where pace=60;

 

MariaDB [books]> select lower(iname) from info where pace=60;

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

| lower(iname) |

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

| rhcse        |

| rhce         |

| rhca         |

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

3 rows in set (0.00 sec)