Some of the records in MySQL use

1, MySQL system time automatic acquisition:

     column type           display format   
TIMESTAMP (+)   yyyymmddhhmmss  
TIMESTAMP (  yymmddhhmmss  );
TIMESTAMP (Ten)   yymmddhhmm  
TIMESTAMP (8)    yyyymmdd  
TIMESTAMP (6)    yymmdd  
TIMESTAMP (4)    yymm  
TIMESTAMP (2)    yy  


It automatically marks the operation of your insert or update with the current date and time. If there is more than one timestamp column in a table, only the first one is automatically updated.

In the following cases, MySQL automatically updates the first timestamp column as the current system time

1, when an INSERT, update, or Load data infile statement does not explicitly specify its value.  

(Note that an update setting has a value listed as it already exists, which will not cause the timestamp column to be updated, because if you set a list as its current value, MySQL ignores the change for efficiency.) See bylaw)  

2, you explicitly set timestamp as null. 

3, the timestamp column except the first one can also be set to the current date and time, as long as the column is set to NULL, or now ().

Example:

create table Test (
  Datetime timestamp () not NULL,
)

2. mysql Delete add primary key index:

Two primary keys for MySQL. Primary key and NOT null Auto_incriment

(1) When a MySQL table is created, a primary key is added to a field primary key is not added since the growth
The insert data can be used without the insert primary key, MySQL will automatically add 0, but the second insert is not filled in the value of the MySQL database or add 0 by default, will cause a duplicate primary key, which is not possible. All when the primary key is defined, the primary key is filled with the value when the insert data is entered.

(2) When a MySQL table is created, a primary key not NULL auto_increment is added to a field;
This is also a primary key. The time since the growth starts with 1. This field can not fill in the value, MySQL database will automatically fill in the value, will not appear primary key status.
Alter table TB Add primary key (ID);
Alter table TB change ID ID int (TEN) not null auto_increment=1;

(3) Delete the self-growing primary key ID
Remove the primary key from the growth first
Alter table TB change ID ID int (10);//delete self-growth
Alter table TB Drop primary key;//Delete main build

(4) Add Delete index

You can create an index when you execute the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone.

1) Add index

ALTER table is used 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 (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Where table_name is the name of the table to increase the index, column_list indicates which columns to index, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name based on the first indexed column. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

2) Index Type

When you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a primary key or a unique index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.

The PRIMARY key index is very similar to a unique index. In fact, the PRIMARY key index is only a unique index with the name PRIMARY. This means that a table can contain only one primary KEY because it is not possible to have two indexes with the same name in a table.

The following SQL statement adds a primary key index to the Inodbtest table on No.

ALTER TABLE inodbtest ADD PRIMARY KEY (No)

3) Delete Index

You can use the ALTER TABLE or DROP INDEX statement to delete an index. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement inside ALTER TABLE, with the following syntax.

DROP INDEX index_name on Talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

Where the first two statements are equivalent, delete the index index_name in table_name.

The 3rd statement is only used when deleting the primary key index, because a table may have only one primary key index, so you do not need to specify the index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.

If a column is removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.

4) View Index

Mysql> Show index from Tblname;

3. mysql uses stored procedures:

(1)

To view stored procedures in databases:

Show procedure status;

To view the creation code for a stored procedure or function

Show CREATE PROCEDURE Proc_name;
Show Create function Func_name;

(2)

Create a stored procedure to insert 30 data into the Inodbtest table:

Ps:

DELIMITER//is used to change the end key

Concat (' JK ', ID) is used to connect JK and digital, converted to varchar type

DELIMITER//create Procedure test_insert997 () Begindeclare ID int;set id=1; while (id<=30) does INSERT into Inodbtest (name) VALUES (concat (' JK ', ID));  

(3)

I. Creating a stored Procedure

1. Basic syntax: CREATE PROCEDURE Sp_name ()
Begin
.........
End

2. Parameter passing

Two. Calling a stored procedure

1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

Three. Delete a stored procedure

1. Basic syntax:
drop procedure sp_name//
2. Precautions
(1) cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

Four. blocks, conditions, loops

1. Block definition, commonly used
Begin
......
End
You can also alias chunks, such as:
Lable:begin
...........
End lable;
You can use leave lable, jump out of chunks, execute code after chunk
2. Conditional statementsifconditions Then
Statement
Else
Statement
End if;

3. Looping statements
(1). While loop

[Label:]  whileexpression do

Statements

END  while [label] ;
(2). Loop loop[Label:]LOOP

Statements

ENDLOOP[label];(3). Repeat until cycle[Label:]REPEAT

Statements

UNTIL expression

ENDREPEAT[label] ;

Operators to use in MySQL stored procedures

Arithmetic operators

+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* Multiply SET var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulus SET var6=10%3; 1

Comparison operators

> Greater than 1>2 False
< less than 2<1 False
<= less than or equal to 2<=2 True
>= greater than or equal to 3>=2 True
Between between two values 5 between 1 and True
Not between is not between two values 5 not between 1 and False
In Set 5 in (1,2,3,4) False
Not in the collection 5 not in (1,2,3,4) True
= equals 2=3 False
<>! = is not equal to 2<>3 False
<=> strictly compares two NULL values for equality Null<=>null True
Like simple pattern matches "guy Harrison" like "guy%" True
REGEXP regular Match "Guy Harrison" REGEXP "[Gg]reg" False
Is null 0 is null False
Is isn't null 0 is not NULL True

MYSQ functions commonly used in stored procedures, String type operations, math classes, date-time classes

A. String Class

CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//returns the position where substring first appeared in string, no return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length of the string from string2
Length (String)//string
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position
Lpad (string2, length, pad)//repeat pad to start with string until string length
LTRIM (string2)//Remove front-end spaces
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str
Rpad (string2, length, pad)//after STR with pad supplement until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//starting with the position of STR, taking a length character,
Note: When working with strings in MySQL, the default first character subscript is 1, i.e. the parameter position must be greater than or equal to 1mysql> select substring (' ABCD ', 0,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 0,2) |
+ ——————— –+
| |
+ ——————— –+
1 row in Set (0.00 sec) mysql> Select substring (' ABCD ');
+ ——————— –+
| SUBSTRING (' ABCD ', up) |
+ ——————— –+
| AB |
+ ——————— –+
1 row in Set (0.02 sec)

TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Take string2 last length character
Space (count)//Generate Count of spaces

Two. Math class

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal into binary
CEILING (NUMBER2)//Up rounding
CONV (number2,from_base,to_base)//Binary conversion
Floor (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//number of decimal digits reserved
Hex (Decimalnumber)//Turn hex
Note: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) to return 19
LEAST (number, number2 [,..])//Find minimum
MOD (numerator, denominator)//redundancy
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//round, decimals is a decimal place] Note: Return types are not all integers, such as:
(1) The default becomes an integer value
Mysql> Select round (1.23);
+ ————-+
| Round (1.23) |
+ ————-+
| 1 |
+ ————-+
1 row in Set (0.00 sec)

Mysql> Select round (1.56);
+ ————-+
| Round (1.56) |
+ ————-+
| 2 |
+ ————-+
1 row in Set (0.00 sec)

(2) You can set the number of decimal digits, return the floating-point data
Mysql> Select round (1.567,2);
+ —————-+
| Round (1.567,2) |
+ —————-+
| 1.57 |
+ —————-+
1 row in Set (0.00 sec)

Sign (NUMBER2)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

Three. Date Time class

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//current timestamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time to Date2
Date_format (datetime, Formatcodes)//Use formatcodes format to display datetime
Date_sub (Date2, INTERVAL d_value d_type)//Subtract one time from Date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)/days of return date
Dayname (date)//English Week
DAYOFWEEK (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//Extract the specified part of the date
Makedate (year, day)//gives the first days of the years and years, generating a date string
Maketime (hour, minute, second)//Generate time string
MONTHNAME (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//seconds turn into time
Str_to_date (string, format)//string turns into time, displayed in format
Timediff (datetime1, datetime2)//Two time difference
Time_to_sec (time)//times to seconds]
WEEK (Date_time [, Start_of_week])//weeks
Year (DateTime)//Years
DayOfMonth (DateTime)/day of the month
HOUR (DateTime)//hour
Last_day (date)//date The last date of the month
Microsecond (DateTime)//microseconds
Month (datetime)//month
MINUTE (DateTime)//Sub-attachment: Types available in Interval
Day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second, MINUTE, Minute_second,month, SECOND, year

Some of the records in MySQL use