痞子一毛 SQL你必须知道的-增删改查与约束

-- 插入数据
--Insert 语句可以省略表名后的列名，但是不推荐
insert into Class values (' 高一一班 ', ' 快班' )
--Insert  [into]  表 (字段名 , 字段名) values( 值，值 )
insert Class values( ' 高二二班' , '中班 ' )
-- 如果插入的行中有些字段的值不确定，那么 Insert 的时候不指定那些列即可。
insert into Class( cName ) values (' 高三三班 ')
select *   from Class
-- 如果想student 这样的表含有很多的列的时候我们可以直接将列文件拖拽到 student 后面自动生成
-- 日期如果不加引号会当做数值处理添加入错误的数据所以
insert into Student( sName , sAge , sNum , sBirthday , cNum ,sSex ) values( ' 王浩' , 12, 321324199101100136 ,'1992-01-10' , 3, ' 男' )
insert into Student( sName , sAge , sNum , sBirthday , cNum ,sSex ) values( ' 张宇' , 14, 321324199101100136 ,'1992-01-10' , 3, ' 男' )
select * from student
delete from Student
-- 修改数据
update Student set sSex =' 女 '
-- 修改多个列只需用逗号隔开
update Student set sSex =' 女 ', sAge =20
-- 更新一部分数据用 where语句表示只更新 sName 是’张宇’的行，注意 SQL中等于判断用单个 = ，而不是== 。
update Student set sSex =' 女 'where sName= ' 张宇'
--Where 中可以使用的其他逻辑运算符： or 、and 、 not、 < 、> 、 >=、 <= 、!= （或 <>）等
update Student set cNum =2 where sAge = 50 or sAge>= 19 and sAge <=20
-- 所有学生的英语成绩加
update Score set english =english + 1;
select * from Score
-- 删除表数据
--Delete 也可以带where 子句来删除一部分数据
delete from student where sName = '张飞 '
--truncate 会连id 号彻底的清除 这是和delete 不同的一点
truncate table Student
-- 或者说Truncate 清空表中的数据没有条件，和 delete 的区别不存日志，清空自动编号
truncate table score
-- 注意应该在每一条 ddl语句以及 dml 语句之后添加 go 否则编译运行此文件时是不能连续的执行其中的语句导致错误
------------------- 插入测试数据 --------------------------
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '刘备 ' ,20 , '男 ' ,123456789012345678 , '1987-5-6', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '关羽 ' ,19 , '男 ' ,123456789012345671 , '1988-8-6', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '张飞 ' ,18 , '男 ' ,123456789012345672 , '1989-5-19', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '曹操 ' ,22 , '男 ' ,123456789012345673 , '1985-12-6', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '夏侯惇 ' ,22 , '男 ' ,123456789012345674 , '1985-3-6', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '华佗 ' ,50 , '男 ' ,12345678901234565 , '1957-1-16', 1 )
insert into Student ( sName, sAge ,sSex , sNum, sBirthday ,cnum ) values ( '甄姬 ' ,18 , '女 ' ,12345678901234565 , '1989-8-8', 1 )
insert into Score ( studentId, english ) values (1 , 90)
insert into Score ( studentId, english ) values (2 , 90)
insert into Score ( studentId, english ) values (3 , 59)
insert into Score ( studentId, english ) values (4 , 100)
insert into Score ( studentId, english ) values (5 , 60)
insert into Score ( studentId, english ) values (6 , 0)
insert into Score ( studentId, english ) values (7 , 80)
-- 练习
-- 插入几条老师信息和成绩
insert into teacher( tName , tSex , tAge , tSalary , tBirthday ) values (' 朱德军 ', ' 女' , 18, 3000 ,'1989-2-3' )
insert into teacher( tName , tSex , tAge , tSalary , tBirthday ) values (' 徐连龙 ', ' 男' , 18, 3000 ,'1984-2-3' )
insert into teacher( tName , tSex , tAge , tSalary , tBirthday ) values (' 杨会玉 ', ' 女' , 18, 3000 ,'1988-2-3' )
insert into Score ( studentId, english, math) values (1 , 29, 34 )
-- 练习：给刘备的英语成绩加分
select sId   from Student where sname= ' 刘备'
update Score set english = english+ 10 where studentId =(select sId  from Student where sname= ' 刘备' )
select * from score
-- 练习：考试题偏难，所有人的成绩加分
update Score set math = math+ 5
-- 练习：所有女学生的成绩加分
update Score set english = english+ 5 where studentId = (select sid from Student where ssex= ' 女' )
-- 删除工资大于的老师
delete from Teacher where tSalary >2000
-- 删除所有老师
delete from Teacher
-- 删除数据时候 把自增长列的值还原成种子
truncate table teacher
-- 约束constraint
-- 数据库约束是为了保证数据的完整性 ( 正确性) 而实现的一套机制
-- 非空约束
-- 主键约束(PK) primary key constraint 唯一且不为空
-- 唯一约束(UQ)unique constraint 唯一，允许为空，但只能出现一次
-- 默认约束(DF)default constraint 默认值
-- 检查约束(CK)check constraint 范围以及格式限制
-- 外键约束(FK)foreign key constraint 表关系

-- 主键约束
-- 假设Student 表创建时没有设置主键现在就需要添加 Primary Key 的约束 constraint中文为约束的意思
alter table Student
add constraint PK_Student primary key ( sId)
-- 如果存在多个主键的话 primary key (sId,****) 逗号分隔
-- 唯一约束
-- 如果表中的 sNum列已经存在重复的值了那么这里始终会报错 需要删除重复数据再来执行此操作
alter table Student
add constraint UQ_Student_sNum unique (sNum )
-- 默认约束
alter table Student
add constraint DF_Student_Sex default (' 男 ') for sSex
-- 注意For 后面的字段名称是不要括号的
--default 默认值
insert into Student ( sName, sAge ,sSex , cNum, sNum )values ( '王浩 ' ,default , default, 0900261 ,52 )
-- 检查约束
-- 如果事先库中已有不符合约束的数值那么执行是不通过的需要修改之后
alter table Student
add constraint CK_Student_sSex check (sSex = '男 ' or sSex =' 女 ')
-- 约定年龄大于 如果插入数据小于则会不通过
alter table Student
add constraint CK_Student_sAge check (sAge >= 18)
-- 外键约束Student 的 cNum与 Class 中的CId 建立外键关系如果 Cid 没有设置成为主键那么是不通过的
alter table Student
add constraint FK_Student_cNum foreign key ( cNum) references Class (cId )
-- 删除约束
alter table Student
drop constraint CK_Student
-- 级联删除一般少用
--on delete cascade
-- 级联更新
--on update cascade
--cascade 级联删除的意思 删除主表数据时会首先删除子表对应的数据
-- 添加外键约束之后容易出现的问题
-- 在子表Student 中添加的 Cnum必须在 class 表中出现过否则不通过
insert into Student ( sName, cNum )values ( 'wnaghao', 100 )
-- 删除主表中的数据时 必须先删除子表中对应的数据 解决方法在建立外键约束时添加 on delete cascade 级联删除会首先删除子表对应数据后再删除父表数据
alter table Student
add constraint FK_Student_cNum foreign key ( cNum) references Class (cId )
on delete cascade
-- 约束练习
--tSex  控制只能是男女，默认男
--tAge  在之间 默认
--tName 唯一
alter table Teacher
add constraint CK_Teacher_tSex check (tSex = '男 ' or tSex =' 女 '),
  constraint DF_Teacher_tSex default ( '男 ' ) for tSex ,
  constraint CK_Teacher_tAge check ( tAge>= 30 and tAge <=40 ),
  constraint DF_Teacher_tAge default ( 30) for tAge ,
  constraint UQ_Teacher_tName unique ( tName)
--Score 表中
--studentId 是外键   先要把 Student表中的 sId 设置为主键
alter table Score
add constraint Fk_Score_studentId foreign key ( studentId) references Student (sId )
-- 在学生表中删除有成绩的学生
-- 成绩表中添加学生表中没有的学生
-- 在创建表的过程中就创建约束
create table Student1
(
       sId int identity( 1 ,1 ) primary key,
       sName nvarchar ( 20) not null,
       sAge int constraint CK_Student1_sAge check ( sAge>= 18 ) constraint DF_Student1_sAge default ( 18),
       scId int constraint Fk_Student1_scid foreign key ( scId ) references Class (cId )
)
-- 但是虽然可以简化代码但是修改查看比较的麻烦一般建议还是分开写
-- 这里添加都用 add 修改都用 alter 删除都用drop
-- 注意删除列要有 column
alter table Student1
drop column scId