mysql综合实例注意点

1 WITH ROLLUP的小技巧

WITH ROLLUP可以把所有数据作为一组，再起一行。一般用于下面的总计。

SELECT note, SUM(num) 
FROM books 
GROUP BY note WITH ROLLUP;

但是前面会有(NULL)，很难看，我们可以：

SELECT IFNULL(note,'合计总库存量') AS note,SUM(num)  # 利用了最后一行note是空的特性
FROM books 
GROUP BY note WITH ROLLUP;

2 CASE WHEN THEN的使用

# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT `name` '书名', note, CASE note  # note和CASE之间要加‘,’因为CASE相当于重开了一列
WHEN novel THEN '小说'
WHEN law THEN 法律     #!!!注意，必须要用‘’引起来，否则报错 Unknown column '小说' in 'field list'
WHEN medicine THEN 医药
WHEN cartoon THEN 卡通
WHEN joke THEN 笑话
END AS 类型
FROM books;

三个注意点：

• note和CASE之间要加 ',' 因为CASE相当于重开了一列

• WHEN\THEN后的内容，必须要用 ' ' 引起来，否则默认是列名，就会报错说找不到此列。

• CASE相当于重开了一列，列名通过“END AS xxx”来命名。

3 CREATE的使用

CREATE TABLE IF NOT EXISTS books(
    id INT, #书编号 
    `name` VARCHAR(50), #书名 !!!!!!!!!注意这里用的是``！！！！！！！！！！
    `authors` VARCHAR(100), #作者 !!!!!用“”和‘’会报错！！！！！！！！！！！
    price FLOAT, #价格 
    pubdate YEAR, #出版日期 
    note VARCHAR(100), #说明 
    num INT #库存 
);

注意点：

• 因为这里是列名，所以不能用单引号或者双引号，要用飘号 `

4 REPLACE、CHAR_LENGTH的使用

# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC
LIMIT 0,1;

注意点：

• 需要使用CHAR_LENGTH( )函数，而不是LENGTH( )函数。

LENGTH( )按照 字节统计，CHAR_LENGTH( )按照 字符统计。汉字的每个字符占三个字节（utf8）。
所以，对于“哈利波特”和“Story of Jane”，如果使用LENGTH( )，“哈利波特”的12字节更长；如果使用CHAR_LENGTH( )，“Story of Jane”的11字节（去掉空格）更长；

1、utf8字符集编码下,一个汉字是算三个字符,一个数字或字母算一个字符。
2、其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

5 常用数据类型

5.1 日期、时间

# TIMESTAMP存储的时间范围比DATETIME要小很多，
#  - 只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间
# 创建数据表，表中包含一个TIMESTAMP类型的字段ts。
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
# 插入数据：
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'),
('990101030405');

INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');

INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());

#### 超出范围的数据，MYSQL8.0会插入一行：0000-00-00 00:00:00
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');

SELECT * FROM test_timestamp1;

DROP TABLE test_timestamp1;

5.2 ENUM

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

DESC test_enum;

INSERT INTO test_enum
VALUES('春'),('秋');

SELECT * FROM test_enum; 

# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);

# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

5.3 SET

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

INSERT INTO test_set (s) VALUES ('A'), ('A,B');

#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');

#向SET类型的字段插入SET成员中不存在的值时，MySQL8.0警告，然后之加入拥有的部分。
INSERT INTO test_set (s) VALUES ('A,B,C,D');

SELECT *FROM test_set;

5.4 JSON

注意使用 js -> '$.age' 来获取json字符串中的元素。

CREATE TABLE test_json(
    `index` INT,
    js json
);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');

SELECT * FROM test_json;

SELECT js -> '$.age' age, js -> '$.name' NAME, js -> '$.address.city' city
FROM test_json;