select语句

4.1.1 select语句
一、select 查询语句

1.select [all | distinct] * | 列名1[,列名2,.........,列名n]
from 表名 
[where 条件表达式]
[group by 列名 [asc | desc ] [having 条件表达式]]
[order by 列名 [asc | desc],.....]
[limit  [offset] 记录数];

4.1.2 查询列
一、查询所有列

1.use onlinedb；select * from goodstype;

二、查询指定的列

1.select gdCode,gdName,gdPrice,gdSaleQty from Goods;

三、计算列值

1.select gdName,gdSaleQty*gdPrice from Goods;
2.select uName, year(now())-year(ubirth) from users;

四、为查询结果中的列指定列标题

1.select gdName as 商品名,gdPrice as 价格,gdCity as 城市 from Goods;
2.select gdName,gdSaleQty*gdPrice from goods;

4.1.3选择行
一、使用比较运算符

1.where 表达式1 比较运算符 表达式2
eg: select uID,uName from Users where uID = 8;
eg:select uID,uName,uPhone from Users where year(uBirth)>= 2000;

二、使用逻辑运算符

1.where [not] 表达式1 逻辑运算符 表达式2
eg:select uID,uName,uPhone from Users where year(uBirth) >= 2000 and uSex'男';
eg:select tID,gdname,gdprice from goods where tid=4 or gdprice<=50;
eg:select gdName,gdPrice from goods where not(gdPrice>50);
eg:select gdName,gdPrice,gdCity from goods where gdCity='长沙' or gdCity ='西安' and gdPrice<=50; 

三、使用between and 运算符

1.where 表达式 [not] between 初始值 and 终止值
eg:select gdname,gdprice from goods where gdprice between 100and 500;

四、使用in运算符

1.where 表达式 [not] in (值1,值2,.........)
eg:select gdname,gdcity from  goods where gdcity in ('长沙','西安','上海');

五、使用like运算符

1.where 列名 [not] like '字符串' [escape '转义字符']
eg:select uname,usex,uphone from users where uname like '李%';
eg:select uname,usex,uphone from users where uname like '_湘%';
eg:select gdname,gdprice,gdcode from goods where gdname '华为P9\_%';
eg:select gdname,gdprice,gdcode from goods where gdname '华为P9|_%' escape '|';

4.1.5 使用limit限制结果集返回的行数

1.limit [offset,] 记录数
eg:select gdcode,gdname,gdprice from goods limit 3;
eg:select gdcode,gdname,gdprice from goods limit 3,3;

4.1.6数据分组统计
一、使用聚合函数

1.sum/avg/max/min ( [all | distinct ] 列名 | 常量 | 表达式)
eg:select sum(gdsaleqty) from goods;
eg:select max(gdprice) from goods
2.count ( { [ [all | distinct] 列名 | 常量 | 表达式] | * } )
eg: select count(*) from users;
eg: select count(distinct uid) from orders;

二、group by 子句

1.group by [ all ] 列名1, 列名2, [ ,....n] [ with rollup] [having 条件表达式]
eg:select uid,uname,usex,ucity from users group by ucity;
eg:select ucity, count (*) from users group by ucity;
2.group_count([distinct] 表达式 [order by 列名] [sparator 分隔符])
eg:select ucity,group_concat(uid) as uids from users group by ucity;
eg:select ucity,group_concat(uid order by uid separator '_') as uids from users group by ucity;
eg:select ucity,count(*) from users where ucity in ('长沙','上海') group by ucity with rollup;
eg:select ucity ,count(*) from users group by ucity having count (*)>=3;

4.2.1连接查询

1.select [ALL | DISTINCT ] * | 列名1[,列名2,....,列名n] from 表1 [别名1] JOIN 表2 [别名2] [on 表1.关系列 = 表2.关系列 | using(列名)][where 表达式]

4.2.2内连接

1.select tname,gdcode,gdname,gdprice from goodstype JOIN goods on goodstype.tid = goods.tid where tname = '服饰';