MySQL入门 | day06 | 数据类型、函数

 1.字符串类型

char ()  存储固定长度字符串

        eg：洲名字的缩写

varchar() 存储可变长度字符串   max：~64kb （65535）

        eg：用户名，密码，邮箱，地址、

mediumtext    中文本串        max： 16MB

        对于存储JSON,SCV字符串和短中长度的数都好用

longtext          长文本串        max：4GB

        可以存储许多年的日志文件

tinytext       微文本类型     max： 255bytes

text             文本类型        max：64kb

2.整数类型

TINYINT                  1bytes  [-128,127]

UNSIGNED TINYINT           [0,255]

SMALLINT               2b        [-32k,32k]

MEDIUMINT             3b       [-8M,8M]

INT                           4b        [-2B,2B]

BIGINT                     8b        [-9Z,9Z]

3.定点和浮点类型

DECIMAL (6，2）  小数型 存储定点数可用

        代表：1234.56

        同义词： DEC、NUMERIC、FIXED

FLOAT     浮点型   4b

DOUBLE 双精度   8b

4.布尔类型

用来存储YES/NO的值

5.枚举和集合类型

ENUM('DA','ZHONG','XIAO ' )枚举类型， 将某一列的值限制在一个有限字符串列表范围内

SET(....) 集合类型

6.日期和时间类型

DATE 存储一个没有时间成分的日期

TIME  存储一个时间值

DATETIME 日期时间类型 8b

TIMESTAMP 时间戳    4b （up to 2038）

YEAR 存储四位数年份

7.Blob类型

二进制长对象类型， 用来存储大型二进制数据

        eg：图像，视频，PDF, WORD文件...

TINYBLOB        255b

BLOB                65KB

MEDIUMBLOB 16MB

LONGBLOB      4GB

8.JSON类型

JSON 会使用大括号来定义一个对象,括号里会有一个或多个键值对

{

        "KEY" ：‘  VALUE 

}

数据库products表中添加新列，定义属性为JSON

UPDATE products
SET properties =
'{
	"dimensions" :[1,2,3],
    "weight" : 10
}'
WHERE product_id = 1

 JSON_OBJECT（）

在这个函数中，可以传递多个键值对

​UPDATE products
SET properties = JSON_OBJECT(
	'weight',10, 
    'dimensions',
    JSON_ARRAY(1,2,3),
    'manufactuter', JSON_OBJECT('name','sony')
)
WHERE product_id = 1

假设查询一个产品，只想知道他的weight

JSON_EXTRACT(properties, '$.weight')  -- 列名 ， $.属性名

SELECT product_id, JSON_EXTRACT(properties, '$.weight')
FROM products
WHERE product_id = 1;

 也可以使用特殊运算符  ->

​SELECT product_id, properties ->'$.weight'
FROM products
WHERE product_id = 1;

如果想去掉得到结果的引号可以使用   ->> 

​SELECT product_id, properties ->>'$.manufacturer.name'
FROM products
WHERE product_id = 1;

如果想更新weight，不重置整个对象，只更新重量值

JSON_SEt() 更新或添加

UPDATE products
SET properties = JSON_SET(
	properties,    -- 表示针对这个对象进行更改
	'$.weight' , 20,  -- 要更改的属性，属性值
	'$.age', 10
)
WHERE product_id = 1

查询

​SELECT product_id, properties
FROM products
WHERE properties ->>'$.weight'

JSON_REMOVE() 删除一个或多个属性

UPDATE products
SET properties = JSON_REMOVE(
	properties,
	'$.age'
)
WHERE product_id = 1

数值函数

一、ROUND（）四舍五入

SELECT ROUND（3.14526,  2）

2表示保留两位小数

二、TRUNCATE （）截断函数

SELECT TRUNCATE（3.14526,  2）

三、CEILING（）上限函数

SELECT CEILING（3.2）返回大于或等于这个数字的最小整数

4

四、FLOOR（）

SELECT FLOOR（3.2）返回小于或等于这个数字的最大整数

3

五、ABS（）绝对值函数

六、RAND（）用来生成0-1区间随机浮点数

mysql numeric functions

可以查看更多mysql可以用到的函数

字符串函数

LENGTH() 得到字符串中的字符数

LOWER()  转换成小写

UPPER()   转换成大写

  可以移除字符串中空格的函数

LTRIM (left trim) （）移除字符串左侧的空白字符或其他预定字符

RTRIM() 移除字符串右侧空白字符或其他...

TRIM() 删除所有前导或尾随空格

LEFT() 返回字符串左侧的几个字符

        SELECT LEFT('WOSHINIDAGE', 4)   --得到WOSH

RIGHT() 返回字符串右侧的几个字符

字符截取函数

SUBSTR()   SUB STRING  得到一个字符串中的任何位置的字符

      SELECT SUBSTR('WOSHINIDAGE', 4,5)   --得到HINID

      SELECT SUBSTR('WOSHINIDAGE', 4)   --得到HINIDAGE

LOCATE() 返回第一个字符或者一串字符匹配位置

        SELECT LOCATE('O','WOSHINIDAGE')     --返回2  不区分大小写

REPLACE(‘参数1’，‘被替换的’， ‘替换后的’)

        SELECT REPLACE('DOGPIG','PIG','POG')  --返回DOGPOG

CONCAT() 用来串联两个字符串

        SELECT CONCAT('PIG','POG') --返回PIGPOG

mysql string functions

可以查看更多mysql可以用到的函数

日期函数

NOW() 调用当前的日期和时间

        SELECT NOW()

        SELECT NOW(), CURDATE()  --只显示日期

        SELECT NOW(), CURTIME()   --只显示时间

得到当前年、月、日     YEAR()，MONTH()，DAY() 

得到当前时刻    HOUR() ， MINUTE() ，SECOND()

DAYNAME()  获取字符串格式的星期数

MONTHNAME() 获取字符串格式的月份

EXTRACT()    2022/10/9

        SELECT extract(YEAR FROM NOW())  --2022

         SELECT extract(DAY FROM NOW())  --9

练习： 返回2019年的订单

SELECT *
FROM orders
WHERE YEAR(order_date ) = '2019'

格式化日期和时间

SELECT date_format(NOW(), '%m''%d''%y')

大小写有区分

 SELECT date_format(NOW(), '%M''%D''%Y')

更多精彩 请搜索mysql date format string

计算日期和时间

DATE_ADD()  给日期时间添加日期成分

        SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) --增加一天

         SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) --减少一天

DATE_SUB()  在一个时间日期上减去日期成分

         SELECT DATE_SUB(NOW(), INTERVAL 1 DAY) --减少一天

DATEDIFF()  查看日期间隔

        SELECT DATEDIFF(NOW(), '2022-10-01') 

TIME_TO_SEC 查看时间间隔

        SELECT time_to_sec('12:36')-time_to_sec('12:26')  --600

IFNULL、COALEASE函数

SELECT 
	order_id,
    ifnull(shipper_id, 'Not assigned') as shipper
FROM orders

如果shipper是空值，把Not assigned赋给它

SELECT 
	order_id,
    coalesce(shipper_id, comments, 'Not assigned') as shipper
FROM orders

假设shipper_id是空值，想要返回注释列的值，如果备注也是空值，返回Not assigned

练习：

由此表写出sql语句： 

SELECT 
	(CONCAT(first_name,last_name))as customer,
	IFNULL(phone,'Unknow') as phone
FROM customers

IF 函数 

SELECT 
	product_id,
    name,
    COUNT(*) AS orders,
    IF (COUNT(*) > 1 ,'Many times','Once') AS frequency
FROM products 
JOIN order_items  USING (product_id)
GROUP BY product_id,name

关键一步在于GROUP BY 

因为这里使用了聚合函数，count

所以要用groupby进行分组查询

CASE运算符

SELECT 
	order_id,
    CASE
		WHEN YEAR(order_date) = '2019'  THEN 'Active' -- '2019'= Year(now())
		WHEN YEAR(order_date) = '2018' THEN 'Last Year'
        WHEN YEAR(order_date) < '2018' THEN 'Archived'
		ELSE 'Future'
	END AS category
FROM orders