当前位置:文档之家› Windows SQL常用命令

Windows SQL常用命令

1.建表:
createtable stud01
(xh char(10)primarykey,
xm char(10),
nl char(10),
bj char(10)
)
2.查询
SELECT命令的语法为:
SELECT [DISTINCT|ALL] {*|模式名.] {表名|视图名|
快照名] .*…| {表达式[列别名]…} } [, [模式名. ] {表名|
视图名|} .*…| 表达式[列别名] ]…
FROM [模式名.] {表名|视图名|快照名} [@数据库链名] [表别名]
[, [模式名.] {表名|视图名|快照名} [@数据库链名]
[表别名] ]…
[WHERE条件]
[START WITH条件CONNECT BY 条件]
[GROUP BY表达式[,表达式] …[HAVING条件]
[UNION|UNION ALL |INTERSECT|MINUS]SELECT命令
[ORDER BY{表达式|位置} [ASC|DESC] [, {表达式|位置[ASC|DESC]}]…]
select*from stud01
注:查询条件
Where xh=’’ and xh=’’
And 与,OR 或关系
(1) 查询年纪为12的学生姓名;
SELECT FROM STUDENT WHERE AGE=12;
(2) 查询年纪在12至16岁之间的学生姓名;
SELECT FROM STUDENT WHERE AGE BETWEEN 12 AND 16;
(3) 查询年纪不在12至16岁之间的学生姓名;
SELECT FROM STUDENT WHERE AGE NOT BETWEEN 12 AND 16; (4) 查询所有姓名以A开头的学生的姓名;
SELECT FROM STUDENT WHERE NAME LIKE 'A%';
(5) 列出所有学生年纪的和,年纪的平均值,最大值,最小值,最大值与最小值之间的差值;
SELECT AVG(AGE), SUM(AGE), MAX(AGE), MIN(AGE), MAX(AGE)-MIN(AGE); (6) 将所有学生按学号顺序升序排列;
SELECT * FROM STUDENT ORDER BY NO DESC;
(7) 将所有学生按学号顺序升序排列;
SELECT * FROM STUDENT ORDER BY NO ASC;
3.插入数:
insert stud01values ('0002','std001','21','计算机')
4.修改
update stud01
set xm='std002'
where xh='0002'
5.删除
delete stud01where xh='0003'
6.去除重复的字段值
selectDISTINCT(xh)from stud01
7.统计个数count(*),sum(),
select count(*)from stud01where xm='std001'
8. 分组(groupby,HAVING)
select xm from stud01where xm='std001'
groupby XM
HAVING COUNT(*)>3
9.substring
Selct substring(bj, 2, 3) from stud01
9.多表查询
学生—课程数据库中包含三个表
学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
Student由学号(Sno),姓名(Sname),性别(Ssex),年龄(Sage),所在系(Sdept)五个属性组成,其中Sno为主码
课程表:Course(Cno,Cname,Cpno,Ccredit)
Course由课程号(Cno),课程名(Cname),选修课号(Cpno),学分(Ccredit)四个属性组成,其中Cno为主码。

学生选课程表:SC(Sno,Cno,Grade)
SC由学号(Sno),课程号(Cno),成绩(Grade)三个属性组成,主码
为(Sno,Cno)
1、查询既不是信息系,数学系,也不是计算机科学系的学生的姓名和性别
Select Sname,Ssex
From Student
Where Sdept Not IN(“IS”,”MA”,”CS”)
2、查询选修了3门以上课程的学生学号
Select Sno from Sc
GROUP BY Sno
HAVING COUNT(*)>3
3、查询选修了课程名为”信息系统”的学生学号和姓名
Select A.Sno, A.Sname from Student A ,Sc B ,Course C
Where A.Sno=B.Sno and o=o and ame=”信息系统”
10.视图
createview v_stud1asselect*from stud01
11.--所有冻结的商家,客户,---
SELECT pt.TraceTime, substring(pt.Content, 7, 15),
substring(pt.Content, 223, 19),substring(pt.Content, 261, 3),
pt.PayTraceAmount, pt.ResponseDesc
FROM PayTrace pt
JOIN Business bs ON pt.LockKey = bs.FrozenNo AND bs.FrozenNo like '12%'
11,建立索引。

CREATE INDEX index_name ON table_name (column_list)
12.删除索引
DROP INDEX index_name ON talbe_name。

相关主题