MySQL查询语句

1. 一样平常查询

select * from table；
创建表：并插入数据，为下面的查询做例

1. create table info (
2. id int primary key,
3. name varchar(10),
4. score decimal(5,2),
5. address varchar(20),
6. hobbid int(5));
8. insert into info values(1,'liuyi',80,'beijing',2);
9. insert into info values(2,'wangwu',90,'shengzheng',2);
10. insert into info values(3,'lisi',60,'shanghai',4);
11. insert into info values(4,'tianqi',99,'hangzhou',5);
12. insert into info values(5,'jiaoshou',98,'laowo',3);
13. insert into info values(6,'hanmeimei',10,'nanjing',3);
14. insert into info values(7,'lilei',11,'nanjing',5);

复制代码

2. 排序语法：关键字排序

升序和降序
默认的排序方式就是升序
ASC ：升序；DESC ：降序；共同order by 语法
按照name列降序分列：

1. select * from info order by name desc;
2. #order by ：指定要排序的列
3. #desc ：指定排序的方式为降序

复制代码

按照hobbid列降序，id列升序分列（先排hobbid，再排id）

1. select * from info order by hobbid desc,id;
2. #id不指定排序方式，就默认是升序排序

复制代码

留意：以多个列作为排序关键字，只有当第一个参数有雷同的值，第二个字段才故意义。由于多个列排序，会先对第一个字段举行排序，当第一个字段存在重复时，对重复的字段，会根据第二个字段再举行排序
3. where条件的筛选功能（比力符）

3.1 区间判定：可与and or 搭配利用

查询分数大于70且小于即是90的行：

1. select * from info where score > 70 and score <= 90;

复制代码

查询分数大于80或小于70的行

1. select * from info where score < 70 or score > 80;

复制代码

3.2 嵌套多条件

条件中再嵌套条件，用（）

1. select * from info where score < 60 or (score > 75 and score < 90)

复制代码

4. 分组查询

SQL查询的结果举行分组，利用 group by 语句 共同聚合函数一起来实现。
4.1 常用聚合函数的范例

• count ：统计个数
  
• sum ：求和
  
• avg ：求匀称数
  
• max ：最大值
  
• min ：最小值
  

4.2 聚合函数用法示例

选择列用于聚合函数，假如利用group by，则还必要至少一个列用于分组

1. select count(name),hobbid from info
2. group by hobbid;

复制代码

表明：**针对 info 表中的数据，按照 hobbid 列中的每个唯一值，统计出该值对应的纪录数目，并将结果按照 hobbid 的值举行分组展示。**直白点说就是统计每个hobbid对应的纪录数目，并体现出来。
在聚合函数分组语句中，全部的非聚合函数列，都可以在group by 语句中。

1. select count(name),hobbid,name from info
2. group by hobbid,name;

复制代码

也可以团结where，having等语句举行筛选

1. select count(name),hobbid from info
2. where score >= 60 group by hobbid;

复制代码

在group中利用having语句

1. select count(name),hobbid,score from info
2. group by hobbid,score
3. having score > 80;

复制代码

having是在group by中用于筛选的语句，写在group by之后
例：以hobbid这一列作为分组，盘算结果score的匀称值，筛选出匀称结果大于即是60分的分组

1. select avg(score),hobbid from info
2. group by hobbid
3. having avg(score) >= 60;

复制代码

例：统计姓名，以爱好和分数作为分组，统计出结果大于80的分组，然后对统计姓名的列按照降序分列

1. select count(name),hobbid,score from info
2. group by hobbid,score
3. having score > 80
4. order by count(name) desc;

复制代码

5. limit

limit 1,3  为例：
1是位置偏移量（可选参数），偏移1体现从第二行开始；
假如不设定位置偏移量，默以为0，从第一行开始。3是从起始行开始一连选取三行。
例：选取以id排序的末了三行：

1. select * from info order by id desc limit 3;

复制代码

6. as ：表和列的别名

实际工作中，表的名字和列的名字大概会很长，誊写起来不太方便，必要多次声明表和列时，完备展示太贫苦，可以设置别名，可以使誊写简化，方便阅读。
设置别名利用 as
设置列名的别名

1. select name as 姓名,score as 成绩 from info;

复制代码

也可以省略as：

1. select name 姓名,score 成绩 from info;

复制代码

设置表名的别名：

1. select i.name as 姓名,i.score as 成绩 from info as i;
2. select i.name 姓名,i.score 成绩 from info i;

复制代码

复制表

1. create table test as select * from info;

复制代码

留意：这种方式只能复制表的数据范例和数据，但是复制不告终构（各种KEY，束缚等）
7. 通配符

like ：含糊查询
% ：体现0个，1个或多个字符
_ ：体现单个字符

1. #查询地址以s开头
2. select * from info where address like 's%';
3. #查询地址包含s
4. select * from info where address like '%s%';
5. #查询地址以s开头，长度至少为2
6. select * from info where address like 's_%';
7. #查询地址以s开头，第三个字母是a
8. select * from info where address like 's_a%';

复制代码

8. 子查询

子查询也叫内查询，嵌套查询。
是在select语句当中又嵌套了一个select。嵌套的select才是子查询，先实行子查询的语句，外部的select再根据自条件的结果举行过滤查找。
子查询可以使多个表，也可以是同一张表。
关联语句： in ; not in ，格式 select (select)

1. select name,score from info
2. where id in
3. ( select id from info where score >= 80);

复制代码

表明：先实行子查询里，筛选出score>=80的id，再回到外层查询，根据查到的id，列出对应id的name，score

1. select name,score from info
2. where id not in
3. (select id from info where score >= 80);

复制代码

表明：实行次序和上面一样，区别在于回到外层查询时，对查到的id取反，即除了查到id，列出其他的id对应的name，score
例：团结其他表举行查询

1. select name,score from info where id not in
2. (select id from test where score >= 80);

复制代码

也可以和update连用
查询test表中name为tianqi的id，将info表中对应id的score改为80

1. update info set score = 80 where id in
2. (select id from test where name = 'tianqi');

复制代码

留意：利用update，子查询不能再查自己的表，会报错
子查询当中多表查询和别名
例：info表和test表，查询两张表id雷同的部分。然后根据id雷同的部分，查询info表的对应id的所在行

1. select * from info as a
2. where a.id in
3. (select b.id from test as b);

复制代码

表明：a.id in (select b.id from test as b);
例：info表和test表，查询两张表id雷同的部分。然后根据id雷同的部分，查询info表的对应id中score大于80的所在行

1. select * from info as a
2. where a.score > 80 and a.id in
3. (select b.id from test as b);

复制代码

例：info表和test表，查询两张表id雷同的部分。然后根据id雷同的部分，查询info表的匀称结果

1. select avg(a.score) from info as a
2. where a.id in
3. (select b.id from test as b);

复制代码

9. exists

exists判定子查询的结果是否为空。不为空返回true；为空返回false

1. select count(*) from info where exists
2. (select id from test where score > 80);

复制代码

表明：看起来似乎是统计score大于80的数目，实际是先实行子查询，查询结果存在，则exists返回值为true，实行外层查询，统计info的总行数
例：查询分数，假如分数小于50，则统计info的字段数

1. select count(*) from info where exists
2. (select id from info where score < 50);

复制代码

10. mysql的视图(view)

视图是一个假造表，表的数据基于查询的结果天生。
视图可以简化复杂的查询，隐蔽查询的细节，访问数据更安全。
视图表是多表数据的一个聚集表。
视图和表之间的区别

• 存储方式：表是实际的数据行，视图不存储数据行，仅仅是查询结果的假造表。
  
• 数据更新：更新表可以直接更新数据表的数据（较老的版本大概不支持）。
  
• 占用空间：表实际占用空间，视图表不占用空间，只是一个动态结果的展示。
  
• 视图表的数据可以是一张表的部分查询数据，也可以是多个表的一部分查询数据。
  

查询当前数据库当中的视图表：

1. show full tables in basename where table_type like 'VIEW';

复制代码

创建视图表：实例：

1. create view test03 as select * from info where score >= 80;
2. #创建视图表test03，展示info表中score>=80的行
3. #此时执行下行
4. select * from test02;
5. #相当于执行下行
6. select * from info where score >= 80;

复制代码

留意：MySQL5.5版本之前，视图表是只读的，不能修改；MySQL5.5版本开始，视图表可以修改，且修改视图表和修改源表是双向的，即修改视图表也能更新源表。
删除视图表：

1. drop view tablename;

复制代码

创建一张视图表，视图表中包罗 id,name,address ,从info 和test当中的name值雷同的部分创建

1. create view v_info as
2. select a.id,a.name,a.address from info as a
3. where a.name in
4. (select b.name from test as b);
6. select * from v_info;

复制代码

可以发现，视图表实在就相当于为查询语句创建了一个别名，为查询做了简化。
平常利用中表的权限是不一样的，由于库的权限是有控制的。而查询视图表的权限相对低。以是利用视图表既可以包管原表的数据安全，也简化了查询的过程。
11. 毗连查询

两张表大概多个表的纪录团结起来，基于这些表共同的字段，举行数据的拼接。
起首，要确定一个主表作为结果集，然后将其他表的行有选择性的选定到主表的结果上(即做一个拼接)。
11.1 毗连的范例

内毗连：两张表大概多张表之间符合条件的数据纪录的聚集。
INNER JOIN，INNER一样平常可以省略
创建两张表：

1. create table test1 (
2. a_id int(11) default null,
3. a_name varchar(32) default null);
5. create table test2 (
6. b_id int(11) default null,
7. b_level int(11) default null);
9. insert into test1 values (1,'aaaa');
10. insert into test1 values (2,'bbbb');
11. insert into test1 values (3,'cccc');
12. insert into test1 values (4,'dddd');
14. insert into test2 values (2,20);
15. insert into test2 values (3,30);
16. insert into test2 values (5,50);
17. insert into test2 values (6,60);

复制代码

把两张表按照a_id,a_name,b_level拼接起来，并以on 后接筛选条件

1. select a.a_id,a.a_name,b.b_level from
2. test1 as a INNER JOIN test2 as b
3. on a.a_id = b.b_id;

复制代码

外毗连：取两个表或多个表之间的交集。
outer join，outer一样平常可以省略
左毗连：左外毗连，left join ，left outer join
左毗连以左表为底子，吸收左表的全部行，以左表的纪录和右表的纪录举行匹配。匹配左表的全部，以及右表中符合条件的行。不符合的体现null。

1. select * from test1 as a left join test2 as b
2. on a.a_id = b.b_id;

复制代码

留意：写在left join 左边的就是左表。
右毗连：右外毗连，right join , right outer join
根本就是跟左毗连反过来。右毗连以右表为底子，吸收右表的全部行，以左表的纪录和右表的纪录举行匹配。匹配右表的全部，以及左表中符合条件的行。不符合的体现null。

1. select * from test1 as a right join test2 as b
2. on a.a_id = b.b_id;

复制代码

留意：写在right join 右边的就是右表。
12. 练习

需求：两张表
第一张表：纪录门生的学号，所属专业，课程，姓名 结果 性别
第二张表：纪录门生的学号，手机 家庭所在，爱好爱好，性别
编写一个查询来查找具有最高分数的门生。
找出至少有两门课程结果及格的门生。
查找每个系的门生人数。
盘算每个系的学一生均分数。
获取至少同时选修了一门与 ‘xxx’ 雷同课程的门生。
找出具有重复名字的门生。
查找在全部课程中都取得了及格分数的门生。
找出每门课程的匀称分数，并按照匀称分数降序分列。
查找门生选课数目高出匀称选课数目的门生信息。
左毗连查询  查学号
右毗连查询  查学号
内毗连查询  查学号
第一张表：纪录门生的学号，所属专业，课程，姓名 结果 性别
第二张表：纪录门生的学号，手机 家庭所在，爱好爱好，性别

1. create table info1 (
2.         id int(4),
3.     major varchar(10),
4.     lesson varchar(10),
5.     name varchar(10),
6.     score decimal(5,2) default null,
7.     sex char(2)
8. );
9. create table info2 (
10.         id int(4),
11.     phone int(11),
12.     address varcha(30),
13.     hobby varchar(10) default null,
14.     sex char(2)
15. );
17. insert into info1 values
18. (1,'电气工程','高数','张三',90,'男'),
19. (1,'电气工程','英语','张三',55,'男'),
20. (1,'电气工程','计算机','张三',80,'男'),
21. (2,'通信工程','高数','李四',60,'男'),
22. (2,'通信工程','英语','李四',55,'男'),
23. (2,'通信工程','计算机','李四',50,'男'),
24. (3,'制药工程','高数','韩梅梅',70,'女'),
25. (3,'制药工程','英语','韩梅梅',90,'女'),
26. (3,'制药工程','计算机','韩梅梅',80,'女');
27. (4,'通信工程','高数','李雷',80,'男'),
28. (4,'通信工程','英语','李雷',80,'男'),
29. (4,'通信工程','计算机','李雷',90,'男'),
31. insert into info1 values
32. (5,'计算机','计算机','张三',88,'男'),
33. (6,'传媒','英语','李四',88,'女');
35. insert into info2 values
36. (1,'18369961111','南京','爬山','男'),
37. (2,'18369962222','北京','遛弯','男'),
38. (3,'18369963333','天津','说相声','女'),
39. (4,'18369964444','东京','下海','男');

复制代码

编写一个查询来查找具有最高分数的门生。
找出至少有两门课程结果及格的门生。
查找每个系的门生人数。
盘算每个系的学一生均分数。
获取至少同时选修了一门与 ‘xxx’ 雷同课程的门生。
找出具有重复名字的门生。
查找在全部课程中都取得了及格分数的门生。
找出每门课程的匀称分数，并按照匀称分数降序分列。
查找门生选课数目高出匀称选课数目的门生信息。
左毗连查询  查学号
右毗连查询  查学号
内毗连查询  查学号

• 查找具有最高分数的门生：
  

1. select * from info1
2. where score = (select max(score) from info1);

复制代码

• 找出至少有两门课程结果及格的门生：
  

1. select count(score),id,name from info1
2. where score >= 60
3. group by id,name having count(score) >= 2;

复制代码

• 查找每个系的门生人数：
  

1. select major,COUNT(DISTINCT id) as stu_num from info1
2. group by major;

复制代码

• 盘算每个系的学一生均分数
  

1. select major,lesson,FORMAT(AVG(score),2) as avg_score from info1
2. group by major,lesson;

复制代码

• 获取至少选修了有一门课程与 ‘id=5的张三’ 的课程雷同的门生
  

1. select distinct id,name from info1 where id<>5 and lesson in
2. (select lesson from info1 where id = 5);
4. #或者用表info1自连接的方式
5. select distinct i1.id,i1.name from info1 i1
6. join info1 i2 on i1.lesson = i2.lesson and i2.id = 5
7. where i1.id <> 5;

复制代码

• 找出具有重复名字的门生
  

1. select distinct id,name from info1 where name in
2. (select name from info1
3. group by name having COUNT(DISTINCT id) > 1)
4. ORDER BY name;

复制代码

• 查找在全部课程中都取得了及格分数的门生
  

1. select distinct id,name from info1 where id not in
2. (select id from info1 where score < 60);

复制代码

• 找出每门课程的匀称分数，并按照匀称分数降序分列
  

1. select lesson,FORMAT(AVG(score),2) as avg_score from info1
2. group by lesson
3. order by avg_score desc;

复制代码

• 查找门生选课数目高出匀称选课数目的门生信息
  

1. select id,name,num_lesson from
2. (select id,name,count(*) as num_lesson from info1
3. group by id,name) as stu_lesson
4. where num_lesson >
5. (select avg(cnt) as avg_lesson from
6. (select count(*) as cnt from info1
7. group by id,name) as avg_count);

复制代码

• 左毗连查询  查学号
  

1. select * from info1 as i1 left join info2 as i2
2. on i1.id = i2.id;

复制代码

• 右毗连查询  查学号
  

1. select * from info1 as i1 right join info2 as i2
2. on i1.id = i2.id;

复制代码

• 内毗连查询  查学号
  

1. select * from info1 as i1 inner join info2 as i2
2. on i1.id = i2.id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金