MySQL - 总结

时间:2022-05-24 07:17:56

MySQL总结

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型:

#1、DDL语句    数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
#2、DML语句    数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3、DCL语句    数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE

关闭服务端 net stop MySQL
启动服务端 net start MySQL

基本操作

增 create database db1 charset utf8;
查 show database;
   show create database db1;
改 alter database db1 charset gbk;
删 drop database db1

 

查看所在库 select database();
切换库 use db1;
增 creat table t1(id int, name char);
查 show tables;
   show create table t1;
   desc t1; 查看表结构
改 alter table t1 add sex char; 添加字段
   alter table t1 drop sex; 删除字段
   alter table t1 modify name char(8); 修改字段类型
   alter table t1 change name Name char(16); 修改原字段名及该字段类型
删 drop table t1;

 

  • 记录

增 insert into db1.t1(id,name) values 
   (1,xionger),(2,xiongda);
查 select id,name from db1.t1  查指定字段
   select * from db1.t1  查所有(不推荐使用)
改 update t1 set name=DD where id=2;
删 delete from t1 where id=2; 删除指定字段记录
   truncate t1; 清空表中记录(重置整张表)

 

表相关操作

存储引擎

存储引擎即表类型,mysql根据不同的表类型会有不同的处理机制

Innodb : 默认的存储引擎,查询速度相对myisam慢,但是更安全 特点是支持行锁,支持外键

innodb类型表有两个表文件
.frm : 表结构相关
.ibd : 数据相关 (innodb的索引就是用数据组织的,以主键为依据组织数据,用树型结构减少IO优化查询)

 

myisam : 老版本的存储引擎

myisam类型表有三个表文件
.frm ; 表结构相关
.MYD : 数据相关
.MYI : 索引文件

 

memory : 内存引擎,数据全部存在内存中,断电数据消失

.frm : 表结构相关

 

blackhole : 黑洞引擎,无论存什么数据,立马消失

.frm : 表结构相关

查看MySQL支持的存储引擎

show enginesG
查看正在使用的存储引擎
show variables like storage_engine%;
创建表时指定存储引擎
create table t1(id int)engine=innodb;

语法

  • 创建表

create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
)
  • 1. 在同一张表中,字段名是不能相同
  • 2. 宽度和约束条件可选
  • 3. 字段名和类型是必须的
  • 修改表

修改表名
ALTER TABLE 表名 RENAME 新表名;
增加字段
ALTER TABLE 表名 
                ADD 字段名  数据类型 [完整性约束条件…],
                ADD 字段名  数据类型 [完整性约束条件…];
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
删除字段
ALTER TABLE 表名 DROP 字段名;
修改字段
ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

 

  • 复制表

复制表结构+记录 (key不会复制: 主键、外键和索引)
create table t2 select * from t1;
只复制表结构
select * from service where 1=2;  条件为假,查不到任何记录
reate table t4 like employees;

 

类型

  • 数值类型

整型 
TINYINT SMALLINT MEDIUMINT INT BIGINT
为该类型指定宽度时,仅仅只是指定查询结果的显示宽度,与存储范围无关

 

浮点型
float[(M,D)] [UNSIGNED] [ZEROFILL]
定义:m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
精确度:随着小数的增多,精度变得不准确
?
double[(M,D)] [UNSIGNED] [ZEROFILL]
定义:m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
精确度:随着小数的增多,精度比float要高,但也会变得不准确
?
decimal[(m[,d])] [unsigned] [zerofill]
定义: 准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
精确度: 随着小数的增多,精度始终准确,对于精确数值计算时需要用此类型,decaimal能够存储精确值的原因在于其内部按照字符串存储。
日期类型
YEAR 年
DATE 年-月-日
TIME 时:分:秒
DATETIME 年-月-日 时:分:秒
  • 字符串类型

char : 定长,数据超出预定长度报错,不够用空格补位
    - 特点: 查询速度快,单浪费空间
    - 检索:在检索或者说查询时,查出的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = PAD_CHAR_TO_FULL_LENGTH;)
    - 注意:精准查找时(select * from user where name=xionger;),char类型补充的空格会自动去掉进行匹配,但是用like模糊查找时(select * from user name like xionger;),char类型补充的空格也会进行匹配
varchar : 变长,数据超出预定长度报错,不够就是实际长度
    - 特点: 精准,节省空间,查询较慢
    - 检索:尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容
?
查看字段长度 : char_length(字段)
    select char_length(name) from user;
  • 集合与枚举类型

枚举enum: 多选一
集合set: 多选多

 

约束条件

约束条件与数据类型的宽度一样,都是可选参数

作用:用于保证数据的完整性和一致性

PRIMARY KEY (PK)    标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT    为该字段设置默认值
?
UNSIGNED 无符号
ZEROFILL 使用0填充

 

  • not null / default

not null - 不可空
null - 可空
default 默认值
创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table user(
    id int,
    name char(16) not null,
    sex char(6) not null default "male"
);

 

  • unique

单列唯一
create table user(
    id int unique,
    name char(16)
);
联合唯一
create table server(
    id int unique,
    ip char(15),
    port int,
    ip port,unique(ip,port)
)

 

  • primary key

从约束角度看: 主键等于not null   unique
1.一张表中有且有一个主键
2.若没有设置主键则会由上到下检索非空且唯一的字段作为主键
3.若没有主键也没有非空且唯一的字段,就采用默认的隐藏字段作为主键(7个bytes),这样丧失了查询效率
innodb类要依据主键组织数据结构(idb文件)
索引的目的是一步步缩小查询范围,减少IO次数

 

  • auto_increment

1.auto_increment通常与primary key连用
2.auto_increment通常加给id字段
3.auto_increment只能给被定义成key(unique key,primary key)的字段加

 

表关系

所有数据在一张表中导致: 组织结构不清晰,扩展性差,浪费空间

foreign key 外键是一种约束,约束两张表的关系

约束1:创建表 先建立被关联表,才能建关联表
约束2:查数据 被关联表先插入数据,关联表才能插入数据
约束3:删数据 先删除关联表中的数据,才能删被关联表的数据
约束4:不能改关联字段 

 

级联: 同步更新,同步删除

on update cascade # 同步更新
on delete cascade # 同步删除
注: 级联是绑定给外键的

 

多对一:

create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_comment char(64)
);
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum(male,female) not null default male,
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade # 同步更新 
    on delete cascade # 同步删除
);

多对多:

create table author(
    id int primary key auto_increment,
    name varchar(20)
);
create table book(
    id int primary key auto_increment,
    name char(16),
    price int
);
?# 创建第三张表
create table author2book(
    id int not null unique auto_increment,
    author_id int not null,
    book_id int not null,
    fk_author foreign key(author_id) references author(id) on delete cascade on update cascade,
    fk_book foreign key(book_id) references book(id) on delete cascade on update cascade,
    primary key(author_id,book_id)
);

 

表查询

单表查询

MySQL - 总结MySQL - 总结
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum(male,female) not null default male, #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#查看表结构
mysql> desc emp;
 -------------- ----------------------- ------ ----- --------- ---------------- 
| Field        | Type                  | Null | Key | Default | Extra          |
 -------------- ----------------------- ------ ----- --------- ---------------- 
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum(male,female) | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
 -------------- ----------------------- ------ ----- --------- ---------------- 

#插入记录
#三个部门:教学,销售,运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(egon,male,18,20170301,老男孩驻沙河办事处外交大使,7300.33,401,1), #以下是教学部
(alex,male,78,20150302,teacher,1000000.31,401,1),
(wupeiqi,male,81,20130305,teacher,8300,401,1),
(yuanhao,male,73,20140701,teacher,3500,401,1),
(liwenzhou,male,28,20121101,teacher,2100,401,1),
(jingliyang,female,18,20110211,teacher,9000,401,1),
(jinxin,male,18,19000301,teacher,30000,401,1),
(成龙,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)
;
案例表信息

 

 

较完整的查询语句
select id,name from emp
    where id > 1 and name like %xx%
    group by city
    having 分组后的条件
    order by 排序依据
    limit 展示条数;

 

  • DISTINCT 去重查询

查出所有的岗位(去掉重复)
SELECT DISTINCT post FROM emp;

 

MySQL - 总结MySQL - 总结
 -------------------- 
| post               |
 -------------------- 
| 张江第一帅形象代言 |
| teacher            |
| sale               |
| operation          |
 -------------------- 
结果

 

  • 四则运行查询

查年薪,重命名
SELECT name, salary*12 FROM emp;
SELECT name, salary*12 AS year_salary FROM emp;

 

MySQL - 总结MySQL - 总结
 -------- ------------- 
| name   | year_salary |
 -------- ------------- 
| jason  |    87603.96 |
| egon   | 12000003.72 |
| kevin  |    99600.00 |
| tank   |    42000.00 |
| owen   |    25200.00 |
| jerry  |   108000.00 |
| nick   |   360000.00 |
| sean   |   120000.00 |
| 歪歪   |    36001.56 |
| 丫丫   |    24004.20 |
| 丁丁   |    12004.44 |
| 星星   |    36003.48 |
| 格格   |    48003.96 |
| 张野   |   120001.56 |
| 程咬金 |   240000.00 |
| 程咬银 |   228000.00 |
| 程咬铜 |   216000.00 |
| 程咬铁 |   204000.00 |
 -------- ------------- 
结果

 

  • CONCAT 定义显式

查出所有员工的名字,薪资,格式为:
    <名字:xxx>    <薪资:666666>
SELECT CONCAT(姓名: ,name,  年薪: , salary*12)  AS year_salary FROM emp;
MySQL - 总结MySQL - 总结
 ------------------------------- 
| year_salary                   |
 ------------------------------- 
| 姓名: jason  年薪: 87603.96   |
| 姓名: egon  年薪: 12000003.72 |
| 姓名: kevin  年薪: 99600.00   |
| 姓名: tank  年薪: 42000.00    |
| 姓名: owen  年薪: 25200.00    |
| 姓名: jerry  年薪: 108000.00  |
| 姓名: nick  年薪: 360000.00   |
| 姓名: sean  年薪: 120000.00   |
| 姓名: 歪歪  年薪: 36001.56    |
| 姓名: 丫丫  年薪: 24004.20    |
| 姓名: 丁丁  年薪: 12004.44    |
| 姓名: 星星  年薪: 36003.48    |
| 姓名: 格格  年薪: 48003.96    |
| 姓名: 张野  年薪: 120001.56   |
| 姓名: 程咬金  年薪: 240000.00 |
| 姓名: 程咬银  年薪: 228000.00 |
| 姓名: 程咬铜  年薪: 216000.00 |
| 姓名: 程咬铁  年薪: 204000.00 |
 ------------------------------- 
结果

 

  •  WHERE 约束

where字句中可以使用:

1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是10或20或30
4. like ‘xxx%‘
    pattern可以是%或_,
    %表示任意多字符
    _表示一个字符 
5. 逻辑运算符:多个条件直接可以使用逻辑运算符 and or not

1.查看岗位是teacher的员工姓名、年龄
select name,age from emp where post = teacher;

 

MySQL - 总结MySQL - 总结
 ------- ----- 
| name  | age |
 ------- ----- 
| egon  |  78 |
| kevin |  81 |
| tank  |  73 |
| owen  |  28 |
| jerry |  18 |
| nick  |  18 |
| sean  |  48 |
 ------- ----- 
结果
2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from emp where post=teacher and age > 30; 

 

MySQL - 总结MySQL - 总结
 ------- ----- 
| name  | age |
 ------- ----- 
| egon  |  78 |
| kevin |  81 |
| tank  |  73 |
| sean  |  48 |
 ------- ----- 
结果
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,age,salary from emp where post=teacher and salary between 9000 and 10000;
MySQL - 总结MySQL - 总结
 ------- ----- ---------- 
| name  | age | salary   |
 ------- ----- ---------- 
| jerry |  18 |  9000.00 |
| sean  |  48 | 10000.00 |
 ------- ----- ---------- 
结果
4. 查看岗位描述不为NULL的员工信息
select * from emp where post_comment is not null;

 

MySQL - 总结MySQL - 总结
Empty set (0.00 sec)
结果
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post=teacher and salary in (10000,9000,30000);

 

MySQL - 总结MySQL - 总结
 ------- ----- ---------- 
| name  | age | salary   |
 ------- ----- ---------- 
| jerry |  18 |  9000.00 |
| nick  |  18 | 30000.00 |
| sean  |  48 | 10000.00 |
 ------- ----- ---------- 
结果

 

6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post=teacher and salary not in (10000,9000,30000);

 

MySQL - 总结MySQL - 总结
 ------- ----- ------------ 
| name  | age | salary     |
 ------- ----- ------------ 
| egon  |  78 | 1000000.31 |
| kevin |  81 |    8300.00 |
| tank  |  73 |    3500.00 |
| owen  |  28 |    2100.00 |
 ------- ----- ------------ 
结果
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary*12 from emp where post=teacher and name like j%;

 

MySQL - 总结MySQL - 总结
 ------- ----------- 
| name  | salary*12 |
 ------- ----------- 
| jerry | 108000.00 |
 ------- ----------- 
结果

 

  • GROUP BY 分组查询

1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的

2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等

3、大前提: 可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

MySQL - 总结MySQL - 总结
#查看MySQL 5.7默认的sql_mode如下:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#!!!注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。


#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
mysql> set global sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;

mysql> select @@global.sql_mode;
 ------------------- 
| @@global.sql_mode |
 ------------------- 
|                   |
 ------------------- 
1 row in set (0.00 sec)

mysql> select * from emp group by post; 
 ---- ------ -------- ----- ------------ ---------------------------- -------------- ------------ -------- ----------- 
| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
 ---- ------ -------- ----- ------------ ---------------------------- -------------- ------------ -------- ----------- 
| 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
|  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
 ---- ------ -------- ----- ------------ ---------------------------- -------------- ------------ -------- ----------- 
4 rows in set (0.00 sec)


#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果,默认都是组内的第一条记录,但其实这是没有意义的

mysql> set global sql_mode=ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> quit #设置成功后,一定要退出,然后重新登录方可生效
Bye

mysql> use db1;
Database changed
mysql> select * from emp group by post; #报错
ERROR 1055 (42000): db1.emp.id isnt in GROUP BY
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
 ---------------------------- ----------- 
| post                       | count(id) |
 ---------------------------- ----------- 
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
 ---------------------------- ----------- 
4 rows in set (0.00 sec)
ONLY_FULL_GROUP_BY

 

单独使用GROUP BY关键字分组
    SELECT post FROM emp GROUP BY post;
    注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM emp GROUP BY post;#按照岗位分组,并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM emp GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from emp group by post;#按照岗位分组,并查看每个组有多少人
如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
  • 聚合函数

#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组

示例:
    SELECT COUNT(*) FROM emp;
    SELECT COUNT(*) FROM empe WHERE depart_id=1;
    SELECT MAX(salary) FROM emp;
    SELECT MIN(salary) FROM emp;
    SELECT AVG(salary) FROM emp;
    SELECT SUM(salary) FROM emp;
    SELECT SUM(salary) FROM emp WHERE depart_id=3;

 

1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;

 

MySQL - 总结MySQL - 总结
 -------------------- -------------------------------------- 
| post               | group_concat(name)                   |
 -------------------- -------------------------------------- 
| operation          | 张野,程咬金,程咬银,程咬铜,程咬铁     |
| sale               | 歪歪,丫丫,丁丁,星星,格格             |
| teacher            | egon,kevin,tank,owen,jerry,nick,sean |
| 张江第一帅形象代言 | jason                                |
 -------------------- -------------------------------------- 
结果

 

2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
MySQL - 总结MySQL - 总结
 -------------------- ----------- 
| post               | count(id) |
 -------------------- ----------- 
| operation          |         5 |
| sale               |         5 |
| teacher            |         7 |
| 张江第一帅形象代言 |         1 |
 -------------------- ----------- 
结果

 

3.查询公司内男员工和女员工的个数
select gender,count(id) from emp group by gender;

 

MySQL - 总结MySQL - 总结
 -------- ----------- 
| gender | count(id) |
 -------- ----------- 
| male   |        10 |
| female |         8 |
 -------- ----------- 
结果

 

4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
MySQL - 总结MySQL - 总结
 -------------------- --------------- 
| post               | avg(salary)   |
 -------------------- --------------- 
| operation          |  16800.026000 |
| sale               |   2600.294000 |
| teacher            | 151842.901429 |
| 张江第一帅形象代言 |   7300.330000 |
 -------------------- --------------- 
结果
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;

 

MySQL - 总结MySQL - 总结
 -------------------- ------------- 
| post               | max(salary) |
 -------------------- ------------- 
| operation          |    20000.00 |
| sale               |     4000.33 |
| teacher            |  1000000.31 |
| 张江第一帅形象代言 |     7300.33 |
 -------------------- ------------- 
结果

 

6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;

 

MySQL - 总结MySQL - 总结
 -------------------- ------------- 
| post               | min(salary) |
 -------------------- ------------- 
| operation          |    10000.13 |
| sale               |     1000.37 |
| teacher            |     2100.00 |
| 张江第一帅形象代言 |     7300.33 |
结果
7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
select gender,avg(salary) from emp group by gender;

 

MySQL - 总结MySQL - 总结
 -------- --------------- 
| gender | avg(salary)   |
 -------- --------------- 
| male   | 110920.077000 |
| female |   7250.183750 |
 -------- --------------- 
结果

 

  • HAVING 分组后过滤

HAVING与WHERE不一样的地方在于!!!!!!

执行优先级从高到低:where > group by > having 
1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from emp group by post having count(id) < 2;

 

MySQL - 总结MySQL - 总结
 -------------------- -------------------- ----------- 
| post               | group_concat(name) | count(id) |
 -------------------- -------------------- ----------- 
| 张江第一帅形象代言 | jason              |         1 |
 -------------------- -------------------- ----------- 
结果
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) > 10000;

 

MySQL - 总结MySQL - 总结
 ----------- --------------- 
| post      | avg(salary)   |
 ----------- --------------- 
| operation |  16800.026000 |
| teacher   | 151842.901429 |
 ----------- --------------- 
结果

 

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) > 10000 and avg(salary) <20000;

 

MySQL - 总结MySQL - 总结
 ----------- -------------- 
| post      | avg(salary)  |
 ----------- -------------- 
| operation | 16800.026000 |
 ----------- -------------- 
结果

 

  • ORDER BY 排序

正序 ASC 默认

倒序 DESC

1. 查询所有员工信息,先按照age升序排序,如果age相同则按照hire_date降序排序
select * from emp ORDER BY age asc,hire_date desc;
MySQL - 总结MySQL - 总结
 ---- -------- -------- ----- ------------ -------------------- -------------- ------------ -------- ----------- 
| id | name   | gender | age | hire_date  | post               | post_comment | salary     | office | depart_id |
 ---- -------- -------- ----- ------------ -------------------- -------------- ------------ -------- ----------- 
|  1 | jason  | male   |  18 | 2017-03-01 | 张江第一帅形象代言 | NULL         |    7300.33 |    401 |         1 |
| 12 | 星星   | female |  18 | 2016-05-13 | sale               | NULL         |    3000.29 |    402 |         2 |
| 17 | 程咬铜 | male   |  18 | 2015-04-11 | operation          | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁 | female |  18 | 2014-05-12 | operation          | NULL         |   17000.00 |    403 |         3 |
| 16 | 程咬银 | female |  18 | 2013-03-11 | operation          | NULL         |   19000.00 |    403 |         3 |
| 11 | 丁丁   | female |  18 | 2011-03-12 | sale               | NULL         |    1000.37 |    402 |         2 |
|  6 | jerry  | female |  18 | 2011-02-11 | teacher            | NULL         |    9000.00 |    401 |         1 |
| 15 | 程咬金 | male   |  18 | 1997-03-12 | operation          | NULL         |   20000.00 |    403 |         3 |
|  7 | nick   | male   |  18 | 1900-03-01 | teacher            | NULL         |   30000.00 |    401 |         1 |
| 13 | 格格   | female |  28 | 2017-01-27 | sale               | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野   | male   |  28 | 2016-03-11 | operation          | NULL         |   10000.13 |    403 |         3 |
|  5 | owen   | male   |  28 | 2012-11-01 | teacher            | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫   | female |  38 | 2010-11-01 | sale               | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪   | female |  48 | 2015-03-11 | sale               | NULL         |    3000.13 |    402 |         2 |
|  8 | sean   | male   |  48 | 2010-11-11 | teacher            | NULL         |   10000.00 |    401 |         1 |
|  4 | tank   | male   |  73 | 2014-07-01 | teacher            | NULL         |    3500.00 |    401 |         1 |
|  2 | egon   | male   |  78 | 2015-03-02 | teacher            | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin  | male   |  81 | 2013-03-05 | teacher            | NULL         |    8300.00 |    401 |         1 |
 ---- -------- -------- ----- ------------ -------------------- -------------- ------------ -------- ----------- 
结果

 

2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from emp group by post having avg(salary) > 10000 order by avg(salary) asc;
MySQL - 总结MySQL - 总结
 ----------- --------------- 
| post      | avg(salary)   |
 ----------- --------------- 
| operation |  16800.026000 |
| teacher   | 151842.901429 |
 ----------- --------------- 
结果
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from emp group by post having avg(salary) > 10000 order by avg(salary) desc;
MySQL - 总结MySQL - 总结
 ----------- --------------- 
| post      | avg(salary)   |
 ----------- --------------- 
| teacher   | 151842.901429 |
| operation |  16800.026000 |
 ----------- --------------- 
结果
  • LIMIT 限制查询记录的条数

示例:
    SELECT * FROM emp ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 
    
    SELECT * FROM emp ORDER BY salary DESC
        LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条

    SELECT * FROM emp ORDER BY salary DESC
        LIMIT 5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条

 

索引

mysql中存在一种专门的数据结构,key,又叫索引,通过该数据结构可以减少IO次数,从而加速查询效率
- index key : 只加速查询效果,没有约束功能
- unique key : 不仅有加速查询效果,还附加了约束功能
- primary key : 不仅有加速查询效果,还附加了约束功能,并且innodb存储引擎会按照主键字段的值老组织表中所有的数据,所以一张innodb表中有且只有一个主键