数据库查询笔记

最新推荐文章于 2022-10-31 15:16:34 发布

lobo_seeworld

最新推荐文章于 2022-10-31 15:16:34 发布

阅读量323

点赞数

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/lobo_seeworld/article/details/78663750

sql数据库专栏收录该内容

3 篇文章

订阅专栏

本文通过创建学生和水果等表格，演示了SQL的基本操作，包括插入数据、删除表、更新自增ID等技巧。并介绍了如何使用in、not in、between...and...等关键字进行高级查询，同时展示了分组、排序等实用功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建数据库

CREATE TABLE students(
    s_id INT PRIMARY KEY COMMENT '编号',

    s_name VARCHAR(10)   COMMENT '名字' NOT NULL

)AUTO_INCREMENT=101;
CREATE TABLE fruits(
    f_id VARCHAR(4) PRIMARY KEY COMMENT '编号',
    s_id INT  COMMENT   '编号',
    f_name VARCHAR(4)   COMMENT '名字' NOT NULL,
    f_price FLOAT(2) COMMENT '价格' NOT NULL
);
DROP TABLE fruits;
DELETE FROM fruits;
INSERT INTO fruits(f_id,s_id,f_name,f_price)
 VALUES
('12',102,'lemon',6.4),
('a1',101,'apple',5.2),
('a2',103,'aprecot',2.2),
('a3',103,'cot',2.3),
('c3',105,'dasat',4.3),
('f3',105,'pacct',8.3),
('da3',107,'oadt',10.3),
('fd3',109,'iaggt',9.3),
('za3',107,'jadt',14),
('dca3',107,'cadsdaft',5.3),
('dav3',107,'gdaddfft',7.3),
('dax3',107,'hhasafdt',2.3);

CREATE TABLE suppliers(
    s_id INT(2) PRIMARY KEY COMMENT '编号' AUTO_INCREMENT,
    s_name VARCHAR(10)  COMMENT '名称',
    s_city VARCHAR(10)   COMMENT '城市' NOT NULL,
    s_zip INT(2)   COMMENT 'zip编号' NOT NULL,
    s_call INT(2)  COMMENT '电话' 


)AUTO_INCREMENT=101;

DELETE FROM suppliers;
DROP TABLE suppliers;
-- ALTER TABLE suppliers AUTO_INCREMENT=100;
-- ALTER TABLE suppliers AUTO_INCREMENT_INCREMENT=2;
-- DROP TABLE suppliers;
INSERT INTO suppliers(s_name,s_city,s_zip,s_call)
 VALUES
('FastFruic Inc','Tianjin',30000,48075),
('LT Supplies','Chongqin',40000,484345),
('LT Supplies','Shanghai',40000,484345),
('FastFruasdfc Inc','Tianjin',30000,48075),
('LT2 Supplies','Chongqin',40000,484345),
('LT3 Supplies','Shanghai',40000,484345),
('FastFruisadf Inc','Tianjin',30000,48075),
('LT fSupplies','Chongqin',40000,484345),
('Lg Supplies','Shanghai',40000,484345)

– 高级查询
– in的关键字查询
– 解释：in操作符用来查询满足指定范围内的条件的记录，使用in。
– 将所有括号括起来，检索条件之间用逗号分开，只要满足条件范围
– 内的一个值即可匹配。

select s_id,f_name,sum(f_price),COUNT(s_id) 
    from fruits
--      where s_id in(SELECT s_id FROM fruits WHERE s_id >101) 
        GROUP BY s_id;
        having count(s_id)>101;

– not in

select s_id,COUNT(s_id) 
    from fruits
        where s_id  in(SELECT s_id FROM fruits WHERE s_id >101) 
        GROUP BY s_id;   -- 默认有一个升序排序
        having count(s_id);

– BETWEEN …AND…

SELECT *
            FROM fruits 
            where f_price BETWEEN 2.1 and 4;

– 不在范围内 not ..BETWEEN…AND

    SELECT *
            FROM fruits 
            where f_price not BETWEEN 2.1 and 4;

– is NULL 查询空值

select
            FROM student
            where s_name is NULL;

– 带and查询, 更加精确

SELECT *
            FROM fruits
            where s_id=101 and f_price>2

– 不重复 DISTINCT

    SELECT DISTINCT *
            FROM fruits;

– 排序升序和降序默认值，什么不写是属于升序，这地方会有一个二级排序的语句

SELECT *
            from fruits
            order by s_id,f_price;

– 多字段排序，首先第一字段必须有相同的字段值，才会对第二字段进行排序

    SELECT *
            from fruits
            order by f_name,f_price;

– 升序排列 ASC 升序

SELECT *
            from fruits
            order by f_price asc;

SELECT *
            from fruits
            order by f_price desc;

– 多字段分组

    SELECT * from fruits GROUP BY s_id;