คำสั่ง MySQL ที่สำคัญ

เตรียมฐานข้อมูล

-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jan 20, 2016 at 04:21 AM
-- Server version: 10.1.9-MariaDB
-- PHP Version: 5.6.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pmj`
--

-- --------------------------------------------------------

--
-- Table structure for table `book`
--

CREATE TABLE `book` (
  `id` int(11) NOT NULL,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `author` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `pub_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `book`
--

INSERT INTO `book` (`id`, `title`, `author`, `price`, `pub_id`) VALUES
(1, 'PHP', 'Jim', 1000, 'p_002'),
(2, 'MySQL', 'John', 1200, 'p_002'),
(3, 'jQuery', 'Jack', 1500, 'p_004'),
(4, 'HTML5/CSS3', 'James', 1700, 'p_001'),
(5, 'VB', 'Joe', 1800, 'p_111');

-- --------------------------------------------------------

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `id` smallint(6) NOT NULL,
  `name` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `gender` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'female',
  `position` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `salary` int(11) NOT NULL,
  `email` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `birthday` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `name`, `gender`, `position`, `salary`, `email`, `birthday`) VALUES
(1, 'John', 'male', 'Manager', 50000, 'john@hotmail.com', '1980-10-20'),
(2, 'Jane', 'female', 'Accountant', 30000, 'jane@yahoo.com', '1985-01-31'),
(3, 'Jill', 'female', 'Accountant', 25000, 'jill@example.com', '1988-05-31'),
(4, 'Jack', 'male', 'Sales', 30000, 'jack@test.com', '1990-12-01'),
(5, 'Jenny', 'female', 'Sales', 25000, 'janny@hotmail.com', '1992-02-14'),
(6, 'Jennifer', 'female', 'Secretary', 20000, 'jennifer@gmail.com', '1992-10-01'),
(7, 'Jim', 'male', 'Programmer', 35000, 'jim@yahoo.com', '1980-04-30');

-- --------------------------------------------------------

--
-- Table structure for table `pubinfo`
--

CREATE TABLE `pubinfo` (
  `pub_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `pub_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(50) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `pubinfo`
--

INSERT INTO `pubinfo` (`pub_id`, `pub_name`, `address`) VALUES
('p_001', 'Rock Press', 'USA'),
('p_002', 'ExPress', 'UK'),
('p_003', 'JPress', 'Japan'),
('p_004', 'DevPress', 'USA');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `book`
--
ALTER TABLE `book`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pub_id` (`pub_id`);

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `pubinfo`
--
ALTER TABLE `pubinfo`
  ADD PRIMARY KEY (`pub_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `book`
--
ALTER TABLE `book`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
  MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

ตาราง Employee

1. การแสดงชื่อฐานข้อมูล ตาราง และโครงสร้างแต่ละคอลัมน์ของตาราง (Show/Describe)

SHOW DATABASES
USE ชื่อฐานข้อมูล
SHOW TABLES
SHOW TABLES FROM ชื่อฐานข้อมูล
DESCRIBE ชื่อตาราง

2. การเลือกข้อมูล (Select)

SELECT name, email FROM employee

3. การกำนดเงื่อนไข (Where)

% = แทนอักขระกี่ตัวก็ได้หรือไม่มีเลย เช่น a% (ขึ้นต้นด้วย a) %a% มี a อยู่ตำแหน่งใดก็ได้

_ = แทนอักขระ 1 ตัว (ต้องมีเสมอ) เช่น an_ (and, any) _an (man, van)

[ ] = ใช้กำหนดช่วงของตัวอักษรที่มีลำดับต่อเนื่อง เช่น [A-F] อักษร A-F ตัวไหนก็ได้ [j-p]an เช่น jan

[^ ] = กำหนดอักษรที่ไม่ต้องการ เช่น [^j-m]an จะต้องไม่เป็น jan แต่ van ได้

SELECT * FROM employee WHERE email LIKE %yahoo%

4. การใช้ Comparison Operators

SELECT * FROM employee WHERE salary >= 30000

5. การใช้ And กับ Or

SELECT * FROM employee
WHERE (gender = 'female') AND (salary >= 25000)

SELECT * FROM employee
WHERE (email LIKE '%@hotmail.com') OR (email LIKE '%@gmail.com')

6. การใช้ Between And กับ Not Between And

SELECT * FROM employee
WHERE salary BETWEEN 20000 AND 25000

SELECT * FROM employee
WHERE salary NOT BETWEEN 20000 AND 25000

7. การใช้ In กับ Not In

SELECT * FROM employee
WHERE name IN ('Jim', 'John', 'Jack')

SELECT * FROM employee
WHERE name NOT IN ('Jim', 'John', 'Jack')

8. การป้องกันการเลือกซ้ำ (Distinct)

SELECT DISTINCT(position) FROM employee

9. การกำหนดคอลัมน์ผลลัพธ์ (As)

SELECT name, salary AS old_salary, salary + (salary*0.1) AS new_salary
FROM employee

10. การเรียงลำดับผลลัพธ์ (Order By)

ASC น้อยไปมาก DESC มากไปน้อย

SELECT * FROM employee ORDER BY name ASC, salary DESC

11. การกำหนดช่วงแถวข้อมูลผลลัพธ์

3 แถวแรก

SELECT * FROM employee ORDER BY salary LIMIT 3

ต้องการแถวที่ 3 - 6 ปกติลำดับแถวแรกคือ 0 เลยต้องเริ่มอ่านที่ลำดับแถวที่ 2 ไปอีก 4 แถว

SELECT * FROM employee ORDER BY salary LIMIT 2, 4

LIMIT ใช้กับ UPDATE, DELETE ก็ได้

เพิ่มตาราง Book และ PubInfo


ตาราง Book	ตาราง PubInfo

12. การใช้ Inner Join

SELECT book.title, pubinfo.pub_name
FROM book INNER JOIN pubinfo
ON book.pub_id = pubinfo.pub_id
ORDER BY book.id

หรือ

SELECT b.title, p.pub_name
FROM book b, pubinfo p
WHERE b.pub_id = p.pub_id
ORDER BY b.id

13. การใช้ Left Join

SELECT book.title, pubinfo.pub_name
FROM book LEFT JOIN pubinfo
ON book.pub_id = pubinfo.pub_id
ORDER BY book.id

14. การใช้ Right Join

SELECT book.title, pubinfo.pub_name
FROM book RIGHT JOIN pubinfo
ON book.pub_id = pubinfo.pub_id
ORDER BY book.id

15. การใช้ Subquery + Comparison Operators

SELECT title, author, price FROM book
WHERE price < (
SELECT price FROM book WHERE title = 'PHP' )

SELECT title, author, price FROM book
WHERE pub_id < (
SELECT pub_id FROM pubinfo WHERE pub_name = 'Express' )

16. การใช้ Subquery + In / Not In

ใช้กรณีที่ค่าที่ได้อาจจะมีมากกว่า 1 แถว เช่น เราต้องการทราบชื่อหนังสือ จาก สนพ. 2 แห่ง แต่เราไม่ทราบค่า pub_id

SELECT book.title, book.author, pubinfo.pub_name, pubinfo.address
FROM book LEFT JOIN pubinfo
ON book.pub_id = pubinfo.pub_id
WHERE book.pub_id IN (
SELECT pub_id FROM pubinfo
WHERE address LIKE '%USA%' OR address LIKE '%UK%' )

17. การเพิ่มข้อมูล (Insert)

INSERT INTO ชื่อตาราง (ชื่อคอลัมน์1, ...) VALUES (ค่าคอลัมน์1, ...)

INSERT INTO ชื่อตาราง VALUES (ค่าคอลัมน์1, ...)

INSERT INTO ชื่อตาราง VALUES (ค่าคอลัมน์1, ...), (ค่าคอลัมน์1, ...), ...

INSERT INTO`employee` (`id`, `name`, `gender`, `position`, `salary`, `email`, `birthday`)
VALUES
(NULL, 'Joe', 'male', 'Sales', '25000', 'joe@yahoo.com', '1992-09-17'),
(NULL, 'Jay', 'female', 'Sales', '25000', 'jay@msn.com', '1992-05-03');

18. การแก้ไขข้อมูล (Update)

UPDATE ชื่อตาราง SET ชื่อคอลัมน์1 = ค่าคอลัมน์1, ...
[ WHERE ... ORDER BY ... LIMIT ... ]

UPDATE employee SET salary = salary - (salary*0.1)
ORDER BY salary DESC LIMIT 3

19. การลบข้อมูล (Delete)

DELETE FROM ชื่อตาราง
[ WHERE ... ORDER BY ... LIMIT ... ]

DELETE FROM employee
WHERE name = 'Jay'

DELETE FROM employee
ORDER BY salary DESC LIMIT 2

20 การแทนที่ข้อมูล (Replace)

REPLACE INTO ชื่อตาราง VALUES (...)

กรณีค่าที่เพิ่ม เป็น PK ซ้ำ ข้อมูลก็จะเปลี่ยนไปตามนั้น แต่ถ้า PK ไม่ซ้ำ จะเป็นการเพิ่มข้อมูล (Insert) ไปอีกแถวแทน

REPLACE INTO employee VALUES (5, 'VB', 'Jane', 1800, 'p_111')

Verzaru's Notes