MySQL学习笔记之三:单表查询(Select)和常用函数
李小艺(2014-03-31)
以下是一些典型的select语句,只涉及到单表查询!个人感觉看懂是容易的,但只有多敲这些SQL命令,才能真正转化为personal skill.
【查询数据典型命令】
SELECT num, name, age, sex, homeaddr FROM employee;
SELECT num, d_id, name
FROM employee
WHERE age<30
ORDER BY d_id DESC;
【1 查询指定记录】
SELECT * FROM employee WHERE d_id=1005;
【2 带IN的查询】
SELECT * FROM employee WHERE d_id IN (1004, 1008, 1109);
SELECT * FROM employee WHERE d_id NOT IN (1004, 1008, 1109);
【3 带BETWEEN AND的查询】
SELECT * FROM employee WHERE age BETWEEN 30 AND 40;
【4 带LIKE的查询】
SELECT * FROM employee WHERE name LIKE ‘Aric’;
SELECT * FROM employee WHERE name LIKE ‘Ari%’;
SELECT * FROM employee WHERE name LIKE ‘Ar_c’;
【5 查询NULL值】
SELECT * FROM work WHERE info IS NULL;
SELECT * FROM work WHERE info IS NOT NULL;
【6 带AND的多条件查询】
SELECT * FROM employee
WHERE d_id=10001 AND sex LIKE ‘man’ AND age<30;
SELECT * FROM employee
WHERE num IN (1,2,3) AND age BETWEEN 15 AND 25
AND homeaddr LIKE ‘%SHENZHEN%’;
【7 带OR的条件查询】
SELECT * FROM employee
WHERE d_id=10001 OR sex LIKE ‘man’;
SELECT * FROM employee
WHERE num IN (1,2,3) OR age BETWEEN 15 AND 25
OR homeaddr LIKE ‘%SHENZHEN%’;
【8 查询结果不重复】
SELECT DISTINCT d_id FROM employee;
【9 对查询结果排序】
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY d_id DESC , age ASC ;
【10 分组查询】
SELECT sex, COUNT(sex)
FROM employee
GROUP BY sex
HAVING COUNT(sex)>=3;
【11 LIMIT限制查询结果的数量】
SELECT * FROM employee LIMIT 4;
SELECT * FROM employee LIMIT 4, 10;
【12 COUNT()函数】
SELECT COUNT(*) FROM employee;
SELECT d_id, COUNT(*) FROM employee GROUP BY d_id;
【13 SUM()函数】
SELECT num, SUM(score) FROM grade WHERE num=10001;
SELECT num, SUM(score) FROM grade GROUP BY num;
【14 AVG()函数】
SELECT AVG(age) FROM employee;
SELECT course , AVG(score) FROM grade GROUP BY course;
【15 MAX()函数】
SELECT MAX(age) FROM employee;
SELECT course, MAX(score) FROM grade GROUP BY course;
【16 MIN()函数】
SELECT MIN(age) FROM employee;
SELECT course, MIN(score) FROM grade GROUP BY course;
下一节将记录多表连接查询的相关知识点。