数据库从入门到精通02

小灰灰 2021-09-11 02:14 368阅读 0赞

### 文章目录 ###

*   *  条件查询
     *   *  distinct
         *  where
         *  like
         *  null
         *  between and
         *  limit
         *  order by
     *  统计案例
     *   *   *  入职统计
             *  年薪统计
     *  聚合 aggregation
     *   *  count
         *  max / min
         *  sum / avg
     *  分组 group
     *   *  group by
         *  having
     *  小结
     *   *  char和varchar有什么区别？
         *  datetime和timestamp有什么区别？
         *  中文乱码
         *  注释
         *  主键、外键、唯一索引的区别？
         *  drop、delete和truncate之间的区别？

## 条件查询 ##

### distinct ###

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;
    
    SELECT DISTINCT loc FROM dept;

### where ###

注意:where中不能使用列别名!!

select * from emp
    
    select * from emp where 1=1 --类似没条件
    
    select * from emp where 1=0 --条件不成立
    
    select * from emp where empno=100 --唯一条件
    
    select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系
    
    select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系
    
    select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
    
    -- 或
    
    select name, sal from emp where sal in(1400,1600,1800);
    
    select name, sal from emp where sal not in(1400,1600,1800);

### like ###

通配符%代表0到n个字符，通配符下划线\_代表1个字符

select * from emp where ename like 'l%' --以l开头的
    
    select * from emp where ename like '%a' --以a结束的
    
    select * from emp where ename like '%a%' --中间包含a的
    
    select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

### null ###

select * from emp where mgr is null --过滤字段值为空的
    
    select * from emp where mgr is not null --过滤字段值不为空的

### between and ###

SELECT * FROM emp
    
    select * from emp where sal<3000 and sal>10000
    
    select * from emp where sal<=3000 and sal>=10000--等效
    
    select * from emp where sal between 3000 and 10000--等效

### limit ###

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条
    
    select * from emp limit 1,2 --从第二条开始,展示2条记录
    
    select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

### order by ###

SELECT * FROM emp order by sal #默认升序
    
    SELECT * FROM emp order by sal desc #降序

## 统计案例 ##

#### 入职统计 ####

\#2015年以前入职的老员工

SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2015-01-01';
    
    SELECT * FROM emp WHERE YEAR(hiredate)<2015

\#2019年以后签约的员工，日期进行格式转换后方便比较

SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,'%Y-%m-%d'))<=2019;

\#2015年到2019年入职的员工

SELECT * FROM emp
    
    WHERE
    
    STR_TO_DATE(hiredate,'%Y-%m-%d')<='2015-01-01'
    
    AND
    
    STR_TO_DATE(hiredate,'%Y-%m-%d')<='2019-12-31'

#### 年薪统计 ####

公司福利不错13薪，年底双薪，统计员工的年薪=sal*13+comm*13

SELECT empno,ename,job,sal*13+comm*13 FROM emp;
    
    SELECT empno,ename,job,sal*13+comm*13 **as**  **年薪** FROM emp;--用as给列起个别名
    
    SELECT empno,ename,job,sal*13+comm*13 年薪 FROM emp; --as也可以省略
    
    select ename, sal+comm from emp
    
    select ename, sal , comm, sal+ifnull(comm,0) from emp--用0替换掉null

## 聚合 aggregation ##

根据一列统计结果

### count ###

select count(*) from emp --底层优化了
    
    select count(1) from emp --效果和*一样
    
    select count(comm) from emp --慢,只统计非NULL的

### max / min ###

select max(sal) from emp --求字段的最大值
    
    select max(sal) sal,max(comm) comm from emp
    
    select min(sal) min from emp --获取最小值
    
    select min(sal) min,max(sal) max from emp --最小值最大值
    
    SELECT ename,MAX(sal) FROM emp group by ename --分组

### sum / avg ###

select count(*) from emp --总记录数
    
    select sum(sal) from emp --求和
    
    select avg(sal) from emp --平均数

## 分组 group ##

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

### group by ###

\#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp
    
    GROUP BY deptno #按照deptno分组
    
    SELECT job,MAX(sal),AVG(sal) FROM emp
    
    GROUP BY job #按照job分组
    
    SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
    
    GROUP BY deptno,job #deptno和job都满足的

### having ###

\#平均工资小于8000的部门

select deptno, AVG(sal) from emp
    group by deptno #按部门分组
    having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having
    
    #deptno出现的次数
    SELECT deptno,COUNT(deptno) FROM emp
    
    GROUP BY deptno #按deptno分组
    
    HAVING COUNT(deptno)>1 #次数多的

## 小结 ##

### char和varchar有什么区别？ ###

char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

### datetime和timestamp有什么区别？ ###

数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是 年与日

time是 时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数