SQL学习笔记

掌握简单的SQL操作语句

(1）建库

//建立数据库
create database school
on 
(name='schmgt1',
filename='D:\DATA\schmgt1.mdf',
size=20mb,
maxsize=50mb,
filegrowth=10%
),
filegroup sch1 规定文件组
(name='schmgt3',
filename='D:\DATA\schmgt3.ndf',
size=10mb,
filegrowth=10%
),
filegroup sch2
(name='schmgt5',
filename='D:\DATA\schmgt5.ndf',
size=10mb,
maxsize=20mb,
filegrowth=1mb
)

log on 日志
(name='schmgtlog',
filename='D:\DATA\schmgtlog.ldf',
size=30mb,
filegrowth=10%
)

（2）库操作

//修改数据库用
alter datebase 数据库名（都要加）

modify name=新文件名
modidy file（name=表名，加要改的数据）
add file（表属性设置）to filegroup 文件名
remove 文件名
drop 数据库名

//建立数据库快照（快照只读记录某个时刻数据库的镜像）
create database 快照名
on
(
	name=文件名
	filename='文件物理' --这里应该是包括所有文件
)，
(
	name=文件名
	filename='文件物理' --这里应该是包括所有文件 多个文件
)
as snapshot of school
go
drop database 快照名 

（3）建表

use scool
go
create table student
(
   学号 char(6)  not null,
   姓名 char(8),
	性别 char(2),
	出生时间 datetime,
	专业 char(12),
	总学分 int check(总学分>=0 and 总学分<=100),
	备注 varchar(500)
)
create table course
(
	课程号 char(3)  not null,
	课程名 char(16) not null,
	开课学期 tinyint default 1,
	学时 tinyint default 0,
	学分 tinyint default 0
)
create table score
(
	学号 char(6)  not null,
	课程号 char(3)  not null,
	成绩 int default 0,
	primary key(学号,课程号)
)

这里可以给主键加名字，直接加在属性后
constraint ww primary key(学号,课程号)
向表插入数据
insert into 表（属性） values（‘字符’，数字，‘’）
如果没有赋值的，要不null或者not null报错，不然就是default
drop table 表名

（4）修改与更新表

use school
go
alter table student 
1、add 姓名 char(8) ==加属性==
2、alter column 姓名 vchar(10) ==修改属性==
3、drop column 姓名 ==删除属性==

==更改数据==
use 数据库
go
==update== 表名
set 列=数据
where 条件

删除元组
delete from 表名
where 条件（可加select一次多删几个条件）

（5）属性加键约束

格式
use 数据库
go
alter 表名
add constranint 约束名 约束类型
或
drop constranint约束名

primary key 主键约束
unique 唯一建约束
foreign key外键约束
外键：1个表的一列依赖其他1个表的主键或唯一列
参照表和被参照表
*add constraint ww foreign key (sno) references course(sno)
违约设置就跟在外键设置后
on delete no action\set null\cascade\set default
on update no action\set null\cascade\set default*

聚集索引和非聚集索引的创建

--聚集索引
create clustered index 索引名称 on 表名（属性 asc，属性 desc）
--非聚集索引
create nonclustered index 索引名称 on 表名（属性 asc，属性 desc）

（6）选择

格式：
use 数据库名
selcet 要选的东西 form 表
where 指定范围

1、*选择所有列，固定的列或值，as设置输出别名
select * from student
select 固定列名 from student
select 列名或一些值 as ww from student

select 可以加的约束 
distinct 消除重复值
top n 输出前n个

select [约束] * from student

2、输出也可以是一些表达式
+、-、*、/、% 都是可以的
例如:
select year（getdate（））- year（出生时间）as 年龄 from student
where 专业=‘通信工程’

3、聚合函数（用于求某列的一些统计值）
select 聚合函数（列名）from 
where 里不能有聚合函数

**聚合函数：**
==sum（）；avg（）；max（）；min（）；count（）；==

（7）where

1、表达式比较
where 属性 =，<，!=，>等加一些约束

2、模式匹配
==like\not like谓词用于字符串匹配==
where 属性 like‘一个谓词’
例如:
AB%： AB开头不限字符数目
AB_： AB开头一个字符，三个字符
AB[1-5]:AB开头3字符，第三字符只能1-5
AB[^125]:AB开头3字符，第三字符不能是125

3、范围（）
where  表达式[not] between 表达式1 and 表达式2
where  表达式[not] in （表达式select找出一些，可以直接写‘属性’，‘属性’）

4、空值比较
where 表达式 is null 或者 is not null

5.1子查询
where 表达式 [not] in (子查询)

5.2特殊比较子查询
where 表达式 [比较运算符] any/some/all（子查询）
与子查询的结果进行比较
例如:
where 出生时间<any (select 出生时间 from st where 专业=‘计算机’)

6、exist
where [not] exist（子查询）
存在返回ture不存在返回false

（8）from

1、存在的表或者视图
select 属性 from 表或视图

2、select查找到的表：这里一定要给查到的表设置一个别名
select 属性 from （子查询）as 表别名
拓展可以设置每个属性的别名
表别名（列别名）

（9）连接

 1、自然连接：消除了目标列的相同的字段名
例如：
select xsb.*，课程号 （不唯一的列就加表名）
where xsb.学号=cjb.学号
2、内连接 inner join
3、外连接 left join ；right join

（10）group by

使用group by时注意select 中没有使用聚合函数的列都要放在其中
如果聚合函数结果需要进一步筛选group by后使用 having

select count（cno）from sc
group by cno
havin count（cno）>2

（11）order by

desc 降序，asc升序

（12）字符串函数（注意于聚合函数的区别）

ascii（）；**左非空第一字节转换为ascii**
ltrim（）；**消除开头空格**
rtrim（）；**消除结尾空格**
lower（）；**小写显示**
upper（）；**大写显示**
revenue（）；**逆序**
left（字符表达式，n个数）；**放回从左边开始的n个字符**
==substring（表达式，start，length）；==
//使用select进行调用
==select substring(ltrim('   ABCDEFG'),3,3)==

（13）创建视图（虚表）

==视图的更新和修改加上==

create view 视图名（选的列要命名的名称）
as
select 把要选的东西写在这，用连接或者什么其他方式

参数设置包括 参数名+参数类型[=某个默认值] 使用通配符也是直接等于
declare @kk 数据类型 <-- 定义变量

（14）创建函数

1、标量函数
create function 函数名（参数）returns  返回类型
as
	begin
	函数体
	end
2、内嵌函数返回表 ==注意这里放回的是表==
create function 函数名（参数）returns table
as return
（需要找的数据）
go

drop function 函数名

（15）创建存储过程

create proc|procedure //名称 参数1、参数2 output说明这个参数做输出
as
	主体
go
游标参数只能做输出 cursor

//存储过程执行
exec|execute 名称
DECLARE @result int
EXECUTE CPA 081101,081107,@result OUTPUT
SELECT @result
//注意调用存储过程，output也需要一个值去接收

drop proc 存储过程名称

（16）创建触发器

create trigger 触发器名 on 表|视图
（for|after|instead of ）（insert|update|delete） DML触发器
as
{
	sql语句
}

触发器执行时，系统会创建临时表将要插入或删除的信息暂存在表中
insert 为inserted表
delete 为deleted表
update 为  新记录inserted和 原记录deleted表
所以写处理语句时从这些表中去找要处理东西

for和after是执行语句后检测，如果不允许执行要加回滚rollbake
instead of 不执行语句直接替换 在sql语句中要加上操作语句

（17）备份文件（执行一个系统自带的存储过程进行备份）

1、创建备件设备

exec sp_dropdevice ‘myBK1’，delflie
//例如：
exec sp_addumpdevice‘DISK’，‘myBK1’，‘d:\school\myBK1.bak’ 
//依次是设备类型;设备名称;设备物理地址

2、备份数据库
backup database 数据库名 to 备份设备（设备名称）
语句后可加 with init（覆盖原数据） 或 with noinit（不覆盖原数据）

可以部分备份

backup database 数据库名 file=‘文件名’ to 备份设备（设备名称）--备份时会有文件的产生
backup database 数据库名 filegroup=‘文件组名’to 备份设备（设备名称）

3、恢复备份

restore databse 数据库 from 备份设备 with file=备份时的文件号 replace
restore databse 数据库 from database_snapshot ‘快照名’

（18）用户和权限

//windows用户
create login [pc名\用户]<-就是要加[]
for Windows
with default_database=数据库名

//sql server 用户
create login 用户名
with password=‘123456’
with default_database=数据库名

drop login 登录名

//数据库用户
create user 用户名
login 登录名
with default_schema=dbo 默认架构

drop user 用户名

1、授予权限grant
2、禁止使用deny
数据库层面的权限直接 to 用户，对某一个表需要写on 表 to用户
3、删除权限revoke 收回权限的意思
数据库层面的权限直接 from 用户，对某一个表需要写on 表 from用户