mysql查询-阿里云开发者社区

开发者社区> 技术小阿哥> 正文

mysql查询

简介:
+关注继续查看

查询有多种方式:普通查询,连接查询,子查询查询,集合查询


1.select 查询语句具有5个子句,只有from是必须的。

SELECT [ALL|DISTINCT|DISTINCTROW|TOP]{*|table.*|[table.]field(x)[AS alias(x)]}

FROMtable_expression[, ...][IN external_database]:这里可以有一个JOIN ON

[WHEREsearch_condition] :条件查询

[GROUP BY group_by_expression]:用于需要使用统计函数时

[HAVING search_condition]:限制GROUP BY的返回组

[ORDER BY order_expression[ASC | DESC]]:对最终的查询结果排序

[WITH OWNERACCESS OPTION]

[LIMIT n]:限制查询结果的返回记录数量

Select查询语句的执行顺序:

from开始执行,对表进行交叉连接,结果传递给wherefrom后可以是表或子查询;

where子句将记录一条一条的进行条件过滤,结果传递给group by ;

group by数据进行分组,以便于having使用聚合函数,将分组表结果传递给having

having子句将记录一组一组的进行条件过滤,在having子句中,只能使用聚合函数,结果传递给select

select按查询要求对返回表一条记录一条记录或一组一组进行计算并输出,select 查询的数据有两种,列及其组合以及分组(group分组的一组列)及其组合,列和分组不能混合查询。将结果传给order by

Order by select计算的结果进行排序

 

聚合统计函数有:SUM,AVG,MAX,MIN,COUNT,STDDEV,VARIANCE只能对已分组的组列进行处理。因为它是在组内进行计算的。没有GROUP BY时,默认整张表被分成了一组。

2.SQL运算符的运用

逻辑运算:AND与,OR或,NOT非;(与>,<等用法相同),用于where和having

比较运算:>,<,>=,<=,=,<>用于where和having

用于返回单列多行单列子查询:

ALL(需要与比较运算符连用,如果一组的比较都为TRUE,那么就是TRUE); 

ANY(需要与比较运算符连用,如果一组的比较有一个为TRUE,那么就是TRUE);

SOME(需要与比较运算符连用,如果在一组比较中,有些为TRUE,那么就是TRUE);

EXISTS(如果子查询包含一些行,那么就是TRUE);

IN(如果操作是等于列表中的一个,那么就是TRUE,可以使用OR代替;

UNIQUE:(如果子查询无重复记录,那么就是TRUE

    通配:

LIKE(如果操作数与一种模式相匹配,那么就是TRUE)[...]任一一个字符,%匹配字符串,-匹配一个或0个字符。

合并具有相同查询字段的2个select查询结果(在运算在order by之前):

UNION(并集,通过组合其它两个结果表并消去表中任何重复行而派生出一个结果表)UNION ALL(当ALLUNION一起使用时,不消除重复行);

INTERSECT(交集,通过只包含两个表中相同的行并消除重复行而派生出一个结果表),INTERSECT ALL(当ALLINTERSECT一起使用时,不消除重复行);

EXCEPT(差集,通过包含所有的表1中但不在表2中的行并消除重复行而派生出一个结果表)EXCEPT ALL,当ALLEXCEPT一起使用时,不消除重复行。例子:SELECT sid FROM student WHERE department =计算机系  UNION SELECT sid FROM learning WHERE cid=c03 AND score>80

3.数据类型类型转换函数CAST() AS 类型 和CONVERT()

  Cast 可以在任何两个数据类型之间进行转换。

例:CASTyear(1966-5-5)) AS char(4) 将时间类型的1966转换成字符类型的‘1966’,在数据库中的时间格式一般为:YYYY-MM-DD-HH-MM-SS

CONVERT(char,GETDATE())->1966-5-5

4.DISTINCT/ALL关键字->重值筛选(DISTINCTROW)

SELECT COUNT(DISTINCT sal) FROM tableTeacher  sal记录不重值

SELECT COUNT(ALL sal) FROM tableTeacher sal记录可重值

SELECT DISTINCT sal FROM tableTeacher 

5.TOP(n)

限制返回查询表中的前n条记录,TOP可用在SUID查询中

例:SELECT TOP 2 * FROM teacher ORDER BY pid

6.LIKE与通配符指定查询条件 

LIKE可用于char,varchar,text,ntext,smalldatatime等类型的查询,LIKE中可以使用的匹配符:

?或_:任何一个单一的字符

*%:任意长度的字符

#0-9之间的单一数字

[字符列表]:在字符列表中的任一值

[!字符列表]:不在字符列表中的任一值

-:指定字符范围

例子:

SELECT customers FROM t1 WHERE Phone LIKE418-###

7.NULL查询

SELECT sno,cno FROM T1 WHERE Score IS NULL

SELECT sno,cno FROM T1 WHERE Score = NULL

8.使用包含判断词EXISTS,ALL,ANY,SOME

例子:

SELECT * FROM T1 WHERE EXISTS(SELECT* FROM T2 WHERE T1.id=T2.id)

SELECT * FROM T1 WHERE T1.id=ANY(SELECT id FROM T2)

SELECT * FROM T1 WHERE T1.id IN(SELECT id FROM T2)

以上三种方法都是在T1表中查询与T2表中id字段数据相同的记录内容

SELECT field1 FROM T1 WHERE price>=ALL(SELECT price FROM T1)

SELECT title,COUNT(title) as T1 FROM Tm WHERE region=WA GROUP BY title HAVING COUNT(title)>5;HAVING COUNT(title)>5中的条件只针对GROUP BY分组中的记录进行记录进行操作

SELECT dname,sex,COUNT(*) FROM T1 GROUP BY dname,sex 查询各个系中男老师和女老师的人数。

9.使用正则表达式查询

SELECT * FROM tablename WHERE fieldname REGEXP regstr 

只要regstr 部分匹配 fieldname WHERETRUE

10.连接(自连接,内连接,外连接,交叉连接):求两表集合的一种方式

自连接:例子 SELECT t1.tname,t1.sal FROM teacher t1,teacher t2 WHERE t1.sal=t2.sal AND t2.name=张军 :查询与张军工资相同的老师,其中t1t2teacher表的别名

内连,接使用比较运算符进行连接,内连接与外连接不同,内连接只显示完全满足条件的记录

等值连接:连接条件中使用=连接列的列值

SELECT emp.*,dept.* FROM emp INTER JOIN dept ON emp.deptno=dept.deptno

不等值连接:使用>,<,!>,!<,<>进行的连接

SELECT a.no,a.name,b.loc FROM a INTER JOIN b ON a.no<>b.no 使用不等建立关系

自然连接:按照两个表中的相同属性进行等值连接后去掉了重复的属性列,保留了所有不重复的属性列

SELECT a.no,sex,con FROM a,b WHERE a.no=b.no

外连接:与内连接不同,将返回匹配时,某表字段出现失配的记录。外连接的查询结果是内连接结果的扩展,包括了内连接的全部结果。作用是在连接操作时避免丢失信息

左外连接:LEFT [OUTER] JOIN,其结果表中保留左表的所有记录

右外连接:RIGHT [OUTER] JOIN,其结果表中保留右关系的所有元组

全外连接:FULL[OUTER] JOIN,其结果表中保留左右关系的所有元组

例子:SELECT stu.sno ,sname,cno FROM stu RIGHT OUT JOIN sc ON stu.sno=sc.sno

stu.sno=sc.sno为条件连接两表形成新表,并且返回sc表中没有连接上的记录

 

交叉连接:交叉连接就是笛卡尔积,将左表的每一条记录连接右表的每一条记录形成一个具有m(左表记录数)×n(右表记录数)条记录数的新表,交叉连接不带WHERE子句

SELECT dept.* FROM dept CROSS JOIN room 将返回dept*room条记录

 

11.集合查询 UNION INTERSECT EXCEPT [ALL] 对查询结果表操作

 

SELECT * FROM dept UNION SELECT * FROM department  去冗余

SELECT * FROM dept UNION ALL SELECT * FROM department  不去冗余

SELECT cno FROM school WHERE tno=199001 UNION SELECT cno FROM course WHERE credits>5  UNION SELECT cno FROM sc  WHERE grade>80 多表查询

 

SELECT empno,ename FROM emp WHERE sal>8000 INTERSECT SELECT empno,ename FROM emp WHERE dep=CLERK 交集

SELECT tnotname FROM teach WHERE sal>8000 EXCEPT [ALL] SELECT  tnotname FROM teach WHERE SEX= 排除:表1排除表2中的结果,并去除重复记录

12.子查询(必须放在()中)

子查询的返回结果有3种:

单列单行:可以当做普通数值处理

单列多行:使用ALL(),IN(),SOME()等函数处理

多列多行:用在from后当做表处理

简单子查询:

SELECT ename FROM emp WHERE deptno IN (SELECT dept.deptno FROM dept WHERE loc=DALLAS)     查询位于‘DALLAS’的所有雇员的姓名

子查询分类:单行单列子查询(只返回一行单列数据),多行单列子查询(返回多行单列数据)和多列子查询(返回单行多列和多行多列数据,用于表及索引的创建中)

字符匹配运算IN,ALL,ANY,LIKE,SOME,EXISTS常用在子查询的结果中

单行子查询:可以使用>,<,=等比较,可以看做一个普通值

SELECT ename,deptno FROM emp WHERE depno=(SELECT deptno FROM emp WHERE empno=7369) 

SELECT ename,deptno,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) 查询工资最少的雇员姓名、部门和工资

SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE A%)查询姓名以A开头的员工信息 :多行子查询

SELECT tname,sal FROM teacher WHERE sal<ANY(SELECT sal FROM teacher WHERE rno=0101 ) AND rno<> 0101 查询教研室中比0101教研室任意老师工资少的老师名单

多列子查询:在使用子查询比较多个列时,可以使用成对比较和非成对比较,而多列子查询适合于成对和非成对比较。成对比较要求多个列的数据必须同时匹配,非成对比较只要求多列数据中的一列数据匹配即可。

成对多列子查询:SELECT tname,titleno,sal,rno FROM teacher WHERE rno=0201 AND (titleno,sal) IN (SELECT titleno,sal FROM teacher WHERE rno=0101查找0201教研室与0101教研室职称和工资完全相同的教师

非成对多列子查询:完全可以用单列子查询代替

EXISTS用法:

SELECT sname FROM student WHERE EXISTS (SELECT *FROM SC WHERE sno=student.sno AND schoolno=19980201)查询所有选修了‘19980201’号课程的学生姓名。

从这条查询语句可以看出SQL(或数据库)的工作机制:类似于把每一条记录依次送入查询语句中比对判断。

将结果表作为子查询的表

SELECT a.ename,a.sal,a.deptno,b.salavg FROM emp a,(SELECT deptno, AVG(sal) salavg FROM emp GROUP BY deptno ) [AS]b WHERE a.sal>b.salavg AND a.deptno=b.deptno 使用了表的别名和字段的别名

例子:

SELECT a.fname,a.lname FROM a,b,(SELECT title_id FROM titles WHERE ytd>1000) AS t WHERE a.au_id=b.au_id AND b.title_id=t.title_id

SELECT a,b,t三张表中查询结果,表tSELECT返回的新表

Select  a.sal*10+3 AS SAL_1,name from t 查询非列字段

13.分组查询

聚合统计函数有:SUM,AVG,MAX,MIN,COUNT,STDDEV,VARIANCE只能对已分组的组列进行处理。因为它是在组内进行计算的。没有GROUP BY时,默认整张表被分成了一组。

 group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面(重要)

SELECT SUM(sal) AS oldsal FROM t WHERE age>40  将整个表看成一个分组

SELECT DNAME,TSEX,COUNT(*) FROM t GROUP BY dname,tsex HAVING COUNT(*)>3对已分组列进行查询

分组查询和单列查询不能混合使用:select后跟的查询列,要么全是分组的,要么全是未分组的

SELECT dname count(sex) AS num_girl FROM teach WHERE sex='' GROUP BY dname HAVING count(sex)>2    分组查询

dname 列被group by分组,count()的计算也是在组内计算的。因此为组列,该select是组列查询。

WHERE HAVING 都是指明条件,WHERE是单列条件,HAVING组条件。

下面是单列查询:

SELECT name , sex FROM teach WHERE sex='女' GROUP BY dname HAVING count(sex)>2 系的女生数>2的各系女生姓名。  单列查询

15.连接查询

所有的连接都可以使用from ...where...实现:

frome t1,t2           :交叉连接(默认:交叉连接,笛卡儿积)

frome t1,t2 where t1.a=t2.a   :内连接

frome t1,t2 where t1.a*=t2.a   :左外连接

frome t1,t2 where t1.a=*t2.a   :右外连接

frome t1,t2 where t1.a*=*t2.a   :全外连接

 

16.group,case和if


16.1 group 分组查询

group by A,B 分组的含义:将A,B字段结合成(A,B)组,于是SELECT A,B(或SELECT B,A)绝不可能重样,但SELECT A是可能重样的,而且重叠条数是在分组后的组名上查询的。另一种方式解释:group by A,B 是将表table按照相同的(A,B)字段合并成组,既然是合并就不可能有相同的(A,B)组名,而SELECT A是在组名中查询A,可能有相同的记录(但同不使用group by A,B,而直接查询select A的结果是意义不一样的,结果当然可能不同) ,分组的时候会进行相同分组的合并,查询是在合并后的结果中查询。

mysql> select * from test;

+----+--------+------+--------+

| id | name   | age  | shcool |

+----+--------+------+--------+

|  1 | liujin |   25 | dd     |

|  2 | lj     |   25 | dd     |

|  3 | lj     |   27 | da     |

|  4 | lj     |   26 | da1    |

|  5 | lj     |   26 | da2    |

|  6 | lk     |   26 | da     |

|  7 | lb     |   26 | da1    |

+----+--------+------+--------+

7 rows in set (0.02 sec)


select name,count(*) from test group by name;

按照sql语句中各个关键字的执行顺序,group by之后的子表应该可能表示为:

| id | name   | age  | shcool |

+----+--------+------+--------+

|  1 | liujin |   25 | dd     |

|  2 | lj     |   25 | dd     |

|  3 |        |   27 | da     |

|  4 |        |   26 | da1    |

|  5 |        |   26 | da2    |

|  6 | lk     |   26 | da     |

|  7 | lb     |   26 | da1    |

+----+--------+------+--------+

这样一个(lj)对应4条记录,但只有一个lj的名字,(lj)组是唯一的

执行结果为:

+--------+----------+

| name   | count(*) |

+--------+----------+

| lb     |        1 |

| liujin |        1 |

| lj     |        4 |

| lk     |        1 |

+--------+----------+


16.2 CASE WHEN语句:分段查询

case when语句,可以实现给查询字表重新赋值

CASE 具有两种格式:a.布尔型:CASE 搜索函数计算一组布尔表达式以确定结果case无入参。 两种格式都支持可选的 ELSE 参数;b."匹配型":简单 CASE 函数将某个表达式与一组简单表达式进行"匹配"以确定结果,case有入参;

第一种用法:

SELECT name,

CASE WHEN birthday < '1981' THEN 'old' 

WHEN birthday > '1988' THEN 'yong'

ELSE 'ok' END AS YOMN

FROM lee



第二种用法:

SELECT NAME, CASE name

WHEN 'sam' THEN 'yong'

WHEN 'lee' THEN 'handsome'

ELSE 'good' END as oldname

FROM lee

例子:

给学生成绩表分段,score>90为优,score<90 &&score>70 良,score<70 &&score>60 中,否则为差

符合case搜索函数的语义

学生表:  id name score 

select name case when score>90 then "优" when score<=90 and score>70 then "良" when score<=70 and score>60 then "中" else "差" end as grade from student;

结果:

name grade

li   优

wa   良

ta   中  


子查询

select * from 

( select paymentno, amount,

case 

when amount >= 0 AND amount < 40 then 'low'

when amount >=40 AND amount < 80 then 'moderate'

when amount >=80 then 'high' 

else 'incorrect' end as lvl

from penalties) as p

where p.lvl = 'low'


16.3  if :有2种用法

a.相当于case:分段查询(用于sql表达式,而不是用于流控制)


IF(expr1,expr2,expr3)  如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

<=>

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''


b.作为流控制使用


create procedure dbname.proc_getGrade  

(stu_no varchar(20),cour_no varchar(10))  

BEGIN 

declare stu_grade float;  

select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;  

if stu_grade>=90 then 

    select stu_grade,'A';  

elseif stu_grade<90 and stu_grade>=80 then 

    select stu_grade,'B';  

elseif stu_grade<80 and stu_grade>=70 then 

    select stu_grade,'C';  

elseif stu_grade70 and stu_grade>=60 then 

    select stu_grade,'D';  

else 

    select stu_grade,'E';  

end if;  

END 


17.执行数据库脚本

1.编写sql脚本,假设内容如下:

  create database dearabao;
  use dearabao;
  create table niuzi (name varchar(20));

  保存脚本文件,假设我把它保存在F盘的helloworld目录下,于是该文件的路径为:F:\helloworld\niuzi.sql

2.执行sql脚本,可以有2种方法:
  第一种方法:在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 < F:\helloworld\niuzi.sql (注意路径不用加引号的!!) 回车即可.
  第二种方法:在命令行下(已连接数据库,此时的提示符为 mysql> ),输入 source F:\helloworld\niuzi.sql (注意路径不用加引号的) 或者 \. F:\hello world\niuzi.sql (注意路径不用加引号的) 回车即可




本文转自 a_liujin 51CTO博客,原文链接:http://blog.51cto.com/a1liujin/1652031,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql查询拼接
1 不同于sqlserver ,不能使用”+”,”+”号是使用字符转为数字处理,无法转换则忽略 select '11'+'12','abc'+1 from dual; 结果如下图: 使用concat()函数 select concat('11','12') from dual; select concat('11',12) from dual;
824 0
MySQL查询——select
MySQL查询——selectSELECT  select的完整语法: 复制代码select col1, col2,... # 业务查询的字段from table_name # 选取的哪张表[where single_conditions] ...
2487 0
MySQL查询日志总结
MySQL查询日志介绍     MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。默认文件名为hostname.log。默认情况下MySQL查询日志是关闭的。
718 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10839 0
一文读懂MySQL查询语句的执行过程
需要从数据库检索某些符合要求的数据,我们很容易写出 Select A B C FROM T WHERE ID = XX 这样的SQL,那么当我们向数据库发送这样一个请求时,数据库到底做了什么?
10 0
MySQL查询表字段个数的两种方法
转自博客http://database.51cto.com/art/201011/234486.htm    MySQL查询表字段使我们经常会遇到的问题,下文对MySQL查询表字段的方法作了详细的说明介绍,希望对您能够有所帮助。
934 0
mysql查询优化explain命令详解
​mysql查询优化的方法有很多种,explain是工作当中用的比较多的一种检查方式。explain翻译即解释,就是看mysql语句的查询解释计划,从解释计划我们能很清楚的看到解释的语句有没有合理用到索引,扫描了多少行数,有没有触及全表扫描、用到临时表等影响慢查询的原因。
934 0
13694
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载