1.默认约束2.主键约束，（唯一约束+非空约束），设置了主键的字段在赋值的时候不能重复，stuID为主键则1，下一个就不能为13.唯一约束（允许一行为空），4.check约束5.主外建约束 （主表 子表）一对多
添加完整性约束外键约束：学生基本信息表（主表）的学生编号是主键考试成绩表的学生（子表）的编号是外键一个表在不同的关系中既可以是主表又可以是子表主外建是一对多
”数据字典“
哪一个是外键表，就给他添加外建约束3个一致：1 外键表外键与主键数据类型一致（语法一只） 2 长度：外键>=主键表；长了也没有意义 3 名称一致。
如果sql语句写的错误很多，只需要看第一行和最后一行错误。如果不多，就仔细看下。
通配符----
select * from employee-- 插入一条忘记加入的字段 直接在表的后面添上add--alter table employee add emName varchar(20) not nullalter table employeeadd constraint PK_emId primary key (emId)alter table employeeadd constraint CK_age CHECK(age between 20 and 55)alter table employeeadd constraint DF_address default('地址不详') for addressalter table employeeadd constraint UQ_emName unique (emName)--外键表alter table employeeadd constraint FK_emtypeId foreign key(emtypeId) references mangertype(mantypeId)标示列可以不是主键，种子和自增量必须是int，标示列不能编辑先自增，再验证，最后插入在数据关系图里 带钥匙的一方是主键表
-----------------------插入（insert）1.匹配，2.多行数据备份表 select * into student1 from student 就生成一个新的student1
select * into student1 from student select * from student1--将字段名备份，但是所建的约束消失select stuNoid,stuName,sex,age,address into student2 from studentselect * from student2--将字段名更改，加as即可select stuNoid as haha,stuName as gaga,sex as hello,age,address into student3 from studentselect * from student3select * from student1use wangwanggocreate table employee( empId int identity primary key, empName varchar(20) not null, sex bit not null, age int , address varchar(20)
)goselect * from employee---接下来将student1表中的数值插入到employee中/*insert into employee (empId,empName,sex,age,address)select (stuNoId,stuName,sex,age,address) from stdent1 where age>20 */ ----插入多行数据union方法 insert student1 (stuNoId,stuName,sex,age,address) select--不支持default关键字要用‘’括起来，当两行数据一致时，默认为一行 '1','liuzhao','1','31','default' union select '1','liu','1','28','default' union select '1','zhao','1','31','default' select * from student1 select * from student1 order by age desc（asc） ------------------------------模糊查询alter table student add groupId int select * from studentinsert student(stuNoId,stuName,sex,age,address)select '5','zhaoliu','1','30','南昌路' union select '6','zhao','1','31','南路' union select '7','liu','1','32','昌路' ---模糊查询 select * from student where address like '%南%' select * from student where stuName like 'wangwu[2-4]' select * from student where stuName like 'wangwu[^2-4]%'--取反 --加上% 可以将wangwua查出来 select * from student where stuName like 'wangwu[^2-4]%' ---- use wangwang go create table person ( perId int identity primary key, personId int not null--身份证 ) go --给身份证添加约束，假如身份证五位数字 /*select * from person alter table person add constraint CK_personId CHECK(personId like [0-4],[0-4],[0-4],[0-4],[0-4]) go*/--更改update student set age=age+1 select * from student where groupId=1update student set age=age+2,stuName=stuName+'mm' where groupId=1--删除delete from student where groupId=1select * from student
delete from student-- 删除表truncate table student--删除表（效率快 ）------------------------------------SQL 结构化查询语言T-Sql是SQL的加强版-----------------------------查询：1.基础查询2.聚合函数3.链接查询4.子查询--更改update student set age=age+1 select * from student where groupId=1update student set age=age+2,stuName=stuName+'mm' where groupId=1--删除delete from student where groupId=1select * from student
delete from student-- 删除表truncate table student--删除表（效率快 ）-------------------------------查询use wangwang goselect * from student where address='南昌路'/*select colname1 as 别名, colname2 as 别名, from tableName where 条件 group by 分组 having 分组后的筛选 order by 排序 */--起别名
select *,'姓名'=stuName from studentselect *,stuName as 'as用法' from student--给表起名select *,stuName as 'as用法' from student as Stualter table student add haha varchar(20)select * from student update student set haha='mm' where age>31select * from student where haha is null--string a=null与a=''是不一样的 null与空select * from student where haha is nullselect * from student where haha=''select * from student order by stuNoId asc-------------------分页处理，关键字--top--percentselect top 5 * from student--每页出现5行select top 50 percent * from student--每页出现总体的百分之n，进1法--排序select * from student order by age desc--降序号select * from student order by stuNoId asc--升序-----函数。select 'aaa' as '列明'--查询an在后面的英语中出现的序列select CHARINDEX('an','my name is wangwang')--查询an在后面的英语中出现的序列,前面14个不算，从第15开始查找select CHARINDEX('an','my name is wangwang',14)-----查询长度select LEN('123456')select address,LEN(address) as ' cahngdu' from student-----------ltrim()可取空格select * from student where ltrim(address)=' 天津路'--左空格select * from student where ltrim(ltrim(address))=' 天津路'--俩边空各---截取字符串rightselect RIGHT('sgdsggueui',4) as '截取后显示'select left('sgdsggueui',4) as '截取后显示'----------替换select replace('sfsfhufhsh','sh','AA')--将sh换成AAselect replace('sfsfhufhsh','s','9')--将sh换成AA------------日期函数select GETDATE()--得到当前日期select DATEADD(DD,10000,'93-07-22')--活一万天 select DATENAME(DW,GETDATE())--今天星期几 select DATENAME(WW,GETDATE())--今天是今年的第几周select * from studentselect convert(varchar(2),age)+address as '新的列明' from studentselect age + convert( int,address) as '新的列明' from student--有错误
---------------------------------------------------案例1create table card( cId int identity primary key, CaId varchar(20) not null)goinsert card values ('fifuejf789io0i658')
select * from cardupdate card set CaId = replace(CaId,'i','1')
--update replace(CaId,'i','1') from card
select REPLACE(replace(CaId,'i','1'),'o','0') from card select * into card2 from card select * from card2 update card2 set card=REPLACE(REPLACE(CaId,'i','1'),'o','0')----------------------------------------------------------------------SQL编程变量分为:1.局部变量，以@符号前缀，先声明再赋值 赋值：set @变量名=值 select @变量名=值 set @name=zhangsan select studentNo from student where studentName=@name 李文才： declare @name varchar(8) set @name='李文才' select @No=studentNo from student where studentName=@name declare @No int//定义 print'学号'+@No 查询出学号+-1的相邻的同学。 select * from student where student.studentNo=@No+1 or student.studentNo=@No-1
----------------------------------------------------insert into Class(GradeId,ClassID) values (4,'201')
@@IDENTITY-------------------------------DECLARE @TAG VARCHAR(1)SET @TAG='*'PRINT @TAG+@TAGPRINT @TAG+@TAG+@TAGPRINT @TAG+@TAG+@TAG+@TAG------DECLARE @xuehao int set @xuehao =20011 select * from student where studentNo=@xuehao//学生记录查出来
declare @year int set @year=DateDiff(DY,'1996-8-7',getdate())/365;年份 select * from student where student.brondate=@year+1 or student.brondate=@year-1;---------------------------------------create database MySchoolon primary(name = 'MySchool_data',filename = 'D:\project\MySchool_data',size = 10mb,maxsize = 100mb,filegrowth=15%)log on(name = 'MySchool_log',filename = 'D:\project\MySchool_log',size = 3mb,maxsize = 20mb,filegrowth=1mb)
if exists(select * from sysobjects where name = 'Student')drop table studentcreate table dbo.Student(StudentNo int NOT NULL,LoginPwd nvarchar(50) NOT NULL,StudentName nvarchar(50) NOT NULL,sex bit NOT NULL, GradeId int NOT NULL,Phone nvarchar(50) NULL,Address nvarchar(255) NULL,BornDate datetime NOT NULL,Email nvarchar(50) NULL,IdentityCard varchar(18) NOT NULL)
if exists(select * from sysobjects where name = 'Subject')drop table subjectcreate table dbo.Subject(SubjectNO int IDENTITY(1,1) NOT NULL,SubjectName nchar(50) NOT NULL,ClassHour int NOT NULL,GradeId int NOT NULL)
if exists(select * from sysobjects WHERE name = 'Result')drop table Result create table dbo.Result(StudentNo int NOT NULL,SubjectNo int NOT NULL,StudentResult int NOT NULL,ExamDate datetime NOT NULL)
if exists(select * from sysobjects where name = 'Grade')drop table Gradecreate table Grade(GradeId int IDENTITY(1,1) NOT NULL,GradeName nvarchar(50) NOT NULL)
--向Grade表插入数据INSERT INTO Grade VALUES('S1')--向Subject表插入数据INSERT INTO Subject VALUES('Winforms',20,1)
--向Student表插入数据INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)
--向Result表插入数据INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')
----------------------------------------------------------------------USE master --设置当前数据库为master，以便访问sysdatabases表 GO IF EXISTS(SELECT * FROM sysdatabases WHERE name ='MySchool') DROP DATABASE MySchool GO CREATE DATABASE MySchool ON ( name='MySchool_data', FILENAME='F:\project\MySchool_data.mdf', SIZE=10mb, --主数据文件的初始大小 MAXSIZE=100mb, --主数据文件增长的最大值 FILEGROWTH=15% --主数据文件的增长率 ) LOG ON ( /*--日志文件的具体描述，各参数含义同上--*/ NAME='MySchool_log', FILENAME='F:\project\MySchool_log.ldf', SIZE=3mb, MAXSIZE=20mb, --日志文件增长的最大值 FILEGROWTH=1mb ) GO
/*--查询得到Student表的信息--*/USE MySchoolGO
SELECT * FROM sysobjects
/*--案例：使用SQL语句删除表Student学生--*/IF EXISTS(SELECT * FROM sysobjects WHERE name='Student') DROP TABLE Student /*--学时2--*//*--案例：使用SQL语句创建Student表--*/CREATE TABLE [dbo].[Student]( [StudentNo] [int] NOT NULL, [LoginPwd] [nvarchar](50) NOT NULL, [StudentName] [nvarchar](50) NOT NULL, [Sex] bit NOT NULL, [GradeId] [int] NOT NULL, [Phone] [nvarchar](50) NULL, [Address] [nvarchar](255) NULL, [BornDate] [datetime] NOT NULL, [Email] [nvarchar](50) NULL, [IdentityCard] [varchar](18) NOT NULL ) GO
/*--案例：使用SQL语句创建Subject科目表--*/IF EXISTS(SELECT * FROM sysobjects WHERE name='Subject') DROP TABLE SubjectCREATE TABLE [dbo].[Subject]( [SubjectNo] [int] IDENTITY(1,1) NOT NULL, [SubjectName] [nchar](50) NOT NULL, [ClassHour] [int] NOT NULL, [GradeId] [int] NOT NULL) GO/*--案例：使用SQL语句创建Result结果表--*/IF EXISTS(SELECT * FROM sysobjects WHERE name='Result') DROP TABLE ResultResultCREATE TABLE [dbo].[Result]( [StudentNo] [int] NOT NULL, [SubjectNo] [int] NOT NULL, [StudentResult] [int] NOT NULL, [ExamDate] [datetime] NOT NULL) GO/*--案例：使用SQL脚本创建Student学生表--*/IF EXISTS(SELECT * FROM sysobjects WHERE name='Student') DROP TABLE StudentCREATE TABLE [dbo].[Student]( [StudentNo] [int] NOT NULL, [LoginPwd] [nvarchar](50) NOT NULL, [StudentName] [nvarchar](50) NOT NULL, [Sex] [bit] NOT NULL, [GradeId] [int] NOT NULL, [Phone] [varchar](50) NULL, [Address] [nvarchar](255) NULL, [BornDate] [datetime] NOT NULL, [Email] [varchar](50) NULL, [IdentityCard] [varchar](18) NOT NULL, ) GO/*--案例：使用SQL脚本创建Grade年级表--*/IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade') DROP TABLE GradeCREATE TABLE [dbo].[Grade]( [GradeId] [int] IDENTITY(1,1) NOT NULL, [GradeName] [nvarchar](50) NOT NULL) GO alter table Student add Constraint pk_StudentNo primary key(StudentNo) --Student表中的唯一约束 alter table Student add Constraint uq_IdentityCard unique(IdentityCard) --student表中的默认约束 alter table Student add Constraint df_Address default('地址不详') for Address --student表中的检查约束 alter table Student add Constraint ck_BornDate check(BornDate>'1980-01-01') --外键约束 alter table Grade add Constraint pk_GradeId PRIMARY key(GradeId) alter table Student add Constraint fk_Grade foreign key(GradeId) references Grade(GradeId) go--向Subjiect添加约束alter table Subjectadd Constraint pk_SubjectNo primary key(SubjectNo)alter table Subjectadd Constraint ck_ClassHour check(ClassHour>0)ALTER TABLE Subject --非空约束（课程名称） ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)alter table subject add Constraint fk_GradeId foreign key(GradeId) references Grade(GradeId)go--使用SQL语句创建Result表的约束ALTER TABLE Result --主键约束（学号、科目号、日期） ADD CONSTRAINT PK_Result PRIMARY KEY (StudentNo, SubjectNo, ExamDate)
ALTER TABLE Result --默认约束（日期为系统当前日期） ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate
ALTER TABLE Result --检查约束（分数不能大于100，小于0） ADD CONSTRAINT CK_StudentResult CHECK (StudentResult BETWEEN 0 AND 100)
ALTER TABLE Result --外键约束（主表Student和从表Result建立关系） ADD CONSTRAINT FK_StudentNo FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)
ALTER TABLE Result --外键约束（主表Subject和从表Result建立关系） ADD CONSTRAINT FK_SubjectNo FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)GO
--------------------------------------------------------------------
DATEDIFF(DY, BornDate, GETDATE())/365DatePart()函数用于返回日期或者时间的单独部分，比如年、月、日、小时、分例如 DatePart(YY,BornDate)返回出生年月中的年份
可以通过DatePart()函数得到学号20011的学生大一岁和小一岁的学生
declare @year datetimeselect @year= BornDate from Student where StudentNo=20011select StudentName,sex,datepart(YY,BornDate) as BornDate from Student where StudentNo=20011 or datepart(YY,BornDate)= @year+1 or datepart(YY,BornDate)=@year-1
--select * from Student where ABS(DATEDIFF(DAY,@year,BornDate))/365<=1
--select * from Student where DATEDIFF(DAY,@year,BornDate)/365<=1 and DATEDIFF(DAY,BornDate,@year)/365<=1--select * from Student------------------------------------------------------------------------------------------子查询 总是用小括号括起来，先执行螺号里面的子查询，然后才执行外围的父查询--注意：将子查询和比较运算符联合使用，必须保证子查询的返回结果为一个。--查看比李思文小的学生--declarre @date datetime--select BornDate as '李斯文的出生日期' from Student where StudentName='李斯文'--select * from Student --where BornDate > (select BornDate as '李斯文的出生日期' from Student where StudentName='李斯文')-------------------------------------------------------高级查询简单子查询的用法declare @Birthday datetime select @Birthday = BornDate from student where studentName='李斯文';select studentName,sex from student where BornDate>@Birthday
------------------------------select StudentResult from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP') and ExamDate=(select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP')) --select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP') --1.从课程名称查询出课程编号，2.从课程编号查询出最近（大）日期，从1和2一起查询出考试成绩。--查询Java课程考试成绩为60分的学生名单--in 关键字：查询出来的结果可能是多个 select StudentName from Student where StudentNo in ( select StudentNo from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='Java Logic') and StudentResult=60 ) ------------------------------------------ --in 用法 查询结果有多个 select StudentNo,studentName from Student where GradeId in (select GradeId from Grade where GradeName = 'S1')------------------------------------------------------------------------------聚合函数：sum avg min max count（null列不计算在rpu内）分组：select groupId,avg(age) from student group by groupId having avg(age)<=30 刷选where 和 having的区别：where 对表中的原始数据进行帅选， having对分组后使用聚合函数计算后的数据进行帅选having一般与group by结合使用------------------------------------------ 视图 视图名称creatview view_name(view_colum_name)As query_expression（查询表达式）[with check option]（添加约束条件）------------------------------------------------- --分组查询 Group by 在select 语句查询的最后。 select studentResult ,sum(subjectNo) from Result group by studentResult --出现Group by 一般就会出现聚合函数 --查询学生每一门课程的平均成绩 --要求显示学生姓名，课程名称， --科目ji的平均分--如何将studentNo与studentName联系在一起？ select SubjectNo , AVG(studentResult)From Result group by subjectNo --left on student. select SubjectName ,AVG(studentResult)from Result left join subject on subject.SubjectNO=result.subjectNo ///left join 外表 on 俩表相等的字段 group by SubjectName --学生的平均分 --select studentNo ,AVG(studentResult) from Result where StudentNo=1000 group by studentNo --查询课程的成绩 显示课程名称以及所对应的成绩 --left join 左链接！！！！ 主从表的关系右边的是主表 on select SubjectNo , AVG(studentResult)From Result group by subjectNo select Result.StudentResult,SubjectName from Result Left join Subject on --left join +主表 on后面加俩个表相同的字段 subject.SubjectNO=result.subjectNo----------------------------------------------------------事务 视图 索引begin transactiondeclare @error int set @error=0update bank set currentmoney=currentmoney-1000 where customername='张三'set @error=@error+@@error 错误信息 全局变量update bank set currentmoney=currentmoney+1000 where customername='李四'set @error=@error+@@error给客户一个可视化的结果，if(@error<>0)beginprint '转账失败，重重新操作'rollback transaction 回滚end
eslebegin print '转账成功张三给李四转了1000' commit transaction 提交给数据库endgo
1.业务逻辑需要多个T_SQL语句来执行
create table bank( customername varchar(5) currentmoney int)
事务属性：原子性（各个步骤不可以再分，要么都执行，要么都不执行） 一致性 当事务完成时，数据必须处于一致状态 隔数离性 并发实物之间彼此隔离，独立，不应依赖其他 永久性开始事务： begin transaction提交事务：commit transaction回滚事务：roollback transaction事务是解决可能存在的问题 ------------------------------------------------------begin transactiondeclare @error int set @error=0insert into Result values(113,2,150,GETDATE())set @error=@error+@@error
if(@error<>0)beginprint '插入失败'rollback transaction end
elsebeginprint '插入成功'commit transactionendselect * from Result---------------------------------------------------------------------------索引：根据索引键查找定数据行汉语字典中的汉字按“页”存放，一般都有汉语拼音的目录（索引），偏旁部首目录等sql中的数据也是按页存放索引;shi sql编排数据的方法唯一索引，主键索引，聚集索引，--------------------------------------------------存储过程系统存储过用程的名字一般以sp_开头扩展存储一般xp
调用存储execute 过程名 [参数] 或者 exec 过程名 [参数]
create proc[edure] 存储过程名 @参数1 数据类型=默认值 output 。。。。。。。 @参数n 数据类型=默认值 outputas sql语句go
转载于:https://www.cnblogs.com/ZkbFighting/p/8290198.html
