三、个人总结的菜鸟教程|sql教程的知识点（SQL函数）

1、SQL函数

SQL Aggregate（聚合函数）函数

    SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

    aggregate 函数有：

    avg() 求平均数

    count() 返回行数

    first() 返回第一条记录

    last() 返回最后一条记录

    max() 返回最大值

    min() 返回最小值

    sum() 返回总和

SQL Scalar函数

    ucase() 将某个字段转成大写

    lcase() 将某个字段转成小写

    mid() 从某个文本字段提取文字

    len() 返回某个文本字段的长度

    round() 将某个数值字段进行指定小数位数的四舍五入

    now() 返回当前系统时间

    format() 格式化某个字段的显示方式

2、SQL AVG()函数

    select avg(column_name) from table_name;

3、SQL COUNT()函数

SQL count(column_name)语法

select count(column_name) from table_name 

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）

SQL count(*)语法

select count(*) from table_name

COUNT(*) 函数返回表中的记录数

SQL conut(distinct column_name)语法

select count(distinct column_name) from table_name

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

4、SQL FIRST()函数

select first(column_name) from table_name

5、SQL last()函数

select last(column_name) from table_name

6、SQL MAX() 函数

select max(column_name) from table_name

7、SQL MIN()函数

select min(column_name) from table_name

8、SQL SUM()函数

select sum(column_name) from table_name

9、SQL GROUP BY语句

select column_name, aggregate_function(column_name) from table_name where ... group by column_name 

10、SQL HAVING子句

select aggregate_function(column_name),column_name from table_name where... group by column_name having aggregatefunction(column_name) ......

11、SQL UCASE() 函数

select ucase(column_name) from table_name

12、SQL LCASE()函数

select lcase(column_name) from table_name

13、SQL MID()函数

MID() 函数用于从文本字段中提取字符。

select mid(column_name,start_number,length) from table_name-------length是可选值

14、SQL LEN()函数

LEN() 函数返回文本字段中值的长度。

SQL LEN（）语法

select len(column_name) from table_name

MYSQL length() 语法

select length(column_name) from table_name

mysql只有length()函数没有len()函数

15、SQL ROUND()函数

select round(cloumn_name,decimals) from table_name

column_name---列名

decimals---小数点后的位数

16、SQL NOW()函数

select now() from table_name

17、SQL FORMAT()函数

select format(column_name,format) from table_name

18、SQL 快速参考

and/or select column_name(s) from table_name where condition and|or CONDITION

alter table alter table table_name add column_name datatype or alter table table_name drop column column_name

as(alias) select column_name as new_column_name from table_name or select column_naem from table_name as new_table_name

between select column_name(s) from table_name where column_name between ... and ...

create database   create database database_name

create table   create table table_name(column_name datatype constraint_name )

create index create index index_name on table_name(column_name) or create unique index index_name on table_name(column_name)

create view create view view_name as select column_name(s) from table_name

delete delete from table_name where condition or delete from table_name or delete * from table_name

drop index drop index index_name(db2/oracle) or alter table table_name drop index index_name(mysql)

drop table drop table table_name

group by   select column_name,aggregate_function(column_name) from table_name group by column_name

having select column_name,aggregate_function(column_name) from table_name group by column_name having aggregate_function(column_name) condition_name

in select column_name(s) from table_name where column_name in(v1,v2,....)

insert into insert into table_name values(v1,v2,v3...) or insert into table_name(k1,k2,k3.....) values(v1,v2,v3....)

inner join select column_name(s) from table1 inner join table2 on ....(取两表交集）

left join select column_name(s) from table1 left join table2 on ...（左表取全部，右表取两表交集）

right join select column_name(s) from table1 right join table2 on ...(右表取全部，左表取两表交集）

full join select column_name(s) from table1 full join table2 on ...(取两表全集)

like select column_name(s) from table_name where column_name like ...('_'代表一个字符，'%'代表0个或多个字符)

order by   select column_name(s) from table_name order by column_name asc|DESC

select select column_name(s) from table_name

select *   select * from table_name

select distinct select DISTINCT column_name(s) from table_name ----select distinct name,alexa from websites 多列去重

select into select * into new_table_name from old_table_name or select column_name(s) into new_table_name from old_table_name (mysql和oracle都不支持这种方法)

insert into select insert into new_table_name(column_name(s)) select * from old_table_naem or insert into new_table_name select column_name(s) from old_table_name(mysql和oracle都支持此方式，但是新表必须存在，并且列类型应与查询出来的结果集相同)

select top select top number|percent column_name(s)

truncate TABLE    truncate table table_name (无需提交事务)

union   select column_name(s) from table1 union select column_name(s) from table2   (去重)

union all  select column_name(s) from table1 union all select column_name(s) from table2 (不去重)

update update table_name set cloumn_name1 = v1,clumn_name2 = v2

where select * from table1 where .....

（红黑间隔只是为了区分语句，不存在重点。）

 

转载于:https://blog.51cto.com/zhuangyong/1888855