[DB2 learning document 7] SQL for DB2

Author: gnuhpc
Source: http://www.cnblogs.com/gnuhpc/

1. SQL data operation language data manipulation language (DML)
See beginning SQL queries: from novice to professional, by Clare church Cher (apress, 2008)
2. SELECT statement
This statement is the simplest and most complex statement in DB2. It contains six parts:
Select: List column names or related structures.
From: describes where to obtain the required data and how to combine multiple targets.
Where: Describe the condition like a predicate.
GROUP: describes how non-aggregated data is processed before aggregated data.
Having: optional, equivalent to the where statement, which serves as a criterion for determining the group generated in a group.
Order: Provides sorting requirements.
So how do I know from which table to retrieve data?
The system provides the following forms for query:
Systables sysibm information about all tables in the database
Sysindexes sysibm information about the indexes on all tables
Sysviews sysibm information on all views in the database
... Similar structure
The system also provides the following views for query:
Tables syscat information about all tables in the database
Indexes syscat information about the indexes on all tables
Views syscat information on all views in the database
Procedures syscat information on all stored procedures in the database
Functions syscat information on all functions in the database
Select * From sysibm. Tables Ables can be viewed. Note that this operation should not be performed in the CLP, otherwise it will be very depressing because the CLP screen is limited and there is too much information.
Commands such as select name, creator, colcount from sysibm. Ables can be executed in CLP.
We can customize the form output:
Select name, creator, colcount as "no of columns", ABS (npages * 4*1024) as "bytes" from sysibm. categorables
How to use where?
Where is used as a filter grep: Select name, creator from sysibm. Tables Ables where creator = 'fuzzy'
How to Use group?
For example, if you want to know the sum of salaries that each department sends to employees in the sample, how do you tell sum that you want to accumulate the salaries of each department separately? You can use group.
Select workdept, sum (salary) as newsalary from employee group by workdept
How to use having?
Similar to where, This Is The grep for group data. For example:
Select workdept, sum (salary) deptsal from employee group by workdept having sum (salary)> 100000
This determines the department with a total salary of more than 1000000. Note that when we use the having statement, though we rename it, we still use the previous name salary to rename it.
How to Use order?
Order to specify which column of elements to sort, for example:
Select workdept, sum (salary) deptsal from employee group by workdept having sum (salary)> 100000 order by workdept
Three large select statements: joins, subqueries, and unions
1. Joins
Compare, process, and use the data of different tables, for example:
Select E. firstnme, E. lastname, D. deptname, D. Location from employee e inner join Department D on E. workdept = D. deptno
Here, E and D simplify the database names. For inner join, let's review the three concepts related to the database:
Inline:
A inner join B on A. ID = B. ID
Query the ID records of both tables
Left outer:
A left join B on A. ID = B. ID
As long as Table A has records, table B has no records
Right outer:
A right join B on A. ID = B. ID
As long as table B has records, Table A has no records
2. subqueries
It is a nested method that can be used for more complex where or having operations. It can be used as a virtual table or view for the from operation. For example:
Select firstnme, surname from employee where empno in (select mgrno from Department)
3. Union
That is, the set in the dimension graph is the same, and the set intersection is intersect, And the set difference set is t, after adding all to these operations, the elements in the original set will be retained without removing them as they are by default. For example:
Select firstnme from employee where salary <style = "font-weight: bold" size = "4"> 3. Use the DB2 Registry
DB2 provides many special values to help you obtain information similar to the current time and date, for example:
Select current timestamp from sysibm. sysdummy1
The following is an example of the benefits of using the registry:
In the "employee" table of the sample database, we add the good friend Jimi Henderson Rix of Mozart (this is not the famous electric guitar player). The statement is as follows:
Insert into employee (empno, firstnme, lastname, hiredate, edlevel) values ('20170101', 'jimi', 'henrix ', current date, 16)
Here, we use the current date register to set the hire time, which is much more reliable than our manual settings.
We can try to see if this value is the current time:
---------------------------------
DB2 => values current time
1--------15: 56: 451 records selected.
-------------------------
That's right!
For more information about time registers and Their Applications on IBM developerworks, see:
Http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
In addition to the time registers, We will list all the registers here. We hope you can use them well:
Client acctng
Client applname
Client userid
Client wrkstnname
Current dbpartitionnum
Current path
Current Schema
Current Server
Current Time
Current Timestamp
Current timezone
Current User
Current Query Optimization
Session user
System User
User

 

1. Basic methods for creating a table:

Create Table nomination
(
Nominationid integer not null,
Nominee char (6) Not null,
Nominator char (6) Not null,
Reason varchar (250 ),
Nomdate date not null
)

Format: column_name data_type constraint_details

Note that the column name cannot be greater than 30. If you do not specify the limit information, we allow null.

There are two notable points behind this seemingly simple operation:

The first is to check whether all storage requirements are met, and the second is permission verification. For permissions, you must be the sysadm Group of the database instance, The dbadm Group of the database, or the database must have the createtab permission and be used in the appropriate tablespace. Of course, this may not be our consideration for the moment, or we will go crazy...

The name of a table starts with a letter and cannot contain illegal characters. The table name must not exceed 128 words. It is unique in a schema. Schema is similar to a namespace. If you do not display the specified schema, you can use the id you used to connect to the database as the schema.

If you have a background for data modeling, you will know that one or more attributes should be defined as unique identifiers in a series of attributes of a given object. The SQL statement can be defined in the following two forms:

Create Table nomination
(
Nominationid bigint not null primary key,
Nominee char (6) Not null,
Nominator char (6) Not null,
Reason varchar (250 ),
Nomdate date not null
)

Or

Create Table nomination
(
Nominationid bigint not null,
Nominee char (6) Not null,
Nominator char (6) Not null,
Reason varchar (250 ),
Nomdate date not null,
Primary Key (nominationid)
)

The following command is used to change the primary key:

DB2 => alter table nomination add primary key (nominationid)

2. Foreign key settings and Processing

The primary key must be used with another restriction: foreign key (reference integrity restriction ). For example:

The first table is the country table, which contains all the countries in the world. The second table is the city table. Note that country_id and country_no are the columns connecting the two tables.

Country_no is the so-called foreign key (also called the reference constraint, referential constraints), which is used to point toPrimary KeyBecause of this relationship, the country_no Column cannot have a value that does not exist in country_id. The data type of the foreign key must be compatible with the primary key type of the parent table.

Create Table Country (
Country_id int not null primary key,
Country_name varchar (30) not null,
Continent_name char (15)
)
Create Table City (
City_id int not null primary key,
City_name varchar (30) not null,
Country_no int references country,
Population int
)

In the nomination example, we introduce a table, category, and change the nomination table to use category as a constraint. First, create the category table,

The following table is created based on this requirement:

Then we can create a form:

Create Table Category
(
Categoryid integer not null primary key,
Cateogryname varchar (50) not null,
Eligibility varchar (250)
)

First, we need to add a column, categoryid

DB2 => alter table nomination
Add column categoryid integer not null

The following error occurs:

Sql0193n in an alter table statement, the column "categoryid" has been
Specified as not null and either the default clause was not specified or was
Specified as default null. sqlstate = 42601

The reason is that if you use the alter table command to forcibly Add a not null parameter, DB2 considers that you may operate on an existing column, but in fact DB2 does not check this stuff. We only need to add a default value to the backend, and DB2 will release it.

DB2 => alter table nomination
Add column categoryid integer not null default 1

We now use this column to connect two tables and point to the category table.

DB2 => alter table nomination add foreign key categoryexists (categoryid)
References category (categoryid)

In this way, we have established a connection, but like other databases, DB2 also provides some on change rules for the volume of these connected tables. We can use:

Delete the created foreign key first:

DB2 => alter table nomination drop constraint categoryexists

We create a new one and use the on Delete rules command to restrict the processing of the database once the parent table is deleted:

DB2 => alter table nomination add foreign key categoryexists (categoryid)
References category (categoryid) on Delete restrict

The on delete option can be followed by the following parameters, which are described in two tables: Color and object:

Cascade:Once the foreign key of any parent table is deleted, all rows in the matched child table are deleted.

Set NULL:Once the foreign key of the parent table is deleted, the related values in the child table are set to null, but the row is retained.

No action:Literally, it does not try to resolve any conflicts. It is actually a sub-record, so it cannot be deleted. In the following example, the delete operation is directly failed.

Restrict:If the child table has reference constraints in the parent table, the delete operation will fail.

Note that there seems to be no difference between this and no action, because the relationship between the two tables is not enough to explain the problem, and only in some cases will lead to the difference, so let's take an example:

T2 is the parent table of T3, and delete rule is set to cascade.

T1 is also the parent table of T3, and delete rule is set:

1) restrict: if any child row in T3 has a reference constraint of T1, any delete operation with reference constraints in T1 or T2 will fail (sqlstate 23001 ). That is to say, this execution permission has the highest priority and takes effect before cascade takes effect.

2) No action: When you delete the reference constraints in T2, because T2 and T3 are cascade, the related lines of T3 are alsoPossibleWill be deleted."Possible"This means that when you delete T2, the reference constraints are insufficient to delete all T1-related rows in T3 .). That is to say, it is the rule executed after the action of other constraints.

In addition to the on Delete rule, we can also set it to the on update Rule, which takes effect when the reference constraints in the parent table are changed.

On update restrict: Restrict this change.

On update no action: the update operation is successful. The value of the parent table changes according to the operation, and the value of the child table remains unchanged.

When we change such a constraint rule, no command can be directly changed. We can only delete the foreign key and then recreate the foreign key.

The previous section describes the reference constraints. This section describes another constraint and its solution.

1. Check constraints:

Once a table check constraint is defined on the table, each update and insert statement will cause a check of the restriction or constraint. If the constraints are violated, data records are not inserted or updated, and an SQL error message is returned.

The basic syntax is: Check (columncolumn-constraints)

For example, create the following table:

Create Table nomination
(
Nominationid bigint not null primary key,
Nominee char (6) Not null,
Nominator char (6) Not null,
Reason varchar (250 ),
Nomdate date not null,
Categoryid integer not null,
Check (nominee! = Nominator ),
Foreign key categoryexists (categoryid)
References category (categoryid) on Delete restrict
)

Of course, we can also add check constraints to existing forms:

DB2 => alter table nomination add Constraint
Noselfnomination check (nominee! = Nominator)

2. Disabling constraints and constraint Deferral)

We can use not enforced to establish a constraint so that the constraint does not take effect. First, we will delete the check constraints created in the previous section:

DB2 => alter table nomination drop constraint noselfnomination

Then, create a solution constraint:

DB2 => alter table nomination add Constraint
Noselfnomination check (nominee! = Nominator) not enforced

You may be wondering why, simply put, this can improve your database performance and use enable query optimization and disable Query Optimization in some cases. We will learn later. Here is a small demonstration:

We should first Delete the constraints we just created:

DB2 => alter table nomination drop constraint noselfnomination

Use the stuff mentioned above to establish a solution constraint:

DB2 => alter table nomination add Constraint
Noselfnomination check (nominee! = Nominator)
Not enforced enable Query Optimization

In this way, we will tell DB2 that we do not use this constraint, but we will use this constraint for performance optimization during queries.

For example, we can query: Select * from nomination where nominee = nominator

DB2 will first look at this solution constraint, which tells us that this is impossible (although this constraint does not enforced ), then DB2 will not look up the table (in fact, this does not necessarily reflect the real situation, so we need to use this solution with caution), and the performance is naturally optimized.

DB2 provides the ability to automatically add values to a column when a row is inserted. The so-called identity rules can be used. For example, to add values to a column in ascending order, of course, it is also very complicated. This is generally used as the identification code and serves as the primary key for defining the table. The generated syntax allows you to customize the policy for generating this value.

Syntax:

Column definition generated {always | by default}
As {identity rules | using your rules}

Delete the table we created last time:

DB2 => drop table nomination

Create another table:

Create Table nomination
(
Nominationid bigint not null primary key generated always as identity,
Nominee char (6) Not null,
Nominator char (6) Not null,
Reason varchar (250 ),
Nomdate date not null,
Categoryid integer not null,
Check (nominee! = Nominator) not enforced enable query optimization,
Foreign key categoryexists (categoryid)
References category (categoryid) on Delete restrict
)

Note that the values in the blacklist cannot be explicitly specified using insert or update.

Identity in DB2 also provides multiple policies. For details, you can check the DB2 Manual. The following is an example:

Delete the table we created last time:

DB2 => drop Table Category

Create a form

Create Table Category
(
Categoryid integer primary key generated always as identity
(Start with 1 increment by 1 minvalue 0 maxvalue 999999999
No cycle cache 5 no order ),
Cateogryname varchar (50) not null,
Eligibility varchar (250)
)

All the statements in identity in the black Chinese characters can be found in the DB2 manual. They are all understood by the natural language at a glance.

Sometimes you don't just want to fill in numbers, but you may want to process some letters, so the following example of converting uppercase is for you:

DB2 => alter table category add Column
Uppercatname varchar (50) generated always as (upper (categoryname ))

These are described in the DB2 document.

1. Table space is the logical storage unit of a table. There are three types of table space:

In: You can specify the common tablespace in which the common data of a form is stored.

Index: You can also specify an independent regular tablespace storage index.

Long: You can also allocate a large tablespace to store large objects in a form.

2. Syntax:

Create Table tablename
(Various column names and attributes)
[In tablespace-name]
[Index in tablespace-name]
[Long in tablespace-name]

3. instance:

Create Table awardwinner
(Awardwinnerid integer primary key generated always as identity
(Start with 1 increment by 1 ),
Datewon date not null,
Totalvotes integer not null,
Picture BLOB)
In userspace1
Index in userspace1
Long in picturelobs

 

1. Create Table... as select... method:

We can use the select method to selectively construct columns in the new form from the source form, for example:

DB2 => Create Table employeecopy
As (select firstnme, lastname from employee)
Definition only

Create Table does not insert data at the same time. The keyword refers to"Only define no data", That is, there is no data import.

2. Create Table... like... method:

Creates a table with identical columns.

Syntax:

Create Table [Tablename] Like [Sourcetable]

<[Including | excluding] column defaults>

<[Including | excluding] identity column attributes>

The created table has the same columns as sourcetable (names, data types, and nullability characteristics) (and does not import data). If you do not use excluding column defaults, any Default constraint defined in the source form will take effect. Note that other attributes will not be copied into the new form, and all unique constraints, reference constraints, triggers, or indexes will not be copied.

DB2 => Create Table employeecopy3 like employeecopy2

Including column defaults
Excluding identity column attributes

If you want to import data, you generally execute statements similar to the following in the control center:

Create Table address2 like address
Insert into address2 select * From Address

 

 

Author: gnuhpc
Source: http://www.cnblogs.com/gnuhpc/