常用SQL语句--表中行操作

表中行操作

1、select--查询

1.1、查询某一列

select 列名 from 表名

select name from table_city

1.2、查询所有

select * from 表名

2、insert--插入

2.1、默认插入选项

insert into 表名 values (值1， 值2)

insert into table_city values (1, 'sun')

2.2、插入时设置指定列值

insert into 表名 (列1， 列2) values (值1， 值2)

insert into table_city (id， name) values (1， 'sun')

3、where -- 条件选取

select 列名 from 表名 where 条件列 运算符 值

select name from table_city where city='beijing'

4、update -- 更新表

update 表名 set 列1 = 新值, 列2 = 新值

update table_city set name = 'shanghai'

update table_city set name = 'shanghai' where id > 4

5、delete -- 删除表中行

delete from 表名 where 条件列名 = 值

delete from table_city from name = 'shanghai'

6、and -- 多条件满足过滤

select * from 表名 where 条件列1 = 值 and 条件列2 = 值

select * from table_city where name = 'beijing' and age = 22

7、 or -- 条件之一满足过滤

select * from 表名 where 条件列1 = 值 or 条件列2 = 值

select * from table_city where name = 'beijing' or age = 22

8、order by -- 表排序

select 列名 from 表名 order by 条件列1, 条件列2

asc -- 升序 -- 默认

select name from table_city order by age, name


desc -- 降序

select name from table_city order by age desc


name升序， age降序

select name from table_city order by age desc, name asc

9、group by -- 结果集分组，结合合计函数 sum()

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

select Customer,sum(OrderPrice) from Orders group by Customer

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

将相同的Customer 列，通过合计函数计算总和，去除重复项，返回新的结果集，忽略group by 如下情况：

Customer	SUM(OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700