Mysql light speed tutorial _ MySQL

Mysql light speed tutorial bitsCN.com

// By redice 2010.07.26
// Redice@163.com

The MYSQL learning outline for the company intern summarizes the most useful knowledge points in project development and can be used as a MYSQL Quick Start tutorial.

1 MYSQL column (field) data type:

(1) string

VARCHAR variable-length string up to 255 characters
It is applicable to strings with a short length (less than 255), such as user name, password, phone number, email address, and address.

TEXT strings with no maximum length limit
Suitable for storing large (more than 255) strings such as article content, product descriptions, and user messages

(2) integer

TINYINT can contain up to three digits.
SMALLINT can accommodate up to five digits
MEDIUMINT can accommodate up to eight digits
INT can contain ten digits
BIGINT can accommodate up to 20 digits

(3) decimal class

DECIMAL is suitable for storing DECIMAL numbers, such as product prices and sales.

DECIMAL (5 + 3, 3) or DECIMAL (8, 3) indicates that the integer part can be at most 5 digits, and the DECIMAL part can be 3 digits.

(4) time and date

DATE in yyyy-mm-dd format
TIME in hh: mm: ss format
DATETIME combines the date and time in the format of yyyy-mm-ddhh: mm: ss. the supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59. it is stored in 8 bytes, time zone conversion (change the time zone and the display will not change)
TIMESTAMP combines the date and time in the format of yyyy-mm-ddhh: mm: ss. The value cannot be stored before 1970 or later than 2037,4 bytes, and is irrelevant to the time zone (change the time zone and display automatic adjustment)

Note: the time and date classes can be viewed as special string classes.

2. common MYSQL built-in functions

(1) character processing class

ASCII (str) returns the ASCII value of the first character of the string str (0 is returned when str is an empty string)
CHAR (N ,...) returns the value of N ,... A string consisting of the corresponding ASCII code characters (the parameter is N ,... is a numerical sequence, and the NULL value is skipped)
CONCAT (str1, str2,...) concatenates the parameter into a long string and returns (if any parameter is NULL, NULL is returned)
REPLACE (str, from_str, to_str) replaces the child string from_str with the to_str string and returns
UCASE (str) or UPPER (str) returns an uppercase String str
CHAR_LENGTH (str) returns the number of characters in the str string (only once for multi-byte characters)
LENGTH (str) or OCTET_LENGTH (str) returns the number of bytes of the str string (calculated by the number of bytes for multi-byte characters)

(2) time and date

CURDATE () returns the current date value in YYYY-MM-DD format, returns the string value
CURTIME () returns the current time value in HH: MM: SS format (the context of the returned value is a string or number), and returns the string value.
NOW () returns the current date and time in YYYY-MM-DD HH: MM: SS format, returns the string value
UNIX_TIMESTAMP (datetimestr) returns a Unix timestamp (the number of seconds since 00: 00: 00GMT, and datetimestr defaults to the current time) and returns an integer.
FROM_UNIXTIME (unix_timestamp) returns the timestamp value in YYYY-MM-DD HH: MM: SS format, returns the string value

Tip: to accelerate the query speed, large systems usually store the timestamp value of time and date in INT (10) format.

3. SQL statement

In SQL statements, single quotes enclose text (string) values. for example, the redice text contains single quotes that need to be escaped, for example, redices blog

Do not use quotation marks for numeric values.

Time and date types are treated by text (string)

(1) SELECT

SELECT column name (field name) FROM table name WHERE query condition

Aggregate functions:
AVG (column), COUNT (column), MAX (column), MIN (column), SUM (column)

(2) DELETE

Delete from table name WHERE query condition

(3) INSERT

Insert into table name (Column 1, column 2,...) VALUES (value 1, value 2 ,....)

For example:
Insert into user (name, pass, age, regtime) values (redice, 123456,24, now ())

(4) UPDATE

UPDATE table name SET column 1 = value 1, column 2 = value 2,... WHERE query condition

This article reference part of w3school article: asp "> http://www.w3school.com.cn/ SQL /index.asp

BitsCN.com