MySQL学习笔记之基础操作(二)

1. 表字段的操作

• 语法：alter table 表名 执行动作；
  • 添加字段 addalter table hobby add tel char(11) after name;
  • 删除字段 dropalter table hobby drop level;
  • 修改数据类型 modifyalter table hobby modify tel char(16);
  • 修改字段名changealter table hobby change tel phone char(16)

2. 时间类型数据

• 日期：DATE 1000-9999
• 日期时间：DATETIME, TIMESTAMP
• 时间：TIME
• 年份：YEAR类型大小（字节）范围DATE31000-01-01/9999-12-31TIME3'-838:59:59/838:59:59'YEAR11901/1/2155DATETIME81000-01-01 00:00:00/9999
  • 时间格式date："YYYY-MM-DD"
    time: "HH:MM:SS"
    datatime: "YYYY-MM-DD HH:MM:SS"
    timestamp: "YYYY-MM-DD HH:MM:SS"
    示例：
    create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date ,registration_time datetime,performance time);
    select * from marathon where birthday < now();
    select * from marathon where performance > "3:0:0";

3. 高级查询语句

• 模糊查询LIKE用于在where 子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。SELECT field1...
  FROM table_name
  WHERE filed1 LIKE condition1
  ​
  示例：
  select * from class_1 where name like "刘%";
• as 用法在sql语句中as用于给字段或者表重命名select name as 姓名，age as 年龄 from class;
  select * from class as cls where cls.age > 17；
• 排序ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
• 使用 ORDER BY 子句将查询数据排序后再返回数据：select field1,field2... from table_name1 where field1 order by field1 [asc [desc]]默认情况ASC表示升序，DESC表示降序复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序select * from class where sex='m' order by age desc;
  select * from class order by score desc,age;
  • 限制
  LIMIT 子句用于限制由select语句返回的数据数量 或者 UPDATE ,DELETE 语句的操作数量也可以用于update、delete语句中带有limit 子句的select语句的基本语法：select column1,column2,columnN
  From table_name
  where field
  LIMIT [num] [offset num]
  # 示例
  select * from cls where sex="m" order by score desc limit 1 offset 2; # 跳过前2项取一项
• 联合查询 UNION操作符用于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的数据。UNION 操作符语法格式：SELECT expression1,expression2, ... expression_n
  FROM tables
  [where conditions]
  union [all|distinct]
  select expression1,expression2,...expression_n
  from tables
  [where conditions];默认union后卫distinct表示删除结果集中重复的数据。如果使用ALL则返回所有结果集，包含重复数据。select * from class where sex='m' union all select * from class where age>9;
• 子查询
  • 定义：当一个select语句中包含另一个select查询语句，则称之为有子查询的语句
  • 子查询使用位置：
    1. from 之后，此时子查询的内容作为一个新表内容，再进行外层select查询select name form (select * from class where sex='m') as man where man.score >90;注意：需要将子查询结果集重命名一下，方便where 子句中的引用操作
    2. where 子句中，此时select查询到的内容作为外层查询的条件值select * from class where age=(select age from class where name='Tom');
       select * from class where name in (select name from hobby);注意：
       1. 子句结果作为一个值使用时，返回的结果需要一个明确的值，不能是多行或者多列。
       2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。
• 查询过程
  • select 的执行过程(5) select distinct <select_list>
    (1) from <left_table> <join_type> JOIN <right_table> ON <on_predicate>
    (2)WHERE <where_predicate>
    (3)GROUP BY <group_by_specification>
    (4)HAVING <having_predicate>
    (6)ORDER BY <order_by_list>
    (7)LIMIT <limit_number>

4. 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据筛选范围。

• 聚合函数方法功能avg(字段名)该字段平均值max(字段名)该字段的最大值min(字段名)该字段的最小值sum(字段名)该字段所有记录的和count(字段名)统计该字段记录的个数 （主键或*,null统计不到）注意：此时select 后只能写聚合函数，无法查找其他字段，除非该字段值全部一样。
• 聚合分组
  • group by给查询的结果进行分组select age,sex,count(*) from class group by age,sex;
    select country,count(id) as number from sanguo
    where gender='M' group by country
    order by number DESC
    limit 2;注意：使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by a，b此时必须A,B两个字段值均相同才算一组。
• 聚合筛选
  • having语句对分组聚合后的结果进行进一步筛选#示例 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
    select country,avg(attack) from sanguo
    group by country
    having avg(attack)>105
    order by avg(attack) DESC
    limit 2;
    1. having语句必须与group by 联合使用；
    2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足，where 只能操作表中实际存在的字段。
• 去重语句
  • distinct语句
    • 不显示字段重复值
    # 示例
    select distinct name,country from sanguo;
  注意：distinct 和from之间所有字段都相同才会去重

5. 索引操作

概念

• 定义索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库中的特定信息。
• 优缺点
  • 优点：加快数据检索速度，提高查找效率
  • 缺点：占用数据库物理存储空间，当对表中数据更新时，索引需要动态维护，降低数据写入效率
  注意：
  1. 通常只在经常进行查询操作的字段上创建索引
  2. 对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引
• 索引分类
  • 普通（MUL）普通索引：字段值无约束，KEY标志为MUL
  • 唯一索引（UNI）唯一索引（unique）:字段值不允许重复，但可为NULL,KEY标志为UNI
  • 主键索引(PRI)一个表中只能有一个主键字段，主键字段不允许重复，且不能为NULL,KEY标志为PRI。通常设置记录编号字段id，能唯一锁定一条记录。
• 索引创建
  • 创建表时直接创建索引create table 表名（
    字段名 数据类型,
    字段名 数据类型,
    index 索引名（字段名）,
    index 索引名（字段名）,
    unique 索引名（字段名）
    ）;
  • 在已有表中创建索引：create [unique] index 索引名 on 表名(字段名)；
    # 示例：
    create unique index name_index on cls(name);
  • 主键索引添加alter table 表名 add primary key(id);
  • 查看索引1. desc 表名； --> KEY 标志为：MUL、UNI。
    2. show index from 表名；
  • 删除索引drop index 索引名 on 表名；
    alter table 表名 drop primary key; #删除主键
  • 扩展：借助性能查看选项查看索引性能set profiling = 1; 打开功能 （生产环境中一般不打开）
    show profiles 查看语句执行信息

• 语法：alter table 表名 执行动作；
  • 添加字段 addalter table hobby add tel char(11) after name;
  • 删除字段 dropalter table hobby drop level;
  • 修改数据类型 modifyalter table hobby modify tel char(16);
  • 修改字段名changealter table hobby change tel phone char(16)

2. 时间类型数据

• 日期：DATE 1000-9999
• 日期时间：DATETIME, TIMESTAMP
• 时间：TIME
• 年份：YEAR类型大小（字节）范围DATE31000-01-01/9999-12-31TIME3'-838:59:59/838:59:59'YEAR11901/1/2155DATETIME81000-01-01 00:00:00/9999
  • 时间格式date："YYYY-MM-DD"
    time: "HH:MM:SS"
    datatime: "YYYY-MM-DD HH:MM:SS"
    timestamp: "YYYY-MM-DD HH:MM:SS"
    示例：
    create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date ,registration_time datetime,performance time);
    select * from marathon where birthday < now();
    select * from marathon where performance > "3:0:0";

3. 高级查询语句

• 模糊查询LIKE用于在where 子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。SELECT field1...
  FROM table_name
  WHERE filed1 LIKE condition1
  ​
  示例：
  select * from class_1 where name like "刘%";
• as 用法在sql语句中as用于给字段或者表重命名select name as 姓名，age as 年龄 from class;
  select * from class as cls where cls.age > 17；
• 排序ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
• 使用 ORDER BY 子句将查询数据排序后再返回数据：select field1,field2... from table_name1 where field1 order by field1 [asc [desc]]默认情况ASC表示升序，DESC表示降序复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序select * from class where sex='m' order by age desc;
  select * from class order by score desc,age;
  • 限制
  LIMIT 子句用于限制由select语句返回的数据数量 或者 UPDATE ,DELETE 语句的操作数量也可以用于update、delete语句中带有limit 子句的select语句的基本语法：select column1,column2,columnN
  From table_name
  where field
  LIMIT [num] [offset num]
  # 示例
  select * from cls where sex="m" order by score desc limit 1 offset 2; # 跳过前2项取一项
• 联合查询 UNION操作符用于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的数据。UNION 操作符语法格式：SELECT expression1,expression2, ... expression_n
  FROM tables
  [where conditions]
  union [all|distinct]
  select expression1,expression2,...expression_n
  from tables
  [where conditions];默认union后卫distinct表示删除结果集中重复的数据。如果使用ALL则返回所有结果集，包含重复数据。select * from class where sex='m' union all select * from class where age>9;
• 子查询
  • 定义：当一个select语句中包含另一个select查询语句，则称之为有子查询的语句
  • 子查询使用位置：
    1. from 之后，此时子查询的内容作为一个新表内容，再进行外层select查询select name form (select * from class where sex='m') as man where man.score >90;注意：需要将子查询结果集重命名一下，方便where 子句中的引用操作
    2. where 子句中，此时select查询到的内容作为外层查询的条件值select * from class where age=(select age from class where name='Tom');
       select * from class where name in (select name from hobby);注意：
       1. 子句结果作为一个值使用时，返回的结果需要一个明确的值，不能是多行或者多列。
       2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。
• 查询过程
  • select 的执行过程(5) select distinct <select_list>
    (1) from <left_table> <join_type> JOIN <right_table> ON <on_predicate>
    (2)WHERE <where_predicate>
    (3)GROUP BY <group_by_specification>
    (4)HAVING <having_predicate>
    (6)ORDER BY <order_by_list>
    (7)LIMIT <limit_number>

4. 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据筛选范围。

• 聚合函数方法功能avg(字段名)该字段平均值max(字段名)该字段的最大值min(字段名)该字段的最小值sum(字段名)该字段所有记录的和count(字段名)统计该字段记录的个数 （主键或*,null统计不到）注意：此时select 后只能写聚合函数，无法查找其他字段，除非该字段值全部一样。
• 聚合分组
  • group by给查询的结果进行分组select age,sex,count(*) from class group by age,sex;
    select country,count(id) as number from sanguo
    where gender='M' group by country
    order by number DESC
    limit 2;注意：使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by a，b此时必须A,B两个字段值均相同才算一组。
• 聚合筛选
  • having语句对分组聚合后的结果进行进一步筛选#示例 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
    select country,avg(attack) from sanguo
    group by country
    having avg(attack)>105
    order by avg(attack) DESC
    limit 2;
    1. having语句必须与group by 联合使用；
    2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足，where 只能操作表中实际存在的字段。
• 去重语句
  • distinct语句
    • 不显示字段重复值
    # 示例
    select distinct name,country from sanguo;
  注意：distinct 和from之间所有字段都相同才会去重

5. 索引操作

概念

• 定义索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库中的特定信息。
• 优缺点
  • 优点：加快数据检索速度，提高查找效率
  • 缺点：占用数据库物理存储空间，当对表中数据更新时，索引需要动态维护，降低数据写入效率
  注意：
  1. 通常只在经常进行查询操作的字段上创建索引
  2. 对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引
• 索引分类
  • 普通（MUL）普通索引：字段值无约束，KEY标志为MUL
  • 唯一索引（UNI）唯一索引（unique）:字段值不允许重复，但可为NULL,KEY标志为UNI
  • 主键索引(PRI)一个表中只能有一个主键字段，主键字段不允许重复，且不能为NULL,KEY标志为PRI。通常设置记录编号字段id，能唯一锁定一条记录。
• 索引创建
  • 创建表时直接创建索引create table 表名（
    字段名 数据类型,
    字段名 数据类型,
    index 索引名（字段名）,
    index 索引名（字段名）,
    unique 索引名（字段名）
    ）;
  • 在已有表中创建索引：create [unique] index 索引名 on 表名(字段名)；
    # 示例：
    create unique index name_index on cls(name);
  • 主键索引添加alter table 表名 add primary key(id);
  • 查看索引1. desc 表名； --> KEY 标志为：MUL、UNI。
    2. show index from 表名；
  • 删除索引drop index 索引名 on 表名；
    alter table 表名 drop primary key; #删除主键
  • 扩展：借助性能查看选项查看索引性能set profiling = 1; 打开功能 （生产环境中一般不打开）
    show profiles 查看语句执行信息

​查找练习
1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo where country="蜀" order by attack desc;
2. 将赵云攻击力设置为360，防御设置为70
update sanguo set defense=70 where name="赵云";
3. 吴国英雄攻击力超过300的改为300，最多改2个
update sanguo set attack=300 where country="吴" and attack>300 limit 2;
4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
select name as 姓名,attack as 攻击力 from sanguo where attack >200 and country="魏";
5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
select * from sanguo order by attack desc ,defense;
6. 查找名字为3字的
select * from sanguo where name like "___";
7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo where country="蜀" and attack >(select attack from sanguo where country="魏" order by attack desc limit 1) ;
8. 找到魏国防御力排名2-3名的英雄
select * from sanguo where country="魏" order by defense desc limit 2 offset 1;
9. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
select * from sanguo where gender="女" and attack >180 union select * from sanguo where gender="男" and attack <250;

select country as 国家,avg(attack) as 平均攻击力 from sanguo where gender="男" group by country order by avg(attack) desc;
select country,gender,count(*),sum(attack) from sanguo group by country,gender;
having对聚合函数结果进行筛选：
select country,gender,count(*),avg(attack) from sanguo group by country,gender having avg(attack)>=300;

聚合练习
​use books;
1. 统计每位作家出版图书的平均价格
select author,avg(price) from book group by author;
2. 统计每个出版社出版图书数量
select press,count(*) from book group by press;
3. 查看总共有多少个出版社
select count(distinct press) from book;
4. 筛选出那些出版过超过50元图书的出版社，并按照其出版图书的平均价格降序排序
select press as 出版社,count(*) as 数量,avg(price) as 均价 from book
where press in (select press from book where price >50)
group by press order by avg(price) desc;

select press as 出版社,count(*),avg(price) as 均价 from book
group by press
having max(price) >50
order by avg(price) desc;
5. 统计同一时间出版图书的最高价格和最低价格
select press_date,max(price),min(price) from book group by press_date;