- 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='测试部')
)