关系数据库标准语言SQL数据类型及数据操作介绍

一般分为关系型数据和nosql数据库， 关系型数据库一般使sql操作，sql对大小写不敏感。

sql的数据类型

sql支持许多内置的数据类型，并且允许用户定义新的域（数据）类型。

char(n)：定长字符串，长度n由用户指定。省略（n）时，长度为1。全称为character。

varchar(n)：变长字符串，最大长度n由用户指定。全称是character varying。定长和变长的区别主要表现在前者需要固定长度的空间，二后者占用的空间在最大长度范围内是可以改变的。

bit(n)：定长二进位串，长度n由用户指定。省略n时，长度为1。

bit varying(n)：变长二进位串，最大长度n由用户指定。

int：整数，其值域依赖于具体的实现，全称是integer。

smallint：小整数，其值域依赖于具体的实现，但是小于int的值域。

numeric(p, d)：p位有效数字的定点数，其中小数点右边占d位。

dec(p, d)：p位有效数字的定点数，其中小数点右边占d位。全称是decimal。

float(n)：精度至少为n位数字的浮点数，其值域依赖于实现。

real：实数，其值域依赖于实现。

double precision：双精度实数，精度依赖于实现，但精度比real高。

date：日期，包括年、月、日，格式为yyyy-mm-dd。

time：时间，包括时、分、秒，格式为hh：mm：ss。time(n)可以表示比秒更小的单位，秒后取n位

timestamp：时间戳，是data和time的结合体，包括年、月、日、时、分、秒。timestamp(n)可以表示比秒更小的单位，秒后取n位。

interval：时间间隔。sql允许对date、time和interval类型的值进行运算。例如，如果x和y都是date类型，则x-y为interval类型，其值为x和y之间的天数。在date或time类型的值上加减一个interval类型的值得到新的date或time类型的值。

数据库操作：

查看当前数据库服务器有哪些数据库： show databases; 使用当前数据库服务器下的某一个数据库：use 数据库名称; 创建数据库：create database 数据库名称； 删除数据库：drop database 数据库名称

数据表操作

创建数据表:

create table <表名> (<列定义>...<列定义>   [<表约束定义>...<表约束定义>]);

<表名>：标识符，是对定义的基本表命名

<列定义>：定义每个属性（列）的名称、类型、缺省值和列上的约束条件，格式是：
<列名> <类型> [default<缺省值>] <列约束定义>,...,<列约束定义>

列约束定义格式：
[constraint <约束名>] <列约束>
其中可选短语“constraint <约束名>”为列约束的命名。常用的列约束包括：
1）not null：不允许该列取空值，不加此限制时，该列可以取空值。
2）primary key：知名该列时主码（主键）,其值非空、唯一。
3）unique：该列上的值必须唯一。这相当于说明该列为候选码。
4）check(<条件>)：指明该列的值必须满足的条件，其中<条件>是一个涉及该列的布尔表达式。

表约束定义格式：
[constraint <约束名>] <表约束>
其中可选短语“constraint <约束名>”为表约束的命名。常用的表约束包括：
1）primary key(a1, a2,...ak)：说明属性列a1...ak构成该表的主码（主键）。当主码只包含一个属性时，也可以用列定义约束定义主码。
2）unique(a1, a2,...ak)：说明这些属性列上的值必须唯一，这相当于说明a1，...，ak构成该表的候选码。当候选码只包含一个属性时，也可以用列约束定义候选码。
3）check(a1,...，ak)：说明该表上的一个完整性约束条件。通常，<条件>是一个涉及到该表一个或多个列的布尔表达式。

外码比较复杂，它具有如下形式：
foreign key(a1,...,ak) references <外表名> (<外表主码>)
[<参照触发动作>]
它说明属性时a1,...,ak是表的外码，<外表名>给出被参照关系的表名，<外表主码>给出被参照关系的主码，而<参照触发动作>说明违反参照完整性时需要采取的措施。

eg：创建教师表teachers
creat table teachers
(
tno      char(7) not null primary key,
tname    char(10) not null,
sex      char(2) check(sex='男' or sex='女'),
birthday date,
title    char(6),
dno      char(4),
foreign key (dno) references departments (dno)
);

这里，我们定义tno为teachers的主码，tname不能为空，而用check短语限定sex的值只能是“男”或“女”。最后一行定义dno为关系teachers的外码，它参照departments的主码dno。


eg：创建选课表sc
create table sc
(
sno     char(9),
cno     char(5),
grade   smallint check(grade>=0 and grade<=100),
primary key(sno, cno),
foreign key(sno) references students (sno),
foreign key(cno) references courses (cno)
);

修改数据表

(1)向数据表中添加新列
alter table <表名> add [column] <列定义>
例：向courses中增加一个新列pno，表示课程的先行课程号
alter table courses add pno char(5);

(2)对于已经存在的列，只允许修改或删除列的缺省值，语句形式为
alter table <表名> alter [column] <列名> {set default <缺省值> | drop default}
例：在students的sex列设置缺省值“女”可以减少大约一半学生性别的输入。可以使用如下语句：
alter table students alter sex set default '女';
而删除sex上的缺省值可以用：
alter table students alter sex drop default;

(3)删除已经存在的列
alter table <表名> drop [column] <列名> {cascade | restrict}
其中cascade表示级联，删除将成功，并且依赖于该列的数据库对象（如涉及到该列的视图）也一并删除。restrict表示受限，仅当没有依赖于该列的数据库对象时删除才成功。
例：删除courses中的pno列可以用：
alter table courses drop pno;

(4)添加表约束
alter table <表名> add <表约束定义>
其中表约束定义与创建基本表相同。

(5)删除表约束
alter table <表名> drop constraint <约束名> {cascade | restrict}
其中被删除的约束名一定是命名的约束，给出约束名。cascade导致删除约束并且同时删除依赖于该约束的数据库对象。而restrict仅当不存在依赖于该约束的数据库对象才可以删除该约束。

删除基本表

当不需要某个基本表时，可以将其删除。语句格式为：
drop table <表名> {cascade | restrict}
其中cascade表示级联删除，依赖于表的数据对象（最常见的是视图）也将一同删除。restrict表示受限删除，如果基于该表定义有视图或者有其他表引用该表（如check，foreign key等约束），或者该表有触发器。存储过程或函数等，则不能删除。
删除基本表将导致存放在表中的数据和表定义都将被彻底删除。

例：
drop table student restrict;

添加

insert语句有两种使用形式，一种是向基本表中插入单个元组；另一种是将查询的结果（多个元组）插入基本表。

1）插入单个元组
语句格式为：
insert into t[(a1, .... ，ak)] values (c1,....,ck);
其中t通常是基本表，也可以是视图，ai是t的属性，ci是常量。
(a1, .... ，ak)缺省时，values子句必须按基本表属性的定义次序提供新元素每个属性上的值。否则，(a1, .... ，ak)中属性的次序可以是任意次序，并且可以仅列举基本表的部分属性。此时，value子句中的常量与属性个数是相等的，并且常量对应相对位置上的属性，新元组取缺省值（如果定义了缺省值的话）或空值为null。

eg.

insert 表名 (栏位名称) values (栏位值)

insert stu_info (stu_no, stu_name, gender, birthday)
values('20171217123', '张三', 1, '2017‐12‐14');

insert stu_info (stu_no, stu_name, birthday)
values ('20171217123', '张三', '2017‐12‐14');

2）插入查询结果

语句格式为
insert into t[(a1, .... ，ak)] <查询表达式>
其中查询表达式通常是一个select语句。该表达式对查询表达式求值，并将结果元组插入到基本表中。

设存放就餐卡登记信息关系cardinf具有如下模式：
cardinf(card-no, name, banlance)
其中属性分别是持卡人编号，name为持卡人姓名，balance为卡中余额。假设现为教师办理一个就餐卡，直接使用教师号作为持卡人编号，并预存100元。
insert into cardinf(card-no, name, banlance)
select tno, tname, 100.00
from teachers;

注意：常量100.00出现在select子句中。这使得查询结果的每个元组的第三列均取常量值100.00。

查询

模糊查询

like表达式允许我们表示模糊查询，一般格式是：
<匹配值> [not] like <模式> [escape'<换码字符>']

其中<匹配值>和<模式>都是字符串表达式，它们的值是可以比较的。通常，<匹配值>是属性，<模式>是给定的字符串常量。<模式>中允许使用通配符。有两种通配符：“_”（下划线）可以与任意单个字符匹配，而"%"可以与零个或多个任意字符匹配。escape'<换码字符>'通常是escape'\'。它定义‘\’为转义字符，将紧随其后的一个字符转义。如果<模式>中的_或%紧跟在\之后，则这个_或%就失去了通配符的意义，而取其字面意义。
当<匹配值>与<模式>匹配时，则like表达式的值为真，否则，值为假。

eg.

select sno, sname
from student
where sname like '李__';   /*有两个下划线，因为一个汉字占两个字符位置*/

select *
from courses
where cname like 'c\_%' escape '\';        /* 定义"\"为转义字符，“_" 被转义 */

null 查询

sql允许元组在某些属性上取空值（null）。空值代表未知的值，不能与其他值进行比较。null表达式允许我们判断给定的值是否为空值。null表达式常见的形式如下：

<值表达式> | <子查询> is [not] null
通常<值表达式>是属性。

eg.

select sno, cno
from sc
where grade is null;

删除

使用delete语句删除表中的某些记录，语句格式为：
delete from t [where <删除条件>]

其中t通常为基本表，但也可以是某些视图，<删除条件>与select语句中的查询条件类似。
delete语句的功能是从指定的表t中删除满足<删除条件>的所有元组。where子句缺省时，则删除表t中全部元组（剩下一个空表t）。

删除所有学生的记录：
delete from students;

删除学号为200624010的学生记录可以用：
delete from students where sno='200624010';

尽管delete语句只能从一个表删除元组，但是删除条件可以涉及到多个表
例如删除计算机系所有学生的选课记录（sc是学生选课关系表）
我们可以先得到计算机系所有学生的学号
delete from sc
where sno in
(
select sno
from students
where speciality='计算机');

修改

使用update语句可以修改表中某些元组指定属性上的值。格式为：
update t
set a1=e1,...,ak=ek
[where <修改条件>]

其中t通常为基本表，但也可以是某些视图；a1,...ak是t的属性，而e1,...ek是表达式；<修改条件>与select语句中的查询条件类似
该语句的功能是：修改表t满足<修改条件>的元组。更具体的说，对于表t中没个满足<修改条件>的元组t，求表达式ei的值，并将它赋予元组t的属性ai。where子句缺省时，则修改表t中全部元组。

update语句只能修改一个表的元组。但是，修改条件中可以包含涉及其他表的子查询。
例如：将微积分课程成绩低于60分的所有学生的微积分课程成绩提高5分。
先得到微积分课程的课程号，再修改微积分成绩
update sc
set grade=grade+5
where grade < 60 and cno in
(select cno
from courses
where cname='微积分'
);

sql函数

查看表的数据条数

格式：select count(参数) from 表名;
参数可以是栏位（列）的序列号，从零开始，也可以是栏位的名称。最好选择主键作为参数，查询速度快。

eg.
select count(0) from 表名;
select count(栏位名) from 表名;

求和函数

sum(栏位名)

查询最大值 查询最小值 查询并去除重复值 查询并使用逻辑运算 数据排序
order by 栏位名 asc； (升序（default）,默认情况可以不写asc)
order by 栏位名 desc; (降序)