Mysql数据库学习笔记（持续更新）

更新地址 https://www.muyuanhuck.cn/archives/207

?数据库 入门

在学习时不必安装mysql，直接安装 phpstudy（小皮面板），可以多版本切换，不限定某一版本

https://www.xp.cn/

连接工具使用VS code 和 mysql插件

• 基本概念解析
  • 数据表（table）：存放数据的表格
  • 字段（column）：每个列，用来表示盖烈数据的含义
  • 记录（row）：每个行，表示一组完整的数据

?数据库基础操作

基础命令

• 列出已有库

  show databases;

• 创建库

  create database 库名 charset = utf8mb4;

  数据库命名不能使用纯数字，区分大小写，不要使用特殊字符和mysql关键字

• 切换库

  use 库名

• 删除库

  drop database 库名

基础数据类型

• 数字类型：
  • 整数类型：int,smallint,tinyint,mediumint,bigint
  • 浮点类型：float，double,declmal
  • 比特值类型：bit

对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数，D是小数点右侧数字的位数。比如DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。
float(5，2) float后的数字5表示数字位数，2表示精确到小数点后2位。
比特值类型指0，1值表达2种情况，如真，假。

• 字符串类型：
  • 普通字符串：char,varchar
  • 存储文本：text
  • 存储二进制数据：blob
  • 存储选项型数据： enum，set

表的基本操作

• 创建表

create table 表名(字段名 数据类型 约束,字段名 数据类型 约束, .......)

• 字段约束
  • 数字为无符号加上 unsigned
  • 字段不为空 not null
  • 字段设置默认值 default
  • comment 增加字段说明
  • auto_increment 定义列为自增的属性，一般用于主键，自加一
  • 字段设为主键 primary key 主键的值不为空且唯一
• 插入（insert）

​ insert into 表名 vlues (值1，值2...),(值1，值2....),...

​ insert into 表名 (字段1，字段2,...) value(值1,,值2...);

练习

创建一个数据库 exercise

创建一个数据表 books 类型和约束自己设计，字段 ：id 书名 作者 出版社 价格 备注

create database exercise charset = utf8mb4;
use book;
create table books(id int primary key auto_increment,bname varchar(50) not null,author varchar(50) default "佚名",press varchar(128) price float unsigned,comment text);

插入数据
insert into books (bname,author,press,price,comment)
values
("边城","沈从文","机械工业出版社",36,"小城故事多"),
("骆驼祥子","老舍","机械工业出版社",43,"你是祥子么？"),
("茶馆","老舍","中国文学出版社",55,"老北京"),
("呐喊","鲁迅","人民教育出版社",71,"最后的声音"),
("朝花夕拾","鲁迅","中国文学出版社",53,"好时光"),
("围城","钱钟书","中国文学出版社",44,"你心中的围城是什么");

插入数据
insert into books
(bname,author,press,price)
values
("林家铺子","茅盾","机械工业出版社",51),
("子夜","茅盾","人民教育出版社",47);

查询(select)

select * from 表名 [where 条件];

select 字段1,字段2,from 表名 [where 条件];

where子句的使用

• 算术运算符

  

e.g.
select * from class_1 where age % 2 = 0;

• 比较运算符

  

e.g.
select * from class where age > 8;
select * from class where age between 8 and 10;
select * from class where age in (8,9);
select * from class where sex is null;

• 逻辑运算符

  not 非 and 与 or 或

e.g.
select * from class where sex = 'm' and age > 9;

• 优先级一览表

查询练习

1. 查找30多元的图书
2. 查找人民教育出版社出版的图书
3. 查找老舍写的，中国文学出版社的图书
4. 查找备注不为空的图书
5. 查找价格超过60元的图书，只看书名和价格
6. 查找鲁迅写的或者茅盾写的图书

​

select * from books where price between 30 and 39.99;
select * from books where press = "人民教育出版社";
select * from books where author = "老舍" and press = "人民出版社"
select * from books where comment is not null
select bname,price from books where price>60
select * from books where author = "鲁迅" or "矛盾"

更新表记录 update

update 表名 set 字段1 = 值1,字段2 = 值2,... where 条件;
注意: update语句后如果不加上where条件，所有记录全部更新

e.g.
update class set age = 18,score = 91 where name = "Abby";
update class set sex ='m' where sex is null;
update class set age = age + 1;

删除表记录 delete

delete from 表名 where 条件;
注意：delete语句后如果不加where条件，所有记录全部清空

e.g.
delete from class where score = 0 and sex = 'm';

表字段的操作 alter

语法： alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 替换字段(change)
    alter table 表名 change 旧字段名 新数据类型;

e.g.
--增加字段
alter table hobby add phone char(10) after price;

--删除字段
alter table hobby drop level;

--修改字段数据类型
alter table hobby modify phone char(16);

--替换字段
alter table hobby change phone tel char(16);

时间类型数据

• 日期： date
• 日期时间： datatime,timestamp
• 时间：time
• 年份：year

• 时间格式

date : "yyyy-mm-dd"
time : "hh:mm:ss"
datetime : "yyyy-mm-dd hh:mm:ss"
timestamp : "yyyy-mm-dd hh:mm:ss"

e.g.
create table marathon (
    id int primary key auto_increment,
    athlete varchar(32),
    birthday date,
    r_time datetime comment "报名时间",
    performance time
);

insert into marathon values 
    (1,"曹操","1998-2-16","2021/5/6 10:10:27","2:38:49"),
    (2,"关羽","2000-7-19","2021/4/30 16:22:09","2:27:18"),
    (3,"孙策","1995-10-23","2021/5/2 20:1:2","2:44:00");

• 日期时间函数

  • now() 返回服务器当前日期时间，格式对应datetime类型

• 时间操作

  时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

select * from marathon where birthday >= '2000-01-01';
select * from marathon where birthday >= '2000-07-01'; and performance <= "2:30:00";

练习 使用book表

1. 将呐喊的价格修改为45元
2. 增加一个字段出版时间 类型为 date 放在价格后面
3. 修改所有老舍的作品出版时间为 2018-10-1
4. 修改所有中国文学出版社出版的但是不是老舍的作品出版时间为 2020-1-1
5. 修改所有出版时间为Null的图书 出版时间为 2019-10-1
6. 所有鲁迅的图书价格增加5元
7. 删除所有价格超过70元或者不到40元的图书

update books set price = 45 where bname = "呐喊";
alter table books add p_time data comment "出版时间" after price;
update books set p_time = '2018-10-1' where author="老舍";
update books set p_time = "2020-1-1" where press = "中国文学出版社" and auther != "老舍";
update books set p_time = "2019-10-1" where p_time = null
update books set price = price + 5 where author="鲁迅"
delete from books where price>70 or price<40;
delete from books where price not between 40 and 70;

---

?高级查询语句

模糊查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号 % 来表示 任意0个或多个字符，下划线，表示任意一个字符。

select field1,field2,....fieldN
from table_name
where field1 like condition1

e.g.
select * from class where name like "1%";
select * from class where name like "___";
select * from class where hobby like "%draw%";

as 用法

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

select name as 姓名,score as 分数 from class;
select cls.name,cls.score from class as cls where cls.score>80;

排序

order by 子句来设定你想按哪种方式来进行排序，再返回搜索结果。

使用order by 子句 将查询数据排序后再返回数据。

select field1,field2,...fieldN from table_name1 where field1
order by field1 [ASC[DESC]]

默认情况ASC表示升序，DESC表示降序。

select * from class order by score desc;
select * from class where sex='m' order by score;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

select * from class order by age,score desc;

限制

LIMIT 子句用于限制由select语句返回的数据流量 或者 update,delete语句的操作数量。带有LIMIT子句的 select 语句的基本语法如下：

select column1,column2,columnN
from table_name
where field
limit [num] [offset num]

update class set score = 83 limit 1;

--男生第一名
select * from class where sex = 'm' order by score desc limit 1;

--男生第二名
select * from class where sex = 'm' order by score desc limit 1 offfset 1;

联合查询

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则返回所有结果集， 包含重复数据。

--分数大于80分的男生和分数
select * from class where score > 80 and sex='m'
union
select * from class where score > 90 and sex='w';

--可以查询不同字段，但是字段数量必须一致
select name,age,score from class where score>80
union
select name,hobby，price from hobby;

--all 表示如果查询结果有重复不去重
--order by 只能加在最后表示对union 结果一起排序

select * from class where sex = 'm'
union all
select * from class where score > 80
order by score;

子查询

• 定义：当一个select查询语句使用（）括起来，放在其他sql语句中，作为其他sql语句的一部分时，即是一个子查询语句。

• 子查询使用位置：

  1. from之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

     select * from(select * from class where sex='m') as man 
     where score>80
     注意： 需要将子查询结果集重命名一下，方便where子句中的引用操作

  2. where 子句中，此时select查询到的内容作为外层查询的条件值

     --查询与tom同岁的学生
     select * from class
     where age=(select age from class where name='Tom')
     
     --class 表中誰报了兴趣爱好班
     select * from class
     where name in(select name from hobby);

  注意：

  1.子语句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。

  2.如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

• 查询过程

(5)SELECT DISTINCT

(1)FROM JOIN ON

(2)WHERE

(3)GROUP BY

(4)HAVING

(6)ORDER BY

(7)LIMIT

高级查询练习

在stu下创建数据报表 sanguo

字段：id name gender country attack defense

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. 查找所有蜀国人信息，按照攻击力排名
2. 吴国英雄攻击力超过300的改为300，最多改2个
3. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
4. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
5. 查找名字为3字的
6. 找到魏国防御力排名2-3名的英雄
7. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
8. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄

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

?聚合操作

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

聚合函数

方法	功能
avg 字段名	该字段的平均值
max 字段名	该字段的最大值
min 字段名	该字段的最小值
sum 字段名	该字段所有记录的和
count 字段名	统计该字段记录的个数