数据库—SQL语言学习

SQL 数据类型

SQL----Structured Query Language

---

SQL的特点

•  综合统一
•  高度非过程化
•  面向集合的操作方式
•  一种语法，两种使用方式
•  支持三级模式结构

---

• 整数

bigint --8字节
int --4字节
smallint --2字节
tinyint --1字节
bit --0或1（一般用于性别表示）

• 精度类型

decimal(整个数据的长度，小数表示范围) --指定范围和精度
比如：231.111，那么decimal第一个参数就取决于你选择保留几位小数，如果保留两位，
那么你就要加上前面的231三位，那么最后decimal的第一个参数就应该填写3+2=5，即decimal(5,2)

numeric --和上面的decimal一样
float --存储8字节，
	  --但只能精确到53位，
	  --如果数据中这个列常用于计算就不建议使用float

• 字符类型

char(长度) --固定长度的，
		  --写多少就固定多少，
		  --意思是存一个char数据的时候不能超过你写的长度
		  --如果没有超过该固定的长度，
		  --但是还是会继续存储这么大的char内存
varchar(长度) --可变长度，意思是只要不超过你固定的长度，
			 --你存的数据多大就多大不会固定大小的意思，
			 --而不是说你存多少是多少，
			 --还是要遵守你写的那个长度上限。

• 时间类型

date --存储年月日
time --存储时分秒
datetime --存储年月日时分秒

重要的关键字

• constraint
  很重要，我不知道为什么好像很多老师都不怎么提，这个是给约束起一个名字，然后如果需要删除约束就拿到名字即可删除，如果约束名字的话DBMS会自动给你创建一个独一无二的名字，但这个名字基本没有了逻辑的，所以一般来说创建索引主键和约束等等我认为都要创建一个名字删除或者修改的时候方便操作。
  使用：就是在创建的动作后面，类型的前面加constraint 约束名
  比如：alter table add constraint 约束名 primary key Student(Sno)
  或者在创建表的时候，在括号里面的属性添加约束的时候起约束名
  create table Student（…
  …
  …
  constraint 约束名 unique(Sno)
  ）

• check
  检查约束

alter table emp add constraint xxx check(age>20)

check 	-- 检查约束，
		--比如年龄不能超过多少岁这样，
		--添加的时候会对check进行判断符合条件才添加进去
not null -- 不允许为空值
unique --定义唯一键
primary key --定义主码主键
foreign key(外键名) references 参照表(参照表的主键) --定义外键

• ASC （升序，不写排序规则会默认升序）
• DESC（降序）

定义数据库

create database 库名;

• 现在学习基本都是使用交互式的DBMS软件进行创建，但是还有一种书上讲的使用SQL语句创建，能够创建的粒度更细一些。

create database on primary
(NAME=逻辑文件名（意思是只要见名知意就行）, 
FILENAME=真实物理文件名（存储路径＋你最后为该数据库文件mdf的文件名.mdf）,
SIZE=初始文件大小（一般使用单位MB）,
MAXSIZE=文件能够容纳的最大内存（一般使用单位：MB）,
FILEGROW=文件每次增长大小,可以用单位MB，也可以用百分比，在初始文件大小的百分比
（比如现在存储的数据已经大过了初始文件大小了，就必须要增长然后不能超过最大容纳MB）
),
(NAME=辅数据文件的逻辑文件名,
FILENAME=辅数据文件的真实物理文件名.ndf,
SIZE=初始文件大小,
MAXSIZE=文件最大大小,
FILEGROW=文件每次增长的大小),
LOG ON(
NAME=日志的逻辑文件名,
FILENAME=日志的真实物理文件名,
SIZE=出示文件大小,
MAXSIZE=文件最大大小,
FILEGROW=文件每次增长的大小
)

• 总结
  数据库创建后有三个基本文件，所以可以对着三个基本文件做更细的设置，首先主数据文件与辅数据文件设置基本都一样，日志文件创建之前必须加一个LOG ON关键字然后创建的内容还是与前两个文件一样的设置。
  （NAME，FILENAME，SIZE，MAXSIZE，FILEGROW）

数据库的文件

创建完数据库一般会产生这仨份文件

• 数据库的主文件 ----- mdf
  一个数据库有且仅有一个主数据库文件
• 数据库的辅文件 ----- ndf
  ndf这个辅数据库文件可以没有，也可以有多个。
• 数据库的日志文件 ------ldf
  这个当数据库被破坏的时候可用来恢复数据，非常有用，这个文件的大小一旦被创建出来就至少为512KB

table创建与删除

---

表是三级模式两级映像中的模式

---

表的定义

create table 表名;

• 定义数据类型的格式：属性名 数据类型 约束。约束可写可不写。
• 举例子

1：不写约束，只定义数据
create table student(
sno varchar(20),
name varchar(20),
age int,
sex char(1),
sbirth data
);
2：在属性后面写约束定义，不只有primary key，还可以是其他约束
create table student(
sno varchar(20) primary key,
name varchar(20),
age int,
sex char(1),
sbirth data
);

3：在全部属性写完后写约束定义，不只有primary key，
还可以是其他约束，并且可以定义联合主键，下面定义联合主键。
create table student(
sno varchar(20),
name varchar(20),
age int,
sex char(1),
sbirth data,
primary key(sno,name)
);

表的alter

alter table 表名 修改操作;

-- 修改表名
alter table 表名 rename as 新表名;

-- 修改字段属性类型
alter table 表名 modify sex varchar(2);
-- 如果是在SQLServer中修改字段属性
alter table 表名 alter column sex varchar(2);

-- 增加属性
alter table 表名 add cno char(5);
//增加主键，这里十分建议声明一个约束名字constraint PK_sno
alter table 表名 add constraint PK_sno primary key(Sno);
//增加一个外键
alter table 表名 add constraint FK_cno foreign key references Course(Cno);
-- 删除属性
alter table [order] drop column cno;

-- 删除约束
alter table 表名 drop 约束名;

表的删除

drop table 表名;

视图

定义视图

视图就是外模式
with check option意思是是如果你要通过视图更新真实表数据的时候必须满足你的条件

-- 不对查询语句中真实表的列名修改
creat view 视图名 as 查询语句 where 条件 with check option;

-- 对查询语句中真实表的列名修改，要写在视图名后面
creat view 视图名(新列明) as 查询语句 where 条件 with check option;

• 有几个查询列名，想要修改就在视图后面全都写上，不能只希望对其中一个起别名，有几个就要起几个，你可以起一样的名字。
• 可以通过查询视图来建立新的视图（不建议使用）
• 可以通过视图修改真实表数据，这时候对应的就是你视图的属性名了，如果没有起名就默认还是原来表的属性名，根据情况按照对应属性名修改即可。
• 解释
  视图是三级模式两级映像的外模式，就是用户直接操作的是视图，不会直接操作到真实的表，也就是说我可以通过创建视图来规定某些用户只能访问我规定的查询语句出来的表的数据。由DBA来分配好哪些用户能用哪些数据。
• 视图只能是查询语句
• 可以通过给出的视图拿到数据后修改真实的表数据。

删除视图

由于可能删除的视图可能被别的视图用着，所以删除的时候也要将使用了该视图的视图显示删除掉，这就是造成了管理困难，这就是为什么不推荐通过视图建新视图。

drop view 视图名;

更新视图

update 视图名 set 视图属性名=新值, 视图属性名=新值,...

插入视图

insert into 视图名(视图属性1,视图属性2,...) values(值1,值2,...)

视图总结

• 不推荐使用视图更新
• 视图更新要满足以下条件
  • 选择语句中投影出来的属性列都是出自于一个表，不能连接查询两个表的属性列都分别取了几个，这样的视图是压根更新不了
  • 挑选出来的属性要包含主键
    就是说一个表有name ,no , sex三个属性，no是主键，但是视图创建的时候通过查询语句投影出来的视图属性没有原表的no主键，这时候就不可以更新
  • 注意：不允许更新是不符合你的with check option而不可以更新是因为你压根不可以更新，是因为你的视图没有包含原表的主键，而且可能你不是出自一个表的属性
• 简而言之不应该使用视图进行更新数据

索引

索引就是内模式，建立索引是为了加快查询（该说不说，数据库查询确实快）

• 聚簇索引
  创建了该索引，那么就对应你看到的数据库数据顺序就是在物理存储中的顺序。

• 当你创建了主键的时候DBMS自动给你这个属性列创建索引，其他属性列则需要自己创建了。

• 可以指定排序方式，默认是升序排

-- 建非聚簇索引
create index 索引名 on 表名(属性列);
-- 建唯一索引
creat unique index 索引名 on 表名(属性列)
creat unique index 索引名 on 表名(属性列1,属性列2,...)
creat unique index 索引名 on 表名(属性列1 desc,属性列2 asc,...)

-- 建聚簇索引
creat clustered index 索引名 on 表名(属性列)


-- 删除索引
drop index 索引名

• creat unique index 索引名 on 表名(属性列1 desc,属性列2 asc,…)
  意思是：按照属性列1降序 和 属性列2升序来排序。这是指定的。

思考：索引不能随便创建，代价高，创建好久加快查询，否则就是浪费。

SQL单表查询

---

SQL的执行顺序

SQL语句执行顺序
from -> where -> group by -> having -> select -> order by，集函数必须在分完组后才能使用
解释：from先到表里面，然后where条件查询查找满足的数据，然后将查出来的数据进行分组，然后分完组后觉得某些不满意的数据再筛选having，然后就select选择出来拿到所有数据，然后将所有数据进行排序。
注意 ：如果想要用集函数，必须在执行了分组之后，在where之前都不能用集函数，必须要分完组或者分组之后的才能使用聚集函数。然后having是在你分完组后不满意进行的再次筛选。
建议 ：如果要用集函数对某个属性进行统计必须要对这个属性进行group by分组，换句话说使用了group by 一般都是希望使用集函数的，然后select出来的属性也可以写你分组的属性列。（除非你只是统计元组个数，就可以不用分组直接使用SUM(*)即可）

---

• 查询表的所有

select * from 表名;

• 比较运算法
  其他和基本的编程语言一样，除了不等于号在SQL中是 <>
• SQL去重
  在SQL语句中，不会像关系代数那样自动去重，在SQL中执行完如果有重复的需要手动去重

-- 假定查询SC表的sno，sno有重复的值
select sno from SC;
-- 使用distinct
select distinct sno from SC;

• 注意：只能在select后面加distinct，如果查询多个属性的时候也是，不能在之后的属性加，比如错误示例：select name, distinct sno from SC; 并且distinct必须跟在属性前面，比如使用集函数的时候也一样SUM(distinct Sno)
  解释：由于去重只是将你查询出来的元组去重，所以只要写在select后面就行，去重的就是你的属性列组成的元组不会重复，何必多此一举非得写在后面。
• 条件查询

select * from 表名 where 条件;

• NOT的使用，一般加载条件前面表示 ‘非’
• between…and…关键字

select * 
from 表名 
where 某个属性列的值（必须是数字） between 下限 and 上限;

• 模糊查询like
  • %表示任意长度字符
  • _表示单个字符
  • escape ‘自定义转义符号’

select * from 表名 属性列 like '%模糊查询的字符%';

-- 查询韩性道友名字，并且名字长度是三个字
select * from 表名 name like '韩__';

-- 查询带有'数字'的字符串
select * from 表名 属性名 like '%数字%';

-- 查询带有'%_'的字符串
-- 解释：因为如果你要查询%或者_，
-- 这两个都是特殊字符能单独表示另外含义，
-- 想要表示字符串来查询需要自定义个转义字符，
-- 告诉计算机后面这玩意表示字符串而不是数据库中特殊的含义。
select * from 表名 属性名 like '%\%\_%' escape '\';

• 空值查询

-- 判断为空值
select * from 表名 where 属性列 is null;
-- 判断为不空值
select * from 表名 where 属性列 is not null;

• 与或符号

select * from 表名 where 条件1 and 条件2;
select * from 表名 where 条件1 or 条件2;
select * from 表名 where (条件1 or 条件2) and (条件3 or 条件4);

• 集合是否包含——in

-- in后的集合可以手动输入
select * 
from 表名 
where 属性1字符 in (字符1,字符2,字符3);

-- in后的集合可以通过子查询获得
select * 
from 表名 
where 属性1字符 in (select 字符 from 表名2);

• order by
  order by 后可以选择排序方式，默认是升序。
  order by 可以跟多个属性列。

-- 降序 
-- 意思是：如果属性列1的降序有同排名的就按照下一个属性列2比较厚升序排列
select * from 表名 order by 属性列1 desc,属性列2 asc;
-- 升序
select * from 表名 order by 属性列 asc;

• 注意： is null 不能用 = null判断 ，因为空值不能作比较。

• 分组查询的作用
  我斗胆认为当初前辈们设计这个算法的时候主要是希望实现将我们一个表的数进行统计后出来的数据将其插入到一个新创建的表里面，比如下面这个典型的例子。
  题目：将所有班级的总人数统计然后放到一个新表中。

  
  -- 创建新表
  CREATE TABLE StuNum( Sclass CHAR(8), 
  TotalNumber INT)
  
  --插入
  INSERT INTO StuNum (Sclass, TotalNumber) 
  ( SELECT Sclass, COUNT(*)
  FROM Students S
  GROUP BY Sclass) 
  
  

多表（连接）查询

• 简单的多表查询

-- 查询两个表
select * from 表1,表2 where 表1.字段 = 表2.字段;

• 思考
  一般在一个项目里面，要进行连接查询的一般两个表都会有一定的联系，比如这个表的外键是另一个表的主键。

  • 在条件中写的一般是对于这俩表中的这个字段进行比较。也就是说如果要防止笛卡尔积就是从这里下手，必须将外键与另一个表的主键进行判断才不会发生笛卡尔积。
  • 多表中一般会进行起别名操作（比如自身连接）

• 自然连接
  在关系演算中可以有的自然连接在SQL中也一并被实现出来了。

select sno,cno,grade
from Student S inner join Reports R 
where S.sno=R.sno

• 外连接
  作为主要的表的一方，即使没有在另一个表中被匹配出来的也会一并显示出来，没有匹配成功的那一个元组数据库默认置为为null。

  • 左外连接

  假如说有一个学生没有选课没有成绩就会匹配不出来，
  但是在这里左外连接中，左边作为主表，还是Student，
  所以即使和这个学生没有选课没成绩也会匹配出来，
  Sno会显示，但是由于在Report中没有信息，
  Student中有，Student为主表，所以主表会显示他的Sno基本信息。
  select sno, cno,grade 
  from Student S left join Repoers 
  where S.sno=R.sno
  

  • 右外连接
    理解了 左外连接自是理解右外连接

  select sno, cno,grade 
  from Student S left join Repoers 
  where S.sno=R.sno
  

嵌套查询

• 坑点：子查询里面不能使用order by
• 嵌套查询中能够使用外层查询的表的属性，只要属性列名不重名即可，但刚刚说了，一般在两个表查询中都会对表进行起别名操作，所以在内层查询中要使用外层表就直接用别名.属性名即可。
• exists表示存在的意思，专门来判断子查询是否满足条件，满足的返回真否则假

-- exists嵌套
select * from 表a;
where exists (select 属性字符 from 表b where 表a.属性值 = 表b.属性值);


-- in的嵌套查询
select * from 表名;
where 属性字符1 in (select 属性字符 from 另外的表);
-- any / all (这俩能够进行比较运算符的) 
-- 大于any是大于一个就满足，大于all是大于所有才满足
select * from 表名 ;
where 属性字符1 >all (select 属性字符 from 另外的表);
select * from 表名 
where 属性字符1 >any (select 属性字符 from 另外的表);

联合union

使用union的意思是并操作，能够自动去重。

这个操作要求参加UNION操作的各个结果
表的列数必须相同
且对应属性的数据类型
也相同

-- 集合并
select * from 表1
union
select * from 表2;

-- 集合交
select * from 表1
intersect
select * from 表2;


-- 集合差
select * from 表1
except
select * from 表2;

数据的 insert / update / delete

• insert

-- 如果是按照表的字段顺序来可以不用写出属性名
insert into 表名
values(值1,值2,...)
-- 也可以显示写出属性名（一般都这么做，也建议这么做）
insert into 表名(属性名1,属性名2,...)
values(值1,值2,...)

• update
  where 条件十分重要，如果没有条件限制会把所有的你要修改的属性列都修改。一般是修改一条数据，也可以用嵌套查询作为条件进行集合修改。

update 表名 set 属性列1=新的值,属性列2=新的值,... where 条件

批量更新有点不一样，一开始看的时候把我看懵了
代码如下，个人理解，不代表真实的底层原理：一般的update语句是没有from的，因为本身就知道update的表名，然后如果也知道了条件就直接使用where即可，这都是一般用户对于自己的持有的数据进行更新的操作，如果不涉及其他表这里会自动省略from，因为update就已知了你要更新的表，假如你要更新的表叫A，所以会自动省略 from A 这里，然后可以直接接where语句了。
但是对于DBA或者程序员有时候需要动态修改的，所以这时候就需要from，from连接的表，然后将这个表中的数据查询出来将其更新或者插入到本表中（本表是指update 后面的那个表）
因此这里动态更新规范的写法应该是： 将要新的数据集合到一个表中，然后让update语句使用这个表，对他update后面接的表的属性进行更新。

UPDATE StuInfo
SET TotalCredit=CR.TC
FROM StuInfo S,
	(SELECT R.Sno, SUM(Ccredit) TC
	FROM Reports R, Courses C
	WHERE R.Cno=C.Cno AND Grade>=60
	GROUP BY R.Sno) CR
WHERE S.Sno=CR.Sno

• delete
  条件十分重要，如果不写条件会把所有数据删除掉，一般是删除一条数据，也可以用嵌套查询作为条件进行集合删除。

delete from 表名 where 条件

//批量删除，这里的θ是指比较运算符其他 比如除了基本> < = 还有in all any这类
delete from 表名 where 表名.属性名 θ (子查询)

• 注意：删除表是用drop table 表名; 而删除数据是delete关键字。

常用集函数

-- 计算元组总数
select count(*) from 表名;
-- 计算某个属性列的和
select sum(grade) from 成绩;
-- 计算某个属性列的平均值
select avg(grade) from 成绩;
-- 计算某个属性列的最大值/最小值
select max(grade) from 成绩;
select min(grade) from 成绩;

ROUND(值，保留小数点)函数
例子：保留两位小数ROUND(11.11111,2)

• 注意事项：所有的集函数不计算null，如果有一边为null值不管是怎样计算结果都为null

• 总结：要想使用集函数，必须先group by 之后的才能执行，或者说在where中不能用集函数

• 思考：我理解的集函数是，将分好组的或者准备select出来的数据进行一次函数计算，出来的结果只有一个，所以一般进行集函数的时候都会进行一次分组，那么问题就在这里，这里必须要养成习惯就是：group by （属性列），只有这个分组的属性列才能出现在集函数括号里面比如：count（属性列），其他不可以，只有这样才不会发生报错。良好习惯：分组（属性列a），集函数（属性列a）

• 使用as起别名

select grade as g form sc;
//给属性名起别名，会显示到查询结果的属性列名字为g而不是grade了

select * from sc as sc1 ,sc as sc2 where sc1.sno=sc2.sno;
//自身连接必须要起别名，否则等值连接会做不了

总结

• 对于整体的级别都用这三个DDL关键字
  注意这里的整体是：数据库、表、索引（表就是对属性列或者约束之类的要使用这关键字，然后索引也一样只是索引没有alter这个操作，只有创建删除）

CREATE、DROP、ALTER

比如：定义数据库之类肯定属于这范畴，然后索引也属于数据库级别的，因为索引关系到整个数据库的物理存储，还有关于约束的也是。

• 对于表数据的有关操作用这几个DML关键字

INSERT、UPDATE、DELETE

• 对于数据权限的操作用DCL关键字

GRANT、REVOKE

说来也奇怪，当时没太注意DDL、DML、DCL，但是知道有这玩意，可当我开始做总结的时候惊奇的发现我总结的已经被人总结过了。