SQL (DDL, DML, DCl, and common functions)

SQLDefinition:SQLIs a general data processing language specification for databases. It can perform the following functions: extract and query data, insert, modify, and delete data, generate and modify database objects, and control database security, database integrity and data protection control.

SQLCategory:
DDL-Data Definition Language (create, alter, drop, declare)

DML-data manipulation language (select, delete, update, insert)

DCL-Data Control Language (Grant, revoke, commit, rollback)

 

In SQL, delete, drop, and truncate indicate deletion, but there are some differences between the three. the truncate and delete statements that only delete data and do not delete the table's structure (Definition) Drop statement will delete the constraints (constrain), triggers (triggers), and indexes (indexes) that the table structure is dependent on ); stored Procedures/functions dependent on the table will be retained, but will change to the invalid state. 2. the delete statement is DML, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution. truncate, drop is DDL, and the operation takes effect immediately. The original data is not placed in rollback segment and cannot be rolled back. trigger is not triggered. 3. the delete statement does not affect the extent used by the table. The high watermark statement keeps the original position unchanged. Obviously, the drop statement releases all the space occupied by the table. The truncate statement is empty by default. To the minextents extent, unless reuse storage is used; truncate will reset the high water line (back to the beginning ). 4. speed, in general: Drop>; truncate>; Delete 5. security: Use drop and truncate with caution, especially when no backup is available. otherwise, you will not be able to use it when you cry. If you want to delete some data rows, use Delete. Note that the WHERE clause is included. the rollback segment must be large enough. to delete a table, use drop to retain the table and delete all data. if it is irrelevant to the transaction, use truncate. if it is related to the transaction or you want to trigger the trigger, delete is used. if you are sorting out the fragments in the table, you can use truncate to keep up with the reuse Stroage, and then re-import/insert data in practical applications. The difference between the three is clear. Use drop when you no longer need the table; Use truncate when you want to keep the table but delete all records; when you want to delete some records (always with a where clause), use Delete.

 

DB2 Data Type

Char (): the maximum length of a fixed-length string is 254.

Varchar (): the maximum length of a variable-length character is 4000

Smallint: the length of a short integer is 2 bytes.

Integer: the length of an integer is 4 bytes.

Real: Single-precision floating point 32-bit approximate value

Double: Double Precision Floating Point 64-bit approximate value

Decimal (m, n): Numeric precision is m decimal is n

Date: Date and Time

Time: Date and Time

Timestamp: Date and Time

DDL-Database definition language: submitted directly.

Create: used to create database objects.

Declare: Except for creating temporary tables used only in the process, the declare statement is very similar to the create statement. The only object that can be declared is a table. And must be placed in the user's temporary tablespace.

Drop: You can delete any objects created with create (database object) and declare (table.

Alter: allows you to modify the information of certain database objects. The index cannot be modified.

The following describes the basic syntax based on objects:

1. database:

Create Database: Create Database database-name [using codeset territory]

Note: The code page is incorrect.

Delete Database: drop database dbname

2. Table:

Create a new table:

Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: Create Table tab_new like tab_old

B: Create Table tab_new as select col1, col2... From tab_old definition only

Modify Table:

Add a column:

Alter table tabname add column Col type

Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type. Add primary key:

Alter table tabname add primary key (COL)

Delete primary key:

Alter table tabname drop primary key (COL)

Drop table tabname

3. tablespace:

Create Table space: Create tablespace tbsname pagesize 4 K managed by database using (File 'file' size)

Add tablespace to container: Alter tablespace tablespace_name add (File "FILENAME" size)

Note: This operation is irreversible and cannot be deleted after being added to the container. Therefore, pay attention when adding the container.

Delete tablespace: Drop tablespace tbsname

4. Index:

Create an index: Create [unique] index idxname on tabname (COL ....)

Delete index: drop index idxname

Note: The index cannot be changed. To change the index, you must delete it and recreate it.

5. View:

Create view: Create view viewname as select statement

Delete view: Drop view viewname

Note: Only columns of the reference type can be modified in the view to change the column range. Other definitions cannot be modified. When the base table of a view is drop, the view becomes invalid.

Drop deletes an attribute column.

 

use yourdatabasealter table your_tabledrop column your-column

 

DML-database manipulation language, which does not implicitly commit the current transaction. whether to submit the transaction depends on the environment settings.

Select: Query compliant data from the table

Note: The connection problem in the condition prevents the flute Product

Delete: deletes data from an existing table.

Update: update the data of an existing table.

Insert: insert data to an existing table

Note: whether to directly submit Delete, update, and insert statements depends on the environment in which the statement is executed.

Pay attention to the full transaction logs during execution.

1. When selecting, pay attention to index predicates and non-index predicates. Try to use index predicates on indexed columns.

Indexable interpretation of predicate types

Col transport con y branch stands for >;,>;=,<=,<, but <>;; is not Indexable.

Col between con1 and con2 y must be final in the matching series.

Col in list y only matches one column

COL is null y

Col like 'xyz % 'y fuzzy match % is later.

Col like '% XYZ' n fuzzy match % above.

Col1 distinct col2 n col1 and col2 from the same table

Col sort expression N for example: C1 (C1 + 1)/2

Both pred1 and pred2 y pred1 and pred2 are Indexable, which refer to columns with the same index.

In addition to (C1 = A or C1 = B), pred1 or pred2 N can be considered as C1 in (a, B)

Not pred1 N or any equivalent form: Not between, not in, not like, and so on.

Examples of using indexes:

<1>; single table index query:

A: Select * from T1 where c1 = 10;

How to retrieve a C1 column without an index

How to retrieve an index on the C1 Column

B: Select * from T1 where c1 = 10 and C2 between 5 and 10 and C3 like 'a %'

Only the C1 column has an index

Separate indexes on C1, C2, and C3 Columns

Joint indexes are available on columns C1, C2, and C3.

You can add index matching rules and advanced rules here, which is hard to understand !!

<2>; two or more table index queries:

(1) execute a query using the nested loop method

A: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t1.c2 = t2.c3

Describes the query method to see which column requires the most index.

B: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t2.c4 = 10 and t1.c2 = t2.c3

(2) Use merged connections to perform connection queries

Example: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t2.c4 = 10 and t1.c2 = t2.c3

(3) Rules or methods for connecting to more than three tables

(2) group by: implements the simple grouping function. When group by is used, query the and constants in the selected column except the group by column, use grouping functions for other columns. You can use grouping functions to implement columns that do not appear in group by, such as Min and Max.

(3) Use of having to make conditional judgment on some grouping columns.

(4) The order by clause causesSQLWhen the query results are displayed, return rows are arranged in order. The order of return rows is determined by the value of the expression specified by the order by clause.

2. Delete: delete records from a table

Syntax format:

Delete from tablename where (conditions)

3. insert: insert records into the table

Syntax format:

Insert into tablename (col1, col2 ,...) Values (value1, value2 ,...);

Insert into tablename (col1, col2 ,...) Values (value1, value2 ,...), (Value1, value2 ,...),......

Insert will not wait for any program and will not cause locking.

4. Update:

Syntax format:

Update tabname set (col1 = values1, col2 = values2 ,...) Where (conditions );

Note: The update speed is slow. You must create an index on the corresponding column.

DCL-Data Control Language

Grant-grant User Permissions

Revoke-Revoke User Permissions

Commit-commit transactions to make database modifications permanent

Rollback: rolls back the transaction, removes all modifications made after the previous commit command, and restores the database content to the status after the previous commit execution ..

1. Grant: All users or administrators grant access permissions to other users.

Syntax format:

Grant [All privileges | privileges,...] On tabname | viewname to [Public | user,...]

2. Revoke: cancels the access permission of a user.

Syntax format:

Revoke [All privileges | privileges,...] On tabname | viewname from [Public | user,...]

Note: you cannot cancel any permissions of instance-level users. They are not authorized by grant but by group.

3. Commit: records the modifications made in the transaction to the database in a permanent manner.

Syntax format:

Commit [work]

4. rollback: undo all the changes made since the last submission.

Syntax format:

Rollback [work]

AdvancedSQLBrief Introduction

I. Computing term used between queries

A: Union operator

The Union operator combines two other result tables (such as Table1 and table2) and removes any duplicate rows from the table to generate a result table. When all is used together with Union (that is, Union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either Table1 or table2.

B: Random t operator

The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.

C: intersect Operator

The Intersect operator derives a result table by only including the rows in Table1 and Table2 and eliminating all repeated rows. When all is used with intersect (intersect all), duplicate rows are not eliminated.

Note: The query results of several computation words must be consistent.

Ii. External Connection

A. left Outer Join:

Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.

B: Right outer join:

Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.

C: Full outer join:

Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

Note: Composite outer connections are connected from left to right. The result set of the left join and the right join

Iii. Super grouping and mobile Functions

A: grouping sets: Used in a singleSQLTo form a multi-level group.

Example: Select company_id, node_id, count (customer_id) from customer group by grouping sets (company_id, node_id)

B: rollup: multiple groups can be formed in a single database operation.

Example: Select company_id, node_id, count (customer_id) from customer group by rollup (company_id, node_id)

Note: rollup operations are not interchangeable. It is important to specify the order of user groups.

C: Cube: generate all groups in the grouping table.

Example: Select company_id, node_id, count (customer_id) from customer group by cube (company_id, node_id)

D: over: Mobile functions can help you analyze mobile data.

Select date, AVG (qty) over (order by date rows between 1 preceding and 1 following) as values from sale

Appendix: common functions

1. type conversion functions:

Convert to numeric type:

Decimal, double, integer, smallint, real

Hex (ARG): convert to the hexadecimal representation of the parameter.

Converted to string type:

Char, varchar

Digits (ARG): returns the string representation of Arg, which must be decimal.

Converted to date and time:

Date, time, timestamp

2. Date and Time:

Year, quarter, month, week, day, hour, minute, second

Dayofyear (ARG): returns the Daily Value of Arg within the year.

Dayofweek (ARG): returns the Daily Value of Arg within a week.

Days (ARG): returns the integer representation of the date, from-01-01.

Midnight_seconds (ARG): the number of seconds between midnight and Arg.

Monthname (ARG): returns the month name of Arg.

Dayname (ARG): returns the week of Arg.

3. String functions:

Length, lcase, ucase, ltrim, rtrim

Coalesce (arg1, arg2 ....) : The first non-null parameter in the returned parameter set.

Concat (arg1, arg2): connects two strings: arg1 and arg2.

Insert (arg1, POs, size, arg2): returns one. arg1 is deleted from the POs and inserted into this position.

Left (ARG, length): returns the leftmost length string of Arg.

Locate (arg1, arg2, <POS> white eye: searches for the location where arg1 first appeared in arg2. If you specify POs, the location where arg1 first appeared starts from the POS of arg2.

Posstr (arg1, arg2): returns the position where arg2 first appeared in arg1.

Repeat (arg1, num_times): returns the string that arg1 is repeated for num_times.

Replace (arg1, arg2, arg3): replace all arg2 in arg1 with arg3.

Right (ARG, length): returns a string consisting of the Left length bytes of Arg.

Space (ARG): returns a string containing Arg spaces.

Substr (arg1, POs, <length> white eye: returns the length starting from the POs position in arg1. If the length is not specified, the remaining characters are returned.

4. mathematical functions:

Abs, Count, Max, Min, Sum

Ceil (ARG): returns the smallest integer greater than or equal to Arg.

Floor (ARG): returns the smallest integer less than or equal to the parameter.

MoD (arg1, arg2): returns the remainder of arg1 divided by arg2. the symbol is the same as that of arg1.

Rand (): returns a random number between 1 and 1.

Power (arg1, arg2): returns the arg2 power of arg1.

Round (arg1, arg2): rounding to truncation. arg2 is the number of digits. If arg2 is negative, rounding to the number before the decimal point.

Sigh (ARG): returns the symbol indicator of Arg. -1, 0, 1 indicates.

Truncate (arg1, arg2): truncates arg1. arg2 is the number of digits. If arg2 is a negative number, the arg2 before the decimal point of arg1 is retained.

5. Others:

Nullif (arg1, arg2): If the two parameters are equal, null is returned. Otherwise, parameter 1 is returned.