use school
---数据操作
insert into biao(id,name,sex,age,date,info,tel,bak) values(1,'高明海','男',20,'2010-9-1','学生',15234565421,'坏学生')
insert into biao(id,name,sex,age,date,info,tel,bak) values(9,'李俊霞','男',20,'2010-9-1','组长',15234565426,'好学生')
insert into biao(id,name,sex,age,date,info,tel,bak) values(3,'闫亚龙','男',20,'2010-9-1','学生',15234565441,'乖学生')

---查看表
select * from biao

---添加部分字段
insert into biao(id,name,sex,age,date,info) values(4,'冯鑫','男',20,'2010-2-3','学委')

---唯一性约束(电话号不能重复)
insert into biao(id,name,sex,age,date,info,tel,bak) values(6,'闫亚平','男',20,'2010-9-1','学生',15234565436,'乖学生')

---检查性约束
insert into biao(id,name,sex) values(7,'王媛','女')

select * from score2
create table score2
(
id int,
math int,
en int,
ch int
foreign key(id) references biao
)

insert into score2(id,math,en,ch) values(3,100,98,99)

delete from biao where id=3

-------------作业7.29-------------

--1.创建一个新闻库
create database news

--2.打开新闻库
use news
--查看库文件
exec sp_helpfile news
--并增加一个次要文件
alter database news
add file
(
name=ne,
filename='f:\SQL\ne1.ndf'
)
--一个日志文件
alter database news
add file
(
name=ne2,
filename='f:\SQL\ne3.ldf'
)

--修改新闻库名字
exec sp_renamedb 'news','new1'
--exec sp_renamedb 'new1','news'

--3.创建新闻表、字段id,title,info,date,bak
create table new
(
id int,
title varchar(50),
info text,
date datetime,
bak varchar(500)
)

--4.查看表结构
exec sp_help new

--5.增加列author
alter table new
add author varchar(20)

--6.修改bak字段的长度为
alter table new
alter column bak varchar(600)

--7.日期设置为默认约束,默认时间为-7-29
alter table new
add constraint n default '2010-7-29' for date

--8.插入条数据
insert into new(id,title,info,date,bak,author)
values(1,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','张三')
insert into new(id,title,info,date,bak,author)
values(2,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(3,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(4,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(5,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(6,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(7,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(8,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(9,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')
insert into new(id,title,info,date,bak,author)
values(10,'新闻表','这是一个新闻表','2010-7-1','绝对精彩','李三')

--9.查看新闻表的全部数据
select * from new

--10.插入一条数据,新闻的名字是我们是学生,内容是我们是钢铁学生
insert into new(id,title,info,date,bak,author)
values(11,'我们是学生','我们是钢铁学生','2010-7-1','绝对精彩','王五')

exec sp_helpconstraint biao

-----------------------------------------------------数据操作---------------------------------------------------------------
use school

---插入数据  insert into 表名(字段、字段....) values(字段值、字段值....)
insert into grade(id,name,sex,age,date,info,tel) values(1,'张','女',21,'2010-6-8','学生',12353675489)
insert into grade(id,name,sex,age,date,info,tel) values(2,'王','男',20,'2010-6-3','学生',12353675488)
insert into grade(id,name,sex,age,date,info,tel) values(8,'李','女',22,'2010-6-5','学生',12353345489)

----查看表
select * from grade

---添加部分字段
insert into grade(id,name,sex,age,info) values(4,'孙','男',22,'老师')

---唯一性约束
insert into grade(id,name,sex,age,date,info,tel) values(5,'李','女',22,'2010-6-5','学生',12353345434)

---检查性约束
insert into grade(id,name,sex) values(6,'高','男')

---查看某字段的记录
select name from grade

---查看所有记录
select * from grade

---修改记录  update
update grade set age='30' where id=2
update grade set name='张三' ,sex='男' where id=1

---删除记录
----(1)单行删除
delete from grade where name='王'
delete from grade where id=5
----(2)多行删除
delete from grade where id='1' or id='2'

----删除空记录
delete from grade where