当前位置: 澳门新濠3559 > 数据库 > 正文

以下摘自官方文档

时间:2019-12-09 01:06来源:数据库
以下摘自官方文档: Oracle / PLSQL: ALIASES website : This Oracle tutorial explains how to use Oracle ALIASES (temporarynames for columns or tables) with syntax and examples. More Advanced SQL 语法: Description Oracle ALIASES

以下摘自官方文档:

Oracle / PLSQL: ALIASES

website

This Oracle tutorial explains how to use Oracle ALIASES (temporary names for columns or tables) with syntax and examples.

More Advanced SQL

语法:

Description

Oracle ALIASES can be used to create a temporary name for columns or tables.

  • COLUMN ALIASES are used to make column headings in your result set easier to read.
  • TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause(列出相同的表不止一次在那个from从句)).

Relational Database

  • has tables which are linked using key attributes

    

Syntax

The syntax to ALIAS A COLUMN in Oracle/PLSQL is:

column_name AS alias_name

OR

The syntax to ALIAS A TABLE in Oracle/PLSQL is:

table_name alias_name

Foreign Keys

  • An attributes in one table that uniquely identifies a row of another table is a foreign key
  • It is a primary key in the other table, it doesn't need to be unique in this table
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]


性能:
   When you do not need to know the number of deleted rows, the TRUNCATE TABLE statement is a faster way to empty a table than a DELETEstatement with no WHERE clause. Unlike DELETE, TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. SeeSection 14.1.34, “TRUNCATE TABLE Syntax” and Section 14.3.5, “LOCK TABLES and UNLOCK TABLES Syntax”.
   简单理解是:truncate 在不锁表的情况下,很快:

   如果想用delete删除快点:
    The time required to delete individual rows in a MyISAM table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable
    可以配置的key_buffer_size大小
多表删除:
(1)不带别名

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Parameters or Arguments

column_name
The original name of the column that you wish to alias.

table_name
The original name of the table that you wish to alias.

alias_name
The temporary name to assign.

Referential Integrity

  • Each foreign key need to refer to an actual row in the table it refers to;-------> this is called Referential Integrity
  • The exception is a NULL value

Or:

以下摘自官方文档。Note

  • If the alias_name contains spaces, you must enclose(包围) the alias_name in quotes(双引号).
  • if the alias_name contains number,you must enclose then alias_name in quotes.(myself addtion)
  • It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
  • The alias_name is only valid within the scope of the SQL statement.

澳门新濠3559,Querying Multiple Tables - Joins

  • INNER JOIN
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
(2)带别名:必须写别名:

Example - ALIAS a column

Generally, aliases are used to make the column headings in your result set easier to read. For example, when concatenating fields together(连接字段), you might alias the result.

For example:

SELECT contact_id, first_name || last_name AS NAME
FROM contacts
WHERE last_name = 'Anderson';

In this example, we've aliased the second column (ie: first_name and last_name concatenated) as NAME. As a result, NAME will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

However, it would have been perfectly acceptable to write this example using quotes as follows:

SELECT contact_id, first_name || last_name AS "NAME"
FROM contacts
WHERE last_name = 'Anderson';

澳门新濠3559 1

Next, let's look at an example where we are required to enclose the alias_name in quotes.

For example:

SELECT contact_id, first_name || last_name AS "CONTACT NAME"
FROM contacts
WHERE last_name = 'Anderson';

In this example, we've aliased the second column (ie: first_name and last_name concatenated) as "CONTACT NAME". Since there are spaces in this alias_name, "CONTACT NAME" must be enclosed in quotes.

INNER JOIN

  • return the rows where the join condition is met
  • SELECT column_name(s)
    FROM talbe1
    INNER JOIN table2
    ON talbe1.column_name = table2.column_name;
  • e.g. SELECT * FROM student INNER JOIN lecturer ON student.advisor=lecturer.staffid;

  If you declare an alias for a table, you must use the alias when referring to the table:

Example - ALIAS a Table

When you create an alias on a table, it is either(任何一个) because you plan to list the same table name more than once in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL statement shorter and easier to read.

Let's look at an example of how to alias a table name in Oracle/PLSQL.

For example:

SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;

In this example, we've created an alias for the products table called p. Now within this SQL statement, we can refer to the products table as p.

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.

For example, we could modify our example above and create an alias for the categories table as well.

SELECT p.product_id, p.product_name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id
ORDER BY p.product_name ASC, c.category_name ASC;

Now we have an alias for categories table called c as well as the alias for the products table called p.

Aliases

  • instead of table1.column_name we can use aliases: aliases are used to temporarily rename a table or column.
  • syntax :
    table rename: SELECT col FROM table1 AS temp_name;
    column rename:SELECT col AS temp_name FROM table1;
DELETE t1 FROM test AS t1, test2 WHERE ...

LEFT JOIN

  • return all rows of table1 (left table) with corresponding rows of table 2 if the condition is met, or null if not

Correct:

RIGHT JOIN

  • return all rows of table2 (Right table) with corresponding rows of table 1 if the condition is met, or null if not
  • e.g.
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

2、删除mysql 中重复记录,并保留一条

DELETE FROM `tb_phones` WHERE id NOT IN(SELECT * FROM(SELECT id FROM `tb_phones` GROUP BY phone)AS b);

FULL OUT JOIN

  • SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 on table1.column_name = table2.column_name;

  

SELF JOIN

  • join a table to itself: compare a column in the table to another column in the same table

  • SELECT alias.col, alias2.col FROM table1 AS alias1 INNER JOIN talbe1 AS alias2 ON alias1.fk = alias2.pk;

Joining Multiple Tables

  • SELECT table1.col, table3.col FROM table1 INNER JOIN table2 ON table1.pk1 = table2.fk1 INNER JOIN table3 ON table2.fk3 = table3.pk3;
  • SELECT table1.col, table3.col FROM table1,table2,table3 WHERE table1.pk1 = table2.fk1 AND table2.fk3 = table3.pk3;

Nested Queries

  • sub-queries must be enclosed in()brackets

IN / NOT IN

VIEW Syntax

  • CREATE VIEW <ViewName> AS <query>
  • CREATE VIEW CSstaffView AS SELECT* FROM Lecturer WHERE school = 'Computing Science';

编辑:数据库 本文来源:以下摘自官方文档

关键词: