SELECT * FROM test_table;
SELECT 1=1 FROM DUAL; -- 在没有表被引用的情况下,允许您指定DUAL作为一个假的表名
SELECT * FROM test_table WHERE sid=1;
SELECT * FROM test_table WHERE age IS NULL;
SELECT * FROM test_table WHERE age IS NOT NULL;
SELECT ren_name,gender FROM test_table WHERE sid=2;
SELECT * FROM test_table WHERE gender='女' AND birth='2015-08-13';
SELECT * FROM test_table WHERE sid > 3;
SELECT * FROM test_table WHERE gender='女' OR sid < 3;
SELECT * FROM test_table WHERE ren_name LIKE '%s';
SELECT * FROM test_table WHERE ren_name LIKE '___s'; -- %通配(和*类似),_代表匹配任意一个字符
SELECT * FROM test_table WHERE ren_name LIKE 'a%';
SELECT * FROM test_table WHERE ren_name LIKE '%b%';
SELECT * FROM test_table ORDER BY birth;
SELECT * FROM test_table ORDER BY birth ASC; -- 升序
SELECT * FROM test_table ORDER BY birth DESC; -- 降序
SELECT * FROM test_table ORDER BY birth DESC,age;
SELECT * FROM test_table ORDER BY birth DESC,age ASC;
SELECT * FROM test_table ORDER BY birth DESC,age DESC;
SELECT gender,COUNT(gender) FROM test_table GROUP BY gender;
SELECT gender,COUNT(gender) FROM test_table GROUP BY gender HAVING gender='女'; -- having是分组之后再接条件的关键字
-- 上面写法效率低,不如以下语句
SELECT gender,COUNT(gender) FROM test_table WHERE gender='女' GROUP BY gender;
SELECT gender,COUNT(gender) FROM test_table WHERE gender='女';
SELECT COUNT(*) FROM test_table;
SELECT COUNT(sid) FROM test_table;
SELECT COUNT(sid) AS 总数 FROM test_table;
SELECT COUNT(sid) AS counts FROM test_table;
SELECT COUNT(sid) counts FROM test_table;
SELECT t.sid FROM test_table AS t;
SELECT t.sid,t.ren_name FROM test_table t;
SELECT * FROM test_table LIMIT 0,3;
-- select top 2 * from test_table; -- mysql没有top语句,ms sql server里面使用