数据库学习笔记.doc

1.数据库的基本概念
1.1数据库的基本结构
基本结构分三个层次
物理数据层，概念数据层，逻辑数据层
1.2数据库的基本特点
1.3目前主流数据库
微软： sql server和access
瑞典MySQL AB公司的mysql
Ibm公司 db2
Ibm公司 Informix
美国oracle公司 oracle
美国Sybase公司 Sybase

一流的公司做标准，二流的公司做服务，三流的公司做产品，四流的公司做项目
1.4简单的比较
小型数据库：access
中型数据库：
大型数据库：oracle，sql server，sysbase，informix（安全性好，一般银行的数据库）
巨型数据库：db2
1.5数据库的选择
1. 成本
2. 实现的功能的多少
3. 并发性（最终用户）
4. 安全性
2.sql server开发工具
备注：
如果是安装在win2000上的话，安装就ok；
如果是装在winxp上的话，需要安装补丁SQL2KSP4;
①安装sql server，可能会遇到的错误提示
已经有一个实例被挂起状态，需要重启
解决方法：
进入到注册表
regedit
local_machine->system->currentControlset->contro->session manager
把键值删除
2.1两种开发工具
sql server2000提供了两种开发工具
1. 企业管理器
企业管理器是微软提供的图形界面方式操作sql server2000的工具
2. 查询分析器
查询分析器是微软提供的用命令行（sql语句）操作sql server2000的工具；
2.2企业管理器的使用
sa ：super administrator
1. 管理员sa的介绍
2. 如何修改sa密码
2.3查询分析器的使用
sql语句包括4个部分：
数据定义语言（DDL）：例如：create，drop，alter等语句；
数据操作语言（DML）：例如：insert，update，delete语句；
数据查询语言（DQL）：例如：select语句；
数据控制语句（DCL）：例如：grant，revoke，commit，rollback等。
3.表的管理
3.1表的命名和列的命名规则
① 表名以字母和下划线开头
② 长度不能超过128个字符；
③ 不要使用sql server的保留字
④ 只能使用A-Z，a-z，0-9，$，_，#等
3.2支持的数据类型
① 字符型
char
varchar
ntext
text
nchar
nvarchar 最大4000个字符
备注：一般带有汉子的段用nvarchar，全英文或者符号的用varchar，因为nvarchar无论是单个字母还是单个汉子都占有两个字节，而varchar字母占一个，汉字占两个；
② 数字型
bit
int
bigint
float 存放小数，但不推荐使用
numeric 小数
小数的时候推荐使用numeric
③ 日期类型
datatime 表示日期
timestamp 时间戳
一般情况下，用datatime表示日期
④ 图片 image保存图片，但是用的很少，一般用路径存放
⑤ 视频 binary字段可以存放，但是往往将视频文件保存在文件服务器上，在sqlserver中值保留文件路径，存取效率高
3.3建表（公司管理系统）
-----职员表
Sql server 建议表的设计者，最好给表定义一个主键，用来标示唯一的一条记录
① 主键（primary key），不能够重复出现，
② 而且必须给值，换句话说主键不允许为null
③ 主键是可以修改的
--创建表dept
create table dept
(deptNo int primary key,
dName nvarchar(30),
loc nvarchar(30)
)
--创建表emp
create table emp
(empNo int,
eName nvarchar(30),
job nvarchar(30),
mgr int,
hireDate datetime,
sal numeric(8,2),
comm numeric(8,2),
deptNo int foreign key references dept(deptNo)
)
--根据需要把deptNo做成外键（foreign key）
--外键只能指向主键，并且要和主键的数据类型要一致
3.4插入字段
插入部分字段
语法：
insert into 表名 （字段列表）values (列表)
3.5修改字段

3.6基本查询
3.6.1查询所有列
语法：
Select * from emp（表名） where 条件
3.6.2查询指定列
语法：
Select 字段1，字段2 from 表名 where 条件
如何查询SMITH的薪水，工作，所在部门编号
select ename,sal,job,deptNo from emp where ename='Smith'
3.6.2如何取消重复行（distinct只能消除完全一样的行，保留一行）
语法：
Select distinct 字段 from 表名 where 条件
如何统计总共有多少部门
select distinct deptno from emp
3.6.3使用算术表达式
如何显示每个雇员的年工资？
select ename,sal*13年薪 from emp
3.6.4使用列的别名
select ename,'年薪'=sal*13 from emp
select ename,sal*13'年薪' from emp
select ename as 姓名 ,sal*13 as 年薪 from emp
注：comm有空值；
select ename ,sal+isnull(comm,0) from emp
3.6.4使用where字句
① 如何显示工资高于3000的员工
select ename ,sal from emp where sal>3000
② 如何查找1981.1.1后入职的员工
select ename,hireDate from emp where hireDate>'1981-1-1'
③ 如何显示工资在2000到3500的员工情况
select ename,sal from emp where sal between 2000 and 3500

3.6.5如何使用like操作符（模糊查询）
备注：
% 表示0到多个字符
_ 表示单个字符
如何显示首字符为S的员工姓名和工资
select ename,sal from emp where ename like 'S%'
如何显示第三个字符为大写O的所有员工的姓名和工资
select ename , sal from emp where ename like '__o%'
3.6.6在where条件中使用in
如何显示empno为123，345，800.。。。。的雇员情况
select ename,empno from emp where empno=7369 or empno=7782
select ename,empno from emp where empno in(7369,7782)
3.6.7使用is null 的操作符
如何显示没有上级的雇员的情况
select * from emp where mgr is null
3.6.8使用逻辑符号
查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为j
select * from emp where (sal>500 or job='manager') and ename='j%'
3.6.9使用order by语句
order by 默认是升序排列
order by desc 降序排列
order by asc 升序排列
order by 可以根据不同的字段进行不同的排序
① 如何按照工资的从低到高的顺序显示雇员的信息
select * from emp order by sal asc
select * from emp order by sal desc
② 如何按照部门号升序而且雇员的工资降序排列
select * from emp order by deptno , sal desc
3.6.10使用别名排序
如何统计每个人的年薪，并按照从低到高的顺序排列？
select ename ,sal*13年薪 from emp order by 年薪
select ename,(sal + isnull(comm,0))*13 年薪 from emp order by 年薪
分页查询
按雇员的id号升序取出
3.7表的复杂查询
3.7.1说明
在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍比较复杂的select语句
3.7.2数据分组-----max，min，avg，sum，count
如何显示所有员工的最高工资和最低工资
select min(sal) from emp
select max(sal) from emp
select min(sal) ,max(sal) from emp
如何显示最低工资和该雇员的姓名
select ename,sal from emp where sal=(select min(sal) from emp)
显示所有员工的平均工资和工资总和
select avg(sal) 平均工资,sum(sal) 总工资 from emp
把高于平均工资的雇员的姓名和工资显示出来
select ename,sal from emp where sal>(select avg(sal) from emp)
计算共有多少员工
select count(*) from emp
显示工资最高的员工的姓名和工资
select ename,sal from emp where sal=(select max(sal) from emp)

3.7.3group by 和having
having 往往和group by 结合使用，可以对分组查询进行筛选
group by 用于对查询结果的分组统计
having 字句用于限制分组显示结果
显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno
显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,job
order by deptno
显示平均工资低于2000的部门号和它的平均工资
select avg(sal),deptno from emp group by deptno having avg(sal)<2000
显示平均工资高于2000的部门号和它的平均工资，并且从低到高排列
select avg(sal),deptno from emp group by deptno having avg(sal)>2000
order by avg(sal)

3.7.4对数据分组的总结
① 分组函数只能出现在选择列表、having、order by 子句中；
② 如果在select 语句中同时包含group by ，having ，order by 那么他们的顺序只能是group by ，having ，order by；
③ 在学则列中如果有列、表达式、和分组函数，那么这些列和表达式必须出现一个在group by语句中，否则会出错；
3.7.5多表查询

显示sales部门位置和其员工姓名
select dname,loc,ename from emp,dept
where dname='sales' and emp.deptno=dept.deptno
显示雇员名，雇员工资及所在部门的名字
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno
显示部门号为10的部门名称，员工名和工资
select dname,ename,sal from emp,dept
where emp.deptno=10 and emp.deptno=dept.deptno
显示雇员名，雇员工资及所在部门的名字，并按部门排序
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno
order by dname

3.7.6自连接

3.7.7子查询

3.7.8分页查询

3.7.9如何处理重复的记录
把cat的记录 distict 后的结果，放入到#temp3

把cat的记录 清空

把#temp3表的数据（没有重复的记录），插入cat 表

把#temp3表的数据（没有重复的记录），插入cat 表

删除 #temp3 表

3.7.10左外连接，右外连接

3.8维护数据的完整性
3.8.1约束

3.8.2使用

3.8.3案例

3.8.4修改表

4.Sql server 数据库的备份和修复
4.1使用企业管理器完成备份和修复

4.2使用查询分析器完成备份和修复

5.Java程序中如何操作sql server

5.1使用jdbc-odbc方式操作sql server数据库
1. 配置数据源（控制面板－管理工具－数据源）
2. 在程序中去连接数据源：
套路：
1.加载驱动
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
2.得到连接（mytest是刚刚建立的数据源）
Connection ct=DriverManager.getConnection
(“jdbc:odbc:mytest”, “sa”, “sa”);
3.创建Statement或者PreparedStatement（用处：发送sql语句）；
Statement sm = ct.createStatement();
4.执行crud
备注：exectueUpdate可以执行cud操作（添加，删除，修改）
如果是查询使用executQuery();

注意：关闭资源！！

继续演示：

查询：
resultSet 结果集，

1. preparedStatement可以提高执行效率（因为他可以预编译）；
2. preparedStatement可以防止sql注入，但是要求用赋值的方式；

5.2使用jdbc来操作数据库
Jdbc方式去操作数据库
1. 引入包java.Sql.*;
2. 引入三个jar包；
3. 套路：
1.加载驱动
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
2.得到连接（mytest是刚刚建立的数据源）
Connection ct=DriverManager.getConnection
(“jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=spdb1”, “sa”, “sa”);
3.创建Statement或者PreparedStatement（用处：发送sql语句）；
Statement sm = ct.createStatement();
PreparedStatement ps = ct.prepareStatment(“ sql 语句 ”);
4. 执行；
备注：exectueUpdate可以执行cud操作（添加，删除，修改）
如果是查询使用executQuery();

5.3 Statement和preparedStatement的区别

5.4在java程序中操作sql server数据库表

5. 实例
1.梁山heros（表的简单操作）
--创建数据库
create database liangshanheros
--创建表
use liangshanheros--使用某个数据库
go
create table hero
(heroid int,--排名
heroname varchar(50),--姓名
heronickname varchar(50),--外号
sex char(2)
)
--使用sql语句添加数据
insert into hero values(1,'宋江','及时雨','男')
insert into hero values(2,'卢俊义','玉麒麟','男')
insert into hero values(3,'吴用','智多星','男')
insert into hero values(4,'公孙胜','入云龙','男')
--使用sql语句来查询数据
select * from hero

2.梁山heros（表的简单操作）
--创建数据库
create database liangshanheros

--创建表
use liangshanheros--使用某个数据库
go
create table hero
(heroid int,--排名
heroname varchar(50),--姓名
heronickname varchar(50),--外号
sex char(2),
sal int
)

--使用sql语句添加数据
insert into hero values(1,'宋江','及时雨','男',2000)
insert into hero values(2,'卢俊义','玉麒麟','男',2300)
insert into hero values(3,'吴用','智多星','男',1500)
insert into hero values(4,'公孙胜','入云龙','男',1500)

--删除一张表（表的结构和表的数据一起删掉）
drop table hero

--使用sql语句来查询数据
select * from hero

--①查询工资低于1600的同志
select * from hero where sal<1600

--②把工资低于1600的人的工资提高10%（update）
--语法：
--update 表名 set 字段名1=？，字段名2=？ where 条件
update hero set sal=sal*1.1 where sal<1600

--③请删除性别为女的同志
delete from hero where sex='女'