最新公告
  • 欢迎您光临旗鱼小站,本站秉承服务宗旨 履行“站长”责任,销售只是起点 服务永无止境!立即加入我们
  • 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        not  d、模糊查询     like    %           表示任意多个任意字符     _           表示一个任意字符  e、范围查询    in                  表示在一个非连续的范围内     between...and...    表示在一个连续的范围内  f、空判断     判断空:is null     判断非空:is not null   g、优先级     小括号,not 比较运算符, 逻辑运算符     andor优先级高,如果同时出现并希望先选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条

    聚合函数查询

        acount(*)       表示计算总行数,括号中可以写*和列名     bmax(列)        表示求此列的最大值     cmin(列)        表示求此列的最小值     dsun(列)        表示求此列的和     eavg(列)        表示求此列的平均值      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怎么进行单表查询?单表查询的语句

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。

    发表评论

    售后服务:

    • 售后服务范围 1、商业模板使用范围内问题免费咨询
      2、源码安装、模板安装(一般 ¥50-300)服务答疑仅限SVIP用户
      3、单价超过200元的模板免费一次安装,需提供服务器信息。
      付费增值服务 1、提供dedecms模板、WordPress主题、discuz模板优化等服务请详询在线客服
      2、承接 WordPress、DedeCMS、Discuz 等系统建站、仿站、开发、定制等服务
      3、服务器环境配置(一般 ¥50-300)
      4、网站中毒处理(需额外付费,500元/次/质保三个月)
      售后服务时间 周一至周日(法定节假日除外) 9:00-23:00
      免责声明 本站所提供的模板(主题/插件)等资源仅供学习交流,若使用商业用途,请购买正版授权,否则产生的一切后果将由下载用户自行承担,有部分资源为网上收集或仿制而来,若模板侵犯了您的合法权益,请来信通知我们(Email: 1262500034@qq.com),我们会及时删除,给您带来的不便,我们深表歉意!

    Hi, 如果你对这款模板有疑问,可以跟我联系哦!

    联系作者
    • 1055会员总数(位)
    • 2027资源总数(个)
    • 15本周发布(个)
    • 0 今日发布(个)
    • 212稳定运行(天)

    提供最优质的资源集合

    免费资源 精品源码