Mysql 常见函数、information_schema元数据库、查询、练习

常见的运算符

• MySQL 提供了一些运算符号，可以在SQL 语句中使用，比如需要对SQL 语句中的某个值，或者某个字段做运算操作的时候，就可以使用这些运算符。

比较运算符

运算符	含义
=	字段等于固定的值
>
<
>=
<=
<> !=	不等于

• 查询出留言id 大于3 的所有留言。

  MariaDB [bbs]> select * from message where id > 3;
  +----+-----+-------+-------------------+
  | id | uid | title | content           |
  +----+-----+-------+-------------------+
  |  4 |   3 | Hello | My Name is admin! |
  +----+-----+-------+-------------------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]>
  

逻辑运算符

参与逻辑运算的操作数是布尔类型的值。

• 真，表示为 True 或1。
• 假，表示为 False 或0。

MariaDB [bbs]> select true;
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [bbs]> select false;
+-------+
| FALSE |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

MariaDB [bbs]>

• and 与运算：有假则假。

  and	True	False
  True	True and True = True	True and False = False
  False	False and True = False	False and False = False

• or 或运算：有真则真。

  or	True	False
  True	True	True
  False	True	False

• ! 非运算：真假取反。

  !	True	False
  –	False	True

• ^ 异或运算：异真同假。

  ^	True	False
  True	False	True
  False	True	False

• 查询出 id 在 2 到 4 之间的留言。

  MariaDB [bbs]> select * from message where id >2 and id < 4;
  +----+-----+-------+---------------------+
  | id | uid | title | content             |
  +----+-----+-------+---------------------+
  |  3 |   1 | ok!   | I'm fine!Thank you! |
  +----+-----+-------+---------------------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]>
  

• 逻辑运算对真假性的影响。

  语句	说明
  and 1=1 or 1=2	判断条件的真假性取决于and 之前语句真假性
  and 1=2	恒假
  or 1=1	恒真

  算数运算符

  运算符	含义
  +
  -
  *
  /

  MariaDB [bbs]> select 10/3=1+1;
  +----------+
  | 10/3=1+1 |
  +----------+
  |        0 |
  +----------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]>
  

  运算符优先级

  逻辑运算与 and 的优先级高于逻辑运算或 or。

  MariaDB [bbs]> select 1=1 or 1=2 and 1=2;
  +--------------------+
  | 1=1 or 1=2 and 1=2 |
  +--------------------+
  |                  1 |
  +--------------------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]> select 1=1 or (1=2 and 1=2);
  +----------------------+
  | 1=1 or (1=2 and 1=2) |
  +----------------------+
  |                    1 |
  +----------------------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]> select (1=1 or 1=2) and 1=2;
  +----------------------+
  | (1=1 or 1=2) and 1=2 |
  +----------------------+
  |                    0 |
  +----------------------+
  1 row in set (0.00 sec)
  
  MariaDB [bbs]>
  

元数据库

元数据

• 包括库名、表名、列名。
• Mysql 元数据存储在 information_schema

结构

information_schema
    |
    +-- schemata            # 所有的库名
    |   |
    |   `-- schema_name     # 库名
    |   
    +-- tables              # 存储了MySQL 数据库中所有的表
    |   |
    |   +-- table_name      # 表名
    |   |
    |   `-- table_schema    # 表名所属的库名
    |
    `-- columns             # 存储了MySQL 中所有的字段(列)
        |
        +-- column_name     # 字段名
        |
        +-- table_name      # 字段所属的表名
        |
        `-- table_schema    # 字段所属的库

information_schema 基本操作

• 查询所有库名

  select schema_name from information_schema.schemata;
  

• 查询所有的表名

  select table_name from information_schema.tables;
  

• 查看表名所在数据库

  select table_name from information_schema.tables where table_schema='bbs';
  

• 查询表中所有的列名

  select column_name from information_schema.columns
  

常见函数和语句

函数速查

函数名字	含义
concat(S1,S2,…Sn)	连接S1,S2,…Sn 为一个字符串
concat_ws()	含有分隔符的连接字符串，第一个参数是分隔符
group_concat()	连接一组字符串，使用逗号隔开
lower(str)	将字符串str 中所有字符变为小写
upper(str)	将字符串str 中所有字符变为大写
left(str,x)	返回字符串str 最左边的x 个字符
right(str,x)	返回字符串str 最右边的x 个字符
substring(str,x,y) substr(str,x,y)	返回从字符串str x 位置起y 个字符长度的字符串
length(str)	返回字符串长度
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
MD5(str)	返回字符串str 的MD5 值
sleep(x)	让数据库沉睡x 秒钟的时间。
ABS(x)	返回x的绝对值
FLOOR(x)	返回小于x 的最大整数值
RAND()	返回(0,1)内的随机值
ROUND(x,y)	返回参数x 的四舍五入，保留y 位小数
ascii(x) ord(x)	返回字母x 的ascii 码值
char(x)	返回数字x 对应的字母
hex(x)	计算数字x 的十六进制
IF(value,t,f)	如果value 是真，返回t,否则返回f

解释

• concat()

  无分隔符拼接字符串

  select username,password from user;
  
  select concat(username,0x3a,password) from user;
  # 在不允许使用引号的情况下 : 的 ASCII 码 
  

• contact_ws()

  有分隔符拼接字符串,第一个参数是分隔符

  select concat_ws(0x5e,username,password) from user;
  
  select concat_ws(0x5e,'a','b','c','d') from user;
  
  # 0x5e 为 ^ 的 ASCII 码
  

• group_concat()

  纵向连接（列）

  select group_concat(username) from user;
  

• left()

  left(字符串，截取位数)

  从字符串左侧开始截取字符

  select left("abcde",2);
  

• substr()

  substr("字符串",开始位置,字符串长度)

  从 1 开始而非 0

  select substr("abcdefgh",1,1);
  
  select substr("abcdefgh",2,3);
  

• length()

  字符串长度

• if()

  • 第一个参数，判断条件，返回值为true 或者false；
  • 如果判断条件为true，返回第二个参数；
  • 如果判断条件为false，返回第三个参数。

练习

• 进入元数据数据库

  use information_schema;
  

  

• 查看元数据库中所有表名

  show tables;
  

  

• 查询出所有的库名

  select schema_name from schemata; 
  

  

• 查询bbs 数据库中所有表名

  select table_name from information_schema.tables where table_schema='bbs';
  

  

• 查询bbs.message 表中所有列名

  select column_name from information_schema.columns where table_schema='bbs' and table_name='message';