数据库函数和高级查询

一、函数（function）：

  1 文本函数

  2 数学函数

  3 日期函数

  4 聚合函数

  5 系统函数

 

1 文本函数(字符串函数)

 

 1.1 获取字符串长度

语法 select LEN(colname) from tbname [where子句]

 

1.2  转换大 小写

语法 select LOWER(varchar() )/ UPPER( varchar() ) [where子句]

 

1.3 去掉左右空格

语法

select LTRIM('      a')

select RTRIM('a      ')

 

1.4 截取字符串

语法

select left('这是从左边截取',(int)num--截取长度)

select right('这是从右边截取',(int)num--截取长度)

select SUBSTRING(varchar( ),(int) num1--起始位置,(int) num2-- 截取长度)

 

 

1.5 字符串替换

语法

select REPLACE(varchar( ),'被更改内容','填入内容')

select STUFF(varchar( ),起始位置,删除长度,'填入内容')

 

1.6 查找字符串

语法 select CHARINDEX(varchar(需要查找内容),varchar(待查对象))

 

1.7 合并字符串函数

MySQL 语法 SELECT CONCAT('华信','智原')

 

sql server 语法 select '3'+'3'

 

2 数学函数

 

2.1 取比参数大的最小整数

2.2 取比参数小的最大整数

2.3 四舍五入（可保留精度）

语法

select CEILING(22.01)

select FLOOR(22.01)

select ROUND(x,y)  y---精度值

提示：y值为负数时，保留的小数点左边的相应位数直接保存为0，不进行四舍五入

 

2.4 获取随机数的函数

语法  SELECT RAND(),RAND(),RAND()

语法  SELECT RAND(8),RAND(8),RAND(8)

 

RAND()返回一个随机的浮点值v，范围在0到1之间(0<= v <=1.0)      RAND(x)用来产生重复序列

 

 

3 日期时间函数 

    

    sql server

3.1 获取当前日期时间

sql server语法 select GETDATE()

 

3.2 将日期指定部分取出以字符串形式返回

sql server语法 select DATENAME(YY,'2019-06-30')

 

3.3 将日期指定部分取出以数值形式返回

sql server语法 select DATEPART(YY,'2017-11-23')

 

3.4 给日期的指定部分加数字（会按照完整的年来加减）

sql server语法  select DATEADD(mm,1,'2015')

 

3.5 两个日期指定部分的差

sql server语法  select DATEDIFF(YY,'2015-10-10','2017-9-30')

 

    MySQL

 3.1 获取当前日期的函数和获取当前时间的函数

语法

SELECT CURDATE()

SELECT CURRENT_DATE()

SELECT CURTIME()

SELECT CURRENT_TIME()

 

 3.2  IF函数条件判断

  语法 ： IF(表达式，返回值v1，返回值v2）  如果表达式是true，则返回值是v1，反之返回值是v2，IF()的返回值为数字或者字符串值

语法示例    SELECT IF(20>3,1,0)

语法示例   SELECT IF(STRCMP('aa','bb'),'yes',0)

两个字符串自左向右逐个字符相比（按ASCII值大小相比较），直到出现不同的字符或遇'\0'为止。

 

3.3 IFNULL(v1,v2)函数

语法  SELECT IFNULL(1,2),IFNULL(NULL,8)

 

3.4 获取最后一个自动生成的ID值的函数

语法  SELECT LAST_INSERT_ID()

MySQL的LAST_INSERT_ID的注意事项：

第一、查询和插入所使用的Connection对象必须是同一个才可以，否则返回值是不可预料的。

使用这函数向一个给定Connection对象返回的值是该Connection对象产生对影响AUTO_INCREMENT列的最新语句第一个AUTO_INCREMENT值的。这个值不能被其它Connection对象的影响，即它们产生它们自己的AUTO_INCREMENT值。

第二、LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID返回表b中的Id值。

第三、 假如你使用一条INSERT语句插入多个行，  LAST_INSERT_ID() 只返回插入的第一行数据时产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。

 

---

 

二、查询

    基本查询：

    select colname,....colname from tbname

    [where]

    [order by]

    [group by]

    [having...]

1 条件查询

 

   1.1  不带条件查询

 

1.1.1  如果要查询表中所有的列，采用通配符

语法 select * from   tbname

 

1.1.2 查询部分列

语法 select colname,colname,... from  tbname

 

 1.2  带条件查询

    1. 2.1 模糊查询

       条件：[not] between and

            [not] like

            [not]in

            is null

语法 select * from tbname where 条件

 

2 利用聚合函数进行查询语句

 

  2.1 数据记录个数总和

语法 select count(emp_no)[自定义名称]from emp

 

2.2 字段数据求和

语法  select sum(colname) [自定义名称]from tbname [where]

 

2.3 字段平均值

语法 select AVG(colname)[自定义名称] from tbname [where]

 

2.4 字段最大值

语法 select MAX(colname)[自定义名称] from tbname [where]

 

2.5 字段最小值

语法  select MIN(colname) [自定义名称] from tbname [where]

 

 

---

 

 

3 排序查询

 

3.1 排序: select * from tbname order by colanme desc(降序)|asc(升序默认，可以不写),colname desc(降序)|asc(升序，可不写)

,.....

语法示例

select * from tbname order by 字段名 desc  

select * from tbname order by 字段名 asc 

3.2 查询前n条数据

sql server语法   select top n * from tbname order by 需要查询的字段 desc

MySQL语法   SELECT DISTINCT 字段名 FROM 表名 ORDER BY 字段名 DESC LIMIT 查询起始位置（0），查询个数（n）

---

 

 

4 分组查询

 

 4.1 基础语法  select 查询字段名,所需函数 from 表名 group by 查询字段名

     ！！！敲黑板 group by 被分组的列名必须跟随聚合函数

例子：

  每个部门岁以上的有多少人,并按降序排列

select dept_no,COUNT(emp_no) from emp

where e_age>=30

group by dept_no

order by COUNT(emp_no) desc

 

每个部门岁以上的人且人数不少于2人

select dept_no,COUNT(emp_no) from emp

where e_age>=30

group by dept_no

having COUNT(emp_no)>=2   having 对分组后的组进行筛选

order by COUNT(emp_no) desc

 

注意：如果在MySQL中赋予了别名 在having后必须跟别名

 

4.2 多列分组

select 大组字段名,小组字段名...[count() 自定义名称] from 表名   group by 大组字段名,小组字段名

 

注意：

1 顺序：where    group by  having  order by 

2 order by后sql server 不允许使用列的别名

3 分组中查询的字段：在select后查询的字段a 出现在group by之后      b 聚合函数中

 

ps ：去掉查询结果中重复的列 关键字 distinct（去重）

语法示例     select distinct 需要查询的列名 from 表名

 

---

 

5 高级查询

 

5.1 多表连接查询

 

     5.1.1 内连接  关键字  inner join  内连接连接两张表*同的列

语法示例

select * from emp

inner join dept

on emp.dept_no = dept.dept_no

 

  课堂案例

查询学生的选课信息（学号学生姓名 教师工号教师姓名课程名称）

select s.stu_no,stu_name,t.t_id,t_name,c.c_id,c_name from stu s

join xuanke x

on s.stu_no=x.stu_no

join  t_c tc

on x.tc_id=tc.tc_id

join teacher t

on t.t_id=tc.t_id

join course c

on c.c_id=tc.c_id

 

     5.1.2 外连接

      5.1.2.1 左外连接    关键字  left [outer] join

语法   select 字段名（可带函数） from 表名 别名 left [outer] join 表名 别名 on 关联列的表达式

课堂案例

查询员工及部门信息

select * from emp e

left outer join

dept d

on e.dept_no=d.dept_no

select * from dept d

left outer join

emp e

on e.dept_no=d.dept_no

 

查询每个部门的人数

select d.dept_no,COUNT(emp_no) from dept d

left outer join

emp e

on e.dept_no=d.dept_no

group by d.dept_no

 

      5.1.2.2 右外连接    关键字 right [outer] join

语法   select 字段名（可带函数） from 表名 别名 right [outer] join 表名 别名 on 关联列的表达式

课堂案例

select * from emp e

right join dept d

on e.dept_no=d.dept_no

 

注意：显示左表中所有的记录，如果左表没有与右表相匹配的，右表将会自动补充null；右链接同样遵循。

      5.1.2.3 完全外连接  

           sql server   关键字    full [outer] join

语法   select 字段名（可带函数） from 表名 别名 full  [outer] join 表名 别名 on 关联列的表达式

 

课堂案例

select d.dept_no,COUNT(emp_no) from emp e

full join

dept d

on e.dept_no=d.dept_no

group by d.dept_no

 

                MySQL       union all  合并结果集         union  合并结果集 去掉重复

示例

SELECT * FROM emp e

LEFT JOIN

dept d

ON e.dept_no=d.dept_no

UNION

SELECT * FROM emp e

RIGHT JOIN

dept d

ON e.dept_no=d.dept_no

 

备注：完全连接相当于左右连接同时使用，删除重复的数据

 

     5.1.3 等值连接  不添加筛选条件时，把两个表的数据挨个匹配

示例

select * from emp e,dept d

where e.dept_no=d.dept_no

 

select * from emp  e,dept d

where e.dept_no=d.dept_no

and d_name='财务部'

 

     5.1.4 交叉连接（笛卡尔积） 关键字  cross

                        作用：和等值连接的作用相同

示例

select * from emp e

cross join

dept d

where e.dept_no=d.dept_no

 

 

---

 

子查询

 

子查询：

    在select    updata    insert    delete中使用查询语句

    注意：（）

    分类：

• 嵌套子查询

• 相关子查询

    别名：

• 起别名

• 不起别名

简单示例

 

select emp_no,e_sal,dept_no from(

select emp_no,e_sal,d_name,e_hit,e.dept_no

from emp e

join dept d

on e.dept_no=d.dept_no

)as e

where e_sal>5000

order by e_sal desc

 

删除财务部的员工信息

 

delete from emp

where dept_no=(

select dept_no from dept

where d_name='财务部'

)

 

where

    1.关系运算符    >    <    =

        子查询结果一定是单个值

    2.[not]  in

        子查询结果    单列    多个值

    3.[not] exists(true/false)

        子查询结果任意，可以是单个值或多个值，只判断子查询是否查到结果，不关注结果是什么

    4.any    some

 

关系运算符示例

查询财务部的员工信息

 

select * from emp

where dept_no=(

select dept_no from dept

where d_name='财务部'

)

 

工资高于平均工资的员工信息

 

select * from emp

where e_sal>(

select AVG(e_sal) from emp

)

 

[not]  in

注意：子查询中的空值问题

查询有员工的部门信息

 

select * from dept

where dept_no in(

select distinct dept_no from emp

)

 

查询没有员工的部门信息

 

select * from dept

where dept_no not in(

select distinct dept_no from emp

where dept_no is not null

)

 

exists

查询有员工的部门

 

select * from dept d

where

     exists(

          select 1 from emp e    --select 1 只需要判断是否存在，不需要查询所有记录

          where e.dept_no=d.dept_no

     )

 

exists效率比in 高

 

查询每个部门中工资高于部门平均工资的员工信息

 

select * from emp e1

where e.sal>

(

     select avg(e_sal) from emp e2

     where e1.dept_no=e2.dept_no

)

 

 

分页

    1.记录总数

    2.每页显示的记录数

    3.当前页码

sql server

 

select top 每页显示记录数 * from emp

where emp_no not in(

     select top ((当前页码 - 1) * 每页显示记录数) emp_no from emp order by emp_no desc

)

order by emp_no desc

 

MySQL 中limit 后面不能跟表达式，需要先定义变量int a = (当前页码 - 1) * 每页显示记录数;

 

复制表结构select  into ... from tbname

into后的表不能事先存在，该语句会自动创建表

从emp表复制emp_no,e_sal字段到emp2表，同时复制表结构和数据

 

select emp_no,e_sal into emp2 from emp

 

从emp表复制emp_no,e_sal字段到emp2表，只复制表结构，不复制数据，where后面可以加限制条件

 

select emp_no,e_sal into emp2 from emp

where 1!=1

 

insert into tbname select from --只复制数据，不复制表结构，tbname需要事先创建

create table emp2(

)

 

insert into emp2 select emp_no,e_sal from emp where e_sal > 6000