旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

MySQL怎样完成单表查询?MySQL单表查询语句【MySQL教程】,MySQL,单表查询,查询语句

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:53评论:0


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

相干引荐:

mysql数据库图文教程

MySQL视频教程

bootstrap视频教程

以上就是MySQL怎样完成单表查询?MySQL单表查询语句的细致内容,更多请关注ki4网别的相干文章!

标签:MySQL单表查询查询语句


欢迎 发表评论: