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 fieldHAVING 筛选ORDER BY fieldLIMIT 限制条数；查看表中所有数据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;去重DISTINCTmysql> SELECT post FROM employee5;mysql> SELECT DISTINCT postFROM 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、逻辑运算符并且或者非andornotd、模糊查询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;分页查询 limitmysql> 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}.*';以上就是MySQL怎么进行单表查询？单表查询的语句的详细内容，更多请关注小潘博客其它相关文章！