Basic common sense

Remove return result whitespace

Remove both sides: TRIM

Remove the left side: LTRIM

Remove the right side: RTRIM

Union

By default, Union automatically removes duplicate rows from the query result set. If you want to return ALL matching rows, use Union ALL. If the requirement is the latter, that is, you really need ALL of the matching rows for each condition to occur (including duplicate rows), then you must use Union ALL instead of WHERE.

When combining a query with a Union, only one Order By clause can be used, which must appear after the last SELECT statement. And it will be used to sort all results returned by all SELECT statements, not just the nearest rule.

Primary key and Null value

Only null-not allowed columns can be used for primary keys, and null-allowed columns cannot be used for primary keys.

Add/remove primary keys

Alter table add primary key({columnName});

Alter TABLE drop primary key({columnName});

Update the table

Alter TABLE {tableName} add {columnName};

Alter TABLE {tableName} drop column {columnName};

P.K.

Concatenate field

Oracle:||

MySQL:concat

Substr and Left/Right

Intercepts the left and right characters of a field

Oracle:substr

MySQL:Left/Right

Create/insert/query a field of the ‘YYYY-MM-DD HH:MM:SS’ time type

Example: the 2017-04-22 20:01:02

create

MySQL: create table time (id int not null,time datetime);

Oracle:create table timetable(id int not null,time2 date); MySQL > alter table name; MySQL > alter table name; MySQL = datetime; Oracle = date;

insert

MySQL：

insert into time values(1,'2017-04-22 20:01:02');

Oracle:

insert into timetable values(1,to_date('2017-04-22 20:01:02','yyyy-MM-dd hh24-mi-ss'));

Query The following uses the 2017 year as an example

MySQL：

select time from time where year(time) = '2017';

Oracle:

select time2 from timetable where to\_number(to_char(time2,'YYYY'))='2017';

The alias

MySQL: select test as test1 from test; (Convert test to alias test1 using as)

Oracle: select test test1 from test; (There is no as in Oracle, so you can define the alias directly after the original one.)

View the statement that creates the table

MySQL:

show create table {tableName};

Oracle:

A.pl /SQL Developer: table name -> Right click -> View -> View SQL

Select * from TEST tselect DBMS_metadata. get_DDl (‘TABLE’,{tableName}) from dual;

Rename the table name

MySQL：

rename table {tableName} to {newTableName};

Oracle:

rename {tableName} to {newTableName};

MySQL > rename MySQL > rename MySQL > rename MySQL > rename

A foreign key

MySQL: select * from table1 where cus_id = ‘key’; create table {table1} (id int not null primary key,cus_id int not null,foreign key(cus_id) references {table2}(cus_id);

MySQL also provides column-level constraints, but column-level foreign key constraints don’t really take effect. Mysql > set foreign key constraint (foreign key)

Oracle: create table {table1} (id int not null primary key,cus_id int not null references {table2}(cus_id); Interestingly, MySQL’s column-level constraints are written in a way that perfectly implements foreign key constraints in Oracle.