SQL Server系列(2)--T-SQL语句

学习视频：

链接：http://pan.baidu.com/s/1i3BAJpr 密码：dja8

常用术语：

关系：关系即二维表，每个关系有一个关系名，即表名
记录：表中的行
域：表中数据的取值范围
关联：不同数据库表之间的数据彼此联系的方式
关键字：属性或属性的组合，可以用于唯一标识一条记录
外部关键字：如果表中的一个字段，不是本表中的关键字，而是其他表的关键字，称之为外部关键字
数据冗余：指数据库中的重复数据
数据完整性：指数据的一致性
插入异常，更新异常，删除异常




T-SQL语言组成：
DML：数据库操作语言（查询select，插入insert，删除delete，更新update等）
DCL：数据库控制语言（控制数据库组件的存取许可、权限等）
DDL：数据库定义语言（建立数据库、数据库对象、建立视图等，多以create开头）






--创建数据库
create database Mydatabase
on
(
name=Mydatabase_data,
filename='f:\Database\Mydatabase.mdf',
size=6,
maxsize=12,
filegrowth=19%
)
log on
(
name=Mydatabase_log,
filename = 'f:\Database\Mydatabase_log.ldf',
size=1,
maxsize=8,
filegrowth=10%
)


--创建表
use Mydatabase
create table Teacher
(
教师编号 int,
教师职称 varchar,
教师年龄 int,
工资 money
)




--添加数据库文件
alter database Mydatabase
add file
(
name=Mydatabase2,
filename='f:\Database\Mydatabase2.mdf',
size=6
)


--添加数据库日志文件
alter database Mydatabase
add log file
(
name=Mydatabase_log2,
filename='f:\Database\Mydatabase_log2.ldf',
filegrowth=10%
)




--删除数据库文件
alter database Mydatabase
remove file Mydatabase2


--删除数据库日志文件
alter database Mydatabase
remove file Mydatabase_log2




--修改数据库表名
exec sp_rename "Teacher2","Teacher"


--修改表中的字段
use Mydatabase
alter table Teacher
add Email varchar(50)


--修改表中的字段并赋默认值
use Mydatabase
alter table Teacher
add Email varchar(50) default '[e-mail]'


--删除字段
use Mydatabase
alter table Teacher
drop column Email


--删除表
use Mydatabase
drop table 学生表


--插入记录
use Mydatabase
insert into Teacher(教师编号,教师职称,教师年龄,工资,Email) values(20,'李老师',30,3000,'[e-mail]')


--数据更新
use Mydatabase
update 教师表 set 教师职称='高级教师',工资=4800 where 教师编号=27


--数据更新时进行运算
use Mydatabase
update 教师表 set 工资=工资*1.2 where 教师编号=1




--筛选数据
select *from 网站职员表 where 年龄>25 or 工资<3500
select *from 网站职员表 where 年龄>25 and 工资<3500
select *from 网站职员表 where 年龄>=25


运算符优先级：
括号，not(非)、正号、负号，乘、除，加、减，比较运算符，and，or


--条件筛选
use Mydatabase
select *from 网站职员表 where (毕业院校='武大' or 毕业院校='江大') and 年龄>25


--连续使用or/and运算符
select *from 网站职员表 where 毕业院校='江大' or 毕业院校='武大' or 毕业院校='武理工'
select *from 网站职员表 where 年龄=23 and 毕业院校='武大' and 工资=3780


--in关键字
use Mydatabase
select *from 网站职员表 where 毕业院校 in('武大','上海交大','华科')


--将字段内容设为控制
update 网站职员表 set 家庭住址=null where 职员编号=8
--查询内容为空值的字段
select *from 网站职员表 where 家庭住址 is null
--查询内容不为空值的字段
select *from 网站职员表 where 家庭住址 is not null


--查找指定范围内内容
select *from 网站职员表 where 工资 between 3200 and 3600
--查找指定范围外内容
select *from 网站职员表 where 工资 not between 3200 and 3600


--消除某一字段重复值的内容
select distinct 毕业院校 from 网站职员表


--查询与某一指定字段某内容相同的其他字段(查找与小江工资相同的其他员工）
select *from 网站职员表 where 工资 in(select 工资 from 网站职员表 where 职员姓名='小红')


--创建一个表,并创建标识列和主键
use Mydatabase
create table 网站经营项目
(
项目编号 int identity(1,1) primary key,
项目名称 varchar(50),
负责人 varchar(20),
合作单位 varchar(50)
)


--跨表查询
select *from 网站职员表 where 职员姓名 in (select distinct 负责人 from 网站经营项目)


--统计数量
select COUNT(*) as 记录行数 from 网站职员表
select COUNT(家庭住址) as 家庭住址 from 网站职员表
--求和
select SUM(工资) as 员工工资总额 from 网站职员表
--求平均
select avg(工资) as 员工平均工资 from 网站职员表
select avg(年龄) as 员工平均年龄 from 网站职员表
--求最值
select max(工资) as 员工最高工资 from 网站职员表
select min(工资) as 员工最低工资 from 网站职员表
select max(年龄) as 员工最高年龄 from 网站职员表
select min(年龄) as 员工最低年龄 from 网站职员表


--查询工资高于平均工资的员工
select *from 网站职员表 where 工资 >(select AVG(工资) from 网站职员表)


--向新增字段添加值
update 网站职员表 set 奖金=500


--查询每个员工的总收入
select SUM(工资+奖金) from 网站职员表 as 员工总收入 where 职员编号=5
select 职员编号,职员姓名,工资+奖金 as 员工总收入 from 网站职员表


--like运算符
select *from 网站职员表 where 毕业院校 like '%大'
select *from 网站职员表 where 毕业院校 like '上%'
select *from 网站职员表 where 毕业院校 like '%海%'


--数据汇总分组查询（by后的字段必须为order by 后的一员）
use Mydatabase
select *from 网站职员表 order by 年龄 compute max(工资),min(工资),avg(工资) by 年龄
select *from 网站职员表 order by 毕业院校 compute max(工资),min(工资),avg(工资) by 毕业院校


--数据分组
use Mydatabase
select 毕业院校, MAX(工资) as 最高工资,MIN(工资) as 最低工资,AVG(工资) as 平均工资,SUM(工资) as 工资总额,SUM(工资+奖金) as 员工收入总额 from 网站职员表 group by 毕业院校


--带分组的嵌套查询(查询出某一院校中工资高于5000的人)
use Mydatabase
select *from 网站职员表 where 毕业院校 in(select 毕业院校 from 网站职员表 group by 毕业院校 having SUM(工资)>4000)






--位词查询(存在与否)（只要集合中包含毕业院校为武大的职员，则显示所有职员）
use Mydatabase
select *from 网站职员表 where exists(select *from 网站职员表 where 毕业院校='武大')




--量词查询
use Mydatabase
select *from 网站职员表 where 工资>any(select 工资 from 网站职员表 where 毕业院校='武大')
select *from 网站职员表 where 工资>all(select 工资 from 网站职员表 where 毕业院校='武大')




--top查询
use Mydatabase
select top 6  *from 网站职员表
select top 20 percent  *from 网站职员表


--保存查询结果(保存到另一个表中,若该表不存在,则创建该表)
use Mydatabase
select *into 第二网站职员表  from 网站职员表 where 工资>3500


--集合并运算(将两个表中数据混合在一个表中显示)
select *from 网站职员表 union select * from 第二网站职员表
--集合交运算
select *from 网站职员表 interset select * from 第二网站职员表


--连接查询
select *from 网站职员表,网站经营项目
select *from 网站职员表,网站经营项目 where 网站职员表.职员姓名=网站经营项目.负责人
--内连接
select *from 网站职员表 inner join 网站经营项目 on 网站职员表.职员姓名=网站经营项目.负责人
--左连接(左边表中所有项目都显示，右边表中满足条件才显示)
select *from 网站职员表 left join 网站经营项目 on 网站职员表.职员姓名=网站经营项目.负责人
--右连接(右边表中所有项目都显示，左边表中满足条件才显示)
select *from 网站职员表 right join 网站经营项目 on 网站职员表.职员姓名=网站经营项目.负责人
--全连接(两个表中所有项目都显示)
select *from 网站职员表 full join 网站经营项目 on 网站职员表.职员姓名=网站经营项目.负责人