数据库
库 database
表 table
每个库中可以包含多张表， 必须在库中建表
每个表中，一行数据，称为 记录
每一列数据，称为字段， 每个字段都有自己的类型
数据库本质是一种文件， 有一种专门的工具软件帮助管理数据库，
这种软件被称为RDBMS(关系型数据库管理系统)
关系型数据库： MYSQL、 Oracle 、MS SQL server、sqlite3
-
MySQL(免费)、Oracle(商业版) 属于同一个公司(Oracle)
-
MS SQL server 针对Windows平台，属于微软
-
Sqlite3 用在 嵌入式设备中， 如 手机、平板等
关系型数据库所用的语言都是 SQL语言
非关系型数据库
MongoDB
Redis：内存型数据库，一般用来做缓存
库操作
-
创建数据库
create database school charset=utf8; # 创建数据库school， 指定编码方式为utf8， 防止中文乱码 -
查看数据库
show databases; -
删除数据库
drop database 库名; -
入库
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;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来查的时候必须使用物理外键
连接查询
两表关联
商品表与分类表
分类表
| 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;
思考：
-
哪个分类没有商品？
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语句
子查询分类:
标量子查询
行子查询
列子查询
-
查询 大于平均价格的商品
select * from goods where price > ( select avg(price) from goods );
-
查询手机分类的商品信息
-
查询 分类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="手机");
-
查询电脑分类的平均价格
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
索引 越多， 会加快 查询 速度， 但是降低 修改(删除、更新、插入) 速度， 因为 索引本质是一种特殊的文件， 改变数据时，还需要改变索引文件
经常查询的字段，适合建立索引
经常改变的字段，不适合建立索引
索引并不是越多越好
事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
-
用户 A - 5000
-
用户 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
每个库中可以包含多张表， 必须在库中建表
每个表中，一行数据，称为 记录
每一列数据，称为字段， 每个字段都有自己的类型
数据库本质是一种文件， 有一种专门的工具软件帮助管理数据库，
这种软件被称为RDBMS(关系型数据库管理系统)
关系型数据库： MYSQL、 Oracle 、MS SQL server、sqlite3
-
MySQL(免费)、Oracle(商业版) 属于同一个公司(Oracle)
-
MS SQL server 针对Windows平台，属于微软
-
Sqlite3 用在 嵌入式设备中， 如 手机、平板等
关系型数据库所用的语言都是 SQL语言
非关系型数据库
MongoDB
Redis：内存型数据库，一般用来做缓存
库操作
-
创建数据库
create database school charset=utf8; # 创建数据库school， 指定编码方式为utf8， 防止中文乱码 -
查看数据库
show databases; -
删除数据库
drop database 库名; -
入库
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;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来查的时候必须使用物理外键
连接查询
两表关联
商品表与分类表
分类表
| 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;
思考：
-
哪个分类没有商品？
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语句
子查询分类:
标量子查询
行子查询
列子查询
-
查询 大于平均价格的商品
select * from goods where price > ( select avg(price) from goods );
-
查询手机分类的商品信息
-
查询 分类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="手机");
-
查询电脑分类的平均价格
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
索引 越多， 会加快 查询 速度， 但是降低 修改(删除、更新、插入) 速度， 因为 索引本质是一种特殊的文件， 改变数据时，还需要改变索引文件
经常查询的字段，适合建立索引
经常改变的字段，不适合建立索引
索引并不是越多越好
事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
-
用户 A - 5000
-
用户 B + 5000
-
开启事务
begin; -- 类似于技能开启，一旦发生错误，可以撤回
start transaction;
-
存点
savepoint 存点; -- 前n步操作都对，可以保存一个点，类似于游戏的存档
-
回滚
rollback; -- 发生错误之后，可以使用这条命令回到初始状态
?
rollback to 存点; --发送错误，回到存点
-
提交事务
commit; -- 所有操作全部做完，确认无误， 提交事务，使修改永久失效， 事务提交之后，自动关闭
事务四大特性：
-
原子性
-
一致性
-
隔离性
-
持久性
Mysql 数据库支持多种引擎：1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV
InnoDB：MySQL数据库的默认引擎， 支持事务