SQL

--常用的数据库的分离和附加
--分离：选数据库（右键）--任务--分离
--附加：选数据库（右键）--附加 --select* from sysobjects  where  name= "proc_%"
--在数据库中如果想从上到下直接运行下来要注意的：
--用到master数据库；查系统数据库；判断自己现在要创建的数据库的名字与系统数据库中数据库的名字是否相同，如果有那么自己可以改自己现在要创建的数据库名字，或判断这个数据库是否还有用，如果没用就将其删掉，创建新数据库；（分两种：第一种：指定路径；第二种：不需要指定路径。指定路径：注意要用到on primary（易忘记的）主数据文件（指定路径时在后面加上.mdf）的名字（name）因为是字符串所以要用到单引号（这里的主数据文件名字不能与数据库名字一致）把创建的数据库放在哪（存放的路径（filename），以便下次要用时直接附加即可）、文件的初始大小（size）（主数据文件要求：初始大小不能小于3mb）、增长类型（filegrowth）（可以按照大小增长、还可以按照百分数增长）、最大大小（maxsize）（可以是无限制unlimited，也可以是规定最大大小），这是主数据文件如果有次数据文件（在指定路径时在后面加上.ndf）就直接在主数据文件括号外用‘，’直接再创建次数据文件，一个数据库中必须有一个主数据文件，可含多个次数据文件，同时还有日志文件log on也和上面一样指定路径时主日志文件后面是.ldf，次日志文件后面是.ndf，日志文件在一个数据库中必须有一个主日志文件可含有多个次日志文件。
use master
go

if exists(select * from sysdatabases where name='L')
drop database L
go

create database L
on primary
(
 
)
--如果数据库已经建成
--要添加次数据库
alter database mydata--注释：修改数据库，数据库的名字是mydata

add file--增加次数据文件
--同样和建主数据文件一样也要名字、路径等--------在往数据库中新增次数据文件时，次数据文件可以不用和主数据文件放在同一个地方

modify file--修改数据库中的文件 指定要进行更改数据文件的名字、只能改maxsize所以修改数据库时只能有两项

remove file  my_dbdata--从数据库中移除my_dbdata这个文件

--删除数据库
drop database mydata

--查看数据库
sp_helpdb mydata
--如果要查词是什么按：F1

--重命名数据库
sp_helpdb 'mydata','mydata1'
--后面一个是新的名字，前面一个是要改的数据库名字

 

use master
--使用master数据库

go
if exists(select * from sysdatabases where name='testdb')
 drop database testdb
--判断系统数据库中是否含有名叫testdb的数据库，如果有就删掉

go
--创建数据库
create database mydata
--create：创建；database：数据库
--:mydata是数据库名字
on primary
--primary可以省略，primary：主要的
--:在主数据文件上创建
(
  name='my_dbdata',
--:my_dbdate叫做逻辑名称 主数据文件的名称
  filename='G:\my_dbdata.mdf',
--:物理名称 主数据文件的路径,写路径是数据库一定要加上后缀名
  size=3mb,
--主数据文件的初始大小，且主数据文件最小不能为3mb，后面的mb可以省略
  filegrowth=1mb,
--文件的增长 可以是数字也可以是百分比
  maxsize=unlimited
--可以限制它的最大值也可以不限制。上是没有限制
),
(
 name='my_dbdata1',
 filename='d:\my_dbdata1.ndf'
)
--次数据文件，名字和后缀名都与主数据文件不一样
log on
--这是创建日志
(
  filename='G:\my_dbdata.ldf',
  --同上
)
--数据库中必须有一个主数据文件和一个日志文件，次数据文件和日志可以有多个。
--如果要用代码写数据文件次数据文件接在主数据文件后面（当主数据文件建完后在括号外面用‘逗号’再建次数据文件。日志也是一样）

--如果数据库已经建成
--要添加次数据库
alter database mydata
--alter:修改
add file
--增加文件
(
  name=''
--不能和主数据文件的名字一样
  filename='G:\my_dbdata.ndf'
--路径：可以不和主数据文件放到一个盘中     
--（其它）
)

--修改数据库中的文件（就是在括号里面写的内容）
alter database mydata
modify file
--modify:修改
(
  name=''
--这里的名字指定要进行更改数据文件的名字（所以主数据文件和次数据文件的名字不能一样）
  maxsize=100mb
--这里的100是该后的
) 

--删除文件
alter database mydata
remove file  my_dbdata
--remove：移除;
--移除要删文件的名字

--查看数据库
sp_helpdb mydata
--如果要查词是什么按：F1

--重命名数据库
sp_helpdb 'mydata','mydata1'
--后面一个是新的名字，前面一个是要改的数据库名字

--删除数据库
drop database mydata

--数据库的分离和附加
--分离：选数据库（右键）--任务--分离
--附加：选数据库（右键）--附加             -------常用的

--在创建表时：
--要使用你自己创建的数据库，如果不说明自己放在哪个数据库系统默认的是master中；判断系统表中是否含有于自己所要创建的表明一样的表，如果有自己是不是要删掉或是改动现要创建的表的名字sysobjects（易忘记）；创建表：首先明确表的结构，在表中分为行和列，在数据库中所写的字段是表中的列，后面的是表的内容的字段类型，有些要注意在后面加上范围，有的则不需要加。
--如果表已经建成
--alter table student

add birthdate datetime
--add +列名+对应的列的类型  --注释：在表中添加一列

alter column stuid varchar(50)  --修改stuid这一列中的字段类型范围将其改为50
--alter +column+列名+所要改的类型的范围

drop column birthdate --删除不需要的列
--drop+column+列名 

--查看表
sp_help student

--修改表名
sp_rename student,shudent1

--删除表
drop table student

--往以建表中插入数据
--插入数据分为往表中所有列分别插入，或者是指定列下面插入数据，如果是所有的在values后面可以加上所有列的列名也可以不加列名，如果是指定的列下面插入数据那么在values后面就必须加上是哪个列要加上数据，在后面写上列名，两种都要注意所加的要‘对应’======into可以省掉
insert into friends values(10000,10001)
insert into student(stuid,stuname) values('001','大大')

--如果一个表已经建好
--发现插入的数据是错误的就要进行修改
update student set stusex='女'where stuname='小小'--注释：修改student表设置（将正确的数据写在设置后面要注意的是：要强调是哪一列要改成什么）后面where是条件（一定是与要修改的数据是在同一行，选定这一行除错误数据外的其他列的列名，指定列名所对应的这一行正确的数据）

--删除表中的所有数据
delete table student
truncate table student
--两者的区别在于：delete是一条数据一条数据的删掉，而truncate是直接删除表中的所有数据，所以当数据很多时就会出现快慢之分，还有一个很重要的区别是：数据虽然删掉但表的结构还在

--删除指定条件下的数据
delete student where stuid='001' and stusex='女'
--直接是删除表然后就是接条件（通常状况下删除的是一整行数据）

--删除表
--drop table 表名

--建表是要注意的：表中的细节

 

--切换数据库：use mydata   --在执行的左边

--判断表是否已经存在
if exists(select * from sysobjects where name='student' and type='u')
 drop table student
go
--创建表
create table student
--table:表
(
  stuid varchar(20),
--stuid表示第二列的名字（因为第一列是系统默认的），varchar是这一列中‘数据’的类型
  stuname varchar(20),
)

--增加列（也叫增加字段）
alter table student
add birthdate datetime
add+列名+对应的列的类型

--修改字段（只能修改数据类型的范围）
alter table student
alter column stuid varchar(50)

--删除字段
alter table student
drop column birthdate

--查看表
sp_help student

--修改表名
sp_rename student,shudent1
--同数据库一样

--往表里面插入数据
--插入部分数据
insert into student(stuid,stuname)
--insert:插入；into可以省略，()不能省略，如果是每一列都要插入数据（）可以省略一定要做到’对应‘
values('001','大大')                                   

--如果表已建成但发现有数据出错
--修改数据
update student set stusex='女'
--修改student表设置哪一列要改成什么
where stuname='小小'
--条件是其它列的列名=....但要与改的内容那一行对应
--如果是修改所有的数据不需要条件也就是不用where直接改
--eg：将一个人的年龄加2岁
update student set stuage=stuage+2
where stuname='小小'

--删除指定条件的数据(一般是行的)
delete student
where stuid='001' and stusex='女'

--删除所有数据(数据虽然删了，但表的结构还是没变。eg：主键还在等等)
delete table student
truncate table student
--区别是：delete是一行一行的删除而truncate是直接删除

--删除表
drop table student

--自动增长
stuid int identity(100,2)
--identity只能用于整型；后面的100是初始值，2是一次增长多少，也可以不加那就是默认的从1开始每次增长1
--如果用了自动增长那就可以免条件
insert into student(stuname)
values('大大')
--这个表中本来是有两列的但添加内容时只写了stuname因为有个条件是自动增长另一列是不用写的，也可以不用（）在values中也只需要写stuname的不然就错了

--不能为空
stuname varchar(20) not null
--这一列的内容不能不写，不然就报错

--主键(就是这一列是唯一的且不能为空。要定义这列为主键首先就要规定这一列不能为空)
stuid varchar(20) constraint pk_stuid primary key
--constraint pk_stuid可以省略

create database person
create table student
(
  stuid varchar(20) constraint pk_stuid primary key,
--主键约束constraint pk_stuid可以省略
  stuname varchar(20),
  stuage int constraint ck_stuage check(stuage between 15 and 30),
                                  --check(stuage>15 and stuage<30)
--检查约束（设定年龄的范围）
  stusex char(2) check(stusex='男' or stusex='女')constraint df_stusex default '男'
                                               --默认约束、如果不填就默认的是’男‘
                 --check(stusex in ('男','女'))
)

insert into student
values('001','大大',20,'男')

--表2课程表
create table course
(
  courseid varchar(20) primary key,
  coursename varchar(50) constraint up_coursename unique,
                                                --唯一约束（与主键的区别是它可以有一项是空）
)

--表3成绩表
create table score
(
  stuid varchar(20) constraint fk_stuid foreign key references student(stuid)on delete cascade on update cascade,
--这个最突出的地方就是用到了'外键',用外键就是和别的表'联系'    
--级联删除：如果你要删除一个人的信息，找到一列对应项就可以将 它所对应的一行全部删掉
--级联更新：如果把一个学生的编号修改了，那么在成绩表里面对应的把那个学生的编号也修改了
--constraint fk_stuid foreign key是可以省略的
  courseid varchar(20) references course(courseid),
  score int check(score between 0 and 100),
  constraint pk_score primary key(stuid,courseid)
--复合主键
)

--如果一个表已经建好想再添加约束
--增加主键约束
alter table student1
alter column stuid varchar(20) not null
add constraint pk_stuid primary key(stuid)
--要想增加主键约束条件：列中的内容必须不能为空

--增加检查约束
alter table student1
add constraint ck_stusex check(stusex in ('男','女'))

--增加缺省约束
alter table student1
add constraint df_stusex default '男'for stusex

--增加唯一约束
alter table course1
add constraint uq_coursename1 unique (coursename)

--增加外键约束
alter table score1
add constraint fk_stuid foreign key(stuid) references student1(stuid)

--删除约束
alter table score
drop constraint pk_stuid

 

 

use pubs
select * from dbo.authors
--select：查询；从那个表查

select au_id,au_lname from dbo.authors

select au_lname+'.'au_fname from dbo.authors

select title_id,title,[type],price*0.8 as '打折后'from dbo.titles
                     --如果出现关键字就要用到[];打折后=price*0.8、as可以省略

select top 10 *from dbo.authors
--查前十的

select top 10 percent * from  dbo.authors 
--查前百分之10的

select distinct au_id from dbo.authors
--如果au_id有重复的就要用到distinct

--条件查询
select au_lname,au_fname,city from dbo.authors
where state='ca'

select * from dbo.titles
where pub_id='0877'and price>16

select * from dbo.titles
where advance<=5500 and ([type]='business' or [type]='psychology')
--因为and和or没有优先级所以用到了（）

select title,pubdate from dbo.titles
where pubdate between '1991-1-1' and '1991-12-31'
--日期类型日期要用引号

select * from dbo.authors
where state='ca' or state='in' or state='md'
--在查询中or表示‘和’的意思；而and则是两个条件都要满足
where state in ('ca','in','md')

--模糊查询
select phone from dbo.authors
where phone like '415%'
--查以415开头的电话号码，%可以代替多个字符、_下划线一个字符、[]匹配其中的任何一个、[^]不匹配其中的任何一个

select * from dbo.authors
where au_fname like'[cs]hery1'

select pub_name from dbo.publishers
where pub_name like '[^abcdef]%'
where pub_name like '[^a-f]%'                      
--[^]表示不是里面任何一个字符，查出来的结果就是：不含abcdef开头的

--排序查询
select title,price from dbo.titles
--查的是title这一列还有pric列
where [type]='business'
--条件是type这一列中的business
order by price desc
--找到business对应的价格把他们进行排序
--排序要用到order by默认的是升序（asc）降序是desc

--not和<>的用法
select * from dbo.titles
where [type]<>'business'
where not [type]='business'
--找type这一列要找的内容不包含business

select * from dbo.titles
where royalty is not null
--查royalty这一列中不是空的所有项

--统计查询
select count(*) from titles
where [type]='business'
--这里的count是数的意思，要数的是type这一列中business有多少个

select count(distinct state)from dbo.authors
--数作者所在的州有几个但不能重复

select avg(price) from dbo.titles
where type='business'
--查type这一列中的business的平均价格
--avg（）、max（）、min（）、sum（）聚合函数（统计函数）

--分组统计查询
select [type],avg(price) from dbo.titles
--查询type这一列，求每一组对应价格的平均值
group by [type]
--将type这一列中的相同项分到一起

select [type],avg(price) from dbo.titles
where royalty=10
group by [type]
--查type这一列，按照是一样的进行分组,取royalty=10的，再求各组的平均值

select [type],avg(price) from dbo.titles
group by [type]
having avg(price)>19
--求平均价格大于19的组。
--如果聚合函数要做条件一定是在having后面且在group by后面

select getdate()
--得到当前系统日期

select dateadd(yy,3,getdate())
--在当今日期上加三年
select year('1991-1-2')
--只查年
select month('1991-1-2')
--select datediff(--月日年任选一，被减数，减数)

select datediff(yy,stubirthday,getdate())from student

--要想‘连接’就一定要找到共同的列
--交叉连接
select * from dbo.titleauthor cross join dbo.titles
where dbo.titleauthor.title_id=dbo.titles.title_id

--内连接
select buyers.buyer_id ,buyer_name ,prod_id,qty
from buyers inner join sales
on buyers.buyer_id=sales.buyers_id

--外连接
select buyers.buyer-id,buyer_name,prod_id,qty
from buyers left outer join sales
on buyers.bu

--自连接

 

 

--嵌套子查询
select au_id,au_lname,au_fname from dbo.authors
where au_id in
(
  select au_id from dbo.titleauthor
  where title_id in
  (
     select title_id from dbo.titles
     where [type]='popular_comp'
  )
)

--相关子查询
select au_lname,au_fname from dbo.authors
where 100 in
(
  select royaltyper from dbo.titleauthor
  where dbo.authors.au_id=dbo.titleauthor.au_id
)

--自连接的另一种方法      待解决
select emp_id from dbo.employee a
where hire_date in
(
  select hire_date from dbo.employee b
  where a.emp_id<>b.emp_id
)

--子查询中用到增删改
update dbo.titles set price=price*2
where pub_id in
(
  select pub_id from dbo.publishers
  where pub_name='new moon books'
)

--批量插入
insert t_test
--这个t_test是新建的一个表
select emp_id,fname from dbo.employee
--将从表dbo.employee中查到的数据插入到新表中，因为用insert只能插入一条数据所以这里没有values

--any和all
select title,price from dbo.titles
where price>all
(
  select price from dbo.titles
  where [type]='business'
)

select title,price from dbo.titles
where price<any
(
  select price from dbo.titles
  where [type]='business'
)

--exists和not exists
select * from dbo.authors
where not exists
(
  select * from dbo.titleauthor
  where dbo.authors.au_id=dbo.titleauthor.au_id
)

--查询
--单表查询
select  top 3  distinct *           
from
where               between ...and .   in   like    and not  or
group by          max()   min()  sum()  avg()  count()
order by           asc    desc
--连接查询
--交叉连接  cross join    where
--内连接   inner join    on
--外连接  
--左外  left outer join
--右外 right outer join
--全外 full outer join
--自链接
--子查询  in   > all  any  <