一.MySql 数据库基础
(一)数据库的概念和作用
数据库是存储和管理数据的仓库(数据库本质就是一个文件系统)
1.数据库的分类
1.关系型数据库(SQL):关系模型(二维表格)的数据库,强调的是表格形式存储数据
2.非关系型数据库(NoSql): 不仅仅是SQL的数据库,强调key-value键值对形式存储数据
(二)数据库介绍
1.常见数据库
1.关系型数据库: MySql, Oracle, DB2, SQLServer , SQLite
2.非关系型数据库: Redis, MongoDB, HBase
2.数据库作用
数据库的作用就是存储和管理数据
按照特定的格式将数据存储起来,为了方便用户对数据增删改查
3.数据库的特点
1. 持久化存储
2. 读写速度极高
3. 保证数据的完整性
4. 对系统的支持性好
(三)SQl
1.语言简介
SQL(结构化查询语言)是关系型数据库都要遵循的规范
SQL:类似普通话
MySql和Oracle等: 类似方言
2.SQL分类
1.DDL: 数据定义语言(用来定义数据库,表和字段) 常用关键字:create drop alter
2.DML: 数据操作语言(用来操作数据记录) 常用关键字: insert update delete
3.DQL: 数据查询语言(用来查询数据记录) 常用关键字: select where from
4.DCL: 数据控制语言(用于创建用户和分配权限的) (了解)
(四)MySql数据库
1.通过命令窗口数据库连接和退出
按住win+R 输入cmd, 打开黑色命令窗口,进行输入
1.登录:
方式1: mysql -u用户名 -p密码
方式2: mysql -u用户名 -p 回车后输入密码
方式3: mysql --host=ip地址 --user=用户名 --password=密码
2.登出
方式1:exit
方式2:quit
2.通过pycharm 连接
3.数据类型
1.整数: int:整数 bit:二进制位
2.字符串: varchar(长度)
3.小数: decimal: 定位浮点数 float:单精度浮点数 double: 双精度排序
4.日期时间:datatime data:日期 time:时间
5.枚举: enum
(五)数据库的增删改查
1.数据库的增删改查
1.增: 创建数据库: create database 数据库名;
2.删: 删除数据库: drop database 数据库名;
3.改: 切换数据库: use 数据库名;
4.查: 查询所有数据库: show databases; 查看当前数据库: select database();
注意1: 创建数据库的时候如果已经存在二次创建会报错如何解决? 使用if not exists判断
注意2: 数据库如何避免中文乱码? 提前设置好编码,
方式1:charset=编码 方式2:character set 编码
注意3: 测试完后的数据库可以删除,但是开发中千万不要随便去删除,容易...
# 测试是否连接成功:查看当前系统时间
select now();
# 注意: 每一条sql语句后加分号;
# 1.对数据库增删改查,注意:默认提供好的数据库不要去修改
# 增: 创建数据库: create database 数据库名;
create database cs;
# 删: 删除数据库: drop database 数据库名;
drop database cs;
# 改: 切换数据库: use 数据库名;
use cs;
# 查: 查询所有数据库:show databases; 查看当前数据库: select database();
show databases;
select database();
# 注意1: 创建数据库的时候如果已经存在二次创建会报错如何解决? 使用if not exists判断
create database if not exists cs2;
create database if not exists cs3;
# 注意2: 数据库容易出现乱码怎么? 在创建数据库的时候提前设置好编码,方式1:charset=编码 方式2:character set 编码
create database if not exists cs4 charset = utf8;
create database if not exists cs5 character set utf8;
# 注意3: 测试完后的数据库可以删除,但是开发中千万不要随便去删除,容易...
drop database cs2;
drop database cs3;
drop database cs4;
drop database cs5;
2.库中表的增删改查
1.增: 创建表 格式: create table 表名(字段名1 字段1类型 字段1约束,字段名2 字段2类型 字段2约束);
2.删: 删除表 格式: drop table 表名;
3.改: 修改表名 格式: rename table 旧表名 to 新表名
4.查: 查询所有表 格式: show tables; 查看表信息: 格式: desc 表名;
注意: 如果想判断表是否存在可以使用if not exists判读
# 2.对数据表的增删改查
# 注意: 要想操作表,需要先有数据库
# 定义数据库
create database if not exists dajin character set utf8;
# 切换(使用)数据库
use dajin;
# 增:创建表: create table 表名(字段名1 字段1类型 字段1约束,字段名2 字段2类型 字段2约束)
create table if not exists users(
uid int primary key auto_increment,
uname varchar(10)
);
# 删: 删除表(以后也不要随意删除) : drop table 表名;
drop table users;
# 改: 修改表名: rename table 旧表名 to 新表名;
rename table users to user;
# 查: 查询当前所有表: show tables; 查看表信息: desc 表名;
show tables;
desc user;
3.表中字段的增删改查
1.增: 创建表 格式: create table 表名(字段名1 字段1类型 字段1约束,字段名2 字段2类型 字段2约束);
2.删: 删除表 格式: drop table 表名;
3.改: 修改表名 格式: rename table 旧表名 to 新表名
4.查: 查询所有表 格式: show tables;
5.查看表信息: 格式: desc 表名;
注意: 如果想判断表是否存在可以使用if not exists判读
# 2.对数据表的增删改查
# 注意: 要想操作表,需要先有数据库
# 定义数据库
create database if not exists dajin character set utf8;
# 切换(使用)数据库
use dajin;
# 增:创建表: create table 表名(字段名1 字段1类型 字段1约束,字段名2 字段2类型 字段2约束)
create table if not exists users(
uid int primary key auto_increment,
uname varchar(10)
);
# 删: 删除表(以后也不要随意删除) : drop table 表名;
drop table users;
# 改: 修改表名: rename table 旧表名 to 新表名;
rename table users to user;
# 查: 查询当前所有表: show tables; 查看表信息: desc 表名;
show tables;
desc user;
4.表中记录增删改查
注意: 要想操作表中数据,必须先有表(表中存储多条记录)
1.增:
插入数据方式1:根据字段插入1条数据
格式: insert into 表名(字段1,字段2...) value (值1,值2...);
格式: insert into 表名(字段1,字段2...) values (值1,值2...);
插入数据方式2:不指定字段插入1条数据
格式: insert into 表名 value (值1,值2...);
格式: insert into 表名 values (值1,值2...);
插入数据方式3:不指定字段插入多条数据,多条数据之间用逗号,隔开
格式: insert into 表名 value (值1,值2...) , (值11,值22...) , (值111,值222...);
格式: insert into 表名 values (值1,值2...) , (值11,值22...) , (值111,值222...);
2.删:
删除数据方式1: 根据条件删除指定数据
格式: delete from 表名 where 条件;
删除数据方式2: 使用delete关键字,可以删除所有数据,但是自增顺序不会被删除,重新添加数据,按照删除前的自增追加
格式: delete from 表名;
删除数据方式3: 使用truncate关键字,可以删除所有数据,也可以删除自增顺序,重新添加数据,重新从1开始自增
格式: truncate 表名;
3.改:
修改数据方式1: 根据条件修改指定数据
格式: update 表名 set 字段名 = 值 where 条件;
修改数据方式2: 修改字段对应的整列数据
格式: update 表名 set 字段名 = 值;
4.查:
查询所有数据方式1: *本质就是所有字段
格式: select * from 表名
格式: select 所有字段 from 表名
查询指定数据方式2: 指定字段
格式: select 指定字段 from 表名
# 4.对表中数据(记录)的增删改查
# 注意: 要想操作表中数据,必须先有表(表中存储多条记录)
# 增: 插入数据方式1 : insert into 表名 (字段1,字段2...) values (值1,值2...)
insert into user (uid, uname, description) VALUES (1,'张三','帅气');
insert into user (uid, uname, description) VALUES (2,'张三1','666');
# 增: 插入数据方式2 : insert into 表名 values (值1,值2...)
insert into user VALUES (null,'张三2','666'); # auto_increment自增长
# 增: 插入数据方式3 : insert into 表名 values (值1,值2...),(值11,值22...),(值111,值222...)
insert into user VALUE (null,'张三3','888'),(null,'张三4','888'),(null,'张三5','888');
insert into user VALUES (null,'张三6','999'),(null,'张三7','999'),(null,'张三8','999');
# 删: 删除指定数据 : delete from 表名 where 条件
delete from user where description = '999';
delete from user where uname = '张三5';
delete from user where uid = '5';
# 删: 删除所有数据方式1: delete from 表名;
delete from user;
# 使用delete可以删除所有数据,但是原来的自增没有清空,重新插入的时候,自增会继续按照删除前的追加
insert into user VALUES (1,'张三1','111'),(2,'张三2','222'),(null,'张三3','333');
# 删: 删除所有数据方式2 : truncate 表名;
truncate user;
# 使用truncate可以删除所有数据,也可以把自增清空,重新插入的时候,重新从1开始自增
insert into user VALUES (null,'张三1','111'),(null,'张三2','222'),(null,'张三3','333');
# 改: 修改指定数据: update 表名 set 字段名=值 where 条件;
update user set description='帅气' where uid = 1;
# 改: 修改全部数据: update 表名 set 字段名=值 ;
update user set description='帅气';
# 查: 查询所有数据: select * from 表名;
select * from user;
select uid , uname , description from user;
(五)对MySql数据库基础的快速记忆
二. MySql约束和查询
(一)约束
1.主键约束
primary key :主键约束 特点:主键默认非空唯一(不能为空不能重复)
注意: 主键在表中只有一个
# 1.创建数据库
create database dajin;
# 2.使用数据库
use dajin;
# 3.创建表
# 添加主键约束方式1: 创建表时添加
create table user1(
uid int PRIMARY KEY,
uname varchar(20),
pwd varchar(20)
);
# 查看表结构
desc user1;
# 添加主键约束方式2: 后期使用add添加
create table user2(
uid int,
uname varchar(20),
pwd varchar(20)
);
# 查看表结构
desc user2;
# add添加
alter table user2 add PRIMARY KEY (uid);
# 添加主键约束方式3: 后期使用change添加
create table user3(
uid int,
uname varchar(20),
pwd varchar(20)
);
# 查看表结构
desc user3;
# change添加
alter table user3 change uid uid int PRIMARY KEY ;
# 删除主键约束(默认就是非空约束和唯一约束)
desc user3; # 有主键约束(非空唯一)
alter table user2 drop PRIMARY KEY ;
desc user2; # 删除主键约束后,非空约束依然存在
alter table user1 drop PRIMARY KEY ; # 删除主键约束只能删除唯一约束,留下了非空约束
alter table user1 change uid uid int; # 删除非空约束
desc user1; # 既没有非空约束,也没有唯一约束
2.唯一约束
unique : 唯一约束 特点: 修饰的字段对应值不能重复
注意: 表中可以有多个唯一约束
# 3.2唯一约束
# 添加唯一约束方式1: 创建表时添加
create table user4(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20) UNIQUE ,
pwd varchar(20)
);
# 查看表结构
desc user4;
# 添加唯一约束方式2: 后期使用add添加
create table user5(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20) ,
pwd varchar(20)
);
# 查看表结构
desc user5;
# add添加
alter table user5 add UNIQUE (uname);
# 添加唯一约束方式2: 后期使用change添加
create table user6(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20) ,
pwd varchar(20)
);
# 查看表结构
desc user6;
# change添加
alter table user6 change uname uname varchar(20) UNIQUE ;
# 唯一约束的删除操作
desc user6; # 有唯一约束(结构有索引包,字段信息key有uni)
alter table user5 drop INDEX uname;
desc user5; # 删除成功(索引包和uni标识都不存在了)
alter table user4 drop INDEX uname;
desc user4; # 删除成功(索引包和uni标识都不存在了)
3.非空约束
not null: 非空约束 特点: 修饰的字段对应的值不能为空
注意: 表中可以有多个非空约束
# 3.非空约束
# 添加非空约束方式1: 创建表时直接添加
create table user7(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20) not null,
pwd varchar(20) not null
);
# 查看表结构
desc user7;
# 添加非空约束方式2: 后期使用change添加
create table user8(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20) ,
pwd varchar(20)
);
# 查看表结构
desc user8;
# change添加
alter table user8 change uname uname varchar(20) not null;
alter table user8 change pwd pwd varchar(20) not null;
# 非空约束的删除操作(利用change)
desc user8; # uname和pwd都是no不能为空
alter table user7 change uname uname varchar(20);
alter table user7 change pwd pwd varchar(20);
desc user7; # 删除完后都是yes可以为空
4.默认约束
default : 默认约束 特点: 修饰的字段可以设置默认值
# 4.默认约束
# 添加默认约束的方式1: 创建表时添加
create table user9(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20),
pwd varchar(20) DEFAULT '123'
);
# 查看表结构
desc user9;
# 注意想要使用默认约束的值,就不要指定字段
insert INTO user9 (uname) value('李四'),('王五'),('赵六');
# 如果指定了默认约束对应字段值,以指定的为主
INSERT into user9 value(null,'小柒','abc');
# 添加默认约束的方式2: 后期使用change添加
create table user10(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20),
pwd varchar(20)
);
# change添加
alter table user10 change pwd pwd varchar(20) DEFAULT 'abc';
# change删除
desc user9; # 有默认值
alter table user10 change pwd pwd varchar(20);
desc user10;# 修改后没有了默认值
# 添加默认约束的方式3: 后期使用alter 字段名 set 添加默认约束
create table user11(
uid int PRIMARY KEY AUTO_INCREMENT,
uname varchar(20),
pwd varchar(20)
);
# 查看表结构
desc user11;
# alter字段set去设置默认约束
alter table user11 alter pwd set default 'abc123';
# alter字段drop去删除默认约束
alter table user11 alter pwd drop default;
# 查看表结构
desc user11;
5.外键约束(第三块有)
(二)查询
数据准备
# 选择数据库
use dajin;
# 创建商品表
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
# 插入数据
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'ThinKPin',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'惠普',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'联想拯救者',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'七匹狼',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'特步',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花鸿星尔克',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'康师傅牛肉面',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香百味草',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'李宁',1,'c002');
1.简单查询
简单查询格式: select 字段名 | * from 表名
distinct关键字: 作用是去重
as关键字: 作用是起别名 注意: 可以给表起别名也可以给字段起别名
# 1.简单查询
# 需求1: 查询所有的商品名称和价格
select pname,price from product;
# 需求2: 查询所有商品信息
select pid,pname,price,category_id from product;
select * from product;
# 需求3: as可以给表或者字段起别名
select p.* from product as p; # 别名后期多表才能体会到别名方便之处
select pname as 商品名称,price as 商品价格 from product;
# 需求4: 查询所有商品价格,要求去重
select DISTINCT price from product;
# 需求5: 双十一让客户看到的价格在原来基础上都涨10元
select pname,price+10 from product;
2.条件查询
条件查询关键字: where
条件查询格式: select 字段名 | * from 表名 where 条件;
条件详解:
1.比较运算符: = > >= < <= != <>
2.范围 查询: 连续范围:between...and 非连续范围: in(x,y)
3.模糊 查询: 关键字:like _:任意1个字符 %:0个或者任意多个字符
4.空 判 断: 为空: is null 不为空: is not null
5.逻辑运算符: 并且:and 或者: or 取反:not
2.1比较运算符
# 2.1比较运算符: > >= < <= <> !=
# 需求1: 查询商品名称为花花公子的所有商品信息
select * from product where pname = '花花公子';
# 需求2: 查询价格为800的商品信息
select * from product where price = 800;
# 需求3: 查询价格不是800的商品信息
select * from product where price != 800;
select * from product where price <> 800;
# 需求4: 查询价格大于60的商品信息
select * from product where price > 60;
# 需求5: 查询价格在200-1000之间(含200,1000)商品信息
select * from product where price>=200 and price<=1000;
# 需求6: 查询价格是800或者2000或者3000的商品信息
select * from product where price=800 or price=2000 or price=3000;
2.2范围查询
# 2.2范围查询 : 连续范围:between...and 非连续范围: in(x,y)
# 需求1: 查询价格在200-1000之间(含200,1000)商品信息
select * from product where price BETWEEN 200 and 1000;
# 需求2: 查询价格是800或者2000或者3000的商品信息
select * from product where price in(800,2000,3000);
2.3模糊查询
# 2.3模糊查询: 关键字:like _:任意1个字符 %:0个或者任意多个字符
# 需求1: 查询以'香'开头的商品信息
select * from product where pname LIKE '七%';
# 需求2: 查询以'斯'结尾的商品信息
select * from product where pname LIKE '%宁';
# 需求3: 查询带有'想'的商品信息
select * from product where pname LIKE '%想%';
# 需求4: 查询以'香'开头,并且是三个字的商品信息
select * from product where pname LIKE '香__';
# 需求5: 查询以'斯'结尾,并且是三个字的商品信息
select * from product where pname LIKE '__狼';
# 需求6: 查询第二字是'花'的商品信息
select * from product where pname LIKE '_想%';
2.4 空判断
# 2.4空判断: 为空: is null 不为空: is not null
# 为了方便查看,插入数据
insert into product VALUE (14,'华为',6999,null);
insert into product VALUE (15,'小米',4999,'null');
insert into product VALUE (16,'诺基亚',999,'');
# 需求1: 查询没有商品分类的商品信息
select * from product where category_id is null;
# 需求2: 查询所有有商品分类的商品信息
select * from product where category_id is not null;
2.5逻辑运算符
# 2.5逻辑运算符: and : 并且 or: 或者 not:取反
# 需求1: 查询价格在400(含)到2000(含)的商品信息
select * from product where price >= 400 and price <= 2000;
# 需求2: 查询价格是2000,3000的商品信息
select * from product where price = 2000 or price = 3000;
# 需求3: 查询不在400到2000之间的商品信息
select * from product where not (price >= 400 and price <= 2000);
# 需求4: 查询商品名称不是香奈儿和花花公子的商品信息
select * from product where not(pname='香奈儿' or pname='花花公子');
select * from product where pname not in ('香奈儿','花花公子');
3. 排序查询
排序查询关键字: order by
排序规则: asc:默认升序 desc:降序
排序查询格式1: select 字段名 | * from 表名 order by 字段名 asc|desc;
注意:如果order by后跟多个字段,先按照第一个字段进行排序,如果第一个字段对应值相同,就按照第二个字段排序
排序查询格式2: select 字段名 | * from 表名 order by 字段名1 asc|desc 字段名2 asc|desc;
# 3.排序查询: order by asc:默认升序 | desc:降序
# 需求1: 按照价格升序排序
select * from product order by price; # 默认升序
select * from product order by price asc; #如果升序asc不建议写
# 需求2: 按照价格升序排序,如果价格相同,那就按照分类id升序排序
select * from product order by price,category_id; # 默认升序
select * from product order by price asc,category_id asc; #如果升序asc不建议写
# 需求3: 按照价格降序排序
select * from product order by price desc;
# 需求4: 按照价格降序排序,如果价格相同,那就按照分类id降序排序
select * from product order by price desc,category_id desc;
# 需求5: 查看所有价格,按照价格降序排序,要求去重
select DISTINCT price FROM product order by price desc;
# 需求6: 查询商品分类为c002的所有商品,并且按照价格升序排序
select * from product where category_id='c002' order by price;
# 需求7: 查询商品分类为c003的所有商品的闪屏名称和价格,并且按照价格降序排序
select pname,price from product where category_id='c003' order by
4.聚合查询
聚合函数: 又叫分组函数
聚合函数特点: 聚合函数(字段名): 自动忽略null值
常见的聚合函数:
count(): 总记录数
max() : 所有记录中最大值
min() : 所有记录中最小值
sum() : 所有记录的总和
avg() : 所有记录的平均值
拓展: round(数据,保留位数) : 让指定的数据保留几位小数
# 4.聚合查询
# 需求1: 查询表中一共多少条记录
select count(pid) from product;
select count(*) from product;
select count(1) from product;
# 需求2: 查询表中分类id不为null的商品个数
select count(*) from product where category_id is not null;
select count(category_id) from product;
# 需求3: 查询所有商品中最大价格
select max(price) from product;
# 需求4: 查询所有商品中最小价格
select min(price) from product;
# 需求5: 查询所有商品的总价
select sum(price) from product;
# 需求6: 查询所有商品的平均价格
select avg(price) as avg from product;
select sum(price)/count(*) as avg from product;
# 需求7: 查询商品分类为c001的商品中最大价格
select max(price) from product where category_id='c001';
# 需求8: 查询商品分类为c002的商品中最小价格
select min(price) from product where category_id='c002';
# 需求9: 查询商品分类为c003的商品总价
select sum(price) from product where category_id='c003';```
### 5.分组查询
分组查询关键字: group by
分组查询格式: select 字段名 | * from 表名 group by 分组字段名 having 分组条件;
where和having的区别?
where : where只能过滤分组前的数据,后面不能直接使用分组函数
having: having用于过滤分组后的数据,后面可以使用分组函数
```sql
# 需求1: 查询商品有哪些分类,要求去重
select DISTINCT category_id from product;
select category_id from product GROUP BY category_id;
# 需求2: 查询每个分类中的商品个数
select category_id,count(*) from product GROUP BY category_id;
# 需求3: 查询每个分类中的商品最大价格
select category_id,max(price) from product GROUP BY category_id;
# 需求4: 查询每个分类中的商品最小价格
select category_id,min(price) from product GROUP BY category_id;
# 需求5: 查询每个分类中的商品总价
select category_id,sum(price) from product GROUP BY category_id;
# 需求6: 查询每个分类的平均价
select category_id,avg(price) from product GROUP BY category_id;
# 拓展: round(数据,保留位数) : 让指定的数据保留几位小数
select category_id,round(avg(price),2) from product GROUP BY category_id;
# 需求7: 查询每个分类中的商品个数,要求只显示大于1的分类和个数
select category_id,count(*) from product GROUP BY category_id having count(*)>1 ;
6.分页查询
分页查询关键字: limit
分页查询格式: select 字段名 | * from 表名 limit x,y;
x:整数 从x索引开始, 索引: 默认0开始
y:整数 查询多少条记录
# 需求1: 展示当前表中前3条记录
select * from product limit 0,3; # 如果索引是0开始,0可以省略不写
select * from product limit 3;
# 需求2: 从第4条记录开始,展示5条记录
select * from product limit 3,5;
# 注意:如果超出范围,不会报错,只是没有查到结果
select * from product limit 20,5;
# 需求3: 查询商品价格最高的3个商品
select * from product order by price desc limit 3;
# 需求4: 查询商品价格最底的5个商品
select * from product order by price limit 5;
(三)MySql约束和查询快速记忆
三,分表操作和多表查询
(一) 分表操作
数据准备
# 选择数据库
use dajin;
# 创建商品表
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
# 插入数据
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'ThinKPin',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'惠普',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'联想拯救者',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'七匹狼',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'特步',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花鸿星尔克',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'康师傅牛肉面',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香百味草',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'李宁',1,'c002');
知识点
分表步骤:
1.创建新表用于存储数据
2.在原始表中查找数据
3.把查找到的数据插入到新表中
分表方式:
方式1.原始方式(笨方法) : 依次按照每一步操作
方式2.第二步和第三步合并: insert into 新表(字段名) select 语句;
方式3.第一第二第三步合并: create table 新表名(字段名 字段类型 字段约束) select
# 分表操作
# 方式1: 笨方法
# 1.创建存储c001所有商品信息的表
create table product1(
pid int PRIMARY KEY,
pname varchar(20),
price double
);
# 2.在原始表中查找出所有的c001的商品
select pid,pname,price from product where category_id='c001';
# 3.把刚才查到的数据放到c001的表中
insert into product1 values(1,'Thinkpin',5000),(2,'惠普',3000),(3,'联想拯救者',5000);
# 方式2: insert into 表名 select 语句
# 1.创建存c002所有商品信息的表
create table product2(
pid int PRIMARY KEY AUTO_INCREMENT,
pname varchar(20),
price double
);
# 2.在原始表中查找出所有c002的商品
# 3.把刚才查到的数据放到c002表中
insert into product2(pname,price) select pname,price from product where category_id='c002';
# 方式3:创建表同时插入数据
# 1.创建存c003所有商品信息的表
# 2.在原始表中查找出所有c003的商品
# 3.把刚才查找到的数据放到c003表中
create table product3(
pid int PRIMARY KEY AUTO_INCREMENT,
pname varchar(20),
price double
)select pname,price from product where category_id='c003';
# 练习: 依次把c004和c005商品分表放到对应表中
create table product4(
pid int PRIMARY KEY AUTO_INCREMENT,
pname varchar(20),
price double
)select pname,price from product where category_id='c004';
create table product5(
pid int PRIMARY KEY AUTO_INCREMENT,
pname varchar(20),
price double
)select pname,price from product where category_id='c005';
(二)多表查询
1.外键约束
知识点
多表详解: “分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。
多表: 至少有两个表: 主表(category) 和 从表(product)
一对多关系: 主表(一方) 和 从表(多方)
注意: MyIsam存储引擎不支持外键约束,不支持事务 innoDB存储引擎支持外键约束,支持事务
my.ini文件中修改存储引擎: default-storage-engine=innoDB
注意: 修改完my.ini文件后一定要重启mysql服务,然后新创建的数据库就会使用innoDB引擎了
外键约束: foreign key
约束从表插入数据: 如果从表插入的数据中外键在主表中不存在,不能直接插入
约束主表删除数据: 如果主表删除的数据已经被从表引用,不能直接删除
# 二.多表查询
# 1.创建数据库
create database day_11;
# 2.使用/切换数据库
use day_11;
# 数据准备
#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) #分类名称
);
# 商品表
CREATE TABLE products (
pid varchar(32) PRIMARY KEY ,
name VARCHAR(40) ,
price DOUBLE ,
category_id varchar(32)
);
# 外键约束: foreign key
# 1.添加外键约束
alter table products add foreign key (category_id) references category (cid);
desc category; # 一方
desc products; # 多方 -> mul标记
# 2.演示外键约束
# 约束从表插入数据: 如果从表插入的数据中外键在主表中不存在,不能直接插入
insert into products value('p001','小米',4999,null); # 插入成功
insert into products value('p002','小米',4999,'c1'); # 报错,因为c1在主表中不存在 child孩子
# 约束主表删除数据: 如果主表删除的数据已经被从表引用,不能直接删除
insert into category value ('c001','手机'); # 插入成功
delete from category where cid='c001'; # 删除成功: 从表没有引用
insert into category value ('c001','手机'); # 插入成功
insert into products value('p002','小米',4999,'c001'); # 从表添加引用
delete from category where cid='c001'; # 删除失败: 因为从表中有引用 parent父
# 3.删除外键约束
alter table products drop FOREIGN KEY products_ibfk_1;
# 查看可视化: 右键products表->图表->显示可视化 :发现没有引用那个箭头了
查询数据准备
# 0.数据准备
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
# 分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
# 商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
3.连接查询
图解
交叉连接查询(笛卡尔积)
select * from category,products; # 笛卡尔积其实是一种错误!
内连接查询(inner…join…on)
# 内连接查询(inner...join...on)
# 需求1: 查询商品"花花公子"对应的分类名
# 原始方式
select category_id from products where pname = '花花公子';
select cname from category where cid='c002';
# 方式1: 隐式内连接
select pname,cname from category c,products p where c.cid=p.category_id and pname='花花公子' ;
# 方式2: 显式内连接
select pname,cname from category c inner join products p on c.cid=p.category_id where pname = '花花公子';
# 需求2: 查询哪些分类的商品已经上架
# 方式1: 隐式内连接
select DISTINCT cname from category c,products p where c.cid=p.category_id and p.flag=1 ;
# 方式2: 显式内连接: inner 可以省略的
select DISTINCT cname from category c join products p on c.cid=p.category_id and p.flag=1 ;
select DISTINCT cname from category c join products p on c.cid=p.category_id where p.flag=1 ;
# 需求3: 查询化妆品的所有商品详细信息
# 方式1: 隐式内连接
select p.* from category c,products p where c.cid=p.category_id and cname='化妆品';
# 方式2: 显式内连接: inner 可以省略的
select p.* from category c join products p on c.cid=p.category_id and cname='化妆品';
select p.* from category c join products p on c.cid=p.category_id where cname='化妆品';
外连接查询(outer…join…on)
# 外连接查询(outer...join...on)
# 为了效果明显,查询前加入一条数据
insert into category value('c004','奢侈品');
# 需求1: 查询所有分类商品的个数
# 外连接分类: 左外连接和右外连接 注意:outer可以省略
# 左表和右表只是位置不同,在前面的是左表,在后面的是右表
# 左外连接: 以左表为主
select cname,count(pname) from category c left outer join products p on c.cid=p.category_id GROUP BY cname;
select cname,count(pname) from category c left join products p on c.cid=p.category_id GROUP BY cname;
# 右外连接: 以右表为主
select cname,count(pname) from products p right outer join category c on c.cid=p.category_id GROUP BY cname;
select cname,count(pname) from products p right join category c on c.cid=p.category_id GROUP BY cname;
# 注意: 左右连接建议记忆一种方式即可
# 需求2: 查询化妆品的所有商品详细信息
# 筛选1次,容易出问题
select * from category c left join products p on c.cid=p.category_id and cname='化妆品';
# 筛选2次,结果准确
select * from category c left join products p on c.cid=p.category_id where cname='化妆品';
4.子查询
# 子查询
# 一个select语句作为另外一个select语句的一部分(条件或者表)
# 需求1: 查询商品"花花公子"对应的分类名
# 原始方式
select category_id from products where pname = '花花公子'; # 结果是c002
select cname from category where cid = 'c002';
# 子查询方式
select cname from category where cid = (select category_id from products where pname = '花花公子');
# 需求2: 查询'服饰'这个分类里所有的商品详情
# 原始方式
select cid from category where cname = '服饰'; # 结果是c002
select * from products where category_id = 'c002';
# 子查询方式
select * from products where category_id = (select cid from category where cname='服饰');
# 需求3: 查询'服饰'这个分类里所有 上架 的商品详情
# 格式化:ctrl+alt+L
# select语句作为条件
select * from products where category_id = (select cid from category where cname='服饰') and flag = 1 ;
select * from products where flag=1 and category_id = (select cid from category where cname='服饰');
# select语句作为表
select * from products p,(select * from category where cname='服饰') c where p.category_id=c.cid and flag=1 ;
select * from products p join (select * from category where cname='服饰') c on p.category_id=c.cid and flag=1 ;
select * from products p join (select * from category where cname='服饰') c on p.category_id=c.cid where flag=1 ;
# 需求4: 查询'服饰'和'家电'的所有商品详情
# 原始方式
select cid FROM category where cname='家电' or cname='服饰'; # 结果是c001,c002
select * from products where category_id in ('c001','c002');
# 子查询方式
select * from products where category_id in (select cid FROM category where cname='家电' or cname='服饰');
# 需求5: 查询'服饰'和'家电'的所有 上架 的商品详情
select * from products where category_id in (select cid FROM category where cname='家电' or cname='服饰') and flag=1;
select * from products where flag=1 and category_id in (select cid FROM category where cname='家电' or cname='服饰');
(三)多表操作和多表查询
四,自连接和窗口函数
(一) 自连接查询
数据准备
#1.创建数据库
CREATE DATABASE dajin;
2.使用数据库
use dajin;
3.导入表并插入数据
步骤: 右键areas.sql->运行areas.sql->目标源+号->双击选择数据库->应用并运行
# 自连接讲解
# 查询广东省下所有的城市
# 自连接方式
select c.title from areas p,areas c where p.id=c.pid and p.title='广东省';
select c.title from areas p join areas c on p.id=c.pid and p.title='广东省';
select c.title from areas p join areas c on p.id=c.pid where p.title='广东省';
# 子查询方式
select title from areas where pid=(select id from areas where title='广东省');
# 查询河北省下所有的城市
# 自连接方式
select shi.title from areas sheng,areas shi where sheng.id = shi.pid and sheng.title='河北省';
select shi.title from areas sheng join areas shi on sheng.id = shi.pid and sheng.title='河北省';
select shi.title from areas sheng join areas shi on sheng.id = shi.pid where sheng.title='河北省';
# 子查询方式
select title from areas where pid=(select id from areas where title='河北省');
(二)开窗函数
知识点
开窗函数: mysql8.0版本以上新增的功能 好处: 简单,快捷
特点: 能够让输出的行数和输入的行数保持一致
格式: 函数 over(partition by 分组字段名 order by 排序字段名 asc|desc)
常见开窗函数:
唯一连续的排序: row_number() 巧记: 1234
并列连续的排序: dense_rank() 巧记: 1223
并列不连续排序: rank() 巧记: 1224
# 开窗函数
# 关闭一扇门: mysql8以后select后面的字段必须在聚合函数或者groupby后面出现,否则报错
select id,title,pid from areas group by pid; # mysql8报错,sqlmode模式做了限制
select id,title,pid,max(pid) from areas; # mysql8报错,sqlmode模式做了限制
# 打开一扇窗: mysql8新增窗口函数,能够让输入的行数和输出的行数保持一致
select id,title,pid,max(pid) over() from areas; # 使用了窗口over()函数后不报错
select id,title,pid,max(pid) over(),pid-max(pid) over() '差值' from areas; # 使用了窗口over()函数后不报错
# 窗口排序函数
# 先选择数据库
use day12;
# 数据准备
CREATE TABLE employee
(
empid int,
ename varchar(20),
deptid int,
salary decimal(10, 2)
);
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);
insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);
insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);
# 窗口排序函数使用
# 格式: 开窗函数 over(order by 字段名)
# 需求1:使用三种排序函数给员工的工资降序排序
select
ename,
salary,
row_number() OVER (order by salary desc) row_number排序,
rank() OVER (order by salary desc) rank排序,
dense_rank() OVER (order by salary desc) dense_rank排序
from employee ;
# 需求2:使用三种排序函数按照部门分别给员工的工资降序排序
select
ename,
salary,
deptid,
row_number() OVER (PARTITION BY deptid order by salary desc) row_number排序,
rank() OVER (PARTITION BY deptid order by salary desc) rank排序,
dense_rank() OVER (PARTITION BY deptid order by salary desc) dense_rank排序
from employee ;
1817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
