目录
说明
1.本文只对基本的sql语句进行总结 不对如何使用SSMS 以及如何连接做说明 详情请访问Sql Server 01
2.本文只对标准sql进行总结
正文
表3.2 SQL的动词
| SQL 功 能 | 动词 |
| 数 据 查 询 | SELECT |
| 数 据 定 义 | CREATE，DROP，ALTER |
| 数 据 操 纵 | INSERT，UPDATE，DELETE |
| 数 据 控 制 | GRANT，REVOKE |
| i | 说明 |
| Q | 问题 |
| A | 解答 |
| At | 注意 |
1.create
创建模式
create schema 'S-T' authorization Wang; --前提有wang这个用户i:1.SQL server 中，默认用户为 dbo，在没有创建模式的情况下，默认的模式名为dbo
2.authorization --授权
创建表
create table tabl1( Col1 smallint primary key,--主码 可以唯一确定一个元组
Col2 int,
Col3 char(10),
Col4 numeric(10,3),
Col5 decimal(5,2),
foreign key(Col5) references Other(Fcol) --Col5是外码 是参照了Other表的Fcol列
);i:1.SQL数据库常用字段数据类型说明
2. PRIMARY KEY (Sno,Cno), 主码由两个属性构成，必须作为表级完整性进行定义
创建索引
i:次序默认为ASC
CREATE UNIQUE INDEX Stusno ON Student(Sno);--Student表按学号升序建唯一索引，
CREATE UNIQUE INDEX Coucno ON Course(Cno);--Course表按课程号升序建唯一索引，
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);--SC表按学号升序和课程号降序建唯一索引
创建视图
CREATE VIEW IS_Student --创建视图
AS
SELECT Sno,Sname,Sage --以这些在Student 表得到的条件
FROM Student
WHERE Sdept= 'IS';i: 带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上where 子句里的条件
CREATE VIEW IS_S1(Sno,Sname,Grade) --行列子集视图
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept= 'IS' AND
Student.Sno=SC.Sno AND
SC.Cno= '1';
创建角色
CREATE ROLE R1;
2.insert
INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );
插入到表
--将学生张成民的信息插入到Student表中。
INSERT
INTO Student
VALUES ('201215126','张成民','男’,18,'CS');
i:1.插入时 insert 后什么都不写 那么values 括号内信息的顺序 必须和表中属性顺序一致
2.插入时注意表中不能为空和有外码约束的属性
3.alter
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
i:
修改表
--ADD
ALTER TABLE Student ADD S_entrance DATE; --不管基本表中原来是否已有数据，新增加的列一律为空值
--ALTER
ALTER TABLE Student ALTER COLUMN Sage INT;
修改索引
ALTER INDEX SCno RENAME TO SCSno;
--将SC表的SCno索引名改为SCSno
4.select
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
查询指定列
SELECT Sno,Sname
FROM Student; --查询全体学生的学号与姓名。查询全部列
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
--或
SELECT *
FROM Student; 查询经过计算的值
SELECT Sname,2016-Sage --假设当时为2016年 计算出生年份
FROM Student;
使用列别名改变查询结果的列标题
SELECT Sname NAME,'Year of Birth:' BIRTH,
2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
消除取值重复的行
-- 如果没有指定DISTINCT关键词，则缺省为ALL
SELECT Sno FROM SC;
--等价于：
SELECT ALL Sno FROM SC;
指定DISTINCT关键词，去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
带有比较大小的查询
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
确定范围的查询
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
确定集合的查询
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA’,'IS' );
模糊查询
i: % （百分号） 代表任意长度（长度可以为0）的字符串
例如a%b表示以a开头，以b结尾的任意长度的字符串
_ （下横线） 代表任意单个字符。
例如a_b表示以a开头，以b结尾的长度为3的任意字符串
where [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]例如：查询学号为201215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘201215121';
--等价于
SELECT *
FROM Student
WHERE Sno = ' 201215121 ';匹配串为含通配符的字符串的模糊查询
--查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
--查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname，Sno
FROM Student
WHERE Sname LIKE '_ _阳%';
使用换码字符将通配符转义为普通字符的模糊查询
--查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ;
涉及空值的查询
SELECT Sno，Cno
FROM SC
WHERE Grade IS NULL;
多重条件查询
逻辑运算符：AND和 OR来连接多个查询条件的查询
i:AND的优先级高于OR 可以用括号改变优先级
--查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20;
order by 子句
SELECT Sno, Grade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC; --查询的成绩按降序排列
聚集函数
MAX([DISTINCT|ALL] <列名>) MIN([DISTINCT|ALL] <列名>)
SELECT COUNT(*)
FROM Student; --查询总人数
SELECT AVG(Grade) --求平均值
FROM SC
WHERE Cno= ' 1 ';
SELECT SUM(Ccredit) --求总学分
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
Group by子句
细化聚集函数的作用对象
按指定的一列或多列值分组，值相等的为一组
SELECT Cno，COUNT(Sno)
FROM SC
GROUP BY Cno; --Cno为一组 进行记录有多少Sno
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3; At：WHERE子句中是不能用聚集函数作为条件表达式 这时需要正确的使用Having
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
i:having 和 where 的区别
WHERE子句作用于表或视图，从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组。
连接查询
等值与非等值连接查询
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno; --对比关系代数中的等值连接自身连接
需要给表起别名以示区别
所有属性名都是同名属性，因此必须使用“别名”
SELECT FIRST.Cno, SECOND.Cpno --同一个表做两个表的属性
FROM Course FIRST, Course SECOND --同一个表
WHERE FIRST.Cpno = SECOND.Cno;
外连接
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno); --写列出左边关系中的
多表连接
两个以上的表进行连接
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course --多表连接
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
嵌套查询
带有IN谓词的子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');
SELECT Sno,Sname --最后在Student关系中
FROM Student --取出Sno和Sname
WHERE Sno IN
(SELECT Sno --然后在SC关系中找出选
FROM SC --修了3号课程的学生学号
WHERE Cno IN
(SELECT Cno --首先在Course关系中找出
FROM Course -- “信息系统”的课程号，为3号
WHERE Cname= '信息系统'
)
);不相关子查询
即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');相关子查询
子查询的查询条件依赖于父查询
由外向里
①取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
②再取外层表的下一个元组
③重复这一过程，直至外层表全部检查完为止
SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG（Grade）
FROM SC y
WHERE y.Sno=x.Sno);带有比较运算符的子查询
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = --用 = 替换in
(SELECT Sdept
FROM Student
WHERE Sname= '刘晨');
带有ANY（SOME）或ALL谓词的子查询
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' CS ')
AND Sdept <> ‘CS ' ; --父查询块中的条件
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' CS ') --查询所有比CS系都小的年龄
AND Sdept <> ' CS ’;
带有EXISTS谓词的子查询
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ' 1 ');
i:所有带IN谓词、比较运算符、ANY和ALL谓词的子查询
都能用带EXISTS谓词的子查询 等价替换 详见
集合查询
查询的本来就是一个集合 所以巧用以下语句可以更方便的查询
并-UNION
交-INTERSECT
差-EXCEPT
SELECT *
FROM Student
WHERE Sdept= 'CS' --条件1
UNION
SELECT *
FROM Student
WHERE Sage<=19; --条件2基于派生表的查询
子查询不仅可以出现在WHERE子句中，
还可以出现在FROM子句中，
这时子查询生成的临时派生表成为主查询的查询对象。
SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno
and SC.Grade >=Avg_sc.avg_grade
); --为临时派生表
5.update
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
作用：
修改指定表中满足WHERE子句条件的元组
SET子句给出<表达式>的值用于取代相应的属性列
如果省略WHERE子句，表示要修改表中的所有元组
可以修改：
修改某一个元组的值
修改多个元组的值
带子查询的修改语句
UPDATE Student
SET Sage=22
WHERE Sno=' 201215121 '; --修改Student 表中的Sno 的Sage为22
6.drop
DROP TABLE <表名>［RESTRICT| CASCADE］;
删除模式
drop schema Wang cascade;--cascade删除模式的同时把该模式中所有的数据库对象全部删除
i: cascade的位置还可以使用
RESTRICT（限制）：
删除表
DROP TABLE Student CASCADE;
--基本表定义被删除，数据被删除
--表上建立的索引、视图、触发器等一般也将被删除
删除索引
DROP INDEX Stusname;删除视图
DROP VIEW BT_S ; --注意级联删除7.Delete
DELETE
FROM <表名>
[WHERE <条件>];
功能：
删除指定表中满足WHERE子句条件的元组
DELETE
FROM Student
WHERE Sno= '201215128 '; --在Student 表中删除属性Sno= 201215128 的元组
8.Grant
GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
i:WITH GRANT OPTION子句:
指定：可以再授予
没有指定：不能传播
GRANT SELECT --把查询Student表权限授给用户U1
ON TABLE Student
TO U1;
GRANT ALL PRIVILIGES --把对Student表和Course表的全部权限授予用户U2和U3
ON TABLE Student,Course
TO U2,U3;
GRANT SELECT --把对表SC的查询权限授予所有用户
ON TABLE SC
TO PUBLIC;
GRANT UPDATE(Sno), SELECT --把查询Student表和修改学生学号的权限授给用户U4
ON TABLE Student
TO U4;
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION; --把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户
--赋予R1 有对Student表进行的SELECT、UPDATE、INSERT权限
GRANT SELECT, UPDATE, INSERT
ON TABLE Student
TO R1;
9.Revoke
REVOKE <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];
REVOKE UPDATE(Sno) --用户U4修改学生学号的权限收回
ON TABLE Student
FROM U4;
REVOKE SELECT --收回所有用户对表SC的查询权限
ON TABLE SC
FROM PUBLIC;
REVOKE INSERT --把用户U5对SC表的INSERT权限收回
ON TABLE SC
FROM U5 CASCADE ;
REVOKE R1 --可以一次性通过R1来回收王平的这3个权限(王平的权限来自于R1）
FROM 王平;
10.Audit
AUDIT ALTER,UPDATE --对修改SC表结构或修改SC表数据的操作进行审计
ON SC;
NOAUDIT ALTER,UPDATE --取消对SC表的一切审计
ON SC;
Thinking
温故而知新，总结真的能很大程度上帮助有顺序有条理的记忆，自己去总结就会想需要注意什么，这样就会对重要的知识二次记忆，对之前忽略的细节，也可以很好的补充。
PS：谁能告诉我为什么那个目录点击是不对的位置。。。。
1691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
