007-sql Overall Overview

I. Overview

1. SQL Basics

databases, data tables, rows, columns, relationships

1. Inquire

Select field 1, field 2,* from table where condition

1. Remove Duplicates

Distinct

Must be placed in front of all columns

1. Interval statements

Between ... and selects a range of data between two values. These values can be numeric, text, or date. and contains both values:

Example: SELECT * from TableA where age between and 50

1. Specifying conditional statements

Inch

1. and/or statements

Connect multiple conditions

1. Sort

Order by ASC Ascending; Desc Descending

1. Rollup Count (*)
2. Aggregation functions

Rollup: Count (*)

Max value: Max (column name)

Minimum value: Min (column name)

Average: Avg (column name)

1. Grouping statements

Group by (field name) having condition

II. Foundation of SQL

SQL Definition: SQL is a database-oriented universal Data Processing language Specification, can accomplish the following kinds of functions: Extract query data, insert modify delete data, generate modify and delete database objects, database security control, database integrity and data protection control.

SQL Category:
ddl-Data Definition language (create,alter,drop,declare)
dml-Data Manipulation Language (Select,delete,update,insert)
dcl-Data Control Language (Grant,revoke,commit,rollback)

DB2 Data Type
CHAR (): Fixed length string maximum length is 254
VARCHAR (): variable-length character maximum length is 4000
SMALLINT: Short integer digit length is 2 bytes
Integer: Integer digit length is 4 bytes
REAL: Single-precision floating point 32-bit approximation
Double: dual-precision floating-point 64-bit approximation
Decimal (M,N): Digital precision is m decimal place n
Date: datetime
Time: DateTime
TIMESTAMP: Date Time

2.1 DDL

ddl-Database Definition language: Submitted directly.
    Create : Used to create database objects.
    DECLARE : In addition to creating temporary tables that are used only in procedures, 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 temp table space.   
    DROP : You can delete any objects created with the Create (database object) and declare (tables).
    ALTER : Allows you to modify information for some database objects. The index cannot be modified.
The following basic syntax is mainly based on objects:
1 , database :
CREATE database: create  database database-name [using codeset codeset territory territory] 
   Note: A problem with the code page.
   Delete database : Drop database dbname
2 , table:

To create a new table:
CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)
To create a new table from 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
To modify a table:
Add a column :
Alter Table table_name Add COLUMN_NAME datatype

Delete a column

Alter table table_name Drop COLUMN column_name
Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.

Change the data type of a column in a table

ALTER tabletable_name ALTER COLUMN COLUMN_NAME datatype

Primary key:
Add : Alter table TabName Add primary key (COL)
Delete : Alter table TabName Drop primary key (COL)

To Delete a table:drop table TabName
3, table space:
Creating tablespace: Create Tablespace tbsname pagesize 4k managed by database using (file ' file ' size)
Table space joins container: Alter tablespace tablespace_name Add (file ' filename ' size)
Note: The operation is irreversible and will not be deleted after joining the container, so be aware of it when you join.
Delete Table space: Drop tablespace Tbsname
4, Index:
  To create an index:Create [unique] index idxname on tabname (col ...)
 To delete an index:Drop INDEX Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
5, Views:
Creating views: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName
Note: The only thing the view can modify is the reference type column, which changes the column range. All other definitions are not modifiable. When the view is based on a base table drop, the view becomes invalid.

2.2 DML

The dml-database manipulates the language, which does not implicitly commit the current transaction and whether the submission is dependent on the environment setting.

SELECT: Query compliance data from a table
Note: The problem of connecting in the condition, avoid the product of flute Descartes
Delete: Deleting data from an existing table
Update: Updating data for an existing table
Insert: Inserting data into an existing table
Note: Whether the delete,update and insert are submitted directly depends on the environment in which the statement is executed.
Note that the transaction log is full at execution time.
1,Selectwhen, note the index predicate and the non-indexed predicate, and use the index verb on the indexed column as much as possible.
predicate types can be indexed annotations
Col∝con Y∝ stands for >;,>;=,=,<=,&lt, but <>; is not indexable.
Col between Con1 and Con2 Y must be final in the matching series.
Col in list Y matches only one matching column
Col is null Y
Col like ' xyz% ' Y fuzzy matches% in the back.
Col like '%xyz ' N fuzzy matches% in front.
Col1∝col2 NCol1 and col2 from the same table
Col∝expressionn Example: C1 (c1+1)/2
Pred1 and Pred2 Y Pred1 and Pred2 are indexable, referring to columns of the same index
Pred1 or Pred2 N except (c1=a or c1=b), he can be considered C1 in (A, B)
Not Pred1 N or any equivalent form: not between,not in,not like and so on.
Examples of using indexes are:
<1>;, an introduction to index queries on a single table:
A:select * from t1 where c1 = 10;
No index on the C1 column, how to retrieve
There is an index on the C1 column, how to retrieve
B:select * from T1 where C1 =10 and C2 between 5 and C3 like ' A% '
Index only on the C1 column
There are separate indexes on the C1,C2 and C3 columns
A federated index on the C1,c2 and C3 columns
This can be filled with index matching rules and advanced rules, more difficult to understand!!
Introduction to index queries on <2>;, two or more tables:
(1), using circular nesting method to execute the query
A:select t1.c1,t1.c2,t2.c3,t2.c4 from T1,t2 where t1.c1=10 and T1.c2 =t2.c3
Describe the methods of the query to see which columns are most needed for indexing.
B:select t1.c1,t1.c2,t2.c3,t2.c4 from T1,t2 where t1.c1=10 and t2.c4 = ten and T1.c2 =t2.c3
(2), use merge connection to perform connection query
Example: Select T1.c1,t1.c2,t2.c3,t2.c4 from T1,t2 where t1.c1=10 and t2.c4 = ten and T1.c2 =t2.c3
(3), more than three tables for connection query the rules or methods taken
(2), GROUP by: the ability to implement a simple grouping, when using group BY, the query selection column in addition to the group by the occurrence of the and constants, the other columns to use the grouping function. You can use some grouping functions to implement some columns that are not present in group by, Min,max, and so on.
(3), having the use of some groups to determine the conditions of the column.
(4), the ORDER BY clause causes SQL to arrange the returned rows in order when the query results are displayed, and the order in which the rows are returned is determined by the value of the expression specified by the ORDER BY clause.
2,DELETE: Deleting records from a table
Syntax format:
DELETE from TableName WHERE (conditions)
3,INSERT: Inserting records into a table
Syntax format:
INSERT into TableName (col1,col2,...) VALUES (Value1,value2,...);
INSERT into TableName (col1,col2,...) VALUES (value1, value2,...), (value1, value2,...),......
Insert does not wait for any programs and does not cause locking.
4,UPDATE：
Syntax format:
UPDATE tabname SET (col1=values1,col2=values2,...) WHERE (conditions);
Note: Update is slow to index on the corresponding column.

2.3. DCL

dcl-Data Control Language

grant-Granting user rights
revoke-Revoke user Rights
Commit-commits the transaction, can make the database modification permanent
rollback-rolls back the transaction, eliminating all modifications made after the last commit command, allowing the contents of the database to revert to the state after the last commit execution.

1. GRANT: All or the administrator assigns access rights to other users
Syntax format:
Grant [All Privileges|privileges,....] on TabName | ViewName to [Public|user,....]
2, REVOKE: Cancellation of a user's access rights
Syntax format:
Revoke [All Privileges|privileges,....] on TabName | ViewName from [Public|user,....]
Note: You cannot cancel any permissions for a user at the instance level, they are not authorized by grant, and are implemented through a group.
3. COMMIT: Permanently record the changes made in the transaction to the database.
Syntax format:
commit [Work]
4. ROLLBACK: Undo all changes made since the last commit.
Syntax format:
Rollback [Work]

Three, advanced SQL introduction

One, the use of arithmetic words between queries
A:union operator
The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operation word must be consistent.
Second, outer connection
A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.
B:right outer join:
Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.
C:full outer join:
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Note: Composite outer joins perform connections in left-to-right order, left-connected result set and right-hand connection
Third, super group and move function
A:grouping sets: Used to form multilevel groupings in a single SQL.
Example: Select Company_id,node_id,count (customer_id) from the customer group by grouping sets (COMPANY_ID,NODE_ID)
B:rollup: Multiple groupings can be formed in a single database operation.
Example: Select Company_id,node_id,count (customer_id) from the customer group by rollup (COMPANY_ID,NODE_ID)
Note: The rollup operation is not an exchangeable operation and it is important to specify the order of the user groups.
C:cube: Generates all combinations of groupings in a grouped table.
Example: Select Company_id,node_id,count (customer_id) from the customer group by Cube (COMPANY_ID,NODE_ID)
D:over: Moving functions can help with moving data analysis
Select Date,avg (Qty) over (order by date rows between 1 preceding and 1 following) as values from sale

Appendix: Introduction to Common functions
1. type conversion function:
Convert to a number type:
Decimal, double, Integer, smallint,real
Hex (ARG): Converts to a 16-binary representation of a parameter.
Converted to a String type:
char, varchar
Digits (ARG): Returns the string representation of ARG, which must be decimal.
Convert to date Time:
Date, Time,timestamp
2, Time and date:
Year, quarter, month, week, day, hour, minute, second
DayOfYear (ARG): Returns the day value of ARG in the year
Dayofweek (ARG): Returns the day value of ARG within the week
Days (ARG): Returns the integer representation of the date from 0001-01-01.
Midnight_seconds (ARG): The number of seconds between midnight and Arg.
Monthname (ARG): Returns the month name of Arg.
Dayname (ARG): The week that returns ARG.
3. String function:
Length,lcase, UCase, LTrim, RTrim
COALESCE (Arg1,arg2 ...): Returns the first non-null parameter in a parameter set.
Concat (ARG1,ARG2): Connects two strings arg1 and arg2.
Insert (ARG1,POS,SIZE,ARG2): Returns a arg1 that removes the size character from the POS and inserts arg2 into the position.
Left (Arg,length): Returns the leftmost length string of Arg.
Locate (arg1,arg2,<pos>;): Find the first occurrence of arg1 in Arg2, specify POS, and start at Arg2 Pos to find where the arg1 first appears.
Posstr (ARG1,ARG2): Returns the position arg2 the first time it appears in Arg1.
Repeat (Arg1, num_times): Returns a string that arg1 is repeated num_times times.
Replace (ARG1,ARG2,ARG3): Replaces all arg2 in arg1 with Arg3.
Right (Arg,length): Returns a string of length bytes to the left of Arg.
Space (ARG): Returns a String that contains a space of Arg.
SUBSTR (arg1,pos,<length>;): Returns the length of the POS position at the beginning of the arg1, and returns the remaining characters if length is not specified.
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, with the same symbol as ARG1.
Rand (): Returns a random number from 1 to 1.
Power (ARG1,ARG2): Returns the arg2 of the arg1.
Round (ARG1,ARG2): Rounding truncation processing, arg2 is the number of digits, and if arg2 is negative, the number before the decimal point is rounded.
Sigh (ARG): Returns the symbol designator for ARG. -1,0,1 indicates.
Truncate (ARG1,ARG2): Truncation Arg1,arg2 is the number of digits, if arg2 is negative, the arg1 bit before the decimal point is preserved.

5. Other:
Nullif (ARG1,ARG2): Returns NULL if 2 parameters are equal, otherwise returns a parameter of 1

1. Database queries: Select usage
Select [Top (value)] Field list from data table [Where condition] [Order by field] [ASC or DESC]

2. Add data: Insert into usage
Insert into Data table (field 1, Field 2, Field 3,...) Values (the Value of field 1, the value of field 2, the value of field 3,...)

3. Deleting data: Delete usage
Delete from data table [Where condition]

4. Update Data: Update usage
Update data Table Set field 1 = field value 1, field 2 = field value 2, ... [Where condition]

5. Build data table: Create table Usage
Create table Data table name (Field 1 name, field 1 type, field 2 Name field 2 Type, ...)

Access data table Common data type: Text,char (number), Memo,number,int,date/time,logical,oleobject

AutoNumber field Add Example: Create TABLE AAA (ID int identity (1, 1) not Null,abcvarchar (+) NULL)

6. Change data table: Alter table Usage
Add field: altertable data table name add Column field name fields type

Delete field: Alter table data table name Drop Column field name

7. Delete Data sheet: Drop table usage

Drop Table Data table name

007-sql Overall Overview