SQLite and MySQL common knowledge points

First, SQLite:

1. Command Line annotator --

2. Paging Query
Select * from messages limit 10,100;
This indicates that 10 rows are skipped and the returned results of 100 rows are taken.

3. Set the default Field Value
Create Table T2 (ID integer default 0, time datetime default (strftime ('% Y-% m-% d % H: % m: % s ', 'Now ')));
Or create table T2 (ID integer default 0, time datetime default (datetime ()));

4. connection string Connection Symbol |

Example:
Update message set content = content | 'abc ';

5. federated primary keys
Create Table Test (col1 char (2), col2 char (2), primary key (col1, col2 ));

6. Ignore (replace) This statement if the primary key already exists during data insertion.
Insert or ignore (replace) into user values ('abc ');

7. Create a trigger to automatically update the last_update_time of the table:
Create trigger tr_test Before update on test for each row begin update Test Set gtime = datetime () Where id = new. ID; end;

MySQL:

1. Command Line annotator #

2. Paging Query
Select * from messages limit 10,100;
This indicates that 10 rows are skipped and the returned results of 100 rows are taken.

3. The default table Creation Time column is the current time.
Create Table mtable (my_time timestamp default current_timestamp );

4. connection string connection function Concat

Example:
Update message set content = content | 'abc ';

5. federated primary keys
Create Table Test (col1 char (2), col2 char (2), primary key (col1, col2 ));

6. Ignore (replace) This statement if the primary key already exists during data insertion.
Insert or ignore (replace) into user values ('abc ');

7. The fields are case sensitive during table creation.

Column_name varchar (20) Character Set utf8 collate utf8_bin

8. Use variables for table names
/* ----- Create a stored procedure: sp_auto_create -------*/
Delimiter ;;
Create procedure sp_auto_create (in tname char (32 ))
Begin
Set @ stmt = Concat ('create table', tname, '(ID int, name varchar (10 ));');
Prepare S1 from @ stmt;
Execute S1;
End ;;
Delimiter;
/* ----- Execute the stored procedure: sp_auto_create to automatically generate the table test -------*/
Call sp_auto_create ('test ');