02-数据库

目录

条件查询

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做的很笨，实现繁琐，

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 #降序
 

聚合 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日到指定日期的毫秒数

中文乱码

如果在dos命令下执行insert插入中文数据，数据有乱码，那现在sqlyog客户端执行下面命令：

set  names  gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试一下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

MySQL数据库默认字符集是lantin1,也就是以后网页中遇到的ISO8859-1,它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;
 

但这样很容易造成服务器和客户端编码不同，如服务器端utf8,客户端ISO8859-1.mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证黄金数据库时用utf8,使用可视化工具一般就基本正确。

注释

/* 很多注释内容 */

#行注释内容

– 行注释内容，这个使用较多

主键，外键，唯一索引的区别？

     primary  key  主键约束，自动创建唯一索引

     Foreign  Key  外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写

     Unique  Index  唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop， delete 和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，truncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是吧表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数。