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 条件列表;

 

 

 

数据查询:

 

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

则返回: 用户名  密码

 

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

则返回:用户名  学校名称

aa     软院

                                                 bb    软院

  3select userName ,"软院" from users;

则返回 : userName    软院

aa                软院

bb                软院

4select 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中的常用函数:

 

 

(一)字符串函数  

1concats1,s2,s3,...,sn)连接字符串;

2insertstrpos , len ,newstr;

如:insert(“abcd”,1,2,“xp” );    xpcd

3lowerstr) lcase( str )  str所有字符变成小写;

      upperstrucasestr) 把str所有字符变成大写;

4left ( str,len)

如:leftabcd3;   abc

5rightstrlen;  bcd

6lpad(str,len,padstr)

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

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

    7rpad(str,len,padstr)

 

8replacestr from_str,to_str

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

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

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

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

| guanxincd                      |

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

9trimstr)  //去掉字符串前后空格

10substringstrpos,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、truncatex,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、ifexpr1expr2,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

 

4case (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);



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值