简单sql语句

一文搞懂候选码、主码、全码、外码、主属性、主键、主关键字、非主属性

http://t.csdn.cn/TVY44

1.定义表

CREATE TABLE 表名（

    列名  数据类型  列级完整性约束条件，

    列名  数据类型  列级完整性约束条件，

    ......


    列名  数据类型  列级完整性约束条件，

    表级完整性约束条件

）；

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null
表级约束有四种：主键、外键、唯一、检查

各约束具体说明：

1.主键约束

PRIMARY KEY约束

PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。

2.唯一性(UNIQUE)约束

UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。

3.检查约束

CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。

4.缺省约束

使用缺省约束时，应该注意以下几点：

每个字段只能定义一个缺省约束。

如果定义的缺省值长于其对应字段的允许长度，那么输入到表中的缺省值将被截断。

不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。

如果字段定义为用户定义的数据类型，而且有一个缺省绑定到这个数据类型上，则不允许该字段有缺省约束。

5.外部键约束

外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。

6.NULL约束

（1）NULL/NOT NULL

是否允许该字段的值为NULL。

2.修改基本表

ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]]
[ DROP column 列名]
[add constraint <完整性约束名> <完整性约束>]
[DROP constraint <完整性约束名>]
[MODIFY<列名> <数据类型>[完整性约束]]
];

例：

CREATE TABLE Student(
 Sno varchar(12) primary key, 
 Sname varchar(20),
 Ssex char(2) default('男'), 
 Sage number(3), 
 Sdept varchar(10)
);
向Student表增加“入学时间”列，其数据类型为日期型。
alter table student add  S_entrance DATE;

3.删除基本表

DROP TABLE<表名>[RESTRICT|CASCADE]

RESTRICT:欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数，否则不能被删除

CASCADE:删除无限制条件，直接删除，与之相关的一起被删除

4.索引的建立与删除

• 建立

  CREATE [UNIQUE] [ INDEX<索引名> ON <表名>(<列名>[<次序>])

​ UNIQUE表明此索引的每一个索引值只对应唯一的数据记录

​ CLUSTER表示要建立的索引是聚簇索引

• 修改

  ​ ALTER INDEX <旧索引名> RENAME TO <新索引名>

• 删除

  DROP INDEX<索引名>

5.数据查询的一般格式

select [all|distinct] <表达式> [别名] [,<表达式> [别名]]...
from <表名|视图名> [,<表名|视图名>]...|(<select语句>) [as] <别名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc|desc]];

• 整个select语句的含义是：根据where子句的条件表达式从from子句指定的基本表、试图或派生表中找出满足条件的元组，再按select子句中的目标列表达选出元组中的属性值形成结果表
• 如果有group by子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。如果带having短语，则只有满足条件的组才予以输出
• 如果有order by子句，则结果表还要按<列名2>的值的升序或降序排列

6.单表查询—选择表中若干列

• 查询指定列

select <列名1>,<列名2>...
from <表名> 
全部列则select *

• 查询经过计算的值

  ​ select <表达式> <别名>,<列名2>...

  ​ from <表名>

  表达式如:100-<列名>

7.单表查询—选择表中若干元组

• 消除取值重复的行

  select distinct <列名1>

  from <表名>

• 查询满足条件的元组

  where子句常用的查询条件

查询条件	谓词
比较	=、>、<、>=、<>、！=、<、<=
确定范围	between…and 、 not between…and
确定集合	in、not in
字符匹配	like 、not like
空值	is null、is not null
多重条件（逻辑运算）	and 、or、not

• ​ 比较

  查询student表中sage<20的学生的名字

  select name
  from student
  where sage<20
  

• 确定范围

  查询student表中sage在18到20之间的学生的名字

select name
from student
where sage between 18 and 20

• 确定集合

  查询student表中sdept为cs或ma学生的名字

  select name
  from student
  where sdept in ('cs','ma')
  

• 字符匹配

  • <匹配串>中的通配字符

  ​ %，a%b表示以a开头，以b结尾的任意长度的字符串

  ​ _，a_b表示长度为3，以a开头，以b结尾的字符串

  • 如果用户要查询的字符串本身就含有通配字符串，则使用escape '<转码字符>'对通配符进行转义

  select name
  from student
  `where sno like 'a\_b' escape '\'
  

  表示匹配串中紧跟在“\"后面的字符”_“不会被转义

• 空值

  此处的is不能用=ti代替

  select name
  from student
  where sdept is not null
  

8.单表查询—order by 子句

​ desc—降序，asc—升序

​ 查询选修了3号课程的学生的姓名，按sno降序排列

select name
from student
where cno="3"
order by sno desc

9.单表查询—聚集函数

聚集函数名	参数类型	结果类型	描述
count	任意集合	一个数值	计算集合元素个数
sum	数值集合	一个数值	计算集合的和
avg	数值集合	一个平均值	计算集合的平均值
max	字符或数值集合	集合中的最大值	找出集合中最大值
min	字符或数值集合	集合中的最小值	找出集合中的最小值

10.单表查询—group by子句

• 将查询结果按某一列或多列的值分组
• having语句是筛选条件，作用于组

11.连接查询—等值与非等值连接查询

• 连接条件（连接谓词）：where子句中用来连接两个表的条件

  <表名1>.<列名1> <比较运算符> <表名2>.<列名2>

• 连接运算符为=时，称为等值连接，使用其他运算符时为非等值连接

• 自然连接：把目标列中重复的属性列去掉则为自然连接

12.连接查询—自身连接

一个表与自身连接

例：给course表取两个别名c1，c2

select c1.cno,select c2.cpon
from course c1,course c2
where c1.cpon = c2.con

13.连接查询—外连接

连接操作时，对于不满足条件的元组，进行保留

• 左外连接

  如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

  列出左边关系中的所有元组

  ...left outer join...on(条件表达式)
  可以用using去掉结果中重复值（代替on)
  

• 右外连接

  列出右边关系中的所有元组

• on和where和having（在使用left outer join时）

  • on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
  • where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉
  • having就是在计算后才起作用的
  • 执行顺序on>where>having

14.连接查询—多表连接

15.嵌套查询—带有 in 谓词的子查询

• 不相关子查询

​ 子查询的查询条件不依赖父查询

select sno,sname
from student
where sdept in
(
	select sdept
	from student
	where sname = "liuchen"
)
//第一个select为父查询，第二个为子查询

16.嵌套查询—带有比较运算符的子查询

• 相关子查询

  例：找出每个学生超过他自己选修课程评价成绩的课程号

select sno,cno
from sc x,
where grade>=(
	select avg(grade)
    from sc,y
    where y.sno=x.sno
)

17.嵌套查询—带有any(some)或all谓词的子查询

谓词	描述
>any	大于查询结果中的某个值
>all	大于查询结果中的所有值
。。。	。。。

可与聚集函数，in 等值转换

18.嵌套查询—带有exists谓词的子查询

带有exists的子查询不返回任何数据，只产生逻辑真值"true"或逻辑假值"false"

与之对应的是not exists

select sname
from student
where exists
(
	select *
    from sc
    where sno = student.sno and cno="1"
)

19.集合查询

• 集合操作：并操作union，交操作intersect、差操作except

• 参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同

  select *
  from student
  where sdept='cs'
  union
  select *
  from student
  where sage<=19
  

20.基于派生表的查询

​ 子查询不仅可以出现在where子句中，还可以出现在from子句中，这时子查询生成它的临时表

select sno
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

21.插入数据—插入元组

insert
into <表名> (属性列1,属性列2,...)
values (常量1,常量2);

insert
into student (sno,sname)
values ('520','臭臭');
//字符串常数用单引号（英文）括起来

22.插入数据—插入子查询结果

insert
into <表名> (属性列1,属性列2,...)
子查询;

23.修改数据

update <表名>
set <列名>=<表达式>...
where <条件>

24.删除数据

delete
from <表名>
where <条件>

25.空值处理

• 空值产生
  • 插入时取空值NULL
  • 未赋值的属性
  • 外连接产生
• 空值判断——is null 、is not null
• 空值的约束条件
  • 属性定义中有not null约束条件的不能取空值
  • 加了unique限制的属性
  • 码属性不能取空值
• 空值的算术运算、比较运算、逻辑运算
  • 算术运算，结果为空值
  • 比较运算，结果为unknown