IT忍者神龟之MySQL函数详细描述_mysql lpad性能-CSDN博客

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);