数据库查询笔记

创建数据库

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;