MYSQL数据库（补充练习）

hang_AI427

已于 2022-08-24 16:23:07 修改

阅读量126

点赞数

分类专栏：笔记 sql应用文章标签：数据库 mysql sql

于 2022-08-23 16:46:59 首次发布

本文链接：https://blog.csdn.net/qq_32752467/article/details/126488132

版权

笔记同时被 2 个专栏收录

54 篇文章 3 订阅

订阅专栏

sql应用

14 篇文章 0 订阅

订阅专栏

一：什么是SQL？

Structured Query Language：结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。

二：SQL通用语法

SQL 语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
数据库的注释：
- 单行注释：-- 注释内容 #注释内容(mysql特有)
- 多行注释：/* 注释内容 */

三：SQL分类

DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言
- 用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

四：练习

（1）MYSQL基础练习

-- DDL操作数据库
show databases;
create database db1;
create database IF NOT exists db1;
create database db2 character set utf8;
show create database db2;
-- 重点  
create database if not exists db3 character set gbk; 
alter database db3 character set utf8;
drop database if exists db1;
select database();

show create database db3;
drop database db1;

-- DDL 操作数据表
-- show databases;
-- drop database if exists db2;
-- drop database if exists db3;
create database if not exists db1 character set utf8;
use db1;
show tables;
-- 新建表 (不含主键约束，主键约束在之前的博客有说)
create table product(
	id int,
    NAME varchar(30),
    price double,
    stock int,
    insert_time date
);
desc product;
show table status from db1 like 'product';
-- 复制表
create table product2 like product;
-- 修改表
alter table product2 rename to product_rename;
alter table product_rename character set utf8;

alter table product_rename add color varchar(10);
alter table product_rename modify color int;
alter table product_rename drop color;
-- 删除表
drop table if exists product_rename;
show tables;

-- DML-insert
INSERT INTO product(id,NAME,price,stock,insert_time) 
VALUES (1,'手机',1999,22,'2099-09-09');

insert into product(id, NAME, price) values (2, '电脑', 4999);
select * from product;

INSERT INTO product VALUES (4,'冰箱',999,26,'2099-08-08'),
(5,'洗衣机',1999,32,'2099-05-10');
select * from product;

-- DML-UPDATE
set sql_safe_updates=0; 
UPDATE product SET price=3500 WHERE NAME='手机';
UPDATE product SET price=1800,stock=36 WHERE NAME='电视';

-- DML- DELETE
delete from product where NAME = '电脑';
drop table if exists product;

-- DQL
-- DQL-单表查询
CREATE TABLE product(
	id INT,				-- 商品编号
	NAME VARCHAR(20),	-- 商品名称
	price DOUBLE,		-- 商品价格
	brand VARCHAR(10),	-- 商品品牌
	stock INT,			-- 商品库存
	insert_time DATE    -- 添加时间
);
INSERT INTO product VALUES (1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');
select NAME from product;
select * from product;

-- 去重
select distinct brand from product;
-- 计算
select NAME, stock+10 from product;
select NAME, IFNULL(stock, 0)+10 getNum from product;

-- 查询函数
select * from product where stock is null;
select * from product where stock in (14, 30, 23);

-- 聚合函数
-- count(*) , MAX(*), MIN(-), AVG(-)

-- 排序函数
SELECT * FROM product ORDER BY price ASC,stock DESC;

-- 分组函数
-- 对金额大于4000元的商品，按照品牌分组，
-- 获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand,SUM(price) AS getSum FROM product 
WHERE price > 4000 GROUP BY brand 
HAVING getSum > 7000 ORDER BY getSum DESC;

-- 分页查询
SELECT * FROM product LIMIT 0,2;  -- 第一页 开始索引=(1-1) * 2

-- 多表查询操作
-- 数据准备
-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),			        -- 用户姓名
	age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);


-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),					-- 订单编号
	uid INT,    -- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);


-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);


-- 中间表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

-- 多表查询
select * from USER, orderlist;
-- 多表查询-子查询
-- 查询张三和李四的订单信息
SELECT number,uid FROM orderlist 
WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');

-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o 
WHERE u.id=o.uid;

-- 多表查询，自关联alter
-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
	员工姓名 employee表        直接上级姓名 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT
	t1.name,	-- 员工姓名
	t1.mgr,		-- 上级编号
	t2.id,		-- 员工编号
	t2.name     -- 员工姓名
FROM
	employee t1  -- 员工表
LEFT OUTER JOIN
	employee t2  -- 员工表
ON
	t1.mgr = t2.id;

-- 连接查询
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;

-- 创建视图 <如果经常需要某种查询，可以保存到虚拟表中>
-- 普通多表查询，查询城市和所属国家
SELECT
	t1.*,
	t2.country_name
FROM
	city t1,
	country t2
WHERE
	t1.cid = t2.id;
-- 创建试图将查询出来的结果保存到这张虚拟表中
create view city_country
as 
select t1.*, t2.country_name from city t1, country t2 where t1.cid = t2.cid;

show tables;
desc product;
create view product_view
as 
select * from product;
-- 查询视图
select * from product;
select * from product_view;

show create view product_view;

-- 修改视图2的列名city_id为id
ALTER
VIEW
	city_country2 (id,city_name,cid,country_name)
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
    -- 删除视图
DROP VIEW city_country;
    -- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

（2）MYSQL进阶练习

-- 进阶
-- 1. insert into 表名 (后接复杂的select操作)
-- 将user, orderlist, product, category汇总成一张表test_tmp，同时新建三个字段用来计算一些数值
-- 注：用户和订单是一对多的关系，用户和商品是一对多，商品种类和商品是一对多。所以构建成一张表会产生很多重复项
create table test_tmp(
	user_id INT comment '用户id',
    user_name varchar(20) comment '用户姓名',
    user_age int comment '用户年龄',
	orderlist_id INT comment '订单id',	
	orderlist_number VARCHAR(30) comment '订单编号',					
	category_id INT comment '商品分类id',  
	category_NAME VARCHAR(10) comment '商品分类id',            
	product_id INT comment '商品id',   
	product_NAME VARCHAR(30) comment '商品名称'
);
desc test_tmp;
drop table test_tmp;
-- 数据整合(只要商品分配category.id是1，2，3的<where判断>)
-- 注：where执行过程是在from join on之后的。
-- 如果需要添加某些列，需要在前面写上要添加的列名, insert into test_tmp (user_id, user_name) (select u.id. u.name XXXX);
-- 这种用select后缀添加的方法实际上省略了values, 如果不用select添加，就需要加上values， insert into test_tmp (user_id, user_name) values (1, '张三');
insert into test_tmp(
select 
u.id, u.name, u.age, o.id, o.number, c.id, c.name, p.id, p.name
from user u 
join orderlist o on u.id = o.id
join us_pro us on u.id = us.uid
join product p on us.pid = p.id
join category c on p.cid = c.id
where c.id in(1,2,3)
);
select * from test_tmp;


-- -------------------------------------------------------------------------------------------------------
-- 视图进阶
use db6;
-- 上述步骤也可以通过视图的方法创建（视图是随着基本表的变化而变化）
-- 注：视图只是方便查询结果的展示，实际的对表的操作就不可以了。
-- 视图中没有事先创建表，所以可能很多表的列名会重复，所以我们需要起别名
create view test_tmp
as
select 
u.id user_id, u.name user_name, u.age user_age, o.id orderlist_id, 
o.number orderlist_number, c.id category_id, c.name category_NAME, p.id product_id, p.name product_NAME
from user u 
join orderlist o on u.id = o.id
join us_pro us on u.id = us.uid
join product p on us.pid = p.id
join category c on p.cid = c.id
where c.id in(1,2,3);

-- 或者
create view test_tmp(
	user_id ,
    user_name ,
    user_age,
	orderlist_id,	
	orderlist_number,					
	category_id,  
	category_NAME,            
	product_id,   
	product_NAME
)
as
select 
u.id user_id, u.name user_name, u.age user_age, o.id orderlist_id, 
o.number orderlist_number, c.id category_id, c.name category_NAME, p.id product_id, p.name product_NAME
from user u 
join orderlist o on u.id = o.id
join us_pro us on u.id = us.uid
join product p on us.pid = p.id
join category c on p.cid = c.id
where c.id in(1,2,3);

DROP VIEW test_tmp;
select * from test_tmp;

-- -------------------------------------------------------------------------------------------------------
-- 如果整合表需要增加额外的列，我们就不可以使用视图的方法了
create table test_tmp(
	user_id INT comment '用户id',
    user_name varchar(20) comment '用户姓名',
    user_age int comment '用户年龄',
	orderlist_id INT comment '订单id',	
	orderlist_number VARCHAR(30) comment '订单编号',					
	category_id INT comment '商品分类id',  
	category_NAME VARCHAR(10) comment '商品分类id',            
	product_id INT comment '商品id',   
	product_NAME VARCHAR(30) comment '商品名称',
    num_a decimal(19, 2) not null default '0.00' comment '数值1', -- 19为整数部分，2为小数部分
    num_b decimal(19, 2) not null default '0.00' comment '数值2',
    num_c decimal(19, 2) not null default '0.00' comment '数值3'
);
drop table test_tmp; 
-- 以下两种insert方法都可以
-- 第一种方法，默认添加所有的列，所以需要全部一一对应上
insert into test_tmp(
select 
u.id, u.name, u.age, o.id, o.number, c.id, c.name, p.id, p.name,
convert(0.00, decimal(19, 2)), convert(0.00, decimal(19, 2)), convert(0.00, decimal(19, 2)) -- 添加0.00，并且是decimal(19, 2)格式的
from user u 
join orderlist o on u.id = o.id
join us_pro us on u.id = us.uid
join product p on us.pid = p.id
join category c on p.cid = c.id
where c.id in(1,2,3)
);
-- 第二种方法，只添加前面的列，因为后边的三列是 decimal(19, 2) not null default '0.00'格式的，会默认补充。
insert into test_tmp
(user_id, user_name, user_age, orderlist_id, orderlist_number, category_id, category_NAME, product_id, product_NAME)
(
select 
u.id, u.name, u.age, o.id, o.number, c.id, c.name, p.id, p.name
from user u 
join orderlist o on u.id = o.id
join us_pro us on u.id = us.uid
join product p on us.pid = p.id
join category c on p.cid = c.id
where c.id in(1,2,3));

select * from test_tmp;

-- update进阶计算1
select * from test_tmp;
-- 现在所有的num_a, num_b和num_c是0.00，所以我们定义规则:  
-- 当user_age >= 24的时候: num_a = user_age-1, num_b = orderlist_id+category_id+product_id, num_c=user_id+1
-- 当user_age < 24的时候: num_a = user_age+1, num_b = orderlist_id+category_id+product_id, num_c=user_id- 1
set sql_safe_updates=0; 
update test_tmp set num_a = user_age-1, num_b = orderlist_id+category_id+product_id, num_c=user_id+1
where user_age>=24;
update test_tmp set num_a = user_age+1, num_b = orderlist_id+category_id+product_id, num_c=user_id-1
where user_age<24;

-- 以上表示临时表创建结束。注：包含了很多一对多和多对多的关系. user中很多重复项
-- 开始创建正式表.我们首先复制一下临时表的字段，再清空所有的信息
-- 使用like复制
create table test like test_tmp;
show tables;
truncate table test;
select * from test;

-- 新表中希望user不重复,先插入user
insert into test (user_id, user_name, user_age)
(select user_id, user_name, user_age from test_tmp 
group by user_id, user_name, user_age);
-- 或者(有时候用distinct和groupy by效果一样，还比较简单)
insert into test (user_id, user_name, user_age)
(select distinct(user_id), user_name, user_age from test_tmp);
select * from test_tmp;
-- 我想将新表剩下的列都删除，然后增加自己的新列
-- 新的列包含以下指标 ，指标1 index_a：每位用户，计算num_a + num_b + num_c的值
alter table test drop orderlist_id;
alter table test drop orderlist_number;
alter table test drop category_id;
alter table test drop category_NAME;
alter table test drop product_id;
alter table test drop product_NAME;
alter table test drop num_a;
alter table test drop num_b;
alter table test drop num_c;

-- 指标1
-- update进阶计算2, 和inner join 关联，将tmp表的数据更新计算到新的表中
alter table test add index_a int;

select tt.num from test t join 
(select user_id, sum(num_a + num_b + num_c) num
from test_tmp group by user_id
) tt on t.user_id = tt.user_id;

update test t inner join
(select user_id, sum(num_a + num_b + num_c) num
from test_tmp group by user_id
) tt 
on t.user_id = tt.user_id
set 
t.index_a = tt.num;
-- 还可以附加where条件 update table1 inner join  table2 where 条件

select * from test;