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_1001，S_1002，S_1003的记录

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

   

  2.5　查询学号不是S_1001，S_1002，S_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　查询年龄在20到40之间的学生记录

  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一起使用。

  % 匹配任意数目字符

  _ 匹配任何单个字符

  扩展正则表达式：使用REGEXP和NOT REGEXP (或RLIKE和NOT 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　查看雇员的佣金之和

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

  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列，那么只统计comm列非NULL的行数。

   

  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　SUM和AVG

  当需要纵向求和时使用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　MAX和MIN

  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 原表名