1.数据库学习数据库
(1)数据库存储数据:字段、记录、表、约束(主键、外键、唯一键、非空、default)
(2)数据库操作数据:insert、update、delete、T-SQL 存储过程、函数、触发器
(3)数据库显示数据:select(及其重要)
2.建立数据库
(1)打开服务
(2)打开连接
3.事务:作为单个逻辑工作单元执行的一系列操作。(一个逻辑工作单元要成为事务,要满足ACID(原子性、一致性、隔离性、持久性)属性)
4.表的相关数据
(1)字段、列、属性:模拟一个事务的某一个静态的特征
元组、记录:字段的组合,表示具体的事物
表:记录的组合,表示同一个类型的事务的集合
表和字段、记录的关系:字段是事务的属性,记录是事务本身,表示事务的集合
(2)create table 命令:
通过图形化界面建表
Create table 用的是()而不是{},且组后一句建议不加“,”因为oracle的不支持有“,”
用到的关键字constraint、references等……
(3)什么是约束
定义:对一个表中的属性操作的限制叫做约束
分类:
主键约束:不允许重复元素,避免数据的冗余
外键约束:通过外键约束从语法上保证所关联的其他事务一定存在(msdn,指定表中强制引用的完整性)
事务和事务之间的关系是通过外键来体现的
Check约束:保证事务属性的取值在合法范围内
Default约束:保证事务的属性一定有一个值(注意属性的个数)
唯一约束(unique):类似于主键,不允许事务属性取值重复,但是,它允许其中有且只有一列为null(但是oracle允许多列为null)
主键和唯一约束的区别在于:
not null:要求用户必须为该属性赋一个值,否则语法出错
(4)表和约束的区别:
数据库通过表解决数据的存储问题
数据库是通过约束来解决事务取值的范围和合法性问题
建表的过程就是指定事务属性及其属性各种约束的过程
(5)什么是关系:
通过外键建立起来的表之间的联系
分类():
一对一:
一对多:把A表的主键添加为B表的外键,那么一个A对应多个B。(即在多的一方添加外键)
多对多:
(6)主键:唯一的标识一个事务的一个字段或多个字段的组合
注:含有主键的表叫做主键表
主键通常是整数,不建议用字符串当主键(如果主键是用于集群式服务可以考虑用字符串)
一定不要用业务主键当主键,要用无关的代理主键作为主键
主键的值通常不允许修改,除非本记录被删除
主键不要定义成id,而要定义成表名Id或者表名_id
(7)外键:如果一个表的若干个字段是来自另外若干个表的主键或者唯一键,则这若干个字段就是外键。
注意:外键通常是来自外表的主键而不是唯一键,因为唯一键可能为null。
外键不一定来自另外表,也可能来自本表的主键
删除主键表和外键表的次序:先删外键表,后删主键表,否则会导致外键表中的数据引用出错。
(8)查询
1.计算列
select * from emp;
--*表示所有的
--from emp表示从emp表查询
select empno, ename from emp;
select ename, sal from emp;
select ename, sal*12 as '年薪' from emp;
--as可以省略;但是一定要记住:"年薪"不可写成‘年薪’(为了保证与oracle的兼容性)或年薪
select ename, sal*12 as "年薪", sal '月', job from emp;
select 888 form emp;
--ok
--输出的行数是emp表的函数,每行只有一个字段值是
select 5;
--ok
--不推荐的做法
2. distinct(不允许重复的)
select deptno from emp; --14行记录不是行记录
select distinct deptno from emp; --distince deptno 会过滤掉重复的deptno
select distinct comm from emp; --distinct也可以过滤掉重复的null 或者说如果有多个null 只输出一个
select distinct comm, deptno from emp;--把comm和deptno的组合进行过滤
select deptno, distinct comm from emp;--error 逻辑上有冲突
select 10000 from emp; --14行记录
3. Between
--查找工资在到之间(包括和)的所有的员工的信息
select * from emp
where sal>=1500 and sal<=3000
等价于
select * from emp
where sal between 1500 and 3000
--查找工资在小于或大于之间的所有的员工的信息
select * from emp
where sal<1500 or sal>3000
等价于
select * from emp
where sal not between 1500 and 3000
4.in(属于若干个孤立的值)
select * from emp where sal in (1500, 3000, 5000)
等价于
select * from emp
where sal=1500 or sal=3000 or sal=5000
select * from emp where sal not in (1500, 3000, 5000) --把sal既不是也不是也不是的记录输出
等价于
select * from emp
where sal<>1500 and sal<>3000 and sal<>5000
--数据库中不等于有两种表示: != <> 推荐使用第二种
--对或取反是并且 对并且取反是或
5.top(显示前n行记录)
select * from emp;
select top 5 * from emp;
select top 15 percent * from emp; --输出的是个,不是个
--把工资在到之间(包括和)的员工中工资最高的前个人的信息输出
select top 4 *
from emp
where sal between 1500 and 3000
order by sal desc --desc降序不写则默认是升序
6.null(没有值)
零和null不同,null表示空值,无值,零时一个确定的值
Null不参与< > 、!=、=这些运算
Null参与如下运算:is is not
select * from emp where comm != null; --输出为空error
select * from emp where comm = null;--输出为空error
--总结:null不能参与<> != =运算
--null可以参与is not is
select * from emp where comm is null; --输出奖金为空的员工的信息
select * from emp where comm is not null; --输出奖金不为空的员工的信息
--任何类型的数据都允许为null
create table t1 (name nvarchar(20), cnt int, riqi datetime);
insert into t1 values (null, null, null)
select * from t1;
select * from emp;
--输出每个员工的姓名年薪(包含了奖金) comm假设是一年的奖金
select empno, ename, sal*12+comm "年薪" from emp;
--本程序证明了:null不能参与任何数据运算否则结果永远为空
isnull(a, 0)如果a时null就返回0,否则返回a的值
select top 5 from emp;
select * from emp where comm is not null
select ename, sal*12+comm from emp;
select ename, sal*12+isnull(comm, 0) "年薪" from emp;
--isnull(comm, 0) 如果comm是null 就返回零否则返回comm的值
7.order by(以某字段为排序的依据)
--asc是升序的意思默认可以不写 desc是降序
select * from emp order by sal; --默认是按照升序排序
select * from emp order by deptno, sal; --先按照deptno升序排序,如果deptno相同,再按照sal升序排序
select * from emp order by deptno desc, sal;
--先按deptno降序排序如果deptno相同再按照sal升序排序
--记住sal是升序不是降序
--order by a desc, b, c, d desc只对a产生影响不会对后面的b c d 产生影响
select * from emp order by deptno, sal desc
--问题:desc是否会对deptno产生影响?
--答案:不会
--先按deptno升序,如果deptno相同,再按sal降序
8. 模糊查询
格式:
Select 字段的集合 from表名 where 某个字段的名字 like 匹配的条件
其中,匹配条件通常有通配符
通配符
% 表示任意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的就输出
[a-f] a到f中的任意单个字符 只能是a b c d e f中的任一个字符
select * from emp where ename like '_[A-F]%' --把ename中第二个字符是A或B或C或D或E或F的记录输出
[a, f] a或f
[^a-c] 不是a也不是b 也不是c的任意单个字符
select * from emp where ename like '_[^A-F]%' --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出
注意:
匹配的条件必须用单引号括起来 不能省略 不能用双引号
统配作为不同字符使用的问题(用转义符\)
create table student
(
name varchar(20) null
,age int
);
insert into student values ('张三', 88);
insert into student values ('Tom', 66);
insert into student values ('a_b', 22);
insert into student values ('c%d', 44);
insert into student values ('abc_fe', 56);
insert into student values ('haobin', 25);
insert into student values ('HaoBin', 88);
insert into student values ('c%', 66)
insert into student values ('long''s', 100)
select * from student;
select * from student where name like '%\%%' escape '\' --把name中包含有%的输出
select * from student where name like '%\_%' escape '\' --把name中包含有_的输出
9. 聚合函数
函数分类:
单行函数:每一行返回一个值
多行函数:多行返回一个值
例子:
select lower(ename) from emp; -- 最终返回的是行lower()是单行函数
select max(sal) from emp; --返回行max()是多行函数
聚合函数的分类
max()
min()
avg()
count()
select count(*) from emp; --返回emp表所有记录的个数
select count(deptno) from emp; --返回值是这说明deptno重复的记录也被当做有效的记录
select count(distinct deptno) from emp; --返回值是 统计deptno不重复且非null的记录的个数
select count(comm) from emp; --返回值是这说明comm为null的记录不会被当做有效的记录
select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp; --ok
select max(sal), lower(ename) from emp //有冲突,单行函数和多行函数不可以通用!
10.group by
use scott;
select * from emp;
--输出每个部门的编号和该部门的平均工资
select deptno, avg(sal) as "部门平均工资"
from emp
group by deptno
-- 判断下面语句是否正确
select deptno, avg(sal) as "部门平均工资", ename
from emp
group by deptno
-- 判断下面语句是否正确
select deptno, ename
from emp
group by deptno
总结: 使用了group by 之后 select 中只能出现分组后的整体信息,不能出现组内的详细信息
group by a, b 的用法
--error
select deptno , job, sal
from emp
group by deptno, job
--error
select *
from emp
group by deptno, job
--ok
select deptno , job, avg(sal)
from emp
group by deptno, job
--ok
select deptno , job, avg(sal) "平均工资", count(*) "部门人数", sum(sal) "部门的总工资", min(sal) "部门最低工资"
from emp
group by deptno, job
order by deptno
select comm, count(*)
from emp
group by comm
select max(sal) from emp; --默认把所有的信息当做一组
11.having(对分组以后的信息进行过滤)
--输出部门平均工资大于的部门的部门编号部门的平均工资
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000
--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
from emp
group by deptno
having avg(sal) > 2000
--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
from emp
group by deptno
having "平均工资" > 2000 --error
--判断下列sql语句是否正确
select deptno "部门编号", avg(sal) as "平均工资"
from emp
group by deptno
having deptno > 1
--判断下列sql语句是否正确
select deptno "部门编号", avg(sal) as "平均工资"
from emp
group by deptno
having "部门编号" > 1 --error
--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
from emp
group by deptno
having deptno > 10
--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
from emp
group by deptno
having count(*) > 3
--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
from emp
group by deptno
having ename like '%A%'
--把姓名不包含A的所有的员工按部门编号分组,
--统计输出部门平均工资大于的部门的部门编号部门的平均工资
select deptno, avg(sal) "平均工资"
from emp
where ename not like '%A%'
group by deptno
having avg(sal) > 2000
--把工资大于,
--统计输出部门平均工资大于的部门的部门编号部门的平均工资
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
from emp
where sal > 2000 --where是对原始的记录过滤
group by deptno
having avg(sal) > 3000 --对分组之后的记录过滤
--判断入选语句是否正确
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
from emp
group by deptno
having avg(sal) > 3000 --对分组之后的记录过滤
where sal > 2000 --where是对原始的记录过滤
总计: 所有select的参数的顺序是不允许变化的,否则编译时出错
select * from emp;
select count(*)
from emp
having avg(sal) > 1000
select ename, sal "工资"
from emp
where sal > 2000
select ename, sal "工资"
from emp
where "工资" > 2000
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
into emp_2
from emp
where sal > 2000 --where是对原始的记录过滤
group by deptno
having avg(sal) > 3000 --对分组之后的记录过滤
select * from emp_2
12.连接查询
定义
将两个或者两个以上的表以一定的连接条件连接起来,从中检索满足条件的数据。
分类
内连接(重点难点)
select "E".ename "员工姓名", "D".dname "部门名称"
from emp "E"
join dept "D"
on "E".deptno = "D".deptno
1.
select ... from A, B的用法
--emp是行8 列 dept是行3列
select * from emp, dept
2.
select ... from A, B where ... 的用法
--输出行11列
select *
from emp, dept
where empno = 7369
3.
select ... from A join B on ... 的用法
--输出列70行
select *
from emp "E"
join dept "D" --join是连接
on 1=1 --on连接条件
--输出列70行
select "E".ename "员工姓名", "D".dname "部门名称"
from emp "E"
join dept "D" --join是连接
on 1=1 --on连接条件 on不能省有join就必须有on
--判断下面语句是否正确
select deptno
from emp "E"
join dept "D" --join是连接
on 1=1
--判断下面语句是否正确
select "部门表".deptno "部门编号"
from emp "员工表"
join dept "部门表" --join是连接
on 1=1
--考虑下面语句的输出结果是什么
--答案:14行11列
select *
from emp "E"
join dept "D" --join是连接
on "E".deptno = "D".deptno
--考虑下面语句的输出结果是什么
--答案:行2列
select "E".ename "员工姓名", "D".dname "部门名称"
from emp "E"
join dept "D" --join是连接
on "E".deptno = "D".deptno
select *
from emp, dept
where emp.deptno = dept.deptno
等价于
select * from emp
join dept
on emp.deptno = dept.deptno
外连接
不但返回满足连接条件的所有记录,而且会返回部分不满足条件的记录
左外连接:
右外连接:
完全连接
交叉连接:select * from emp cross join dept 等价于 select * from emp, dept
自连接
自己和自己连接查询
用聚合函数 求薪水最高的员工的信息
select * from emp where sal = (select max(sal) from emp)
不准用聚合函数 求薪水最高的员工的信息
select * from emp
where empno not in (
select distinct "E1".empno
from emp "E1"
join emp "E2"
on "E1".sal< "E2".sal
)
联合
输出每个员工的姓名 工资 上司的姓名
select "E1".ename, "E1".sal, "E2".ename "上司的姓名"
from emp "E1"
join emp "E2"
on "E1".mgr = "E2".empno
union
select ename, sal, '已是最大老板' from emp where mgr is null
select * from emp;
13.嵌套查询
5.数据库中的字符串必须用‘’括起来,“”是用来表示某个数据或事务的名字时候用。