我的SQL笔记&一些练习题（一）

Oracle 数据库使用的端口为：1521

在Windows平台下需保证 Oracle server xe 和 Oraclelistener 服务正常运行才可以连接到数据库

MySQL数据库使用的端口为：3306

连接数据库可以使用CML模式，或者使用Navicat，一个第三方数据库管理工具，支持连接Oracle SQL，MySQL，Windows SQL Server

---------------------------------

SQL语句的分类:

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

子句组成的查询块：

SELECT <字段名表>

FROM <表或视图名>

WHERE <查询条件>

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、

索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

                    表/视图/索引/同义词/簇

DDL操作是隐性提交的！不能rollback 

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制

数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1.GRANT：授权。

2. ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

    回滚---ROLLBACK

    回滚命令使数据库状态回到上次最后提交的状态。其格式为：

    SQL>ROLLBACK;

3.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常见的数据类型：

Varchar2(N)：长度可变，最小1个字符，最大4000字符

Char（N）：长度固定，最小1个字符，最大2000字符

Number（N， M）：数值类型，N的长度为1-38字符之间，M不超过N

DATE：日期类型

Long：字符串类型，最大可以存储2G可变长度字符类型（不常用）

Long raw：字符串类型，最大可以存储2G可变长度的二进制字符  (不常用 )

CLOB： 最大可以存储4G可变长度字符类型

BLOB：最大可以存储4G可变长度二进制字符

---------------------------------

Oracle对象命名规则

1：必须以字母开头，不能使用数字开头，长度在1-30位字符间

2：对象名可以出现的字符为字母（大小写都可），数字，下划线 _ $ #

3：同一个Oracle服务器用户所拥有的对象名不可以重复

4：名字不能是Oracle保留字（关键字）

5：名字中字母大小写不敏感

---------------------------------

建立数据库要遵循的三个范式

1NF：表中列不可分割

2NF：表中每行数据不可重复

3NF：当两张表有主外键关系，其中一张表不能出现另一张表的非主键列

---------------------------------

数据库事务ACID基本概念(原子性、一致性、隔离性、持久性)

1.原子性(Atomicity):事务要么成功(可见)，要么失败(不可见)。不存在事务部分成功的情况。

2.一致性(Consistency)：数据库在事务开始前和结束后都应该是一致的。

3.隔离性(Isolation):一个事务不会看到另外一个还未完成的事务产生的结果。每个事务就像在单独、隔离的环境下运行一样。

4.持久性(Durability)：成功提交的事务，数据是持久保留，不会因为系统失败而丢失。

---------------------------------

建立一个简单的Stuent表

CREATE TABLE student      --CREATE TABLE 创建表格
(
stuNo   VARCHAR2(10)      --学号字符串类型
,stuName VARCHAR2(30)     --学员姓名，字符串类型
,stuAge  NUMBER(3)        --学员年龄，整数类型，3位长度
,stuScore NUMBER(3, 1)    --学员成绩，浮点类型
);

/*为student表添加数据*/
INSERT INTO student           --插入数据到表中
VALUES('TX1001', 'TOM', 20, 99.5);

/* 查询student表中数据*/
SELECT stuNo, StuName, StuAge
FROM student;
SELECT *
FROM student;

/*删除student表*/
DROP TABLE student;

SQL关键字

AS：用于为字段取别名

DISTINCT：去除重复记录，用于查询语句

/*查询员工从事工作类型*/  
SELECT DISTINCT empDep As 部门
FROM emp;

/*  去除重复记录   */
SELECT DISTINCT eAge
FROM employee;

数据库数据约束

Not null ：数据非空

Unique：唯一约束

Primary key：主键约束（非空+唯一，一个表格只能最多有一个主键约束）

Foreign key：外键约束（通过此约束为两表建立关系）

Check：检查约束   例：CHECK(tAge >10 AND tAge <30)

Oracle比较运算符：

=等于，>大于,  <小于, >=大于等于，<=小于等于
<>不等于
Oracle其他关键字及运算符
AND OR NOT
BETWEEN ......AND            /* WHERE tAge BETWEEN '10' AND '30';数字类型 */
IN，   NOT IN           
IS NULL,   (IS NOT NULL)

示例：

/*查询参加工作时间在1997-7-9之后，不从事IT_PROG工作的员工信息*/
SELECT *
FROM emp
WHERE  hireDate > '09-9月-1997' AND empDep NOT IN('IT_PROG');

或者
SELECT *
FROM emp
WHERE  hireDate > '09-9月-1997'
AND empDep NOT IN('IT_PROG');

数据库中的模糊查询

LIKE(模糊查询需要加 % ),   NOT LIKE

小% --表示‘小‘开头的模糊匹配

%明 --表示’明‘结尾的模糊匹配

%小明% --表示字符内包含’小明‘即匹配

特殊字符：%（任意多个字符） _（代表一个字符）

排序查询：

ORDER BY…ASC（升序）默认情况，此关键字可不写

DESC（降序）排序                      ORDER BY tAge DESC;

ORDER BY 后面可出现字段。表达式和别名 ORDER BY tAge;

SELECT *
FROM emp
ORDER BY empId DESC;   --DESC降序排序

ALTER关键字，对表进行修改

ALTER TABLE emp ADD(列 字符格式);           (address VARCHAR(100));    --表示添加字段
ALTER TABLE emp MODIFY(列 字符格式);        (address VARCHAR(50));     --修改字段
ALTER TABLE emp DROP CLOUMN address;                                   --删除字段

RENAME 关键字

RENAME emp TO employee;    --把emp表修改为employee

TRUNCATE 关键字 删除表内数据

TRUNCATE TABLE employee   --删除表内所有数据

CONSTRAINT 约束关键字

用于建立约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 增加的约束类型 （列名）

/*主外键关系一对多*/
CREATE TABLE　student
(
       stuId      VARCHAR2(10) PRIMARY KEY      ----行级约束
      ,Name       VARCHAR2(30) NOT NULL
      ,age        VARCHAR2(3)  NOT NULL
      ,address    VARCHAR2(100)
      ,subId      VARCHAR2(10) NOT NULL
      /*创建外键将本表subId字段的取值引用（主表）至subject表的subId列*/
      ,CONSTRAINT fk_student_subId FOREIGN KEY(subid) REFERENCE subject(subId)   --表级约束
);
-------------------------

/*主外键关系多对多*/
CREATE TABLE　student
(
       stuId      VARCHAR2(10) PRIMARY KEY      ----行级约束
      ,Name       VARCHAR2(30) NOT NULL
      ,age        VARCHAR2(3)  NOT NULL
      ,address    VARCHAR2(100)
      /*创建外键将本表subId字段的取值引用（主表）至subject表的subId列*/
      ,CONSTRAINT fk_student_subId FOREIGN KEY(subid) REFERENCE subject(subId)   --表级约束
);
 
 
CREATE TABLE　subject
(
       subId VARCHAR2(10)        PRIMARY KEY
      ,name  VARCHAR2(30)        NOT NULL
      ,description VARCHAR2(100) NOT NULL
     
      ,CONSTRAINT fk_relation_subId FOREIGN KEY(stuid) REFERENCE student(stuId)
      ,CONSTRAINT fk_relation_subId FOREIGN KEY(subid) REFERENCE subject(subId)
);
CREATE TABLE relation
(
       rId VARCHAR2(10) PRIMARY KEY
      ,stuId VARCHAR2(10) NOT NULL
      ,subId VARCHAR2(10) NOT NULL     
);

------------------------
CREATE TABLE student          --CREATE TABLE 创建表格
(
stuNo   VARCHAR2(10)     --学号字符串类型
,stuName VARCHAR2(30)     --学员姓名，字符串类型
,stuAge  NUMBER(3)        --学员年龄，整数类型，3位长度
,stuScore NUMBER(3, 1)    --学员成绩，浮点类型
);

/*为student表添加数据*/
INSERT INTO student           --插入数据到表中
VALUES('TX1001', 'TOM', 20, 99.5);
INSERT INTO student
VALUES('TX1002', 'aa', 20, 99);
INSERT INTO student
VALUES('TX1003', 'bb', 20, 98);

/* 查询student表中数据*/
SELECT stuNo AS 学生编号, StuName AS 学生姓名, StuAge AS 学生年龄, stuScore / 10 AS 成绩
FROM student
WHERE stuNo = 'TX1001';

-------修改--------------
UPDATE student
SET stuScore = 8
WHERE stuNo = 'TX1001';

-------删除数据----------
DELETE FROM student
WHERE stuNo = 'TX1003';

/*查询全表数据*/
SELECT *
FROM student;

/*删除student表*/
DROP TABLE student;

/* 创建员工表 */9000
CREATE TABLE employee
(
eId     VARCHAR2(10)
,eName   VARCHAR2(30)
,eAge    NUMBER(2)
,eSalary NUMBER(7, 2)
);

/* 为员工表添加数据 */
INSERT INTO employee
VALUES('EMP1001', 'Tom', 25, 5000.00);
INSERT INTO employee
VALUES('EMP1002', 'Jim', 25, 6000.00);
INSERT INTO employee
VALUES('EMP1003', 'Rose', 30, 8000.00);

/* 全表查询 */
SELECT *
FROM employee;

/* 查询列为表达式，并为列取别名 */
SELECT eName AS 姓名, eAge 年龄, eSalary * 12 年薪
FROM employee
WHERE eId = 'EMP1001';

/* 去除重复记录 */
SELECT DISTINCT eAge
FROM employee;

/* 修改数据 */
UPDATE employee
SET eSalary = 9000.00, eAge = 28
WHERE eId = 'EMP1003';

/* 删除记录 */
DELETE FROM employee
WHERE eId = 'EMP1003';

/* 创建表，使用约束 */
CREATE TABLE teacher
(
tId   VARCHAR2(10) PRIMARY KEY
,tName VARCHAR2(30) UNIQUE                         NOT NULL
,tAge  NUMBER(2)    CHECK(tAge >= 22 AND tAge <= 65) NOT NULL -- CHECK(tAge BETEEN 22 AND 65) - tAge <> 30
);

INSERT INTO teacher
VALUES('TEA1001', 'Tom', 25);
INSERT INTO teacher
VALUES('TEA1002', 'Tim', 20);

/* IN关键字 */
SELECT eId, eName, eSalary
FROM employee
WHERE deptId NOT IN('DEPT1001', 'DEPT1002');

CREATE TABLE emp
(
eId    VARCHAR2(10) PRIMARY KEY
,eName  VARCHAR2(30) NOT NULL
,eMail VARCHAR2(30)
);
INSERT INTO emp
VALUES('emp1001', 'Tom', 'tom@126.com');
INSERT INTO emp(eId, eName)
VALUES('emp1002', 'Tim');
/*查询emp表中eMail非空的列*/
SELECT *
FROM emp
WHERE eMail IS NOT NULL;

CREATE TABLE stu
(
sId      VARCHAR2(10) PRIMARY KEY
,sName    VARCHAR2(30) NOT NULL
,sAddress VARCHAR2(30) NOT NULL
);
INSERT INTO stu
VALUES('stu1001', 'Tom', '天津市河西区');
INSERT INTO stu
VALUES('stu1002', 'Tim', '北京市丰台区');

/* 模糊查询 */
SELECT *
FROM stu
WHERE sAddress NOT LIKE '%天津%';

DROP TABLE stu;

CREATE TABLE stu
(
sId    VARCHAR2(10) PRIMARY KEY
,sName  VARCHAR2(30) NOT NULL
,sScore NUMBER(3)    NOT NULL
);
INSERT INTO stu
VALUES('stu1001', 'Tom', 95);
INSERT INTO stu
VALUES('stu1002', 'Tim', 98);
INSERT INTO stu
VALUES('stu1003', 'Jim', 58);
SELECT *
FROM stu
ORDER BY sScore ASC;
SELECT *
FROM stu
ORDER BY sScore DESC;
SELECT sName, sScore + 5 AS Score
FROM stu
ORDER BY Score DESC;
DROP TABLE student;

/* 主外键关系，一对多 */
CREATE TABLE student
(
stuId   VARCHAR2(10) PRIMARY KEY
,name    VARCHAR2(30) NOT NULL
,age     NUMBER(3)    NOT NULL
,address VARCHAR2(100)
,subId   VARCHAR2(10) NOT NULL
--创建外键，本表subId字段的取值应取自subject表subId列的值
,CONSTRAINT fk_student_subId FOREIGN KEY(subId) REFERENCES subject(subId)
);
CREATE TABLE subject
(
subId       VARCHAR2(10)  PRIMARY KEY
,name        VARCHAR2(30)  NOT NULL
,description VARCHAR2(100) NOT NULL
);
INSERT INTO subject
VALUES('1001', 'JAVA', 'DESC');
INSERT INTO subject
VALUES('1002', 'ANDROID', 'DESC');
INSERT INTO subject
VALUES('1003', 'PHP', 'DESC');
INSERT INTO student
VALUES('101', 'Tom', 18, 'Tianjin', '1001');
INSERT INTO student
VALUES('102', 'Tim', 19, 'Beijing', '1002');
SELECT *
FROM student;
DROP TABLE subject;

/* 主外键关系，多对多 */
CREATE TABLE student
(
stuId   VARCHAR2(10) PRIMARY KEY
,name    VARCHAR2(30) NOT NULL
,age     NUMBER(3)    NOT NULL
,address VARCHAR2(100)
);
CREATE TABLE subject
(
subId       VARCHAR2(10)  PRIMARY KEY
,name        VARCHAR2(30)  NOT NULL
,description VARCHAR2(100) NOT NULL
);
CREATE TABLE relation
(
rId VARCHAR2(10) PRIMARY KEY
,stuId VARCHAR2(10) NOT NULL
,subId VARCHAR2(10) NOT NULL
,CONSTRAINT fk_relation_stuId FOREIGN KEY(stuId) REFERENCES student(stuId)
,CONSTRAINT fk_relation_subId FOREIGN KEY(subId) REFERENCES subject(subId)
);

转载于:https://blog.51cto.com/mikeyoo/2132086