SQL SERVER 2005练习集1--读书笔记

来源：互联网发布：极限矩阵iplay 编辑：程序博客网时间：2024/05/22 06:50

入门篇

////////////////////////////////////////////

% : 表示0~n个任意字符

_ : 表示单个任意字符

[] :表示在括号里列出的任意字符

[^]:表示任意个没有在括号中列出的字符

***********************

系统数据库

MASTER数据库:

记录着SQL SERVER2005系统中的所有系统级别的信息,包括所有的登录帐户和系统配置以及所包含的数据库、

数据文件的位置；是SQL SERVER启动的第一个入口，是所有系统数据库中的重中之重，一但损坏就无法启动SQL

SERVER。

MODEL数据库：

是新建数据库的模板。

TEMPDB数据库：

保存了所有的临时表和临时存储过程。

MSDB数据库：

提供了SQL SERVER2005代理程序调度、报警、作业以及记录操作员等活动。

/////////////////////////////////////////

use demo

/*创建用户自定义(newtype)数据类型*/

/*exec sp_addtype newtype ,"char(10)"

go*/

/*删除自定义(newtype)数据类型*/

/*exec sp_droptype newtype

go*/

/*更改表的属性*/

/*alter table family

alter column id int not null

go*/

/*创建一个存储员工信息的表*/

/*create table employee

(

/*为表列产生一个自动生成的序号*/

id int identity(1,1),

[name] char(20),

gender char(4)

)

go*/

/*查看表的属性*/

execute sp_help employee

////////////////////////////////////////////

use school

/*create table student

(

id int not null,--学号

[name] char(20),

gender char(2),--性别

age smallint,

grade smallint,--年级

class smallint,

tercher_id int,

parents_id int,

)

on students --将信息创建在students文件组上

go*/

/*create table teachers

(

id int not null,

[name] char(20),

gender char(2),

age smallint,

subject char(20),

teaching_age smallint,--教学年龄

position char(20)--教师职位

)

on teachers

go*/

/*create table parents

(

id int,

father char(20),

mother char(20),

f_telephone char(20),

m_telephone char(20),

phone char(20),--固话

f_company varchar(50),

m_company varchar(50),

f_position char(20),--职位

m_position char(20)

)

on parents

go*/

/*带空格的表格名称的用法*/

create table [学生]

(

学生编号 int,

学生姓名 char(20),

学生性别 char(2),

学生年龄 smallint,

学生所在年级 smallint,

学生所在班级 smallint,

老师的编号 int,

家长的编号 int

)

on students

/* 录入家长的基本信息*/

insert into parents

(

id,father,mother,f_telephone,m_telephone,phone,f_company,m_company,f_position,m_position

)

values

(

2001,'Edd.Cott','Rose.Li','989001','338453',null,'IBM','BMW','Engineer','Worker'

)

insert into parents

(

id,father,mother,f_telephone,m_telephone,phone,f_company,m_company,f_position,m_position

)

values

(

2002,'Jack.Chen','Lily','757980',null,'00-76876','Oracle',null,'Mgr.',null

)

insert into parents

(

id,father,mother,f_telephone,m_telephone,phone,f_company,m_company,f_position,m_position

)

values

(

2003,'Tom.Wang','Misto','991871','777444','00-23534','HP',null,'Worker',null

)

insert into parents

(

id,father,mother,f_telephone,m_telephone,phone,f_company,m_company,f_position,m_position

)

values

(

2004,'Peter.Zhang',null,'887653','597555','00-45654','Microsoft','Ebay','Engineer','Engineer'

)

select * from parents

/*insert into teachers

(

id,[name],gender,age,subject,teaching_age,position

)

values

(

101,'MS.Zhang','女','30','语文','10','特级教师'

)

insert into teachers

(

id,[name],gender,age,subject,teaching_age,position

)

values

(

102,'SongDan','女','23','法语','1','初级教师'

)

insert into teachers

(

id,[name],gender,age,subject,teaching_age,position

)

values

(

103,'Jacky','男','50','数学','31','学年组长'

)

insert into teachers

(

id,[name],gender,age,subject,teaching_age,position

)

values

(

103,'CoCo','女','48','化学','25','特级教师'

)

go*/

select * from teachers

//////////////////////////////////

use school

/*按着性别统计分组(group by)*/

select count(id),gender

from student

group by gender

/*按着性别统计分组(group by)*/

select count(id),gender

from student

group by gender

having count(id)>2 --统计人数超过2人的数据

select * from student

order by name desc--排序

/*select top 2 *--查找头两个

from student

go*/

/*查找不重复的信息distinct*/

select distinct teacher_id

from student

--IN字句

select * from parents

where f_position

(

'worker',

'engineer'

)

与上面的效果相同

select * from parents

where f_position='worker' or f_position='engineer'

--not in 字句

select * from parents

where f_position

not in

(

'worker',

'engineer'

)

--计算总数

select id,f_telephone,m_telephone

from parents

order by id

compute count(f_telephone),count(m_telephone)

--compute by 字句,分类计算总数

select f_position,f_telephone,m_telephone

from parents

order by f_position

compute count(f_telephone),count(m_telephone)

by f_position

--表数据量的统计

select count(*) as 全表数据量统计

from parents

select count(phone) as 家庭电话数据量统计

from parents

--笛卡尔乘积

select student.name,parents.father,parents.mother

from student,parents

--避免笛卡尔乘积

select student.name,parents.father,parents.mother

from student,parents where student.parents_id=parents.id

--内联接(join 联接两个不同的表,on用于给这两个表之间联接条件)

select student.name,parents.father,parents.mother

from student inner join parents

on student.parents_id=parents.id

--使用表别名

select s.name as 学生姓名,

p.father as 学生父亲名,

p.mother as 学生母亲名

from student as s inner join parents as p

on s.parents_id=p.id

--使用右外联接(right outer join)

select s.name as 学生姓名,

p.father as 学生父亲名,

p.mother as 学生母亲名

from student as s right outer join parents as p

on s.parents_id=p.id

--使用左外联接(left outer join)

select s.id as 学生编号,

s.name as 学生姓名,

p.father as 学生父亲名,

p.mother as 学生母亲名

from student as s left outer join parents as p

on s.parents_id=p.id

--使用完整外联接(full outer join)

select s.id as 学生编号,

s.name as 学生姓名,

p.father as 学生父亲名,

p.mother as 学生母亲名

from student as s full outer join parents as p

on s.parents_id=p.id

//////////////////////////////////////////////////////////////

--自联接

use demo

/*SET IDENTITY_INSERT employee ON

insert into employee

(id,[name],gender,manage_id)

values

(1001,'Peter','男',1001)

SET IDENTITY_INSERT employee off

go*/

/*SET IDENTITY_INSERT employee ON

insert into employee

(id,[name],gender,manage_id)

values

(1002,'Alice','女',1001)

SET IDENTITY_INSERT employee off

SET IDENTITY_INSERT employee ON

insert into employee

(id,[name],gender,manage_id)

values

(1003,'Tom','男',1002)

SET IDENTITY_INSERT employee off

SET IDENTITY_INSERT employee ON

insert into employee

(id,[name],gender,manage_id)

values

(1004,'Jack','男',1001)

SET IDENTITY_INSERT employee off

go*/

/*select e.id as 员工编号,

e.name as 员工姓名,

p.id as 其上司员工编号,

p.name as 其上司姓名

from employee as e

inner join

employee as p

e.id=p.manage_id

go*/

/////////////////////////////////////////

--数据导入

insert into 学生

(学生编号,学生姓名,学生性别,学生年龄,学生所在年级,学生所在班级,老师的编号,家长的编号)

select id ,[name],gender,age,grade,class,teacher_id,parents_id

from student where gender='女'

--select * from 学生

--修改一行

/*update student

set [name]='Jack.Chen' where id=2006003

go*/

--select * from student where id=2006003

--修改多行

/*update student

set teacher_id=103

where id in

(

2006001,

2006002,

2006003

)*/

--select * from student where teacher_id=103

--删除一行

/*delete from student

where id=2006005

select * from student

go*/

--删除多行

delete from student

where id in

(2006001,2006002)

select * from student

--删除所有

delete from student

select * from student

--设主键

/*先将ID的属性变为空*/

alter table dbo.student

alter column id int not null

/*设主键*/

alter table dbo.student

add constraint pk_student_id primary key(id)

--建一个名为TEST的表,带主键

/*create table test

(

id int primary key,

[name] char(20),

city char(10),

)

go*/

--查看表属性

--execute sp_help test

--go

--创建多列主键

/*create table book

(

isbn int,

copy_no int,--图书副本号

book_name char(20),

constraint pk_book_isbn_copy_no primary key(isbn,copy_no)--定义表级主键约束

)

go*/

exec sp_help book

--成年人读者信息表

/*create table adult_member

(

member_id int, --读者编号

member_name char(10),

member_job char(10),

member_genber char(2),

constraint pk_adult_member_id primary key(member_id)

)

--非成年人读者信息

create table junior_member

(

member_id int,

adult_member_id int,

member_name char(10),

member_genber char(2),

constraint pk_junior_member_id primary key(member_id),

constraint fk_junior_member_id foreign key(member_id)

references adult_member(member_id)

)

exec sp_help adult_member

go*/

--创建全体读者的信息

create table member

(

member_id int,

member_name char(10),

member_gender char(2),

member_age int,

constraint pk_member_id primary key(member_id)

)

--为表adult-member创建一个外键约束

alter table adult_member

add constraint fk_adult_member_id_member_id

foreign key(member_id) references member(member_id)

--为表junior-member创建一个外键约束

alter table junior_member

add constraint fk_junior_member_id_member_id

foreign key(member_id) references member(member_id)

alter table student

--为学生与家长间建立关联关系

add constraint fk_student_parents_id_parents_id

foreign key(parents_id)

references parents

--为学生与老师间建立关联关系

alter table student

add constraint fk_student_tescher_id_teacher_id

foreign key(teacher_id)

references teachers

--先删除外键再删除主键

--先将学生表上创建的与家长表关系的外键删除

alter table student

drop constraint fk_student_parents_id_parents_id

--删除家长表上的主键

alter table parents

drop constraint pk_parents_id