SQL语句练习（7）

sql
-- 创建Seat表
CREATE TABLE Seat (
  id INT PRIMARY KEY,
  student VARCHAR(255)
);

-- 插入数据
INSERT INTO Seat (id, student) VALUES (1, 'Abbot');
INSERT INTO Seat (id, student) VALUES (2, 'Doris');
INSERT INTO Seat (id, student) VALUES (3, 'Emerson');
INSERT INTO Seat (id, student) VALUES (4, 'Green');
INSERT INTO Seat (id, student) VALUES (5, 'Jeames');


select (case when id%2=0 then id-1
             when id=(select max(id) from Seat) then id
             else id+1 end) as id , student from Seat order by id
        
        
select s1.id,coalesce(s2.student,s1.student) 
as student from Seat as s1 
left join Seat as s2 on ((s1.id+1)^1)-1 = s2.id
order by id


select if(mod(s.id,2)=0,s.id-1,if(
    s.id=tmp.cnt,s.id,s.id+1
    )) as id,
    s.student
from Seat s ,(select count(*) cnt from Seat) tmp order by id

总结

COALESCE函数

COALESCE是一个常见的SQL函数，用于返回参数列表中的第一个非空表达式。它接受多个参数，并按顺序逐个检查它们，返回第一个非空值。如果所有参数都为NULL，则返回NULL。

COALESCE函数的语法如下：

sql
COALESCE(expression1, expression2, ..., expressionN)

其中，expression1到expressionN是要检查的表达式。它们可以是列名、常量或其他表达式。

以下是COALESCE函数的工作原理的示例说明：

sql
SELECT COALESCE(NULL, 'Value 1', 'Value 2');

以上查询将返回 'Value 1'，因为它是参数列表中的第一个非空值。

sql
SELECT COALESCE(NULL, NULL, 'Value 3', NULL);

以上查询将返回 'Value 3'，因为它是参数列表中的第一个非空值。

COALESCE函数在处理可能包含NULL值的列时非常有用。它可以用于提供默认值或处理空值情况。例如，如果有一个包含学生成绩的表，可以使用COALESCE函数将NULL值替换为一个默认值：

sql
SELECT student_name, COALESCE(grade, 'N/A') AS final_grade
FROM student_grades;

在上面的例子中，如果grade列的值为NULL，COALESCE函数将返回字符串'N/A'作为最终成绩。

if函数

在MySQL中，IF函数是一个条件函数，根据给定的条件返回不同的结果。它的语法如下：

sql
IF(condition, value_if_true, value_if_false)

其中，condition是一个布尔表达式或条件，value_if_true是在条件为真时返回的值，value_if_false是在条件为假时返回的值。

以下是使用IF函数的示例：

sql
SELECT student_name, IF(grade >= 60, 'Pass', 'Fail') AS result
FROM student_grades;

在上面的例子中，如果grade列的值大于等于60，IF函数将返回字符串'Pass'，否则返回字符串'Fail'。这将为每个学生的成绩生成一个result列。

IF函数也可以嵌套在其他函数或表达式中使用。例如：

sql
SELECT student_name, IF(grade >= 60, IF(grade >= 90, 'A', 'B'), 'C') AS grade_category
FROM student_grades;

在上面的例子中，如果grade大于等于60，然后根据成绩是否大于等于90来返回'A'或'B'，否则返回'C'。这将为每个学生的成绩生成一个grade_category列。

sql
-- 创建Movies表
CREATE TABLE Movies (
  id INT PRIMARY KEY,
  movie VARCHAR(255),
  description VARCHAR(255),
  rating FLOAT
);

-- 插入数据
INSERT INTO Movies (id, movie, description, rating) VALUES (1, 'War', 'great 3D', 8.9);
INSERT INTO Movies (id, movie, description, rating) VALUES (2, 'Science', 'fiction', 8.5);
INSERT INTO Movies (id, movie, description, rating) VALUES (3, 'irish', 'boring', 6.2);
INSERT INTO Movies (id, movie, description, rating) VALUES (4, 'Ice song', 'Fantacy', 8.6);
INSERT INTO Movies (id, movie, description, rating) VALUES (5, 'House card', 'Interesting', 9.1);


SELECT id, movie, description, rating
FROM Movies
WHERE description <> 'boring' AND id % 2 = 1
ORDER BY rating DESC;

SELECT m.id, m.movie, m.description, m.rating
FROM Movies m
JOIN (
  SELECT id
  FROM Movies
  WHERE description <> 'boring' AND id % 2 = 1
) AS sub
ON m.id = sub.id
ORDER BY m.rating DESC;

总结二

mod函数，mod(a,2)=1

sql
-- 创建Customer表
CREATE TABLE Customer (
  customer_id INT,
  product_key INT
);

-- 创建Product表
CREATE TABLE Product (
  product_key INT PRIMARY KEY
);

-- 插入数据到Customer表
INSERT INTO Customer (customer_id, product_key) VALUES (1, 5);
INSERT INTO Customer (customer_id, product_key) VALUES (2, 6);
INSERT INTO Customer (customer_id, product_key) VALUES (3, 5);
INSERT INTO Customer (customer_id, product_key) VALUES (3, 6);
INSERT INTO Customer (customer_id, product_key) VALUES (1, 6);

-- 插入数据到Product表
INSERT INTO Product (product_key) VALUES (5);
INSERT INTO Product (product_key) VALUES (6);


SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

总结四

DISTINCT关键字用于在查询结果中消除重复的行。当查询涉及到多个表或者包含多个列时，可能会导致查询结果中出现重复的行。使用DISTINCT关键字可以确保查询结果中只返回唯一的行。

目录

总结

COALESCE函数

if函数

总结二

总结四