【多表查询思维方式】:首先看需要查询的条件中涉及到几个表的字段,然后在按多
表连接的方式来输出
外键:含有外键字段的表为外键表。 外键表里面有一项字段是来自主键表的,所以
两者先得创建主键表,然后在创建外键表。
主键的设置:不允许重复元素 避免了数据的冗余 *主键设置不要使用业务逻辑,要
用代理主键
外键约束:体现了表与表之间的关系
用命令创建数据库:注意:最后一个字段最好不要逗号!!
create table emp
(
id int primary key,
name nvarchar(20) not null,
sex nchar(1),
dept_id int constraint fk_dept
)
**检查约束 check
保证事物属性的取值在合法的范围之内
例:stu_sal int check (stu_sal>=1000 and stu_sal<=8000)
**默认约束 default
保证事物的属性一定会有一个值
例:stu_sex nchar(1) default('男')
**唯一约束 unique *跟主键不同的是唯一键可以有一列为null
SQL里面只允许一个unique列为空
Oracle允许多个unique列为空
保证了事物属性的取值不允许重复,
但允许其中有一列且只能有一列为空
例: stu_name nvarchar(200) unique
--查找工资在1500~3000之间(包括)
例子:
select sal from emp
where sal>=1500 and sal<=3000 等价于 where sal between 1500 and 3000
in(属于若干个孤立的值)
select * from emp
where sal in(1500,3000,5000) 等价于 where sal=1500 or sal=3000 or sal=5000
top跟percent(百分比)
例:
select top 5 * from emp; --输出emp表的前5行
select top 15 percent * from emp --输出emp表总行数的15% 输出的是3个不是2
个。如果是个小数2.1那么就会取3
--把工资1500~3000之间(包括)的员工中工资最高的前4个人信息输出
例:
select top 4 sal from emp
where sal between 1500 and 3000
order by sal desc
--输出奖金非空的员工信息**
select comm from emp where comm <> null --输出为空 error
select comm form emp where comm != --输出为空 error
*总结上述:null不能参与<> 和!=和= 运算
null可以参与如下运算:is和not is
null不能参与任何数学运算,否则得出来的值为null
任何数字与null参与数学运算结果永远是null
任何类型的数据都允许为null
order by (已...排序) 升序(asc)为默认可以不写
例: *强烈建议为每一个字段都指定排序的标准
order by a,b --a,b都是升序
order by a,b desc --a升序,b降序
order by a desc,b --a降序,b升序
order by a desc,b desc --a,b都是降序
--模糊查询 **我们匹配的条件必须用单引号引起来
通配符:
% 表示任意0个或多个字符 详见如下
select *
from emp
where ename like '%A%' --ename只要含有字母A就输出
select *
from emp
where ename like 'A%' --ename只要首字母是A就输出
select *
from emp
where ename like '%A' --ename只要尾字母是A就输出
_ 表示任意的单个字符 详见如下
select *
from emp
where ename like '_A%' --ename只要第二个字符是A就输出
select *
from emp
where ename like '_[A-F]%' --ename只要第二个字符是A,B,C,D,E,F中任意一个就
输出
同上[A,F] 跟上面不同的是这个只有A,F两个限定
'_[^A-F]%' --ename中把第二个字符不是A,B,C,D,E,F的记录输出 ^ 取反的意
思
极端例子重点注意: escape单引号中内容可以是任意字符如果换成escape 'm'
即为: where name like '%m%%' escape 'm'
select *
from student
where name like '%\%%' escape '\' --把name中含有%的输出
where name like '%\_%' escape '\' --把name中含有_的输出
聚合函数 【多行记录返回一个值,通常用于统计分组的信息】
函数的分类
单行函数
每行返回一个值
多行函数
多行返回一个值
聚合函数是多行函数
例:select lower(ename) from emp; --最终返回的是14行 lower()是单行函数
lower 转换为小写字母
select max(sal) from emp; --返回是1行 max()是多行函数
聚合函数的分类
max()
min()
avg()
count(*) 求个数
返回值是表中所有记录个数
例: select count(*) from emp; --返回emp表的所有记录的个数
select count(deptno) from emp; --返回值是14 重复也会计算
select count(distinct deptno) from emp; --返回值是3
select count(comm) from emp; --返回4个值,null不会计算
注意的问题:
select max(sal),min(sal),count(*) from emp; --ok
select max(sal),lower(ename) from emo; --error 单行函数和多行函数不能混用
group by
--输出每个部门的编号 和 该部门的平均工资
例:
select deptno avg(sal) as "部门平均工资"
from emp
group by deptno
group by
格式: group by 字段的集合
功能: 把表中的记录按照字段分成不同的组
例子:如上
注意:group by a,b,c的用法
先按a分组,如果a相同,再按b分组,如果b相同,再按
c分组,最终统计的是最小分组的信息
一定要明白下列语句为什么是错误:
例1 select deptno,avg(sal) as "部门平均工资",ename
from emp
group by deptno 错误原因ename输出的是14行而分组后的输出是3行
例2 select deptno,ename
from emp
group by deptno 错误原因ename不包含在group by子句当中
例3 select deptno,job,sal
from emp
group by deptno,job 错误原因
记住:使用了group by 之后select 中只能出现分组后的整体信息,
不能出现组内的详细信息
having 【对分组之后的信息进行过滤】因此在使用having前都会使用group by
例:输出部门平均工资大于1500的部门编号 部门平均工资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000
例:把姓名不包含A的所有员工按部门的编号分组
统计输出部门平均工资大于2000的部门编号和部门平均工资
连接查询
定义:【将两个表或者两个以上的表以一定的连接条件连接起来,
从中检索出满足条件的数据】
分类
内连接
1: select...from A,B的用法
--emp14行8列 dept5行3列
select * from emp,dept
【输出的结果】:把A表每条记录都和B表每条记录匹配
即:行数为A,B的乘积
列数为A,B的之和
2: select...from A,B where...的用法
对
select... from A,B
产生的结果用where条件过滤详见自己emp表
3: select...from A jion B on ...的用法 --join是连接
例:--【下面输出的是11列70行】
select *
from emp "E"
join dept "D" --join是连接
on 1=1 --on连接条件
--【下面输出的是2列70行】
select "E".ename "员工姓名","D".dname "部门名称"
from emp "E"
join dept "D" --join是连接
on 1=1 --on连接条件 on不能省,有join就必须有
on
****连接的条件为1=1时候即条件为空
--【考虑下面语句的输出结果是什么?】答案:14行11列
--分析只有当E,D两表符合on字段限制的条件才会匹配
select "E".ename "员工姓名","D".dname "部门名称"
from emp "E"
join dept "D"
on "E".deptno="D".deptno
4:A: select...from A,B where... sql92标准
与
B: select...from A join B on... sql99标准
的比较
输出结果是一样
推荐使用SQL99标准 1:更容易理解
2:在sql99标准中,on和where可以做不同的分工
on指定连接条件
where对连接之后的临时表的数据进行过滤
--把工资大于2000的员工的姓名和部门的编号输出【为什么要推荐sql99下面详
细例子】
--sql92实现方式
select *
from emp E,dept D
where E.sal >2000 and E.deptno = D.deptno
--sql99实现方式
select *
from emp E join dept D
on E.deptno = D.deptno
where E.sal >2000
|
|