sql知识点总结(完整)

1、Sql 总结1.数据模型主要有:层次模型,网状模型,关系模型,2.数据库设计的步骤:需求分析,概念结构设计,逻辑结构设计,数据库物理设计,数据库实施,数据库运行和维护六个阶段。3.实体之间的关系:一对一、一对多、多对多。4.数据库文件主要有:主数据文件、次数据文件、日志文件其中次数据文件是可选的。-这是建库的过程if exists(select*from sysdatabases where name='tt'drop database ttcreate database tton(name=tt,filename='d:datatt.mdf',size=4mb

2、,maxsize=50mb,filegrowth=15%log on(name=tt1,filename='d:datatt1.ldf',size=5mb,maxsize=79mb,filegrowth=15%-这是对数据库的修改alter database ttmodify file(name=tt1,maxsize=89mb-增加日志文件alter database ttadd log file(name=oo,filename='d:dataoo.ldf',size=5mb,maxsize=79mb,filegrowth=15%-查看数据库sp_helpd

3、b tt5.重要的数据类型Int float char(size datetime varchar(size 6.在数据库中添加表use ttgoif exists(select*from sysobjects where name='t_li' drop table t_licreate table t_li(a char(4not null,b int not null,c datetimeinsert into t_li values('yy',78,2012-5-12insert into t_li (a,bvalues('ttf',89

4、select*from t_li-新建一个表,往表里添加t_li的数据create table t_ti1(a char(4not null,b int not nullinsert into t_ti1select a,b from t_li-这种方法不用重建select a,binto t_li2from t_liselect*from t_li26.使用union关键字插入多行数据-利用union一次插入多行数据insert into t_li (a,b,cselect'aa',55,2012-8-12 unionselect'cc',54,2032-5-

5、127.对数据表进行操作-对表的修改alter table t_lialter column a char(8select*from t_li-添加字段alter table t_liadd d char(9-删除字段alter table t_lidrop column d-表的查询select*from t_li8.对字段添加约束-添加主键约束应该注意是主键约束字段的值不能是重复的alter table t_liadd constraint pk_a primary key(a-添加外键约束alter table t_liadd constraint fr_b foreign key(br

6、eferences t_li4(b-添加唯一约束alter table t_liadd constraint t_li_uq unique(a-添加默认约束alter table t_liadd constraint t_li_df default(20for b-添加check约束alter table t_liadd constraint t_li_ck check(b between 0 and 50 -删除约束alter table t_lidrop constraint t_li_ck9.对于表的查询(单表查询select*from Customersselect c_ID,c_Na

7、me,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_Passwordfrom Customersselect会员的编号=c_ID,用户名=c_Name,c_TrueName as'真实名字',c_Password '

8、名字'from Customersselect*from Customerswhere 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=&#

9、39;VIP'-(将湖南的VIP客户记录显示出来。select*from Customers where c_Address='湖南株洲市'AND C_Type='VIP'-(将的客户记录显示出来。select*from Customerswhere c_Email like'%'-(将前%的客户记录显示出来。select top 10 percent*from Customers-(将姓刘的客户记录显示出来。select*from Customerswhere c_TrueName like'刘%'-(将客户按年龄进行降

10、序(由大到小排序。select year(getdate(-year(c_Birthas'c_Age'from Customersorder by year(getdate(-year(c_Birthdesc-(将客户按类型升序排序,如果类型相同按年龄的降序进行排序。select*from Customersorder by c_Type,year(getdate(-year(c_birthdesc10.对表中数据的操作-对表中数据的操作-修改表中的数据-把学号为的同学的名字改为xiaoxinupdate studentset name='xiaoxin'whe

11、re sno='01'-删除表中数据delete from studentwhere sno='01'如果要删除整个表中的数据,还可以使用Truncate table语句它相当于与一个没有where子句的delete语句。与delete相比,他在执行时使用的系统资源和事务日志更少,执行速度更快例如要将图书表中的所有数据删除。Truncate table booksTruncate table只能删除表中的数据行,不会删除表结构及各种约束。Truncate table 不能删除具有引用关系的数据表(引用关系是两个表的主关键字和外关键字的数据应对应一致,这属于_引用

12、_完整性11.sql语句的全称是structure query language12.要求一个人的年龄 year(getdate(-year(birth13.聚合函数-计算所有会员的积分之和。select sum(upointfrom customers-计算所有会员的平均积分。select avg(upointfrom customers-计算所有会员的最高积分。select max(upointfrom customers-计算所有会员的最低积分。select min(upointfrom customers-统计会员表中积分大于的会员个数。select count(*from custo

13、merswhere upoint>30014.分组select sex,count(sexas个数from customersgroup by sexselect city,sex,count(sexfrom customersgroup by city,sex having count(sex>2having与where的用法一样,但是having与group by 一块用15.内连接-查找某位同学的学号,姓名以及他的得分select student.sid,sname,scorefrom student inner join scoreon student.sid=score.

14、sidselect sc.sid,s.sname,sc.scorefrom student s inner join score scon s.sid=sc.sidselect sc.score,s.sname,sc.sidfrom score sc inner join student son s.sid=sc.sid-三个表的内连接select sc.score,s.sname,amefrom score sc inner join student son s.sid=sc.sidjoin course con c.cid=sc.cid-内连接需要进行条件筛选,直接在后面加where既可s

15、elect sc.score,s.sname,s.sgender,sc.cidfrom score sc inner join student son s.sid=sc.sidwhere s.sgender='男'-笛卡尔乘积(交叉连接select s.sname,sc.scorefrom student s,score sc-查询不满足条件的内连接(不等值连接结果集select sc.score,s.sname,sc.cidfrom score sc inner join student s on s.sid<>sc.sid-另一种内连接查询方法-两个表的内连接(

16、等值连接select sc.score,s.sname,sc.cidfrom score sc,student swhere s.sid=sc.sid-三个表的内连接(等值连接select sc.score,s.sname,amefrom score sc,student s,course cwhere s.sid=sc.sid and sc.cid=c.cidselect sc.score,s.sname,sc.cidfrom score sc,student swhere s.sid<>sc.sid16.外连接-左外连接select*from studentselect*fro

17、m scoreselect*from student s left outer join score sc on s.sid=sc.sid-右外连接select s.sname,sc.cid,sc.scorefrom student s right outer join score sc on s.sid=sc.sid-完全外连接select s.sname,sc.score,sc.cidfrom score sc full outer join student son s.sid=sc.sid-联合查询select*from testtable unionselect*from course

18、17.视图-可以创建一个“热点”商品的视图。-create view vw_HotGoodsasselect g_id as商品号,g_name as商品名称,t_id as类别号,g_price as价格,g_discount as折扣,g_number as数量from Goodswhere g_status='热点'-查看视图-select*from vw_HotGoods-查看生成视图代码-sp_helptext vw_HotGoods*【任务-2】需要了解所有订单所订购的商品信息(商品名称、购买价格和购买数量和订单日期,同时将创建的视图文本加密。*/create vi

19、ew vw_allorderswith encryptionasselect orders.o_id as订单号,o_date as订单日期,g_name as商品名称,d_price as购买价格,d_number as购买数量from ordersjoin orderdetailson orders.o_id=orderdetails.o_idjoin goodson orderdetails.g_id=goods.g_id-解密-alter view vw_allordersasselect orders.o_id as订单号,o_date as订单日期,g_name as商品名称,d_

20、price as购买价格,d_number as购买数量from ordersjoin orderdetailson orders.o_id=orderdetails.o_idjoin goodson orderdetails.g_id=goods.g_id18.存储过程create proc addernum1 int,num2 int,he int outputasselect he=num1+num2godeclare result intexec adder 20,30,result outputprint str(result,3-4创建一存储过程up_getdetailbyname

21、,通过数学参数学生姓名-(如“张然”,筛选出该学生的基本信息,对不存在此学生的输入值,-必须做一检测,打印信息“不存在此学生”。if exists(select*from sysobjects where name='up_getdetailbyname' drop proc up_getdetailbynamegocreate proc up_getdetailbynamesname char(10asif sname in(select姓名from studentselect*from studentwhere姓名=snameelseprint'不存在姓名为'

22、;+rtrim(ltrim(sname+'的同学!'up_getdetailbyname '李明'up_getdetailbyname '张然'19.触发器-向student表插入一条记录,查看inserted表和deleted表的变化create trigger tr_studenton studentfor insertasselect*from insertedselect*from deleted-更新student表中的一条记录,查看inserted表和deleted表的变化create trigger tr_student_updateon studentfor updateasselect*from insertedselect*from deletedcreate trigger tr_student_deleteon studentfor deleteasselect*from insertedselect*from deletedinsert into student values(&#