SQL|MySQL数据库编程01 sql|数据库|mysql

文章目录

MySQL编程基础
- 常量与变量
- - 常量
  - - 字符串常量
    - 数值常量
    - 日期和时间常量
    - 布尔值常量
    - NULL值
  - 变量
  - - 局部变量
    - 全局变量
- 常用系统函数
- - 字符串函数
  - - 计算字符串字符数和长度
    - 合并字符串
    - 字符串大小写转换
    - 删除空格
    - 取子串
  - 数学函数
  - 日期和时间函数
  - - 获取当前系统的日期及取日期的年月日
    - 获取当前系统的日期时间
  - 系统信息函数
  - 条件控制函数
  - - IF
    - CASE
  - 数据类型转换函数

MySQL编程基础常量与变量常量
字符串常量字符串常量指用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号。

mysql> SELECT * FROM products WHERE prod_id='BR01'; +----+---------+-------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+-------------------+------------+ |4 | BR01| 8 inch teddy bear |5.99 | +----+---------+-------------------+------------+ 1 row in set (0.00 sec)考虑下面的SQL命令为什么没有输出mysql> SELECT * FROM products WHERE 'prod_id'='BR01'; Empty set (0.04 sec)

数值常量数值常量可以分为整数常量和小数常量

将产品编号为BR03的产品价格改为1250（用科学计数法表示） mysql> UPDATE products SET prod_price=1.25E+3 WHERE prod_id='BR03'; Query OK, 1 row affected (0.14 sec) Rows matched: 1Changed: 1Warnings: 0mysql> SELECT * FROM products WHERE prod_id='BR03'; +----+---------+--------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+--------------------+------------+ |6 | BR03| 18 inch teddy bear |1250.00 | +----+---------+--------------------+------------+ 1 row in set (0.00 sec)

日期和时间常量新增一列为产品生产日期，默认值为’2022-04-02 12:00:00’

文章图片

对数值稍作修改

文章图片

查询生产日期大于’2022-04-02 13:30:00’的产品

mysql> SELECT * FROM products WHERE prod_date>'2022-04-02 13:30:00'; +----+---------+-------------+------------+---------------------+ | id | prod_id | prod_name| prod_price | prod_date| +----+---------+-------------+------------+---------------------+ |8 | RYL01| King doll|9.99 | 2022-04-03 12:00:00 | |9 | RYL02| Queen dool|9.99 | 2022-04-03 12:00:00 | | 19 | MR01| toy dog|5.00 | 2022-04-03 12:00:00 | | 23 | MR02| toy dog|2.00 | 2022-04-03 12:00:00 | +----+---------+-------------+------------+---------------------+ 4 rows in set (0.00 sec)

布尔值常量查询产品价格是否大于7的返回结果

mysql> SELECT prod_name,prod_price>7 FROM products; +---------------------+--------------+ | prod_name| prod_price>7 | +---------------------+--------------+ | Fish bean bag toy|0 | | Bird bean bag toy|0 | | Rabbit bean bag toy |0 | | 8 inch teddy bear|0 | | 12 inch teddy bear|1 | | 18 inch teddy bear|1 | | Raggedy Ann|0 | | King doll|1 | | Queen dool|1 | | toy dog|0 | | toy dog|0 | +---------------------+--------------+ 11 rows in set (0.00 sec)

NULL值新增一行产品价格为空的信息

mysql> SELECT * FROM products WHERE prod_id='MR03'; +----+---------+-----------+------------+---------------------+ | id | prod_id | prod_name | prod_price | prod_date| +----+---------+-----------+------------+---------------------+ | 12 | MR03| NULL|NULL | 2022-04-02 12:00:00 | +----+---------+-----------+------------+---------------------+ 1 row in set (0.00 sec)

给为NULL的产品价格加10会返回什么？
给为1250的产品价格加NULL会返回什么？

mysql> UPDATE products SET prod_price=prod_price+10 WHERE prod_id='MR03'; Query OK, 0 rows affected (0.04 sec) Rows matched: 1Changed: 0Warnings: 0mysql> SELECT * FROM products WHERE prod_id='MR03'; +----+---------+-----------+------------+---------------------+ | id | prod_id | prod_name | prod_price | prod_date| +----+---------+-----------+------------+---------------------+ | 12 | MR03| NULL|NULL | 2022-04-02 12:00:00 | +----+---------+-----------+------------+---------------------+ 1 row in set (0.00 sec)mysql> UPDATE products SET prod_price=prod_price+NULL WHERE prod_price=1250; Query OK, 1 row affected (0.07 sec) Rows matched: 1Changed: 1Warnings: 0mysql> SELECT * FROM products WHERE prod_id='BR03'; +----+---------+--------------------+------------+---------------------+ | id | prod_id | prod_name| prod_price | prod_date| +----+---------+--------------------+------------+---------------------+ |6 | BR03| 18 inch teddy bear |NULL | 2022-04-02 12:00:00 | +----+---------+--------------------+------------+---------------------+ 1 row in set (0.00 sec)

可以看到NULL+10=NULL; 1250+NULL=NULL;
变量
局部变量查询产品编号为BR01的产品价格并赋值给变量price

mysql> SET @price=(SELECT prod_price FROM products WHERE prod_id='BR01'); Query OK, 0 rows affected (0.04 sec)mysql> SELECT @price; +--------+ | @price | +--------+ |5.99 | +--------+ 1 row in set (0.03 sec)

查询产品编号为BR02的产品名称和产品价格并分别赋值给name_v和price_v

mysql> SELECT prod_name,prod_price INTO @name_v,@price_v FROM products WHERE prod_id='BR02'; Query OK, 1 row affected (0.00 sec)mysql> SELECT @name_v,@price_v; +--------------------+----------+ | @name_v| @price_v | +--------------------+----------+ | 12 inch teddy bear |8.99 | +--------------------+----------+ 1 row in set (0.00 sec)

根据变量所给的值查询指定产品的信息

mysql> SET @p_id='BR03'; Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM products WHERE prod_id=@p_id; +----+---------+--------------------+------------+---------------------+ | id | prod_id | prod_name| prod_price | prod_date| +----+---------+--------------------+------------+---------------------+ |6 | BR03| 18 inch teddy bear |NULL | 2022-04-02 12:00:00 | +----+---------+--------------------+------------+---------------------+ 1 row in set (0.00 sec)

全局变量

mysql> SELECT @@back_log; /*返回MySQL主要连接请求的数量*/ +------------+ | @@back_log | +------------+ |151 | +------------+ 1 row in set (0.00 sec)mysql> SELECT @@basedir; /*返回MySQL安装基准目录*/ +-----------+ | @@basedir | +-----------+ | D:\MySQL\ | +-----------+ 1 row in set (0.00 sec)mysql> SELECT @@license; /*返回服务器的许可类型*/ +-----------+ | @@license | +-----------+ | GPL| +-----------+ 1 row in set (0.00 sec)mysql> SELECT @@port; /*返回服务器侦听TCP/IP连接所用的端口*/ +--------+ | @@port | +--------+ |3306 | +--------+ 1 row in set (0.00 sec)mysql> SELECT @@version; /*返回服务器版本号*/ +-----------+ | @@version | +-----------+ | 8.0.19| +-----------+ 1 row in set (0.00 sec)

查看所有的全局变量

mysql> SHOW GLOBAL VARIABLES;

会返回很多信息，建议模糊查找，比如

mysql> SHOW GLOBAL VARIABLES LIKE 'sto%'; +---------------------------------+-------+ | Variable_name| Value | +---------------------------------+-------+ | stored_program_cache| 256| | stored_program_definition_cache | 256| +---------------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)

常用系统函数字符串函数
计算字符串字符数和长度 CHAR_LENGTH:返回字符串所包含的字符个数
LENGTH:返回字符串的字节长度，一个汉字3个字节，一个数字或字母是一个字节

mysql> SELECT CHAR_LENGTH('mysql'); +----------------------+ | CHAR_LENGTH('mysql') | +----------------------+ |5 | +----------------------+ 1 row in set (0.04 sec)mysql> SELECT LENGTH('mysql'); +-----------------+ | LENGTH('mysql') | +-----------------+ |5 | +-----------------+ 1 row in set (0.00 sec)mysql> SELECT CHAR_LENGTH('汉字'); +-----------------------+ | CHAR_LENGTH('汉字')| +-----------------------+ |2 | +-----------------------+ 1 row in set (0.00 sec)mysql> SELECT LENGTH('汉字'); +------------------+ | LENGTH('汉字')| +------------------+ |6 | +------------------+ 1 row in set (0.00 sec)

合并字符串

mysql> SELECT CONCAT('MySQL版本:',@@version) 版本信息; +--------------------+ | 版本信息| +--------------------+ | MySQL版本:8.0.19| +--------------------+ 1 row in set (0.00 sec)

字符串大小写转换

mysql> SELECT LOWER(@str1) 转换为小写,UPPER(@str1) 转换为大写; +-----------------+-----------------+ | 转换为小写| 转换为大写| +-----------------+-----------------+ | mysql| MYSQL| +-----------------+-----------------+

删除空格只能删除前后空格，不能删除字符串中间的空格

mysql> SET @str1=' MySQL ';

mysql> SELECT LTRIM(@str1) 删除前导空格,RTRIM(@str1) 删除尾部空格,TRIM(@str1) 删除两侧空格; +--------------------+--------------------+--------------------+ | 删除前导空格| 删除尾部空格| 删除两侧空格| +--------------------+--------------------+--------------------+ | MySQL|MySQL| MySQL| +--------------------+--------------------+--------------------+

取子串 SUBSTRING(str,start,length)

首字母为M的产品编号 mysql> SELECT * FROM products WHERE SUBSTRING(prod_id,1,1)='M'; +----+---------+-----------+------------+---------------------+ | id | prod_id | prod_name | prod_price | prod_date| +----+---------+-----------+------------+---------------------+ | 10 | MR01| toy dog|5.00 | 2022-04-03 12:00:00 | | 11 | MR02| toy dog|2.00 | 2022-04-03 12:00:00 | | 12 | MR03| NULL|NULL | 2022-04-02 12:00:00 | +----+---------+-----------+------------+---------------------+第四个字符为3的产品编号 mysql> SELECT * FROM products WHERE SUBSTRING(prod_id,4,1)='3'; +----+---------+--------------------+------------+---------------------+ | id | prod_id | prod_name| prod_price | prod_date| +----+---------+--------------------+------------+---------------------+ |6 | BR03| 18 inch teddy bear |NULL | 2022-04-02 12:00:00 | | 12 | MR03| NULL|NULL | 2022-04-02 12:00:00 | +----+---------+--------------------+------------+---------------------+

数学函数

mysql> SELECT ABS(-1) 绝对值,PI() AS π,SQRT(9) AS 平方根,MOD(9,4) AS 余数,ROUND(2.568,2) AS 四舍五入保留2位小数; +-----------+----------+-----------+--------+------------------------------+ | 绝对值| π| 平方根| 余数| 四舍五入保留2位小数| +-----------+----------+-----------+--------+------------------------------+ |1 | 3.141593 |3 |1 |2.57 | +-----------+----------+-----------+--------+------------------------------+

日期和时间函数
获取当前系统的日期及取日期的年月日 CURDATE():返回当前系统日期
YEAR(d)、MONTH(d)、DAY(d):分别返回日期或日期时间d的年月日的值

mysql> SET @d=CURDATE(); Query OK, 0 rows affected (0.00 sec)mysql> SELECT @d,YEAR(@d),MONTH(@d),DAY(@d); +------------+----------+-----------+---------+ | @d| YEAR(@d) | MONTH(@d) | DAY(@d) | +------------+----------+-----------+---------+ | 2022-04-03 |2022 |4 |3 | +------------+----------+-----------+---------+

查询产品生产日期距今几天（也可以是几年）

mysql> SELECT prod_name 产品名称,DAY(CURDATE())-DAY(prod_date) 距今几日 FROM products; +---------------------+--------------+ | 产品名称| 距今几日| +---------------------+--------------+ | Fish bean bag toy|2 | | Bird bean bag toy|2 | | Rabbit bean bag toy |2 | | 8 inch teddy bear|1 | | 12 inch teddy bear|1 | | 18 inch teddy bear|1 | | Raggedy Ann|1 | | King doll|0 | | Queen dool|0 | | toy dog|0 | | toy dog|0 | | NULL|1 | +---------------------+--------------+

获取当前系统的日期时间

mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE(); +---------------------+---------------------+---------------------+---------------------+ | CURRENT_TIMESTAMP() | LOCALTIME()| NOW()| SYSDATE()| +---------------------+---------------------+---------------------+---------------------+ | 2022-04-03 21:42:10 | 2022-04-03 21:42:10 | 2022-04-03 21:42:10 | 2022-04-03 21:42:10 | +---------------------+---------------------+---------------------+---------------------+

系统信息函数

mysql> SELECT USER() AS 用户,DATABASE() AS 当前数据库; +----------------+-----------------+ | 用户| 当前数据库| +----------------+-----------------+ | root@localhost | learn| +----------------+-----------------+

条件控制函数
IF 返回前6条记录，如果产品价格为NULL时显示0，否则显示当前字段值。

mysql> SELECT prod_id 产品编号,IF(prod_price IS NULL,0,prod_price) 产品价格 FROM products LIMIT 6; +--------------+--------------+ | 产品编号| 产品价格| +--------------+--------------+ | BNBG01|3.49 | | BNBG02|3.49 | | BNBG03|3.49 | | BR01|5.99 | | BR02|8.99 | | BR03|0 | +--------------+--------------+

CASE 如果产品价格小于5元，为便宜，否则为贵；

mysql> SELECT prod_id 产品编号,prod_price 产品价格, -> CASE -> WHEN prod_price < 5 THEN '便宜' -> ELSE '贵' -> END 价格类型 -> FROM products; +--------------+--------------+--------------+ | 产品编号| 产品价格| 价格类型| +--------------+--------------+--------------+ | BNBG01|3.49 | 便宜| | BNBG02|3.49 | 便宜| | BNBG03|3.49 | 便宜| | BR01|5.99 | 贵| | BR02|8.99 | 贵| | BR03|9.00 | 贵| | RGAN01|4.99 | 便宜| | RYL01|9.99 | 贵| | RYL02|9.99 | 贵| | MR01|5.00 | 贵| | MR02|2.00 | 便宜| | MR03|5.00 | 贵| +--------------+--------------+--------------+ 12 rows in set (0.00 sec)

数据类型转换函数
【SQL|MySQL数据库编程01】CAST(x AS 新类型)
CONVERT(x 新类型)

mysql> SELECT prod_name,prod_price INTO @name,@price FROM products WHERE prod_id='BR02'; Query OK, 1 row affected (0.00 sec)mysql> SELECT CONCAT(@name,'的价格是',CAST(@price AS CHAR(7))) 信息; +------------------------------------+ | 信息| +------------------------------------+ | 12 inch teddy bear的价格是8.99| +------------------------------------+