数据库单表查找

#注释,单表查询select

use test;

创建fruits表：

CREATE TABLE FRUITS

(

f_id   char(10)      NOT NULL,

s_id     INT            NOT NULL,

f_name  char(255)  NOT NULL,

f_price   decimal(8,2)   NOT NULL,

PRIMARY  KEY(f_id)

);

插入数据：

INSERT INTO fruits (f_id, s_id, f_name, f_price)
     VALUES('a1', 101,'apple',5.2),
     ('b1',101,'blackberry', 10.2),
     ('bs1',102,'orange', 11.2),
     ('bs2',105,'melon',8.2),
     ('t1',102,'banana', 10.3),
     ('t2',102,'grape', 5.3),
     ('o2',103,'coconut', 9.2),
     ('c0',101,'cherry', 3.2),
     ('a2',103, 'apricot',2.2),
     ('l2',104,'lemon', 6.4),
     ('b2',104,'berry', 7.6),
     ('m1',106,'mango', 15.6),
     ('m2',105,'xbabay', 2.6),
     ('t4',107,'xbababa', 3.6),
     ('m3',105,'xxtt', 11.6),
     ('b5',107,'xxxx', 3.6);
         
#显示fruits表的所有数据,按Tab键补全
#SELECT 字段,字段... FROM 表名;
#查询所有数据,*表示所有字段

#执行当前语句:Shift+Ctrl+R
SELECT * from fruits;

#查询f_name
select f_name from fruits;

#查询f_name,f_price
SELECT f_name,f_price from fruits;

#查询价格为10.2的水果名称和价格
SELECT f_name,f_price from fruits WHERE f_price=10.2;

#查询价格低于5的所有水果信息
SELECT * FROM fruits WHERE f_price<5;

#查询apple的信息
SELECT * from fruits WHERE f_name = 'apple';

#查询名称为mango的水果信息
SELECT * from fruits WHERE f_name = 'mango';

#查询价格不等于15.6的水果名称和价格
#SELECT f_name,f_price FROM fruits where f_price!=15.6;
SELECT f_name,f_price FROM fruits where f_price <>15.6;

#查询名字为'apple'或者'blackberry',或者'berry'的水果信息  (||,or表示 或)
#SELECT * from fruits WHERE f_name='apple' || f_name='blackberry' || f_name='berry';
#SELECT * from fruits WHERE f_name='apple' or f_name='blackberry' or f_name='berry';
SELECT * from fruits WHERE f_name in('apple','blackberry','berry');

#ORDER BY 排序,ASC(升序,默认),DESC(降序)
#查询价格大于等于5的水果名称和价格,并且按价格升序(默认)
SELECT f_name,f_price from fruits WHERE f_price>=5 ORDER BY f_price DESC;

#查询水果的所有信息,并按名字降序
SELECT * FROM fruits ORDER BY f_name DESC;

#查询s_id不等于101,也不等于102的水果信息 (且用&&或者and表示)
#SELECT * from fruits WHERE s_id!=101 && s_id!=102 ORDER BY s_id;
SELECT * from fruits WHERE s_id!=101 and s_id<>102 ORDER BY s_id;
SELECT * from fruits WHERE s_id NOT in(101,102) ORDER BY s_id;

#IN:在列举的值中
#NOT IN :不在列举的值中

#BETWEEN x AND y :在x到y的范围内
#价格在2到10.2范围内的水果名和价格
SELECT f_name,f_price FROM fruits WHERE f_price>=2 AND f_price<=10.2;
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2 AND 10.2;

#价格在11到3之间的水果名和价格
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 11 AND 3;#f_price>=11 and f_price<=3
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 3 AND 11;
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 3 AND 11 ORDER BY f_price DESC;

#查询价格在2到10.2之外的水果名称和价格
SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2 AND 10.2;

#注意,IN和 BETWEEN AND的区别