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 行)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步