Mysql数据库基本操作（插入、修改、更新、模糊查询、条件查询、消除重复、分页、聚合、分组、多表查询、内连接.....）

Mysql数据库基本操作

1、单表基本查询

先创建数据库

CREATE DATABASE hrm;

创建表

插入数据

INSERT INTO 表名（列名1，列名2 …）VALUES(列值1，列值2…);

#修改语句 update

UPDATE employees SET sex = 'male'; 
UPDATE employees SET age = 23;

查询数据表中所有数据 语法：select * from 表名

select * from t_student;

查询指定字段的显示 语法：select 字段1，字段2，… from 表名

 select c_id,c_name,c_address from t_student；

as 别名 在查询时，默认结果显示的字段和表中字段名相同，可以通过别名来修改显示的样式 语法：select 字段1 as 别名，字段2 别名，… from 表名

 select c_id as 学号 ,c_name as 姓名 ,c_address 地址 from t_student;

在给字段起别名时，可以使用 as ，也可以直接在字段后跟别名，省略 as 。

消除重复数据 在查询数据时，查询结果可能会有很多重复的数据，如果不想重复，可以使用 distinct 来实现去重。 语法：select distinct 字段名 from 表名

 select distinct c_address from t_student;

注意：distinct 在去重时，会比较所有的指定字段，只有完全相同时才认为是重复的。

带条件查询 where 子句 查询数据时，需要根据不同的需求设置条件。 通过 where 子句来设置查询条件

 select * from t_student where c_gender='男';
 

比较运算符

等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>

select * from t_student where c_age < 20;

逻辑运算符

and
or
not

select * from t_student where c_age < 20 and c_gender = ‘女’;

当有外键关联无发删除数据时：

SET FOREIGN_KEY_CHECKS = 0;  // 先设置外键约束检查关闭
 
DROP TABLE mytable;  // 删除数据，表或者视图
 
SET FOREIGN_KEY_CHECKS = 1; // 开启外键约束检查，以保持表结构完整性

获取时间的某个片段： DATE_FORMAT(s_time,‘%Y-%m-%d’)=? （问号表示时间）

1.1 模糊查询

1.1.1 like

% 表示任意多个任意字符
_ 表示一个任意字符

select * from t_student where c_name like '孙';
select * from t_student where c_name like '孙%';
select * from t_student where c_name like '孙_';

1.1.2范围查询

in 表示在一个非连续的范围内 , 可以使用 or 实现

select * from t_students where id in(1,3,8);

between … and … 表示在一个连续的范围内，可以使用 and 实现 ```
空判断 在数据库中，允许在d数据添加是没有数据，使用空值来表示。 空值不等于0，也不等于‘’，需要使用特殊的判断方式

1.1.3判断空值语法：is null

select * from t_student where c_age is null;

1.1.4判断非空值 语法：is not null

select * from t_student where c_age is not null;

查询结果排序 排序是一个在查询数据时非常重要的操作。比如买东西时，想按一定的条件进行有序显示。就需要使用排序

排序使用 order by 子句 asc(默认) 升序 / desc 降序 语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]

单字段排序

select * from t_student order by c_age;
select * from t_student order by c_age asc;

默认使用就是升序排序，可以不指定 asc ，效果相同。

多字段排序 可以对多个字段进行排序，只需将字段的排序方式依次写在 order by 后面即可，字段间使用逗号分隔

select * from t_student order by c_age desc,c_id asc;

1.1.5分页查询查询

数据库时，由于数据较多，在显示过程中不可能将数据全部显示。 可以使用分页查询，只显示指定的一部分数据 语法：select from 表名 limit start=0,count *说明

从start开始，获取count条数据
start默认值为0
需要获取数据的前n条的时候可以直接写 limit n

limit有两个参数，第一个参数表示从第几行数据开始查，第二个参数表示查几条数据，“limit 3,2”表示从第四行数据开始，取两条数据。

select * from t_student limit 3;
select * from t_student limit 2,3;

查询第 N 页 M 条数据，可以通过公式算出：(N - 1) * M （(N - 1) * M表示第一个参数）

1.2 聚合函数在MySQL中提供了一些定义好的函数，利用这些函数提供对数据的统计功能。

常用的聚合函数如下：

sum 求和函数 对指定的字段求和

select sum(c_age) from t_student;

avg 求平均值函数 对指定字段求平均值

select avg(c_age) from t_student;

max 求最大值函数

select max(c_age) from t_student where c_gender = '男';

min 求最小值函数

select min(c_age) from t_student where c_gender = '女';

count 统计记录总数

select count(*) from t_student;
select count(*) from t_student where c_gender = '女';

1.3 IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL() 函数语法格式为：

IFNULL(expression, alt_value)

分组 分组就是将相同数据放到一起进行处理。 单纯的分组是没有意义的，需要配合聚合函数一起使用。 语法： select 分组的字段名,聚合函数… from 表名 group by 分组字段名 having 分组后的条件

注意：在执行 group by 分组时，select 后只能有被分组的字段，不允许有其它字段，除非这些字段在聚合函数中

- 单字段分组
​```sql
select c_gender from t_student group by c_gender;
​```
<img src='images/88.png'>
 
- 多字段分组（了解）
可以对多个字段进行分组，作用同上，需要注意的是多字段时，只有对应字段完全相同，才能分为同一组
​```sql
 select c_gender,c_address from t_student group by c_gender,c_address;
​```
<img src='images/89.png'>
 
- group_concat()
作用：根据分组结果，使用group_concat()来获取分组中指定字段的集合
语法：group_concat(字段名)
​```sql
select c_gender,group_concat(c_name) from t_student group by c_gender;s
​```
<img src='images/90.png'>
 
- 分组和聚和函数使用
单纯的使用分组并没有实际意义，需要使用聚合函数对数据进行处理。
​```sql
select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(*) from t_student group by c_gender;
select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(c_age) from t_student group by c_gender;
​```
<img src='images/91.png'>
 
- having条件子句
having 作用和 where 类似，用来去分组数据进行筛选
where 是对 form 表 中取数据时进行筛选
having 是对 group by 分组后的数据进行筛选
因为在执行顺序上，在执行 where 时，分组还没有执行
得先根据 where 的条件取出数据，才能去取出的数据进行分组。
 
​```sql
select c_gender,group_concat(c_name) from t_student group by c_gender having c_gender = '女';
select c_gender,group_concat(c_name) from t_student where c_age > 50 group by c_gender having c_gender = '女';
​```
 
<img src='images/92.png'>
 
- 分组汇总（无大用，了解即可）
作用：会在分组下方，加一行，显示汇总
语法：with rollup
 
​```sql
select c_gender from t_student group by c_gender with rollup;
select c_gender,count(*) from t_student group by c_gender with rollup;
 
​```
<img src='images/93.png'>

2、多表查询数据

在数据库操作中，数据往往不是存在一张表中的，同一个项目中，根据设计范式，数据可能分散在不同的多张表中，这时查询数据时，就需要多表查询。

2.1普通多表查询（无意义）

作用：直接将表放在from后面，进行读取。 语法：select 表名.字段 … from 表名1，表名2…

 select * from t_student,t_class;

这种查询方式没有任何意义。 在查询时，数据库会将表1中的数据逐条和表2中的所有数据连接，组成一条新记录。 查询的结果为 M * N 条，实际就是笛卡尔积结果。

2.2多表查询

连接条件: 在多个表进行查询时，表与表之间应该是有有关系的，一般会以外键的形式来建立表间的关系。 查询时按照条件建立记录的匹配规则。 比如学生表中保存了学生的信息和所在班级的ID，班级表中保存了班级的信息。 在查询学生的班级信息时，可以通过学生表中的班级ID和班级表中的ID匹配进行查询

select t_student.c_name,t_class.c_name  from t_student,t_class where t_student.c_class_id = t_class.c_id;

2.3表别名

在多表操作时，由于表的名字比较长，在写SQL语句时非常不方便。可以在查询 时，给表起个别名，代替表名来操作 语法： select 别名.字段名… from 表1 as 表1别名，表2 表2别名… [条件]

 select ts.c_name as '姓名' , tc.c_name '班级名' from t_student as ts,t_class tc where ts.c_class_id = tc.c_id;

2.4内连接查询

作用：查询的结果为两个表匹配到的数据 语法： select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 图示： 数据库默认的连接方式就是内连接查询， inner join 可以不显示的写出来。 这种连接方式会以笛卡尔积的形式进行连接。 所以在连接时，必须要给定连接条件。 连接条件使用 on 进行指定。尽量不要使用 where,where在其它连接方式时，指定的连接条件无效。

 select ts.c_name, tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id = tc.c_id;

inner好像可以省略

2.5左连接查询

作用：查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。 语法： select * from 表1 left join 表2 on 表1.列 运算符 表2.列 图示：

 select ts.c_name, tc.c_name from t_student as ts left join t_class tc on ts.c_class_id = tc.c_id;

2.6右连接查询

作用：查询的结果为根据右表中的数据进行连接，如果左表中没有满足条件的记录，则连接空值。 语法： select * from 表1 right join 表2 on 表1.列 运算符 表2.列 图示：

 select ts.c_name, tc.c_name from t_student as ts right join t_class tc on ts.c_class_id = tc.c_id;

在实际工作中，右连接使用的非常少，因为左连接完全可以替代右连接，在连接过程中，只需要调整表的顺序即可。

2.7 子查询

作用：作用：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句 语法： select * from 表1 where 条件 运算符 （select 查询）

外部那个select语句则称为主查询
主查询和子查询的关系

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句

2.8标量子查询

作用：子查询返回的结果是一个数据(一行一列) 语法：主查询 where 条件 比较运算符 (列子查询)

查询班级中年龄大于平均年龄的学生信息

查询班级学生平均年龄
查询大于平均年龄的学生

select * from t_student where c_age > (select avg(c_age) from t_student);

2.9列级子查询

作用：子查询返回的结果是一列(一列多行) 语法：主查询 where 条件 in (列子查询)

查询所有学生所在班级的班级名称

找出学生表中所有的班级 id
找出班级表中对应的名字

select * from t_class where c_id in (select c_class_id from t_student);

行级子查询 作用：子查询返回的结果是一行(一行多列) 语法：主查询 where (字段1,2,…) = (行子查询)

查找班级年龄最大,所在班号最小的的学生

找出最大年龄和最小班号
找出年龄和班号满足条件的学生

select * from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);

2.10 自连接查询

作用：在查询数据时，只有一张表，查询时使用自己连接自己。 语法： select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 where 条件

为什么需要自连接

以要设计表结构来存储 全国 所有的省份和 全国所有的市

设计省信息的表结构provinces
id 省的编号
ptitle 省名称

设计市信息的表结构citys

id 市编号
ctitle 市名称
proid 市所属的省的编号
citys表的proid表示城市所属的省，对应着provinces表的id值

如果需要查询一个省 比如广东省对应的所有的时的信息 ,我们可以使用两个表连接查询。

问题: 能不能将两个表合成一张表呢？

观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的。
这样做的好处在于: 存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。
定义表areas，结构如下

id
atitle
pid

关于这个表的说明:

因为省没有所属的省份，所以可以填写为null
城市所属的省份pid，填写省所对应的编号id
这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

问题: 如果还是要查询广东省对应的所有的市的信息，咱们应该怎么做呢?

areas表和自身进行连接这种形式的连接 就成为自连接。

准备数据

创建areas表的语句如下： 注意，表所在的数据库字符集必须是utf8的，如果不是会导入数据出错

从sql文件中导入数据
source /home/python/Desktop/areas.sql;
自查询create table areas(
aid int primary key,
atitle varchar(20),
pid int
);

查询一共有多少个省

select count(*) from areas where pid is null;

查询省的名称为“山西省”的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

查询市的名称为“广州市”的所有区县

select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';