第四章 SQL与关系数据库基本操作 - 数据库操作 4.2 - 《04735-数据库系统原理(MySQL)》

一、MySQL使用基础
二、MySQL中的SQL
1、常量

mysql> # 常量-字符串常量操作演示
mysql> select 'MySql';
+-------+
| MySql |
+-------+
| MySql |
+-------+
1 row in set (0.04 sec)
mysql> select N'MySql';
+-------+
| MySql |
+-------+
| MySql |
+-------+
1 row in set (0.04 sec)

mysql> # 常量-数值常量操作演示
mysql> select 24;
+----+
| 24 |
+----+
| 24 |
+----+
1 row in set (0.03 sec)

mysql> select 5.7;
+-----+
| 5.7 |
+-----+
| 5.7 |
+-----+
1 row in set (0.04 sec)

mysql> select 5.7e-2;
+--------+
| 5.7e-2 |
+--------+
|  0.057 |
+--------+
1 row in set (0.33 sec)

mysql> # 常量-十六进制常量的三种表示方法
mysql> select 0x4D7953514c;
+--------------+
| 0x4D7953514c |
+--------------+
| MySQL        |
+--------------+
1 row in set (2.45 sec)

mysql> select x'4D7953514c';
+---------------+
| x'4D7953514c' |
+---------------+
| MySQL         |
+---------------+
1 row in set (0.04 sec)

mysql> select X'4D7953514c';
+---------------+
| X'4D7953514c' |
+---------------+
| MySQL         |
+---------------+
1 row in set (0.03 sec)

# 推荐操作写法
mysql> select 0x4D7953514c;
+--------------+
| 0x4D7953514c |
+--------------+
| MySQL        |
+--------------+
1 row in set (0.04 sec)

mysql> # 日期时间常量操作演示
mysql> select '2020-8-24 11:54:43';
+--------------------+
| 2020-8-24 11:54:43 |
+--------------------+
| 2020-8-24 11:54:43 |
+--------------------+
1 row in set (0.04 sec)

mysql> # 位字段值(了解，不考)操作演示
mysql> select b'0';
+------+
| b'0' |
+------+
|      |
+------+
1 row in set (0.03 sec)

mysql> select b'1';
+------+
| b'1' |
+------+
|     |
+------+
1 row in set (0.04 sec)

mysql> select b'01';
+-------+
| b'01' |
+-------+
|      |
+-------+
1 row in set (0.04 sec)

mysql> # 布尔值操作演示
mysql> select false;
+-------+
| FALSE |
+-------+
|     0 |
+-------+
1 row in set (0.03 sec)

mysql> select True;
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

# NULL值操作演示
mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

2、变量

mysql> # 变量操作演示
mysql> set @a=8.012;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+-------+
| @a    |
+-------+
| 8.012 |
+-------+
1 row in set (0.00 sec)

mysql> select @a+1;
+----------------------------------+
| @a+1                             |
+----------------------------------+
| 9.012000000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select @b;
+------+
| @b   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> set @c;
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> select @c;
+------+
| @c   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

二、 MySQL中的SQL
3、运算符

mysql> # 算数运算符-求模/取余
mysql> select 8/3;
+--------+
| 8/3    |
+--------+
| 2.6667 |
+--------+
1 row in set (0.00 sec)

mysql> # 运算符-位运算符
mysql> # 5=101 、 1=001
mysql> select 5&1;
+-----+
| 5&1 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select 5|1;
+-----+
| 5|1 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> select 5^1;
+-----+
| 5^1 |
+-----+
|   4 |
+-----+
1 row in set (0.00 sec)

mysql> select ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

mysql> select 5>>1;
+------+
| 5>>1 |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select 5<<1;
+------+
| 5<<1 |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> # 运算符-比较运算符操作演示
mysql> select 5<>1 & 5!=1;
+-------------+
| 5<>1 & 5!=1 |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select (5<>1) & (5!=1);
+-----------------+
| (5<>1) & (5!=1) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> # 相等或都等于空
mysql> select @x<=>@y;
+---------+
| @x<=>@y |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> # 运算符-逻辑运算符
mysql> select NOT 5;
+-------+
| NOT 5 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 1 AND 5;
+---------+
| 1 AND 5 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 1 OR 5;
+--------+
| 1 OR 5 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 1 XOR 5;
+---------+
| 1 XOR 5 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

4、表达式
表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样，表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。这样，根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期表达式。

mysql> # 表达式操作演示
mysql> select 1+5;
+-----+
| 1+5 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)

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

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

mysql> select '2020-08-24 17:08:13'+1;
+-------------------------+
| '2020-08-24 17:08:13'+1 |
+-------------------------+
|                    2021 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

5、内置函数
数学函数：例如ABS()函数、SQRT()函数；
聚合函数：例如 COUNT()函数;
字符串函数：例如ASCI()函数、CHAR()函数;
日期和时间函数：例如NOW()函数、YEAR()函数;
加密函数：例如ENCODE()函数、 ENCRYPT()函数
控制流程函数：例如IF()函数、 IFNULL()函数;
格式化函数：例如FORMAT()函数;
类型转换函数，例如CAST()函数;
系统信息函数，例如USER()函数、VERSION()函数

mysql> # 内置函数
mysql> select ABS(-2);
+---------+
| ABS(-2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> select SQRT(4);
+---------+
| SQRT(4) |
+---------+
|       2 |
+---------+
1 row in set (0.01 sec)

mysql> select ASCII('A');
+------------+
| ASCII('A') |
+------------+
|         65 |
+------------+
1 row in set (0.00 sec)

mysql> select ASCII(A);
ERROR 1054 (42S22): Unknown column 'A' in 'field list'
mysql> select CHAR(65);
+----------+
| CHAR(65) |
+----------+
| A        |
+----------+
1 row in set (0.01 sec)

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-08-24 17:44:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> select YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)

mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            8 |
+--------------+
1 row in set (0.01 sec)

mysql> select DAY(NOW());
+------------+
| DAY(NOW()) |
+------------+
|         24 |
+------------+
1 row in set (0.00 sec)

mysql> select CAST(0x41);
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> select CAST(0x41 as unsigned);
+------------------------+
| CAST(0x41 as unsigned) |
+------------------------+
|                     65 |
+------------------------+
1 row in set (0.01 sec)

mysql> select USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)