1.创建表
use master
create table student
(
stu_id varchar(10),
stu_name varchar(50),
sex varchar(10),
age int,
grade int,
class varchar(10),
primary key(stu_id)
)
2.插入
insert into student
values('3005218075','小明','男','20','2005','三班')
insert into student
values('3005218076','小红','女','18','2005','三班')
insert into student
values('3005218077','小刚','男','15','2005','五班')
insert into student
values('3005218078','曹操','男','22','2005','三班')
insert into student
values('3005218079','刘备','男','17','2005','一班')
insert into student
values('3005218080','赵云','男','20','2005','三班')
insert into student
values('3005218081','小猪','男','18','2005','二班')
insert into student
values('3005218088','貂蝉','女','18','2006','三班')
3.查询
select *
from student
where stu_id='3005218075' and sex='男'
select stu_name,sex
from student
where stu_id='3005218088'
stu_name sex
貂蝉 女
select min(age) as '年龄',grade
from student
group by grade
年龄 grade
15 2005
18 2006
select stu_id as '学号',stu_name as '名字'
from student
where grade='2005'
学号 名字
1 3005218075 小明
2 3005218076 小红
3 3005218077 小刚
4 3005218078 曹操
5 3005218079 刘备
6 3005218080 赵云
7 3005218081 小猪
4.更新
update student
set stu_name='猪八戒',
grade='2007'
where stu_name='小猪'
5.删除
delete from student
where stu_name='刘备'
//全部删除数据
delete from student
6.存储过程
create procedure select_proc
( @stu_id varchar(20),
@age int
)
as select * from student where stu_id=@stu_id and age=@age
命令已成功完成。
create procedure insert_proc
( @stu_id varchar(10),
@stu_name varchar(50),
@sex varchar(10),
@age int,
@grade int,
@class varchar(10)
)
as insert into student
values(@stu_id,@stu_name,@sex,@age,@grade,@class)
命令已成功完成。
exec select_proc '3005218088',18
stu_id stu_name sex age grade class
3005218088 貂蝉 女 18 2006 三班
7.触发器
create trigger trigger_student on student
after insert
as
declare @name varchar(50)
set @name=(select stu_name from inserted)
print(@name+'has been inserted!')
命令已成功完成。
insert_proc '300521','吕布','man','22','2005','二班'
吕布has been inserted!
（所影响的行数为 1 行）
insert into student values('3005218007','007','男','25','2002','一班')
007has been inserted!
（所影响的行数为 1 行）