常用SQL

1.distinct(只能单列),去除重复的值
select distinct column_name from table_name;

2.order by, asc升序，desc降序
selectt * from table_name order by column_name asc|desc;(可以选择多列)

3.insert into
① insert into table_name values(value1,value2…);
② oracle: insert into table1 select * from table2;(table1已经存在)

4.update
update table_name set column1=value1, column2=value2… where some_column=some_value;

5.delete
delete from table_name where some_column=some_value;

6.top
SQL Server: select top number * from table_name;
Oracle: select * from table_name where rownum <= 5;
select rownum column_name from table_name;
Mysql: select * from table order by column_name asc limit 5;

7.like
select * from table_name where column_name like pattern;

8.通配符
%: 替代0个或多个字符
_: 替代一个字符
[]: 字符串中的任何单一字符
[^]、[!]: 不在字符串中的任何单一字符

9.in
select * from table_name where column_name in(values1,values2…);

10.between
select * from table_name where column_name between value1 and value2 and …;
select * from table_name where column_name not between value1 and value2…

11.alias(可以为表名称或列名称指定别名)
列: select column_name as alias_name from table_name;
表: select * from table_name as alias_name;

11.join(把来自两张或多张表的行结合起来，基于这些表之间的共同字段)
select table1., table2. from table2 join table1 on table1.=table2.;

12.inner join(关键字在表中存在至少一张匹配时返回行)
select * from table1 inner join table2 on table1.=table2.;

13.left join(LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL)
select * from table1 left join table2 on table1.=table2.;

14.right join(RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL)
select * from table1 right join table2 on table1.=table2.;

15.full join(FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一张表中存在匹配，则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。)
select * from table1 full join table2 on table1.=table2.；

16.union(UNION 操作符用于合并两张或多张 SELECT 语句的结果集。
请注意，UNION 内部的每张 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每张 SELECT 语句中的列的顺序必须相同。)
select * from table1 union all select * from table2;

17.into(从一张表复制信息到另一张表。SELECT INTO 语句从一张表复制数据，然后把数据插入到另一张新表中)
SQL Server: select * into new_table from table1;
Oracle: create table new_table as select * from table1;

18.insert into(从一张表复制信息到另一张表。INSERT INTO SELECT 语句从一张表复制数据，然后把数据插入到一张已存在的表中。,INSERT INTO SELECT 语句从一张表复制数据，然后把数据插入到一张已存在的表中。目标表中任何已存在的行都不会受影响)
insert into table2 select * from table1;
insert into table2(*) select * from table1;

19.database(创建数据库）
create database database_name;

20.alter table(oracle)
增加列: alter table table_name add(column property);
删除列: alter table table_name drop column column_name;
改表名: alter table table_name rename to new_table_name;
改列名: alter table table_name rename column column_name to new_column_name;
改列数据类型: alter table table_name modify(column_name datatype);

(sql server)
增加列: alter table table_name add column attribute;
删除列: alter table table_name drop column column_name;
改表名: exec sp_rename ‘旧表名’, ‘新表名’;
改列名: exec sp_rename ‘表名.就列名’, ‘新列名’;
改列数据类型: alter table 表名 alter column 列名 新属性;

21.视图View，基于SQL语句的结果集的可视化表
创建视图：create view view_name as select * from table (where condition);
删除视图：drop view view_name;

22.查看系统日期：
oracle: select sysdate from dual;
Mysql: select sysdate() from dual;

23.自定义系统日期显示方式
alter session set nls_date_format=‘yyyy-mm-dd’;

24.Avg函数
select avg(column_name) from table;
select some_column, avg(column_name) from table group by some_column;

25.Count函数(返回数目)
select count(column_name) from table;
select some_column, count(column_name) from table group by some_column;

26.Max函数
select max(column_name) from table;
select some_column, max(column_name) from table group by some_column;

27.Min函数
select min(column_name) from table;
select some_column, min(column_name) from table group by some_column;

28.Sum函数
select sum(column_name) from table;
select some_column, sum(column_name) from table group by some_column;

29.Upper函数(把字符变为大写)
select upper(column_name), … from table;

30.Lower函数(把字符变为小写)
select lower(column_name), … from table;

31.to_char

32.to_date

33.to_number

34.查看用户的权限
select * from user_role_privs;

35.查看用户的系统权限
select * from dba_role_privs;

36.查看表结构
desc table_name;
describe table_name;

37.修改权限
alter user scott account lock/unlock;

38.修改密码
alter user scott identified by hadis;

39.查看当前用户
show user;

40.创建用户
create user *** identified by ???;

41.删除一张表的全部数据
delete from table_name;