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