mysql进阶

一、复习

MySQL 管理文件

# 库相关的
# 创建数据库
create database 数据库名 charset 编码;

# 查看数据库
show databases;
show create database 库名;
select database();

# 选择数据库
use 数据库名

# 删除数据库
drop database 数据库名;

# 修改数据库编码
alter database 库名 charset 编码;

# 表相关的操作
# 1 创建表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],  
字段名3 类型[(宽度) 约束条件]   
);

# 注意
"""
1. 一张表里面 字段名是不能相同的
2. 宽度和约束条件是可写可不写的
3. 字段名和类型是必选要写的
4. 最后一行字段不要写逗号
"""

# 1.1 都有哪些类型:
整型 tinyint int(2) bigint 对于整数你写它的宽度没有一点意义 除了整数类型你可以写 其他的都建议写
小数 float(5, 2) double decimal
字符串 char(10) root  root0000000 varchar(10)
枚举与集合 enum 单选 set 多选
时间 date datetime time year

# 都有哪些约束条件
not null
unique key  唯一
primary key 主键 标识唯一 一条记录 提示我们的查询效率
auto_increment 自增
default 默认值
unsigned  无符号


# 2. 修改表
alter table 表名 add/modify/change/rename
add 字段名 数据类型[(宽度) 约束条件]
modify 字段名 新数据类型[(宽度) 约束条件]
change 旧字段 新字段 新数据类型[(宽度) 约束条件]

# 3.  查询表
show tables();
desc 表名; # 看表结构

# 删除表
drop table 表名;

# 增 
insert into 表(字段1) values
(值1, 值2),
(值1, 值2),
(值1, 值2);


# 查询
select * from 表名;
select 字段 from 表名;

where 行筛选 算术运算符 比较运算符 逻辑运算符


# 修改和删除
update 表 set 字段=值 where .....,
delete from 表 where ....,

二、查询语句进阶

2.1 模糊查询

like 在where后面 进行模糊查询

like 使用% 表示0个或者多个字符
下划线 _ 表示任意一个任意字符

select * from class_1 where name like '陈%';
select * from class_1 where name like '___';

2.2 as用法

在SQL语句中使用as 用于给字段或者表重命名

select name as 姓名, age as 年龄 from class_1;

select * from class_1 as c where c.age>17;

注意只是在一条SQL语句里面生效

2.3 排序

order by 用来设定你想按照那个字段 使用哪种方式排序来排序

# 语法
select 字段1.....  from 表名 where 字段
order by 字段 [asc desc]
# asc 升序
# desc 降序

# 不写where 是对所有的字段排序
select * from class_1 order by score;

# 性别为男的按照成绩排序 默认是升序(小到大)
select * from class_1 where sex='m' order by score;

# 性别为男的按照成绩排序 降序(大到小)
select * from class_1 where sex='m' order by score desc;

复合排序 对多个字段排序

select * from class_1 order by score,age desc;

2.4 限制

limit 限制由 select 语句返回的数据数量或者 update delete语句的操作数据

select * from class_1 limit 1;  # 只显示一条数据

# 修改第一个性别为男的分数
update class_1 set score=99 where sex='m' limit 1;

# 看分数最高的男性
select name, score from class_1 where sex='m'
order by score desc  # 按照成绩排序 降序
limit 1
;

2.5 子查询

一个select语句里面包含了另外一个select语句 子查询

select name from (select * from class_1 where sex='m')
 as s where s.score>70;


# 找到比张三年级还要大的人
select * from class_1 where age >
(select age from class_1 where name='张三');

注意:

子查询作为一个值使用的时候 返回的需要是一个明确的值

三、聚合操作

聚合:在数据查找的基础上对数据进一步整理筛选

3.1 聚合函数

avg(字段名) 这个字段的平均值

select avg(score) from class_1;

max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录求和
count(字段名)	统计这个字段的个数

注意 count是不会统计 空值的 count(*) 表一共有多少记录

3.2 聚合分组

根据不同的条件对数据分组

group by

select sex, max(score) from class_1
group by sex;

# 1. 拿到男生最高分 女生最高分
select * from class_1 where score in
(select max(score) from class_1 where sex in ('m', 'w') group by sex) and sex!='o';

select sex, group_concat(name) from class_1 group by sex;

3.3 聚合筛选

having 对分组后的结果做进一步的筛选

select sex, avg(score) from class_1
group by sex
having avg(score) > 40
;

3.4 去重语句

select distinct sex from class_1;

3.5 聚合运算

查询表记录的时候做数学运算

# 查询的时候 成绩翻倍
select name, score*2 from class_1;

select distinct 字段1.... from 表名
    where
    group by
    having
    order by
    limit

1. 找到表 from
2. 拿到 where指定的条件
3. 分组 group by
4. 过滤 having
5. 执行 select
6. 去重 distinct
7. 排序 order by
8. 显示条数

create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum("魏","蜀","吴"),  # 国家
attack smallint,  # 攻击力
defense tinyint  # 防御力
);

insert into sanguo
values 
(1, '曹操', '男', '魏', 256, 63),
(2, '张辽', '男', '魏', 328, 69),
(3, '甄姬', '女', '魏', 168, 34),
(4, '夏侯渊', '男', '魏', 366, 83),
(5, '刘备', '男', '蜀', 220, 59),
(6, '诸葛亮', '男', '蜀', 170, 54),
(7, '赵云', '男', '蜀', 377, 66),
(8, '张飞', '男', '蜀', 370, 80),
(9, '孙尚香', '女', '蜀', 249, 62),
(10, '大乔', '女', '吴', 190, 44),
(11, '小乔', '女', '吴', 188, 39),
(12, '周瑜', '男', '吴', 303, 60),
(13, '吕蒙', '男', '吴', 330, 71);


-- 1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo where country='蜀'
order by attack desc
;
-- 2. 将赵云攻击力设置为360，防御力设置为70
update sanguo set attack=360,defense=70 where name='赵云';


-- 3. 吴国英雄攻击力超过300的改为300，最多改2个
-- 4. 查找攻击力超过200的魏国英雄名字和攻击力，并且
-- 显示为姓名  攻击力
-- 5. 所有英雄攻击力按照降序排序，如果攻击力相同则按照
-- 防御降序排序
-- 6.查找所有名字为3个字的英雄
-- 7. 查找比魏国最高攻击力的人攻击力还要高的蜀国英雄
-- 8. 找到魏国防御力排名前2的英雄
-- 9. 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量
-- 10. 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力