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;