Mysql database notes, mysql database

Mysql database notes, mysql database

/* Log on to the database */
Mysql-u root-p

Select curdate () as date;/* Add two dates */select date_add (curdate (), interval 1 month);/* Get month, likewise, the year, day */select month ('1970-10-30 ');/* modulo operation */select mod (2010 );

/* Remove n characters from the right of the string */
Select right ('stringing', 5 );

1. Pattern Matching:

Standard SQL mode matching:

Operator: like, not like

Match character: _ single character, % 0 or multiple characters

　　Extended SQL mode matching:

Operator: regexp, not regexp

Count () and group

Select *, count (*) from tableName [where] order by columnName [, columnName]

select * , count(*) from SC order by Sno;　

2. function SELECT function (column) [as someName] FROM table

SELECT MAX(article) AS article FROM shop;SELECT MIN(article) AS article FROM shop;SELECT AVG(article) AS article FROM shop;SELECT SUM(article) AS article FROM shop;SELECT COUNT([DISTINCT ]article) AS article FROM shop;SELECT COUNT(*) AS article FROM shop;

 

3. Restrict the LIMIT clause　　　　　　　　　　　　　　　　　　　　　　　　　

SELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 1;

 

4. User Variables

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Reference: MySQL5.1 reference manual Simplified Chinese version

Http://www.w3school.com.cn/ SQL/SQL _functions.asp