Mysql usage records

Mysql usage records

1. Automatic Acquisition of Mysql System Time:

Column Type display format
TIMESTAMP (14) YYYYMMDDHHMMSS
TIMESTAMP (12) YYMMDDHHMMSS
TIMESTAMP (10) YYMMDDHHMM
TIMESTAMP (8) YYYYMMDD
TIMESTAMP (6) YYMMDD
TIMESTAMP (4) YYMM
TIMESTAMP (2) YY


It will automatically mark your INSERT or UPDATE operations with the current date and time. If a table has multiple TIMESTAMP columns, 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 setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency. See appendix)

2. You explicitly set the TIMESTAMP column 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 (14) not null,
)

2. Mysql deletes and adds a primary key index:

Two primary keys of Mysql. Primary key and not null auto_incriment

(1) When a mysql table is created, when a primary key is added to a field and no auto-increment is added
When inserting data, you do not need to insert the primary key. mysql will automatically add 0, but the value is not filled in during the second insert operation. mysql database will still add 0 by default, leading to duplicate primary keys, this is not acceptable. When the primary key is defined, the primary key value must be filled in when the data is inserted.

(2) When creating a mysql table, add the primary key not null auto_increment to a field;
This is also a primary key. The growth starts with 1. This field does not need to be filled in. mysql database automatically fills in the value without the primary key.
Alter table tb add primary key (id );
Alter table tb change id int (10) not null auto_increment = 1;

(3) Delete the auto-increment primary key id
Delete auto-incrementing primary keys first
Alter table tb change id int (10); // Delete auto-Increment
Alter table tb drop primary key; // Delete the primary key

(4) add or delete an index

You can CREATE an INDEX when executing the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE.

1) Add an index

Alter table is used to create a common index, UNIQUE index, or 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)

Table_name is the name of the table to be indexed, and column_list indicates which columns are indexed. When multiple columns are indexed, they are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index 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 creating an index, you can specify whether the index can contain duplicate values. If not, the index should be created as a primary key or UNIQUE index. For single-column uniqueness indexes, this ensures that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is not repeated.

The primary key index is very similar to the UNIQUE index. In fact, the primary key index is only a UNIQUE index with the name PRIMARY. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name.

The following SQL statement adds the PRIMARY KEY index to the InodbTest table No.

Alter table InodbTest add primary key (No)

3) delete an 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 processed as a statement in alter table. The syntax is as follows.

Drop index index_name ON talbe_name

Alter table table_name drop index index_name

Alter table table_name DROP PRIMARY KEY

The first two statements are equivalent. The index index_name in table_name is deleted.

The first statement is only used to delete the primary key index. Because a table only has one primary key index, 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 deleted from the table, the index is affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

4) view Indexes

Mysql> show index from tblname;

3. Mysql stored procedures:

(1)

View the stored procedure in databases:

Show procedure status;

View the code for creating a stored procedure or function

Show create procedure proc_name;
Show create function func_name;

(2)

Create a stored procedure and insert 30 data records into the InodbTest table:

PS:

DELIMITER // is used to change the end key

Concat ('jk ', ID) is used to connect jk and numbers and convert to varchar

DELIMITER //create procedure test_insert997()BEGINdeclare ID int;set ID=1;WHILE(ID<=30) do INSERT INTO InodbTest(name) VALUES(concat('jk',ID));  SET ID=ID+1;end while;END // 

(3)

1. Create a stored procedure

1. Basic Syntax: create procedure sp_name ()
Begin
.........
End

2. parameter transfer

Ii. Call the Stored Procedure

1. Basic Syntax: call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

Iii. delete stored procedures

1. Basic Syntax:
Drop procedure sp_name //
2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

4. blocks, conditions, and loops

1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statement if condition then
Statement
Else
Statement
End if;

3. Loop statements
(1). while Loop <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KCjxpbWcgc3JjPQ = "http://www.bkjia.com/uploads/allimg/150408/04103W620-0.gif" alt = "\"> [label:] WHILE expression DO

Statements

End while [label];
(2) loop LOOP [label:] loop

Statements

End loop [label]; (3). repeat until LOOP [label:] REPEAT

Statements

UNTIL expression

End repeat [label];

Operators used in mysql stored procedures

Arithmetic Operators

+ Add SET var1 = 2 + 2; 4
-Subtract SET var2 = 3-2; 1
* Multiply by SET var3 = 3*2; 6
/Except SET var4 = 10/3; 3.3333
DIV Division SET var5 = 10 DIV 3; 3
% Modulo 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 is 5 BETWEEN two values BETWEEN 1 AND 10 True
Not between two values 5 not between 1 AND 10 False
IN the set, 5 IN (,) is False.
Not in is not in the set 5 not in (,) True
= Equal to 2 = 3 False
<> ,! = Not equal to 2 <> 3 False
<=> Strictly compare whether two NULL values are equal NULL <=> NULL True
LIKE simple pattern matching "Guy Harrison" LIKE "Guy %" True
REGEXP regular expression match "Guy Harrison" REGEXP "[Gg] reg" False
Is null 0 is null False
Is not null 0 is not null True

Common functions in mysq stored procedures, string operations, mathematics, and date and time

I. string type

CHARSET (str) // returns the string Character Set
CONCAT (string2 [,...]) // connection string
INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 is returned.
LCASE (string2) // converts it to lowercase
LEFT (string2, length) // take the length from the LEFT of string2
LENGTH (string) // string LENGTH
LOAD_FILE (file_name) // read content from the file
LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.
LPAD (string2, length, pad) // repeat pad to start with string until the string length is length
LTRIM (string2) // remove leading Spaces
REPEAT (string2, count) // REPEAT count times
REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str
RPAD (string2, length, pad) // use pad after str until the length is length.
RTRIM (string2) // remove backend Spaces
STRCMP (string1, string2) // compare the size of two strings by character,
SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,
Note: When processing strings in mysql, the default subscript of the first character is 1, that is, the position parameter must be greater than or equal to 1 mysql> select substring ('abcd );
+ -------- +
"Substring ('abc',) |
+ -------- +
|
+ -------- +
1 row in set (0.00 sec) mysql> select substring ('abc );
+ -------- +
| Substring ('abcd', 1, 2) |
+ -------- +
| AB |
+ -------- +
1 row in set (0.02 sec)

TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character FROM the specified position
UCASE (string2) // converts to uppercase
RIGHT (string2, length) // gets the last length character of string2
SPACE (count) // generate count Spaces

Ii. Mathematics

ABS (number2) // absolute value
BIN (decimal_number) // convert decimal to binary
CEILING (number2) // rounded up
CONV (number2, from_base, to_base) // hexadecimal conversion
FLOOR (number2) // round down
FORMAT (number, decimal_places) // number of reserved decimal places
HEX (DecimalNumber) // convert to hexadecimal
Note: HEX () can input a string, returns its ASC-11 code, such as HEX ('def ') returns 4142143
You can also input a decimal integer to return its hexadecimal encoding. For example, HEX (25) returns 19.
LEAST (number, number2 [,...]) // calculates the minimum value.
MOD (numerator, denominator) // evaluate the remainder
POWER (number, power) // Exponent
RAND ([seed]) // Random Number
ROUND (number [, decimals]) // rounding, decimals is decimal places] Note: The return type is not an integer, for example:
(1) The default value is integer.
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) the number of decimal places can be set to return floating point data.
Mysql> select round (1.567, 2 );
+ ------ +
| Round (1.567, 2) |
+ ------ +
| 1, 1.57 |
+ ------ +
1 row in set (0.00 sec)

SIGN (number2) // return SIGN, positive and negative or 0
SQRT (number2) // Square

Iii. Date and Time

ADDTIME (date2, time_interval) // Add time_interval to date2
CONVERT_TZ (datetime2, fromTZ, toTZ) // convert the time zone
CURRENT_DATE () // current date
CURRENT_TIME () // current time
CURRENT_TIMESTAMP () // current Timestamp
DATE (datetime) // return the DATE part of datetime
DATE_ADD (date2, INTERVAL d_value d_type) // Add a date or time in date2
DATE_FORMAT (datetime, FormatCodes) // display datetime in formatcodes format
DATE_SUB (date2, INTERVAL d_value d_type) // subtract a time from date2
DATEDIFF (date1, date2) // two date differences
DAY (date) // returns the DAY of the date
DAYNAME (date) // english week
DAYOFWEEK (date) // Week (1-7), 1 is Sunday
DAYOFYEAR (date) // The day of the year
EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM date
MAKEDATE (year, day) // specifies the day of the year and year to generate a date string.
MAKETIME (hour, minute, second) // generate a time string
MONTHNAME (date) // name of the English month
NOW () // current time
SEC_TO_TIME (seconds) // converts seconds to time
STR_TO_DATE (string, format) // convert string to time, which is displayed in format
TIMEDIFF (datetime1, datetime2) // two time difference
TIME_TO_SEC (time) // time to seconds]
WEEK (date_time [, start_of_week]) // WEEK
YEAR (datetime) // YEAR
DAYOFMONTH (datetime) // The day of the month
HOUR (datetime) // HOUR
LAST_DAY (date) // the last date of the Month of date
MICROSECOND (datetime) // MICROSECOND
MONTH (datetime) // MONTH
MINUTE (datetime) // specifies the type available in INTERVAL.
DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND, MINUTE, MINUTE_SECOND, MONTH, SECOND, YEAR