Mysql学习

余甘不甘

已于 2023-07-18 09:02:46 修改

阅读量178

点赞数

分类专栏：数据库文章标签： sql 学习数据库

于 2022-05-30 12:20:24 首次发布

本文链接：https://blog.csdn.net/qq_48621751/article/details/124785189

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.准备工作

本篇文章的知识点全来自于本·福达前辈写的《SQL必知必会》，这真的是一本SQL讲解的通俗易懂的好书，强烈推荐给大家，而且在牛客上还有与书本对应的习题集，很方便自己去实践和验证相关的知识。

数据库学习路线

1.1sql必知必会在线编程地址

1.2 用到的数据库脚本

create

-- ----------------------
-- Create Customers table
-- ----------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);


-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);

-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL 
);

-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);


-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);


-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

populate

-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2012-02-08', '1000000001');

-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

1.3表的关系图

在这里插入图片描述

customers

在这里插入图片描述

orderitems

在这里插入图片描述

orders

在这里插入图片描述

products

在这里插入图片描述

vendors

在这里插入图片描述

基础知识

not

not in
not like

查询是否要加引号

in 里面如果查询字段为字符串，那还是要加引号的
题目地址

like 里面也要打引号
题目地址

单引号和双引号区别

地址

一个例子记住sql语句中执行顺序？

详解地址

在这里插入图片描述

select order_num from OrderItems group by order_num having(sum(quantity)) >=100 order by order_num;

可以理解为先得到原表OrderItems，然后group by将唯一的值组合成为一组，得到虚拟表，再根据条件过滤，然后查询，然后再排序

内连接、左外连接、右外连接

地址

奇奇怪怪的sql语句不兼容

题目

我最初想到的sql语句是下面这个，但是它只能在navicat上运行，在线编程时会报错

select C.cust_name,sum(OI.item_price * OI.quantity)  as total_price from Customers as C join Orders as O on  C.cust_id = O.cust_id join OrderItems as OI on  O.order_num = OI.order_num group by OI.order_num having(total_price) >= 1000 order by total_price;

后面选择修改成以下语句

select C.cust_name,OI.total_price from Customers as C join Orders as O on  C.cust_id = O.cust_id join (select order_num,sum((item_price * quantity)) as total_price from OrderItems group by order_num having(total_price) >= 1000 ) as OI on O.order_num = OI.order_num;

个人感觉跟sql关键词的执行顺序有关,第一个语句里的 having(total_price)执行在前，sum(OI.item_price * OI.quantity) as total_price执行在后，但是后面的反而依赖前面为算出的值进行筛选

一个例子记住对表的各种操作

创建表

create table student (  #注意这里是括号而不是花括号
   stu_name char(30) NOT NULL COMMENT "姓名",  # 可以理解为先定义数据命名，然后再定义相关约束
	 stu_age  int NOT NULL  COMMENT "年龄"  # 注意最后一行末尾没有","号
);

插入数据（增 insert）

insert into student(stu_name,stu_age) values ("张三",17);
insert into student(stu_name,stu_age) values ("李四",18);
insert into student(stu_name,stu_age) values ("王五",19);

删除数据( 删 delete)

delete from student where stu_name = "张三";

改变数据( 改 uptate)

update student set  stu_name = "刘尚界",stu_age = 20,stu_no = 11 where stu_name = "李四";

增加字段并为该字段创建索引(改变表结构 alter)

alter table student add column stu_no  int NOT NULL COMMENT "学号";
alter table student add index idx_stu_no (stu_no); # idx_stu_no为索引名字,stu_no为索引列

# 两个可以合并为
alter table student
add column stu_no  int NOT NULL COMMENT "学号",
add index idx_stu_no (stu_no);

添加主键

alter  table student add primary key(stu_no);

2.mysql语句在线编程练习

2.1检索顾客名称并且排序

题目
代码

select cust_name from `Customers`
 order by cust_name DESC;

题目

select order_num,count(order_num) as order_lines from OrderItems group by order_num order by order_lines;

2.2 between and的用法

地址
代码

select prod_name,prod_price from `Products` 
where prod_price between 3 and 6;

2.3 IN 的使用

在这里插入图片描述

题目
代码

select order_num,prod_id,quantity from  `OrderItems` 
where prod_id in ('BR01','BR02','BR03') and  quantity>=100;

2.4 like的使用(like是出现某个字符串，not like是不出现)

题目
代码

select prod_name,prod_desc from `Products` 
where prod_desc like '%toy%';

2.5有先后顺序的模糊查询

题目
代码

select prod_name,prod_desc from Products 
where prod_desc like '%toy%carrots%';

2.6计算字段的别名

题目
代码

select prod_id,prod_price,prod_price*0.9 as sale_price from Products;

2.7 upper,concat,substring三大函数的联用

地址
代码

select cust_id,cust_name,
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) as user_login
from Customers  //注意substring是从1开始

2.8 日期的比较

题目
代码
这里推荐两个简单易懂的思路
思路一:用 like来查找

select order_num, order_date 
from Orders
where order_date like '2020-01%'
order by order_date

思路二：字符串比较

select *
from Orders
where order_date >= '2020-01-01 00:00:00' and order_date <= '2020-01-31 23:59:59'
order by order_date;

2.9sum函数的使用

题目
代码

select sum(quantity) as items_ordered from OrderItems;

2.10 常用的数值处理函数

在这里插入图片描述

代码
- 最大值—max()
- 最小值—min()
- 平均值—avg()
- 总值 —sum()
  -总数 —count()

select max(prod_price) as max_price
from Products
where prod_price<=10

2.11 having函数的使用

题目
代码

where在数据分组前进行过滤，having在数据分组后进行过滤

select  order_num,sum(item_price*quantity) as total_price from  OrderItems 
group by order_num
having sum(item_price*quantity)>=1000  
;

2.12groupby,having,order by在同一语句的顺序

题目

在这里插入图片描述

select order_num from OrderItems group by order_num having(sum(quantity)) >=100 order by order_num;

2.13子查询in的使用

题目
代码

select cust_id from Orders where 
order_num in (select order_num from OrderItems where item_price>=10);

2.14 两表联合查询

题目
代码

select  a.cust_id,b.total_ordered from Orders as a 
inner join (select order_num,sum(item_price*quantity) as total_ordered from OrderItems group by order_num) as b
on a.order_num=b.order_num order by total_ordered desc;

//二刷思路
select o.cust_id,sum((ot.item_price * ot.quantity)) as total_ordered from Orders as o join OrderItems as ot on o.order_num = ot.order_num group by cust_id order by total_ordered desc;

2.15三表联合查询

题目
代码

select C.cust_name,O.order_num,a.OrderTotal from Orders as O 
inner join (select order_num,sum(quantity*item_price) as OrderTotal from OrderItems group by order_num) a
on O.order_num=a.order_num
inner join Customers as C 
on O.cust_id=C.cust_id
order by C.cust_name,a.order_num;

// 二刷 别名最好起单词第一个字母的大写，比如 OrderItems as OI,下面的别名没起好
select c.cust_name,o.order_num,(oi.quantity*oi.item_price) as OrderTotal from Customers as c join Orders as o on c.cust_id = o.cust_id  join OrderItems as oi on o.order_num = oi.order_num order by c.cust_name,o.order_num;

2.16 利用外连接返回查询的空值

题目
代码

select C.cust_name,O.order_num from Customers as C
left  join Orders as O 
on C.cust_id=O.cust_id
order by C.cust_name;

2.17把空值替换成0返回

题目

在这里插入图片描述

select P.prod_name,if(OI.orders is null,0,OI.orders)  from  Products as P left join (select prod_id,count(prod_id) as orders from OrderItems group by prod_id ) as OI   on P.prod_id = OI.prod_id order by P.prod_name;

2.18组合查询union的使用

题目
代码

select prod_id,quantity from OrderItems
where quantity=100
union 
select prod_id,quantity from OrderItems 
where prod_id like 'BNBG%'
order by prod_id;

2.19 用union把两个表拼接到一起

题目
代码

select prod_name from Products
union 
select cust_name as prod_name from Customers
order by prod_name

2.20 limit用法讲解

在这里插入图片描述

2.21增加字段并为该字段创建索引

ALTER TABLE `t_task_member` 
ADD COLUMN `copy_seq` INT ( 11 ) DEFAULT '0' COMMENT '复制任务的序列号',-- 添加列
ADD COLUMN `user_task_start_time` datetime ( 6 ) DEFAULT NULL COMMENT '用户任务开始时间',
DROP INDEX `unk_sub_task_id_shop_id_task_member_id`,-- 删除索引
ADD INDEX `idx_sub_task_id` ( `sub_task_id` ) USING BTREE,-- 添加普通索引
ADD UNIQUE `unk_sub_task_id_copy_seq_shop_id_task_member_id` ( `sub_task_id`, `copy_seq`, `shop_id`, `task_member_id` ) USING BTREE;-- 添加唯一索引

2.22 distinct查询结果不重复

题目

select distinct prod_id from OrderItems;

2.23 先对某列进行升序排序，再对某列进行降序排序

题目

select cust_id,order_num from `Orders` order by cust_id,order_date desc;

余甘不甘

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习

1.准备工作本篇文章的知识点全来自于本·福达前辈写的《SQL必知必会》，这真的是一本SQL讲解的通俗易懂的好书，强烈推荐给大家，而且在牛客上还有与书本对应的习题集，很方便自己去实践和验证相关的知识。数据库学习路线1.1sql必知必会在线编程地址1.2 用到的数据库脚本create-- ------------------------ Create Customers table-- ----------------------CREATE TABLE Customers( c
复制链接

扫一扫