常用sql语句

--创建数据库
create database test

use test

--删除数据库
drop database test

--删除表
drop table person

--查看表的信息
sp_help person

--创建表和字段
create table person
(
    pId int identity(1,1) primary key, --设置主键，自增长
    pName varchar(50) not null unique, --设置唯一
    pAge int not null,
    pSex varchar(2) not null
)

create table book
(
    bId int identity(1,1) primary key,
    bName varchar(100) not null,
    bPrice int not null,
    bTime dateTime default getDate(), --设置默认值
    pId int foreign key references person(pId) not null --设置外键
)

--插入数据
insert into person(pName,pAge,pSex) values('张三',25,'男')
insert into person(pName,pAge,pSex) values('李斯',18,'男')
insert into person(pName,pAge,pSex) values('望里',45,'女')

insert into book (bName,bPrice,pId) values('c++编程',50,1)
insert into book (bName,bPrice,pId) values('java编程',80,2)
insert into book (bName,bPrice,pId) values('c#编程',90,3)
insert into book (bName,bPrice,pId) values('vb编程',60,1)

--插入数据并返回插入的id，字段必须是自增长
insert into book (bName,bPrice,pId) values('javascript编程',60,1) select @@identity

--把一个表中的数据插入到另一个表
insert into book2(bName,bPrice,pId) select bName,bPrice,pId from book

--更新数据
update book set bName='javascript' where bId=3

--查询数据
select * from book


--删除数据
delete book where bId=4

--排序 desc esc
select * from book order by pId

--查询前面2条数据
select top 2 * from book order by bId

--查询范围 between
select * from book where bPrice between '50' and '70'
select * from book where bPrice between 50 and 70

--模糊查询
select * from book where bName like 'c%'

update book set bName='vb' where bId=4

--查询空字段
select * from book where bName is null

--去掉重复行
select distinct bName from book

--求平均值
select avg(bPrice) from book

--求最大值
select max(bPrice) from book
--求最小值
select min(bPrice) from book
--求总值
select sum(bPrice) from book
--求总行数
select count(*) from book


--今天
select * from book where Datediff(dd,bTime,getDate())=0

--一周
select * from book where datediff(dd,bTime,getDate())<7-- and datepart(dw,bTime)<datepart(dw,bTime)-1

--返回星期
select *,'星期'=datepart(dw,bTime)-1 from book


--多表表查询
select * from book
select * from person

select * from book b join person p on b.pId=p.pId
select * from book b inner join person p on b.pId=p.pId


select b.bName, b.bPrice,p.pName,p.pSex from book b join person p on b.pId=p.pId

select b.bName, b.bPrice,p.pName,p.pSex from book b join person p on b.pId=p.pId

select b.bName, b.bPrice,p.pName,p.pSex from book b join person p on b.pId=p.pId where p.pName='张三'

--连接
select * from book b left outer join person p on b.pId=p.pId
select * from book b right outer join person p on b.pId=p.pId and b.pId=1

--子查询
select * from book where pId=(select pId from person where pName='张三')

--版本
select @@version

--sql服务器名
select @@servername

--当前进程的服务器进程id号
select @@spid

--当前执行过程的存储的进程id
select @@procid

--受最后询问影响的行数，0如果没有受影响
select @@rowcount

--自Mircrosoft sql server启动以来连接的和企图连接的数的总和
select @@connections
--某用户当前活动的事务个数
select @@trancount
--同时连接的最大个数
select @@max_connections
--在当前sql服务器对话期间已出现错误总数
select @@total_errors

--声明变量
declare @var int
select @var=2
select @var

--打印
declare @var int
select @var=2
print @var

--if else begin end

declare @var int
select @var=5
if @var<10
    print '小于10'
else
   begin
    print '大于10'
    print '或者等于10'
   end

--创建存储过程
create procedure select_book
as
    select * from book
-------------------------------

create procedure sel_person
as
begin
    print '查询person表'
    select * from person
end
-------------------------------
create procedure sel_person2
as
    print '查询person表'
    select * from person


--创建带参数的存储过程
create procedure sel_book
(
    @bid int,
    @name varchar(50)
)
as
    select * from book where bId=@bid or bName=@name
--执行存储过程
execute select_book
-----------------
exec select_book
----------------
select_book
----------------
sel_person2
---------------
sel_book 1,'vb'

--判断存储过程是否存在

sp_helptext select_book

--删除存储过程

drop procedure select_book
drop procedure sel_book
drop procedure sel_person
drop procedure sel_person2

--exists
if exists(select * from book where bId=3234)
print 'ok'
else
print 'no'

--创建触发器

--update触发器
create trigger tri_updateBook
on book
for update
as
    declare @id int
    select @id=bId from inserted
    update book set bTime=getdate() where bId=@id
--delete触发器
create trigger tri_delBook
on book
for delete
as
    declare @id int
    select @id=pId from deleted
    delete person where pId=@id
------------------------------
create trigger tri_delBook
on book
for delete
as
    declare @id int
    select @id=pId from deleted
    delete person where pId=@id

------------------------
select * from book
select * from person

delete book where pId=3

update book set bName='c语言' where bId=2

--删除触发器
drop trigger tri_updateBook
drop trigger tri_delBook