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

发布时间:2024年01月04日

(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 不能用等号

文章来源:https://blog.csdn.net/gyeolhada/article/details/135363556
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。