MYSQL语句笔记2

查询链接：

http://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
http://www.w3resource.com/mysql/mysql-functions-and-operators.php

http://www.tutorialspoint.com/mysql/mysql-date-time-functions.htm

1.SELECT FROM  WHERE

SELECT user_guid, free_start_user
FROM users
WHERE free_start_user=1;

2.OPERATORS

/*
If you wanted to examine the Dog IDs of dogs who weighed between 10 and 50 pounds, you could query:
*/
SELECT dog_guid, weight
FROM dogs
WHERE weight BETWEEN 10 AND 50;

SELECT dog_guid, dog_fixed, dna_tested
FROM dogs
WHERE dog_fixed=1 OR dna_tested=1;

SELECT dog_guid, dog_fixed, dna_tested
FROM dogs
WHERE dog_fixed=1 AND dna_tested!=1;

3.字符串的使用需要加引号。

双引号或者单引号，单包含关键字的必须是 ``号。

SELECT dog_guid, breed
FROM dogs
WHERE breed='golden retriever';

/*
The IN operator allows you to specify multiple values in a WHERE clause.
*/

SELECT dog_guid, breed
FROM dogs
WHERE breed IN ("golden retriever","poodle");

LIKE  操作符。

SELECT dog_guid, breed
FROM dogs
WHERE breed LIKE ("s%");
/*
WHERE breed LIKE ("s%") = the breed must start with "s", but can have any number of letters after the "s"
WHERE breed LIKE ("%s") = the breed must end with "s", but can have any number of letters before the "s"
WHERE breed LIKE ("%s%") = the breed must contain an "s" somewhere in its name, but can have any number of letters before or after the "s
*/

4.时间操作符

/*
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
*/

SELECT dog_guid, created_at
FROM complete_tests
WHERE DAYNAME(created_at)="Tuesday"

/******************************/
SELECT dog_guid, created_at
FROM complete_tests
WHERE DAY(created_at) > 15

/***************************/
SELECT dog_guid, created_at
FROM complete_tests
WHERE created_at > '2014-02-04'

SELECT dog_guid, test_name, subcategory_name
FROM complete_tests
WHERE YEAR(created_at) = '2014' and MONTH(created_at)='10'

5.判断是否为NULL。

SELECT user_guid
FROM users
WHERE free_start_user IS NOT NULL;
/*****************************/

SELECT user_guid
FROM users
WHERE free_start_user IS NULL;