Shulou(Shulou.com)06/01 Report--

Create tablespace user1 datefile''size 20m / / create a tablespace named User1

Scalability of autoextend on// data files

Autoextend off// shuts down

Autoextend on next 5M// specifies the growth rate of the data file

Autoextend on next 5m maxsize 500M

/ / check whether the tablespace is created successfully

Select file_name,tablespace_name from dba_data_files order by file_name

/ / View tablespace

Select tablespace_name,status,allocation_type from dba_tablespaces

Select user_id,username,default_tablespace from dba_users// modifies the SQL statement of the database default tablespace

Alter tablespace default tablespace user1

Alter tablespace user2 rename to user20

Dorp tablespace user20 including contents and datafiles

/ / create a primary key constraint

Create table student (student_id number primary key,student_name vachar2 (20), student_birthday date,student_address vachar2 (50), student_phone vachar2 (20))

/ / create a foreign key

Create table customers (customer_id number primary key,customers_name vachar2 (50), customers_address vachar2 (50), customers_phone vachar2 (20), email vachar2 (20)

Create table orders (orders_id number primary key,customers_id number,goods_name vachar2 (20), quantity number,unit vachar2 (10))

Alter table orders add constraint fk_orders_customer foreign key (customer_id) referemces customers (customer_id)

/ / create a view

Create table employees1 (employee_id number primary key,first_name vachar2 (4), last_name vachar2 (4), province vachar2 (10), city vachar2 (10), salary number)

Select text from user_views where view_name='VW_EMPLOYEES'

Select * from VW_EMPLOYEES

/ / modify the view

Create or replace view VW_EMPLOYEES as

Select employee_id,last_name | | first_name as employee_name,province | |'-'| | city as location,salary from employees1

/ / Delete the view

Drop view VW_EMPLOYEES

/ / create an index

Create index idx orders (orders_id,goods_name)

Function

/ / intercept string

The first four characters of the fifth bit of select substr ('1234567890) from dual;// (' 1234567890)

/ / get the location where the string appears

Select instr ('big big tiger','big') from dual

Itrim () / / remove the first space of the string

Rtrim () / / remove the trailing space of the string

Current_timestamp () / / returns the current timestamp of the current session time zone

Current_times () / / returns the current date of the current session time zone

/ / the in criterion is used to determine whether the column values of the table exist in the list (collection). The exists criterion can be used to determine whether the query result set is empty.

/ / ranking

Select student_name,rank () over (orders by student_age) position from student

/ / if else

Set serverout on

Delete employee_num number

Begin

Select count (*) into employee_num from t_employees where status= "ACT"

If employees_num > 0 then

Dbms_output.put_line ('existing record')

Else

Dbms_output.put_line ('no record')

End if

End

/ / case when

Declare employee_num number

Begin

Select count (*) into employee_num from t_employees where status= "ACT"; / / store the number in the variable employee_num

Case

When employee_num > 1 then

Dbms_output.put_line ('multiple valid records')

When employee_num=1 then

Dbms_output.put_line ('only one valid record')

Else

Dbms_output.put_line ('no record')

End case

End

Create a storage process

Create or replace procedure update_students

Begin

Update student set student_age=10

Commit

End update_students

/ / execute the storage process

Excute update_students

IN OUT parameter

Create or replace procedure swap (in_out_param1 in out number,in_out_param2 in out number) as

Begin

Declare param number

Begin

Param:=in_out_param1

In_out_param1:=in_out_param2

In_out_param2:=param

End

End

Tree query

Query

Find duplicate data

Select distinct month from t_salary// obtains unique record

Order by / / ascending asc and descending desc permutation

Group by / / is used to group record collections

Having / / conditional filtering for data sources

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.