MySQL Base operation statement

★mysql Index

• ALTER TABLE to create a normal index, a unique index, or a primary key index

  ALTER TABLE table_name ADD index index_name (column_list)

  ALTER TABLE table_name add unique index_name (column_list)

  ALTER TABLE table_name ADD primary key index_name (column_list)

• CREATE index

  CREATE INDEX index_name on table_name (column_list)

  Create unique index index_name on table_name (column_list)

• Drop Index

  DROP INDEX index_name on table_name

• ALTER TABLE table_name DROP

  ALTER TABLE table_name DROP INDEX Index_name

  ALTER TABLE table_name DROP PRIMARY key

★mysql built-in functions

• String functions

  Concat (string1,string2) connection string

  LCase (string2) Convert to lowercase

  UCase (string2) converted to uppercase

  Length (string) string lengths

  LTrim (string2) Remove the preceding space

  RTrim (string2) Remove the trailing spaces

  Repeat (string2,count) repeating string string2 count times

  Replace (STR,SEARCH_STR,REPLACE_STR) replaces search_str with REPLACE_STR in str

  SUBSTRING (str,position,length) takes a length character from the position of STR

  Space (count) generates count of spaces

• Mathematical functions

  Bin (decimal_number) decimal conversion into binary

  Ceiling (num2) rounding up

  Floor (num2) rounding down

  Max (NUM1,NUM2) takes maximum value

  Min (num1,num2) Take minimum value

  sqrt (num2) Open square

  RAND () returns a random number within 0-1

• Date function

  Curdate () returns the current date

  Curtime () returns the current time

  Now () returns the current date time

  Week (date) returns the date of the week of the year

  Year (date) returns the date of day

  DATEDIFF (EXPR,EXPR2) returns the number of days between the start time expr and the end time expr

★mysql preprocessing statements

Set STMT1 preprocessing, passing a data as a where judgment condition

• Prepare STMT1 from "select * from T1 where id>? ';

• Set a variable

  Set @i=1;

• Performing STMT1 preprocessing

  Execute STMT1 using @i;

• Set the value of I to 5

  Set @i=5;

• Perform STMT1 preprocessing again
  

  Execute STMT1 using @i;

• Remove preprocessing STMT1

  Drop prepare STMT1;

★mysql Transaction Processing

• Set autocommit=0; Turn off the auto-commit feature

• Delete from T1 where id=1;

• SavePoint P1; Generate Restore Point P1

• Delete from T1 where id=2;

• SavePoint P2; Generate Restore Point P2

• Rollback to P1; reverts to P1, and subsequent restore points (such as P2) are automatically invalidated

• Rollback Back to the most original restore point

★mysql Storage

• Create a storage

\d//modifier Terminator delimiter

CREATE PROCEDURE P1 ()

Begin

Set @i=0;

While @i<10 do

Select @i;

Set @[email protected]+1;

End while;

End

//

• Execute storage p1

  Call P1 ();

• See the status information for procedure P1

  Show procedure Status\g

• See specific information about the procedure P1

  Show CREATE PROCEDURE P1\g

★mysql Trigger

• Create a trigger named TG1 that inserts the same data into the T2 while inserting data into T1

  Create Trigger TG1 before insert into T1 for each row

  Begin

  INSERT into T2 (ID) VALUES (new.id);

  End

• Create a trigger TG2, implement delete T1 table data t2 table corresponding data is also deleted

  Create Triggrt TG2 before Delete on T1 for each row

  Begin Delete T2 where id=old.id;

  End

• Create a trigger TG3 to update the data for the T1 table after the T2 table corresponding data is also updated
  

Create Triggrt TG2 before update on T1 for each row

Begin update T2 set id=new.id where id=old.id;

End

• View triggers

  Show triggers;

★ Restore MySQL Database auto-growth ID

• When emptying the table, you cannot use the DELETE from table_name instead of the TRUNCATE TABLE table_name, so the auto_increment is restored to 1.

And the Delete command is to delete the data row by line, while truncate is scanning all, the speed is more efficient than the delete faster

• Or, after emptying the contents, modify the table directly with the ALTER command: ALTER TABLE table_name auto_increment=1;
  

MySQL Base operation statement