数据库的操作语句
创建数据库
(1)create database test
(2)create database test1
on
(
name=sss,
filename='d:/a.mdf',
size=10,
maxsize=100
)
log on
(
name=ssss,
filename='d:/a.ldf',
size=10,
maxsize=100
)
----使用数据库
use test1
----删除数据库
drop database test
一、 表的操作语句
---创建表
use test1
create table student1
(
sid int primary key,
sname varchar(10) not null,
sage int not null
)
create table student2
(
name1 varchar(10),
name2 varchar(10),
sid int foreign key references student1(sid),
primary key(name1,name2)
)
--由其他表来创建新表
select sid,sname into student3 from student1
--创建自动增长字段的表
create table student4
(
sid int identity(1,1) primary key
)
--删除表
drop table student4
--修改表
use test1
alter table student4 add sname varchar(100) DEFAULT 'sss' not null 增加列
alter table student1 drop column sage 删除列
alter table student4 alter column sname varchar(50) not null 修改列
数据操纵语言
(1)insert语句:添加一行数据
insert into student3 values(1,'张三',10)
insert into student3(sid,sname,sage) values(2,'李四',12)
(2)delete语句:删除一行数据
delete from 表名字 where 条件
delete from student3 where sid=1
(3)update语句:修改一行数据
update [表名] set 列名=值,列名=值,列名=值,列名=值 where条件
update student3 set sname='王五' where sid=2
(4)select语句:查询语句
--投影查询
select * [列名,列名,列名] from [表名]
select * from student3
select distinct * from student3 --去掉重复显示行
select sid as '学号', sname as '姓名' ,sage as '年龄' from student3 --重命名
--条件查询
select * from 表名
---条件表达式 = < > <> >= <= and or not between...and...
select * from student3 where sage=12
--SQL批处理:
基本语法:
USE 数据库 SQL内容 GO 结束命令
--逻辑控制语句
BEGIN END
IF ELSE
WHILE 循环
GOTO 和 RETURN
CASE 分支
--逻辑控制语句
if 100<>100
begin
print 'ssssssssssss'
end
else
begin
print 'aaaaaaaaaaaaa'
end
print '11111111111'
goto a
print '22222222222'
a:
print '33333333333'
select sid,sname,sage,
case ssex
when '男' then 'male'
when '女' then 'female'
else '人妖'
end as '性别'
from student3
--事务
begin tran --开启
delete from student3 where sage=10
commit tran --提交
rollback tran --回滚
--自连接
select * from student s1 inner join student s2 on s1.cid=s2.cid
select * from student s1, student s2 where s1.cid=s2.cid
--外连接
--左外连接
select distinct s1.sname,c1.cname from student s1 left join class c1 on s1.cid=c1.cid
--右外连接
select distinct s1.sname,c1.cname from student s1 right join class c1 on s1.cid=c1.cid
--全连接
select distinct s1.sname,c1.cname from student s1 full join class c1 on s1.cid=c1.cid
--聚集函数
-- max
-- min
-- count
-- avg
-- sum
select max(cid) from student
select min(cid) from student
select count(*) from student
select avg(cid) from student
select sum(cid) from student
--排序语法
-- order by
-- DESC
-- ASC
select * from student order by sid desc
--分组查询
-- GROUP BY
select cid from student group by cid
--模糊查询
--like
-- %
-- _
-- []
select * from student where sname like '%刘%'
select * from student where sname like '刘__'
select * from student where sname like '刘德[华丰]'
select * from student where sname like '刘德[^丰]'
字符串函数
datalength(Char_expr)
返回字符串包含字符数,但不包含后面的空格
select datalength(sname) from student
substring(expression,start,length) 取子串
right(char_expr,int_expr) 返回字符串右边int_expr个字符
upper(char_expr) 转为大写
lower(char_expr) 转为小写
ltrim(char_expr) rtrim(char_expr) 取掉空格
数学函数
rand([int_expr]) 随机数产生器
select rand() from student
getdate() 返回日期
select getdate() from student
datepart(datepart,date_expr) 取日期一部份
select datepart(dd,getdate()) from student
user_name() 用户在数据库中的名字
select user_name() from student
db_name() 数据库名
select db_name() from student
创建索引
create clustered/unique index aaa on student(sid)
删除索引
drop index student.aaa
select * from student where sid=1 and sname <> ''
视图
创建、修改视图
Create/ alter view hh as select * from student where cid=2
select * from hh
delete from hh
insert into hh(sid,sname,cid) values(444,'sss',4)
update hh set sname='sss'
drop view hh 删除视图
游标
select sname from student
declare youbiao cursor scroll --自由游标 可以随意读取行
for select sname from student
open youbiao --打开游标
declare @name varchar(100) --定义变量
fetch First from youbiao into @name --读取第一行数据 交给@name
print @name --打印第一行的名字
close youbiao --关闭游标
deallocate youbiao --销毁游标
---------------------------
declare youbiao cursor scroll --自由游标 可以随意读取行
for select sname from student
open youbiao --打开游标
declare @name varchar(100) --定义变量
fetch First from youbiao into @name --读取第一行数据 交给@name
print @name --打印第一行的名字
while @@FETCH_STATUS = 0
begin
fetch next from youbiao into @name
if @@FETCH_STATUS <> 0
break
print @name
end
close youbiao --关闭游标
deallocate youbiao --销毁游标
存储过程:没有返回值的函数
执行存储过程
execute 过程名字 参数,参数,参数
列出服务器上所有数据库
exec sp_databases
列出服务器信息
exec sp_server_info
列出当前环境中所有的存储过程
exec sp_stored_procedures
自定义存储过程
创建/修改
create/alter procedure 存储过程名字 as T-SQL
create/alter procedure ppp as select * from student
执行存储过程
exec ppp
删除存储过程
drop procedure ppp
定义变量
declare @变量名 数据类型
变量赋值
set @name='值'
传递参数
普通传递参数(值传递)
create procedure 名字 @变量名 数据类型...多个 as
执行
execute 名字 1,2,3
execute 名字 @变量1=1, @变量2=2,@变量3=3
create procedure ppp @name varchar(50),@id int
as
select * from student where sname=ltrim(rtrim(@name))
exec ppp '范范',12
exec ppp @id=12,@name='范范'
高级传递参数(地址传递)
create procedure 名字 @变量名字 数据类型 output ...多个
as
执行
declare @name varchar(50)
set @name='aaa'
print @name
exec 名字 @name output
print @name
create procedure ppp @name varchar(50) output
as
set @name='刘德华'
declare @sname varchar(50)
exec ppp @sname output
print @sname
触发器:类似于事件处理
create trigger 触发器名字
on 表|视图 for insert,update,delete
as
T-SQL
create trigger hhh
on student for insert
as
select * from inserted
insert into student(sname,ssex) values('武阳','女')
insert into student(sname,ssex) values('萧亚轩','女')
drop trigger hhh
create trigger ddd
on student for delete
as
select * from deleted
delete from student where sname='武阳'