MySQL查询案例

• MySQL基本查询语句

DROP TABLE IF EXISTS fruits;
CREATE TABLE fruits
(
id VARCHAR(10) NOT NULL, #水果Id
sid INT(11) NOT NULL,     #类别ID
NAME VARCHAR(255) NOT NULL,#名称
price DECIMAL(8,2) NOT NULL,#价格
PRIMARY KEY(id)
);
INSERT INTO fruits VALUES('a1',101,'apple',5.2),
('a2',103,'apricot',2.2),
('b1',101,'blackberry',10.2),
('b2',104,'berry',7.6),
('b5',107,'xxxx',3.6),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('c0',101,'cherry',3.2),
('l2',104,'lemon',6.4),
('m1',105,'mango',15.7),
('m2',105,'xbabay',2.6),
('m3',105,'xxtt',11.60),
('o2',103,'coconut',9.20),
('t1',102,'banana',10.30),
('t2',102,'grape',5.0),
('t4',107,'xbababa',3.60);

INSERT INTO fruits VALUES('w1',101,'西瓜',4.5);
INSERT INTO fruits VALUES('w2',101,'香蕉',3.5);
INSERT INTO fruits VALUES('w3',101,'香瓜',6.5);
INSERT INTO fruits VALUES('w4',101,'木瓜',6.5);
INSERT INTO fruits VALUES('w5',101,'香肠',6.5);
INSERT INTO fruits VALUES('w6',101,'火肠',6.5);

INSERT INTO fruits VALUES('w8',102,'apple',6.5);
INSERT INTO fruits VALUES('w9',103,'apple',6.5);
#示例7-1
SELECT * FROM fruits;
#等价于
SELECT id,sid,NAME,price FROM fruits;

#示例7-2
SELECT NAME FROM fruits;

#示例7-3
SELECT fname,fprice FROM fruits;

#示例7-4 查询水果价格为10.2水果记录
SELECT *
FROM fruits
WHERE fprice=10.2

#示例7-5 查询水果名称为‘apple’价格
SELECT NAME,price
FROM fruits
WHERE NAME='apple'

#示例7-5 查询水果名称为‘apple’价格
SELECT NAME,price
FROM fruits
WHERE NAME='apple'  


#示例7-8 查询水果sid为101,102
SELECT NAME,sid,price
FROM fruits
WHERE sid  IN (101,102)


#示例7-8查询水果sid不为101,102
SELECT NAME,sid,price
FROM fruits
WHERE sid  NOT IN (101,102)


#示例7-9 查询价格在2.0~10.2所有水果
SELECT*
FROM fruits
WHERE price>=2.0 AND price<=10.2


#示例7-9 
SELECT*
FROM fruits
WHERE price BETWEEN 2.0 AND 10.2


#示例7-10 查询价格在2.0~10.2之外水果
SELECT*
FROM fruits
WHERE price NOT BETWEEN 2.0 AND 10.2

SELECT*
FROM fruits
WHERE  price<2.0 OR price>10.2

#示例7-14 查询水果名为以y结尾前有四个字符

SELECT*
FROM fruits
WHERE NAME LIKE '____y'



DROP TABLE IF EXISTS customers;

CREATE TABLE customers
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
address VARCHAR(50),
city VARCHAR(50),
zip VARCHAR(10),
contact VARCHAR(50),
email VARCHAR(255)
) ENGINE=INNODB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

INSERT INTO customers VALUES
(NULL,'RedHook','200 Street','Tianjin','300000','Liming','Liming@163.com'),
(NULL,'Stars','333 Fromage Lane','Dalian','116000','Zhaobo','jerry163.com'),
(NULL,'Netbhood','1 Sunny Place','Qiandao','266000','Luocong',NULL),
(NULL,'JOTO','828 Reverside Drive','Haikou','570000','YangShan','sam@163.com');

#示例7-15 查询email为空记录
SELECT * FROM customers WHERE email IS  NULL;  #null=null结果为false

#示例7-16 查询email不为空记录
SELECT * FROM customers WHERE email IS NOT  NULL;


#示例7-17 查询sid=101且价格大于5
SELECT * 
FROM fruits 
WHERE sid=101 AND price>5;

#示例7-20 不重复

SELECT DISTINCT sid FROM fruits;

#示例7-21 排序
#
SELECT * FROM fruits ORDER BY NAME; #默认是升序
SELECT * FROM fruits ORDER BY CONVERT(NAME USING GBK) ASC; #ASC升序 中文按拼音顺序排序   
SELECT * FROM fruits ORDER BY  CONVERT(NAME USING GBK) DESC; #DESC降序


#按sid升序排序，再按price降序排序
SELECT * FROM fruits ORDER BY sid ASC,price DESC;




SELECT * FROM fruits ORDER BY sid

#示例7-26 分组select语句只能出现分组字段和聚集函数
SELECT sid,COUNT(*)
FROM fruits GROUP BY sid ORDER BY sid;

#示例7-27 根据sid分组，并显示每个供应商供应的水果名称
# 
SELECT sid AS 供应商代号,GROUP_CONCAT(NAME) AS 水果名称
FROM fruits 
GROUP BY sid

#示例7-28 查询供商水果品种大于3的供应商id
SELECT sid,COUNT(*)
FROM fruits 
GROUP BY sid 
HAVING COUNT(*)>3
ORDER BY sid;


#示例7-29 查询供商水果品种,并统计数量
SELECT sid,COUNT(*)
FROM fruits
GROUP BY sid WITH ROLLUP


#示例7-30 多字段分组
SELECT sid,NAME,COUNT(*)
FROM fruits
GROUP BY sid,NAME WITH ROLLUP
ORDER BY sid,NAME  DESC


#示例7-30 limit
SELECT * FROM fruits LIMIT 5  ;  
SELECT * FROM fruits LIMIT 0,5 ;
SELECT * FROM fruits LIMIT 5,5 # 起始记录索引（从0开始）,最多返回记录数
SELECT * FROM fruits LIMIT 10,5 #从第11条（索引为10）记录开始，最多返回5条记录


SELECT *  FROM customers;
#示例-34 统计所有顾客人数
SELECT COUNT(*) AS 总人数  FROM customers

SELECT * FROM fruits

SELECT SUM(price) FROM fruits;
SELECT sid,SUM(price) FROM fruits GROUP BY sid;
SELECT sid,AVG(price) FROM fruits GROUP BY sid;
SELECT sid,MAX(price) FROM fruits GROUP BY sid;
SELECT sid,MIN(price) FROM fruits GROUP BY sid;


DROP TABLE IF EXISTS dept;

#部门表
CREATE TABLE DEPT
(
DEPTNO INT(11) PRIMARY KEY, 	#部门编号
DNAME VARCHAR(14), 		#部门名称
LOC VARCHAR(13) 		#部门位置
);
INSERT INTO DEPT VALUES(10,'研发部','北京'),(20,'市场部','上海'),(30,'测试部','南京');
INSERT INTO DEPT VALUES(40,'人事部','北京');
SELECT * FROM dept;
 
DROP TABLE IF EXISTS EMP;
 
CREATE TABLE EMP
(
EMPNO INT(11) PRIMARY KEY ,	#雇员编号
ENAME VARCHAR(10),		#表示雇员姓名
JOB VARCHAR(9),			#表示工作职位
MGR INT(11),			#表示一个雇员的领导编号
HIREDATE DATE,			#表示雇佣日期
SAL DECIMAL(7,2),		#表示月薪，工资
COMM DECIMAL(7,2),		#表示奖金或佣金
DEPTNO INT(11),			#表示部门编号
CONSTRAINT FK_EMP_DEPT FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
INSERT INTO emp VALUES(7369,'SMITH', 'CLERK',7902, '1980-7-12',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN', 'SALESMAN',7698, '1981-2-20',  1600 ,  300,30);
INSERT INTO emp VALUES(7521,'WARD', 'SALESMAN',7698,'1981-2-22', 1250  , 500,30);
INSERT INTO emp VALUES(7566,'JONES', 'MANAGER',7839,'1981-4-2', 2975  , NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN', 'SALESMAN',7698,'1981-9-28',  1250 , 1400	,30);
INSERT INTO emp VALUES(7698,'BLAKE', 'MANAGER',7839,'1981-5-1', 2850  ,NULL, 30);
INSERT INTO emp VALUES(7782,'CLARK', 'MANAGER',7839,'1981-6-9', 2450  ,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT', 'ANALYST',7566,'1987-6-13', 3000  ,NULL,20);
INSERT INTO emp VALUES(7839,'KING', 'PRESIDENT', NULL,'1981-17-11', 5000  , NULL,10);
INSERT INTO emp VALUES(7844,'TURNER', 'SALESMAN',7698,'1981-9-8', 1500 , NULL,30);
INSERT INTO emp VALUES(7876,'ADAMS', 'CLERK',7788, '1987-5-23', 1100  , NULL,20);
INSERT INTO emp VALUES(7900,'JAMES', 'CLERK', 7698,'1981-12-3	', 950 , NULL,30);
INSERT INTO emp VALUES(7902,'FORD', 'ANALYST',7566, '1981-12-3', 3000 , NULL,20);
INSERT INTO emp VALUES(7934,'MILLER', 'CLERK', 7782,'1982-1-23', 1300 , NULL,10);
INSERT INTO emp VALUES(7959,'ALICE', 'CLERK', 7782,'1982-1-23', 1300 , NULL,NULL);



SELECT * FROM dept;


SELECT * FROM emp;

#笛卡积
#联接查询
SELECT * FROM emp,dept WHERE emp.`DEPTNO`=dept.`DEPTNO`;

#内联查询  
SELECT * FROM emp INNER JOIN dept ON emp.DEPTNO=dept.DEPTNO ;

#内联查询 添加where子名
SELECT emp.*,dept.dname,dept.LOC FROM emp INNER JOIN dept 
 ON emp.DEPTNO=dept.DEPTNO 
 WHERE HIREDATE>='1982-1-1'
 
#内联查询 为表命名别名 as可选
SELECT e.*,d.dname,d.LOC FROM emp AS e INNER JOIN dept AS  d
 ON e.DEPTNO=d.DEPTNO 
 WHERE HIREDATE>='1982-1-1'
 
#外联：左外联接,右外联接,完全外联接不支持
#左外联接
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno

#右外联接
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno

#完全外联接 不支持,可union
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
UNION #all 加上all不会合并相同记录
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno

#子查询和外层查询关联符号 (关系运算符> =)接合 any/ some ,all, in/ in not,

#子查询示例一：查询测试部门所有员工

#1采用内联查询实现
SELECT * 
FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
WHERE d.dname='测试部'

#1采用子查询实现
SELECT * FROM emp e WHERE e.deptno=(SELECT deptno FROM dept d WHERE dname='测试部')


#子查询示例二：查询"办公地点LOC在北京"所有员工
SELECT * FROM emp e WHERE e.deptno IN(SELECT deptno FROM dept WHERE loc='北京')

#子查询示例三：查询工资大于所有"测试部"的员工
#使用 >和 max()函数
SELECT * FROM emp WHERE sal>(
		SELECT MAX(e.sal)
		FROM emp e
		WHERE e.deptno =(SELECT deptno FROM dept d WHERE dname='测试部')
)

#使用 all
SELECT * FROM emp WHERE sal>ALL(
		SELECT e.sal
		FROM emp e
		WHERE e.deptno =(SELECT deptno FROM dept d WHERE dname='测试部')
)

#子查询示例四：查询工资大于任一"测试部"的员工

#使用 >和 min()函数
SELECT * FROM emp WHERE sal>(
		SELECT MIN(e.sal)
		FROM emp e
		WHERE e.deptno =(SELECT deptno FROM dept d WHERE dname='测试部')
)

#使用any 或some
SELECT * FROM emp WHERE sal>ANY (
		SELECT e.sal
		FROM emp e
		WHERE e.deptno =(SELECT deptno FROM dept d WHERE dname='测试部')
)