内容概要
- SQL与NOSQL语句
- 数据库相关概念
- 常见基本SQL语句
- 字符编码及基本配置文件
- 数据库存储引擎
- 创建表的完整语法
- MySQL字段基本数据类型
- MySQL字段常见约束条件
- SQL语句查询关键字
- 多表查询的两种方式
SQL语句
数据库服务端也可以服务多种类型的客户端
客户端可以是自己开发的，也可以是Python代码编写或JAVA代码编写
SQL
操作关系型数据库的语言
NOSQL
操作非关系型数据库的语言
# 如果我们想要跟数据库进行交互 那么就必须使用数据库指定的语言
'''
SQL有时候也代指关系型数据库
NOSQL有时候也代指非关系型数据库
根据具体情况指向不同
'''
数据库重要概念
"""
强调:小白阶段为了更加方便的理解 做了以下比喻 本质其实有一点点的区别
"""
库 就相当于是 文件夹
表 就相当于是 文件夹里面的文件
记录 就相当于是 文件夹里面的文件中的一行行数据
基本SQL语句
1.SQL语句必须以分号结尾
2.SQL语句编写错误后不需要担心，执行报错即可
数据库我们知道，它其实就是对数据进行增删改查操作的
查看所有库的名称：
show databases;
查看所有表名称
show tables，因为表在库中，我们需要先进入库中才可以查看库中的表
输入命令 use 库名;
这样就显示出了所有在stundent中的表名
查看所有记录
select * from mysql.user;
基于库的增删改查
创建库
create database 库名；
查看指定库
show create database 库名；
编辑库
alter database 库名 charset='utf8';
删除库
drop database 库名
基于表的增删改查
创建表
操作表之前需要先确定库
use student； 我们先进入这个student表中
查看库中所有的表
create table 表名(字段名 字段类型,字段名 字段类型);
查看表
show tables; 查看库下所有的表名称
show create table 表名; 查看指定表信息
describe 表名; 查看表结构
desc 表名;
ps:如果想跨库操作其他表 只需要在表名前加库名即可
desc mysql.user;
编辑表
alter table 表名 rename 新表名 ；
删除表
drop table 表名字;
添加表字段
alter table 表名 add 字段名 字段类型（数字）约束条件;
将新添加的表字段插入到某个字段名下面
alter table 表名 add 字段名 字段类型（数字）约束条件 after 已有字段;
修改字段类型
alter table 表名 modify 新字段类型（数字）约束条件;
修改字段名字和类型
alter table 表名 change 旧表名 新表名 字段类型（数字）约束条件;
基于记录的增删改查
增加数据
insert into 表名 valuse(数据值，)；
查找数据
select 数据名字 from 表名； * 为全部
修改数据
update 表名 set 字段名=新数据 where 筛选条件;
删除数据
delete from 表名
delete from 表明 where 选择条件
字符编码与配置文件
当前用户、版本、编码、端口号
\s查看MySQL相关信息
解决MySQL 5.6.44 编码不统一问题
MySQL5.6及之前的版本编码需要人为统一 之后的版本已经全部默认统一
如果我们想要永久的去配置字符编码，那么我们就需要对MySQL的配置文件进行一些修改
# 1.utf8mb4能够存储表情 功能更强大
# 2.utf8与utf-8是有区别的 MySQL中只有utf8
MySQL 5.6.44默认的配置文件名字为 my_defalut.ini 我们需要对配置文件进行复制并且重命名为my.ini
my.ini配置文件内容修改如下：
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
保存后重启服务即可完成字符编码设置
show variables like '%char%'
MySQL存储引擎
存储引擎：
是数据库针对数据采取的多种存取方式
查看常见存储引擎的方式：
show engines;
常见的四个存储引擎
MyISAM
MySQL5.5版本之前的默认存储引擎
存储数据的速度比较快，但是功能比较少，安全性较低
InnoDB
MySQL5.5版本之后默认的存储引擎
支持事物、行锁、外键、等操作 存取速度没有MyISAM快，但是安全性更高
Memory
基于内存存取数据 仅用于临时表数据存取
BlackHole
任何写入进去的数据都会立刻丢失
我们可以创建四张表来看看在数据库文件中都是什么样子的
1.innodb两个文件
.frm 表结构
.ibd 表数据(表索引)
2.myisam三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory一个文件
.frm 表结构
4.blackhole一个文件
.frm 表结构
MySQL对大小写不敏感
创建表的完整语法
create table 表名(
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个 空格隔开即可
4.最后一行结尾不能加逗号
如图
表结构
字段类型
字段类型之整形
tinyint 1bytes 正负号(占1bit)
smallint 2bytes 正负号(占1bit)
int 4bytes 正负号(占1bit)
bigint 8bytes 正负号(占1bit)
我们也可以取消正负号
create table t6(id tinyint unsigned);
insert into t6 values(-129),(128),(1000);
数字在很多地方都是用来表示限制存储数据的长度
但是在整型中数字却不是用来限制存储长度
create table t1(id int(3)); 不是用来限制长度
insert into t1 values(12345);
create table t2(id int(5) zerofill); 而是用来控制展示的长度
insert into t2 values(123),(123456789);
create table t14(id int);
"""以后写整型无需添加数字"""
严格模式
我在表t2中插入两条数据，但是超出了字段类型的范围，没有报错！
没有报错是因为我们在之前的设置字符编码时，没有设置严格模式
我们可以通过命令show variables liek '%mode%'；来查看现在的MySQL格式
当我们在使用数据库存储数据时，如果数据不符合我们的存储规范，应该直接报错，而不是擅自修改数据并存储进表中，这样会导致我们的数据失真(失去了存储数据的意义)
其实在正常情况下是应该报错的，但是我们在开头配置MySQL字符编码的时候不小心修改了配置文件
我们需要修改一下相应配置！
1.临时修改
set session sql_mode='strict_trans_tables';
在当前客户端有效
set global sql_mode='strict_trans_tables';
在当前服务端有效
2.永久修改
直接修改配置文件
永久修改教程
首先我们也需要对mysql进行停止服务
与配置字符编码的操作一样，我们只需要在my.ini的[mysqld]下面加上
sql_mode=STRICT_TRANS_TABLES 即可！
启动服务并登录mysql就修改成功了~~
我们再次输入超出tinint范围的数字试一下：
成功报错~ 严格模式设置成功
字段类型之浮点型
1.float(15,10)
括号内数字代表，总共存储15位数 小数点后面占10位
2.double(15,10)
括号内数字代表，总共存储15位数 小数点后面占10位
3.decimal(15,10)
括号内数字代表，总共存储15位数 小数点后面占10位
我们新建三个表并插入小数进去，观察它们到底有什么区别
我们可以看到，float的小数 并不精确
三者的核心区别在于精确度不同，
float < double < decimal
字段类型之字符串
char:(定长)
char(4) 代表的意思为，最多存储四个字符，超出就报错，如果不够四个字符会自动用空格补齐四个字符
varchar:（变长）
varchar(4) 代表的意思为，最多存储四个字符，超出就报错，不够则有几位就存储几位
select char_length('字段名') from 表名;
可以查询表中字段明的字符长度
char 与 varchar 对比
char
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的速度较char慢
"""char与varchar的使用需要结合具体应用场景"""
字段类型之枚举与集合
枚举
多选一
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into t15 values(1,'tony','猛男');
insert into t15 values(2,'jason','male');
insert into t15 values(3,'kevin','others');
集合
多选多(多选一)
create table t16(
id int,
name varchar(16),
hobbies set('basketabll','football','doublecolorball')
);
insert into t16 values(1,'jason','study');
insert into t16 values(2,'tony','doublecolorball');
insert into t16 values(3,'kevin','doublecolorball,football');
字段类型之日期类型
datetime 年月日时分秒
date 年月日
time 时分秒
year 年
create table t17(
id int,
name varchar(32),
register_time datetime,
birthday date,
study_time time,
work_time year
);
insert into t17 values(1,'tom','2000-11-11 11:11:11','1938-01-11','11:11:11','2000');
ps:以后涉及到日期相关字段一般都是系统自动获取 无需我们自己操作
字段约束条件
无符号、零填充
限制条件是我们需要在 创建表时字段名后面可以添加的
unsigned# 无符号
无符号的限制条件为，在插入id字段数据时，不能带有负号了
zerofill # 零填充
mysql -uroot -p
create database db1;
use db1;
create table t1(id int(4) unsigned zerofill);
insert into t1 values(3);
select * from t1;
+------+
| id |
+------+
| 0003 |
+------+
非空
我们先来创建以一个新表t2
create table t2 (
id int ,
name varchar(32)
);
然后我们插入数值
insert into t2(id) values(2);
insert into t2 values(3,'小明');
insert into t2(name) values('大白');
mysql> select * from t2;
+------+--------+
| id | name |
+------+--------+
| 2 | NULL |
| 3 | 小明 |
| NULL | 大白 |
+------+--------+
我们可以看到我们没有插入数值的地方 变成了NULL
结论：所有字段类型不加约束条件的情况下都可以为空
not null # 非空
create table t3(
id int not null ,
name varchar(32)
);
insert into t3(name) values('小明');
默认值
default #默认值
默认值就时如果我们加上了默认值的约束条件，那么我们如果不给字段添加数据，字段默认值为default后面的数据，如果添加了数据就根据添加的数据来。
create table t4(
id int,
name varchar(32) default '匿名'
);
insert into t4(id) values(2);
mysql> select * from t4;
+------+--------+
| id | name |
+------+--------+
| 2 | 匿名 |
+------+--------+
insert into t4(id) values(4),(5),(6);
mysql> select * from t4;
+------+--------+
| id | name |
+------+--------+
| 2 | 匿名 |
| 4 | 匿名 |
| 5 | 匿名 |
| 6 | 匿名 |
+------+--------+
唯一值
unique # 唯一
单列唯一
create table t5(
id int unique;
name varchar(32)
);
insert into t5 values(1,'小明'),(2,'小美');
insert into t5 values(1,'大白');
select * from t5;
+------+--------+
| id | name |
+------+--------+
| 1 | 小明 |
| 2 | 小美 |
+------+--------+
联合唯一
unique(字段名，字段名)
create table t7(
id int not null,
port int,
ip varchar(32) ,
unique(ip,port)
);
mysql> select * from t7
-> ;
+----+------+--------------+
| id | port | ip |
+----+------+--------------+
| 1 | 3306 | 127.0.0.1 |
| 2 | 8080 | 127.0.0.1 |
| 3 | 8023 | 198.168.0.11 |
+----+------+--------------+
insert into t7 values(1,'127.0.0.1',3306),(2,'127.0.0.1',8080),(3,'198.168.0.11',8023);
insert into t7 values(4,'127.0.0.1',8023);
主键
primary key # 主键
主键单从约束层面上面而言主键相当于 not null + unique(唯一且非空)
mysql> create table t8(
-> id int primary key,
-> name varchar(32)
-> );
mysql> insert into t8(name) values('小明');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into t8 values(1,'大白');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t8 values(2,'小明');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t8;\
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 小明 |
+----+--------+
InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
1.当表中没有主键也没有其他非空切唯一的字段的情况下
InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
2.当表中没有主键但是有其他非空且唯一的字段 那么会从上往下将第一个该字段自动升级为主键
create table t7(
id int,
age int not null unique,
phone bigint not null unique,
birth int not null unique,
height int not null unique
);
"""
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是'id'(编号)字段
id nid sid pid gid uid
create table userinfo(
uid int primary key,
);
"""
自增
auto_increment # 自增
该约束条件不能单独出现 并且一张表中只能出现一次 主要就是配合主键一起用
mysql> create table t9(
-> id int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
错误的表定义;只能有一个auto列，必须将其定义为键
mysql> create table t9(
-> id int primary key auto_increment);
Query OK, 0 rows affected (0.02 sec)
mysql> create table t10(
-> id int primary key auto_increment,
-> name varchar(32));
insert into t10 values('小明');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t10(name) values('小明');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t10;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
+----+--------+
外键
外键引子
我们需要一张员工表
id name age dep_name dep_desc
1.表语义不明确（到底是员工还是部门）
2.存取数据过于冗余
3.数据的拓展性极差
所以我们如果想要解决上述几个问题 我们需要将表 一分为2
id name age
id dep_name dep_desc
这样的话上述的三个问题就解决了
但是问题是，这样处理后，员工与部门之间没了关系
那么 外键的作用就是用于表示数据与数据之间关系的字段
表关系
表关系、数据关系，其实意思是一样的 知识说法上有区分
表关系总共四种：
一对多
多对多
一对一
没有关系
我们对关系的判断可以采用'换位思考'的原则
一对多关系
我们还是拿上述的员工与部门之间的关系为例子
1. 先站在员工表的角度
问：一名员工是否可以对应多个部门？
答：否
2. 再站在部门表的角度
问：一个部门是否能对应多个员工？
答：是
那么我们就可以凭借上述问题得出结论：
# 一个可以一个不可以 表与表之间的关系就是一对多！
那么针对'一对多'关系，外键字段应该创建在'多'的一方 ，也就是 '员工'
create table staff(
id int primary key auto_increment,
name varchar(32),
dep_id int,
foreign key(dep_id) references department(id)
);
create table department(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除
针对3有简化措施>>>:级联更新级联删除
create table staff1(
id int primary key auto_increment,
name varchar(32),
dep_id int,
foreign key(dep_id) references department1(id)
on update cascade
on delete cascade
);
create table department1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
"""
外键其实是强耦合 不符合解耦合的特性
所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系
"""
多对多关系
以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以
结论:两个都可以 关系就是'多对多'
针对'多对多'不能在表中直接创建 需要新建第三张关系表
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(32),
-> phone varchar(32)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table book(
-> id int primary key auto_increment,
-> book_name varchar(32),
-> book_price bigint
-> );
Query OK, 0 rows affected (0.02 sec)
create table author2book(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
我们通过新建了一张表来做外键，这样的话 作者与书籍的关系就显现出来了！
一对一关系
以用户表与用户详情表为例
1.先站在用户表的角度
问:一个用户能否对应多个用户详情
答:不可以
2.再站在用户详情表的角度
问:一个用户详情能否对应多个用户
答:不可以
结论:两个都可以 关系就是'一对一'或者没有关系
针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name varchar(32),
age int
userinfo_id int unique, # 由于是一对一 所以我们需要将外键变成唯一的
foreign key(userinfo_id) references userinfo(id)
on update cascade
on delete cascade
);
create table userinfo(
id int primary key auto_increment,
hobby varchar(32),
titile varchar(32),
email varchar(64)
);
SQL语句查询关键字
select
指定需要查询的字段信息->
select *
查询所有字段
select score
支持查询指定字段
select char_length(score)
支持对字段进行处理的查询
from
指定需要查询的表信息
from myslq.user
from 表名
SQL语句中关键字的执行顺序宇编写顺序并不一致！
select ip,port from clientinfo;
由于我们查询的ip,port字段是属于表clientinfo的，所有他的执行顺序也是先执行 from clientinfo ,再select ip,port
对应关键字的编写顺序并不需要特别刻意，再前期我们对MySQL不熟悉的情况下我们可以根据需求分步骤操作！
前期数据准备
我们提前准备一些数据，方便对下面的方法去做讲解
登录mysql 然后创建一个名为db3的库
create table staff_info(
id int primary key auto_increment,
name varchar(32) not null, # 名字非空
gender enum('male','female') not null default'male',# 可以选male,或者female,默认值为male
age int(3) unsigned not null default 28, # 无符号，非空，默认为28
hire_date date not null, # 日期非空
post varchar(64),
post_comment varchar(128),
salary double(15,2),
office int,# 一个部门一个屋子
department_id int
);
mysql> desc staff_info;
+---------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| gender | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(64) | YES | | NULL | |
| post_comment | varchar(128) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| department_id | int(11) | YES | | NULL | |
+---------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)
#插入记录
#三个部门：教学，销售，运营
insert into staff_info(name,gender,age,hire_date,post,salary,office,department_id) values
('sui','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
编写SQL语句->小技巧
针对select后面的字段名我们可以暂时先用*符号来占位置，最后回来再修改
在实际应用中select后面很少直接写* 因为 * 代表全部，当表中字段与数据非常多的时候非常浪费数据库资源
'''
SQL语句的编写类似于代码的编写，不是一次性就能完成的，需要反复的修改，修补
'''
查询关键字 -> where筛选
查询所有id大于等于4小于等于7的数据：
解决方法1
select * from staff_info where id>=4 and id<=7;
我们可以直接在where后面指定条件，这样的话我们就可以通过select找到我们的需求
解决方法2
select * from staff_info where id between 4 and 7; # between 在...之间
我们也可以使用关键字between 来增加我们的筛选条件范围
查询薪资是20000或者18000或者17000的数据:
解决方法1
select * from staff_info where salary=17000 or salary=20000 or salary=18000;
这种方法虽然繁琐，但是逻辑比较清晰
解决方法2
select * from staff_info where salary in(17000,18000,20000);支持成员运算
查询id小于3大于6的数据
解决方法1
select * from staff_info where not id between 3 and 6;
因为SQL支持逻辑运算符，所以我们可以通过not来做一个取反操作
解决方法2
select * from staff_info where id<3 or id>6;
查询员工姓名中包含字母o的员工姓名与薪资
条件不够精确的查询称之为 -> 模糊查询
模糊查询：
# 模糊查询的关键字为： like
# 模糊查询的常用符号：
%:匹配任意个数的任意字符
-:匹配单个个数的任意字符
解决方法1
select name,salary from staff_info where name like '%o%';
查询员工姓名是由四个字符组成的员工姓名与其薪资
解决方法1
select name,salary from staff_info where name like '____';
解决方法2
select name,salary from staff_info where char_length(name)=4;
查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
解决方法
因为null是关键字而不是字符，所以不能通过=号进行筛选比较
select name,post_comment from staff_info where post_comment is NULL;
"""
在MySQL中也有很多内置方法 我们可以通过查看帮助手册学习
help 方法名
"""
查询关键字 -> group by分组
分组就是将一个个单独的数据，组成到一起，变成一个整体
# 将人类 按照性别 进行分组
# 将人类 按照肤色 进行分组
# 将人类 按照地域 进行分组
分组的目的是为了更方便，更好的去统计相关的数据
# 人类男女性别占比
# 人类种族肤色占比
# 人类活动地域占比
聚合函数
专门用于'分组之后'的数据统计
max\min\sum\avg\count
最大值、最小值、求和、平均值、计数
将员工按照部门分组
select * from staff_info group by post;
"""
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
或者直接修改my.ini文件 将my.ini文件的sql_mode改为：sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
select post from emp group by post;
select age from emp group by age;
分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
"""
解决方法
select post from staff_info group by post;
获取每个部门的最高工资
我们注意题目，分组不一定是必要的，有的时候有更简单的解决方法
要不要分组我们完全可以从题目的需求中分析出来尤其是出现关键字 每个 平均'
解决方法1
select post,max(salary) from staff_info group by post;
针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from staff_info group by post;
注意，as 后面的字符 只在本次select 结果显示时生效，下次查询如果没有 as 命名的话还是原来的字段名！
一次性获取部门薪资(最高薪，最低薪，平均薪资，月支出)相关统计
解决方法
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
统计每个部门的人数
解决方法
select post,count(id) as 部门人数 from staff_info group by post;
我们不能直接去count(name) 是因为 name 里面可能会有重复的信息！ 但是每个表的id作为主键是非空且唯一的
统计每个部门的部门名称以及部门下的员工姓名
select post,name from staff_info group by post;
分组以外的字段无法直接填写 需要借助于方法 group_concat(字段名)
我们也可以直接在group_concat(name,age) 这样填写，这样就会将名字与age拼起来
在group_concat()中就类似于python的字符串+字符串，也可以自己增加字符串拼接
解决方法
select post,group_concat(name) from staff_info group by post;
查询关键字 -> having过滤
having与where 本质是一样的 都是用来对数据进行筛选的
但是 where 关键字是用于 分组之前 而 having 关键字是用于分组之后
统计各部门年龄在30岁以上的员工平均工资，并且保留大于10000的数据
解决方法
select post,avg(salary) from staff_info where age>30 group by post having avg(salary) > 10000;
查询关键字 -> distinct去重
去重有一个必须的条件也是很容易被忽略的条件
# 数据必须一模一样才可以去重
select distinct age,name,post from staff_info;
因为 distinct关键字 去重的关键字针对的是多个字段组合的结果，所以当他们组合起来一样的话就失去了单个字段的去重效果
查询关键字 -> order by排序
select age from staff_info order by age; # 默认升序 asc（可以省略）
select age from staff_info order by age desc 修改为降序
select * from staff_info order by age,salary desc;
这样写的话就相当于先排年龄的'升序'，然后再根据工资的'降序'来排序
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序(升序)
我们可以一步一步做，不需要一次性写完。
1.先筛选出所有年龄大于10岁的员工
select * from staff_info where age > 10;
2.再对他们按照部门分组统计平均薪资
select post,avg(salary) from staff_info where age > 10 group by post;
3.针对分组的结果做二次筛选
select post,avg(salary) from staff_info where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
select post,avg(salary) from staff_info where age > 10 group by post having avg(salary)>1000 order by avg(salary);
"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""
查询关键字 -> limit 分页
当表中数据特别多的情况下，我们很少会一次性获取所有的数据，我们根据需求来看，可能有的时候只需要一点点即可，因为分页的处理会减少数据的读取量，我们做了分页的设置也能降低减少多余的资源消耗
select * from staff_info limit 3;
直接展示从头开始的3条数据
select * from staff_info limit 3,3;
从第三条数据之后，往后展示三条 相当与 前不取的意思
查询关键字 -> 正则表达式
regexp为正则表达式的关键字 我们再这个关键字后面填写相应正则即可
select * from staff_info where name regexp '^s';
多表查询的思路
多表查询我们可以通过 表名.字段名的方法去查询
我们先来创建一张表
select * from staff1,department1; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
select * from staff1,department1 where staff1.dep_id=department1.id;
多表查询的方法
方法1 多表连查
我们还是使用
department1 以及 staff1 这两个表进行操作演示
select * from staff1
+----+--------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | dragon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+--------+--------+------+--------+
mysql> select * from department1;
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 财务 |
+-----+--------------+
财务的id 我修改为205了！！！！！
方式1
连表操作
inner join 内连接
select * from staff1 inner join department1 on department1.id=staff1.dep_id;
只连接两张表中公有的数据部分
我们应该在 inner 与 join 两侧写上 查询的表名
只连接两张表中公有的数据部分
在查询除了两表公共部分信息内容外，应该写 表名.字段名 这样的形式去筛选数据
left join 左连接
select * from staff1 left join department1 on department1.id=staff1.dep_id;
以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充
right join 右连接
select * from staff1 right join department1 on department1,id=staff1.dep_id
以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充
我们可以通过这一张图的显示来看出内连接 左连接 右连接 的区别
全连接
union
select * from staff1 left join department1 on department1.id=staff1.dep_id union select * from staff1 right join department1 on department1.id=staff1.dep_id
以左右表为基准 展示所有的数据 各自没有的全部NULL填充
'''
学会了连表操作之后也就可以连接N多张表
思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
'''
方式2
子查询
子查询就是将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
例子：求姓名是nick的员工部门名称
子查询在此时就相当于我们日常解决问题的方式：
首先我们需要根据nick获取部门编号
select dep_id from staff1 where name='nick'
其次再根据部门表编号获取部门名称
select name from department1 where id=(select dep_id from staff1 where name='nick');
'在很多时候多表查询需要结合实际情况判断用那种，更多时候甚至是互相配合使用'
额外知识点补充
concat 连接
主要用于分组之前的字段拼接操作
select concat(name,'&',sex) from staff1;
concat_ws 主要用于拼接多个字段并且保持中间的连接符一致
select concat_ws('&',name,age,sex) from staff1;
exists
sql语句1 exists （sql语句2）
sql语句2 有结果的情况下会执行sql语句1 如果没有结果则返回空数据
select * from staff1 where exists (select name from department1);
__EOF__
