MySQL——5.DQL 数据查询语言



DQL------------------------------------------------------------------------------------------

查询:select

可以查询一些系统函数,或是直接将mysql当做一个简单的计算器。

常用: version()

current_date

user()

(4+1)*5 //当做计算器

Hello,world //打印字符串

 

查询数据:

select [all|distinct] *字段名|表达式  /*要查询的列名称*/

from  表名  /*要查询的表名称*/

where  条件表达式  /*查询条件*/

group by 字段名  /*对结果分组*/

having 条件表达式  /*对结果筛选*/

order by 字段名|表达式 asc|desc  /*对结果排序*/

limit  [偏移值,] 显示行  /*结果限定*/

 

1 基础查询

1.1 查询所有列

SELECT * FROM stu;

 

1.2 查询指定列

SELECT sid, sname, age FROM stu;

 

2 条件查询

SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号)。如果是数值,请不要使用引号。

  • =!=<><<=>>=

  • BETWEEN…AND WHERE 列名 BETWEEN 1 AND 2 (包含值1,值2)

  • IN(set)

  • IS NULL

  • AND WHERE 条件 AND 条件

  • OR WHERE 条件 OR 条件

  • NOT

     

    2.2 查询性别为女,并且年龄小于50的记录

      SELECT * FROM stu WHERE gender='female' AND age<50;

     

    2.3 查询学号为S_1001或者姓名为liSi的记录

    SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

     

    2.4 查询学号为S_1001S_1002S_1003的记录

    SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

     

    2.5 查询学号不是S_1001S_1002S_1003的记录

    SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

     

    2.6 查询年龄null的记录

    SELECT * FROM stu WHERE age IS NULL;

     

    2.7 查询年龄2040之间的学生记录

    SELECT * FROM stu WHERE age>=20 AND age<=40;

    或者SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

     

    2.8 查询性别男的学生记录

    SELECT * FROM stu WHERE gender!='male';

    或者SELECT * FROM stu WHERE gender<>'male';

    或者SELECT * FROM stu WHERE NOT gender='male';

     

    2.9 查询姓名不为null的学生记录

    SELECT * FROM stu WHERE NOT sname IS NULL;

    或者SELECT * FROM stu WHERE sname IS NOT NULL;

     

    3 模糊查询

    当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE

    通配符: 可以替代一个或多个字符。必须与LIKE一起使用。

    % 匹配任意数目字符

    _ 匹配任何单个字符

    扩展正则表达式:使用REGEXPNOT REGEXP (RLIKENOT RLIKE,他们是同义词)

    . 匹配任何单个字符,同_

    [charlist] 匹配在字符列中的任何单一字符

    [a-z] 匹配任何字母

    [0-9] 匹配任何数字

    [^charlist][!charlist] 匹配不在字符列中的任何单一字符

    * 匹配任意数目在它前面的字符

    {n} 重复n

     

     

    3.1 查询姓名由5个字母构成的学生记录

    SELECT * FROM stu WHERE sname LIKE '_____';

    模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母,5个“_”表示5个任意字母。

     

    3.2 查询姓名由5个字母构成,并且第5个字母为“u”的学生记录

    SELECT * FROM stu WHERE sname LIKE '____i';

     

    3.3 查询姓名以“z”开头的学生记录

    SELECT * FROM stu WHERE sname LIKE 'z%';

    其中%”匹配0~n个任何字母。

     

    3.4 查询姓名中第2个字母为“i”的学生记录

    SELECT * FROM stu WHERE sname LIKE '_i%';

     

    3.5 查询姓名中包含“a”字母的学生记录

    SELECT * FROM stu WHERE sname LIKE '%a%';

     

    3.6 查询姓名以A”或“L”或“N”开头的学生记录:

    SELECT * FROM stu WHERE sname LIKE '[ALN]%';

     

    4 字段控制查询

    4.1 去除重复记录

    去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT

    关键字distinct用于返回唯一不同的值。

    SELECT DISTINCT sal FROM emp;

     

    4.2 查看雇员的佣金之和

      因为salcomm两列的类型都是数值类型,所以可以做加运算。如果salcomm中有一个字段不是数值类型,那么会出错。

    SELECT *,sal+comm FROM emp;

    comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL

    SELECT *,sal+IFNULL(comm,0) FROM emp;

     

    4.3 给列名添加别名,也可给表名添加别用,用法一样。

    在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total

    SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

    给列起别名时,是可以省略AS关键字的:

    SELECT *,sal+IFNULL(comm,0) total FROM emp;

    如果别名包含空格,需要用双引号括起来:

    SELECT CANCAT(first_name, ,last_name) AS FULL NAME FROM us;

     

    5 查询排序

    5.1 查询所有学生记录,按年龄升序排序

    SELECT * FROM stu ORDER BY sage ASC;

    或者SELECT * FROM stu ORDER BY sage;

     

    5.2 查询所有学生记录,按年龄降序排序

    SELECT * FROM stu ORDER BY age DESC;

     

    5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序

    SELECT * FROM emp ORDER BY sal DESC,empno ASC;

     

    5.4 查询所有雇员,按月薪升序排序,如果月薪相同时,按编号升序排序

    SELECT * FROM emp ORDER BY sal [ASC],empno [ASC];

     

6 聚合函数

聚合函数是用来做纵向运算的函数:

  • COUNT():统计指定列不为NULL的记录行数;

  • MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;

  • MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;

  • SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0

  • AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0

     

    6.1 COUNT

    当需要纵向统计时可以使用COUNT()

    COUNT(*)对选中的行进行计数,而COUNT()只对非NULL值进行计数。

    可以将COUNT()DISTINCT组合对选择结果集中不同的值进行计数。

    查询emp表中记录数:

    SELECT COUNT(*) AS cnt FROM emp;

     

    查询emp表中有佣金的人数:

    SELECT COUNT(comm) cnt FROM emp;

    注意,因为count()函数中给出的是comm列,那么只统计commNULL的行数。

     

    l 查询emp表中月薪大于2500的人数:

    SELECT COUNT(*) FROM emp WHERE sal > 2500;

     

    l 统计月薪与佣金之和大于2500元的人数:

    SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

     

    l 查询有佣金的人数,以及有领导的人数:

    SELECT COUNT(comm), COUNT(mgr) FROM emp;

     

    6.2 SUMAVG

    当需要纵向求和时使用sum()函数。

    AVG()只能对数值型字段使用,这个函数在计算平均值时也忽略空值。

    l 查询所有雇员月薪和:

    SELECT SUM(sal) FROM emp;

     

    l 查询所有雇员月薪和,以及所有雇员佣金和:

    SELECT SUM(sal), SUM(comm) FROM emp;

     

    l 查询所有雇员月薪+佣金和:

    SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

     

    l 统计所有员工平均工资:

    SELECT SUM(sal)/ COUNT(sal) FROM emp;

    或者SELECT AVG(sal) FROM emp;

     

    6.3 MAXMIN

    l 查询最高工资和最低工资:

    SELECT MAX(sal), MIN(sal) FROM emp;

     

     

    7 分组查询

    当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部门来分组。

    我们可以查询某张表,把某张表里面的数据按照某个字段来进行分组,我们可以得到分组之后的信息,分组一般是结合我们的聚合函数来使用。

     

    7.1 分组查询

    l 查询每个部门的部门编号和每个部门的工资和:

    SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

     

    l 查询每个部门的部门编号以及每个部门的人数:

    SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

     

    l 查询每个部门的部门编号以及每个部门工资大于1500的人数:

    SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING sal>1500;

     

     

    8 限定查询

    TOP用于规定要返回的记录的数目。可接具体number数字或是percent百分比

    语法:select top number|percent column_name(s) from table

     

    LIMIT用来限定查询结果的起始行,以及总行数。

    语法:select column_name(s) from table limit [offset] number

     

    FIRST(column_name)函数 返回指定字段中第一个记录的值。

    LAST(column_name)函数 返回指定字段中最后一个记录的值。

     

    8.1 查询5行记录,起始行从0开始

    SELECT * FROM emp LIMIT 0, 5;

    注意,起始行从0开始,即第一行开始!

     

    8.2 查询10行记录,起始行从3开始

    SELECT * FROM emp LIMIT 3, 10;

     

    8.3 分页查询

    如果一页记录为10条,希望查看第3页记录应该怎么查呢?

    l 第一页记录起始行为0,一共查询10行;

    l 第二页记录起始行为10,一共查询10行;

    l 第三页记录起始行为20,一共查询10行;

     

    8.4 随机抽取一个记录

    SELECT * FROM emp ORDER BY RAND() LIMIT 1;

     

    9 子查询

    9.1 where型子查询(把内层查询结果当作外层查询的比较条件)

    #不用order by 来查询最新的商品

    select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

    #取出每个栏目下最新的产品(goods_id唯一)

    select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

     

    9.2 from型子查询 (把内层的查询结果供外层再次查询)

    #用子查询查出挂科两门及以上的同学的平均成绩

    思路:

    #先查出哪些同学挂科两门以上

    select name,count(*) as gk from stu where score < 60 having gk >=2;

    #以上查询结果,我们只要名字就可以了,所以再取一次名字

    select name from (select name,count(*) as gk from stu having gk >=2) as t;

    #找出这些同学了,那么再计算他们的平均分

    select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

     

    9.3 exists型子查询(把外层查询结果拿到内层,看内层的查询是否成立)

    #查询哪些栏目下有商品,栏目表category,商品表goods

    select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

     

     

    10  多表联合查询

    10.1 union

    union用于合并两个或多个select语句的结果集。

    请注意,union内部的select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条select语句的列的顺序必须相同。

    注释:union选取不同的值。如果允许重复的值,请使用union all

    语法select 列名 from 1 union [all] select 列名 from 2

    另外,union结果集中的列名总是等于union中第一个select语句中的列名。

     

    10.2 引用多个表 FROM子句列出表名,并用逗号分隔

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P

    等效于

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons [INNER] JOIN Orders ON Persons.Id_P =

    Orders.Id_P

    自引用

     

    10.3 内连接 [inner] join

    查询结果是左右连接的交集,【即左右连接的结果去除null项后的并集(去除了重复项)】

    语法:select 列名 from 1 [inner] join 2 on 1.列名=2.列名

     

    10.4 左连接、左外连接 left join

    以左表为准,去右表找数据,如果没有匹配的数据,则以null补空位,所以输出结果数>=左表原数据数

    语法:select 列名 from 1 LEFT join 2 on 1.列名=2.列名

     

    10.5 右连接、右外连接 right join

    a left join b 等价于 b right join a 推荐使用左连接代替右连接

    语法:select 列名 from 1 RIGHT join 2 on 1.列名=2.列名

     

    以图解为例:(截图没截干净,讲究看吧...

表ta:

表tb:


内连接:inner join


左连接:left join


右连接:right join


11 表的备份

从一个表中选取数据,然后把数据插入另一个表中

常用于创建表的备份复件或者用于对记录进行存档

in 向另一个数据库中拷贝表

语法:SELECT 列名 INTO 新表名 [IN 其他数据库] FROM 原表名




  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值