超全的数据库常用语句及相关内容总结
数据库
1. 创建:
create database 数据库名
[On/log on
[primary]
-*-<filespec>
(
Name = ,
Filename = ,
Size = [kb|mb|gb|tb],
Maxsize = [kb|mb|gb|tb],
Filegrowth= [kb|mb|gb|tb],
)(,……n)]
-*-
2. 修改:
alter database 数据库名
{
|Modify name = 新数据库名 ---- 命名
|collate 规则 ----- 排序规则
|add file <filespec> [to filegroup 文件组名]
|add log file <filespec>
|remove file 文件逻辑名 ------ 移除
|modify file <filespec> ------ 修改
|add filegroup 文件组名
|remove filegroup 文件组名
|modify filegroup 文件组名
}
3. 删除:
drop database 数据库名
4. 备份:
创建备份设备
sp_addumpdevice ‘设备类型’ , ‘逻辑名’ , ‘物理名----完整的路径’
备份数据库
backup database|log 数据库名 to 备份设备名 [with differential-----差异备份]
直接备份
backup database|log 数据库名 to disk = ‘物理路径.bak’ [with differential-----差异备份]
删除备份设备
sp_dropdevice ‘备份设备名’
5. 还原:
restore headeronly from 数据库名
6. 从磁盘文件中恢复
restore headeronly from disk = ‘物理路径.bak’
7. 分离:
sp_detach_db ‘数据库名’
8. 附加:
create database 数据库名
On <filespec> for attach
数据表
1. 创建:
create table 表名
(列名 类型 not null [,……n])
2. 查看:
sp_help 表名
3. 修改:
alter table 表名
{
alter column 列名 类型 not null [,……n]) ----------列的类型,是否为空进行
}
4. 更改
sp_rename ‘表名.列名’ , ‘新列名’ -----------该列名
add 列名 类型 not null [,……n]) ----------再增添列
drop column 列名 ----------删除该列
5. 删除:
drop table 表名
6. 数据插入:
insert [into] 表名[(列名[,……n])]
values (列名[,……n])
[select 语句]
7. 数据更新:
update 表名 set 列名 = 条件 where = 条件
8. 数据删除:
delete [from] 表名 [where = 条件]
索引
1. 创建:
create [clustered] [clustered] [unique] index 索引名 on 数据库名/视图/表名 (列名[ASC|DESC][,……n])
2. 查看:
sp_helpindex 索引名
3. 修改:
sp_rename ‘数据库名.索引名’,’新索引名’ index
4. 删除:
drop index 索引名 on 数据库名
约束
1. 使用规则
创建:
create rule/default 规则名 as (可以用where的子句,in,like,between)
查看:
exec sp_helptext 规则名
绑定:
exec sp_bindrule 规则名, ’表名.列名’
解绑:
exec sp_bindrule ’表名.列名’
删除:
drop rule规则名
2. 使用约束
主键:
在创建或更改表时设置主键
①单个主键:
alter/create table 主表名
add/on
( [
列名 类型 not null constraint主键名 primary key
[,……n])]
②两个以上:
alter/create table 主表名
add/on
( [
列名 类型 not null
[,……n])]
constraint主键名 primary key (组合主键1,组合主键2,……)
外键:
alter/create table 主表名
add/on
( [
列名 类型 not null
[,……n])]
constraint外键名 foreign key (外键即列名) references 从表名(列名即外键)
非空:
alter table 表名 alter column 非空字段即列名 类型 not null
唯一:
alter/create table 表名
add/on
([
列名 类型 not null
[,……n]
])
constraint 主键名 primary key (列名1,列名2,……)
constraint 约束名 unique (列名1,列名2,……)
检查:
alter table 表名 with nocheck
add
constraint 约束名 check(......)
默认:
alter table 表名
add
constraint约束名 default ‘默认值’ for 列名
删除:
alter table 表名
drop constraint 约束名
查询
-
查询
select *******[ as 插入列的列名] from 表名 where 条件
-
排序
select ******* as [插入列的列名]from 表名 order by 排序的列名 ASC|DASC
-
查询结果放入新表
select 表名.列名 into 新表名 from 表名 where 条件
-
分组
select *****from 表名 group by 分组的列-----按某列划分组
-
条件筛选
select *****from 表名 group by 列名 having 条件
-
内连接
等值连接
所有相等的联结到一起,有重复
等值连接必须要有等值的条件,当条件不同时连接的结果也不相同,两个关系可以没有相同的属性列select 学生.学号,姓名,课程.课程名称,成绩 from 选课,学生,课程 where 学生.学号 = 选课.学号 and 课程.课程编号= 选课.课程编号 select 学生.学号,姓名,课程.课程名称,成绩 from (选课 inner join 学生 on 选课.学号 = 学生.学号
不等值连接
where 或者on 后面 列比较不是用=,而是用一下这些 <>,>,<,>=,<=,like,in,between on,not 等等自然连接
所有相等的联结到一起,无重复
是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组,并且在结果中去除重复的属性列。 -
外连接
左外连接
左外连接的内容都显示,右表没有的数据显示为null右外连接
右外连接的内容都显示,左表没有的数据显示为null全外连接
左外+右外 -
交叉连接
第一个表的数据乘以第二个表的数据
视图
-
创建和修改
create/alter view 视图名 [with encryption加密|schemabinding将视图绑定到基础表] as select 语句
-
删除:
drop view 视图名
-
插入表数据
insert into 视图名 values(‘ ’ , ’ ’,……)
-
修改表数据
update 视图名 set 入学成绩=589 where 学号=’s5024’ #修改s5024同学的入学成绩为589
-
删除表数据
delete from 视图名 where 条件
函数
-
标量函数
create function 函数名 returns 数据类型 as begin 函数体 return 返回的标量值 end
-
内联表值函数
create function 函数名 returns table as begin select语句 函数体 return 返回的标量值 end
-
多语句表值函数
create function 函数名 returns @变量名 table as begin 函数体 return 返回的标量值 end
游标
-
打开
open 名称
-
读取
begin fetch [[next][prior][first][last]|absolute n|relative n] from] into 名称 into 变量 end
-
关闭
close 名称
-
释放
deallocate 名称
存储过程
-
创建:
create procedure 名称 as select 子句
-
执行:
exec 名称
-
修改:
alter proc 名称 as select 子句
-
删除:
drop procedure 名称
触发器
-
创建:
create trigger 名称 on 表名 [with encryption] for [delete|update|insert] as select 子句
-
修改:
alter trigger 名称 on 表名 [with encryption] for [delete|update|insert] as select 子句
-
删除:
drop trigger 名称
事务
一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
事务控制语句
-
begin 或 start transaction 显式地开启一个事务;
-
commit 也可以使用 commit work,不过二者是等价的。commit
会提交事务,并使已对数据库进行的所有修改成为永久性的; -
rollback 也可以使用 rollback work,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
-
savepoint identifier,savepoint 允许在事务中创建一个保存点,一个事务中可以有多个 savepoint;
-
release savepoint identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
-
rollback to identifier 把事务回滚到标记点;
-
set transaction 用来设置事务的隔离级别。innodb 存储引擎提供事务的隔离级别有read
uncommitted、read committed、repeatable read 和 serializable。
mysql 事务处理主要有两种方法:
-
用 begin, rollback, commit来实现
• begin 开始一个事务
• rollback 事务回滚
• commit 事务确认 -
直接用 set 来改变 mysql 的自动提交模式:
• set autocommit=0 禁止自动提交
• set autocommit=1 开启自动提交
锁
锁是网络数据库中的一个非常重要的概念,当多个用户同时对数据库并发操作时,会带来数据不一致的问题,所以,锁主要用于多用户环境下保证数据库完整性和一致性。
数据库锁出现的目的:处理并发问题
更好的理解:以公共卫生间为例,每个卫生间都可供多个路人使用,因此,可能出现多个路人同时需要使用进卫生间。为了避免冲突,卫生间装了“锁”,某一个路人在卫生间里把锁锁住了,其他路人就不能从外面打开了,只能等待里面的路人从里面把锁打开,外面的人才能进去。