数据库

2022-10-14 20:37:38

库 database

表 table

每个库中可以包含多张表， 必须在库中建表

每个表中，一行数据，称为 记录

每一列数据，称为字段， 每个字段都有自己的类型

 

数据库本质是一种文件， 有一种专门的工具软件帮助管理数据库，

这种软件被称为RDBMS(关系型数据库管理系统)

关系型数据库： MYSQL、 Oracle 、MS SQL server、sqlite3

  • MySQL(免费)、Oracle(商业版) 属于同一个公司(Oracle)

  • MS SQL server 针对Windows平台，属于微软

  • Sqlite3 用在 嵌入式设备中， 如 手机、平板等

关系型数据库所用的语言都是 SQL语言

非关系型数据库

MongoDB

Redis：内存型数据库，一般用来做缓存

库操作

  1. 创建数据库

    create database school charset=utf8;  # 创建数据库school， 指定编码方式为utf8， 防止中文乱码

  2. 查看数据库

    show databases;

  3. 删除数据库

    drop database 库名;  

  4. 入库

    use 库名;

表操作

建表

ID NAME AGE GENDER IS_VIP
1 小红 23 女 true
2 小绿 20 男 false
3 小黑 22 男 true 
create table users(
    # 字段名   字段类型  字段属性
    id int unsigned primary key auto_increment,
    name varchar(10) not null,
    age int unsigned,
    gender enum("男", "女") default "男",
    is_vip bool default false
    )charset=utf8;

查看当前库的所有表

show tables;

查看表结构

desc  students; # 查看表students的结构

删除表

drop table 表名;

字段类型

  • 数字

    • 整数

      • int: 整数， 0 -1 1

      • int unsigned：无符号整数， 0 1 2 3 4

      • bit： 只能存0 或1， 代表真或假

      • bool，实际是 tinyint(1), 插入 true， 实际就是1， 插入 false， 实际就是0

    • 小数

      • float： 一般的浮点数

      • decimal(n,m)： 存金钱有关的数字, 总共n位数字， m位小数, 正或负都可以

  • 字符串

    • varchar(n)： 创建大小为n的变长字符串， 如 varchar(10), 只存"hello", 剩余空间可以被别人使用

    • char(10): 创建大小为n定长字符串，如 char(10), 只存"hello", 剩余空间用空格补够10个长度

  • 枚举

    • enum(A, B):在列举的所有情况中，选择一个， 如 enum("男", "女")

  • 时间

    • date :年月日, 如 2020-8-13

    • time: 时分秒， 如 13:54:00

    • datetime: 年月日 时分秒， 如 20202-8-13 13：55：30

字段属性

  • 主键： 唯一标识一条记录，primary key

  • 自增： auto_increment， 一般都是对主键自增

  • 非空： not null

  • 默认: default

  • 唯一: : unique

  • 外键：foreign key

唯一约束 与 主键

  • 两者都不能重复

  • 主键不能为空， 唯一约束可以为空

分类表

ID CATE_NAME
1 手机
2 电脑
3 家居

商品表

ID GOODS_NAME PRICE CATE_ID
1 Apple Iphone 11 5899.00 1
2 联想 小新 Air14 4599.00 2
3 荣耀 4Tpro 1489.00 1 
-- 创建分类表， 先创建被关联的表
create table cate(
    id int unsigned primary key auto_increment,
    cate_name varchar(10) not null
);
​
-- 创建商品表， 后创建关联表
create table goods(
    id int unsigned primary key auto_increment,
    goods_name varchar(128) not null,
    price decimal(6,2),
    cate_id int unsigned,
    foreign key(cate_id) references cate(id)
);
​
-- 外键关联： 外键 需要关联 另一表的主键， 强制约束

表结构操作

  • 查看表结构

desc 表名;

  • 添加字段

-- alter table 表名 add 字段名 字段类型  字段属性;
alter table students add height float not null;

  • 删除字段

-- alter table 表名 drop 字段名;
alter table students drop age;

  • 修改字段类型、属性， 不修改字段名

-- alter table 表名 modify 字段名 新类型 新属性;
alter table students modify height decimal(7.2) not null;

  • 修改字段名、类型、属性

-- alter table 表名 change 旧字段名  新字段名 新类型 新属性;
alter table students change height money decimal(7,2) default 0;

  • 查看表的创建语句

-- show create table 表名;
show create table students\G -- \G 也可以结束SQL语句

  • 修改表名

-- alter table 表名 rename (as) 新名;  as加不加都可以
alter table students rename (as) student;

 

数据操作

  • 插入数据

insert into cate values(0, "手机");  -- 插入表中所有字段的数据，只有主键中插入0，代表自增
insert into cate values(0, "电脑");
insert into cate values(0, "家居");
insert into cate values(0, "家具"),(0, "厨具"),(0, "餐具");
-- 指定字段插入
insert into goods(goods_name, price, cate_id) values("Apple Iphone 11",5899.00,1);
insert into goods(goods_name, price, cate_id) values("联想 小新 Air14",4599.00,2),("荣耀 4Tpro", 1489.00, 1);

  • 查询数据

select * from cate; -- * 代表所有字段
select * from goods;

  • 删除数据

delete from goods where id=3;  -- 删除id为3的记录

  • 修改数据

update cate set cate_name='家具' where id=3;
​
update goods set price=price*1.2 where price<2000; -- 低于2000的商品价格上调20%
update goods set price=price*0.9 where price>5000; -- 超过5000的商品 打9折

查询

条件查询

比较运算符

select * from goods where price > 2000; -- 查询价格大于2000的商品
select * from goods where id>=3;   -- 查询id不小于3的商品信息
​
select * from goods where price < 2000; -- 查询价格小于2000的商品
select * from goods where id<=3;   -- 查询id不大于3的商品信息
​
select * from goods where id=2; -- 查询id为2的商品信息
select * from goods where id!=2; -- 查询id不为2的商品信息
select * from goods where id<>2; -- 查询id不为2的商品信息

逻辑运算符

select * from goods where price>2000  and id>=2; -- 查询价格大于2000 且 id不小于2 的商品信息
select name, price from goods where id=1 or id=3; -- 查询id为1 或 id为3 的商品名称和价格

范围查询

-- 查询id在5到50之间的商品信息
select * from goods where id>=5 and id<=50;
select * from goods where id between 5 and 50; -- 用来判断连续的范围
-- 查询id为1 或3 或 5 的商品信息
select * from goods where id=1 or id=3 or id=5;
select * from goods where id in (1,3,5);  -- 用来判断不连续的范围

NULL判断

 select * from goods where price is null; -- 查询价格为空的商品信息
 select * from goods where price is not null; -- 查询价格不为空的商品信息
 
 select * from goods where goods_name=""; -- 判断是有内容，只不过内容为空字符串

模糊查询

select * from goods where goods_name like "%电脑%"; -- 查询名称含有“电脑”的商品信息，%任意个任意字符
select * from goods where goods_name like "电脑%"；  -- 查询名称以"电脑"开头的商品信息
select * from goods where goods_name like "%电脑"；  -- 查询名称以"电脑"结尾的商品信息
​
select * from person where name like "李_"; -- 查询姓李且名为1个字的 人的信息，_ 一个任意字符

排序

select * from goods order by price; -- 默认升序排序
select * from goods order by price asc; -- asc确定升序
​
select * from goods order by price desc; -- desc 降序排序
​
 select * from goods order by price desc, id desc; -- 多个字段排序，先按照前面的字段排序；只有前面字段值相等，才会按照下一个字段排序

分页

select * from goods limit 0,10; -- 代表每页10条数据，第1页
select * from goods limit 10,10; -- 代表每页10条数据，第2页
select * from goods limit 40,10; -- 代表每页10条数据，第5页
​
select * from goods limit (n-1)*m, m; -- n代表第几页，m代表每页多少条数据

select * from goods limit 10 offset 0; -- 代表每页10条数据，第1页
select * from goods limit 10 offset 10; -- 代表每页10条数据，第2页
select * from goods limit 10 offset 20; -- 代表每页10条数据，第3页
select * from goods limit 10 offset 40; -- 代表每页10条数据，第5页

select * from goods limit m offset (n-1)*m; -- n代表第几页，m代表每页多少条数据

分组

select cate_id as "类别", count(*) as "数量", max(price) as "最高价", min(price) as "最低价", round(avg(price),2) as "平均价格" from goods group by cate_id;
-- 分组+聚合函数

  • max(): 最大值

  • min():最小值

  • count(*): 统计数量

  • sum()：求和

  • avg(): 求平均值

  • round():四舍五入的方式保留位数

select cate_id, group_concat(goods_name)  from goods group by cate_id;
-- 列举每个分类下的商品名称

  • group_concat(): 列举每组成员的信息

select publish,round(avg(price), 2) from book group by publish  having publish="作家出版社";
​
select publish,round(avg(price), 2) from book  where publish="作家出版社";

  • 注意：group by分组之后的条件判断，不能使用 where关键字，但是可以使用 having

连接查询

两表关联

商品表与分类表

分类表

ID CATE_NAME
1 手机
2 电脑
3 家居

商品表

ID GOODS_NAME PRICE CATE_ID
1 Apple Iphone 11 5899.00 1
2 联想 小新 Air14 4599.00 2
3 荣耀 4Tpro 1489.00 1

  • 内连接(inner join): 左右两表都有的数据,才可以查询到

select * from goods inner join cate   on goods.cate_id=cate.id;

  • 左连接(left join)：以左表作为主表， 右表不存在的数据，用NULL填充

select * from goods left join cate  on goods.cate_id=cate.id;

  • 右连接(right join)：以右表作为主表， 左表不存在的数据，用NULL填充

select * from goods right join cate   on goods.cate_id=cate.id;

思考：

  1. 哪个分类没有商品？

select cate.id, cate.cate_name from cate left join goods on goods.cate_id=cate.id  where goods.id is null;
-- 以分类表作为主表，如果分类表在左，用左连接
-- 判断对应的商品信息为NULL
-- 指定查询 分类的信息

学生表与班级表

-- 班级表
create table class (
    id int unsigned primary key auto_increment,
  name varchar(20) not null,
  number int unsigned default 0
);
​
--学生表
create table student(
    id int unsigned primary key auto_increment,
    name varchar(20) not null,
    age int unsigned,
    gender enum("男", "女"),
    birthday date,
    class_id int unsigned,
    foreign key(class_id) references class(id)
);

  • 查询每个学生的班级信息

select * from student inner join class on student.class_id=class.id;

  • 查询每个班级学生的平均年龄

select class.name, avg(student.age) from student inner join class on student.class_id=class.id  group by class.id;
-- 先将 班级表 与 学生表 进行连接
-- 按照班级信息  进行 分组
-- 使用聚合函数  avg() 求平均年龄

  • 查询每个班级男生的数量

-- 先连接 学生 和班级
-- 先找出 所有的男生
-- 按照 班级分组
-- 使用count(*) 统计数量
​
select class_id, count(*) from student where gender="男" group by class_id;
​
select c.name, count(*) from student as s inner join class as c on s.class_id=c.id where s.gender="男"  group by s.class_id;

  • 查询出生日期 在 1999-12-14 到 2003-5-6 之间的学生数量

select count(*) from student where birthday between "1999-12-14"  and  "2000-3-6";

  • 查询 出生在 2000 年之前的学生信息

select * from student where birthday < "2000-1-1";

自关联

表自身 与 表自身进行 关联， 使用 关联查询时，需要对表起别名 进行区分

create table  pk (
    id int unsigned primary key auto_increment,
    content varchar(300) not null,
    pid int unsigned
);

insert into pk values(0, "今天很凉快", null);
insert into pk values(0, "同上", 1);
insert into pk values(0, "废话", 2);
insert into pk values(0, "很适合睡觉", 1);


mysql> select * from pk;
+----+-----------------+------+
| id | content         | pid  |
+----+-----------------+------+
|  1 | 今天很凉快       | NULL |
|  2 | 同上            |    1 |
|  3 | 废话            |    2 |
|  4 | 很适合睡觉       |    1 |
+----+-----------------+------+
4 rows in set (0.00 sec)

mysql> select * from pk as a inner join pk as b  on a.id=b.pid;
+----+-----------------+------+----+-----------------+------+
| id | content         | pid  | id | content         | pid  |
+----+-----------------+------+----+-----------------+------+
|  1 | 今天很凉快      | NULL |  2 | 同上            |    1 |
|  2 | 同上            |    1 |  3 | 废话            |    2 |
|  1 | 今天很凉快      | NULL |  4 | 很适合睡觉      |    1 |
+----+-----------------+------+----+-----------------+------+
3 rows in set (0.00 sec)

子查询

SQL语句嵌套SQL语句

子查询分类:

  • 标量子查询

  • 行子查询

  • 列子查询

  1. 查询 大于平均价格的商品

select * from goods where price > (  select avg(price) from goods  );

  1. 查询手机分类的商品信息

    • 查询 分类id

    select id  from cate  where cate_name="手机"; --1

    • 分类id ===> 手机信息

    select * from goods where cate_id=1;
select * from goods where cate_id= (select id  from cate  where cate_name="手机");

  1. 查询电脑分类的平均价格

select avg(price) from goods where cate_id= (select id  from cate  where cate_name="电脑");

多对多

一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。 比如：一个学生可以有多个老师，一个老师可以教多个学生 解决方案：创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段（主键）

数据库

1：找出选修过何老师的课程的学生成绩

select scgrade from studentcourse INNER JOIN course on studentcourse.cno = course.cno where Cteacher ='何昊';

2：列出有不及格课程（成绩小于60）的学生的姓名

select sname from studentcourse as sc INNER JOIN student as s on s.sno=sc.sno where scgrade<60 GROUP BY sname;

3：查询SC表中对应何昊老师所授课程的女生的信息

select * from studentcourse as sc INNER JOIN course as c on sc.cno = c.cno INNER JOIN student as s on s.sno=sc.sno where Cteacher='何昊' and sex='女';

4：找出没有选修过何老师的课程的所有学生的姓名

select sname from student where sno not in(select sno from studentcourse INNER JOIN course on studentcourse.cno = course.cno where Cteacher ='何昊')；

视图

将经常使用的查询语句固化为视图， 类似于"函数封装", 简化查询过程，提高查询效率

  • 创建视图

create view  视图名称  as SQL查询语句; --视图名称 建议 以 "V_"开头
​
create  view v_avg  as  select avg(price) from goods where cate_id= (select id  from cate  where cate_name="电脑");

  • 查看视图

show tables;

  • 查询视图的结果

select * from 视图名称;
select * from v_avg;

  • 删除视图

drop view 视图名称;

索引

索引：对表中的某些字段建立索引，减少查询时间，提高查询效率，索引就类似于书的目录

  • 创建索引

create index 索引名称 on 表名(字段);
​
create index name_index on goods(good_name); -- 对商品表中的 goods_name 字段创建索引name_index

  • 查看索引

show index from 表名;
​
show index from goods; -- 查看商品表goods中的索引

  • 删除索引

drop index 索引名称  on 表名;
​
drop index name_index on goods; --删除商品表goods中的索引name_index

索引 越多， 会加快 查询 速度， 但是降低 修改(删除、更新、插入) 速度， 因为 索引本质是一种特殊的文件， 改变数据时，还需要改变索引文件

  • 经常查询的字段，适合建立索引

  • 经常改变的字段，不适合建立索引

  • 索引并不是越多越好

事务

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

  1. 用户 A - 5000

  2. 用户 B + 5000

  • 开启事务

begin; -- 类似于技能开启，一旦发生错误，可以撤回
start transaction;

  • 存点

savepoint  存点;  -- 前n步操作都对，可以保存一个点，类似于游戏的存档

  • 回滚

rollback; -- 发生错误之后，可以使用这条命令回到初始状态
​
rollback to 存点; --发送错误，回到存点

  • 提交事务

commit; -- 所有操作全部做完，确认无误， 提交事务，使修改永久失效， 事务提交之后，自动关闭

事务四大特性：

  • 原子性

  • 一致性

  • 隔离性

  • 持久性

Mysql 数据库支持多种引擎：1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV

InnoDB：MySQL数据库的默认引擎， 支持事务

 

 

 

 

上一篇：防止商品超卖的 3 个思路！


下一篇：20210726# MySQL数据库的高级使用