MySQL入门很简单---MySQL的查询数据

1. SELECT 属性列表 FROM 表名或试图列表 [WHERE 条件表达式1] [GROUP BY 属性名1 [HAVING 条件表达式2]] [ORDER BY 属性名2 [ASC|DESC]] LIMIT

SELECT * FROM user;
SELECT * FROM user WHERE id=1000;
SELECT * FROM user WHERE id IN(1000,1001);
SELECT * FROM user WHERE age BETWEEN 15 AND 25;
SELECT * FROM user WHERE name LIKE '%dodo%';
SELECT * FROM user WHERE info IS NULL;
SELECT * FROM user WHERE id=1000 AND sex=1;
SELECT * FROM user WHERE ORDER BY age DESC;

2.分组查询

SELECT * FROM user GROUP BY sex;
SELECT sex,GROUP_CONCAT(name) FROM user GROUP BY sex;
SELECT sex,COUNT(sex) FROM user GROUP BY sex HAVING COUNT(sex) >= 3;
SELECT * FROM user GROUP BY gid,sex;
SELECT sex,COUNT(sex) FROM user GROUP BY sex WITH ROLLUP;

3.LIMIT COUNT SUM AVG MAX MIN

SELECT * FROM user LIMIT 10;
SELECT * FROM user LIMIT 100,10;
COUNT();
SUM();
AVG();
MAX();
MIN();

4.连接查询 内连接查询和外连接查询
SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON(表名1.属性名1=表名2.属性名2)

5.子查询：通过子查询，可以实现多表之间的查询。子查询中可能包括IN ,NOT IN,ANY,ALL,EXISTS和 NOT EXISTS

6.合并查询结果：合并操作使用UNION和UNION ALL关键词。使用UNION关键词时数据库系统会将所有的查询结果合并到一起，然后去除相同的记录。而UNION ALL关键字则只是简单的合并到一起.

7.使用正则表达式查询: 属性名 REGEXP '匹配方式'

//查询以特定字符或字符串开头的记录 ^
SELECT * FROM user WHERE name REGEXP '^L';

//查询以特定字符或字符串结尾的记录 $
SELECT * FROM user WHERE name REGEXP 'c$';

//用"."来替代字符串中任意一个字符
SELECT * FROM user WHERE name REGEXP '^L...y$';

//使用[]匹配字符串中任意一个字符
SELECT * FROM user WHERE name REGEXP '[ceo]';

//使用[^]匹配指定字符以外的字符
SELECT * FROM user WHERE name REGEXP '[^a-w0-9]';

//匹配指定字符串
SELECT * FROM user WHERE name REGEXP 'ic';
SELECT * FROM user WHERE name REGEXP 'ic|uc|ab';

//使用*和+来匹配多个字符
SELECT * FROM user WHERE name REGEXP 'a*c';
SELECT * FROM user WHERE name REGEXP 'a+c';

//使用{m}或者{m,n}来指定字符串连续出现的次数.
SELECT * FROM user name REGEXP 'ab{1,3}';

把事情办好的秘密就是行动。成功之路就是有条理思考之后的行动！行动！行动！