DML,DDL,DQL小练习_mysql简单dml,dql练习-CSDN博客

本文链接：https://blog.csdn.net/Csu__/article/details/127954828

-- 练习1
/*  DML操作数据库  */
-- 查看所有数据库
SHOW DATABASES;

-- 直接创建数据库db1,db2

CREATE DATABASE db1;
CREATE DATABASE db2;
-- 判断是否存在并创建数据库db4
CREATE DATABASE IF NOT EXISTS db4;


-- 删除db4数据库
DROP DATABASE db4;

-- 判断数据库存在才删除db2
DROP DATABASE IF EXISTS db2;

-- 切换数据库到db1
USE db1;


/*  DDL操作表  */

/*
创建商品表(goods)包含: 
	商品名称(name),
	商品价格(price),
	商品销量(sales_volume),
	商品生产日期(produced_date)
*/
CREATE TABLE goods(
		NAME VARCHAR(10),
		price DOUBLE,
		sales_volume INT,
		produced_date DATE
               );

/*
需求：设计一张学生表，请注重数据类型、长度的合理性
	编号，			整数
	姓名，			姓名最长不超过10个汉字
	性别，			因为取值只有两种可能，因此最多一个汉字
	生日，			取值为年月日
	入学成绩，		小数点后保留两位
	邮件地址，		最大长度不超过 64
	家庭联系电话，	不一定是手机号码，可能会出现 - 等字符，20位以内
*/

CREATE TABLE student(
		id INT,
		NAME VARCHAR(10),
		sex CHAR(1),
		birthday DATE,
		score DOUBLE(5,2),
		email VARCHAR(64),
		phone VARCHAR(20)
);


-- 查看db1数据库中的所有表
SHOW TABLES;

-- 查看表结构
DESC goods;

-- 直接删除表student表
DROP TABLE student;


-- 将goods改名成goods2
ALTER TABLE goods RENAME TO goods2;


-- 为goods2表添加一个新的字段img,类型为varchar(20)
ALTER TABLE goods2 ADD img VARCHAR(20); 


-- 将goods2表中的img字段的改成varchar(100)
ALTER TABLE goods2 MODIFY img VARCHAR(100);


-- 将goods2表中的img字段名改成icon，类型varchar(80)
ALTER TABLE goods2  CHANGE img icon VARCHAR(80);


-- 删除goods2表中的字段icon
ALTER TABLE goods2 DROP icon;

/* DML操作表中记录 */
-- 插入数据, 给指定列添加数据 给goods表添加一条数据 NAME='格力空调', price=3699
-- 注意:在MySQL中字符串可以使用""或'', 建议''
INSERT INTO goods2(NAME,price) VALUES('格力空调',3999);

-- 插入数据, 所有的字段名都写出来(少数做法)
-- 注意:日期使用''
INSERT INTO goods2(NAME,price,sales_volume,produced_date) VALUES('海尔空调',2999,1000,'2022-11-20');


-- 插入数据, 插入所有字段不写字段名(常用做法)
-- '小米11', 4999, 300, '2021-03-22'
INSERT INTO goods2 VALUES('小米12',4999,300,'2022-11-01');


-- 扩展:一条SQL语句加入多条数据, VALUES 后面可以跟多个(), 一个()对应一条数据
-- 'iPhone 12', 6799, 12000, '2020-10-28'
-- 'DELL 7590', 8799, 300, '2019-06-18'
-- '立白洗衣粉', 12.9, 39000, '2018-02-13'
INSERT INTO goods2 VALUES
			('iPhone 12', 6799, 12000, '2020-10-28'),
			('DELL 7590', 8799, 300, '2019-06-18'),
			('立白洗衣粉', 12.9, 39000, '2018-02-13'),
			('华为P40',4999,300,'2022-03-01');


-- 不带条件修改数据，将所有的price改成0
UPDATE goods2 SET price = 0;


-- 带条件修改数据，把name为'华为P40'的商品price改成5999
UPDATE goods2 SET price = 5999 WHERE NAME = '华为P40';
-- update 表名 set 字段名 = 新的值[where条件]

-- 一次修改多个列，把name为'小米12'的商品price改成3999, sales_volume改成10000

UPDATE goods2 SET price = 3999,sales_volume = 10000 WHERE NAME ='小米12';

-- 带条件删除数据，删除name为'小米12'的数据，如果条件相同就一起删除
DELETE FROM goods2 WHERE NAME = '小米12';


-- 不带条件删除数据，删除表中的所有数据
DELETE FROM goods2;


/*  DQL查询记录  */

-- 准备数据
CREATE TABLE goods2 (
  NAME VARCHAR(10),
  price DOUBLE,
  sales_volume INT,
  produced_date DATE,
  category VARCHAR(20)
);

INSERT INTO goods2 VALUES 
('华为P40',5999,1000,'2020-08-20','手机'),
('小米11',4999,5000,'2020-12-28','手机'),
('红米K30',2999,22000,'2020-03-11','手机'),
('糯米',8.99,200,'2016-06-08','食物'),
('米糊',7.99,30,'2013-11-22','食物'),
('iPhone 12',6799,12000,'2020-10-28','手机'),
('DELL 7590',8799,300,'2019-06-18','电脑'),
('立白洗衣粉',12.9,39000,'2018-02-13','日用品'),
(NULL,88,666,NULL,NULL),
('联想电脑',8799,700,'2017-03-13','电脑'),
('惠普电脑',8799,50,'2008-12-13','电脑');

-- 查询goods2表中的 name 和 price 列
SELECT NAME,price FROM goods2;

-- 细节:查询只是查看数据,不会修改表中数据
-- 查询goods2表中所有字段
SELECT NAME,price,sales_volume,produced_date,category FROM goods2;

-- 查询所有字段, 使用*代表所有列, 列就是字段
SELECT * FROM goods2;

-- 我们学习是一般使用*这样查询简单快速,实际工作中,你需要什么字段就查询什么字段.

-- 添加一条相同的数据: '立白洗衣粉', 12.9, 39000, '2018-02-13','日用品'
INSERT INTO goods2 VALUES('立白洗衣粉', 12.9, 39000, '2018-02-13','日用品');

-- 去除重复查询: DISTINCT
SELECT DISTINCT NAME FROM goods2;

-- 扩展:查询每个商品的销售额price * sales_volume
SELECT price * sales_volume FROM goods2;

-- 扩展:所有商品价格打8折
SELECT price*0.8 FROM goods2;


-- 查询goods2表中的 name 和 price 列
-- name列的别名为 商品名称，price列的别名为 价格

SELECT NAME AS 商品名称,price AS 价格 FROM goods2;

-- 取别名时AS关键字可以省略
SELECT NAME 商品名称,price 价格 FROM goods2;


-- 条件查询
-- 查询price大于1000的商品
SELECT * FROM goods2 WHERE price >1000;

-- 查询sales_volume小于5000的商品
SELECT * FROM goods2 WHERE sales_volume <5000;

-- 查询price不等于6799的商品
SELECT * FROM goods2 WHERE price != 6799;


-- 逻辑运算符
-- 查询price大于1000且sales_volume小于500的商品(两个条件同时满足)
SELECT * FROM goods2 WHERE price>1000 AND sales_volume <500;

-- 查询price大于8000 或 sales_volume小于100的商品(两个条件其中一个满足)
SELECT * FROM goods2 WHERE price>8000 OR sales_volume <100;

-- 查询name是华为P40和小米11和米糊的商品
SELECT * FROM goods2 WHERE NAME = '华为P40' OR NAME = '小米11' OR NAME = '米糊';

-- in: 在...里面,只要是满足()里面的数据都可以
-- 查询name是 华为P40 和 小米11 和 米糊 的商品
SELECT * FROM goods2 WHERE NAME IN('华为P40','小米11','米糊');

-- 扩展:查询name不是华为P40和小米11和米糊的商品
SELECT * FROM goods2 WHERE NAME NOT IN('华为P40','小米11','米糊');

-- 范围: BETWEEN 值1 AND 值2 -- 表示从值1到值2范围，包头又包尾
-- 查询price大于等于1000，且小于等于5000的商品
SELECT * FROM goods2 WHERE price BETWEEN 1000 AND 5000;

-- 细节: between 值1 and 值2, 小的写前,面大的写后面

-- 扩展:查询商品名称是null的商品
SELECT * FROM goods2 WHERE NAME IS NULL;

-- 扩展:查询商品名称不是null的商品
SELECT * FROM goods2 WHERE NAME IS NOT NULL;

-- 模糊查询like
-- 查询米开头的商品
SELECT * FROM goods2 WHERE NAME LIKE '米%';

-- 查询商品名称中包含'米'字的商品
SELECT * FROM goods2 WHERE NAME LIKE '%米%';

-- 扩展:查询名称第二个字为米的商品
SELECT * FROM goods2 WHERE NAME LIKE '_米%';

-- 扩展:查询名称最后字为米的商品
SELECT * FROM goods2 WHERE NAME LIKE '%米'

-- 扩展:查询商品名称为4个字的
SELECT * FROM goods2 WHERE NAME LIKE '____'


/*  查询排序  */
-- order by 表示排序, 默认ASC升序, DESC降序

-- 单列排序
-- 查询所有数据,使用price升序排序
SELECT * FROM goods2 ORDER BY price ASC;


-- 查询所有数据,使用price降序排序
SELECT * FROM goods2 ORDER BY price DESC;

-- order by 默认是升序



-- 组合排序
-- 查询所有数据,在price降序排序的基础上，如果price相同再以sales_volume降序排序
SELECT * FROM goods2 ORDER BY price DESC ,sales_volume DESC;


-- 聚合函数
-- SELECT 聚合函数(字段) FROM 表名;
-- 查询商品个数, COUNT统计时会忽略NULL值
SELECT COUNT(NAME) FROM goods2;

-- COUNT最好的处理方式, *表示所有列理解为统计行数,最准确
SELECT COUNT(*) FROM goods2;

-- 扩展用法：统计price大于1000的总个数
SELECT COUNT(*) FROM goods2 WHERE price >1000;

-- 查询所有商品总销量
-- 总销量是把所有商品的销量加起来
SELECT SUM(sales_volume) FROM goods2;

-- 查询销量最低的商品
SELECT MIN(sales_volume) FROM goods2;

-- 查询销量最高的商品
SELECT MAX(sales_volume) FROM goods2;

-- 查询商品平均价格
SELECT AVG(price) FROM goods2;

-- 扩展:让小数显示指定的位数(2位)
-- ROUND(数据, 小数位数)
SELECT ROUND(AVG(price),2) FROM goods2;


-- 注意:查询聚合函数时无法同时查出同行的其它数据，需要使用之后学习的子查询



/*  分组查询
【注意】
1.where条件要放在group by 的前面；
2.where条件后面不能和聚合函数放一起用，如果非要用就用having条件判断并且放在group by后面使用。
3.不能直接select * from 表名 group by 列名；
  */
-- 按商品类型分组,不能直接select * from 表名 group by 列名；
SELECT category FROM goods2 GROUP BY category;

-- 分组后会返回每组的第一条数据
-- 通常我们只获取分组字段
-- 分组后通常是为了统计,分组后聚合函数操作每一组的数据

-- 查询每种类型的商品数量
SELECT category,COUNT(*) FROM goods2 GROUP BY category


-- 查询销量大于100的商品,按商品类型分组,统计每组的数量
SELECT category,COUNT(*) FROM goods2 WHERE sales_volume >100 GROUP BY category;

-- 查询销量大于100的商品,按商品类型分组,统计每组的数量,并只显商品类型数量大于2的数据

-- 这么写是错的SELECT category,COUNT(*) FROM goods2 WHERE sales_volume >100 and COUNT(*) >2 group BY category;  【Invalid use of group function】

SELECT category,COUNT(*) FROM goods2 WHERE sales_volume >100 GROUP BY category HAVING COUNT(*) >2;

-- 查询商品表中数据，跳过前面2条，显示3条
SELECT * FROM goods2 LIMIT 2,3;

-- 假设我们一每页显示3条记录的方式来分页，SQL语句如下：
-- 第一页: 跳过0条, 获取3条

-- 如果跳过的条数是0,可以省略

-- 第二页: 跳过3条, 获取3条

-- 第三页: 跳过6条, 获取3条

-- 第四页: 跳过9条, 获取3条

-- 计算公式：起始索引 = (当前页码-1)  *  每页显示的条数


-- 练习2
-- DDL操作数据库

-- 直接创建数据库db1

CREATE DATABASE db1;

-- 判断是否存在并创建数据库db2
CREATE DATABASE IF NOT EXISTS db2;

-- 查看所有的数据库
SHOW DATABASES;


-- 删除db2数据库
DROP DATABASE db2;

-- 切换数据库
USE db1;

-- DDL操作表
-- 创建student表包含id, name, birthday字段

CREATE TABLE student(id INT,
		     NAME VARCHAR(10),
		     birthday DATE
		     ); 

-- 查看MySQL数据库中的所有表
SHOW TABLES;

-- 直接删除表student表
DROP TABLE student;

-- 判断表是否存在并删除student表
DROP TABLE IF EXISTS student;

-- 创建student表包含id, name, birthday字段

CREATE TABLE student(id INT,
		     NAME VARCHAR(10),
		     birthday DATE
		     ); 

-- 为学生表添加一个新的字段remark,类型为varchar(20)
ALTER TABLE student ADD remark VARCHAR(20); 


-- 将student表中的remark字段的改成varchar(100)

ALTER TABLE student MODIFY remark VARCHAR(100);

-- 将student表中的remark字段名改成intro，类型varchar(30)

ALTER TABLE student CHANGE remark intro VARCHAR(30);

-- 删除student表中的字段intro
ALTER TABLE student DROP intro;


-- 将学生表student改名成student2
ALTER TABLE student RENAME TO student2;
DROP TABLE student; 
-- DML操作表中记录
-- 创建student表包含id, name, birthday, sex, address字段
CREATE TABLE student(id INT,
		     NAME VARCHAR(10),
		     birthday DATE,
		     sex CHAR(1),
		     address VARCHAR(25));

-- 插入数据,所有的字段名都写出来
INSERT INTO student(id,NAME,birthday,sex,address) VALUES(1,'小明','2020-02-02','男','北京');

-- 插入数据,插入所有字段可以不写字段名
INSERT INTO student VALUES (2,'小天','2021-02-02','男','广东');

-- 插入部分数据，往学生表中添加 部分数据
INSERT INTO student(id,NAME,sex) VALUES(3,'小王','女');

-- 不带条件修改数据，将所有的性别改成女
UPDATE student SET sex = '女';

-- 带条件修改数据，将id号为1的学生性别改成男
UPDATE student SET sex = '男' WHERE id = 1;


-- 一次修改多个列，把id为3的学生，生日改成1988-08-08，address改成北京
-- 不能这么写 update student set birthday = '1988-08-08',set address = '北京' where id =3;
UPDATE student SET birthday = '1988-08-08',address = '北京' WHERE id =3;
-- 带条件删除数据，删除id为3的记录
DELETE FROM student WHERE id = 3;

-- 不带条件删除数据,删除表中的所有数据
DELETE FROM student;
-- DQL查询数据

-- 准备数据
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT,
  NAME VARCHAR(20),
  age INT,
  sex VARCHAR(5),
  address VARCHAR(100),
  math INT,
  english INT
);

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);



-- 查询student表中所有字段
SELECT id,NAME,age,sex,address,math,english FROM student;

-- 查询所有字段,使用*代表所有列
SELECT * FROM student;

-- 查询student表中的 name 和 sex 列
SELECT NAME,sex FROM student;

-- 查询sudent表中name 和 sex 列，
-- name列的别名为 姓名，sex列的别名为 性别
SELECT NAME AS 姓名,sex AS 性别 FROM student;


-- 取别名时AS关键字可以省略
SELECT NAME 姓名,sex 性别 FROM student;

-- 查询address列并且结果不出现重复的address
SELECT DISTINCT address FROM student;

-- 条件查询


-- 查询math分数大于80分的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE math >80;


-- 查询english分数小于等于90分的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE english <= 90;


-- 查询age不等于20岁的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE age != 20;


-- 查询英语成绩不是null的
SELECT id,NAME,age,sex,address,math,english FROM student WHERE english IS NOT NULL;


-- 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT id,NAME,age,sex,address,math,english FROM student WHERE age > 35 AND sex = '男';


-- 查询age大于35或性别为女的学生(两个条件其中一个满足)
SELECT id,NAME,age,sex,address,math,english FROM student WHERE age > 35 OR sex = '女';


-- 查询id是1或3或5的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE id =1 OR id = 3 OR id=5';

-- in: 在...里面,只要是满足()里面的数据都可以
-- 查询id是1或3或5的学生 
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  id in(1,3,5);

-- 查询id不是1或3或5的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  id not in(1,3,5);


-- 范围: BETWEEN 值1 AND 值2 -- 表示从值1到值2范围，包头又包尾
-- 查询english成绩大于等于75，且小于等于90的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  english between 75 and 90;

-- 注意: between 值1 and 值2, 小的写前面大的写后面

-- 模糊查询like 
-- 查询姓马的学生: 第一个是马,后面无所谓
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  name like'马%';


-- 查询姓名中包含'德'字的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  name like '%德%';


-- 查询姓马，且姓名有三个字的学生
SELECT id,NAME,age,sex,address,math,english FROM student WHERE  name like'马__';


-- 排序
-- 单列排序
-- 查询所有数据,使用年龄升序排序
SELECT id,NAME,age,sex,address,math,english FROM student order by age asc;

-- 查询所有数据,使用年龄降序排序
SELECT id,NAME,age,sex,address,math,english FROM student  order by age desc;



-- 组合排序
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT id,NAME,age,sex,address,math,english FROM student  order by age desc,math desc;


-- 聚合函数
-- SELECT 聚合函数(字段) FROM 表名;
-- 查询学生总数
select count(*) from student;

-- 查询年龄大于40的总数
select count(*) from student where age > 40;

-- 查询数学成绩总分
select sum(math)  from student;

-- 查询数学成绩最低分
select min(math)  from student;


-- 查询数学成绩平均分
select avg(math)  from student;


-- 让小数显示指定的位数
select round(avg(math))  from student;


-- 查询英语成绩总分
select sum(english)  from student;


-- 查询英语成绩最高分
select max(english)  from student;


-- 查询英语成绩平均分
select avg(english)  from student;


-- 分组查询
-- 按性别分组
select sex from student group by sex

-- 查询男女各多少人
-- 1.按照性别分为男女两组
-- 2.统计每组的人数
select sex,count(*) from student group by sex;


-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex,count(*)from student where age >25 group by sex;

-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- 并只显示性别人数大于2的数据是分组后的条件，
-- 分组后的条件使用having
select sex,count(*)from student where age >25 group by sex having count(*) >2;

-- 查询男女每组的数学总分
select sum(math),sex from student group by sex;

-- 查询男女每组的数学平均分
select avg(math),sex from student group by sex;

-- 查询男女每组的数学最高
select max(math),sex from student group by sex;

-- 查询男女每组的数学低
select min(math),sex from student group by sex;

-- 查询男女每组的数学最高分和英语最高分
select max(math),max(english),sex from student group by sex;


-- 查询学生表中数据，跳过前面2条，显示6条
select * from student limit2,6;

-- 假设我们一每页显示5条记录的方式来分页，SQL语句如下：
-- 第一页: 跳过0条, 获取5条
select * from student limit 0,5;

-- 如果跳过的条数是0,可以省略
select * from student limit 5;

-- 第二页: 跳过5条, 获取5条
select * from student limit 5,5;


-- 第三页: 跳过10条, 获取5条
select * from student limit 10,5;