Sql语句
1 数据定义
1.1 CREATE TABLE
定义基本表
CREATE TABLE <表名> (<列名> <数据类型> [列级完整性约束条件]
[, <列名> <数据类型> [列级完整性约束条件]]
...
[, <表级完整性约束条件>]);
primary key (A1,A2,A3,...)
:指定主键属性集foreign key (A1,A2,A3,...) references T2
:声明表示关系中任意元组在属性(A1,A2,A3,...)
上的取值必须对应于T2
中某元组在主码属性上的取值
数据类型:
int
:整形。等价于全称integer
smallint
:小整数型real
,double precision
:浮点数与双精度浮点数(精度与机器相关)float(n)
:精度至少为n位的浮点数char(n)
:固定长度的字符串varchar(n)
:可变长度的字符串
例:建立一个“学生信息”表Student:
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
1.2 ALTER TABLE
修改基本表
ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束]]
[DROP <完整性约束>]
[MODIFY COLUMN <列名> <数据类型>];
ADD
子句:增加新列和新的完整性约束DROP
子句:删除指定的完整性约束MODIFY COLUMN
子句:修改原有列的定义,包括列名和数据类型
例子:
ALTER TABLE Student ADD S_entrance DATE; //向Student表增加“入学时间”列,其数据类型为日期型
ALTER TABLE Student MODITY COLUMN Sage INT; //将年龄的数据类型由字符型改为整数
ALTER TABLE Student ADD UNIQUE(Sname); //增加Student表Sname必须取唯一值的约束条件
1.3 DROP TABLE
删除基本表
DROP TABLE <表名> [RESTRICT | CASCADE];
RESTRICT
:删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如:check、foreign key等约束),不能有视图,不能有触发器,不能有存储过程或函数等。如果存在这些依赖该表的对象,则该表不能被删除CASCADE
:删除没有条件限制。在删除该表的同时,相关的依赖对象,例如视图,都将被一起删除
2 数据查询
2.1 SELECT
SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>]...
FROM <表名或视图名> [, <表名或视图名>]...
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
ALL
:显示所有(不去重)DISTINCT
:去除重复
整个SELECT
语句的含义是:根据WHERE
子句的条件表达式,从FROM
子句指定的基本表或视图中找出满足条件的元组,再按SELECT
子句中的目标列表达式,选出元组中的属性值形成结果表
如果有GROUP BY
子句,则将结果按<列名1>
的值进行分组,该属性列值相等的元组为一个组。通常会在每组中作用聚合函数。如果GROUP BY
子句带HAVING
子句,则只有满足指定条件的组才予以输出
如果有ORDER BY
子句,则结果表还要按<列名2>
的值的升序或降序排列
2.2 WHERE
WHERE
子句可以使用下列一些条件表达式进行筛选:
=
:指定属性的值为给定值的IS
:如IS NULL
。不能被=
代替like
:字符串匹配%
:匹配任意子串_
:匹配任意一个字符
and
、or
、not
BETWEEN AND
(NOT BETWEEN AND
):介于…之间的(不介于…之间的)IN(...)
:指定属性的值为IN
中给出的某个值的
SELECT * from Student WHERE Sname='Bill Gates'; //名字是Bill Gates的
SELECT * from Student WHERE Sname like '%Bill%'; //名字中包含有Bill的
SELECT * from Student WHERE Sage BETWEEN 20 AND 23; //年龄20~23的
SELECT Sname , Ssex from Student WHERE Sdept IN('CS','IS','MA'); //CS、IS或MA系的
SELECT * FROM Student WHERE Sage IS NULL; //没有年龄信息的
2.3 ORDER BY
对查询结果按一个或多个属性列的升序(ACS)或降低(DESC)排序,默认为升序
例子:
SELECT * FROM Student ORDER BY Sage;
SELECT * FROM Student ORDER BY Sdept, Sage desc; //先按专业升序排序,然后同一专业按年龄降序排序
2.4 LIMIT
可以用于强制
SELECT
返回指定的记录数
接受1个或2个数字参数。参数必须是一个整数常量:
- 1个参数:表示返回最前面的记录行数目
- 2个参数:第一个指定第一个返回记录行的偏移量(从0开始算),第二个参数指定返回记录行的最大数目
例子:
SELECT * FROM Student LIMIT 5, 10; //返回记录行6-15
SELECT * FROM Student LIMIT 5; //返回前5个记录行
2.5 聚集函数
聚集函数有以下几种:count
、sum
、avg
、max
、min
- 总数:
select count(*) as totalcount from table1;
- 求和:
select sum(field1) as sumvalue from table1;
- 平均:
select avg(field1) as avgvalue from table1;
- 最大:
select max(field1) as maxvalue from table1;
- 最小:
select min(field1) as minvalue from table1;
2.6 GROUP BY
根据一个或多个属性的值对元组分组,值相同的为一组
分组后聚集函数将作用于每一个组,即每一组都有一个函数值
如果分组后还要求按一定的条件对这些分组进行筛选,最终只输出满足指定条件的组,则使用HAVING
短语指定筛选条件
例子:
//按年龄分组,统计每个年龄的人数,并输出(年龄,该年龄的人数)
select Sage, count(*) from Student group by Sage;
//按年龄分组,统计每个年龄的人数,选出人数大于1的分组,输出(年龄,该年龄的人数)
select Sage, count(*) from Student group by Sage having count(*) > 1;
2.7 连接查询
一个查询涉及多个表
假设有2个表——Student表和SC表(选课表):
- 内连接(自然连接):当使用内连接时,如果Student中某些学生没有选课,则在SC中没有相应元组。最终查询结果舍弃了这些学生的信息
- 外连接:如果想以Student表为主体列出每个学生的基本情况及其选课情况。即使某个学生没有选课,依然在查询结果中显示(SC表的属性上填空值)。就需要使用外连接
例子:
//内连接:查询每个学生及其选修课程的情况(没选课的学生不会列出)
SELECT Student.*, SC.*
FROM Student , SC
WHERE Student.Sno=SC.Sno;
//外连接:查询每个学生及其选修课程的情况(没选课的学生也会列出)
SELECT Student.*, SC.*
FROM Student LEFT JOIN SC ON(Student.Sno=SC.Sno);
3 数据操作
3.1 INSERT
插入元组
INSERT
INTO table1(field1,field2...)
VALUES(value1,value2...);
如果INTO
语句没有指定任何属性列名,则新插入的元组必须在每个属性列上均有值
例子:
INSERT INTO Student(Sno, Sname, Ssex, Sdept, Sage)
VALUES('201009013', '王明', 'M', 'CS', 23);
3.2 UPDATE
修改(更新)数据
UPDATE table1
SET field1=value1, field2=value2
WHERE 范围;
功能是修改指定表中满足WHERE
子句条件的元组。如果省略WHERE
子句,则表示要修改表中的所有元组
例子:
UPDATE Student
SET Sage=22
WHERE Sno='201009013';
3.3 DELETE
删除元素
DELETE
FROM table1
WHERE 范围;
功能是删除指定表中满足WHERE
子句条件的元组。如果省略WHERE
子句,则表示删除表中的所有元组。但表仍存在
例子:
DELETE
FROM Student
where Sno='201009013';