数据库：基础SQL知识+SQL实验1

（1）基础知识：

1.创建数据库：

CREATE DATABASE <database_name>

2.删除数据库：

DROP DATABASE <database_name>

3.相关数据类型：

[1] 字符串类型

• CHAR(n)：固定长度的字符数据，长度为 n 个字符。最大长度为 2000 字节。
• VARCHAR2(n)：可变长度的字符数据，最大长度为 4000 字节。
• LONG：可变长度的字符数据，最多可以存储 4GB。每个表最多允许一个 LONG 类型字段。

[2] 数值类型

• NUMBER(p,q)：通用的数值数据类型，具有精度为 p 位，小数位数为 q 位。
• DECIMAL(n,m)：具有 n 位总位数，其中小数点后有 m 位的浮点数。要求 n 大于或等于 m，且 m 大于等于 0。
• INTEGER(p)：有符号整数，宽度为 p 位。
• FLOAT(p)：采用科学计数法表示的浮点数，具有 p 位的二进制精度。
• REAL：单精度浮点数，取值范围为 -3.402E+38 到 -1.175E-37 或 1.175E-37 到 -3.402E+38 或 0。

[3] 日期/时间类型

• DATE：固定长度的日期/时间数据，以 dd-mm-yy 格式表示。
• TIME：以 HH.MM.SS 格式表示的时间。
• TIMESTAMP：以 DD-MM-YY-HH.MM.SS.000000 格式表示的时间戳。格式为日期-月份-年份-小时-分钟-秒-微秒（微秒为小数秒的六位精度）。

4.Types of Constraints约束类型：

• PRIMARY KEY（主键）： 强制唯一标识表中的每一行。主键确保表中的每个记录都具有唯一的标识符，且不允许为主键列的值为空。

• UNIQUE（唯一性约束）： 防止列中的数据重复。唯一性约束确保表中的每个记录在特定列中都有唯一的值，但与主键不同，唯一性约束允许列中的值为空。

• FOREIGN KEY（外键）： 强制表之间的父子关系。外键约束确保某一表中的值在另一表中具有匹配的值，形成父子关系，通常用于关联两个表。

• NOT NULL（非空约束）： 防止在列中存储空值（NULL）。非空约束确保表中的特定列不包含空值，即所有记录都必须具有该列的非空值。

• CHECK（检查约束）： 验证列中的值是否符合指定的条件。检查约束用于确保列中的数据满足特定的条件，例如范围、正则表达式等。

• DEFAULT（默认值）

5.Constraints States数据约束状态：

• ENABLE / DISABLE（启用 / 禁用）：

  • ENABLE（启用）： 表示启用约束，即在执行数据操作时将强制执行约束规则。
  • DISABLE（禁用）： 表示禁用约束，即在执行数据操作时不会强制执行约束规则。禁用约束可用于在大规模数据操作时提高性能。

• VALIDATE / NOVALIDATE（验证 / 不验证）：

  • VALIDATE（验证）： 表示启用约束时对现有数据进行验证，确保它们满足约束条件。如果不满足条件，则无法启用约束。
  • NOVALIDATE（不验证）： 表示启用约束时不对现有数据进行验证。这允许启用约束，即使现有数据不满足约束条件，但在以后的数据插入时，仍然会强制执行约束规则。

• INITIALLY IMMEDIATE / INITIALLY DEFERRED（初始立即 / 初始延迟）：

  • INITIALLY IMMEDIATE（初始立即）： 表示在事务开始时立即应用约束规则。
  • INITIALLY DEFERRED（初始延迟）： 表示在事务开始时不应用约束规则，直到事务结束时才进行检查和强制执行。

• DEFERRABLE / NOT DEFERRABLE（可延迟 / 不可延迟）：

  • DEFERRABLE（可延迟）： 表示在事务期间可以延迟对约束的检查和强制执行，直到事务结束时才应用。通常与 INITIALLY DEFERRED 一起使用。
  • NOT DEFERRABLE（不可延迟）： 表示约束将立即在事务中强制执行，不允许延迟检查。

6.三大操作：CREATE、ALTER、DROP

[1]CREATE创建表格：

?CREATE TABLE <table name> ( ? ? ? ?

<attribute name 1> <data type 1> <Width> <Constraint>, ? ? ?

? ... ? ? ? ?

<attribute name n> <data type n> > <Width> <Constraint>);

例子：

CREATE TABLE student ?? ?(

SSN?? ?NUMBER(3)?? ?PRIMARY KEY ? ? ?CHECK (SSN >=111 and SSN <=999), ?? ?

SNAME?? ??? ?CHAR(10) NOT NULL , ??

?BirthDate ?? ?DATE , ??

?DEPTNO ?? ?NUMBER CHECK DEPTNO IN (10, 20, 30, 40) ）;

[2]ALTER修改表格：

1) Add, drop, modify table columns 添加、删除、修改列

• ALTER TABLE <tablename>? ADD (column_name datatype);?
• ALTER TABLE <table_name> DROP COLUMN?(column_name);
• ALTER TABLE <table_name> MODIFY (column_name datatype);

2) Add and drop constraints 添加、删除、修改约束

• ALTER TABLE <tablename> MODIFY (<columnname> NULL|NOT NULL);
• ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> ? PRIMARY KEY (<colname>, ...) | ? FOREIGN KEY (<colname>, ...) ? ? ? REFERENCES <schema>.<tablename> (<colname>, ...) | ? UNIQUE (<colname>, ...) | ? CHECK (<colname>, ...) (<check_list>);
• ALTER TABLE <tablename> RENAME CONSTRAINT <oldname> TO <newname>| MODIFY CONSTRAINT <constraintname> ? <constraint_state> <constraint_state> ...;
• ALTER TABLE <tablename>?DROP?CONSTRAINT <constraintname>;
• When a PRIMARY KEY constraint is created (and not disabled), a unique index is created to help enforce the constraint.

3) Enable and Disable constraints 启用、禁用约束

• ALTER TABLE <tablename>? DISABLE?CONSTRAINT <constraint_name>;
• ALTER TABLE <tablename>? ENABLE CONSTRAINT <constraint_name>;
• ALTER TABLE CUSTOMER ENABLE CONSTRAINT CUST_UNQ EXCEPTIONS TO BADCUSTOMERS USING CUST_UNQ_INDEX;（Changing a constraint's state改变约束状态）

1. ALTER TABLE CUSTOMER： 表示对名为 "CUSTOMER" 的表进行修改。
2. ENABLE CONSTRAINT CUST_UNQ： 启用名为 "CUST_UNQ" 的约束。这表示在之后的数据操作中，将会强制执行 "CUST_UNQ" 约束。
3. EXCEPTIONS TO BADCUSTOMERS： 这部分指定了一个异常表，名为 "BADCUSTOMERS"。如果启用约束时发现数据不符合 "CUST_UNQ" 约束，就将违反约束的行插入到 "BADCUSTOMERS" 表中。
4. USING CUST_UNQ_INDEX： 表示在启用约束时使用名为 "CUST_UNQ_INDEX" 的索引。

[3]DROP删除表格：

DROP TABLE <tablename>

7.VIEWS视图

[1]创建视图：

CREATE VIEW view_name

AS SELECT column_list FROM table_name [WHERE condition];

[2]修改视图：

ALTER VIEW view_name [(column_list)] [WITH ENCRYPTION]

AS select_statement [WITH CHECK OPTION]

例子：A one more column by the name of Address of student table.

ALTER VIEW view_student

AS SELECT sno, sname,Address FROM student;

[3]删除视图：

DROP VIEW view_name;

8.INDEX索引：

CREATE INDEX index_name ?ON table_name (column_name)

例子：

CREATE INDEX NAME_IDX ON CUSTOMER_T(CUSTOMER_NAME)

This makes an index for the CUSTOMER_NAME field of the CUSTOMER_T table.

9.插入、删除与更新

[1]INSERT（Adds data to a table）

• Inserting a record with all fields：（所有列都有值）
• INSERT INTO CUSTOMER_T VALUES (001, ‘Contemporary Casuals’, 1355 S. Himes Blvd.’,? ? ? ‘Gainesville’, ‘FL’, 32601);
• Inserting a record with specified fields：（部分列有值）
• INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION,? ? PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);
• Inserting records from another table：（从另一张表的数据插入使用SELECT语句）
• INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE STATE = ‘CA’;

[2]DELETE（Removes rows from a table）

• Delete certain rows：
• DELETE FROM CUSTOMER_T WHERE STATE = ‘HI’;
• Delete all rows
• DELETE FROM CUSTOMER_T;

[3]UPDATE

• Modifies data in existing rows:
• UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7;

10.查询操作

1. SELECT 子句： 列举了要返回的列。它定义了查询的输出，指定了结果集中包含哪些列。

2. FROM 子句： 指示了从哪个表（或表的组合）中检索数据。它定义了查询的数据源。

3. WHERE 子句： 指定了哪些行符合特定的条件。它用于筛选在 FROM 子句中指定的表中的数据，以便只返回满足条件的行。

4. GROUP BY 子句： 将具有相同属性的行分组，以便可以对每个组应用聚合函数。WHERE 子句在 GROUP BY 子句之前应用，用于过滤数据。

5. HAVING 子句： 在 GROUP BY 子句定义的组中进行选择。由于它作用于 GROUP BY 子句的结果，允许在 HAVING 子句的谓词中使用聚合函数。

6. ORDER BY 子句： 指定返回的行的顺序。如果没有 ORDER BY 子句，SQL 查询返回的行的顺序是不确定的。

11.其它要点

（1）符号表述

（2）% ：可多个字符， ?_：?一个字符。

例子：在a和c中有至少一个字符：

SELECT SNAME FROM STUDENT WHERE SNAME LIKE 'a_%c';

（2）实验内容：

1 List all information about the employees.

???????SELECT * FROM EMP;

2 List all information about the departments

??????SELECT * FROM DEPT;

3 List only the following information from the EMP table ( Employee name, employee number, salary, department number)

? ? ? SELECT ENAME,EMPNO,SAL,DEPTNO FROM EMP;

4 List details of employees in departments 10 and 30.

????SELECT * FROM DEPT WHERE DEPTNO=10 OR DEPTNO=30;

5 List all the jobs in the EMP table eliminating duplicates.

?????SELECT DISTINCT JOB FROM EMP；

（使用distinct去除重复的数据）

6. What are the names of the employees who earn less than ￡20,000?

?????SELECT ENAME,SAL FROM EMP WHERE SAL<20000;

7. What is the name, job title and employee number of the person in department 20 who earns more than ￡25000?

????SELECT ENAME,EMPNO,JOB FROM EMP WHERE SAL>25000 AND DEPTNO=20;

8. Find all employees whose job is either Clerk or Salesman.

????SELECT * FROM EMP WHERE ?JOB='CLERK' OR JOB='SALESMAN');

9. Find any Clerk who is not in department 10.

????SELECT * FROM EMP WHERE JOB = 'CLERK' AND DEPTNO!=10;

10.Find everyone whose job is Salesman and all the Analysts in department 20.

????SELECT * FROM EMP

? ? WHERE JOB='SALESMAN' OR ?(JOB='ANALYSTS' AND DEPTNO=20);

（找两种人用or）

11. Find all the employees who earn between ￡15,000 and ￡20,000.Show the employee name, department and salary.

????SELECT ENAME,DEPTNO,SAL FROM EMP WHERE SAL BETWEEN 15000 AND 20000;

（直接使用between and 不用大小于号，是取左右端点的）

12 Find the name of the President.

?????SELECT ENAME,JOB FROM EMP WHERE JOB='PRESIDENT';

13 Find all the employees whose last names end with S

????SELECT * FROM EMP WHERE TRIM(ENAME) LIKE '%S'

（使用字符串修整 TRIM(STRING)LIKE'%S'表后缀，'S%'表前缀，'%S%'表中间出现）

14 List the employees whose names have TH or LL in them

??????SELECT * FROM EMP?

? ? ?WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';

?（之间对名字使用LIKE也能进行查找）

15 List only those employees who receive commission.

????SELECT * FROM EMP WHERE COMM>0;

16 Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

????SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM EMP?ORDER BY ENAME;

?（排序使用ORDER BY，字符串排序默认是alphabetical order）

17. Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.

???SELECT ENAME,JOB,SAL,HIREDATE,DEPTNO FROM EMP ORDER BY SAL;

（排序使用ORDER BY，整型排序默认是ascendingorder即升序）

18. List all salesmen in descending order by commission divided by their salary.

???????SELECT * FROM EMP WHERE JOB LIKE 'SALESMAN’

?ORDER BY NVL(COMM,0)/SAL ?;

（如果某个员工的COMM为NULL，NVL函数将返回0作为替代值，否则返回实际的COMM值。）

19. Find employees in department 30 who receive commision, in ascending order of commission

????SELECT * FROM EMP WHERE DEPTNO=30 AND COMM>0 ORDER BY COMM;

20.Find the names, jobs, salaries and commissions of all employees who do not have managers.

? ? ?SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE MGR=NULL;

21.Find all the salesmen in department 30 who have a salary greater than or equal to ￡18000.

? ? SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND SAL>=18000);

22.Find the employees who were hired before 01-Jan-1998 and have salary above 5000 or below 1000.

???SELECT * FROM EMP

? ?WHERE HIREDATE <=TO_DATE('01-1月-1998') AND SAL NOT BETWEEN 1000 AND 5000);

（使用TO_DATE将‘01-1月-1998’转化为标准日期形式）

23.What is the command?to add primary key constraint to EMPNO

???????ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY(EMPNO);?

OR

? ? ? ALTER TABLE EMP?ADD PRIMARY KEY(EMPNO);??

24.What is the command to add a new column EMP_family_name to existing EMP table

? ? ?ALTER TABLE EMP ADD (EMP_FNAME CHAR(20));

（ADD（列名字 列数据类型(限定字符数)））

25.How to drop primary key constraint for EMPNO

?????ALTER TABLE EMP?DROP PRIMARY KEY CASCADE?

（CASCADE关键字的作用是，除了删除EMP表的主键约束之外，还会删除任何引用EMP表主键的外键约束，这样就不需要手动去删除与之相关的外键）

26.rename EMP table to EMPLOYEE

??????ALTER TABLE EMP RENAME TO EMPLOYEE;

27.rename EMPLOYEE ?back to EMP

???????ALTER TABLE EMPLOYEE RENAME TO EMP;

28.What is the SQL command to remove column EMP_family_name from EMP table

???ALTER TABLE EMP DROP COLUMN EMP_FNAME;

（DROP COLUMN 列名）

29.What is the SQL command to copy emp table to employee table

? ?CREATE TABLE EMP_COPY ?AS ?SELECT ?* ?FROM EMP;

（使用CREATE TABLE 名字 AS SELECT EMP的所有来对EMP进行复制）

30.What is the SQL command to drop employee table

? ? DROP TABLE EMP_COPY;

31.What is the SQL command to display name’s of employee entered interactively from user

? INSERT INTO EMP?VALUES ('&ENAME');

32 ?What is the SQL ?command to find the employee whose commission?is NULL

?SELECT *FROM EMP? WHERE COMM IS NULL;

（判断是不是NULL，要用 IS 不能用等号）