sql知识点总结.doc

Sql 总结
1.数据模型主要有:层次模型,网状模型,关系模型,
2.数据库设计的步骤:需求分析,概念结构设计,逻辑结构设
计,数据库物理设计,数据库实施,数据库运行和维护六个阶段。
3.实体之间的关系:一对一、一对多、多对多。
4.数据库文件主要有:主数据文件、次数据文件、日志文件
其中次数据文件是可选的。
--这是建库的过程
if exists(select*from sysdatabases where name='tt'
drop database tt
create database tt
on(
name=tt,
filename='d:\data\tt.mdf',
size=4mb,
maxsize=50mb,
filegrowth=15%
log on(
name=tt1,
filename='d:\data\tt1.ldf',
size=5mb,
maxsize=79mb,
filegrowth=15%
--这是对数据库的修改
alter database tt
modify file(
name=tt1,
maxsize=89mb
--增加日志文件
alter database tt
add log file(
name=oo,
filename='d:\data\oo.ldf',
size=5mb,
maxsize=79mb,
filegrowth=15%
----查看数据库
sp_helpdb tt
5.重要的数据类型
Int float char(size datetime varchar(size 6.在数据库中添加表
use tt
go
if exists(select*from sysobjects where name='t_li' drop table t_li
create table t_li
(
a char(4not null,
b int not null,
c datetime
insert into t_li values('yy',78,2012-5-12
insert into t_li (a,bvalues('ttf',89
select*from t_li
--新建一个表,往表里添加t_li的数据
create table t_ti1(
a char(4not null,
b int not null
insert into t_ti1
select a,b from t_li
---这种方法不用重建
select a,b
into t_li2
from t_li
select*from t_li2
6.使用union关键字插入多行数据
---利用union一次插入多行数据
insert into t_li (a,b,c
select'aa',55,2012-8-12 union
select'cc',54,2032-5-12
7.对数据表进行操作
---对表的修改
alter table t_li
alter column a char(8
select*from t_li
--添加字段
alter table t_li
add d char(9
--删除字段
alter table t_li
drop column d
--表的查询
select*from t_li
8.对字段添加约束
---添加主键约束应该注意是主键约束字段的值不能是重复的alter table t_li
add constraint pk_a primary key(a
---添加外键约束
alter table t_li
add constraint fr_b foreign key(b
references t_li4(b
--添加唯一约束
alter table t_li
add constraint t_li_uq unique(a
---添加默认约束
alter table t_li
add constraint t_li_df default(20for b
--添加check约束
alter table t_li
add constraint t_li_ck check(b between 0 and 50 ---删除约束
alter table t_li
drop constraint t_li_ck
9.对于表的查询(单表查询
select*from Customers
select c_ID,c_Name,c_TrueName,c_Password from Customers
-----(查询WebShop数据库中会员信息表Customers中会员的编号(c_ID、
-----用户名(c_Name、真实姓名(c_TrueName、年龄(c_Age和密码(c_Password。select c_ID,c_Name, c_Truename,year(getdate(-year(c_Birth
'c_Age',c_Password
from Customers
select会员的编号=c_ID,用户名=c_Name,c_TrueName as'真实名字',c_Password '名字'from Customers
select*from Customers
where c_Type='VIP'
--6将VIP客户的编号(c_id、姓名(c_name、出生日期(c_birth、籍贯(c_address、----
----联系电话(c_phone和地址(c_email显示出来并以汉字标题显示列名。
select编号=c_id,姓名=c_name, c_birth as'出生年月', c_address 籍贯,
c_phone as'联系电话', c_email '地址'from Customers where c_Type='VIP'
----(将湖南的VIP客户记录显示出来。
select*from Customers where c_Address='湖南株洲市'AND C_Type='VIP'
-----(将的客户记录显示出来。
select*from Customers
where c_Email like'%'
----(将前%的客户记录显示出来。
select top 10 percent*from Customers
----(将姓刘的客户记录显示出来。
select*from Customers
where c_TrueName like'刘%'
-----(将客户按年龄进行降序(由大到小排序。
select year(getdate(-year(c_Birthas'c_Age'from Customers
order by year(getdate(-year(c_Birthdesc
----(将客户按类型升序排序,如果类型相同按年龄的降序进行排序。
select*from Customers
order by c_Type,year(getdate(-year(c_birthdesc
10.对表中数据的操作
---对表中数据的操作
---修改表中的数据
---把学号为的同学的名字改为xiaoxin
update student
set name='xiaoxin'
where sno='01'
---删除表中数据
delete from student
where sno='01'
如果要删除整个表中的数据,还可以使用Truncate table语句它相当于与一个没有where子句的delete语句。与delete相比,他在执行时使用的系统资源和事务日志更少,执行速度更快
例如要将图书表中的所有数据删除。
Truncate table books
Truncate table只能删除表中的数据行,不会删除表结构及各种约束。Truncate table 不能删除具有引用关系的数据表
(引用关系是两个表的主关键字和外关键字的数据应对应一致,这属于_____引用___完整性
11.sql语句的全称是structure query language
12.要求一个人的年龄 year(getdate(-year(birth
13.聚合函数
--计算所有会员的积分之和。
select sum(upoint
from customers
--计算所有会员的平均积分。
select avg(upoint
from customers
--计算所有会员的最高积分。
select max(upoint
from customers
--计算所有会员的最低积分。
select min(upoint
from customers
--统计会员表中积分大于的会员个数。
select count(*
from customers
where upoint>300
14.分组
select sex,count(sexas个数
from customers
group by sex
select city,sex,count(sex
from customers
group by city,sex having count(sex>2
having与where的用法一样,但是having与group by 一块用15.内连接
--查找某位同学的学号,姓名以及他的得分
select student.sid,sname,score
from student inner join score
on student.sid=score.sid
select sc.sid,s.sname,sc.score
from student s inner join score sc
on s.sid=sc.sid
select sc.score,s.sname,sc.sid
from score sc inner join student s
on s.sid=sc.sid
----三个表的内连接
select sc.score,s.sname,ame
from score sc inner join student s
on s.sid=sc.sid
join course c
on c.cid=sc.cid
--内连接需要进行条件筛选,直接在后面加where既可
select sc.score,s.sname,s.sgender,sc.cid
from score sc inner join student s
on s.sid=sc.sid
where s.sgender='男'
--笛卡尔乘积(交叉连接
select s.sname,sc.score
from student s,score sc
--查询不满足条件的内连接(不等值连接结果集select sc.score,s.sname,sc.cid
from score sc inner join student s on s.sid<>sc.sid
--另一种内连接查询方法
--两个表的内连接(等值连接
select sc.score,s.sname,sc.cid
from score sc,student s
where s.sid=sc.sid
--三个表的内连接(等值连接
select sc.score,s.sname,ame
from score sc,student s,course c
where s.sid=sc.sid and sc.cid=c.cid
select sc.score,s.sname,sc.cid
from score sc,student s
where s.sid<>sc.sid
16.外连接
-----左外连接
select*from student
select*from score
select*
from student s left outer join score sc on s.sid=sc.sid
--右外连接
select s.sname,sc.cid,sc.score
from student s right outer join score sc on s.sid=sc.sid
--完全外连接
select s.sname,sc.score,sc.cid
from score sc full outer join student s
on s.sid=sc.sid
--联合查询
select*from testtable union
select*from course
17.视图
--可以创建一个“热点”商品的视图。---
create view vw_HotGoods
as
select g_id as商品号,g_name as商品名称,t_id as类别号,g_price as价
格,g_discount as折扣,g_number as数量
from Goods
where g_status='热点'
--查看视图--
select*from vw_HotGoods
--查看生成视图代码--
sp_helptext vw_HotGoods
*【任务-2】需要了解所有订单所订购的商品信息(商品名称、购买价格和购买数量和订单日期,
同时将创建的视图文本加密。*/
create view vw_allorders
with encryption
as
select orders.o_id as订单号,o_date as订单日期,g_name as商品名称,d_price as购买价格,d_number as购买数量
from orders
join orderdetails
on orders.o_id=orderdetails.o_id
join goods
on orderdetails.g_id=goods.g_id
--解密--
alter view vw_allorders
as
select orders.o_id as订单号,o_date as订单日期,g_name as商品名称,d_price as购买价格,d_number as购买数量
from orders
join orderdetails
on orders.o_id=orderdetails.o_id
join goods
on orderdetails.g_id=goods.g_id
18.存储过程
create proc adder
@num1 int,
@num2 int,
@he int output
as
select @he=@num1+@num2
go
declare @result int
exec adder 20,30,@result output
print str(@result,3
--4创建一存储过程up_getdetailbyname,通过数学参数学生姓名
--(如“张然”,筛选出该学生的基本信息,对不存在此学生的输入值,
--必须做一检测,打印信息“不存在此学生”。
if exists(select*from sysobjects where name='up_getdetailbyname' drop proc up_getdetailbyname
go
create proc up_getdetailbyname
@sname char(10
as
if @sname in(select姓名from student
select*
from student
where姓名=@sname
else
print'不存在姓名为'+rtrim(ltrim(@sname+'的同学!'
up_getdetailbyname '李明'
up_getdetailbyname '张然'
19.触发器
--向student表插入一条记录,查看inserted表和deleted表的变化
create trigger tr_student
on student
for insert
as
select*from inserted
select*from deleted
--更新student表中的一条记录,查看inserted表和deleted表的变化
create trigger tr_student_update
on student
for update
as
select*from inserted
select*from deleted
create trigger tr_student_delete
on student
for delete
as
select*from inserted
select*from deleted
insert into student values('005','bb','男'
--禁用/启用触发器
disable trigger tr_student_delete
on student
enable trigger tr_student_delete
on student
---库级别触发器----------------------
create trigger tr_altertable
on database
for alter_table
as
begin
print'数据表已被修改'
end
20.索引
create clustered index idx_usersname
on Users(u_Name
--删除索引
drop index Users.PK__Users__1273C1CD
--【任务-2】在Users表的u_Name列上创建唯一的非聚集索引。-- create unique nonclustered index idx_UsersName
on users(u_name
--【任务-3】在OrderDetails表的o_ID列和g_ID列上创建复合非聚集索引。 create nonclustered index idx_OID_GID on OrderDetails(o_ID,g_ID --【任务-4】查看Goods表的索引。 sp_helpindex Users sp_helpindex OrderDetails sp_helpindex Goods 20.要判断视图，存储过程，和 触发器 if exists(select * from sysobjects where name='up_getdetailbyname' drop proc up_getdetailbyname go 做题时要记得先判断 21．延伸 11

19