MySQL怎么完成单表查询？MySQL单表查询语句

mysql> SELECT post FROM employee5; mysql> SELECT DISTINCT post FROM employee5; 注：不能部分使用DISTINCT，通常仅用于某一字段。

mysql> SELECT name, salary, salary*14 FROM employee5; mysql> SELECT name, salary, salary*14 AS Annual_salary FROM employee5; mysql> SELECT name, salary, salary*14 Annual_salary FROM employee5;

CONCAT() 函数用于连接字符串 mysql> SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;

a、语法 select * from 表名 where 条件 b、比较运算符 大于 小于 大于等于 小于等于 不等于 > < >= <= !=或<> c、逻辑运算符 并且 或者 非 and or notd、模糊查询 like % 表示任意多个任意字符 _ 表示一个任意字符 e、范围查询 in 表示在一个非连续的范围内 between...and... 表示在一个连续的范围内 f、空判断 判断空：is null 判断非空：is not null g、优先级 小括号,not 比较运算符， 逻辑运算符 and比or优先级高，如果同时出现并希望先选or，需要结合()来使用

mysql> SELECT name,post FROM employee5 WHERE post='hr';

mysql> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>5000;

mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000; mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL; mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL; mysql> SELECT name,job_description FROM employee5 WHERE job_description='';

mysql> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ; mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ; mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;

通配符’%’ mysql> SELECT * FROM employee5 WHERE name LIKE 'al%'; 通配符’_’ mysql> SELECT * FROM employee5 WHERE name LIKE 'al___';

mysql> SELECT * FROM employee5 ORDER BY salary; mysql> SELECT name, salary FROM employee5 ORDER BY salary ASC; mysql> SELECT name, salary FROM employee5 ORDER BY salary DESC;

mysql> SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC; # 先按入职时间，再按薪水排序 mysql> SELECT * FROM employee5 ORDER BY hire_date DESC, salary DESC; # 先按职位，再按薪水排序 mysql> SELECT * FROM employee5 ORDER BY post, salary DESC;

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0 mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5; mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始，共显示5条

a、count(*) 表示计算总行数，括号中可以写*和列名 b、max(列) 表示求此列的最大值 c、min(列) 表示求此列的最小值 d、sun(列) 表示求此列的和 e、avg(列) 表示求此列的平均值 mysql> SELECT COUNT(*) FROM employee5; mysql> SELECT COUNT(*) FROM employee5 WHERE dep_id=101; mysql> SELECT MAX(salary) FROM employee5; mysql> SELECT MIN(salary) FROM employee5; mysql> SELECT AVG(salary) FROM employee5; mysql> SELECT SUM(salary) FROM employee5; mysql> SELECT SUM(salary) FROM employee5 WHERE dep_id=101;

# 单独使用GROUP BY关键字分组 mysql> SELECT post FROM employee5 GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

# 按照id分组，并查看组内成员 mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;

# 按照dep_id 分组， 并计算组内成员工资总和 mysql> SELECT dep_id,SUM(salary) FROM employee5 GROUP BY dep_id; # 按照dep_id分组，并计算组内成员工资平均值 mysql> SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;

mysql> SELECT * FROM employee5 WHERE name REGEXP '^j'; mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]+.*'; mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]{2}.*';