SQL server 一些命令
主键约束 PRIMARY KEY
外键约束 FOREIGN KEY
默认值约束 DEFAULT
自增ID IDENTITY(1,1) 第一个种子值,第二个步长
2
3
4
5
# 一.定义
数据:描述事物的符号记录。
数据库:一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
数据库管理系统:一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,简称DBMS
数据库系统:指在计算机系统引入数据库后的系统,一般由数据库,数据库管理系统,应用系统和数据库管理员构成。
登录名:服务器方的一个实体,使用一个登录名只能进入服务器,但是不能让用户访问服务器中的数据库资源。每个登录名的定义存放在master数据库的syslogins表中。
用户名:一个或多个登录对象在数据库中的映射,可以对用户对象进行授权,以便为登录对象提供对数据库的访问权限。用户定义信息存放在每个数据库的sysusers表中。
SQLSERVER把登录名与用户名的关系称为映射。用登录名登录SQLSERVER后,在访问各个数据库时,SQLSERVER会自动查询此数据库中是否存在与此登录名关联的用户名,若存在就使用此用户的权限访问此数据库,若不存在就是用guest用户访问此数据库(guest是一个特殊的用户名,后面会讲到)。 一个登录名可以被授权访问多个数据库,但一个登录名在每个数据库中只能映射一次。即一个登录可对应多个用户,一个用户也可以被多个登录使用。好比SQLSERVER就象一栋大楼,里面的每个房间都是一个数据库.登录名只是进入大楼的钥匙,而用户名则是进入房间的钥匙.一个登录名可以有多个房间的钥匙,但一个登录名在一个房间只能拥有此房间的一把钥匙。 链接或登录Sql Server服务器时是用的登录名而非用户名登录的,程序里面的链接字符串中的用户名也是指登录名。
B-tree:一个节点可以拥有多于两个子节点的二叉查找树。
根结点至少有两个子节点
每个结点有M-1个key,升序排列
。。。。
特点:
1.关键字集合分布在整棵树中;
2.任何一个关键字出现且只出现在一个结点中;
3.搜索有可能在非叶子结点结束;
4.其搜索性能等价于在关键字全集内做一个二分查找
与B+tree的区别:
(1)有K个子节点的节点必然有k个key
(2)非叶子结点仅具有索引作用,跟记录有关的信息均放在叶子节点中
(3)树的所有叶子节点构成一个有序链表,可以按照Key排序的次序遍历全部记录
B+树的优点在于: 1.由于B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。 数据存放的更加紧密,具有更好的空间局部性。 因此访问叶子节点上关联的数据也具有更好的缓存命中率。 2.B+树的叶子结点都是相链的,因此对整棵树的便利只需要一次线性遍历叶子结点即可。 而且由于数据顺序排列并且相连,所以便于区间查找和搜索。 而B树则需要进行每一层的递归遍历,相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。
但是B树也有优点,其优点在于: 由于B树的每一个节点都包含key和value,因此经常访问的元素可能离根节点更近,因此访问也更迅速。
触发器:触发器是一种特殊类型的存储过程,它被分配给某个特定的表。当对这个表进行插入、更新或删除操作时, 自动调用触发器执行触发器里规定的操作.
触发器是确保数据完整性和一致性的基本有效的方法。
和表相关联,可以看作是表的一部分;
不能直接引用,通过insert/update/delete自动激活;
属于事务机制;
同一类型的触发器在表上可以有多个
特点:
- 不接受用户参数,也不返回用户参数;
- 事件驱动,自动调用;存储在表上。
- 对数据库中的相关表进行级联更新和删除
- 强制比Check约束更复杂的数据完整性。
- 可以引用其他表中的列。
分为:插入型触发器,删除型触发器,更新型触发器
--创建触发器
CREATE TRIGGER 触发器名
ON 表名
FOR delete/ insert/ update
AS 触发器操作
--删除触发器
DROP TRIGGER 触发器名
--启用,停用触发器
ALTER TABLE 表名 DISABLE|ENABLE TRIGGER 触发器名
2
3
4
5
6
7
8
9
10
# 1.模式
sqlserver数据库的结构为: 数据库–>模式–>表/视图/存储过程/函数/触发器 模式就是数据库中一类对象的集合
定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
当模式名缺省时,自动创建一个模式名为用户名的模式
DROP SCHEMA <模式名> <CASCADE|RESTRICT> --cascade(级联), restrict(限制)
cascade(级联)表示在删除模式的同时把该模式中所有的数据库对象全部删除 restrict(限制)表示如果该模式中已经定义了下属的数据库对象,如表或视图等,则拒绝该删除语句的执行。
# 2.表的定义、删除与修改
2.1 定义基本表
列级约束有六种:主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null
表级约束有四种:主键、外键、唯一、检查
外键参考必须是唯一性索引
创建一个模式就是建立了一个数据库(命名空间)
CREATE TABLE <表名> (<列名> <数据类型> [列级约束条件]
, <表级完整性约束条件>)
2
[例1]
CREATE TABLE Student
(Sno CHAR(7) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
2
3
4
5
6
7
[例2]
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY Cpno REFERENCES Course(Cno)
);
--Cpno是外码
2
3
4
5
6
7
8
CREATE TABLE SC
(Sno CHAR(7),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FORENGIN KEY Sno REFERENCES Student(Sno),
FORENGIN KEY Cno REFERENCES Course(Cno)
);
2
3
4
5
6
7
8
2.2 数据类型
# 3.模式与表
方法一: 在创建表时,显示给出模式名
CREATE TABLE S-T.Student(.....)
方法二: 创建模式时同时创建表
方法三: 设置所属模式
# 4.修改表
使用 ALTER TABLE
修改
ALTER TABLE <表名>
[ADD <新列名> <数据类型> <完整性约束>] --增加新列
[DROP <完整性约束名>]
[MODIFY <列名> <数据类型>] --修改字段类型
--添加字段
ALTER TABLE 表名 ADD 字段名 INT DEFAULT 0
--修改字段名
EXEC sp_rename Student.Ssex,SEX
--修改字段类型
ALTER TABLE 表名 ALTER COLUMN 字段名 类型
--修改字段默认值
ALTER TABLE 表名 DROP CONSTRAIT 约束名
ALTER TABLE 表名 ADD CONSTRAIT 约束名 DEFAULT (('默认值')) for 字段名
--添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(字段名) REFRENCES 被引用表名(字段名)
--删除字段
ALTER TABLE DROP COLUMN 字段名
--删除约束 先获取表有哪些约束,再根据名字删除
sp_helpconstraint 表名
ALTER TABLE DROP CONSTRAINT 约束名
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
tip::: 介绍
添加字段时若非空时需要添加一个默认值,例如
ALTER TABLE 表名 ADD 字段名 NOT NILL DEFAULT 0
修改字段类型时有默认值需要先删除约束,再修改。
:::
ALTER TABLE Student ADD S_en DATE;
ALTER TABLE Student MODIFY Sage INT; --修改原有列属性可能会破坏
ALTER TABLE Student DROP UNIQUE(Sname)
2
3
使用DROP TABLE
删除表。
DROP TABLE Student;
# 5.索引的建立与删除
建立索引是加快查询速度的有效手段。分为聚集索引和非聚集索引
UNIQUE
表示唯一索引,CLUSTER
表示聚簇索引。
CREATE [UNIQUE|CLUSTER] INDEX 索引名 ON 表名(列名|ASC(DESC))
CREATE UNIQUE INDEX S ON Student(Sno,DESC)
2
3
删除索引: DROP INDEX 索引名
# 二.数据查询
SELECT [ALL|DISTINCT] <目标列表达式>
FROM <表名或视图名>
WHERE <条件表达式>
GROUP BY <列名> HAVING <条件表达式>
2
3
4
# 1.单表查询
SELECT DISTINCT Sno
FROM SC; --去重
2
1.1查询满足条件的元组,通过 WHERE
实现
查询条件 | 谓词 |
---|---|
比较 | =,<,>,>=,<=,!=,<>,!>,!< |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,NOT IS NULL |
多重条件 | AND,OR,NOT |
SELECT Sname FROM Student WHERE Sdept='CS';
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('CS','MA','IS')
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%'; --查询所有姓刘的学生姓名,学号和性别
SELECT Sname FROM Student WHERE Sname LIKE '欧阳_'
SELECT Sno,Cno FROM SC WHERE Grade IS NULL
SELECT Sname FROM Student WHERE Sdept='CS' AND Sage <20;
2
3
4
5
6
7
8
1.2 ORDER BY
子句:对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认为升序。
SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
2
3
4
1.3聚集函数
COUNT
,SUM
,AVG
,MAX
,MIN
COUNT([DISTINCT|ALL],[*|列名])
1.4GROUP BY
子句:将查询结果按照某一列或多列的值分组,值相等的为一组
SELECT score FROM sc GROUP BY score
HAVNIG短语指定筛选条件
HAVING作用于组,从中选择满足条件的
WHERE作用于表或者视图,从中选择满足条件的
例如使用了GROUP BY
短语就使用HAVING
# 2.连接查询
2.1等值与非等值连接查询 (内连接:也称为等值连接,返回两张表都满足条件的部分)
SELECT * FROM Teacher
SELECT Course.*,Teacher.*
FROM Course,Teacher
WHERE Course.TId=Teacher.TId --等值查询,连接符不是‘=’的就是非等值连接查询
01 语文 02 02 李四
02 数学 01 01 张三
03 英语 03 03 王五
2
3
4
5
6
7
8
自然连接:等值连接中把目标列重复的属性列去掉
2.2自身连接 需要为表取别名
2.3外连接
外连不但返回符合连接和查询条件的数据行,还返回不符合条件的一些行。外连接分三类:左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)暂时就学会这些
2.4复合连接
WHERE子句中有多个条件
# 3.嵌套查询
1.IN
SELECT sname from Student WHERE sid in ( SELECT sid FROM sc WHERE score>80)
# 三.视图
# 1.建立视图
CREATE VIEW <视图名> [列名,列名...]
AS 子查询
[WHERE CHECK OPTION]
2
3
子查询可以是任意SELECT语句,但通过不允许含有ORDER BY 子句和DISTINCT 短语
WHERE CHECK OPTIN对视图进行update,insert,delete时要保证行满足视图定义中的谓词条件(即子查询中的条件表达式);
# 2.删除视图
DROP VIEW <视图名>
# 3.查询视图
视图就相当于一个虚拟表,所以所有对基本表的查询语句,对视图也同样适用
# 4.视图更新
对视图的更新最终会转换成对基本表的更新。 可用操作有update,insert,delete,
# 例子
CREATE TABLE zuo
(ID INT IDENTITY(1,1) PRIMARY KEY,
COL INT DEFAULT(100),
SEX INT CHECK(SEX=0 OR SEX=1),
RAD INT FOREIGN KEY REFERENCES S_id(id))
2
3
4
5
# 四.实例
--MySQL根据timestamp类型字段查询当日,本月数据
select * from __ where DATE_FORMAT(timestamp字段,'%Y-%m-%d') = DATE_FORMAT(CURDATE(),'%Y-%m-%d');
select * from __ where DATE_FORMAT(timestamp字段,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m');
--上月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format(now( ), '%Y%m' ) ,date_format(时间字段名, '%Y%m')) =1
2
3
4
5
来自 blog
https://blog.csdn.net/wenjoy/article/details/102777470
https://www.begtut.com/sql/func-mysql-period-diff.html