select
select * form table_name;
select distinct
根据字段查询不重复的列值
select distinct field_name form table_name;
where
select * form table_name where field_name = '';
and&or
and 为并
select * form table_name where field_name1 = '' and field_name2 = '';
or 为或
select * form table_name where field_name1 = '' or field_name2 = '';
and or 结合
select * form table_name where field_name1 = '' and (field_name2 = '' or field_name3 = '') ;
order by
根据关键字进行排序
asc 升序 从小到大
desc 降序 从大到小
select * form table_name order_by field_name;
order by 多列
select * form table_name order_by field_name,field_name,1;
insert into
可以不指定列名
insert into table_name values ('','','');
指定列名
insert into (field_name,field_name,field_name) table_name values ('','','');
update
update table_name set field_name='',field_name='' where field_name2='';
delete
delete form table_name where field_name='';
select top
取前百分之五十的数据
select top 50 percent * form table_name
select like
slelct * form table_name where field_name like 'a%';
通配符
字符 | 含义 |
---|---|
% | 替代 0 个或多个字符 |
_ | 替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist]或[!charlist] | 不在字符列中的任何单一字符 |
in
select * form table_name where field_name in ('','');
between
介于两个值之间
select * form table_name where field_name between '' and '';
select * form table_name where (field_name1 between '','') and (field_name2 in '','');
sql 别名
列的别名
select field_name1 as f1 ,field_name2 as f2 from table_name;
把多个列结合到一起并命名 concat
select concat(field_name1,field_name2,field_name3) as field_name4 from table_name;
表别名
select t.field_name1 from table_name as t where t.field_name2 = '';
join、inner join
两张表的交集
select * from table_name1 join(inner join) table_name2 on table_name1.field_name = table_name2.field_name;
left join
返回左表的全部,即使右表不存在,也返回null
select * from table_name1 left join table_name2 on table_name1.field_name = table_name2.field_name;
right join
返回右表的全部,
select * from table_name1 right join table_name2 on table_name1.field_name = table_name2.field_name;
full outer join
返回两张表,即使某些数据在另一张表里没有,也返回null。MYSQL不支持。
select * from table_name1 full outer join table_name2 on table_name1.field_name = table_name2.field_name;
union
合并两个select的结果集,默认返回不同的值
select field_name from table_name1 union select field_name from table_name2;
union all 返回所有结果集,允许重复值
select field_name from table_name1 union all select field_name from table_name2;
select into
查询出一张表的数据,复制到另外一张新表,所以执行的时候会创建一张新表 mysql不支持
select * into table_name from table_name1;
insert into select
复制一张表的数据到一张已存的表中
insert into table_name select * from table_name1;
create database
创建数据库
create database my_db
create table
创建数据库表
create table table_name(
field_name1 int,
field_name2 char(255),
);
not null 约束
约束,不为空
create table table_name(
field_name1 int not null,
field_name2 char(255) not null,
);
删除not null 约束
alter table teble_name modify field_name int null
unique
唯一性约束,保证列和列集的唯一性
mysql写法
create table table_name(
field_name1 int not null,
field_name2 char(255) not null,
unique(field_name1),
);
SQL Server / Oracle / MS Access写法
create table table_name(
field_name1 int not null unique,
field_name2 char(255) not null,
);
avg()
求列的平均值
select avg(field_name) from table_name;
count
count(field_name)返回指定列的数目
select count(field_name) from table_name;
count(*)返回整表的数目
select count(*) from table_name;
count( distinct field_name) 返回指定列 不同值得数目
select count(distinct field_name) from table_name;
first()
返回指定列中第一个记录的值
只有 MS Access 支持 FIRST() 函数。
last()
返回指定列中最后一个记录的值
只有 MS Access 支持 FIRST() 函数。
max()
返回指定列中最大值
select max(field_name) form table_name;
min()
返回指定列的最小值
select min(field_name) form table_name;
sum
返回指定列的和
select sum(field_name) form table_name;
group by()
根据一列或者多列进行分组
select field_name form table_name group by(field_name2);
having()
where无法和聚合函数一起使用,所以增加having
where后不能跟sum所以有了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;
exists()
EXISTS 运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False。
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
ucase()
把字段值转为大写
select ucase(field_name) from table_name;
lcase()
把字段值转为小写
select lcase(field_name) from table_name;
mid()
截取指定字段的值从某位置开始,指定位数
select mid(field_name,start,len) from table_name;
field_name | 字段名 |
---|---|
start | 开始位置 |
len | 指定长度 |
len()
返回字段值得长度
select len(field_name) from table_name;
round()
返回指定字段值,规定几位小数
select round(field_name,num) from table_name;
field_name | 字段名 |
---|---|
num | 小数位数 |
now()
返回系统当前时间
select field_name,now() as date from table_name;
format()
对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name;
column_name | 必需。要格式化的字段。 |
---|---|
format | 必需。规定格式。 |