SQL常用命令

水中的鱼

前两天的帖子里是一些基本的命令，现在又添加了一些常用的命令

001班新来了一名叫李五的北京男生
insert into  student(number,name,sex,jg,class)
values ('012','李五','男','北京','001')
这里，如果定义number为主键，那么主键不能为空，要有内容，字段名和数值要一一对应
-----------------------
李四的英语课考了85分
insert into grade(studyno,kch,cj)
values ('001','G001','85')
-----------------------
定义2个变量学号和性别，并赋值，然后在student表中增加一行。
declare @number varchar(15)        
declare @sex varchar(15)
set @number='014'
set @sex='女'
insert into  student(number,name,sex,jg)
values (@number,'李五',@sex,'北京')
-----------------------
convert 类型转换函数
用循环实现在student表中加入5个李五，籍贯是北京,性别是女，学号是011，012，013，014，015
declare @number varchar(15)        
declare @sex varchar(15)
declare @n int
set @n=1
set @number='01'
set @sex='女'
while (@n<=5)
begin
set @number=@number+convert(varchar(2),@n)
insert into  student(name,number,age,sex,jg)
values ('李五',@number,22,@sex,'北京')
set @n=@n+1
set @number='01'
end
-----------------------
declare @number varchar(15)        
declare @sex varchar(15)
declare @n int
declare @name varchar(15)
set @n=6
set @number='01'
set @sex='女'
while (@n<=10)
begin
set @number=@number+convert(varchar(2),@n)
insert into  student(name,number,age,sex,jg)
values ('李五','@number',22,@sex,'北京')
set @n=@n+1
set @number='01'
set @name='李四'
end
-----------------------
构造1000条用户名和密码的测试数据，用户名为：user1,user2...uaer1000,要求密码全为111111(先准备好一个表，包括username,password两个字段名)
declare @num int
declare @username varchar(10)
declare @password varchar(10)
set @num=1
set @password='111111'
while(@num<=1000)
begin
set @username='user'+convert(varchar(6),@num)
insert into loginuser(username,password)
values (@username,@password)
set @num=@num+1
end
-----------------------
update 修改
修改张三班级
update student set class='003'
where name='张三'
-----------------------
把'于洋','李四','李五'的班级都修改为003班
update student set class='003'
where name in('于洋','李四','李五')
-----------------------
学生张三该名为张力并且班级调换到002班
update student set name='张力',class='002'
where name='张三'
-----------------------
所有的学号前加一个字符0
update student set number='0'+number
-----------------------
删除学号为空
update set 学号=null
-----------------------
删除列
列内容改变
update
-----------------------
删除张三所有的信息
delete from student
where name='张三'
-----------------------
存储过程：
没做成存储过程的语句
declare @number varchar(15)
declare @sex varchar(15)
declare @name varchar(15)
declare @n int
set @n=11
set @number='01'
set @sex='女'
set @name='李四'
while(@n<=13)
begin
set @number=@number+convert(varchar(2),@n)
set @name=@name+convert(varchar(2),@n)
insert into stu(name,number,age,sex,jg,birth)
values(@name,@number,23,@sex,'北京','1980-1-2')
set @n=@n+1
set @number='01'
set @name='张三'
end
-----------------------
以上形成存储过程的语句
CREATE PROCEDURE insert_stu  AS
declare @number varchar(15)
declare @sex varchar(15)
declare @name varchar(15)
declare @n int
set @n=11
set @number='01'
set @sex='女'
set @name='李四'
while(@n<=13)
begin
set @number=@number+convert(varchar(2),@n)
set @name=@name+convert(varchar(2),@n)
insert into stu(name,number,age,sex,jg,birth)
values(@name,@number,23,@sex,'北京','1980-1-2')
set @n=@n+1
set @number='01'
set @name='张三'
end
GO
-----------------------
exec insert_stu     执行存储过程 没有参数
-----------------------
CREATE PROCEDURE delet_stu
@name varchar(50)
AS
delete from grade
where number in
(select number from stu
where [email=name=@name]name=@name[/email])
delete from stu
where [email=name=@name]name=@name[/email]
GO
-----------------------
exec delete_stu '张三'  执行存储过程 有参数

yzylion

删除表格三种方式:
delete:做日志,不删除表的结构
drop:做日志,删除数据的同时删除表的结构
trancate:删的最快,不做日志,但是保留表结构,注意这个删除命令:trancate
格式:
drop table tablename
others 一样:

命令添加一个字段:
alter table tablename
add columnname datetype
(注意:这样的方式添加的字段，不能有限制条件not null)
order delete an column
alter table tablename
drop column columnname

yzylion

count
avg
max
min
sum

以上是五大SQL经常使用的函数
在分组统计的应用中用的比较多

yzylion

跨表查询
select a.name,b.sex from a,b
where a.number=b.no
copy:from a to b;
select * into b from a'
copy:from a to b
select * into b from a where 1<>1
(in tablename b date !=tablename a date)

yzylion

select * from tablename
where 条件表示式
group by,order by

update tablename set columnname=NewValue where columnname=name
insert into tablename(columnname1,columnname2.....)
values
(columnvalue,columnvalue......)

delete from tablename[where columnname=name]

yzylion

create table login
(username varchar(20) not null;
passwordarchar(10) not null)

declare @password varchar(10)
declare @user varchar(20)
declare @n int;
set @n=1
set @user=1
set @password=111111
which(@n<=1000)
begin
{
insert into login(username,password)
values
(@user,@password)
set @user=@user+1
set @n=@n+1;
}
end

yzylion

declare @name varchar(20)
declare @number decimal(10)
declare @sex varchar(20)
declare @name varchaer(20)
declare @n int
set @n=1
set @number=011
which(@n<=5)
begin
{
   insert into student
values
(@name,@set,@number)
set @n=@n+1;
set @number=@number+1;
}
end