SQL(简单查询&条件查询&模糊查询&排序&统计函数)

首先创建一张表，跟着把sql按顺序敲完即可学会SQL(简单查询&条件查询&模糊查询&排序&统计函数)

创建表：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` float(7,2) DEFAULT NULL,
  `COMM` float(7,2) DEFAULT NULL,
  `DEPTNO` int(4) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `FK_DEPTNO` (`DEPTNO`) USING BTREE,
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '20');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

语法：

select {columns}
from {table|view|other select}
[where 条件]
[group by 分组条件]
[having 分组后再限定]
[order by 排序]

-- 1.查询指定的列：
select empno,ename,hiredate from emp;

-- 2.起别名：as 可以省略不写
select empno as 员工编号,ename '员工姓名' from emp;

-- 3.字符串类型可以做连续运算
select concat('我的名字是',ename,',喔喔') from emp;

-- 4.去重：distinct
select  job from emp;
select distinct job from emp;	
select distinct job,deptno from emp;

条件查询

⽐较运算符：=，!=,<>,>,<,>=,<=
逻辑运算符：and ，or， not
范围：between and，in,not in
null：is null，is not null

练习1：查询1981年以后⼊职的员⼯信息
select * from emp where hiredate>='1981-1-1';
练习2：查询部⻔编号为30或者⼯资⼤于2000的员⼯信息。
select ename,sal,deptno from emp where sal>2000 or deptno=30;
练习3：在emp表中，使⽤in关键字查询职务为”president”,”manager”和”analyst”中任意⼀种的员
⼯信息。
SELECT * FROM emp WHERE job IN('president','manager','analyst');

模糊查询：like

%：匹配0-多个任意的字符
_：匹配1个任意字符
like '_a';只有两个字符
like '%a%';包含a
like 'a%';以a 字⺟开头的

//名字的第三个字⺟为a的员⼯信息
mysql> select * from emp where ename like '__a%';
select * from emp where ename like 'A%';

排序：orderby

asc：升序，默认
desc：降序
select查询完后，排序要写在整个sql语句的最后。

select * from emp order by sal;
select * from emp order by sal desc;

统计函数

也叫聚合函数，通常⽤于求整个表中某列的数据的：总和，平均值，最⼤值，最⼩值。通常不搭配表中
的字段⼀起查询。
sum(),
avg(),
max()
min(),
count(*/主键)

练习1：求部⻔编号20中员⼯的平均⼯资，⼯资总和，⼯资最⼤值，最⼩值，⼈数。
select ename,sum(sal),avg(sal),max(sal),min(sal)
,count(empno),count(comm)from emp where deptno=20;