数据库的基本使用

一、常用数据类型和约束

1. 数据类型

整数：int，有符号范围（-2147483648 ~ 2147483647），无符号（unsigned）范围（0 ~ 4294967295）
小数：decimal，decimal(5,2)表示5位数，小数占2位，整数占3位
字符串：varchar，范围（0~65533），varchar(3)表示最多存3个字符，一个中文或字母都占一个字符
日期时间：datetime，如 ‘2022-11-24 16:56’

2. 约束

1. 主键（Primay Key）： 唯一性，非空性，能唯一标识表中的每一条记录
2. 唯一（unique）：唯一性，可以空，不可以重复
3. 非空（not null）：不可以为空
4. 默认约束（default）：不填写此值时使用默认值
5. 外键约束（foreign key）：一个表中的一个字段引用另一个表的主键

二、数据库的操作

开始使用mysql：mysql -u 用户名 -p

1. 创建

语法格式：

creat database 数据库名 charset=[字符编码] collate=[校验规则];

mysql> create database test_database charset=utf8 collate=utf8_general_ci;
查看创建结果: show create database 数据库名;

2. 使用

语法格式：

打开数据库
use 数据库名;
查看当前使用的数据库
mysql> select database();

3. 修改

语法格式：

alter database [数据库名] default character set 字符集名 default collate 校对规则名

创建testpython数据库，并修改该数据库的字符集和校验规则
mysql> create database testpython charset=gb2312;
mysql> alter database testpython default character set utf8mb4 default collate utf8mb4_general_ci;

4. 删除

语法格式：

mysql> drop database 数据库名;

5. 备份

# mysqldump -uroot -p 数据库名 > 备份名.sql;

6. 恢复

需要先创建一个新的数据库，再执行恢复操作
# mysql -uroot -p 新数据库名 < tt.sql

三、数据表的操作

1. 创建表

语法格式：

create table 表名(
	字段名 类型 约束,
	字段名 类型 约束
	...	
)
# 查看创建的表
show create table 表名;

create table students(
	id int unsigned not NULL primary key auto_increment,
	name varchar(20),
	age int unsigned,
	height decimal(5,2)
);

2. 查看表结构

语法格式：

desc students;

3. 删除表

drop table 表名;
drop table if exists 表名; 如果表存在删除表

四、数据的操作-增删改查

1. 添加数据

1.1 添加一行数据

格式一：所有字段设置值，值的顺序与表中字段的顺序一致
备注：主键自动增长，插入时需要占位，通常使用0 \ default \ null 来占位

insert into 表名 values(…)

insert into students values(0,'张飞',18,188.8);

格式二：部分字段设置值，值的顺序与给出的字段顺序对应

insert into 表名(字段1, …) values(值1, …)

insert into students(name) values('孙悟空');

1.2 添加多行数据

格式一：

insert into 表名 values(…),(…)…

insert into students values(default,'猪八戒',17,180),(default,'沙和尚',17,186.87);
格式二：

insert into 表名(字段1,…) values(值1,…),(值2,…)…

insert into students(name,age) values('乔端阳',18),('李芳芳', 17);

2. 修改字段值

语法格式：

update 表名 set 字段1=值1, 字段2=值2,… where 条件

update students set name='狄仁杰', age=20 where id=2;

3. 删除表记录

注：一旦删除无法恢复
语法格式：

delete from 表名 where 条件;

delete from students where name='沙和尚';
一般在表中设置字段如 isdelete 默认值为0，1表示删除，0表示未删除，查询数据时只查询isdelete为0的数据。
update students set isdelete=1 where id=3;

4. 查询数据

4.1 查询所有数据

select * from 表名;

select * from goods; 查询goods表中的所有数据

4.2 查询部分字段

select 字段1,字段2,… from 表名;

select tarde_name, price from goods; 只查询tarde_name、price字段

4.3 字段起别名

select 字段1 as别名1, 字段2 as 别名2… select from 表名;

select tarde_name as 商品名, price as 价格 from goods;

4.4 查询结果去重

select distinct(字段名) from 表名;
select distinct 字段名 from 表名;

select distinct(platform) from goods; 查询platform并去重

4.5 复杂查询

条件查询：

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

4.5.1 比较运算符

等于：=
大于：>
大于等于：>=
小于：<
小于等于：<=
不等于：!=或<>

select * from goods where price <= 50; 筛选价格小于等于50的数据

4.5.2 逻辑运算符

and
or
not

select * from goods where price <= 50 and platform = '拼多多'; 查询价格小于等于50并且平台为拼多多的数据

4.5.3 模糊查询

like 表示模糊查询
% 表示任意多个任意字符
_ 表示一个任意字符

select * from students where name like '乔%'; 查询姓“乔”的同学数据
select * from students where name like '张_'; 查询姓“张”且名字只有一个字的同学
select * from students where name like '%乔'; 查询名字以“乔”结尾的同学数据
select * from students where name like '%乔%'; 查询姓名中包含“乔”的同学数据

4.5.4 范围查询

in 表示在一个连续的范围内，格式 in(…,…)

select * from students where age in(15,17,20); 查询年龄为15、17、20的学生

betwween…and… 表示在一个连续的范围内

select * from students where age between 17 and 20; 查询年龄在17-20岁之间的学生，包含17和20

4.5.5 判断空

判断为空 is NULL 或 is null
判断不为空 is not null

select * from students where height is null;

4.6 排序

语法格式：

select * from 表名 order by 列1 asc|desc,列2 asc|desc,…;

默认按照列值从小到大排序
asc 从小到大，即升序
desc 从大到小，即降序

select * from goods order by price desc; 价格按照从大到小排序
select * from goods order by price desc, count desc; 价格按照从大到小排序，价格一样 count按从大到小排序

4.7 聚合函数

对一组数据进行计算，返回三个结果的实现过程。
常用聚合函数：

count() 查询总记录数
max() 查询最大值
min() 查询最小值
sum() 求和
avg() 求平均值

select count(*) from goods; 查询总记录数
select max(price) from goods;查询最高价格
select min(price) from goods; 查询最低价格
select sum(count) from students; 查询数量总和
select avg(age) from students; 查询年龄平均值
select count(*), max(price), min(price), avg(price) from goods;

4.8 分组

按照字段分组，字段相同的数据会被放到一个组中
分组的目的是对每一组数据进行统计（使用聚合函数）
语法格式：

select 字段1，… 聚合… from 表名 group by 字段1，字段2…

select sex,count(*) from students group by sex; 统计班级中各性别人数

分组后数据筛选：

select 字段1,聚合… from 表名 group by 字段1,字段2… having 字段1,字段2
having后面的条件运算符与where相同

查询班级中男生人数：
select count(*) from students where sex='男';
select count(*) from students group by sex having sex='男';

where和having：
where是对from后面指定的表进行数据筛选，属于对原始数据的筛选；
having是对group by的结果进行筛选；
having后面的条件中可以用聚合函数，where后面不可以；

select class from students group by class having avg(age) > 18; 查询平均年龄大于18的班级

4.9 分页

语法格式：

select * from 表名 limit start,count;
从start开始，获取count条数据
start索引从0开始

select * from students limit 0,3; 查询前3条数据
select * from students limit 3;

limit典型应用场景就是实现分页查询
每页显示m条数据，要求显示第n页的数据
select * from students limit (n-1)*m, m;

select * from students limit 4, 2; 每页显示2条，显示第3页的数据
select * from goods order by price desc limit 1; 查询价格最贵的商品
select * from goods where price = (select max(price) from goods);

5. 多表连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的数据有选择性的连接到选定的主表结果集上。

注：如果要保证一张数据表的数据都存在，则一定不能选择内连接，可以选择左连接或右连接。

5.1 内连接——inner join

内连接就是两张或多张表中同时符合某种条件的数据记录的组合；
通常在 from 子句中使用关键字 inner join 来连接多张表，并使用 on 子句设置连接条件；
内连接是系统默认的表连接，所以在 from 子句后可以省略 inner 关键字，只使用关键字 join ，同时有多个表时，也可以连续使用 inner join 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表；

select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段;

select * from goods inner join category on goods.typeId = category.typeId;
select * from goods as g inner join category as c on go.typeId = ca.typeId; 给表起别名，缩短表名利于编写

5.2 左连接——left join

左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行；
左连接中，左表的记录会全部表示出来，而右表只会显示符合搜索条件的记录，无符合的数据右表字段都显示null；

select 字段 from 表1 left join 表2 on 表1.字段 = 表2.字段;

select * from goods left join category on goods.typeId = category.typeId;

5.3 右连接——right join

右连接也被称为右外连接，在 from 子句中用 right join 或者 right out join 关键字来表示。
右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

select 字段 from 表1 right join 表2 on 表1.字段 = 表2.字段;

select * from goods right join category on goods.typeId = category.typeId;

5.4. 自关联

数据表只有一张，表中至少有两个字段之间有某种联系
通过给表起别名的形式，将原本只有一张的数据表变为2张，然后通过对应字段实现连接查询即可

查询表中河北省所有的市:
select * from areas as a1 left join areas as a2 on a1.aid = a2.pid where a1.aid = 130000;

查询河南省所有的市和区:
select * from areas as a1 left join areas as a2 on a1.aid = a2.pid left join areas a3 on a2.aid = a3.pid where a1.atitle = '河南省';

6. 子查询

在一个select中嵌入另外一个select语句，嵌入的select语句称之为子查询语句，外部的select语句称之为主查询语句。
查询大于平均年龄的学生：
select * from students where age > (select avg(age) from students);
查询拼多多的商品，并去分类表中查出对应分类：
select * from (select * from goods where company = '拼多多') as g1 left join category on g1.typeId = category.id;

五. 数据库设计

1. ER模型

E-R模型的基本元素是：实体、联系、属性

E表示entry，实体：描述具有相同特征事物的抽象
属性：每个实体具有的各种特征称之为属性
R表示relationship，联系：实体之间存在各种关系，关系的类型包括一对一、一对多、多对多

学生为一个实体，其具有属性：学号、姓名、年龄、班级等
学生与学生之间存在角色关系，组长和组员，他们之间有一对多的关系

1.1 一对一

实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值。

1.2 一对多

实体A对实体B为1对多：表B中创建一个字段，存储表1的主键值。

1.3 多对多

实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值。

2. 主键与外键

	主键	外键
作用	用来保证数据的完整性	用来和其他表建立联系
定义	一条记录的唯一标识，不能重复，不能为空	一个表的属性是另一个表的主键，可以重复，可以为空
个数	一个表主键只能有一个	一个表可以有多个外键

注意：如果大量增加外键设置，会严重影响除数据查询之外的其他操作（增/删/改）的操作效率，因此在实际项目中很少会被采用。

设置主键：

创建数据表时，create table中指定主键
字段名 数据类型 primary key

create table ss (
    id int unsigned primary key auto_increment,
    name varchar(10),
    age int
);

删除主键：

alter table 表名 drop primary key;

删除主键需要先取消主键的自动递增:
alter table ss change id id int;
alter table ss drop primary key;

设置外键：
在创建数据表时，create table中设置外键：

constraint 外键名 foreign key(自己的字段) references 主表(主表字段);

# 创建学生表，以班级编号关联班级表
create table students(
	name varchar(10),
	class_id int unsigned,
	constraint stu_fk foreign key(class_id) references class(id)
);

删除外键：

alter table 表名 drop foreign key 外键名称;

alter table students drop foreign key stu_fk;

3. 索引

	索引
作用	提高查询排序的速度
定义	快速查询特定值的记录
个数	一个表索引只能有一个

可以大幅度提高查询语句的执行效率，如果大量增加索引设置，会影响其他操作的操作效率，不能添加过多索引。

设置索引：

表已存在的时候创建索引
create index 索引名称 on 表名(字段名称(长度))

create index name_index on students(name(10));
删除索引：

drop index 索引名称 on 表名;

create index name_index on students;