IT忍者神龟之MySQL函数详细描述

CRUD:增删改查   create read update delete 

 

插入语句：insert into 表名（字段名）value(相对应的值)；

插入多条语句：insert into 表面（字段名）values（相对应的值），（相对应的值）；

修改： update 表名 set 字段名=值 where 条件列表；

删除：delete from 表名 where 条件列表；

查询：

查询所有：select * from 表名 where 条件列表；

查询某些字段：select 字段，字段 from 表名 where 条件列表；

 

 

 

数据查询：

 

1、select userName as "用户名"，password “密码” from users;

则返回： 用户名  密码

 

2、select userName as "用户名"，“软院”as “学校名称”from users;

则返回：用户名  学校名称

aa     软院

                                                 bb    软院

  3、select userName ,"软院" from users;

则返回 ： userName    软院

aa                软院

bb                软院

4、select userName from users limit 0,4 ;

(分页 )   从第一条（下标为0）开始取四条

5、查询排序：

select * from users order by height;//默认为升序

select * from users  order by height asc;

select * from users  order by height desc;

MySql中的常用函数：

 

 

（一）字符串函数  

1、concat（s1,s2,s3,...,sn）连接字符串；

2、insert（str，pos , len ,newstr）;

如：insert（“abcd”，1,2，“xp” ）;    xpcd

3、lower（str） lcase( str )  把str所有字符变成小写；

      upper（str）ucase（str） 把str所有字符变成大写；

4、left ( str,len)

如：left（abcd，3）;   abc

5、right（str，len）;  bcd

6、lpad(str,len,padstr)

如：mysql>select lpad("abc",5,"***");  **abc// 长度大于字符串长度 则补入*补成len长度的字符串

       mysql>select lpad("abc",2,"**");  ab //长度小于字符串长度，则从左裁剪等长

    7、rpad(str,len,padstr)

 

8、replace（str ，from_str,to_str）

如：mysql>  select replace("abcd","ab","guanxin");

+--------------------------------+

| replace("abcd","ab","guanxin") |

+--------------------------------+

| guanxincd                      |

+--------------------------------+

9、trim（str）  //去掉字符串前后空格

10、substring（str，pos,len）

如：mysql>  select substring("abcdef",1,5);

+-------------------------+

| substring("abcdef",1,5) |

+-------------------------+

| abcde                   |

+-------------------------+

 

 

(二) 数值函数

1、abs(x) //返回x的绝对值

如：mysql> select abs(-3.14);

+------------+

| abs(-3.14) |

+------------+

| 3.14       |

+------------+

         2、rand()//返回0-1之间的随机数

如：mysql> select rand();

+---------------------+

| rand()              |

+---------------------+

| 0.38327023888311373 |

+---------------------+

1 row in set

 

mysql> select rand();

+--------------------+

| rand()             |

+--------------------+

| 0.6705344940261305 |

+--------------------+

1 row in set

            Rand(x);//返回为一样的0-1之间的随机数

如：mysql> select rand(1);

+---------------------+

| rand(1)             |

+---------------------+

| 0.40540353712197724 |

+---------------------+

1 row in set

 

mysql> select rand(1);

+---------------------+

| rand(1)             |

+---------------------+

| 0.40540353712197724 |

+---------------------+

1 row in set

 

mysql> select rand(11);

+-------------------+

| rand(11)          |

+-------------------+

| 0.907234631392392 |

+-------------------+

1 row in set

 3、truncate（x,y）//x:小数  y:取小数点几位

如：mysql> select truncate(3.14566,3);

+---------------------+

| truncate(3.14566,3) |

+---------------------+

| 3.145               |

+---------------------+

1 row in set

 

mysql> select truncate(3.14566,0);

+---------------------+

| truncate(3.14566,0) |

+---------------------+

| 3                   |

+---------------------+

1 row in set

 

 

（三）日期函数：

1、curdate() //当前日期

如：mysql> select curdate();

+------------+

| curdate()  |

+------------+

| 2013-09-23 |

+------------+

1 row in set

2、curtime()//当前时间

如：mysql> select curtime();

+-----------+

| curtime() |

+-----------+

| 22:54:52  |

+-----------+

1 row in set

3、now()、 localtime()、localtime、sysdate()、

如：返回的都是：

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2013-09-23 22:56:27 |

+---------------------+

1 row in set

 

mysql> select localtime();

+---------------------+

| localtime()         |

+---------------------+

| 2013-09-23 22:56:54 |

+---------------------+

1 row in set

 

mysql> select localtime;

+---------------------+

| localtime           |

+---------------------+

| 2013-09-23 22:57:26 |

+---------------------+

1 row in set

 

 

mysql> select sysdate();

+---------------------+

| sysdate()           |

+---------------------+

| 2013-09-23 22:58:04 |

+---------------------+

1 row in set

4、year(date)//返回年份

如：mysql> select year(now());

+-------------+

| year(now()) |

+-------------+

|        2013 |

+-------------+

1 row in set

 

mysql> select(localtime());

+---------------------+

| (localtime())       |

+---------------------+

| 2013-09-23 22:59:40 |

+---------------------+

1 row in set

5、month(date)//返回月份

如：mysql> select month(localtime);

+------------------+

| month(localtime) |

+------------------+

|                9 |

+------------------+

1 row in set

 

mysql> select month(sysdate());

+------------------+

| month(sysdate()) |

+------------------+

|                9 |

+------------------+

1 row in set

 

 

 

 

 

6、monthname(date)//返回月份的英文名

  如：mysql> select monthname(now());

+------------------+

| monthname(now()) |

+------------------+

| September        |

+------------------+

1 row in set

（四）流程函数

   1、if（expr1，expr2，expr3）；expr1如果为true 则返回expr2的值，如果为false 则返回expr3的值

mysql> select if(age=20,100,age) from student;

+--------------------+

| if(age=20,100,age) |

+--------------------+

|                 21 |

|                 22 |

|                100 |

|                 28 |

|                 26 |

+--------------------+

5 rows in se

2、ifnull(expr1,expr2) 如果expr1不为空，则返回expr1的值，如果expr1为空则返回expr2的值

mysql> select userName from student;

+----------+

| userName |

+----------+

| NULL     |

| 李四     |

| 张海天   |

| 张欣     |

| 张传     |

+----------+

5 rows in set

 

mysql> select ifnull(userName,"空");

mysql>  select ifnull(userName,"空")from student;

+-----------------------+

| ifnull(userName,"空") |

+-----------------------+

| 空                    |

| 李四                  |

| 张海天                |

| 张欣                  |

| 张传                  |

+-----------------------+

5 rows in set

3、case when(value) then (result) else (defailt) end

mysql> select age from student;

+-----+

| age |

+-----+

|  21 |

|  22 |

|  20 |

|  28 |

|  26 |

+-----+

5 rows in set

mysql> select case when age>27 then "最大" else age end 

from student;

+-------------------------------------------+

| case when age>27 then "最大" else age end |

+-------------------------------------------+

| 21                                        |

| 22                                        |

| 20                                        |

| 最大                                      |

| 26                                        |

+-------------------------------------------+

5 rows in set

 

4、case (expr) when (value) then (result) end

mysql> select age from student;

+-----+

| age |

+-----+

|  21 |

|  22 |

|  20 |

|  28 |

|  26 |

+-----+

5 rows in set

 

 

 

mysql> select case age when 28 then "最大" when 20 then "最小" else age end from student;

+---------------------------------------------------------------+

| case age when 28 then "最大" when 20 then "最小" else age end |

+---------------------------------------------------------------+

| 21                                                            |

| 22                                                            |

| 最小                                                          |

| 最大                                                          |

| 26                                                            |

+---------------------------------------------------------------+

5 rows in set

 

 

(五)系统函数

1、返回当前数据库名称：

mysql> select database();

+--------------+

| database()   |

+--------------+

| student_test |

+--------------+

1 row in set

2、返回当前数据库版本信息

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.5.25    |

+-----------+

1 row in set

 

3、返回当前登录用户信息：

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set

 

 

 

 

 

4、加密后返回字符串

mysql>  select password(userNumber) from student where userId=1;

 

+-------------------------------------------+

| password(userNumber)                      |

+-------------------------------------------+

| *6FECC218350DB1E056A9D5DC8A42CD3948AFEB6D |

+-------------------------------------------+

1 row in set

 

mysql>  select md5(userNumber) from student where userId=1;

+----------------------------------+

| md5(userNumber)                  |

+----------------------------------+

| 3785ef83d0f3ea3541f3a1cb49f75b0e |

+----------------------------------+

1 row in set

 集合运算 
操作两组查询结果，进行交集、并集、减集运算 
1、 union和union all进行并集运算 
--union 并集、不重复 
select id, name from student where name like 'ja%' 
union 
select id, name from student where id = 4; 
--并集、重复 
select * from student where name like 'ja%' 
union all 
select * from student; 
2、 intersect进行交集运算 
--交集（相同部分） 
select * from student where name like 'ja%' 
intersect 
select * from student; 
3、 except进行减集运算 
--减集（除相同部分） 
select * from student where name like 'ja%' 
except 
select * from student where name like 'jas%';

 连接查询 
1、 简化连接查询 
--简化联接查询 
select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id; 
2、 left join左连接 
--左连接 
select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;
3、 right join右连接 
--右连接 
select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;
4、 inner join内连接 
--内连接 
select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;
--inner可以省略 
select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id; 

cross join交叉连接 
--交叉联接查询，结果是一个笛卡儿乘积 
select s.id, s.name, c.id, c.name from student s cross join classes c 
--where s.cid = c.id; 

自连接（同一张表进行连接查询） 
--自连接 
select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;

函数 
1、 聚合函数 
max最大值、min最小值、count统计、avg平均值、sum求和、var求方差 
select 
max(age) max_age, 
min(age) min_age, 
count(age) count_age, 
avg(age) avg_age, 
sum(age) sum_age, 
var(age) var_age 
from student; 
2、 日期时间函数 
select dateAdd(day, 3, getDate());--加天 
select dateAdd(year, 3, getDate());--加年 
select dateAdd(hour, 3, getDate());--加小时 
--返回跨两个指定日期的日期边界数和时间边界数 
select dateDiff(day, '2011-06-20', getDate()); 
--相差秒数 
select dateDiff(second, '2011-06-22 11:00:00', getDate()); 
--相差小时数 
select dateDiff(hour, '2011-06-22 10:00:00', getDate()); 
select dateName(month, getDate());--当前月份 
select dateName(minute, getDate());--当前分钟 
select dateName(weekday, getDate());--当前星期 
select datePart(month, getDate());--当前月份 
select datePart(weekday, getDate());--当前星期 
select datePart(second, getDate());--当前秒数 
select day(getDate());--返回当前日期天数 
select day('2011-06-30');--返回当前日期天数 
select month(getDate());--返回当前日期月份 
select month('2011-11-10'); 
select year(getDate());--返回当前日期年份 
select year('2010-11-10'); 
select getDate();--当前系统日期 
select getUTCDate();--utc日期 
3、 数学函数 
select pi();--PI函数 
select rand(100), rand(50), rand(), rand();--随机数 
select round(rand(), 3), round(rand(100), 5);--精确小数位 
--精确位数，负数表示小数点前 
select round(123.456, 2), round(254.124, -2); 
select round(123.4567, 1, 2); 
4、 元数据 
select col_name(object_id('student'), 1);--返回列名 
select col_name(object_id('student'), 2); 
--该列数据类型长度 
select col_length('student', col_name(object_id('student'), 2)); 
--该列数据类型长度 
select col_length('student', col_name(object_id('student'), 1)); 
--返回类型名称、类型id 
select type_name(type_id('varchar')), type_id('varchar'); 
--返回列类型长度 
select columnProperty(object_id('student'), 'name', 'PRECISION'); 
--返回列所在索引位置 
select columnProperty(object_id('student'), 'sex', 'ColumnId');  用户自定义函数 
# 查看当前数据库所有函数 
--查询所有已创建函数 
select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
and type in('fn', 'if', 'tf'); 
# 创建函数 
if (object_id('fun_add', 'fn') is not null) 
drop function fun_add 
go 
create function fun_add(@num1 int, @num2 int) 
returns int 
with execute as caller 
as 
begin 
declare @result int; 
if (@num1 is null) 
set @num1 = 0; 
if (@num2 is null) 
set @num2 = 0; 
set @result = @num1 + @num2; 
return @result; 
end 
go 
调用函数 
select dbo.fun_add(id, age) from student; 
--自定义函数，字符串连接 
if (object_id('fun_append', 'fn') is not null) 
drop function fun_append 
go 
create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024)) 
returns nvarchar(2048) 
as 
begin 
return @args + @args2; 
end 
go 
select dbo.fun_append(name, 'abc') from student; 
# 修改函数 
alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024)) 
returns nvarchar(1024) 
as 
begin 
declare @result varchar(1024); 
--coalesce返回第一个不为null的值 
set @args = coalesce(@args, ''); 
set @args2 = coalesce(@args2, '');; 
set @result = @args + @args2; 
return @result; 
end 
go 
select dbo.fun_append(name, '#abc') from student; 
# 返回table类型函数 
--返回table对象函数 
select name, object_id, type from sys.objects where type in ('fn', 'if', 'tf') or type like '%f%';
if (exists (select * from sys.objects where type in ('fn', 'if', 'tf') and name = 'fun_find_stuRecord'))
drop function fun_find_stuRecord 
go 
create function fun_find_stuRecord(@id int) 
returns table 
as 
return (select * from student where id = @id); 
go 
select * from dbo.fun_find_stuRecord(2);