预备知识-SQL基础
0.1数据类型
关系型数据库中,SQL语句主要包括四种即DDL、DML、DQL、DCL
DDL(Data Definition Language) 数据定义语言
DML(Data Manipulation Language) 数据操纵语言
DQL(Data Query Language) 数据查询语言
DCL(Data Control Language) 数据控制语言
1. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE
子句组成的查询块:
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
2 .数据操纵语言DML
数据操纵语言DML主要有三种形式:
插入:INSERT
更新:UPDATE
删除:DELETE
3.数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如:
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇
DDL操作是隐性提交的!不能rollback
4 .数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制
数据库操纵事务发生的时间及效果,对数据库实行监视等。如:
GRANT:授权
回滚—ROLLBACK
ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点。
回滚命令使数据库状态回到上次最后提交的状态。其格式为:
SQL>ROLLBACK;
COMMIT [WORK]:提交
在数据库的插入、删除和修改操作时,只有当事务在提交到数据库时才算完成。在事务提交前,只有操作数据库的这个人才能有权看到所做的事情,别人只有在最后提交完成后才可以看到。
提交数据有三种类型:显式提交、隐式提交及自动提交。下面分别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为:
SQL>COMMIT;
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是:
ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,
EXIT,GRANT,NOAUDIT,QUIT,REVOKE,RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON,则在插入、修改、删除语句执行后,
系统将自动进行提交,这就是自动提交。其格式为:
SQL>SET AUTOCOMMIT ON;
一、以Oracle为例,学习SQL
在Oracle开发中,客户端把SQL语句发送给服务器,服务器对SQL语句进行编译、执行,把执行的结果返回给客户端。Oracle SQL语句由如下命令组成:
Ø 数据定义语言(DDL),包括CREATE(创建)命令、ALTER(修改)命令、DROP(删除)命令等。
Ø 数据操纵语言(DML),包括INSERT(插入)命令、UPDATE(更新)命令、DELETE(删除)命令、SELECT … FOR UPDATE(查询)等。
Ø 数据查询语言(DQL),包括基本查询语句、Order By子句、Group By子句等。
Ø 事务控制语言(TCL),包括COMMIT(提交)命令、SAVEPOINT(保存点)命令、ROLLBACK(回滚)命令。
Ø 数据控制语言(DCL),GRANT(授权)命令、REVOKE(撤销)命令。
目前主流的数据库产品(比如:SQL Server、Oracle)都支持标准的SQL语句。数据定义语言,表的增删改操作,数据的简单查询,事务的提交和回滚,权限的授权和撤销等,Oracle与SQL Server在操作上基本一致。
二、Oracle的数据类型
Oracle数据的核心是表,表中的列使用到的常用数据如下:
类型 | 含义 |
CHAR(length) | 存储固定长度的字符串。参数length指定了长度,如果存储的字符串长度小于length,用空格填充。默认长度是1,最长不超过2000字节。 |
VARCHAR2(length) | 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1,最长不超过4000字符。 |
NUMBER(p,s) | 既可以存储浮点数,也可以存储整数,p表示数字的最大位数(如果是小数包括整数部分和小数部分和小数点,p默认是38为),s是指小数位数。 |
DATE | 存储日期和时间,存储纪元、4位年、月、日、时、分、秒,存储时间从公元前4712年1月1日到公元后4712年12月31日。 |
TIMESTAMP | 不但存储日期的年月日,时分秒,以及秒后6位,同时包含时区。 |
CLOB | 存储大的文本,比如存储非结构化的XML文档 |
BLOB | 存储二进制对象,如图形、视频、声音等。 |
NUMBER类型的示例:
格式 | 输入的数字 | 实际的存储 |
NUMBER | 1234.567 | 1234.567 |
NUMBER(6,2) | 123.4567 | 123.46 |
NUMBER(4,2) | 12345.67 | 输入的数字超过了所指定的精度,数据库不能存储 |
对于日期类型,可以使用sysdate内置函数可以获取当前的系统日期和时间,返回DATE类型,用systimestamp函数可以返回当前日期、时间和时区。
如:SELECT sysdate systimestamp from DUAL
返回系统日期和时间、日期、时间和时区
注意:
Oracle的查询中,必须使用“select 列… from 表”的完整语法,当查询单行函数的时候,from后面使用DUAL表,dual表在系统中只有一行一列,该表在输出单行函数时为了select…from的语法完整性而使用。
三、创建表和约束
3.1创建表
Oracle创建表同SQL Server一样,使用CREATE TABLE命令来完成。
3.2创建约束
创建约束使用如下命令:
语法格式:ALTER TABLE命令
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。 |
不论创建表还是约束,与SQL Server基本相同,注意:在Oracle中default是一个值,而SQL Server中default是一个约束,因此Oracle的default设置可以在建表的时候创建。
案例1:创建一个学生信息(INFOS)表和约束
代码:Oracle创建表和约束
CREATE TABLE INFOS
(
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, --姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座号
ENROLLDATE DATE, --入学时间
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
)
/ ①
ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ②
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER
CHECK(GENDER = '男' OR GENDER = '女') ③
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT
CHECK(SEAT >=0 AND SEAT <=50) ④
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE
CHECK(AGE >=0 AND AGE<=100) ⑤
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO
CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR
(CLASSNO >='2001' AND CLASSNO<='2999')) ⑥
/
ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ⑦
/
代码解析:
① 在Oracle代码中,“/”执行缓存区中的语句,由于缓冲区中只存储一条刚刚保存过语句,由于每条语句没有用分号结尾,只是保存在缓冲区,因此每条语句后面都有单独一行“/”。
② 创建一个主键约束。
③ 与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束,表示该列值是唯一的,列中的值不能重复。
Oracle中创建外键约束与SQL Server相同。
案例2:创建一个成绩表(SCORES)表和约束
代码演示:Oracle创建表和约束
CREATE TABLE SCORES
(
ID NUMBER , --ID ①
TERM VARCHAR2(2), --学期 S1或S2
STUID VARCHAR2(7) NOT NULL, --学号
EXAMNO VARCHAR2(7) NOT NULL, --考号 E+班号+序号
WRITTENSCORE NUMBER(4,1) NOT NULL, --笔试成绩
LABSCORE NUMBER(4,1) NOT NULL --机试成绩
)
ALTER TABLE SCORES
ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')
/
ALTER TABLE SCORES
ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ②
/
代码解析:
① SQL Server中可以使用identify创建自动增长列,但是Oracle中的自动增长需要借助序列(Sequence)完成,在后面讲解。
② Oracle中的外键约束定义。
四、DML数据操纵语言
数据操纵语言(DML)用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。
4.1简单查询
数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是:
SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名
语法解析:
1. *表示表中的所有列。
2. 列名可以选择若干个表中的列名,各个列表中间用逗号分隔。
3. 表达式可以是列名、函数、常数等组成的表达式。
4. WHERE子句是查询的条件。
5. ORDER BY 要求在查询的结果中排序,默认是升序。
注:Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。
语法结构:根据结果集创建表
CREATE TABLE 表名 AS SELECT语句 |
代码:根据结果集创建表
SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS; TABLE CREATED |
使用上面命令创建的新表中,不存在任何约束,并且把查询的数据一起插入到新表中。如果只复制表结构,只需使查询的条件不成立(比如where 1=2),就不会查询从出任何数据,从而复制一个表结构。
代码演示:复制表结构
SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2; TABLE CREATED |
4.2数据插入
使用INSERT命令完成对数据的插入
语法结构:根据结果集创建表
INSERT INTO 表名(列名1,列名2……) VALUES (值1,值2……)
语法解析:
- 列名可以省略。当省略列名时,默认是表中的所有列名,列名顺序为表定义中列的先后顺序。
- 值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
代码演示:向INFOS表和SCORES表中插入数据
SQL> INSERT INTO INFOS VALUES ( ①
2 's100102', '林冲', '男', 22, 2,
3 TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '), ②
4 '西安', '1001'
5 )
6 /
1 row inserted
SQL> INSERT INTO INFOS VALUES (
's100104','阮小二','男',26,3,SYSDATE,default,'1001'); ③
1 row inserted
SQL>COMMIT; ④
代码解析:
① 表名后面缺省了列名,默认是表Infos中的所有列名,values中的值要与表中列一一对应,包括顺序和数据类型的对应。在SQL*Plus中一条语句可以写在多行,那么从第二行开始,sqlplus会为每一行前面给出行号。
② 在Oracle中,日期是国际化的,不同的区域安装的数据库,默认的日期格式不同,因此为了程序便于移植,日期的输入要使用TO_DATE函数对日期格式化后输入,采用格式化字符串对日期进行格式化时,格式化字符串中字符不区分大小写,常见的格式化字符如下:
1. yyyy表示四位年份
2. mm表示两位月份,比如3月表示为03
3. dd表示两位日期
4. hh24表示小时从0-23,hh12也表示小时从0-11。
5. mi 表示分钟
6. ss表示秒
③ 在遇到存在默认值的列时,可以使用default值代替。
④ commit是把用户操作(添加、删除、修改操作)提交,只有提交操作后,数据才能真正更新到表中,否则其他用户无法查询到当前用户操作的结果。
在Oracle中,一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是:INSERT INTO 表 SELECT子句,如下示例:
代码演示:INSERT向表中插入一个结果集
SQL> INSERT INTO INFOS2 SELECT * FROM INFOS; 5 rows inserted |