MySQL进阶操作

MySQL进阶操作

1.数据新增

1.1主键

主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

primary key

创建表时就可以设定一个字段为主键了

create table nihao(id int primary key);

之后写入的数据只能唯一

1.2自增长

自增长 auto_increment语句

1、创建一张新表

create table nihao(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
);

id这个字段就拥有主键和自增长

2.数据查询

• 查询选项
• 别名应用
  • 字段别名
  • 表别名
• where子句
  • 比较运算
  • 逻辑运算
  • 空运算
• group by子句
  • 聚合函数
  • 回溯统计
• having子句
• order by子句
• limit子句
  • 分页制作

1、数据去重

查询选项：用于对查询结果进行简单数据筛选

• 查询选项是在select关键字之后，有两个互斥值
  • all：默认，表示保留所有记录
  • distinct：去重，重复的记录（所有字段都重复）

示例

create table t_39(
	id int primary key auto_increment,
    goods_name varchar(50) not null,
    goods_price decimal(10,2) default 0.00,
    goods_color varchar(20),
    goods_weight int unsigned comment '重量，单位克'
)charset utf8;

insert into t_39 values(null,'mate10',5499.00,'blue',320),
(null,'mate10',5499.00,'gray',320),
(null,'nokia3301',1299,'black',420);

# 考虑所有字段的去重（不含逻辑主键）
select distinct goods_name,goods_price,goods_color,goods_weight from t_39;
select goods_name,goods_price,goods_color,goods_weight from t_39; # 保留所有

# 不考虑颜色去重
select distinct goods_name,goods_price,goods_weight from t_39;
select all goods_name,goods_price,goods_weight from t_39;

2、字段选择&别名

1、明确需要查询的字段信息

• 全部：*
• 部分：确定字段列表

2、确定存在数据冲突或者需要数据保护（通常可以理解为对外提供给别的系统访问）

• 使用别名

  示例

  1、查询商品信息

# 全部查询
select * from t_39;

# 需求为商品名字和价格
select goods_name,goods_price from t_39;

# 别名使用
select goods_name as gn,goods_price gp from t_39;

2、不需要数据源的数据获取：select的表达式本身能算出结果

# 获取当前时间戳和版本号
select unix_timestamp() as now,@@version as version,@@version;

3、数据源

1、单表数据源：最简单的数据源，直接从一个数据表获取

select * from t_27;

2、多表数据源：利用一张表的一条数据匹配另外一张表的所有记录，记录结果为：记录数 = 表1记录数 * 表2记录数；字段数 = 表1字段数 + 表2字段数（笛卡尔积）

select * from t_27,t_30;

3、子查询数据源：数据来源是一个select对应的查询结果

• 查询语句需要使用括号包裹
• 查询结果需要指定别名

select * from (select * from t_27,t_30) t; # 数据有冲突查不出来
select * from (select * from t_27) as t;

4、如果有时候名字较长或者使用不方便，可以利用表别名

select * from t_30 as t;

select t1.*,t2.stu_name from t_27 as t1,t_30 t2;

4、where子句

1、确定要查询的数据需要进行条件筛选

2、使用where进行数据筛选

示例

1、查询t_35表中学生为lily的成绩信息

select * from t_35 where stu_name = 'Lily';

2、因为where是在磁盘取数据时进行条件筛选，此时数据没有进入内存，所以字段别名是无效的

# 错误
select stu_name name,score from t_35 where name = 'Lily';

5、运算符

• 比较运算符
  • >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）
  • between A and B：A和B之间（A小于B），包括A和B本身（数值比较）
  • in (数据1,数据2,…数据N)：在列举的数据之中
  • like ‘pattern’：像上面样的，用于字符串比较
    • _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配abc，ab1，但不能匹配abcd）
    • %：匹配当前位置（往后）任意数量任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd）
• 逻辑运算符
  • and（逻辑与）、or（逻辑或）、not（逻辑非）
• null运算符
  • is null（为空）、is not null（不为空）

1、确定需要使用运算符进行运算

2、根据数据要求使用准确的运算符

示例

1、查询成绩不及格的所有学生信息

# 成绩条件：成绩是数值，又是比大小，可以直接使用比较运算符
select * from t_35 where score < 60;

2、查询成绩在60-90间的学生信息

# 成绩条件：区间60到90，可以有两种解决方案

select * from t_35 where score between 60 and 90;
select * from t_35 where score >= 60 and score <= 90;

3、查询还没有成绩的学生

# 成绩条件：成绩为null，所以不能用比较符号查，只能使用is null实现
select * from t_35 where score is null;

6、group by子句

group by子句：分组统计，根据某个字段将所有的结果分类，并进行数据统计分析

• 分组的目的不是为了显示数据，一定是为了统计数据
• group by子句一定是出现在where子句之后（如果同时存在）
• 分组统计可以进行统计细分：先分大组，然后大组分小组
• 分组统计需要使用统计函数
  • group_concat(字段名)：将组里的某个字段全部保留
  • any_value(字段名)：不属于分组字段的任意一个组里的值
  • count()：求对应分组的记录数量
    • count(字段名)：统计某个字段值的数量（NULL不统计）
    • count(*)：统计整个记录的数量（较多）
  • sum()：求对应分组中某个字段是和
  • max()/min()：求对应分组中某个字段的最大/最小值
  • avg()：求对应分组中某个字段的平均值

1、确定要进行数据统计

2、确定统计对象：分组字段（可以多个）

3、确定要统计的数据形式：选择对应统计函数

4、分组统计

示例

1、创建一张表，存储学生信息

create table t_40(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('m','f','s'),
age tinyint unsigned not null,
class_name varchar(10) not null
);

insert into t_40 values(null,'lxy','m',18,1),
(null,'zmq','m',18,1),
(null,'lc','m',19,2),
(null,'wjw','m',28,3),
(null,'llp','m',29,3),
(null,'gy','f',18,1),
(null,'lfh','f',18,1),
(null,'mf','s',19,1),
(null,'nene','f',21,10),
(null,'bo','m',15,10),
(null,'li','m',28,3);

2、统计每个班的人数

select count(*),class_name from t_40 group by class_name;

3、多分组：统计每个班的男女学生数量

select count(*),class_name,gender from t_40 group by class_name,gender;

4、统计每个班里的人数，并记录班级学生的名字

select count(*),group_concat(name),class_name from t_40 group by class_name;

group by子句有自己明确的位置：在where之后（where可以没有）

#错误
select count(*),group_concat(name),class_name from t_40 group by class_name where gender = '女';
#group by 子句必须在where之后
select count(*),group_concat(name),class_name from t_40 where gender = '男' group by class_name ;

7、分组排序

分组排序：在分组后统计结果时可以根据分组字段进行升序或者降序显示数据

• 默认的系统就会自动对分组结果根据分组字段进行升序排序
• 可以设定分组结果的排序方式
  • group by 字段名 [ASC]：升序排序（默认）
  • group by 字段名 DESC：降序排序

示例

对分组结果女性优先显示：gender为枚举，男值为1，女值为2

select count(*),class_name,gender,group_concat(name),any_value(name) from t_40 group by class_name,gender desc;

8、having子句

having子句：类似于where子句，是用来进行条件筛选数据的

• having子句本身是针对分组统计结果进行条件筛选的

• having子句必须出现在group by子句之后（如果同时存在）

• having针对的数据是在内存里已经加载的数据

• having几乎能做where能做的所有事，但是where却不一定

  • 字段别名（where针对磁盘数据，那时还没有）
  • 统计结果（where在group by之前）
  • 分组统计函数（having通常是针对group by存在的）

1、前面有分组统计

2、需要针对分组统计后的结果进行数据筛选

3、使用having组织条件进行筛选

示例

获取班级人数小于3的班级

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # 多用了一次函数（效率降低）

select class_name,group_concat(name) from t_40 group by class_name having count(*) < 3;

9、order by子句

order by子句：排序，根据某个指定的字段进行升序或者降序排序

• 排序的参照物是校对集
• order by子句在having子句字后（如果同时存在）
• 排序分为升序和降序：默认是升序
  • order by 字段 [ASC]：升序
  • order by 字段 DESC：降序
• 多字段排序：在根据某个字段排序好后，可以再细分排序

示例

1、单字段排序：给所有学生按照年纪大小升序排序

select * from t_40 order by age;
select * from t_40 order by age asc;

2、多字段排序：先性别降序排序，然后按年龄升序排序

select * from t_40 order by gender desc,age;
select * from t_40 order by gender desc,age asc;

10、limit子句

limit子句：限制数据的获取数量（记录数）

• limit子句必须在order by子句之后（如果同时存在）
• limit限制数量的方式有两种
  • limit 数量：限制获取的数量（不保证一定能获取到指定数量）
  • limit 起始位置,数量：限制数据获取的位置以及数量（分页）

示例

1、获取t_40表中前3条数据

select * from t_40 limit 3;

2、获取t_40表中第3条以后的3条数据

select * from t_40 limit 3,3;
select * from t_40 limit 6,3;