通用 sql | 数据库 命令

基础教程：

选择 | 条件 | 排序 | 插入 | 更新 | 删除 |

select| where| order by| insert into| update| delete |

---

select 选择:

select *(column_name1,column_name2…) from table_name;

select distinct 唯一不重复。

---

where 条件语句:
select * from table_name where column_name=’’;
select * from table_name where column_name=’’ like…in…between…regexp…;

and 满足两个条件 select * from table_name where xx and xx ;

or 满足其中一个条件;

---

order by 升序:

select * from table_name order by column_name;

order by column_name desc(descending) 降序。

insert into 插入:
insert into table_name values(’’,’’,’’,);

insert into table_name (column_name1,column_name2…)
values (value1,value2,value3…);

update 更新:

update table_name set column_name1=’’,column_name2=’’…
where column_name=’’;

delete 删除行 delete from table_name where column_name=’’;

---

高级教程：

select top | like | %/- | in | between| as |

数目    | 模糊搜索 | 通配符 |  包含可能值|  范围  |  别名  |

---

---

join | union | select into | create database | create table |

连接 | 连接 | 复制1表到2 | 创建数据库 | 创建表 |

---

| constraint | create index | drop | alte table | auto_increment |

| 约束 | 创建索引 | 删除行 | 添加删除或修改列 | 自增 |

---

create view | datetime| null | ifnull |

视图       |   日期	 | 保守值 | null函数 |

---

---

select top 数目：
SQL Server / MS Access : select top number|percent column_name from table_name;
MySQL : select top column_name from table_name limit ;
Oracle : select top column_name from table_name where rownum <= number;

like 模糊搜索:
select * from table_name where column_name like ‘’;

通配符：54
%：匹配一个或多个字符 (like)
-：匹配一个字符 (like)
¹：匹配从a、b或c开头的列 (regexp)
^[abc]：匹配不是从a、b或c开头的列 (regexp)

in:列值包含多个可能
select * from table_name where column_name in (‘a’,‘b’);

between:在什么到什么之间,小前大后
select * from table_name where column_name between a and b

as：别名
select column_name1 as a,column_name2 as b…
from table_name ;

select name
from table_name as a
where a.name=’’;

join:
left join… on ; inner join …on :要求左边至少有一条记录
right join …on 要求右边至少有一条记录
full join …on （mysql不支持） 两个表至少有一条记录
select * from table_one as one
left join table_two as two on one.id=two.id;

union： 连接
select column_name(s) from table_name
union
select column_name(s) from table_name
连接两个数据库的记录

select into:复制一个表的数据到另一个表
select * into newtable_name from table_name;
select column_name(s) into newtable_name from table_name;
create table newtable_name like 旧表
create table table_name
as
select * from 旧表

create database table_name:创建数据库

create table table_name ：创建表
create table table_name (
column_name datatype(size),
…
);

constraint ：约束
not null : 不允许空值
unique ： 不允许重复
primery key ： 主键
foreign key ： table orders / persons
create table 时：
mysql ：foreign key p_id references person(p_id);
sql server / oracle / MS Access :foreign key references penson(p_id);
如需创建多列：
constraint fk_oderspersons foreign key p_id references person(p_id);
如果已创建：
alter table table_name add foreign key p_id references persons(p_id);
alter table table_name add constraint fk_oderspersons foreign key p_id references persons(p_id);
如需删除：
mysql :alter table table_name drop foreign key p_id;
sql server / oracle / MS Access :alter table table_name drop constraint fk_oderspersons;
check ：限定值的范围 例如：（p_id>0);
default: 设置默认值;

create index:创建索引
create unique index index_name on table_name (column_name);

create unique index index_name on table_name (lastname,firstname);

---

drop : 删除
删除索引：
MS Access：drop index index_name on table_name;
sql server:drop index table_name.index_name;
oracle:drop index index_name;
mysql:alter table table_name drop index index_name;
删除数据库：
drop database database_name;
删除表：
drop table table_name;
删除内容：
truncate table table_name;

alter table: 在已有的表中添加、删除、修改列。
添加列：
alter table table_name add column_name datatype;
删除列：
alter table table_name drop column column_name；
更改列属性：
sql server / MS Access :
alter table table_name alter column column_name datatype;
mysql / oracle :
alter table table_name modify column column_name datatype; (oracle 10G 后版本 去掉column）

auto_increment: 自增
mysql : auto_increment;
sql server: identity(1,1); 1开始值，后1每次曾加多少
Access : autoincrement(1,1);
oracle : create sequence seq_persons minvalue 1 start with 1 increment by 1 cache 10;
insert into table table_name (id ,name ,age) values(seq_persons.nextval,‘name’,‘阿哥’);

create view：视图
创建视图：create view view_name as select column_name(s) from table_name where discontinued=no ;
删除视图：drop view view_name;

date : 日期
date : yyyy-mm-dd
table_name_date datetime not null default curdate();

null：保守值
where is null 选取null值
where is not null 选取非null值

null 函数：
isnull() / ifnull() / nvl() / ()localesce()
column_name*(column_name+ifnull(column_name,0))
sql server / MS Access :isnull()
oracle : nvl()
mysql : ifnull() / localesce()

通用数据类型：
字符/字符串：varchar (char不可变)
整数：
设置精度：integer§
精度5：smallint
精度10：Interger
精度19：bigint
numeric(p,s) / decimal(p,s)：设置数字精度p代表整数s代码小数
单精度16：float / double precision
单精度7：real
设置尾数精度:float§
年月日：date
时分秒：time
年月日 时分秒： datestamp
xml：存储xml数据
interval：固定整数时间
元素：
multiset()：无序集合
array：有序集合

sql函数：
aggregAte：
avg：平均数
count：行
max：最大值
min：最小数
first：第一个
last：最后一个
sum：总和
scalar：
ucase ：将某个字段转换为大写
lcase ：小写
len：字段长度
round：四舍五入
format：格式化日期
now ：返回当前的日期和时间
subString(字段,1,end) ：从文本字段提取字符 (mysql用mid)
group by :…
having :…
avg:
select avg(count) from table_name;
select count from table_name where count > (select avg(count) from table_name);
count:
select count(column_name) from table_name;
select count(distinct column_name) from table_name;(不重复)(不适用于MS Access);
first ：
Ms Access : select first(column_name) from table_name;
mysql : select * / column_name(s) from table_name limit 1
sql server : select top mumber / percent column_name from table_name ;
oracle : select * / column_name(s) from table_name where rownum <= number;
last : MS Access : select last(column_name) from table_name;n
mysql : select * from table_name order by id desc limit 1;
sql server : select top 1 * from table_name order by id desc;
oracle : select * from table_name order by id desc where rownum <= 1 ;
max：select max(column_name) from table_name;
min: select min(column_name) from table_name;
sum: select sum(column_name) from table_name;
group by : …
having : …
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name 以它为一组分配列
HAVING SUM(access_log.count) > 200;
ucase：转换大写
lcase：转换小写 (sql server : upper / lower)
mid: mysql : select mid(name,1,4) from table_name;
substr : select substr(name,1,4) from table_name;
len : celect len(column_name) from table_name; (mysql length)
round : select round(column_name,number) from table_name;
now : select now() ;
format : select format(date,’%y-%m-%d’) from table_name; (mysql date_format)

						---------------------mysql----------------

mysql导出：
select * from table_name
into outfile ‘path/文件名’ (后面内容可选)fields terminated by ‘’ enclosed by ‘’ ;

---

mysql导入：
load data local infile ‘path/文件名’ into table table_name;

• List item

---

---

1. abc ↩︎