insert into userinfo(username,userage,userdescript) values('wangkang',20,'民大..........')
insert into userinfo values('soft',15,'sdfsadf')
insert into userinfo values('张十分',23,'sdfsadfa')
insert into userinfo values('张士大夫撒',23,'sdfas')
--修改语句
update userinfo set username ='李四' where userid=3
update userinfo set username='王五',userage=34 where userid=4
--删除语句
delete from userinfo
--创建视图
create view selectuser
as
select * from userinfo where username = '张十分' and userage = 23
create view selu
as
select * from userinfo where userage>(select avg(userage) from userinfo)
--通过视图查询数据
select * from selectuser
select * from selu
--建立索引
create index usernamesel on userinfo(username)
--删除索引
drop index userinfo.usernamesel
--添加用户的存储过程
create procedure adduser
@username varchar(50),
@userage int,
@userdescript varchar(50)
as
insert into userinfo values(@username,@userage,@userdescript)
exec adduser '张三',28,'JAVA 工程师'
--删除用户的信息
create procedure deleteuser
@username varchar(50),
@userid int
as
delete from userinfo where username=@username and userid=@userid
exec deleteuser 'java23',11
--删除用户表
create procedure dropuserinfo
as
drop table userinfo
exec dropuserinfo
--删除用户的存储过程
drop procedure deleteuser
--更新用户的存储过程
create procedure updateuser
@username varchar(50),
@userage int,
@userdescript varchar(50),
@userid int
as
update userinfo set username=@username ,userage=@userage,userdescript = @userdescript where userid=@userid
exec updateuser '李四',26,'硬件工程师',1
--查询用户的存储过程
create procedure seluser
as
select * from userinfo
exec selectuser
--修改用户字段的存储过程
alter procedure userinfo
@userage int
as
select convert(varchar)
--alter table userinfo alter column userage varchar
exec userinfo 20
--查询语句
select * from userinfo;
select * from userinfo where userage=16
select * from userinfo where userage select * from userinfo where userage between 12 and 18
--查询平均年龄
select avg(userage) from userinfo
--查询最大年龄
select max(userage) from userinfo
--模糊查询语句
select * from userinfo where username like '张%'
select * from userinfo where username like '张_'
select * from userinfo where username like '%十%'
--通过名字来查询
select * from userinfo where username = '张十分'
select * from userinfo where username = '张十分' and userage = 23
--求大于学生平均年龄的信息
select * from userinfo where usernage>(select avg(userage) from userinfo)
--按学生的学号降序排列
select * from userinfo order by userid desc
--加入了判断语句的存储过程,如果传入用户名为空,则用随机函数生成一个
if username exists
begin
drop procedure userinfo
end
--创建判断用户是否为空的存储过程
create procedure alteruser
@username varchar(50),
@userage int,
@userdescript varchar(200)
as
if @username is null
begin
insert into userinfo values(rand(10)*1000,@userage,@userdescript) --生成随机数
end
else
begin
insert into userinfo values(@username,@userage,@userdescript)
end
exec alteruser null,12,'空用户名'
exec alteruser '王五',12,'用户名'
--数据库
--数据表(新建,修改,删除)
--数据操作语句(插入,修改,删除)
--视图(新建,修改,删除)
--索引(新建,修改,删除)
--存储过程(新建,修改,删除)
--查询语句
--1查询字段名及别名,
--2指定数据源(也就是那几张表),
--3指定查询条件(between,like,in)
--聚合函数查询
--对结果排序
--查询一段记录
--去掉重复字段
--多表联合查询
--合并查询
--连接查询(内连接,无限制连接,外连接,自连接,自查询)
--分组查询及统计
--创建数据库
create database java23;
--指定使用的数据库
use java23;
--创建表格
create table userinfo(
userid int primary key identity,
----字段名 字段类型 字段约束条件
username varchar(50) not null,
userage int,
userdescript varchar(200)
)
--修改基本表(添加add 删除drop modify)
--------------------------新列名 数据类型
alter table userinfo add sex varchar
alter table userinfo add price int
--修改字段类型
--alter table 表名 alter column 字段名 字段类型
alter table userinfo alter column price varchar
--删除字段
--alter table 表名 drop column 列名
alter table userinfo drop column price
--修改列名
exec useru 'userinfo.username','userprice','column'
--插入语句
insert into userinfo(username,userage,userdescript) values('wangkang',20,'民大..........')
insert into userinfo values('soft',15,'sdfsadf')
insert into userinfo values('张十分',23,'sdfsadfa')
insert into userinfo values('张士大夫撒',23,'sdfas')
--修改语句
update userinfo set username ='李四' where userid=3
update userinfo set username='王五',userage=34 where userid=4
--删除语句
delete from userinfo
--创建视图
create view selectuser
as
select * from userinfo where username = '张十分' and userage = 23
create view selu
as
select * from userinfo where userage>(select avg(userage) from userinfo)
--通过视图查询数据
select * from selectuser
select * from selu
--建立索引
create index usernamesel on userinfo(username)
--删除索引
drop index userinfo.usernamesel
--添加用户的存储过程
create procedure adduser
@username varchar(50),
@userage int,
@userdescript varchar(50)
as
insert into userinfo values(@username,@userage,@userdescript)
exec adduser '张三',28,'JAVA 工程师'
--删除用户的信息
create procedure deleteuser
@username varchar(50),
@userid int
as
delete from userinfo where username=@username and userid=@userid
exec deleteuser 'java23',11
--删除用户表
create procedure dropuserinfo
as
drop table userinfo
exec dropuserinfo
--删除用户的存储过程
drop procedure deleteuser
--更新用户的存储过程
create procedure updateuser
@username varchar(50),
@userage int,
@userdescript varchar(50),
@userid int
as
update userinfo set username=@username ,userage=@userage,userdescript = @userdescript where userid=@userid
exec updateuser '李四',26,'硬件工程师',1
--查询用户的存储过程
create procedure seluser
as
select * from userinfo
exec selectuser
--修改用户字段的存储过程
alter procedure userinfo
@userage int
as
select convert(varchar)
--alter table userinfo alter column userage varchar
exec userinfo 20
--查询语句
select * from userinfo;
select * from userinfo where userage=16
select * from userinfo where userage select * from userinfo where userage between 12 and 18
--查询平均年龄
select avg(userage) from userinfo
--查询最大年龄
select max(userage) from userinfo
--模糊查询语句
select * from userinfo where username like '张%'
select * from userinfo where username like '张_'
select * from userinfo where username like '%十%'
--通过名字来查询
select * from userinfo where username = '张十分'
select * from userinfo where username = '张十分' and userage = 23
--求大于学生平均年龄的信息
select * from userinfo where usernage>(select avg(userage) from userinfo)
--按学生的学号降序排列
select * from userinfo order by userid desc
--加入了判断语句的存储过程,如果传入用户名为空,则用随机函数生成一个
if username exists
begin
drop procedure userinfo
end
--创建判断用户是否为空的存储过程
create procedure alteruser
@username varchar(50),
@userage int,
@userdescript varchar(200)
as
if @username is null
begin
insert into userinfo values(rand(10)*1000,@userage,@userdescript) --生成随机数
end
else
begin
insert into userinfo values(@username,@userage,@userdescript)
end
exec alteruser null,12,'空用户名'
exec alteruser '王五',12,'用户名'