MySQL 小测试

╰半夏微凉° 2024-03-23 20:58 68阅读 0赞

**目录**

基础查询

高级查询

--------------------

####  **基础查询** ####

现有用户表user数据如下：

![130915a5bea14a2ca1c3d46192bc489d.png][]

1、写出ddl语句创建如上表，插入数据，查询所有数据

#创建表user
    create table user(
    id int UNSIGNED auto_increment key ,
    device_id int UNSIGNED,
    gender varchar(6),
    age TINYINT,
    university varchar(4),
    province varchar(10)
    )
    #插入数据
    insert into user(device_id,gender,age,university,province)
    values(2138,'male',21,'北京大学','Beijing'),
    (3214,'male',null,'复旦大学','Shanghai'),
    (6543,'female',20,'北京大学','Beijing'),
    (2315,'female',23,'浙江大学','Zhejiang'),
    (5432,'male',25,'山东大学','Shandong');
    #查询所有数据
    select * from user;

![e1c47794fb064bb8b57ea1e74aaa614f.png][]

2、查询设备id为2138对应的性别、年龄和学校的数据，请你取出相应数据

select u.gender,u.age,u.university from user u where u.device_id=2138;

![1cf5a285c82f4fbcb3492c13a7d6e7e7.png][]

3、查询用户来自于哪些学校，请从用户信息表中取出学校的去重数据

select DISTINCT(university) university from user;

![da9c725d5cd14c6399199c723286c34e.png][]

4、查询前两2条用户信息

select * from user order by id LIMIT 2;

![fae250fbdd1b45b5993e3bf6e3ab567e.png][]

5、查询用户年龄并按降序排序

select age from user order by age desc ;

![0d8f800a2bb34a2bba71e2c0a6a96f8f.png][]

6、查询所有北京大学的学生

select * from user where university like '北京大学';
    select * from user where university = '北京大学';

![dc04151792174433ad5df154b792d98c.png][]

7、 查询学校名字带‘京’的学生

select * from user where university like '%京%';

![85c9c5737fd04852b892ffc8a3cdbb65.png][]

8、查询24岁以上的用户，请你取出满足条件的设备ID、性别、年龄、学校

select device_id,gender,age,university from user  where age>=24;

![36361056c73543bf838ab40b025a5a86.png][]

9、查询除复旦大学以外的所有用户信息

select * from user where university != '复旦大学';
    select * from user where not university = '复旦大学';

![c8f4459e19ec430ea14039499f60a9c4.png][]

10、查询所有学校及学校的人数

select university,count(*) num from user GROUP BY university;

![06e2280732834b46a5dacd8ebacfc54a.png][]

11、查询年龄在20到23之间的用户信息

select * from user where age BETWEEN 20 and 23;
    select * from user where age >=20 and age<=23;
    select * from user where age in (20,21,22,23);

#### ![2f292fd86a274fddb6a36a5416ec071a.png][] 高级查询 ####

1、创建并合理设计表：至少满足如下要求

商品： 商品名称 、商品描述、商品价格、商品数量、商品类别等

商品类别： 类别名称 、多级分类，类别排序

商品购买表： 购买的商品、数量 、价格 等

#创建商品类别
    create table category(
    id int UNSIGNED auto_increment key ,
    name varchar(20),
    pid int UNSIGNED DEFAULT 0,
    sorts TINYINT UNSIGNED
    );
    #创建商品表
    create table goods(
    id int UNSIGNED auto_increment key ,
    name varchar(20),
    content text,
    price decimal(10,2),
    stock MEDIUMINT DEFAULT 0,
    cid int unsigned,
    categoryPath varchar(200),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP on update CURRENT_TIMESTAMP
    );
    #商品购买表
    create table goods_sale(
    id int UNSIGNED auto_increment key ,
    gid int UNSIGNED,
    name varchar(20),
    num MEDIUMINT DEFAULT 0,
    price decimal(10,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

![7a17432744294a9e846cbfca051eb3b1.png][]

2、写一个存储过程，实现商品类别数据插入至少4条

create PROCEDURE p_initcategory()
    begin
    insert into category(id,name,pid,sorts)
    values(1,'电器',0,1),(2,'电脑',0,2),(null,'电视',1,1),(null,'空调',1,2),
    (null,'笔记本',2,1);
    end;
    #调用
    call p_initcategory();

![4780232859804926b523f1532c6cd092.png][]

3、写一个存储过程，插入商品数据n条

create PROCEDURE p_initgoods()
    begin
    insert into goods(name,content,price,stock,cid,categoryPath)
    values('小米电视','',2999,100,'3','1/3/'),('格力空调','',6999,50,'4','1/4/'),('创维电
    视','',2100,10,'3','1/3/');
    end;
    #调用
    call p_initgoods();

![87e200286fd54580b9ca3a9241e1ea7c.png][]

4、关联查询显示商品名称、价格、数量、类别

select g.`name`,g.price,g.stock,c.`name` categoryname from goods g JOIN
    category c on g.cid=c.id;

![0dc9af58eebe4a01bf2e67a3b9b3fea3.png][]

5、根据类别查询出对应商品数据

select * from goods where INSTR(categoryPath,'3/');

![f343d09fb8e44e05b0008fa56f740dde.png][]

6、写一个触发器添加商品购买记录时，自动更新商品数量

create trigger t_update_goods_stock
    after insert
    on goods_sale for each row
    begin
    update goods set stock=stock-new.num where id=new.gid;
    end;
    #插入购买数据
    insert into goods_sale(gid,name,num,price) values(1,'小米电视',2,2999);
    select * from goods_sale;
    select * from goods;

![8fce30b77d5f49639960eb07554eff12.png][]

7、写一个函数，根据类别id返回类别名称

create FUNCTION f_queryCategoryName(_id int UNSIGNED)
    returns varchar(20)
    begin
    DECLARE _name varchar(20);
    select name into _name from category where id=_id;
    return _name;
    end;
    select f_queryCategoryName(1);

![02b4b44e301847bf99985cee53b94856.png][]

8、统计商品销售情况，显示商品名称、销售数量,取排名前三

select s.`name`,sum(s.num) num from goods_sale s GROUP BY name ORDER BY
    num desc limit 3;

![6f4cd9fdd52041b1ae75d0dde06f6953.png][]

9、查询当天的购买记录

select * from goods_sale where DATE_FORMAT(create_time,'%Y-%m-%d')=CURDATE();

![6702c8d2c5484ba1a7d8a9b0bd185619.png][]10、查询最近3天的购买记录

select * from goods_sale where DATE_FORMAT(create_time,'%Y-%m-
    %d')>=DATE_FORMAT(DATE_SUB(now(),interval 3 day),'%Y-%m-%d');

![975ea64507b34621bd39dc6f7876a431.png][]

[130915a5bea14a2ca1c3d46192bc489d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/84f181d91d9c43e19588917b0d1531d5.png
[e1c47794fb064bb8b57ea1e74aaa614f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/6016dc7a974a4a04b34a9a3a36062b05.png
[1cf5a285c82f4fbcb3492c13a7d6e7e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e35dbcc6b7144a69addc22861bc6f085.png
[da9c725d5cd14c6399199c723286c34e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/5d949cd9b78b413f9d7e170290248922.png
[fae250fbdd1b45b5993e3bf6e3ab567e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/746ee9dd08f94685b3f1b1e7c06591fe.png
[0d8f800a2bb34a2bba71e2c0a6a96f8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a19ecf15425a404ba27aa6ff311ae5b1.png
[dc04151792174433ad5df154b792d98c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/99c37bf200db44408ceff6e464a77405.png
[85c9c5737fd04852b892ffc8a3cdbb65.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e5b0e7ef82b3461db5beb421d89c584c.png
[36361056c73543bf838ab40b025a5a86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a9811131f7f14caa830725a752be49e5.png
[c8f4459e19ec430ea14039499f60a9c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/5c1a36f3dab74068840738c8c3732421.png
[06e2280732834b46a5dacd8ebacfc54a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/57ad6dcd74a64ff4aa3ca692c3620c3f.png
[2f292fd86a274fddb6a36a5416ec071a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e4cef16737564d8b938e7768903a4a84.png
[7a17432744294a9e846cbfca051eb3b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/0de007f303c3429a9e51b30396f005be.png
[4780232859804926b523f1532c6cd092.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/2bac5f0ea918462d936ebe22f4c225dd.png
[87e200286fd54580b9ca3a9241e1ea7c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/737cab401469409387de972872f93891.png
[0dc9af58eebe4a01bf2e67a3b9b3fea3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/8a9df5a0588f4956a559285c58ba5d65.png
[f343d09fb8e44e05b0008fa56f740dde.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/86ebca724d8344b3a6b2d3c404d85b46.png
[8fce30b77d5f49639960eb07554eff12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/711de6829b754b5098893813a36fa12a.png
[02b4b44e301847bf99985cee53b94856.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ee803bc08f2542b9993d047176fff56a.png
[6f4cd9fdd52041b1ae75d0dde06f6953.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/0587f869bf064836a733573643a0ca77.png
[6702c8d2c5484ba1a7d8a9b0bd185619.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e44e267f094441babc0740f2f5b774ab.png
[975ea64507b34621bd39dc6f7876a431.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/7084f3b47c2545ccaa6f3e025dd0b26d.png