Alan的SQL语句学习笔记

1.数据库的创建，删除，重命名，添加文件

/*删除数据库*/
drop database liuyifei
 
/*创建数据库*/
create database Girlfirend
on primary
(
   name='story',
   filename='D:\story.mdf',
   size=8mb,
   maxsize=unlimited
)
log on
(
  name='lovely',
  filename='D:\lovely.ldf',
  size=200mb,
  maxsize=unlimited
)
go
/
 
/*更改数据库的名字*/
alter database Girlfirend
modify name=liuyifei
 
/*对一个数据库添加文件*/
alter database liuyifei
add file 
(
  name=xixi,
  filename='D:\xixi.ndf'
)

2。数据表的创建，删除，截断，字段相关操作

/*创建数据表*/
create table trait 
(
  age int not null,
  nickname  varchar(20) ,
  superpower varchar(30),
  glamour varchar(10),
)

/*创建表的主键*/
create table movie
(
  age int primary key,
  nickname  varchar(20) ,
  superpower varchar(30),
)
/*注意主键是用来限制列的唯一性的，
一个表只能有一个主键，
但是一个主键可以有多个列组成*/ 


/*添加字段*/
alter table movie
add premiere varchar(50)

/*修改字段长度*/
alter table movie
alter column premiere varchar(30)

/*删除字段*/
alter table movie
drop column premiere


/*删除表*/
drop table movie

/*截断表——只删除表的数据，不删除表的本身*/
truncate table movie

/*重命名表*/
sp_rename movie,film

3.数据库主键，约束，索引,表的更新，插入和删除

/*按住ctrl可以设置多个主键（联合主键）*/

/*设置主键约束，图形创建时要设定默认值，否则保存不了*/
alter table trait
add
constraint pk_trait primary key
(
   age
)

/*设置外键约束,设置外键约束时，references后的必须是一个主键*/
alter table film
add
constraint fk_film foreign key (stuno)
references trait(age)

/*设置检查约束*/
alter table film
add
constraint ck_credit
check(credit like '1%')

/*经常控制事务的语句*/
begin transaction--本地事务的开始
begin distribute transaction--分布式事务的开始
commit transaction --事务的提交
rollback transaction--事务回滚，可以回到起点，也可以回到保存点 
rollback--回滚，跳回到某个点
commit--提交
save transaction--设置保存点
set implicit_transactions on--隐式事务
read uncommitted--未提交读取，减少并发控制的开销
read committed--提交读取，
repeatable read --重复读取
serializable --隔离级别最高，建议少用，影响系统用性能

/*显示事务*/
begin transaction film
insert into film(age,credit,superpower)values(7,8,'flash3')
update film set superpower='lovely3'where age=7
commit transaction
--一直查询超时执行错误，这个问题还没有解决

/*隐式事务*/
set implicit_transactions on
insert into liuyifei.dbo.film(age,credit,superpower)values(9,9,'SQL')
commit
set implicit_transactions off

/*查看活跃事务*/
begin transaction
delete from film
with(tablockx)--使用锁
where age=9
dbcc opentran(liuyifei) --使用dbcc opentan 查看最早的活动事务

/*索引的创建*/
create unique index inx_film
on film(age)

delete from film--删除表中数据

/*强制索引查询，一般查询某一列的数据才使用索引*/
select * from film
with(index(inx_film))
where age=1--加了where就叫做强制

/*查询索引*/
sp_helpindex film

/*索引禁用与重启*/
alter index inx_film
on film
diable--禁用索引，将disable换成rebuild

/*索引重命名*/
sp_rename  'film.inx.film','ix.film','INDEX'--报错了，这个地方还没有成功....

sp_helpindex film

4. 查询

/*查询语句*/

--查询全部数据
select * from stu_info

--查询局部数据
select name,sex,major from stu_info

--查询局部分非重复数据 
select distinct sex from stu_info

--设置别名
select name as 'nickname' from stu_info

--选择表中特定数据插入新表（表）
select major into fake from stu_info 
select * from fake

--连接字段
select birthday+mark as '毫无意义的数字' from stu_info 

--按范围查询
select name,mark from stu_info 
where mark between 8 and 10 

select name,sdept from stu_info

select name from stu_info
where name like('田%')

select name mark from stu_info
where mark>0

--按排序查询（desc降序，asc升序，默认升序排列）
select * from stu_info
order by mark desc,stu_ID --排序可以多个，类似C++定义排序

select name,sdept from stu_info
order by 2 desc--按相应位置字段的长度排序  

select top 50 percent name,sdept from stu_info--查询前百分之50的数据
order by 2 desc--按相应位置字段的长度排序

select * from stu_info 
where mark>7
order by stu_ID  --多层嵌套时，order必须在where的后面 

select * from stu_info
where name like '[田敏]%'--中括号代表范围，选取的是姓田的或者姓敏的

 select * from stu_info 
 --函数查询
select sum(mark) from stu_info --求和
select avg(mark) from stu_info --求平均值
select max(mark),min(mark) from stu_info --求最值
select count(name) from stu_info --求元组的数量
select CONVERT(char,GETDATE(),101); --转换函数

--分组查询
select major from stu_info group by major --去除了重复值

select major ,count(*) as '男生人数' 
from stu_info 
where sex='男' 
group by MAJOR  
order by count(*) --嵌套，order by永远在其他语句的后面

select major,count(*) as '人数'
from stu_info 
group by major
having MAJOR in ('计算机科学与技术','会计学')
order by COUNT(*) desc

--having语句用于筛选组，where语句用于筛选记录
--having语句中可以使用聚合函数，where不能
--having语句中的字段必须是被group by 包含，where任意

 --连接查询
 select * from stu_info,stu_grade --无条件连接

 select * from stu_info,stu_grade 
 where  stu_info.stu_ID=stu_grade.STU_ID--等值连接