Summary of DDL,DML,DCL in Oracle __oracle

DML (Data Manipulation Language): Used to retrieve or modify data.
DML includes: SELECT: for retrieving data;
INSERT: For adding data to the database;
UPDATE: Used to modify existing data from the database
Delete: Used to delete data from the database.

DDL (data definition Language): Used to define the structure of data, such as creating, modifying, or deleting database objects.
DDL includes: DDL statements can be used to create users and rebuild database objects. Here is the DDL command:
CREATE TABLE: Creating tables
ALTER TABLE
drop TABLE: Deleting tables
CREATE INDEX
DROP INDEX

DCL (Data Control Language language): Defines permissions for database users.
DCL include:
ALTER PASSWORD
GRANT
REVOKE
CREATE synonym

One: DCL (Data Control Language)
1, the creation user test2, the password is also Test2 (remembers most has by the cent, the number end):
Create user test2 identified by Test2;
2, give Test2 authorization: Create session; (Allow users to login to Oracle):
Grant create session to Test2;
3, to the Test2 allocation of the right to create a table;
Grant CREATE table to test2;
4, to the TEST2 allocation of the use of table space permissions;
Grant unlimited tablespace to test2;

Two: DDL (data definition language exercises)
1. Create TABLE T_user (:sql>
UserID number is not null primary key,
Username varchar is not NULL,
Age Number (3),
Sex varchar (2),
Departid number is not NULL,
Foreign KEY (Departid) references T_depart (Departid)
);
★alter table Student Add primary KEY (UserID) The associated primary foreign key can also be created with alter.

2, delete the table:sql> drop tables T_depart;

3. Create sequence:
Create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 to increment by 1;

III: DML (Data manipulation language):
1. Insert Inserts SQL:
(1) INSERT into T_depart (departid,departname,createdate)
VALUES (1, ' marketing Department ', Sysdate);
(2) INSERT into t_user values (Seq_user.nextval, ' Ma Wentao ', 23, ' Male ');

2. Delete Deletes sql:
(1) Delete t_user; (It's awful, if you don't add a condition when you delete it, all the data in this table will be deleted.) )
(2) Delete t_user where UserID = 3;

3. Update sql:
(1) Update t_user set username = ' Fight Wei ', sex = ' male ';
(It's too scary.) The names of all row Records in the condition table are modified without the update. )
(2) Update t_user set username = ' Wentao ' where UserID = 7;
★ Summary: I find that the FROM keyword is not used in SQL statements that are added, deleted, and modified.

4. Select query SQL: Format-->select From where GROUP BY Having ORDER by;
(1) Query All users: SELECT * from T_user;
★ In Oracle This indicates that an alias cannot be added as a keyword
For example: SELECT * from T_user u;
SELECT * from T_user as u;

(2) Query the specified column: select Username,sex from T_user;

(3) As to the column with an alias display: Select Username as user name from T_user; (the AS keyword here can be omitted)

(4) distinct remove duplicate rows: SELECT distinct username from T_user;

(5) Using the operator: select age+10 from T_user; (Give everyone an age of 10 years)

(6) Connection string: Select ' Username: ' | | Username from T_user; (Oracle with | | Do the connection string operator)

(7) WHERE clause: SELECT * FROM t_user where username = ' baby ';

(8) between and in ... Between:
SELECT * from T_user where UserID between 9 and 10; This is also equivalent to the following SQL:
SELECT * from T_user where UserID >=9 and UserID <=10;

(9) An arbitrary value in the matching set: SELECT * from T_user where username in (' Ma Wentao ', ' baby ');

(a) Like fuzzy query:% matches 0 or more arbitrary strings, _ matches 1 arbitrary strings.

SELECT * FROM T_user where username like '% tao% ';

(one) null to determine that a column is empty: SELECT * from t_user where sex is null;
(here with IS, cannot use =, can be not NULL if you want to return a record that is not NULL)

Order by Sort: ASC: Ascending arrangement (can be omitted), DESC: Descending order
Ascending: Select U.userid,u.username from T_user u order by U.userid;
Descending: Select U.userid,u.username from T_user u order by u.userid Desc;

(13) System functions (processing a set of data, returning a value):
avg– average, count– statistic record number, max– maximum, min– minimum, sum– sum
<1> returns the smallest and largest user number: select min (userid), Max (userid) from T_user;
<2> returns the total number of records: SELECT COUNT (*) from T_user;
<3> returns the number of records in which a field is not empty: select count (Sex) from t_user;
<4> returns the number of records that are not empty and not duplicates: SELECT count (Distinct sex) from t_user;

Group BY grouping (no direct return * is grouped, often used with aggregate function count (age)):
<1> groups by name, and counts each group: SELECT COUNT (*), username from T_user Group by username;
<2> grouped by multiple fields: Select Username,age,count (*) from T_user GROUP by Username,age;
Group by has a principle that all columns following the Select Do not have columns that use aggregate functions and must appear after group by.

Having a filter group: Select username from T_user GROUP by username have count (*) > 2;

(16) subquery (the subquery itself can return only a single value):
<1> The subquery is placed behind the select and returned as one of the fields.
Select U.username (select D.departname from T_depart d where D.departid = U.departid) from T_user u;
(Return to the user and belong to the department, this neutron query in theory to execute the query first, but I feel oh, hey. )
The <2> subquery is placed from the back, as a temporary table.
SELECT * FROM (select Username,sex s t_user where departid=1)
where s = ' male ';
(This seed query should be executed first)
<3> The subquery is placed behind the where, as part of the condition.
SELECT * from t_user where Departid = (select Departid from t_depart where departname = ' Finance Department ');
(This seed query should also be executed first)

(17) union query (when n tables are connected, you need to n-1 a join condition):
<1> equivalent connection (inner connection): Select U.username,d.departname from T_user u,t_depart D
where U.departid = D.departid;
<2> External connection: that is, the record of not meet the conditions returned, with a + on the line,
The (+) operator means the other side of the record that does not meet the federated condition can be output. This feeling is not very common.
Select B.book_id,b.book_name from Book_info as b,book_click_num as C
where b.book_id = c.book_id (+);


Iv. Trigger Trigger: A block of code that is executed automatically when a particular event occurs.
These events include:
(1) DML statement (INSERT,UPDATE,DELETE): Before triggers an action before a DML statement is executed, triggering an action after the DML statement is executed.
(2) DDL statements (CREATE and ALTER)
(3) System events, such as Startup/Shutdown [Startup/shutdown], error [Errors]
(4) User events, such as login/exit [Logon/logoff]
★ Two special variable-->:new new record value,: Old keep original record value
Simple example: Create or Replace Trigger Update_depart_trigger