当前位置:Gxlcms > 数据库问题 > mysql

mysql

时间:2021-07-01 10:21:17 帮助过:19人阅读

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 库名;

表操作

建表

IDnameagegenderis_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: 只能存01, 代表真或假

      • 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

唯一约束 与 主键

  • 两者都不能重复

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

分类表

IDcate_name
1 手机
2 电脑
3 家居

商品表

idgoods_namePriceCate_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;8
?
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

物理外键与逻辑外键

外键的作用:约束多表中的数据必须是在主表存在

公司里一般不用外键,公司里常用的是逻辑外键。

所谓的逻辑外键就是一个普通的字段(类型为int)

物理外键:就是使用Forimary key来约束过的字段

物理外键和逻辑外键不同:orm来查的时候必须使用物理外键

连接查询

两表关联

商品表与分类表

分类表

IDcate_name
1 手机
2 电脑
3 家居

商品表

idgoods_namePriceCate_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 # 物理外键
);
?
create table areas(
  aid int primary key,
  atitle varchar(20),
  pid int # 逻辑外键
 
);
?
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="电脑");

视图

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

  • 创建视图

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数据库的默认引擎, 支持事务

多对多

数据库

database

table

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

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

每一列数据,称为字段, 每个字段都有自

人气教程排行