2021-04-24

MySQL的查询数据

1.单表查询

语法：
       select |字段列表|表达式 from 表名 [where 条件] [order by 字段列表]

说明：

select * from 表名（*相当于按照表中字段顺序罗列表中的所有字段）。

字段列表：当查询结果只是表中部分字段时，可将要显示的字段罗列出来，字段之间以逗号间隔。select 字段1,字段2 from 表名

表达式：可以是算术运算符也可以是数据库中的函数。select age+1 from student;（查询过程中使age列都加一并且出现）select length(字段名) from student;（length获取字符串的个数，单行函数，后面会说。）

where：指定查询过滤条件。

order by：对查询结果进行排序。

这些下面会有讲解。

---

 字段别名：select所选字段后面可以指定别名以使查出来的结果所显示的字段更好理解，字段名与别名之间使用空格或as关键字间隔；为了阅读方便，推荐使用as关键字这种方式定义字段别名。

例子：

select age+1 stu_age from student;
select length(name) as name_length from student;

 

在默认情况下，查询结果中显示的字段名为大写字母，如果别名中包含空格、特殊字符（例如%）或者对大小写敏感，则别名需要使用双引号引起来。

例子：

select age+1 "stu age" from student;
select length(name) as "name%length" from student;

 

表别名：表名与别名之间使用空格间隔（MySQL数据库中表的别名可以使用as关键字间隔，但Oracle数据库不允许；表的别名最多可以有30个字符；表别名的作用方法在整个select语句中都有效，离开select语句即无效）。

例子：

select student.name,student.address from student
select s.name,s.address from student s

---

 

create table student(
    id char(36) primary key,
    name varchar(8) not null,
    age int(3) default 0,
    mobile char(11),
    address varchar(150)
)
insert into student
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区')    

where：指定查询过滤条件。（需要充分理解，最好自己在软件上一个个尝试慢慢体会）
like：进行数据模糊查询
%：匹配0次或多次      

例子：

select * from student where name like '张%'   会导致出现张三和张李三；前面%代表匹配一次，后面%代表匹配两次；

select * from student where name like '%李%’  这个前面%可以理解为李的前面有0到无穷个字，这个后面%可以理解为李的后面面有0到无穷个字，而这行代码指的是名字中含有李的学生信息。
_：只匹配1次      

如果你前面的%理解了，那这个_就不是问题。

   select * from student where name like '张_’     只会出现张三的学生信息

但如果人的名称里出现了% _号该如何查询呢？

escape：取消%或_字符的通配符特性

例子：

#查询姓名中含有%字符的学生信息
select * from student where name like '%#%%' escape ‘#’(并不是非要填#，也可以填其它字母或者字符。可以理解为取消了#后面第一个%的特性)
#查询姓名中含有_字符的学生信息
select * from student where name like '%$_%' escape ‘$’(并不是非要填$，也可以填其它字母或者字符。可以理解为取消了#后面第一个%的特性)
#查询姓名以%%结尾的学生信息
select * from student where name like ‘%A%A%' escape ‘A’;表明了escape‘A’是取消了所有A后面第一个%或_的特性.
 注意：
escape后面单引号中只能是单个字符；
escape后面可以是字母、#、$、,、\等字符，不能是_；

逻辑条件：and、or

#查询张姓且地址中含有北京的学生信息
select * from student where name like '张%' and address like '%北京%’;
#查询张姓或地址中含有北京的学生信息
select * from student where name like '张%' or address like '%北京%’;

between 下限 and 上限：等同于“(字段名>=下限) and (字段名<=上限)”[小的数在前面，大的数在后面，包括边界值]

#查询年龄在10~28之间的学生信息
select * from student where age between 10 and 28;
#上面SQL语句等效于该SQL语句
select * from student where age >= 10 and age<=28;

关系条件：=、!=、<、=<、>、>=等（就是where后面的限制条件，这个和C语言不同，=就是=，不是赋值的意思，不需要写==）

in（value1,value2,value3......valuen）：等同于“ 表名1 = value1 or表名2 = value2 or 表名3 = value3...... or 表名n = valuen ”

select * from student where age in(10,17,24);
#上面SQL语句等效于下面语句
select * from student where age = 10 or age=17 or age = 24;

null ：包括is null 和 is not null（is null不能写成 = null，同样，is not null不能写成!=null）

select * from student where address is null;查询地址为空的学生信息

select * from student where address is not null；查询地址不为空的学生信息

order by：对查询结果进行排序。（asc升序，desc降序，asc为默认可以省略）

分为两种。一种是单列排序，另一种是多重排序。

单列排序：以一个字段对查询结果进行排序，最终显示经过一次排序后的查询结果

用字段排序

例子：

select id,name,age,mobile,address from student order by age desc;

用字段别名排序

例子：

select id,name,age as stu_age,mobile,address from student order by stu_age desc;

多重排序：也称多列排序，即先以一个字段对查询结果进行排序，然后在这个排序的基础上再对另一个字段进行排序，最终显示经多次排序后的查询结果；

select * from student order by age desc,name asc;（首先会根据age降序排列，再根据相同的age，比对name进行升序排列）

distinct：去除相同的行（“相同的行”指不同行之间的相同列中的数值相同）
例如：
select distinct age from student;
select distinct age,name from student;
注意：该关键字必须紧跟select关键字的后面，不然无法运行。即如下写法是错误的： select age,distinct name from student

 

算术表达式

MySQL数据库中的算术运算符包括+、-、*和/，其中/运算符的结果为浮点类型的数值
       例如：select age + 1 age from student;
MySQL数据库中没有取余运算符，如果要对数据进行取余运算只能借助数据库mod(x,y)来实现，其中x为被除数，y为除数
       例如：select mod(1,3) as result from dual;

 

dual表为MySQL数据库伪表，在没有目标表的情况下，用来使select语法完整（select子句必须包括from子句）。
例子：

select 1+1 from dual;

2.多行函数（组函数忽略空值，组函数默认考虑重复值，下面有相对处理方法，ifnull函数和distinct）

常用组函数：

avg(字段名)：求平均值

max(字段名)：求最大值

min(字段名)：求最小值

sum(字段名)：求和

count(字段名)：求行数，如果使用*则不会忽略空值的行

stddev(字段名)：求标准差

variance(字段名)：求方差

count、max和min组函数操作的数据的数据类型可以是char、varchar、int或date，但不能为clob；avg、sum、stddev和varlance仅能用在数字类型的数据上。

select count(name) from student;#3，student表中有一条数据中的name为空

select count(distinct name) from student;#2，student表中有一条数据中的name为空，有两条数据name的值重复

select count(ifnull(name,' ')) from student;#4， 将name为空的值置为1个空格

select count(*) from student;#4，*不会忽略空值的行 （因为*代表了所有字段，而主键不为0，则 每行不可能全为空值）

group by

group by用于将表中数据划分为若干个组，group by后面用于指定分组的依据

再给你一串代码

create table student(
id char(1) primary key,
name varchar(8),
sex char(2) default '男' ,
age int(3) default 0
)
 
insert into student values ('1','王明','男',18);
insert into student values ('2','孙丽','女',17);
insert into student values ('3','王明','男',27);
insert into student (id,sex,age) values ('4','男',27);

运行完后

 select count(id) from student group by sex 会根据sex进行分组，但是此时只有数字没有特殊含义，我们需要再count（id）前加上sex即可清楚知道男女分别人数。

select name from student group by name 会根据name分组

select name,sex from student group by name,sex     先根据name分组，name相同再根据sex分组，与前面的order by类似。

注意：

如果select语句中使用group by进行了分组，则select子句中只可以有组函数和分组字段，不能含有其他字段，否则SQL语句报错；

如果group by子句后面跟着order by子句，则order by子句用于排序的字段必须是组函数或分组字段；

如果select语句中含有where、order by，那么group by需放在where之后order by之前，即先获取符合where条件的“有效”数据，再依据group by对数据进行分组，最后再排序；

 

having的由来：

where后面不能使用多行函数，只能使用单行函数和字段，having关键字弥补了这一不足：having子句用于对分组结果进行约束

 select name from student group by name having count(name)>1

注意：

having子句一般和group by子句一起使用，MySQL数据库也可以单独使用，但是Oracle数据库不允许单独使用

having子句必须放在group by子句之后，order by子句之前

3.

多表查询

多个表关联查询需要依据多表之间列关系将其连接起来，这种连接方式分为三种：内连接(inner join)、外连接(outer join)及交叉连接(cross join)

多表查询并不难理解，难的是你如何写出这段代码。我首先给你一段代码，你再去尝试。

create table user_info(
      id int(2) primary key,
      user_name varchar(12) unique,
      password varchar(15) not null,
      real_name varchar(8) not null,
      age int(3)
);

create table address(
      id int(2) primary key,
      user_id int(2) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
);

insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');
 
insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');
 
insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9);

insert into address values(6,6,'刘倩','13159775555','吉林长春');

内连接：

如果依据多个表之间列关系进行内连接，查询结果集仅包括满足连接条件的数据行。内连接分为等值连接、不等值连接以及自然连接，其中等值连接最为常见。
等值连接：在连接条件中使用等号(=)运算符比较被连接列的列值是否相等，分显式等值连接和隐式等值连接：

显式等值连接：（别名看不懂，上面有讲解，ui.*指的是ui的全部字段；我建议你先看隐式等值连接再看显式等值连接，这样比较容易理解。显式等值连接的ui.*,addr.*顺序可以换，后面也可以换，只不过把列的标题换了）
              select ui.*,addr.* from user_info ui inner join address addr on ui.id = addr.user_id;
隐式等值连接：
              select ui.*,addr.* from user_info ui,address addr where ui.id = addr.user_id;

内连接会了，外连接自然会了，只不过把inner换成left。

外连接：

如果依据多表之间列关系进行外连接，查询结果集不仅仅包括满足on连接条件的数据行,还包括不满足on连接条件的数据行。
左外连接（left [outer] join）：返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“left [outer] join”关键字左边的表）不满足on连接条件的数据行；
       select ui.*,addr.* from user_info ui left join address addr on ui.id = addr.user_id;

交叉连接：（没必要理解，没什么实际用处，想知道就去查查笛卡尔积）

左表（“cross join”关键字左边的表）中的每一行与右表（“cross join”关键字右边的表）中的所有行组合，交叉联接的结果是一个笛卡尔积。
       select ui.*,addr.* from user_info ui cross join address addr;