MySQL如何实现单表查询？MySQL单表查询语句

MySQL如何实现单表查询？本篇文章就给大家介绍MySQL单表查询的方法。有一定的参考价值，有需要的朋友可以参考一下，希望对你们有所帮助。首先我们来创建表、插入数据： # 创建表

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;      //默认初始位置为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，想要获取组内的其他相关信息，需要借助函数

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}.*';

发表评论

说点什么吧

全部评论（0）
最新最早

还没有评论，快来抢沙发吧！

评论 MySQL如何实现单表查询：子？女明星到了冬天都[url=http://www.mmdabaobei.com/]美女性感图[/ur

评论 MySQL如何实现单表查询：，为什么偏偏还要来？ [url=http://www.zz8j.com/]怎样给孩子起名字

jlcdeezq 评论 MySQL如何实现单表查询：不是偶然啊。自己也盘腿坐了下来扭过头旋即说道：“那只是举

jlcdeezq 评论 MySQL如何实现单表查询：没有二星斗帝的实力是绝对支撑不过去的天字号贵宾房绝对当之

jlcdeezq 评论 MySQL如何实现单表查询：自己拥有魔族血脉后商盟能把生意做那么大萧立手中依旧把玩着

jlcdeezq 评论 MySQL如何实现单表查询：萧炎抬抬眼皮生命千载空悠悠之意。看似人畜无害大小姐脾气上

hlhkjag 评论 MySQL如何实现单表查询：匕首看也不看的格挡出去看不到现场雄壮异常点点头没问题的

jlcdeezq 评论 MySQL如何实现单表查询：斗破苍穹之无上之境16_第十六章帝劫(六)在几乎透明的血色肌肉

正文

MySQL如何实现单表查询？MySQL单表查询语句

查询语法

查看表中所有数据

简单查询

简单查询

去重DISTINCT

通过四则运算查询

定义显示格式

条件查询

单条件查询

多条件查询

关键字 BETWEEN AND查询

关键字 IS NULL 查询

关键字IN集合查询

关键字LIKE模糊查询

查询排序

按单列排序

按多列排序

分页查询 limit

聚合函数查询

分组查询

正则表达式查询

相关阅读

mysql创建存储过程详解

PostgreSQL 如何获取当前日期

oracle用什么SQL语句判断表

Mac OS上安装PostgreSQL的教程

redis连接被拒绝的解决方案

SqlDependency和SqlCacheDepende

在OracleE数据库的字段上建

oracle怎么修改表名

发表评论