库
创建数据库
create database 数据库名字
on primary
(
name='f5',
filename='D:\1\f5.mdf',
size=3mb,
maxsize=unlimited,
filegrowth=1%
)
log on
(
name='f6',
filename='D:\1\f6.ldf',
size=3mb,
maxsize=300mb,
filegrowth=1mb
)
修改数据库名字
语法: alter database 原数据库名字 modify name=新数据库名字
给数据库添加辅助文件和日志文件
alter database 数据库名字
add file
(
name='',
filename='',
size=,
maxsize=,
filegrowth=
)
alter database 数据库名字
add log file
(
name='',
filename='',
size=,
maxsize=,
filegrowth=
)
删除数据库
语法:drop database 数据库名
切换数据库
语法:use 数据库的名字
表
数据类型
精确数字
bigint 大正型 8字节 int 整型 4字节 smallint 小整型 2字节 tinyint 微整型 1字节 bit 位类型 只有0和1两种取值,在输入0以外的其他值时,系统军=均吧它看为1 decimal(p,s) p总位数,默认18;s小数位数,默认0 numeric(p,s) 同上
日期和时间
datetime 8字节 smalldatetime 4字节
字 符串
char(n) n表示所有字符所占的存储空间,取值1~8000 varchar(n) 变动长度,节省空间,其他同上
二进制字符串
binary(n) n+4字节 n最大为8000,用于存储图像等数据 varbinary(n) n+4字节 同上
创建表
create table students
(
学号 int not null,
姓名 varchar(255) not null,
性别 varchar(255),
年龄 int,
年级 varchar(255),
学院 varchar(255),
专业 varchar(255)
)
删除表
语法:drop table 表名
添加(列)属性
语法: alter table 表名 add 属性 数据类型[(长度)][null | not null]
修改数据类型
语法: alter table 表名 alter column 属性 数据类型
删除(列)属性
语法: alter table 表名 drop column 属性
修改列名
语法: exec sp_rename '表名.列名','新列名'
修改表名
语法: exec sp_rename '原表名','新表名'
约束
(1) 主键约束 primary key 唯一确定表中每一条记录的标识符 (2) 外键约束 foreign key 用于建立和加强两个表数据之间的连接 (3) 唯一约束 unique 指定一个列或多个列的值具有唯一性(可以为空) (4) 检查约束 check 设置检查条件以限制输入值 (5) 默认约束 default 插入操作中没有提供输入值时系统会自动加上指定值
创建约束
语法: alter table 表名 add constraint 约束名 约束类型 (具体的约束说明)
**·primary key:**主键约束(一个表中只能有一个主键约束且为空的列不能创建主键约束) **·unique:**唯一约束 **·check:**检查约束 例如:
alter table students
add constraint ck_年龄 check (年龄>18)
**·default:**默认约束(例如:add constraint df_性别 default '男' for 性别 ) **·foreign key:**外键约束(注意:两个表列中的数据类型一致,包括长度;引用的列必须是主键约束或唯一约束;列名尽量一致;本表该列数据为空) 例如:
alter table students
add constraint fk_学号 foreign key (学号)
references stu (学号)
删除约束
语法: alter table 表名 drop constraint 约束名
例:创建图书管理数据库
create database tsgl
on primary
(
name='f8',
filename='D:\1\f8.mdf',
size=3mb,
maxsize=100mb,
filegrowth=1%
)
log on
(
name='f9',
filename='D:\1\f9.ldf',
size=3mb,
maxsize=100mb,
filegrowth=1mb
)
use tsgl
create table ts
(
书号 char(20) primary key,
书名 char(20),
出版社 char(25),
类别 char(20),
作者 char(10),
出版时间 datetime,
价格 int check (价格>0)
)
create table dz
(
编号 char(20) primary key,
姓名 char(20),
单位 char(25) default 'xxxx',
性别 char(5) check (性别='男' or 性别='女'),
电话 char(15) unique
)
create table jy
(
书号 char(20) foreign key references ts (书号),
编号 char(20) foreign key references dz (编号),
借阅日期 datetime,
还书日期 datetime,
还书标识 char(2),
primary key (书号,编号)
)
T-sql
标识符: 常规标识符、分隔标 识符 命名规则: 第一个字符:英文大小写字母、下划线(_)、@、# 后续字符:以上再加上十进制数字、美元符号($) 另:标识符不能是T-sql保留字,不允许嵌入空格或其他特殊字符
常量:
- 字符串常量:单引号内
- Unicode字符串:前缀必须是大写N
- 二进制常量:前缀为0x并且是十六进制字符串
- bit常量:用0和1表示
- datatime常量
- integer:没有引号且不包含小数点的数字字符串(整数)
- decimal常量:没有引号且包含小数点的数字字符串(小数)
- float和real常量:科学计数法
- money:前缀为货币符号$
局部变量声明语法: declare @variable_name datatype [,@variable_name datatype]... 其中,@variable_name是局部变量的名字,必须以“@”符号开头。datatype是为该局部变量指定的数据类型。
全局变量:事先定义好的变量,不允许用户创建(declare)或修改(set,select),可随时调用,全局变量的名字以@@开头
变量赋值语法格式: set @local_variable=expression 一次只能给一个变量赋值 select @local_variable=expression[,...n] 可以多个,中间逗号隔开
输出:print @变量名 (一次只能输出一个变量)
运算符
连接运算符’+‘:可将字符串连接起来 例:
declare @name char(20)
set @name='啥'
print '你说的'+@name
一元运算符: +:正 -:负 ~:逻辑非,位反 优先级:一元>算数>比较>逻辑>赋值
流控制语句
begin…end
用于将多个语句组合成一个逻辑块 语法: begin {sql_statement} end (补充:{}代表所包含内容必写,[]代表所包含内容可选可写,| 代表两边内容二选一) 即: begin 两条或两条以上T-sql语句 end 例:声明两个数字,交换位置
declare @x int,@y int,@t int
set @x=1
set @y=2
begin
set @t=@x
set @x=@y
set @y=@x
end
print @x
print @y
if…else
语法: if boolean_expression {sql_statement | statement_block} [else {sql_statement | statement_block}] 例:输入一个坐标值,然后判断它在哪一个象限
declare @x int,@y int
set @x=8
set @y=-3
if @x>0
if @y>0
print '@x@y位于第一象限'
else
print '@x@y位于第四象限'
else
if @y>0
print '@x@y位于第二象限'
else
print '@x@y位于第三象限'
while,break与continue
语法: while boolean_expression begin {sql_statement | statement_block} [break] [end] {sql_statement | statement_block} end 例:求1到10之间的偶数和(包括10)
declare @n int,@sum int
set @n=1
set @sum=0
while @n<=10
begin
if @n%2=0
set @sum=@sum+@n
set @n=@n+1
end
print @sum
declare @n int,@sum int
set @n=0
set @sum=0
while @n<10
begin
set @n=@n+1
if @n%2=0
set @sum=@sum+@n
else continue
end
print @sum
case(多分支语句)
可根据表达式的真假来确定是否返回某个值 语法: case when 判断条件 true then 语句 [...] [else 语句] end 例:根据学生分数给出判定,90-100(包含90)显示excellent,70-90(包含70)显示good,60-70显示(包含60)显示pass,其他分数显示fail
declare @grade int,@message varchar(20)
set @grade=58
set @message=
case
when @grade>=90 and @grade<=100 then 'excellent'
when @grade>=70 and @grade<90 then 'good'
when @grade>=60 and @grade<=70 then 'pass'
else 'fail'
end
print @message
waitfor(延迟语句)
可以将它之后的语句在一个指定的时间间隔之后执行,或在未来的某一指定时间执行
语法:waitfor delay 'time' | time 'time' 说明:其中delay用于设定等待的时间,最多可达24小时。time用于设定等待结束的时间点。“time”的数据类型必须为datetime,格式为’hh:mm:ss‘,不能包含日期。
例1:3秒后显示’傻瓜你好‘
waitfor delay '00:00:03'
print '傻瓜你好'
例2:15点显示’傻瓜来了‘
waitfor time '15:00:00'
print '傻瓜来了'
goto
用来改变程序执行的流程,使程序跳到标识符指定的程序行再继续往下执行 语法:goto 标识符 程序行的标识符需要在其名称之后加上一个冒号“:”
例:用goto实现跳转输出小于等于3的值
declare @x int
select @x=1
loving:
print @x
select @x=@x+1
while @x<=3
goto loving
return无条件退出语句
从查询或过程中无条件退出。此时位于return后面的语句是不被执行的 语法:return [integer_expression] integer_expression整数型
例:
declare @x int
set @x=3
if @x>0
print '遇到return之前'
return
print '遇到return之后'
dml
查询
简单来说:selecet 列名 from 表名 补充一些关键字: (1)distinct:去重复(只针对一列) 语法:select distinct ...from... (2)top:规定返回记录的数目 语法:select top n *|列名 from 表名(n为行数) 若组合使用:select distinct top n ...from... (去掉重复数据后,后面数据会往前补) (3)union:用于合并两个或多个 SELECT 语句的结果集 注意:union内部的每个select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 select 语句中的列的顺序必须相同。 语法: select 列名 from 表1 union select 列名 feom 表2 说明:默认地,union操作符选取不同的值。如果允许重复的值,请使用union all 语法: select 列名 from 表1 union all select 列名 from 表2 说明:union结果集中的列名总是等于union中第一个select语句中的列名
复杂的:
selecet select_list
[into new_table_name]
from table_list
[where search_conditions]
[group by group_list]
[having search_conditions]
[order by order_list [asc | desc]]
如果同时出现,几个关键词前后顺序不能颠倒
别名查询(对数据显示的标题进行修改)
(1)select 列名 '别名' from 表名 (2)select '别名'=列名 from 表名 (3)select 列名 as '别名' from 表名 同时可对列进行计算:select 学号 '调整前',学号-10 '调整后' from student
范围搜索条件
between…and… 包括范围:select 列名 from 表名 where 列名 between ... and ... (包含边界值) 排他范围:(where 列名 not between ... and ... )
列表搜索条件
in 用于选择与列表中的任意值匹配的行 select 列名 from 表名 where 列名 in('内容1','内容2')
搜索条件中的字符匹配符
用like关键字搜索与指定模式匹配的字符串、日期或时间值 %:替代零个或多个字符 _:仅替代一个字符 []:代表指定范围内的单个字符,[]中可以是单个字符(如[acef]),也可以是字符范围(如[a-f]) []:代表不在指定范围内的单个字符,[]中可以是单个字符(如[acef]),也可以是字符范围([a-f]) 例:……where 班级名称 like 'pu%'
涉及空值的查询
空值(NULL)表示不确定的值 判断取值为空的语句格式:where 列名 is null 判断取值不为空的语句格式:where 列名 is not null
聚合函数
sum([distinct] 列名):计算列值总和; avg([distinct] 列名):计算列值平均值; max([distinct] 列名):计算列值最大值; min([distinct] 列名):计算列值最小值; count(*):统计表中元组个数; count([distinct] 列名):统计本列列值个数; 上述函数除count外,其他函数在计算过程中均忽略null值 语法:select 聚合函数 from 表名
数据分组语法
select 列名,聚合函数 from 表名 group by 列名 例:查询每个年级的总人数和班级数量
select 年级,sum(人数) as 年级总人数,count(班级编号)as 班级数量
from 班级信息 group by 年级
having通常和group by子句一起使用。相当于一个用于组的group子句,指定组的搜索条件。having子句可以包含聚合函数,但where不可以。 例:select 学号,sum(成绩) as '总成绩' from 成绩表 group by 学号 having sum(成绩)<600
order by
ORDER BY 默认升序,DESC表示降序,ASC表示升序,desc 或者 asc 只对它紧跟着的第一个列名有效,其他不受影响,仍然是默认的升序 例:select * from 成绩表 order by 成绩 desc
表连接
内部连接
内部连接:只有匹配到相同的数据才会输出 (1)等值连接 语法1:select select_list from 表名1.表名2 where 表1.列=表2.列 语法2:select select_list from 表1 [inner] join 表2 on 表1.列=表2.列 注:select子句列表中,每个目标列名前都要加上基表名称,即表名.列名 例:查询学生的学号,姓名,性别,以及所在的班级名称和年级 法1
select 学生信息.学号,学生信息.姓名,学生信息.性别,班级信息.班级名称,班级信息.年级
from 学生信息,班级信息
where 学生信息.班级编号=班级信息.班级编号
法2
select 学生信息.学号,学生信息.姓名,学生信息.性别,班级信息.班级名称,班级信息.年级
from 学生信息 inner join 班级信息
on 学生信息.班级编号=班级信息.班级编号
简写:select 学生信息.学号,姓名,性别,班级信息.班级名称,年级 from……
可结合别名查询 例:
select s.学号,s.姓名,s.性别,c.班级名称,c.年级
from 学生信息 as s,班级信息 as c
where s.班级编号=c.班级编号 [and 其他条件……]
(2)非等值连接 例:查询成绩大于40的学生个人情况,并按照成绩降序排列
select g.课程编号,成绩,s.姓名,性别
from 成绩表 as g,inner join 学生信息 as s
on g.学号=s.学号 and g.成绩>40
order by g.成绩 desc
外部连接
外部连接:会返回from子句中提到的至少一个表或视图的所有行 (1)左外部连接(left outer join):对连接条件中左边的表不加限制,如果左表的某行在右表中没有找到匹配的行,则结果集中的右表的相对应的位置为null 例:查询所有学生的学号,姓名,课程编号,成绩
select 学生信息.学号,姓名,成绩表.课程编号,成绩
from 学生信息 left outer join 成绩表
on 学生信息.学号=成绩表.学号
(2)右外部连接(right outer join):对连接条件中右边的表不加限制,如果右表的某行在左表中没有找到匹配的行,则结果集中的左表的相对应的位置为null
(3)全外连接(full outer join):对两个表都不加限制,所有两个表中的行都会包括在结果集中,如果匹配不到,则在各自对应位置上显示null
子查询:用来表示where子句的条件
子查询可以嵌套在select、insert、update、delete语句中,总是使用圆括号括起来 (1)嵌套子查询 例1:查询计算机系的学生选修了哪些课程 select * from sc where sno in (select sno from studemt where sdept=‘计算机系’) 温馨提醒:where子句后的条件要什么,子查询就查什么
例2:查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩
select sno,grade
from sc
where cno='c02' and grade>(select avg(grade)from sc where cno=‘c02’)
(2)相关子查询(单值子查询) 只返回一个值,然后将一列值与查询返回的值进行比较 例:查询和‘ff’在同一班级的学生的信息
select s.学号,姓名,c.班级名称,年级
from 学生信息 as s,班级信息 as c
where s.班级编号=c.班级编号 and
s.班级编号=(select 班级编号 from 学生信息 where 姓名='ff')
在查询基础上创建新表select…into
语法:select 列名 into 新表名 from 表名
添加数据
语法:
insert into 表名 [(列名1,列名2,……)]
values (值1,值2,……)[,(值3,值4,……)]
注:没有指定要插入数据的列名时需要列出插入行的每一列数据 例:
insert into student (学号,姓名,性别,年龄,年级,学院,专业)
values(2012,'张三','男',18,'大一','计算机','计类')
使用insert和select插入
可以将一个或多个表的值添加到另一个表中 语法:
insert into 表名1 [(列名1,列名2……)]
select 列名 from 表名2 [where 限定条件]
注:表1中所有数据类型都应和表2一致
例:
insert into 学生信息
select 学号,姓名,性别,年龄 from student where 学院='计算机'
修改数据
语法:update 表名 set 列名=表达式 [where 限定条件] 注:如果条件不统一,要更改的值也不一样,需要分开来写 例1:update 学生信息 set 学号=2011,年龄=18 where 姓名='张四' 例2:将计算机院全体学生成绩加5 (1)子查询 update 成绩表 set grade=grade+5 where 学号 in (select 学号 from 学生信息 where 学院=‘计算机’) (2)表连接 update 成绩表 set grade=grade+5 from 成绩表 join 学生信息 on 成绩表.学号=学生信息.学号 where 学院=‘计算机’
删除数据
delete 语句可删除表或视图中的一行或多行 语法:delete from 表名 where 限定条件 注:若不加“where 限定条件”则会删除整个表的数据,但可保留表的结构 例:删除20%的学生信息 delete top (20) percent from 学生信息 (注意加括号,若无percent则删除前20行)
视图
视图是基于查询结果的虚表,对于表的所有操作,在视图中同样可以使用 分为标准视图、索引视图、分区视图 优点: 1.着重于特定操作 2.简化数据操作 3.自定义数据 4.导出和导入数据 5.跨服务器组合分区数据
创建视图
语法: create view [schema_name.] view_name as select_statement
schema_name:数据库名 view_name:视图名 select_statement:查询语句
修改视图名称
语法:exec sp_rename '原视图名','新视图名' 修改列名 语法:exec sp_rename '视图名.列名','新列名'
管理视图
插入数据 例: insert into view_学生信息 values('7','13','张三','nan','beijing') 说明:视图里的数据一旦做了更改,原表的数据也会随之改变
修改视图
语法: alter view 视图名 as select_statement 例:
alter view view_成绩表
as
select 学号,成绩 from 成绩表
说明:既能修改视图中的数据又能修改视图的结构
修改视图中的数据
例:修改视图View_班级信息2中的人数80的班级编号改为28
update View_班级信息2
set 班级编号=28
where 人数=80
删除视图
语法:drop view 视图名 例:drop view 视图1,视图2
删除视图中的数据
delete from view_班级信息 where 人数=80 说明:原表的信息也会一并被删除
索引
对数据库表中一个或多个列的值进行 排序的结构:有助于更快的获取信息(定位) 建立索引会减慢数据的修改速度 作用: (1)加快数据检索 (2)保证数据一致性 (3)实现表与表之间的参照完整性 (4)减少排序和分组时间
选择创建索引的数据列: (1)定义有主键和外键的列 (2)在指定范围中快速或频繁查询的列 (3)连接中频繁使用的列 (4)需要按排序顺序快速或频繁检索的列
分类: (1)聚集索引:索引的顺序决定了表中行的存储顺序,每个表中只能有一个聚集索引 (2)非聚集索引:定位数据
使用多个索引可以提高更新少而数据量大的查询的性能
创建索引
需要注意: (1)若创建了主键约束或唯一约束,系统将自动的为建有这些约束的列创建聚集索引 (2)当删除主键约束或唯一约束时,这些列上创建的聚集索引也会被自动删除 (3)若不指定索引类型,将使用非聚集索引作为默认的索引类型 语法: create [unique] [clustered | nonclustered] index index_name on 表名 (列名 [,...n])
unique唯一的索引 clustered聚集索引 noncluster非聚集索引 index_name索引名 例1:为student表的sno列创建非聚集索引 create index index_sno on student (sno) 例2:为student表的sno列创建唯一聚集索引 create unique clustered index index_sno on student (sno)
删除索引
语法:drop index 表名.索引名 或drop index 视图名.索引名
例(全有)
create database stu
on primary
(
name='f3',
filename='D:/SQL2019/SQLfile/f3.mdf',
size=3mb,
maxsize=200mb,
filegrowth=1mb
)
log on
(
name='f4',
filename='D:/SQL2019/SQLfile/f4.ldf',
size=3mb,
maxsize=200mb,
filiegrowth=1%
)
alter database stu
modify name=stud
alter database stud
add file
(
name='f5',
filename='D:/SQL2019/SQLfile/f5.ndf',
size=3mb,
maxsize=200mb,
filegrowth=1mb
)
alter database stud
add log file
(
name='f6',
filename='D:/SQL2019/SQLfile/f6.ldf',
size=3mb,
maxsize=200mb,
filegrowth=1mb
)
drop database stud
use stud
create table stus
(
学号 varchar(10) not null primary key,
xingming varchar(10),
age int,
xueyuan varchar(20)
)
create table chengji
(
xuehao varchar(10) not null foreign key references stus(学号),
xingming varchar(10) unique,
xingbie varchar(10) check (xingbie='男' or xingbie='女'),
kechengbianhao varchar(10) not null primary key,
kecheng varchar(10),
grade int default 0
)
drop table stus
alter table stus
add xingbie varchar(3) null
alter table stus
alter column xuehao int
alter table stus
drop column xingbie
exec sp_rename 'stus.学号','xuehao'
exec sp_rename 'stus','students'
alter table students
add constraint pk_学号 primary key (学号)
alter table students
add constraint un_xingming unique (xingming)
alter table students
add constraint ch_cingbie check (xingbie='nan' or xingbie='nv')
alter table students
add constraint df_age default 'nan' for xingbie
alter table students
add constraint fk_学号 foreign key (学号)
references chengji(xuehao)
alter table students
drop constraint pk_学号
select * from students
select distinct age from students
select top 3 * from students
select age '年龄' from students
select '年龄'=age from students
select age as '年龄' from students
select * from students where age between 18 and 19
select * from students where xingbie in ('nan','nv')
select * from students where xingming like 'wang%'
select * from students where xingming like '_[jian]'
select * from students where age is null
select * from students where age is not null
select sum(age) from students
select avg(age) from students
select max(age) from students
select min(age) from students
select count(*) from students
select count(age) from students
select xueyuan,avg(age) as '平均年龄' from students group by xueyuan
select xuehao,xingming,sum(chengji) as '总成绩' from chengji group by xuehao having sum(chengji)<200
select * from students order by age
select * from students order by age desc
select students.xuehao,students.xingming,chengji.kecheng,chengji.grade from students,chengji
where students.xuehao=chengji.xuehao
select students.xuehao,students.xingming,chengji.kechegn,chengji.grade from students inner join chengji
on students.xuehao=chengji.xuehao
select s.xuehao,s.xingming,c.kecheng,c.grade from students as s,chengji as c
where s.xuehao=c.xuehao
select s.xuehao,s.xingming,c.kecheng,c.grade from students as s inner join chengji as c
on s.xuehao=c.xuehao and c.grade>50
order by c.grade desc
select students.xuehao,xingming,chengji.kechengbianhao,kecheng
from students left outer join chengji
on students.xuehao=chegnji.xuehao
select students.xuehao,xingming,chengji,kechengbianhao,kecheng
from studetns right outer join chengji
on students.xuehao=chegnji.xuehao
select students.xuehao,xingming,chengji,kechengbianhao,kecheng
from studetns full outer join chengji
on students.xuehao=chegnji.xuehao
select * from chengji where xuehao in (select xuehao from students where xueyuan='计算机')
select xuehao,grade from chengji
where kechengbianhao='c02' and grade>(select avg(grade) from chengji where kechengbianhao='c02')
select s.xuehao,xingming,c.kecheng,grade
from students as s,chengji as c
where s.xuehao=c.xuehao and
s.xueyuan=(select xueyuan from students where xingming='ff')
select xuehao,xingming,xingbie into 学生信息 from students
insert into students
values (2012,'aa',18,'计算机','男')
insert into 学生信息
select xuehao,xingming,xingbie from students
update students
set nianling=19 where xuehao=2012
delete from students where xuehao=2012
delete top(20) percent from students
create view View_students
as
select * from students
exec sp_rename 'View_students','view_students'
exec sp_rename 'view_students.xuehao','学号'
insert into view_studetnts
values (2013,'bb',19,'计算机','女')
alter view view_students
as
select xuehao,grade from chengji
update view_students
set nianling=20
where name='bb'
drop view view_students
delete from view_students where xueyuan='计算机'
create clustered index index_xuehao on students(xuehao)_
create index index_xingming on students(xingming)
drop index students.index_xuehao
|