MySQLクエリー探索(一)単表クエリー
57988 ワード
#
CREATE TABLE fruits (
f_id CHAR ( 10 ) NOT NULL,
s_id INT NOT NULL,
f_name CHAR ( 255 ) NOT NULL,
f_price DECIMAL ( 8, 2 ) NOT NULL,
PRIMARY KEY ( f_id )
);
INSERT INTO fruits ( f_id, s_id, f_name, f_price )
VALUES
( 'a1', 101, 'apple', 5.2 ),
( 'b1', 101, 'blackberry', 10.2 ),
( 'bs1', 102, 'orange', 11.2 ),
( 'bs2', 105, 'melon', 8.2 ),
( 't1', 102, 'banana', 10.3 ),
( 't2', 102, 'grape', 5.3 ),
( 'o2', 103, 'coconut', 9.2 ),
( 'c0', 101, 'cherry', 3.2 ),
( 'a2', 103, 'apricot', 2.2 ),
( 'l2', 104, 'lemon', 6.4 ),
( 'b2', 104, 'berry', 7.6 ),
( 'm1', 106, 'mango', 15.6 ),
( 'm2', 105, 'xbabay', 2.6 ),
( 't4', 107, 'xbababa', 3.6 ),
( 'm3', 105, 'xxtt', 11.6 ),
( 'b5', 107, 'xxxx', 3.6 );
CREATE TABLE customers (
c_id INT NOT NULL AUTO_INCREMENT,
c_name CHAR ( 50 ) NOT NULL,
c_address CHAR ( 50 ) NULL,
c_city CHAR ( 50 ) NULL,
c_zip CHAR ( 10 ) NULL,
c_contact CHAR ( 50 ) NULL,
c_email CHAR ( 255 ) NULL,
PRIMARY KEY ( c_id )
);
INSERT INTO customers ( c_id, c_name, c_address, c_city, c_zip, c_contact, c_email )
VALUES
( 10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', '[email protected]' ),
( 10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', '[email protected]' ),
( 10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL ),
( 10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', '[email protected]' );
CREATE TABLE orderitems (
o_num INT NOT NULL,
o_item INT NOT NULL,
f_id CHAR ( 10 ) NOT NULL,
quantity INT NOT NULL,
item_price DECIMAL ( 8, 2 ) NOT NULL,
PRIMARY KEY ( o_num, o_item )
);
INSERT INTO orderitems ( o_num, o_item, f_id, quantity, item_price )
VALUES
( 30001, 1, 'a1', 10, 5.2 ),
( 30001, 2, 'b2', 3, 7.6 ),
( 30001, 3, 'bs1', 5, 11.2 ),
( 30001, 4, 'bs2', 15, 9.2 ),
( 30002, 1, 'b3', 2, 20.0 ),
( 30003, 1, 'c0', 100, 10 ),
( 30004, 1, 'o2', 50, 2.50 ),
( 30005, 1, 'c0', 5, 10 ),
( 30005, 2, 'b1', 10, 8.99 ),
( 30005, 3, 'a2', 10, 2.2 ),
( 30005, 4, 'm1', 5, 14.99 );
CREATE TABLE suppliers (
s_id INT NOT NULL AUTO_INCREMENT,
s_name CHAR ( 50 ) NOT NULL,
s_city CHAR ( 50 ) NULL,
s_zip CHAR ( 10 ) NULL,
s_call CHAR ( 50 ) NOT NULL,
PRIMARY KEY ( s_id )
);
INSERT INTO suppliers ( s_id, s_name, s_city, s_zip, s_call )
VALUES
( 101, 'FastFruit Inc.', 'Tianjin', '300000', '48075' ),
( 102, 'LT Supplies', 'Chongqing', '400000', '44333' ),
( 103, 'ACME', 'Shanghai', '200000', '90046' ),
( 104, 'FNK Inc.', 'Zhongshan', '528437', '11111' ),
( 105, 'Good Set', 'Taiyuang', '030000', '22222' ),
( 106, 'Just Eat Ours', 'Beijing', '010', '45678' ),
( 107, 'DK Inc.', 'Zhengzhou', '450000', '33332' );
CREATE TABLE orders ( o_num INT NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id INT NOT NULL, PRIMARY KEY ( o_num ) );
INSERT INTO orders ( o_num, o_date, c_id )
VALUES
( 30001, '2008-09-01', 10001 ),
( 30002, '2008-09-12', 10003 ),
( 30003, '2008-09-30', 10004 ),
( 30004, '2008-10-03', 10005 ),
( 30005, '2008-10-08', 10001 );
CREATE TABLE dept ( d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT, d_name VARCHAR ( 50 ), d_location VARCHAR ( 100 ) );
CREATE TABLE employee (
e_no INT NOT NULL PRIMARY KEY,
e_name VARCHAR ( 100 ) NOT NULL,
e_gender CHAR ( 2 ) NOT NULL,
dept_no INT NOT NULL,
e_job VARCHAR ( 100 ) NOT NULL,
e_salary SMALLINT NOT NULL,
hireDate DATE,
CONSTRAINT dno_fk FOREIGN KEY ( dept_no ) REFERENCES dept ( d_no )
);
INSERT INTO dept
VALUES
( 10, 'ACCOUNTING', 'ShangHai' ),
( 20, 'RESEARCH ', 'BeiJing ' ),
( 30, 'SALES ', 'ShenZhen ' ),
( 40, 'OPERATIONS ', 'FuJian ' );
INSERT INTO employee
VALUES
( 1001, 'SMITH', 'm', 20, 'CLERK', 800, '2005-11-12' ),
( 1002, 'ALLEN', 'f', 30, 'SALESMAN', 1600, '2003-05-12' ),
( 1003, 'WARD', 'f', 30, 'SALESMAN', 1250, '2003-05-12' ),
( 1004, 'JONES', 'm', 20, 'MANAGER', 2975, '1998-05-18' ),
( 1005, 'MARTIN', 'm', 30, 'SALESMAN', 1250, '2001-06-12' ),
( 1006, 'BLAKE', 'f', 30, 'MANAGER', 2850, '1997-02-15' ),
( 1007, 'CLARK', 'm', 10, 'MANAGER', 2450, '2002-09-12' ),
( 1008, 'SCOTT', 'm', 20, 'ANALYST', 3000, '2003-05-12' ),
( 1009, 'KING', 'f', 10, 'PRESIDENT', 5000, '1995-01-01' ),
( 1010, 'TURNER', 'f', 30, 'SALESMAN', 1500, '1997-10-12' ),
( 1011, 'ADAMS', 'm', 20, 'CLERK', 1100, '1999-10-05' ),
( 1012, 'JAMES', 'm', 30, 'CLERK', 950, '2008-06-15' );
範囲
#s_id 101 102 ， NOT IN，（IN OR）SQL ：
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;
# 2.00 10.20 ：
SELECT
f_name, f_price
FROM
fruits
WHERE
f_price BETWEEN 2.00 AND 10.20;
#not between
SELECT
f_name, f_price
FROM
fruits
WHERE
f_price NOT BETWEEN 2.00 AND 10.20;
ワイルドカードフィルタ
# ’b’ ：
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';
# f_name ’g’ ：
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '%g%';
# ’b’ ， ’y’ ：
SELECT f_name
FROM fruits
WHERE f_name LIKE 'b%y';
# ’y’ ， ’y’ 4 ：
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '----y';
空とNULL
# c_email :
null c_id、c_name c_email ：
SELECT c_id, c_name, c_email
FROM customers
WHERE c_email is null or c_email='';
# customers c_email null， c_id、c_name c_email ：
SELECT c_id, c_name, c_email
FROM customers
WHERE c_email IS NOT null and c_email !='';
繰り返さない、並べ替え
#s_id ：
SELECT DISTINCT s_id
FROM fruits;
# f_price ， f_name ，
# ， ：
#
SELECT f_price, f_name
FROM fruits
ORDER BY f_price DESC, f_name;
グループ化
#GROUP BY
# s_id， s_id ：
SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id;
109 1
101 3
103 2
104 2
107 2
102 3
105 3
106 1
#GROUP_CONCAT
# s_id fruits ， ：
SELECT
s_id,
GROUP_CONCAT( f_name ),
count( * ) AS total
FROM
fruits
GROUP BY
s_id;
101 apple,blackberry,cherry 3
102 orange,banana,grape 3
103 apricot,coconut 2
104 berry,lemon 2
105 melon,xbabay,xxtt 3
106 mango 1
107 axxx,xbababa 2
109 about 1
#HAVING （ ： ， having ）
# s_id fruits ， 1 ：
SELECT
s_id,
GROUP_CONCAT( f_name ) f_names
FROM
fruits
GROUP BY
s_id
HAVING
COUNT( f_name ) > 2;
#
# s_id f_name fruits ， SQL ，
SELECT s_id, f_name, group_concat(f_name)
FROM fruits
group by s_id,f_name;
group by & order by
# ： 100 ：
SELECT
o_num,
GROUP_CONCAT( quantity ) quantity,
GROUP_CONCAT( item_price ) item_price,
SUM( quantity * item_price ) AS orderTotal
FROM
orderitems
GROUP BY
o_num
HAVING
orderTotal >= 10
ORDER BY
orderTotal desc;
集約、統計関連
limit
# 4 ：
SELECT *
From fruits LIMIT
4;
# 5 ， 3 ：
SELECT *
From fruits LIMIT
4, 3;
統計関数
count() sum() avg() max() min()
#COUNT()
# customers ：
SELECT COUNT(*) AS cust_num
FROM customers;
# customers （ ）SQL ：
SELECT COUNT(c_email) AS email_num
FROM customers;
# ：
SELECT o_num, COUNT(f_id)
FROM orderitems
GROUP BY o_num;
#SUM()
# ：
SELECT o_num, SUM(quantity) AS items_total
FROM orderitems
GROUP BY o_num;
#AVG()
# ：
SELECT s_id, AVG(f_price) AS avg_price
FROM fruits
GROUP BY s_id;
# ：
SELECT
s_id,
MAX( f_price ) AS max_price
FROM
fruits
GROUP BY
s_id
ORDER BY
max_price DESC