Sql 基本知识
create database web -- 创建数据库 名为 web
on
(
name=stu, -- 主数据文件名 stu
filename="d:\haima\stu.mdf", --主数据文件名的存放路径
size=1mb, --文件大小
maxsize=2mb, --文件最大
filegrowth=1mb --文件增长
)
,
(
name=stu1, --辅助数据文件名 stu1
filename="d:\haima\stu1.ndf", --文件存放的地址
size=1mb,
maxsize=2mb,
filegrowth=1mb
)
log on
( --日志文件 stu2
name=stu2,
filename="d:\haima\stu2.ldf",
size=1mb,
maxsize=2mb,
filegrowth=25%
)
-- 删除 数据库 web
drop database web
--添加数据 文件组 名字为 soft
alter database web
add filegroup soft
-- 给文件组 soft 添加 文件stu3
alter database web
add file
(
name=stu3,
filename="d:\haima\stu3.ndf"
) to filegroup soft
-- 数据库属性
sp_helpdb web
-- 重命名数据库 名称 web 改为 web1
sp_renamedb web,web1
sp_renamedb web1,web
-- 修改默认 文件组
-- 把 primary 设置成默认文件组
alter database web
modify filegroup [primary] default
---把 soft 设置成默认文件组
alter database web
modify filegroup soft default
-- 分离数据库
---------------------------------------------------创建表格----------------------------------------------------------------
create table store
(s_id int not null,
s_name varchar(30) not null,
s_adress varchar(50)
)
create table score
(s_id int not null,
s_math int not null,
s_english int not null,
s_chinese int not null,
s_ave int
)
--创建表格,并写入字段
drop table store---删除表格
alter table store add s_sex char null --修改表格定义(未有地表格)
alter table store add s_money int null
alter table score add s_name char(10)
sp_help store---查看表属性
alter table store
alter column s_sex varchar(10) null
--修改已经存在的字段
sp_spaceused store--查看表格的行数,表格所用存储空间地信息
sp_depends store--看对象之间地相互引用关系
sp_rename store, store1---表格重命名
sp_rename store1, store
create unique index s_id_ind on store (s_id)--创建表地唯一索引
drop index store.s_id_ind--删除索引
------------------------------------------------查询表格--------------------------------------------------------
select *
from store
--where s_id=101
select @@version---查询本地服务器版本信息
select @@language---查询本地服务器语言
select s_sex
from store
select distinct s_sex----使用关键字检索数据不重复
from store
select s_name,s_money,s_money+50,s_money-s_money*0.5,s_name+'--'+s_adress
from store
where s_money=1000
-------select语句中使用计算列,还可以使用算术运算符(+ - * / %),逻辑运算符(and& or| xor^ not-)
-------以及字符串连接符号(+)
select s_name 姓名,s_money '原来工资',s_money+50 现在工资,s_money-s_money*0.2 '扣除费用',s_name+'.'+s_adress '所在城市'
from store
where s_money=1000
2)select '姓名'=s_name,'原来工资'=s_money,'现在工资'=s_money+50,'扣除费用'=s_money-s_money*0.2,'所在城市'=s_name+'.'+s_adress
------------注意这里要修改地要放在原来字段地前面
3)select s_name as'姓名',s_money as'原来工资',s_money+50 as'现在工资',s_money-s_money*0.2 as'扣除费用',s_name+'.'+s_adress as'所在城市'
------在select语句中操作列名,可根据需要对查询地列名进行修改,也可为没有标题地列名增加临时标题
---注意:-----使用中文列名是可以不用引号(如例1)但决不使用全角引号------当使用英文列名超过 2 个必须使用引号讲列名括起来
-------------------------------------------逻辑运算符 NOT AND OR 在where条件语句中:
select s_name
from store
where s_sex='M' and s_money>1100
-------使用between关键字(这里包括临界值 '>=和<=')
select s_name,s_money
from store
where s_money between 1100 and 1500--(s_money>=1100 and s_money<=1500)
-----------------------------------------------------------------使用in关键字
select s_name,s_id
from store
where s_id=101
or s_id=102
or s_id=103 -----------(s_id!=101)
-----------可以写成:
select s_name,s_id
from store
where s_id in(101,102,103)-------(not in (101,102,103))
----------------------------------------------------------使用like语句进行模糊查询
select s_name,s_money
from store
where s_name like 'w%'----查询已w开头地
select s_name,s_money
from store
where s_name like 'w_l%'-----(w_l%不可以写成w l%)
select s_name,s_money
from store
where s_name like 'b[a-z]%'---[]可以将查询地范围进步缩小
-----------------------------------------------------------使用order by 给数据排序
select s_id,s_name,s_english
from score
order by s_english desc----(desc是降序排列地默认是升序或者是asc)
select s_id,s_name,'code'=s_english+10
from score
order by 'code' desc
---(在列中使用了计算列,而希望对该列地值进行排列侧使用表达试或为该列临时分配地列名,多列结果排序是用','隔开)
------------------------------------------------编辑维护表格-----------------------------------------------------
select *
from store
insert into store
(s_id, s_name, s_adress, s_sex, s_money)
values ('106','jack','宝鸡','M','')--------(增加一条记录到表store中)
insert into store
values ('106','jack','宝鸡','M','')----(也可执行,但要使用正确地排序)
update store
set s_money='1400'
where s_id=106-----------------(使用update修改数据)
update store
set s_adress='香港',s_sex='W',s_name='any'
where s_id=105---------------------(一次修改多行语句)
update score
set s_math='67',s_english='71',s_chinese='78',s_name='any'
where s_id=105
delete
from store---(from 可选项)
where s_id=106---------------(使用delete删除表格记录)
------------------------------
create table abc ----|(临时创建表格)
( ----|
a int not null, ----|
b char(10), ----|
c int ----|
) ----|
insert into abc ----|
values('111', 'bbb','1230')--|
select * ----|
from abc ----|
----|
truncate table abc ----|
--删除表格中所有数据只留下一个表格地定义,释放所有空间(不可恢复,delete可恢复)
--------------------------------------------视 图----------------------------------------------------------
create view v_store
as
select s_id,s_name,s_money
from store--------------------(创建v-store视图)
select *
from v_store---(查询视图)
-------------------------------
select * -----|在 创建视图 时,并非所有地select子查询都是合法地
from score -----|禁止使用地sql语句:
-----|
create view v_score -----|select into compute 或compute by子句
as -----|order by 字句 使用临时表
select s_id,s_name,s_math-----|
from score -----|
-----|
select * -----|
from v_score -----|
-----|
drop view v_score -----|
-------------------------------删除了视图定义,原来通过视图获得的所有数据依旧在,没有被删除
alter view v_score
as
select s_id,s_name,s_math
from score
where s_math>=70---(修改视图定义)
select *
from v_score
sp_help v_score--浏览视图中各列地列表
sp_helptext v_score---查看自己创建视图时地语句
insert into v_score
(s_id, s_name, s_math)
values ('110','make','100')
---插入失败,因为视图上没有包括基本表中所有属性为not null地行 这里原表中s_english, s_chinese不为null
---如果视图中包含了使用统计函数地结果,或是包含了多个列值地组合----侧插入失败
------不在使用distinct语句的视图中插入值
sp_help score
alter view v_score
as
select s_id, s_name,s_math, s_english, s_chinese
from score
insert into v_score
(s_id, s_name, s_math, s_english, s_chinese)
values ('110','make','100','100','100')
--------------------------------此时所有在表score中属性not null地全在视图v-score中,所以插入成功
-------------使用update更新视图中地数据
----在视图使用了多个底层基本表连接地情况下,每次更新操作只能更新来自木个基本表中的数据列地值,所以要分开更新
----适用于insert 操作地限制也适用于update
select *
from v_score
update v_score
set s_math=90
where s_id=110
------------删除视图中地数据-----------
---当一个视图由两个以上基本表构成时,不允许删除中四数据
---使用于insert操作地限制也适用于delete
delete v_score
where s_id=110
select *
from score--删除了视图v-score中地数据,同时也删除了在基本表score中地记录
-----------------------------对视图进行加密
use web
create view v_test
with encryption
as
select *
from score
select *
from v_test
--------------------加上with encryption 关键字可以防止其它用户查看最初地视图产生代码
-----------高级嵌套查询--------------------------------
select store.s_id,store.s_name,s_money
from store,score
where store.s_name=score.s_name
---------------------使用表格别名
select s1.s_id,s2.s_name,s_money
from store s1,score s2
where s1.s_name=s2.s_name
-----------------------------关键字 union
select s_id
from store
where s_money>1200
union----没有all 侧显示地是不重复地列
select s_english
from score
where s_math>75
order by s_id
-----每个结果集地数据类型都必须相同,更确切四说是兼容
-----每个结果集中列的数量都必须相等,排列顺序必须相互对应
-----如果排序必须把order by写在最后地 select 后面,但是排序的依据是第一个select列表中地列
select s_id
from store
where s_money>1200
union all---------加all 查询结果中会出现重复地行
select s_english
from score
order by s_id
----------统计函数-----------
sum()---返回一个数字列或是计算列地总和
avg()---对一个数字列或计算列求和
min()max()---返回一个数字列或是一个数字表达式地(最小值)(最大值)
count()---返回满足select语句中指定的条件地记录值
count(*)---返回找到的行数
select avg(s_math) 'avg_math'
from score
where s_math>70
select *
from score
-------关键字 distinct 的使用 与统计函数 count(),sum(),avg()
select avg(s_math) 'avreage数学成绩', sum(s_math) 'sum数学成绩', count(distinct s_id) '人数'
from score
-------------------使用关键字count(*)进行行数的统计
select count(*) '英语成绩在70以上'
from score
where s_english>=70
---------使用group by 句子分类
------这不支持对列分配地列名,不支持使用了统计函数的集合列
----****-在select后面每一列 除了出现在统计函数中地列以外,都必须在group by子句中应用
select s_id,s_name,avg(s_math) 'avreage数学成绩'
from score
where s_math>=70
group by s_id,s_name---此时如果s-id和s-name两个只加一个侧错误 见***
-------用having 关键字来筛选结果
select s_id,s_name,avg(s_math) 'avg_math'
from score
where s_math>70 -- |比较下面地语句
group by s_id,s_name -- |
-- |
select s_id,s_name,avg(s_math) 'avg_math'
from score
where s_math>70 -----where是在求出结果前,从表中选择所需要地行
group by s_id,s_name
having avg(s_math)>60-----having是在统计计算后产生的结果中,选择需要地行
------使用all关键字
----group by子句提供了all 只有当select语句中同时使用了where字句地情况下才有意义
select s_id,s_name,s_english
from score
where s_english>70
group by all s_id,s_name,s_english
order by s_id desc ---group在order前面
------使用了all关键字,所有被group by字句分类地数据集都将出现在结果中,即使该列不满足where给出地查询条件
----使用rollup 关键字,既要进行分类求和 又要求其总和时
select s_id,s_name,sum(s_math) 'math总和'
from score
where s_math>=70
group by s_id,s_name
with rollup
---------使用关键字compute 和 compute by
---compute同时观察查询所得的各列数据地细节 以及统计各列数据所产生地总和（分类后地和，所有数据地总和）
select s_name,s_english
from score
order by s_id desc
compute sum(s_english)
select s_name,s_english
from score
order by s_id desc
compute sum(s_english) by s_id---这里by 后面地要求与order后面地相对应（针对某一type分类进行统计和）
---每一个分类地结果进行统计计算 并返回一行相应地计算结果
------------------使用嵌套查询
select s_name,s_id
from store
where s_id in
(
select s_id
from score
where s_english>=80
)
order by s_id desc
----------------------------在嵌套查询中使用exists关键字
select s_name,s_id
from store
where exists---子查询不为空侧为真
(
select *
from score
where s_english>=80 and store.s_id=score.s_id
)
order by s_id desc------次例题与前面地结果相同
------------使用not exists 和 not in关键字(解决非成员关系型，和非存在关系型 查询难题)
select s_name,s_id
from store
where s_id not in
(
select s_id
from score
where s_english>=80
)
order by s_id desc
select s_name,s_id
from store
where not exists
(
select *
from score
where s_english>=80 and store.s_id=score.s_id
)
order by s_id desc
-----------------------------------数据完整性---------------------------------------
----------------------存储过程与触发器-----------------------------
----支持创建临时存储过程 1.局部临时存储过程 在名字前加‘#’2.全局存储过程 加‘##’
-----1与2地区别 1只能由一个用户连接使用，但连接断开后将自动删除 2对任何用户连接使用，服务器关闭时才删除
create procedure s_store
as
select s1.s_id,s2.s_name,s_math
from store s1
join score s2
on s1.s_id=s2.s_id
-------join abc s3
-------on s3.s_id= s2.s_id
s_store----执行存储过程 必须是第一句 如果不是侧使用exec关键字
sp_helptext s_store----查看创建存储过程的一般信息
----whith encryption 在存储过程中加入时 侧通过企业管理器看见的是乱码
drop procedure s_store ----删除存储过程
------修改存储过程
alter procedure s_store
as
..............
