SQL 合集（未完全更新）

一：数据模型

数据模型：是对现实世界数据特性的抽象

数据模型是数据库的核心和基础

数据模型的组成： 数据结构    数据操作    数据完整性约束

常用的数据模型：

1. 层次模型  
2. 网状模型
3. 关系模型
4. 面对对象数据模型
5. 对象关系数据模型
6. 半结构化数据模型 

数据库系统的三级模式结构：

1.模式：    也称逻辑模式 ，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图     在模式结构的中间层 

2.外模式：也称子模式 ，是用户可以看到和使用的局部数据的逻辑结构和特征的描述，是用户的数据视图

3.内模式：也称存储模式 ，一个数据库只有一个内模式，是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

外模式/模式映射：定义外模式与模式之间的对应关系。
    每一个外模式都对应一个外模式/模式映像，在外模式中规定了从模式中如何分离出。    
外模式/模式映像的作用：
    保证了数据的逻辑独立性。当模式改变时，数据库管理员修改有关的外模式/模式映像，使外模式保持不变；而应用程序是根据数据的外模式编写的，从而应用程序不必修改，保证了数据与应用程序的逻辑独立性，简称为数据的逻辑独立性。

模式/内模式映像：
     定义了数据全局逻辑结构与存储结构之间的对应关系（例如说明逻辑记录和字段在内部是如何表示的）。
    数据库中模式/内模式映像是唯一的，定义在模式当中。
模式/内模式映像的作用：保证了数据的物理独立性。
    1）当数据的存储结构改变时（如本来我们用堆存储，改成了B+树），数据库管理员修改模式/内模式映像，使模式保持不变。
    2）应用程序不受影响，保证了数据与程序的物理独立性，简称为数据的物理独立性。

二：SQL的一些概念 

SQL：结构化查询语言，是一种关系数据库的标准语言

SQL特点：

1. 综合统一
2. 高度非过程化
3. 面向集合的操作方式
4. 以同一语法结构提供多种使用方式
5. 简单简洁，易学易用

SQL语言分类：

DQL 数据查询语言	Select 列 From表名 where条件
DML 数据操纵语言	Insert Updata Delete ...
DDL 数据定义语言	创建数据库及其对象
DCL数据控制语言	用于授予和回收特权   commit grant  ...

数据库常用对象

表	包括数据库中所有数据对象，行和列组成，用于组织和存储数据
字段	表中的列 一个表可以有多个列，自己的属性，数据类型 ，大小
视图	（虚拟表）一张或多张表导出的表
索引	为了给用户提供一种快速访问数据的方式，索引是建立在表上的
存储过程	一组为了完成特定功能的SQL语句集合（增查改删等）
触发器	收到命令时 会自动去触发和执行
约束	对数据表的列进行一种限制，规范表列
缺省值	对表中的列可以指定一个默认值，没有值会主动给与一个默认值

数据类型： 

整型：

bigint	8个字节             -2^6—+2^63
int	4个字节            - 2^31—+2^31
smallint	2个字节             -2^15 —+2^15
tinyint	1个字节              0-255

浮点型：

float	近视数值   存在精度损失
real	近视数值
decimal	精确数值 decimal（n,m） n为最大小数位（18最大）  m为最小小数位

二进制数据类型：

bit	0 1 NULL
binary	固长    最多8000字节
varbinary	变长    最多8000字节
varbinary（max）	变常    最多2GB字节
image	变长    最多2GB字节

字符型

char（n）	固长    最多8000字节
varchar（n）	变长    最多8000字节
varchar(max)	变长    8000—1073741824不超过8000 按varcher（n）使用
text	变长    最多2GB字节

Unicode 字符串  在字符串前加n  数据长度减半

区别：uchar存储汉字和字符占2个字节     char 字符占1个字节 汉字占2个字节

日期数据类型

datatime	1753.1.1-9999.12.31年  精度3.33毫秒      8个字节
datatime2	1753.1.1-9999.12.31年  精度100纳秒       6-8个字节
smalldatatime	19001.1-2079.6.6年        精度1分钟          4个字节
data	仅存储日期 1-9999.12.31年                       3个字节
time	仅存储时间       精度100纳秒
datatimeoffset	与datatime2相同   外加时间偏量
timestamp	存储唯一一个数字，当创建和修改某行时会更新 相当于一个计数器 每个表只能有一个

 SQL 约束：

主键： 是一个表的唯一标识，不能重复，不能为空

创建一个主键，同时会自动创建一个聚集索引

外键： 一般用在两个表之间要建立关联时候，创建一个列为外键，在另外一张表中必须为主主键

主键表和外键表

两个表一旦建立外键关系，外键表里对应的外键列，他的值必须为它对应的主键表里的主键值

1.主键约束：唯一性 非空 不能修改

2.外键约束：加强两个表的列数据之间的连接。先创建主表主键，然后定义表中的外键。只有主表的主键才能被从表来作为外键使用 ，主表限制了从表更新和插入的操作。当删除主表中的数据时，应先删除从表的数据，然后在删除主表的数据

3.唯一性约束：确保表中一列数据 没有相同的值，表中可以定义多个。唯一键可以为NULL

4.Check 约束 ：通过逻辑表达式来判断数据的有效性，用来限制输入一列或多列的值的范围

5.Default 约束：默认值 ，如果插入新的数据行时没有指定数据，那么系统会给一个默认值 NULL

 三：创建库和表

1.数据库操纵关键词

功能	动词
数据查询	select 查询
数据定义	create 新建   drop 删除    alter修改
数据操纵	insert 插入   update 更新    delect删除
数据控制	grant 授权   revoke  收回权力

2.创建库

进入数据库点击新建查询：

master：系统数据库，记录了SQL Server系统的所有系统级信息，还记录了系统所有系统级信息，还记录了所有其他数据库的存在，数据库文件位置，SQL初始化信息

create  database   数据库名

on primary（） 数据库核心文件

log on（） 日志文件

drop  database  数据库名     删除数据库

use master--选择要操作的数据库
go--批处理命令，跳转到master
--创建数据库
create database Teacher --Teacher为数据库名称
on primary
(
   name='Teacher',--主要数据文件名
   filename='D:\Sql server\Teacher.mdf',--主要数据文件路径
   size=5MB,--主要数据文件初始大小
   filegrowth=1MB--文件增量
)
log on--创建日志文件
(
   name='Teacher_log',--日志文件文件名
   filename='D:\Sql server\Teacher_log.ldf',--日志文件路径
   size=1MB,--日志文件文件初始大小
   filegrowth=10%--文件增量
)
go
--删除数据库
drop database Teacher
go

3.创建表 ：

第一种你可以自己选中数据库存放表

点击新建查询 然后可以在左上角选择数据库  最好是自己创建的数据库

第二种：直接在代码框输入

create  table  表名

（

      列名  数据类型  约束，

      列名  数据类型  约束

  ）

drop   table   表名    删除表

use Person--选中你要添加表的数据库
go
--创建一个表
create table Person
(
   --格式： 列名  数据类型  约束
   id int primary key ,--primary key为主键 一个表最好有一个主键 （后面会介绍）
   name char(10),
   pnumber int ,
   age int ,
   height int 

)
--删除表  drop table +表名
drop table Person
go

 修改表：

添加列：alter table 表名 add 列名  属性

删除列：alter table 表名 drop column 列名

修改列：alter table 表名 alter column 列名  参数

修改列名：exec sp_rename '表名.列名','新名','column'  最好不要使用

--添加列
alter table Person add  addresss_ nchar(50) null

--删除列
alter table Person drop column wt

--修改列
alter table Person alter column height smallint null

--修改列名
exec sp_rename 'Person.wt','weight_','column'

4.添加约束

alter    表名   add   constraint

加以下约束

主键约束： 约束名   primary key（列名）

外键约束：约束名    foreign （外码）references   从表名（外码）

默认值约束： 约束名   default（数值）for  列名

唯一约束：  约束名   unique（列名）

限制约束： 约束名  check（列名>x）

第一种：在创建表时添加 约束

use Teacher
go
create table Student
(
   Son   varchar(11) primary key not null,
   SName varchar(10) not null,
   Ssex  varchar(4) not null,
   Sage  int   default(0) not null,
   stdept varchar(20) not null 
)

create table Course
(
   Con int identity(1,1) primary key not null,
   Cname varchar(10) not null,
   Cpno int null,
   Ccredit int not null,
)

create table SC
(
   Son varchar(11) not null,
   con int not null,
   Grade int not null,
   primary key(Son,con),
   --外键约束
   --格式:foreign key(外码)references 表名（外码）
   foreign key(Son)references Student(Son),
   foreign key(con)references Course(con)
)

 第二种：在创建表后添加

create table P
(
   id int ,
   name varchar(20),
   age int 
)
create table p1
(
   name varchar(20),
   height int 
)
--设置主键
alter table P add constraint
p_1 primary key(id)
--设置外键
alter table p1 add constraint
p_2 foreign key (name) references P(name)
--设置限制约束
alter table P add constraint
p_3 check(age>0)
--设置唯一约束
alter table P add constraint
p_3  unique(age)
--设置默认约束
alter table P add constraint
p_4  default(10) for age

标识列：不能手动插入数据，插入时自动生成，这个列的数据类型必须是不带小数的

标识种子：identity（初始值，增量）  不设置的化默认为（1，1）

第一条记录的值（可设置）

增量：每次插入时的值的变化量

  --列名  数据类型  identity(初始值，增量)  约束

create table student
(
   id  int  identity(1,1)  primary key 
)
--下一章会详细介绍

 四：SQL 的数据操作

1.数据的插入：insert

1.1 单条数据的插入

没写列名 就是 全部列

insert   into  表名（列名）  values（内容）	单条数据插入
insert     ignore   into  表名（列名） values（内容）	如果不存在则插入 ，如果存在则忽略
insert    replace into 表名  values （内容）	如果不存在则插入，如果有则替换
replace into 表名（列名） values （内容）	强制插入数据
insert   表名（列名） select  内容	单条数据插入

create table student
(
   id  int  identity(1000,1) primary key,
   name  char(20) not null,
   age  int  check(age>0 and age<150)
)
--因为有标识符 所以可以不用导入 id的数据
insert into student 
values ('wangyi',50);

insert ignore into student --存在相同的话，忽略该操作
values ('wangqi',30);

insert replase into student--存在相同的话，则替换数据
values ('wangti',20);

insert student
select 'wangpi,10' 

 1.2多个数据添加

insert into 表名（列名）

values （内容），（内容），（内容），（内容）

 insert  表名（列名）

select   内容   union

select   内容   union

select   内容   

union	去除重复的元素
union all	不会去除重复的元素

create table student
(
   id  int  identity(1000,1) primary key,
   name  char(20) not null,
   age  int  check(age>0 and age<150)
)

insert  into student
values ('www1',10),('www2',20),('www3',30),('www4',40)

insert  student
select 'eee1',10 union
select 'eee2',20 union
select 'eee3',30 union
select 'eee4',40 

 1.3 克隆其他表的数据

insert  into 目标表（列名）    (目标表已经在数据库中)

 select  列名  from  源表    

select   列名  into  目标表   （目标表之前在数据库中不存在，执行操作时自动创建）

 from   原表         

create p
(
   id  int primery key,
   name  varchar(20) not null,
   age   int  not null
)
create p1
(
   name  varchar(20) not null,
   age   int  not null
)

--把p表中的数据克隆到 p1
insert into p1
select  name,age from p

--把p表中的数据克隆到 p2
select id,name,age into p2
from p                     --p2没有不存在，则数据库自动会创建

 2.更新和删除

1.1更新

update   表名   set     列名='数据',列名='数据'....
where   列名='数据'   

被修改的列数 是数字的话可以不用  '  '

where 是限制条件  不加where 会更新全部数据

--把性别 弄反， 女变男  男变女

update  student  set  sex= if(sex='男','女'，'男')

 2.2 删除

--格式  delete   from   表名
--         where   删除的列 

delete from table

这种方法 如果删除的数据有标识列的话，那标识列的值是按照删除前的值而自增

delete语句会造成标识列的值不连续

delete from  student --单个删除
where id=1006
delete from  student --批量删除
where id>1001 and id<1006

 2.3清空

1.truncate   table   表名 （不能带用where 进行单个删除）

清空所有表中的数据  恢复到初始化  标识列也恢复

2.delete  from  表名

清空所有表中的数据  恢复到初始化，但标识列不会恢复

delete from  m   --删除表中全部数据，标识列不会恢复
truncate table m --删除表中全部数据，标识列会恢复

truncate	效率高，不会生成日志，不会激活触发器，是即时操作， 不能恢复
delete	效率低，每删除一条数据，就会再日志里记录，会激活触发器，能恢复

2.4删除表

drop table 表名   restrict（默认）  |  cascade   删除整个表

restrict：表不能有约束，不能有视图，不能有触发器，不能有存储过程或函数

cascade: 无限制，删除表时其它的也一并删除