カテゴリー: MySQL

MySQL 関数

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  likes INT,
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Merci', 4),
  ('Arigato', 4),
  ('Gracias', 15),
  ('Danke', 8);

-- + - * / %

SELECT
  likes * 500 / 3 AS bonus,
  FLOOR(likes * 500 / 3) AS floor,
  CEIL(likes * 500 / 3) AS ceil,
  -- ROUND(likes * 500 / 3) AS round
  ROUND(likes * 500 / 3, 2) AS round
FROM
  posts;

MySQL NULL

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  likes INT,
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', NULL),
  ('Gracias', 15),
  ('Danke', NULL);

-- SELECT * FROM posts;
-- SELECT * FROM posts WHERE likes != 12;
-- SELECT * FROM posts WHERE likes != 12 OR likes IS NULL;
SELECT * FROM posts WHERE likes IS NOT NULL;

MySQL SELECT

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  likes INT,
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 4),
  ('Gracias', 15),
  ('Danke', 23);

-- SELECT * FROM posts;
-- SELECT id, message FROM posts;

-- > >= < <=
-- SELECT * FROM posts WHERE likes >= 10;

-- = != <>
SELECT * FROM posts WHERE message = 'Danke';
SELECT * FROM posts WHERE message != 'Danke';
SELECT * FROM posts WHERE message <> 'Danke';