--数据转换
select ids,code,name,brand,
(cast(year(time)as varchar(10))+'年'
+cast(month(time)as varchar(10))+'月'
+cast(day(time)as varchar(10))+'日')as 时间,oil,POWERS,EXHAUST,price ,pic from car
select * from car where ceiling(oil)>=8.0
select * from car where ceiling(oil)>8.0
--子查询
--把查询语句查询出的结果当做一个数值使用或一组数值使用
--all所有的,any任意一个,修饰符
--in('','')在数值范围内not in()不在括号的数值范围内
select price from car where name ='宝马3系 325i 时尚型'
select *from car where name like '%奥迪%'and price>=42.38
select *from car where name like '%奥迪%'and price>=(select price from car where name ='宝马3系 325i 时尚型')
select *from car where name like '%奥迪%'and price>(select max(price)from car where name like'%宝马%')
select *from car where name like '%奥迪%'and price>all(select price from car where name like '%宝马%' )
select *from car where name like '%奥迪%'and price>any(select price from car where name like '%宝马%' )
--in('','')在数值范围内not in()不在括号的数值范围内 == or的用法
select * from car where brand ='b001'or brand ='b002'or brand ='b003'
select * from car where brand in('b001','b002','b003')
select * from car where brand not in('b001','b002','b003')
--表连接 (inner)join on内连接
select* from car
select*from brand
select code,name,brand,brand_code from car
join brand on car.brand =brand .brand_code
select code,name,brand,brand_code from car
inner join brand on car.brand =brand .brand_code
select code,name,brand,brand_code from car
left join brand on car.brand =brand .brand_code
select code,name,brand,brand_code from car
right join brand on car.brand =brand .brand_code
--子查询
select code,name,(select brand_name from brand where car.brand =brand .brand_code )as brand_name from car
--表的拼接 用,笛卡尔积,类似for的穷举
select*from car,brand
select*from car,brand where car.brand =brand .brand_code