Query using MySQL data in Windows (iii)

First, sort

SELECT * FROM Employees order by name;

Character-Type default character order (that is, z is always greater than a), regardless of case, if you want to sort by encoded values (that is, lowercase is always greater than uppercase), use the

SELECT * FROM Employees order by binary name;

Second, take the first n record (n in this example is 3)

SELECT * FROM Employees

ORDER BY ID

Limit 3;

Three, date Time function

1. Get the current date

Select Curdate ();

2. Take the Year

Select year (' 20140909 ');

Select year (Curdate ());

3. Take the Month

Select month (' 20140909 ');

4. Date of collection

Select DayOfMonth (' 20140909 ');

5. Get the current date and time

Select Now ();

6. Date plus minus

Select Date_add (' 20140909 ', Interval 1 month);

Description

Interval N Unit

N: Positive number indicates plus, negative number means minus

Unit: Can be Year,month,day,hour,minute,second,week

Four, arithmetic operations

1. Modulo Operation 5%2

Select mod (5,2);

Five, pattern matching

1. Wildcard characters, using like or not

% All characters

_ One character

2. Regular expressions, using RegExp (rlike) or not regexp (not rlike)

. Match any single character

[] matches any character in square brackets

* Match 0 or more characters in front of it

{n} where n represents a number, the number of characters before it is repeated

^ Mode start

$ pattern End

SELECT * FROM employees where name RegExp ' ^b ' matches names starting with B

SELECT * FROM employees where name regexp binary ' ^b ' matches only lowercase letter B First Name

Vi. using User variables (in this case @name as variable names)

Select @name: =name from Employees where id=1;

Select @name;