聊技术 | SQL和SQL之间细微的差异

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE students (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

SELECT 
CONCAT(first_name, ' ', last_name) AS full_name 
FROM students;

SELECT 
first_name || ' ' || last_name AS full_name 
FROM students;

SELECT 
first_name + ' ' + last_name AS full_name 
FROM students;

SELECT 
SUBSTRING(name, 1, 3) AS initials 
FROM students;

SELECT name[1:3] AS initials FROM students;

SELECT * FROM students LIMIT 100 OFFSET 20;

SELECT 
TOP 100 * FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, 
* FROM students) 
AS temp WHERE row_num > 20;

SELECT 
DATE_FORMAT(created_at, '%Y-%m-%d') AS date 
FROM products;

SELECT 
TO_CHAR(created_at, 'YYYY-MM-DD') AS date 
FROM products;

SELECT 
CONVERT(VARCHAR(10), created_at, 120) AS date 
FROM products;

CREATE TABLE my_table (
  is_active TINYINT(1)
);

CREATE TABLE my_table (
  is_active BOOLEAN
)
;

CREATE TABLE my_table (
  is_active BIT
);

select `姓名` from student;

select "姓名" from student;

select ifnull(age,0) from student;

select COALESCE(age,0) from student;

select isnull(age,0) from student;