#数据库
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', 'LMing@163.com' ),
( 10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', 'Jerry@hotmail.com' ),
( 10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL ),
( 10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com' );
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