基本查询语句

mysql数据查询语言

重点，该语言用来查询记录，不会修改数据库和表结构。

insert into student (id,name,age,gander) values (1,'zhangsan',13,'男');
insert into student (id,name,age) values (2,'lisi',13);
insert into student values (3,'wangwu',13,'男');

update student set age = 25;
UPDATE student set age = 13 where id = 2;
UPDATE student set age = 15,gander = '女' where id > 1;
UPDATE student set age = 90 where id < 3 and  gander='女';

DELETE from student where age > 15;
DELETE from student where id = 3;
DELETE FROM student;

一、构建数据库

创建数据库以及创建表单：

drop TABLE if EXISTS student;
CREATE TABLE student (
    id INT(10) PRIMARY key,
    name VARCHAR (10),
    age INT (10) NOT NULL,
    gander varchar(2)
);

drop TABLE if EXISTS course;
CREATE TABLE course (
  id INT (10)  PRIMARY key,
  name VARCHAR (10) ,
  t_id INT (10) 
) ;

drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
  id INT (10)  PRIMARY key,
  name VARCHAR (10) 
);

drop TABLE if EXISTS scores;
CREATE TABLE scores(
  s_id INT ,
  score INT (10),
  c_id INT (10) ,
	PRIMARY key(s_id,c_id)
) ;

表单填充数据：

insert into  student (id,name,age,gander)VALUES(1,'白一',19,'男');
insert into  student (id,name,age,gander)VALUES(2,'连二',19,'男');
insert into  student (id,name,age,gander)VALUES(3,'邸三',24,'男');
insert into  student (id,name,age,gander)VALUES(4,'李四',11,'男');
insert into  student (id,name,age,gander)VALUES(5,'张五',18,'男');
insert into  student (id,name,age,gander)VALUES(6,'武七',18,'女');
insert into  student (id,name,age,gander)VALUES(7,'张八',16,'男');
insert into  student (id,name,age,gander)VALUES(8,'康九',23,'男');
insert into  student (id,name,age,gander)VALUES(9,'杨十',22,'女');
insert into  student (id,name,age,gander)VALUES(10,'王十一',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1);
insert into  course (id,name,t_id)VALUES(2,'语文',2);
insert into  course (id,name,t_id)VALUES(3,'c++',3);
insert into  course (id,name,t_id)VALUES(4,'java',4);
insert into  course (id,name)VALUES(5,'php');


insert into  teacher (id,name)VALUES(1,'老张');
insert into  teacher (id,name)VALUES(2,'老孙');
insert into  teacher (id,name)VALUES(3,'薇薇姐');
insert into  teacher (id,name)VALUES(4,'磊磊哥');
insert into  teacher (id,name)VALUES(5,'大微姐');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

二、单表查询

1、基本查询(后缀都是统一为from 表名)

(1)查询所有列:select * from 表名;其中＊表示查询所有列，而不是所有行的意思。

(2)查询指定列:select 列1，列2，列n from 表名；

(3)完全重复的记录只显示一次:在查询的列之前添加distinct

(4)列运算

a.数量类型的列可以做加、减、乘、除:`SELECT sal*5 from 表名；`说明:1.遇到null加任何值都等于null的情况，需要用到ifnull()函数。2.将字符串做加减乘除运算，会把字符串当作0。

b.字符串累类型可以做连续运算(需要用到concat()函数):`select concat(列名1，列名2) from 表名;`其中列名的类型要为字符串。

c. 给列名起别名:`select 列名1 (as) 别名1,列名2 (as) 别名2 from 表名；`

(5)条件控制

a.条件查询。在后面添加where指定条件:`select * from 表名 where 列名＝指定值;`
b.模糊查询：当你想查询所有姓张的记录。用到关键字like。

select * from 表名 where 列名 like ‘张_’;
(_代表匹配任意一个字符，％代表匹配0～n个任意字符)。

2、排序(所谓升序和降序都是从上往下排列)

• 1.升序:select * form 表名 order by 列名 (ASC ); ()里面的内容为缺省值；
• 2.降序:select * from 表名 order by 列名 DESC;
• 3.使用多列作为排序条件: 当第一列排序条件相同时，根据第二列排序条件排序(当第二列依旧相同时可视情况根据第三例条件排序)。eg:select * from 表名 order by 列名1 ASC, 列名2 DESC;意思是当列名1的值相同时按照列名2的值降序排。

3、聚合函数

• 1.count:select count(列名) from 表名；,纪录行数。
• 2.max:select max(列名) from 表名；,列中最大值。
• 3.min:select min(列名) from 表名；,列中最小值。
• 4.sum:select sum(列名) from 表名；,求列的总值，null 和字符串默认为0。
• 5.avg:select avg(列名) from 表名;,一列的平均值。

4、分组查询

分组查询的信息都是组的信息，不能查到个人的信息，其中查询组的信息是通过聚合函数得到的。

语法：select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名；其中分组列名需要的条件是该列名中有重复的信息。

查询的结果只能为：作为分组条件的列和聚合函数；查处的信息都是组的信息。

分组查询前，还可以通过关键字where先把满足条件的人分出来，再分组。语法为:select 分组列，聚合函数 from 表名 where 条件 group by 分组列；

分组查询后，也可以通过关键字having把组信息中满足条件的组再细分出来。语法为：select 分组列，聚合函数 from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件)；

select gander,avg(age) avg_age,sum(age) sum_age from student GROUP BY gander HAVING  gander = '男'

5、LIMIT子句(mysql中独有的语法)

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

例如：select * from 表名 limit 4，3；表示起始行为第5行，一共查询3行记录。

--如果一个参数 说明从开始查找三条记录
SELECT id,name,age,gander FROM student limit 3
--如果两个参数 说明从第三行起(不算)，向后查三条记录
SELECT id,name,age,gander FROM student limit 3,3

三、多表查询

笛卡尔积：简单来说就是两个集合相乘的结果，集合A和集合B中任意两个元素结合在一起。

1、内连接

内连接

内连接查询操作只列出与连接条件匹配的数据行，使用INNER JOIN或者直接使用JOIN 进行连接。

内连接可以没有连接条件，没有条件之后的查询结果，会保留所有结果(笛卡尔集)，与后面分享的交叉连接差不多。

在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

查询结果，注意列数是 4 列，两张表的字段直接拼接在一起，重复的字段在后面添加数字序列以做区分

通俗讲就是根据条件，找到表 A 和 表 B 的数据的交集

例子：

普通的多表查，课内连接接通相同
SELECT * from teacher t , course c where t.id = c.t_id   
(这样会先生成笛卡尔积，效率可能略低)

SELECT * from teacher t JOIN course c on t.id = c.t_id 
SELECT * from teacher t inner JOIN course c on t.id = c.t_id 

结果：只有满足条件的会显示，5号老师没课程，5号课程没老师都不会显示
1	王宝强	1	数学	1
2	贾宝玉	2	语文	2
3	温迪	3	c++	3
4	路人甲	4	java	4

2、外连接(常用)

外连接不只列出与连接条件相匹配的行，而且还加上左表(左外连接时)或右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

(1)左连接(左外连接)

查询结果如下

根据条件，用右表(B)匹配左表(A)，能匹配，正确保留，不能匹配其他表的字段都置空 Null。

也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上左表的数据集， Venn 图表示就是

红色部分代表查询结果

例子：

SELECT * from teacher t LEFT JOIN course c on t.id = c.t_id

结果：只有满足条件的会显示，5号老师没课程，依然显示，5号课程没老师都不会显示，
左边表的所有数据都显示

1	王宝强	1	数学	1
2	贾宝玉	2	语文	2
3	温迪	3	c++	3
4	路人甲	4	java	4
5	路人乙

(2)右连接(右外连接)

查询结果如下

根据条件，用左表(A)匹配右表(B)，能匹配，正确保留，不能匹配其他表的字段都置空 Null。

也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上右表的数据集， Venn 图表示就是

例子：

SELECT * from teacher t right JOIN course c on t.id = c.t_id

结果：只有满足条件的会显示，5号老师没课程不显示，5号课程没老师都，依然显示，
右边表的所有数据都显示

1	王宝强	1	数学	1
2	贾宝玉	2	语文	2
3	温迪	3	c++	3
4	路人甲	4	java	4
		  5	   php

3、全连接，mysql不支持，oracle支持

目前我的 MySQL 不支持此种方式，可以用其他方式替代解决，在此不展开。

理论上是根据条件找到表 A 和 表 B 的数据的交集，再加上左右表的数据集

四、子查询

1、where 型子查询

将查询结果当条件

例子：查询有一门学科分数大于八十分的学生信息

SELECT * from student where id in
(select DISTINCT s_id from scores where score > 90);

where 型子查询，如果是 where 列 =(内层 sql) 则内层 sql 返回的必须是单行单列，单个值。

where 型子查询，如果是 where 列 in(内层 sql) 则内层 sql 返回的必须是单列，可以多行。

2、from 型子查询

在学习 from 子查询之前，需要理解一个概念：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

取排名数学成绩前五名的学生，正序排列。

select * from  (SELECT s.id,s.name,e.score,c.`name` cname from student s LEFT JOIN scores e
on s.id = e.s_id left JOIN course c on e.c_id = c.id
where c.`name` = '数学' order by e.score desc limit 5 ) t ORDER BY t.score

五、练习题

1、查询‘01’号学生的姓名和各科成绩。 *

2、查询各个学科的平均成绩，最高成绩。 *

3、查询每个同学的最高成绩及科目名称。 *

4、查询所有姓张的同学的各科成绩。 *

5、查询每个课程最高分的同学信息。 *

6、查询名字中含有“张”和‘李’字的学生信息和各科成绩 *

7、查询平均成绩及格的同学的信息。*

8、将学生按照总分数进行排名。 *

9、查询数学成绩的最高分、最低分、平均分。 **

10、将各科目按照平均分排序。 **

11、查询老师的信息和他所带科目的平均分。 **

12、查询被“张楠”和‘‘老孙’叫的课程的最高分和平均分。 **

13、查询查询每个同学的最好成绩的科目名称。 **

14、查询所有学生的课程及分数。**

15、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。**

16、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 **

17、查询有不及格课程的同学信息。 **

18、求每门课程的学生人数。 **

19、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 。***

20、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 ***

21、查询有一门课程成绩在90分以上的学生信息； ***

22、查询出只有三门课程的全部学生的学号和姓名 ***

23、查询有不及格课程的课程信息 ***

24、检索至少选修四门课程的学生学号 ***

25、查询没有学全所有课程的同学的信息 ***

26、查询学全所有课程的同学的信息。 ****

27、 查询各学生都选了多少门课 ***

28、查询课程名称为”数学”，且分数低于60的学生姓名和数学分数。 ***

29、查询学过”张楠”老师授课的同学的信息 。 ****

30、查询没学过”张楠”老师授课的同学的信息 ****