mysql如何实现单表查询？本篇文章就给大家介绍mysql单表查询的方法。有一定的参考价值，有需要的朋友可以参考一下，希望对你们有所帮助。
首先我们来创建表、插入数据：
# 创建表mysql> create table company.employee5( id int primary key AUTO_INCREMENT not null, name varchar(30) not null, sex enum('male','female') default 'male' not null, hire_date date not null, post varchar(50) not null, job_description varchar(100), salary double(15,2) not null, office int, dep_id int );# 插入数据mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('tianyun','male','20180202','hr','hrcc',600,502,101), ('harry','male','20180202','hr',NULL,6000,502,101), ('emma','female','20180206','sale','salecc',20000,503,102), ('christine','female','20180205','sale','salecc',2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200,503,102), ('gougou','male','20180205','sale','',2200,503,102);# 查看表结构mysql> desc employee5; +-----------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(30) | NO | | NULL | || sex | enum('male','female') | NO | | male | || hire_date | date | NO | | NULL | || post | varchar(50) | NO | | NULL | || job_description | varchar(100) | YES | | NULL | || salary | double(15,2) | NO | | NULL | || office | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+-----------------+-----------------------+------+-----+---------+----------------+
查询语法
SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数；
查看表中所有数据
mysql> select * from employee5;+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+| id | name | sex | hire_date | post | job_description | salary | office | dep_id |+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 || 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 || 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 || 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 || 5 | tianyun | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 || 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 || 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 || 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 || 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 || 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+10 rows in set (0.00 sec)
简单查询
简单查询
mysql> SELECT * FROM employee5;mysql> SELECT name, salary, dep_id FROM employee5;
去重DISTINCT
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;
关键字 BETWEEN AND查询
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;
关键字 IS NULL 查询
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='';
关键字IN集合查询
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) ;
关键字LIKE模糊查询
通配符’%’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;
分页查询 limit
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0mysql> 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，想要获取组内的其他相关信息，需要借助函数
GROUP BY关键字和group_concat()函数一起使用
# 按照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;
GROUP BY和集合函数一起使用
# 按照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}.*';
相关推荐：
表单大师AI
一款基于自然语言处理技术的智能在线表单创建工具，可以帮助用户快速、高效地生成各类专业表单。
74 查看详情
mysql数据库图文教程
MySQL视频教程
bootstrap视频教程
以上就是MySQL如何实现单表查询？MySQL单表查询语句的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/381274.html
微信扫一扫 
支付宝扫一扫 
