MySQL là hệ thống quản lý cơ sở dữ liệu SQL quan hệ mã nguồn mở phổ biến nhất. MySQL là một trong những RDBMS tốt nhất đang được sử dụng để phát triển các ứng dụng phần mềm dựa trên web
Hướng dẫn này sẽ giúp bạn bắt đầu nhanh với MySQL và giúp bạn cảm thấy thoải mái với lập trình MySQL
Tài liệu tham khảo này đã được chuẩn bị cho người mới bắt đầu để giúp họ hiểu các khái niệm cơ bản đến nâng cao liên quan đến ngôn ngữ MySQL
Trước khi bạn bắt đầu thực hành với nhiều loại ví dụ khác nhau được đưa ra trong tài liệu tham khảo này, tôi giả định rằng bạn đã biết cơ sở dữ liệu là gì, đặc biệt là RDBMS và ngôn ngữ lập trình máy tính là gì
Đọc "Cách cài đặt MySQL và bắt đầu" về cách cài đặt, tùy chỉnh và bắt đầu với MySQL
Tóm tắt các lệnh MySQL được sử dụng trong Hướng dẫn này
Để biết cú pháp chi tiết, hãy xem hướng dẫn sử dụng MySQL "Cú pháp câu lệnh SQL" @ http. // nhà phát triển. mysql. com/doc/refman/5. 5/en/sql-cú pháp. html
Một ví dụ cho người mới bắt đầu [Nhưng KHÔNG dành cho người giả]
Một máy chủ cơ sở dữ liệu MySQL chứa nhiều cơ sở dữ liệu [hoặc lược đồ]. Mỗi cơ sở dữ liệu bao gồm một hoặc nhiều bảng. Một bảng được tạo thành từ các cột [hoặc trường] và hàng [bản ghi]
Các từ khóa và lệnh SQL KHÔNG phân biệt chữ hoa chữ thường. Để rõ ràng, chúng được hiển thị bằng chữ hoa. Tên hoặc mã định danh [tên cơ sở dữ liệu, tên bảng, tên cột, v.v. ] phân biệt chữ hoa chữ thường trong một số hệ thống, nhưng không phân biệt chữ hoa chữ thường trong các hệ thống khác. Do đó, cách tốt nhất là coi số nhận dạng là phân biệt chữ hoa chữ thường
HIỂN THỊ CƠ SỞ DỮ LIỆUBạn có thể sử dụng
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
Cơ sở dữ liệu "
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+0" được cung cấp trong quá trình cài đặt để bạn thử nghiệm
Hãy để chúng tôi bắt đầu với một ví dụ đơn giản - cơ sở dữ liệu bán sản phẩm. Một cơ sở dữ liệu bán sản phẩm thường bao gồm nhiều bảng, e. g. , sản phẩm, khách hàng, nhà cung cấp, đơn đặt hàng, thanh toán, nhân viên, v.v. Hãy gọi cơ sở dữ liệu của chúng ta là "______51" [lấy cảm hứng từ cơ sở dữ liệu mẫu Northwind Trader của Microsoft]. Chúng ta sẽ bắt đầu với bảng đầu tiên có tên là "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" với các cột sau [có kiểu dữ liệu như đã chỉ ra] và các hàngCơ sở dữ liệu. gió nam
Bảng. productsproductID
INTMã sản phẩm
CHAR[3]tên
VARCHAR[30]số lượng
INTgiá< . 231002PENBút Xanh80001. 251003PENBút Đen20001. 251004PECBút chì 2B100000. 481005PECBút chì 2H80000. 49
DECIMAL[10,2]1001PENPen Red50001.231002PENPen Blue80001.251003PENPen Black20001.251004PECPencil 2B100000.481005PECPencil 2H80000.49
Tạo và xóa cơ sở dữ liệu - CREATE DATABASE and DROP DATABASE
Bạn có thể tạo cơ sở dữ liệu mới bằng lệnh SQL "______53"; . Bạn có thể tùy ý áp dụng điều kiện "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+5" hoặc "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+6" cho các lệnh này. Ví dụ,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........2
QUAN TRỌNG. Sử dụng các lệnh SQL
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+7 [và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8] hết sức cẩn thận, vì các thực thể đã xóa không thể khôi phục được. KHÔNG CÓ HOÀN TÁCHIỂN THỊ TẠO CƠ SỞ DỮ LIỆU
Các lệnh
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+9 sử dụng một số giá trị mặc định. Bạn có thể gõ "
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........60" để hiển thị lệnh đầy đủ và kiểm tra các giá trị mặc định này. Chúng tôi sử dụng
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........61 [thay vì
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........62] để hiển thị kết quả theo chiều dọc. [Hãy thử so sánh kết quả đầu ra của
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........62 và
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........61. ]
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........65 và
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........66]. Bạn có thể bao gồm các khoảng trống và ký tự đặc biệt hoặc sử dụng từ khóa MySQL làm định danh bằng cách kèm theo một cặp trích dẫn ngược, ở dạng
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........67
Để đảm bảo tính mạnh mẽ, lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........68 trích dẫn lại tất cả các mã định danh, như được minh họa trong ví dụ trênNhận xét và Phiên bản Nhận xét
Nhận xét nhiều dòng của MySQL được đính kèm trong
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........69 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 làm cơ sở dữ liệu mặc định, chúng tôi chỉ cần gọi bảng là "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2". Mặt khác, chúng ta cần tham chiếu bảng là "
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+5" hoặc "_______56". Để tạo một bảng, bạn cần xác định tất cả các cột của nó bằng cách cung cấp tên, loại và thuộc tính của các cột
Hãy tạo một bảng "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" trong cơ sở dữ liệu của chúng tôi "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1"giải thích
Chúng tôi xác định 5 cột trong bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2.
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+04,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+05,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+06 và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+07. các loại là
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
03 ismysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
09 - số nguyên không âmmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
04 làmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
201 - một chuỗi chữ và số có độ dài cố định gồm 3 ký tựmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
05 ismysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
203 - một chuỗi có độ dài thay đổi lên đến 30 ký tự.
Chúng tôi sử dụng chuỗi có độ dài cố định chomysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
04, vì chúng tôi giả sử rằngmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
04 chứa chính xác 3 ký tự. Mặt khác, chúng tôi sử dụng chuỗi có độ dài thay đổi chomysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
05, vì độ dài của nó thay đổi -mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
207 hiệu quả hơnmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
208.mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
06 cũng làmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
09 [số nguyên không âm]mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
07 ismysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
212- một số thập phân có 2 chữ số thập phân.
______1213 là chính xác [được biểu thị dưới dạng số nguyên có dấu thập phân cố định]. Mặt khác,mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
214 vàmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
215 [số thực] không chính xác và chỉ xấp xỉ. Loạimysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
213 được khuyến nghị cho tiền tệ.
Thuộc tính "______1217" chỉ định rằng cột không được chứa giá trị
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 là một giá trị đặc biệt cho biết "không có giá trị", "giá trị không xác định" hoặc "giá trị bị thiếu". Trong trường hợp của chúng tôi, các cột này sẽ có một giá trị phù hợp. Chúng tôi cũng đặt giá trị mặc định của các cột. Cột sẽ nhận giá trị mặc định nếu không có giá trị nào được chỉ định trong quá trình tạo bản ghi
Chúng tôi đặt cột
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03 làm cái gọi là khóa chính. Giá trị của cột khóa chính phải là duy nhất. Mỗi bảng sẽ chứa một khóa chính. Điều này đảm bảo rằng mọi hàng có thể được phân biệt với các hàng khác. Bạn có thể chỉ định một cột hoặc một tập hợp các cột [e. g. ,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........221 và
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........222] làm khóa chính. Một chỉ mục được xây dựng tự động trên cột khóa chính để hỗ trợ tìm kiếm nhanh. Khóa chính cũng được sử dụng làm tham chiếu cho các bảng khác
Chúng tôi đặt cột
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03 thành
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........224. với giá trị bắt đầu mặc định là 1. Khi bạn chèn một hàng có
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 [được đề xuất] [hoặc 0 hoặc giá trị bị thiếu] cho cột
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........224, giá trị tối đa của cột đó cộng với 1 sẽ được chèn vào. Bạn cũng có thể chèn một giá trị hợp lệ vào cột
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........224, bỏ qua phần tăng tự động
Chèn hàng - INSERT INTO
Hãy lấp đầy bảng "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2" của chúng ta bằng các hàng. Chúng tôi đặt
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03 của bản ghi đầu tiên thành 1001 và sử dụng
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........224 cho các bản ghi còn lại bằng cách chèn một
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 hoặc với một giá trị cột bị thiếu. Lưu ý rằng các chuỗi phải được đặt trong một cặp dấu ngoặc đơn [hoặc dấu ngoặc kép]CHÈN VÀO Cú pháp
Chúng ta có thể sử dụng câu lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........232 để chèn một hàng mới với tất cả các giá trị của cột, sử dụng cú pháp sau
Bạn cần liệt kê các giá trị theo cùng thứ tự mà các cột được xác định trong
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........233, được phân tách bằng dấu phẩy. Đối với các cột kiểu dữ liệu chuỗi [
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........208,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........207] thì kèm theo giá trị bằng một cặp dấu nháy đơn [hoặc nháy kép]. Đối với các cột có kiểu dữ liệu số [
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........236,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........213,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........214,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........215], chỉ cần đặt số
Bạn cũng có thể chèn nhiều hàng vào một câu lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........232
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8
Để chỉ chèn một hàng có giá trị trên các cột đã chọn, hãy sử dụng
Các cột còn lại sẽ nhận giá trị mặc định, chẳng hạn như
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........224, mặc định hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218
Truy vấn cơ sở dữ liệu - CHỌN
Nhiệm vụ phổ biến, quan trọng và phức tạp nhất là truy vấn cơ sở dữ liệu để tìm một tập hợp con dữ liệu đáp ứng nhu cầu của bạn - bằng lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243. Lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 có cú pháp như sau
Ví dụ như,
CHỌN không có bảngBạn cũng có thể phát hành
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 mà không cần bàn. Ví dụ: bạn có thể
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 một biểu thức hoặc đánh giá một hàm dựng sẵnToán tử so sánh
Đối với các số [
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........236,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........213,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........214], bạn có thể sử dụng các toán tử so sánh.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........250 [bằng],
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........251 hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........252 [không bằng],
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........253 [lớn hơn],
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........254 [nhỏ hơn],
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........255 [lớn hơn hoặc bằng],
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........256 [nhỏ hơn hoặc bằng], để so sánh hai số. Ví dụ:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........257,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........258
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........214 [số thực] cho bằng nhau [
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........250 hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........251], vì chúng không chính xác. Mặt khác,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........213 là chính xác
Đối với chuỗi, bạn cũng có thể sử dụng
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........250,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........251,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........253,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........254,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........255,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........256 để so sánh hai chuỗi [e. g. ,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........269 Thứ tự của chuỗi phụ thuộc vào cái gọi là đối chiếu đã chọn. Ví dụ,Khớp mẫu chuỗi - THÍCH và KHÔNG THÍCH
Đối với các chuỗi, ngoài việc khớp hoàn toàn bằng cách sử dụng các toán tử như
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........250 và
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........251, chúng ta có thể thực hiện khớp mẫu bằng cách sử dụng toán tử
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........272 [hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........273] với các ký tự đại diện. Ký tự đại diện _______ 1274 khớp với bất kỳ ký tự đơn nào; . Ví dụ,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
276 khớp với các chuỗi bắt đầu bằngmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
277;mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
278 khớp với các chuỗi kết thúc bằngmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
279;mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
280 khớp với các chuỗi chứamysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
281;mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
282 khớp với các chuỗi chứa chính xác ba ký tự;mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
283 khớp với các chuỗi bắt đầu bằngmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
284, theo sau là bất kỳ ký tự đơn nào, theo sau làmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
285, theo sau là 0 hoặc nhiều ký tự
MySQL cũng hỗ trợ khớp biểu thức chính quy thông qua toán tử
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........286toán tử số học
Bạn có thể thực hiện các phép toán số học trên các trường số bằng các toán tử số học, như được lập bảng dưới đây
Toán tử Mô tả+Cộng-Trừ*Nhân/chia DIVInteger Division%Modulus [Phần dư]Toán tử logic - AND, OR, NOT, XORBạn có thể kết hợp nhiều điều kiện với toán tử boolean
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........287,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........288,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........289. Bạn cũng có thể đảo ngược một điều kiện bằng cách sử dụng toán tử
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........290. Ví dụ như,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+VÀO, KHÔNG VÀO
Bạn có thể chọn từ các phần tử của một tập hợp với toán tử
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........291 [hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........292]. Điều này dễ dàng và rõ ràng hơn biểu thức
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........293 tương đương
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........6GIỮA, KHÔNG GIỮA
Để kiểm tra xem giá trị có nằm trong một phạm vi hay không, bạn có thể sử dụng toán tử
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........294. Một lần nữa, điều này dễ dàng và rõ ràng hơn biểu thức
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........293 tương đương
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 là một giá trị đặc biệt, đại diện cho "không có giá trị", "giá trị bị thiếu" hoặc "giá trị không xác định". Bạn có thể kiểm tra xem một cột có chứa
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 bởi
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........298 hoặc
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........299 hay không. Ví dụ,
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 là một lỗi - một lỗi rất phổ biến. Ví dụ,ĐẶT HÀNG THEO Mệnh đề
Bạn có thể sắp xếp thứ tự các hàng đã chọn bằng mệnh đề
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+0
Hàng đã chọn sẽ được sắp xếp theo các giá trị trong
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........66]. Nếu một số hàng có cùng giá trị trong
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........20Mệnh đề GIỚI HẠN
Truy vấn
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 trên cơ sở dữ liệu lớn có thể tạo ra nhiều hàng. Bạn có thể sử dụng mệnh đề
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........21
Sản xuất báo cáo tóm tắt
Để tạo báo cáo tóm tắt, chúng ta thường cần tổng hợp các hàng có liên quan
RIÊNG BIỆTMột cột có thể có các giá trị trùng lặp, chúng tôi có thể sử dụng từ khóa
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........22
Ví dụ,
THẬN TRỌNG. Nếu mệnh đề
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243, sử dụng cùng tiêu chí, để kiểm tra tập kết quả trước khi đưa ra truy vấn
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 trong phần sau
Xóa hàng - DELETE FROM
Sử dụng lệnh
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2". Một cơ sở dữ liệu thực tế chứa nhiều bảng liên quan
Sản phẩm có nhà cung cấp. Nếu mỗi sản phẩm có một nhà cung cấp và mỗi nhà cung cấp chỉ cung cấp một sản phẩm [được gọi là mối quan hệ một-một], chúng ta chỉ cần thêm dữ liệu của nhà cung cấp [tên, địa chỉ, số điện thoại] vào bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2. Giả sử rằng mỗi sản phẩm có một nhà cung cấp và một nhà cung cấp có thể cung cấp 0 hoặc nhiều sản phẩm [được gọi là mối quan hệ một-nhiều]. Đưa dữ liệu của nhà cung cấp vào bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 dẫn đến trùng lặp dữ liệu. Điều này là do một nhà cung cấp có thể cung cấp nhiều sản phẩm, do đó, dữ liệu của cùng một nhà cung cấp xuất hiện trong nhiều hàng. Điều này không chỉ gây lãng phí dung lượng lưu trữ mà còn dễ dẫn đến tình trạng không thống nhất [vì tất cả các dữ liệu trùng nhau phải được cập nhật đồng thời]. Tình hình thậm chí còn phức tạp hơn nếu một sản phẩm có nhiều nhà cung cấp và mỗi nhà cung cấp có thể cung cấp nhiều sản phẩm, trong mối quan hệ nhiều-nhiều
Mối quan hệ một-nhiều
Giả sử rằng mỗi sản phẩm có một nhà cung cấp và mỗi nhà cung cấp cung cấp một hoặc nhiều sản phẩm. Chúng ta có thể tạo một bảng tên là
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2, chúng tôi thêm một cột mới vào bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 -
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 làm khóa ngoại tham chiếu cột
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 tham chiếu bảng
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........29THAY ĐỔI BẢNG
Thay vì xóa và tạo lại bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2, chúng ta sẽ sử dụng "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+803" để thêm một cột mới
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 vào bảng cha
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 luôn đề cập đến một
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 thành một ________ 2186 hợp lệ trong bảng [giả sử
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+816]CHỌN với THAM GIA
Lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 có thể được sử dụng để truy vấn và nối dữ liệu từ hai bảng có liên quan. Ví dụ: để liệt kê
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+05 của sản phẩm [trong bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2] và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+05 của nhà cung cấp [trong bảng
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2, vì bạn không thể xác định số lượng nhà cung cấp và do đó, số lượng cột cần thiết cho các
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03 vào bảng
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+831, như minh họaCơ sở dữ liệu. gió nam
Bảng. products_suppliersproductID
INT
[Khóa ngoại]supplierID
INT
[Khóa ngoại]20015012002501200350120045022001503< . gió nam
Database: southwind
Bảng. nhà cung cấpsupplierID
INTname
VARCHAR[30]phone
CHAR[8]501ABC Traders88881111502XYZ Company88882222503QQ Corp88883333
Database: southwind
Bảng. productsproductID
INTMã sản phẩm
CHAR[3]tên
VARCHAR[30]số lượng
INTgiá< . 522002PECBút chì 4B2000. 622003PECBút chì 5B1000. 732004PECBút chì 6B5000. 47
DECIMAL[10,2]2001PECPencil 3B5000.522002PECPencil 4B2000.622003PECPencil 5B1000.732004PECPencil 6B5000.47
Hãy tạo bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+831. Khóa chính của bảng gồm 2 cột.
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+03 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2. [Cột này đã được thêm vào để thiết lập mối quan hệ một-nhiều. Nó không còn cần thiết trong mối quan hệ nhiều-nhiều. ]
Trước khi có thể xóa cột này, bạn cần xóa khóa ngoại được tạo trên cột này. Để xóa một khóa trong MySQL, bạn cần biết tên ràng buộc của nó do hệ thống tạo ra. Để tìm tên ràng buộc, hãy nhập "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+837" và lưu ý tên ràng buộc của khóa ngoại trong mệnh đề "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+838". Sau đó, bạn có thể xóa khóa ngoại bằng cách sử dụng "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+839"
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 với
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+842 để truy vấn dữ liệu từ 3 bảng, ví dụ:
Sơ đồ cơ sở dữ liệu như sau. Cả hai bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2, sẽ hiệu quả hơn nếu tạo một bảng khác có tên là
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+846s và liên kết nó với
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+2 bằng mối quan hệ một đối một, như minh họa
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+848 để kết xuất [sao lưu] toàn bộ cơ sở dữ liệu
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+9,
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........233 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+848 như sau
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+848 tạo ra một tập lệnh SQL [bao gồm các lệnh
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........233 và
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+1 trước đó
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+860 có thể được sử dụng làm khóa chính cho bảng
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+861; . Khóa chính được gọi là khóa đơn nếu nó là một cột đơn; . Hầu hết các RDBMS xây dựng một chỉ mục trên khóa chính để hỗ trợ tìm kiếm nhanh. Khóa chính thường được dùng để liên kết với các bảng khác
Khóa ngoại
Khóa ngoại của bảng con dùng để tham chiếu đến bảng cha. Có thể áp đặt ràng buộc khóa ngoại để đảm bảo cái gọi là tính toàn vẹn tham chiếu - các giá trị trong bảng con phải là các giá trị hợp lệ trong bảng cha
Chúng tôi xác định khóa ngoại khi xác định bảng con, tham chiếu đến bảng cha, như sau
Bạn có thể chỉ định hành động tham chiếu cho
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+8 thông qua các mệnh đề tùy chọn
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+868 và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+869
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
870 [mặc định]. không cho phépmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
8 hoặcmysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
873. xếp tầng tác vụmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
8 hoặcmysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
876. đặt giá trị khóa ngoại trong bảng con thànhmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
218 [nếu cho phépmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
218]mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
879. một thuật ngữ SQL có nghĩa là không có hành động nào trên hàng của cha mẹ. Tương tự nhưmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
870 trong MySQL, không cho phépmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
8 hoặcmysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
831 [con], e. g. ,Bản ghi không thể bị xóa do ràng buộc "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
885" mặc định đã được áp đặtChỉ mục [hoặc Khóa]
Chỉ mục [hoặc Khóa] có thể được tạo trên [các] cột đã chọn để hỗ trợ tìm kiếm nhanh. Không có chỉ mục, "
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
886" cần khớp với cộtmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
03 của tất cả các bản ghi trong bảngmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
2. Nếu cộtmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
03 được lập chỉ mục [e. g. , sử dụng cây nhị phân], việc so khớp có thể được cải thiện rất nhiều [thông qua tìm kiếm cây nhị phân]Bạn nên lập chỉ mục các cột thường được sử dụng trong mệnh đề
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
892] và cần phải được chứng minh. Tuy nhiên, cơ sở dữ liệu quan hệ thường được tối ưu hóa cho các truy vấn và truy xuất, nhưng KHÔNG dành cho các bản cập nhậtTrong MySQL, từ khóa
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
893 đồng nghĩa vớimysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
894Trong MySQL, các chỉ mục có thể được xây dựng trên
- một cột duy nhất [column-index]
- một tập hợp các cột [chỉ mục nối]
- trên cột giá trị duy nhất [
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
895 hoặcmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
896] - trên tiền tố của một cột cho chuỗi [
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
207 hoặcmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
208], e. g. , 5 ký tự đầu tiên
Có thể có nhiều hơn một chỉ mục trong một bảng. Chỉ mục được tạo tự động trên [các] cột khóa chính
Bạn có thể xây dựng chỉ mục thông qua
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
233,mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
803Thêm SQL
Truy vấn phụ
Kết quả của một truy vấn có thể được sử dụng trong một câu lệnh SQL khác. Truy vấn con rất hữu ích nếu có nhiều hơn một bảng tham gia
CHỌN với Truy vấn conTrong ví dụ bán sản phẩm nhiều-nhiều trước đây, làm cách nào để tìm nhà cung cấp không cung cấp bất kỳ sản phẩm nào?
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
250,mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
253] trên vô hướng,mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
291 hoặcmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
292 cho một hàng hoặc cột,mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
8 hoặcmysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
82- định dạng.
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
83Ghi chú- Đừng dùng
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
84 - Kiểm tra cập nhật cho
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
85 - Chèn các giá trị bằng các hàm có sẵn của MySQL
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
86 - Chèn các giá trị không hợp lệ hoặc nằm ngoài phạm vi. MySQL thay thế bằng tất cả các số không.
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
87Ghi chú. Có thể không hoạt động trong MySQL 5. 7? - Có thể sử dụng hàm tích hợp hữu ích
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
00 để tính ngày trong tương lai, e. g. ,mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
88
- Đừng dùng
Lượt xem
Chế độ xem là một bảng ảo không chứa dữ liệu vật lý. Nó cung cấp một cách khác để xem dữ liệu
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+89
giao dịch
Một giao dịch nguyên tử là một tập hợp các câu lệnh SQL mà TẤT CẢ đều thành công hoặc TẤT CẢ đều thất bại. Giao dịch là quan trọng để đảm bảo rằng không có cập nhật một phần cho cơ sở dữ liệu, được cung cấp một nguyên tử của các câu lệnh SQL. Giao dịch được thực hiện thông qua
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+01 và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+02
Nếu bạn bắt đầu một ứng dụng khách
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........243 trong quá trình giao dịch [trước khi cam kết hoặc khôi phục], bạn sẽ không thấy các thay đổi
Ngoài ra, bạn cũng có thể tắt cái gọi là chế độ
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+05, được đặt theo mặc định và cam kết mọi câu lệnh SQL đơn lẻ
Một giao dịch nhóm một tập hợp các hoạt động thành một đơn vị đáp ứng kiểm tra ACID
- nguyên tử. Nếu tất cả các hoạt động thành công, các thay đổi được cam kết với cơ sở dữ liệu. Nếu bất kỳ hoạt động nào không thành công, toàn bộ giao dịch sẽ được khôi phục và không có thay đổi nào được thực hiện đối với cơ sở dữ liệu. Nói cách khác, không có cập nhật một phần
- Tính nhất quán. Một giao dịch biến đổi cơ sở dữ liệu từ trạng thái nhất quán này sang trạng thái nhất quán khác
- Sự cách ly. Các thay đổi đối với giao dịch không hiển thị đối với giao dịch khác cho đến khi chúng được cam kết
- Độ bền. Các thay đổi đã cam kết sẽ bền và không bao giờ bị mất
Biến người dùng
Trong MySQL, bạn có thể xác định các biến người dùng thông qua
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
06 trong lệnhmysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........
243, hoặc- Lệnh
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
08 hoặcmysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+
09
Ví dụ như,
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+10 tạo ra tất cả các kết hợp của n1×n2 hàng - nó được gọi là Tích Descartes hoặc Tích chéo
Bạn có thể áp đặt ràng buộc bằng cách sử dụng mệnh đề ________ 511, ví dụ:
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+15 có thể tạo ra các hàng nằm trong một bảng nhưng không phải trong một bảng khác. Có hai loại
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+15.
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17 tạo ra các hàng nằm trong bảng bên trái, nhưng có thể không nằm trong bảng bên phải;
Trong một
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17, khi một hàng trong bảng bên trái không khớp với bảng bên phải, nó vẫn được chọn nhưng bằng cách kết hợp với một bản ghi "giả mạo" của tất cả các
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | ........218 cho bảng bên phải
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+17 đảm bảo rằng tập kết quả chứa mọi hàng trên bảng bên trái. Điều này rất quan trọng, vì trong một số truy vấn, bạn muốn có kết quả trên mọi hàng ở bảng bên trái, không có kết quả khớp ở bảng bên phải, e. g. , tìm kiếm các mặt hàng không có nhà cung cấp. Ví dụ,
mysql> SELECT name, price FROM products WHERE price < 1.0; +-----------+-------+ | name | price | +-----------+-------+ | Pencil 2B | 0.48 | | Pencil 2H | 0.49 | +-----------+-------+ 2 rows in set [0.00 sec] mysql> SELECT name, quantity FROM products WHERE quantity SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+22,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+861 và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+24.
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+24 là một bảng nối hỗ trợ mối quan hệ nhiều-nhiều giữa
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+22 và
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %'; +-----------+-------------+----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | +-----------+-------------+----------+----------+-------+ mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %'; +-----------+-------------+---------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+---------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | +-----------+-------------+---------+----------+-------+ mysql> SELECT * FROM products WHERE NOT [quantity >= 5000 AND name LIKE 'Pen %']; +-----------+-------------+-----------+----------+-------+ | productID | productCode | name | quantity | price | +-----------+-------------+-----------+----------+-------+ | 1003 | PEN | Pen Black | 2000 | 1.25 | | 1004 | PEC | Pencil 2B | 10000 | 0.48 | | 1005 | PEC | Pencil 2H | 8000 | 0.49 | +-----------+-------------+-----------+----------+-------+861
Làm thế nào để tìm hiểu MySQL từng bước?
Liên hệ với MySQL. .
Tạo và chọn cơ sở dữ liệu
Tạo bảng
Đang tải dữ liệu vào bảng
Lấy thông tin từ một bảng. Chọn tất cả dữ liệu. Chọn các hàng cụ thể. Chọn các cột cụ thể. Sắp xếp hàng. Tính toán ngày. Làm việc với giá trị NULL. Khớp mẫu. đếm hàng. Sử dụng nhiều hơn một bảng
Làm thế nào để tìm hiểu cơ sở dữ liệu MySQL cho người mới bắt đầu?
Khái niệm chính. .
Giới thiệu về cơ sở dữ liệu
Giới thiệu về PhpMyAdmin
Tổng quan về giao diện PhpMyAdmin
MySQL Security và Root Superuser
MySQL Tạo cơ sở dữ liệu và bảng
MySQL Tạo người dùng mới
Cơ sở dữ liệu MySQL và các đặc quyền cụ thể của bảng
MySQL sửa đổi và xóa cơ sở dữ liệu
Các tính năng chính của MySQL là gì?
Hãy cho chúng tôi hiểu một số tính năng chính liên quan đến MySQL. .
Mã nguồn mở. MySQL là mã nguồn mở, có nghĩa là bất kỳ ai cũng có thể tải xuống, sử dụng và sửa đổi phần mềm này. .
Nhanh chóng và đáng tin cậy. .
có thể mở rộng. .
Loại dữ liệu. .
Bộ nhân vật. .
Chắc chắn. .
Hỗ trợ cơ sở dữ liệu lớn. .
Chương trình khách hàng và tiện ích
những điều cơ bản của cơ sở dữ liệu là gì?
Những cái bàn. Một bảng cơ sở dữ liệu có hình thức tương tự như một bảng tính, trong đó dữ liệu được lưu trữ trong các hàng và cột. .
Các hình thức. Biểu mẫu cho phép bạn tạo giao diện người dùng trong đó bạn có thể nhập và chỉnh sửa dữ liệu của mình. .
Báo cáo. Báo cáo là những gì bạn sử dụng để định dạng, tóm tắt và trình bày dữ liệu. .
Truy vấn. .
macro. .
mô-đun