数据库笔记6：检索，排序检索，过滤数据

insert into products(prod_id,prod_name,pro_price)values('avno1','.5 ton anvil',5.99);
insert into products(prod_id,prod_name,pro_price)values('avno2','1 ton anvil',9.99);
insert into products(prod_id,prod_name,pro_price)values('avno3','2 ton anvil',14.99);
insert into products(prod_id,prod_name,pro_price)values('ol1','oil can',8.99);
insert into products(prod_id,prod_name,pro_price)values('fu1','fuses',3.42);
insert into products(prod_id,prod_name,pro_price)values('slite','sling',4.49);
insert into products(prod_id,prod_name,pro_price)values('tnt1','tnt (1 stick)',2.50);
insert into products(prod_id,prod_name,pro_price)values('tnt2','tnt (5 stick)',10.00);
insert into products(prod_id,prod_name,pro_price)values('fb','bird seed',10.00);
insert into products(prod_id,prod_name,pro_price)values('fc','carrots',2.50);
insert into products(prod_id,prod_name,pro_price)values('safe','safe',50.00);
insert into products(prod_id,prod_name,pro_price)values('dtntr','detonator',13.00);
insert into products(prod_id,prod_name,pro_price)values('jp1000','jetpack 1000',35.00);
insert into products(prod_id,prod_name,pro_price)values('jp2000','jetpack 2000',55.00);




-- 创建products表
create table products
(
 prod_id char(40),
 prod_name char(40) not null,
 pro_price int not null,
 primary key(prod_id)
)engine = innodb;

-- 检索单个列
select prod_name from products;
-- 检索多个列
select prod_id,prod_name,prod_price from products;
-- 检索所有列
select * from products;
-- 返回所有指定的行
select ven_id from ven;
-- 返回行中不同的值
select     distinct vend_id from ven;
-- 返回开始到第五行
select prod_name from products limit 5;
-- 返回从第五行开始的五行
select prod_name from products limit 5,5;
-- 从第3行开始取4行
select prod_name from products limit 4 offset 3;
-- 假设products表在ven表里面
select products.prod_name from ven.products;
-- 升序
select prod_name,pro_price,prod_id from products order by prod_name,pro_price;
-- 降序
select prod_name,pro_price,prod_id from products order by pro_price desc;
-- desc之前的降序之后的升序
select prod_name,pro_price,prod_id from products order by pro_price desc,prod_name;
-- 检索降序并输出一行
select pro_price,prod_id from products order by pro_price desc limit 1;
-- 检索两个列并判断prod_price为3的行
select prod_name from products where prod_price = 3;
-- 检索两个列并判断prod_name为fuses的行,不区分字母大小写
select prod_name,pro_price from products where prod_name ='Fuses';
-- 检索两个列并判断prod_price小于10的行
select prod_name,pro_price from products where pro_price < 10;
-- 检索两个列并判断prod_price小于等于10的行
select prod_name,pro_price from products where pro_price <= 10;
-- 检索三列并prod_id判断不等于jp1000的行
select  prod_id,prod_name,pro_price from products where prod_id <> 'jp1000';
-- 与上面相同
select  prod_id,prod_name,pro_price from products where prod_id != 'jp1000';
-- 检索pro_price 5到10之间的所有产品
select  prod_name,pro_price from products where pro_price between 5 and 10;
-- 检索pro_price为空的所有产品
select  prod_id,prod_name,pro_price from products where pro_price is null;
-- 创建customers表
create table customers
(
 cust_id int,
 cust_email char(50) null,
 primary key(cust_id)
)engine = innodb;

-- 向customers表cust_id列插入数据
insert into customers(cust_id)values(1002);
-- 向customers表cust_id列插入数据
insert into customers(cust_id)values(1003);
-- 检索cust_email为空的所有产品
select  cust_id from customers where cust_email is null;

create table ven
(
 ven_id int auto_increment,
 primary key(ven_id)
)engine = innodb;
-- 添加vend_id列
alter table ven add vend_id int;
insert into ven(vend_id)values(1001);
insert into ven(vend_id)values(1001);
insert into ven(vend_id)values(1001);
insert into ven(vend_id)values(1002);
insert into ven(vend_id)values(1002);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1003);
insert into ven(vend_id)values(1005);
insert into ven(vend_id)values(1005);

深入理解：

select * from student;

 from student t;

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

select

 t.sid,

 t.sname,

-- 设置别名

 from student t;

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

select 

 t.sid,

 t.sname,

-- 常量列

 5,

from student t;

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

select 

-- sid列数据加一，并创建一个t.sid+1列的数据

 t.sid +1,

 t.sname,

 t*

 from student t;

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

select

-- 两个整型数据相加，得出t.score+t.ccid列名的相加数据

 t.score+t.ccid

 from student t;

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

select

-- 'ni'列名

 t.score 'ni'，

-- 'why'列名

 t.ccid 'why',

-- 'you'列名

 t.score+t.ccid 'you'

 from student t;

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

select

-- 整型和字符类型数据相加，得出t.score+t.sname列名的t.score数据(字符类型和整型数据相加，结果也是一样，以整型为主)

 t.score+t.sname

 from student t;

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

select

-- 改为'我'列名

 t.score '我'

 from student t;

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

select

-- '加'列名

 t.score '加'，

-- ‘王’列名

 5 ‘王’

 from student t;

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

select

-- '加'列名

 t.score '加'，

 5 

 from student t

-- 常数列名不能相加（还是‘加’列名数据，5列名数据）

 where t.score +5;

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

select s.score+5 'q',s.score+s.ccid,5 '我' from student s;

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

select 

-- '加'列名

 t.ccid '加',

-- 常数列名

 5 ,

-- t.ccid+5列名相加的数据
 t.ccid + 5
from student t;

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

-- 

select * from student t where t.score>70 order by t.score desc limit 5;
------------------------------------------
select s.*,1 'a',2 'b',3 'c' from student s  
 where s.score+1=s.sid+87 and s.score between 60 and 95
  order by sid desc limit 1;
------------------------------------------

select * from student t
 where t.score not in (60,90,85);

------------------------------------------
select * from student 
 where sname is not null;

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

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

create table student

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

insert into student(sname,score,ccid) values('wpq2',93,1);