Oracle SQL basics (I) Statements and functions DCL DDL DQL

Create Table abc
Create table abc (a varchar2 (10), bvarchar (10 ));

Add a column
Alter table abc add c number;

Delete a column
Alter table abc drop column c;

Delete table
Drop abc;

Data Control Language,
Grant select permission
Grant select on [tablename] to [user]

Revoke select permission:
Revoke select on [tablename] from [user]

Insert
Insert into abc (a, B) values ('aaa', 'bbb ');

Modify
Update abc set B = 'ttt' where a = 'aaa ';

Delete
Delete from abc where a = 'aaa ';

Function:
Common system functions
Select length ('abcdef ') from dual; character, if it is a Chinese character, it is still the original number of characters
Select lengthb ('abcdef') from dual; one more byte if it is a Chinese character

Truncates null characters.
Select ltrim ('aaa') from dual; truncates spaces on the left
Select rtrim ('A') from dual truncates spaces on the right
Select trim ('aaa') from dual truncates all space characters

Varchar2 and char are variable. The latter is an unchangeable length.

Extract some characters in the string. Start from 2 and start from 3.
Select substr ('abcder', 2, 3) from dual;

Write the last three digits
Select substr ('abcde', length ('abcde')-3 + 1) from dual;

Get current time
Select sysdate from dual;

Set the current time format
Alter session set nls_date_format = 'dd-mon-yyyyhh: mi: ss ';

Query current date
Select current_date from dual;

The next Wednesday of the current date is that day
Select next_day (sysdate, 'weday') from dual;
[SQL] view plaincopy
Conversion functions
Convert to character
Select to_char (sysdate, 'yyyy-mm-ddhh24: mi: ss') from dual;

Convert to date
Select to_date ('12-March-09 ') from dual

Convert to number
Select to_number ('123') from dual;
View the maximum value
Select max (price) from books
Minimum value
Select min (price) from books

Sum
Select sum (price) from books
Average
Select avg (price) from books
Number of fields
Select count (price) from books
Query the number of records in a table
Select count (*) from dual;
Aggregate functions cannot be combined with where
Current Login
Select user from dual;
Decode Function
Decode (sex, 'male' 1, 0) when the gender is male, the base is + 1. If the gender is not male, the data is cleared. Then the field is
Show the number of men
Selectsum (decode (sex. 'male',) Number of men, sum (decode (sex, 'female ',) Number of women from tablename
Replace null values with those not listed
Selecta1, nvl (a2, 'Unspecified ') label from dual;
Empty a2
Select * from aa where a2 is null;
Ascending and descending
Select * from aa order by a1 asc; L
Select * from aa order by a1 desc drop

Remove duplicate data
Selectdistinct a1 from aa;